TEMA 5
DISENO DE UNA BASE DE DATOS
1. Fases de diseno de una BD . . . . . . . . . . . . . . . . . . . 2
2. Captacion y analisis de requerimientos . . . . . . . . . 3
3. Diseno conceptual de la BD . . . . . . . . . . . . . . . . . . . 5
4. Diseno logico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5. Ejemplos de diseno logico . . . . . . . . . . . . . . . . . . . .29
6. Diseno fısico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
1
Fases de diseno de una BD
Realizar les fases de diseno de una BD sobre un ejemplode BD bancaria, utilizando el modelo conceptual E-R yel modelo relacional.
FASES:
1. Captacion y analisis de requerimientos
2. Diseno conceptual de la BD
3. Diseno logico
4. Diseno fısico
2
1. Captacion y analisis de requerimientos
Especificaciones obtenidas a partir de entrevistas conlos usuarios.
Requisitos de datos:
• El banco esta organizado por sucursales.
Cada sucursal esta en una unica ciudad, identificada por unnombre unico.
El banco controla los activos de cada sucursal.
• Los clientes se identifican por DNI.
El banco almacena el nombre, calle y ciudad de sus clientes.
Clientes pueden tener cuentas y pueden solicitar prestamos.
Cliente puede estar asociado con un empleado particular, comoresponsable del prestamo o como banquero personal.
• Los empleados se identifican por DNI.
El banco almacena el nombre y telefono del empleado, nom-bres de los subordinados que tiene y el DNI de su jefe, asıcomo la fecha en que lo contrataron y el tiempo que llevatrabajando.
3
• El banco tiene dos tipos de cuentas: corriente y de ahorro.
Las cuentas pueden asociarse a mas de un cliente, y un clientepuede tener mas de una cuenta.
Cada cuenta tiene un numero unico.
Banco mantiene el saldo de la cuenta y la fecha de accesomas reciente.
Cada cuenta de ahorro almacena un tipo de interes y cadacuenta corriente almacena su descubierto.
• Un prestamo se realiza en una sucursal particular y puede estarasociado a uno o mas clientes.
Un prestamo se identifica con un numero unico de prestamo.
Banco mantiene el importe del prestamo y los pagos realiza-dos.
Por cada pago se guarda el numero de pago, la fecha y elimporte.
Numero de pago identifica un pago particular de un prestamoconcreto.
Numero de pago de un prestamo no identifica de forma unicael pago entre todos los pagos de prestamos realizados en elbanco.
En un banco real ademas se mantiene informacion sobrelos movimientos de las cuentas corrientes y de ahorro(abonamientos y cargos), que para simplificar el modelono se tiene en cuenta.
4
2. Diseno conceptual de la BD
Se disena un esquema E-R con las especificaciones an-teriores, que consta de una coleccion de ENTIDADESy RELACIONES descritos en un DIAGRAMA E-R.
ENTIDADES:
• CLIENTE(Nombre-Cliente,DNI,Calle-Cliente,Ciudad-Cliente)
• EMPLEADO(DNI-E, Nombre-Empleado, Numero-Telefono,Sueldo, Jefe, Nombre-Subordinado,Fecha-Inicio,Antiguedad)con
– Nombre-Subordinado: Atributo multivalorado.
– Antiguedad: Atributo derivado de Fecha-Inicio.
• CUENTA(Numero-Cuenta,Saldo) con subgrupos
– CUENTA-AHORRO(Tipo-Interes)
– CUENTA-CORRIENTE(Descubierto)
• SUCURSAL(Nombre-Sucursal,Ciudad-Sucursal,Activos)
• PRESTAMO(Numero-Prestamo,Importe) con entidad debil
– Prestamo-Pago(Numero-Pago,Fecha-Pago, Importe-Pago)
5
INTERRELACIONES:
• PRESTATARIO: Interrelacion n−n entre CLIENTE y PRESTAMO
Expresa que un cliente puede tener n prestamos y que unprestamo puede tener n clientes que lo hayan solicitado.
• PRESTAMO-SUCURSAL: Interrelacion n−1 entre PRESTAMOy SUCURSAL
Expresa que un prestamo esta en una unica sucursal, y queuna sucursal puede tener n prestamos.
• IMPOSITOR(Fecha-Acceso): Interrelacion n−n entre CLIENTEy CUENTA
Expresa que un cliente tiene n cuentas y una cuenta puedeser compartida por n clientes.
• BANQUERO-CONSEJERO(Tipo): Interrelacion n − 1 entreCLIENTE y EMPLEADO
Expresa que un cliente puede tener un unico consejero, mien-tras que un empleado puede aconsejar a n clientes.
• TRABAJA-PARA: Interrelacion 1−n unaria entre EMPLEADOcon papeles:
– JEFE: Un empleado tiene un unico jefe.
– TRABAJADOR: Un empleado(jefe) puede tener n traba-jadores.
6
DIAGRAMA E-R:
7
3. Diseno logico
Conversion del esquema E-R a un esquema relacional,basado en relaciones y atributos.
Model E-R
Model BD relacional
Conceptos de diseno muyparecidos, permetiendopasar facimente de unmodelo a otro.
CONVERSION DE UN ESQUEMA E-R A RELA-CIONES:
Transformar diferentes elementos del modelo E-R a rela-ciones preservando el nombre de los elementos E-R.
Passos:
(a) Conversion de entidades fuertes
(b) Conversion de entidades debiles
(c) Conversion de interrelaciones
(d) Conversion de atributos multivalorados
(e) Conversion de especializaciones (y generalizaciones)
(f) Conversion de agregaciones
(g) Normalizacion de las relaciones (tema 6)
8
(a) Conversion de entidades fuertes
Proposicion: Sea E una entidad fuerte con atributosa1, a2, . . . , an, con llave primaria CP (E) = ai . . . aj, conai, aj ∈ E,
la entidad E se representa mediante una relacion E concabecera
{a1, a2, . . . an}
y Clave Primaria
CP (Relacion E) = CP (Entidad E) == {ai . . . aj}
Anadir una instanciacion ≡ Insertar una tuplaa la entidad a la relacion
9
Ejemplo: Entidad PRESTAMO.
se representa mediante la relacion PRESTAMO
10
Ejemplo: Entidad CLIENTE.
se representa mediante la relacion CLIENTE
11
(b) Conversion de entidades debiles
Proposicio: Sea A una entidad debil con atributos a1,a2, . . . an, con discriminante ai . . . aj (ai, aj∈A), que de-pende de la entidad fuerte B, con CP (B) = b1, b2, . . . bn,
la entidad A se representa mediante la relacion A concabecera
{a1, a2, . . . an} ∪ {b1, b2, . . . bn}
y clave primaria
CP (Relacion A) = {ai . . . aj} ∪ {b1, b2, . . . bn}
12
Ejemplo: Entidad PAGO(Numero-Pago,Fecha-Pago, Importe-Pago)con CP (PRESTAMO) = Numero-Prestamo
se representa mediante la relacion PAGO
13
(c) Conversion de interrelaciones
Proposicion: Sea R una interrelacion con a1, a2, . . . an
la union de las claves primarias de las entidades queparticipan en R, y b1, b2 . . . bn los atributos descriptivosde la interrelacion R,
la interrelacion R se representa mediante la relacion Rcon cabecera
{a1, a2, . . . an} ∪ {b1, b2 . . . bn}
clave primaria
CP (R) = {a1, a2, . . . an}
y por cada entidad E que participa en R se define unaclave externa que referencia E como
CE(R → E) = {ai . . . aj}
siendo {ai . . . aj} los atributos de la relacion R que cor-responden a la CP (E).
14
Ejemplo: Interrelacion PRESTATARIO sin atributos entre las enti-dades CLIENTE y CUENTA, con CP (CLIENTE) = DNI yCP (PRESTAMO) = Numero-Prestamo.
Esta interrelacion se representa mediante la relacion PRESTATA-RIO
CP (prestatario) = dni,numero-prestamoCE(prestatario → cliente) = dni
CE(prestatario → prestamo) = numero-prestamo
Esquema relacional:
15
Casos especiales:
(c1) Redundancia de relaciones
(c2) Combinacion de relaciones
16
(c1) Redundancia de relaciones
Interrelacion sin atributos entre una entidad fuerte y unaentidad debil crea relaciones redundantes.
Proposicion: Sea R una interrelacion entre la entidadfuerte B y la entidad debil A. La interrelacion R se con-vierte a las siguientes relaciones:
Entidad fuerte B(b1, b2 . . . bn) con CP (B) = (br, . . . bs)↓ relacion
B(b1, b2 . . . bn) con CP (B) = (br . . . bs)
Entidad debil A(a1, a2 . . . am) con discriminante DIS(A) = (ai . . . aj)
↓ relacionA(a1, a2 . . . am, br . . . bs) con CP (A) = (ai . . . aj, br . . . bs)
Interrelacio R↓ relacion
R(ai . . . aj, br . . . bs) con CP (R) = (ai . . . aj, br . . . bs)CE(R → B) = (ai . . . aj)
CE(R → A) = (ai . . . aj, br . . . bs)
⇓Relacion R REDUNDANTE
EN CONSECUENCIA, La relacion obtenida de una interrelacionentre una entidad fuerte y una debil NO SE GENERA, pues esredundante.
Unicamente se generan las relaciones que provienen de la entidadfuerte y debil.
La CE(R → B) se transmite a la relacion A convertida en CE(A →B)
CE(A → B) = (ai . . . aj)
17
Ejemplo: Interrelacion PRESTAMO-PAGO
Entidad fuerte PRESTAMO con CP (PRESTAMO) = Numero-Prestamo
↓ relacionPRESTAMO(Numero-Prestamo,Importe) con
CP (PRESTAMO) = Numero-Prestamo
Entidad debil PAGO con discriminanteDIS(PAGO) = Numero-Pago
↓ relacionPAGAMENT(Numero-Prestamo, Numero-Pago,
Data-Pagament,Import-Pago)
con CP (PAGO) = Numero-Prestamo,Numero-Pago
Interrelacion PRESTAMO-PAGO↓ relacion
PRESTAMO-PAGO(Numero-Prestamo,Numero-Pago) con
CP (PRESTAMO-PAGO) = Numero-Prestamo,Numero-Pago
CE(PRESTAMO-PAGO → PRESTAMO) = Numero-Prestamo
CE(PRESTAMO-PAGO → PAGO) == Numero-Prestamo,Numero-Pago
⇓Relacion PRESTAMO-PAGO redundante
respecto relacion PAGO
18
Ejemplo: Interrelacion PRESTAMO-PAGO
↓ relaciones
19
(c2) Combinacion de interrelaciones
Proposicion: Dada una interrelacion R de correspon-dencia n− 1 entre dos entidades A y B, se definen tresrelaciones R,A,B.
Si la PARTICIPACION de la entidad A en la interrelacionR es TOTAL (toda instanciacion de la entidad A estaen la interrelacion R), se pueden combinar las relacionesA y R para crear una unica relacion formada por la unionde los atributos de las dos relaciones.
Ejemplo: Interrelacion CUENTA-SUCURSAL, de n − 1 entre lasentidades CUENTA y SUCURSAL,
Participacion de CUENTA a CUENTA-SUCURSAL es TOTAL (uncuenta no puede existir sin estar asociada a una sucursal).
Segun la proposicion anterior,
Entidades CUENTA, SUCURSAL einterrelacion CUENTA-SUCURSAL
↓ relaciones
SUCURSAL(Nombre-Sucursal,Ciudad-Sucursal,Activos)COMPTE(Numero-Cuenta,Saldo)CUENTA-SUCURSAL(Numero-Compte,Nom-Sucursal)
⇓ combinacion
SUCURSAL(Nombre-Sucursal,Ciudad-Sucursal,Activos)CUENTA(Numero-Cuenta,Saldo,Nombre-Sucursal)
20
Ejemplo: Entidades CUENTA, SUCURSAL e interrelacionCOMPTE-SUCURSAL
↓ relaciones
21
(d) Conversion de atributos multivalorados
Atributo simple de entidad ⇒ Atributo simple de unarelacion.
Atributo multivalorado de una entidad ⇒ Relacion.
Proposicion: Dado un atributo multivalorado m en unaentidad o interrelacion E, con CP (E) = a1 . . . ap,
el atributo m se representa mediante la relacion M concabecera
a1 . . . ap ∪mclave primaria
CP (M) = a1, . . . ap ∪my clave externa
CE(M → E) = a1 . . . ap
donde cada valor del atributo multivalorado m corres-ponde a una tupla de la relacion M.
Ejemplo: Atributo Nombre-Subordinado en la entidad EMPLEADO
se convierte con una relacion NOM-SUB(Nombre-Subordinado,DNI-E) con CP(NOM-SUB)=DNI-E, Nombre-Subordinado y
CE(NOM-SUB → EMPLEADO)=DNI-E.
22
(e) Conversion de especializaciones
Dos metodos diferentes de conversion a relaciones, unade ellas por el caso concreto de una especializacion dis-junta y completa.
Conversion de un unico nivel de especializacion.
(e1) Caso general
Proposicion: Dada una entidad de alto nivel A conatributos a1, a2, . . . , an y CP (A) = ai, . . . aj, donde ai, aj ∈A, y un conjunto de entidades de bajo nivel B(1), B(2), . . . B(p)
con atributos b(i)1 , b(i)2 , . . . b(i)m(i) para la entidad B(i), donde
m(i) es el numero de atributos de la entidad B(i),
la entidad A se representa mediante la relacion A con lacabecera
{a1, a2, . . . an}y con clave primaria
CP (A) = {ai, . . . aj}
Para cada entidad de bajo nivel B(i) se crea una relacionB − i con cabecera
{ai . . . aj} ∪ {b(i)1 , . . . b(i)m(i)}
clave primariaCP (B − i) = {ai, . . . aj}
y clave externa que referencia A como
CE(B − i → A) = {ai, . . . aj}
23
Ejemplo: Entidad de alto nivel CUENTA con entidades de nivelbajo CUENTA-CORRIENTE y CUENTA-AHORRO.
Se forman las relaciones siguientes:
CUENTA(Numero-Cuenta,Saldo)
CUENTA-AHORRO(Numero-Cuenta,Tipo-Interes)
CUENTA-CORRENT(Numero-Cuenta,Descubierto)
con
CP (CUENTA) = CP (CUENTA-AHORRO) =CP (CUENTA-CORRIENTE) = Numero-Cuenta
CE(COMPTE-AHORRO → CUENTA) = Numero-CompteCE(COMPTE-CORRIENTE → CUENTA) = Numero-Cuenta
Esquema relacional:
24
(e2) Especializacion disjunta y completa
Proposicion: Dada una entidad de alto nivel A conatributos a1, a2, . . . , an y CP (A) = ai, . . . aj, on ai, aj ∈ A,y un conjunto de entidades de bajo nivel B(1), B(2), . . . B(p)
con atributos b(i)1 , b(i)2 , . . . b(i)m(i) por la entidad B(i), donde
m(i) es el numero de atributos de la entidad B(i), ysuponiendo una especializacion disjunta y completa,
para cada entidad de bajo nivel B(i) se crea una relacionB − i con cabecera
{a1 . . . an} ∪ {b(i)1 , . . . b(i)m(i)}
y clave primaria
CP (B − i) = {ai . . . aj}
Si en esta conversion se aplica para los casos:
Especializacion ⇒ REDUNDANCIAsolapada
Especializacion ⇒ Perdida de informacionno completa (tuplas no representadas)
25
Ejemplo: Entidad de alto nivel CUENTA con entidades de nivel bajoCUENTA-CORRIENTE y CUENTA-AHORRO, con generalizacionDISJUNTA y COMPLETA.
Se forman las relaciones siguientes:
CUENTA-AHORRO(Numero-Cuenta,Saldo,Tipo-Interes)
CUENTA-CORRIENTE(Numero-Cuenta,Saldo,Descubierto)
conCP (CUENTA-AHORRO) =
CP (CUENTA-CORRIENTE) = Numero-Cuenta
Esquema relacional:
26
(f) Conversion de agregaciones
Proposicion: Sea AG una interrelacion con atributosb1 . . . bw entre la entidad A(a1 . . . an) con CP (A) = (ai . . . aj)y la agregacion G que consta de
• E1 . . . En, una coleccion de entidades dentro G.
• R1 . . . Rm una coleccion de interrelaciones en G, dondela union R′(r′1 . . . r′s) con CP (R′) = (r′r . . . r′t) s’inter-relaciona con AG.
La interrelacion AG se representa mediante las siguien-tes relaciones:
• Por cada entidad Ei en G se crea una relacion E− i.
• Por cada interrelacion Rj en G se crea una relacionR− j.
• Se define una relacion AG con cabecera
{ai . . . aj} ∪ {r′r . . . r′t} ∪ {b1 . . . bw}
clave primaria
CP (AG) = {ai . . . aj} ∪ {r′r . . . r′t}= CP (A) ∪ CP (R′)
y claves externas
CE(AG → Ri) = {r′p . . . r′q} = CP (Ri)∀y = 1 . . . m p, q ∈ {r . . . t}
CE(AG → A) = {ai . . . aj} = CP (A)
27
Ejemplo: Interrelacion RESPONSABLE-PRESTAMO entre la en-
tidad EMPLEADO y la agregacion CPP.
Esquema relacional:
28
Ejemplos de diseno logico
Ejemplo 1.1: Secretaria Universidad
Esquema E-R:
Esquema Relacional:
29
Ejemplo 1.2: Secretaria Universidad
Esquema E-R:
Esquema Relacional:
30
Ejemplo 1.3: Secretaria Universidad
Esquema E-R:
Esquema Relacional:
31
Ejemplo 2: Companıa transportista.
Esquema E-R:
Esquema Relacional:
32
Ejemplo 3: BD de una liga de baloncesto.
Esquema E-R:
Esquema Relacional:
33
Ejemplo 4: Companıa de discos.
Esquema E-R:
Esquema Relacional:
34
Ejemplo 5: Catastro municipal.
Esquema E-R:
Esquema Relacional:
35
Ejemplo 6: Hospital
Esquema E-R:
Esquema Relacional:
36
Ejemplo 7: Universidad
Esquema E-R:
Esquema Relacional:
37
4. Diseno fısico
Definicion de los ficheros y estructuras de almacena-je correspondientes al conjunto de relaciones.
Estructures de almacenamiento sobre los ficheros mejo-ran el tiempo de acceso a la informacion.
Estructuras de almacenamiento (indexacion, tablas ha-shing) sobre ficheros en funcion del numero de consultasque se hagan a los diferentes campos de informacion,segun las especificaciones de diseno.
Conceptos ampliados en el TEMA 7.
Ejemplo BD bancaria:
• FICHEROS: Un fichero por cada relacion.
El conjunto de campos de informacion del ficherocorresponde a los atributos de la relacion que re-presenta.
• ESTRUCTURAS DE ALMACENAJE: Segun nom-bre y tipos de transacciones a los ficheros.
38