Boletín optimización | Inma Morilla
EJERCICIO 1. Dadas las tablas: ALUMNO(nº-matricula,nombre,apell ALUMNO(nº-matric ula,nombre,apellidos,dni,dir idos,dni,dirección, ección, ciudad, Tlf). 1000 alumnos. ASIGNATURA(cod-asignatura, ASIGNATURA(cod- asignatura, nombre-asig). 100 asignaturas. MATRICULADO(nº-matricula,cod-asi MATRICULADO(nº-matr icula,cod-asignatura,not gnatura,nota). a). 10000 filas.
OPTIMIZAR LAS SIGUIENTES CONSULTAS: a) Obtener el nombre de las asignaturas en las que está matriculado ‘A777’. b) Obtener el nombre y dni de los alumnos de Sevilla matriculados en GBD y SAD. c) Lugar de procedencia de los alumnos que han aprobado ASGB.
a) Select as.nombre From Asignatura as, matriculado m, alumno a Where as.cod-asignatura=m.cod-asignatura And m.nº-matricula = a.nº-matricula And dni=’A777’; Π a.nombre (σdni=’A777’(asignatura X matriculado X alumno))
1
πas.nombre (σ as.codasig = m.codasig m.nºmatricula=a.nºmatricula( π nºmatricula, dni,nºmatricula(σ dni=’A777’(ALUMNO)))))))
codasig (ASIGNATURA X (π codigoasig (MATRICULADO)) X
(σ (π
SELECT as.nombre FROM ASIGNATURA, (SELECT codasig FROM ( (SELECT nºmatricula, codasig FROM MATRICULADO) X (SELECT dni, nºmatricula FROM ALUMNO WHERE dni=’A777’) ) WHERE m.nºmatricula = a.ºmatricula ) WHERE as.codasig = m.codasig
Compras/clientes (media)= 2.000.000/3.000 Productos/clientes (media)=50.000/3.000 2
Boletín optimización | Inma Morilla
1b) (SELECT NOMBRE, DNI FROM ALUMNO A, MATRICULADO M, ASIGNATURA AS WHERE A.NºMATRICULA = M.NºMATRICULA AND CIUDAD=SEVILLA AND NOMBRE-ASIG=GBD) n (SELECT NOMBRE, DNI FROM ALUMNO A, MATRICULADO M, ASIGNATURA AS WHERE A.NºMATRICULA = M.NºMATRICULA AND CIUDAD=SEVILLA AND NOMBRE-ASIG=SAD)
Π nombre,dni (σ a.nºmatricula = m.nºmatricula ^ m.codasig = as.codasig ^ ciudad=Sevilla ^ nombre-asig = GBD (ALUMNO X MATRICULADO X ASIGNATURA)) n Π nombre,dni (σ a.nºmatricula = m.nºmatricula ^ m.codasig = as.codasig ^ ciudad=Sevilla ^
nombre-asig = SAD (ALUMNO X MATRICULADO X ASIGNATURA))
3
Π nombre, dni ( σ nºmatricula = m.nºmatricula (π nombre,dni,nºmatricula(σ ciudad =
SEVILLA(ALUMNO)) X (π nºmatri(σ mcod=as.cod(MATRICULADO nombreasig=GBD(ASIGNATURA))))))) n
x
π as.codasig(σ
Π nombre, dni ( σ nºmatricula = m.nºmatricula (π nombre,dni,nºmatricula(σ ciudad =
SEVILLA(ALUMNO)) X (π nºmatri(σ mcod=as.cod(MATRICULADO x π as.codasig(σ nombreasig=SAD(ASIGNATURA)))))))
SELECT nombre,dni FROM ( SELECT nombre, dni, nºmatricula FROM ALUMNO WHERE ciudad=SEVILLA) WHERE nºmatircula=m.nºmatricula
4
Boletín optimización | Inma Morilla
1c) SELECT al.ciudad FROM ALUMNO AL, MATRICULADO M, ASIGNATURA A WHERE AL.NºMATRI=M.NºMATRI AND MA.COD-ASIG = A.COD-ASIG AND A.NOMBRE-ASIG = ‘ASGB’ AND M.NOTA >=5 Π ciudad (σ al.nºmatri = m.nºmatri ^ ma.cod-asig = a.cod-asig ^ a.nombre-asig = ASGB ^ m.nota
>=5 (ALUMNO X MATRICULADO X ASIGNATURAS))
Π ciudad (σ al.nºmatri = m.nºmatri ( ( π nºmatri,ciudad (ALUMNO)) X (π nºmatri (σ m.cod-asig =
a.cod-asig ((π cod-asig(σ nombre=ASGB(ASIGNATURA))) nota>=5(MATRICULADO))) )))))
5
X
(π
nºmatri,cod-asig(σ
SELECT ciudad FROM (SELECT NºMATRICULA,CIUDAD FROM ALUMNOS) AL, (SELECT NºMATRI FROM (SELECT COD-ASIG FROM ASIGNATURA WHERE NOMBRE=ASGB) A, (SELECT NºMATRI, COD-ASIG FROM MATRICULADO WHERE NOTA>=5) M WHERE M.COD-ASIG = A.COD-ASIG) MA ) WHERE AL.NºMATRICULA = MA.NºMATRICULA
6
Boletín optimización | Inma Morilla
EJERCICIO2. Dadas las tablas: CLIENTE ( cod-cli,nombre,dirección,ciudad,edad, teléfono). 3000 clientes. PRODUCTO(cod-producto,descripción,preciounitario). 50000 productos. COMPRAS(cod-producto,cod-cli,cantidad). 2000000 de filas.
OPTIMIZAR LAS SIGUIENTES CONSULTAS: a) Nombre de los clientes que viven en Madrid. b) Nombre de los clientes que compran el producto ‘P5’ y no han comprado el producto ‘P3’. c) Nombre de los clientes que compran algún producto de precio mayor de 10 euros.
2a) SELECT NOMBRE FROM CLIENTE WHERE CIUDAD = MADRID
Π nombre ( σ ciudad=Madrid(CLIENTE))
optimizada
Π nombre ( σ ciudad=Madrid (π nombre,ciudad(CLIENTE)))
SELECT NOMBRE FROM (SELECT NOMBRE, CIUDAD FROM CLIENTE) WHERE CIUDAD = MADRID
7
2b) SELECT NOMBRE FROM CLIENTE C, COMPRAS COM WHERE C.COD-CLI = COM.COD-CLI AND COM.COD-PRODUCTO=’P5’
Π nombre ( σ cod-cli = com.cod-cli ^ com-cod-product=P5 (CLIENTE X COMPRAS))
Π nombre(σ cod-cli = com.cod-cli ( (π cod-cli, nombre (CLIENTE)) X ( π cod-cli(σ codproduct=P5(COMPRAS)))))
SELECT NOMBRE FROM (SELECT COD-CLI, NOMBRE, FROM CLIENTES) CL, (SELECT COD-CLI FROM COMPRAS WHERE COD-PRODUCT=P5) C WHERE CL.COD-CLI = C.COD-CLI
8
Boletín optimización | Inma Morilla
2c) SELECT CLI.NOMBRE FROM CLIENTE C, COMPRAS COM, PRODUCTO P WHERE C.COD-CLI = COM.COD-CLI AND COM.COD-PRODUCT = P.COD-PRODUCT AND P-PRECIOUNITARIO > 10
Π nombre ( σ cod-cli = com. Cod-cli ^ com.cod-pro = p.cod-pro ^ preciounitario > 10 (CLIENTE X
COMPRAS X PRODUCTOS))
9
Π nombre (σ c.cod-cli = com.cod-cli((π cod-cli(CLIENTE)) X (π cod-cli (σ com.cod-proc = p.cod-pro
(π cod-pro, cod-cli(COMPRAS)) X π cod-pro (σ preciounitario>10(PRODUCTO))))))
SELECT NOMBRE FROM (SELECT COD-CLI FROM CLIENTES) C, (SELECT COD-CLI FROM (SELECT COD-PRO FROM COMPRAS) COM, (SELECT COD-PRO FROM PRODUCTO WHERE PRECIOUNITARIO > 10) P WHERE COM.COD-PROD = P-COD-PRO) CO) WHERE C.COD-CLI = CO.COD-CLI
10
Boletín optimización | Inma Morilla
EJERCICIO 3. Dadas las tablas: PROVEEDORES(cod-proveed, nombre,ciudad,calle, numero) 1000 proveedores. ARTÍCULOS(cod-art,nombre-art,precio,descripción,color). 50000 artículos. PEDIDOS( cod-pedido,cod-art,cantidad). 10000000 pedidos. SUMINISTROS(cod-proveed,cod-art,capacidad-distribución) 50000000 filas. Contiene los artículos que suministra cada proveedor y en que proporción puede suminstrarlos. OPTIMIZAR LAS SIGUIENTES CONSULTAS:
a) Nombre de los artículos de color rojo con capacidad de distribución >50. b) Nombre de los proveedores que suministran algún artículo de color rojo. c) Nombre de los artículos que han sido solicitados alguna vez a un proveedor en cantidad superior a la de distribución. d) Nombre de los proveedores que no suministran el artí culo ‘A7’. e) Nombre de los proveedores que suministran, al menos, un artículo de los que suministra el proveedor P1. f) Nombre de los proveedores que suministran todos los art ículos. g) Proveedores que han suministrado artículos de color rojo y verde .
3a) SELECT ART.NOMBRE FROM ARTICULES ART, SUMINISTROS SUM WHERE ART.COD-ART = SUM-COD-ART AND ART.COLOR =’ROJO’
AND SUM.CAPACIDAD-DIST>50
Π art.nombre ( σ art.cod-art = sum.cod-art ^ art.color=rojo ^ sum.cap-dist>50 (ARTICULOS X SUMINISTROS))
11
Π nombre (σ art.cod-art = sum.codart ( (π cod-art,nombre(σ art.color=rojo
(π codart,color,nombre (ARTICULOS)))) X (π cod-art (σ sum.cap-dist > 50 (π cod-art, capdist(SUMINISTROS))))))
SELECT NOMBRE FROM (SELECT COD-ART, NOMBRE FROM (SELECT COD-ART, COLOR, NOMBRE FROM ARTICULOS WHERE ART.COLOR) ART (SELECT COD-ART FROM (SELECT COD-ART, CAP-DIST FROM SUMINISTROS) WHERE SUM.CAP-DIST >50) SUM ) WHERE ART.COD-ART = SUM.COD-ART
12
Boletín optimización | Inma Morilla
3b) SELECT PROV.NOMBRE FROM PROVEEDORES P, SUMINISTROS S, ARTICULOS A WHERE P.COD-PRE = S.COD-PRE AND S.COD-ART = A.COD-ART AND A.COLOR = ROJO
Π nombre ( σ p.cod-pre = s.cod-pre ^ s.cod-art = a.cod-art ^ a.color = rojo (PROVEEDORES X
SUMINISTROS X ARTICULOS))
13
Π nombre (σ cod-pre = s.cod-pre (( π cod-pre,nombre(PROVEEDORES)) X (π codpre (σ cod-art =
a.cod-art (π cod-art(SUMINISTROS)) X (π cod-pre, cod-art ( σ color = rojo ( π cod-pre, cod-art, color (ARTICULOS)))))))))
SELECT NOMBRE FROM (SELECT COD-PRE, NOMBRE FROM PROVEEDORES) P, (SELECT COD-PRE FROM (SELECT COD-ART FROM SUMINISTROS) S, (SELECT COD-PRE, COD-ART FROM (SELECT COD-PRE,COD-ART, COLOR FROM ARTICULOS) A WHERE S.COD-ART = A .COD-ART) ) SA WHERE P.COD-PRE = SA.COD-PRE
14
Boletín optimización | Inma Morilla
3C) SELECT ART.NOMBRE FROM SUMINISTROS S, PEDIDOS P, ARTICULOS A WHERE S.COD-ART = P.COD-ART AND A.COD-ART = P.COD-ART AND P.CANT > S.CAP-DIST
Π art.nombre ( σ s.cod-art = p.cod-art ^ p.cod-art = a.cod-art ^ p.cant > s.cap-dist (SUMINISTROS
X PEDIDOS X ARTICULOS))
15
Π nombre ( σ cod-art = p.cod-art ( ( π cod-art,nombre (ARTICULO)) X (π cod-art (σ p.cant > s.cap-
dist ( (π cod-art, cant (PEDIDOS)) X (π cod-art, cap-dis (SUMINISTROS)) )))))
SELECT NOMBRE FROM (SELECT COD-ART, NOMBRE FROM ARTICULOS) A, (SELECT COD-ART FROM (SELECT COD-ART, CANT FROM PEDIDOS) P, (SELECT COD-ART, CAPT-DIST FROM SUMINISTROS) S WHERE P.COD-ART = S.COD-ART AND P-CANT > S.CAP-DIS) PS WHERE A.COD-ART = PS.COD-ART
16
Boletín optimización | Inma Morilla
3d) (SELECT P.NOMBRE FROM PROVEEDORES P) MINUS (SELECT P.NOMBRE FROM PROVEEDORES P, SUMINISTROS S WHERE P.COD-PRE = S.COD-PRE AND S.COD-ART=’A7’)
Π p.nombre (PROVEEDORES) – (Π p.nombre (σ p.cod-pre = s.cod-pre ^ s.cod-art =A7
(PROVEEDORES X SUMI)))
Π nombre (PROVEEODRES) – (Π nombre (σ p-cod-pre = s.cod-pre (( π cod-pre, nombre
(PROVEEODRES)) X (π cod-pre(σ cod-art =’A7’ (SUMINISTROS)))))
17
SELECT NOMBRE FROM PROVEEDORES MINUS SELECT NOMBRE FROM (SELECT COD-PRE, NOMBRE, FROM PROVEEDORES) P, (SELECT COD-PRE FROM SUMINISTROS WHERE COD-ART=A7) S WHERE P.COD-PRE= S.COD-PRE
3g) SELECT COD-PROVED FROM SUMINISTROS S, ARTICULOS A WHERE S.COD-ART = A.COD-ART AND A.COLOR = ROJO INTERSECT SELECT COD-PROVED FROM SUMINISTROS S, ARTICULOS A WHERE S.COD-ART = A.COD-ART AND A.COLOR = VERDE 18
Boletín optimización | Inma Morilla
Π cod-proved (σ s.cod-art = a.cod-art ^ a.color = rojo (SUMINISTROS X ARTICULOS)) n Π cod-proved (σ s.cod-art = a.cod-art ^ a.color = verde (SUMINISTROS X ARTICULOS))
Π cod-proved (σ s.cod-art = a.cod-art ((π cod-art (SUMINISTROS)) X (π cod-art(σ color=rojo (π
color (ARTICULOS)))))) n Π cod-proved (σ s.cod-art = a.cod-art ((π cod-art (SUMINISTROS)) X (π cod-art(σ color=verde (π
color (ARTICULOS))))))
SELECT COD-PROVED FROM (SELECT COD-ART FROM SUMINISTROS) S, (SELECT COD-ART FROM ARTICULOS WHERE COLOR=ROJO) A WHERE S.COD-ART = A.COD-ART
n SELECT COD-PROVED FROM (SELECT COD-ART FROM SUMINISTROS) S, (SELECT COD-ART FROM ARTICULOS WHERE COLOR=VERDE) A WHERE S.COD-ART = A.COD-ART
19
20
