Tema 3: Diseño lógico de Bases de Datos: el Modelo Relacional

Tema 3:

Dise˜ no l´ ogico de Bases de Datos:

el Modelo Relacional

Andr´es Cord´on Franco

Departamento de

Ciencias de la Computaci´on e Inteligencia Artificial UNIVERSIDAD DE SEVILLA

Bases de Datos

Curso 2005–06

Contenido:

3.1 Introducci´ on al Modelo Relacional

3.2 Elementos b´ asicos del Modelo Relacional:

tabla o relaci´ on, registro o tupla, campo

3.3 Clave primaria (PK) y claves ajenas (FK) de una tabla

3.4 Valores nulos. Restricciones de integridad 3.5 Transformaci´ on de esquemas: del diagrama

E–R al esquema relacional.

3.6 Jerarqu´ıas de generalizaci´ on en el esquema

relacional.

Introducci´ on al Modelo Relacional(I):

• Fue introducido por E.F. Codd en 1970.

Aunque los primeros SGBD relacionales no aparecieron hasta los a˜ nos 80.

• Supuso una revoluci´ on en el dise˜ no l´ ogico de BD, dando lugar a la segunda generaci´ on de SBGD.

• Es el modelo l´ ogico m´ as extendido en la ac- tualidad (ORACLE, Access, dBaseIV, ...)

• Los datos se estructuran l´ ogicamente en forma de relaciones (tablas).

• Intuitivamente, una BD relacional es un

conjunto de tablas enlazadas entre s´ı.

Introducci´ on al Modelo Relacional(II):

• Conceptos fundamentales:

Relaci´ on: tabla bidimensional

Registro o tupla: fila de la tabla Campo: columna de la tabla

Tabla ESCRITOR (2 registros de 4 campos) DNI Nombre Direcci´ on Fecha 44345789 Ana P´ erez Sol, 17 9/5/1960 56123009 Luis G´ omez Feria,2 5/5/1961 Tabla ESCRIBE (3 registros de 3 campos)

ISBN Libro Autor A˜ no 84-8088-004-9 44345789 2003 44-9876-123-7 44345789 1999 34-5678-321-5 56123009 1989

Las tablas se enlazan entre s´ı mediante campos

con contenido com´ un. (El campo Autor de la

tabla ESCRIBE y el campo DNI de la tabla

ESCRITOR permiten enlazar ambas tablas).

Definiciones(I):

Una relaci´ on de grado m consta de dos partes:

• Cabecera: conjunto fijo de m campos.

Cada campo est´ a definido por su Nombre y su Dominio (que indica el tipo de valores que contendr´ a dicho campo).

Lo escribiremos (Nombre:Dominio).

• Cuerpo: conjunto variable de registros (tam- bi´ en denominados tuplas).

Un registro es un conjunto de m valores, correspondientes a los campos de la relaci´ on:

{(Nombre

: Valor

), . . . , (Nombre

: Valor

)}.

Notas:

1.- Cada relaci´ on tiene asociado un Nombre que la identifica.

2.- Una relaci´ on de grado m puede represen-

tarse mediante una tabla bidimensioinal de m

columnas y tantas filas como registros aparez-

can en la relaci´ on.

Definiciones(II):

Ejemplo: Relaci´ on Escritor

DNI Nombre Direcci´ on Fecha 44345789 Ana P´ erez Sol,17 9/5/1960 56123009 Luis G´ omez Feria,2 5/5/1961

• La cabecera de la relaci´ on Escritor es:

{ (DNI:Num´ erico), (Nombre:Texto),

(Direcci´ on:Texto), (Fecha:Fecha/Hora) }

• El cuerpo de la relaci´ on Escritor est´ a for- mado por dos registros:

{ (DNI:56123009), (Nombre:Luis G´ omez), (Direcci´ on:Feria,2), (Fecha:5/5/1961) } { (Fecha:9/5/1960), (DNI:44345789),

(Direcci´ on:Sol,17), (Nombre:Ana P´ erez) }

• La relaci´ on Escritor es de grado 4.

Propiedades de las Relaciones(I):

• Todos los registros de una relaci´ on deben tener el mismo n´ umero de campos, aunque al- guno est´ e vac´ıo (se admite el valor NULL).

• Los valores de los campos son at´ omicos: fi- jado un registro, cada campo toma un ´ unico valor (no se admiten campos multivaluados).

• No se admiten registros duplicados. Esto es, dos registros de una misma relaci´ on deben diferir, al menos, en el valor de un campo.

• Dos campos de la misma relaci´ on no pueden tener el mismo nombre.

• Dos campos de relaciones distintas s´ı pueden tener el mismo nombre (Nombre1.Nombre2 de- nota el campo Nombre2 de la relaci´ on Nombre1).

Ejemplo: Escritor.DNI

• El orden de los campos no importa.

• El orden de los registros no importa.

Propiedades de las Relaciones(II):

• Ejemplo 1:

DNI Nombre Direcci´ on Fecha 44345789 Ana P´ erez Sol, 17 9/5/1960 40876100 Jos´ e Ru´ız Luna,1 1/1/1972 56123009 Luis G´ omez Feria,2 5/5/1961 Direcci´ on Nombre Fecha DNI

Feria,2 Luis G´ omez 5/5/1961 56123009 Luna,1 Jos´ e Ru´ız 1/1/1972 40876100 Sol,17 Ana P´ erez 9/5/1960 44345789 Las 2 relaciones anteriores son id´ enticas. S´ olo difieren en el orden de los campos y resgistros.

• Ejemplo 2:

Nombre Edad Estudios Juan P´ erez 41 Lcdo. Qu´ımica Ana S´ anchez 37 Lcdo. Medicina

Lcdo. F´ısica Juan P´ erez 41 Lcdo. Qu´ımica

La relaci´ on anterior NO es v´ alida. Posee cam-

pos multivaluados y registros repetidos.

Propiedades de los Campos(I):

Cada campo debe poseer un Nombre (rela- cionado con los datos que contendr´ a) y debe tener asociado un Tipo de dato.

• Texto: almacena cadenas de caracteres, ya sean n´ umeros (con los que no se vaya a relizar operaciones), letras o cualquier s´ımbolo.

• Num´ erico: almacena n´ umeros destinados a realizar operaciones.

• Fecha/hora: almacena fechas y horas.

• S´ı/No: para almacenar datos que solo ten- gan dos posibilidades(verdedro-falso).

• Autonum´ erico: valor num´ erico (1,2,3,..) que el SGBD incrementa de modo autom´ atico cada vez que se a˜ nade un registro.

• Memo: almacena texto largo.

• Moneda: almacena valores de moneda.

• Objeto OLE: almacena gr´ aficos, im´ agenes

o textos creados por otras aplicaciones.

Propiedades de los Campos(II):

Propiedades adicionales:

• Descripci´ on: texto breve que aclara el con- tenido o la finalidad del campo.

• Tama˜ no: indica el tama˜ no m´ aximo permi- tido (s´ olo es aplicable a campos de texto o num´ ericos).

• Requerido o NOT NULL: no se permiten valo- res nulos para dicho campo.

• Predeterminado: se fija un valor por de- fecto para el campo.

Ejemplo: Relaci´ on Cliente (5 campos) { (NIF:Texto(9),NOT NULL),

(Nombre:Texto(50),NOT NULL,

Descripci´ on=”Nombre y apellidos del cliente”), (Fecha:Fecha/Hora,

Descripci´ on=”Fecha de nacimiento del cliente”), (Nacionalidad:Texto(20),

Predeterminado=”Espa˜ nola”),

(Direcci´ on:Texto) }

Clave primaria y claves ajenas(I):

Clave: conjunto de campos cuyos valores de- terminan un´ıvocamente a cada registro de la relaci´ on. Dicho conjunto de campos debe ser minimal, esto es, ning´ un subconjunto propio de la clave puede actuar tambi´ en como clave.

Clave candidata: cada uno de los campos o combinaciones de campos que pueden actuar como clave de la relaci´ on.

Clave primaria(PK=Primary Key ): clave can- didata elegida por el dise˜ nador de la BD para la relaci´ on.

Clave ajena o secundaria (FK=Foreign Key ):

campo o combinaci´ on de campos de una relaci´ on

que funciona como clave primaria de otra relaci´ on

de la BD (relaci´ on referenciada o relaci´ on padre

para la clave ajena).

Clave primaria y claves ajenas(II):

1.- Las claves ajenas son esenciales en el Mo- delo Relacional, ya que permiten enlazar tablas de la BD.

2.- Una clave ajena y la clave primaria de la relaci´ on referenciada asociada han de estar de- finidas sobre los mismos dominios.

3.- Una relaci´ on puede poseer m´ as de una clave ajena (tendr´ a una clave ajena por cada relaci´ on referenciada de la cual dependa).

4.- Una relaci´ on puede no poseer ninguna clave ajena.

5.- Una clave ajena puede enlazar una relaci´ on consigo misma (relaciones reflexivas).

6.- En el Modelo Relacional, toda relaci´ on posee clave primaria.

Observaci´ on: N´ otese la diferencia con el Mo-

delo Entidad-Relaci´ on, donde existen entidades

que no poseen PK (las entidades d´ ebiles con

dependencia en identificaci´ on).

Clave primaria y claves ajenas(III):

Relaci´ on Editorial

Nombre Direcci´ on Ciudad Pa´ıs La˜ N Sol,5 Sevilla Espa˜ na Relaci´ on Escritor

Nombre DNI Nacionalidad Ana Ru´ız 56234111 Chilena Relaci´ on Libro

C´ odigo T´ıtulo Autor Nombre-Ed 1256AB Volver 56234111 La˜ N

Relaci´ on Editorial: PK = (Nombre:Texto) Relaci´ on Escritor: PK = (DNI:Texto)

Relaci´ on Libro: PK = (C´ odigo:Texto)

FK = (Nombre-Ed:Texto) (→ Editorial)

FK = (Autor:Texto) (→ Escritor)

Valores nulos en el Modelo Relacional:

Valor nulo (NULL): marca utilizada para repre- sentar informaci´ on desconocida o no aplicable.

El valor de un campo puede ser nulo por dos razones distintas:

• Existencia de registros con ciertos atribu- tos desconocidos en ese momento.

• Existencia de campos inaplicables a ciertos registros de una tabla.

Ejemplo: Relaci´ on Obra

C´ odigo T´ıtulo Tipo Editorial A˜ no 123A La huida Libro La˜ N 2002 678V El infinito Libro NULL NULL

564B Azul Cuadro NULL 1975

Los valores nulos del registro ’678V’ lo son por

informaci´ on deconocida, mientras que el valor

nulo del registro ’564B’ representa un campo

no aplicable (un cuadro no posee editorial).

Restricciones de integridad(I):

(A) Restricciones inherentes:

Integridad de entidad: ning´ un campo que forme parte de la clave primaria de una relaci´ on puede tomar valores nulos.

Nota: Para conseguir la integridad de enti- dad, basta declarar como ’Requerido’ todos los campos que formen parte de la PK de cada relaci´ on de la BD.

(B) Restricciones de usuario:

Integridad referencial: Si una relaci´ on R1 posee una clave ajena que la enlaza con la relaci´ on padre R2, entonces todo valor de dicha clave ajena de R1:

• debe coincidir con alg´ un valor de la clave primaria de R2 que haya sido previamente introdicido en la BD; o bien

• debe tomar el valor nulo (NULL).

Restricciones de integridad(II):

Ejemplo: Relaci´ on Escritor

DNI Nombre Fecha Pa´ıs

67543198 Luis Ru´ız 1/1/1965 Chile 89564123 Ana P´ erez 2/7/1977 Espa˜ na PK = (DNI:Texto)

Relaci´ on Obra

C´ odigo T´ıtulo Autor Fecha 345 La huida 67543198 1993 111 El fin 33567900 1982

654 NULL NULL 2001

PK = (C´ odigo:Texto)

FK = (Autor:Texto) (→ Escritor)

La BD anterior NO cumple la restricci´ on de integridad referencial.

El valor del campo Autor del segundo registro

de la tabla Obra (33567900) NO se correspon-

de con ning´ un valor del campo DNI de la tabla

Escritor.

Restricciones de integridad(III):

La relaci´ on R1 est´ a enlazada con la relaci´ on padre R2 mediante una clave ajena C.

Para mantener la integridad referencial...

(A) Insercci´ on:

El SGBD s´ olo permite insertar un nuevo res- gistro en la tabla R1 cuando el valor del campo C para ese registro coincida con alg´ un de la PK de R2 que aparezca en la tabla.

(B) Borrado (eliminaci´ on en cascada):

Si eliminamos un registro de la tabla padre R2, el SGBD elimina autom´ aticamente todos los registros de la tabla R1 que est´ an relacionados con dicho registro.

(C) Modificaci´ on (actualizaci´ on en cascada):

Si modificamos el valor de la PK de un regis-

tro de la tabla padre R2, el SGBD modifica

autom´ aticamente dicho valor en todos los re-

gistros de la tabla R1 que est´ en relacionados

con ´ el.

Paso del DER al Esquema Relacional(I):

ENTIDADES:

(A) Entidades fuertes

Por cada entidad fuerte del diagrama E-R, se crear´ a una nueva relaci´ on en el esquema rela- cional con tantos campos como atributos posea la entidad. La PK de la relaci´ on creada es la misma que la PK de la entidad.

Ejemplo: La entidad fuerte

Alumno(DNI,Nombre,Direcci´ on,Fecha) genera la Relaci´ on Alumno definida por:

{ (DNI:Texto), (Nombre:Texto),

(Direcci´ on:Texto), (Fecha:Fecha/Hora) } PK = (DNI:Texto)

(B) Entidades d´ ebiles (en existencia):

Se tratan como entidades fuertes.

Paso del DER al Esquema Relacional(II):

(C) Entidades d´ ebiles (en identificaci´ on):

Se crear´ a una nueva relaci´ on con los campos:

• un campo por cada atributo de la entidad, y

• se a˜ naden los campos que forman la PK de la entidad padre de la cual depende.

PK =

(

discriminador de la entidad d´ ebil + PK de la entidad padre

Se a˜ nade adem´ as una clave ajena a la relaci´ on:

FK=PK de la entidad padre(→ Relaci´ on padre) Ejemplo: Cuenta(C´ odigo,Titular,Fecha)

Operaci´ on(N´ umero,Descripci´ on,Cantidad) Relaci´ on Operaci´ on:

{ (N´ umero:Texto) , (C´ odigo-cuenta:Texto) , (Cantidad:Num´ erico), (Descripci´ on:Texto) } PK = (N´ umero:Texto)+(C´ odigo-cuenta:Texto) FK = (C´ odigo-cuenta:Texto) (→ Cuenta)

Nota: En una cadena de dependencias, las

claves primarias se propagan en cascada.

Paso del DER al Esquema Relacional(III):

RELACIONES:

Las relaciones se tratar´ an de forma distinta seg´ un el tipo de relaci´ on : (N:M), (1:N), (1:1).

(Suponemos que R asocia las entidades E1,E2) (A) Relaciones de tipo (N:M):

En el esquema relacional, creamos una nueva relaci´ on con los siguientes campos:

• los campos de la PK de la entidad E1,

• los campos de la PK de la entidad E2,

• los campos correspondientes a los atributos propios de la relaci´ on (si los hubiese).

Clave primaria:

PK = (PK de E1) + (PK de E2) Claves ajenas:

Se a˜ naden dos claves ajenas a la nueva relaci´ on:

FK = PK de E1 (→ Relaci´ on E1)

FK = PK de E2 (→ Relaci´ on E2)

Paso del DER al Esquema Relacional(IV):

(B) Relaciones de tipo (1:N):

NO se crear´ a ninguna relaci´ on nueva. En su lugar, modificaremos la relaci´ on asociada a la entidad que partcipa con cardinalidad m´ axima

“muchos”. Suponemos que...

E1 participa con cardinalidad (–,n) E2 participa con cardinalidad (–,1)

(–) Modificamos la relaci´ on asociada a la en- tidad E1 como sigue:

• a˜ nadimos los campos que forman la PK de la entidad E2,

• a˜ nadimos los campos correspondientes a los atributos propios de la relaci´ on (si los hubiese),

• la clave primaria de la relaci´ on E1 no var´ıa pero s´ı a˜ nadimos una nueva clave ajena:

FK = PK de la entidad E2 (→ Relaci´ on E2)

Paso del DER al Esquema Relacional(V):

(C) Relaciones de tipo (1:1):

NO se crear´ a ninguna relaci´ on nueva. Se tratan como las relaciones (1:N).

Puesto que las dos entidades participan con cardinalidad (–,1), tenemos dos opciones:

• A˜ nadir a la relaci´ on asociada a E1 la PK de E2 y los atributos propios de la relaci´ on (1:1)

• A˜ nadir a la relaci´ on asociada a E2 la PK de E1 y los atributos propios de la relaci´ on (1:1) Notas:

1.- Si una entidad participa con cardinalidad (1,1) y la otra con cardinalidad (0,1), optare- mos por modificar la relaci´ on correspondiente a la entidad que participa con cardinalidad (1,1).

(Ventaja: Evitamos valores nulos)

2.- El dise˜ nador de la BD puede optar por

tratar una cierta relaci´ on (1:N) o (1:1) como

relaci´ on (N:M) y a˜ nadir una nueva relaci´ on en

la BD para ella.

Paso del DER al Esquema Relacional(VI):

RELACIONES ESPECIALES:

(A) Relaciones d´ ebiles en identificaci´ on:

Basta a˜ nadir los atributos propios de la relaci´ on d´ ebil en identificaci´ on (si los hubiese) a la tabla previamente creada para la entidad d´ ebil.

(B) Relaciones reflexivas:

• Tipo (N:M): se crear´ a una nueva relaci´ on siguiendo las instrucciones anteriores, pero la PK de la entidad que participa aparecer´ a dos veces (con nombres distintos seg´ un el rol con el que participe en la relaci´ on reflexiva).

• Tipo (1:N): NO se crear´ a una nueva relaci´ on.

Se tratar´ an como se describi´ o anteriormente.

Ahora bien, en la tabla asociada a la entidad que participa en la relaci´ on reflexiva aparecer´ a dos veces su PK (con nombres distintos):

(-) una vez como PK de la tabla, y

(-) otra vez como FK de la tabla que la enlaza

consigo misma.

Paso del DER al Esquema Relacional(VII):

(C) Relaciones de grado k ≥ 3

Debemos analizar la relaci´ on y estudiar la mejor opci´ on en cada caso particular.

Soluci´ on General: Se trata como una relaci´ on binaria (N:M). Es decir, se crea una nueva tabla para la relaci´ on siguiendo las instrucciones descritas para las relaciones de tipo (N:M).

Ahora bien:

• en lugar de dos, habr´ a que a˜ nadir k claves ajenas en la tabla creada,

• la PK de la nueva tabla no tiene por qu´ e ser

igual a la suma de las PK de las entidades par-

ticipantes. Puede que haya que eliminar alguno

de los campos.

Paso del DER al Esquema Relacional(VIII):

Ejemplo: Relaci´ on Imparte entre Asignatura, Grupo, Aula y Profesor ; con atributo propio Horario.

Cod-asig Grupo Cod-aula DNI Horario PK = (Cod-asig:Texto),(Grupo:Num´ erico) Claves ajenas:

FK = (Cod-asig:Texto) (→ Asignatura) FK = (Grupo:Num´ erico) (→ Grupo)

FK = (Cod-aula:Texto) (→ Aula) FK = (DNI:Texto) (→ Profesor)

Soluci´ on particular: Existe una entidad (en- tidad hija) tal que cada instancia suya est´ a relacionada con una ´ unica tupla de las k − 1 entidades restantes.

No se crea una nueva tabla. En su lugar, se a˜ nade a la tabla de la entidad hija:

• las PK de las k − 1 restantes entidades (con las correspondientes k − 1 claves ajenas),

• los atributos propios de la relaci´ on (si los

hubiese).

Paso del DER al Esquema Relacional(IX):

JERARQU´IAS DE GENERALIZACI ´ ON:

No existe soluci´ on general. Hay que analizar ventajas e inconvenientes en cada caso.

Opci´ on 1: Tabla ´ unica

Se crea una ´ unica tabla para representar la je- rarqu´ıa con las siguientes caracter´ısticas:

Nombre: nombre de la entidad padre.

Clave primaria: PK de la entidad padre Campos:

• atributos de la entidad padre,

• la uni´ on de los atributos de los subtipos; y

• un nuevo campo Tipo para indicar a qu´ e subtipo de la jerarqu´ıa pertenece cada registro.

Inconvenientes:

1.- Aparici´ on de muchos valores nulos.

2.- P´ erdida de informaci´ on si existen en el DER

relaciones en las que no participa la entidad

padre sino un cierto subtipo.

Paso del DER al Esquema Relacional(X):

Opci´ on 2: Orientada a objetos

Se a˜ nade una nueva tabla por cada subtipo de la jerarqu´ıa y se consideran que son entidades distintas (no es necesario incluir una tabla para la entidad padre a menos que la jerarqu´ıa sea parcial).

Para cada subtipo, su tabla asociada contendr´ a los siguientes campos:

• atributos de la entidad padre (la PK de la entidad padr´ e ser´ a la PK de la tabla)

• atributos propios del subtipo en cuesti´ on.

Inconvenientes:

1.- Una relaci´ on del DER en la que participa la entidad padre ha de clonarse para cada subtipo de la jerarqu´ıa (aparecen muchas tablas).

2.- Informaci´ on redundante (los atributos de la entidad padre se repiten para cada subtipo de la jerarqu´ıa).

3.- Jerarqu´ıas solapadas: ¿en qu´ e tabla se guarda

un registro que pertenece a varios subtipos?

Paso del DER al Esquema Relacional(XI):

Opci´ on 3: Directo del Diagrama E-R

Soluci´ on intermedia. Se a˜ naden nuevas tablas para la entidad padre y los subtipos, y se rela- cionan mediante claves ajenas.

Tabla entidad padre:

• Campos: atributos de la entidad padre.

• PK = PK de la entidad padre.

Tabla para cada subtipo:

• Campos: atributos propios del subtipo + PK de la entidad padre.

• PK = PK de la entidad padre.

• FK=PK entidad padre(→ Tabla entidad Padre).

Inconvenientes:

1.- Se repiten registros. Cada registro de la jerarqu´ıa aparece dos veces: una en la tabla padre y otra en el subtipo correspondiente.

2.- Muchas claves ajenas. Puede ralentizar las

consultas en la BD.

Bibliograf´ıa:

• Concepci´ on y dise˜ no de bases de datos, Adoraci´ on de Miguel, Mario Piattini, RA–

MA Editorial (1993)

• Apuntes de Ficheros y Bases de Datos,

Mercedes Marqu´ es, Universidad Jaume I en

Castell´ on (2001)