el modelo de datos relacional, las bd restricciones relacionales y … · 2010-07-24 · integridad...
TRANSCRIPT
1© C. P. G. 2007 FBD Tema 3
Tema 3. Modelo relacional
El modelo de datos relacional, las restricciones relacionales y el álgebra
relacional.Elmasri/Navathe 02
Transformación de ER y ERE en relacional, y otros lenguajes relacionales.
Elmasri/Navathe 02
• Conceptos (7.1)• Restricciones (7.2)• Operaciones de actualización (7.3)• Transformación ER-relacional (9.1)• Álgebra relacional (7.4, 7.5)• Consultas con álgebra relacional (7.6)
2© C. P. G. 2007 FBD Tema 3
Conceptos
• BD: Colección de relaciones.
• Relación: Semeja una tabla:– Fila: Representa a entidad o vínculo.– Nombres de tabla y columnas: Identifican el
significado de los valores.– Valores de columna: Mismo tipo de datos.
• La BD Universidad del tema 1 sigue este modelo.
• Terminología:– A las tablas se las llama relaciones.– A las filas tuplas.– A las cabeceras de columna atributos.– Al tipo de datos de una columna dominio.
• Dominio D: Conjunto de valores atómicos (indivisible).
Consta de nombre, tipo de datos y formato.Ej. Edades_de_Empleados: valor entre 16 y 80.
NombreSmithBrown
Código alumno178
EspecialidadCSCS
Año12
ALUMNO
3© C. P. G. 2007 FBD Tema 3
Conceptos (2)
• Esquema de relación R(A1, ... , An): Describe la relación.– R es el nombre de la relación.– A1, ... , An su lista de atributos.– dom(Ai) dominio de Ai . .
– Grado de la relación: Número de atributos (n).
• Relación (estado o ejemplar de relación) r ó r(R) de un esquema R(A1, A2, . . ., An)es un conjunto de n-tuplas r={t1, t2, . . ., tm}:
– Cada n-tupla es una lista ordenada de valores t=<v1, v2, . . ., vn>
– Cada vi con 1≤ i ≤ n es:• Un elemento de dom(Ai).• O un valor nulo.
– El esquema R se llama intensión. – Un estado de relación r(R) se llama extensión.
4© C. P. G. 2007 FBD Tema 3
Características de las relaciones
• Orden entre las tuplas: No se considera ningún orden concreto (como en los elementos de un conjunto).
• Orden en los valores de una tupla:– Una n-tupla es una lista ordenada de n valores.– A nivel lógico lo que cuenta es mantener la
correspondencia entre atributos y valores.
• Valores en las tuplas: Son atributos atómicos. – No se admiten atributos compuestos .– Ni multivaluados .
• Existe el valor nulo.
5© C. P. G. 2007 FBD Tema 3
Notación
• ALUMNO (Nombre, Código alumno, Año, Especialidad)Esquema de la relación ALUMNO de grado 4.
• t = <‘Smith’, 17,1,’CS’> 4-tupla de la relación ALUMNO.
(ALUMNO indica el cjto. actual de tuplas, también llamado estado actual de la relación o ejemplar r(R)).
• t [Nombre] = <‘Smith’>
• t [Cód. Alumno, Especialidad, Año] = <17,’CS’,1>
• ALUMNO.Nombre Califican a los atributos• ALUMNO.Año de la relación ALUMNO.
NombreSmithBrown
Código alumno178
EspecialidadCSCS
Año12
ALUMNO
6© C. P. G. 2007 FBD Tema 3
Restricciones en las relaciones
• De dominio: El valor de cada atributo debe ser atómico. En SQL se verán los tipos disponibles.
• De clave: Atributo(s) de un esquema de relación que identifican unívocamente a las tuplas.– Superclave y clave.– Clave candidata y clave primaria.
• Integridad de entidades:Ninguna clave primaria puede contener el valor nulo.
• Integridad referencial: Una tupla que referencia a otra relación, debe referirse a una tupla existente en dicha relación.
• Claves externas (extranjeras o foráneas): – Son la forma de hacer referencia a otras
relaciones.– En una relación aparece la clave principal de otra
relación.
7© C. P. G. 2007 FBD Tema 3
Restricciones de clave
• En una relación no hay 2 tuplas con todos sus valores iguales (si no, no sería un conjunto de tuplas).
• Superclave:Subconjunto de atributos de un esquema de relación que cumple:
– No hay 2 tuplas con todos sus valores iguales.– El conjunto de atributos de una relación es
superclave.
• Clave: Superclave donde cualquier atributo que se elimine produce un subconjunto que no es superclave.
– El ser o no clave no debe cambiar con el tiempo.
• Clave candidata: Una clave posible.
• Clave primaria:La clave candidata elegida.
– Se subrayan.– Es preferible que sea un solo atributo o un
subconjunto reducido de atributos.
8© C. P. G. 2007 FBD Tema 3
Restricciones de clave: Ejemplo
NúmeroMatrícula NúmeroSerieMotor Marca Modelo AñoTexas ABC-739 A69352 Ford Mustang 96
COCHE
Florida TVP-347 B43696 Oldsmobile Cutlass 99N. York MPO-22 X83554 Oldsmobile Delta 95
California 432-TFY C43742 Mercedes 190-D 93California RSK-629 Y82935 Toyota Camry 98
Texas RSK-629 U028365 Jaguar XJS 98
Figura 7.4. La relación COCHE con dos claves candidatas:NúmeroMatrícula y NúmeroSerieMotor
Ejemplo de superclave:
NúmeroMatricula + Marca
9© C. P. G. 2007 FBD Tema 3
Esquema de la BD relacional “EMPRESA”
Figura 7.5. Esquema de base de datos relacional EMPRESA; las claves primarias están subrayadas.
• Una BD relacional contiene muchas relaciones.• Las tuplas se relacionan entre sí.
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
LOCALIZACIONES_DEPT
NÚMEROD LOCALIZACIÓND
PROYECTO
NOMBREP NÚMEROP LOCALIZACIÓNP NUMD
TRABAJA_EN
NSSE NP HORAS
DEPENDIENTE
NSSE NOMBRE-DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
NSS_SUPERV ND
EMPLEADO...
SEXO SALARIO...
10© C. P. G. 2007 FBD Tema 3
Estado de la BD relacional “EMPRESA”
Figura 7.6 (1ª parte)Ejemplar (Estado) de base de datos relacional del esquema EMPRESA.
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
1955-12-081965-01-09
1968-07-191941-06-201962-09-151972-07-311969-03-291937-11-10
Valle 638, Houston, TXFresnos 731, Houston, TX
Castillo 3321, Sucre, TXBravo 291, Bellaire, TXEspiga 875, Heras, TX
Rosas 5631, Houston, TXDalias 980, Houston, TXSorgo 450, Houston, TX
...
SEXO
HH
MMHMHH
NSS_SUPERV NDSALARIO
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...TRABAJA_EN
NSSE NP HORAS
333445555
123456789
999887777
987654321
666884444453453453
987987987
888665555
123456789
453453453
333445555333445555333445555
999887777
987987987
987654321
21
3122310
3020
101030302020
7.532.5
40.020.020.010.010.010.0
30.010.0
10.035.05.020.015.0nulo
NOMBRED NÚMERODNSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555 1981-06-19
1995-01-011988-05-22
11© C. P. G. 2007 FBD Tema 3
Estado de la BD relacional “EMPRESA” (cont)
Figura 7.6 (2ª parte) Ejemplar (Estado) de base de datos relacional del esquema EMPRESA.
LOCALIZACIONES_DEPT
NÚMEROD LOCALIZACIÓND1 Houston4 Stafford5 Bellaire5 Sugarland5 Houston
DEPENDIENTENSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
M HIJA1986-04-05Alicia333445555H HIJO1983-10-25Theodore333445555M ESPOSA1958-05-03Joy333445555H ESPOSO1942-02-28Abner987654321H HIJO1988-01-04Michael123456789M HIJA1988-12-31Alicia123456789M ESPOSA1967-05-05Elizabeth123456789
PROYECTONOMBREP NÚMEROP LOCALIZACIÓNP NUMDProductoX 1 Bellaire 5ProductoY 2 Sugarland 5ProductoZ 3 Houston 5
Automatización 10 Stafford 4Reorganización 20 Houston 1
Nuevos beneficios 30 Stafford 4
12© C. P. G. 2007 FBD Tema 3
Restricciones de integridad (RI)
• Integridad de entidades: Ningún valor de una clave primaria puede tener valor nulo.
• Integridad referencial:– Se da entre 2 relaciones.– Garantiza la consistencia entre tuplas de las 2
relaciones.– Una tupla de la relación A hace referencia a la
relación B. – Deberá referirse a una tupla existente en B.– Ejemplo:
EMPLEADO.ND y DEPARTAMENTO.NUMEROD .
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555 1981-06-19
1995-01-011988-05-22
NOMBRE INIC APELLIDO NSS
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
SALARIO NSS_SUPERV ND
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...
A B
13© C. P. G. 2007 FBD Tema 3
Clave externa (CE)
• CE conjunto no vacío de atributos de R1 .• CE es clave extranjera si:
– CP es clave primaria de R2 .– Los dominios de CE coinciden con los de CP.– CE hace referencia a CP:
• ∀ t1 ∈ R1 ∃ t2 ∈ R2 | t1 [CE] = t2[CP] .• o bien t1 [CE] = valor nulo .
• Ejemplo:EMPLEADO.ND hace referencia a DEPARTAMENTO.
• Puede hacer referencia a la propia relación:EMPLEADO.NSS_SUPERV .
NOMBRED NÚMEROD NSS_JEFE
DEPARTAMENTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555
FECHA_INIC_JEFE
1981-06-191995-01-011988-05-22
NOMBRE INIC APELLIDO NSS
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
SALARIO NSS_SUPERV ND
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...
14© C. P. G. 2007 FBD Tema 3
Integridad referencial en el esquema
Figura 7.7. Restricciones de integridad referencial representadas en el esquema de la base de datos relacional EMPRESA.
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
LOCALIZACIONES_DEPT
NÚMEROD LOCALIZACIÓND
PROYECTO
NOMBREP NÚMEROP LOCALIZACIÓNP NUMD
TRABAJA_EN
NSSE NP HORAS
DEPENDIENTE
NSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN SEXO
SALARIO NSS_SUPERV ND
EMPLEADO...
...
15© C. P. G. 2007 FBD Tema 3
Restricciones de integridad
• Las RI se especifican utilizando una parte del lenguaje llamada DDL:– DDL = Data Definition Language / Lenguaje de
definición de datos.
– Este tipo de lenguaje debe contar con mecanismos para especificar RI.
– El SGBD puede imponerlas automáticamente.
• RI semánticas:– “El salario de un empleado no debe ser superior
al de su jefe”.
– “Dedicación semanal de un empleado a todos los proyectos no superior a 56 horas”.
• Muy pocos SGBD soportan las RI semánticas.• Se están incorporando mecanismos.
16© C. P. G. 2007 FBD Tema 3
Operaciones de actualización
• Las operaciones de actualización se especifican utilizando otra parte del lenguaje llamada DML:DML= Data Manipulation Language / Lenguaje de Manipulación de Datos
• Las operaciones de actualización cambian los datos de la BD, frente a las operaciones de consulta que simplemente recuperan datos de la BD pero no los cambian.
• Son las siguientes:– Insertar.– Eliminar.– Modificar.
• Cuando se aplican no deben violar ninguna restricción de integridad.
• Veremos:– Qué RI puede violar cada operación.– Qué acciones se pueden emprender en caso de
violación.
17© C. P. G. 2007 FBD Tema 3
Insertar
• Insertar <‘Cecilia’, ’F’, ’Kolonsky’, ’677678989’, ‘05-ABR-60’, ‘Calle Viento 6357, Malinalco, TX’, ‘M’, 28000, nulo, 4 >
en EMPLEADO.
• Puede violar los 4 tipos de RI:– Dominio:
Insertar< ..., ‘Depto 3’>– Entidades:
Insertar <‘Cecilia’,... ,’Kolonsky’, nulo,...,4>– Clave:
Insertar <‘Cecilia’, ... , ’999887777’, ... , 4>– I. referencial:
Insertar <‘Cecilia’, ..., 7>
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
1955-12-081965-01-09
1968-07-191941-06-201962-09-151972-07-311969-03-291937-11-10
Valle 638, Houston, TXFresnos 731, Houston, TX
Castillo 3321, Sucre, TXBravo 291, Bellaire, TXEspiga 875, Heras, TX
Rosas 5631, Houston, TXDalias 980, Houston, TXSorgo 450, Houston, TX
...
SEXO
HH
MMHMHH
SALARIO NSS_SUPERV ND
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...
ACEPTABLE
18© C. P. G. 2007 FBD Tema 3
Insertar (2)
• Ante violación de RI al insertar:– Rechazar:
• Es lo que se hace normalmente.• Útil explicación SGBD.
– Corregir:• Se usa más para modificaciones y
eliminaciones.• Entidades:
– Pedir NSS válido.– Que la clave no sea nula ni exista ya.
• I.Referencial:– Pedir valor existente o nulo.– Pedir introducción de la tupla
correspondiente al valor referenciado:» Pedir datos del departamento 7
(que no existía)– Pedir en Cascada :
» Pedir datos del departamento 7» Una vez que el departamento
existe, pedir de nuevo el empleado
19© C. P. G. 2007 FBD Tema 3
Eliminar
• Eliminar todos los TRABAJA_EN con NSSE=‘999887777’ y NP=10.
• Se eliminan todas las tuplas que cumplan la condición.
• Solamente se puede violar la integridad referencial si las CEs de otras tuplas de la BD hacen referencia a la tupla que se va a eliminar.
• Puede violar I. referencial:– Eliminar todo EMPLEADO con NSS=‘999887777’– Eliminar todo EMPLEADO con NSS=‘333445555’
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
1955-12-081965-01-09
1968-07-191941-06-201962-09-151972-07-311969-03-291937-11-10
Valle 638, Houston, TXFresnos 731, Houston, TX
Castillo 3321, Sucre, TXBravo 291, Bellaire, TXEspiga 875, Heras, TX
Rosas 5631, Houston, TXDalias 980, Houston, TXSorgo 450, Houston, TX
...
SEXO
HH
MMHMHH
SALARIO NSS_SUPERV ND
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...
ACEPTABLE
20© C. P. G. 2007 FBD Tema 3
Eliminar (2)
• Ante violación de RI al eliminar, se puede:– Rechazar.– Propagar:
• Eliminar todas las tuplas con referencia a la eliminada. A esto se le denomina eliminación en cascada.
– Modificar:• Poner un valor existente en referencias.• Poner valor nulo en referencias
(no si es parte de la clave primaria).– Propagar y modificar:
• Al eliminar un EMPLEADO podríamos:– Eliminar referencias en TRABAJA_EN y
DEPENDIENTE.
– Modificar referencias en EMPLEADO y DEPARTAMENTO (las de los empleados y departamentos en los que era jefe el empleado borrado).
• En general, el SGBD permite especificar la opción a aplicar.
21© C. P. G. 2007 FBD Tema 3
Modificar
• Modificar el SALARIO del EMPLEADOcon NSS=‘999887777’ a 28.000.
• Con atributos que no sean clave primaria ni externa:– No suelen producirse problemas.– Salvo que no sea un valor del dominio.
• Si es clave primaria:– Equivale a eliminar la tupla e insertar una nueva.– Mismos problemas que en insertar y eliminar.
• Si es clave extranjera:– El SGBD debe asegurar que el nuevo valor existe.
• Ejemplos que violan la RI:– I. Referencial:
Modificar todos los ND de EMPLEADOcon NSS=‘999887777’ a 7.
– Clave primaria e I. referencial:Modificar todos los NSS de EMPLEADO
con NSS=‘999887777’ a ‘987654321’.
ACEPTABLE
22© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional
• Algoritmo en 7 pasos para transformar un esquema E/R en el modelo relacional correspondiente. Los pasos se han de dar en el orden indicado.
• Paso 1: T. entidades normales (no débiles)Por cada tipo de entidad normal E crear una relación R que contenga todos los atributos simples de E. Si hay algún atributo compuesto se ponen sus componentes simples. Se elige una clave como clave primaria.
A1 A2 A3 A5R (de E)
A6E
A1A2
A3
A4A5
A6
E2A4
A1A2
A3 A2 A3 A4R (de E2)
23© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional (2)
• Paso 2: T. entidades débiles (+Vínculos identificadores)Por cada tipo de entidad débil W crear una relación R con todos los atributos de W (los compuestos con sus componentes simples).Se incluyen también como atributos las claves primarias de la/s relación/es propietarias.La clave de la nueva relación R estará formada por la combinación de las claves primarias de las relaciones propietarias más la clave parcial de W.
Con este paso quedan tratados tanto los tipos de entidad débiles, como sus vínculos identificadores.
W E
A1 B2B1
B1 A1 A2R (de W)A2
CE
24© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional (3)
• Paso 3: T. relación 1:1
Por cada vínculo 1:1 se incluye como clave externa en S (mejor que S sea la de participación total, si la hay) la clave primaria de T. Si hay atributos en el vínculo se ponen también en S.
• Paso 4: T. relación 1:N
Por cada vínculo 1:N se incluye como clave externa en S (la relación surgida de la parte N del vínculo) la clave primaria de T. Si hay atributos en el vínculo se ponen también en S.
E1 E2
A1 B2A1 A2 B1S (de E1, T=E2)A2
1 1B1
R1
R1CE
E1 E21 1
Mejor
E1 E2
A1 B2A1 A2 B1S (de E1, T=E2)A2
N 1B1
R1
R1CE
25© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional (4)
• Paso 5: T. relación M:N
Por cada tipo de vínculo M:N se crea una nueva relación S para representar al vínculo R.Se incluyen como atributos de clave externa las claves primarias de las relaciones surgidas a partir de E1 y E2.La clave primaria de S será la combinación de las claves primarias anteriores.Si el tipo de vínculo tiene atributos, éstos se incluyen también en S.
– Se puede usar también para 1:1 y 1:N.– Mejor cuando haya pocas ocurrencias de la relación
(menos nulos).
E1 E2R
A1 B2A1 B1S (de R)A2
M NB1
R1
R1CE CE
26© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional (5)
• Paso 6: Atributos multivaluadosPor cada atributo multivaluado A se crea una nueva relación S que incluye el atributo correspondiente a A y la clave primaria K de la relación correspondiente al tipo de entidad donde está A.La clave será normalmente la combinación de A más K.
EA1A2
A1 A3EA3
A1 A2R (de A2)
CE
E2A1A2
A4
A5A1 A3E2
A3
A1 A4R2 (de A2)
A5CE
27© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional (6)
• Paso 7: T. relaciones n-arias
Por cada tipo de vínculo n-ario R se crea una nueva relación S con atributos clave externa las claves primarias de las relaciones surgidas de todos los tipos de entidad participantes. La clave primaria de S será una combinación de estas claves.Si existen atributos en el vínculo, se añadirán también como atributos de S.
– Si hay algún (*,1) entonces la clave es la de esta entidad. El * es la participación (0 parcial ó 1 total).
E1 E2R
A1 B2A1 B1 C1S (de R)A2 B1
R1
R1CE
E3
C2C1
CECE
28© C. P. G. 2007 FBD Tema 3
ESQUEMA ER DE LA BD “EMPRESA”
1
Nombre
NombreP Iniciales Apellido
FAMILIAR*
ES_FAMILIA_DE
Fecha nacimiento
Parentesco
Sexo
N
Nombre
TRABAJA_PARA DIRIGE
TRABAJA_EN
PROYECTONombre
NúmeroLocalización
DEPARTAMENTO
Nombre
Número
Localizaciones
Fecha inicio
Número de empleadosHoras
CONTROLA
N
1
1
1
N
1
N
M
Sexo Dirección SalarioNSSFecha nacimiento
SUPERVISA
1
N
supervisor
supervisado
EMPLEADO
*Familiar=dependiente
29© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional BD EMPRESA
NOMBREPROYECTO
NÚMERO LOCALIZACIÓN
NOMBREDEPARTAMENTO
NÚMERO
• PASO 1: TE. Fuertes
• PASO 2: TE. Débiles
FAMILIARNOMBRENSS FECHA_NCTOSEXO PARENTESCO
• PASO 3: TV. 1:1
NOMBREDEPARTAMENTO
NÚMERO NSS_JEFE FECHA_INIC_JEFE
• PASO 4: TV. 1:N
NOMBREPROYECTO
NÚMERO LOCALIZACIÓN ND
NOMBREPEMPLEADO
INICIALES APELLIDO NSS FECHA_NCTO SEXO
DIR SALARIO...
NOMBREPEMPLEADO
INICIALES APELLIDO NSS FECHA_NCTO SEXO
DIR SALARIO ND NSS_SUPERV...
30© C. P. G. 2007 FBD Tema 3
Transformación ER-relacional BD EMPRESA (2)
NOMBREPROYECTO
NÚMERO LOCALIZACIÓN ND
NOMBREDEPARTAMENTO
NÚMERO NSS_JEFE FECHA_INIC_JEFE
• PASO 5: TV. M:N
TRABAJA_ENNSSENÚMEROP HORAS
• PASO 6: Atr. Multivaluados
LOCALIZACIONES_DPTONÚMEROD LOCALIZACIÓN
• Solución completa:
LOCALIZACIONES_DPTONÚMEROD LOCALIZACIÓN
TRABAJA_ENNSSENÚMEROP HORAS
NOMBREPEMPLEADO
INICIALES APELLIDO NSS FECHA_NCTO SEXO
DIR SALARIO ND NSS_SUPERV...FAMILIAR
NOMBRENSS FECHA_NCTOSEXO PARENTESCO
31© C. P. G. 2007 FBD Tema 3
Transformación ER- relacional
• Más ejemplos:Convertir los esquemas conceptuales de los ejercicios BUQUES y BIBLIOTECA del tema anterior en los modelos relacionales correspondientes aplicando el algoritmo de los 7 pasos.
32© C. P. G. 2007 FBD Tema 3
Definir esquemas de BDR (relaciones)
• Un esquema de base de datos relacional (BDR) se define mediante un lenguaje de definición de datos (DDL).
• El DDL nos permite:– Dar nombre al esquema de BD.
– Declarar dominios de atributos:• Nombre dominio.• Tipo de datos.
– Definir cada relación:• Nombre relación.• Nombre atributos.• Dominio atributos.• Indicar clave primaria (y candidatas).• Indicar claves extranjeras (claves que son
primarias en otra relación).
• La mayoría de los DDLs se basan en el lenguaje SQL, que a su vez se basa en el álgebra relacional.
33© C. P. G. 2007 FBD Tema 3
Álgebra relacional
• Operaciones para manipular relaciones enteras.
• De teoría de conjuntos:– UNIÓN.– INTERSECCIÓN.– DIFERENCIA.– PRODUCTO CARTESIANO.
• Específicas:– SELECCIÓN.– PROYECCIÓN.– REUNIÓN (JOIN).
• DIVISIÓN.
• Otras:– FUNCION AGREGADA.– CIERRE RECURSIVO.– REUNIÓN EXTERNA.– UNIÓN EXTERNA.
Binarias
Binaria
Unarias
Binaria
Operación Unaria: Interviene una sola relaciónOperación Binaria: Intervienen dos relaciones
34© C. P. G. 2007 FBD Tema 3
Selección
• Selecciona un subconjunto de tuplas de una relación.
• Las que satisfacen una condición.• El resultado es otra relación.
• σND=4(EMPLEADO)• σSALARIO>30000(EMPLEADO)
• La relación resultante tiene los mismos atributos.• Tiene menor o igual número de tuplas.
• Condición: {=, <, ≤≤≤≤, >, ≥≥≥≥, ≠≠≠≠} Y, O, NO.
σσσσ<condición selección>(R)
SIGMA
OPERADORES RELACIONALES
OPERADORES LÓGICOS
35© C. P. G. 2007 FBD Tema 3
Selección (2)
σ(ND=4 Y SALARIO>25000) o (ND=5 Y SALARIO>30000)(EMPLEADO)
Fig. 7.8. (a)
Propiedades de la selección:• Conmutativa:
σ<COND1>( σ<COND2>(R) ) = σ<COND2>( σ<COND1>(R) )
• Combinación cascada de selecciones:σ<COND1>( σ<COND2>(R) ) = σ<COND1> Y <COND2>(R)
NOMBRE INIC APELLIDO
Franklin
Jennifer
Ramesh
T
S
K
Wong
Wallace
Narayan
NSS
333445555
987654321
666884444
FECHA_NCTO
1955-12-08
1941-06-20
1962-09-15
DIRECCIÓN
Valle 638, Houston, TX
Bravo 291, Bellaire, TX
Espiga 875, Heras, TX
...
SEXO
H
M
H
SALARIO NSS_SUPERV ND
40.000
43.000
38.000
888665555
888665555
333445555
5
4
5
...
36© C. P. G. 2007 FBD Tema 3
Proyección
• Selecciona algunas columnas de una relación.• El resultado es otra relación.• La relación resultante tiene sólo los atributos
especificados.• Atributos en el mismo orden especificado.• Eliminación de duplicados.
ππππ<lista de atributos>(R)
lista de atributos: Atributos separados por comas
PI
37© C. P. G. 2007 FBD Tema 3
Proyección (2)
• πAPELLIDO, NOMBRE, SALARIO(EMPLEADO)
NOMBREAPELLIDO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
SALARIO
40.00030.000
25.00043.00038.00025.00025.00055.000
Propiedades de la proyección:• Combinación cascada de proyecciones:
π<LISTA1> (π<LISTA2> (R)) = π<LISTA1> (R)donde <LISTA1> ⊂ <LISTA2> .
• No conmutativa.
Fig. 7.8. (b)πAPELLIDO, NOMBRE, SALARIO (EMPLEADO)
SEXO
HH
MMHHH
SALARIO
40.00030.000
25.00043.00038.00025.00055.000
Fig. 7.8. (c)πSEXO, SALARIO (EMPLEADO)
38© C. P. G. 2007 FBD Tema 3
Resultados intermediosCambios de nombre
• En álgebra relacional se pueden asignar los resultados obtenidos en una operación (todo resultado da una relación) a otra relación; para así ir hallando resultados intermedios.
• Los nombres de los atributos de S son los mismos que tuviésemos en R. Se pueden cambiar los nombres de los atributos mediante:
S←←←←RR: Cualquier operación o conjunto de operaciones que nos de una relación.S: Nueva relación en la que se guarda Ry que tiene los mismos atributos que R.
S(lista nuevos nombres)←←←←Rlista nuevos nombres: Nuevos nombrespara los atributos resultantes, separadospor comas y en el mismo orden que estuvieran en R.
39© C. P. G. 2007 FBD Tema 3
Resultados intermediosCambios de nombre (2)
• πNOMBRE, APELLIDO, SALARIO(σND=5(EMPLEADO))
Fig 7.9. (a)
• Alternativa con resultados intermedios:
EMPS_DEP5 ← σND=5(EMPLEADO)RESULTADO←πNOMBRE,APELLIDO,SALARIO(EMPS_DEP5)
• Alternativa con resultados intermedios y cambios de nombre:
TEMP ← σND=5(EMPLEADO)R(NOMBRE_PILA, PRIMER_APELL, SALARIO)
← πNOMBRE,APELLIDO,SALARIO(TEMP)
NOMBRE APELLIDO
FranklinJohn
RameshJoyce
WongSmith
NarayanEnglish
SALARIO
40.00030.000
38.00025.000
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
SEXO
FranklinJohn
RameshJoyce
TB
KA
WongSmith
NarayanEnglish
333445555123456789
666884444453453453
1955-12-081965-01-09
1962-09-151972-07-31
Valle 638, Houston, TXFresnos 731, Houston, TX
Espiga 875, Heras, TXRosas 5631, Houston, TX
HH
HM
...
SALARIO NSS_SUPERV ND
40.00030.000
38.00025.000
888665555333445555
333445555333445555
55
55
...
TEMP
NOMBRE_PILA PRIMER_APELL
FranklinJohn
RameshJoyce
WongSmith
NarayanEnglish
SALARIO
40.00030.000
38.00025.000
R
Fig. 7.9. (b) Resultados de expresiones del álgebra relacional.
40© C. P. G. 2007 FBD Tema 3
Operaciones de T. de conjuntos
Unión, intersección y diferencia.• Son operaciones binarias.• Su resultado es otra relación.• Las 2 relaciones han de tener igual tipo de tuplas:
Compatibilidad de unión.
R ∪∪∪∪ SLa Unión entre dos relaciones R y S nos da todaslas tuplas tanto de R como de S, eliminando las que están repetidas.
R ∩∩∩∩ SLa Intersección entre dos relaciones R y S nos da todas las tuplas comunes a R y S, eliminando las que están repetidas.
R - SLa Diferencia entre dos relaciones R y S nos da todas las tuplas de R que no están en S.
41© C. P. G. 2007 FBD Tema 3
Operaciones de T. de conjuntos
Ejemplo de Unión
EMPS_DEP5 ← σND=5(EMPLEADO)RESULTADO1←πNSS(EMPS_DEP5)RESULTADO2(NSS) ←πNSS_SUPERV (EMPS_DEP5)RESULTADO← RESULTADO1∪ RESULTADO2
NSS
333445555123456789
666884444453453453
RESULTADO1
NSS333445555888665555
RESULTADO2
NSS
333445555123456789
666884444453453453
RESULTADO
888665555
Fig. 7.10. RESULTADO← RESULTADO1∪ RESULTADO2
42© C. P. G. 2007 FBD Tema 3
Ejemplos de unión e intersección
• Propiedades de Unión e intersección:– Conmutativas:
R ∪ S = S ∪ RR ∩ S = S ∩ R
– Asociativas:(R ∪ S) ∪ T = R ∪ (S ∪ T) (R ∩ S) ∩ T = R ∩ (S ∩ T)
Fig. 7.11 (a)Dos relacionescompatibles con la unión.
ALUMNONOM
RameshSusana
JosuéBárbaraAmanda
JaimeErnesto
APEL
SánchezYáñez
LandaJaimesFloresVélez
Gómez
NOM
RicardoJohn
SusanaFrancisco
PROFESOR
Ramesh
APEL
BuenoSmith
YáñezJiménezSánchez
NOM
RameshSusana
APEL
SánchezYáñez
NOM
RameshSusana
JosuéBárbaraAmanda
JaimeErnesto
APEL
SánchezYáñez
LandaJaimesFloresVélez
Gómez
RicardoJohn
FranciscoBuenoSmith
Jiménez
Fig 7.11. (b) ALUMNO ∩ PROFESOR
Fig 7.11. (c) ALUMNO ∪ PROFESOR
43© C. P. G. 2007 FBD Tema 3
Ejemplos de diferencia
• La diferencia no es conmutativa:R — S ≠≠≠≠ S — R (en general)
Fig. 7.11. (d)ALUMNO — PROFESOR
ALUMNONOM
RameshSusana
JosuéBárbaraAmanda
JaimeErnesto
APEL
SánchezYáñez
LandaJaimesFloresVélez
Gómez
NOM
RicardoJohn
SusanaFrancisco
PROFESOR
Ramesh
APEL
BuenoSmith
YáñezJiménezSánchez
NOMJosué
BárbaraAmanda
JaimeErnesto
APELLandaJaimesFloresVélez
Gómez
NOM
RicardoJohn
Francisco
APEL
BuenoSmith
Jiménez
Fig. 7.11. (e)PROFESOR— ALUMNO
44© C. P. G. 2007 FBD Tema 3
Operaciones de T. de conjuntos
Producto cartesiano (CROSSJOIN).• Intervienen dos relaciones.• Esas relaciones no tienen por qué ser compatibles
con la unión.
R ×××× S
El resultado del producto cartesiano consta de todas las combinaciones posibles de cada tupla de R seguida de otra de S. Es decir:- Tiene todos los atributos de R y S; primero los de
R y seguidos los de S.- Tiene n* m tuplas; siendo n el número de tuplas
de R y m el número de tuplas de S.
PRODUCTO CARTESIANO
45© C. P. G. 2007 FBD Tema 3
Ejemplo Producto cartesiano
• Ejemplo: Obtener los familiares de empleadas.
EMPS_MUJER ← σsexo=‘M’(EMPLEADO)NOMBRESEMP←πNOMBRE,APELLIDO,NSS(EMPS_MUJER)DEPENDIENTES_EMP ←
NOMBRESEMP ×××× DEPENDIENTEDEPENDIENTES_REALES←
σNSS=NSSE(DEPENDIENTES_EMP)RESULTADO←πNOMBRE,APELLIDO,NOMBRE_DEPEN-
DIENTE(DEPENDIENTES_REALES)
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
SEXO
EMPS_MUJER
AliciaJenniferJoyce
JSA
ZelayaWallaceEnglish
999887777987654321453453453
1968-07-191941-06-201972-07-31
Castillo 3321, Sucre, TXBravo 291, Bellaire, TX
Rosas 5631, Houston, TX
MMM
...
SALARIO NSS_SUPERV ND25.00043.00025.000
987654321888665555333445555
445
...
NOMBRE APELLIDO NSS
NOMBRES_EMP
AliciaJenniferJoyce
ZelayaWallaceEnglish
999887777987654321453453453
Fig. 7.12.
46© C. P. G. 2007 FBD Tema 3
Ejemplo Producto Cartesiano (2)
DEPENDIENTES_EMP ←NOMBRES_EMP ×××× DEPENDIENTE
NOMBRE APELLIDO NSSNOMBRES_EMP
AliciaJenniferJoyce
ZelayaWallaceEnglish
999887777987654321453453453DEPENDIENTE
NSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
M HIJA1986-04-05Alicia333445555H HIJO1983-10-25Theodore333445555M ESPOSA1958-05-03Joy333445555H ESPOSO1942-02-28Abner987654321H HIJO1988-01-04Michael123456789M HIJA1988-12-31Alicia123456789M ESPOSA1967-05-05Elizabeth123456789
DEPENDIENTES_EMP
NOMBRE APELLIDO NSS
Alicia Zelaya 999887777
Jennifer Wallace 987654321
Joyce English 453453453
Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777
NSSE NOMBRE_DEPENDIENTE SEXO ...M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789
Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321
Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453
Fig. 7.12
47© C. P. G. 2007 FBD Tema 3
Ejemplo Producto Cartesiano (3)
DEPENDIENTES_REALES←σNSS=NSSE(DEPENDIENTES_EMP)
RESULTADO← πNOMBRE,APELLIDO,NOMBRE_DEPENDIENTE(DE-PENDIENTES_REALES)
NOMBRE APELLIDO NSS
DEPENDIENTES_REALES
NSSE NOMBRE_DEPENDIENTE SEXO ...H ...Abner987654321Jennifer Wallace 987654321
NOMBRE APELLIDO
RESULTADO
NOMBRE_DEPENDIENTE
AbnerJennifer Wallace
DEPENDIENTES_EMP
NOMBRE APELLIDO NSS
Alicia Zelaya 999887777
Jennifer Wallace 987654321
Joyce English 453453453
Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777Alicia Zelaya 999887777
NSSE NOMBRE_DEPENDIENTE SEXO ...M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789M ...Alicia333445555H ...Theodore333445555M ...Joy333445555H ...Abner987654321H ...Michael123456789M ...Alicia123456789M ...Elizabeth123456789
Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321Jennifer Wallace 987654321
Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453Joyce English 453453453
48© C. P. G. 2007 FBD Tema 3
Reunión (JOIN)
• Combina tuplas relacionadas de 2 relaciones.• Operación muy importante para una BDR.• Permite procesar vínculos entre relaciones.• Eliminación de duplicados.• Las relaciones que intervienen no tienen por qué ser
compatibles con la unión.• Su resultado puede tener un máximo de n* m tuplas
(si todas las tuplas cumplen la condición de reunión).
R <condición de reunión>S
El resultado de la Reunión consta de todas las combinaciones de cada tupla de R seguida de otra de S, que satisfagan la condición de reunión.Es equivalente a un producto cartesianoseguido de una selección.
REUNIÓN
49© C. P. G. 2007 FBD Tema 3
Ejemplo de Reunión
• Ejemplo: Obtener el nombre del departamento y el nombre (nombre y apellido) del jefe de cada departamento.
JEFE_DEPTO ←DEPARTAMENTO NSS_JEFE=NSS EMPLEADO
(se lee: reunión entre departamento y empleado con NSS_JEFE= NSS)
RESULTADO ←πNOMBRED,APELLIDO,NOMBRE(JEFE_DEPTO)
Fig. 7.13. La operación REUNIÓN.
NSS FECHA_NCTO DIRECCIÓN SEXO
333445555987654321888665555
1955-12-081941-06-201937-11-10
Valle 638, Houston, TXBravo 291, Bellaire, TXSorgo 450, Houston, TX
HMH
SALARIO NSS_SUPERV ND
40.00043.00055.000
888665555888665555
nulo
541
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
JEFE_DEPTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555 1981-06-19
1995-01-011988-05-22
NOMBRE INIC APELLIDO
FranklinJenniferJaime
TSE
WongWallace
Borg
...
...
50© C. P. G. 2007 FBD Tema 3
Ejemplo de Reunión (2)
• En el ejemplo del producto cartesiano:DEPENDIENTES_EMP ←
NOMBRES_EMP ×××× DEPENDIENTEDEPENDIENTES_REALES←
σNSS=NSSE(DEPENDIENTES_EMP)
• Lo anterior equivale a:DEPENDIENTES_REALES ←
NOMBRES_EMP NSS=NSSE DEPENDIENTE
51© C. P. G. 2007 FBD Tema 3
Condición de reuniónTipos de reunión (JOIN)
Condición de reunión: – En términos de atributos de R y S.– Evaluada por cada combinación de tuplas.– Tiene la forma: <condición> Y <condición> Y ... Y <condición> – Cada condición tiene la forma:
• Ai θθθθ Bj donde Ai ∈ R y Bj ∈ S• θθθθ ∈ {=, <, ≤, >, ≥, ≠}
Tipos de reunión:
• Reunión Theta (θθθθ ): Cualquier operación de reunión. – Las tuplas cuyo atributo de reunión sea nulo
NO aparecen en el resultado.
• Equirreunión (Equijoin): Las que sólo utilizan comparaciones de igualdad.– El resultado siempre tiene pares de atributos
con valores idénticos en todas las tuplas.
52© C. P. G. 2007 FBD Tema 3
Condición de reuniónTipos de reunión (2)
• Reunión natural (Natural join): – Equirreunión seguida de la eliminación de
atributos superfluos (se eliminan los atributos cuyo nombre se repite).
– Reunión en base a todos los pares de atributos de igual nombre.
– Exige algún par de atributos de igual nombre en las dos relaciones que intervienen.
– Se identifica con *.
R * SUna Reunión Natural es un tipo de Reuniónen la que la condición de reunión está formadapor tantas condiciones de igualdad unidas mediante el operador lógico Y como paresde atributos tengan el mismo nombre en R y S.
REUNIÓNNATURAL
53© C. P. G. 2007 FBD Tema 3
Ejemplo de reunión natural
Añadir a cada proyecto la información del departamento que lo controla
DEPTO(NOMBRED, ND,NSS_JEFE,FECHA_INIC_JEFE) ←DEPARTAMENTOPROY_DEPTO ← PROYECTO * DEPTO
• ND es el atributo de reunión.
NOMBRED ND NSS_JEFE FECHA_INIC_JEFEDEPTO
Dirección 1 888665555 1981-06-19Administración 4 987654321 1995-01-01Investigación 5 333445555 1988-05-22
PROYECTO
NOMBREP NÚMEROP LOCALIZACIÓNP ND
ProductoX 1 Bellaire 5ProductoY 2 Sugarland 5ProductoZ 3 Houston 5
Automatización 10 Stafford 4Reorganización 20 Houston 1
Nuevos beneficios 30 Stafford 4
PROY_ DEPTO
NOMBREP NÚMEROP LOCALIZACIÓNP ND
ProductoX 1 Bellaire 5ProductoY 2 Sugarland 5ProductoZ 3 Houston 5
Automatización 10 Stafford 4Reorganización 20 Houston 1Nuevos beneficios 30 Stafford 4
Investigación 333445555 1988-05-22Investigación 333445555 1988-05-22Investigación 333445555 1988-05-22
Administración 987654321 1995-01-01
Administración 987654321 1995-01-01Dirección 888665555 1981-06-19
NOMBRED NSS_JEFE FECHA_INIC_JEFE
Fig. 7.14. (a) PROY_ DEPTO ← PROYECTO * DEPTO
54© C. P. G. 2007 FBD Tema 3
Ejemplo de reunión natural (2)
Añadir los lugares de ubicación de cada departamentoDEPTO_LOCS ←DEPARTAMENTO * LOCALIZACIONES_DEPT
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555 1981-06-19
1995-01-011988-05-22
LOCALIZACIONES_DEPT
NÚMEROD LOCALIZACIÓND
1 Houston4 Stafford5 Bellaire5 Sugarland5 Houston
• En este ejemplo sólo hay un par de atributos de igual nombre (NÚMEROD).
• Si hubiera más pares con igual nombre se seleccionarían sólo las tuplas que igualen todas las parejas de atributos.
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPTO_ LOCS
AdministraciónDirección
41
987654321888665555 1981-06-19
1995-01-01Investigación 5 333445555 1988-05-22
LOCALIZACIÓND
Houston
StaffordBellaire
Sugarland
Houston
Investigación 5 333445555 1988-05-22Investigación 5 333445555 1988-05-22
Fig. 7.14.(b) La operación REUNIÓN NATURAL.
55© C. P. G. 2007 FBD Tema 3
Selectividad de reunión.Conjunto completo
• Selectividad de reunión: Razón que se obtiene dividiendo el tamaño esperado para la reunión (se realiza una estimación) entre el tamaño máximo posible (nº de tuplas de R* nº de tuplas de S).
• El álgebra relacional es un Conjunto completo: – Cualquier operación del álgebra relacional se
puede expresar como secuencia de operaciones del conjunto {σ, π, ∪ , —, ×××× }.
– Operaciones tan importantes como la reunión no son estrictamente necesarias.
– Se mantienen por comodidad y frecuencia de uso.
56© C. P. G. 2007 FBD Tema 3
División
• Ejemplo: T ←←←← R ÷÷÷÷ S
Fig. 7.15 (b)
• En el ejemplo: Z={A,B}; X={A}; Y={B}
Aa1
S
a2a3
Bb1
T
b4
Aa1
R
a2a3a4a1
a2a3
a4a1a2
a3
a3
b1
b4
b1b1b1
b4b4
b2b2b3b3b3
B
R(Z) ÷÷÷÷ S(X)
Z: Atributos de RX: Atributos de SPara dividir se tiene que cumplir que X ⊆ ZY: Son los atributos de R que no están en S,es decir, R-S. Constituyen los atributos del resultado de la división. Devuelve cada conjunto de atributos Y de R que tenga a todos y cada uno de los X distintos que haya en S.
57© C. P. G. 2007 FBD Tema 3
Ejemplo de División
• Obtener los nombres de los empleados que trabajan en todos y cada uno de los proyectos en los que trabaja John Smith.
SMITH ← σNOMBRE=‘John’ Y APELLIDO=‘Smith’(EMPLEADO)NÚMSP_SMITH ← πNP(TRABAJA_EN NSSE=NSS SMITH)
Fig. 7.15 (a)
NSS_NÚMSP ← πNP,NSSE(TRABAJA_EN)NSSS(NSS)← NSS_NÚMSP ÷ NÚMSP_SMITH
Fig. 7.15 (a)
RESULTADO ← πNOMBRE, APELLIDO(NSSS * EMPLEADO)
NÚMSP_SMITHNP12
NP12
NSSE
3122310203010
123456789123456789666884444453453453
1030302020
453453453333445555333445555333445555333445555999887777999887777987987987987987987987654321987654321888665555
NSS_NÚMSP NSSSNSS
123456789453453453
58© C. P. G. 2007 FBD Tema 3
Otras operaciones relacionales
• Los SGBD introducen operaciones no estándar en el álgebra relacional: funciones agregadas, cierre recursivo, reunión y unión externa.
Funciones agregadas (ℑℑℑℑ))))
<atribs agrupación> ℑℑℑℑ <lista funciones> (R)
Devuelve una relación con los resultados de la listade funciones agrupados por los atributos indicados.
<atribs agrupación>: Uno o varios atributos separados por comas. Son los atributos por los que queremos que aparezca cada resultado.<lista funciones>: Son pares de la forma <función agregada> <atributo> separados por comas.<función agregada>: Las más comunes son:CUENTA : Contabiliza el número de tuplas no nulas del atributo indicado.SUMA: Suma el contenido de las tuplas del atributoindicado. PROMEDIO: Lo mismo para la media. MÁXIMO: Calcula el valor máximo contenido en las tuplas del atributo indicado.MÍNIMO: Lo mismo para el valor mínimo.
F GÓTICA
59© C. P. G. 2007 FBD Tema 3
Otras operaciones relacionales (2)
Ejemplos de Funciones agregadas (ℑℑℑℑ))))
R(ND, NÚM_DE_EMPLEADOS,PROMEDIO_SALARIO) ← ND ℑℑℑℑ CUENTA NSS, PROMEDIO SALARIO(EMPLEADO)
• R2← ND ℑℑℑℑ CUENTA NSS, PROMEDIO SALARIO(EMPLEADO)
• R3← ℑℑℑℑ CUENTA NSS, PROMEDIO SALARIO(EMPLEADO)
ND NÚM_DE_EMPLEADOS PROMEDIO_SALARIO5 4 332504 3 310001 1 55000
R
ND CUENTA_NSS PROMEDIO_SALARIO5 4 332504 3 310001 1 55000
R2
CUENTA_NSS PROMEDIO_SALARIO8 35125
R3
Fig. 7.16. La operaciónFUNCIÓN.
60© C. P. G. 2007 FBD Tema 3
Otras operaciones relacionales (3)
Cierre recursivo• Se aplica a un vínculo recursivo (como SUPERVISA).• Ej: Obtener los supervisados por James Borg a
todos los niveles (directa o indirectamente).
NSS_BORG ←πNSS(σNOMBRE=‘James’ Y APELLIDO=‘Borg’EMPLEADO))
SUPERVISIÓN (NSS1, NSS2) ← πNSS, NSS_SUPERV(EMPLEADO)
Supervisados directamente por Borg:RDO1 (NSS) ← πNSS1(SUPERVISIÓN NSS2=NSSNSS_BORG)
Supervisados por los subordinados directos de Borg:RDO2 (NSS) ← πNSS1(SUPERVISIÓN NSS2=NSSRDO1)
Supervisados en los niveles 1 y 2 por Borg:RDO ← RDO1 ∪ RDO2
NSS1 NSS2
SUPERVISIÓN
333445555123456789
999887777987654321666884444453453453987987987888665555
888665555333445555
987654321888665555333445555333445555987654321
nulo
NSS_BORG888665555
RDO1NSS
987654321333445555
NSS
999887777123456789
666884444453453453987987987
RDO2
61© C. P. G. 2007 FBD Tema 3
Otras operaciones relacionales (4)
Reunión externa izquierda R ]××××| S• Conserva todas las tuplas de R aunque sea rellenando todos los
campos correspondientes a S con valores nulos.
• TEMP ← EMPLEADO ]××××|NSS=NSS_JEFE DEPARTAMENTO
• RESULTADO ← πNOMBRE, INIC, APELLIDO, NOMBRED(TEMP)
Reunión externa derecha R |××××[ SReunión externa completa R ]××××[ S
Fig. 7.18. La operaciónREUNIÓN EXTERNA
IZQUIERDA
...NOMBRED NÚMERODNSS_JEFE
DEPARTAMENTO
InvestigaciónAdministración
Dirección
541
333445555987654321888665555
APELLIDO NSS
EMPLEADO
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
ND
55
445541
......
RESULTADOAPELLIDO NOMBRED
Smith nuloWong InvestigaciónZelaya nulo
Wallace AdministraciónNarayan nuloEnglish nuloJabbar nuloBorg Dirección
NOMBRE INICJon B
Franklin TAlicia J
Jennifer SRamesh KJoyce A
Ahmad VJaime E
62© C. P. G. 2007 FBD Tema 3
Otras operaciones relacionales (5)
Unión externa
• Unión de tuplas parcialmente compatibles.• El resultado conserva los atributos no compatibles.• Las tuplas sin valor para atributos no compatibles
tienen valor nulo.
Ejemplo: Atributos CENTRO y DPTO incompatibles.
NOMBRE
RicardoJohn
Susana
PROFESOR
Ramesh
APELLIDO
BuenoSmith
YáñezSánchez
DPTO
21
13
ALUMNONOMBRE
RameshSusana
JosuéBárbaraAmanda
APELLIDO
SánchezYáñez
LandaJaimesFlores
CENTRO
FDSSFISS
FQSSFISSEPSS
RESULTADONOMBRE
RameshSusana
JosuéBárbaraAmanda
APELLIDO
SánchezYáñez
LandaJaimesFlores
CENTRO
FDSSFISS
FQSSFISSEPSS
DPTO1
nulo3
nulonulo
RicardoJohn
BuenoSmith
21nulo
nulo
63© C. P. G. 2007 FBD Tema 3
Consultas álgebra relacional
C1) Nombre y dirección de los empleados del departamento de investigación.
DEPTO_INV←σNOMBRED=‘Investigación’(DEPARTAMENTO)EMPS_DEPTO_INVEST←DEPTO_INV NUMEROD=ND EMPLEADO
RESULTADO ← πNOMBRE,APELLIDO,DIRECCIÓN (EMPS_DEPTO_INVEST)
¿Con *?
C2) Nº de proyecto, nº de departamento que lo controla, apellido, dirección y fecha de nacimiento del gerente del departamento de todos los proyectos realizados en Stafford.
PROYS_STAFFORD←σLOCALIZACIÓNP=‘Stafford’(PROYECTO)DEPTO_CONTR←
PROYS_STAFFORD NUMD=NÚMEROD DEPARTAMENTO
JEFE_DEPTO_PROY← DEPTO_CONTR NSS_JEFE=NSS EMPLEADO
RESULTADO ← πNÚMEROP,NUMD,APELLIDO,DIRECCIÓN,
FECHA_NCTO (JEFE_DEPTO_PROY)
NOMBRE INICAPELLIDONSS FECHA_NCTO DIRECCIÓN
NSS_SUPERV ND
EMPLEADO...
SEXO SALARIO...NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFEDEPARTAMENTO
PROYECTONOMBREP NÚMEROP LOCALIZACIÓNP NUMD
64© C. P. G. 2007 FBD Tema 3
Consultas álgebra relacional (2)C3) Nombre de los empleados que trabajan en todos los proyectos
del departamento 5.PROYS_DEPTO5(NP)← πNÚMEROP (σNUMD=5 (PROYECTO))EMP_PROY(NSS,NP) ← πNSSE,NP (TRABAJA_EN)RESUL_NSS_EMP← EMP_PROY ÷ PROYS_DEPTO5
RESULTADO ← πAPELLIDO, NOMBRE (RESUL_NSS_EMP * EMPLEADO)
C4) Números de proyecto donde interviene Wong como trabajador o como director del dpto. que lo controla.
WONGS(NSSE)← πNSS (σAPELLIDO=‘Wong’ (EMPLEADO))WONG_TRAB_PROYS← πNP (TRABAJA_EN * WONGS)
JEFES ←πAPELLIDO, NÚMEROD (EMPLEADO NSS=NSS_JEFEDEPARTAMENTO)
DPTOS_DIRIG_WON(NUMD)← πNÚMEROD(σAPELLIDO=‘Wong’ (JEFES))
WONG_JEFE_PROYS(NP) ←πNÚMEROP (DPTOS_DIRIG_WON * PROYECTO)
RESULTADO ← WONG_TRAB_PROYS ∪ WONG_JEFE_PROYS
NOMBRE INICAPELLIDONSS FECHA_NCTO DIRECCIÓN
NSS_SUPERV ND
EMPLEADO...
SEXO SALARIO...NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFEDEPARTAMENTO
PROYECTONOMBREPNÚMEROPLOCALIZACIÓNP NUMD
TRABAJA_ENNSSE NP HORAS
65© C. P. G. 2007 FBD Tema 3
Consultas álgebra relacional (3)
C5) Empleados (apellido, nombre) con 2 o más dependientes.T1(NSS, NUM_DE_DEPS) ←
NSSE ℑℑℑℑ CUENTA NOMBRE_DEPENDIENTE (DEPENDIENTE)T2 ← σNUM_DE_DEPS>=2 (T1)
RESULTADO ← πAPELLIDO, NOMBRE, (T2 * EMPLEADO)
C6) Empleados (apellido, nombre) sin dependientes.TODOS_EMPS ← πNSS(EMPLEADO)EMPS_CON_DEPS(NSS) ← πNSSE(DEPENDIENTE)EMPS_SIN_DEPS ← TODOS_EMPS — EMPS_CON_DEPS
RESULTADO ←πAPELLIDO, NOMBRE(EMPS_SIN_DEPS * EMPLEADO)
C7) Jefes de depto (apellido, nombre) con algún dependienteJEFES(NSS) ← πNSS_JEFE(DEPARTAMENTO)EMPS_CON_DEPS(NSS) ← πNSSE(DEPENDIENTE)JEFES_CON_DEPS←JEFES ∩ EMPS_CON_DEPS
RESULTADO←πAPELLIDO, NOMBRE(JEFES_CON_DEPS * EMPLEADO)
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFEDEPARTAMENTO
DEPENDIENTENSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN SEXO
SALARIO NSS_SUPERV ND
EMPLEADO ......
66© C. P. G. 2007 FBD Tema 3
67© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.19 Analizar todas las R.I. que viola cada operación cuando se ejecuta sobre la BD de la figura 7.6:
a) Insertar <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1952-06-21’, ‘2365 Ave. Naranjos, Bellaire TX’, ‘H’, 58000, ‘888665555’, 1> en EMPLEADO.
b) Insertar <‘ProductoA’, 4,‘Bellaire’, 2> en PROYECTO.c) Insertar <‘Producción’, 4, ‘943775543’, ‘1998-10-01’> en
DEPARTAMENTO.d) Insertar <‘677678989’, nulo, ‘40.0’> en TRABAJA_EN.e) Insertar <‘453453453’, ‘John’, ‘H’, ‘1970-12-12’,
‘ESPOSO’> en DEPENDIENTE.f) Eliminar tuplas de TRABAJA_EN con NSSE=‘333445555’.g) Eliminar la tupla de EMPLEADO con NSS= ‘987654321’.h) Eliminar la tupla de PROYECTO con NOMBREP=‘ProductoX’.i) Modificar NSS_JEFE y FECHA_INIC_JEFE en las tuplas de
DEPARTAMENTO con NÚMEROD=5 por los valores ‘123456789’ y ‘1999-10-01’ respectivamente.
j) Modificar NSS_SUPERV en las tuplas de EMPLEADO con NSS=‘999887777’ por el valor ‘943775543’.
k) Modificar HORAS de la tupla TRABAJA_EN con NSSE=‘999887777’ y NP=10 por el valor ‘5.0’.
DEPENDIENTENSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
M HIJA1986-04-05Alicia333445555H HIJO1983-10-25Theodore333445555M ESPOSA1958-05-03Joy333445555H ESPOSO1942-02-28Abner987654321H HIJO1988-01-04Michael123456789M HIJA1988-12-31Alicia123456789M ESPOSA1967-05-05Elizabeth123456789
Figura 7.6.68© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.19 (2)
Figura 7.6 (cont)
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN
EMPLEADO
FranklinJohn
AliciaJenniferRameshJoyce
AhmadJaime
TB
JSKAVE
WongSmith
ZelayaWallaceNarayanEnglishJabbarBorg
333445555123456789
999887777987654321666884444453453453987987987888665555
1955-12-081965-01-09
1968-07-191941-06-201962-09-151972-07-311969-03-291937-11-10
Valle 638, Houston, TXFresnos 731, Houston, TX
Castillo 3321, Sucre, TXBravo 291, Bellaire, TXEspiga 875, Heras, TX
Rosas 5631, Houston, TXDalias 980, Houston, TXSorgo 450, Houston, TX
...
SEXO
HH
MMHMHH
SALARIO NSS_SUPERV ND
40.00030.000
25.00043.00038.00025.00025.00055.000
888665555333445555
987654321888665555333445555333445555987654321
nulo
55
445541
...
TRABAJA_EN
NSSE NP HORAS
333445555
123456789
999887777
987654321
666884444453453453
987987987
888665555
123456789
453453453
333445555333445555333445555
999887777
987987987
987654321
21
3122310
3020
101030302020
7.532.5
40.020.020.010.010.010.0
30.010.0
10.035.05.020.015.0nulo
LOCALIZACIONES_DEPT
NÚMEROD
14555
LOCALI-ZACIÓNDHoustonStaffordBellaire
SugarlandHouston
PROYECTONOMBREP NÚME-
ROPLOCALI-ZACIÓNP NUMD
ProductoX 1 Bellaire 5ProductoY 2 Sugarland 5ProductoZ 3 Houston 5
Automatización 10 Stafford 4Reorganización 20 Houston 1
Nuevos beneficios 30 Stafford 4
DEPARTAMENTO
NOMBRED
InvestigaciónAdministración
Dirección
NÚME-ROD
541
FECHA-INIC-JEFE
1981-06-191995-01-011988-05-22
NSS_JEFE
333445555987654321888665555
69© C. P. G. 2007 FBD Tema 3
Ejercicio 1: Transformación ER-Relacional
• Transformar a relacional el esquema ER de la figura:
PERTENECE_A DIRIGE
TRABAJA_EN
Nombre
Sexo
PROYECTONombreNúmeroLugar
DEPARTAMENTO
NombreNúmeroLugares
Dir SalarioNSSFecha_
ncto
NomP Paterno Materno
FechaInic
NDeEmpleadosHoras
CONTROLA
N
1
1
1
N
1
N
M
SUPERVISIÓN
1
N
supervisor
supervisado
DEPENDIENTE
DEPENDIENTES_DE
Fecha_ncto
Parentesco
Sexo
1
N
Nombre
EMPLEADO
70© C. P. G. 2007 FBD Tema 3
Ejercicio 2: Transformación ER-Relacional
A
A1 A2
C
E F
D
B
B1
D1
D2
F1E1
C1
C2
R3
R4 R5
R1P
M N
M
NM
N
M NNN
N
11
1
1
1R2
B2B3
71© C. P. G. 2007 FBD Tema 3
Ejercicio 3: Transformación ER-Relacional
A
A1 A2
C
D E
B
B1 B2
E1D2
C1
C2
D1
D11 D12
BDE
M N1
N
M
N
1 111
N
NN
1
A3 B3
D3 E2
P
M
N
AC
BD
AB
CD
72© C. P. G. 2007 FBD Tema 3
Ejercicios: Operaciones del álgebra
Dibujar las relaciones resultantes de realizar las siguientes operaciones del álgebra relacional:
• NSS de los que trabajan más de 1 hora en el proyecto 1:
PRY1_MAS1HORA← σNP=1 Y HORAS>1 (TRAB)R1 ← πNSSE (PRY1_MAS1HORA)
• NP de los proyectos en los que trabaja alguna persona:R2← πNP (TRAB)
• NP de los proyectos en los que NO trabaja ninguna persona:TODOS_PRY ← πNP (PROY)R3← TODOS_PRY - R2
• Información de cada persona asociada a la información de SU departamento:R4 ← EMP ND=NUMD DEP
• Nombre de cada persona junto al nombre del departamento en el que trabaja:R5 ← πNOMBRE, NOMD (R4)
NOMBRE NSS ND
EMP
IkerAnaJon
112233
112
Karmele 44 2
NUMD NOMD
DEP
12
LSIATC
NP NOMBRE
PROY
12
.NETXML
34
EJBUML
NSSE NP HORAS
TRAB
111122
121
523
22 3 144 3 4
73© C. P. G. 2007 FBD Tema 3
Ejercicios: Operaciones del álgebra (2)
NOMBRE NSS ND
EMP
IkerAnaJon
112233
112
Karmele 44 2
NUMD NOMD
DEP
12
LSIATC
NP NOMBRE
PROY
12
.NETXML
34
EJBUML
NSSE NP HORAS
TRAB
111122
121
523
22 3 144 3 4
• Nombre y NSS de cada empleado junto al número de cada proyecto en el que trabaja:
PERS ← πNOMBRE, NSS(EMP)TRB(NSS,NP) ← πNSSE, NP (TRAB)R6← PERS * TRB
• Nombre de cada empleado junto al nombre de cada proyecto en el que trabaja:
PRY(NP,NOMP) ← PROYPERS_PRY ← R6 * PRYR7← πNOMBRE, NOMP(PERS_PRY)
– ¿Qué cambia en PERS_PRY si se usa PROY en lugar de PRY?
74© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.18
Figura 7.7. Restricciones de integridad referencial representadas en el esquema de la base de datos relacional EMPRESA.
NOMBRED NÚMEROD NSS_JEFE FECHA_INIC_JEFE
DEPARTAMENTO
LOCALIZACIONES_DEPT
NÚMEROD LOCALIZACIÓND
PROYECTO
NOMBREP NÚMEROP LOCALIZACIÓNP NUMD
TRABAJA_EN
NSSE NP HORAS
DEPENDIENTE
NSSE NOMBRE_DEPENDIENTE SEXO FECHA_NCTO PARENTESCO
NOMBRE INIC APELLIDO NSS FECHA_NCTO DIRECCIÓN SEXO
SALARIO NSS_SUPERV ND
EMPLEADO...
...
75© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.18 (2)
Sobre el esquema de BD de la figura 7.7:
a) Empleados (nombre, apellido) del departamento 5 que trabajan más de 10 horas/semana en el proyecto ‘ProductoX’.
b) Empleados con un dependiente con su mismo nombre de pila.
c) Empleados cuyo jefe directo es ‘Franklin Wong’.d) Nombre de cada proyecto junto al número total de horas
trabajadas por los empleados en él.e) Nombres y apellidos de todos los empleados que trabajan
en cada uno de los proyectos. Hacerlo de dos maneras:� Nombre del empleado junto al nombre de cada
proyecto en el que trabaja.� Nombres de los empleados que trabajen en todos y
cada uno de los proyectos de la empresa.f) Empleados que no trabajan en ningún proyecto.g) Nombre de cada departamento junto al salario medio de
los empleados asignados al mismo.h) Salario medio de las empleadas de la empresa.i) Nombre y dirección de los empleados que trabajan en
algún proyecto que, por una parte, está situado en Houston y por otra el proyecto pertenece a un departamento que no está situado en Houston.
j) Apellidos de los jefes de departamento sin dependientes.
76© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.20
CÓD_AEROPUERTO NOMBRE CIUDAD ESTADO
AEROPUERTO
NÚMERO LÍNEA
VUELO
DÍAS
NÚM_VUELO
TRAMO_VUELO
NÚM_TRAMO
CÓD_AEROPUERTO_SALE HORA_SALIDA_PROGRAMADA
CÓD_AEROPUERTO_LLEGA HORA_LLEGADA_PROGRAMADA
NÚM_VUELO CÓD_TARIFA
TARIFAS
IMPORTE RESTRICCIONES
NOMBRE_TIPO MAX_ASIENTOS
TIPO_AVIÓN
COMPAÑÍA
NOMBRE_TIPO_AVIÓN
PUEDE_ATERRIZAR
CÓD_AEROPUERTO
ID_AVIÓN
AVIÓN
TOTAL_DE_ASIENTOS TIPO_AVIÓN
NÚM_VUELO
RESERVA_ASIENTOS
NÚM_TRAMO FECHA NÚM_ASIENTO
NOMBRE_CLIENTE TFNO_CLIENTE
Fig. 7.19. Esquema de la base de datos relacional
LÍNEAS AÉREAS
NÚM_VUELO
EJEMPLAR_TRAMO
NÚM_TRAMO
CÓD_AEROPUERTO_SALE HORA_SALIDA
CÓD_AEROPUERTO_LLEGA HORA_LLEGADA
FECHA NÚM_ASIENTOS_DISPONIBLES
ID_AVIÓN
77© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.20 (2)
Sobre el esquema de BD de la figura 7.19:
A) Números de vuelo junto al aeropuerto de salida de su primer tramo y aeropuerto de llegada de su último tramo.
B) Número de vuelo y día, de los vuelos o tramos de vuelo que salen del aeropuerto ‘Houston Intercontinental’ (código ‘IAH’) y llegan al aeropuerto ‘Internacional de los Ángeles’ (código ‘LAX’).
C) Prepara una lista con número de vuelo, código de aeropuerto de salida, horas de salida programadas, y días, de todos los vuelos o tramos de vuelo que salgan de algún aeropuerto de ‘Houston’ y lleguen a algún aeropuerto de ‘Los Ángeles’.
D) Obtén toda la información de tarifas del vuelo ‘CO197’.
E) Obtén el número de asientos disponibles en el vuelo ‘CO197’ del ‘1999-10-09’.
Se supone que cada vuelo está formado por uno o varios tramos, que se numeran en orden ascendente.Ejemplo: El vuelo CO197 consta de 3 tramos (1,2 y 3)
78© C. P. G. 2007 FBD Tema 3
Elmasri & Navathe 7.23
A) Número de copias de la tribu perdida en la sucursal de 'Sharpstown'.
B) Identificador de sucursal y número de copias del libro La tribu Perdida en cada una de las sucursales.
C) Nombres de lectores sin libros en préstamo.D) Título del libro, nombre y dirección del lector de los
préstamos de la sucursal de 'Sharpstown' con fecha de devolución la de hoy.
E) Por cada sucursal: Nombre y total de ejemplares de libro en préstamo.
F) Para los lectores con más de 5 libros en préstamo, obtener su nombre, dirección y nº de libros.
G) Por cada libro escrito total o parcialmente por Stephen King, obtener su título y el nº de copias en la sucursal ‘Central’.
IdLibro Título NombreEditorialLIBRO
Nombre Dirección TeléfonoEDITORIAL
IdLibro IdSucursal NúmCopiasCOPIAS_LIBRO
IdLibro NombreAutorAUTORES_LIBROS
IdLibro IdSucursal NúmTarjetaPRÉSTAMOS
FechaSale FechaDevol
IdSucursal NombreSucursal DirecciónSUCURSAL
NúmTarjeta Nombre DirecciónLECTOR
Teléfono
Consultas sobre la BD BIBLIOTECA
79© C. P. G. 2007 FBD Tema 3
Consultas sobre la BD BUQUES
1. Nombres de los buques que hayan visitado puertos de todos los mares.
2. Nombres de aquellos buques que, en el mismo día, hayan salido de un puerto y llegado a otro puerto situado en un mar distinto al del puerto de partida, junto al nombre de los mares visitados.
3. Número de países visitados por cada buque que haya visitado algún puerto.
4. Nombres de aquellos buques que hayan ido en menos de 5 días desde el puerto de Santander al de Cádiz, habiendo hecho escala en el puerto de Vigo.
5. Nombre y país de los puertos que hayan sido visitados por todos los buques.
6. Nombre y país de aquellos puertos que hayan sido visitados por buques de todos los tipos.
7. Información de todas las visitas de buques con puerto de origen Detroit junto a la información de su visita anterior al mismo puerto de esa visita.
8. Número de veces que han ido a Cádiz cada uno de los buques cuyo tonelaje es > 1000.
80© C. P. G. 2007 FBD Tema 3
EJERCICIOS DE MODELADO (TEMAS 2 Y 3)
Para cada uno de los siguientes enunciados se pide:� Diseño conceptual de la BD utilizando el modelo E-R� Realizar el paso del modelo E-R al modelo relacional, obteniendo el
esquema relacional.
1.- La cadena de videoclubs Glob_Gusters ha decidido, para mejorar su servicio, emplear una BD para almacenar la información referente a las películas que ofrece en alquiler. Esta información es la siguiente:
a. Una película se caracteriza por su título, nacionalidad,productora y fecha (Por ejemplo, Quo Vadis, Estados Unidos, M.G. M., 1995).
b. En una película pueden participar varios actores (nombre, nacionalidad, sexo), algunos de ellos como actores principales.
c. Una película está dirigida por un director (nombre, nacionalidad).d. De cada película se dispone de uno o varios ejemplares diferenciados por
un número de ejemplar y caracterizados por su estado de conservación.e. Un ejemplar se puede encontrar alquilado a algún cliente (nombre,
dirección, teléfono). Se desea almacenar la fecha de comienzo del alquiler y la de devolución.
2.- La asociación “Amigos de la Fiesta” desea recoger en una BD toda la información acerca de las corridas de toros que se celebran en España y de todos los datos relacionados con ellas.
a. Se desea tener información acerca de cada corrida, identificada conjuntamente por un número de orden, la feria en la que se celebra y el año de celebración (por ejemplo: orden=2, feria=San Isidro, año=1990).
b. En una determinada corrida actúan una serie de toreros (mínimo 1 y máximo 3) de los que se desea guardar su DNI, nombre, apodo y fecha en que tomó la alternativa (fecha en la que se convirtió en matador de toros). Además se desea saber quién fue el torero que le dio la alternativa (padrino) en su día (un torero puede dar la alternativa a varioscompañeros o a ninguno).
c. En cada corrida un torero obtiene una serie de premios (cuántas orejas, cuántos rabos y si salió por la puerta grande o no) de los que se desea mantener información.
81© C. P. G. 2007 FBD Tema 3
EJERCICIOS DE MODELADO (TEMAS 2 Y 3)d. Cada torero puede tener un apoderado del que es protegido. A su vez, un
apoderado lo puede ser de varios toreros. De él se desea saber su DNI, nombre, dirección y teléfono.
e. Una corrida se celebra en una plaza de toros de la que se desea saber su nombre que se supone único, localidad, dirección y aforo. En una misma plaza se pueden celebrar varias corridas de toros.
f. En cada corrida son estoqueados al menos 6 toros. Cada toro viene identificado con el código de la ganadería a la que pertenece, el año en que nació y un número de orden. Además, se desea mantener información acerca de su nombre y color así como el orden en que fue toreado.
g. Cada toro pertenece a una ganadería determinada. De cada ganadería se pretende saber su código, nombre, localidad y antigüedad (fecha de creación).
3.- El ministerio de Educación y Ciencia desea mantener información acerca de todos los cuadros que se encuentran en las pinacotecas españolas y toda la información relacionada con ellos.
a. De cada pinacoteca se desea saber el nombre ( que se supone único), la ciudad en que se encuentra, la dirección y los metros cuadrados que tiene.
b. Cada pinacoteca tiene una serie de cuadros de los que se quiere mantener información acerca de su código, nombre, dimensiones, fecha en que fue pintado y técnica utilizada.
c. Cada cuadro es pintado por un determinado pintor (nombre, país, ciudad, fecha de nacimiento y fecha de defunción). Un pintor puede tener a otro como maestro; a su vez, un maestro puede serlo de varios (o de ninguno).
d. Los pintores pueden pertenecer o no a una escuela de la que se desea saber su nombre y en qué país y qué fecha apareció.
e. Los pintores pueden tener también uno o varios mecenas que les protegen (nombre, fecha, país y ciudad de nacimiento y fecha de muerte). A su vez, un mismo mecenas puede serlo de varios pintores. Se desea saber cuál es la relación que existe entre un pintor y su mecenas.