Cuestión: Proceso de consultas en BD Relacionales
Políticas y heurísticas para la optimización de árboles algebraicos.
Peso: 15%
Tiempo:15min
Problema 1: Diseño conceptual
Una empresa decide crear un portal para gestionar ofertas de productos y servicios en la web. Su valor añadido consiste en
agrupar estas ofertas por ciudades y capturar peticiones o pedidos de particulares vinculados a estas ofertas.
Para el desempeño de su labor cuentan con un grupo de proveedores homologados. Un proveedor puede tener presencia
en más de una ciudad. Se registran los datos de contacto: nombre, dirección, teléfonos y email, así como la persona de
contacto en cada ciudad.
Cada oferta está vinculada a un proveedor en una ciudad. La oferta se identifica con un idOferta y tiene un intervalo de
tiempo de vigencia, una descripción y un precio unitario de venta al consumidor.
Durante el tiempo de exposición de las ofertas en el portal, los usuarios registrados (idUsuario, Email, nombre, dirección,
tarjeta de crédito) pueden generar pedidos. Una vez que se realiza un pedido, éste queda vinculado a la oferta en cuestión
mediante un cupón de pedido (idCupon) en un instante del tiempo. El usuario, una vez cerrada la oferta puede imprimir su
cupón que es cargado en su tarjeta de crédito. Con el mismo puede realizar el consumo del bien o servicio ofertado por
cada proveedor en el lugar elegido.
Existirá en la base de datos una estructura geográfica de países, provincias o estados y ciudades.
Cada oferta es clasificada según una tipología de productos o servicios (ej.: Relax, Tratamiento corporal, Restaurantes, etc.)
que es dinámica y se va actualizando a medida que aparecen nuevas expectativas.
La organización pacta una comisión de gestión con cada proveedor a nivel global e independiente de la ciudad dónde se
ubican las ofertas. Mensualmente se liquidan estas comisiones, generando una factura por proveedor sobre todos los
cupones vendidos que se agrupan por ofertas. En la BD se registrará la factura (idFactura, fecha, ..) y el detalle de
ofertas/cupones, comisión aplicada e importe de la factura, a la que se aplica el tipo de IVA general (18%).
a) Represente un esquema conceptual de entidades ERD según notación de la asignatura. Especifique sólo las
propiedades identificadoras de entidades en dicho esquema.
b) Represente en álgebra relacional una restricción de integridad para asegurar que
“En la misma ciudad no hay dos
ofertas concurrentes de la misma tipología”.
Con las relaciones:
Proveedores(idProveedor,Proveedor,comisión,Email,..)
PK(idProveedor)
Usuarios(idUsuario,Usuario,Email)
PK(idUsuario)
Ofertas(idOferta,Inicio,Fin,Tipo,PrecioUnit,idProveedor,Ciudad)
PK(idOferta),
FK(idProveedor)/Proveedores
Cupones(idCupon,FechaHora,idOferta,idUsuario,Tipo)
PK(idCupon)
FK1(idOferta)/Ofertas
FK2(idUsuario)/Usuarios
Problema 2: Diseño tecnológico SQL2
Exprese en SQL2 las restricciones de integridad:
I)
“En la misma ciudad no hay dos ofertas concurrentes del mismo tipo”.
II)
“Un proveedor no realiza más de 3 ofertas el mismo mes en la misma ciudad”.
Peso: 15%(50%,50%)
Tiempo:20min
Problema 3: Diseño tecnológico ORACLE
I)
Defina un trigger para asegurar la restricción de integridad:
“En la misma ciudad no hay dos ofertas
concurrentes del mismo tipo”.
II)
Cree un procedimiento en ORACLE para sacar por pantalla la factura a emitir mensualmente a cada proveedor
como comisiones a cobrarle por los cupones vendidos, agrupándolos por ofertas.
a) Parámetros del procedimiento: Fecha del mes en formato “DD/MM/AAAA”
b) Salida:
<idProveedor,Proveedor, idOferta, Inicio Oferta, Fin Oferta, Precio Unitario, NºCupones vendidos, Importe>
Ordenado por Proveedor e idOferta
TipoOferta Ubicación Oferta Cupón Usuario 1 1 1 1 0:N 0:N 0:N 0:N IdOferta TipoOferta IdUsuario IdCupón Línea Factura Factura Proveedor Cupón 1 0:N 0:N 0:M IdCupón IdProveedor IdFactura
Solución P1
Vista de Proveedores
Vista de Ofertas
RI
. . . . . . . [ . , . ] . [ . , . ],
,
O x IdO ferta O y IdO ferta O x IdC iudad O y IdC iudad O x T ipoO ferta O y T ipoO ferta
O x Inicio O y Inicio O y F in O x F in O y Inicio O y F in
O fertas O x
O fertas O y
IdPaisPaís Estado Ciudad
Proveedor Ubicación 1 0:N 1 0:N 0:N 0:M IdEstado IdCiudad IdProveedor
Vista Facturas
R1) “En la misma ciudad no hay dos ofertas concurrentes del mismo tipo”.
"N o H ay_ D o s_ O fertas_ C o n cu rren tes"
*
O x ,
O X .Id O ferta< > O Y .Id O ferta
A N D O x .Id C iu d ad = O y.Id C iu d ad
A N D O x .T ip o O ferta= O y.T ip o O ferta
A N D
O x .In icio
C R E A T E A S S E R T IO N
S E L E C T
F R O M O ferta s
O ferta s O y
W H E R E
C H E C K N O T E X IS T S
B E T W E E N O y.In icio A N D O y.fin
O R
O x .F in B E T W E E N O y.In icio A N D O y.fin
R2) “Un proveedor no realiza más de 3 ofertas el mismo mes en la misma ciudad”.
"N o H ay_ D o s_ O fertas_ C o n cu rren tes"
(*)
P r
,
,
(
)
(*)
3
(*)
C R E A T E A S S E R T IO N
S E L E C T co u n t
F R O M O ferta s
G R O U P B Y Id
o veed o r C iu d a d E xtra ct M o n th F R O M in icio
H A V IN G co u n t
C H E C K N O T E X IS T S U N IO N
S E L E C T co u n t
F R O M
P r
,
,
(
fin )
(*)
3
O ferta s
G R O U P B Y Id
o veed o r C iu d a d E xtra ct M o n th F R O M
H A V IN G co u n t
Solución P3
-- Actualizamos la sesion para visualizar datos set serveroutput on
--Borramos los triggers, paquetes y procedimientos drop trigger ciudadSinOfertasMismoTipo;
drop package riofertas;
drop trigger cSinOfertasMismoTipo_before; drop trigger cSinOfertasMismoTipo_after; drop procedure listarFacturas;
--drop procedure listarFacturaProveedor; --Borramos las tablas
drop table cupones; drop table ofertas; drop table usuarios; drop table proveedores; --Creamos las tablas CREATE TABLE proveedores
(idproveedor number(2) PRIMARY KEY, proveedor varchar2(30),
comision number(2), email varchar2(50)); CREATE TABLE usuarios
(idusuario number(2) PRIMARY KEY, usuario varchar2(30),
email varchar2(50)); CREATE TABLE ofertas
(idOferta integer PRIMARY KEY, inicio date, fin date, tipo varchar2(15), precioUnit float, idProveedor number(2), ciudad varchar2(20),
CONSTRAINT ofertas_idproveedor_fk FOREIGN KEY (idProveedor) REFERENCES proveedores ON DELETE CASCADE); CREATE TABLE cupones
(idCupon integer PRIMARY KEY, fechahora date,
idOferta integer, idUsuario integer, tipo varchar2(15),
CONSTRAINT cupones_idoferta_fk FOREIGN KEY (idOferta) REFERENCES ofertas ON DELETE CASCADE, CONSTRAINT cupones_idusuario_fk FOREIGN KEY (idUsuario) REFERENCES usuarios ON DELETE CASCADE); -- Introducimos los datos
INSERT INTO proveedores VALUES (1,'PROV UNO',10,'email1'); INSERT INTO proveedores VALUES (2,'PROV DOS',20,'email2'); INSERT INTO proveedores VALUES (3,'PROV TRES',30,'email3'); INSERT INTO usuarios VALUES (1,'USU UNO','email1'); INSERT INTO usuarios VALUES (2,'USU DOS','email2'); INSERT INTO usuarios VALUES (3,'USU TRES','email3');
INSERT INTO ofertas VALUES (1,'01/01/2011','31/01/2011','UNO',100,1,'Huelva'); INSERT INTO ofertas VALUES (2,'01/02/2011','01/03/2011','DOS',200,2,'Huelva'); INSERT INTO ofertas VALUES (3,'01/03/2011','30/03/2011','TRES',300,1,'Huelva'); INSERT INTO ofertas VALUES (4,'01/04/2011','30/04/2011','UNO',100,2,'Huelva'); INSERT INTO ofertas VALUES (5,'01/05/2011','30/05/2011','DOS',200,1,'Huelva'); INSERT INTO ofertas VALUES (6,'01/02/2011','28/02/2011','UNO',300,2,'Huelva'); INSERT INTO cupones VALUES (1,'20/06/2011',1,1,'UNO');
INSERT INTO cupones VALUES (2,'20/06/2011',2,1,'UNO'); INSERT INTO cupones VALUES (3,'20/06/2011',3,2,'UNO'); INSERT INTO cupones VALUES (4,'20/06/2011',4,3,'UNO'); INSERT INTO cupones VALUES (5,'20/06/2011',5,3,'UNO'); INSERT INTO cupones VALUES (6,'20/06/2011',6,3,'UNO'); INSERT INTO cupones VALUES (7,'20/06/2011',1,1,'UNO'); INSERT INTO cupones VALUES (8,'20/06/2011',1,1,'UNO'); INSERT INTO cupones VALUES (9,'20/06/2011',1,2,'UNO'); INSERT INTO cupones VALUES (10,'20/06/2011',1,3,'UNO'); INSERT INTO cupones VALUES (11,'20/06/2011',2,3,'UNO'); INSERT INTO cupones VALUES (12,'20/06/2011',1,3,'UNO'); COMMIT;
CREATE OR REPLACE TRIGGER ciudadSinOfertasMismoTipo
BEFORE INSERT OR UPDATE OF inicio, fin, tipo, ciudad ON ofertas FOR EACH ROW
DECLARE
cuenta INTEGER; BEGIN
SELECT count(*) INTO cuenta FROM ofertas
WHERE tipo=:new.tipo AND ciudad=:new.ciudad AND
((:new.inicio>=inicio AND :new.inicio<=fin) OR (:new.fin>=inicio AND :new.fin<=fin) OR (:new.inicio<inicio AND :new.fin>fin)); IF cuenta > 0 THEN
raise_application_error(-20600,:new.inicio || '-' || :new.fin || ' - no se puede establecer como tarifa para ese tipo y ciudad con las tarifas actuales.');
END IF; END; /
-- Para crearlo para tablas mutantes, creamos el paquete, el before y el after CREATE OR REPLACE PACKAGE riofertas AS
newoferta ofertas%rowtype; END;
/
CREATE OR REPLACE TRIGGER cSinOfertasMismoTipo_before
BEFORE INSERT OR UPDATE OF inicio, fin, tipo, ciudad ON ofertas FOR EACH ROW
BEGIN riofertas.newoferta.inicio := :new.inicio; riofertas.newoferta.fin := :new.fin; riofertas.newoferta.tipo := :new.tipo; riofertas.newoferta.ciudad := :new.ciudad; END; /
CREATE OR REPLACE TRIGGER cSinOfertasMismoTipo_after
AFTER INSERT OR UPDATE OF inicio, fin, tipo, ciudad ON ofertas DECLARE
cuenta INTEGER; BEGIN
SELECT count(*) INTO cuenta FROM ofertas
WHERE tipo=riofertas.newoferta.tipo AND ciudad=riofertas.newoferta.ciudad AND
((riofertas.newoferta.inicio>=inicio AND riofertas.newoferta.inicio<=fin) OR
(riofertas.newoferta.fin>=inicio AND riofertas.newoferta.fin<=fin) OR (riofertas.newoferta.inicio<inicio AND riofertas.newoferta.fin>fin));
IF cuenta > 1 THEN
DBMS_OUTPUT.PUT_LINE(cuenta);
raise_application_error(-20600,riofertas.newoferta.inicio || '-' || riofertas.newoferta.fin || ' - no se puede establecer como tarifa para ese tipo y ciudad con las tarifas actuales.');
END IF; END; /
-- Probamos el trigger
INSERT INTO ofertas VALUES (7,'01/01/2010','31/12/2010','UNO',300,2,'Huelva'); INSERT INTO ofertas VALUES (8,'01/05/2011','28/05/2011','UNO',300,2,'Huelva'); INSERT INTO ofertas VALUES (9,'01/03/2011','31/03/2011','UNO',300,2,'Huelva'); INSERT INTO ofertas VALUES (10,'01/02/2009','28/02/2012','UNO',300,2,'Huelva'); INSERT INTO ofertas VALUES (11,'01/03/2011','28/02/2011','UNO',300,2,'Huelva'); COMMIT;
select * from ofertas; -- Probamos el update
UPDATE ofertas SET tipo='UNO' WHERE idoferta=3;
UPDATE ofertas SET inicio='10/02/2011' WHERE idoferta=9;
INSERT INTO ofertas VALUES (11,'01/03/2011','28/02/2011','UNO',300,2,'Sevilla'); UPDATE ofertas SET ciudad='Huelva' WHERE ciudad='Sevilla';
CURSOR c IS SELECT p.idproveedor, p.proveedor,
o.idoferta, o.inicio, o.fin, o.preciounit,
count(*) as numCupones, count(*)*o.preciounit as Importe, p.comision, (count(*)*o.preciounit*p.comision)/100 as CobroCom FROM proveedores p, ofertas o, cupones c
WHERE p.idproveedor=o.idproveedor AND o.idoferta=c.idoferta AND
to_char(w_fecha,'MM')=to_char(c.fechahora,'MM')
GROUP BY p.idproveedor, p.proveedor, p.comision, o.idoferta, o.inicio, o.fin, o.preciounit ORDER BY p.idproveedor, o.idoferta;
fila c%ROWTYPE; mostrarCabecera integer; BEGIN mostrarCabecera:=0; OPEN c; LOOP
FETCH c INTO fila; EXIT WHEN c%NOTFOUND;
IF (mostrarCabecera=0) THEN
DBMS_OUTPUT.PUT_LINE('<idProveedor> - <Proveedor> - <idOferta> - <Inicio> - <Fin> - <Precio> - <Cupones Vendidos> - <Importe>');
mostrarCabecera:=1; END IF;
DBMS_OUTPUT.PUT_LINE( fila.idproveedor || ' - ' || fila.proveedor || ' - ' || fila.idOferta || ' - ' || fila.inicio || ' - ' || fila.fin || ' - ' || fila.preciounit || ' - ' || fila.numCupones || ' - ' || fila.CobroCom);
END LOOP; CLOSE c;
IF (mostrarCabecera=0) THEN
DBMS_OUTPUT.PUT_LINE('Sin cupones para el mes introducido por parametro'); END IF;
END listarFacturas; /
-- Probamos el procedimiento exec listarFacturas('20/06/2011');
