diseño de bases de datos relacionales john freddy duitama muñoz facultad de ingeniería u.de.a....
TRANSCRIPT
Diseño de Bases de Datos
Relacionales
John Freddy Duitama MuñozFacultad de Ingeniería
U.de.A.
Relacionales
John Freddy Duitama MuñozFacultad de Ingeniería
U.de.A.
Esta presentación puede ser usada solo para fines académicos y mencionando siempre al autor.
John Freddy Duitama M.Universidad de Antioquia.Facultad de Ingeniería.
Normalización La Normalización, abarca dos tópicos:
Dependencia Funcional: Técnica de diseño que permite examinar las relaciones entre los atributos.
Formas Normales: Pruebas para el agrupamiento óptimo de los atributos.
Con la normalización se pretende que: Los atributos con una relación lógica fuerte
(dependencia funcional) se encuentren en la misma relación.
Definir el número mínimo de atributos necesarios para soportar requisitos de datos de una organización.
Las relaciones tengan una redundancia mínima. Cada atributo se representa una sola vez, con excepción de las claves foráneas. Actualización con un mínimo de operaciones.
Reduce posibles inconsistencias de datos.
Reduce espacio de almacenamiento.
DEPENDENCIA FUNCIONALDEF: Sean y atributos de la relación R.
Decimos que determina funcionalmente a en R, denotado por
Tambien se puede decir que:depende funcionalmente de
Si y sólo si :Para todos los pares de tuplas t1, t2 de la relación R, tales que t1[] = t2[] también se cumple que t1[ ] = t2[]
Ejemplo: cédula --> nombre.Cada vez que se tiene un número de cédula, esta debe coincidir con el mismo nombre.
Si t1 y t2 coinciden en el atributo ,Entonces deben coincidir también en el atributo .
CLAVESSea K un conjunto de uno o más atributos de la
relación R.
DEF: K es una clave para la relación R si:
Si K todos los atributos de R;
Ningún subconjunto de K determina funcionalmente a todos los demás atributos de R. Es decir, la clave debe de ser mínima.
DEP. FUNCIONAL COMPLETADEF: Sean y atributos de la relación R.
Decimos que depende funcionalmente de manera completa de
Si y sólo si: depende funcionalmente de pero no de ningún subconjunto propio de .
Es decir, Una dependencia funcional es completa si la eliminación de cualquier atributo de hace que la dependencia deje de existir.
Cedula, nombre salario Si se quita el nombre la dependencia continúa
Cedula salario Entonces no era completa
Sean las relaciones:Préstamo (sucursal, número-préstamo, nombre-cliente, valor)Cliente (nombre-cliente, calle, ciudad )deposito ( nombre-sucursal, cuenta, nombre-cliente, saldo)
Si Número-préstamo --> nombre-cliente. Un préstamo sólo puede efectuarse a un cliente. Un cliente puede tener varios préstamos.
Número-préstamo-->valor es cierta en préstamo?calle --> ciudad es cierta en cliente?Cuenta --> saldo es cierta en depósito ?Saldo --> cuenta es cierta en depósito ?
Ejemplos de dependencias Funcionales
El diseño de una Base de Datos relacional requiere definir aquellas dependencias funcionales (D.F.) que se deben cumplir siempre.
Se llaman triviales a las dependencias que se
satisfacen en todas las relaciones.
1. A A
cedula cedula
1. Si y son conjuntos de atributos y
entonces se cumple que
Dependencias Triviales
1. Reglas de reflexividad: (dependencia trivial)Si y son conjuntos de atributos y entonces se cumple que
2. Regla de aumento: Si para los conjuntos de atributos y se cumple que --> y es un conjunto de atributos, entonces se cumple que --> .
(cedula, telefono) (nombre, telefono)
3. Regla de la transitividad: Si se cumple --> y se cumple --> , entonces se cumple -- > .
cedula_cliente numero_cuentanumero_cuenta nombre_conyugecedula_cliente nombre_conyuge
Axiomas de Armstrong
Reglas adicionales - ArmstrongReglas adicionales, derivadas de las anteriores :4. Regla de unión:
Si se cumple --> y --> se cumple --> Cédula nombre y cédula teléfono
Cédula (nombre , teléfono)
Regla de la descomposición: Si se cumple --> entonces se cumple --> y --
> cédula (apellido, dirección)
cédula apellido y cedula dirección
6. Regla de la pseudo-transitividad: Si --> y --> entonces se cumple -->
Cédula Ciudad_residencia
(Teléfono, Ciudad_residencia) dirección_residencia
(Cédula,Teléfono) dirección_residencia
Implicación lógica de las D.F.
Ejemplo :
Sea la relación R (A, B, C, G, H, I) Con el conjunto de Dependencias Funcionales
F={ A B, A C, CG I, CG H, B H }
Puedo hallar nuevas dependencias funcionales implicadas lógicamente por F:
a. A B y B H luego : A H. por axioma-3.b. CG H y CG I luego CG HI por axioma-4c. A C luego AG CG por axioma-2d. AG CG y CG I luego AG I por axioma-3e. AG CG y CG H luego AG H por axioma-3.
Objetivo: Almacenar la información con un mínimo de redundancia y fácil recuperación.
Problemas: Repetición de la información.1. Representación de la información 2. Pérdida de información.
Problemas en el diseño de una B. de D.
nombre-sucursal activos ciudad número-préstamo nombre-cliente valor
Centro 9’000.000
Arganzuela 17 Santos 1.000
Moralzarzal 2’100.000
La Granja 23 Gómez 2.000
Navacerrada 1’700.000
Aluche 15 López 1.500
Centro 9’000.000
Arganzuela 14 Sotoca 1.500
Becerril 400.000 Aluche 93 Santos 500
Collado Mediano 8’000.000
Aluche 11 Abril 900•Qué ocurre al agregar un préstamo ?•Qué ocurre si una sucursal cambia de activos ?•Qué ocurre con las sucursales que no tengan préstamos?•Qué ocurre si eliminamos el último préstamo de una sucursal?
Prestamo
En otras palabras :Una sucursal existe independiente de los préstamos que haga.
Una sucursal está situada exclusivamente en una ciudad.una sucursal tiene solo un valor total de activos.
Una sucursal puede efectuar muchos préstamos.Un préstamo solo se otorga en una sucursal.
Solución:Sucursal (nombre-sucursal, activos, ciudad)
Préstamo (número-préstamo, nombre-cliente, valor, nombre-sucursal)
Problemas en el diseño de una B. de D.
Cómo descomponer una relación en variasObjetivo: evitar la pérdida de información.
Cómo descomponer la relación préstamos en varias relaciones sin pérdida de información?préstamo (nombre-sucursal, activos, ciudad, número-préstamo, nombre-cliente, valor)
Sean:Sucursal (nombre-sucursal, activos, ciudad, valor)Préstamos (número-préstamo, nombre-cliente, valor) Dos proyecciones de la relación original, nótese que valor actúa como si fuera clave foránea.
Qué ocurre si pretendo reconstruir a préstamo?
Si hay varios préstamos con el mismo valor; significa que no podemos identificar a qué sucursal corresponde que préstamo.
Descomposición sin pérdidaSea R una relación.
Una descomposición {R1, R2, ..., Rn} de R es una descomposición de producto sin pérdida
si :R = R1(R) R2(R) Rn (R)
Se debe Verificar:R1 y R2 forman una descomposición sin pérdida de R, si por lo
menos una de las D.F. siguientes se cumple:R1 R2 --> R1.R1 R2 --> R2.
Veamos un Ejemplo:
Ejemplo de descomposición sin pérdidaPrestar (nombre-sucursal, activo, ciudad, préstamo, valor, nombre-
cliente)
F= { nombre-sucursal activo, nombre-sucursal ciudad, préstamo nombre-cliente, valor, nombre-sucursal}
Si la descomponemos en :Sucursal (nombre-sucursal, activo, ciudad)Préstamo (nombre-sucursal, préstamo, nombre-cliente, valor)
Debemos probar :Sucursal préstamo Sucursal es decir: nombre-sucursal nombre-sucursal, activo, ciudad.
Por unión : nombre-sucursal activo, ciudadPor aumento : nombre-sucursal nombre-sucursal, activo, ciudad.
NORMALIZACIÓN
Normalización Es la técnica utilizada para diseñar
“buenas” relaciones desde el punto de vista de: Minimizar la redundancia Minimizar el mantenimiento de datos Minimizar el impacto de futuros cambios de
datos e ingreso de información
Anomalías de Inserción
Anomalías de Actualizacióny Borrado
Anomalías
Supóngase la relación:Envío
sede_ppal nit producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No nit es del proveedor
CP
Anomalía¿ Supongamos un proveedor que nos suministra
100 productos cambia de sede, que implicaciones trae esto?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
¿ Suponga que hay 50 proveedores de “leche” y que ésta se vuelve un producto con IVA,
que implicaciones trae esto?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
¿ Qué pasa si queremos ingresar un proveedor que todavía no nos ha suministrado algún producto pero deseamos registrar sus datos?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
¿Qué pasa si en el almacén ya no desean vender chicha pero desean preservar la información del proveedor 128?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
¿Qué pasa si en el almacén ya no desean negociar con el proveedor 128? ¿Qué pasa con la información del producto chicha?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
¿Cuántas veces dice la relación envio donde está situado cada proveedor?
¿Cuántas veces dice la relación envio si un producto está gravado o no con IVA?
Envíosede_ppal #nit #producto cantidad con_iva
Med 101 Leche 10 NoBog 201 Chorizo 29 SiMed 101 Yogur 12 SiMed 101 Pasas 100 No
Bog 201 Leche 12 No Bog 201 Pasas 100 No Med 128 Chicha 10 No
Todo lo anterior indica que aunque la relación representa el negocio, posee muchos problemas
La idea de la normalización es “producir” relaciones que representen el negocio pero que al mismo tiempo eviten (en lo posible) anomalías como las anteriores
FORMAS NORMALES
6 formas normales clásicas:1NF, 2NF, 3NF, BCNF (Boyce Codd Normal Form), 4NF, 5NF
Mientras una relación esté en una forma normal más alta “mucho mejor”
Generalmente se acepta normalizar hasta BCNF
Las formas normales 4 y 5 son casos “extremos”
Si una relación cumple una forma normal nn automáticamente cumplirá las n-1n-1 formas normales anteriores, es decir, cada forma normal es “más fuerte” que sus predecesoras.
El análisis de 1NF, 2NF y 3NF está considerado sólo para relaciones con una sola clave candidata.
Para relaciones con más de 1 clave candidata directamente se aplica BCNF
Primera Forma Normal : 1FNDominio Atómico. Los elementos del dominio son indivisibles.
Primera Forma normal : 1FNUna relación está en primera forma normal si y sólo si todos los dominios de los atributos son atómicos.
Ejemplos:Venta (número-fac, cliente, producto[i], unidades[i] )No está en primera forma normal.
Posible solución:Venta (número-fac, producto, unidades, producto)
Primera Forma Normal : 1FN
Primera Forma normal : 1FNUna relación está en primera forma normal si y sólo si todos los dominios de los atributos son atómicos.
Empleado (código, nombre, teléfono) código = 016-242224 donde
016 = departamento 242224 = código empleado
No está en primera forma normal.
Posible solución: Empleado(departamento, cod-empleado, nombre, teléfono) clave primaria(departamento, cod-empleado)
Segunda Forma Normal: 2FNSegunda Forma normal: 2FN
Una relación está en 2FN, si y sólo si está en 1FN y todos los
atributos no clave dependen funcionalmente de manera
completa (DFC) de la clave primaria.
Ejemplo, sea la relación : venta(número-factura, producto, cliente, unidades)
clave primaria: número-fac, producto.
¿Venta cumple la 1FN?
¿El Cliente DFC de la clave primaria?
¿Las unidades DFC de la clave primaria?
Ejemplo de Segunda Forma Normal
¿Las unidades DFC de la clave primaria?
(número-fac, producto) unidades
Comprobar si al quitar alguno de los atributos del lado izquierdo, se conserva la dependencia funcional.
número-fac unidades F
producto unidades F
Al quitar el atributo producto o el número-fac la
dependencia NO se conserva, entonces (número-fac ,
producto) si DFC a unidades. Sin embargo, falta
comprobar…
Ejemplo de Segunda Forma Normal
¿El Cliente DFC de la clave primaria?(número-fac , producto) cliente
Comprobamosnúmero-fac cliente V
producto cliente F
Al quitar el atributo producto, la dependencia se conserva,
entonces (número-fac , producto) NO DFC a cliente.
Es decir (número-fac , producto) cliente de manera
parcial. Entonces no se cumple la 2NF
Ejemplo de Segunda Forma Normal
Posible solución:
Dependencias funcionales completas:número-fac cliente número-fac, producto unidades
Se descompone en:
Factura (#número-fac, cliente)
Venta (#número-fac, #producto, unidades)numero-fac clave foránea de Factura
Tercera Forma normal: 3 FNUna relación está en 3FN si y solo si está en 2FN y todos los atributos no claves dependen de manera directa de la clave primaria.
Equivalentemente.Una relación está en 3FN si y sólo si los atributos no clave son: Mutuamente independientes. Dependen por completo de la clave primaria.
Dicho de otro modo:
R(A,B,C) con clave primaria A.
R.B --> R.C y R.A-->R.Bse descompone en:R1(B,C) con clave primaria B.R2(A,B) con clave primaria A y B clave ajena de R1.
Tercera Forma Normal: 3FN
Cuando no existen dependencias transitivas, que se dan cuando un atributo que no es parte de la clave primaria depende de otros atributos que
tampoco son clave primaria.
Ejemplo de Tercera Forma Normal
R(número-fac, cliente, fecha-fac, teléfono-cliente)
Con: número-fac --> clientenúmero-fac --> fecha-faccliente --> teléfono-clienteClave primaria: número-fac
Se descompone en :
R1(cliente, teléfono-cliente) clave primaria(cliente)
R2(número-fac, cliente, fecha-fac)clave primaria (número-fac); cliente clave ajena de R1.
Forma Normal Boyce/Codd
Forma normal Boyce/Codd: FNBC
Una relación está en FNBC, si cumple la 3FN, y si y solo si cada
determinante, atributo o conjunto de atributos que determina
completamente a otro, es clave candidata.
Class_Code y Enroll_grade dependen funcionalmente de manera
completa de (Stu_id, Staff_id); sin embargo, staff_id depende de
class_code.
El propósito de la tabla es mostrar qué tutores están asign
Stu_id Staff_id Class_code Enroll_grade
Ejemplo
Stu_id Staff_id Class_code Enroll_grade
Class_Code es un determinante que no es clave candidata.
Stu_id Staff_id Class_code Enroll_grade
125 25 21334 A
125 20 32456 C
135 20 28458 B
144 25 27563 C
144 20 32456 B
¿Qué pasa si se asigna otro profesor para la clase 32456?¿Qué pasa si el estudiante 135 deja la clase 28458?
¿Cómo se soluciona?
A B C D
A C B D
A C D C B
Stu_id Class_code Enroll_grade Class_code Staff_id
PROBLEMA:Sea: asesor (sucursal, nombre-cliente, nombre-asesor) F = { nombre-asesor sucursal, sucursal, nombre-cliente nombre-asesor}
Asesor no está en BCNF.Como descomponer asesor para hallar dos relaciones en
BCNF?
R/ Ninguna descomposición BCNF de esta relación conserva todas las dependencias originales.
SLN: Debo abandonar BCNF para conservar las dependencias.
Conservación de dependencias en BCNF
En general la 4FN La definición de la 4NF
confía en la noción de una dependencia multivalor.
Una tabla con una dependencia multivalor es una donde la existencia de dos o más relaciones independientes muchos a muchos causa redundancia; y es esta redundancia la que es suprimida por la cuarta forma normal.
Sucursal Empleado Propietario
B003 Ann Beech Carol Farrel
B003 David Ford Carol Farrel
B003 Ann Beech Tina Murphy
B003 David Ford Tina Murphy
Solución: Colocar los atributos en una nueva relación junto con una
copia de los determinantes.
Sucursal Empleado
B003 Ann Beech
B003 David Ford
Sucursal Propietario
B003 Carol Farrel
B003 Tina Murphy
Finalmente la 5FN También conocida como forma normal de
proyección-unión (PJ/NF).
Para detalles de esta forma, se recomienda la lectura de:
http://es.wikipedia.org/wiki/5NF
44
BIBLIOGRAFÍA Thomas M. Connolly, Carolyn E Begg. Sistemas de bases de
datos. Un enfoque práctico para diseño, implementación y gestión. Cuarta edición. Pearson Addison-Wesley 2005.
Peter Rob / Carlos Coronel. Sistemas de bases de datos. Diseño, implementación y administración. International thomson editores. 2004.
C.J. Date. Introducción a los sistemas de Bases de Datos. Sexta edición. Volúmen 1. Addison-Wesley. 1995.
Jeffrey D. Ullman. Principles of Database and Knowledge-Base System. Volúmenes I. Computer Science Press. 1988. Capítulo 7.
Henry F. Korth, Abraham Silberschatz. Fundamentos de Bases de Datos. Tercera edición. 1998.