· 8 years ago · May 28, 2018, 06:08 PM
1
2--- Borrado de tablas
3drop table IF EXISTS producto cascade;
4drop table IF EXISTS sub_prod cascade;
5drop table IF EXISTS problema cascade;
6drop table IF EXISTS desarrollador cascade;
7
8--- Script para crear las tablas
9create table producto (
10id_producto varchar(5) not null,
11descripcion varchar(80) not null,
12fecha_cierre timestamp,
13constraint pk_producto primary key(id_producto)
14);
15create table sub_prod (
16id_sub_prod int not null,
17id_producto varchar(5) not null,
18descripcion varchar(80) not null,
19version_cerrada numeric(10,2) not null,
20constraint pk_sub_prod primary key(id_sub_prod, id_producto)
21);
22create table problema (
23id_problema int not null,
24id_sub_prod int not null,
25id_producto varchar(5) not null,
26id_equ_reporta char(1) not null,
27id_des_reporta int not null,
28id_equ_a_cargo char(1),
29id_des_a_cargo int,
30fecha_reporte timestamp not null,
31descripcion varchar(80) not null,
32fecha_cierre timestamp,
33cantidad_horas numeric(10,2) not null,
34constraint pk_problema primary key(id_problema)
35);
36create table desarrollador(
37id_equipo char(1) not null,
38id_desar int not null,
39nombre varchar(80) not null,
40apellido varchar(80) not null,
41e_mail varchar(120),
42fecha_ingreso timestamp not null,
43salario numeric(10,2),
44constraint pk_desarrollador primary key(id_equipo, id_desar)
45);
46alter table sub_prod add constraint fk1 foreign key (id_producto) references producto(id_producto) on delete cascade on update cascade;
47alter 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;
48alter 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;
49alter 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;
50-- Inserts
51insert into producto (id_producto, descripcion, fecha_cierre) values ('P1','Producto 1', to_date('02/26/2016','dd/MM/yyyy'));
52insert into producto (id_producto, descripcion, fecha_cierre) values ('P2','Producto 2', null);
53insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (1, 'P1', 'Descripción Sub Producto 1-P1', 2.3);
54insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (2, 'P1', 'Descripción Sub Producto 2-P1', 2.7);
55insert into sub_prod (id_sub_prod, id_producto, descripcion, version_cerrada) values (3, 'P1', 'Descripción Sub Producto 3-P1', 3.2);
56insert 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);
57insert 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);
58insert 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);
59insert 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);
60insert 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
61(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);
62insert 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
63(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);
64insert 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
65(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);
66
67----
68/*
69EJERCICIO 1
70Las modalidades para Borrado y Modificación a derecha para cada una de las restricciones de integridad referencial son las siguientes:
71FK1: (cascade, cascade) - FK2: (restrict, cascade) - FK_Reporta: (cascade, cascade) - FK_aCargo: (null, null) match simple
72Suponga que la BD está instanciada sólo con las tuplas adjuntas en este ejercicio.
73Explique 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):
74a) DELETE FROM PRODUCTO WHERE id_producto = 'P1';
75
76Las 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.
77
78b) UPDATE PRODUCTO SET id_producto = 'P3' WHERE id_producto = 'P1';
79Las 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.
80
81c) DELETE FROM PRODUCTO;
82Las 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.
83
84d) DELETE FROM DESARROLLADOR WHERE id_desar = 2;
85
86e) UPDATE DESARROLLADOR id_desar = 5 WHERE id_desar = 1;
87f) UPDATE PROBLEMA set id_equ_a_cargo = 'A', id_des_a_cargo = null where id_problema = 2
88
89*/
90
91
92-- EJERCICIO 2 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
93--Los desarrolladores sin salario deben tener indicada la dirección de mail
94ALTER TABLE desarrollador
95ADD CONSTRAINT CK_2_1 CHECK (e_mail IS NOT NULL OR salario IS NOT NULL);
96
97-- EJERCICIO 3 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
98-- No puede haber problemas cuya fecha de cierre sea posterior a la fecha de cierre del producto al que están asociados
99CREATE ASSERTION CK_2_2
100CHECK NOT EXISTS
101(SELECT 1
102FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
103where P.fecha_cierre > O.fecha_cierre);
104
105-- EJERCICIO 4 Plantee en SQL estándar, mediante el recurso declarativo más adecuado (justificando la elección), la siguiente restriccion
106-- El total de horas que pueden dedicarse a resolver problemas de un mismo subproducto tiene un tope de 500.
107CREATE ASSERTION CK_2_3
108CHECK NOT EXISTS
109(SELECT 1
110FROM PROBLEMA P
111GROUP BY id_problema, id_sub_prod
112HAVING SUM(cantidad_horas) > 500);
113
114-- EJERCICIO 5 Especifique el/los trigger/s (con su/s función/es) necesarios para implementar en Postgresql la restricción:
115-- "No puede haber problemas cuya fecha de cierre sea posterior a la fecha de cierre del producto al que están asociados
116
117
118CREATE OR REPLACE FUNCTION FN_FECHA_CIERRE()
119RETURNS trigger AS $$
120DECLARE
121 cantidad integer;
122BEGIN
123 IF (TG_NAME = 'CK_2_2_PROBLEMA') THEN
124 SELECT COUNT(*) INTO CANTIDAD
125 FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
126 where P.fecha_cierre > O.fecha_cierre
127 AND id_problema = NEW.id_problema;
128 END IF;
129 IF (TG_NAME = 'CK_2_2_PRODUCTO') THEN
130 SELECT COUNT(*) INTO CANTIDAD
131 FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
132 where P.fecha_cierre > O.fecha_cierre
133 AND id_producto = NEW.id_producto;
134 END IF;
135 IF ( cantidad > 0 ) THEN
136 RAISE EXCEPTION 'fecha de cierre de problemas mayor que la de producto';
137 END IF;
138RETURN NEW;
139END $$
140LANGUAGE 'plpgsql';
141
142CREATE TRIGGER CK_2_2_PROBLEMA
143BEFORE INSERT OR UPDATE OF fecha_cierre ON PROBLEMA
144FOR EACH ROW EXECUTE PROCEDURE FN_FECHA_CIERRE();
145
146CREATE TRIGGER CK_2_2_PRODUCTO
147BEFORE UPDATE OF fecha_cierre ON PRODUCTO
148FOR EACH ROW EXECUTE PROCEDURE FN_FECHA_CIERRE();
149
150
151-- EJERCICIO 6 Escriba una consulta SELECT que permita resolver la siguiente consulta
152-- Obtener todos los datos de los desarrolladores que no han reportado ni que han estado a cargo de problemas
153SELECT *
154FROM DESARROLLADOR
155WHERE (id_equipo, id_desar) NOT IN
156(SELECT id_equ_reporta, id_des_reporta FROM problema)
157AND (id_equipo, id_desar) NOT IN
158(SELECT id_equ_a_cargo, id_des_a_cargo FROM problema);
159
160-- EJERCICIO 7 Escriba una consulta SELECT que permita resolver la siguiente consulta
161-- 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.
162SELECT *
163FROM DESARROLLADOR
164WHERE (id_equipo, id_desar) NOT IN
165(SELECT id_equ_a_cargo, id_des_a_cargo
166FROM problema
167WHERE fecha_reporte > (current_date - 7) );
168
169
170-- 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)
171create table ReporteProblemas (
172id_producto varchar(5),
173descripcion_producto varchar(80),
174id_problema int,
175descripcion_problema varchar(80),
176fecha_reporte timestamp );
177
178CREATE OR REPLACE FUNCTION completar_reporteproblema()
179RETURNS integer AS
180$BODY$
181DECLARE
182 mi_consulta RECORD;
183BEGIN
184 DELETE FROM REPORTEPROBLEMA;
185 FOR mi_consulta IN SELECT O.id_producto, O.descripcion AS descripcion_producto, P.id_problema, P.descripcion, fecha_reporte
186 FROM PROBLEMA P JOIN PRODUCTO O ON (P.id_producto = O.id_producto)
187 WHERE P.fecha_cierre is null
188 OR O.fecha_cierre IS NULL
189 LOOP
190 INSERT INTO REPORTEPROBLEMA VALUES (
191 mi_consulta.id_producto,
192 mi_consulta.descripcion_producto,
193 mi_consulta.id_problema,
194 mi_consulta.descripcion,
195 mi_consulta.fecha_reporte);
196
197
198 END LOOP;
199 RETURN 1;
200END;
201$BODY$
202LANGUAGE plpgsql;
203
204-- ejecutar la función
205select completar_reporteproblema();