![Page 1: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/1.jpg)
DL - 1
Tema 4. DISEÑO LÓGICO
Objetivos
•Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular
•Conocer las reglas de transformación de un esquema conceptual en el MERE en un esquema lógico en el MR
•Conocer cómo evitar la posible pérdida de semántica al traducir elementos del MERE a elementos del MR
•Conocer estrategias de elección de la opción de diseño lógico más adecuada entre varias alternativas posibles
•Conocer guías y recomendaciones para trasladar un esquema en el MR a un esquema en el modelo de datos específico soportado por el SGBD de implementación
![Page 2: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/2.jpg)
DL - 2
DISEÑO LÓGICO..a grandes rasgos
Transformación
Esquema Conceptual Esquema Lógico de Datos
• El objetivo del diseño lógico es convertir los esquemas conceptuales en un esquema lógico que se ajuste al modelo de SGBD sobre el que se vaya a implementar el sistema.
• Ya que aquí se trata el diseño de bases de datos relacionales, en esta etapa se obtiene un conjunto de relaciones (tablas) que representen los datos de interés. – Este conjunto de relaciones se valida mediante la normalización,
técnica que se estudia en el próximo tema.
![Page 3: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/3.jpg)
DL - 3
DISEÑO LÓGICO
• Mientras que el objetivo fundamental del diseño conceptual es completitud y la expresividad de los esquemas conceptuales,
• el objetivo del diseño lógico es obtener una representación que use, del modo más eficiente posible, los recursos que el modelo de SGBD posee para estructurar los datos y para modelar las restricciones.
![Page 4: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/4.jpg)
DL - 4
DISEÑO LÓGICO
Transformación
Esquema Conceptual Esquema Lógico de Datos
• Objetivos del Diseño lógico: – Eliminar redundancias, conseguir máxima simplicidad, evitar
cargas suplementarias de programación, ...– Conseguir una estructura lógica adecuada, un equilibrio entre
requisitos de usuario y eficiencia de implementación, ...
• PORTABILIDAD– Introducción de exigencias del SGBD específico lo más tarde
posible.• Implementación del diseño lógico sobre diferentes SGBD• Migración entre versiones de un mismo SGBD
![Page 5: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/5.jpg)
DL - 5
ETAPAS del DISEÑO LÓGICO
Diseño Lógico Estándar (DLS)– Se elige el modelo de datos de representación, no el SGBD– Transformación independiente del SGBD específico y otras
consideraciones físicas– Esquema Conceptual Esquema Lógico Estándar (ELS)– Uso de un Modelo Lógico de datos eStándar (MLS)
•Relacional, Red, Jerárquico, Orientado a Objetos
– Se elige el MODELO DE DATOS, no el SGBD concreto– ELS descrito mediante lenguaje estándar del modelo de datos
• SQL-92 en el Modelo Relacional• Diagrama de Estructura de Datos
![Page 6: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/6.jpg)
DL - 6
ETAPAS del DISEÑO LÓGICO
Diseño Lógico Específico (DLE)– Se elige el SGBD específico– Adaptación del Esquema de la BD a un SGBD concreto
(comercial) – Esquema Lógico Estándar Esquema Lógico
Específico (ELE)– Uso del Modelo Lógico propio del SGBD elegido
• Informix, Oracle, DB2, Interbase,...
– ELE descrito mediante lenguaje DDL del SGBD específico
![Page 7: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/7.jpg)
DL - 7
DISEÑO LÓGICO ESTÁNDAR (DLS)
• Reglas para el modelo básico– Dominios– Atributos– Tipos de entidad Relación– Tipos de relación
• N:M Relación• 1:1;1:N;N:1 ¿relación o propagación?
• Reglas para extensiones del modelo– Relaciones exclusivas– Jerarquías de G/E
![Page 8: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/8.jpg)
DL - 8
DISEÑO LÓGICO ESTÁNDAR (DLS)
Pérdida de semántica !!
¿de donde proviene una relación?desaparición de relaciones
(0,n)(0,m)
Cod_libro
Cod_Autor
EDITORIAL
LIBRO escribe
edita
AUTOR
Cod_Editorial
(1,1)
(0,n)
1
N
NM
AUTOR(cod_autor, … )
LIBRO(cod_libro, …, cod_editorial,… )
EDITORIAL(cod_editorial, … )
ESCRIBE(cod_autor, cod_libro, … )
Esquema relacional:
FK FK
FK
Solución: Anotarlo en la documentación; reglas de integridad
![Page 9: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/9.jpg)
DL - 9
DLS: Dominios
Transformación directa: El modelo relacional admite dominios aunque no disponibles en la mayor parte de las implementaciones comerciales.
![Page 10: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/10.jpg)
DL - 10
DLS: Entidades regulares
• Cada atributo simple Atributo de R• Identificador principal Clave primaria de R (cláusula PRIMARY KEY)• Identificador alternativo Clave alterna de R (cláusula UNIQUE + NOT NULL )
![Page 11: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/11.jpg)
DL - 11
DLS: Atributos compuestos
A) “Eliminar” atributo compuesto y considerar todos sus componentes como atributos simples
B) “Eliminar” los componentes y considerar el atributo compuesto como un único atributo
PERSONA
fechaNac
dni
dirección
nombre
calle
ciudad
provincia
dni
PERSONA
fechaNacnombre
calle
ciudad
provincia
PERSONA
fechaNac
dni
dirección
nombre
![Page 12: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/12.jpg)
DL - 12
DLS: Atributos multivaluados de entidades
Atributo Multivaluado de E• Nueva Relación S, en la que el atributo multivaluado se
representa como un atributo simple A• S contendrá, un atributo F, clave ajena a la clave primaria de
R• Clave Primaria de S = (F,A) | A
PERSONA
fechaNac
dni
dirección (1,n)
nombre
PERSONA(dni, nombre, fechaNac)
DIRECCION_PERSONA(dni, dirección)
FK
PERSONA
fechaNac
dninombre
DIRECCIONPERSONA
dni dirección
¡¡ cardinalidades max y min !!
![Page 13: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/13.jpg)
DL - 13
DLS: Atributos derivados
• Es necesario decidir si se almacena o no
• Si se almacena, será un atributo de la relación que corresponda y deberá crearse un disparador que calcule su valor y lo mantenga actualizado
• Si no se almacena, deberá crearse un procedimiento que calcule su valor cada vez que se solicite
![Page 14: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/14.jpg)
DL - 14
DLS: Interrelaciones Binarias 1:1
(a) Participación TOTAL de ambos tipos de entidad
– ÚNICA RELACIÓN R. Cuando...• Los tipos de entidad NO participan en otros tipos de
interrelación
– Propagación de claves en una u otra dirección (indiferente)• Clave Primaria de R = clave primaria de R1 o de R2 (*si son distintas*)• La otra será clave alternativa (NOT NULL UNIQUE)
– Atributos simples de IR o componentes simples de atributos compuestos, también se incluyen como atributos de la relación R
E1 E2I R
calle
(1,1) (1,1) numCasa
nomCli
codCli
codCli
cp
ENVIOINFORMACIONCLIENTE
CLIENTE( codCli, nomCli, numCasa, calle, cp, ...)
R1 R2
![Page 15: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/15.jpg)
DL - 15
DLS: Interrelaciones 1:1 (2)
(b) Una entidad con participación TOTAL y otra con PARCIAL
(b.1) PROPAGACIÓN DE CLAVE. Cuando...
– La clave de la entidad con participación parcial “se propaga” hacia la entidad con participación total
(0,1)(1,1)DEPARTAMENTOEMPLEADO
nomEmp nomDep
numDep
DIRIGE
EMPLEADO(codEmp, nomEmp, ...)
DEPARTAMENTO(numDep, nomDep, codDirector, fechaInicDir...)
FK (NOT NULL, UNIQUE)
Un empleado de una empresa puede ser el gerente de un (único) departamento (desde cierta fecha, en la que fue nombrado como tal), o bien no dirigir ninguno.
fechaInic
codEmp
![Page 16: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/16.jpg)
DL - 16
DLS:Interrelaciones 1:1 (3)
(b) Una entidad con participación TOTAL y otra con participación PARCIAL
(b.2) NUEVA RELACIÓN R. Cuando... • Hay pocas instancias del tipo de Interrelación IR
– Atributos de R:• claves primarias de R1 y de R2
– son claves ajenas (a la clave primaria de R1 y de R2, respectivamente)– son claves candidatas en R
» uno de ellos será la Clave Primaria de R (la de participación total, si existe)
» el otro será Clave Alternativa de R (NOT NULL, UNIQUE)• atributos simples (o componentes simples de atributos compuestos) de IR
– Evita NULOS en los atributos propagados
EMPLEADO(codEmp, nomEmp, ...)
DIRIGE(codEmp, numDep, fechaInic)
DEPARTAMENTO(numDep, nomDep,...)
FK
FK
Participación ¿Total?
![Page 17: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/17.jpg)
DL - 17
DLS: Interrelaciones 1:1 (4)
(b) Una entidad con participación TOTAL y otra con participación PARCIAL
(b.3) Muchas instancias del tipo de relación: ÚNICA RELACIÓN. – Atributos: todos (los de los tipos entidad e interrelación)– Clave Primaria: la de la entidad con participación PARCIAL
(EMPLEADO)– Debe permitirse NULOS en los atributos propagados (empleados NO
directores)
• desde la entidad con participación TOTAL y desde la interrelación
CREATE TABLE EMPLEADO (codEmp códigos PRIMARY KEY, nomEmp nombres,...,numDepDir códigos UNIQUE,NULLnomDepDir nombres, NULL...,fechaInicDir fechas, NULL...)
![Page 18: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/18.jpg)
DL - 18
DLS: Interrelaciones 1:1 (y 5)
(c) Ambos tipos entidad con participación PARCIAL NUEVA RELACIÓN R. – R se construye exactamente igual que en el caso (b.2)– Evita los valores nulos que aparecerían si se propagara la clave
de R1 a R2 o viceversa (caso (b.1))
(0,1) (0,1)MUJERHOMBRE
nif nif
MATRIMONIO
HOMBRE(nif, ...)
MATRIMONIO(nifEsposa, nifEsposo, fecha, lugar)
MUJER(nif, ...)
fecha
FK (NOT NULL UNIQUE)
FK
lugar
![Page 19: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/19.jpg)
DL - 19
DLS: Interrelaciones 1:N
Interrelaciones Binarias 1:N
(a) PROPAGACIÓN DE CLAVE
– En R2 se incluyen nuevos atributos para contener valores de...• clave primaria de R1
– Clave ajena en R2 hacia R1 (ojo con acciones disparadas por Integridad Referencial)• atributos simples (o componentes simples de atributos compuestos) de IR
(a.1) Card(E2)=(1,1) -- Participación TOTAL u obligatoria de E2 en IR
E1 E2IR
R1 R2
PROVINCIA CIUDAD(0,n)(1,1)
nomProv
codProvnombreCiudad
ESTA_EN
FK: NULOS NO PERMITIDOS
1 N
1 N
PROVINCIA(codProv, nomProv, ...) CIUDAD(nomCiudad, codProv, ...)
![Page 20: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/20.jpg)
DL - 20
DLS: Interrelaciones 1:N (2)
(a.2) Card(E2)=(0,1) -- Participación PARCIAL u opcional de E2 en IR
PINACOTECA CUADRO(1,n)(0,1)
nomMuseo
codCuadro
CUADRO(codCuadro, titulo, pintor, nomMuseo, sala...)
PINACOTECA(nomMuseo, ciudad, ...)
EXPONE
FK
titulo
pintorciudad
sala
NULOS PERMITIDOS
1 N
![Page 21: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/21.jpg)
DL - 21
DLS:Interrelaciones 1:N (y 3)
(b) NUEVA RELACIÓN R. Cuando...• Aparecen demasiados NULOS en la clave propagada (pocas ocurrencias
del tipo interrelación), o
• IR tiene varios atributos propios, o
• IR puede transformarse en un futuro en un tipo interrelación N:M
– R se construye exactamente igual que para interrelaciones 1:1 (caso b.2) Clave primaria: atributo procedente de la entidad con cardinalidad N E2
ESTUDIANTE COCHE(0,1) (0,n)
nif matricula
ESTUDIANTE(nif, nombre, ...)
COCHE_DE_ESTUDIANTE(nifEstudiante, matricula)
COCHE(matricula, modelo, ...)
PROPIETARIO_DE
FK
modelonombre
FK
1 N
![Page 22: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/22.jpg)
DL - 22
DLS:Interrelaciones N:M
Interrelaciones Binarias N:M– Nueva relación R cuyos atributos son:
• uno(s) por cada clave primaria de R1 y R2– Son claves ajenas a la clave primaria de R1 y R2, respectivamente– Su combinación (concatenación) forma la clave primaria de R
• atributos simples (o componentes simples de atributos compuestos) del tipo interrelación
E1 E2IR
R1 R2
AUTOR LIBRO
fechaFin
(0,n)(1,4) titulo
nomAutor
codAutor
isbn
derechosAutor
AUTOR(codAutor, nomAutor, ...)
ESCRIBE(codAutor, isbn, fechaFin, derechosAutor)
LIBRO(isbn, titulo, ...)
ESCRIBE
FK
FK
![Page 23: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/23.jpg)
DL - 23
DLS: InterRelaciones M:N
– Especificación de las acciones disparadas por Integridad Referencial
CREATE TABLE ESCRIBE(codAutor Autores, codLibro Codigos
CONSTRAINT max_autores_libroCHECK (NOT EXISTS (SELECT codLibro FROM ESCRIBE
GROUP BY codLibro HAVING COUNT(*)>4),
fechaFin DATE NOT NULL, derecAutor NUMBER(2) DEFAULT 20,
PRIMARY KEY (codAutor, codLibro),
FOREIGN KEY(codAutor) REFERENCES AUTOR(codAutor)ON DELETE NO ACTIONON UPDATE CASCADE,
FOREIGN KEY(codLibro) REFERENCES LIBRO(isbn)ON DELETE CASCADEON UPDATE CASCADE );
![Page 24: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/24.jpg)
DL - 24
DLS: Cardinalidades
– Especificación de Restricciones: CARDINALIDADES MÍNIMA y MÁXIMA
CREATE ASSERTION num_autores_libro CHECK((4>=(SELECT MAX(ocurrencias)
FROM (SELECT COUNT(*) AS ocurrenciasFROM ESCRIBEGROUP BY codLibro))
AND((1<=(SELECT MIN(ocurrencias)
FROM (SELECT COUNT(*) AS ocurrenciasFROM ESCRIBEGROUP BY codLibro));
SET CONSTRAINTS {ALL | nombre_constraint`[,...]} {DEFERRED | INMEDIATE}
INITIALLY {DEFERRED | INMEDIATE}
![Page 25: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/25.jpg)
DL - 25
DLS: Dependencia Existencia / Identificación
Dependencia en existencia e identificación (E2 depende de E1)
– Caso particular de IR 1:1 o 1:N con propagación de clave y participación total de E2
• clave ajena F de R2 hacia R1 (atributo(s) propagado(s) de R1 a R2) – no permite NULL
• clave primaria de R2:– DEPENDENCIA EN EXISTENCIA
» atributo(s) clave primaria de R2 (identificador principal de E2)
– DEPENDENCIA EN IDENTIFICACIÓN» combinación de atributos: F y clave parcial (discriminante) de
R2
– Actualizaciones y Borrados en R1 se transmiten en CASCADA hacia R2
![Page 26: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/26.jpg)
DL - 26
DLS: Dependencia Existencia / Identificación (2)
FAMILIAREMPLEADO
1 N
(1,1) (0,n)
EMPLEADO ( nifEmp, nomEmp, ...)
FAMILIAR ( nifFam, nifEmp, ... )
FK nulos no permitidos: NOT NULLON DELETE CASCADEON UPDATE CASCADE
nifFamnifEmpnomEmp
CREATE TABLE FAMILIAR
( nifFam nifs PRIMARY KEY,
nifEmp nifs NOT NULL,
FOREIGN KEY (nifEmp) REFERENCES empleado(nifEmp)
ON DELETE CASCADE
ON UPDATE CASCADE );
TIENEE
![Page 27: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/27.jpg)
DL - 27
DLS: Dependencia Existencia / Identificación (3)
VISITA_MEDICAPACIENTE
1 N
(1,1) (1,n)
PACIENTE ( historial, nombre, ...)
VISITA_MEDICA ( historial, fecha, hora, ... )
FKnulos no permitidosON DELETE CASCADEON UPDATE CASCADE
fechahistorialnombre hora
observaciones
CREATE TABLE visita_médica
( historial códigos REFERENCES paciente ON DELETE CASCADE
ON UPDATE CASCADE ,
fecha fechas,
hora horas,
observaciones VARCHAR(100),
PRIMARY KEY (historial, fecha, hora) );
RECIBEID
![Page 28: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/28.jpg)
DL - 28
DLS: Atributo multivaluado en IR
Atributo Multivaluado de tipos interrelación IR• Nueva Relación S, en la que el atributo multivaluado se
representa como un atributo simple A
E1 E2IR
R1 R2R
m (0,n)
S
![Page 29: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/29.jpg)
DL - 29
DLS: Reglas para el Modelo Básico
Atributo Multivaluado de tipos interrelación IR (cont.)
Según IR sea...– 1:1
• S incluye un atributo F, clave ajena a la clave primaria de R1 o de R2
• Clave Primaria de S = (F, A)– 1:N ( E2 es el tipo entidad con cardinalidad N )
• S incluye un atributo F, clave ajena a la clave primaria de R2• Clave Primaria de S = (F, A)
– N:M• S incluye dos atributos F1 y F2, clave ajena a las clave
primaria de IR • Clave Primaria de S = (F1, F2, A)
![Page 30: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/30.jpg)
DL - 30
PROFESOR(nifProf, ...)
OFERTA(nifProf, numSeminario, maxNumAlumnos)
SEMINARIO(numSeminario,...)
SEMINARIO_OFERTADO(nifProfesor, numSemin, trimestre)
DLS:Atributos Multivaluados en IR
+PROFESOR SEMINARIO SEMINARIO OFERTADO
maxNumAlumnos
numSemin
nifProfesor
trimestre OFERTA
nifProf
numSeminario
trimestre (1,3)
PROFESOR SEMINARIO(0,n)(1,m)
maxNumAlumnosnifProf
OFERTA
numSeminario
Caso N:M
FK
FK
FK
R1
R
R2
SAF2F1
![Page 31: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/31.jpg)
DL - 31
DLS: Interrelaciones Reflexivas
EMPLEADO JEFE DE
subordinado
jefe
( Solución problemática si puede haber muchos empleados sin jefe demasiados nulos )
nifEmpnomEmp
EMPLEADO (nifEmp, nomEmp, ...)
JEFE_DE(nifJefe, nifSubordinado, ...)
Caso N:M EMPLEADO ( nifEmp, nomEmp, ...)
JEFE_DE ( nifJefe, nifSubordinado, ... )
Caso 1:N
EMPLEADO ( nifEmp, nomEmp, ..., nifJefe, ... )
• Relación donde la clave primaria del tipo de entidad aparece DOS VECES
• Nombres de esos atributos según roles del tipo entidad en la interrelación
![Page 32: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/32.jpg)
DL - 32
PRODUCTO(codigo, descripcion, ...)
COMPONENTE(codAgregado, codComponente) --- un producto es componente de un único producto, o de ninguno
FK
PRODUCTO
(0,n)
(0,1)
descripcion
codigo
COMPUESTO_POR
componente
agregado
N
1
FK
PRODUCTO(codigo, descripcion, codProducto,...)FK: nulos permitidos
(Al Producto Agregado o Compuesto)
Producto o Componente
DLS: Interrelaciones Reflexivas (cont.)
![Page 33: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/33.jpg)
DL - 33
DLS: Interrelaciones n-arias
– Relación R que incluye los atributos...• Uno por cada clave primaria de R1, R2, R3...
– Serán claves ajenas a la relación Ri correspondiente
• Atributos simples o componentes simples de atributos compuestos de IR
– Clave primaria de R
• Normalmente, es la combinación de todas las claves externas hacia Ri
• pero es posible que la PK de R sea un subconjunto de esa superclave
E1 E2IR
R1 R2E3
R3
![Page 34: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/34.jpg)
DL - 34
DLS: Interrelaciones N-arias
CLIENTE VENDEDOR(0,n) (0,n)
nifCliente
nifVendedor
VENTA
BANCO cifBanco
COCHE
matricula
(0,1)
(0,n)
fechaVenta
VENTA (matricula, nifVendedor, nifCliente, cifBanco, fechaVenta, ...)
*¿Cuál es la superclave de esta relación? concatenación**¿y cuál es su clave primaria? matricula***¿Cómo asegurar que no haya ventas sin cliente o sin coche o sin vendedor? no nulos****¿Puede reflejarse la existencia de ventas directas (sin banco)? no poniendo no nulo en banco
![Page 35: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/35.jpg)
DL - 35
Relaciones exclusivas (1)• Caso 1:N: Curso organizado O impartido por profesor
CREATE TABLE Curso {
cod_curso PRIMARY KEY
Nom_curso
…………
Director ..REFERENCES Profesor(idProf) ON UPDATE CASCADE
Profesor..REFERENCES Profesor(idProf) ON UPDATE CASCADE
….
CONSTRAINT organiza_xor_imparte
CHECK (( director NOT IN (SELECT profesor FROM CURSO)
AND (profesor NOT IN (SELECT director FROM CURSO))
};
![Page 36: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/36.jpg)
DL - 36
Relaciones exclusivas (2)• Caso N:M: Alumno estudia titulaciones o cursa masters
CREATE TABLE Alumno_estudia_titulacion {
…………
Alu ..REFERENCES Alumno(numExp) ON DELETE / UPDATE CASCADE
titu..REFERENCES Titulacion(idTit) ON UPDATE CASCADE
….
PRIMARY_KEY(alu, titu),
CONSTRAINT titulacion_xor_master
CHECK (( alu NOT IN (SELECT alu FROM alumno_cursa_master)
};
Similar para la tabla Alumno_cursa_master
![Page 37: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/37.jpg)
DL - 37
Relaciones exclusivas (3)• Caso 1:1: Empleado jefe de departamento o director de sucursal
CREATE TABLE Departamento {
codDep ….PRIMARY KEY
….
jefe ..REFERENCES Empleado(codEmp) ON UPDATE CASCADE
CONSTRAINT jefe_ok
CHECK (( jefe NOT IN (SELECT director FROM Sucursal)
};
Similar para la tabla Sucursal
![Page 38: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/38.jpg)
DL - 38
DLS: Jerarquías
Jerarquías de Especialización/Generalización
(a) TRANSFORMACIÓN DIRIGIDA POR EL SUPERTIPO• Los subtipos se diferencian en pocos atributos• Interrelaciones establecidas con el supertipo o
son las mismas para todos los subtipos
– Se crea una única relación R que contiene...• TODOS los atributos del supertipo P y de los subtipos S1 y S2• un atributo nuevo -- atributo discriminante d de la jerarquía• (posibles) nuevas restricciones semánticas
– La clave primaria de R es el atributo correspondiente al AIP del supertipo
P
S2S1
d
![Page 39: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/39.jpg)
DL - 39
DLS: Jerarquías (2)
DOCUMENTO codigo
LIBROARTÍCULO
titulo
idioma
añoEdicion nomEditorial
tipo
CREATE TABLE DOCUMENTO(codigo ... PRIMARY KEY,titulo... ,idioma ... ,tipo ... ,nomEditorial ... NULL,añoEdicion ... NULL,...CHECK (( tipo = “ARTICULO” AND
añoEdicion IS NULL ANDnomEditorial IS NULL)
OR ( tipo = “LIBRO” ANDañoEdicion IS NOT NULL ANDnomEditorial IS NOT NULL))
);
AtributoDISCRIMINANTE
RestriccionesSEMÁNTICAS
![Page 40: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/40.jpg)
DL - 40
DLS:Jerarquías (3)
– Si la jerarquía es TOTAL, el discriminante no permite NULOS
– Si la jerarquía es SOLAPADA,• Tratar el discriminante como un ATRIBUTO MULTIVALUADO, o
• Añadir un atributo (booleano) por cada subtipo (indica si o al subtipo)
Ventajas e Inconvenientes
Acceso eficiente a TODA la información sobre una entidad concreta (acceso a una sola relación)
*Aparición de nulos (atributos que proceden de subtipos para entidades que no pertenecen a tales subtipos)
* Toda operación sobre subtipos debe “buscar” las instancias de los subtipos en el conjunto completo (supertipo) de instancias
![Page 41: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/41.jpg)
DL - 41
DLS: Jerarquías (4)
(b) TRANSFORMACIÓN “TOTAL”• Los subtipos se diferencian en muchos atributos• Se desea mantener los atributos comunes en
una relación separada
– una relación R para el supertipo P • incluye atributos de P• la clave primaria de R es el atributo correspondiente al AIP del supertipo
– una relación Ri para cada sutipo Si • contiene atributos del subtipo Si y un atributo clave ajena hacia la clave primaria de R
• La clave primaria de cada Si es el atributo clave ajena a la clave primaria de R
*Funciona para todo tipo de jerarquías. Y es la mejor desde el punto de vista semántico. *Conviene si operaciones estrictamente locales a subtipos o a supertipo (pocas operaciones acceden conjuntamente a atributos de subtipos y supertipo)
Menos eficiente en el acceso
P
S2S1
d
![Page 42: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/42.jpg)
DL - 42
DLS: Jerarquías ( y 5)
(c) TRANSFORMACIÓN DIRIGIDA POR LOS SUBTIPOS• Existen muchos atributos NO comunes (en los subtipos)• Existen pocos atributos comunes (en el supertipo)• Los accesos a datos de subtipos siempre afectan a
datos comunes
– Se crea una relación Ri para cada sutipo Si • contiene atributos del subtipo Si y• atributos comunes (del supertipo)
– La clave primaria de cada Si es el atributo del AIP del supertipo
*Funciona bien para jerarquías totales y disjuntas
*Conviene si el concepto representado por el supertipo no se requiere en el diseño lógico
*Con jerarquías solapadas aparecen “repeticiones” *Con jerarquías parciales surgen problemas de “falta de representación” de entidades no pertenecientes a ningún subtipo.
P
S2S1
d
![Page 43: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/43.jpg)
DL - 43
nombre
Categorías
COCHE
VEHICULO MATRICULADOmatrículaPROPIETARIO
FechaCompra
tiene
U
CAMIÓN
DNI nombre
BANCOPERSONA EMPRESA
NºVehículoNºVehículo
U
PERSONA ( DNI,…,IdPropietario)
BANCO ( Nombre,…,IdPropietario)
EMPRESA ( Nombre,…,IdPropietario)
PROPIETARIO (IdPropietario, TipoPropietario)
Clave sustituta
COCHE ( Nºvehículo,…)
CAMIÓN ( Nºvehículo,…)
VEHÍCULO MATRIC ( Nºvehículo, matrícula …)
![Page 44: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/44.jpg)
DL - 44
DISEÑO LÓGICO ESPECÍFICO (DLE)
Del Esquema Lógico Estándar al Esquema Lógico Específico (ELE)
– Conocimiento del SGBD ¿soporta el MLS?¿hasta qué punto?¿cómo escribir el ELE con la sintaxis propia del SGBD?
– Estudio de la correspondencia entre conceptos del MLS y del SGBDPueden darse dos casos:
1. SGBD con soporte total del MLS sin restricciones• Transformación (casi) directa al SQL propio del SGBD
2. SGBD no soporta algunos conceptos, o sí lo hace pero con restricciones
• Uso de conceptos distintos alternativos• Programación complementaria
• La mayor parte del ELS sirve como ELE, así que sólo veremos los aspectos que necesitan transformaciones adicionales
![Page 45: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/45.jpg)
DL - 45
DLE: transformaciones adicionales
Dominios
• Algunos productos comerciales sólo ofrecen sintaxis de definición de dominios, pero no implementan la semántica asociada– Según Codd (1990)
• Declaración única de cada tipo de datos permitido en el esquema,• Soporte de integridad y coherencia entre dominios (operaciones compatibles como la UNION,
INTERSECCION, ...),• Posibilidad de creación de operadores y características propias de los dominios,• Facilitar la definición de comprobaciones del SGBD (menor/mayor que),• Posible indexación sobre el dominio, no sobre las columnas de las tablas,• Simplificar operaciones complejas sobre varias columnas, haciendola sobre el directamente
sobre el dominio
• La mayoría NO ofrece ningún soporte para definición de dominios– Definir tipo de datos, longitud, restricciones para cada atributo (columna)– Simulación:
• Tablas de dominio y • Procedimientos de comprobación de valores correctos
![Page 46: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/46.jpg)
DL - 46
DLE: transformaciones adicionales
Claves Primarias
• Si el SGBD no dispone de sintaxis para definición de PK o sólo ofrece la sintaxis para hacerlo, pero no implementa su semántica (como Oracle6)...
– Especificar cada atributo componente de la PK como NOT NULL– Especificar que la combinación de todos los componentes de la PK
ha de tener valores únicos (y asegurar esto tras inserciones y actualizaciones)
– Mantener la definición de cada clave primaria como comentario en el catálogo del SGBD o, si éste lo soporta, incluir la definición sintáctica
*Nota: en SQL2 no es obligatorio especificar la PK de una relación, en los productos comerciales tampoco (por compatibilidad con
versiones anteriores)
![Page 47: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/47.jpg)
DL - 47
DLE: transformaciones adicionales
Claves Ajenas
• Unos productos soportan este concepto (a partir de Oracle7)• Algunos lo hacen a nivel sintáctico, pero no implementan la
semántica asociada (Oracle6)• Otros permiten crear un procedimiento (almacenado en el
catálogo) que implementa cada clave ajena
• El mecanismo de Integridad Referencial penaliza los tiempos de respuesta del sistema (a consultas interactivas, sobre todo)– Borrados/actualizaciones en cascada
![Page 48: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/48.jpg)
DL - 48
DLE: transformaciones adicionales
Claves Ajenas (y 2)
• Algunos productos NO soportan este concepto, entonces...– Introducir las restricciones de clave ajena FK como requisitos
de especificación de programas– Especificar como NOT NULL los atributos de FK con nulos no
permitidos
– Mantener la definición de cada clave ajena como comentario en el catálogo del SGBD o, si éste lo soporta, incluir su definición sintáctica
– Utilizar mecanismos de seguridad (GRANT, REVOQUE) para prohibir operaciones de actualización interactivas que pueden violar RI referencial
– Crear un procedimiento que periódicamente compruebe y notifique posibles violaciones de la Integridad Referencial
![Page 49: DL - 1 Tema 4. DISEÑO LÓGICO Objetivos Comprender la conveniencia y ventajas de disponer de un esquema lógico de BD independiente de un SGBD particular](https://reader035.vdocumento.com/reader035/viewer/2022062409/5665b4351a28abb57c8ffe9f/html5/thumbnails/49.jpg)
DL - 49
DLE: transformaciones adicionales
Otros conceptos del Modelo Relacional
• Será necesario crear procedimientos que verifiquen las restricciones de integridad definidas en la fase de Diseño Lógico Estándar
• Si el SGBD lo permite, se almacenarán en el catálogo del SGBD
• Si no, serán parte de los programas de aplicación– Restricciones de integridad como especificaciones de procesos