Download DISEÑO DE BASES DE DATOS

DISEÑO DE BASES DE
DATOS
Francisco Moreno
09/08/2017
Curso Bases de Datos
1
Convenciones
CP = Clave Primaria.
NN = No Nulo.
CA = Clave Alternativa.
CF = Clave Foránea.
Nota: Si hay varias claves alternativas o foráneas
se les coloca un subíndice numérico para
diferenciarlas, ver ejemplos a continuación.
09/08/2017
Curso Bases de Datos
2
el jefe
de
mandado
por
EMPLEADO
#código
*ced
*nombre
*salario
09/08/2017
DEPTO
adscrito
a
#código
*nombre
el lugar de
trabajo de
Curso Bases de Datos
3
Conversión E-R a Relacional
Cuadro de especificaciones:
Relación EMPLEADO
Nombre
columna
código
ced
Tipo de
clave
CP
CA
Nulos
NN
NN
NN
NN
1
2
7
4
71721
43214
71234
43125
Jimmy
Nia
Candy
Brandy
1000
2500
6000
5500
Ejemplos
nombre salario
jefe
dpto
CF1
CF2
(EMPLEADO)
(DEPTO)
NN
NULL
1
1
7
5
5
6
8
Aunque se sobreentiende por la regla de integridad de la CP.
09/08/2017
Curso Bases de Datos
4
MECÁNICO
#cédula
*nombre
*salario
el ejecutor de
el receptor de
ejecutada por
recibida por
REVISIÓN
En este ejemplo:
- Un automóvil solo
puede tener una
revisión por día.
- La placa de los autos
está descompuesta en
letras y números.
AUTOMÓVIL
#fecha
#letras
°valor
para
sujeto #números
de *marca
*modelo
09/08/2017
Curso Bases de Datos
La relación* REVISIÓN tendrá tres claves
foráneas:
• Dos hacia MECÁNICO
• Una hacia AUTOMÓVIL. Además esta clave
foránea:
– hace parte de la clave primaria de REVISIÓN
(esto lo indica la rayita azul en el modelo entidad
relación) junto con la fecha.
– es compuesta, ya que la clave primaria de la
relación* AUTOMÓVIL está conformada por
{letras, números}.
* Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
6
Conversión E-R a Relacional
Cuadro de especificaciones:
Relación REVISIÓN
Nombre
columna
fecha
Tipo de
clave
CP
Nulos
NN
Ejemplos
09/08/2017
30/01/99
15/02/99
30/01/99
30/01/99
valor
100
NULL
200
NULL
ccejecuta
ccrecibe
CF1
CF2
CP, CF3
CP, CF3
(MECÁNICO)
(MECÁNICO)
(AUTOMÓ
VIL)
(AUTOMÓ
VIL)
NN
NN
NN
8
1
9
8
ABK
ABK
ITA
ITA
234
234
123
231
1
1
NULL
9
Curso Bases de Datos
placalet placanu
ras
meros
7
Conversión E-R a Relacional
RX
#a
#b
*c
*d
Supóngase que la entidad
RX tiene los siguientes
identificadores únicos
alternativos:
{b, c}
{c, d}
09/08/2017
Curso Bases de Datos
8
Conversión E-R a Relacional
Cuadro de especificaciones:
Relación RX
09/08/2017
Nombre
columna
a
b
c
d
Tipo de
clave
CP
CP,
CA1
CA1,
CA2
CA2
Nulos
NN
NN
NN
NN
Ejemplos
10
10
20
20
45
20
30
30
31
22
97
97
Curso Bases de Datos
9
Conversión E-R a Relacional
• Obtener datos ejemplo de las tuplas mediante: entrevistas,
documentación, conversaciones con los usuarios.
• Convertir cada entidad en una relación*
– Un cuadro de especificaciones por cada relación* (como los de la
diapositivas 4, 7 y 9).
– Preferiblemente con el mismo nombre que tiene en el modelo E-R.
• Convertir los atributos en columnas:
– Los atributos obligatorios son no nulos (NN).
– Nombres cortos pero significativos (usualmente los mismos que
tienen en el modelo E-R), pueden ser abreviaturas entendibles.
* Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
10
Conversión E-R a Relacional
• Convertir los identificadores únicos en
claves primarias:
– Identificador único con varios atributos 
clave primaria compuesta.
– Si el identificador único está conformado por
relaciones* con otras entidades, las claves
foráneas respectivas harán parte de la clave
primaria (como en el ejemplo de REVISIÓN
con AUTOMÓVIL)
* Relación en el sentido del modelo E-R.
09/08/2017
Curso Bases de Datos
11
Conversión E-R a Relacional
• Convertir las relaciones* en claves foráneas:
– Asignar nombre(s) a la(s) columna(s) que conforman la
CF y rotularla(s) “CF” en el cuadro de especificaciones e
indicar la relación** a la que referencia.
– Relaciones* 1 a muchos: La CF se coloca en la relación**
correspondiente a la entidad donde llega la cardinalidad
muchos.
– Si la relación* es obligatoria (en el lado de la entidad que
posee la CF), la CF es NN.
– Relación* recursiva 1 a muchos: se adiciona una columna
CF a la relación** y se referencia hacia sí misma (hacia
su CP). ¿Puede admitir nulos?
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
12
Conversión E-R a Relacional
• Claves Foráneas (cont.):
– Relaciones* 1 a 1: Colocar la CF en el lado de la
obligatoriedad y es NN.
• Si ambos lados de la relación* son obligatorios u opcionales, la
CF se coloca en cualquiera de las dos relaciones**.
• Si ambos lados de la relación* son opcionales, la CF admite
nulos.
– Una CF que surge de una relación* 1 a 1 es una clave
candidata ¿Por qué?
– Relaciones* muchos a muchos: se transforman y dan
origen a una tercera relación** (intersección).
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
13
Conversión E-R a Relacional
• Arcos:
para
FACTURA
INDIVIDUO
generador
de #código
*nombre
#código
°fecha
SOCIEDAD
para
generador #código
de *nombre
Se pueden llevar al
modelo relacional
mediante arco explícito
o arco genérico.
09/08/2017
*fecha constitución
Curso Bases de Datos
14
Conversión E-R a Relacional
Arco explícito:
– Una CF por cada relación* participante en el
arco.
– Se debe usar cuando las CFs tienen diferentes
dominios.
– Para manejar la exclusividad se debe recurrir a
una cláusula de verificación (CHECK) para
garantizar que si una CF del arco es no nula las
demás CFs del arco deberán ser nulas.
* Relación en el sentido del modelo E-R.
09/08/2017
Curso Bases de Datos
15
Conversión E-R a Relacional
Arco genérico:
– Una columna representa a todas las relaciones* en el arco.
– Si el arco es obligatorio, la columna debe ser NN, opcional
de lo contrario.
– El dominio debe ser igual en todas las relaciones* del arco.
– La columna solo debe aceptar valores provenientes de las
CPs de las relaciones** correspondientes.
– Una columna adicional para saber cual de las relaciones** se
referencia en la columna.
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
16
Conversión E-R a Relacional
• Supertipos/
subtipos:
EMPLEADO
#código
*nombre
DE PLANTA
*salario
Se verán dos formas
para llevarlo al modelo
relacional.
*valor hora
en
en
el lugar de
trabajo de
DEPTO
#código
09/08/2017
TEMPORAL
Curso Bases de Datos
adscrito a
EMPRESA
#nit
17
Conversión E-R a Relacional
1. Diseño de los subtipos en una sola relación**.
Características:
- Recomendable cuando los subtipos tienen pocos atributos y pocas
relaciones* propias.
- La mayoría de las consultas involucran datos de diferentes
subtipos.
- La relación** resultante contiene los datos de todos los subtipos.
- El acceso al supertipo es “directo”.
- El acceso a los subtipos se logra mediante vistas.
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
18
Conversión E-R a Relacional
• El diseño es así:
•
•
•
•
Crear una relación** para el supertipo.
Crear una columna para cada atributo del supertipo.
Crear una columna para cada atributo de los subtipos.
Crear una columna llamada “tipo” para identificar a
cada subtipo.
• Crear columnas CF para cada relación* del supertipo.
• Crear columnas CF para cada relación* de los subtipos.
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
19
Conversión E-R a Relacional
• Ventaja:
– Una sola relación** para manejar el supertipo y todos sus
subtipos.
• Desventajas:
– Requiere la creación de una columna para
identificar a los subtipos.
– Todos las columnas de los subtipos deben ser
opcionales (admitir nulos) ¿Por qué? ¿Esto que
implicaciones tiene?
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
20
Conversión E-R a Relacional
Desventajas:
– Implica verificar que si una tupla pertenece a un
subtipo dado, los atributos de los demás subtipos
deberán ser nulos.
– Si un atributo de un subtipo es obligatorio en el
modelo E-R, la columna correspondiente en el
modelo relacional queda opcional.
– Implica el manejo de vistas para “extraer” los
subtipos.
09/08/2017
Curso Bases de Datos
21
Conversión E-R a Relacional
2. Diseño de los subtipos en relaciones** separadas.
El diseño es así:
• Crear una relación** para el supertipo:
- Crear una columna por cada atributo del
supertipo.
- Crear columnas CF para cada relación* del
supertipo.
- Crear una columna llamada “tipo” para identificar a
cada subtipo.
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
22
Conversión E-R a Relacional
• Crear una relación** para cada subtipo:
– Crear columnas para cada atributo del subtipo.
– Crear columnas CF para cada relación* del
subtipo.
– Crear una CF hacia el supertipo en cada uno de
los subtipos. Esta CF será la CP del subtipo.
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
23
Conversión E-R a Relacional
Ventajas:
- Cada relación* tiene instancias solo de un subtipo.
- Si un atributo de un subtipo es obligatorio, la columna
correspondiente en el modelo relacional queda
obligatoria.
Desventajas:
- Acceso al supertipo que implique atributos de los
subtipos implica una reunión (join)  Se ve luego.
- Subtipos excluyentes: Implica garantizar que la CP del
supertipo solo aparezca en uno de los subtipos.
* Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
24
Conversión E-R a Relacional
Otra Alternativa:
Eliminar la relación** correspondiente al
supertipo y agregar todas sus columnas y
relaciones* a cada relación** correspondiente a
los subtipos, pero igualmente exige controles
adicionales y además se pierde la noción de
supertipo...
* Relación en el sentido del modelo E-R.
** Relación en el sentido del modelo relacional.
09/08/2017
Curso Bases de Datos
25