Bases de Datos – Año 2014

Pag. [1]

Práctica Modelo Relacional

Con apoyo del material de estudio e Internet, resuelva la siguiente práctica. La fecha de presentación límite es: Mie. 23/04/2014

Forma de Presentación: enviada al correo: Walter.coayla@gmail.com

Dudas: al 990220266, Walter.coayla@gmail.com, al grupo en Google, o cualquier otro medio de comunicación.

Ejercicio A: Defina clave candidata, primaria, alternativa y foránea.

Ejercicio B: Responda y justifique las siguientes preguntas:

1. ¿Las filas de una relación tienen un orden específico? Compare con una tabla.

2. ¿Puede una relación carecer de clave primaria?

3. Una clave alternativa ¿Puede contener valores nulos? ¿Puede contener valores duplicados?

4. ¿Puede una relación carecer de clave foránea? ¿Cuáles son las posibles acciones que se pueden definir en un motor para mantener la integridad referencial?

5. ¿Una clave foránea puede contener valores nulos?

6. Enuncie la Propiedad de Clausura, e indique las consecuencias que de ella se desprenden.

Ejercicio C:

1. Especifique una relación que posea al menos una clave foránea (que no forme parte de la clave primaria de dicha relación), especifique la relación a la que hace referencia, identifique su clave primaria. La clave foránea, ¿puede contener nulos? ¿Qué acciones de integridad referencial especificaría si el motor provee las 3 alternativas conocidas? Justifique.

2. Especifique una relación que posea una clave foránea que forme parte de la clave primaria. Especifique la relación a la que hace referencia, identifique su clave primaria. La clave foránea, ¿puede contener nulos? ¿Qué acciones de integridad referencial especificaría si el motor provee las 3 alternativas conocidas? Justifique.

Ejercicio D: Considere las tablas X y Z. Indique los resultados que se obtienen en las siguientes operaciones.

a) ς A ≤ 15 (Z) g) X X.P = Z.A Z

b) π P,R (X) h) X P = A Z

c) ρ A,B,C (X) U Z Obs: renombro X porque debe ser Unión Compatible

i) X Q = B Z

d) π C (ρ A,B,C (X) ) ∩ π C (Z) Obs: renombro X porque debe ser Unión Compatible

j) X X.Q = Z.B Z

e) π B (ρ A,B,C (X) ) - π B (Z) Obs: renombro X porque debe ser Unión Compatible

k) ( ρ A,Q,R (X)) * Z

f) X x Z

X P Q R Z A B C

10 a 5 10 b 6

15 b 8 25 c 3

25 a 6 10 b 5

Bases de Datos – Año 2014

Pag. [2]

Ejercicio E: Considere las tablas R y S.

¿Cuál es el resultado de la operación R ÷ S?

Ejercicio F: A continuación se detallan las relaciones que forman Bases de Datos de dominios diferentes. En todos los casos, identifique claves primarias y foráneas, y exprese a través del Álgebra Relacional las consultas enumeradas.

R M O S O A X X

B Y Y

A Z Z

B X

C Y

A Y

Bases de Datos – Año 2014

Pag. [3]

Caso 1 - Deportes PERS (Personas) = {Dni, Nom, Sexo, Edad, Dir, Tel, Zona} DEPO (Deportes) = {Codd, Nomd, Clase} CLUB (Clubes) = {Codc, Nomc, Dir, Tel, Cat, Zona} PRAC (Practican) = {Dni, Codd, Codc}

1. Obtener nombre, sexo y edad de todas las personas.

2. Obtener un listado que incluya el nombre de los clubes que están ubicados en Capital, los que están en Desamparados y los de Rivadavia.

3. Obtener nombre, sexo y edad de las personas que practican deportes.

4. Obtener el DNI de las personas que practican tenis.

5. Obtener el nombre de los clubes donde se practica fútbol.

6. Obtener para todas las personas que practican deportes: nombre de la persona, nombre del deporte y el nombre del club correspondiente.

7. Obtener el nombre de las personas que practican (simultáneamente) los deportes D01, D22 y D10 (códigos).

8. Obtener el nombre de las personas que no practican deportes en Ausonia.

9. Obtener el DNI de las personas que practican deportes en Ausonia o bien practican en la UVT.

10. Obtener el código de los deportes que son practicados solamente por hombres.

11. Obtener nombre y dirección de las personas que viven en la misma zona que Juan Pérez, DNI 20.498.425.

12. Obtener el nombre de las personas mayores que Ana García (DNI: 18.498.425).

13. Obtener el nombre de las personas que practican en clubes que están ubicados en la misma zona en la que ellos viven.

14. Obtener el nombre de las personas junto al nombre del deporte que práctica. Se deben incluir todas las personas registradas mas allá que no estén practicando ningún deporte.

15. Obtener el nombre de los deportes practicados en todos los clubes.

16. Obtener los clubes (todos sus datos) en los que se practican todos los deportes de balón (clase).

17. Obtener las personas (todos sus datos personales) que practican todos los deportes.

18. Obtener el nombre de las personas que practican todos los deportes que se practican en el Banco Hispano.

Bases de Datos – Año 2014

Pag. [4]

Caso 2 - Administración central de la feria de la Capital

La administración central de la feria de la Capital de nuestra provincia posee una base de datos que mantiene información relativa a los diferentes puestos, las expensas comunes que se deben pagar mensualmente, las diferentes mercaderías que se comercializan, etc.

Puestos = {nrop, tamaño, ubicación, dni} Personas = {dni, nombre, dirección} Expensas = {nrop, mes, año, fechaVenc, pagado (si/no), fechaPago, importe, dni} Productos = {codp, desc, tipo} SeVende = {nrop, codp}

1. Obtener los productos (todos sus datos) que son comercializados (SeVende) en el puesto P05.

2. Obtener los datos de las personas que comercializan “Hortalizas” (tipo) en sus puestos de venta.

3. Puestos (número de puesto) que comercializan tomates y zanahorias.

4. Personas (todos los datos personales) que comercializan tomates y/o zanahorias (en el mismo o distinto puesto).

5. Personas (dni y nombre) que son propietarios de más de un puesto.

6. Puestos (todos los datos) que están al día en el pago de sus expensas. Considere que es factible comparar una fecha con un literal.

7. Puestos (todos los datos) que comercializan todos los productos cítricos (tipo).

Bases de Datos – Año 2014

Pag. [5]

Caso 3 – Alumnos de una Facultad ALU (Alumnos) = {dni, nomAlu, dir. tel}

MAT (Materias) = {cod, nomMat, despliegue, carrera}

INSC (Inscriptos) = {dni, cod, fecha}1

BOL (Boletas) = {dni, cod, fecha} 2

EXA (Exámenes) = {dni, cod, fecha, nota}

1. Alumnos (nombre) que han rendido (aprobado) Base de Datos y Base de Datos Avanzadas.

2. Exámenes (nombre del alumno, nombre de la materia, fecha y nota) rendidos en forma regular.

3. Alumnos (todos los datos) que nunca tuvieron aplazos.

4. Alumnos (dni y nombre) que han sacado boleta de todas las materias de la carrera Lic. en Cs de la Computación

5. Alumnos (DNI y nombre) que están inscriptos en más de una carrera.

6. Materias (todos los datos) que tienen al menos un inscripto en el presente año. 3 Notas: 1- Un alumno se inscribe una sola vez en una materia. Puede estar inscripto en materias de carreras distintas, es decir, puede cursar más de una carrera. 2- Las boletas no tienen vencimiento. 3- Existe una función year(date) que devuelve el año de una fecha determinada.

Bases de Datos – Año 2014

Pag. [6]

Caso 4 – BIBLIOTECA LIBRO = {codlib, título, idioma, tema, editorial} EJEMPLAR = {codlib, nroord, edición} SOCIO = {dni, noms, direc, tel} AUTOR = {coda, noma} PRESTAMO = {codlib, nroord, dni, fechapres, fechadev, fecharealdev,devuelto(si/no)} ES_DE = {codlib, coda}

Resuelva los siguientes requerimientos:

1. Obtener el nombre de los Socios que no adeudan libros; es decir, no adeuda ningún ejemplar.

2. Obtener título e idioma de los libros que tratan sobre el tema “Nutrición” como así también tratan sobre “Cocina Saludable”.

3. Obtener los socios (dni y nombre) que se les ha prestado libros que tratan sobre el tema “Salud” y también se les ha prestado libros que tratan sobre el tema “Cocina Saludable”.

4. Mostrar el título, el tema y el nombre de la editorial de los ejemplares que fueron prestados en el 2009* y fueron devueltos cuando correspondía; es decir, la fecha de devolución coincide con la fecha real de devolución.

5. Obtener los títulos de los libros escritos por “Borges” como así también los libros escritos por “García Márquez”.

6. Obtener los datos de los socios que se llevaron en más de un oportunidad el mismo ejemplar.

7. Obtener DNI y nombre de los socios que se les ha prestado algún ejemplar de todos los libros que tratan el tema “Matemática Aplicada”. Es decir, han consultado todos los libros que tratan el tema “Matemática Aplicada”.

*NOTA: Use la función year (date) cuyo argumento de entrada es una fecha y devuelve el año correspondiente.

Bases de Datos – Año 2014

Pag. [7]

Caso 5 - Rentas PERS (Personas) = {Dni, Nombre, DomCalle, DomNro, DomOrient, Tel} LOCAL (Localidades) = {IdLoc, NomLoc} TIP_INM (Tipos de Inmuebles) = {IdTipo, Descripcion} INMU (Inmuebles) = {NroCat, Calle, Nro, Orientación, IdTipo (tipo de inmueble), IdLoc, DniProp} AUTO (Automotores) = {Patente, Marca, Modelo, DniProp } CUO_INM (Cuotas de Inmuebles) = {Mes, Año, FechaVenc, Pagado (si/no), Importe, NroCat} CUO_AUT (Cuotas de Automotores) = {Mes, Año, FechaVenc, Pagado (si/no), Importe, Patente}

Nota: * Existe una función date que devuelve la fecha.

1. Obtener los datos personales correspondientes a las personas que poseen todos los tipos de inmuebles posibles (terrenos, departamentos, etc).

2. Obtener nombre y documento de las personas que están al día en el pago de sus impuestos de inmuebles, es decir, tienen pagadas todas sus cuotas de inmuebles vencidas.

3. Obtener nombre y documento de las personas que están al día en el pago de sus impuestos, es decir, tienen pagadas todas sus cuotas vencidas.

4. Obtener todos los datos personales de los contribuyentes que son propietarios de por lo menos un inmueble y un vehículo.

5. Obtener el nombre de las personas que poseen sólo propiedades en Santa Lucía y en Capital.

Bases de Datos – Año 2014

Pag. [8]

Caso 6 – Banco PERS (Personas) = {Dni, Nombre, Dirección, Local, Tel} CCTES (Cuentas Corrientes) = {Nro, Fecha, ImporteInicial, SaldoActual**, Dni1*, Dni2*} CAHORRO (Cajas de Ahorro) = {Nro, Fecha, ImporteInicial, SaldoActual, Dni1*, Dni2*} TRANSAC = {NroTrans, Nro (de cuenta corriente o caja de ahorro), Fecha, Importe, IdTipo, Saldo**} TIPO_TRANS = {IdTipo, Desc}

Notas: * Tanto las cuentas corrientes como las cajas de ahorro pueden tener dos titulares. ** Los saldos de las cuentas corrientes pueden ser negativos.

1. Cajas de ahorros (todos sus datos) que aún no tienen movimientos.

2. Nombre de las personas que poseen al menos una cuenta corriente como así también al menos una caja de ahorro.

3. Cuentas corrientes que nunca generaron saldos negativos.

4. Personas que tienen dinero disponible (tienen saldo a favor en alguna cuenta corriente o en alguna caja de ahorro).

5. Cuentas corrientes (número y saldo) a las que se les efectuó al menos una transacción de cada tipo; es decir, todos los tipos de transacciones posibles.

6. Personas (todos los datos) que efectuaron en más de una ocasión transacciones en sus cuentas corrientes en rojo, es decir con saldo negativo.

Bases de Datos – Año 2014

Pag. [9]

Ejercicios Propuestos

Caso 7 – Accidentes de Tránsito COMPSEG (Compañía de Seguros) = {Cuit, Nom, Domi, Tel} VEHI (Vehículos) = {Pat, Modelo, Tipo (Moto, Auto, Camioneta o Camión), Cuit, Cm, Dni} MARCAS = {Cm, Descrip} PERS (Personas) = {Dni, Nom, Edad, Domi, Tel} ACTA = {NroActa, Lugar, Fecha, hora} INVO (Involucrados) = {NroActa, Pat, Dni, Conductor (si/no), daño (sin daño, leve, grave, o fatal)}

1. Obtener el nombre de las compañías de Seguros que tienen asegurados todos los tipos de vehículos.

2. Obtener todos los datos de los vehículos que no han tenido accidentes fatales.

3. Obtener el nombre de las personas que no tienen asegurados todos los vehículos en la misma compañía.

4. Obtener la patente de los autos involucrados en choques, en los que viajaba solamente el conductor (es decir, no tenía acompañantes).

5. Obtener todos los datos de las personas que han chocado manejando su propio vehículo.

Bases de Datos – Año 2014

Pag. [10]

Caso 8 – Agencia de Seguros

Una agencia de seguros posee una base de datos formada por las siguientes tablas:

PERS(Personas) = {Dni, Nom, Direcc, Email, Tel}

POL(Pólizas) = {Nro, F_desde, F_hasta, Importe, Patente, Dni, Estado (activa/inactiva)}

COB_POL(Cobertura Pólizas) = {Nro, Id}

TIPOS_COB(Tipos de Coberturas) = {Id, Descrip, Monto}

VEHIC(Vehículos) = {Patente, Marca, Modelo, Tipo (auto, camión, etc)}

1. Mostrar nombre y dirección de email de las personas que han contratado para su/sus vehiculo/s los tipos de cobertura “contra robo” y “contra incendio”.

2. Mostrar todos los datos personales de las personas que tienen más de un vehículo asegurado en la compañía.

3. Generar un informe que incluya DNI, nombre y teléfono de las personas que poseen asegurados camiones como así también incluya DNI, nombre y teléfono de las personas que poseen asegurados camionetas (el informe debe incluir a ambos).

4. Mostrar patente y modelo de los vehículos que tienen cobertura “contra terceros” únicamente.

5. Mostrar todos los datos de las pólizas que incluyen todos los tipos de coberturas registradas.

6. Mostrar nombre de las personas que tienen alguna póliza inactiva.

Bases de Datos – Año 2014

Pag. [11]

Caso 9 – Robos de Sucursales

SUC(Sucursal) = {cods, noms, localidad, mail} JUEZ = {dnij, nomj, dir,fechainicio} DEL(Delincuente) = {dnid, nomd, apodo} VIG(Vigilante) = { dniv, nomv, dir, cel, mail } CONT(Contratado) = {dniv, cods, fechadesde, fechahasta} ROBO = {dnid, dnij, cods, fecha, condena(si/no)}

Resuelva los siguientes requerimientos:

1. Obtener los delincuentes (todos sus datos) que han robado en la sucursal ubicada en la localidad de Capital como así también los que han robado en la sucursal ubicada en la localidad de Rawson.

2. Obtener los delincuentes (nombre y apodo) que robaron entre el año 2000 y el año 2009 indicando el nombre del Juez que intervino.

3. Obtener los vigilantes (todos sus datos) que fueron contratados en la misma sucursal en distintos periodos.

4. Obtener los vigilantes (nombre y celular) que fueron contratados en algún periodo del año 2009 en la sucursal de Santa Lucia y también fueron contratados en algún periodo del año 2009 en la sucursal de Rivadavia.

5. Obtener los vigilantes (todos sus datos) que nunca fueron contratados en la sucursal de la localidad de Jáchal.

6. Obtener el/los delincuentes (todos sus datos) que han robado en todas las sucursales.

*NOTA: Use la función year(date) cuyo argumento de entrada es una fecha y devuelve el año correspondiente.