ASIR-Diseño_logico_Modelo_Relacional.pdf

Administración de Sistemas Informáticos en Red (ASIR) Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Modelo Relacional © Hipatia Educación, S.L. Madrid (España), 2023-24 Autores: Dan Triano, Carlos Lafuente Martínez 2 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Índice 1. Introducción............................................................................................. 4 2. Objetivos pedagógicos............................................................................. 5 3. Diseño lógico. Modelo Relacional............................................................. 6 1. Proceso de diseño de una base de datos.................................................. 6 2. Estructura del modelo relacional.............................................................. 8 2.1. Características de una relación.............................................................. 11 2.2. Dominios y atributos.............................................................................. 11 2.3. Atributos clave....................................................................................... 15 2.4. Resumen de propiedades de las relaciones o tablas............................... 19 2.5. Vistas.................................................................................................... 20 3. Esquema relacional................................................................................ 21 4. Reglas de integridad.............................................................................. 29 4.1. Mantenimiento de la integridad referencial............................................ 33 5. Transformación del modelo E-R en el modelo relacional........................ 35 5.1. Método para crear un esquema relacional a partir del E-R...................... 35 6. Normalización del modelo de datos....................................................... 44 6.1. Formas normales................................................................................... 45 6.2. Cuadro resumen de formas normales..................................................... 53 6.3. Desnormalización.................................................................................. 54 7. Álgebra relacional.................................................................................. 55 4. Resumen................................................................................................ 59 5. Glosario................................................................................................. 60 6. Referencias bibliográficas...................................................................... 62 3 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 1. Introducción En el proceso de diseño de una base de datos, tras un esquema conceptual como el obtenido con un modelo Entidad-Relación, será necesario crear un modelo lógico, ya orientado a las operaciones que se podrán hacer con los datos y soportado por al SGBD que vayamos a utilizar. El modelo lógico más frecuente, y durante muchos años casi el único en determinados ámbitos de gestión, es el modelo relacional. El modelo relacional de una base de datos fue introducido en 1970 por el informático inglés Edgar Frank Codd en IBM y, no tardó en consolidarse como un nuevo paradigma en los modelos de bases de datos. Se trata de una estructura de datos simple y uniforme (la relación), en la que el lugar y el modo en cómo se almacenen los datos no tienen relevancia para el usuario. La ventaja del modelo relacional es que los datos se tratan de una manera que a los usuarios les resulta muy fácil de entender y utilizar. Los datos se almacenan en tablas de dos dimensiones, filas y columnas, y permite establecer interconexiones entre los datos de las distintas tablas de una forma muy versátil y sencilla para relacionar los datos de ambas tablas, motivo por el cual se le conoce con el nombre de modelo relacional. Durante su diseño, una base de datos relacional pasa por un proceso llamado normalización de una base de datos con el que se conserva la integridad del modelo. Cuando se dispone de un esquema ER previo se pueden seguir unas reglas claras para construir el esquema relacional derivado del primero. El esquema relacional permite realizar unas operaciones de manipulación de los datos según el álgebra relacional, con los que se obtienen unos resultados concretos y predecibles de forma similar a como se hace con la teoría de conjuntos y el álgebra de Boole. Es un anticipo a lo que se podrá hacer con el lenguaje SQL. A partir de un esquema relacional bien definido se podrá construir, en un paso posterior, una base de datos relacional generando las sentencias apropiadas en un lenguaje estandarizado DDL (Data Definition Language). 4 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 2. Objetivos pedagógicos Los objetivos de aprendizaje que se pretenden alcanzar con esta unidad son: ▪ Entender la importancia de la fase de modelado lógico dentro del proceso de diseño de una base de datos. ▪ Conocer el modelo relacional, su terminología y los elementos de que consta un esquema relacional. ▪ Saber establecer las reglas de integridad en un modelo relacional. ▪ Desarrollar habilidades para convertir un modelo ER en un modelo relacional siguiendo unas pautas establecidas. ▪ Realizar el proceso de normalización de un esquema relacional para asegurar la coherencia de los datos. ▪ Entender las operaciones con datos mediante el álgebra relacional. 5 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 3. Diseño lógico. Modelo Relacional 1. Proceso de diseño de una base de datos Cuando se quiere diseñar una base de datos relacional, el proceso natural será haber creado previamente un esquema entidad-relación a partir de unos requisitos, sobre todo porque ambos modelos son muy compatibles, como veremos más adelante. En el proceso de diseño lógico se creará un esquema relacional, que no es más que una definición de tablas con los atributos apropiados y claves foráneas para mantener las reglas de integridad de los datos. Con dicho esquema ya se podrá hacer el diseño físico y crear la base datos con el DDL en el SGBD elegido. Ilustración 1. Fases del diseño de una base de datos relacional y productos obtenidos. El modelo de datos relacional se habrá elegido, en la mayoría de necesidades de diseño, de una base de datos que tenga información de gestión transaccional. Es el más frecuente para bases de datos centralizadas, aunque se pueden implementar procesos de replicación de ciertos datos en distintos servidores con el objeto de obtener un mejor rendimiento y seguridad. 6 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes A este nivel de diseño lógico, en el que trabajan los diseñadores o analistas de aplicaciones, aún quedan ocultos los detalles de las estructuras físicas de almacenamiento. Se representarán de forma sencilla las estructuras de datos apropiadas con los elementos que permitan relacionar la información contenida en la BBDD. El diagrama Entidad-Relación obtenido en la etapa de diseño conceptual servirá ahora, en la fase de diseño lógico, para definir la estructura de la base de datos relacional. El modelo ER y el modelo relacional tienen unos planteamientos con importantes semejanzas que harán que el paso del diagrama ER al esquema relacional se pueda hacer con unos criterios muy claros e intuitivos. 7 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 2. Estructura del modelo relacional Los principios del modelo relacional fueron establecidos por el informático inglés Edgar Frank Codd en 1970. El primer prototipo de Codd fue el sistema R en los laboratorios IBM, en San José (California). Al mismo tiempo, surgió INGRES como prototipo en la Universidad de Berkeley. Posteriormente, aparecieron SGBD como DB2 (IBM) y ORACLE, a partir del sistema R y, más tarde, surgió SQL Server de Microsoft. El interés de las grandes empresas informáticas denota el gran éxito del modelo relacional y el camino tecnológico que supuso. El modelo relacional se basa en la teoría de conjuntos y la lógica de predicados, lo que hace que sea un modelo seguro y robusto. En contraposición al almacenamiento de datos en ficheros, en el modelo relacional no se debe prestar atención al lugar y el modo en cómo se almacenen los datos, sino solo en su organización. El modelo relacional de Codd perseguía una serie de objetivos: ▪ Independencia física: la manera en que se almacenen los datos no debe afectar a la manipulación de los datos por los usuarios. ▪ Independencia lógica: si se modifican ciertos elementos de la base de datos no se verán afectados los programas y usuarios que accedan a otros elementos de ésta mediante vistas. ▪ Flexibilidad: para que cada usuario/aplicación acceda a los datos de la manera más adecuada. ▪ Uniformidad en las estructuras lógicas (colección de tablas): para que sea sencilla la concepción y manipulación. ▪ Sencillez: en el conjunto del modelo y de los lenguajes relacionales de usuario (SQL). En el modelo relacional podremos representar: ▪ Estructuras de datos, en las que se almacena la información. ▪ Restricciones de integridad, reglas que deben cumplir los datos siempre para mantener la coherencia, de acuerdo con el modelo conceptual o realidad representada. 8 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Operaciones de manipulación de los datos, tanto para agregar, modificar y eliminar información de la base de datos como para recuperar o consultar información consistente. La estructura básica del modelo relacional es la relación que se puede representar como una tabla. Es el lugar donde se representan los datos y cuyo contenido varía en el tiempo. En la tabla o relación se distingue: ▪ El nombre de la tabla. ▪ Los atributos, que representan las propiedades de la relación y se representan mediante columnas. ▪ El dominio, que es el conjunto de valores válido que toma cada atributo. ▪ Las tuplas, que son las ocurrencias de la relación, y se representan como filas de la tabla que contienen los valores que toma cada atributo para cada elemento de la relación. IMPORTANTE Los términos relación y tupla han caído en desuso. Así pues, utilizaremos para ellos la terminología más común y denominaremos tabla (table) a la relación y fila (row) a la tupla. En cambio, el término atributo, denominado también columna (column), se utiliza indistintamente al hablar del modelo relacional. Es común también el uso de otros sinónimos como campo (field) para atributo y registro para fila. Modelo Base de datos Sistemas de relacional Relacional ficheros Relación Tabla Fichero Tupla Fila Registro Atributo Columna Campo Ilustración 2. Equivalencias en la terminología. EJEMPLO Tenemos una relación de alumnos donde la cabecera define los atributos de cada elemento que contiene. Cada fila contiene los datos de un alumno. 9 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Ilustración 3. Ejemplo de una relación o tabla. EJEMPLO CREADO CON UNA HERRAMIENTA GRÁFICA Puede verse claramente representada la estructura de una base de datos relacional con varias tablas. En cada tabla se enumeran sus atributos y el tipo de datos de cada uno. Además, se representan las relaciones que existen entre las diferentes tablas mediante la notación de pata de gallo. Como se puede apreciar, la semejanza con un diagrama ER es bastante grande. Ilustración 4. Ejemplo de modelo de datos relacional creado con una herramienta gráfica. 10 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 2.1. Características de una relación Uno de los éxitos del modelo relacional radica en la simplicidad del diseño básico de la tabla o relación. La información en las tablas son datos explícitos, es decir, no existen apuntadores o enlaces entre tablas. De partida: ▪ A cada tabla se le da un nombre único. No podrán existir dos tablas con el mismo nombre. Cada tabla es, a su vez, un conjunto de filas y columnas. ▪ A cada columna se le asigna un nombre distinto que refleje la característica correspondiente del mundo real. ▪ Todas las entradas (datos) de cualquier columna son de un solo tipo. Más características de una relación (tabla): ▪ Las filas y las columnas de una tabla no están ordenadas, es decir, su orden es irrelevante. ▪ No admite tuplas (filas de datos) duplicadas. Cada fila de la tabla debe ser única, es decir, no pueden existir dos filas con datos idénticos en todos los atributos. La razón es que cada fila representa un hecho diferenciado, y la presencia de dos filas idénticas sería redundante. ▪ La tabla es plana, esto quiere decir que en el cruce de una fila y columna hay un solo valor (son valores atómicos) y, por tanto, no se admiten atributos multivaluados. 2.2. Dominios y atributos El dominio es el conjunto finito de valores permitidos que puede tomar cada atributo. 11 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Por tanto, los valores contenidos en una columna pertenecen a un dominio. Todos los dominios tienen un nombre y un tipo de dato asociado, y pueden tener definidas ciertas restricciones que lo definan con más detalle. Existen dos tipos de dominio: ▪ Dominios generales, con valores admitidos entre un máximo y un mínimo. Por ejemplo, un intervalo o rango de valores. Un tipo de datos implica también un rango. ▪ Dominios restringidos, solo contienen un conjunto de valores específico. Por ejemplo, el atributo Convocatoria, en la que un alumno aprueba una asignatura, contendrá un valor perteneciente al conjunto {ordinaria, extraordinaria}. Un atributo es en realidad el papel o rol que desempeña un dominio en una relación. Podemos decir que un atributo representa el uso de un dominio para una determinada relación o tabla, es decir, aporta un significado semántico a un dominio. Por tanto, se hace necesario que los atributos tengan nombres significativos. EJEMPLO Si tenemos un atributo que contiene una fecha en una tabla llamada Persona, ese atributo no representará una fecha sin más, sino que será una fecha de nacimiento o de incorporación, de alta o de baja, etc., es decir, que el dato contenido en el atributo tiene un significado concreto. EJEMPLO DE RELACIÓN CON MÁS ATRIBUTOS QUE DOMINIOS En la siguiente relación tenemos información de personas, con tres atributos: su nombre, su provincia de nacimiento y su provincia de residencia. Sin embargo, solo hay dos dominios: el de códigos de provincia y el de nombres de personas. 12 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Nombre Provincia_ Provincia_ Persona Nacimiento Residencia Luis P1 P2 María P1 NULL José P5 P1 Pepe P1 P4 Silvia P2 P2 Ilustración 5. Ejemplo de relación con más atributos que dominios. Tipos de datos Cada SGBD incorporan sus propias especificaciones en cuanto a tipos de datos y no podemos decir que sean compatibles al 100%. Los tipos de datos que puede contener una columna o atributo pueden clasificarse en tres grandes grupos: ▪ Numéricos. Los tipos de datos numéricos pueden dividirse en numéricos enteros y numéricos con partes fraccionarias (coma flotante). Por ejemplo, un atributo Edad sería un numérico entero y el Importe sería un número decimal con dos decimales. ▪ Fecha. Los tipos de datos fecha sólo admiten fechas y horas. Por ejemplo, un atributo Fecha_Nacimiento podría tomar el valor ‘21/07/2003’. Cuando se almacenan las fechas se comprueba de una manera estricta si una fecha es válida o no, que no es lo mismo que comprobar por separado que el mes esté comprendido entre 0 y 12, y el día entre 0 y 31. ▪ Cadena. Los tipos de datos cadena pueden dividirse en alfanuméricos o en aquellos que almacenan datos binarios. Un ejemplo sería el atributo Apellidos, en el que se definirá una longitud máxima de por ejemplo 50 caracteres. El tipo de datos exacto se requerirá sobre todo en el diseño físico, para la creación de las tablas en el SGBD, y pueden variar de un SGBD a otro. 13 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes El tipo de datos se deberá tener en cuenta también durante la manipulación de los datos, usando los operadores apropiados al tipo de dato. EJEMPLOS DE TIPOS DE DATOS Ejemplos de tipos de datos son: INTEGER, DATE, CHAR(25), VARCHAR(10), etc. Las cadenas de longitud fija, del tipo CHAR, pueden tener una longitud de 255 caracteres en MySQL, 2000 bytes o caracteres en Oracle y, sin embargo, en SQLServer pueden llegar a 8000 caracteres. El valor NULL Un valor especial, que puede permitirse o no para un atributo, es el valor NULL, que representa la ausencia de valor. NULL es un valor muy importante en el modelo relacional, puesto que permite representar y trabajar con datos desconocidos o que carecen de valor. NULL representa el valor de un atributo que no contiene ninguno de los valores del dominio de dicho atributo. Se usa la palabra reservada NULL para representarlo, pero internamente el servidor conserva un valor especial. EJEMPLO 1 En una tabla de Vehículos se puede tener un atributo que indique la posición del volante cuyo dominio sería {Izquierda, Derecha}. Si una fila de datos corresponde a una motocicleta ese atributo no tendría sentido y no se puede asignar ninguno de los valores del dominio y, por tanto, quedaría a NULL. EJEMPLO 2 En una tabla de Personas donde se tiene un atributo para indicar el número de hijos/as, cuyo dominio está definido como el rango de valores del 0 al 20, donde el 0 representa que la persona no tiene descendientes. Si para una persona no 14 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes se conoce ese dato, no es lo mismo conservar un valor cero, que tiene un significado concreto (no tiene hijos/as), que NULL que representa precisamente la ausencia de información. NULL es también una palabra propia del lenguaje de consulta estructurada SQL (Structured Query Language) para indicar que no existe un valor para un atributo de una tabla. ¿Sabías qué? En el SGBD, con el valor NULL no se podrán hacer operaciones aritméticas ni comparaciones con otros valores, ya que un atributo con valor NULL no equivale a cero ni a una cadena vacía. Con NULL solo pueden hacerse operaciones lógicas para consultar si un atributo tiene o no valor NULL. 2.3. Atributos clave Clave candidata Una clave candidata es un atributo que sirve para identificar sin ambigüedad y de forma única a cada fila de la tabla. Todas las relaciones deben tener, por definición, una clave candidata que puede ser simple o compuesta por varios atributos. Como no puede haber tuplas repetidas, al menos el conjunto de todos los atributos cumple la función de clave candidata. Sin embargo, lo que interesa es que la clave candidata no contenga información superflua. Por tanto, un subconjunto de atributos de una tabla es una clave candidata si cumple estas dos propiedades: ▪ Unicidad. Valor único, es decir, que no hay 2 tuplas con el mismo valor en esa clave. 15 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Es mínima. No se puede eliminar ningún atributo de la clave candidata sin que se rompa la unicidad. EJEMPLO En una tabla de alumnos, los atributos DNI y Nº Matrícula son claves candidatas. Clave primaria De entre las claves candidatas se elige una para identificar las tuplas de la tabla y se la llama clave primaria. La elección de esta clave primaria afectará a la implementación física de la tabla, los métodos de acceso y la referencia desde otras tablas. Una clave candidata que no es la primaria recibe el nombre clave alternativa. Para elegir la clave primaria, convenientemente, el diseñador habrá de tener en cuenta el significado de la tabla y sus atributos, y sopesar diversos factores como: ▪ Estabilidad del dato. Es preferible que el valor(es) que identifica(n) a una tupla no cambie a lo largo del tiempo. ▪ Facilidad de uso. Un dato sencillo como un número entero es más fácil de usar que un alfanumérico con un formato más o menos definido. ▪ Fiabilidad. Si se prevé que pueda haber en el futuro tuplas con el mismo valor no se aconseja su elección. ▪ Universalidad. En ocasiones, se utiliza un atributo con un dominio que es utilizado también en otras bases de datos. Son ejemplos las codificaciones estándar de países con dos y tres letras, o la numeración de las provincias españolas en concordancia de los códigos postales. En ocasiones, el diseñador decide crear un atributo -numérico- que haga la función de clave primaria. Su valor se asigna de manera arbitraria, con el único requisito de guardar un valor numérico único para cada fila, que no tiene que ver 16 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes con los datos almacenados ni tiene significado por sí mismo. Para ello existe la propiedad “autonumérico” que proporcionan los SGBD, con la que el propio sistema asigna el número siguiente al dar de alta una fila o registro. EJEMPLO DE CLAVES CANDIDATAS Y CLAVE PRIMARIA Si tenemos la relación de datos de Proveedores siguiente: ID NOMBRE ESTADO CIUDAD 1 Sánchez 20 Londres 2 Jaramillo 10 Madrid 3 Beteta 30 Paris 4 Torres 20 Londres 5 Arranz 30 Atenas Ilustración 6. Tabla con datos de Proveedores. Podemos proponer varias claves candidatas y comprobar si, con los datos actuales, se cumple la Unicidad y si son Mínimas: Posibles ¿Cumple ¿Es Observaciones claves candidatas UNICIDAD? MINIMA? ID ✓ ✓ NOMBRE ✓ ✓ ESTADO ⊗ - Se repiten 20 y 30 CIUDAD ⊗ - Se repite Londres (ID, ESTADO) ✓ ⊗ Sobra ESTADO (ID, NOMBRE) ✓ ⊗ Sobra NOMBRE o ID Otros subconjuntos de atributos tampoco serían mínimos. Ilustración 7. Comprobación de claves candidatas. 17 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes En este ejemplo tenemos, por tanto, dos claves candidatas: ID y NOMBRE. Como clave primaria parece más conveniente elegir el atributo ID, a falta de saber alguna connotación sobre ese dato, ya que es más sencillo que el Nombre y, sobre todo, porque el Nombre puede repetirse en el futuro. Clave foránea Se denomina clave foránea (foreign key), también llamada clave externa o ajena, de una relación a un conjunto de atributos cuyos valores han de coincidir con los valores de la clave primaria de otra relación. ▪ La clave foránea y la clave principal deben estar definidas sobre el mismo domino. ▪ La clave foránea puede admitir valores nulos o bien tener restricción de obligatoriedad (NOT NULL). La potencia del modelo relacional estriba en que las distintas tablas estén relacionadas adecuadamente mediante claves foráneas, y el SGBD obligará a que dicha relación siempre se cumpla, conservando la llamada integridad referencial. También puede definirse una clave foránea sobre la propia tabla, es decir, un atributo que hace referencia a la clave principal de la propia tabla. Es algo similar a lo que ocurre en las relaciones reflexivas del modelo E-R. El vínculo entre dos tablas se establece desde una clave foránea de la primera tabla a la clave principal de la otra tabla. El SGBD asegurará que siempre se conserve la ¡Recuerda! integridad referencial. 18 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes EJEMPLO DE CLAVE FORÁNEA Pensamos, en el caso de una tabla Empleados y otra de Departamentos de una empresa. Para tener asignado a cada empleado a un departamento, en el modelo relacional usaremos una clave foránea en el empleado con el número del departamento al que pertenece. El dominio del atributo NºDPTO de Empleado es el conjunto de valores de la clave primaria de la tabla Departamento y el SGBD impedirá que se asigne un departamento no existente. La siguiente figura representa la finalidad de la clave foránea. Ilustración 8. Ejemplo de asociación entre registros mediante una clave foránea. 2.4. Resumen de propiedades de las relaciones o tablas Las propiedades que cumplen las relaciones (tablas) en el Modelo Relacional son: ▪ No hay dos relaciones que se llamen igual. ▪ No puede haber dos atributos que se llamen igual en la misma relación. ▪ No puede haber tuplas repetidas en una relación. ▪ El orden de las tuplas es irrelevante, es decir, no están ordenadas. ▪ El orden de los atributos es irrelevante. Los atributos no están ordenados. 19 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Los atributos son atómicos, en cada intersección de una fila y una columna de la tabla hay un único valor. ▪ Los atributos tienen un dominio de valores admitidos. ▪ El valor NULL es especial para representar la ausencia de valor o valor indeterminado. A cada atributo se le indicará si admite valores nulos o no. 2.5. Vistas En una base de datos relacional también podrá haber un tipo especial de relaciones llamadas vistas (views), que son relaciones derivadas de otras relaciones. Una vista tiene columnas, las que se recuperan de otras relaciones, pero no tiene una clave como las tablas. Son relaciones en cierta manera virtuales, puesto que no contienen datos en sí mismas, sino solo una expresión de consulta mediante la cual recuperan tuplas de datos de otras relaciones. No se puede decir realmente que las vistas son un elemento del modelo relacional, sino más bien una utilidad que se utilizará en las bases de datos relacionales. Las vistas se corresponden al nivel externo de la arquitectura ANSI/SPARC, y son una potente herramienta en el SGBD para facilitar al usuario final la información sin que deba conocer en detalle el esquema relacional. Ilustración 9. Empleo de vistas para facilitar el uso del esquema relacional. 20 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 3. Esquema relacional Una base de datos relacional es un conjunto de relaciones (tablas) normalizadas. La normalización es una mejora imprescindible del diseño inicial del que hablaremos más adelante en esta unidad. El estado de una relación está constituido por el esquema y el cuerpo, es decir, el conjunto de tuplas en un momento dado. Para representar el esquema de una base de datos relacional se debe dar la definición formal de las relaciones que la forman, esto es, el nombre de las relaciones y sus cabeceras, con los atributos de éstas, los dominios sobre los que se definen estos atributos, las claves primarias y las claves externas. En el esquema, los nombres de las relaciones aparecen seguidos de los nombres de los atributos encerrados entre paréntesis. Es importante que las claves primarias, que son los atributos subrayados, sean nombradas las primeras. Normalmente, el paso siguiente a crear el esquema relacional es crear el esquema físico en DDL y ahí sí se especificará con precisión cada dominio (tipo de datos y valores admitidos). Definición formal de relación Una relación R sobre un conjunto de dominios D1, D2, …Dn, se compone de dos partes, una cabecera y un cuerpo. ▪ La cabecera está formada por un conjunto fijo de atributos, esto es, parejas de nombre de atributo-dominio: ((A1:D1), (A2:D2), … (An:Dn)) 21 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ El cuerpo está formado por un conjunto de tuplas, el cual varia con el tiempo. Cada tupla, a su vez, está formada por un conjunto de pares atributo-valor. La fila o tupla número ‘i’ tendría los valores: ((A1:Vi1), (A2:Vi2), … (An:Vin)) El esquema de una relación se forma con su nombre y su cabecera, es decir: R { (A1:D1), (A2:D2), … (An:Dn) } ¡Recuerda! Dos características de una relación son: ▪ El grado de la relación: indica el número de atributos de la relación. ▪ La cardinalidad de la relación, indica el número de tuplas que contiene. En la siguiente figura se representan los elementos y características de una relación: Ilustración 10. Concepción teórica de una relación o tabla. 22 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes EJEMPLO Supongamos que nuestro modelo relacional consta de las relaciones Proveedor (del ejemplo anterior) y esta de Empleado: PROVEEDOR EMPLEADO ID NOMBRE ESTADO CIUDAD DNI NOMBRE NSS Ciudad_NCTO Fecha_NCTO 1 Sánchez 20 Londres 1 P. García 111111 Bilbao 23/02/1974 2 Jaramillo 10 Madrid 2 L. Rojo 222222 Vitoria 04/03/1990 3 Beteta 30 Paris 3 A. Aguirre 333333 Zamora 31/12/1964 4 Torres 20 Londres 4 P. Bonet 444444 Barcelona 17/11/1970 5 Arranz 30 Atenas 5 I. Goiko 555555 Bilbao 08/08/1984 6 J. Amillo 666666 Zaragoza 02/02/2002 Ilustración 11. Tablas Proveedor y Empleado. El esquema relacional, de la manera más sencilla, sería la siguiente definición: PROVEEDOR (ID, NOMBRE, ESTADO, CIUDAD) EMPLEADO (DNI, NOMBRE, NSS, CIUDAD_NCTO, FECHA_NCTO) Para que el esquema sea más completo se debería especificar el dominio, separado por ‘:’, a continuación de cada atributo, bien indicando el tipo de datos o un nombre de dominio y una especificación aparte. EJEMPLO COMPLETO El esquema anterior, indicando el dominio, sería la siguiente definición: PROVEEDOR (ID:entero, NOMBRE:cadena(25), ESTADO: {10,20,30,40,50}, CIUDAD:cadena(25) ) EMPLEADO (DNI:DNI, NOMBRE:cadena(25), NSS:cadena(15), CIUDAD_NCTO:cadena(25), FECHA_NCTO:fecha) 23 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Dominios: se han indicado los tipos de datos de los atributos. Además, destacamos los siguientes casos: ▪ Estado: se han supuesto unos determinados valores que serían válidos para la empresa, por ejemplo: 10, 20, 30, 40 y 50. ▪ Dominio DNI: es una cadena de longitud 9: 8 dígitos y 1 letra, existiendo un conocido algoritmo, denominado Módulo 23, que da coherencia entre las cifras y la letra: se divide el número del DNI entre 23 y el resto de la división (un núm. del 0 a 22) corresponde a la letra según la siguiente lista: Resto 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Letra T R W A G M Y F P D X B N J Z S Q V H L C K E Ilustración 12. Correspondencias del algoritmo Módulo 23 ACTIVIDAD PROPUESTA Determinar el grado y la cardinalidad de las relaciones Proveedor y Empleado anteriores. Claves foráneas en el esquema Habitualmente, una clave foránea (FK) tendrá el mismo nombre que la clave primaria (PK) a la que haga referencia. Pero se puede completar ese nombre con alguna alusión a la función o rol que desempeña el atributo. Esto resultará imprescindible si hay más de una FK referida al mismo dominio. EJEMPLO Si de una persona se conserva el código de la provincia de su domicilio y también la de su nacimiento, unos nombres apropiados para esos atributos podrían ser cod_provincia_domicilio y cod_provincia_nacimiento. No hay una notación especial prevista, para identificar en el nombre, que un atributo sea clave externa o foránea (foreign key). Para definir su dominio sí usaremos la indicación FK o FOREIGN KEY, y en el modelo físico será imprescindible especificarlo. Sin embargo, es aconsejable, en el esquema 24 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes relacional, añadir al nombre de una clave foránea la coletilla ‘_FK’ o bien ‘(FK)’ ya que ayudará a su interpretación. EJEMPLO En el siguiente ejemplo se describe el esquema relacional de una tabla en notación sencilla. El uso de nombres apropiados para los atributos nos ayuda a saber algo más de los mismos, aunque no estén definidos los dominios. BILLETE_AVION (Num_billete, localizador, fecha_compra, Pasaporte_pasajero, pais_residencia_FK, compañía_aérea, Num_vuelo, fecha_salida, pais_origen_FK, fecha_llegada, país_llegada_FK) Deducimos que hay entre los atributos tres datos de país, que son FK’s que harán referencia a una tabla de países. ACTIVIDAD PROPUESTA Dado el esquema relacional sencillo que está definido más abajo, realiza las siguientes cuestiones: Esquema relacional: en una compañía aseguradora se quiere tener información de las pólizas de seguro y vehículos asegurados, con las siguientes tablas: Vehiculo (matricula, marca, modelo, color, fecha_matriculación) Marca (ID_marca, nombre, país_origen, año_creación) Modelo (ID_modelo, nombre, tipo_vehiculo, marca, potencia, tipo_motor, tamaño, país_fabricación) Persona (DNI, nombre, apellidos, teléfono, tipo_carnet, fecha_carnet) PolizaSeguro (num_poliza, dni_tomador, fecha_inicio, matricula, tipo_seguro, dni_conductor) 1. Propón un domino apropiado para cada atributo. Para algunos bastará indicar someramente un tipo de datos, y para otros podrás definir unos rangos válidos o un conjunto limitado de valores. 25 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 2. Expresa el esquema relacional completo. Puedes modificar el nombre de algún atributo como consideres para mejorar la interpretación, por ejemplo, añadiendo ‘FK’ a los que desempeñan esta función. 3. Dibuja las tablas en una hoja de cálculo o procesador de textos y anota en ellas unos datos de ejemplo inventados por ti. Cinco registros en cada tabla serán suficientes. Al no tratarse de un SGBD deberás procurar anotar datos coherentes en cuanto a sus dominios (tipos de datos, valores, FK, etc.). Representación gráfica del esquema relacional Una deficiencia del esquema relacional es que su representación es, como hemos visto, solo textual, citando las tablas y sus atributos de una determinada manera. Las claves foráneas no se identifican de una manera especial y eso dificulta la interpretación de las tablas que están relacionadas. Por ello, se suelen complementar los esquemas con líneas o flechas para señalar dónde “apuntan” las FK’s o, mejor aún, por diagramas relacionales completos como el de la Ilustración 4. Las variantes gráficas para representar esquemas relacionales tienen en común el uso de una caja para cada tabla, conteniendo la lista de atributos y detallando o no, su tipo de datos y obligatoriedad. Las PK se identifican claramente en negrita, con una llave o con la abreviatura ‘PK’. Las FK se identifican y una línea llevará hasta la tabla a la que referencian. El tipo de línea varía de un estilo a otro y puede ser: ▪ Terminado en flecha apuntando de la FK a la PK. ▪ Notación pata de gallo. ▪ Terminado con ’⁕’ o ’∞’ para el lado N de la relación (la FK) y con ‘1’ o ‘Ⅰ’ para el lado 1 (la PK). 26 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Ilustración 13. Creación de un diagrama relacional en DBdesigner. Anteriormente, ya se citaban numerosas herramientas software para el diseño de BBDD que, en la mayoría de los casos, también permiten todas las facilidades para el diseño relacional. Recordemos algunas: ▪ Herramientas de pago: ERwin Data Modeler, ER/Studio, Lucidchart, Miro, Enterprise Architect. ▪ Software libre: RISE, PgModeler, DBdesigner y otras. ▪ Herramientas incorporadas en el paquete de software del propio SGBD: MySQL Workbench (software libre), Oracle SQL Developer Data Modeler (software libre), Enterprise Manager de SQLServer, etc. En muchos casos, estas herramientas permiten leer el esquema relacional desde una base de datos ya implementada en un servidor, construir el diagrama correspondiente, y luego aplicar las modificaciones que hagamos en el diagrama sobre la base de datos. 27 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes La ventaja de usar una herramienta gráfica para el diseño relacional no es solo la presentación del diagrama sino el poder reaprovechar toda esa definición de elementos del esquema y generar el diseño físico, esto es, las sentencias de creación de las tablas en lenguaje DLL (SQL). 28 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 4. Reglas de integridad Las reglas de integridad del modelo relacional son una serie fundamental de restricciones sobre los datos que el SGBD relacional debe asegurar que se cumplan en todo momento: 1. Regla de integridad de unicidad de la clave primaria [UNIQUE]. No puede tener en ningún momento dos tuplas con la misma combinación de valores de la clave primaria o de claves alternativas. 2. Regla de integridad de entidades. Restricción de obligatoriedad [NOT NULL] de la clave primaria. Ningún atributo que forme parte de la clave primaria de una relación puede tomar el valor NULL. Dicho de otra manera, no puede haber ninguna tupla con algún valor desconocido o inexistente para alguno de los atributos de la clave. 3. Regla de integridad referencial [FOREING KEY]. Los valores que toma una clave foránea deben ser valores nulos o valores que existen en la clave primaria a la que haga referencia. Para implementar esta regla se necesita elegir las políticas adecuadas respecto a la obligatoriedad de las claves foráneas. 4. Regla de integridad de dominio [CHECK]. Todos los valores no nulos que contiene la base de datos para un determinado atributo deben ser del dominio declarado para dicho atributo. Es decir, hay unas condiciones, que se habrán especificado en el SGBD, que deben cumplir los valores de los atributos. IMPORTANTE En las reglas anteriores se nombraron en mayúsculas y entre corchetes las palabras reservadas del lenguaje SQL para especificar dichas reglas. Las cláusulas UNIQUE y NOT NULL también se podrán especificar si es necesario sobre atributos que no sean clave primaria. En ese caso, UNIQUE estaría especificando una clave candidata alternativa. Si los valores de las tablas están en un documento o en una hoja de cálculo debemos ser muy cuidadosos para respetar siempre las reglas de integridad anteriores. Sin embargo, si estas tablas están en un SGBD relacional, se habrán 29 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes especificado totalmente en su creación y el SGBD no permitirá que se violen las restricciones del modelo. EJEMPLO DE APLICACIÓN DE LAS REGLAS DE INTEGRIDAD Partimos de un diagrama E-R de dos entidades con una relación 1:N. Se trata de la entidad Departamento (DPTO) y Empleado (EMP) donde cada empleado pertenece a un departamento. En el esquema relacional lo que tenemos son dos tablas y en la tabla EMP hay un atributo adicional (NºDPTO) que es la clave externa o foránea para informar del departamento del empleado: Diagrama ER: Esquema Relacional sencillo: DPTO (NºDPTO, NOMBRE) EMP (NºEmpleado, NOMBRE, Teléfono, Comisión, NºDPTO) Ilustración 14. Diagrama E-R y su esquema relacional sencillo. Detallando en el esquema relacional los dominios de los atributos y algunas restricciones de datos obligatorios (NOT NULL) tendríamos: Esquema Relacional detallando los dominios: DPTO (NºDPTO:CódigoDepartamento, NOMBRE:cadena(20) NOT NULL) EMP (NºEmpleado:entero, NOMBRE:cadena(20) NOT NULL, Teléfono:cadena(9) NOT NULL, Comisión:decimal, NºDPTO: CódigoDepartamento NOT NULL) Dominio CódigoDepartamento: número entero asignado en la tabla DPTO. Ilustración 15. Esquema Relacional detallando los dominios. 30 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Vamos a suponer el siguiente estado de las tablas: EMP NºE NOMBRE NºDPTO Teléfono Comisión DPTO 1 Sánchez 200 123 NULL NºDPTO NOMBRE 2 Jaramillo 100 125 1,5 100 VENTAS 3 Beteta 100 234 3 200 RRHH 4 Torres 300 321 NULL 300 PRODUCCION 5 Arranz 300 237 NULL Ilustración 16. Tuplas en la tabla DPTO y la tabla EMP Nos fijamos en que, actualmente, se cumplen todas las reglas de integridad del modelo: ▪ Unicidad e integridad de claves: las claves primarias, en ambas tablas, tienen valores únicos y no nulos. ▪ Integridad referencial: la clave foránea NºDPTO en EMP tiene valores válidos que existen en la tabla DPTO. ▪ Integridad de dominios: todos los valores no nulos son del dominio especificado. Algunas filas de EMP no tienen dato de Comisión (NULL), pero sí se permite pues ese atributo no tiene la exigencia NOT NULL. Veamos ahora si podríamos o no añadir ciertas filas o registros en las tablas respetando las reglas de integridad de los datos. Se indica debajo de cada operación la hipotética respuesta (mensaje de error) que daría el SGBD tras verificar la integridad: ▪ Añadir un departamento a DPTO con nombre ‘CONTABILIDAD’ pero sin un número de departamento: ¡Tupla no permitida! Se incumpliría la regla de integridad de entidad. La clave primaria no puede tener valor nulo. Ilustración 17. Resultado de la comprobación de la integridad. 31 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Añadir un departamento a DPTO con los siguientes valores de atributos: (200, ‘CONTABILIDAD’) ¡Tupla no permitida! Se incumpliría la regla de unicidad de la clave primaria. Clave duplicada; el valor 100 para NºDPTO ya existe en DPTO. Ilustración 18. Resultado de la comprobación de la integridad. Añadir un empleado, es decir, una fila en EMP con los siguientes valores de atributos (anotados en el mismo orden del esquema): (6, ‘Hernández’, 400, ‘255’, 3) ¡Tupla no permitida! Se incumpliría la regla de integridad referencial. El NºDPTO 400 no es válido porque no existe en la tabla DPTO. Ilustración 19. Resultado de la comprobación de la integridad. ▪ Añadir una fila en EMP con los siguientes valores de atributos: (7, ‘Hernández’, 200, ‘255’, ‘tres’) ¡Tupla no permitida! Se incumpliría la regla de integridad de dominio. El valor ‘tres’ no es válido para el atributo Comisión. Ilustración 20. Resultado de la comprobación de la integridad. ▪ Añadir una fila en EMP con los siguientes valores de atributos: (8, ‘Hernández’, 200, NULL, 3) ¡Tupla no permitida! Se incumpliría la regla de integridad de dominio. Se requiere un valor para el atributo Teléfono. Ilustración 21. Resultado de la comprobación de la integridad. 32 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes En la siguiente figura se muestran coloreadas las filas que no pueden ser añadidas: EMP DPTO NºE NOMBRE NºDPTO Teléfono Comisión NºDPTO NOMBRE 1 Sánchez 200 123 NULL 100 VENTAS 2 Jaramillo 100 125 1,5 200 RRHH 3 Beteta 100 234 3 300 PRODUCCION 4 Torres 300 321 NULL ¿? CONTABILIDAD 5 Arranz 300 237 NULL 200 CONTABILIDAD 6 Hernández 400 255 3 7 Hernández 200 255 tres 8 Hernández 200 NULL 3 ¡Tuplas NO permitidas! El SGBD no dejaría añadir las filas de color naranja pues incumplen las reglas de integridad. Ilustración 22. Ejemplo de aplicación de las reglas de integridad. 4.1. Mantenimiento de la integridad referencial. Veamos cómo afectan, para mantener la integridad referencial todas las posibles actualizaciones de datos. Usaremos el ejemplo anterior (DPTO  EMP). En cada situación se indican las acciones posibles para mantener la integridad referencial: ▪ Si se borra una tupla de DPTO: No dejar borrar el departamento si tiene algún empleado (RESTRICCIÓN). Eliminar también todos sus empleados (eliminación en CASCADA). 33 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Anular la referencia de los empleados que pertenezcan al departamento eliminado (asignar NULL--> Nº DPTO). ▪ Si se actualiza una tupla de DPTO modificando su clave (el NºDPTO): No dejar hacer la actualización. El sistema modifica tanto el NºDPTO del DPTO como el NºDPTO del EMP (actualización en CASCADA). Modificar el NºDPTO del DPTO y poner a nulo el NºDPTO en EMP a esos empleados. ▪ Al Insertar una tupla en EMP Asignar al empleado un NºDPTO que exista en la tabla DPTO. Asignar valor nulo en el NºDPTO del empleado. Nota: en este ejemplo concreto este caso tampoco se permitiría pues se exigió NOT NULL a ese atributo para cumplir con la cardinalidad (1,1). ▪ Al Borrar una tupla de EMP No es necesario comprobar nada. ▪ Si se Modifica el NºDPTO del EMP El nuevo valor asignado debe existir en la tabla de DPTO. Se podría asignar el valor nulo al NºDPTO del empleado. 34 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 5. Transformación del modelo E-R en el modelo relacional Será lo más habitual haber creado un diagrama Entidad-Relación como resultado del modelado conceptual del sistema de información que se pretende informatizar. Ese diagrama E-R nos será muy útil para dar el siguiente paso que es crear el modelo relacional. Además, ambos modelos tienen planteamientos con una gran semejanza y podemos decir que son muy compatibles. 5.1. Método para crear un esquema relacional a partir del E-R El método para crear un esquema relacional a partir de un esquema E-R es el siguiente: ▪ Toda entidad del MER se transforma en una tabla. ▪ Todo atributo de entidad se transforma en columna dentro de su tabla. ▪ El identificador único de la entidad se convierte en clave primaria (PK). ▪ Toda relación N:M se convierte en una nueva tabla de FK’s (foreign keys), que tendrá como PK al conjunto de PK de las entidades que asocia, donde a su vez cada una es FK. ▪ En la transformación de relaciones 1:N existen dos soluciones posibles: a) Propagar la clave (añadir una FK), se añade el atributo PK de la entidad con cardinalidad máxima 1 a la de cardinalidad máxima N. b) Convertir la relación en una nueva tabla de FK’s (como si fuera relación de N:M) si la relación tiene atributos propios o cuando se prevé que en un futuro la relación se convertirá en N:M. ▪ En la transformación de relaciones 1:1 se tienen en cuenta las cardinalidades (Mín,Máx) de la relación. a) Transformar la relación en una tabla de FK’s si las dos entidades poseen cardinalidad (0,1), conteniendo una PK compuesta por FK’s. b) Propagar la clave (añadir una clave externa), si una entidad tiene (0,1) y la otra (1,1) se propaga la PK de la que tiene cardinalidad (1,1). 35 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes c) Si ambas tienen cardinalidad (1,1) se puede propagar la clave a cualquier de las dos tablas. También se puede considerar fundir las dos entidades en una sola tabla. Ejemplos de transformación A continuación, veremos con ejemplos visuales los distintos casos de esta transformación. Resumidamente podemos decir que consiste en implementar las relaciones entre entidades como foreign keys (FK) entre tablas. EJEMPLO 1. RELACIÓN 1:1 CON CARDINALIDADES MÍNIMAS 1 Y RELACIÓN 1:N Esquema E-R del que queremos obtener el esquema relacional: Nota: a falta de atributos en este esquema ER, propondremos algunos en el relacional Ilustración 23. Esquema E-R del ejemplo 1. Primer paso: a partir de las entidades del E-R creamos directamente las tablas del Relacional con sus atributos propios y distinguimos la clave principal. EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura) Segundo paso: a partir de las relaciones del E-R creamos las claves foráneas (FK) y las tablas de claves que sean necesarias: 36 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Relación “Trabaja_en” (1:1). Ambas entidades tienen cardinalidades (1,1) por lo que podemos elegir entre añadir id_videoclub en Empleado, o bien, añadir num_emp en Videoclub. Elegimos la segunda opción. ▪ Relación “Supervisa” (1:N). En Videoclub se añade num_emp como clave externa para informar del supervisor. IMPORTANTE Siempre es aconsejable poner nombres significativos a las claves foráneas para que representen el rol de ese dato. En este caso se hace imprescindible y los llamamos num_emp_supervisor y num_emp_trabajador. El esquema relacional quedaría así: EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura, num_emp_trabajador, num_emp_supervisor) Las tablas, con unos valores de ejemplo, serían así: VIDEOCLUB EMPLEADO ID_video... NE_trabajador NE_supervisor NE nombre... (FK) (FK) 100...... 1... 100 100 101...... 2... 102 100 102...... 3... 101 102 Ilustración 24. Tablas resultantes, con unos valores de ejemplo. Solución B: La otra opción válida de esquema relacional es, poniendo en Empleado el videoclub en el que trabaja: EMPLEADO (num_emp, nombre, dni, f_nacimiento, Id_videoclub) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura, num_emp_supervisor) 37 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes En este caso las tablas serían algo así con los valores de ejemplo: VIDEOCLUB EMPLEADO ID_video... NE_supervisor NE nombre... Id_video (FK) (FK) 1... 100 100...... 1 2... 100 101...... 3 3... 102 102...... 2 Ilustración 25. Tablas resultantes, con unos valores de ejemplo. EJEMPLO 2. RELACIÓN 1:1 CON AMBAS CARDINALIDADES MÍNIMAS 0 El esquema E-R del que queremos obtener el esquema relacional, en el que solo son diferentes del ejemplo anterior las cardinalidades de la relación Trabaja_En, es: Nota: a falta de atributos en este esquema ER, propondremos algunos en el relacional. Ilustración 26. Esquema E-R del ejemplo 2. Primer paso: a partir de las entidades del E-R tenemos directamente las tablas con sus atributos propios y la clave principal: EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura) Segundo paso: a partir de las relaciones del E-R creamos las claves foráneas (FK) y tablas de claves que sean necesarias: 38 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ▪ Relación “Supervisa” (1:N). En Videoclub se añade num_emp como clave externa para informar del supervisor. ▪ Relación “Trabaja_en” (1:1) con ambas cardinalidades (0,1). Creamos una tabla para almacenar las parejas de FK de Videoclub y Empleado: EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura, num_emp_supervisor) TRABAJA (Id_videoclub, Num_emp) En la nueva tabla de claves externas (TRABAJA), cada atributo es FK de una tabla y conjuntamente son la PK. No es obligatorio que cada videoclub tenga un trabajador, ni que cada empleado trabaje en un videoclub. Solo las ocurrencias de la relación se conservan en la tabla. Ahora las tablas con unos valores de ejemplo tendrían este aspecto: VIDEOCLUB La PK es compuesta y cada atributo es FK ID_Video... NE_supervisor EMPLEADO (FK) NE nombre... 1... 100 100...... TRABAJA 2... 100 101...... Id_Video NE 3... 102 (FK) (FK) 102...... 1 100 Si en el videoclub núm. 2 no trabajara ningún 3 101 empleado, no habría una fila para él aquí. Ilustración 27. Tablas resultantes, con unos valores de ejemplo. EJEMPLO 3. RELACIÓN 1:1 CON CARDINALIDADES MÍNIMAS 0 Y 1 En este esquema E-R debemos fijarnos nuevamente en las cardinalidades de la relación Trabaja_En: en todo Videoclub trabaja un Empleado, pero puede haber empleados que no trabajen en videoclubs. 39 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Nota: a falta de atributos en este esquema ER, propondremos algunos en el relacional. Ilustración 28. Esquema E-R del ejemplo 3. Primer paso: a partir de las entidades del E-R, las tablas del Relacional serán inicialmente: EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura) Segundo paso: fijándonos en las relaciones del E-R crearemos las claves foráneas (FK) y/o tablas de claves que sean necesarias: ▪ Relación “Supervisa”: añadimos al Videoclub la PK del empleado supervisor. ▪ Relación “Trabaja_en” con una cardinalidad (0,1) y otra (1,1): añadiremos en el Videoclub la PK del empleado que actúa como ‘trabajador’. EMPLEADO (num_emp, nombre, dni, f_nacimiento) VIDEOCLUB (Id_videoclub, nombre, dirección, f_apertura, num_emp_trabajador, num_emp_supervisor) Tenemos una solución similar a la propuesta para el Ejemplo 1. 40 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes EJEMPLO 4. RELACIÓN N:M CON ATRIBUTOS En este pequeño esquema E-R tenemos una relación N:M que además tiene atributos propios: Nota: a falta de atributos en este esquema ER, propondremos algunos en el relacional. Ilustración 29. Esquema E-R del ejemplo 4. Primer paso: a partir de las entidades del E-R, proponemos las tablas con algunos atributos y sus PK: ALUMNO (ID_Alumno , Nombre, Apellidos,….) ASIGNATURA (COD_Asignatura, Nombre, Abreviatura, NumHorasLectivas, Objetivos, …) Segundo paso: nos fijamos en la relación del E-R: ▪ La relación “Cursa” (N:M) se convierte en una nueva tabla (Calificaciones) que contiene como atributos las PK’s de Alumno y de Asignatura, actuando por separado como FK y conjuntamente como PK de esta tabla Calificaciones. El atributo de la relación (calificación) pasa a ser atributo de esta tabla, ya que es un dato que existe cuando se emparejan ambos. El esquema relacional queda así: ALUMNO (NMat, Nombre, Apellidos, …) ASIGNATURA (COD_Asignatura, Nombre, Abreviatura, NumHorasLectivas, Objetivos, …) CALIFICACIONES (ID_alumno, COD_asignatura, calificación) Visualmente las tablas serían, con unos datos de ejemplo: 41 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes ALUMNO La PK es compuesta y ASIGNATURA sus atributos son FK NMat Nombre... COD_Asig Nombre... 100...... A10...... CALIFICACIONES 101...... F02...... NMat Cod_Asig calific 102...... D05...... (FK) (FK) 100 A10 7,5 Solo estarán las combinaciones 100 D05 NULL necesarias de alumno-asignatura. 102 A10 9 Ilustración 30. Tablas resultantes, con unos valores de ejemplo. La tabla Calificaciones permite registrar los alumnos que cursan cada asignatura, y para eso no es necesario aún el dato de calificación, por lo que este atributo tendría la característica NOT NULL, ya que no es obligatorio. Cuadro resumen Para transformar un esquema Entidad-Relación a un esquema Relacional: Modelo Objeto a crear en el Modelo Relacional Entidad-Relación Entidad y sus Tabla y sus columnas atributos Propagar la clave: FK en la tabla con N, 1:N o bien nueva tabla de FK’s. Relaciones Propagar la clave: clave ajena, 1:1 o bien nueva tabla, Binarias o bien uniendo las dos entidades en una sola tabla. M:N Otra tabla nueva de FK’s, con clave primaria igual a la suma de las PK de las dos tablas. 42 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes Añadir clave ajena 1:N o bien nueva tabla con ambas claves. Relaciones otra tabla con la pareja de claves. 1:1 Reflexivas O bien Clave ajena en cualquiera de las dos tablas Otra tabla nueva, cuya clave principal es la clave de la entidad M:N duplicada Se crea una tabla nueva con las claves primarias de las ‘n’ entidades: 1. Su PK es la concatenación de claves primarias de las Relaciones n-arias entidades con grado diferente a 1 (N, M,...) (ternarias, cuaternarias, etc.) 2. Si alguna tiene cardinalidad máxima 1, al menos ha de haber (n-1) claves primarias de otras (n-1) entidades, y han de participar en la relación las claves primarias de las entidades con cardinalidad máxima 1. Crear una tabla con las claves de las entidades y los atributos Relaciones con de la relación. Esta tabla no es imprescindible en las atributos relaciones 1:1 o 1:N, pero sí aconsejable Ilustración 31. Resumen de criterios para convertir un esquema ER en Relacional. 43 ASIR Módulo: Gestión de Bases de Datos Unidad: Diseño lógico. Administración de Sistemas Modelo Relacional informáticos y Redes 6. Normalización del modelo de datos La normalización es una técnica para optimizar la estructura de datos en el modelo relacional minimizando las redundancias y anomalías en la actualización de datos y, maximizando la estabilidad del esquema a futuro. Según Kendall (2005): La normalización consiste en transformar las tablas o vistas de usuario complejas en un juego de estructuras de datos más pequeñas y estables. Además de ser más simples y estables, las estructuras de datos resultantes serán más fáciles de mantener. Si hemos creado nuestro esquema relacional a partir de un esquema E-R correcto y siguiendo el método propuesto más arriba, tendremos ya un alto grado de normalización y un mínimo grado de redundancia y, posiblemente, esté en 4ª Forma Normal. De ahí lo aconsejable de hacer un diseño conceptual primero y después un diseño lógico basado en el anterior, ya que el resultado será mucho mejor. Sin embargo, en ocasiones, se ha partido de listados de datos o volcados de datos procedentes del usuario u otros sistemas de información, y se ha creado directamente el esquema relacional. Ahí es muy frecuente no obtener un buen diseño y tener que hacer un proceso de normalización. Incluso siguiendo unas buenas prácticas pueden haber problemas indetectables en esta fase de diseño y la base de datos puede sufrir problemas que queremos evitar, como: ▪ Redundancia de información, que son datos que se encuentran repetidos en las tablas continua e innecesariamente. ▪ Incumplimiento de reglas de integridad, que suelen deberse a dependencias funcionales entre los atributos. ▪ Ambigüedad en los datos, que ocurre cuando no queda claro si la información de un atributo se refiere a un elemento u otro del modelo. Son problem

ASIR-Diseño_logico_Modelo_Relacional.pdf

Document Details

Related

Full Transcript

Upgrade to continue