Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
El texto está diseñado para programadores, analistas o técnicos de sistemas que deseen conocer la mecánica de trabajo de las bases de datos a través del uso de una de ellas en concreto: Microsoft SQL Server 2000. Aspectos que se revisan: 1- Conceptos teóricos sobre Bases de Datos. 2- Diseño y modelización de datos tomando como base el modelo Entidad/Relación 3- Generalidades de SQL Server 2000 4- Lenguaje SQL a través de la implementación del mismo en SQL Server 2000 (Transact SQL) 5- Ejemplos de diseño utilizando una herramienta CASE: Power Designor Se requiere como mínimo tener conocimientos del sistema operativo Windows a nivel de usuario. No es imprescindible, aunque si recomendable, conocer fundamentos de programación. BBAASSEESS DDEE DDAATTOOSS CCOONN SSQQLL SSEERRVVEERR 22000000.. TTRRAANNSSAACCTT SSQQLL JJOORRGGEE MMOORRAATTAALLLLAA Desarrollo de software ADVERTENCIA LEGAL Todos los derechos de esta obra están reservados a Grupo EIDOS Consultoría y Documentación Informática, S.L. El editor prohíbe cualquier tipo de fijación, reproducción, transformación, distribución, ya sea mediante venta y/o alquiler y/o préstamo y/o cualquier otra forma de cesión de uso, y/o comunicación pública de la misma, total o parcialmente, por cualquier sistema o en cualquier soporte, ya sea por fotocopia, medio mecánico o electrónico, incluido el tratamiento informático de la misma, en cualquier lugar del universo. El almacenamiento o archivo de esta obra en un ordenador diferente al inicial está expresamente prohibido, así como cualquier otra forma de descarga (downloading), transmisión o puesta a disposición (aún en sistema streaming). La vulneración de cualesquiera de estos derechos podrá ser considerada como una actividad penal tipificada en los artículos 270 y siguientes del Código Penal. La protección de esta obra se extiende al universo, de acuerdo con las leyes y convenios internacionales. Esta obra está destinada exclusivamente para el uso particular del usuario, quedando expresamente prohibido su uso profesional en empresas, centros docentes o cualquier otro, incluyendo a sus empleados de cualquier tipo, colaboradores y/o alumnos. Si Vd. desea autorización para el uso profesional, puede obtenerla enviando un e-mail fmarin@eidos.es o al fax (34)-91-5017824. Si piensa o tiene alguna duda sobre la legalidad de la autorización de la obra, o que la misma ha llegado hasta Vd. vulnerando lo anterior, le agradeceremos que nos lo comunique al e-mail fmarin@eidos.es o al fax (34)-91- 5012824). Esta comunicación será absolutamente confidencial. Colabore contra el fraude. Si usted piensa que esta obra le ha sido de utilidad, pero no se han abonado los derechos correspondientes, no podremos hacer más obras como ésta. © Jorge Moratalla, 2001 © Grupo EIDOS Consultaría y Documentación Informática, S.L., 2000 ISBN 84-88457-24-3 Bases de datos con SQL Server 2000. Transact SQL Jorge Moratalla Responsable editorial Paco Marín (fmarin@eidos.es) Autoedición Magdalena Marín (mmarin@eidos.es) Jorge Moratalla (jmoratalla@eidos.es) Coordinación de la edición Antonio Quirós (aquiros@eidos.es) Grupo EIDOS C/ Téllez 30 Oficina 2 28007-Madrid (España) Tel: 91 5013234 Fax: 91 (34) 5017824 www.grupoeidos.com/www.eidos.es www.LaLibreriaDigital.com mailto:fmarin@eidos.es mailto:fmarin@eidos.es mailto:fmarin@eidos.es mailto:aquiros@eidos.es mailto:mmarin@eidos.es mailto:lmblanco@eidos.es http://www.grupoeidos.com/www.eidos.es http://www.lalibreriadigital.com/ Índice ÍNDICE................................................................................................................................................... 5 INTRODUCCIÓN ................................................................................................................................. 9 DEFINICIÓN DE BASE DE DATOS........................................................................................................... 9 CONCEPTOS BÁSICOS ......................................................................................................................... 10 DEL ENFOQUE TRADICIONAL A LOS SISTEMAS DE BASES DE DATOS ................................................. 10 ARQUITECTURA ANSI/X3/SPARC ................................................................................................... 12 EL MODELO RELACIONAL .................................................................................................................. 12 VISIÓN GENERAL SOBRE EL DISEÑO DE BASES DE DATOS.............................................. 15 FASES DEL DISEÑO ............................................................................................................................. 15 DISEÑO CONCEPTUAL ........................................................................................................................ 17 DISEÑO LÓGICO.................................................................................................................................. 17 DISEÑO FÍSICO.................................................................................................................................... 21 DISEÑO CONCEPTUAL................................................................................................................... 23 INTRODUCCIÓN .................................................................................................................................. 23 ETAPAS DEL DISEÑO CONCEPTUAL .................................................................................................... 24 EL MODELO ENTIDAD / RELACIÓN ..................................................................................................... 24 EJEMPLOS PRÁCTICOS DE DISEÑO CONCEPTUAL................................................................................ 29 DISEÑO LÓGICO............................................................................................................................... 35 INTRODUCCIÓN .................................................................................................................................. 35 PASO DEL ESQUEMA CONCEPTUAL AL ESQUEMA LÓGICO ESTÁNDAR ............................................... 36 ETAPAS EN EL DISEÑO LÓGICO........................................................................................................... 37 PARTICIONAMIENTO HORIZONTAL DE RELACIONES .......................................................................... 38 PARTICIONAMIENTO VERTICAL DE RELACIONES ............................................................................... 41 PARTICIONAMIENTO MIXTO............................................................................................................... 44 TEORÍA DE LA NORMALIZACIÓN ........................................................................................................ 45 EJEMPLOS PRÁCTICOS DE NORMALIZACIÓN ...................................................................................... 49 PROCESO DE DESNORMALIZACIÓN .................................................................................................... 55 DISEÑO FÍSICO ................................................................................................................................. 57 INTRODUCCIÓN .................................................................................................................................. 57 ESTRATEGIAS EN EL DISEÑO FÍSICO................................................................................................... 58 CONCEPTOS BÁSICOS SOBRE GESTIÓN DE FICHEROS ......................................................................... 58 ORGANIZACIÓN DE FICHEROS............................................................................................................ 59 TÉCNICAS PARA EL AUMENTO DE EFICIENCIA ...................................................................................59 INTRODUCCIÓN A SQL SERVER ................................................................................................. 63 ¿QUÉ ES SQL SERVER?...................................................................................................................... 63 ORÍGENES........................................................................................................................................... 64 SQL SERVER E INTERNET.................................................................................................................. 64 LO NUEVO EN SQL-SERVER 2000 ................................................................................................ 65 INTRODUCCIÓN .................................................................................................................................. 65 NUEVAS CARACTERÍSTICAS............................................................................................................... 65 SOPORTE PARA XML ......................................................................................................................... 66 PARTICIONAMIENTO HORIZONTAL DE RELACIONES Y GESTIÓN DE VISTAS DISTRIBUIDAS ............... 67 SOPORTE PARA VIRTUAL INTERFACE ARCHITECTURE (VIA) ........................................................... 67 FUNCIONES DE USUARIO .................................................................................................................... 67 INDEXACIÓN DE VISTAS ..................................................................................................................... 67 NUEVOS TIPOS DE DATOS................................................................................................................... 67 NUEVOS TRIGGERS............................................................................................................................. 68 REGLAS DE INTEGRIDAD REFERENCIAL EN CASCADA ....................................................................... 68 NUEVAS CARACTERÍSTICAS DE INDEXACIÓN .................................................................................... 68 SOPORTE PARA CONSULTAS DISTRIBUIDAS ....................................................................................... 68 CARACTERÍSTICAS DE SEGURIDAD Y CIFRADO DE DATOS ................................................................. 68 INSTALACIÓN DE SQL SERVER 2000 ......................................................................................... 69 EL MODELO E/R EN SQL-SERVER 2000 ..................................................................................... 75 INTRODUCCIÓN .................................................................................................................................. 75 CREAR UN NUEVO DIAGRAMA ........................................................................................................... 77 RESTRICCIONES E INTEGRIDAD REFERENCIAL................................................................................... 79 MODIFICACIÓN DEL ESQUEMA........................................................................................................... 81 CREAR UNA NUEVA RELACIÓN .......................................................................................................... 82 EL ANALIZADOR DE CONSULTAS.............................................................................................. 85 INTRODUCCIÓN .................................................................................................................................. 85 LAS OPCIONES DE MENÚ .................................................................................................................... 85 LA BARRA DE HERRAMIENTAS........................................................................................................... 91 EJECUTANDO UNA CONSULTA ........................................................................................................... 92 EL LENGUAJE DE DEFINICIÓN DE DATOS (DDL).................................................................. 97 INTRODUCCIÓN .................................................................................................................................. 97 TIPOS DE DATOS................................................................................................................................. 97 CREACIÓN DE TABLAS ....................................................................................................................... 99 MODIFICACIÓN DE TABLAS.............................................................................................................. 100 BORRADO DE TABLAS ...................................................................................................................... 101 CREACIÓN Y BORRADO DE ÍNDICES ................................................................................................. 101 7 EJEMPLOS PRÁCTICOS DE USO DEL DDL............................................................................. 103 INTRODUCCIÓN ................................................................................................................................ 103 LA SENTENCIA CREATE TABLE ................................................................................................... 103 LA SENTENCIA ALTER TABLE...................................................................................................... 107 LA SENTENCIA DROP TABLE ........................................................................................................ 111 LA SENTENCIA CREATE INDEX.................................................................................................... 112 LA SENTENCIA DROP INDEX......................................................................................................... 112 EL LENGUAJE DE MANIPULACIÓN DE DATOS (DML) ....................................................... 115 INTRODUCCIÓN ................................................................................................................................ 115 LA SENTENCIA SELECT .................................................................................................................... 115 LA CLÁUSULA WHERE ..................................................................................................................... 117 LA CLÁUSULA GROUP BY ................................................................................................................ 118 LA CLÁUSULA HAVING.................................................................................................................... 119 LA CLÁUSULA ORDER BY ................................................................................................................ 119 FUNCIONES ESCALARES PARA SELECT ............................................................................................ 119 LA SENTENCIA INSERT ..................................................................................................................... 122 LA SENTENCIA UPDATE ................................................................................................................... 123 LA SENTENCIA DELETE.................................................................................................................... 123 OPERADORES BÁSICOS Y CONSIDERACIONES DEL LENGUAJE ................................... 125 INTRODUCCIÓN ................................................................................................................................ 125 OPERADOR PROYECCIÓN ................................................................................................................. 125 OPERADOR UNION ........................................................................................................................... 127 OPERADOR JOIN ...............................................................................................................................128 OPERADORES PROPIOS DE TRANSACT SQL ..................................................................................... 132 VARIABLES GLOBALES..................................................................................................................... 135 SENTENCIAS CONDICIONALES.......................................................................................................... 137 SENTENCIAS ITERATIVAS................................................................................................................. 138 LA SENTENCIA INSERT................................................................................................................ 139 INTRODUCCIÓN ................................................................................................................................ 139 SINTAXIS.......................................................................................................................................... 139 EJEMPLOS......................................................................................................................................... 140 CARGA DE LA BASE DE DATOS DE EJEMPLO..................................................................................... 143 LA SENTENCIA SELECT............................................................................................................... 147 INTRODUCCIÓN ................................................................................................................................ 147 LA CLAÚSULA WHERE ................................................................................................................... 148 EL OPERADOR JOIN .......................................................................................................................... 149 LAS FUNCIONES DE AGREGADO ....................................................................................................... 151 LA CLAÚSULA GROUP BY ............................................................................................................. 154 LA SENTENCIA UPDATE.............................................................................................................. 157 INTRODUCCIÓN ................................................................................................................................ 157 EJEMPLOS......................................................................................................................................... 157 LA SENTENCIA DELETE .............................................................................................................. 163 INTRODUCCIÓN ................................................................................................................................ 163 LA INTEGRIDAD REFERENCIAL......................................................................................................... 164 LA SENTENCIA TRUNCATE TABLE.............................................................................................. 165 EJEMPLOS......................................................................................................................................... 166 PROCEDIMIENTOS ALMACENADOS Y TRIGGERS ............................................................. 169 INTRODUCCIÓN ................................................................................................................................ 169 PARÁMETROS POR REFERENCIA....................................................................................................... 170 PROCEDIMIENTOS ALMACENADOS DE SISTEMA............................................................................... 172 EXTENDED STORED PROCEDURES.................................................................................................... 173 TRIGGERS......................................................................................................................................... 174 EJEMPLOS PRÁCTICOS DE USO DE PROCEDIMIENTOS ALMACENADOS .................. 177 INTRODUCCIÓN ................................................................................................................................ 177 LA SENTENCIA IF ... ELSE............................................................................................................... 177 LA SENTENCIA CASE ...................................................................................................................... 179 EJECUCIÓN DINÁMICA DE SENTENCIAS: LA INSTRUCCIÓN EXEC ................................................... 180 CONVERSIÓN DE TIPOS..................................................................................................................... 181 LA SENTENCIA WHILE.................................................................................................................... 182 TRIGGERS EN SQL-SERVER 2000 .............................................................................................. 183 INTRODUCCIÓN ................................................................................................................................ 183 LAS TABLAS DELETED E INSERTED .................................................................................................. 184 TIPOS DE DESENCADENADORES....................................................................................................... 184 LIMITACIONES DE LOS TRIGGERS..................................................................................................... 184 RESOLUCIÓN DIFERIDA DE NOMBRES .............................................................................................. 185 EJEMPLOS......................................................................................................................................... 185 SEGURIDAD ..................................................................................................................................... 189 INTRODUCCIÓN ................................................................................................................................ 189 CONCESIÓN DE PRIVILEGIOS............................................................................................................ 189 REVOCACIÓN DE PRIVILEGIOS ......................................................................................................... 191 DENEGACIÓN DE PERMISOS ............................................................................................................. 193 MANTENIMIENTO DE VISTAS ........................................................................................................... 193 RECOMENDACIONES ........................................................................................................................ 194 EJEMPLO PRÁCTICO DE IMPLEMENTACIÓN...................................................................... 195 INTRODUCCIÓN ................................................................................................................................ 195 LENGUAJE DE DEFINICIÓN DE DATOS............................................................................................... 196 LENGUAJE DE MANIPULACIÓN DE DATOS ........................................................................................ 198 PRESENTE Y FUTURO DE LAS BASES DE DATOS ................................................................ 203 DISEÑO CONCEPTUAL CON POWER DESIGNOR................................................................. 207 INTRODUCCIÓN ................................................................................................................................ 207 CREACIÓN DE UNA ENTIDAD............................................................................................................ 208 CREACIÓN DE RELACIONES.............................................................................................................. 213 LA RELACIÓNDE HERENCIA............................................................................................................. 215 DISEÑO FÍSICO CON POWER DESIGNOR............................................................................... 217 PASO DEL ESQUEMA CONCEPTUAL AL ESQUEMA FÍSICO ................................................................. 217 MODIFICACIÓN DE LAS TABLAS....................................................................................................... 218 COLUMNAS ...................................................................................................................................... 219 INDICES ............................................................................................................................................ 219 ATRIBUTOS EXTENDIDOS................................................................................................................. 221 TRIGGERS......................................................................................................................................... 221 RESTRICCIONES................................................................................................................................ 222 CREACIÓN DE VISTAS....................................................................................................................... 222 EJEMPLO DE DISEÑO CON POWER DESIGNOR................................................................... 225 Introducción Definición de base de datos La primera pregunta que surge a la hora de comenzar este curso es la siguiente: ¿Qué es una Base de Datos?. Existen varias definiciones para responder a esta pregunta: • "Colección de datos interrelacionados almacenados en conjunto sin redundancias perjudiciales o innecesarias; su finalidad es servir a una aplicación o más, de la mejor manera posible; los datos se almacenan de modo que resulten independientes de los programas que los usan; se emplean métodos bien determinados para incluir nuevos datos y para modificar o extraer los datos almacenados". (Martin, 1975). • "Colección o depósito de datos, donde los datos están lógicamente relacionados entre sí, tienen una definición y descripción comunes y están estructurados de una forma particular. Una base de datos es también un modelo del mundo real y, como tal, debe poder servir para toda una gama de usos y aplicaciones". (Conference des Statisticiens Européens, 1977). • "Conjunto de datos de la empresa memorizado en un ordenador, que es utilizado por numerosas personas y cuya organización está regida por un modelo de datos". (Flory, 1982). • "Conjunto estructurado de datos registrados sobre soportes accesibles por ordenador para satisfacer simultáneamente a varios usuarios de forma selectiva y en tiempo oportuno". (Delobel, 1982). • "Colección no redundante de datos que son compartidos por diferentes sistemas de aplicación". (Howe, 1983). Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 10 • "Colección integrada y generalizada de datos, estructurada atendiendo a las relaciones naturales de modo que suministre todos los caminos de acceso necesarios a cada unidad de datos con objeto de poder atender todas las necesidades de los diferentes usuarios". (Deen, 1985). • "Conjunto de ficheros maestros, organizados y administrados de una manera flexible de modo que los ficheros puedan ser fácilmente adaptados a nuevas tareas imprevisibles". (Frank, 1988). • "Colección de datos interrelacionados". (Elsmari y Navathe, 1989). Como se ha visto, el concepto de base de datos ha ido cambiando a lo largo del tiempo. En la actualidad podemos establecer la definición de base de datos como sigue. "Colección o depósito de datos integrados, almacenados en soporte secundario (no volátil) y con redundancia controlada. Los datos, que han de ser compartidos por diferentes usuarios y aplicaciones, deben mantenerse independientes de ellos, y su definición (estructura de la base de datos) única y almacenada junto con los datos, se ha de apoyar en un modelo de datos, el cual ha de permitir captar las interrelaciones y restricciones existentes en el mundo real. Los procedimientos de actualización y recuperación, comunes y bien determinados, facilitarán la seguridad del conjunto de los datos". Conceptos básicos • Sistema de Gestión de Base de Datos (SGBD): Conjunto de programas que permiten la implantación, acceso y mantenimiento de la base de datos. El SGBD, junto con la base de datos y con los usuarios, constituyen el Sistema de Base de Datos. • Modelo de datos: Conjunto de conceptos que permiten describir, a distintos niveles de abstracción, la estructura de una base de datos, a la cual denominamos esquema. • Sistema de Información: Colección de personas, procedimientos y equipos diseñados, construidos, operados y mantenidos para recoger, registrar, procesar, almacenar y recuperar esa información. • Esquema de una Base de Datos: Estructura de la Base de Datos. • Ocurrencia del esquema: Conjunto de datos que se encuentran almacenados en el esquema en un momento determinado. El esquema no varía mientras no varíe el mundo real que éste describe, mientras que una ocurrencia del esquema es distinta en el transcurso del tiempo. Del enfoque tradicional a los sistemas de bases de datos Para comprender mejor las diferencias entre los sistemas tradicionales basados en ficheros y los sistemas de bases de datos, pongamos un ejemplo. Supóngase que disponemos de un archivo maestro de clientes con la siguiente información: nombre, dirección, ciudad, provincia y teléfono. Si además de esto, añadimos dos ficheros, uno para la emisión de facturas, y otro para la emisión de informes, podemos encontrarnos con los siguientes problemas: • Producción de inconsistencias o incoherencias, debido a la replica de información (la misma información puede estar almacenada en distintos ficheros). © Grupo EIDOS 1. Introducción. 11 • Malgasto de memoria secundaria (por el mismo motivo). • Si en este momento se quiere añadir en número de fax, se hace necesario una completa reestructuración de dicho fichero, sin mencionar el rediseño del código de la aplicación, para dar cabida a este cambio. En cambio, en un sistema de gestión de bases de datos, estos problemas no tienen cabida, ya que el control de la información es inherente al propio sistema. Algunas de las ventajas que ofrece utilizar un Sistema de Bases de Datos son las siguientes: 1. Independencia entre datos y tratamientos. El cambio en los programas no influye en la disponibilidad de los datos, así como la modificación de éstos no afecta a la reprogramación de las aplicaciones que los usan. 2. Coherencia de resultados: Debido a que los datos son almacenados una sola vez, se evitan los problemas que puede ocasionar la redundancia. Más adelante veremos cómo se permite una cierta duplicidad de datos, con el fin de conseguir una mayor eficiencia, controlada por el sistema y que no afecta a la redundancia lógica. 3. Mejor disponibilidad de datos para los usuarios: Los datos son compartidos por un conjunto de usuarios, que accede a ellos de forma concurrente, siempre que estén autorizados a ello. 4. Mayor valor informativo: El conjunto de los datos almacenados en la BD ofrece un mayor valor informativo, que la suma de ellos independientemente. 5. Mayor eficiencia en la recogida, validación e introducción de los datos en el sistema: Al no existir redundancia, los datos se recogen y validan una sola vez, aumentando así la eficiencia. 6. Reducción del espacio de almacenamiento: La desaparición (o disminución) de redundancias, unido a las técnicas de compactación, implica una menor ocupación de almacenamiento secundario. A pesar de todas estas ventajas, los Sistemas de Bases Datos no están exentos de inconvenientes. Aquí se detallan los más importantes: 1. Instalación costosa: La implantación de unsistema de base de datos puede implicar un coste elevado, tanto en el equipo físico (adquisición de nuevas instalaciones, o ampliaciones de las existentes) como en el lógico (sistemas operativos, programas, compiladores, etc.), así como el coste de adquisición o mantenimiento del SGBD. 2. Implantación larga y difícil: Debido a las causas mencionadas anteriormente, la implantación de un sistema de base de datos puede convertirse en una tarea larga y complicada. 3. Falta de rentabilidad a corto plazo: Los amplios costes que conlleva la implantación, implica una rentabilidad no a corto, sino a medio, o incluso largo plazo. 4. Escasa estandarización: Esto supone una dificultad añadida a los usuarios de la base de datos. 5. Desfase entre teoría y práctica: Los constantes avances teóricos en al ámbito de las bases de datos, que muchas veces no se ven traducidos en la práctica, hacen llevarse a engaño a muchos usuarios, creyendo que constituyen ya una realidad, cuando no han sido todavía plasmados. Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 12 Arquitectura ANSI/X3/SPARC ANSI/X3/SPARC es un grupo de estudio del Standard Planning and Requirements Commitee (SPARC) del ANSI (American National Standars Institute), dentro del Comité X3 que se ocupa de ordenadores e informática. En sus estudios acerca de los SGBD, propugnaron una arquitectura basada en tres niveles de abstracción: 1. Nivel Externo: Es el nivel más cercano a los usuarios, y en el se definen los datos tal y como los va a ver este. Cada usuario puede tener su propio modelo externo, con aquellos datos e interrelaciones que dicho usuario necesite. En este nivel, también deberán definirse las restricciones de uso, como por ejemplo el derecho a insertar o borrar determinados datos, o poder acceder a ellos. 2. Nivel Conceptual: Proporciona una descripción global del esquema independiente de la estructura física de la base de datos, es decir, cuales son los datos, como están organizados, las relaciones entre ellos y las restricciones de integridad y confidencialidad. El modelo conceptual, que es único, establece el modelo teórico sobre el que están asentados los modelos externos. 3. Nivel Interno: Nivel más bajo en la abstracción. Describe la estructura almacenamiento físico de los datos, las estrategias de acceso a los datos, etc. Así mismo especifica todos los aspectos relacionados con el hardware, como por ejemplo dispositivos de memoria a usar (tamaño de páginas, número de éstas, tamaño de los buffers, etc.), técnicas de compresión de datos, criptografiado, etc. El modelo interno, que es único, corresponde a la implementación del modelo conceptual. El modelo relacional El modelo más usado, y por lo tanto el que se estudiará en este curso, es el modelo relacional. El motivo de que sea éste el modelo más extendido, radica en la facilidad y en su amplia base matemática, lo que permitirá, como ya se verá más adelante, el poder estructurar o reestructurar las relaciones, para evitar cierto tipo de anomalías, o acelerar las consultas / actualizaciones. Dicho modelo se basa en la representación de la información por medio de estructuras tipo tabla, denominadas relaciones, y que almacenan información para una determinada entidad. Cada una de estas relaciones representa en sus columnas los valores significativos, es decir, de los que interesa conocer su valor, para cada entidad. Dichas columnas se denominan atributos, y para cada uno de ellos existirá un valor (cabe la posibilidad de que existan atributos en los que no aparezca ningún valor). Cada fila representa la información para cada ocurrencia de una determinada entidad. La información se descompondrá, como ya se ha dicho, en dar valores para cada uno de los atributos de la entidad. A dichas filas también se las denomina tuplas. Cada atributo o conjunto de atributos que identifica unívocamente a cada tupla de la relación se denomina clave. Las claves se representan subrayando el / los atributo/s que forman parte de ella. El siguiente ejemplo (Figura 1) representa una relación denominada empleado, que almacena información sobre los empleados de una empresa. La información que se desea saber de cada empleado es su código, su nombre y apellidos, su sueldo y su categoría. Por lo tanto, los atributos son cod_empleado, nombre, apellidos, sueldo y categoría. Además, el atributo cod_empleado es clave de la relación, ya que si se sabe el valor de dicho atributo, se puede saber a que empleado nos estamos refiriendo. © Grupo EIDOS 1. Introducción. 13 Figura 1 Visión general sobre el diseño de bases de datos Fases del diseño El diseño de una base de datos suele descomponerse en tres grandes fases (diseño conceptual, lógico y físico), lo que permite reducir la complejidad que entraña el diseño, a la vez que ayuda a alcanzar los dos principales objetivos que tienen las bases de datos: • Ser una representación fidedigna del mundo real, • Ser un servidor operacional y eficiente de los datos. El diseño conceptual parte de la especificación de requerimientos, y produce como resultado el esquema conceptual de la base de datos. Un esquema conceptual es una descripción a alto nivel de la estructura de la base de datos, independientemente de la elección del equipamiento y del Sistema Gestor de Base de Datos (en adelante referido como SGBD) que se usen para la implementación de la base de datos. El diseño lógico parte del esquema conceptual y genera el esquema lógico. Un esquema lógico es la descripción de la estructura de la base de datos que puede procesarse por un SGBD. Una vez elegido el modelo lógico, pueden existir un conjunto de esquemas lógicos equivalentes al mismo esquema conceptual. La meta del diseño lógico es producir el esquema lógico más eficiente con respecto a las operaciones de consulta y actualización. Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 16 El diseño físico toma como punto de partida el esquema lógico y como resultado produce el esquema físico. Un esquema físico es una descripción de la implementación de la base de datos en memoria secundaria; describe las estructuras de almacenamiento y los métodos de acceso para acceder a los datos de una manera eficiente. Por ello, el diseño físico se genera para un SGBD y un entorno físico determinado. Figura 2. Diseño de una base de datos En la Figura 2 se resumen las tres grandes fases del diseño de una base de datos: primero se diseña el esquema conceptual (que se realiza con un modelo conceptual de datos), esquema que proporciona una descripción estable de la base de datos (independiente del SGBD) que se vaya a utilizar; posteriormente se pasa del esquema conceptual al modelo de datos propio de SGBD elegido (diseño lógico); por último se eligen las estructuras de almacenamiento, los caminos de acceso (índices), y todos los aspectos relacionados con el diseño físico. Figura 3. Correspondencia entre el diseño y la arquitectura ANSI/X3/SPARC © Grupo EIDOS 2. Visión general sobre el diseño de bases de datos. 17 La Figura 3 muestra la correspondencia existente entre las fases de diseño y los niveles de la arquitectura ANSI/X3/SPARC. El diseño conceptual es una etapa previa al diseño lógico. A su vez, el diseño lógico se corresponde con los niveles externo (donde se definen las vistas de usuario, es decir, conjunto de información que puede ser accedida por un usuario) y lógico (estructura de tablas y restricciones) de la arquitectura ANSI/X3/SPARC. El diseño físico se corresponde con el nivel físico de dicha arquitectura. Diseño conceptual El objetivo del diseño conceptual, también denominado modelo conceptual, y que constituye la primera fase de diseño, es obtener una buena representación de los recursos de información de la empresa, con independencia de usuario o aplicaciones en particular y fuera de consideraciones sobre eficienciadel ordenador. Consta de dos fases: • Análisis de requisitos: Es en esta etapa donde se debe responder a la pregunta: ¿qué representar?. Se pretende en esta etapa elaborar un esquema descriptivo del mundo real, mediante distintas técnicas, aunque la más usada es la de entrevistas a los usuarios, lo que implica una descripción de los datos mediante el uso del lenguaje natural. Los problemas que presenta esta primera especificación, se irán refinando hasta obtener el esquema conceptual. • Conceptualización: En esta etapa se intenta responder a la pregunta: ¿cómo representar?. Consiste en ir refinando sucesivamente el primer esquema descriptivo, para conseguir pasar del mundo real al esquema descriptivo y de éste al esquema conceptual, que deberá ser expresado sin tener en consideración cuestiones de implementación, es decir, debe ser independiente del SGBD a usar. Diseño lógico El objetivo del diseño lógico es transformar el esquema conceptual obtenido en la fase anterior, adaptándolo al modelo de datos en el que se apoya el SGBD que se va a utilizar. El modelo relacional es el único modelo que ha permitido abordar la fase de diseño lógico aplicando una teoría formal: el proceso de normalización. Sin embargo, la normalización no cubre toda esta fase, mostrándose insuficiente para alcanzar todos los objetivos de la misma. En la práctica a veces es preciso proceder a una reestructuración de las relaciones. La Figura 4, resume la fase de diseño lógico, en la que una vez estructurado el esquema origen, analizando diferentes factores como las distintas dependencias o la posibilidad de existencia de valores nulos, se obtiene un esquema relacional, al que se añaden las claves ajenas y otras restricciones de integridad. Ahora bien, si se tiene en cuenta el segundo de los objetivos mencionados anteriormente, el de que la base de datos ha de ser un servidor operacional y eficiente de los datos, se hace necesaria una reestructuración de relaciones, con el fin de mejorar la eficiencia de la base de datos. Esta reestructuración toma como entrada el esquema relacional estructurado obtenido anteriormente, así como el análisis de los requisitos de determinadas vistas o aplicaciones críticas, obteniendo de esta manera un esquema relacional resultante, en el que priman las consideraciones de eficiencia. En la Figura 4, se detallan las dos etapas en las que se divide la fase de diseño lógico. La primera, consistente en la estructuración de las relaciones atendiendo a consideraciones de tipo lógico, incluye Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 18 la normalización, así como el particionamiento horizontal de las mismas cuando sea necesario, mientras que en la segunda se reestructuran las relaciones teniendo en cuenta consideraciones de tipo físico que pueden llevar a la desnormalización, o al particionamiento horizontal, vertical o mixto. La razón de esta etapa de reestructuración se encuentra en la falta de flexibilidad de la estructura interna de los actuales SGBD, los cuales no ofrecen los adecuados instrumentos de diseño físico, obligando a trasladar a la fase de diseño lógico consideraciones de eficiencia que deberían ser ajenas a dicha fase. Figura 4. Estructuración y reestructuración de relaciones El objetivo de la estructuración de relaciones es obtener un esquema relacional estructurado, tomando como entrada un esquema relacional origen con todas sus dependencias, valores inaplicables, etc. En esta etapa de estructuración se conseguirá, por razones lógicas, un esquema normalizado, en el cual se han eliminado los valores nulos (inaplicables) mediante un particionamiento horizontal basado en la selección, seguido de la proyección. Las herramientas para llevar a cabo este objetivo son dos: © Grupo EIDOS 2. Visión general sobre el diseño de bases de datos. 19 • El proceso de normalización • El particionamiento horizontal de relaciones El proceso de normalización consiste en sustituir una relación por un conjunto de esquemas equivalentes, que representan la misma información que la relación origen, pero que no presentan cierto tipo de anomalías a la hora de realizar operaciones sobre ella, como se muestra en la Figura 5, en la que una relación origen ha sido sustituida por otras dos, mediante un proceso de normalización. Figura 5. Normalización de relaciones El particionamiento horizontal de relaciones, permite eliminar valores nulos inaplicables que pueden aparecer en las relaciones mediante un proceso de selección, seguido de proyecciones sobre las relaciones resultantes. El resultado de este particionamiento horizontal será la división de una relación en la que existen o pueden existir valores nulos, en varias relaciones en las que los valores nulos inaplicables no tienen cabida. Figura 6. Particionamiento horizontal de relaciones El objetivo de la reestructuración es el de mejorar la eficiencia de la base de datos. En la primera etapa de estructuración de relaciones, se ha propugnado por razones lógicas, normalizar el esquema, así como eliminar los valores nulos mediante un proceso de particionamiento horizontal. Sin embargo, esto no quiere decir que las relaciones que se vayan a almacenar en la base de datos sean las resultantes de estos procesos, ya que se ha logrado el primero de los objetivos de las bases de datos (ser una representación fidedigna del mundo real), pero puede que no el segundo: el de que la base de Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 20 datos ha de ser un servidor operacional y eficiente de los datos, por lo que se hace necesaria esta segunda etapa en el diseño lógico. Para lograr este objetivo existen diversos métodos o formas de organizar los datos, considerando esta idea de mejora de la eficiencia, que son: • El proceso de desnormalización • El particionamiento de relaciones El proceso de desnormalización es justamente el proceso inverso al de normalización. La Figura 7, muestra un ejemplo en el que dos tablas previamente normalizadas, dan origen a una tabla más grande (que es justamente la tabla que se tenía antes de realizar la normalización), mediante un proceso de desnormalización. Figura 7. Desnormalización de relaciones El particionamiento de relaciones es otra forma de conseguir una mayor eficiencia en la base de datos. El objetivo de este proceso es dada una relación, dividirla en otras relaciones de forma horizontal, o vertical, o mixta (incluye ambas). A cada una de estas formas de particionamiento se la denomina, respectivamente, particionamiento horizontal, particionamiento vertical y particionamiento mixto. El particionamiento horizontal de relaciones consiste en la división de las tuplas de una relación en subconjuntos. Esto es muy útil cuando existen consultas que acceden sólo a determinada parte de la información dependiendo del valor de algún atributo, o en bases de datos distribuidas, ya que cada subconjunto puede ubicarse en distintos nodos de la red, acercándose al lugar de su tratamiento. En el particionamiento vertical, los atributos de una relación R son distribuidos en grupos no solapados y la relación R se proyecta en relaciones fragmentarias de acuerdo a estos grupos de atributos. Estos fragmentos se colocan en diferentes localizaciones de la base de datos distribuida. Por ello, el objetivo del particionamiento vertical es crear fragmentos verticales de una relación de manera que minimice el coste de acceso a los elementos de datos durante el procesamiento de la transacción. Si los fragmentos se ajustan bien a los requisitos del conjunto de transacciones facilitado, entonces el coste de proceso de las transacciones podría ser minimizado. El particionamiento vertical también puede usarse en la partición de tablas individuales en bases de datos centralizadas, y en la división de datos entre diferentes niveles de jerarquías de memoria, etc.En el caso de bases de datos distribuidas, el coste de proceso de transacciones se minimiza incrementando el proceso local de las transacciones (en un "nodo") así como reduciendo el número de accesos a objetos de datos que no son locales. © Grupo EIDOS 2. Visión general sobre el diseño de bases de datos. 21 Como su propio nombre indica, el particionamiento mixto engloba a ambos tipos de particionamiento (horizontal y vertical). Consiste en aplicar un particionamiento vertical a uno o más de los fragmentos obtenidos mediante un particionamiento horizontal, o viceversa. Diseño físico El objetivo del diseño físico, que es la última fase del proceso de diseño, es conseguir una instrumentación lo más eficiente posible del esquema lógico. Aquí se tienen en cuenta aspectos del hardware, requisitos de procesos, características del SGBD, del SO y en general, cualquier factor cercano a la "maquina". Con ello se persigue: • disminuir los tiempos de respuesta • minimizar espacio de almacenamiento • evitar las reorganizaciones • proporcionar la máxima seguridad • optimizar el consumo de recursos El principal problema que se plantea en la fase de diseño físico es el debido a la poca flexibilidad de los actuales SGBD, los cuales obligan a trasladar a la fase de diseño lógico, aspectos de carácter físico, que deberían ser ajenos a dicha fase. Esto obliga a iterar desde la fase de diseño físico a la de diseño lógico, y viceversa, hasta obtener conseguir los objetivos anteriormente expuestos, lo que explica la obligación de la etapa de reestructuración en el diseño lógico. Como resultado del diseño físico se genera un esquema físico, que es una descripción de la implementación de la base de datos en memoria secundaria; describe las estructuras de almacenamiento y los métodos de acceso para acceder a los datos de una manera eficiente. Por ello el diseño físico se genera para un SGBD y un entorno físico determinado. Diseño conceptual Introducción Como ya se ha visto en el tema anterior, el diseño conceptual, que constituye la primera etapa en el diseño de una base de datos, consiste en obtener una buena representación de los recursos de información de la empresa, con independencia de usuario o aplicaciones en particular y fuera de consideraciones sobre eficiencia del ordenador. Puesto que no se corresponde con ningún nivel de la arquitectura ANSI/X3/SPARC, sino que es un paso previo, tiende a ser no tenido en cuenta a la hora de proceder al diseño de una base de datos. Esto no es aconsejable, ya que el diseño lógico parte del esquema conceptual y, si éste no es correcto, o no representa fielmente la información del mundo real, el esquema de la base de datos no será estable, viéndonos obligados a reajustarlo constantemente debido a las deficiencias arrastradas desde esta etapa de diseño. De ahí la importancia de realizar un buen esquema conceptual, que represente fielmente las características del mundo real. Otro error que se suele cometer en esta etapa de diseño es el de considerar aspectos tales como la eficiencia del equipo hardware en el que se vaya a montar la base de datos, o SGBD's concretos. Como ya se ha dicho, el esquema conceptual debe representar la información fuera de consideraciones sobre hardware y sobre el SGBD sobre el que se implementará. Por lo tanto, se pueden establecer las siguientes características que debe cumplir un buen esquema conceptual: Debe representar fielmente la información del mundo real • Es independiente del SGBD • Es independiente del Hardware Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 24 Conviene no olvidar, por lo tanto, que un buen diseño del esquema conceptual, influirá positivamente en el resto de etapas. Etapas del diseño conceptual La fase de diseño conceptual, puede subdividirse a su vez en dos etapas: 1. Etapa de análisis de requisitos: En esta etapa se debe responder a la pregunta "¿Qué representar?". El objetivo es elaborar un esquema descriptivo de la realidad, en el que se provean detalles de los datos a representar. Dicho esquema se obtiene mediante el estudio u observación del mundo real (estudio de las reglas de la empresa, entrevista a los usuarios, etc.). Aunque existen muchas respuestas sobre el modo de recoger dicha información, la más utilizada es el lenguaje natural que, aunque carece del formalismo que pueden infligir otros métodos, permite una mejor y más fácil comprensión de la información por parte del usuario, y le permite especificar los requisitos sin la intervención de formalismos. Este primer esquema percibido bruto (como lo llaman Benci y Rolland), se ira refinando sucesivamente, hasta llegar al esquema conceptual. 2. Etapa de conceptualización: En esta etapa se debe responder a la pregunta "¿Cómo representar?". En ella se transforma el esquema obtenido en la primera, mediante refinaciones sucesivas. Se deberá obtener el esquema conceptual mediante una representación normalizada, que se apoye en un modelo de datos que cumpla determinadas propiedades (según Piattini y De Miguel): coherencia, plenitud, no redundancia, simplicidad, fidelidad, etc. El modelo que se estudiará es el Modelo Entidad / relación (en adelante referido como ME/R o modelo E/R), que es el más utilizado hoy en día. El modelo entidad / relación El modelo E/R fue propuesto por Peter P. Chen en dos artículos que publicó en los años 1976 y 1977. En ellos define dicho modelo como una vista unificada de los datos, centrándose en la estructura lógica y abstracta de los datos, como representación del mundo real, con independencia de consideraciones de tipo físico. Posteriormente se fueron proponiendo nuevas aportaciones al modelo, lo cual explica que no exista uno sólo, sino distintos modelos según los autores. Los objetivos que debe cumplir un esquema conceptual son los siguientes (Piattini y De Miguel): 1. Captar y almacenar el universo del discurso mediante una descripción rigurosa. 2. Aislar la representación de la información de los requisitos de máquina y exigencias de cada usuario en particular 3. Independizar la definición de la información de los SGBD en concreto. A continuación se describirá el proceso de creación de un esquema conceptual, siguiendo el modelo E/R. Éste se basa en una representación gráfica de una serie de entidades relacionadas entre sí. Al utilizar una representación de este tipo, el modelo E/R permite distinguir fácilmente y a simple vista, las relaciones existentes entre las distintas entidades. Existen muchas formas de representarlo, como ya se ha comentado; la que se utilizará aquí no es, por supuesto, la única forma de hacerlo. Los elementos de los que se componen son los siguientes: © Grupo EIDOS 3. Diseño conceptual 25 1. Entidades: Una entidad es "una persona, lugar, cosa, concepto o suceso, real o abstracto, de interés para la empresa" (ANSI 1977). En el modelo E/R, se representa por un rectángulo, con el nombre de dicha entidad escrito en la parte superior. Por ejemplo, la Figura 8 representa la entidad automóvil. Figura 8 2. Atributos: Un atributo es cualquier característica que describe a una entidad. Los atributos de una entidad se colocan dentro del rectángulo que representa dicha entidad, justo debajo del nombre de ésta. Por ejemplo, se puede decir que un automóvil tiene las siguientes características: nº de matricula, marca, modelo y color, lo cual se muestra en la Figura 9. Figura 9 3. Clave: La clave de una entidad es un atributo o conjunto de atributos de dicha entidad, que son capaces de identificar unívocamente una ocurrencia de una entidad. Es decir, si conocemos el valor de dichos atributos, seremos capaces de conocer a que ocurrencia de entidad, entre todas las posibles, hace referencia. Esto implica que los valores de los atributos clave no se pueden repetir para dos ocurrencias de la misma entidad. En nuestro ejemplo, seremoscapaces de identificar de que automóvil estamos hablando, con sólo conocer el valor del atributo matrícula, ya que no existe una misma matrícula para dos automóviles distintos. Los atributos marca, modelo o color no identifican unívocamente una ocurrencia de la entidad, ya que pueden existir dos automóviles distintos de la misma marca, modelo o color. En el modelo E/R, un atributo clave se representa subrayando dicho atributo. Figura 10 4. Relación: Una relación representa, como su propio nombre indica, una correspondencia entre dos entidades. Si tenemos dos entidades automóvil y persona, podemos tener una relación entre ellas. Dicha relación se puede establecer en ambos sentidos: una persona posee un automóvil, y Un automóvil pertenece a una persona. Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 26 5. Cardinalidad de una relación: La cardinalidad de una relación representa el número de ocurrencias que se pueden dar de una relación. Puede ser de tres tipos: Cardinalidad 1-1: cada ocurrencia de una entidad se relaciona con una ocurrencia de otra entidad. Ej.: una persona posee un automóvil. Se representa como indica la Figura 11. Figura 11 Cardinalidad 1-N: también llamada uno a muchos. Cada ocurrencia de una entidad puede relacionarse con varias ocurrencias de otra entidad. Ej.: una persona posee varios automóviles. Se representa como muestra la Figura 12. Figura 12 Cardinalidad N-M: también llamada muchos a muchos. Cada ocurrencia de una entidad puede relacionarse con varias ocurrencias de otra entidad y viceversa. Ej.: una persona posee varios automóviles y un automóvil puede pertenecer a varias personas. Se representa como aparece en la Figura 13. Figura 13 6. Cardinalidad máxima de una relación: representa el número máximo de ocurrencias de una entidad con las que se puede relacionarse otra entidad. Ej.: una persona puede tener como máximo tres automóviles. 7. Cardinalidad mínima de una relación: representa el número mínimo de ocurrencias de una entidad con las que se puede relacionarse otra entidad. Ej.: un automóvil debe pertenecer como mínimo a una persona. 8. Entidad débil: se dice que una entidad es débil, o es dependiente de otra, cuando no somos capaces de conocer a que ocurrencia de entidad nos estamos refiriendo, ni siquiera conociendo su clave, sino que debemos conocer el valor de algún otro atributo de otra © Grupo EIDOS 3. Diseño conceptual 27 entidad. Por ejemplo, si tenemos las entidades edificio (con el atributo clave codigo_edificio) y planta (con el atributo codigo_planta), ésta última es una entidad débil, ya que no somos capaces de identificar una planta con sólo conocer el código de la planta, sino que además se necesita conocer el código del edificio al que se hace referencia, para determinar la planta dentro del edificio. Figura 14 En general, en una relación se suele representar conjuntamente las cardinalidades máxima y mínima. En los anteriores casos no se han considerado las cardinalidades mínimas. Éstas vienen a representar la opcionalidad de la ocurrencia de una entidad en una relación, es decir, si dicha ocurrencia se debe dar obligatoriamente, o si por el contrario se puede obviar. Los tipos de cardinalidades son los que aparecen en la Figura 15, (nos fijaremos sólo en un sentido de la relación, el de la izquierda). Cardinalidad máxima 1, cardinalidad mínima 1. Cardinalidad máxima 1, cardinalidad mínima 1. Cardinalidad máxima N, cardinalidad mínima 0. Cardinalidad máxima N, cardinalidad mínima 1. Figura 15 Veamos a continuación unos ejemplos para comprender mejor las cardinalidades máxima y mínima. Como se podrá comprobar, las cardinalidades de una relación se ponen en la última relación a la que se hace referencia, por ejemplo, si se tienen las entidades alumno y asignatura, la cardinalidad de la relación un alumno cursa asignaturas, se pondrá al lado de la entidad asignatura. En el siguiente ejemplo(Figura 16), se tiene una relación 1-1 en la que un automóvil pertenece a una única persona (cardinalidad máxima 1), sin la posibilidad de que exista un automóvil que no tenga dueño (cardinalidad mínima 1). Esto significa que en el modelo no interesa tener información de aquellas personas que no tengan automóvil. Figura 16 En la Figura 17, se tiene una relación 1-1 en la que una persona puede tener un automóvil como mucho (cardinalidad máxima 1), o puede no tener ninguno (cardinalidad mínima 0). Esto significa que el modelo interesa tener información de todas las personas, aunque no tengan automóvil. Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 28 Figura 17 En el siguiente ejemplo (Figura 18), se tiene una relación 1-N, en la que un profesor puede dar clase a muchos alumnos (cardinalidad máxima N), pero como mínimo debe hacerlo a uno (cardinalidad mínima 1). Esto significa que en el modelo no interesa tener información de aquellos profesores que no dan clase. Figura 18 En el siguiente ejemplo, se tiene una relación N-N, en la que una persona puede tener varios automóviles (cardinalidad máxima N), pero puede que no tenga ninguno (cardinalidad mínima 0). Esto significa que en el modelo interesa tener información de todas las personas, aunque no tengan automóvil. Figura 19 Para concluir esta sección se verá un ejemplo completo que representará todos los conceptos vistos hasta ahora. Supongamos que se desea establecer un modelo conceptual para la gestión de una biblioteca. Se desean tener almacenados todos los libros que la componen. Para cada libro interesa conocer el ISBN, el título, el autor o autores, la editorial, el año de publicación y la materia. De cada autor se quiere conocer su nombre, apellidos y nacionalidad. Un autor podrá haber escrito varios libros, de la misma forma que en un libro pueden participar varios autores. De la editorial se desea conocer el nombre y la ciudad. A dicha biblioteca podrán estar suscritos varios usuarios. De ellos se quiere saber su DNI, número de socio, nombre, apellidos, dirección y teléfono. Por cuestiones directivas, se limita el número de ejemplares prestados a cada usuario a uno. Se dispone, a su vez, de un único ejemplar de cada libro, por lo que un libro prestado a un usuario, no podrá ser prestado a otro hasta que se devuelva. Deberá quedar constancia de la fecha de préstamo de cada ejemplar. © Grupo EIDOS 3. Diseño conceptual 29 Figura 20 Lo más destacable del anterior ejemplo es la entidad préstamo. Es una entidad débil que depende de libro y de socio, ya que para diferenciar un préstamo de otro, se necesita saber no sólo el libro, sino el socio al cual se ha prestado. También se pueden observar que las cardinalidades mínimas son 1. Esto quiere decir que sólo se guardará información de las entidades cuando exista, al menos, una ocurrencia de la entidad. Las únicas relaciones que tienen cardinalidad opcional, son las que tienen como origen o destino a la entidad préstamo, lo cual es lógico, ya que tendremos información de todas las entidades, aunque todavía no se haya realizado ningún préstamo. Ejemplos prácticos de diseño conceptual A continuación resolveremos unos problemas de diseño conceptual, para ir familiarizando al lector con los conceptos vistos hasta ahora. Para realizarlos se utilizará la S-Designor, que es una herramienta CASE que abarca gran parte del ciclo de vida de las aplicaciones, incluyendo el diseño de esquemas conceptuales. No se preocupe si no conoce la herramienta, ya que se verá en detalle en próximos temas, simplemente quédese con la idea general de la construcción del esquema. El problema que nos planteamos es el siguiente. Supóngase que se desea informatizar una tienda de discos. Para ello se desean tener almacenados los nombres de todos los discos disponibles, además de sus cantantes y canciones.Así mismo se desean almacenar los clientes que han comprado en dicha tienda. Pues bien, empezaremos identificando las entidades, entendiendo por entidad un grupo de características que tienen entidad propia. Como primera entidad, podemos establecer los discos que se venden, ya que se desea conocer información de ellos, como puede ser un código que lo identifique dentro de la estantería. Por otro lado se desea almacenar todos los artistas que intervienen en los discos de nuestra tienda, y para cada uno de ellos se desea conocer su nombre y apellidos, por lo tanto ya tenemos identificada una segunda entidad. Además, se desea conocer todas las canciones que están disponibles en los discos, identificada cada una de ellas por un código de canción, y que además tendrán sus propias letras. Pues ya tenemos la tercera entidad. La cuarta estará formada por los clientes, de los cuales se desea almacenar su nombre, apellidos, dirección y teléfono, y que podrán estar identificados internamente por un código de cliente. Figura 21 Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 30 Una vez establecidas las entidades, sólo nos queda relacionarlas. Podemos observar las siguientes relaciones: 1. Entre disco y canción: en un disco pueden aparecer varias canciones, y cada canción puede estar en varios discos (N-M). 2. Entre cantante y canción: un cantante puede componer varias canciones, y una canción puede estar compuesta por varios cantantes (N-M). 3. Entre cliente y disco: un cliente puede comprar varios discos, pero un disco sólo puede ser comprado por un cliente 1-N. Por lo tanto, el esquema conceptual es que muestra la Figura 22 Figura 22 Vamos a plantearnos otro problema. Supongamos que se desea tener almacenados todos los datos de los profesores de una empresa dedicada a impartir cursos, así como una breve descripción de éstos, y los alumnos a los cuales se les ha impartido. Empezamos identificando entidades. De un profesor se desea conocer su nombre y apellidos, dirección y despacho, por lo tanto establece una entidad. Otra entidad podría ser el alumno, del cual se desea conocer su nombre, apellidos, dirección y teléfono. Ni que decir tiene que el curso describe otra entidad, de la cual se desea conocer su descripción. Sin embargo, podemos recurrir a un procedimiento muy usual, denominado tipificación de estados, muy usado en el diseño conceptual, y que consiste en tener una entidad que tipifique los posibles estados que puede tomar un atributo. La principal ventaja de este procedimiento radica en que muchas veces supone un ahorro de espacio de almacenamiento (por ejemplo al identificar nombres de ciudades largas con un solo número) además de una estandarización de los datos almacenados (el estado sólo se almacena una vez). Por ejemplo podemos tipificar las ciudades, para lo cual creamos una nueva entidad ciudad, donde se almacenará un código y la descripción de la ciudad. Cuando almacenemos la ciudad de un alumno, sólo deberemos especificar el código de la ciudad. © Grupo EIDOS 3. Diseño conceptual 31 Figura 23 Una vez establecidas las entidades, vamos a definir las relaciones entre ellas. 1. Profesor y Curso: un profesor puede impartir varios cursos, pero un curso sólo puede ser impartido por un profesor (1-N). 2. Alumno y Curso: un alumno puede asistir a varios cursos, y a un curso pueden asistir varios alumnos (N-M). 3. Alumno y Ciudad: un alumno vive en una ciudad, y una ciudad puede tener varios alumnos (1-N). Por lo tanto, el esquema conceptual es el mostrado en la Figura 24. Figura 24 Cabe destacar que el atributo calificación se da como consecuencia de la relación entre las entidades curso y alumno. Por lo que podrá ser introducido en la entidad intermedia que surja cuando se haga el paso a tablas (véase siguiente capítulo). Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 32 Vamos a ver un último ejemplo. Supóngase un banco que desea almacenar todos sus clientes, además de los productos que puede ofrecer a éstos. Cada cliente podrá escoger entre todos estos productos el o los que más le plazcan (créditos, fondos de inversión, libretas de ahorro, etc.). De la misma forma, dicho banco tiene intereses en otras empresas, por lo que desea conocer en todo momento la situación de dichas empresas, para poder mejorar su política de inversiones. Puesto que dicho banco esta constituido como sociedad anónima, desea almacenar todos los componentes de su consejo de administración (actuales y ex-miembros) así como todas las actas de las reuniones ordinarias y extraordinarias. Las decisiones de inversión en estas empresas saldrán como resultado de dichas reuniones, así como la oferta de nuevos productos. Como habrá podido observar, este ejemplo es un poco más complejo, pero no desespere, el proceso es similar al de los demás ejemplos. Empezaremos definiendo entidades y las veremos representadas en la Figura 25. 1. Cliente: se desea conocer su nombre, apellidos, dirección y NIF, y estará identificado por un código interno cod_cliente. 2. Producto: del cual queremos saber su descripción y estará identificado por un código interno cod_producto. 3. Empresa: identifica las empresas en las cuales el banco ha invertido. De ellas se desea conocer su código, nombre y CIF. 4. Consejo: establece los componentes del consejo de administración. Para ello se almacenará el nombre, apellidos y código de cargo de cada uno de sus componentes, y si el cargo es vigente o no. Podremos utilizar una nueva entidad que tipifique los tipos de cargo. 5. Tipo_cargo: describe los posibles cargos que puede tomar una persona en el consejo de administración, y esta compuesto por un código de tipo de cargo, y una descripción del mismo (secretario, presidente, etc.). 6. Reunión: entidad encargada de describir la información de las actas de las reuniones. Sus atributos son cod_reunión, fecha, extraordinaria, que especifica si la reunión ha sido ordinaria o extraordinaria y una descripción. Figura 25 © Grupo EIDOS 3. Diseño conceptual 33 Identifiquemos ahora las relaciones. Su representación gráfica aparece en la Figura 23. 1. Cliente y producto: cada cliente puede escoger varios productos, y cada producto puede ser ofrecido a varios clientes. 2. Consejo y cargo: un miembro del consejo sólo tiene un cargo, y cada cargo puede pertenecer a más de un miembro. 3. Reunión y consejo: a cada reunión pueden asistir varios miembros del consejo de administración, y cada miembro puede asistir a más de una reunión. 4. Reunión y producto: de cada reunión puede salir la oferta de mas de un nuevo producto pero cada producto nuevo sólo puede salir de una reunión. 5. Reunión y empresa: de cada reunión pueden salir decisiones de invertir en más de una empresa, y cada decisión de inversión sólo sale de una reunión. Diseño lógico Introducción Como ya se ha señalado, el diseño lógico de una base de datos consta de dos etapas: el diseño lógico estándar y el diseño lógico específico. En el diseño lógico estándar, se toma el esquema conceptual resultante de la fase de diseño conceptual, y teniendo en cuenta los requisitos de proceso, de construye un esquema lógico estándar (ELS), que se apoya en un modelo lógico estándar (MLS), que será el mismo modelo de datos soportado por el SGBD a utilizar (relacional, jerárquico, etc.), pero sin las restricciones de ningún producto comercial en concreto. En nuestro caso se utilizará el MLS relacional. Una buena forma de describir el ELS es utilizando el lenguaje estándar del MLS (por ejemplo SQL). Una vez obtenido el ELS, y considerando el modelo lógico específico (MLE) propio del SGBD a usar (ORACLE, INFORMIX, SQL-SERVER, etc.), se elabora el esquema lógico específico (ELE). Al igual que en el caso anterior, una buena forma de describirlo es utilizando el lenguajede definición de datos (LDD) del producto especifico utilizado (en el caso de SQL-SERVER, se usará el TRANSACT SQL). El diseño lógico específico está muy ligado a la fase de diseño físico, ya que ambos dependen mucho del SGBD que se utilice. En la fase de diseño lógico, además de las herramientas ya descritas (MLS, MLE, lenguajes SQL), se disponen de otras que permiten establecer un buen diseño lógico, como por ejemplo la normalización, la desnormalización, etc., que ya se verán mas adelante en este tema. Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 36 Paso del esquema conceptual al esquema lógico estándar Lo primero que hay que realizar en la fase de diseño lógico, es obtener el esquema lógico estándar, a partir del esquema conceptual obtenido en la primera fase. Las reglas que permiten pasar del modelo E/R al esquema lógico, son las que a continuación se explican: • Cada entidad se transforma en una relación: esto es, cada entidad genera una tabla, con sus mismos atributos, incluyendo las claves. • Cada relación N-M genera una tabla: las relaciones entre entidades con cardinalidad N-M generan una tabla, con los atributos clave de ambas entidades. • Cada relación 1-N importa las claves de la entidad con las que se relaciona: cada relación con cardinalidad 1-N importa los atributos clave que contiene la entidad con cardinalidad N. • Cada relación dependiente, importa la clave de la otra entidad, como clave. Para entender mejor el funcionamiento de este método, veamos el paso a tablas del ejemplo visto en el tema anterior acerca de la gestión de una biblioteca. La entidad libro está relacionada con la entidad editorial con cardinalidad 1-N, por lo tanto importa la clave de la entidad con cardinalidad 1. A su vez, esta relacionada con la entidad con la entidad autor, pero en este caso, la cardinalidad es N-M, lo que implica que se generará una tabla intermedia, en la que se almacenarán las claves de ambas entidades. Esta tabla, a la que denominaremos Libro_autor mantiene la información de los códigos de libros junto con los códigos de autores. Posteriormente, si se desea extraer más información, tanto del libro como del autor, se deberá acceder a sendas tablas. Por último se dispone de la entidad Préstamo, que es dependiente tanto de la entidad Libro como de la entidad Usuario, lo que quiere decir que se generará una tabla, con los atributos de la entidad Préstamo además de las claves de las entidades de las que es dependiente, es decir, ISBN y Num_socio, que entrarán como claves en dicha tabla. Esta última relación obtenida, mantiene información de qué libros han sido prestados a qué usuarios y en qué fecha. El esquema de las tablas resultantes es el que se muestra en la Figura 26 Figura 26 Veamos ahora el paso a tabla de otro ejemplo visto en el tema anterior, cuyo esquema conceptual es el que muestra la Figura 27. Empezaremos identificando las relaciones, y concretando las tablas que generarán: 1. Cliente-Disco: puesto que es una relación 1-N, la entidad disco generará una tabla con sus atributos, e importará el atributo clave de la entidad con cardinalidad 1, es decir, cod_cliente. A su vez, la entidad cliente generará su propia tabla, con sus propios atributos, es decir, cod_cliente, nombre, apellidos y telefono. © Grupo EIDOS 4. Diseño lógico 37 2. Disco-Canción: es una relación 1-N, a si que, siguiendo el mismo razonamiento anterior, la tabla canción generada importará el cod_disco de la entidad disco. 3. Canción-Cantante: en este caso se tiene una relación N-M, es decir, se generará una tabla intermedia, con los atributos claves de las entidades que relaciona, es decir, cod_canción y cod_cantante. 4. Disco_Cantante: siguiendo el mismo razonamiento, al ser una relación N-M, se generará una tabla intermedia, con los atributos cod_cantante y cod_disco. Figura 27 Con todo esto, el esquema lógico resultante del esquema conceptual anterior, queda como aparece en la Figura 28. Figura 28 Etapas en el diseño lógico Como ya se ha comentado, la fase de diseño lógico de una base de datos consiste en dos etapas: • Etapa de estructuración: donde el objetivo primordial es encontrar un esquema que sea una representación fidedigna del mundo real. La forma de lograrlo es mediante el particionamiento horizontal, para evitar valores nulos, y el proceso de normalización. • Etapa de reestructuración: donde se tienen en cuenta aspectos más ligados con el nivel físico, y que consiste el modificar el esquema obtenido en la fase anterior para adaptarlo a las consideraciones de eficiencia. Esta etapa, que debería ser ajena al diseño lógico, se considera aquí debido a la falta de flexibilidad de los SGBD, obligando a trasladar a esta etapa aspectos Bases de datos con SQL Server 2000 – Transact SQL © Grupo EIDOS 38 mas relacionados con el nivel físico. La forma de lograrlo es mediante la desnormalización, y el particionamiento, bien sea horizontal, vertical o mixto. Comenzaremos por estudiar el particionamiento horizontal de relaciones, usado tanto en la etapa de estructuración como en la de reestructuración, para dar paso posteriormente al proceso de normalización, dejando para el final el particionamiento vertical, mixto y la desnormalización. Particionamiento horizontal de relaciones Como su propio nombre indica, el particionamiento horizontal consiste en dividir longitudinalmente las filas que forman una tabla, esto es, separar las filas que conforman una relación, para llevarlas a otra. Para entenderlo mejor, supóngase el siguiente ejemplo en el que se tiene la tabla publicación, con los siguientes campos: cod_publicación, título, autor, editorial. En un momento dado, la información que tenemos en la tabla es la mostrada en la Tabla 1. Cod_publicación Título Autor Editorial 123BB3-3 El Quijote Miguel de Cervantes Ibérica 113ZD3-0 El impacto de las TI Francisco Hierves 2322-DD Rimas y Leyendas G. A. Becquer Alcalá Tabla 1 Tenemos información sobre dos libros, con su autor y su editorial correspondiente, y un artículo (El impacto de las TI) que, como es obvio, no tiene editorial. En este caso, podría ser conveniente "partir" dicha tabla horizontalmente en otras dos, es decir llevar parte de la información a una tabla, y parte a otra. Aquí la diferenciación se establece entre aquellas filas que tienen editorial, es decir, libros, y aquellas que no tienen editorial, o artículos. Por lo tanto, se crean dos tablas: una para albergar los libros y otra para ubicar los artículos. La Tabla 2 posee la tabla de libros, mientras que la Tabla 3 contiene los artículos. Cod_publicación Título Autor Editorial 123BB3-3 El Quijote Miguel de Cervantes Ibérica 2322-DD Rimas y Leyendas G. A. Becquer Alcalá Tabla 2 © Grupo EIDOS 4. Diseño lógico 39 Cod_publicación Título Autor 113ZD3-0 El impacto de las TI Fco. Hierves Tabla 3 Con esto, lo que hemos conseguido es eliminar la existencia de valores nulos no aplicables. Un valor nulo es aquel que representa información desconocida, inexistente o no válida (en nuestro caso el valor del atributo editorial en el artículo). Los valores nulos pueden ser aplicables o no aplicables. Mientras los no aplicables no cambian, es decir, permanecen nulos, los aplicables pueden dejar de serlo en algún momento. Esto que acabamos de realizar, es el primer paso que se debe seguir en el proceso de estructuración de relaciones. Sin embargo, el particionamiento horizontal no sólo se utiliza aquí, sino que también puede usarse en la fase de reestructuración para conseguir una mayor eficiencia. Por ejemplo, si se dispone de una base distribuida (para entendernos, una base de datos con distintos nodos, situados en distintas localizaciones, con determinada información en cada uno) con información acerca de clientes, y suponiendo que se dispone de dos nodos, uno en Madrid
Compartir