Capítulo 2 Diseño de Bases de Datos

2.1 Fases del Diseño de Bases de Datos Es una practica estándar el dividir el diseño de bases de datos en las siguiente fases Análisis de Requerimientos Diseño Conceptual Diseño Lógico Diseño Físico

Figura 2.1: Fases del Diseño de Bases de Datos 2.1.1 Análisis de Requerimientos La fase de análisis de requerimientos produce una descripción operacional de la base de datos. Su objetivo es asegurar que la base de datos contenga los datos necesarios para las funciones y aplicaciones donde se usara la base de datos. Esta fase es realizada normalmente por los diseñadores de bases de datos a través de entrevistas con los usuarios del sistema que será realizado. En este sentido se dice que esta fase es una fase de: Adquisición de Conocimiento. La salida de esta fase (valga la redundancia) son los requerimientos del sistema. 2.1.2 Diseño Conceptual La fase de Diseño Conceptual se alimenta del Análisis de Requerimientos y produce un diseño que trata de reflejar como son los datos. Es una práctica común que estas dos primeras fases sean hechas de manera participativa y a través de refinamientos sucesivos a través de la interacción de los diseñadores y los usuarios del sistema. El diseño conceptual trata de crear un Modelo Parcial del Universo donde se trata de capturar lo suficiente para poder soportar todas las funciones a las que servirá el sistema final. El resultado final de esta fase es un Esquema de la Base de Datos. No necesariamente este esquema puede ser implementado directamente en algún manejador de base de datos. Dentro de esta fase es común el uso del modelo Entidad - Relación. 2.1.3 Diseño Lógico Tomando el esquema de la base de datos de la fase de Diseño Conceptual, esta fase produce un diseño que se acerca más a la implementación en un Sistema Manejador de Base de Datos. En esencia esta fase transforma el modelo Entidad - Relación en tablas que podrán ser implementadas en un sistema manejador de base de datos particular. El modelo de datos que usaremos para esta etapa es el modelo ELKA(Entity Link Key Attribute). Una vez que el modelo Entidad - Relación es transformado a tablas y produce el modelo ELKA, se eliminan ciertas anomalías, debidas principalmente a la redundancia, el proceso a través del cuál se da esto se

conoce como NORMALIZACIÓN. Es importante comentar que el proceso de NORMALIZACIÓN es un Medio y no un Fin. 2.1.4 Diseño Físico Una vez que tenemos las tablas resultantes del Diseño Lógico es importante el decidir tanto la estructura de almacenamiento y las estrategias de acceso. La estructura de almacenamiento se refiere a como almacenar los datos, y la estrategia de acceso se refiere a como llegar a los datos. Algunos ejemplos de estructuras de almacenamiento son: Archivos Planos, Archivos Comprimidos, Archivos Codificados, Formatos Específicos (DBF, DAT, DBM, etc.). Las estrategias de acceso pueden ser: Acceso Secuencial, Acceso Binario, Acceso Heap, Acceso usando Btrees, etc. Cada vez es más común que los sistemas manejadores de base de datos tengan ya predefinida la estructura de almacenamiento y como estrategia de acceso tengan solo dos: Acceso Secuencial y Acceso usando B-Trees. Entonces esta etapa se reduce en términos simples a la selección de los INDICES para acelerar el acceso. En ocasiones por eficiencia es posible que en esta fase del proceso se realice una DESNORMALIZACIÓN, es decir aceptar una Forma Normal de Menor Nivel que a la que se puede llegar, recuérdese que la NORMALIZACIÓN es un medio y no un fin. 2.1.5 Ejemplo de Diseño de una Base de Datos Suponga que es deseado en el departamento de capacitación de una empresa el llevar el control de los cursos de capacitación y de la capacitación de cada empleado. Análisis de Requerimientos y Diseño Conceptual En estas dos fases es fundamental el poder identificar en base a las necesidades del sistema las entidades de interés y sus relaciones. En base a las entrevistas realizadas se plantea que es necesario el poder realizar la planeación de cursos y llevar el control de los cursos que ha tomado cada empleado. Los atributos de interés que se han identificados se ilustran en la figura 2.2.

Figura 2.2: Atributos de Interés de Empleados y Cursos Con esto podemos llevar el control de los empleados y cursos, pero no de la relación entre ellos, de este modo es necesario el crear una relación que indique que cursos ha tomado cada empleado y que empleados han tomado que curso. En este sentido es necesario adicionalmente el poder identificar que tipo de relación hay: Un empleado solo puede tomar un curso? Un curso solo puede ser tomado por un empleado? Un curso puede ser tomado por varios empleados? Un empleado puede tomar varios cursos?

De acuerdo a lo analizado (que reflejaría las reglas del negocio particular) se determino que un empleado puede tomar varios cursos y un curso puede ser tomado por varios empleados. Entonces surge el modelo en Entidad - Relación ilustrado en la figura 2.3.

Figura 2.3: Modelo Entidad - Relación de la Base de Datos de Empleados y Cursos Diseño Conceptual En esta fase tomando el modelo entidad - relación debemos producir el modelo ELKA correspondiente. En la Figura 2.4 se ilustra el modelo ELKA resultante.

Figura 2.4: Modelo ELKA de la base de datos de Empleados y Cursos El proceso de Normalización involucra (por lo general) el particionar las tablas del modelo ELKA en tablas NORMALIZADAS donde se ha reducido o eliminado la redundancia. Por ejemplo, si todos los empleados del mismo departamento tuvieran el mismo Salario, entonces podríamos particionar la tabla de Empleado en dos según se ilustra en la figura 2.5.

Figura 2.5: Modelo ELKA de la base de datos de Empleados y Cursos NORMALIZADO Diseño Físico Tomando como base el modelo ELKA normalizado se procede a realizar el diseño físico de la base de datos. Asumiendo que (normalmente) no se tiene la opción de seleccionar la estructura de almacenamiento, esta etapa se refiere solo a la asignación de los tipos de datos específicos de cada campo y a la definición de los índices(B-Trees). Como regla general debe haber un índice por cada llave de cada tabla, pero adicionalmente se deberían de diseñar índices para optimizar las consultas o reportes que son más frecuentes. También es importante el considerar que dependiendo de la frecuencia de uso, el tamaño de las bases de datos, el tamaño de los índices, el costo de actualizar los índices, etc. algunos índices se designan como temporales y otros como permanentes. Para nuestro caso el resultado final esta ilustrado en las tablas 2.1 y 2.2 Nombre De Campo Tipo de Campo #Empleado Numérico 6 dígitos NombreEmpleado Carácter 35 posiciones Dirección Carácter 40 posiciones Departamento Carácter 20 posiciones #Curso Numérico 6 dígitos NombreCurso Carácter 35 posiciones Salario Numérico 6 dígitos enteros 2 decimales Tabla 2.1: Definición de Campos de la Base de Datos de Empleados y Cursos Indice Tabla Campo EMPX MPLEADO #Empleado CURSOX CURSO #Curso DEPX DEPARTAMENTO Departamento INSCX INSCRITO #Empleado INSCX INSCRITO #Curso Tabla 2.2: Definición de Campos de la Base de Datos de Empleados y Cursos Para poder soportar la obligatoriedad de algunas relaciones es necesario crear adicionalmente reglas de integridad que pueden ser soportadas directamente por el sistema manejador de base de datos o se tienen que programar. Dentro de este aspecto es importante considerar todas las reglas de integridad (que aún sin estar capturadas en los modelos Entidad - Relación o el ELKA) garantizarían que la base de datos conserve su INTEGRIDAD 2.2 El Modelo Entidad - Relación Un modelo de datos trata de capturar la organización lógica de los datos, adicionalmente en ocasiones es posible capturar en él algunas reglas de integridad y facilitar la ejecución de consultas.

El modelado de datos semántico que usaremos será el de Entidad - Relación, una Entidad es cualquier cosa de la cuál deseamos llevar información, una Relación representa la manera en la cuál diferentes entidades(aunque puede ser la misma). Los tres componentes de un diagrama Entidad Relación son: Entidades. Representados como rectángulos con el nombre de la entidad dentro(el nombre es en singular). Relaciones. Representados como rombos, con el nombre de la relación dentro. Que reflejan la manera en que se relacionan las entidades. Atributos. Representados como Ovalos con el nombre del atributo dentro. Adicionalmente es importante saber que: Los atributos se unen a las entidades a través de líneas. Las entidades se unen a las relaciones a través de líneas con las interpretaciones dadas en la figura 2.6.

Figura 2.6: Diferentes conectores de los enlaces que conectan entidades. De esta manera si tenemos que dos entidades están conectadas a través de una relación tendremos un total de 16 posibles combinaciones. Cuando una relación conecta tres entidades tendremos 64 posibles combinaciones de terminaciones, etc. Por otro lado existen tres tipos de Relaciones segun se indica en las figuras 2.7, 2.8 y 2.9.La relación isa indica que una entidad es un subconjunto de otra, esto implica que ambas tienen la misma llave. La relación id implica que una de las entidades tiene adicionalmente otros campos como llave.

Figura 2.7: Representación de una relación normal.

Figura 2.8: Representación de una relación isa

Figura 2.9: Representación de una relación id 2.2.1 Ejemplos Asignación de Salones El problema de asignación de salones puede ser planteado de manera muy simplificada como la planeación en tiempo y espacio de un conjunto de cursos , es decir, se tiene que definir para cada curso en que salón y a que hora se imparte. En este sentido un posible modelo entidad relación es ilustrado en la figura 2.10

Figura 2.10: Modelo Entidad-Relación para el problema de Asignación de Salones Explosión de Materiales El problema de explosión de materiales que surge en diversas empresas manufactureras, se refiere principalmente a la posibilidad de modelar que una parte está compuesta de varias partes y una parte forma parte de varias partes. Un posible modelo Entidad -Relación es presentado en la figura 2.11.

Figura 2.11: Modelo Entidad - Relación de Explosión de Materiales Departamentos, Empleados y Proyectos Se tiene una empresa en la que los empleados están asignados a departamentos, dentro de la empresa se desarrollan diversos proyectos y en él pueden participar empleados incluso de diferente departamento. Un posible modelo Entidad - Relación es presentado en la figura 2.12.

Figura 2.12: Modelo Entidad-Relación de Departamentos, Empleados y Proyectos Proyecto, Proveedor y Parte Se sabe que en una empresa se desarrollan proyectos que utilizan partes suministradas por varios proveedores. Adicionalmente se sabe que los pedidos (Proveedor-Parte-Proyecto) son almacenados en diversos almacenes(pero un pedido en un solo almacén). Un posible modelo Entidad - Relación está en la figura 2.13.

Figura 2.13: Modelo Entidad - Relación de Proyecto, Proveedor y Parte Empresa Completa Se ilustra en la figura 2.14

Figura 2.14: Modelo Entidad-Relación de empresa completa

Estado Civil La relación isa es usada para ilustrar el estado civil de empleados en la figura 2.15

Figura 2.15: Modelo Entidad-Relación del Estado Civil de Empleados REGLA PARA UNA RELACIÓN isa Y UNA RELACIÓN id. Una relación es isa cuando la entidad que se considera HIJA tiene la misma llave que el PADRE. Una relación es id cuando la entidad que se considera HIJA la llave de la entidad PADRE más otros atributos Universidad Dentro de una universidad se desea automatizar el proceso de inscripciones, manejo de calificaciones, generación de listas y en general los servicios de control escolar. De acuerdo a la información recopilada se tienen identificadas las siguientes entidades: ALUMNO Matricula (Llave) Nombre Carrera Dirección Tutor PROFESOR RFC (Llave) Nombre Grado

Especialidad Salario Dirección SALON Número (Llave) Ubicación Capacidad MATERIA Clave (Llave) Nombre Descripción PLAN DE ESTUDIOS Carrera (Llave) Materias del plan Nombre Descripción SEMESTRES ID (Llave) Inicio Fin Anotaciones Se sabe además que:

• Un alumno puede no estar inscrito en algún semestre. • Un alumno solo puede tener una carrera. • Un alumno puede estar tomando cero, una o más materias. • Un profesor puede impartir cero, una o más materias (incluso puede tener varios

grupos de la misma). • En un salón puede haber programadas, cero, una o más materias(pero no a la misma

hora). • Las materias son abiertas por grupos pudiendo haber cero, uno o más grupos de una

materia. • Cada materia puede tener o ser prerrequisito o correquisito de cero, una o más

materias. • Las materias pueden ser comunes a diferentes carreras. • Cada materia es evaluada con 3 exámenes parciales y uno final. Siendo la calificación

final el promedio de las cuatro calificaciones. Además se lleva registro de faltas. El modelo entidad-relación de la universidad es indicado en la figura 2.16.

Figura 2.16: Modelo Entidad-Relación de una Universidad 2.2.2 Praxis del Diseño de Bases de Datos Uno de los posibles problemas de utilizar el modelo Entidad - Relación como herramienta para el diseño conceptual es que no es implementable directamente en archivos planos, y es necesario realizar la conversión a su equivalente en archivos. Ante esto han surgido algunos paquetes que realizan la conversión automática de diagramas Entidad - Relación a Sistemas Manejadores de Bases de Datos comerciales, uno de estos paquetes es ERWIN que genera código para ORACLE, SYBASE, DB2, etc. Algunos diseñadores al no contar con una forma automatizada de manipular los diagramas Entidad - Relación, han optado por utilizar una forma de modelado más cercana a archivos planos. Una de estas técnicas es el modelo ELKA. Es importante aclarar que una posible opción, sería el generar los diagramas Entidad - Relación y después convertirlos a un diagrama ELKA; aunque en la práctica muchos diseñadores generan directamente el diagrama ELKA sin pasar por el diagrama Entidad - Relación. 2.3 El Modelo ELKA

2.3.1Modelo de una Base de Datos Sencilla

Supongamos que en el departamento de capacitación de una empresa se desea llevar información de los cursos tomados por cada empleado y de los cursos. Los atributos de interés de los empleados son:

#Empleado Nombre Dirección Departamento al que pertenecen Salario Los atributos de interés de cada curso son: #Curso Nombre del Curso. Seguramente usted obtendría el diseño de la figura 2.17

Figura 2.17: Ejemplo de una base de datos simple El como llegar a este modelo de base de datos puede hacerse usando la metodología ELKA como se comentará en la siguiente sección. 2.3.2 Diseño de Bases de Datos El diseño de una base de datos es una parte muy importante en el desarrollo de una aplicación. Se han propuesto diferentes metodologías para llevar a cabo esta tarea. Una de estas metodologías es el uso del MODELO ELKA que será visto a continuación. El modelo ELKA tiene las siguientes componentes clave: E Entity Entidad L Link Liga K Key Llave A Attribute Atributo Veremos a través de un ejemplo como se emplea esta metodología de diseño: Suponga que una compañía necesita tener una Base de Datos que contenga la información de las siguientes Entidades: PROVEEDORES, PARTES, PROYECTOS, EMPLEADOS, ALMACENES, DEPARTAMENTOS Los atributos relevantes de cada entidad son los siguientes:

PROVEEDORES Num_Prov (llave) Nombre Status PROYECTOS Num_Proy (llave) Nombre Fecha_Ini Fecha_Fin PARTES Num_Par (llave) Nombre Color EMPLEADOS Num_Emp (llave) Nombre Sueldo ALMACENES Num_Alm (llave) Capacidad DEPARTAMENTOS Num_Dep (llave) Nombre Además se sabe que: Un proveedor puede suministrar una o más partes a uno o más proyectos Un proyecto puede tener asignados uno o más empleados incluso de diferente departamento. Un empleado solo está asignado a un proyecto y solo pertenece a un departamento. Un departamento tiene uno o más empleados. Un almacén puede tener cero, uno ó mas pedidos de diferentes partes suministrados por diferentes proveedores. Una parte puede ser suministrada en varias cantidades por diferentes proveedores. Un pedido solo puede estar en un almacén. Un proyecto puede tener uno o más pedidos. ENTIDAD

Una entidad es cualquier objeto del cuál se desean almacenar datos dentro de un base de datos. ENLACE Un enlace es la relación o forma en que se relacionan las entidades v.g. Un departamento se relaciona con empleados de forma que un departamento puede tener uno o más empleados. Un empleado se relaciona con departamentos de forma que un empleado solo pertenece a un departamento. TIPOS DE ENLACE El modelo ELKA define 4 tipos de Enlaces: 1-a-1 1-a-N DEBIL (Cero, Uno ó más) 1-a-N FUERTE (Uno ó Más) N-a-M LLAVE Es un atributo ó atributos que permite identificar unívocamente a un elemento de una entidad. ATRIBUTO Es una característica de un elemento de una entidad. Un elemento de una entidad es implementada computacionalmente como un registro(también conocido como Tuplo). Un atributo es entonces un campo de un registro. Representación de una Entidad ELKA representa una entidad como un rectángulo con un recuadro en la esquina inferior izquierda. En el recuadro se pone el nombre de la entidad. En la parte superior dentro del rectángulo se ponen los nombres de los atributos separados por comas. Los atributos que forman parte de la llave van subrayados (la llave puede ser de un solo atributo). La entidad almacen es ilustrada en la figura 2.18.

Figura 2.18: Representación de la entidad almacen Representación de Enlaces Enlace 1-a-1 La representación es ilustrada en la figura 2.19.

Figura 2.19: Representación de un enlace 1 a 1 Esto indica que la entidad A hereda la llave X a la entidad B. Por cada ocurrencia de un tuplo en A existen cero ó una ocurrencia del tuplo en B Por cada ocurrencia de un tuplo en B existe una ocurrencia del tuplo en A. De acuerdo al planteamiento anterior, un empleado s olo está asignado a un proyecto y a un departamento de forma que tenemos enlaces 1-a-1 según se indica en la figura 2.20

Figura 2.20: Ejemplo de enlaces 1 a 1 Enlace 1-a-N Débil La representación de este enlace se indica en la figura 2.21

Figura 2.21: Representación de un enlace 1 a N débil Esto indica que la entidad A hereda la llave X a la entidad B. Por cada ocurrencia de un tuplo en A existen cero, una ó más ocurrencias del tuplo en B Por cada ocurrencia de un tuplo en B existe una ocurrencia del tuplo en A. Considerando que existe una entidad llamada PEDIDOS que contiene los atributos: Num_Prov Num_Par Num_Proy Cantidad

Tenemos que un ALMACEN puede tener cero, uno o más pedidos de diferentes partes suministradas por diferentes proveedores. De esto tenemos una relación 1-a-N DEBIL entre PEDIDOS y ALMACENES como se ilustra en la figura 2.22

Figura 2.22: Ejemplo de un enlace 1 a N débil Enlace 1-a-N Fuerte La manera de representar este enlace se indica en la figura 2.23.

Figura 2.23: Representación de un enlace 1 a N fuerte Esto indica que la entidad A hereda la llave X a la entidad B. Por cada ocurrencia de un tuplo en A existen una ó más ocurrencias del tuplo en B Por cada ocurrencia de un tuplo en B existe una ocurrencia del tuplo en A. De acuerdo a la definición un departamento tiene uno o más empleados y un proyecto tiene uno o mas empleados, según se indica en la figura 2.24

Figura 2.24: Ejemplo de enlaces 1 a N fuertes Enlace N-a-M Se representa a través de dos enlaces 1-a-N ya sean fuertes o débiles utilizando una entidad conectora. Los casos se ilustran en las figuras 2.25, 2.26, 2.27, 2.28.

Figura 2.25: Ejemplo de enlaces 1 a N fuertes

Figura 2.26: Representación de enlace N-a-M

Figura 2.27: Representación de enlace N-a-M

Figura 2.28: Representación de enlace N-a-M 2.3.3 Modelo Elka Final Es ilustrado en la figura 2.29

Figura 2.29 Modelo ELKA Final 2.3.4 Modelos ELKA Manejando Relaciones Recursivas Explosión de Materiales El problema de explosión de materiales, de forma tal que una parte puede estar compuesta de cero, una o más partes y una parte puede formar parte de cero, una ó más partes.En la figura 2.30 se ilustra este modelo.

Figura 2.30: Modelo ELKA de explosión de materiales Requisitos de Materias El problema de requisitos de materias de forma tal que una materia tiene cero, uno o más requisitos y una materia puede ser requisito de cero, una o más materias. Se ilustra en la figura 2.31.

Figura 2.31: Modelo ELKA de requisitos de materias Organigrama El problema de un organigrama tradicional en el que un empleado es jefe de cero, uno ó más empleados y un empleado tiene cero o un jefe.

La figura 2.32 contiene este modelo.

Figura 2.37: Modelo ELKA de un sistema experto Instrucciones de un Programa El problema de una base de datos para un programa en el que se manejan instrucciones de tipo secuencial e instrucciones de tipo condicional en las que si la condición es verdadera sigue una instrucción y sino sigue otra. La figura 2.38 contiene el modelo elka correspondiente. Figura 2.38: Modelo ELKA para las instrucciones de un programa Procesos Concurrentes