Clase Fundamentos De Bases De Datos

Diseño de Base de Datos Relacionales

1

¿Que es una Base de datos?  Conjunto unificado de información, que

será compartida por diferentes usuarios de la organización  La función básica de una base de datos es permitir el almacenamiento y la recuperación de la información necesaria, para que las personas de la organización puedan tomar decisiones. 2

Diseño de una BD 

Los modelos conceptuales se basan en un criterio intuitivo de lo que ocurre en el mundo real y como este mundo real debe ser modelado



Si bien son intuitivos, son de fácil aplicación y además muy efectivos en sus resultados

3

Diseño de una BD  Central al diseño relacional es la idea de

dependencia de datos  “Un modelo mal diseñado se transforma

en el talón de aquiles de un sistema.”

4

Diseño de una BD 

La Teoría es útil  





Como control de nuestros diseños Para comprender algunas cosas que no pueden ser representadas en el Modelo ER Para comprender las consecuencias de conceptos tales como redundancia (que puede ser utilizado para mejorar la eficiencia de los sistemas)

Distinguimos un conjunto (incompleto) de estas restricciones para el modelo, que pueden implementarse en varios SGBD relaciones actualmente en el mercado 5

Restricciones de Integridad  

Restricciones de Dominio Restricciones de Integridad Referencial 

 



Test que permite controlar operaciones de actualización Inserción, Borrado (cascada), Modificación Clave

Aserciones  

Predicados que debe satisfacer la base de datos El sistema controla su validez lo que puede ocasionar una gran sobrecarga si no se utiliza con cuidado 6

Restricciones de Integridad 

Disparadores 



Acciones que se ejecutan como efecto lateral ante una operación Deben especificarse: • Bajo qué operaciones tendrían que ejecutarse • Cuáles son las acciones que debería realizar



Dependencias Funcionales 



El valor de un conjunto de atributos determina unívocamente el valor de otro conjunto de atributos Noción como generalización de clave

7

Claves de un Esquema  Clave o Llave 



Conjunto de atributos de un esquema que permite distinguir unívocamente las tuplas de una relación. Si el conjunto tiene un subconjunto propio con la misma propiedad lo llamamos SuperClave

 En término de DF 

K es clave de R, si K ⊆ R y K 8

¿Qué es un mal diseño? 

Un mal diseño lleva a: Repetir Información Inhabilidad para representar cierta información

 



Supongamos un esquema Proveedor ( Nombre_Proveedor, Dirección_Proveedor, Item, Item_Precio_Unitario )



Problemas en el diseño: 

Redundancia • La dirección del Proveedor se repite por cada item provisto 9

¿Qué es un mal diseño?  Problemas en el diseño: 

Potencial inconsistencia (anomalías de actualización) • ¿cómo actualizamos la dirección de un proveedor?



Anomalías de Inserción • ¿cuál es la dirección de un proveedor qué aún no ha provisto items?



Anomalías de Borrado • Si borro el último item provisto, ¿cómo guardo la dirección del proveedor? 10

Un Buen Diseño  Objetivos de un buen diseño  



Evitar datos redundantes Asegurar que se representen las relaciones entre los datos Facilitar el control de que las actualizaciones de la base de datos resulten en una violación de las restricciones de integridad

11

¿Qué es un mal diseño?  Corrección del problema 

Reemplazar el esquema con dos nuevos esquemas:

Proveedor ( Nombre_Proveedor, Dirección_Proveedor) Provee ( Nombre_Proveedor, Item, Item_Precio_Unitario )

 Veamos como soluciona los problemas  

Redundancia Anomalías (Inserción, Modificación y Borrado) 12

¿Qué es un mal diseño?  Nuevo Problema 



¿Cómo recupero la información inicial? (cuando los datos estaban en una sola tabla) Utilizando la operación JOIN

Nueva_Proveedor = Proveedor ►◄ Provee Nueva_Proveedor ( Nombre_Proveedor, Dirección_Proveedor, Item, Item_Precio_Unitario)

 Ejemplo con Valores 13

Buen Diseño: Pautas informales  Conocer la semántica de los atributos 



Diseñe un esquema de relación de modo que sea fácil de explicar su significado. No combine atributos de varios tipos de entidades y tipos de relaciones (modelo ER) en una sola relación (modelo relacional)

 Reducir los valores redundantes en las

tuplas 

Diseñe los esquemas de las relaciones de modo que no haya anomalías de actualización

14

Buen Diseño: Pautas informales 

Reducir los valores nulos en las tuplas 



Hasta donde sea posible, evite incluir en una relación atributos cuyos valores puedan ser nulos. Si no es posible evitar los nulos, asegúrese de que se apliquen sólo en casos excepcionales y no a la mayoría de las tuplas de una relación

Prohibir tuplas espurias 

Diseñe los esquemas de relación de manera que puedan reunirse mediante condiciones de igualdad sobre atributos que sean claves primarias o candidatas, a fin de garantizar que no se formarán tuplas espurias 15

Tuplas espurias o erróneas  Supongamos el esquema Estudiante (Legajo, Nombre, Ciudad, Provincia)

Legajo 10 20 30 40

Nombre Jorge Analía Carlos María

Ciudad Neuquén Plottier Cipolletti Centenario

Provincia Neuquén Neuquén Río Negro Neuquén 16

Tuplas espurias o erróneas  Solución al problema de redundancia

Estudiante (Legajo, Nombre, Provincia) 

Dirección (Provincia, Ciudad)

Legajo Nombre Provincia 10 Jorge Neuquén 20 Analía Neuquén 30 Carlos Río Negro 40 María Neuquén

Ciudad Neuquén Plottier Centenario Cipolletti

Provincia Neuquén Neuquén Neuquén Río Negro 17

Tuplas espurias o erróneas Legajo 10 20 30 40

Nombre Jorge Analía Carlos María Legajo 10 10 10 20 20 20 30 40 40 40

Provincia Neuquén Neuquén Río Negro Neuquén Nombre Jorge Jorge Jorge Analía Analía Analía Carlos María María María

►◄

Provincia Neuquén Neuquén Neuquén Neuquén Neuquén Neuquén Río Negro Neuquén Neuquén Neuquén

Ciudad Neuquén Plottier Centenario Cipolletti Ciudad Neuquén Plottier Centenario Neuquén Plottier Centenario Cipolletti Neuquén Plottier Centenario

Provincia Neuquén Neuquén Neuquén Río Negro

Tuplas Espurias

18

Tuplas espurias o erróneas   



Se trata de las tuplas adicionales que no estaban en la relación original Representan información errónea o agregada que no es válida Se obtiene porque el join se realizó entre dos tablas cuyos esquemas no fueron elegidos como un “buen” diseño Esto se debe a que el atributo que relaciona ambos esquemas no es una clave primaria o clave candidata en cualquiera de las relaciones 19

Preservación de Dependencias 

 



 

Si actualizamos una relación de una BD. ¿Podemos fácilmente controlar si una dependencia funcional X→Y es violada? Sólo podríamos hacerlo si X ∪Y están contenidos en algún conjunto de atributos La proyección de un conjunto de dependencias funcionales F en un conjunto de atributos Z, FZ es {X→Y | X→Y∈F + and X ∪Y ∈Z } Una decomposición R1, …, Rk preserva dependencias si F + = ( FR1 ∪ ... ∪ FRk ) + Lo que significa que la descomposición no ha perdido alguna dependencia funcional esencial. Conservaríamos así las restricciones de integridad de la relación R original. 20

Ejemplo de Preservación 

El esquema de relación Proveedor 



Conjunto de dependencias funcionales   







Nombre → Dirección, Ciudad Dirección, Ciudad → CP Nombre,Item → Precio

Considerar la descomposición 

 

{ Nombre, Dirección, Ciudad, CP, Item, Precio }

{Nombre, Dirección, Ciudad, CP} {Nombre, Item, Precio}

¿Es Join sin Pérdida? ¿Preserva dependencias? ¿Cómo cambia si se reemplaza la primera dependencia con Nombre, Dirección → Ciudad ? 21

Ejemplo de Preservación II 

Esquema: 



Conjunto de DF:  







profesor → tema estudiante, tema → profesor

La Descomposición: 



{estudiante, profesor, tema}

{estudiante, profesor} {profesor, tema}

¿Es join sin pérdida? ¿Tiene preservación de dependencias? 22

Formas Normales 

Definición de Forma Normal 





Es una restricción al esquema de BD que presumible-mente evita ciertas propiedades indeseables en la BD. Se definen primero para un esquema de relación en el esquema de la BD y se extienden hasta aplicar al esquema de BD como un todo

1º FN 



Un esquema relacional R está en primera forma normal (1FN) si el valor de dom(A) para cada atributo de R es atómico Los valores del dominio no pueden ser listas, conjunto de valores o valores compuestos.

23

1era Forma Normal

Atributos ery: (Encontrar todos los profesores de Física) Multivaluados Profes or ID# Nombre Apellido Módulos 12345

Fred

Jones

IT, Business, History

23456

Sarah

Smith

Chemistry, Physics, IT

34567

Joe

Clegg

Maths, Physics, Art

45678

Mary

WindsorMaths, Physics, Geography

56789

Edward Griswald Maths, PE, Gen Studies

67890

Cyril CoombsHistory, French, PE Una relación está en 1º Forma Normal si no tiene 78901 Mei Ti Quinn Chemistry, Biology atributos con valores multivaluados ...

...

...

...

24

1era forma Normal

Atomic Attributes (still search multiple column Profes or ID# Nombre Apellido Modulo 1 Modulo 2 Modulo 3 12345

Fred

Jones

IT

Business History

23456

Sarah

Smith

Chemistry

Physics

34567

Joe

Clegg

Maths

Physics

Art

45678

Mary

WindsorMaths

Physics

Geography

56789

Edward GriswaldMaths

PE

Gen Studies

67890

Cyril

Coombs History

French

PE

78901

Mei Ti

Quinn

Chemistry

...

...

...

...

IT

Biology 25

Formas Normales 

Dependencia Funcional Parcial / Total  





Sea F un conjunto de DF y X → Y en F+. Y es parcialmente dependiente de X en F si X → Y no es reducido a izquierdo, ie ∃ X’ ⊂ X / X’→ Y ∈ F+. Y es totalmente dependiente de X en F si X → Y es reducido a izquierdo, ie ¬∃ X’ ⊂ X / X’→ Y ∈ F

Atributo Primario o Primo 

Sea R un esquema de Relación, y A ∈ R, F un conjunto de DF sobre R. A es primo en R con respecto a F si A pertenece a alguna clave de R. De lo contrario A es no primo. 26

Formas Normales 

2º FN 





Un esquema relacional R está en segunda forma normal (2FN) cuando todos los atributos no primos tienen dependencia total respecto de cada una de las claves. Un esquema de base de datos está en segunda forma normal respecto a un conjunto de dependencias funcionales F si cada esquema de relación Ri en R está en 2FN con respecto a F. Ejemplo

27

2da Forma Normal

No hay grupos repetitivos, pero hay problema Profes or ID# Nombre Apellido Módulo La Información 12345 Fred Jones IT está si duplicada. Una relación está Forma Normal está en 12345 Fred Jonesen 2ºBusiness 12345 23456 23456 23456 34567 34567 34567 45678 45678 ...

Fred Jones History 1º Forma Normal y ningún atributo no primo es Sarah Smith Chemistry (La información está Sarah de Smith Physics dependiente una parte de una clave. contenida en las Sarah Joe Joe Joe Mary Mary ...

Smith Clegg Clegg Clegg Windsor Windsor ...

IT Maths Physics Art Maths Physics ...

correspondencias entre atributos) Depende de ID# (parte de la clave primaria) 28

28

2da Forma Normal

Profes or ID# Nombre 12345 23456 34567 45678 ...

Fred Sarah Joe Mary ...

Apellido Jones Smith Clegg Windsor ...

Módul o ID# Módulo BS001Business HI001 History PH002Physics ... ...

En esta propuesta, las tablas representan “cosas” o conceptos que tienen claro significado. Por un lado el “Profesor”, por el otro los “Módulos”. Estas son las “entidades” sobre las cuales se ha hablado en la modelización conceptual. 29

2º Forma Normal Profes or ID# Nombre 12345 23456 34567 45678 ...

Fred Sarah Joe Mary ...

Apellido Jones Smith Clegg Windsor ...

Módul o ID# Módulo BS001Business HI001 History PH002Physics ... ...

Profesor/Mód ulo TID# MID# 12345 12345 12345 23456 23456 23456 34567 34567 34567 45678 45678 ...

IT003 BS001 HI001 CH002 PH002 IT003 MA002 PH002 AR001 MA002 PH002 ... 30

Formas Normales 

Dependencia Funcional Transitiva 



Sea F un conjunto de DF y X → Y en F+, y A un atributo de R. A es transitivamente dependiente de X en R con respecto a F si hay un subconjunto Y de R tal que X → Y ∈ F y ¬(Y → X) y Y → A sobre F y A ∉ XY. • Ejemplo



3º FN 

Un esquema relacional R está en tercera forma normal (3FN) cuando está en 1FN y no hay atributos no primos en R que tengan dependencia transitiva respecto de cada una de las claves de R.

31

Formas Normales 

3º FN 

 

Un esquema de base de datos está en tercera forma normal con respecto a F si cada esquema de relación Ri de R está en tercera forma normal con respecto a F. Ejemplo El esquema relacional R está en 3FN si para cada dependencia funcional X → A que vale en R, se cumple lo siguiente: • A ∈ X (trivial), • X es una superclave para el esquema R, o • A es miembro de alguna de las claves de R 32

Formas Normales 

3º FN 



Un esquema de base de datos está en tercera forma normal respecto a un conjunto de dependencias funcionales F si cada esquema de relación Ri en R está en 3FN con respecto a F.

Teorema 



Cualquier esquema de relación R que está en 3FN con respecto al conjunto de dependencias funcionales F está también en 2FN con respecto a F. Demostración: Una dependencia parcial implica una dependencia transitiva. 33

3ra Forma Normal Estos atributos no dependen de ID#

Profes or ID# Nombre Módulo 12345 12121 11345 26456 21156 22256 35667 31367 11167 21378 12178 ...

Jones Brown Paul Sylvester Mike Smith Clegg Marshall Dominic Windsor Matt ...

Nombre del Módulo Information Technology Business Studies History of Art Chemistry Physics IT Maths Physics Art Maths Physics ...

Una relación está en 3º Forma Normal si está en 2º Forma Normal y no existen atributos no primos que dependen transitivamente de alguna de las claves. IT Bu Hi Ch Ph IT M a Ph Ar M a Ph

34

Formas Normales 

Forma Normal de Boyce Codd 







El esquema relacional R está en FNBC si para cada dependencia funcional X → Y no trivial que vale en R, X es una superclave para el esquema Por definición, un esquema en FNBC también está en 3FN

FNBC es la más deseable de las restricciones de una descomposición pero puede demostrarse que no siempre puede obtenerse esta descomposición y mantener todas las propiedades (LJ y PD) FNBC es más estricta que 3FN.

35

Formas Normales 



Siempre es posible partir de un esquema de relación R que no se encuentra en 3FN con respecto a un conjunto F de dependencias funcionales y descomponerlo en un esquema de base de datos relacional que está en 3FN con respecto a F Descomponer un esquema de base de datos relacional significa “romper” el esquema relacional inicial en un par de esquemas R1 y R2 (posiblemente que se intersectan) tales que la relación r(R) que satisface a F se descompone sin pérdida en R1 y R2. Es decir 



πR1 (r)

πR2 (r) = r

Podemos repetir el proceso en cada Ri si alguno de ellos no ha alcanzado la forma normal buscada.

36

Relación de Formas Normales

37

Normalización Forma Original No Normalizada 1NF

Ejemplo de Pasos de una propuesta del proceso de Normalización

1º Forma Normal

2NF

2º Forma Normal

3NF

3º Forma Normal

38

Proceso de Normalización 





Técnica formal para analizar una relación basada en sus claves primarias y las dependencias funcionales que existen entre los atributos del esquema. Usualmente se ejecuta en una serie de pasos. Cada paso corresponde a una forma normal específica (es posible), con propiedades conocidas. En la medida que avanza el proceso, las relaciones se hacen más restringidas (más fuertes) en su formato y también menos 39 39

Proceso de Normalización

40

Algoritmo de Descomposición BCNF resultado:= {R} Hecho:= false Calcular F+ Mientras (not Hecho) do Si (Algún esquema S no está en BCNF) Entonces Sea A → B una df no trivial válida en S tal que A → S no pertenece a F+, A y B disjuntos resultado:= (resultado - S) ∪ (S -B) ∪ {AB} Sino Hecho:=true Fin Mientras 41

Algoritmo de Descomposición 3FN Sea F un cubrimiento mínimo i:=0 Para cada A → B en F hacer Si ningún esquema Rj 1≤ j ≤ i contiene AB Entonces i:= i+1 Ri:= {A,B} Si ningún esquema Rj, 1 ≤ j ≤ i contiene una clave para R Entonces i:= i+1 Ri:= alguna clave para R

42

Resumen 





Siempre existe una descomposición en 3NF con las propiedades de join sin pérdida y preservación de dependencias. Siempre es posible encontrar una descomposición en FNBC que sea join sin pérdida, pero no siempre es posible encontrar alguna que preserva todas las dependencias. Ejemplo de Descomposición

43

Algoritmo: Determinación de Clave 

Definiciones Previas   



Atributos Esenciales: sólo lado izquierdo Atributos Posibles: ambos lados Atributos No Esenciales: sólo lado derecho

Propiedades:   

Un atributo esencial pertenece a cualquier clave Un atributo no esencial no pertenece a ninguna clave Para un atributo posible no es posible afirmar si pertenece o no a alguna clave, excepto si esta incluido en la clausura del conjunto de atributos esenciales. En ese caso, no pertenece a alguna clave 44

Metas del Proceso de Normalización   

Obtener los atributos correctos (Buscar los atributos correctos) Obtener las entidades correctas (Dependencias funcionales) Obtener las relaciones correctas (Dependencias Multivaluadas)

45

Proceso de Normalización 

En términos de las formas normales, la normalización se expresa como: 





1ra Forma Normal elimina los grupos repetitivos. 2da + 3ra Forma Normal elimina todas las dependencias funcionales entre los atributos no primos. 4ta Forma Normal elimina todas las dependencias multivaludas 46

1era Forma Normal

Atributos ery: (Encontrar todos los profesores de Física) Multivaluados Profes or ID# Nombre Apellido Módulos 12345

Fred

Jones

IT, Business, History

23456

Sarah

Smith

Chemistry, Physics, IT

34567

Joe

Clegg

Maths, Physics, Art

45678

Mary

WindsorMaths, Physics, Geography

56789

Edward Griswald Maths, PE, Gen Studies

67890

Cyril CoombsHistory, French, PE Una relación está en 1º Forma Normal si no tiene 78901 Mei Ti Quinn Chemistry, Biology atributos con valores multivaluados ...

...

...

...

47

1era forma Normal

Atomic Attributes (still search multiple column Profes or ID# Nombre Apellido Modulo 1 Modulo 2 Modulo 3 12345

Fred

Jones

IT

Business History

23456

Sarah

Smith

Chemistry

Physics

34567

Joe

Clegg

Maths

Physics

Art

45678

Mary

WindsorMaths

Physics

Geography

56789

Edward GriswaldMaths

PE

Gen Studies

67890

Cyril

Coombs History

French

PE

78901

Mei Ti

Quinn

Chemistry

...

...

...

...

IT

Biology 48

2da Forma Normal

No hay grupos repetitivos, pero hay problema Profes or ID# Nombre Apellido Módulo La Información 12345 Fred Jones IT está si duplicada. Una relación está Forma Normal está en 12345 Fred Jonesen 2ºBusiness 12345 23456 23456 23456 34567 34567 34567 45678 45678 ...

Fred Jones History 1º Forma Normal y ningún atributo no primo es Sarah Smith Chemistry (La información está Sarah de Smith Physics dependiente una parte de una clave. contenida en las Sarah Joe Joe Joe Mary Mary ...

Smith Clegg Clegg Clegg Windsor Windsor ...

IT Maths Physics Art Maths Physics ...

correspondencias entre atributos) Depende de ID# (parte de la clave primaria) 49

49

2da Forma Normal

Profes or ID# Nombre 12345 23456 34567 45678 ...

Fred Sarah Joe Mary ...

Apellido Jones Smith Clegg Windsor ...

Módul o ID# Módulo BS001Business HI001 History PH002Physics ... ...

En esta propuesta, las tablas representan “cosas” o conceptos que tienen claro significado. Por un lado el “Profesor”, por el otro los “Módulos”. Estas son las “entidades” sobre las cuales se ha hablado en la modelización conceptual. 50

2º Forma Normal Profes or ID# Nombre 12345 23456 34567 45678 ...

Fred Sarah Joe Mary ...

Apellido Jones Smith Clegg Windsor ...

Módul o ID# Módulo BS001Business HI001 History PH002Physics ... ...

Profesor/Mód ulo TID# MID# 12345 12345 12345 23456 23456 23456 34567 34567 34567 45678 45678 ...

IT003 BS001 HI001 CH002 PH002 IT003 MA002 PH002 AR001 MA002 PH002 ... 51

3ra Forma Normal Estos atributos no dependen de ID#

Profes or ID# Nombre Módulo 12345 12121 11345 26456 21156 22256 35667 31367 11167 21378 12178 ...

Jones Brown Paul Sylvester Mike Smith Clegg Marshall Dominic Windsor Matt ...

Nombre del Módulo Information Technology Business Studies History of Art Chemistry Physics IT Maths Physics Art Maths Physics ...

Una relación está en 3º Forma Normal si está en 2º Forma Normal y no existen atributos no primos que dependen transitivamente de alguna de las claves. IT Bu Hi Ch Ph IT M a Ph Ar M a Ph

52