Tema 40 – Diseño de bases de datos relacionales

Tema 40 – Diseño de bases de datos relacionales

Contenido

TEMA 40: Diseño de base de datos relacionales……………………………………………………………………… 1

Contenido…………………………………………………………………………………………………………………………… 1

1. INTRODUCCIÓN…………………………………………………………………………………………………………… 1

2. FASES DEL DISEÑO………………………………………………………………………………………………………. 1

3. EL MODELO E-R…………………………………………………………………………………………………………… 3

3.1 Entidades…………………………………………………………………………………………………………………… 3

3.2 Relaciones…………………………………………………………………………………………………………………. 4

3.3 Atributos……………………………………………………………………………………………………………………. 4

3.4 Entidades y relaciones…………………………………………………………………………………………………. 5

3.5 Del E/R al modelo lógico…………………………………………………………………………………………….. 6

4. FORMAS NORMALES………………………………………………………………………………………………… 6

4.1 Primera forma normal…………………………………………………………………………………………………. 7

4.2 Segunda forma normal………………………………………………………………………………………………… 7

4.3 Tercera forma normal………………………………………………………………………………………………….. 8

4.4 Forma normal de Boyce-Codd……………………………………………………………………………………… 9

4.5 Cuarta forma normal…………………………………………………………………………………………………… 9

4.6 Quinta forma normal…………………………………………………………………………………………………… 9

5. NIVEL FÍSICO. OPTIMIZACIÓN……………………………………………………………………………….. 10

5.1 Índices…………………………………………………………………………………………………………………. 10

5.2 Vistas…………………………………………………………………………………………………………………… 10

5.3 Estadísticas…………………………………………………………………………………………………………… 10

1. INTRODUCCIÓN

De lo que se trata es, a partir de un caso real, llegar al diseño de una base de datos coherente y eficiente.

2. FASES DEL DISEÑO

Requerimos de una metodología lo suficientemente potente para realizar esta transformación de una forma metódica y eficiente.

Sin esta metodología podemos llegar a un diseño inapropiado de nuestra base de datos, que puede derivar en alguno de los siguientes problemas:

– Redundancia: Repetición innecesaria de información. Ocupa más espacio.

– Incoherencia: Si tenemos elementos duplicados y modificamos solo uno, se produce incoherencia. Porque tendríamos que tener 2 elementos iguales.

– Pérdida de dependencias funcionales: Pueden pasarse por alto si no disponemos de una metodología de diseño.

Esta metodología se puede dividir en 3 fases:

1. Diseño conceptual: Se estudia el problema y se selecciona que elementos del mundo real se van a modelar. (modelo Entidad-Relación)

2. Diseño lógico: Partiendo del modelo conceptual obtenido en la fase anterior, se llega a un diseño lógico. Se transforman las entidades y relaciones obtenidas en tablas.

3. Diseño físico: Este diseño depende del ordenador. Empleando el gestor de la base de datos implementamos nuestra base en una o varias máquinas.

clip_image002

clip_image004

3. EL MODELO E-R

El modelo conceptual en el que se apoya el modelo relacional es el que denominaremos modelo E-R. Se basa en llegar a un nivel de abstracción, que permita definir los elementos que componen nuestra base de datos.

Se compone de 2 elementos:

1) Entidades (E)

2) Relaciones (R)

Ambas contienen atributos que almacenan información.

3.1 Entidades

clip_image005Las entidades se representan con un rectángulo y dentro situamos el nombre de la entidad.

Distinguimos 2 clases:

– Entidades fuertes: No depende de ninguna otra entidad.

– Entidades débiles: Está condicionada a la aparición de otra entidad. Se representa con un doble recuadro, encerrando el nombre del a entidad. (Ejemplo. Para que exista una cuenta bancaria, es necesario que exista otra entidad, en la que pondríamos titular, que es el beneficiario de la misma. Si no hay ningún titular, no existe la cuenta.

clip_image007

3.2 Relaciones

Las relaciones se representan con un rombo; y dentro situaremos el nombre de la entidad. Las relaciones representan asociaciones entre entidades.

clip_image008Ejemplo: Videoclub. 2 entidades (socios y películas) entre ellos existe una relación (alquilar).

clip_image009
clip_image010

Relación binaria

El grado de una relación nos indica el conjunto de entidades que asocia. Una interrelación puede ser binaria (asocia 2 entidades), ternaria (asocia 3 entidades) …

Las ocurrencias de cada entidad dentro de la relación pueden ser:

1:1 De una a una. Por cada ocurrencia de una entidad sólo puede aparecer uno de la entidad asociada.

1: N De una a muchas. Por cada ocurrencia de una entidad pueden aparecer muchas ocurrencias de la entidad asociada.

N:M De muchas a muchas. La cantidad de asociaciones de una entidad con otra es múltiple.

clip_image011 1:N

clip_image012clip_image013

3.3 Atributos

El último elemento que puede representar el modelo entidad-relación son los atributos. Los atributos almacenan las propiedades que nos interesan de las entidades.

Los atributos aparecen encerrados dentro de una elipse, y conectados con la entidad o relación a la que pertenecen mediante una línea.

Dentro de los atributos tenemos varias clases. Tenemos atributos “normales” que simplemente dan información, y después tenemos otros atributos que sirven además apara identificar las tuplas. Estos atributos son denominados claves.

Superclave

Atributos que identifican de forma inequívoca a una entidad dentro del conjunto de entidades. Num socio

Clave candidata

Atributos que identifican de forma inequívoca a una tupla u ocurrencia de la entidad. DNI

Clave primaria

Clave candidata por la que ha optado el diseñador.

Clave ajena

Es parte de la clave primaria de una relación, y simultáneamente es clave primaria de otra entidad.

Clave alterna

Claves candidatas por las que no ha optado el diseñador como claves primarias.

Claves traslapadas

2 claves que contienen atributos comunes.

Atendiendo a la cantidad de atributos que componen una clave las podemos clasificar en:

Claves simples: La clave está formada por un solo atributo.

Claves compuestas: La clave está formada por más de un atributo.

clip_image015

3.4 Entidades y relaciones

Es un error común confundir las entidades con las relaciones.

El mismo objeto se puede representar tanto como una entidad como una relación.

La diferencia entre entidades y relaciones es que las entidades tienen existencias por sí mismas, y las relaciones dependen para su existencia, como mínimo, de 2 entidades.

clip_image017

3.5 Del E/R al modelo lógico

El siguiente paso lógico es transformarlo al nivel lógico.

Debemos seguir:

1. Las entidades como las relaciones se transforman en tablas.

2. Las relaciones tendrán como clave principal una clave compuesta.

3. Los atributos se transforman en campos de las tablas.

Esto es un ejemplo al transformar la entidad socios en tabla:

ENTIDAD: SOCIOS

Atributos

Observaciones

Definición del dominio

Nombre

X(30)

Apellidos

X(60)

Número

Clave principal

X(6)

Dirección

X(60)

Tlf

X(9)

CP

X(5)

e-mail

X(60)

Localidad

X(30)

4. FORMAS NORMALES

En la segunda fase del diseño de base de datos es donde debemos emplear la teoría de la normalización. 5 reglas que debe cumplir nuestro diseño si queremos que este normalizado.

Estas reglas sirven para evitar los posibles problemas que podríamos tener al actualizar tablas. Cada forma normal elimina un tipo de redundancia. A medida que vamos avanzando en el diseño, las tablas se van fragmentando.

EJEMPLO:

1. ordenes (id_orden, fecha, id_cliente, nom_cliente, estado, num_art, nom_art, cant, precio)

Ordenes

Id_orden

Fecha

Id_cliente

Nom_cliente

Estado

Num_art

nom_art

cant

Precio

2301

23/02/11

101

Martin

Caracas

3786

Red

3

35,00

2301

23/02/11

101

Martin

Caracas

4011

Raqueta

6

65,00

2301

23/02/11

101

Martin

Caracas

9132

Paq-3

8

4,75

2302

25/02/11

107

Herman

Coro

5794

Paq-6

4

5,00

2303

27/02/11

110

Pedro

Maracay

4011

Raqueta

2

65,00

2303

27/02/11

110

Pedro

Maracay

3141

Funda

2

10,00

4.1 Primera forma normal

Un atributo no puede tomar más de un valor.

Al examinar estos registros, podemos darnos cuenta que contienen un grupo repetido para NUM_ART, NOM_ART, CANT y PRECIO. La 1FN prohíbe los grupos repetidos, por lo tanto, tenemos que convertir a la primera forma normal. Los pasos a seguir son:

· Tenemos que eliminar los grupos repetidos.

· Tenemos que crear una nueva tabla con la PK de la tabla base y el grupo repetido.

Los registros quedan ahora conformados en dos tablas que llamaremos ORDENES y ARTICULOS_ORDENES
ordenes (id_orden, fecha, id_cliente, nom_cliente, estado)

Articulos_ordenes (id_orden, num_art, nom_art, cant, precio)

Ordenes

Id_orden

Fecha

Id_cliente

Nom_cliente

Estado

2301

23/02/11

101

Martin

Caracas

2302

25/02/11

107

Herman

Coro

2303

27/02/11

110

Pedro

Maracay

Articulos_ordenes

Id_orden

Num_art

nom_art

cant

Precio

2301

3786

Red

3

35,00

2301

4011

Raqueta

6

65,00

2301

9132

Paq-3

8

4,75

2302

5794

Paq-6

4

5,00

2303

4011

Raqueta

2

65,00

2303

3141

Funda

2

10,00

4.2 Segunda forma normal

Ahora procederemos a aplicar la segunda formal normal, es decir, tenemos que eliminar cualquier columna no llave que no dependa de la llave primaria de la tabla. Los pasos a seguir son:

· Determinar cuáles columnas que no son llave no dependen de la llave primaria de la tabla.

· Eliminar esas columnas de la tabla base.

· Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual dependen.

La tabla ORDENES está en 2FN. Cualquier valor único de ID_ORDEN determina un sólo valor para cada columna. Por lo tanto, todas las columnas son dependientes de la llave primaria ID_ORDEN.
Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN ya que las columnas PRECIO y NOM_ART son dependientes de NUM_ART, pero no son dependientes de ID_ORDEN. Lo que haremos a continuación es eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla ARTICULOS con dichas columnas y la llave primaria de la que dependen.

Las tablas quedan ahora de la siguiente manera.

Articulos_ordenes (id_orden, num_art, cant)

Articulos_ordenes

Id_orden

Num_art

cant

2301

3786

3

2301

4011

6

2301

9132

8

2302

5794

4

2303

4011

2

2303

3141

2

Articulos ( num_art, nom_art, precio)

Articulos

Num_art

nom_art

Precio

3786

Red

35,00

4011

Raqueta

65,00

9132

Paq-3

4,75

5794

Paq-6

5,00

3141

Funda

10,00

4.3 Tercera forma normal

La tercera forma normal nos dice que tenemos que eliminar cualquier columna no llave que sea dependiente de otra columna no llave. Los pasos a seguir son:

· Determinar las columnas que son dependientes de otra columna no llave.

· Eliminar esas columnas de la tabla base.

· Crear una segunda tabla con esas columnas y con la columna no llave de la cual son dependientes.

Al observar las tablas que hemos creado, nos damos cuenta que tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se encuentran en 3FN. Sin embargo, la tabla ORDENES no lo está, ya que NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la llave primaria.

Para normalizar esta tabla, moveremos las columnas no llave y la columna llave de la cual dependen dentro de una nueva tabla CLIENTES. Las nuevas tablas CLIENTES y ORDENES se muestran a continuación.

ordenes (id_orden, fecha, id_cliente)

Ordenes

Id_orden

Fecha

Id_cliente

2301

23/02/11

101

2302

25/02/11

107

2303

27/02/11

110

Clientes (id_cliente, nom_cliente, estado)

Ordenes

Id_cliente

Nom_cliente

Estado

101

Martin

Caracas

107

Herman

Coro

110

Pedro

Maracay

Por lo tanto, la base de datos queda de la siguiente manera:

ordenes (id_orden, fecha, id_cliente)

Clientes (id_cliente, nom_cliente, estado)

Articulos ( num_art, nom_art, precio) Articulos_ordenes (id_orden, num_art, cant)

4.4 Forma normal de Boyce-Codd

Una relación está en FNBC si cada determinante es una clave candidata.

A veces es mejor no aplicarla, porque puede dar lugar a pérdida de dependencias funcionales.

clip_image019

4.5 Cuarta forma normal

Las dependencias funcionales multivaluadas son un tipo de dependencia funcional. Sin embargo, en las dependencias funcionales multivaluadas, un determinante no implica a un solo valor, sino que implica a un conjunto de valores. Deben aparecer relaciones N:M.

Ejemplo:

Supongamos que queremos informatizar a los alumnos de un instituto. Almacenamos el curso en el que cada alumno está, y la modalidad que cursa. (dependencia funcional multivaluada)

Si un estudiante cursa 1Bachillerato, las modalidades por las que puede optar se reducen a un conjunto de valores como Tecnología, humanidades, Ciencias sociales y científico sanitario.

4.6 Quinta forma normal

Dependencia funcional de combinación, de proyección o de JOIN.

Se dice que una relación R, posee dependencia funcional de combinación, sobre sus proyecciones si:

R=R1 * R2* R3 … * R4 à La forma de solucionar esto es descomponer la relación R en N proyecciones independientes.

No tenemos por qué llegar a las 5 forma normal, está en manos del diseñador de la BD. Normalmente se suele llegar hasta la 3ra forma normal.

5. NIVEL FÍSICO. OPTIMIZACIÓN

Realizamos la implementación de la BD. Cada sistema gestor de base de datos dispone de diferentes mecanismos de optimización a ver algunos de los más extendidos.

5.1 Índices
5.1.1 Elección de índices

Van a aumentar la velocidad de acceso a nuestra BD, pero también van a incrementar el espacio de la misma para realizar su almacenamiento.

Debemos buscar una solución de compromiso entre el espacio que ocupan los índices, y la mejora de rendimiento que producen. Normalmente se suelen elegir las claves principales.

5.1.2 Índices en SQL

CREATE INDEX nombre_indice ON nombre_tabla(lista_atributos)

CREATE INDEX Indice_DNI ON Profesores(DNI)

5.2 Vistas

Una vista es una tabla virtual, en realidad no existe. Es una tabla que se genera con datos de otras tablas. No almacenamos información nueva, solo enlaces a las tablas que contienen datos.

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

5.3 Estadísticas

En ocasiones hay que realizar un estudio estadístico de la BD. Podemos ver que consultas son las más solicitadas, las tablas más usadas, los campos que más veces se consultan, etc,

Podemos realizar una optimización de los accesos más empleados, lo que va a incrementar el rendimiento de la BD.