Examen: Sistemas de base de datos

Dado el siguiente modelo relacional:
Sectores(sector_id, sector_desc)
Empleados(emp_id, emp_apellido, emp_nombre, emp_sexo, emp_fecnac, emp_fecing, emp_id_supervisor, emp_sector_id)
Tipos_accidentes(ta_id, ta_desc)
Accidentes(acc_id, acc_fecha, acc_severidad, acc_emp_id, acc_sector_id, acc_ta_id)

Se pide:

1. Escribir los comandos del DDL necesarios para crear dichas tablas considerando:

Dominios:
o sector_id, emp_sector_id, acc_sector_id: alfanumérico de 2 posiciones.
o acc_id, emp_id, emp_id_supervisor, acc_emp_id: entero de 5 dígitos.
o ta_id, acc_ta_id: alfanumérico de 3 posiciones.
o sector_desc, emp_apellido, emp_nombre, ta_desc: alfanumérico de a lo sumo 30 posiciones.
o emp_sexo: alfanumérico de 1 posicion.
o emp_fecnac, emp_fecing, acc_fecha: fecha y hora.
o acc_severidad: número entero de 2 dígitos.
Autonumerados (de 1 en 1):
o acc_id

Restricciones:
o Todos los campos que contienen _id y son numéricos deben ser mayores a cero.
o emp_apellido, emp_nombre: debe comenzar con una letra mayúscula.
o emp_sexo: solo admite carácter ‘M’ o ‘F’.
o sector_id, emp_id, ta_id, acc_id: son claves primarias en las tablas Sectores, Empleados,
Tipos_accidentes y Accidentes.
o emp_id_supervisor: es clave foránea en Empleados tomando solo valores que pertenecen al dominio del atributo emp_id en la tabla Empleados.
o emp_sector_id: es clave foránea en Empleados tomando solo valores que pertenecen al dominio del
atributo sector_id en la tabla Sectores.
o acc_emp_id: es clave foránea en Accidentes tomando solo valores que pertenecen al dominio del atributo
emp_id en la tabla Empleados.
o acc_sector_id: es clave foránea en Accidentes tomando solo valores que pertenecen al dominio del atributo
sector_id en la tabla Sectores.
o acc_ta_id: es clave foránea en Accidentes tomando solo valores que pertenecen al dominio del atributo ta_id en la tabla Tipos_accidentes.
o emp_fecing: debe ser mayor o igual a 01/01/2005 y no debe admitir valores nulos.
o acc_severidad: entre 1 y 10.

2. Definir un store procedure llamado lista_igualxacci que reciba como parámetros de entrada 2 fechas y que liste los códigos de tipos de accidentes, descripciones de tipos de accidentes y para cada uno de ellos la cantidad de accidentes que se produjeron en ese rango de fechas para los empleados que se accidentaron en el mismo sector donde trabajan. La consulta debe ordenarse en forma descendente por cantidad de accidentes y a coincidencia de dicho valor en forma ascendente por nombre de accidente.

3. a) Definir una función llamada acc_x_aniosector que reciba como parámetro el valor de un año y devuelva como
resultado una tabla con la siguiente estructura:
sector_id: alfanumérico de 2 posiciones.
cantidad_accidentes: numérico de 5 dígitos.
A nivel de filas, deberá contener todos los sectores que tuvieron accidentes de trabajo en el valor del año pasado como parámetro y para cada sector la cantidad total de accidentes que se produjeron en dicho año.
b) Definir el comando necesario (utilizando la función del punto anterior) para resolver la siguiente consulta:
ID Sector Nombre Sector Cantidad accidentes 2009 Cantidad accidentes 2010
Se deberán incluir todos los sectores independientemente que haya o no tenido accidentes en esos años.

4. Como la tabla de accidentes tiene gran cantidad de registros se nos pide:
4.1 Definir el comando SQL para agregar un atributo cant_accidentes en la tabla Tipos_accidentes con dominio entero de
5 dígitos y valores mayores o iguales que cero.
4.2 Definir el comando SQL para actualizar este campo según la cantidad de accidentes que tiene la instancia actual de la
tabla Accidentes.
4.3 Definir un trigger con nombre act_tipo_acc de tipo “después de” en la tabla accidentes, de forma tal que ante
inserciones, modificaciones y borrados en dicha tabla, actualice en forma automática el campo definido en el punto
4.1.

5. Definir un trigger llamado ctrl_accidentes de tipo “en lugar de” que actué sobre la tabla accidentes por inserciones o modificaciones y que verifique que las filas que se insertan o modifiquen corresponden a empleados que tienen más de 18 años a la fecha. Si una o mas filas de las que se insertan no cumple con esta condición, emitir el mensaje de error “No se permite el ingreso de accidentes de menores de edad” y volver atrás todo el lote que se estaba insertando.
Sugerencia: para verificar si dada una fecha transcurrieron más de 18 años, utilizar la siguiente expresión: DATEDIFF(day, fecha, getdate()) / 365 y comparar el resultado contra la constante 18

Solución:
/* Item 1 */
create table Sectores
(
sector_id char(2) primary key,
sector_desc varchar(30)
)
create table Empleados
(
emp_id numeric(5) check(emp_id > 0) primary key,
emp_apellido varchar(30) check(emp_apellido like ‘[A-Z]%’),
emp_nombre varchar(30) check(emp_nombre like ‘[A-Z]%’),
emp_sexo char(1) check(emp_sexo in (‘M’,’F’)),
emp_fecnac datetime,
emp_fecing datetime not null check(emp_fecing >= ‘20050101’),
emp_id_supervisor numeric(5) check(emp_id_supervisor > 0) foreign key references Empleados(emp_id),
emp_sector_id char(2) foreign key references Sectores(sector_id)
)
create table Tipos_accidentes
(
ta_id char(3) primary key,
ta_desc varchar(30)
)
create table Accidentes
(
acc_id numeric(5) check(acc_id > 0) identity primary key,
acc_fecha datetime,
acc_severidad numeric(2) check(acc_severidad between 1 and 10),
acc_emp_id numeric(5) check(acc_emp_id > 0) foreign key references Empleados(emp_id),
acc_sector_id char(2) foreign key references Sectores(sector_id),
acc_ta_id char(3) foreign key references Tipos_accidentes(ta_id)
)

/* Item 2 */
create procedure lista_igualxacci
@fecha_desde datetime,
@fecha_hasta datetime
as
select TA.ta_id as [Codigo Tipo Accidente], ta_desc as [Descripcion Tipo de Accidente], count(*) as Cantidad
from Accidentes A
inner join
Tipos_accidentes TA
on A.acc_ta_id = TA.ta_id
inner join
Empleados E
on A.acc_emp_id = E.emp_id
where A.acc_sector_id = E.emp_sector_id AND
acc_fecha between @fecha_desde AND @fecha_hasta
group by Ta.ta_id, ta_desc
order by 3 desc, 2
Universidad del Salvador
Sistemas de Información Sistemas de Bases de Datos 2do. Parcial

/* Item 3 a */
create function acc_x_aniosector (@anio numeric(4)) returns @T1 table
(sector_id char(2),
cantidad_accidentes numeric(5)
)
as
begin
insert into @T1
select acc_sector_id, count(*)
from Accidentes A
where year(acc_fecha) = @anio
group by acc_sector_id
return
end

/* Item 3 b */
select S.sector_id as [ID Sector],
sector_desc as [Nombre Sector],
isnull(T2009.cantidad_accidentes,0) as [Cantidad accidentes 2009],
isnull(T2010.cantidad_accidentes,0) as [Cantidad accidentes 2010]
from Sectores S
left outer join
dbo.acc_x_aniosector(2009) T2009
on S.sector_id = T2009.sector_id
left outer join
dbo.acc_x_aniosector(2010) T2010
on S.sector_id = T2010.sector_id

/* Item 4 1 */
alter table Tipos_accidentes
add cant_accidentes numeric(5) check(cant_accidentes >= 0)

/* Item 4 2 */
update Tipos_accidentes
set cant_accidentes = isnull(cant_accidentes,0) + cantidad
from Tipos_accidentes T
inner join
(Select acc_ta_id, count(*) as cantidad
from Accidentes
group by acc_ta_id
) CA
on T.ta_id = Ca.acc_ta_id

/* Item 4 3 */
create trigger act_tipo_acc on Accidentes after insert, update, delete
as
update Tipos_accidentes
set cant_accidentes = isnull(cant_accidentes,0) + cantidad
from Tipos_accidentes T
inner join
(Select acc_ta_id, count(*) as cantidad
from Inserted
group by acc_ta_id
) CA
on T.ta_id = Ca.acc_ta_id
update Tipos_accidentes
set cant_accidentes = isnull(cant_accidentes,0) – cantidad
from Tipos_accidentes T
inner join
(Select acc_ta_id, count(*) as cantidad
from Deleted
group by acc_ta_id
) CA
on T.ta_id = Ca.acc_ta_id

/* Item 5 */
create trigger ctrl_accidentes on Accidentes instead of insert, update
as
if exists (select 1
from Inserted I
inner join
Empleados E
on I.acc_emp_id = E.emp_id
where (DATEDIFF(day, emp_fecnac, getdate()) / 365 ) < 18
)
RAISERROR(‘No se permite el ingreso de accidentes de menores de edad’,16,1)
else
if exists(select 1 from deleted)
update Accidentes
set acc_fecha = i.acc_fecha,
acc_severidad = i.acc_severidad,
acc_emp_id = i.acc_emp_id,
acc_sector_id = i.acc_sector_id,
acc_ta_id = i.acc_ta_id
from Accidentes A inner join Inserted I
on A.acc_id = I.acc_id
else
insert into Accidentes(acc_fecha,acc_severidad,acc_emp_id,acc_sector_id,acc_ta_id)
select acc_fecha,acc_severidad,acc_emp_id,acc_sector_id,acc_ta_id
from Inserted