IBD_Parcial_2016_solución

--- Borrado de tablas
drop table IF EXISTS producto cascade;
drop table IF EXISTS sub_prod cascade;
drop table IF EXISTS problema cascade;
drop table IF EXISTS desarrollador cascade;

--- Script para crear las tablas
create table producto (
id_producto varchar(5) not null,
descripcion varchar(80) not null,
fecha_cierre timestamp,
constraint pk_producto primary key(id_producto)
);
create table sub_prod (
id_sub_prod int not null,
id_producto varchar(5) not null,
descripcion varchar(80) not null,
version_cerrada numeric(10,2) not null,
constraint pk_sub_prod primary key(id_sub_prod, id_producto)
);
create table problema (
id_problema int not null,
id_sub_prod int not null,
id_producto varchar(5) not null,
id_equ_reporta char(1) not null,
id_des_reporta int not null,
id_equ_a_cargo char(1),
id_des_a_cargo int,
fecha_reporte timestamp not null,
descripcion varchar(80) not null,
fecha_cierre timestamp,
cantidad_horas numeric(10,2) not null,
constraint pk_problema primary key(id_problema)
);
create table desarrollador(
id_equipo char(1) not null,
id_desar int not null,
nombre varchar(80) not null,
apellido varchar(80) not null,
e_mail varchar(120),
fecha_ingreso timestamp not null,
salario numeric(10,2),
constraint pk_desarrollador primary key(id_equipo, id_desar)
);
alter table sub_prod add constraint fk1 foreign key (id_producto) references producto(id_producto) on delete cascade on update cascade;
alter table problema add constraint fk2 foreign key (id_sub_prod, id_producto) references sub_prod (id_sub_prod, id_producto) on delete restrict on update cascade;
alter table problema add constraint fk_reporta foreign key(id_equ_reporta, id_des_reporta) references desarrollador(id_equipo, id_desar) on delete cascade on update cascade;
alter table problema add constraint fk_aCargo foreign key(id_equ_a_cargo, id_des_a_cargo) references desarrollador(id_equipo, id_desar) on delete set null  on update set null;
-- Inserts
insert into producto (id_producto, descripcion, fecha_cierre) values ('P1','Producto 1', to_date('02/26/2016','dd/MM/yyyy'));
insert into producto (id_producto, descripcion, fecha_cierre) values ('P2','Producto 2', null);
insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (1, 'P1', 'Descripción Sub Producto 1-P1', 2.3);
insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (2, 'P1', 'Descripción Sub Producto 2-P1', 2.7);
insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (3, 'P1', 'Descripción Sub Producto 3-P1', 3.2);
insert into desarrollador(id_equipo, id_desar, nombre, apellido, e_mail, fecha_ingreso, salario) values ('A', 1, 'Juan', 'Perez', 'jperez@gmail.com', to_date('2/1/2005','dd/MM/yyyy'), 10000);
insert into desarrollador(id_equipo, id_desar, nombre, apellido, e_mail, fecha_ingreso, salario) values ('A', 2, 'Jhon', 'Doe', 'jdoe@gmail.com', to_date('1/2/2005','dd/MM/yyyy'), 11000);
insert into desarrollador(id_equipo, id_desar, nombre, apellido, e_mail, fecha_ingreso, salario) values ('A', 3, 'Juana', 'Perez', 'japerez@gmail.com', to_date('2/1/2003','dd/MM/yyyy'), 12000);
insert into desarrollador(id_equipo, id_desar, nombre, apellido, e_mail, fecha_ingreso, salario) values ('B', 1, 'Jane', 'Doe', 'jadoe@gmail.com', to_date('2/1/2007','dd/MM/yyyy'), 13000);
insert into problema(id_problema, id_sub_prod, id_producto, id_equ_reporta, id_des_reporta, id_equ_a_cargo, id_des_a_cargo, fecha_reporte, descripcion, fecha_cierre, cantidad_horas) values
(1, 1, 'P1', 'A', 1, null, null, to_date('1/5/2010','dd/MM/yyyy'), 'No funciona la pantalla de login', to_date('8/5/2010','dd/MM/yyyy'), 20);
insert into problema(id_problema, id_sub_prod, id_producto, id_equ_reporta, id_des_reporta, id_equ_a_cargo, id_des_a_cargo, fecha_reporte, descripcion, fecha_cierre, cantidad_horas) values
(2, 2, 'P1', 'A', 2, 'A', 1, to_date('1/6/2011','dd/MM/yyyy'), 'No funciona la pantalla de CC', to_date('8/6/2011','dd/MM/yyyy'), 10);
insert into problema(id_problema, id_sub_prod, id_producto, id_equ_reporta, id_des_reporta, id_equ_a_cargo, id_des_a_cargo, fecha_reporte, descripcion, fecha_cierre, cantidad_horas) values
(3, 3, 'P1', 'A', 1, 'A', 2, to_date('1/5/2013','dd/MM/yyyy'), 'No funciona el AMB de Cliente', to_date('8/5/2013','dd/MM/yyyy'), 50);

----
/*
EJERCICIO 1
Las modalidades para Borrado y Modificación a derecha para cada una de las restricciones de integridad referencial son las siguientes:
FK1: (cascade, cascade)  -  FK2: (restrict, cascade)  - FK_Reporta: (cascade, cascade)  - FK_aCargo: (null, null) match simple
Suponga que la BD está instanciada sólo con las tuplas adjuntas en este ejercicio.
Explique el efecto de las siguientes sentencias sobre cada tabla de la BD, haciendo clara referencia a las RIR que se activan en cada caso (NOTA: en cada caso considere el efecto sobre la INSTANCIA ORIGINAL de la BD, los resultados NO SON ACUMULATIVOS):
a)    DELETE FROM PRODUCTO WHERE id_producto = 'P1';

Las RIR que se activan son FK1 - cascade  y FK2 - restrict. Se intentaría borrar el producto cuyo id_producto es P1 y todos los registros de sub_prod (P1,1 ; P1,2 y P1,3) pero todos estan siendo referenciados desde problema.

b)    UPDATE PRODUCTO SET id_producto = 'P3' WHERE id_producto = 'P1';
Las RIR que se activan son FK1 - cascade  y FK2 - cascade. Se actualiza en sub_prod y problema todos los registros que hacen referencia a P1 reemplazandolo por P3.

c)    DELETE FROM PRODUCTO;
Las RIR que se activan son FK1 - cascade  y FK2 - restrict. Se intentaría borrar todos los registros producto y  de sub_prod (P1,1 ; P1,2 y P1,3) pero todos estan siendo referenciados desde problema.

d)    DELETE FROM DESARROLLADOR WHERE id_desar = 2;

e)    UPDATE DESARROLLADOR id_desar = 5 WHERE id_desar = 1;
f)    UPDATE PROBLEMA set id_equ_a_cargo = 'A', id_des_a_cargo = null  where id_problema = 2

*/


-- EJERCICIO 2 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
--Los desarrolladores sin salario deben tener indicada la dirección de mail
ALTER TABLE desarrollador
ADD CONSTRAINT CK_2_1 CHECK (e_mail IS NOT NULL OR salario IS NOT NULL);

-- EJERCICIO 3 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
-- No puede haber problemas cuya fecha de cierre sea posterior a la fecha de cierre del producto al que están asociados
CREATE ASSERTION CK_2_2
CHECK NOT EXISTS
(SELECT 1
FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
where P.fecha_cierre > O.fecha_cierre);

-- EJERCICIO 4 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
-- El total de horas que pueden dedicarse a resolver problemas de un mismo subproducto tiene un tope de 500.
CREATE ASSERTION CK_2_3
CHECK NOT EXISTS
(SELECT 1
FROM PROBLEMA P
GROUP BY id_problema, id_sub_prod
HAVING SUM(cantidad_horas) > 500);

-- EJERCICIO 5 Especifique el/los trigger/s (con su/s función/es) necesarios para implementar en Postgresql la restricción:
-- "No puede haber problemas cuya fecha de cierre sea posterior a la fecha de cierre del producto al que están asociados


CREATE OR REPLACE FUNCTION FN_FECHA_CIERRE()
RETURNS trigger AS $$
DECLARE
    cantidad integer;
BEGIN
    IF (TG_NAME = 'CK_2_2_PROBLEMA') THEN
		SELECT COUNT(*) INTO CANTIDAD
		FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
		where P.fecha_cierre > O.fecha_cierre
		AND id_problema = NEW.id_problema;
	END IF;
    IF (TG_NAME = 'CK_2_2_PRODUCTO') THEN
		SELECT COUNT(*) INTO CANTIDAD
		FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
		where P.fecha_cierre > O.fecha_cierre
		AND id_producto = NEW.id_producto;
	END IF;
		IF ( cantidad > 0 ) THEN
		RAISE EXCEPTION 'fecha de cierre de problemas mayor que la de producto';
	END IF;
RETURN NEW;
END $$
LANGUAGE 'plpgsql';

CREATE TRIGGER CK_2_2_PROBLEMA
BEFORE INSERT OR UPDATE OF fecha_cierre ON PROBLEMA
FOR EACH ROW EXECUTE PROCEDURE FN_FECHA_CIERRE();

CREATE TRIGGER CK_2_2_PRODUCTO
BEFORE UPDATE OF fecha_cierre ON PRODUCTO
FOR EACH ROW EXECUTE PROCEDURE FN_FECHA_CIERRE();


-- EJERCICIO 6 Escriba una consulta SELECT que permita resolver la siguiente consulta
-- Obtener todos los datos de los desarrolladores que no han reportado ni que han estado a cargo de problemas
SELECT *
FROM DESARROLLADOR
WHERE (id_equipo, id_desar) NOT IN
(SELECT id_equ_reporta, id_des_reporta FROM problema)
AND (id_equipo, id_desar) NOT IN
(SELECT id_equ_a_cargo, id_des_a_cargo FROM problema);

-- EJERCICIO 7 Escriba una consulta SELECT que permita resolver la siguiente consulta
-- Listar los Desarrolladores ociosos en la última semana, es decir aquellos que no han sido asignados a ningún problema de ningún producto en la última semana.
SELECT *
FROM DESARROLLADOR
WHERE (id_equipo, id_desar) NOT IN
(SELECT id_equ_a_cargo, id_des_a_cargo
FROM problema
WHERE fecha_reporte > (current_date - 7) );


-- EJERCICIO 8 Realice un procedimiento que al ejecutarse borre los datos de la tabla ReporteProblemas (si hubieran) y la complete con los productos no cerrados conjuntamente con los problemas no resueltos (fechas de cierre nulas)
create table ReporteProblemas (
id_producto varchar(5),
descripcion_producto varchar(80),
id_problema int,
descripcion_problema varchar(80),
fecha_reporte timestamp );

CREATE OR REPLACE FUNCTION completar_reporteproblema()
RETURNS integer AS
$BODY$
DECLARE
	mi_consulta RECORD;
BEGIN
	DELETE FROM REPORTEPROBLEMA;
	FOR mi_consulta IN SELECT O.id_producto, O.descripcion AS descripcion_producto, P.id_problema, P.descripcion, fecha_reporte
						FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
						WHERE P.fecha_cierre is null
						OR O.fecha_cierre IS NULL
		LOOP
		INSERT INTO REPORTEPROBLEMA VALUES (
				mi_consulta.id_producto,
				mi_consulta.descripcion_producto,
				mi_consulta.id_problema,
				mi_consulta.descripcion,
				mi_consulta.fecha_reporte);


    END LOOP;
	RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

-- ejecutar la función
select completar_reporteproblema();