Download PRÁCTICO 3: Algebra – Optimización Algebra relacional y álgebra

BASES DE DATOS I
UNCPBA – Fac. Ciencias Exactas
PRÁCTICO 2 – P2
Curso 2008
PRÁCTICO 3: Algebra – Optimización
Algebra relacional y álgebra de bolsas
1) Considerando la siguiente base de datos
1.1) Interprete en lenguaje natural el resultado de las siguientes operaciones expresadas en álgebra relacional
y plantéelas en SQL:
a)  materia.tipo_doc, = profesor.tipo_doc and materia.nro_doc=profesor.nro_doc (MATERIA X PROFESOR)
b)  tipo_doc, nro_doc, apellido (ALUMNO
PERSONA)
c)  tipo_doc, nro_doc, id_materia(MATERIA PROFESOR)
d)  tipo_doc, nro_doc ALUMNO -  tipo_doc, nro_doc INSCRIPTO_EN
e) [  tipo_doc, nro_doc (ALUMNO PROFESOR) ] PERSONA
f) [  telefono (PROFESOR PERSONA ) ∩  telefono (FACULTAD) ]
1.2) Escriba en álgebra relacional las expresiones que permitan resolver los siguientes servicios:
a)
b)
c)
d)
e)
f)
g)
Obtener la lista de las materias correspondientes al plan #P1
Listar los DNI de los alumnos inscriptos en materias de la facultad #F2.
Listar los profesores de la facultad #F1 y las materias que tienen a cargo
Obtener los datos completos de los alumnos inscriptos en materias del plan #P2
Generar una lista con los apellidos y número de libreta de los alumnos del profesor Martinez
Obtener los datos completos de alumnos inscriptos en materias de carreras de más de 2 años de duración
Listar los alumnos que se han inscripto en todas las materias del plan #P1
1.3) Resuelva las siguientes consultas en álgebra relacional y en SQL por medio de unión, intersección o
diferencia:
a) Listar las personas que son alumnos o profesores de la facultad Facu-1
Página 1 de 4
BASES DE DATOS I
UNCPBA – Fac. Ciencias Exactas
b)
c)
d)
e)
PRÁCTICO 2 – P2
Curso 2008
Encontrar las materias que no poseen alumnos inscriptos.
Listar los apellidos coincidentes entre alumnos y profesores.
Generar la lista de libretas de los alumnos que no se hayan inscripto en materias del plan #P1.
Listar los datos completos de los miembros de la facultad #F2, ya sea profesores o alumnos.
1.4) Analice si las expresiones planteadas en 1.2) y 1.3) son válidas también en álgebra de bolsas y
reformúlelas en caso contrario. Comente acerca de los resultados obtenidos en cada caso.
1.5) Evalúe si las siguientes consultas producirían el mismo resultado según se trate de álgebra relacional o
álgebra de bolsas:
a)
b)
c)
d)
 id_materia (nro_doc > 30.000.000 MATERIASXALUMNO)
 tipo_doc, nro_doc ALUMNO -  tipo_doc, nro_doc ALUMNOSXPLAN
 apellido (ALUMNO PERSONA) -  apellido (ALUMNOSXPLAN PERSONA)
(tipo_doc = DNI, nro_doc = 34.567.890) (  tipo_doc, nro_doc MATERIASXALUMNO)
1.6) Analice cada consulta y conviértala a expresiones del álgebra relacional o de bolsas, según corresponda:
a) Obtener los datos de los alumnos inscriptos en materias junto con el nombre de cada materia.
select *
from persona p, materia m, materiasxalumnos ma
where p.tipo_doc
= ma.tipo_doc and
p.nro_doc
= ma.nro_doc and
ma.id_materia = m.id_materia;
b) Obtener los datos de las facultades donde se dictan carreras cuya duración es de 2 años o menos.
select id_facultad, nombre_facultad, telefono, decano
from facultad
where id_facultad IN (select id_facultad from carrera where duracion <= 2);
c) Obtener los datos de todas las facultades con sus carreras si las tuviera
select *
from facultad f left join carrera c on (f.id_facultad = c.id_facultad);
Optimización de Consultas
2) Considere las siguietes relaciones que representan tablas de una Base de Datos de pacientes de Obras
Sociales:
OBRASOCIAL(idobrasocial, dobrasocial) CIE10(idcie10,dcie10);
PACIENTE(idpaciente, apynomb, domicilio, idobrasocial) PROFESIONAL(idprofesional, apynomb)
INTERNACION(idinternacion, idprofesional, idpaciente,idcie10, fechaingreso)
donde cada tabla contiene la siguiente cantidad de tuplas:
 Obra Social = 1.300
 Paciente = 300.000
 Profesional = 100
 cie10 (Clasificación Internacional de Enfermedades) = 11.500
 Internacion = 42.000
Página 2 de 4
BASES DE DATOS I
UNCPBA – Fac. Ciencias Exactas
PRÁCTICO 2 – P2
Curso 2008
2.1) Para los siguientes pares de sentencias equivalentes (que permiten obtener el mismo resultado):

Construya el árbol de la consulta.

Calcule la cantidad de tuplas involucradas en cada procesamiento.

¿Cuál de ellas resulta más eficiente en función del análisis anterior?
a) Obtener el diagnóstico de las internaciones.
a.1) SELECT c.dcie10
FROM internacion i left join cie10 c on (i.idcie10 = c.idcie10)
WHERE i.idcie10 is not null;
a.2) SELECT c.dcie10
FROM internacion i, cie10 c
WHERE i.idcie10 = c.idcie10 and
i.idcie10 is not null;
b) Obtener el apellido, el número y nombre del club para todos los socios
b.1) SELECT s.apellido, s.id_club, i.nombre_club
FROM socio s, club c
WHERE s.id_club = c.id_club;
b.1) SELECT s.apellido, s.id_club, i.nombre_club
FROM socio s join club c on (s.id_club = c.id_club);
c) Obtener todas las internaciones que fueron atendidas por Obra Social.
b.1) SELECT i.idinternacion
FROM paciente p left join obrasocial os on (p.idobrasocial = os.idobrasocial) as pacos pos, internacion i
WHERE pos.idobrasocial is not null and i.idpaciente = p.idpaciente;
b.2) SELECT i.idinternacion
FROM internacion i left join paciente p on (i.idpaciente = p.idpaciente),
obrasocial os
WHERE p.idobrasocial = os.idobrasocial;
2.2) Optimizar cada una de las siguientes consultas y comparar el resultado con la consulta original en
términos de cantidad de tuplas/volumen de información involucrada.
a) obtener qué profesional atendió a cada paciente.
select p.idpaciente, pr.idprofesional
from paciente p, profesional pr, internacion i
where p.idpaciente = i.idpaciente and i.idprofesional = pr.idprofesional
b) Obtener los idpaciente de las personas que se internaron antes del 24/09/2007 y después del
24/09/2008, y que además se llamen “PEREZ, MARIA”
select idpaciente
from ( select * from internacion i, paciente p
where i.fechaingreso > ‘24/09/2007’ and i.idpaciente = p.idpaciente
union
select * from internacion i, paciente p
where i.fechaingreso < ‘24/09/2007’and i.idpaciente = p.idpaciente
) as pin
where pin.apynomb = “PEREZ, MARIA”;
Página 3 de 4
BASES DE DATOS I
UNCPBA – Fac. Ciencias Exactas
PRÁCTICO 2 – P2
Curso 2008
2.3) Analizar cada caso y justificar la creación o no de índices. Considerar que cada consulta es muy frecuente
en el contexto planteado.
a) select *
from socio
where fecha_nacimiento > to_date('10/09/1997', 'dd/mm/yyyy');
b) select *
from socio
where nro_socio > 10;
c) select *
from socio
where nombre = 'John' and e_mail = '[email protected]';
d) select *
from socio
where nombre LIKE ‘Ke%’ and e_mail = ‘[email protected]’;
e) select *
from socio
where nombre LIKE ‘%vin’ and e_mail = ‘[email protected]’;
2.4) Si tiene creado un índice para la tabla Socio (no único) sobre los atributos Apellido y Nombre en dicho
orden, para cuales de las siguientes consultas se utilizará dicho índice:
a)
b)
c)
d)
select * from socio where apellido = 'De Haan' and nombre = 'Lex';
select * from socio where apellido = 'De Haan';
select * from socio where apellido like 'De Ha%';
select * from socio where apellido like '%De Ha%';
Tips para Oracle:

EXPLAIN PLAN FOR sentencia select;

Información en la tabla PLAN_TABLE y su contenido

Información que brinda el Execute explain Plan del SQL Developer.

Uso de Optimizer Hints de Oracle en la sentencia SELECT
Página 4 de 4