Bases De Datos Relacionales

índice

Módulo A Unidad didáctica 1: Introducción a las Bases de Datos Unidad didáctica 2: Metodologías de desarrollo de Bases de Datos

3 19

Módulo B Unidad didáctica 1: Fase de análisis de requisitos – Modelo E/R Unidad didáctica 2: Modelo Relacional

Módulo C

33 1113

189

Unidad didáctica 1: Álgebra relacional Unidad didáctica 2: SQL – Lenguaje de consulta estructurado

199

MÓDULO A

UNIDADES DIDÁCTICAS:

1. Introducción a las Bases de Datos 2. Metodologías de Desarrollo de Bases de Datos

MÓDULO A OBJETIVOS

En esta unidad aprenderás:

Introducción a las Bases de Datos

x x x x

UNIDAD DIDÁCTICA 1

Índice de la unidad:

1. La Base de Datos como un componente de los sistemas de información 2. Definición de la Base de Datos 3. Sistema de Gestión de Bases de Datos 4. Arquitectura de Bases de Datos a tres niveles 5. Lenguajes de SGBD 6. Herramientas de SGBD 7. Algunas arquitecturas de Sistemas de Bases de Datos

x

Qué es una base de datos y sus principales ventajas Qué es un sistema gestor de bases de datos y sus principales funciones Arquitectura de una base de datos según los tipos de usuarios Qué es el lenguaje SQL Diferencias entre Sistemas centralizados, cliente-servidor, sistemas paralelos y sistemas distribuidos

Introducción a las Bases de Datos

El componente que nos interesa en este libro, la Base de Datos, se

1.

encuentra en el software del sistema de información. A continuación, vamos a

La Base de Datos como un componente de los Sistemas de

dar una definición más precisa de este componente que desglosaremos en todos

Información

sus aspectos. Posteriormente, estudiaremos la necesidad de las Bases de Datos Desglosando en detalle los componentes de un SI nos encontramos con

en los sistemas de información actuales en contraposición a los sistemas de

cinco grandes componentes: -

ficheros.

El Contenido, es decir, los datos con su correspondiente descripción, almacenados en un soporte de ordenador (por ejemplo, en unos grandes almacenes se tendrían los datos de clientes, ventas, productos, etc.).

-

Equipo físico (hardware) formado por la unidad central de proceso y los equipos periféricos (discos, terminales, impresoras, redes, ...).

-

Equipo lógico (software) compuesto por los programas, documentación, lenguajes de programación, etc. que debe gestionar los datos (creación, consulta,

recuperación

y

mantenimiento)

así

como

controlar

las

comunicaciones y dar soporte a tratamientos específicos (por ejemplo, gestión de personal, facturación, etc.). -

El Administrador, encargado de asegurar la calidad de los datos almacenados

y

de

permitir

su

uso

correcto

y

permanente.

El

administrador o administradores debe controlar la disponibilidad, la confidencialidad y la integridad de los datos. La disponibilidad se refiere a que los datos deben estar accesibles en todo momento, es decir, que ante cualquier tipo de catástrofe o fallo, se tengan los mecanismos adecuados de recuperación para que el sistema siga funcionando; la confidencialidad se encarga de no desvelar datos a usuarios no autorizados y la integridad asegura que los datos no se falseen, es decir, que sean correctos, válidos y precisos. -

Un conjunto de Usuarios formado por las personas que acceden al sistema de información y que pueden ser de dos tipos: informáticos (analistas y programadores encargados de desarrollar las aplicaciones, bases de datos, etc.) y los usuarios finales con pocos conocimientos de informática que requieren consultas y actualizar los datos mediante interfaces adecuados a sus características.

5

6

Introducción a las Bases de Datos

partes de la BD y no se controlara: puede ocurrir que si un cliente

2.

cambia de dirección postal y sólo se actualiza esta información en uno de

Definición de Base de Datos

los sitios, entonces la BD quedaría en estado inconsistente (el cliente Definición: Una Base de Datos (BD) es una colección o depósito de

aparece

datos integrados, almacenados en soporte secundario (no volátil) y con

Posteriormente, se volverá al tema de la redundancia cuando se estudien

redundancia controlada. Los datos, que han de ser compartidos por

los modelos de datos como herramientas de diseño de Bases de Datos.

diferentes usuarios y aplicaciones, deben mantenerse independientes de

con

datos

distintos

en

distintas

partes

de

la

BD).

Por otro lado, las BD han de atender a múltiples usuarios de la organización

ellos y su definición (estructura de la BD), única y almacenada junto con

(informáticos que desarrollan programas de acceso a la BD, administrativos

los datos, se ha de apoyar en un modelo de datos, el cual ha de permitir

usuarios de las aplicaciones, etc.) así como a distintas aplicaciones (por ejemplo,

captar las interrelaciones y restricciones existentes en el mundo real.

aplicaciones de contabilidad, de facturación, etc., todas ellas accediendo a los

Los procedimientos de actualización y recuperación, comunes y bien

datos contenidos en la BD de la empresa).

determinados, facilitarán la seguridad del conjunto de los datos. De Esta visión unificada de los datos se contrapone con los sistemas

Miguel et. al (1999)

tradicionales de ficheros. Aunque no entraremos aquí en detalles de en qué Veamos en qué consiste cada uno de los aspectos mencionados en esta

consisten los sistemas de ficheros que se utilizaban con anterioridad a la

definición de Base de Datos que no son más que distintas definiciones según

aparición de las BD sí indicaremos algunos aspectos relevantes que los

distintas perspectivas.

diferencian de las BD; estos aspectos son:

-

-

La Base de Datos es un conjunto de datos relativos a una determinada

-

Independencia de datos y procesos: es el objetivo fundamental de las

parcela del mundo real (por ejemplo, una biblioteca, una empresa

BD, mantener separados los datos de los tratamientos que los utilizan.

petroquímica, una universidad, etc.,) que se almacenan en un soporte

En los sistemas basados en ficheros cada fichero se diseñaba para

informático no volátil (es decir, dispositivos de memoria secundaria como

responder a las necesidades de una aplicación determinada y apenas

discos, cintas, etc. que hacen que los datos no desaparezcan "cuando no

podían utilizarse por otra aplicación. En las BD, los datos se encuentran

se están usando").

en un único almacén y son accedidos por todas las aplicaciones.

Además, no debe existir redundancia (el término redundancia es

-

Descripción de los datos junto con los datos: la definición y descripción2

sinónimo de datos repetidos), es decir, no deben existir duplicidades

del conjunto de datos contenidos en la BD deben ser únicas y estar

perjudiciales ni innecesarias (a ser posible un determinado tipo de dato,

integradas con los mismos datos. Posteriormente se verá que los datos

por ejemplo, los datos de un cliente de una empresa, sólo deben

están interrelacionados y estructurados de acuerdo a un modelo capaz

aparecer en un sitio en la BD). En ocasiones, es necesaria cierta

de recoger el máximo contenido semántico. En los sistemas de ficheros,

redundancia (a nivel de almacenamiento físico1) que mejora la eficiencia

los datos se encuentran en distintos ficheros diseñados expresamente

de la BD, por ejemplo, ante determinados tipos de consultas de datos.

(ad-hoc) para cada tipo de aplicación, y la descripción de los datos se

Sin embargo, esta redundancia siempre debe ser controlada por el

encuentra junto con los programas de la aplicación. Recuérdese, por

sistema para que no se produzcan inconsistencias; piense el lector qué

ejemplo, un programa en un lenguaje de programación en el que al

sucedería si los datos de los clientes de una empresa se repiten en varias

principio del código se define de qué tipo son las variables y las

En secciones posteriores estudiaremos que se habla de tres niveles (conceptual, lógico y físico) en la arquitectura de una BD. Por ahora, nos basta saber que el nivel físico concierne a cómo se almacenan los datos en los ficheros de la BD.

2 En las siguientes secciones se verá que la descripción de los datos es lo que se denomina estructura o esquema de la BD.

1

7

8

Introducción a las Bases de Datos

estructuras de datos que se van a manejar en el programa que accede a los ficheros. -

Procesos

3.

de

actualización

y

recuperación

recuperación y actualización de los datos se

bien

establecidos:

la

En la sección anterior se ha estudiado qué es una BD y se ha mencionado

realiza de acuerdo a

alguna de las funcionalidades de un SGBD. En esta sección nos centraremos en

procesos bien determinados que se incluyen en el Sistema de Gestión de Bases

de

Datos3

instrumentos

(SGBD).

necesarios

El

para

SGBD el

también

mantenimiento

proporcionará de

la

Sistemas de Gestión de Bases de Datos

describir en profundidad qué es un SGBD y cuál es la funcionalidad que debe

los

proporcionar.

seguridad

(confidencialidad, disponibilidad e integridad) del conjunto de datos.

Definición: Un Sistema de Gestión de Bases de Datos (SGBD4) es un conjunto

coordinado

de

programas,

procedimientos,

lenguajes,

herramientas, etc., que suministra, tanto a los usuarios no informáticos como a los analistas, programadores o administradores de una BD, los medios necesarios para describir y manipular los datos integrados en la BD, manteniendo su integridad , confidencialidad y disponibilidad. Miguel et al. (1999).

Aplicación 1

Aplicación 2

Aplicación 3

SGBD

Base de Datos

Figura 1.1: Sistema de Bases de Datos

3

En el siguiente apartado se estudiará que es un SGBD. Por ahora, sólo decir que el conjunto de programas y herramientas que nos permite crear, actualizar, manipular y mantener una BD.

4 En inglés, Database Management System (DBMS). 9

10

...

Aplicación n

De

Introducción a las Bases de Datos

Se denomina Sistema de Bases de Datos a la unión de una BD, un SGBD más las aplicaciones que acceden a la BD. La Figura 1.1 muestra la arquitectura

4.

Arquitectura de BD a tres niveles

de un sistema de BD; en ella se observa que el SGBD hace de interfaz entre los A continuación, vamos a definir cuál es la arquitectura de una BD según la

usuarios que acceden a la BD mediante las aplicaciones y la Base de Datos que

visión que de ella tienen los distintos tipos de usuario. En una BD se identifican

contiene toda la información.

tres capas de estructuración según tres niveles de abstracción. Así, se distingue Ya hemos mencionado en la sección anterior que existen distintos tipos de

un nivel externo, un nivel lógico y un nivel físico.

usuarios (administradores, programadores, usuarios finales) con necesidades -

diferentes. Para poder dar soporte a estos usuarios el SGBD debe proporcionar

El nivel externo se corresponde con la visión de la BD que cada usuario tiene en particular. Esto significa que no todos los usuarios necesitar

una serie de funciones que se describen a continuación:

conocer la BD completa sino que únicamente necesitan una vista parcial -

Función de definición: permite a los diseñadores de la BD describir los

de ella (la que le permita llevar a cabo su trabajo); por ejemplo, un

elementos de datos, su estructura y las relaciones que existen entre

administrativo que trabaje elaborando las nóminas de los empleados de

ellos; como se estudiará más adelante, el SGBD proporciona un lenguaje

una empresa no necesita conocer los datos relativos a las ventas de

para la definición las tablas, los atributos que la componen, las

productos de esa empresa.

restricciones semánticas así como las características de tipo físico o -

almacenamiento.

El nivel lógico se corresponde con la visión total de la empresa; esta vista global se interpone entre el nivel externo y el nivel físico siendo

-

Función de manipulación: permite a los usuarios de la BD añadir,

independiente tanto del equipo como de cada usuario en particular; por

suprimir o modificar los datos de la misma siempre y cuando se respeten

ejemplo, el administrador de la BD si necesita tener una vista completa

los aspectos de seguridad que haya establecido el administrador de la

de la BD de la empresa para llevar a cabo su trabajo.

BD. -

Función de control: esta función aúna los interfaces que requieren los

El nivel físico se corresponde con la vista del soporte físico informático en cuanto a que se refiere a la forma en que se organizan los datos en el

distintos tipos de usuarios para comunicarse con la BD así como las

almacenamiento físico (índices o punteros, longitud de los campos,

herramientas necesarias para el administrador para establecer los

caminos de acceso a los datos, particionamientos de memoria, etc.).

mecanismos de seguridad y mantenimiento de la BD. Para que el SGBD pueda llevar a cabo estas funciones se necesita un La gestión de estos tres niveles debe estar soportada en cualquier SGBD.

lenguaje que permita especificar lo que cada tipo de usuario necesita en su comunicación con la BD. En las BD relacionales se emplea el SQL (Standard Query Language).

11

12

Introducción a las Bases de Datos

5.

Lenguajes de un SGBD

6.

Herramientas de un SGBD

De acuerdo a las funciones a las que debe dar soporte un SGBD estudiadas

Aparte de los lenguajes vistos en el apartado 5, los SGBD proporcionan otro

en el apartado 3 y a los distintos niveles de estructuración de una BD vistos en

tipo de herramientas de gran utilidad en el desarrollo de aplicaciones de Bases

el apartado 4, los SGBD deben proporcionar un lenguaje para que los distintos

de Datos. Entre otras, existen:

tipos de usuario puedan comunicarse con la BD. Así, en los SGBD relacionales se

-

tiene el lenguaje SQL que de acuerdo a su función se descompone en: -

diseño

implementación

de

BD

que

generalmente

incluyen

diagramadores para esquemas conceptuales y lógicos de bases de datos,

estructura lógica de la BD (nivel lógico), la estructuras externas

generadores de código SQL, etc. -

externo) así como la estructura interna (nivel físico).

generadores de informes y pantallas que facilitan la presentación de los datos recuperados de la BD.

Lenguaje de Manipulación de Datos (LMD): una vez se ha descrito la BD,

-

ésta ya está preparada para cargar los datos en las estructuras definidas

generadores de aplicaciones basados en lenguajes de cuarta generación (4GL) que permiten a los usuarios desarrollar aplicaciones sin tener que

y para su utilización. Así, el LMD permite añadir, suprimir, modificar y

programar en lenguajes convencionales

buscar datos en la BD. Es el SGBD el que se encarga de acceder al correspondiente soporte físico para localizar los datos con los que se

-

harán las operaciones especificadas. -

e

Lenguaje de Definición de Datos (LDD): utilizado para definir la

requeridas para el desarrollo de las diferentes aplicaciones (nivel

-

herramientas de ayuda al desarrollo (CASE5) en las fases de análisis,

facilidades de usuario para facilitar la consulta de los datos (menús, interfaces gráficas, etc.).

Lenguaje de Control: el administrador de la BD utiliza este lenguaje para especificar los aspectos de seguridad física (copias de seguridad, rearranque de la BD en caso de caída, etc.) así como de protección frente a accesos no permitidos (autorizaciones y contraseñas, perfiles de usuarios, etc.). El lenguaje de control también se requiere para definir los interfaces que necesitan los distintos usuarios para comunicarse con la BD.

5 Computer Aided Software Engineering 13

14

Introducción a las Bases de Datos

interfaz gráfica. Los sistemas servidores satisfacen las peticiones generadas por

7.

los sistemas clientes.

Algunas arquitecturas de Sistemas de Bases de Datos

Se distinguen dos tipos de servidores:

En este apartado revisaremos brevemente algunos conceptos relacionados con las distintas arquitecturas de Sistemas de BD. En una arquitectura se

-

reflejan aspectos como la conexión en red, el paralelismo y la distribución:

Servidores de transacciones (servidores de consultas) con un interfaz mediante el que los clientes envían peticiones para realizar una acción que el servidor ejecutará y cuyos resultados se devuelven al cliente.

-

-

-

Red: permite que algunas tareas se ejecuten en un sistema servidor y que otras se ejecuten en los clientes (son lo que se denominan sistemas

en las que se realiza el procesamiento enviando después los datos de

de BD cliente-servidor).

vuelta).

Paralelismo: acelerar la ejecución de tareas (transacciones, etc.) de acuerdo al sistema informático subyacente (sistemas de BD paralelos).

-

Servidores de datos (el servidor envía los datos a las máquinas clientes

Respecto a los sistemas paralelos representan una solución al manejo de BD muy grandes o con un gran volumen de transacciones por segundo. El objetivo

Distribución: Datos situados donde se han generado o donde son más

es realizar operaciones simultáneamente mediante el uso de varios procesadores

necesarios pero accesibles desde todos los sitios (sistemas de BD

y varios discos en paralelo. Los modelos de arquitecturas para máquinas

distribuidos).

paralelas son:

Según estos aspectos distinguimos sistemas centralizados, sistemas cliente-

servidor, sistemas paralelos y sistemas distribuidos.

Memoria compartida: Todos los procesadores comparten una memoria común.

En los sistemas centralizados existe un único sistema informático sin -

interacción con otros ordenadores. En estos sistemas podemos diferenciar entre:

Disco compartido: Los procesadores comparten un disco común (cada procesador con su memoria)

-

Sistema monousuario formado por un ordenador personal o por una estación de trabajo con una única CPU y un sistema operativo monousuario

(no

permite

que

varios

usuarios

puedan

acceder

-

Sin compartimiento: No hay compartición ni de disco ni de memoria

-

Jerárquico: modelo híbrido de los anteriores.

simultáneamente a la BD) -

Sistema multiusuario formado por varias CPU y con sistema operativo Por último, en los sistemas distribuidos la BD se almacena en varios

multiusuario con terminales conectados al sistema servidor; estos

ordenadores que no comparten ni memoria ni discos pero que se comunican

terminales no poseen ninguna funcionalidad propia aparte de la de

mediante redes de alta velocidad o líneas telefónicas. Estos ordenadores se

visualizar el resultado de los procesos que se ejecutan en el servidor.

encuentran en varios lugares geográficos distintos. En un sistema distribuido se En los sistemas cliente-servidor existe un reparto de funcionalidades, es

dan dos tipos de transacciones:

decir, los terminales se sustituyen por ordenadores personales que gestionan el interfaz de usuario SQL, interfaz de formularios, diseñadores de informes e

15

16

Introducción a las Bases de Datos

-

Transacciones locales: Acceso a datos del ordenador en el que se inició la transacción

-

Transacciones globales: Acceso a datos de un ordenador distinto o acceso a datos de varios ordenadores distintos. Una Base de Datos (BD) es una colección o depósito de datos integrados, almacenados en soporte secundario (no volátil) y con

Las ventajas que proporcionan los sistemas distribuidos frente a los sistemas

redundancia controlada. Las ventajas que presentan son las siguientes:

centralizados son la compartición de datos (acceso a datos en distintos sitios), por ejemplo,

dos sucursales bancarias pueden compartir datos entre sí;

- Independencia de datos y procesos: - Descripción de los datos junto con los datos - Procesos de actualización y recuperación bien establecidos

la

autonomía en cuanto a que cada administrador controla su BD y, por último, la disponibilidad de los datos pues si un ordenador falla,

En una BD se identifican tres capas de estructuración según tres

están los demás para

niveles de abstracción.

poder seguir trabajando, en particular, si hay duplicación de datos.

- El nivel externo - El nivel lógico - El nivel físico Un Sistema de Gestión de Bases de Datos (SGBD) es un conjunto coordinado de programas, procedimientos, lenguajes, herramientas, etc., que suministra, tanto a los usuarios no informáticos como a los analistas, programadores o administradores de una BD, los medios necesarios para describir y manipular los datos integrados en la BD, manteniendo su integridad , confidencialidad y disponibilidad. Sus principales funciones son las de definición, manipulación y control. En una arquitectura de bases de datos se reflejan aspectos como la conexión en red, el paralelismo y la distribución. Según estos aspectos distinguimos sistemas centralizados, sistemas cliente-servidor, sistemas paralelos y sistemas distribuidos.

Sistemas Centralizados

Monousuario Multiusuario

Sistemas Cliente-Servidor

Servidores de transacciones Servidores de datos

Sistemas paralelos

Memoria Compartida - Disco Compartido Sin compartimiento - Jerárquico

Sistemas distribuídos

17

18

Transacciones Locales

MÓDULO A OBJETIVOS

En esta unidad aprenderás:

Metodologías de Desarrollo de Bases de Datos

UNIDAD DIDÁCTICA 2

Índice de la unidad:

1. Qué es una metodología y para qué sirve 2. Modelos de datos como instrumentos de diseño de Bases de Datos 3. Una metodología de desarrollo de BD 3.1

Modelado Conceptual

3.2

Transformación de esquemas conceptuales E/R a esquemas relacionales

3.3

Diseño físico

x x x

Qué es metodología En qué consiste el diseño de una base de datos Descripción de una metodología propia de diseño de una base de datos, basada en la construcción de esquemas E/R, creación del modelo relacional y posterior optimización de la base de datos.

Metodologías de Desarrollo de Bases de Datos

SOPORTE CASE

Automatizadas

TÉCNICAS

1. Qué es una metodología y para qué sirve

Incorporadas

Defición: Una metodología es un conjunto de procedimientos, técnicas y ayudas

Se apoyan en

METODOLOGÍA

a la documentación para el desarrollo de un producto

software.", Amescua et al. (1995); en el caso que nos ocupa en este libro, el producto software es una Base de Datos

MODELOS

Figura 1.2: Relación entre los componentes de una metodología

Una metodología nos indica las actividades a seguir en el desarrollo de principio a fin de la Base de Datos, qué es lo que hay que realizar en cada actividad indicando qué se necesita como entrada, qué se produce como salida e

Por último, las herramientas CASE permiten dar soporte automatizado a la

incluso quién está involucrado. Por lo tanto, es como un libro de recetas de

aplicación de las técnicas de una metodología así como a los modelos que

cocina en el que se va indicando paso a paso todas las actividades a realizar

incorporan. Los entornos CASE no solo deben automatizar las técnicas aisladas

para lograr la Base de Datos deseada.

correspondientes a una metodología sino también dar soporte a toda la metodología

Como muestra la Figura 1.2, una metodología se apoya en los siguientes

de

desarrollo

mediante

la

incorporación

de

un

conductor

elementos: técnicas, modelos y soporte CASE. Veamos en qué consiste cada uno

metodológico que ayude al analista, diseñador o programador a desarrollar su

de estos elementos.

labor en cada actividad definida en la metodología. Todos estos conceptos se estudiaran particularizados para una metodología

Las técnicas representan cómo llevar a cabo cada una de las actividades o

de BD en las siguientes secciones.

pasos de los que consta la metodología, es decir, proporcionan procedimientos para llevar a cabo cada tarea; en ocasiones estas técnicas son procedimentales (secuencia perfectamente definida de los pasos a realizar en una tarea como en un algoritmo) y en otros casos son heurísticas (reglas, recomendaciones o sugerencias a seguir que en ningún caso establecen el proceso exacto de realización de una tarea; generalmente se utilizan en tareas con un alto componente creativo). Los modelos son los instrumentos que empleamos para representar una determinada realidad (generalmente tienen una notación gráfica que facilita su comprensión y validación); se utilizan en las técnicas para soportar la actividad que llevan a cabo. En el siguiente apartado se estudiarán los modelos de datos involucrados en el diseño de una BD

21

22

Metodologías de Desarrollo de Bases de Datos

determinada realidad (estos elementos varían de un modelo a otro

2. Modelos de datos como instrumentos de diseño de Bases de

según su riqueza semántica).

Datos

- Elementos no permitidos: Son las restricciones que representan las limitaciones impuestas a la estructura del modelo o a los datos que

El diseño de BD consiste en describir la estructura de la BD de forma que se

invalidan ciertos ejemplares de la BD. Las restricciones son de dos

represente fielmente la parcela del mundo real1 que se quiere almacenar. Ello se

tipos:

realiza mediante un proceso de abstracción (que se denomina modelado) que se apoya en un modelo de datos. Un modelo de datos es el instrumento que se

i. Restricciones inherentes: son las limitaciones impuestas a

aplica a un UD para obtener una estructura de datos que se denomina esquema

las estructuras del modelo (reglas impuestas para la

de la BD (Figura 1.3).

utilización y combinación de los distintos constructores del modelo). ii. Restricciones

Mundo Real

semánticas

(o

de

usuario):

son

las

restricciones que se deducen de los supuestos semánticos explícitos o implícitos o derivados de nuestro conocimiento

Modelo de Datos

del mundo real que se quiere reflejar en la BD (por ejemplo,

"todo

empleado

debe

pertenecer

a

un

departamento", "el sueldo de un determinado empleado

Esquema (Estructura de Datos)

siempre será inferior al sueldo de su jefe", etc.). B. Dinámica: Formada por un conjunto de operadores que permiten manipular

Figura 1.3: Aplicación de un Modelo de Datos

los

datos

y

que

están

reflejados

en

el

Lenguaje

de

Manipulación de Datos. Como se estudiará después, esta parte dinámica no tiene sentido para todos los modelos de datos. Un modelo de datos proporciona un conjunto de conceptos, reglas y Además, cada modelo de datos tiene una representación gráfica que suele

convenciones que nos permiten especificar y manipular los datos que queremos

ser en forma de grafos o tablas.

almacenar en la BD. Todo modelo de datos se compone de una parte estática y una parte dinámica como se explica a continuación.

A lo largo del desarrollo de una BD se utilizan varios modelos de datos que

A. Estática: Conjunto de estructuras (también denominados constructores

nos permiten representar la realidad según las distintas fases de una

del modelo) que permiten definir los datos y sus restricciones asociadas

metodología y según distintos niveles de abstracción. Aunque los siguientes

especificados según un Lenguaje de Definición de Datos (apartado 5 del

capítulos se dedicarán al estudio pormenorizado de cada uno de los modelos, la

tema anterior). Esta parte estática consta de elementos permitidos y

Tabla 1 muestra la parte estática y dinámica del Modelo Entidad/Interpelación

elementos no permitidos:

(E/R) para modelado conceptual y el Modelo Relacional para diseño lógico de BD.

- Elementos permitidos: son los objetos, asociaciones entre objetos, propiedades, etc. que proporciona el modelo para representar una

1

En el ámbito de las Bases de Datos se denomina Universo del Discurso (UD) 23

24

Metodologías de Desarrollo de Bases de Datos

Modelo E/R

Modelo Relacional

Elementos permitidos

-

Entidades, atributos, interrelaciones, jerarquías, dominios

Restricciones inherentes

-

Obligatoriedad de Identificador Principal de Entidad. No existen interrelaciones entre interrelaciones. -

-

-

-

-

-

Dinámica

-

Representación -

Restricciones sobre atributos: identificador principal, identificador alternativo, simples/compuestos, univaluados/multivaluados, obligatorios/opcionales y atributos derivados. Restricciones sobre interrelaciones: restricciones de

No existen grupos repetitivos. Regla de integridad de entidad.

-

Definición de clave primaria.

-

Restricción de unicidad. Restricción de obligatoriedad. Integridad referencial (clave ajena). Restricciones de verificación.

-

3. Una Metodología de desarrollo de Bases de Datos

Obligatoriedad de clave primaria. Orden de tuplas y atributos no es significativo.

-

-

Restricciones semánticas

Relación, atributos, dominios

Aunque existen distintas metodologías para el desarrollo de BD, Elsmari y Navathe (1997), en este libro se seguirá la propuesta en De Miguel et al. (1999) que cubre las fases de diseño conceptual, diseño lógico estándar y diseño lógico específico. La figura que se presenta a continuación muestra las fases de esta metodología con los modelos que se aplican en cada una de ellas. Aunque el uso de esquemas conceptuales facilita el diseño de BD no siempre la fase de modelado conceptual se lleva a cabo. La parte derecha de la Figura muestra la metodología completa de diseño de BD mientras que la parte izquierda muestra

cardinalidad, tipo de correspondencia, dependencia en existencia, dependencia en identificación, interrelaciones exclusivas Restricciones sobre jerarquías: exclusividad/solapamiento, totalidad/parcialidad

el diseño de BD partiendo del diseño relacional de una BD.

No es de interés puesto que es un modelo abstracto no implementable

Lenguaje SQL-92.

Grafos

Grafos y tablas.

-

MUNDO REAL OBJETOS CON SUS SUCESOS, PROPIEDADES, ASOCIACIONES Y RESTRICCIONES SEMÁNTICAS

Modelado Conceptual

ESQUEMA CONCEPTUAL

MODELO E/R

Transformación al modelo lógico estándar

MODELO RELACIONAL (SQL-92)

ESQUEMA LÓGICO ESTÁNDAR

MODELO RELACIONAL (SQL-92)

Transformación al modelo lógico específico

MODELO RELACIONAL (SGBD)

MODELO INTERNO (SGBD)

ESQUEMA LÓGICO ESPECÍFICO

MODELO RELACIONAL (SGBD)

ESQUEMA INTERNO

MODELO INTERNO (SGBD)

Veamos a continuación en qué consiste cada una de estas fases: 25

26

Metodologías de Desarrollo de Bases de Datos

3. 1 Modelado Conceptual

3.2 Transformación de esquemas conceptuales E/R a esquemas relacionales

Consiste en la representación del UD (parte del mundo real que se quiere almacenar

los

Una vez se ha validado con el usuario el esquema E/R correspondiente a la

constructores del modelo E/R se recoge toda la semántica que puede

en

la

BD)

en

BD ya es posible realizar la transformación a un esquema lógico, en nuestro

obtenerse mediante la observación del UD o bien a partir de unas

caso, a un esquema relacional. Para este paso si que existe un procedimiento

especificaciones

la

exhaustivo a seguir con el fin de traducir todos los constructores del modelo

información que debe contener la BD. En este libro se construirán esquemas

E/R a constructores del modelo Relacional. En un primer paso, se hace una

conceptuales de BD a partir de esquemas descriptivos.

transformación al modelo relacional estándar (SQL-92). El modelo relacional

textuales

esquemas

(esquemas

conceptuales

descriptivos)

E/R.

que

Mediante

describan

estándar no es directamente implementable en un SGBD relacional pues cada

Esta primera fase de análisis tiene como objetivo poder validar con el

SGBD implementa de manera libre un subconjunto de este estándar. Es en la

usuario (persona o conjunto de personas que nos encargan una BD para

fase de transformación a un modelo lógico específico (es decir, el propio de

cubrir sus necesidades de negocio) la información que contendrá la BD. Por

cada SGBD comercial3) cuando ya se habla de BD directamente trasladables a

ello, los esquemas E/R son los de mayor nivel de abstracción (capacidad para

un producto comercial.

ocultar los detalles y fijarse en lo esencial), con constructores muy naturales (estructuras muy cercanas al usuario y fácilmente comprensibles por personas no informáticas). Nótese que los esquemas conceptuales no son directamente

3.3 Diseño Físico

implementables en un ordenador; por ello, no tienen ninguna connotación física y pueden traducirse a cualquier modelo lógico2. El esquema E/R viene a

En esta fase se tienen en cuenta aspectos relacionados con la carga de la

ser para una BD como los planos de un arquitecto son imprescindibles para

BD, la optimización de consultas y otros aspectos relacionados con la

una casa: algo necesario a priori en la construcción de una BD. Sin estos

eficiencia en el almacenamiento y funcionamiento de la BD y que son

planos

realizadas por el administrador de la BD a través de las utilidades que

es

imposible

conocer

cuáles

son

los

requisitos

que

deberán

contemplarse en la BD.

proporciona el SGBD que se vaya a utilizar.

La construcción de esquemas E/R es una labor creativa que se realiza en

Como se observa en la parte izquierda de la Figura, también es posible

sucesivos pasos de refinamiento; consecuentemente, no todos los analistas

realizar el diseño de la BD directamente en el modelo relacional sin llevar a

obtendrán el mismo esquema E/R cuando modelan una determinada realidad

cabo previamente la fase de modelado conceptual. En este caso, el diseñador

pues dependerá de la labor intelectual que lleve a cabo cada uno en su visión

plasmará directamente en un esquema relacional la semántica del mundo real

del UD. Sin embargo, si es posible seguir una serie de heurísticas o

que debe quedar recogida en la BD.

recomendaciones de gran utilidad cuando se modela una BD. En el capítulo dedicado al modelo E/R se estudiarán estas heurísticas en detalle.

3

Por ejemplo, Oracle, Access, SQL-Server, Informix, etc. son SGBD comerciales que no implementan de igual manera el modelo relacional.

2 Aunque en este libro sólo se estudia el modelo relacional como modelo para Diseño Lógico de BD existen otros modelos (jerárquico, en red, etc.). 27

28

Metodologías de Desarrollo de Bases de Datos

Una metodología es un conjunto de procedimientos, técnicas y ayudas a la documentación para el desarrollo de un producto software.

Un modelo de datos es el instrumento que se aplica a un Universo de Discurso para obtener una estructura de datos que se denomina esquema de la BD

Una metodología de bases de datos consta de tres partes fundamentales:

o

Modelado Conceptual a través del Modelo Entidad-Relación

o

Transformación de esquemas conceptuales E/R a esquemas

o

Diseño Físico. Eficiencia en el almacenamiento y

relacionales

funcionamiento de la base de datos

29

30

MÓDULO B

UNIDADES DIDÁCTICAS:

1. Fase de Análisis de Requisitos: Modelo Entidad Interrelación (E/R) 2. Modelo Relacional

MÓDULO B

OBJETIVOS

En esta unidad aprenderás:

Fase de Análisis de Requisitos: Modelo E/R

UNIDAD DIDÁCTICA 1

Índice de la unidad:

1. Introducción 2. Elementos Básicos del Modelo Entidad/Relación 3. Extensiones del Modelo Entidad/Relación 4. Diseño Caso Práctico – “Mentor” 5. Diseño Caso Práctico – “Historia” 6. Diseño Caso Práctico – “Constructora” 7. Diseño Caso Práctico – “Arte”

x x x

Qué es el modelo Entidad/Relación Cuáles son los elementos básicos del modelo Entidad/Relación y su representación gráfica. Cuáles son los pasos para obtener el Modelo Entidad/Relación a partir de los requisitos previos.

Fase de Análisis de Requisitos : Modelo E/R

1.

conocimiento del mundo real que se desea representar a través de un análisis de

Introducción

los requisitos o especificaciones del problema.

Los datos constituyen en la actualidad el arma más poderosa de cualquier

En la realización del esquema o diseño conceptual de cualquier base de

organización o empresa. Una buena gestión de los datos puede influir de manera

datos es fundamental el conocimiento del problema a modelar y es en este

más que notable en los beneficios de cualquier organización. Pongámonos en el

conocimiento donde representan un papel primordial los usuarios finales del

caso de una entidad bancaria y pensemos en los miles de clientes con cuyos

sistema, pues es en esta primera etapa de modelización en la que el diseñador

datos se realizan operaciones diarias; la mala utilización de los mismos puede

de la base de datos debe hacer tantas entrevistas como sean necesarias con los

traer consigo pérdidas enormes para la empresa. En ocasiones esta mala

usuarios para conseguir clarificar todas las especificaciones del problema. Una

utilización puede ser debida a la falta de formación de los empleados, pero

vez clarificados los objetivos y las necesidades se deberá pasar al diseño

muchas veces es ocasionada por un mal diseño del sistema de información o

propiamente dicho de la base de datos.

base de datos que gestiona los datos. El modelo E/R, como todos los modelos, consiste en un conjunto de Hoy en día todas las empresas cuentan con herramientas informáticas de

conceptos, reglas y notaciones que permiten formalizar la semántica del mundo

creación de bases de datos; entonces, ¿por qué se producen fallos?. La

real que se pretende modelar (también denominada Universo del Discurso) en

respuesta no está en las herramientas en sí, sino, y reincidiendo en el tema, en cómo se

una representación gráfica o diagrama que denominamos esquema de la Base de

diseña la base de datos. Cada herramienta dispone de sus propios

Datos.

utensilios de diseño, pero todos ellos se basan en los mismos conceptos teóricos, En este capítulo se explican cuáles son los elementos básicos que componen

conceptos que si se desconocen no pueden ser aplicados.

el modelo E/R y cómo se utilizan a la hora de diseñar una Base de Datos. Por lo dicho anteriormente parece, si no completamente necesario, sí al menos muy conveniente, la utilización de un modelo de datos que permita

Aunque, como ya se comentó anteriormente, todo modelo de datos tiene

diseñar bases de datos a nivel conceptual (y por tanto muy cercana al usuario) y

una parte estática y otra dinámica; en este capítulo únicamente nos referiremos

por supuesto la formación de personal cualificado en este campo.

a la estática del modelo E/R, pues la parte dinámica carece de utilidad al no ser soportada por ningún SGBD actual.

El modelo Entidad/Interrelación (E/R) es un modelo conceptual que ha demostrado ser muy válido para cumplir con este objetivo, pues está a un nivel de abstracción lo suficientemente elevado como para poder diseñar cualquier base de datos con independencia de la máquina en la que se implemente. Además, en la actualidad disponemos en el mercado de una amplia gama de herramientas que automatizan en gran parte las tareas del diseño1 y que toman como base este modelo de datos. El modelo E/R fue propuesto por Peter Chen en 1976. Desde entonces muchos autores se han interesado por él, estudiándolo y ampliándolo, consiguiendo

así

diversas

variantes

del

modelo

(distintas

formas

de

representación de los objetos), pero todas ellas parten del mismo concepto: el

1

Herramientas CASE (Computer Aided Software Engineering). 35

36

Fase de Análisis de Requisitos : Modelo E/R

Supongamos que de cada alumno queremos la información referente a su

2. Elementos básicos del Modelo E/R

D.N.I., Nombre, Dirección, Teléfono y Nacionalidad. En la figura 2.2, aparece Los elementos u objetos básicos del modelo E/R son cuatro: entidades, interrelaciones,

cómo representamos los atributos en el modelo E/R.

atributos y dominios. A continuación se explican cada uno de

ellos.

2. 1 Entidades Las entidades, también llamadas tipos de entidad, representan conjuntos D.N.I.

de elementos con existencia propia y que se caracterizan por las mismas

Nombre Dirección

propiedades. Generalmente son personas, cosas, lugares,..., es decir,

Teléfono

conceptos sobre los que necesitamos guardar información y distinguibles de

ALUMNO

los demás objetos. Su representación gráfica se hace por medio de un

Nacionalidad

rectángulo dentro del cual se escribe el nombre de la entidad en mayúsculas Figura 2.2: Un ejemplo de entidad con sus atributos

(generalmente un sustantivo). Por ejemplo, si queremos diseñar una base de datos para gestionar todos

Los ejemplares, también denominados ejemplares o elementos, de un tipo

los alumnos de los cursos Mentor, entre los tipos de entidad que deberíamos

de entidad se definen como los valores correspondientes a los atributos que

definir estarían ALUMNO y CURSO. El primero representaría el conjunto de

hemos definido para ella.

todos los alumnos que se inscriben en los diferentes cursos, el segundo

Por ejemplo dos ejemplares del tipo de entidad ALUMNO serían:

recogería todos los cursos ofertados por el aula Mentor. Su representación (DNI, 7515958), (Nombre, Juan), (Dirección, C/ Irún, nº 9 Madrid),

gráfica sería (véase el esquema de la figura 2.1).

(Teléfono, 91-675-65-65), (Nacionalidad, Española) (DNI, 7077777), (Nombre, Ana),(Dirección, C/ Bailén, nº 9, Madrid), (Teléfono, 91-678-98-99), (Nacionalidad, Española) ALUMNO

CURSO

Por lo tanto los valores de los atributos constituyen una parte importante de los datos que almacenaremos posteriormente en la Base de Datos. Es Figura 2.1: Dos ejemplos de entidades

importante destacar que un mismo concepto no tiene por qué representarse siempre de la misma forma (por ejemplo, como una entidad o como un

2. 2 Atributos

atributo). Así, si estuviéramos modelando una Base de Datos para una tienda

Todo tipo de entidad tiene unas características o cualidades propias que

de ropa, probablemente tendríamos una entidad denominada PRENDA y uno

queremos recoger dentro de nuestro diseño. El modelo E/R define estas

de sus atributos podría ser Color (roja, negra, etc.). Sin embargo, si

cualidades como atributos, así por ejemplo el nombre del alumno, el teléfono,

estuviéramos hablando de una Base de Datos para gestionar la información

etc., describen propiedades de cada uno de los miembros que pertenecen al

de un taller de vehículos dedicado a trabajos de chapa y pintura, el concepto

tipo de entidad ALUMNO. Estas propiedades no tienen existencia propia, es

de color puede tener tal importancia que pase a ser una entidad COLOR, pues

decir, sólo tienen sentido en el esquema de la Base de Datos en tanto en

tiene existencia propia y un conjunto de propiedades (código de color,

cuanto aparecen formando parte de una entidad o, como veremos más

textura, tipo de mezcla, etc.).

adelante, de otro de los elementos del modelo E/R, de una interrelación. 37

38

Fase de Análisis de Requisitos : Modelo E/R

Tipos de atributos

En el ejemplo de la figura 2.3, el atributo Teléfono aparece representado con una línea de puntos lo que significa que estamos ante un Atributo

Como se puede observar en la figura 2.2 no todos los atributos se

Opcional que nos informa de que existen alumnos que puede que no tengan

representan de la misma forma; ello significa que existen diversas formas de

número de teléfono o que al fin y al cabo es un atributo cuyo valor no es

recoger restricciones semánticas sobre los atributos de una entidad o de una

demasiado importante y por eso no lo ponemos como obligatorio. Por tanto,

interrelación. En el ejemplo aparece el atributo D.N.I. con un círculo negro, este tipo de atributo se denomina

cuando los valores de un atributo van a ser desconocidos o por alguna otra

identificador principal (IP) y lo que

causa no van a tener valor se denominan Atributos Opcionales.

indica es que el atributo o propiedad DNI es único para cada ejemplar del tipo Supongamos que para el tipo de entidad CURSO es importante recoger las

entidad ALUMNO.

siguientes propiedades: nombre, libro de consulta y dirección Web. De estas Para poder distinguir una ejemplar de otra, dentro de un mismo tipo de

tres características de CURSO elegiremos como identificador principal el

entidad, el modelo E/R obliga a que cada vez que definimos un tipo de

nombre, ya que cada curso tiene un nombre distinto, la dirección Web sería

entidad se defina un atributo que identifique cada ejemplar, es decir, un IP.

un identificador alternativo porque toma valores únicos para cada curso y

Por lo tanto en todos los tipos de entidad tiene que aparecer de forma

libro de consulta sería un atributo opcional ya que permitimos que haya

obligatoria una característica que identifique de forma única cada uno de los

cursos que no tengan o que desconozcamos su libro de referencia. La entidad

ejemplares.

CURSO con sus atributos queda representada en la figura 2.4.

Esta es la representación que nos proporciona el modelo E/R para distinguir este tipo de atributo del resto de atributos que componen el tipo de entidad. En un tipo de entidad sólo puede aparecer un

Nombre

Libro de consulta

Dirección Web

identificador

principal, pero pueden existir distintos atributos que también identifiquen los

CURSO

ejemplares de esta; este tipo de atributos se denominan Identificadores Alternativos (IA).

Figura 2.4: Un ejemplo de atributos IP, IA y opcional

Veamos un ejemplo, supongamos que queremos añadir para el tipo de

Existen otras formas de recoger restricciones semánticas sobre los

entidad ALUMNO, la dirección de correo electrónico que este posee, sabiendo

atributos que se estudiarán en el capítulo siguiente, en donde ampliaremos

que es única para cada uno de los alumnos. El atributo e-mail sería un

estos conceptos.

identificador alternativo y como vemos en la figura 2.3 se representa con un Dominios

círculo mitad negro mitad blanco, indicando que su valor es único para cada ejemplar del tipo de entidad ALUMNO.

Supongamos que el atributo nacionalidad, véase figura 2.5, sólo puede tomar los valores “española” o “extranjera”. Para los conjuntos de valores sobre los que se definen los atributos utilizaremos un objeto del modelo E/R denominado Dominio. Un dominio se define por un nombre y un conjunto de

D.N.I.

Nombre Dirección e-mail

valores. En nuestro ejemplo véase la definición del dominio Nacionalidad en la figura 2.6 resaltado en color azul y cursiva.

Teléfono ALUMNO Nacionalidad Figura 2.3: La entidad ALUMNO y sus atributos 39

40

Fase de Análisis de Requisitos : Modelo E/R

están relacionadas con sus clientes”, “las editoriales se relacionan con los D.N.I. Nombre Dirección e-mail

libros que publican”, “los tutores de los cursos Mentor tienen asignados una serie de alumnos”, etc.

Teléfono

Gráficamente las interrelaciones se representan mediante un rombo unido

ALUMNO

a los tipos de entidad mediante líneas; dentro del rombo se escribe el nombre

Nacionalidad

de la interrelación en minúsculas, que en general, suele coincidir con un verbo

El atributo “Nacionalidad” toma valores del dominio Nacionalidad = (Española, Extranjera)

en infinitivo. Volviendo al ejemplo anterior veamos como se representa la relación

Figura 2.6: Dominio Nacionalidad y su representación textual

existente entre los alumnos que realizan cursos. Podríamos definir una interrelación Realizar entre ambas entidades, como muestra la figura 2.7.

D.N.I. Nombre Dirección e-mail

ALUMNO

CURSO

Realizar

Teléfono ALUMNO

Figura 2.7: Ejemplo de Interrelación

Nacionalidad Nacionalidad

Nacionalidad

No todas las relaciones o asociaciones son iguales, en general se dividen

= (Española, Extranjera)

en relaciones que denominamos de uno a muchos, como por ejemplo la que presentamos a continuación: “una sucursal es únicamente de una entidad

Figura 2.5: Dominio Nacionalidad

bancaria (uno) pero una entidad bancaria posee varias sucursales (muchos)”. También existen las relaciones muchos a muchos, como por ejemplo “un curso Mentor tiene asociados tutores (muchos) y los tutores pueden tutorar distintos cursos Mentor (muchos)”.

En general los dominios no se suelen representar en el modelo por problemas de espacio, pero para tener constancia de los valores que puede

Para

tomar un atributo se suele anotar después de la representación gráfica una

poder

recoger

estas

características

que

nos

distinguen

unas

relaciones de otras, que nos permite, además, recoger más información

representación textual.

acerca del problema que estamos modelando, vamos a introducir los siguientes propiedades de una interrelación: grado, tipo de correspondencia y

2. 3 Interrelaciones

cardinalidad. Las interrelaciones representan asociaciones del mundo real entre una o El grado de una interrelación es el número de entidades que intervienen en

más entidades. Por ejemplo, en la figura 2.1 presentábamos los alumnos y los

ella, debe ser como mínimo dos, es decir, el número de entidades que

cursos del Mentor como entidades sin ningún tipo de relación, pero para poder

intervienen en una interrelación debe ser de al menos dos; existe un caso

expresar que un alumno esta matriculado en distintos cursos y que en un

especial en el que sólo participa una entidad en la interrelación aunque de dos

curso se pueden matricular alumnos necesitamos una Interrelación que nos

formas distintas (es lo que se denomina interrelación reflexiva, como se verá

muestre la asociación existente entre ellos. Por lo tanto, vemos la necesidad

después). En el ejemplo de la figura 2.7 se representa una interrelación

de poder representar este concepto ya que aparece continuamente en el

binaria, denominada así por tratarse de una interrelación entre dos tipos de

mundo real; algunos ejemplos son: “las sucursales de una entidad bancaria 41

42

Fase de Análisis de Requisitos : Modelo E/R

entidad. De la misma forma, cuando el grado es tres se habla de

La cardinalidad de un tipo de entidad que interviene en una interrelación

interrelaciones ternarias y, en general, de interrelaciones n-arias cuando el

binaria se define como el número mínimo y el número máximo de ejemplares

grado es n. El tipo de interrelaciones que aparece de forma habitual en el

de un tipo que pueden relacionarse con un elemento de otro tipo de entidad.

modelado de una Base de Datos es la interrelación binaria y a partir de ahora

Para representar las cardinalidades utilizamos un par (x, y) situado sobre la

nos centraremos solo en este tipo de interrelaciones.

línea que une el tipo de entidad con la interrelación, donde x indica el número mínimo e y el número máximo. Además, y cuando la cardinalidad máxima es

El Tipo de correspondencia de una interrelación binaria se define como el

n, se dibuja una punta de flecha hacia la entidad correspondiente (figura 2.8).

número máximo de ejemplares de un tipo de entidad que pueden estar

En el ejemplo que nos ocupa y suponiendo que no se establece ninguna

asociados con un ejemplar del otro tipo de entidad. Su representación gráfica

restricción adicional, el número mínimo de alumnos que pueden matricularse

se hace por medio de un par X:Y colocado sobre el rombo de la interrelación,

en un curso es uno (no tendría sentido un curso con 0 matriculados), y el

donde X e Y representan los ejemplares asociadas de los tipos de entidad en

número máximo n (número ilimitado), por tanto la cardinalidad del tipo de

estudio2. En nuestro ejemplo, en principio, el número de cursos a los que un

entidad ALUMNO es (1,n) como se muestra en la figura 2.10.

alumno puede optar es ilimitado y el de alumnos que realizan un curso también, por tanto la correspondencia sería N:M o muchos a muchos (Figura 2.8).

M:N ALUMNO

(1,n) Realizar

CURSO

N:M ALUMNO

Realizar

Figura 2.10: Ejemplo de cardinalidades

CURSO

La interpretación de la interrelación Realizar sería “un curso Mentor es Figura 2.8: Tipo de Correspondencia N:M

realizado como mínimo por un alumno y como máximo n”. Si tuviéramos

Si, por el contrario, en las especificaciones del problema se nos dijera que

limitación en la matriculación de los alumnos en un curso, por ejemplo, los

cada alumno solo puede matricularse de un curso, el tipo de correspondencia

cursos Mentor como máximo admiten 40 alumnos, lo representaríamos de la

entre ALUMNO y CURSO cambiaría, sería 1:N o uno a muchos, y se

siguiente forma:

representaría de la manera que aparece en la figura 2.9.

M:N ALUMNO

N:1 ALUMNO

Realizar

(1,40) Realizar

CURSO

CURSO Figura 2.11: Ejemplo de cardinalidades

Figura 2.9: Tipo de Correspondencia N:1

De la misma manera, el número mínimo de cursos que puede realizar un alumno es uno y el máximo n, es decir, la cardinalidad de CURSO es (1,n) y por tanto tendríamos que representar la punta de flecha hacía la entidad CURSO y encima de esta línea la cardinalidad como se muestra en la figura 2

2.12. Esta representación se puede generalizar en el caso de grado n, de la forma X:Y:Z:..... 43

44

Fase de Análisis de Requisitos : Modelo E/R

Los atributos nombre y dirección de EMPLEADO son obligatorios ya que dicha información la consideramos importante; por ejemplo, sin ellos no podríamos mandar la nómina o contactar por cualquier causa con los

M:N ALUMNO

(1,n)

(1,n)

Realizar

CURSO

empleados de la empresa. El teléfono lo podemos considerar como un atributo opcional y, por último, el número de afiliación de la seguridad social (NSS) al tomar valores únicos para cada empleado lo consideraremos un atributo

Figura 2.12: Cardinalidades de la interrelación Realizar

alternativo. La entidad EMPLEADO con sus propiedades queda representada Las cardinalidades mínimas y máximas son, como se puede apreciar, una

como se muestra en la figura 2.15.

extensión del tipo de correspondencia y nos dan más información referente al tipo de interrelación que estamos representando. Código DNI Nombre Dirección

Veamos otro ejemplo con la relación que existe entre los empleados de

NSS

una empresa y el departamento en el que trabajan. Sabemos que un

EMPLEADO

empleado trabaja en un departamento y que a cada departamento se le

Teléfono

asigna al menos un empleado. De cada empleado se desea la siguiente información: un código de empleado (número que le identifica), DNI, nombre

Figura 2.15: Atributos de la entidad EMPLEADO

completo, dirección, teléfono y número de afiliación de la seguridad social. Para los departamentos necesitamos un nombre, único para cada uno de

Un

razonamiento

similar

nos

llevará

a

representar

la

entidad

ellos, una localización y un número de teléfono. ¿Cuál sería su diseño en el

DEPARTAMENTO con las características que la definen como se muestra en la

modelo E/R?.

figura 2.16.

Podemos detectar de forma clara que necesitamos dos entidades, Nombre Localización Teléfono

EMPLEADO y DEPARTAMENTO, objetos que tienen existencia propia con determinadas características. Para la entidad EMPLEADO tenemos como identificador principal el código de empleado, figura 2.13; el DNI, que es

DEPARTAMENTO

único para cada empleado será un atributo alternativo ya que hemos elegido el código como identificador principal por especificaciones del problema (figura 2.14). Figura 2.16: Atributos de la entidad DEPARTAMENTO

La interrelación que une las entidades representadas anteriormente, EMPLEADO y DEPARTAMENTO, es binaria ya que relaciona dos entidades; el tipo de correspondencia es 1:N o de uno a muchos, ya que un empleado está Código

Código

DNI

asignado a un departamento y a un departamento pertenecen varios empleados.

EMPLEADO

Por

último,

se

indican

las

cardinalidades

EMPLEADO

entidades participantes en dicha interrelación (figura 2.17).

Figura 2.13: Entidad EMPLEADO y su IP

que

recogen

explícitamente como se relacionan cada una de los ejemplares de las

Figura 2.14: Entidad EMPLEADO con IP y IA

45

46

Fase de Análisis de Requisitos : Modelo E/R

Código

Nombre Localización Teléfono

DNI Nombre Dirección

Nombre Dirección e-mail

D.N.I.

N:1

NSS

Nombre F_Comienzo

Teléfono EMPLEADO

Trabajar

(1,1)

DEPARTAMENTO

WWW

M:N

(1,n)

ALUMNO

Libro

F_Finalización

Realizar

Teléfono

(1,n)

CURSO

Nacionalidad Figura 2.17: Interrelación Trabajar

Figura 2.19: Interrelación con atributos

La interpretación de la interrelación Trabaja sería la siguiente: “Un

¿Cómo serían los ejemplares de la interrelación “Realizar”? Si pensamos en

empleado trabaja como mínimo y como máximo en un solo departamento

el mundo real, los valores nos vienen dados de la siguiente forma: Juan ha

(1,1)” y tendríamos una línea continua entre el rombo de la interrelación y la

realizado el curso de “Iniciación a Internet” durante el periodo de 12-02-96 al

entidad DEPARTAMENTO para reflejar este hecho.

05-06-96. Algo parecido ocurre en el modelo E/R, los elementos que se encuentran en la interrelación “Realizar” son de la siguiente forma:

Si interpretamos la figura 2.18 desde el tipo de entidad DEPARTAMENTO su lectura sería la siguiente: “En un departamento trabajan como mínimo un

{(DNI, 7515458), (Nombre, Inciación a Internet), (F_Comienzo, 12-02-

empleado y como máximo N”.

96), (F_Finalización, 05-06-96)}. {(DNI, 856593), (Nombre, Access Avanzado), (F_Comienzo, 02-12-96), (F_Finalización, 15-03-97)}.

Código

Nombre Localización Teléfono

DNI Nombre Dirección

Todos estos ejemplares se corresponden con los valores de los atributos

N:1

NSS EMPLEADO

(1,n)

Trabajar

(1,1)

identificadores de los tipos de entidad ALUMNO y CURSO que están

DEPARTAMENTO

relacionados,

junto

con

los

atributos

propios

de

la

interrelación.

La

Teléfono

interpretación o lectura que tienen estos elementos es la siguiente: el alumno con DNI 7515458 ha realizado el curso “Iniciación a Internet” durante el

Figura 2.18: Interrelación Trabajar

periodo del 12-02-96 al 05-06-96; el alumno con DNI 856593 ha realizado el Atributos de una interrelación

curso “Access Avanzado” durante el periodo del 02-12-96 al 15-03-97.

Como ya se ha mencionado, los atributos no solo están referidos a los

Hay que distinguir entre una ejemplar de un tipo de entidad y un tipo de

tipos de entidad. Las interrelaciones también pueden tener atributos propios,

interrelación, pues una ejemplar de un tipo de interrelación existe siempre y

atributos cuyos valores tienen sentido únicamente en el caso de que se

cuando existan ejemplares de los tipos de entidad que intervienen en la

establezca la relación entre los tipos de entidad que las une, como pueden ser

asociación. Los ejemplares no tienen representación gráfica en el modelo E/R

las fechas de comienzo y de finalización de un curso, que no tienen sentido si

pues se corresponden con los datos que realmente se almacenarán en la base

dicho curso no es realizado por al menos un alumno. Un ejemplo de estos

de datos y no con el diseño conceptual de ésta.

atributos se muestra en la figura 2.19 en color verde. Hemos visto los elementos básicos del modelo E/R que nos permitirán el diseño de la Base de Datos de forma Conceptual, es decir, tendremos una representación sencilla y natural del caso que queremos modelar que, además, no depende del Sistema Gestor de Bases de Datos que utilizamos para su posterior implementación y que lo que intentará será recoger de la 47

48

Fase de Análisis de Requisitos : Modelo E/R

mejor forma posible todas las especificaciones del problema de manera que

Grado de una

sea fácilmente comprensible por usuarios no informáticos.

en una interrelación.

Tipo

de

binarias Elementos del

Representación Gráfica

Descripción

Ejemplar

en

Cosa u objeto con identidad propia de ENTIDAD

de

Un

una entidad

ejemplar,

también

de

manera

única

los

ejemplares o ejemplares de una entidad

Identificador

Distingue

alternativo

de

manera

única

los

ejemplares o ejemplares de una entidad

Atributo

Indica que el atributo siempre debe

Obligatorio

tomar un valor para cada ejemplar de la entidad o interrelación a la que pertenece

Atributo

Indica que el atributo puede no tomar

Opcional

valor para cada ejemplar de la entidad o interrelación a la que pertenece

Interrelación

Asociación o relación que existe entre Interrelación

mínimo

de

relacionarse con un único ejemplar de la otra.

de

interrelación

Un ejemplar,

ejemplar, de

una

también

denominado

interrelación

es

la

asociación de los valores de los atributos

participantes en la interrelación.

Característica o propiedad de un tipo

Identifica

y

identificadores principales de las entidades

de entidad.

Identificador

máximo

interrelaciones

binaria

los atributos definidos para ella.

principal

Número

ejemplares de una entidad que puede

una

conjunto de los valores correspondientes a

(x, y)

Máxima

Ejemplar

denominado

ejemplar, de un tipo de entidad es el

Atributo

y

binarias

la que necesitamos guardar información.

con un ejemplar del otro tipo de entidad.

1:1

Cardinalidades Mínima

Entidad

tipo de entidad que pueden estar asociados N:M

interrelaciones

el momento:

Número máximo de ejemplares de un

1:N

Correspondencia en

Veamos un cuadro resumen de los conceptos del Modelo E/R tratados hasta

modelo E/R

Número de entidades que participan

interrelación

entidades.

49

50

Fase de Análisis de Requisitos : Modelo E/R

pueden serlo por dos motivos: bien porque la existencia de sus ejemplares en

3. Extensiones del Modelo E/R

la base de datos depende de una entidad fuerte bien porque sus ejemplares Posteriormente al modelo E/R propuesto por Chen se realizaron algunas

requieran para su identificación de los atributos identificadores (algunas veces

extensiones para darle más riqueza semántica. Esto significa que se le han

llamados atributos externos) de otra entidad.

añadido nuevos conceptos para que el modelo se adapte mejor a la realidad que Por ejemplo, los ejemplares correspondientes a los alumnos del MENTOR

queremos modelar, es decir, recoja mayor semántica.

no dependen de ninguna otra entidad para existir en la base de datos; por Vamos a introducir algunos de estos nuevos conceptos retomando el

ello la entidad ALUMNO es una entidad fuerte. Sin embargo, en el caso de una

ejemplo visto en el apartado anterior sobre una empresa en el que habíamos

base de datos de una cadena hotelera podríamos tener el tipo de entidad

representado la relación que existía entre los empleados y los departamentos de

HABITACIÓN dependiente del tipo de entidad HOTEL ya que para que existan

la empresa.

ejemplares de HABITACIÓN es necesario que existan ejemplares de HOTEL.

Supongamos que la empresa es un consorcio de distintas librerías

Una ejemplar de HABITACIÓN no tiene existencia por si misma porque

especializadas en libros y revistas informáticas llamada INTERFAZ. Sabemos que

siempre estará asociada a una ejemplar de HOTEL. Además, si se elimina un

los empleados de INTERFAZ están asignados a un departamento y que la

determinado ejemplar de la entidad HOTEL de la base de datos también

relación entre EMPLEADO y DEPARTAMENTO se representa como se indica en la

deberán desaparecer los ejemplares de la entidad HABITACIÓN asociadas a

figura 2.20.

él. La representación de una entidad débil difiere de la de una entidad regular pues el rectángulo de la entidad débil es de doble recuadro como se muestra en la figura 2.21.

N:1 DEPARTAMENTO

Teléfono (1, 1)

(1, n) Trabajar

EMPLEADO

DNI

DNI

ENTIDAD DÉBIL

NSS Nombre Dirección

Nombre Localización Teléfono

Figura 2.21: Notación gráfica de una entidad débil

Figura 2.20: Interrelación Trabajar

3. 2 Interrelaciones binarias

3. 1 Entidades

La clasificación anterior entre entidades fuertes y débiles da lugar a dos En el apartado 2 se estudió que las entidades en un esquema E/R son los

tipos de interrelaciones según los tipos de entidades que asocian.

objetos principales sobre los que debe recogerse información y generalmente denotan personas, lugares, cosas o eventos de interés. En esta sección vamos

Las interrelaciones regulares relacionan tipos de entidades regulares o

a estudiar cómo las entidades pueden clasificarse por la fuerza de sus

fuertes. Las interrelaciones débiles relacionan un tipo de entidad regular y un

atributos identificadores.

tipo de entidad débil. Además, en las interrelaciones débiles podemos distinguir:

Las entidades fuertes o regulares tienen existencia propia, es decir, poseen identificadores internos que determinan de manera única la existencia de sus

Dependencia en existencia. Este tipo de interrelación refleja que los

ejemplares. Las entidades débiles son dependientes de otras entidades y

ejemplares del tipo de entidad débil que se relacionan con un determinado 51

52

Fase de Análisis de Requisitos : Modelo E/R

ejemplar del tipo de entidad regular dependen de él y, si éste desaparece,

Como se muestra en la figura 2.23 el IP, es decir, el número de habitación

ellos también. Veamos un ejemplo que clarifique esta definición:

se repite para distintos hoteles (la habitación número 1 existe en el hotel “Mar” y en el hotel “Sol”). Para solucionar este problema, existen dos

Supongamos que la empresa INTERFAZ necesita conocer los datos de los

soluciones:

familiares que están a cargo de cada empleado de la empresa (cónyuge, hijos, etc.) para de esta manera apoyar a aquellos cuya carga familiar sea

1

numerosa. 1

Para saber los familiares que dependen de cada empleado debemos crear

Abeto Roja

SI SI

n°_habitación nombre WC

habitación perteneciente al Hotel “MAR“

un nuevo tipo de entidad, que denominaremos FAMILIAR, cuyos atributos podrían ser el DNI (como IP), el nombre completo y parentesco con el

habitación perteneciente al Hotel “SOL“

HABITACIÓN

empleado. Como se puede observar, la existencia de un miembro de la familia depende plenamente de que ese miembro tenga a una persona de su familia trabajando en la empresa, o lo que es lo mismo que exista un ejemplar de El valor del IP se repite para hoteles distintos

EMPLEADO que este relacionado con él; es decir, los familiares sólo existen en

Figura 2.23: Ocurrencias de la entidad HABITACIÓN

la base de datos si existe un empleado con el que se relacionen y si un determinado EMPLEADO se va de la empresa, entonces se eliminarán todas

1. La primera consiste en cambiar el IP, por ejemplo, poner el nombre de

los ejemplares de FAMILIAR que dependan de él. Así, tenemos una

la habitación como IP; esto significa que los nombres de la habitación no

interrelación de dependencia en existencia entre EMPLEADO y FAMILIAR

pueden repetirse en los distintos hoteles y esto no es posible asegurarlo.

representada como muestra la figura 2.22. 2. La segunda, y más razonable, consiste en crear una interrelación débil de dependencia en identificación, es decir, los ejemplares de la entidad débil requieren para su identificación de los atributos identificadores de la entidad

N:1 (0, n) FAMILIAR

E

fuerte.

Teléfono EMPLEADO

Encargado

Así,

cada

ejemplar

de

HABITACIÓN

está

identificada

por

la

concatenación de su número y del nombre del hotel en que se encuentra. Por

(1, 1)

ejemplo, la habitación 1 “Sol”, habitación 1 “Mar”, etc. Su representación es la que se muestra en la figura 2.24. NSS

DNI DNI

Nombre

Parentesco

Nombre Dirección

Figura 2.22: Ejemplo de una interrelación con Dependencia en Existencia Nº Habitación

Dependencia en Identificación: Este tipo de interrelación complementa a la

Teléfono

anterior en que, además de que los ejemplares del tipo de entidad débil (1, n)

dependen de la existencia de un ejemplar de la entidad regular, también

HABITACIÓN

I Posee

(1, 1) HOTEL

necesitan para su identificación el IP de la entidad regular. Así, veíamos anteriormente que la entidad HABITACIÓN era débil respecto al HOTEL al que

Nombre WC

pertenece. Si construimos las interrelación existente entre ambas entidades debemos pensar si el atributo “Nº de Habitación” de la entidad HABITACIÓN

Figura 2.24:

es suficiente para identificar cada ejemplar de esta. 53

54

N:1 Nombre Dirección Ejemplo de una interrelación con Dependencia en Identificación

Fase de Análisis de Requisitos : Modelo E/R

Supongamos que en la entidad Empleado queremos recoger que un empleado puede tener más de un teléfono, tendríamos un atributo Teléfonos

Otro tipo de interrelación es la denominada jerárquica que expresa la

que tendría cero o más valores, esto es lo que llamamos atributo multivaluado

clasificación de un determinado tipo de entidad en uno o más tipos de

y se representa como se muestra en la figura 2.26.

entidad. Por ejemplo, supongamos que la empresa Interfaz tiene tres departamentos INFORMATICA, PUBLICACIONES y RECURSOS HUMANOS. Esta

Otro tipo de atributo es el atributo compuesto, que representa una

clasificación de los departamentos se representaría como una jerarquía

agregación de atributos simples. Vamos a modificar el atributo Nombre de la

(también denominada generalización). Las generalizaciones nos proporcionan

entidad EMPLEADO ya que queremos un atributo, Nombre Completo,

un mecanismo de abstracción que permite descomponer una entidad (que se

compuesto por Nombre y Primer Apellido. Su representación sería la que se

denominará supertipo) en subtipos. De esta forma vemos un conjunto de

muestra en la figura 2.26.

ejemplares de una entidad como de otra entidad. Así, por ejemplo, una "Persona" es un "Animal" y un "Reptil" es un "Animal"; en este caso, "Animal" puede considerarse el supertipo y "Persona" y "Reptil" son subtipos de

Nombre

Primer Apellido

"Animal". Los ejemplares o ejemplares de "Persona" lo son también de

Dirección e-mail

"Animal" e igual sucede con las de "Reptil".

Nombre Completo

La figura 2.25 muestra la jerarquía de departamentos de la empresa

Teléfono EMPLEADO

D.N.I.

INTERFAZ representada por un triángulo invertido que une el supertipo con

Nacionalidad

los subtipos. Figura 2.26: Ejemplo de atributo compuesto

Además, todas las restricciones semánticas definidas para los atributos Código

Nombre

pueden combinarse entre sí, es decir, (pueden existir en un esquema E/R

Ubicación

atributos

Número de empleados DEPARTAMENTO

Nombre director

Teléfono contacto

PUBLICACIONES

Facturación

RECURSOS HUMANOS

INFORMÁTICA

e-mail de contacto

multivaluados

simples

opcionales,

univaluados

compuestos

opcionales, multivaluados obligatorios, multivaluados compuestos, etc.).

Número de equipos

Figura 2.25: Ejemplo de generalización total exclusiva

3. 3 Atributos En este apartado ampliaremos nuestro conocimiento acerca de las restricciones semánticas sobre los atributos de las entidades y de las interrelaciones para de esta forma poder representar más fielmente los requisitos que nos piden para el diseño de una determinada base de datos. 55

56

Fase de Análisis de Requisitos : Modelo E/R

quiere almacenar en la BD acerca de los tutores es la siguiente: DNI, nombre completo y dirección de correo electrónico. La información que se desea almacenar en la Base de Datos se refiere Para este caso práctico se ha pensado en una continuación del ejemplo

a los alumnos matriculados en cada curso, teniendo en cuenta la

que se ha ido desarrollando a lo largo de este capítulo sobre el diseño de una

fecha de inicio y la fecha de finalización de cada alumno en un

Base de Datos que recoja información acerca del proyecto llevado a cabo por el

determinado curso y sabiendo que un alumno se ha podido matricular

Ministerio de Educación denominado MENTOR. Hay que tener en cuenta que los

de uno o varios cursos y que un curso tiene como mínimo un alumno.

supuestos semánticos de este ejemplo son hipotéticos. A continuación se expondrán los requisitos que se van considerar en este apartado para llevar a

El proyecto MENTOR, además, tiene en cuenta que ha de facilitar a los

cabo el diseño de la base de datos. Dicho proyecto se encarga de ofertar cursos

alumnos el acceso a Internet y por lo tanto ha instalado aulas con

por Internet para alumnos del territorio nacional.

todos los servicios necesarios para el pleno desarrollo de los cursos. Cada alumno pertenece a un aula y el mantenimiento tanto de los

La información que se desea almacenar en la Base de Datos se refiere a

ordenadores como de los programas se lleva a cabo por los

los alumnos matriculados en cada curso, teniendo en cuenta la fecha de inicio y

administradores de aula.

la fecha de finalización de cada alumno en un determinado curso y sabiendo que un alumno se ha podido matricular de uno o varios cursos y que un curso tiene

Cada aula tiene asignado un código único, descripción y una dirección.

como mínimo un alumno. La información que se necesita de cada administrador es su DNI, nombre completo, dirección de correo electrónico.

De los alumnos se desea saber el DNI, nombre completo, dirección, teléfono, nacionalidad, pero sólo interesa saber si la nacionalidad es española o no, y la dirección de correo electrónico. La dirección de correo electrónico es imprescindible para poder realizar los cursos y además es única para cada alumno. La información referente a los cursos consta del nombre, título del libro de consulta que se utiliza (aunque existen cursos que no lo poseen) y dirección de Internet donde se encuentra todo el material que se puede utilizar durante el curso. Cada curso tiene asociado un grupo de personas expertas, llamadas tutores, que son las encargadas de resolver las dudas propuestas por los alumnos, la evaluación de los mismos e incluso el hombro para que estos se desahoguen. Dentro de los tutores de un mismo curso existe una figura importante que es la de coordinador que se encarga de realizar labores de unificación y planificación. No hay que olvidar que una persona experta puede ser tutora de varios cursos y que además un coordinador de curso es un tutor. La información que se

57

24

Fase de Análisis de Requisitos : Modelo E/R

La información referente a los cursos consta de nombre de este, título

4. DISEÑO PROPUESTO AL CASO PRÁCTICO - MENTOR

del libro de consulta que utiliza (aunque existen cursos que no lo Para realizar el diseño conceptual de la BD en el modelo E/R seguiremos una

poseen) y dirección de Internet donde se encuentra todo el material

serie de pasos que nos ayudarán a identificar los elementos básicos del modelo.

del que consta. Curso es una entidad

Estos pasos son iterativos, es decir, un esquema E/R se construye según distintas fases de refinamiento. Además, las soluciones no son únicas, cada

Cada curso tiene asociado un grupo de personas expertas, llamadas

diseñador puede ver el mundo real de distinta forma, dando lugar a distintos

tutores, que son las encargadas de resolver los problemas propuestos

esquema E/R válidos. Sin embargo, sí se puede estudiar si un determinado

por los alumnos, la evaluación de los mismos e incluso el hombro

esquema E/R refleja mejor que otro los supuestos semánticos del enunciado. No

para que estos se desahoguen. Dentro de los tutores de un mismo

hay que olvidar que en el esquema E/R de una base de datos hay que recoger la

curso existe una figura importante que es la de coordinador que se

mayor semántica posible y no dejar para las siguientes fases de desarrollo

encarga de realizar labores de unificación. No hay que olvidar que

(diseño lógico e implementación) ningún supuesto semántico, siempre que sea

una persona experta puede ser tutora de varios cursos y que además

posible.

un coordinador de curso es un tutor. La información que se quiere almacenar en la BD acerca de los tutores es la siguiente: DNI, nombre completo y dirección de correo electrónico. De este párrafo 1º paso: Identificar y enumerar las posibles entidades teniendo en

podemos extraer que, por un lado necesitamos una entidad para

cuenta la siguiente heurística: en general, un tipo de entidad es un

almacenar los datos de los tutores y por otro lado vemos que se

sustantivo dentro de una oración con una seria de propiedades o

destaca en el texto la labor del coordinador y se podría pensar si es o

características tales como, DNI del alumno, nombre del curso, etc.

no un atributo de la entidad tutores. El proyecto MENTOR, además, tiene en cuenta que ha de facilitar a los

La información que se desea almacenar en la Base de datos se refiere a los

alumnos el acceso a Internet y por lo tanto a instalado aulas con

alumnos matriculados en cada curso. Teniendo en cuenta la fecha de inicio de

todos los servicios necesarios para el pleno desarrollo de los cursos.

cada alumno en un determinado curso así como su fecha de finalización y

Aunque, por ahora desconocemos posibles atributos de las aulas

sabiendo que un alumno se ha podido matricular de uno o varios cursos y que un

parece que interactúa lo suficiente como para pensar en que pueda

curso tiene como mínimo un alumno.

ser una entidad

En el texto presentamos en negrita y subrayado los tipos de entidad que

Cada alumno pertenece a un aula y el mantenimiento tanto de los

hemos detectado.

ordenadores como de los programas se lleva a cabo por los administradores de aula. Aula va cogiendo peso como posible entidad

De los alumnos se desea saber el DNI, nombre completo, dirección, teléfono, nacionalidad, pero sólo interesa saber si la nacionalidad es

Cada aula tiene asignado un código único, una descripción y una

española o no, y la dirección de correo electrónico. La dirección de

dirección.

correo electrónico es imprescindible para poder realizar los cursos y

Definitivamente

aula

es

una

entidad,

ya

nos

han

especificado sus atributos

además única para cada alumno. Observamos que del único sustantivo que tenemos información que almacenar (DNI, nombre

La información que se necesita de cada administrador es su DNI,

completo, dirección, teléfono, nacionalidad,e-mail) es de alumnos

nombre completo, dirección de correo electrónico. Este párrafo

luego éste, será una entidad.

muestra claramente que administrador es una entidad ya que es necesario almacenar información sobre él. 59

60

Fase de Análisis de Requisitos : Modelo E/R

Los tipos de entidades que hemos localizado son: ALUMNO, CURSO, TUTOR,

por los alumnos, la evaluación de los mismos e incluso el hombro

AULA y ADMINISTRADOR. Del enunciado se podría deducir que COORDINADOR

para que estos se desahoguen. Dentro de los tutores de un mismo

es también un tipo de entidad; dejamos para más adelante la discusión sobre si

curso existe una figura importante que es la de coordinador que se

este concepto puede representarse como una entidad, un atributo o una

encarga de realizar labores de unificación. No hay que olvidar que

interrelación.

una persona experta puede ser tutora de varios cursos y que además un coordinador de curso es un tutor. La información que se quiere almacenar en la BD acerca de los tutores es la siguiente: DNI, nombre completo y dirección de correo electrónico. De este párrafo podemos extraer que, por un lado necesitamos una entidad para

2º paso: Identificar y enumerar las posibles interrelaciones, teniendo en cuenta la siguiente heurística: en general, una interrelación viene

almacenar los datos de los tutores y por otro lado vemos que se

reflejada por un verbo dentro de una oración que relaciona dos objetos.

destaca en el texto la labor del coordinador y se podría pensar si es o no un atributo de la entidad tutores. El proyecto MENTOR, además, tiene en cuenta que ha de facilitar a los

En el texto aparece un número correlativo como superíndice en los verbos

alumnos el acceso a Internet y por lo tanto a instalado aulas con

que indican la posible existencia de una interrelación.

todos los servicios necesarios para el pleno desarrollo de los cursos. La información que se desea almacenar en la Base de datos se refiere a

Aunque, por ahora desconocemos posibles atributos de las aulas

los alumnos matriculados1 en cada curso. Teniendo en cuenta la

parece que interactúa lo suficiente como para pensar en que pueda

fecha de inicio de cada alumno en un determinado curso así como su

ser una entidad

fecha de finalización y sabiendo que un alumno se ha podido Cada alumno pertenece3 a un aula y el mantenimiento4 tanto de los

matricular de uno o varios cursos y que un curso tiene como mínimo

ordenadores como de los programas se lleva a cabo por los

un alumno.

administradores de aula. Aula va cogiendo peso como posible entidad De los alumnos se desea saber el DNI, nombre completo, dirección, Cada aula tiene asignado un código único, una descripción y una

teléfono, nacionalidad, pero sólo interesa saber si la nacionalidad es española o no, y la dirección de correo electrónico. La dirección de

dirección.

correo electrónico es imprescindible para poder realizar los cursos y

especificado sus atributos

además única para cada alumno. Observamos que del único

Definitivamente

aula

es

una

entidad,

ya

nos

han

La información que se necesita de cada administrador es su DNI,

sustantivo que tenemos información que almacenar (DNI, nombre

nombre completo, dirección de correo electrónico. Este párrafo

completo, dirección, teléfono, nacionalidad,e-mail) es de alumnos

muestra claramente que administrador es una entidad ya que es

luego éste, será una entidad.

necesario almacenar información sobre él.

La información referente a los cursos consta de nombre de este, título

Para que nos sea más sencillo saber qué tipos de entidades están

del libro de consulta que utiliza (aunque existen cursos que no lo

relacionadas vamos a construir una matriz donde en la primera fila y la primera

poseen) y dirección de Internet donde se encuentra todo el material

columna se enuncian los tipos de entidad anteriormente enumerados y se

del que consta. Curso es una entidad

señalará en el cruce de filas y columnas aquellas interrelaciones que hemos

Cada curso tiene asociado2 un grupo de personas expertas, llamadas

detectado. De esta forma se facilita también la identificación de posibles

tutores, que son las encargadas de resolver los problemas propuestos

interrelaciones que no aparecen explícitamente expresadas en los supuestos semánticos del enunciado pero que son, bien de sentido común, bien deducidas 61

62

Fase de Análisis de Requisitos : Modelo E/R

del enunciado; estas interrelaciones también tienen que aparecer en el esquema

D.N.I.

Nombre

Dirección

ALUMNO

WWW

Teléfono

ALUMNO

Interrelaciones ALUMNO CURSO

Libro

Nombre

e-mail

E/R de la base de datos.

CURSO Matricular 1

TUTOR AULA ADMINISTRADOR

TUTOR

AULA Pertenecer 3

Matricular

CURSO

Nacionalidad

ADMINISTRADOR

Figura 2.27: Interrelación Matricular

Asociar 2 ¿Coordinar?

El estudio del tipo de correspondencia y las cardinalidades máximas y

Mantener 4

mínimas también se realizó durante el desarrollo del capítulo pero recordaremos que la correspondencia es de muchos a muchos (N:M) ya que un alumno puede matricularse de uno o varios cursos, cardinalidad (1,n), y en un curso se

En la tabla se muestra el nombre de las interrelaciones y una numeración

matriculan uno o varios alumnos, cardinalidad (1,n). Además, para saber las

que indica el orden en el que han ido apareciendo en el texto; se muestran

fechas en las que un alumno inició y finalizó un curso se introducen dos atributos

sombreadas las celdas que representan interrelaciones simétricas a las definidas

que pertenecen a la interrelación Matricular (figura 2.28)

en el resto de las celdas. Además de las interrelaciones extraídas del texto hay que estudiar si en las celdas vacías deberían aparecer nuevas interrelaciones. Así, podría existir la interrelación Coordinar entre TUTOR y CURSO; dejaremos esta interrelación entre interrogaciones con el fin de estudiar posteriormente si debe reflejarse de esta forma.

D.N.I.

Nombre Dirección e-mail

Nombre F_Comienzo

Teléfono

Libro

WWW

F_Finalización

M:N ALUMNO

3º paso: Dibujar las interrelaciones (estudiando el tipo de correspondencia y las cardinalidades) y los tipos de entidad con los atributos correspondientes.

(1,n)

Matricular

(1,n)

CURSO

Nacionalidad Figura 2.28: Interrelación Matricular con cardinalidades y atributos en la interrelación

Interrelación Matricular Tanto los atributos de CURSO como de ALUMNO se presentaron a lo largo del capítulo, pero recordamos que el IP (identificador principal) de CURSO es

Interrelación Asociar

Nombre y el de ALUMNO es DNI. Tanto el atributo WWW como el e-mail son Antes de analizar las propiedades de la interrelación Asociar veamos los

identificadores alternativos, es decir, los valores que toman para cada elemento

atributos del tipo de entidad TUTOR.

del tipo de entidad CURSO o ALUMNO son únicos; Los atributos Libro y Teléfono son opcionales. La figura 2.27 muestra el esquema E/R correspondiente a la

Según se muestra en el enunciado, la información que se requiere para los

interrelación Matricular.

tutores es: DNI, nombre completo y dirección de correo. De estos tres atributos tenemos que elegir cual de ellos puede ser el IP. Elegiremos el DNI aunque bien podría ser la dirección de correo si esta es única. Por lo que el e-mail será un atributo alternativo y el nombre completo un atributo obligatorio.

63

64

Fase de Análisis de Requisitos : Modelo E/R

El tipo entidad

y sus atributos quedan representados como muestra la

datos siempre estarán ocupados con algún curso. Nos quedamos con la primera

figura 2.29.

alternativa para poder dejar descanso a los tutores. De esta forma la cardinalidad mínima es 0. DNI

Nombre completo

e-mail

- ¿A cuántos cursos está asociado como máximo un TUTOR? En este caso, TUTOR

como en las especificaciones no se restringe el número de cursos que un tutor puede impartir, la cardinalidad máxima será N.

Figura 2.29: Entidad TUTOR

Gráficamente, las cardinalidades de la entidad TUTOR se representan al lado contrario de esta, es decir, junto al tipo de entidad CURSO (Figura 2.31). La interrelación Asociar relaciona las entidades TUTOR y CURSO (véase la tabla del paso 2 y Figura 2.30); tenemos que estudiar el tipo de correspondencia y las cardinalidades máximas y mínimas para completar las propiedades de la

DNI

Nombre completo e-mail

Nombre

interrelación.

Libro

WWW

N:M (0,n)

Si leemos detenidamente las especificaciones del texto tenemos que un tutor

CURSO

Asociar

TUTOR

puede realizar sus labores en varios cursos y que en un curso puede ser tutorado por varias personas expertas (tutores) por lo tanto la correspondencia es N:M o

Figura 2.31: Cardinalidad de TUTOR en la interrelación Asociar

muchos a muchos.

De forma análoga se razonaría para el caso de las cardinalidades asociadas a DNI

Nombre completo e-mail

Nombre

Libro

CURSO (figura 2.32). Un curso como mínimo ha de ser tutorado por un TUTOR

WWW

N:M

Asociar

TUTOR

(cardinalidad mínima 1) y como máximo por N (cardinalidad máxima N). CURSO

DNI

Figura 2.30: Interrelación Asociar

Nombre completo e-mail

Nombre

Libro

WWW

N:M (1,n) TUTOR

(0,n) Asociar

CURSO

Veamos ahora las cardinalidades máximas y mínimas del tipo de entidad TUTOR en la interrelación Asociar; para ello, tenemos que mirar al tipo de

Figura 2.32: Cardinalidades en la interrelación Asociar

entidad TUTOR y preguntarnos: - ¿A cuántos cursos está asociado como mínimo un TUTOR? La respuesta podría ser 0 si consideramos que podemos tener tutores que en un momento

En los pasos 1 y 2 dejamos sin estudiar el concepto de coordinador de los

dado no estén tutorando ningún curso, o, por el contrario, podríamos poner un 1

cursos. Volviendo a releer el texto nos preguntamos ¿qué pasa con la figura del

con lo que supondríamos que todos los tutores que tenemos en nuestra base de

coordinador?, ¿cuál sería su representación? 65

66

Fase de Análisis de Requisitos : Modelo E/R

Como bien se indica en los supuestos semánticos del enunciado, el coordinador es también un tutor y, además, puede desarrollar una función Veámoslo con los ejemplos de ejemplares de la interrelación mostrados en la

añadida de planificación en determinados cursos. Esto significa que un tutor en

figura 2.34; el curso “Diseño de BD” tiene tres tutores cuyos DNI son 3446721,

determinados cursos (pero solamente en aquellos donde participa) puede

7423412, 4567433. El tutor con DNI 4567433 no es coordinador y los tutores

realizar dos labores: tutor y coordinador. Por lo que una solución puede ser

con DNI 3446721 y 7423412 están definidos como coordinadores. Si no

considerar, dentro de la interrelación Asocia un atributo, Coordinador, definido

queremos violar la restricción semántica de que un curso no tenga más de un

dentro del dominio Verdad que toma los valores (SI, NO), y el cual nos indicará

coordinador, entonces en el diseño lógico de la BD se debería definir algún

con el valor SI que un determinado tutor desarrolla la función de coordinador en

mecanismo que cuando se haya definido un coordinador para un curso, entonces

un curso con el que se relaciona (Figura 2.33).

no se permita introducir ninguno más. Sin embargo, la solución de la figura 2.34 si contempla la restricción semántica consistente en que los coordinadores de los cursos deben ser tutores de los mismos, es decir, no es posible definir un DNI

Nombre completo e-mail

Nombre

Libro

WWW

coordinador de un curso que no sea tutor del mismo.

N:M (1,n)

(0,n)

CURSO

Asociar

TUTOR

DNI

Nombre completo

Nombre

e-mail

Libro

WWW

N:M

Coordinador Coordinador está definido dentro del Dominio VERDAD = (SI,NO)

(1, n) TUTOR

(0, n) CURSO

Asociar

Figura 2.33: Interpretación de la figura Coordinador

1:N

Analicemos como se interpreta el atributo Coordinador en la interrelación

(1, 1)

(0, n)

Asocia. El atributo Coordinador toma un valor para cada ejemplar de la

Coordinar

interrelación Asociar; esto significa que como un determinado curso puede tener más de un tutor y el atributo Coordinador podría tomar el valor de SI en esas

Figura 2.35: Interrelación Coordinar.

ejemplares de la interrelación Asocia, entonces estamos permitiendo en el esquema E/R que un curso tenga más de un coordinador. Esto significa que no

Otra posible solución para representar la semántica de la figura de

respetamos uno de los supuestos semánticos del enunciado.

coordinador de un curso consiste en utilizar otra interrelación denominada Coordinar entre TUTOR y CURSO con las cardinalidades mostradas en la figura

3446721

2.35. La interrelación Coordinar representa que un determinado TUTOR puede ser coordinador de más de un CURSO y que un CURSO tiene uno y solo un

7423412 4567433

Asociar

TUTOR

TUTOR

la restricción de que un curso sólo tiene un coordinador, sin embargo, no recoge

SI

Asociar

TUTOR

TUTOR que lo coordina. Si bien esta propuesta de solución recoge a la perfección

CURSO CURSO

Asociar

que el coordinador de un curso tenga que ser obligatoriamente un tutor de ese

CURSO

Diseño de BD

curso (ver figura 2.36). Para controlar esta última restricción habría que incluir

SI NO

Diseño de BD

un mecanismo en el diseño lógico que obligara a que el coordinador de un curso

Diseño de BD

debe ser un tutor del mismo.

Figura 2.34: Ejemplares de la interrelación Asociar

67

68

Fase de Análisis de Requisitos : Modelo E/R

Interrelación Pertenecer

3446721 7423412

Esta interrelación asocia las entidades ALUMNO y AULA. En primer lugar se

4567433

TUTOR TUTOR

TUTOR

CURSO

Asociar

representarán los atributos del tipo de entidad AULA que (código_aula,

CURSO

Asociar

descripción y dirección). El código_aula será el IP, y el resto de atributos serán

CURSO

Asociar

Diseño de BD

obligatorios.

Diseño de BD

2223456

Diseño de BD

Coordinar

TUTOR

Teléfono

1:N

CURSO

ALUMNO

Pertenecer

AULA

Nacionalidad

Diseño de BD

e-mail

Figura 2.36: Ejemplares de la interrelación Asociar y Coordinar

Código_aula

DNI

DescripciónDirección

Nombre

Dirección

Figura 2.38: Interrelación Pertenecer

Aunque existen otras formas de representar la figura del coordinador de un curso, no vamos a presentarlas aquí con el fin de no complicar el ejercicio. Se

Para la interrelación Pertenecer tenemos un tipo de correspondencia de uno

han mostrado las dos más significativas. Para este caso práctico se ha seleccionado

la

primera

propuesta

de

solución

(considerar

el

a muchos porque según el enunciado cada ALUMNO pertenece a un AULA

atributo

(suponemos que un alumno no puede estar asociado a más de un aula);

Coordinador en la interrelación Asocia). De esta forma, el esquema E/R definido

además, un AULA puede tener asociados varios ALUMNOS (figura 2.38). Las

hasta el momento se muestra en la figura 2.37:

cardinalidad mínima y máxima de ALUMNO es (1,1) ya que el alumno está asignado a una y solo un AULA. Las asociadas a AULA serían (1,n) ya que no tendría mucho sentido mantener un aula sin alumnos (figura 2.39).

Nombre DNI completo e-mail

Nombre lLibro WWW N:M

TUTOR

(1, n)

Asociar

(0, n)

F_Comienzo Coordinador

1:N

CURSO AULA

(1, n)

(1,1)

F_Finalización Matricular

nacionalidad = (española, no_española)

(1, n)

Dirección Código_aula Descripcion

N:M

Teléfono

Pertenecer

Teléfono (1,n)

ALUMNO

Nacionalidad

e-mail DNI Nombre Dirección

Figura 2.39: Cardinalidades de la interrelación Pertenecer

coordinador = (SI, NO) ALUMNO

Nacionalidad El esquema E/R obtenido hasta el momento es el mostrado en la figura 2.40.

DNI

e-mail Nombre Dirección

Figura 2.37: Esquema E/R parcial

69

70

Fase de Análisis de Requisitos : Modelo E/R

Nombre DNI completo e-mail

Nombre DNI completo e-mail

Nombre Libro WWW

N:M

N:M TUTOR

(1, n)

Asociar

(0, n)

TUTOR

CURSO

F_Comienzo Coordinador

Nombre Libro WWW

Asociar

(1, n)

Matricular

(0, n)

CURSO

F_Comienzo Coordinador

F_Finalización

nacionalidad = (española, no_española)

(1, n)

N:M

(1, n)

F_Finalización

nacionalidad = (española, no_española)

Matricular

N:M

coordinador = (SI, NO)

administrador = (SI, NO) 1:N

(1, n)

(1,n)

(1,1) Pertenecer

AULA

Código_aula Descrip Dirección

DNI

ALUMNO

1:N

Teléfono Nacionalidad

Pertenecer

AULA

e-mail Nombre Dirección

DNI

Código_aula Descripcion Dirección

Figura 2.40: Esquema E/R parcial

(1, n)

(1,n)

(1,1)

ALUMNO

Teléfono Nacionalidad

e-mail Nombre Dirección

(1,1) 1:N

Mantener

(1,n) ADMINISTRADOR e-mail DNI

Interrelación Mantener La interrelación Mantener se da entre las entidades ADMINISTRADOR y

Nombre

Figura 2.41: Esquema E/R completo

AULA. Los atributos del tipo de entidad ADMISTRADOR son DNI, nombre completo y e-mail y representan el IP, un atributo obligatorio y otro alternativo, Observando el esquema E/R final, las entidades TUTOR y ADMINISTRADOR

respectivamente.

tienen atributos comunes. Podremos identificar generalizaciones si encontramos El tipo de correspondencia es de uno a muchos (1:N) ya que un

una serie de atributos comunes a un conjunto de entidades; estos atributos

ADMINISTRADOR solamente puede estar asignado a un AULA y sin embargo un

comunes describirán al supertipo y los atributos particulares permanecerán en

AULA puede ser mantenida por más de un ADMINISTRADOR lo que nos indica

los subtipos. Puede ocurrir que los subtipos no tengan atributos propios, como es

también las cardinalidades: (1,n) para el tipo de entidad AULA y (1,1) para el

el caso que nos ocupa; en ese caso, sólo existirán subtipos si éstos van a

tipo de entidad ADMINISTRADOR.

participar en interrelaciones (aparte de las interrelaciones en las que participe el supertipo). Así, podemos tener, como muestra la figura 2.42, el supertipo

Así, el diseño conceptual de la base de datos referente al proyecto MENTOR

PERSONA con los atributos DNI, nombre y e-mail y los subtipos TUTOR y

se representa en la figura 2.413:

ADMINISTRADOR que no tienen ningún atributo propio. Los subtipos TUTOR y ADMINISTRADOR siguen participando en las mismas interrelaciones que en la figura 2.41. Sin embargo, el supertipo PERSONA no participaría en ninguna interrelación; por ello, se ha optado por eliminar la generalización de la solución

3

Como se ha mencionado al principio del ejercicio, esta representación conceptual no es única; puede haber diversas interpretaciones. Lo importante es que el usuario o la persona que nos ha encargado el diseño este conforme con este y refleje lo más fielmente posible las características del problema.

propuesta. 71

72

Fase de Análisis de Requisitos : Modelo E/R

Nombre DNI completo e-mail

En este segundo caso práctico se ha pensado en el diseño de una Base de Datos para los estudiantes universitarios de Historia. Debemos crear una base

PERSONA

de datos que permita consultar la información más relevante de la Edad Media, y más concretamente de las cruzadas que se llevaron a cabo en dicha época. Veremos a continuación los requisitos que se plantean en este nuevo proyecto:

TUTOR

Interesa conocer la información de los caballeros más importantes de ADMINISTRADOR

los que sea almacenará la información de su nombre, fecha de nacimiento y apodo.

Figura 2.42: Generalización de PERSONA

También será importante conocer las provincias de aquella época, de las que guardaremos la información de su denominación, número de habitantes y los caballeros que las gobernaron, teniendo en cuenta que un caballero pudo gobernar más de una provincia y que una provincia estuvo gobernada por diferentes caballeros en distintas fechas. Interesará conocer el año de inicio y el número de años que estuve gobernada por cada caballero. De

las cruzadas almacenaremos la información del nombre, la fecha

de inicio y la fecha de fin, los caballeros que participaron, la fecha en la que se incorporaron, la fecha en la que se retiraron y el resultado que obtuvieron (derrota, victoria o abandono). En esta época también fueron muy importantes los reyes, de los que nos interesa conocer el nombre y sus apellidos, fecha de nacimiento, corona y las provincias sobre las que reinaron. Por último, interesa conocer para cada rey la información de su ascendente. En aquella época ya se sabe que su ascendiente sería otro rey.

73

74

Fase de Análisis de Requisitos : Modelo E/R

Por último, interesa conocer para cada rey la información de su

5. DISEÑO PROPUESTO AL CASO PRÁCTICO - HISTORIA

ascendente. En aquella época ya se sabe que su ascendiente sería otro rey. Para realizar el diseño conceptual de la BD en el modelo E/R seguiremos los

Las entidades que hemos encontrado son: CABALLERO, PROVINCIA,

mismos pasos que seguimos en el ejemplo anterior con el fin de identificar los

CRUZADA y REY.

elementos básicos del modelo. Recordemos que las soluciones no son únicas, y cada pueden existir distintos esquema E/R válidos. 2º paso: Identificar y enumerar las posibles interrelaciones, teniendo en cuenta la siguiente heurística: en general, una interrelación viene reflejada por 1º paso: Identificar y enumerar las posibles entidades teniendo en

un verbo dentro de una oración que relaciona dos objetos.

cuenta la siguiente heurística: en general, un tipo de entidad es un sustantivo dentro de una oración con una seria de propiedades o características. Por ejemplo, DNI del alumno, nombre del curso, etc.

Igual que en el ejemplo visto anteriormente, en los verbos con posiblidad de interrelación aparece un número correlativo que se muestra como superíndice.

En el texto presentamos en negrita y subrayado los tipos de entidad que

Interesa conocer la información de los caballeros más importantes de

hemos detectado.

los que sea almacenará la información de su nombre, fecha de nacimiento y apodo.

Interesa conocer la información de los caballeros más importantes de los que sea almacenará la información de su nombre, fecha de

También será importante conocer las provincias de aquella época, de

nacimiento y apodo.

las que guardaremos la información de su denominación, número de habitantes y los caballeros que las gobernaron, teniendo en cuenta

También será importante conocer las provincias de aquella época, de

que un caballero pudo gobernar1 más de una provincia y que una

las que guardaremos la información de su denominación, número de

provincia estuvo gobernada por diferentes caballeros en distintas

habitantes y los caballeros que las gobernaron, teniendo en cuenta

fechas. Interesará conocer el año de inicio y el número de años que

que un caballero pudo gobernar más de una provincia y que una

estuvo gobernada por cada caballero.

provincia estuvo gobernada por diferentes caballeros en distintas fechas. Interesará conocer el año de inicio y el número de años que

De las cruzadas almacenaremos la información del nombre, la fecha de

estuvo gobernada por cada caballero.

inicio y la fecha de fin, los caballeros que participaron2, la fecha en la que se incorporaron, la fecha en la que se retiraron y el resultado que

De las cruzadas almacenaremos la información del nombre, la fecha

obtuvieron (derrota, victoria o abandono).

de inicio y la fecha de fin, los caballeros que participaron, la fecha en la que se incorporaron, la fecha en la que se retiraron y el resultado

En esta época también fueron muy importantes los reyes, de los que

que obtuvieron (derrota, victoria o abandono).

nos interesa conocer el nombre y sus apellidos, fecha de nacimiento, corona, y la provincia sobre la que reinó más tiempo, teniendo en

En esta época también fueron muy importantes los reyes, de los que

cuenta que una provincia pudo tener diferentes reyes.

nos interesa conocer el nombre y sus apellidos fecha de nacimiento, corona, y las provincias sobre las que reinaron.

Por último, interesa conocer para cada rey la información de su ascendente. En aquella época ya se sabe que su ascendiente sería otro rey. 75

76

Fase de Análisis de Requisitos : Modelo E/R

A

continuación

realizaremos

la

matriz

de

interrelaciones,

donde

Fecha_Nac

Apodo Nombre .

recordaremos que en la primera fila y en la primera columna especificaremos las

Denominación Num_Hab

entidades localizadas en el texto, mientras que en el cruce de filas y columnas CABALLERO

indicaremos las interrelaciones encontradas.

Interrelaciones

CABALLERO

PROVINCIA

REY

Historia 1:: Interrelación Gobernar

Participar 2

CABALLERO PROVINCIA

CRUZADA

PROVINCIA

Gobernar

Gobernar 1

CRUZADA REY

Reinar 3

Ascender 4

Para el estudio de las cardinalidades, nos fijaremos en los requisitos indicados en el enunciado, donde se afirma que “Un caballero puede gobernar más de una provincia y que una provincia estuvo gobernada por deferentes caballeros en distintas fechas”.

La numeración como en el ejemplo anterior, muestra el orden de aparición

Además nos indican que debemos conocer el

año de inicio y el número de años que estuvo gobernada por cada caballero.

en el texto. Se muestran sombreadas las celdas que representan interrelaciones Para hallar las cardinalidades, preguntaremos:

simétricas.

- ¿Un caballero, cuántas provincias pudo gobernar? Podemos pensar que habrá caballeros que no gobernaron ninguna provincia (0), y que habrá otros

3º paso: Dibujar las interrelaciones (estudiando el tipo de correspondencia y

que habrán gobernado más de una, por lo que las cardinalidades mínimas y

las cardinalidades) y los tipos de entidad con los atributos correspondientes.

máximas son (0,n). Recordemos que la cardinalidad se expresa en sentido contrario. - ¿Una provincia, por cuántos caballeros estuvo gobernada? Siempre estuvo

Interrelación Gobernar

gobernada como mínimo por un caballero, pero a lo largo de la historia pudo estar gobernada por diferentes caballeros (1,n).

En esta interrelación participan las entidades CABALLERO y PROVINCIA. Los atributos de la entidad CABALLERO son: Nombre, Fecha de nacimiento y

Por último vemos que nos hablan de dos atributos: “Año de inicio y número

Apodo. Elegiremos como IP (identificador principal) el atributo Apodo, ya que

de años” que un caballero estuvo gobernando una provincia. Como vemos, son

identifica de forma única a cada caballero. En cuanto a la entidad PROVINCIA,

dos atributos que dependen de la interrelación Gobernar.

nos interesa conocer, según los requisitos del modelo, la Denominación y el Apodo Nombre .

Número de Habitantes siendo el IP (identificador principal) la denominación

Año_inicio

Denominación Num_Hab Num_Años

(única para cada provincia).

M:N CABALLERO

La figura ”Historia 1: Interrelación Gobernar” muestra la representación de

(1,n)

(0,n) Gobernar

PROVINCIA

las entidades Caballero y Provincia con sus atributos , y la representación de la Historia 2: Interrelación Gobernar con cardinalidades y atributos de interrelación

interrelación Gobernar en el diagrama entidad-relación.

77

78

Fase de Análisis de Requisitos : Modelo E/R

Recordemos que la cardinalidad se representa gráficamente en sentido

Interrelación Participar

contrario.

Esta interrelación representa las cruzadas en las que participaron los caballeros. Analizaremos los atributos de la nueva relación CRUZADA, que según

- En una cruzada, ¿cuántos caballeros pudieron participar? Como mínimo un

el enunciado deben ser: Nombre, la fecha en la que comenzó la cruzada y la

caballero y como máximo N.

fecha en la finalizó. Como IP (identificador principal) elegiremos el atributo Nombre. La entidad y sus atributos quedan representados en la siguiente figura: Nombre Apodo completo Fecha_Nac

Fecha_Inicio

Nombre

Fecha_Fin

N:M Nombre Fecha_Inicio

Fecha_Fin

CABALLERO

(1,n)

(1,n)

CRUZADA

Participar

CRUZADA Historia 5: Cardinalidad de CABALLERO y CRUZADA en la interrelación Participar

Historia 3: Entidad CRUZADA

Vemos que la cardinalidad de la interrelación es del tipo N:M.

El siguiente paso será establecer las cardinalidades entre las dos entidades de esta interrelación PARTICIPAR, donde intervienen las entidades CRUZADA y

Ahora bien, también interesa conocer para cada caballero que participó en

CABALLERO.

cada cruzada, la información de la fecha de incorporación, la fecha de retirada y el resultado (derrota, abandono, victoria). Estos atributos no son propios ni de la

- Un caballero, ¿en cuántas cruzadas pudo participar? Como mínimo en una

entidad CABALLERO ni de la entidad CRUZADA, sino que la información depende

y como máximo en muchas (1,n).

de cada uno de los caballeros que participó en una cruzada, por lo tanto, son atributos de la interrelación PARTICIPAR:

Nombre Apodo completo Fecha_Nac

Nombre

Fecha_Inicio

Fecha_Fin Nombre Apodo completo Fecha_Nac

(1,n) CABALLERO

Participar

CRUZADA CABALLERO

(1,n)

Nombre F_Retirada F_Incorpor Resultado N:M (1,n) Participar

Historia 4: Cardinalidad de CABALLERO en la interrelación Participar

Historia 6: Esquema entidad/relación parcial

79

80

Fecha_Inicio

CRUZADA

Fecha_Fin

Fase de Análisis de Requisitos : Modelo E/R

Esta interrelación asocia las relaciones PROVINCIA y REY. Sus atributos son:

(1,n)

PROVINCIA

Reinar

Nombre, Apellidos, (ID Principal) Fecha de nacimiento y Corona.

Fecha_Nac

Nombre

Denominación Num_Habitantes

Interrelación Reinar

Corona

REY

Historia 9 : Cardinalidad de la entidad Provincia

Nombre Completo Fecha_Nac Corona

REY

Un rey, ¿cuántas provincias reinó? Puedo reinar sobre uno o más provincias.

Historia 7: Entidad REY

Veamos la representación de la interrelación Reinar

Fecha_Nac

Nombre

Denominación Num_Habitantes

Corona

N:1 PROVINCIA Nombre

Denominación Num_Habitantes

Fecha_Nac

(1,n)

Reinar

(1,1)

REY

Corona Historia 10 : Cardinalidad de la interrelación

PROVINCIA

Reinar

REY

Historia 8 : Representación de la interrelación Reinar

Por lo tanto la cardinalidad entre estas relaciones es 1:N - En una provincia, cuántos reyes reinaron? Uno o más de uno (en distintas fechas)

Interrelación Ascender En esta interrelación participa únicamente la relación REY relacionada consigo misma.

81

82

Fase de Análisis de Requisitos : Modelo E/R

Hasta ahora hemos representado todas las interrelaciones de forma parcial, por lo que el siguiente paso es la representación global de las interrelaciones a

Nombre Fecha_Nac Completo

través del diagrama entidad-relación:

Corona

Apodo Nombre .

REY

Fecha_Nac

Año_inicio

Denominación Num_Años

Num_Hab

M:N CABALLERO

(1,n)

(0,n)

PROVINCIA

Gobernar

(1,n)

(1,n)

Ascender

Historia 11: Interrelación Asociar

M:N

Nombre

Para hallar la cardinalidad, preguntaremos:

F_Retirada F_Incorpor Resultado Participar

Fecha_Inicio

M:N

Reinar

Fecha_Fin

(1,n)

- ¿Un rey cuántos ascendientes tiene? Tendrá dos. (Se incluye la información

Nombre Fecha_Nac Completo

CRUZADA

de los Reyes y de las Reinas)

Corona (1,n)

- Un rey, ¿de cuántos Reyes es ascendiente? De uno o más de uno (en el

REY

caso de que haya tenido más de un hijo). Por lo tanto, la representación sería: (1,n)

Nombre Fecha_Nac Completo

Ascender

Corona

REY Historia 13 : Diagrama Entidad- Relación completo

(1,n)

(1,n)

Ascender

(1,n)

N:M

Historia 12: Interrelación Asociar

83

84

N:M

Fase de Análisis de Requisitos : Modelo E/R

6. DISEÑO PROPUESTO AL CASO PRÁCTICO - CONSTRUCTORA

Seguiremos los mismos pasos que hemos visto en ejemplos anteriores. Recordemos que las soluciones no son únicas, y que pueden existir distintos En este tercer caso práctico se requiere el diseño de una Base de Datos

esquemas E/R válidos.

para una Constructora que dispone de varias sucursales dedicadas al alquiler de sus inmuebles. Se desea almacenar la información de su negocio. Se exponen a 1º paso: Identificar y enumerar las posibles entidades teniendo en

continuación los requisitos para la creación de la base de datos:

cuenta la siguiente heurística: en general, un tipo de entidad es un sustantivo dentro de una oración con una seria de propiedades o

Cada sucursal se identifica por un código y se requiere la información

características. Por ejemplo, DNI del alumno, nombre del curso, etc.

de su dirección, código postal, población y teléfono. Se desea conocer los comerciales que trabajan en las distintas

En el texto presentamos en negrita y subrayado los tipos de entidad que

sucursales. De ellos se quiere conocer su Dni, nombre, apellidos y la

hemos detectado.

fecha de contratación.

Cada sucursal se identifica por un código y se requiere la información

De los inmuebles que gestiona cada sucursal interesa conocer el

de su dirección, código postal, población y teléfono.

Código del inmueble, su dirección, código_postal, población, nº de habitaciones, nº de baños, y el importe de su alquiler mensual. Se

Se desea conocer los comerciales que trabajan en las distintas

tendrá en cuenta que un inmueble será gestionado por una única

sucursales. De ellos se quiere conocer su Dni, nombre, apellidos y la

sucursal.

fecha de contratación.

Los clientes reales son aquellos que tienen alquilado algún inmueble.

De los inmuebles que gestiona cada sucursal interesa conocer el

Nos interesa conocer la información de su DNI, nombre, apellidos, y

Código del inmueble, su dirección, código_postal, población, nº de

teléfono. Tendremos en cuenta que un inmueble puede ser alquilado

habitaciones, nº de baños, y el importe de su alquiler mensual. Se

por diferentes clientes a lo largo del tiempo y nos interesará conocer

tendrá en cuenta que un inmueble será gestionado por una única

la fecha de inicio, la fecha de fin y el importe máximo mensual que

sucursal.

un cliente pagó por ese alquiler.

Los clientes reales son aquellos que tienen alquilado algún inmueble.

Sus clientes potenciales son aquellos que buscan el alquilar de un

Nos interesa conocer la información de su DNI, nombre, apellidos, y

inmueble. Nos interesa conocer la información de su DNI, nombre,

teléfono. Tendremos en cuenta que un inmueble puede ser alquilado

apellidos, teléfono, y precio máximo que está dispuesto a pagar por

por diferentes clientes a lo largo del tiempo y nos interesará conocer

el alquiler. Un cliente potencial será atendido por un único comercial.

la fecha de inicio, la fecha de fin y el importe máximo mensual que un cliente pagó por ese alquiler.

Cada sucursal depende a su vez, de una sucursal principal a la que enviará mensualmente todos sus informes de alquileres. La sucursal

Sus clientes potenciales son aquellos que buscan el alquilar de un

principal tendrá a su cargo varias sucursales.

inmueble. Nos interesa conocer la información de su DNI, nombre, 85

86

Fase de Análisis de Requisitos : Modelo E/R

apellidos, teléfono, y precio máximo que está dispuesto a pagar por

la fecha de inicio, la fecha de fin y el importe máximo mensual que

el alquiler. Un cliente potencial será atendido por un único comercial.

un cliente pagó por ese alquiler.

Cada sucursal depende a su vez, de una sucursal principal a la que

Sus clientes potenciales son aquellos que buscan el alquilar de un

enviará mensualmente todos sus informes de alquileres. La sucursal

inmueble. Nos interesa conocer la información de su DNI, nombre,

principal tendrá a su cargo varias sucursales.

apellidos, teléfono, y precio máximo que está dispuesto a pagar por el alquiler. Un cliente potencial será atendido4 por un único comercial.

Las

entidades

que

hemos

encontrado

son:

SUCURSAL,

Cada sucursal depende5 a su vez, de una sucursal principal a la que

COMERCIAL,

enviará mensualmente todos sus informes de alquileres. La sucursal

INMUEBLE, CLIENTE REAL y CLIENTE POTENCIAL.

principal tendrá a su cargo varias sucursales.

2º paso: Identificar y enumerar las posibles interrelaciones, teniendo en A

cuenta la siguiente heurística: en general, una interrelación viene

continuación

realizaremos

la

matriz

de

interrelaciones,

donde

recordaremos que en la primera fila y en la primera columna especificaremos las

reflejada por un verbo dentro de una oración que relaciona dos objetos.

entidades localizadas en el texto, mientras que en el cruce de filas y columnas indicaremos las interrelaciones encontradas. Igual que en el ejemplo visto anteriormente, en los verbos con posiblidad de interrelación aparece un número correlativo que se muestra como superíndice.

Interrelaciones

SUCURSAL

COMERCIAL

INMUEBLE

Cada sucursal se identifica por un código y se requiere la información de su dirección, código postal, población y teléfono. Se desea conocer los comerciales que trabajan1 en las distintas sucursales. De ellos se quiere conocer su Dni, nombre, apellidos y la fecha de contratación.

SUCURSAL

Depender 5

COMERCIAL

Trabajar 1

INMUEBLE

Gestionar 2

CLIENTE

Código del inmueble, su dirección, código_postal, población, nº de

CLIENTE

REAL

POTENCIAL

Alquilar 3

CLIENTE REAL De los inmuebles que gestiona2 cada sucursal interesa conocer el

CLIENTE

Atender 4

POTENCIAL

habitaciones, nº de baños, y el importe de su alquiler mensual. Se tendrá en cuenta que un inmueble será gestionado por una única sucursal. Recordemos que la numeración, se corresponde con el orden de aparición en

Los clientes reales son aquellos que tienen alquilado3 algún inmueble.

el texto. Se muestran sombreadas las celdas que representan interrelaciones

Nos interesa conocer la información de su DNI, nombre, apellidos, y

semánticas.

teléfono. Tendremos en cuenta que un inmueble puede ser alquilado por diferentes clientes a lo largo del tiempo y nos interesará conocer 87

88

Fase de Análisis de Requisitos : Modelo E/R

3º

paso:

Dibujar

las

interrelaciones

(estudiando

el

tipo

de

correspondencia y las cardinalidades) y los tipos de entidad con los atributos correspondientes.

Cod_Suc. Cod_Postal Direccion Población Teléfono

Nombre Apellidos Fecha_Contrato DNI

SUCURSAL

Interrelación Trabajar

(1,n)

COMERCIAL

Trabajar

En esta interrelación participan las entidades SUCURSAL y COMERCIAL. Constructora 2::

Interrelación Trabajar

Los atributos de la entidad SUCURSAL son: Cod_Suc, Dirección, Cod_Postal, Población y Teléfono. Elegiremos como IP (identificador principal) el atributo Cod_Suc (Código de Sucursal), ya que identifica de forma única a las distintas sucursales con las que cuenta la Constructora. En cuanto a la entidad COMERCIAL, nos interesa conocer para cada uno de los comerciales, su DNI que

- Un comercial, ¿ en cuántas sucursales trabaja? Lo lógico sería pensar que

será el IP (identificador principal), su nombre, apellidos y la fecha de

un comercial trabaja únicamente en una sucursal, por lo que la cardinalidad sería

contratación.

1:1 (Como mínimo trabajaría en una sucursal y como máximo también en una única sucursal).

La figura”Constructora 1: Interrelación Trabajar” muestra el diagrama parcial entidad-relación.

Cod_Suc. Cod_Postal Direccion Población Teléfono Cod_Suc. Cod_Postal Direccion Población Teléfono

Nombre Apellidos Fecha_Contrato DNI

Nombre Apellidos Fecha_Contrato DNI

1:N SUCURSAL

(1,1)

(1,n)

Trabajar SUCURSAL

Trabajar

Constructora 3:: Constructora 1::

COMERCIAL

COMERCIAL Interrelación Trabajar

Interrelación Trabajar

Interrelación Gestionar Para el estudio de las cardinalidades, nos preguntaremos lo siguiente:

Esta interrelación representa los inmuebles que gestiona cada sucursal. Analizaremos los atributos de la nueva relación INMUEBLES. En el enuanciado nos indican que las propiedades más relevantes para esta entidad son: Código

- En una sucursal, ¿cuántos comerciales trabajan? Puedes trabajar como

del inmueble, su dirección, código_postal, población, nº de habitaciones, nº de

mínimo 1 y como máximo N. Por lo tanto la cardinalidad será 1:N. Recordemos

baños, y el

que la cardinalidad se expresa en el sentido contrario.

elegiremos el atributo Código del inmueble ya que identificará de forma única a

importe de su alquiler mensual. Como IP (identificador principal)

cada uno de los inmuebles de la constructora. 89

90

Fase de Análisis de Requisitos : Modelo E/R

El tipo de entidad y sus atributos quedan representados en la siguiente figura:

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler

Cod_Suc. Cod_Postal Direccion Población Teléfono 1:N Dirección. Población Num_Baños Num_Habitaciones.Importe_Alquiler Cod_Inmueble. Cod_Postal

(1,n)

(1,1) SUCURSAL

INMUEBLE

INMUEBLE

Gestionar

Constructora 6: Cardinalidad de SUCURSAL e INMUEBLE

Constructora 4: Entidad Inmueble

Vemos que la cardinalidad de la interrelación es del tipo 1:N.

Interrelación Alquilar

El siguiente paso será establecer las cardinalidades de la interrelación GESTIONAR, donde intervienen las entidades SUCURSAL e INMUEBLE.

Esta

interrelación

ALQUILAR

asocia

las

entidades

CLIENTE

(real)

e

INMUEBLE. Los atributos de la entidad CLIENTE (real), según el enunciado son:

- Una sucursal, ¿cuántos inmuebles puede gestionar? Como mínimo podrá

DNI, nombre, apellidos, y teléfono. Elegiremos como IP (Identificador Principal)

gestionar 1 y como máximo N (Muchos).

el atributo DNI y representaremos la entidad de la siguiente forma:

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler

Cod_Suc. Cod_Postal Direccion Población Teléfono

(1,n) SUCURSAL

Gestionar

DNI.

Nombre

Apellidos

Teléfono

INMUEBLES

CLIENTE (real)

Constructora 5: Cardinalidad de SUCURSAL en la interrelación Gestionar

Constructora 7: Entidad Cliente (Real)

- Un inmueble, ¿por cuántas sucursales es gestionado? Como mínimo por una sucursal y como máximo también por 1, ya que en el enunciado nos indican que un inmueble será gestionado por una única sucursal.

A

continuación

estableceremos

las

cardinalidades

ALQUILAR, donde intervienen las entidades CLIENTE (real) ello, preguntaremos:

91

92

de

la

interrelación

e INMUEBLE. Para

Fase de Análisis de Requisitos : Modelo E/R

- Un cliente ¿Cuántos inmuebles puede alquilar? Puede ser que alquile como mínimo 1, y como máximo muchos (N), ya que puede alquilar diferentes inmuebles a lo largo del tiempo.

DNI.

Nombre

Apellidos

Teléfono

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Fecha_InicFecha_Fin Importe_Alquiler Importe_Max N:M

(1,n) CLIENTE (real)

DNI.

Nombre

Apellidos

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler

Teléfono

(1,n) CLIENTE (real)

Alquilar

(1,n)

Alquilar

INMUEBLE

Constructora 10: Cardinalidad y atributos de la interrelación ALQUILAR

INMUEBLE

Interrelación Atender Constructora 8: Cardinalidad de CLIENTE en la interrelación ALQUILAR

En esta interrelación participan las entidades CLIENTE (potencial)

y

COMERCIAL. Los atributos de la entidad CLIENTE (potencial) son: DNI, nombre, apellidos, - Un inmueble, ¿por cuántos clientes es alquilado? Como mínimo estará

teléfono, y precio máximo que está dispuesto a pagar por el alquiler. Elegiremos

alquilado por un cliente y como máximo por muchos (N), ya que un inmueble

como IP (identificador principal) el atributo DNI, ya que identifica de forma

pudo estar alquilado por más de un cliente en distintas fechas.

única a los clientes (pontenciales). Antes de continuar, podemos observar que los atributos de los clientes reales y clientes potenciales son prácticamente los mismos, por lo que podemos

DNI.

Nombre

Apellidos

Teléfono N:M (1,n)

CLIENTE (real)

hablar de una generalización, donde crearemos un supertipo denominado

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler (1,n)

Alquilar

CLIENTE

y

dos

subtipos:

CLIENTES

(real)

y

CLIENTE

representación de esta generalización se expone a continuación:

INMUEBLE

Nombre DNI

Apellidos

Constructora 9: Cardinalidad de la interrelación ALQUILAR

Teléfono CLIENTE

Ahora bien, también interesa conocer la fecha de inicio, la fecha de fin y el

Precio_Max

importe máximo mensual que un cliente pagó por ese alquiler. Esos atributos no son propios ni de la entidad Cliente (real), ni de la entidad Inmueble, sino que son propios de la relación. CLIENTE (REAL)

CLIENTE POTENCIAL

Constructora 11: Generalización de Cliente 93

94

(potencial).

La

Fase de Análisis de Requisitos : Modelo E/R

Como la entidad cliente y cliente real, tienen los mismos atributos puesto DNI

que la entidad cliente real no tiene ninguno propio, se decide mantener únicamente las entidades cliente (real) y cliente potencial. La entidad cliente

Apellidos Nombre Teléfono

Nombre Apellidos Fecha_Contrato N:1

DNI

real, pasa a denominarse Cliente, por lo que a partir de ahora ya no hablaremos CLIENTE POTENCIAL

de cliente real y cliente potencial, sino únicamente de cliente y cliente potencial:

(1,n)

(1,1)

Atender

Constructora 13::

CLIENTE

COMERCIAL

Cardinalidad de la interrelación Atender

CLIENTE POTENCIAL Por lo tanto, hemos visto que la cardinalidad entre las entidades CLIENTE POTENCIAL Y COMERCIAL es N:1.

Continuaremos analizando la cardinalidad entre las entidades CLIENTE POTENCIAL y COMERCIAL.

Interrelación Depender

Comenzaremos por establecer la cardinalidad de la entidad CLIENTE POTENCIAL:

Para

finalizar,

analizaremos

la

interrelación

Depender.

La

entidad

participante es SUCURSAL ya que se relaciona consigo misma:

- Un cliente potencial ¿por cuántos comerciales es atendido? Según se nos indica en el enunciado “Un cliente potencial será atendido por un único comercial”

Dirección Población Cod_Postal Teléfono Cod_Sucural DNI

Apellidos Precio_Max Nombre Teléfono

Nombre Apellidos Fecha_Contrato DNI

CLIENTE POTENCIAL

(1,1)

Atender

Constructora 12::

SUCURSAL

COMERCIAL

Cardinalidad de Cliente

Depender

Constructora 14: Interrelación Depender

- Un comercial ¿a cuántos clientes potenciales atiende? Como mínimo atenderá a 1 y como máximo a muchos (1:N) A continuación establecemos las cardinalidades: - Una sucursal, ¿de cuántas sucursales depende? Según se deduce del enunciado, una sucursal dependerá únicamente de otra sucursal:

95

96

Fase de Análisis de Requisitos : Modelo E/R

Diagrama Entidad-Relación Dirección Población Cod_Postal Teléfono Cod_Sucural

SUCURSAL

N:1 1:1

Depender (1,n)

(1,1

Depender

Constructora 15: Interrelación Depender

Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler

Cod_Suc. Cod_Postal Direccion Población Teléfono 1:N (1,1)

De Una sucursal, ¿cuántas sucursales dependan? Pueden ser una o más de

SUCURSAL

(1,n)

INMUEBLE

Gestionar

una. (1,1)

(1,n) Importe_Max Fecha_Fin Fecha_Ini

Dirección Población Cod_Postal Teléfono Cod_Sucural

Trabajar

Alquilar

1:N

N:M

SUCURSAL (1,n)

1:N

1:1

DNI.

Nombre

Apellido

Teléfono

(1,n)

COMERCIAL

CLIENTE DNI

Depender

Fecha_Contrato Nombre Apellidos (1,1)

N:1 Constructora 16: Interrelación Depender

DNI

1:N Atender

Por lo tanto, estamos ante una cardinalidad N:1.

Precio_Max Apellidos Nombre Teléfono

(1,n) CLIENTE POTENCIAL

Constructora 17: Diagrama Entidad Relación Final

97

98

Fase de Análisis de Requisitos : Modelo E/R

7. DISEÑO PROPUESTO AL CASO PRÁCTICO – OBRAS DE ARTE Para la realización del diseño conceptual y con el fin de obtener el modelo entidad-relación,

identificaremos

los

principales

elementos

del

modelo.

Seguiremos los mismos pasos vistos en los casos prácticos anteriores: Se necesita almacenar la información en una base de datos de los artistas más importantes del momento. Se almacenará la información de sus obras y de las sedes donde

1º paso: Identificar y enumerar las posibles entidades teniendo en

han permanecido expuestas. Se presenta a continuación la

cuenta la siguiente heurística: en general, un tipo de entidad es un

especificación de los requisitos de forma detallada:

sustantivo dentro de una oración con una seria de propiedades o características tales como, DNI del alumno, nombre del curso, etc.

De los artistas se requiere: DNI, nombre, apellidos, sexo, apodo En el texto presentamos en negrita y subrayado los tipos de entidad que

(quienes lo tengan), fecha de nacimiento y su dirección de correo

hemos detectado.

electrónico. surrealista,

De los artistas se requiere: DNI, nombre, apellidos, sexo, apodo

impresionista,...) y diferentes técnicas (óleo, escultura, acuarela, ….)

(quienes lo tengan), fecha de nacimiento y su dirección de correo

Cada estilo y cada técnica vendrán identificadas por un código y se

electrónico.

Un

artista

puede

trabajar

distintos

estilos

(cubista,

almacenará la información de su descripción.

Un

artista

puede

trabajar

distintos

estilos

(cubista,

surrealista,

De las obras es necesario recoger el nombre de la obra, dimensiones

impresionista,...) y diferentes técnicas (óleo, escultura, acuarela, ….)

(alto, largo, ancho), valor de la última subasta (si es que ha sido

Cada estilo y cada técnica vendrán identificadas por un código y se

subastada), valor estimado por el tasador, así como el artista que la

almacenará la información de su descripción.

creó. También se recogerá información del estilo al que pertenece y

De las obras es necesario recoger el nombre de la obra, dimensiones

de la técnica empleada.

(alto, largo, ancho), valor de la última subasta (si es que ha sido

Además si la obra ha estado expuesta en alguna sede, se necesita

subastada), valor estimado por el tasador, así como el artista que la

saber el nombre y la dirección de la sede, así como la exposición en

creó. También se recogerá información del estilo al que pertenece y

la que tuvo cabida. Una obra puede haber sido expuesta en varias

de la técnica empleada.

sedes y en una misma sede varias veces, por lo que es importante

Además si la obra ha estado expuesta en alguna sede, se necesita

almacenar la fecha en la que se realizó la exposición.

saber el nombre y la dirección de la sede, así como la exposición en la que tuvo cabida. Una obra puede haber sido expuesta en varias sedes y en una misma sede varias veces, por lo que es importante almacenar la fecha en la que se realizo la exposición.

99

100

Fase de Análisis de Requisitos : Modelo E/R

Los tipos de entidades que hemos localizado son: ARTISTA, ESTILO,

A continuación realizaremos la matriz que nos permitirá conocer las

TÉCNICA, OBRA y SEDE. Del enunciado se podría deducir que SUBASTA es

relaciones existentes entre las entidades. En la primera fila y la primera columna

también un tipo de entidad, pero parece ser que solo interesa conocer el valor de

se enuncian los tipos de entidad anteriormente enumerados y se señalará en el

la obra en la última subasta, independientemente de la subasta. Parece que

cruce de filas y columnas aquellas interrelaciones que hemos detectado:

dicha entidad no cobra peso en nuestro modelo.

Interrelaciones

ARTISTA

ESTILO

ARTISTA ESTILO TÉCNICA OBRA SEDE

2º paso: Identificar y enumerar las posibles interrelaciones, teniendo en cuenta la siguiente heurística: en general, una interrelación viene reflejada por un verbo dentro de una oración que relaciona dos objetos.

TECNICA

OBRA

Trabajar 1

Trabajar 2 Crear 3

Pertenecer 4

Emplear 5

SEDE

Exponer 6

En el texto aparece un número correlativo como superíndice en los verbos que indican la posible existencia de una interrelación.

3º

paso:

Dibujar

las

interrelaciones

(estudiando

el

tipo

de

correspondencia y las cardinalidades) y los tipos de entidad con los

De los artistas se requiere: DNI, nombre, apellidos, sexo, apodo

atributos correspondientes.

(quienes lo tengan), fecha de nacimiento y su dirección de correo electrónico. Un artista puede trabajar1/2 distintos estilos (cubista, surrealista,

Interrelación Trabajar 1

impresionista,...) y diferentes técnicas (óleo, escultura, acuarela, ….) Las entidades participantes de esta interrelación son ARTISTA y ESTILO. La

Cada estilo y cada técnica vendrán identificadas por un código y se

entidad ARTISTA presenta los atributos DNI, nombre, apellidos, sexo, apodo

almacenará la información de su descripción.

(quienes lo tengan), fecha de nacimiento y su dirección de correo electrónico. De las obras es necesario recoger el nombre de la obra, dimensiones

Como IP se elige el atributo DNI ya que permite identificar de forma única a

(alto, largo, ancho), valor de la última subasta (si es que ha sido

cada uno de los artistas. La entidad ESTILO contará con los atributos Código de

subastada), valor estimado por el tasador, así como el artista que la

Estilo y Descripción.

creó3. También se recogerá información del estilo al que pertenece4 y de la técnica empleada5. Además si la obra ha estado expuesta6 en alguna sede, se necesita saber el nombre y la dirección de la sede, así como la exposición en

La figura 1: Interrelación Trabajar muestra el esquema parcial E/R:

la que tuvo cabida. Una obra puede haber sido expuesta en varias sedes y en una misma sede varias veces, por lo que es importante almacenar la fecha en la que se realizo la exposición.

101

102

Fase de Análisis de Requisitos : Modelo E/R

DNI

DNI

Cod_Estilo

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Descripcion

Cod_Estilo

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Descripcion N:M

ARTISTA

Artistas 1:

ARTISTA

ESTILO

Trabajar

Trabajar.

Para

ver

Interrelación Trabajar

la

(1,n)

ESTILO

Trabajar

Artistas 3: Cardinalidad de la Interrelación Trabajar

Interrelación Trabajar 2

Analizaremos a continuación las cardinalidades máximas y mínimas de la interrelación

(1,n)

cardinalidad

de

la

entidad

Artista,

Las entidades participantes de esta interrelación son ARTISTA y TÉCNICA.

preguntaremos:

La entidad ARTISTA ya la hemos visto anteriormente. La entidad TÉCNICA

- Un artista, ¿cuántos estilos trabaja? Puede trabajar un estilo

contará con los atributos Código de Técnica y Descripción.

o más de

uno. (1:N) La figura 1: Interrelación Trabajar muestra el esquema parcial E/R:

DNI

Cod_Estilo

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Descripcion DNI

ARTISTA

(1,n)

Trabajar

Cod_Técnica

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Descripcion

ESTILO ARTISTA

Trabajar

Artistas 2: Cardinalidad de Artista en la interrelación Trabajar

Artistas 4:

TÉCNICA

Interrelación Trabajar

Veremos ahora la cardinalidad de la entidad Estilo:

- Un estilo ¿cuántos artistas lo pueden trabajar? Un estilo lo puede trabajar un único artista o más de uno (1:N) De la misma forma que en el ejemplo anterior, para ver la cardinalidad de la entidad Artista, preguntaremos: - Un artista, ¿cuántas técnicas trabaja? Puede trabajar una técnica o más de una. (1:N)

103

104

Fase de Análisis de Requisitos : Modelo E/R

La interrelación Crear presenta un tipo de cardinalidad de uno a muchos, DNI

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

porque una obra será creada por un único artista y un artista creará una o varias

Cod_ Técnica Descripcion

obras. Esta cardinalidad se representa en la figura 8

ARTISTA

(1,n)

TÉCNICA

Trabajar

DNI

Artistas 5: Cardinalidad de Artista en la interrelación Trabajar

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Valor_Subasta Valor_Tasador Nombre Dimensiones

1:N ARTISTA

- Una técnica ¿cuántos artistas la pueden trabajar? Una técnica la puede

(1,1)

(1,n)

OBRA

Crear

trabajar un único artista o más de uno (1:N). Artistas 8: Cardinalidad de la Interrelación Crear

DNI

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Interrelación Pertenecer

Cod_Técnica Descripcion

Esta interrelación relaciona a dos entidades que ya han sido analizadas:

N:M ARTISTA

(1,n)

(1,n)

Trabajar

OBRA y ESTILO. TÉCNICA

Analizaremos primero la cardinalidad de la entidad Obra. Artistas 6: Cardinalidad de la Interrelación Trabajar

- Una obra, ¿ a cuántos estilos pertenece? Pertenecerá a uno y solo a uno.

Interrelación Crear Valor_Subasta Valor_Tasador Nombre Dimensiones

Cod_Estilo Descripcion

La interrelación Crear une las entidades OBRA y ARTISTA. Los atributos de la OBRA

entidad OBRA son: Nombre, dimensiones, valor de la última subasta y valor

(1,1)

Pertenecer

ESTILO

estimado por el tasador. Artistas 9: Cardinalidad de la Entidad Obra Artistas Valor_Subasta Nombre Dimensiones Valor_Tasador

- Un estilo, ¿a cuántas

OBRA

obras. Artistas 7: En tidad

OBRA

105

106

obra pertenece?

Puede pertenecer a una o más

Fase de Análisis de Requisitos : Modelo E/R

Cod_Estilo

Valor_Subasta Valor_Tasador Nombre Dimensiones

Descripcion

Interrelación Exponer

N:1 OBRA

Esta última interrelación relaciona a las entidades OBRA y SEDE. Los

(1,1)

(1,n)

ESTILO

Pertenecer

atributos de la entidad Sede serán Nombre y Dirección. Como identificador principal elegiremos el atributo Nombre ya que identificará de forma única a

Artistas 10:

Cardinalidad de la Interrelación Pertenecer

cada una de las sedes.

Interrelación Emplear Esta interrelación también relaciona dos entidades que ya han sido

Nombre

Valor_Subasta Valor_Tasador Nombre Dimensiones

analizadas: OBRA y TÉCNICA. Veremos la cardinalidad de la interrelación: - Una obra, ¿cuántas técnicas emplea? En una obra se empleará una única

Dirección

OBRA

SEDE

Exponer

técnica predominante.

Artistas 13: Interrelación Exponer

Cod_Técnica

Valor_Subasta Valor_Tasador Nombre Dimensiones

Descripcion

Veremos a continuación las cardinalidades mínimas y máximas de la entidad OBRA

(1,1)

Emplear

OBRA en la interrelación Exponer. TÉCNICA

- Una obra, ¿en cuántas sedes ha estado expuesta? Una obra puede haber sido expuesta en varias sedes.

Artistas 11:Cardinalidad de la Entidad Obra en la interrelación Emplear

Nombre

Valor_Subasta Valor_Tasador Nombre Dimensiones

- Una técnica, ¿en cuántas obras se emplea? Se puede emplear en una o

Dirección

más obras.

(1,n) OBRA

Exponer

Artistas 14: Cardinalidad de la Entidad Obra

Cod_Técnica

Valor_Subasta Valor_Tasador Nombre Dimensiones

SEDE

Descripcion N:1

OBRA

(1,n)

(1,1)

Emplear

- En una sede, ¿ cuántas obras se pueden exponer? Una o más de una. TÉCNICA

Artistas 12: Cardinalidad de la Interrelación Pertenecer

107

108

Fase de Análisis de Requisitos : Modelo E/R

Diagrama Entidad-Relación

Nombre

Valor_Subasta Valor_Tasador Nombre Dimensiones

Dirección N:M

OBRA

(1,n)

(1,n)

SEDE

Exponer

DNI

Apellidos Fecha_Nac Nombre Sexo Apodo E-mail

Cod_Estilo Descripcion N:M

Artistas 15: Cardinalidad de la Interrelación Exponer (1,n)

ARTISTA

(1,n)

Trabajar

ESTILO (1,1)

(1,1)

Además, nos indican que “Una obra puede haber sido expuesta en una

Cod_Técnica

(1,n)

misma sede varias veces, por lo que es importante almacenar la fecha en la obra

Descripcion

N:M

1:N

(1,1)

se expuso en la sede.

Crear

Trabajar

(1,n)

TÉCNICA

Como vemos, nos encontramos con un atributo “Fecha de exposición” que no depende ni de la entidad obra, ni de la entidad Sede sino que depende de la

(1,n) Valor_Subasta Dimensiones Valor_Tasador Nombre

relación Exponer: Fecha en la obra se expuso en la sede

OBRA Valor_Subasta Valor_Tasador Nombre Dimensiones

Nombre Dirección Fecha_Exposición N:M

(1,n)

(1,n)

Exponer

Nombre Dirección Fecha_Exposición N:M

(1,n) (1,n)

OBRA

(1,n)

(1,n)

Exponer

SEDE N:1

Artistas 15: Cardinalidad de la Interrelación Exponer

Emplear

N:1

Pertenecer

109

110

SEDE

Fase de Análisis de Requisitos : Modelo E/R

El modelo E/R, como todos los modelos, consiste en un conjunto de conceptos, reglas y notaciones que permiten formalizar la semántica del mundo real que se pretende modelar (también denominada Universo del Discurso) en una representación gráfica o diagrama que denominamos esquema de la Base de Datos.

Los elementos básicos del modelo Entidad/Relación son: - Entidades: representan conjuntos de elementos con existencia propia y que se caracterizan por las mismas propiedades - Interrelaciones: representan asociaciones del mundo real entre una o más entidades - Atributos: Características o cualidades propias de las entidades que queremos recoger dentro de nuestro diseño. - Dominios: Conjunto de valores sobre los que se definen los atributos

Los pasos que se deben seguir para obtener el modelo Entidad/Relación son los siguientes: - Identificar y enumerar las posibles entidades - Identificar y enumerar las posibles interrelaciones - Dibujar las interrelaciones y los tipos de entidad con los atributos correspondientes.

111

MÓDULO B

OBJETIVOS

En esta unidad aprenderás:

Modelo Relacional

x x x

UNIDAD DIDÁCTICA 2 En esta unidad aprenderás:

1. Introducción 2. Elementos Básicos del Modelo Relacional 3. Estática del Modelo Relacional 4. Reglas básicas para la transformación del modelo E/R al modelo relacional - Modelo Relacional - Caso Práctico “Mentor” - Modelo Relacional - Caso Práctico “Historia” - Modelo Relacional - Caso Práctico “Constructora” - Modelo Relacional - Caso Práctico “Arte” 5. Introducción a las formas normales 6. Formas Normales - Normalización – Caso Práctico “Mentor”

x x

Cuáles son los elementos básicos del modelo relacional. A conocer la parte estática y dinámica del modelo relacional. En qué consiste la integridad referencial. Cuáles son las reglas básicas de transformación del modelo E/R al modelo relacional. Conocer las formas normales y aplicar las reglas de normalización.

Modelo Relacional

1.

2.

Introducción

Elementos básicos del Modelo Relacional Los elementos básicos del modelo relacional son las relaciones, cuya

El modelo relacional fue propuesto en 1970 como alternativa a los modelos

representación gráfica se realiza en forma de tabla. En las relaciones se pueden

existentes hasta ese momento por el investigador Edgar Codd, tomando como

distinguir varios tipos de elementos: su nombre, los atributos que contiene y que

base la teoría matemática de las relaciones. Este modelo perseguía la sencillez

representan las columnas de la tabla, y las tuplas o filas de la tabla. Además

de comprensión y de manipulación de la base de datos por parte del usuario

también podemos incluir los dominios que son aquellos conjuntos de donde los

final.

atributos toman los valores.

En un principio el modelo era simplemente un modelo teórico objeto de

Supongamos que queremos modelar una base de datos para mantener

investigación por parte de diversos centros, pero a medida que la tecnología fue

información a cerca de los museos de la Comunidad de Madrid y las exposiciones

evolucionando fueron surgiendo los primeros SGBD que lo soportaban y hoy en

que en ellos se pueden visitar, a través del modelo de datos relacional. De cada

día el modelo relacional es el más conocido y difundido por los sistemas

museo se desea almacenar su nombre, la dirección, la zona (Madrid, El Escorial,

comerciales (ORACLE, INFORMIX,... ).

etc) y el número de salas que contiene. Tendríamos la relación que se muestra en la figura 2.43.

La parte estática del modelo relacional consiste en la definición de los objetos permitidos. En la parte dinámica se definen las operaciones que se pueden realizar con estos objetos y sobre la base de datos.

MUSEO

Para que un SGBD pueda implementar un modelo de datos necesita apoyarse en un lenguaje de programación que gestione dicho modelo. El

Nombre

lenguaje aceptado por todos los sistemas de bases de datos comerciales es el

Dirección

Zona

Salas

SQL1, lenguaje estructurado de consulta que permite crear, manipular y obtener datos de todos los objetos que constituyen la base de datos. Este lenguaje fue

Arqueológico

Serrano, 13

Madrid

43

Centro de Arte Reina

Santa Isabel, 52

Madrid

21

Plaza de San Diego, S/N

Alcalá de Henares

normalizado por la Organización Internacional de Estándares (ISO) en 19922 y sufre, como todos los estándares, revisiones periódicas que hacen que

Sofía

continuamente se editen anexos de la norma. No obstante y como casi todas las normas, no está soportada por ningún producto comercial, es decir, cada SGBD

Universidad de

incorpora su propio SQL que recoge, dependiendo de cada sistema, lo más

7

Alcalá de Henares

significativo del estándar. Figura 2.43: Ejemplo de representación de una relación

En este capítulo nos vamos a centrar en definir los elementos básicos del modelo y explicaremos una metodología de diseño de bases de datos relacionales mediante la traducción de los objetos del modelo E/R explicado en

El nombre de la relación de la figura es MUSEO, los atributos o columnas son

capítulo anterior.

“Nombre”, “Dirección”, “Zona” y “Sala”. Las tuplas o filas son (Arqueológico; Serrano, 13; Madrid; 43), (Centro de Arte Reina Sofía; Santa Isabel, 52; Madrid; 21) y (Universidad de Alcalá de Henares; Plaza de San Diego, s/n; Alcalá de 1

Structured Query Languaje

2

Se conoce como SQL’92

Henares; 7 ). El atributo “Zona” pertenece a un dominio Zonas = (Madrid, Alcalá de Henares, Torrejón de Ardoz,...). 115

116

Modelo Relacional

Dentro de una relación podemos distinguir además dos nociones: el grado y

3.

Estática del Modelo Relacional

la cardinalidad. El grado es el número de atributos (columnas) que posee la Una relación, como se dijo en el apartado anterior se compone de un

relación, en el caso de nuestro ejemplo el grado sería cuatro. La cardinalidad se

nombre, unos atributos (con sus correspondientes dominios) y un conjunto de

refiere al número de tuplas (filas) de la relación, en nuestro caso tres.

tuplas. Es necesario distinguir entre lo que es la relación en sí y su contenido, es Hasta ahora hemos hablado de los elementos básicos del modelo, hemos

decir, una relación se define mediante un nombre y unos atributos de la

dicho que las relaciones son tablas, pero estas tablas tienen sus limitaciones, no

siguiente manera:

son las tablas que normalmente manejamos; por ejemplo, en estas tablas se prohibe que exista más de un valor en cada celda, este tipo de prohibiciones

NOMBRE_RELACIÓN ( Atributo1: dominio1, Atributo2: dominio2,......, AtributoN: dominioN )

pertenecen a las restricciones inherentes al modelo y las comentaremos más adelante en la siguiente sección.

Figura 2.44: Definición de relación

A continuación presentamos la estática y la dinámica del modelo relacional Los dominios son opcionales, pues los atributos pueden tomar valores en un

definiendo formalmente todos los objetos y comentando las sentencias básicas

conjunto acotado definido previamente, o bien y en principio, ser infinitas las

del lenguaje SQL para tratarlas.

posibilidades de un valor determinado. El cuerpo de una relación, es decir, su contenido, es el conjunto de tuplas de la relación que, por su propia naturaleza varía con el tiempo, pues no tendría mucho sentido disponer de una base de datos que almacenara en todo momento la misma información, que esta información no variara con el tiempo, ya que en este caso con disponer de ficheros aislados para almacenarla podríamos tener suficiente3. En la figura 2.45 se muestra un ejemplo de las partes de las que se compone una relación.

Definición de relación: ARTISTA (Nombre; Nacionalidad: Nacionalidades; Especialidad: Especialidades) Contenido de la relación:

ARTISTA Nombre

Nacionalidad

Especialidad

Agustín Redondella Dolores Dahlahaus Mario Scifano

Nacional Internacional Internacional

Pintura Fotografía Fotografía

Figura 2.45: Definición y contenido de una relación 3 Estos son los llamados ficheros históricos, archivos que recopilan información que ya no va a variar, como podrían ser las transacciones efectuadas por los clientes de una sucursal bancaria en el año 1996.

117

118

Modelo Relacional

Dentro de las relaciones existen principalmente dos tipos: relaciones base y

Pues bien, qué ocurriría si en nuestro mundo real existiesen dos

vistas. Las relaciones base son relaciones con existencia propia, las vistas son

artistas con el mismo nombre, nacionalidad y especialidad. No piense el

relaciones que provienen de otras relaciones base. Un ejemplo de relación base

lector que este supuesto es tan descabellado, por ejemplo podría darse

sería la relación ARTISTA de la figura 2.45, una vista sería la relación obtenida

el caso de un artista contemporáneo que coincidiera en nombre con otro

de seleccionar en la relación anterior aquellos artistas cuya especialidad fuera la

clásico (al no almacenarse la fecha de nacimiento no habría forma de

fotografía, a la que podríamos llamar FOTÓGAFO4.

distinguirlos). Podríamos actuar de forma negligente y no almacenar uno de los dos, con lo cual la obra de este autor quedaría sin reflejarse. Otra

Las relaciones se crean mediante el lenguaje SQL con las sentencias

posibilidad sería pensar en añadir un nuevo atributo que identificara a

“CREATE TABLE” o “CREATE VIEW” dependiendo del tipo de relación de que se

cada artista unívocamente dando valores distintos a cada tupla, como

trate.

puede ser un “Código de Artista”. De esta forma cada tupla sería distinta de las demás.

Hasta este momento únicamente hemos hablado de los objetos del modelo relacional, pero no hemos hecho mención de cómo interactúan estos objetos en

Este atributo que identifica unívocamente cada tupla de una relación es

la base de datos, ni de cómo diseñar realmente una base de datos en dicho

lo que se denomina en el modelo relacional clave primaria y en SQL se

modelo. Para ello debemos previamente comentar las restricciones, tanto las

define como “PRIMARY KEY”. Para representar la clave primaria de una

inherentes al modelo, como las restricciones de usuario, que son aquellas que

relación, se suele poner en negrita el o los atributos implicados en dicha

permiten recoger con la mayor fidelidad posible el mundo real objeto de nuestro

clave.

diseño.

Algún lector avispado podría discrepar de la opción escogida para identificar a los artistas de nuestra relación, pues para qué se elige introducir un atributo cuyo contenido es tan pobre semánticamente

3.1 Restricciones inherentes.

hablando si, habiendo escogido la “Fecha de nacimiento”, éste, junto con - Ningún atributo puede tomar más de un valor para cada tupla.

el atributo “Nombre” identificarían por completo todas las tuplas. La

Esto es lo mismo que decir que en cada una de las celdas de una tabla

justificación de nuestra elección radica en motivos de eficiencia, es

que represente una relación no puede haber más de un valor. Así por

mucho más eficiente a la hora de almacenar nuestra base de datos, una

ejemplo, no sería válido que en la relación ARTISTA el artista Mario

clave primaria corta en el sentido de que va a ser numérica o

Scifano tuviera como especialidades Fotografía y Escultura. Otra cosa

alfanumérica de pocos caracteres, mientras que el nombre y la fecha

distinta sería que en nuestro mundo real un artista pudiera tener varias

ocuparían más.

especialidades, en cuyo caso deberíamos modelar la relación de otra

Hemos justificado la razón de nuestra elección de clave primaria, no

manera, por ejemplo creando otra donde se almacenaran todas las

obstante

posibles especialidades y relacionándola con la primera, como veremos

“Nombre”

y

“Fecha

de

nacimiento”

sería

también

un

identificador válido para la relación. Este identificador se denomina en el

más adelante en el capítulo.

modelo relacional clave alternativa5. Es decir, una clave alternativa es aquella que aun pudiendo haber sido escogida como clave primaria de

- No importa el orden ni de las tuplas ni de los atributos.

una relación, no lo ha sido por otros motivos, como pueden ser motivos - Todas las tuplas de una relación deben ser distintas.

de eficiencia, etc.

4

5

Nótese que hemos dado un nombre a la vista, pues una vista es un tipo de relación y por tanto debe constar de los elementos de toda relación. 119

120

En SQL se define UNIQUE

Modelo Relacional

Es importante aclarar que se ha hablado de clave primaria y clave

La obligatoriedad de valores en un atributo es también una propiedad

alternativa y en el ejemplo hemos mencionado a dos atributos, esto es

de la clave primaria, por su propia definición.

porque la clave puede estar formada por varios atributos pero siempre

- Integridad Referencial.

será una sola clave. Una clave primaria es aquella que identifica cada tupla

podríamos

Se podría decir que la integridad referencial trata la forma en la que los

encontrarnos por ejemplo, cuatro atributos de una relación que

de

una

relación

de

manera

mínima,

es

decir,

datos de dos o más tablas se deben relacionar para no atentar contra la

identifiquen perfectamente a cada tupla, pero si con solo tres de ellos

integridad de la base de datos, es decir, para evitar que haya

también las podemos identificar, la clave estará formada por esos tres.

información no necesaria.

- Regla de integridad de la entidad.

La integridad referencial se controla mediante lo que se denomina clave ajena. Una clave ajena de una tabla es un atributo o un conjunto

Esta regla hace referencia a que ningún atributo que forme parte de la

de atributos, que es clave candidata6 de otra tabla con la cual está

clave primaria puede tomar un valor nulo.

relacionada la primera, en SQL se define como “FOREIGN KEY”. Para aclarar conceptos supongamos que se desean almacenar en la base de datos de nuestro ejemplo los premios que les han sido concedidos a los

3.2 Restricciones de usuario.

artistas por la Real Academia y, teniendo en cuenta que un premio es otorgado a un único artista. Esta relación PREMIO aparece un la figura

- Valores de uno o varios atributos que no pueden repetirse.

2.4. Se puede observar que posee un atributo “Artista” que almacena el Supongamos que, aun con todas las aclaraciones hechas en el

artista al que se le ha concedido cada premio, y que coincide con la clave

apartado de restricciones inherentes, el usuario decide que no puede

primaria de la relación ARTISTA (“Cod_artista”); “Artista” es clave ajena

haber dos artistas con el mismo nombre, esto haría que el atributo

de la relación PREMIO. Se representa mediante una flecha que sale de la

“Nombre” fuera único para cada tupla.

clave ajena y apunta hacia la clave primaria de la tabla con la que se relaciona.

De aquí se deduce que toda clave primaria es única también, pero el lenguaje SQL mediante su definición de clave primaria ya lo contempla.

Existen situaciones en los que el control de la integridad referencial no es tan sencillo como en el caso anterior. Supongamos que se desean

- Atributos que deben tener siempre valores para todas las tuplas de la

almacenar las Escuelas a las que pertenece cada artista

relación.

y la relación

que existe entre cada escuela y los artistas de nuestra base de datos, Por ejemplo, y en el caso que nos ocupa, supongamos que no está

sabiendo que a una misma escuela pueden pertenecer varios artistas y

permitido que un artista no tenga especialidad definida, esto significaría

que un artista puede haber pertenecido a escuelas diferentes (en

obligar a que el atributo especialidad tomara siempre valores, y el SQL lo

distintos periodos de tiempo); en primer lugar debemos crear una

reflejaría a través de “NOT NULL”.

relación ESCUELA cuyos atributos serán “Cod_escuela” (que será la clave primaria de la relación) y “Nombre”. Para relacionar los artistas con las

El hecho de encontrarnos con atributos que no tengan valores es muy

escuelas, en principio podemos pensar en varias soluciones:

frecuente. Supongamos que en la relación ARTISTA queremos almacenar también la extensión de la obra de cada autor. Es claro que en muchos

Podríamos introducir el atributo Cod_artista en la relación ESCUELA

casos este dato no se conocerá, por tanto tendríamos entradas en la

como clave ajena que llamara a la relación ARTISTA, pero pensemos en

tabla cuya celda correspondiente a la obra estaría vacía. 6

121

122

Una clave candidata es aquel atributo o conjunto de atributos que pueden ser clave de una relación.

Modelo Relacional

las filas de la tabla resultante. Por cada escuela solo tendríamos un

* Representación de la clave ajena por extensión de las relaciones:

artista que perteneciera a ella, puesto que el modelo relacional no ARTISTA

admite más de un valor en cada celda, lo que no es correcto.

Cod_artista

Podríamos pensar en el caso simétrico e introducir el atributo

0010508 0012454 1200004

Cod_escuela en la relación ARTISTA. Esto significaría que cada artista

Nombre

Especialidad

Nacionalidad

Agustín Redondella Dolores Dahlahaus Mario Scifano

Nacional Internacional Internacional

Pintura Fotografía Fotografía

solo puede pertenecer a una escuela, supuesto que va en contra de lo que se pide en el enunciado. PREMIO Código

¿Qué hacer entonces?

005-4 234-3 123-5

El problema se resuelve introduciendo una nueva relación PERTENECE cuyos atributos sean “Cod_artista” y “Cod_escuela”. Además estos

Nombre Caminos de hierro Ballantines Caja Madrid

Cod_artista 1200004 1200004 0010508

atributos formarán en conjunto la clave primaria y de esta forma

* Representación de la clave ajena por intensión de las relaciones:

tendremos a cada artista con las distintas escuelas a las que pertenece y cada escuela con todos sus artistas, como se puede ver en la figura 2.5.

ARTISTA (Cod_artista, Nombre, Nacionalidad, Especialidad)

Podemos incluir dos nuevos atributos “Fecha inicio” y “Fecha fin” 7, para

PREMIO (Código, Nombre, Cod_artista)

recoger el periodo de tiempo en que cada artista pertenece a una escuela

determinada.

Por

último,

tanto

“Cod_artista”

como

Figura 2.46: Ejemplo de utilización de clave ajena

“Cod_escuela” por separado, son claves ajenas de las relaciones ARTISTA y ESCUELA respectivamente.

7

Este atributo puede tomar valores nulos pues los artistas de una determinada escuela pueden seguir perteneciendo a ella en la actualidad. 123

124

Modelo Relacional

caso de nuestros artistas, se desea recoger la relación que existe entre un artista y su maestro, en el caso de que exista; un maestro a su vez

* Representación de la clave ajena por extensión de las relaciones:

es un artista y por tanto tiene todas las cualidades (atributos) de ellos. Podríamos introducir un atributo “Cod_artista_maestro” en la relación

ARTISTA

ARTISTA de manera que fuera clave ajena de ARTISTA que referencia a

Cod_artista 0010508 0012454 1200004

Especialidad

Nacionalidad

Nombre Agustín Redondella Dolores Dahlahaus Mario Scifano

la misma relación ARTISTA, como se muestra en la figura 2.48.

Pintura Fotografía Fotografía

Nacional Internacional Internacional

* Representación de la clave ajena por extensión de las relaciones:

PERTENECE Cod_escuela 123/7 142/8 241/1 123/7 241/6

Cod_artista

Fecha inicio

Fecha

1200004 1200004 0010508 0012454 0010508

12/10/1965 12/4/1986 1/5/1969 16/2/1975 25/7/1991

12/12/1979 --24/16/1987 -----

ARTISTA Cod_artista 0010508 0012454 1200004

Nombre

Nacionalidad

Agustín Redondella Nacional Dolores Dahlahaus Internacional Internacional Mario Scifano

Especialidad Cod_artista_maestro Pintura Fotografía Fotografía

0125000 1200004 ------

ESCUELA Nombre

Cod_escuela 123/7 142/8 241/1 241/6

Impresionismo fotográfico Surrealismo de luz y sombras Modernismo Inglés Cubismo

* Representación de la clave ajena por intensión de las relaciones:

ARTISTA (Cod_artista, Nombre, Nacionalidad, Especialidad, Cod_artista_maestro)

* Representación de la clave ajena por intensión de las relaciones: ARTISTA (Cod_artista, Nombre, Nacionalidad, Figura 2.48: Ejemplo de utilización de clave ajena

PERTENECE (Cod_escuela, Cod_artista, Fecha inicio, Fecha fin)

Como consecuencia de la introducción de la noción de clave ajena en

ESCUELA Cod_escuela, Nombre)

las relaciones, se pueden definir ciertas operaciones de borrado y modificación que permiten mantener la integridad de los datos de nuestras bases de datos. Estas operaciones son las siguientes: Figura 2.47: Ejemplo de utilización de clave ajena

- Operación restringida (“NO ACTION”8).

Hasta el momento hemos visto como se pueden interrelacionar dos El borrado (DR) o la modificación (UR) de las filas de la relación

relaciones distintas mediante la definición de clave ajena, pero qué

que contiene la clave ajena no se permite mientras existan tuplas

sucede en el caso de que una relación deba interrelacionarse con ella

en la relación a la que se referencia. Esta situación ocurre por

misma, esta situación, aunque en principio pueda parecer atípica, ocurre muchas veces en el mundo real. Supongamos por ejemplo, que en el

8

125

126

También conocida como RESTRICT

Modelo Relacional

ejemplo

en

la

relación

ARTISTA

con

la

clave

ajena

4.

“Cod_artista_maestro”. Solamente eliminamos un artista cuando

Reglas básicas para la transformación del modelo E/R al

modelo relacional

no tenga ningún maestro, en caso contrario se rechaza la En la segunda fase del diseño tenemos que transformar el esquema

operación.

realizado en el modelo Entidad/Interrelación a un esquema lógico, ya que no También se podría dar este tipo de operación en el caso por

existe ningún Sistema Gestor de Bases de Datos que soporte el modelo E/R. Esta

ejemplo de que en nuestra base de datos de artistas se desee

fase es muy importante ya que para pasar de un esquema a otro debemos

mantener siempre el histórico de todos los premios, en este caso

preservar todas las características recogidas en la primera de fase. De esta

no debemos permitir borrar ningún artista de la relación ARTISTA

forma nos aseguraremos que la Base de Datos que implementemos se

al que se le haya concedido algún premio, solo podremos eliminar

corresponda con lo que en un principio queríamos recoger y almacenar.

a los artistas que no tienen premios. En este apartado explicaremos las reglas básicas para transformar un - Operación en cascada (“CASCADE”).

esquema E/R en un esquema relacional. De esta forma iremos cubriendo todas las fases del diseño de una base de datos.

En este tipo de operación, cuando se elimina (DC) o modifica (UC) una tupla de la relación que es referenciada, los cambios se

La primera de las reglas de transformación nos dice que toda entidad se

transmiten en cascada a las tuplas de la relación que contiene la

transforma en una relación o tabla, y los atributos o características asociadas a

clave ajena cuyos valores se han modificado. Así por ejemplo en

ella pasan a ser atributos de la relación.

el caso del apartado anterior, utilizando este tipo de operación, si Supongamos que queremos transformar la entidad alumno, Figura 2.51:

borramos un artista se borran también todos los datos de su maestro, por lo que no es la mejor opción. En cambio, si utilizamos la operación “CASCADE” en la relación PERTENECE,

D.N.I.

cuando eliminemos un artista, se eliminará la relación que existe

Nombre Dirección e-mail Teléfono

entre este y la escuela a la que pertenecía, pero no los datos ALUMNO

propios de la escuela.

Nacionalidad

Estos dos tipos de operaciones sobre la clave ajena son los más habituales y los que implementan la mayoría de los SGBD

Figura 2.51: La entidad ALUMNO y sus atributos

comerciales. Existen otros dos tipos de operaciones (puesta a nulos “SET NULL” y valor por defecto “SET DEFAULT”) que por su complejidad y falta de uso en los SGBD no se van a definir aquí,

Según la regla de transformación esta entidad pasa a ser una relación cuyo

no obstante hay una amplia bibliografía sobre el tema, la cual

nombre, en general, es el plural del nombre de la entidad. En nuestro ejemplo,

aparece al final del capítulo y que desde aquí animamos a que se

por tanto, se llamará ALUMNOS. Esta relación tendrá como clave primaria

consulte.

(PRIMARY KEY) el identificador principal de la entidad, DNI, ALUMNOS ( Dni ) como clave alternativa (UNIQUE) el E-mail ya que proviene de un identificador alternativo, ALUMNOS ( Dni, E-mail ) 127

128

Modelo Relacional

como atributos que no admiten valores (NOT NULL) tenemos Nombre,

CURSO (Nombre, Libro_Consulta*, WWW)

Dirección y Nacionalidad ya que en el esquema E/R son atributos obligatorios,

Donde en la relación CURSO tenemos como clave primaria el Nombre, como

ALUMNOS ( Dni, E-mail, Nombre, Dirección, Nacionalidad)

alternativa WWW y como atributo que admite valores nulos Libro_Consulta.

Y por último, tenemos un atributo que puede admitir valores nulos, Teléfono

Como la interrelación Matricular tiene correspondencia N:M crearemos una

que proviene de un atributo opcional en el esquema E/R.

nueva relación compuesta de dos atributos, el DNI del Alumno y el Nombre del curso donde ambos formarán la clave primaria:

ALUMNOS ( Dni, E-mail, Nombre, Dirección, Nacionalidad, Teléfono*)

MATRICULAR (DNI, Nombre)

Como podemos observar la primera regla de transformación se centra en uno de los elementos básicos del modelo E/R, las reglas que nos faltan nos

Cada uno de los atributos de la relación MATRICULAR serán además, claves

guiarán para realizar la transformación del otro elemento importante de este

ajenas que referencian a las relaciones ALUMNOS y CURSO respectivamente, de

modelo: la interrelación.

esta

forma

nos

aseguramos

que

los

valores

de

estos

atributos

estén

almacenados previamente en las relaciones CURSO y ALUMNOS. Esto significa

La segunda regla de transformación nos indica que las interrelaciones

que un alumno no puede realizar un curso o lo que es lo mismo, que exista una

cuyo tipo de correspondencia es N:M se transforman en una nueva relación cuyo

tupla en la relación MATRICULAR, sin que el alumno o el curso se hallan

nombre se corresponde con el nombre de la interrelación y donde la clave

registrado como tal.

primaria se compone de los atributos identificadores de las dos entidades que relaciona.

ALUMNOS ( DNI, Nombre, Dirección, Nacionalidad, E-mail, Teléfono*)

Veamos con el ejemplo de la relación que existe entre ALUMNO y CURSO cómo se transforma la interrelación N:M (Figura 2.52).

MATRICULAR ( DNI, Nombre )

CURSO (Nombre, Libro_Consulta*, WWW) Libro de Nombre consulta

D.N.I. Nombre Dirección e-mail

WWW Figura 2.53: Transformación de una interrelación N:M

Teléfono N:M ALUMNO

Matricular

CURSO

En el esquema relacional de la Figura 2.53 nos faltaría representar las

Nacionalidad

opciones de borrado y modificación que en este caso sólo pueden ser o restringidas o en cascada ya que, tanto el DNI como el Nombre al pertenecer a la clave primaria de la relación MATRICULAR no pueden tomar valores nulos ni

Figura 2.52: Interrelación con correspondencia N:M

valores por defecto. Si borramos un alumno ¿borraríamos todos los cursos que ha realizado?. El primer paso a realizar es convertir las entidades y sus atributos en las

Para recoger esta característica deberíamos tomar la opción de borrado en

relaciones correspondientes:

cascada al igual que si quisiéramos que las modificaciones en la relación ALUMNO se propagaran a la relación MATRICULAR. Por el contrario, si no

ALUMNOS ( DNI, Nombre, Dirección, Nacionalidad, E-mail, Teléfono*)

queremos borrar a los alumnos que tengan cursos asociados en MATRICULAR 129

130

Modelo Relacional

escogeríamos la opción de borrado restringida. Tomamos la primera alternativa explicada y razonamos de la misma forma para la clave ajena Nombre. EMPLEADOS (Código, DNI, Nombre, Dirección, NSS, Teléfono, Nombre_D) ALUMNOS ( DNI, Nombre, Dirección, Nacionalidad, E-mail, Teléfono*)

DEPARTAMENTO (Nombre , Localización, Teléfono)

BC:MC

MATRICULAR ( DNI, Nombre ) Figura 2.56: Transformación de la Interrelación Trabajar

BC:MC

CURSO (Nombre, Libro_Consulta*, WWW)

De Figura 2.54: Transformación de una interrelación N:M con las opciones de borrado y modificación

esta

departamento

manera y

que

recogemos un

que un

departamento

empleado puede

tener

trabaja

en

asociados

un

solo

distintos

empleados, o lo que es lo mismo, representamos la semántica de una interrelación del tipo 1:N aunque perdemos el nombre de la interrelación. La

tercera

y

última

regla

nos

indica

que

la

transformación

de

interrelaciones cuyo tipo de correspondencia es 1:N se traduce en una propagación de clave o en una nueva relación si la interrelación posee atributos. Veamos en que consiste el fenómeno de propagación de clave mediante el siguiente ejemplo. Código

DNI Nombre Dirección

Nombre LocalizaciónTeléfono N:1

NSS EMPLEADO

Trabajar

DEPARTAMENTO

Teléfono

Figura 2.55: Interrelación Trabajar con correspondencia 1:N

Transformamos las entidades en relaciones: EMPLEADOS (Código, DNI, Nombre, Dirección, NSS, Teléfono) DEPARTAMENTO (Nombre, Localización, Teléfono) Y para representar el tipo de interrelación 1:N añadimos un nuevo atributo en la relación EMPLEADO, Nombre_D, que será clave ajena que referencia a la relación DEPARTAMENTO.

131

132

Modelo Relacional

Objetos en el modelo E/R

Transformación al modelo

Entidad: ALUMNO

relacional - Relación: ALUMNOS

teléfono

Para afianzar los conocimientos sobre la aplicación de las reglas de

ALUMNO

transformación vamos a retomar retomamos el ejemplo que diseñamos el caso

nacionalidad

e-mail DNI nombre dirección

práctico expuesto en la sección 1 de esta segunda parte, acerca de una base de

Clave primaria: DNI

Identificador principal: DNI Atributos

Obligatorios:

Nombre,

Dirección y Nacionalidad

Atributos

NOT

NULL:

Nombre,

Dirección y Nacionalidad

datos para el proyecto MENTOR. El esquema E/R correspondiente al diseño de la base de datos se muestra a continuación: Objetos en el modelo E/R

Nombre DNI completo e-mail

Entidad:AULAS

nombre

WWW

libro

AULA

N:M TUTOR

(1, n)

Asociar

(0, n)

código_aula

CURSO

F_Comienzo Coordinador

Transformación al modelo relacional - Relación: AULAS

nombre

dirección

Identificadorprincipal: Código_Aula

Clave primaria: Código_Aula

Atributos Obligatorios: Descripción,

Atributos NOT NULL: Descripción,

Dirección

Dirección

(1, n)

F_Finalización

nacionalidad = (española, no_española)

Matricular

N:M

Una vez transformadas las entidades, veamos que tipo de interrelación las coordinador = (SI, NO) 1:N

(1, n)

(1,n)

(1,1) Pertenecer

AULA

ALUMNO

asocia. Pertenecer tienen un tipo de correspondencia 1:N y además no contiene

teléfono

ningún atributo, por lo que aplicando la tercera regla de transformación

nacionalidad

propagamos la clave de la relación AULAS a la relación ALUMNOS, quedando de esta manera.

dirección código_aula

DNI

descripcion

e-mail nombre dirección

ALUMNOS (DNI. Nombre, Dirección, Teléfono*, Nacionalidad, e-mail, aula)

(1,1) 1:N

Mantener

(1,n)

AULAS(Código_Aula, Descripción, Dirección)

ADMINISTRADOR e-mail DNI

nombre

Figura 2.57: Esquema E/R completo

Observar que el nuevo atributo de la relación ALUMNOS, Aula, no se llama igual que la clave primaria de la relación a la que referencia ya que no es La transformación la haremos interrelación a interrelación, por lo que

necesario que tengan el mismo nombre.Al añadir un nuevo atributo debemos

primero transformaremos las entidades asociadas a estas. Empezaremos por la

pensar en las propiedades del mismo, es decir, si el atributo admite nulos o no, y

interrelación PERTENECER y las entidades ALUMNO y AULA que asocia.

las opciones de borrado y modificación de esta, ya que es clave ajena. 133

134

Modelo Relacional

Si obligamos a que el atributo Aula tenga un valor (NOT NULL), estaremos

Si quisiéramos borrar la fila o tupla correspondiente al Código_Aula = ‘01’,

recogiendo que un alumno siempre ha de tener un aula asignada. En caso

¿qué haríamos con los alumnos, Juan López y Olga Calle, asignados a esta aula?

contrario, admitiríamos que existan alumnos en nuestra Base de Datos que no Si eligiéramos el borrado en cascada se eliminarían de forma automática las

pertenezcan a ningún aula. Según se muestra en la siguiente figura, debemos

dos filas en la relación ALUMNOS correspondientes a estos alumnos. Si no

adoptar la primera propuesta, para no perder información recogida en el

queremos perder esta información, tendremos que elegir la opción de borrado

esquema conceptual.

restringuido. La opción de modificación será en cascada.

Un alumno pertenece a un y solo un aula

La 1:N (1,1) Pertenecer

AULA

(1,n)

DNI

código_aula descripcdirección

ALUMNO

interrelación

MANTENER

asocia

la

entidad

AULA

con

la

entidad

teléfono

ADMINISTRADOR. Transformaremos esta última a una nueva relación para

nacionalidad

comentar la transformación de la interrelación.

e-mail nombre dirección

Objetos en el modelo E/R Entidad:ADMINISTRADORES

Transformación al modelo relacional Relación: ADMINISTRADORES

Ahora vamos a estudiar las opciones de borrado y modificación asociadas a la clave ajena, Aula.

ADMINISTRADOR

Identificador Principal: DNI

Clave Primaria: DNI

Identificador Alternativo: e-mail

Clave Alternativa: e-mail

Atributos Obligatorios: Nombre y

Atributos NOT NULL: Nombre y e-mail

e-mail

DNI

Nombre

Dirección

Teléfono

Nacionalidad

e-mail

Aula

Pez,15

4674039

Española

[email protected]

01

56321411 Andrea Solís Bronce,25

4801325

Española

[email protected]

03

36952144 Olga Calle

Principe,18

7771482

Española

85669900 José Pérez

La Cruz,1

4671482

No_española

07545658 Juan López

[email protected] [email protected]

DNI

nombre

e-mail

01 02

Como la interrelación MANTENER es del tipo 1:N tendremos que aumentar con un nuevo atributo la relación ADMINISTRADORES, que nos dará información Código_Aula

Descripción

Dirección

01 02 03

Los Carrascales El Hayedo La Nogalera

Avd. de España 3, Cercedilla Gran Vía 10, Coslada Irún 49,

acerca del código del aula a la que esta asignado un determinado administrador.

ADMINISTRADORES ( DNI, Nombre, e-mail, Aula ) ( AREAS (Código_Aula, Nombre, Dirección)

La interrelación MATRICULAR es del tipo N:M y asocia las entidades CURSO y ALUMNO. En la tabla se muestra la transformación de la entidad Curso. 135

136

Modelo Relacional

ALUMNO Objetos en el modelo E/R

Transformación al modelo relacional - Relación: CURSOS

DNI

Nombre

Dirección

Teléfono

Nacionalidad

e-mail

Aula

07545658

Juan López

Pez,15

4674039

Española

[email protected]

01

Entidad: CURSO

Identificador Principal: Nombre

Clave primaria: nombre

56321411

Andrea Solís

Bronce,25

4801325

Española

[email protected] 03

Identificador Alternativo: WWW

Clave alternativa: WWW

36952144

Olga Calle

Principe,18

7771482

Española

[email protected] 01

Atributos Obligatorios: WWW

Atributos NOT NULL: WWW

85669900

José Pérez

La Cruz,1

4671482

No_española

[email protected] 02

Atributos Opcionales: Libro

Atributos NULL: libro

CURSO

nombre WWW

libro

Aplicando directamente la regla de transformación para interrelaciones N:M,

MATRICULAR

crearíamos una nueva relación donde incluiríamos los atributos asociados a la interrelación MATRICULAR, F_Comienzo y F_Finalización.

Alumno

Curso

F_Comienzo

F_Finalización

07545658

Access 97

23-05-2000

19-12-2000

07545658

Access 2000

23-05-2000

30-01-2001

ALUMNOS (DNI, Nombre, Dirección, Teléfono*, Nacionalidad, e-mail, aula)

MATRICULAR ( Alumno, Curso, F_Comienzo, F_Finalización)

CURSO CURSOS ( Nombre, WWW, Libro*)

La clave primaria de la relación MATRICULAR se compone de dos atributos,

Curso

WWW

Access 97

www.mentor.es/access97.html

Access 2000

www.mentor.es/accss2000.html

Libro

Alumno, que contiene valores de DNIs correspondientes a tuplas que aparecen en la relación ALUMNOS y Curso que almacena valores de los nombres de los cursos que se imparten en el Mentor. ¿Esta clave primaria identifica cada una de

En este caso la clave primaria de MATRICULAR identificaría cada una de las

las tuplas pertenecientes a la relación MATRICULAR?

tuplas, además de esta forma controlaríamos que el mismo alumno no este matriculado en el mismo curso dos veces.

Supongamos que el alumno Juan López cuyo DNI es 07545658 se matriculó en dos cursos: “Access 97“ y “Access 2000“, el 23-05-2000, y finalizó el 19-12-

Discutamos ahora las opciones de borrado y modificación de las claves

2000 y el 30-1-2001, respectivamente.

ajenas de esta relación.

137

138

Modelo Relacional

Si borramos una fila de la relación alumno y este está matriculado en algún

ALUMNOS (DNI, Nombre, Dirección, Teléfono*, Nacionalidad, e-mail, aula)

curso. ¿Borraríamos las tuplas en las que aparece su DNI en la relación

BC:MC

MATRICULAR?

MATRICULAR ( Alumno, Curso, F_Comienzo, F_Finalización)

Pues parece normal que asi sea, ya que si el alumno se da de baja es mejor

BR:MC

que no aparezca como matriculado en ningún curso del Mentor.

CURSOS ( Nombre, WWW, Libro*)

¿Qué pasaría si diéramos de baja un curso? Si borráramos una fila de la tabla CURSO y en la relación MATRICULAR aparecen tuplas con el código de este

La última interrelación que nos falta por transformar es ASOCIAR, pero

curso, la opción de borrar en Cascada haría desaparecer estas filas lo que

primeramente transformaremos la entidad TUTOR, la cual participa en dicha

conllevaría que los alumnos que estaban matriculados en este curso no

interrelación.

aparezcan matriculados en ningún curso. Eso haría que la base de datos no cumpliera los requisitos especificados en el modelo Conceptual.

DNI

D.N.I.

Nombre

Dirección e-mail

Nombre completo

e-mail

Transformación al modelo

Entidad: TUTOR

relacional - Relación:TUTORES

TUTOR

Libro

Nombre F_Comienzo

Teléfono

WWW

(1,n)

Matricular

Identificador Principal: DNII

Clave primaria: DNI

Identificador Alternativo: e-mail

Clave alternativa: e-mail

Atributos Obligatorios: Nombre

Atributos NOT NULL: Nombre

completo

completo

F_Finalización

M:N ALUMNO

Objetos en el modelo E/R

(1,n)

CURSO

Nacionalidad

Un curso tiene al menos un alumno matriculado

La interrelación ASOCIAR es del tipo N:M por lo que tendremos que crear una nueva relación. La mejor opción de borrado en ese caso sería la restringida, de esta forma

TUTORES (DNI, Nombre_completo, e-mail)

primero avisaríamos a los alumnos matriculados en el curso que se pretende anular para indicarles si quieren matricularse en otro de los cursos del mentor y de esta forma asignarles al nuevo curso que elijan, para luego proceder a la

ASOCIAR (Tutor, Curso, Es_Coordinador)

eliminación. Para las opciones de modificación en ambas claves ajenas serán en cascada.

CURSOS (Nombre, Libro, WWW)

La clave primaria estará formada por dos atributos, Tutor y Curso, que a su vez serán claves ajenas que referenciarán a las relaciones TUTORES y CURSOS, respectivamente.

139

140

Modelo Relacional

Si borramos un curso de la tabla CURSOS borraremos todas aquellas filas en

TUTORES (DNI, Nombre_completo, e-mail)

la tabla ASOCIAR que se relacionen con él y no nos importará que existan

BR/MC

tutores que no se relacionen con cursos porque cumplimos las especificaciones del modelo conceptual por lo tanto el borrado es en cascada.

ASOCIAR (Tutor, Curso, Es_Coordinador) BC/MC

DNI

CURSOS (Nombre, Libro, WWW)

Nombre completo e-mail

Nombre

Libro

WWW

N:M (1,n)

El esquema relacional definitivo sería el siguiente: (0,n)

CURSO

Asociar

TUTOR

ADMINISTRADORES (

DNI , Nombre, e-mail, Aula)

BR/MC AULAS ( Código_Aula , Descripción, Dirección) BR/MC

Un tutor puede estar asignado a 0 o n cursos

ALUMNOS ( DNI , Nombre, Dirección, Teléfono*, Nacionalidad, e-mail, aula) BC:MC MATRICULAR ( Alumno , Curso , F_Comienzo, F_Finalización)

Sin embargo, la opción de borrado para la clave ajena tutor será restringida,

BR:MC

de esta forma nos aseguramos que al dar de baja a un tutor en la relación

CURSOS ( Nombre , WWW , Libro*)

TUTORES dejemos a algún curso sin tutor, esto produciría una inconsistencia con

BC/MC

los requisitos especificados.

ASOCIAR ( Tutor, Curso , Es_coordinador) BR/MC TUTORES ( DNI , Nombre,_completo,

DNI

Nombre completo

Nombre

e-mail

Libro

WWW

N:M (1,n) TUTOR

(0,n) Asociar

CURSO

Un curso tiene al menos un tutor

La opciones de modificación en ambos casos sería en cascada.

141

142

e-mail)

Modelo Relacional

Analizaremos interrelación a interrelación. La primera que analizaremos será la interrelación GOBERNAR, con sus entidades CABALLERO y PROVINCIA. Recordaremos su representación en el modelo entidad-relación:

Veamos a continuación la transformación al modelo relacional a partir del diagrama entidad-relación que obtuvimos para crear la base de datos de los Apodo Nombre Apellido . Fecha_Nac

estudiantes universitarios de Historia.

Año_inicio

Denominación Num_Hab Num_Años

M:N CABALLERO Apodo Nombre .

Fecha_Nac

Año_inicio

Denominación Num_Años

(1,n)

(0,n) Gobernar

PROVINCIA

Num_Hab

M:N CABALLERO

(1,n)

(0,n)

PROVINCIA

Gobernar

(1,n)

M:N

Nombre

(1,n) Apodo Nombre Apellido . Fecha_Nac

F_Retirada F_Incorpor Resultado Participar

Fecha_Inicio

Objetos en el modelo E/R

Transformación al modelo relacional - Relación:

Entidad: CABALLERO

M:N

CABALLEROS

CABALLERO

Reinar

Fecha_Fin

Identificador principal: Apodo

Clave primaria: Apodo

Atributos Obligatorios: Nombre,

Atributos NOT NULL:

Apellido, Fecha_Nac

Nombre, Apellido, Fecha_Nac

(1,n) CRUZADA

Nombre Fecha_Nac Completo Corona (1,n) Objetos en el modelo E/R

REY

Transformación al modelo relacional

Entidad: PROVINCIA

(1,n)

Denominación Num_Hab

(1,n)

Relación: PROVINCIAS

PROVINCIA Ascender

Identificador principal:

N:M

Clave primaria: Denominación

Denominación Atributos NOT NULL: Atributos Obligatorios: Num_Habitantes

143

144

Num_Habitantes

Modelo Relacional

Lo primero que haremos será transformar las entidades y sus atributos en

CABALLEROS ( Apodo, Nombre, Apellido, Fecha_Nac)

relaciones:

BR:MC GOBERNAR ( Apodo, Denominación, Año_Inicio, Num_Años)

CABALLEROS ( Apodo, Nombre, Apellido, Fecha_Nac)

BR:MC

PROVINCIAS ( Denominación, Num_Habitantes)

PROVINCIAS ( Denominación, Num_Habitantes)

Interrelación N:M con atributos de la interrelación y opciones de borrado y actualización

Una vez transformadas las entidades, vemos que las cardinalidades entre ambas en N:M, por lo que aplicando la regla de transformación para esta

Veamos a continuación la interrelación PARTICIPAR en la que intervienen las

interrelación, crearíamos una nueva relación GOBERNAR donde la clave primaria

entidades CABALLERO y CRUZADA. Recordaremos su representación en el

se compone de los dos atributos que son clave primaria en las entidades:

modelo entidad-relación:

GOBERNAR (Apodo, Denominación)

Nombre Apodo completo Fecha_Nac

CABALLERO

Según el diagrama entidad-relación existen dos atributos que pertenecen a

(1,n)

Nombre F_Retirada F_Incorpor Resultado N:M (1,n) Participar

Fecha_Inicio

Fecha_Fin

CRUZADA

la interrelación Gobernar: Año de Inicio, Número de Años, por lo que debemos representarlos

dentro

de

la

relación

GOBERNAR

(Apodo,

Denominación,

Año_Inicio,Num_Años).

Lo primero que haremos será transformar la entidad CRUZADA:

Objetos en el modelo E/R

Transformación al modelo relacional

CABALLEROS ( Apodo, Nom bre, Apellido, Fecha_Nac)

Nombre

Fecha_Inicio

Fecha_Fin

Entidad: CRUZADA Relación: CRUZADAS

GOBERNAR ( Apodo, Denominación, Año_Inicio, Num_Años) CRUZADA

PROVINCIAS ( Denominación, Num_Habitantes)

Identificador principal: Nombre

Clave primaria: Nombre

Atributos Obligatorios:

Atributos NOT NULL:

Fecha_Inicio, Fecha_Fin

Fecha_Inicio, Fecha_Fin

Discutiremos a continuación las opciones de borrado y modificación de las claves ajenas de esta relación.

La interrelación PARTICIPAR es del tipo N:M por lo que crearemos una nueva relación donde la clave primaria será la unión de las claves primarias de las

Como se desea guardar la relación existente entre los caballeros y las

relaciones CABALLERO y CRUZADA.

provincias que gobernaron, no debemos permitir el borrado en cascada en la relación GOBERNAR. El borrado debe ser restringido y la modificación en cascada. 145

146

Modelo Relacional

CABALLEROS ( Apodo, Nom bre, Apellido, Fecha_Nac)

CABALLEROS ( Apodo, Nombre, Apellido, Fecha_Nac) BR:MC

PARTICIPAR ( Nombre_Cruzada, Apodo )

PARTICIPAR ( Nombre_Cruzada, Apodo, Fecha_Incorporación, Fecha_Retirada, Resultado ) BR:MC

CRUZADAS (Nombre_Cruzada, Fecha_Inicio, Fecha_Fin)

CRUZADAS (Nombre_Cruzada, Fecha_Inicio, Fecha_Fin) Transformación de una interrelación N:M con atributos de la interrelación y opciones de borrado y actualización

A continuación analizaremos la interrelación REINAR. Lo primero que

A la relación PARTICIPAR debemos añadir los atributos propios de la

haremos será recordar su representación en el modelo entidad-relación.

interrelación:

CABALLEROS ( Apodo, Nombre, Apellido, Fecha_Nac)

Nombre

Denominación Num_Habitantes

BR:MC

Fecha_Nac

Corona

M:N

PARTICIPAR ( Nombre_Cruzada, Apodo, Fecha_Incorporación, Fecha_Retirada, Resultado ) PROVINCIA

(1,n)

(1,n)

REY

Reinar

BR:MC CRUZADAS (Nombre_Cruzada, Fecha_Inicio, Fecha_Fin)

De esta forma, podremos almacenar la información de los caballeros que Transformaremos

participaron en las distintas cruzadas, con la fecha en la que se incorporó cada

centraremos

uno, su fecha de retirada y el resultado en su cruzada.

las

las

entidad

entidades REY

ya

que que

intervienen. la

entidad

En

este

PROVINCIA

caso, la

nos

vimos

anteriormente. Analizaremos por último para esta interrelación las opciones de borrado y modificación. Como no debemos perder la información de las cruzadas en las que participaron los caballeros, el borrado en la relación PARTICIPAR debe ser Objetos en el modelo E/R

restringido, mientras que la actualización sobre la relación PARTICIPAR será en Nombre

cascada, de tal forma que si se actualiza la información de las tablas

Fecha_Nac

Corona

Transformación al modelo relacional

Entidad: REY Relación: REYES

CABALLEROS o CRUZADAS, se propague dicha actualización sobre la relación PARTICIPAR.

REY

147

148

Identificador principal: Nombre

Clave primaria: Nombre

Atributos Obligatorios:

Atributos NOT NULL:

Fecha_Nac, Corona

Fecha_Nac, Corona

Modelo Relacional

Nombre Fecha_Nac Completo

Vemos que la interrelación Reinar tiene una cardinalidad M:N, por lo que la

Corona

representación en el modelo relacional será la siguiente:

REY (1,n)

PROVINCIAS ( Denominación , Num_Habitantes )

(1,n)

REINAR ( Nombre_Rey, Denominación_Provincia ) Ascender

N:M

REY ( Nombre_Rey, Fecha_Nac, Corona )

Transformación de una interrelación M:N

Vemos que la cardinalidad en este caso es N:M. Recodemos que este tipo de cardinalidades

No debemos permitir el borrado en cascada en la relación REINAR, ya que

se

resuelven

en

el

modelo

relacional

interesa conservar la información de los reyes que reinaron en las diferentes

de las claves primarias de las relaciones a las que asocia.

provincias. Por lo tanto, el borrado será restringido. La actualización, sí debe realizarse en cascada, de tal forma que si actualizamos alguna tupla de las relaciones PROVINCIAS o REY, se propaque la actualización sobre la relación

REY ( Nombre_Rey, Fecha_Nac, Corona )

REINAR.

PROVINCIAS ( Denominación , Num_Habitantes )

transformando

la

interrelación Ascender en un nueva relación donde la clave primaria es la unión

ASCENDER ( Nombre_Rey, Nombre_Rey_Asc) BR:MC

REINAR ( Nombre_Rey, Denominación_Provincia ) BR:MC

REY ( Nombre_Rey, Fecha_Nac, Corona )

Transformación de una interrelación M:N con opciones de borrado y modificación

Por último y para terminar con el modelo relacional, analizaremos la interrelación ASCENDER. La relación REY ya la analizamos en el punto anterior por lo que recordaremos su representación en el modelo entidad-relación:

149

150

Modelo Relacional

MODELO RELACIONAL FINAL Para obtener el modelo relacional del proyecto de la Constructora, partiremos del diagrama entidad-relación, analizaremos cada interrelación y en función de las cardinalidades,

aplicaremos

la

correspondiente

regla

transformación.

CABALLEROS ( Apodo, Nombre, Apellido, Fecha_Nac)

N:1 Depender

BR:MC (1,n)

GOBERNAR (Apodo, Denominación, Año_Inicio, Num_Años)

(1,1

BR:MC Cod_Postal Dirección. Num_Baños Cod_Inmueble. Num_Habitaciones. Importe_Alquiler

PROVINCIAS ( Denominación, Num_Habitantes) Cod_Suc. Cod_Postal Direccion Población Teléfono BR:MC

1:N (1,1)

PARTICIPAR (Nombre_Cruzada, Apodo, Fecha_incorporación, Fecha_Retirada, Resultado)

SUCURSAL

BR:MC

(1,n)

INMUEBLE

Gestionar

(1,1)

(1,n)

CRUZADAS (Nombre_Cruzada, Fecha_Inicio, Fecha_Fin)

Importe_Max Fecha_Fin Fecha_Ini

Trabajar

Alquilar

1:N REY (Nombre_Rey, Fecha_Nac, Corona)

N:M

BR:MC (1,n)

BR:MC

REINAR( Nombre_Rey, Denominación_Provincia)

DNI.

Nombre

Apellido (1,n)

COMERCIAL

CLIENTE DNI

ASCENDER (Nombre_Rey, Nombre_Rey_Ascendente)

Fecha_Contrato Nombre Apellidos (1,1)

BR:MC DNI

1:N Atender

151

152

Apellidos Nombre Teléfono

(1,n) CLIENTE POTENCIAL

Teléfono

de

Modelo Relacional

En primer lugar analizaremos será la interrelación TRABAJAR, en la que intervienen

las

entidades

SUCURSAL

y

COMERCIAL.

Recordaremos

A continuación, transformaremos las entidades y sus atributos en relaciones:

su

SUCURSAL ( Cod_Suc, Dirección, Cod_Postal, Población, Teléfono)

representación en el modelo entidad-relación:

COMERCIAL ( DNI, Apellidos, Fecha_Contrato) Una vez transformadas las entidades, vemos que la interrelación es del tipo 1:N por lo que aplicando la regla de transformación, añadiremos un nuevo Cod_Suc. Cod_Postal Direccion Población Teléfono

Nombre Apellidos Fecha_Contrato

atributo en la relación COMERCIAL

DNI SUCURSAL

(1,1)

(1,n)

Trabajar

que

nos

dará

información

sobre

la

sucursal en la que están trabajando los comerciales.

1:N COMERCIAL

SUCUSAL (Cod_Suc, Dirección, Cod_Postal, Población,_Teléfono)

COMERCIAL ( DNI, Apellidos, Fecha_Contrato, Cod_Suc)

Objetos en el modelo E/R

Cod_Suc. Cod_Postal Direccion Población Teléfono

Transformación al modelo Transformación de una interrelación 1:N

relacional Entidad: SUCURSAL Relación: SUCURSALES

Estudiaremos las opciones de borrado y modificación de las tuplas. En este

SUCURSAL Identificador principal: Cod_Suc

Clave primaria: Cod_Suc

Atributos Obligatorios: Dirección,

Atributos NOT NULL: Dirección,

Cod_Postal, Población, Teléfono

Cod_Postal, Población, Teléfono

caso, no debemos permitir eliminar los comerciales de una sucursal cuando ésta se elimine de la base de datos, por lo que el borrado será restringido; sin embargo la modificación de una sucursal debe propagarse a los comerciales que trabajan en ella, por lo que la actualización debe realizarse en cascada.

SUCUSAL (Cod_Suc, Dirección, Cod_Postal, Población,_Teléfono) Objetos en el modelo E/R

Nombre Apellidos Fecha_Contrato DNI

COMERCIAL

MC:BR

Transformación al modelo

COMERCIAL ( DNI, Apellidos, Fecha_Contrato, Cod_Suc)

relacional - Relación: Entidad: COMERCIAL

COMERCIALES

Identificador principal: DNI

Clave primaria: DNI

Atributos Obligatorios: Nombre,

Atributos NOT NULL: Nombre,

Apellidos, Fecha_Contrato

Apellidos, Fecha_Contrato

Transformación de una interrelación 1:N con opciones de Modificación y Borrado

La segunda interrelación que transformaremos al modelo relacional será la denominada

GESTIONAR,

que

relaciona

a

las

entidades

INMUEBLE

COMERCIAL. Recordemos su representación en el diagrama entidad-relación:

153

154

y

Modelo Relacional

Veremos ahora las acciones en caso de modificación y borrado. No se debe eliminar los inmuebles de una determinada sucursal, si dicha sucursal se da de

Cod_Postal Dirección. Num_Baños Cod Inmueble. Num Habitaciones. Importe_ Alquiler

Cod_Suc. Cod_Postal Direccion Población T eléfono

baja en la base de datos, por lo que el borrado será restringido. Ahora bien, si una Sucursal cambiara de código, la actualización se propagaría en cascada a

1 :N (1,n)

(1,1) SUCURSAL

todos sus registros relacionados en la tabla Inmueble.

INMUEBLE

Gestionar

SUCUSAL (Cod_Suc, Dirección, Cod_Postal, Población,_Teléfono)

Objetos en el modelo E/R

Nombre Apellidos Fecha_Contrato DNI

COMERCIAL

BR:MC

INMUEBLE ( Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler, Cod_Suc)

Transformación al modelo relacional - Relación:

Entidad: INMUEBLE

Identificador principal:

Transformación de una interrelación 1:N con opciones de modificación y borrado

INMUEBLES

Clave primaria: Cod_Inmueble

Pasaremos a transformar la interrelación Alquilar que relaciona las entidades

Cod_Inmueble

CLIENTE e INMUEBLE. Su representación en el diagrama e-r es la siguiente:

Atributos NOT NULL: : Atributos Obligatorios: Dirección,

Dirección, Cod_Postal,

Cod_Postal, Num_Habitaciones,

Num_Habitaciones, Num_Baños,

Num_Baños, Importe_Alquiler

Importe_Alquiler

DNI.

Nombre

Apellidos

T eléfono

Cod_Postal Dirección. Num_Baños Cod Inmueble. Num Habitaciones. Fecha_InicFecha_Fin Importe_ Alquiler Importe_Max N:M

(1,n) CLIENTE (real)

(1,n)

Alquilar

INMUEBLE

Igual que en la interrelación anterior (Trabajar), la interrelación Gestionar presenta una cardinalidad 1:N, por lo que aplicaremos la misma regla de transformación para pasar al modelo relacional. Añadiremos un nuevo atributo La tabla que se muestra a continuación presenta la transformación de las

en la relación INMUEBLE que nos dará información sobre la sucursal en la que se

entidades en relaciones:

está gestionando su alquiler.

Objetos en el modelo E/R

DNI.

Nombre

Apellidos

SUCUSAL (Cod_Suc, Dirección, Cod_Postal, Población,_Teléfono)

Teléfo

Transformación al modelo relacional - Relación: CLIENTES

Entidad: CLIENTE

INMUEBLE ( Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler, Cod_Suc)

CLIENTE (real)

Transformación de una interrelación 1:N

155

156

Identificador principal: DNI

Clave primaria: DNI

Atributos Obligatorios: Nombre,

Atributos NOT NULL Nombre,

Apellidos y Teléfono

Apellidos y Teléfono

Modelo Relacional

La interrelación ALQUILAR es del tipo N:M por lo que crearemos una nueva CLIENTE ( DNI, Nombre, Apellidos, Teléfono)

relación donde la clave primaria será la unión de las claves primarias de las

BR:MC

relaciones CLIENTE e INMUEBLE.

ALQUILAR ( DNI, Cod_Inmueble, Fecha_Inicio, Fecha_Fin, Importe_Max ) BR:MC

INMUEBLE ( Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler)

CLIENTE ( DNI, Nombre, Apellidos, Teléfono) Transformación de una interrelación N:M con atributos de la interrelación y opciones de

ALQUILAR ( DNI, Cod_Inmueble )

borrado y actualización

INMUEBLE ( Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler)

La siguiente interrelación que vamos a transformar al modelo relacional es la interrelación Atender que relaciona las entidades CLIENTE POTENCIAL y COMERCIAL. Su representación en el diagrama e-r es la siguiente:

Debemos añadir los atributos propios de la interrelación ALQUILAR: Fecha de inicio, fecha de fin e importe máximo mensual del alquiler.

DNI

CLIENTE ( DNI, Nombre, Apellidos, Teléfono)

Apellidos Precio_Max Nombre Teléfono

Nombre Apellidos Fecha_Contrato N:1

ALQUILAR ( DNI, Cod_Inmueble, Fecha_Inicio, Fecha_Fin, Importe_Max )

CLIENTE POTENCIAL

(1,n)

DNI (1,1)

Atender

COMERCIAL

INMUEBLE ( Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler)

Una vez añadidos estos atributos a la interrelación, podremos conocer la información de la fecha de inicio, la fecha de fin y el importe máximo que pagó cada cliente por el alquiler del inmueble. Objetos en el modelo E/R

DNI.

Analizaremos por último para esta interrelación las opciones de borrado y modificación. Como en nuestra base de datos interesa almacenar la relación

Apellidos Nombre Teléfono Precio_Max

CLIENTE POTENCIAL

existente entre los clientes y los inmuebles, no se debe permitir el borrado en cascada en la relación ALQUILAR, por lo que el borrado debe ser restringido.

La modificación se realizará en cascada, de tal forma que si se modifica el cógido de un cliente o el código de un inmueble, dichas modificaciones se propaguen en sus tuplas correspondientes de la relación ALQUILAR.

157

158

Transformación al modelo relacional - Relación: CLIENTES

Entidad: CLIENTE POTENCIAL

POTENCIALES

Identificador principal: DNI

Clave primaria: DNI

Atributos Obligatorios: Nombre,

Atributos NOT NULL Nombre,

Apellidos, Teléfono, Precio_Max

Apellidos, Teléfono, Precio_Max

Modelo Relacional

Dirección Población Cod_Postal Teléfono Cod_Sucural

CLIENTE POTENCIAL (DNI, Nombre, Apellidos, Teléfono, Precio_Max DNI_Comercial)

SUCURSAL COMERCIAL ( DNI, Apellidos, Fecha_Contrato, Cod_Suc)

1:N

1:1

Igual que en las interrelaciones Gestionar y Trabajar vistas anteriormente, la Depender

interrelación Atender presenta una cardinalidad 1:N. En este caso, añadiremos

N:1

un nuevo atributo en la relación CLIENTE POTENCIAL que nos dará información sobre el COMERCIAL que le atiende en la búsqueda de un alquiler.

Aplicando la regla de transformación para cardinalidad 1:N, añadiremos en la tabla Sucursal un nuevo atributo: Veremos ahora las acciones en caso de modificación y borrado. No se debe permitir eliminar un COMERCIAL de nuestra base de datos mientras existan Clientes Potenciales asociados al comercial, por lo que el borrado será

SUCURSAL (Cod_Suc, Dirección, Cod_Postal, Población, Teléfono, Cod_Suc)

restringido. Sin embargo, la actualización debe realizarse en cascada.

Transformación de una interrelación N:1 con opciones de borrado y actualización BR:MC

CLIENTE POTENCIAL (DNI, Nombre, Apellidos, Teléfono, Precio_Max DNI_Comercial)

COMERCIAL ( DNI, Apellidos, Fecha_Contrato, Cod_Suc)

Por último analizaremos la interrelación Depender, donde la entidad Sucursal se relaciona consigo misma. Recordemos la representación en el diagrama entidad-relación.

159

160

Modelo Relacional

MODELO RELACIONAL FINAL

SUCURSAL ( Cod_Suc, Dirección, Cod_Postal, Población, Teléfono, Cod_Suc) A partir del

diagrama entidad-relación que obtuvimos en nuestro ejemplo

de las Obras y Artistas, crearemos el modelo relacional. Para ello, iremos

BR:MC

analizando cada una de las interrelaciones tal y como hemos hecho hasta ahora.

COMERCIAL (Dni, Apellidos, Fecha_Contrato, Cod_Suc) BR:MC CLIENTE_POTENCIAL (DNI, Nombre, Apellidos, Teléfono, Precio_Max, DNI_Comercial)

DNI

Apellidos Fecha_Nac Nombre Sexo Apodo E-mail

Cod_Estilo Descripcion N:M

INMUEBLE (Cod_Inmueble, Dirección, Cod_Postal, Num_Habitaciones, Num_Baños, Importe_Alquiler, Cod_Suc) (1,n)

ARTISTA

(1,n) Trabajar

BR:MC ALQUILAR (DNI, Cod_Inmueble, Fecha_Inicio, Fecha_Fin, Importe_Max)

ESTILO (1,1)

BR:MC (1,1)

BR:MC

Cod_Técnica

(1,n)

CLIENTE (DNI, Nombre, Apellidos, Teléfono)

Descripcion

N:M

1:N

(1,1) Crear Trabajar

(1,n)

TÉCNICA

(1,n) Valor_Subasta Valor_Tasador Nombre Dimensiones

OBRA

Nombre Dirección Fecha_Exposición N:M

(1,n)

(1,n) Exponer

(1,n) (1,n)

N:1 Emplear

N:1 Pertenecer

161

162

SEDE

Modelo Relacional

Analizaremos en primer lugar la interrelación Trabajar en la que intervienen las entidades ARTISTA y ESTILO.

La interrelación TRABAJAR presenta una cardinalidad del tipo N:M por lo que crearemos una nueva relación donde la clave primaria será la unión de las claves

DNI

primarias de las relaciones ARTISTA y ESTILO.

Cod_Estilo

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Descripcion N:M

ARTISTA

(1,n)

(1,n) Trabajar

ARTISTA ( DNI, Nombre, Apellidos, Sexo, Fecha_Nac, Apodo, E-mail)

ESTILO

TRABAJAR ( DNI, Cod_Estilo)

ESTILO (Cod_Estilo, Descripción)

Objetos en el modelo E/R

DNI

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

Transformación al modelo

Una

relacional - Relación: ARTISTAS

vez

obtenido

este

modelo

relacional,

analizaremos

para

esta

interrelación las opciones de borrado y modificación. Como se desea guardar la

Entidad: ARTISTA

relación existente entre los artistas y sus estilos, no debemos permitir el borrado ARTISTA

Identificador principal: DNI

Clave primaria: DNI

Atributos Obligatorios: Nombre,

Atributos NOT NULL Nombre,

Apellidos, Sexo, Fecha_Nac,

Apellidos, Sexo, Fecha_Nac,

Apodo, E- Mail

Apodo, E- Mail

en cascada sobre la relación TRABAJAR, por lo que el borrado debe realizarse de forma restringida. La modificación, sin embargo, se realizará en cascada.

ARTISTA ( DNI, Nombre, Apellidos, Sexo, Fecha_Nac, Apodo, E-mail) BR:MC

TRABAJAR ( DNI, Cod_Estilo) Cod_Estilo

Objetos en el modelo E/R

Descripcion

Transformación al modelo

BR:MC

relacional- Relación: ESTILOS

ESTILO (Cod_Estilo, Descripción)

Entidad: ESTILOS

ESTILO Identificador principal:

Transformación de una interrelación N:M con opciones de borrado y actualización

Clave Primaria: Cod_Estilo

Cod_Estilo Atributos NOT NULL: Atributos Obligatorios:

Cod_Estilo, Descripción

Veremos a continuación la interrelación Trabajar (2), en la que intervienen

Cod_Estilo, Descripción

las entidades ARTISTA y TÉCNICA.

163

164

Modelo Relacional

Las opciones de modificación y borrado serán la mismas que las vistas en la interrelación TRABAJAR (Artista y Estilo): Borrado restringido y modificación en DNI

Cod_Técnica

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail

cascada.

Descripcion N:M

ARTISTA

(1,n)

(1,n) Trabajar

TÉCNICA

ARTISTA ( DNI, Nombre, Apellidos, Sexo, Fecha_Nac, Apodo, E-mail) BR:MC

TRABAJAR ( DNI, Cod_Técnica) BR:MC

TÉCNICA (Cod_Técnica, Descripción)

Transformación de una interrelación N:M con opciones de borrado y actualización Objetos en el modelo E/R

Transformación al modelo relacional

Entidad: TÉCNICA

Cod_Técnica Descripcion

TÉCNICA

Relación: TÉCNICAS

Identificador principal:

A continuación transformaremos la interrelación Crear:

Clave Primaria: Cod_Técnica

Cod_Técnica

DNI Atributos NOT NULL:

Atributos Obligatorios:

Fecha_Nac Apellidos Nombre Sexo Apodo E-mail 1:N

Cod_Técnica, Descripción

Valor_Subasta Valor_Tasador Nombre Dimensiones

Cod_Técnica, Descripción

ARTISTA

(1,1)

(1,n) Crear

OBRA

La interrelación TRABAJAR(2) presenta, al igual que la interrelación vista anteriormente, una cardinalidad del tipo N:M. Aplicaremos la misma regla de transformación, creando una nueva relación donde la clave primaria será la

Objetos en el modelo

Transformación al modelo

E/R

relacional

unión de las claves primarias de las relaciones ARTISTA y TÉCNICA. Valor_Subasta Valor_Tasador Nombre Dimensiones

ARTISTA ( DNI, Nombre, Apellidos, Sexo, Fecha_Nac, Apodo, E-mail) OBRA

Entidad: OBRA

Identificador principal:

Relación: OBRAS

Clave Primaria: Nombre

Nombre Atributos NOT NULL:

TRABAJAR ( DNI, Cod_Técnica) Atributos Obligatorios:

Dimensiones, ValorSubasta,

Dimensiones,

ValorTasador

ValorSubasta, ValorTasador

TÉCNICA (Cod_Técnica, Descripción)

165

166

Modelo Relacional

Esta vez, la interrelación CREAR, presenta una cardinalidad del tipo 1:N. Para su transformación al modelo relacional, añadiremos un nuevo atributo en la

Cod_Estilo

Valor_Subasta Valor_Tasador Nombre Dimensiones

relación OBRA que nos dará información sobre el ARTISTA que la creó.

Descripcion N:1

OBRA

(1,n)

(1,1) Pertenecer

ESTILO

ARTISTA (DNI, Nombre, Apellidos, Teléfono, Sexo, Fecha_Nacimiento, Apodo, E-mail)

De nuevo nos encontramos con una cardinalidad del tipo 1:N. Para su transformación al modelo relacional, añadiremos un nuevo atributo en la relación

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, DNI)

OBRA que nos dará información sobre el ESTILO al que pertenece. Veremos ahora las acciones en caso de modificación y borrado. No se debe permitir eliminar las obras pertenecientes a un artista, cuando este se borre de la base de datos, por lo que el borrado será restringido. En cambio la

ESTILO (Cod_Estilo, Descripción)

modificación de un artista debe realizar la modificación en cascada de sus obras.

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, Cod_Estilo) ARTISTA (DNI, Nombre, Apellidos, Teléfono, Sexo, Fecha_Nacimiento, Apodo, E-mail) BR:MC

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, DNI)

En cuanto a las opciones de modificación y borrado, no se permitirá eliminar un estilo miestras existan obras que pertenezcan a dicho estilo. En cambio, si

Transformación de una interrelación N:1 con opciones de borrado y actualización

modificamos los datos del estilo se modificarán en cascada todas las obras de dicho estilo.

El siguiente paso será analizar la interrelación PERTENECER, donde intervienen dos entidades ya vistas anteriormente: OBRA y ESTILO.

ESTILO (Cod_Estilo, Descripción) BR:MC

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, Cod_Estilo)

Transformación de una interrelación N:1 con opciones de borrado y actualización

167

168

Modelo Relacional

Una de las últimas interrelaciones de nuestro modelo entidad-relación es EMPLEAR, en la que participan dos entidades ya vistas anteriormente:

Cod_Técnica

Valor_Subasta Valor_Tasador Nombre Dimensiones

Descripcion

Por último analizaremos la interrelación EXPONER, donde intervienen dos

N:1 OBRA

(1,n)

(1,1) Emplear

entidades: OBRA y SEDE. TÉCNICA

Nombre

Valor_Subasta Valor_Tasador Nombre Dimensiones

OBRA

Aplicando la regla correspondiente de transformación para cardinalidades del

Dirección Fecha_Exposición N:M

(1,n)

(1,n) Exponer

SEDE

tipo N:1, añadiremos un nuevo atributo en la relación OBRA que nos dará información sobre la técnica empleada.

TÉCNICA (Cod_Técnica, Descripción) Objetos en el modelo E/R

Transformación al modelo relacional

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, Cod_Estilo, Cod_Técnica)

Entidad: SEDE

Nombre Dirección

En cuanto a las opciones de borrado y actualización, no eliminaremos una SEDE

técnica de nuestra base de datos mientras tenga obras realizadas con dicha técinica. Si una Técnica cambiara de código, la actualización se propagaría en

Relación: SEDES

Identificador principal: Nombre

Clave Primaria: Nombre

Atributos Obligatorios: Dirección

Atributos NOT NULL: Dirección

cascada a sus registros relacionados en la tabla OBRA.

La cardinalidad de la interrelación es del tipo N:M. Aplicaremos la regla de

TÉCNICA (Cod_Técnica, Descripción)

transformación, por lo que crearemos una nueva relación donde la clave primaria BR:MC

será la unión de las claves primarias de las relaciones SEDE y OBRA.

OBRA ( Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, Cod_Estilo, Cod_Técnica)

169

170

Modelo Relacional

MODELO RELACIONAL FINAL SEDE (Nombre, Dirección)

EXPONER ( Nombre_Sede, Nombre_Obra)

ARTISTA ( DNI, Nombre, Apellidos, Sexo, Fecha_Nac, Apodo, E-mail) BC:MC

OBRA (Nombre, Dimensiones, Valor Subasta, Valor Tasador, Cod_Estilo, Cod_Técnica )

TRABAJAR_ESTILO (DNI, Cod_Estilo) Para poder conocer la fecha de exposición de las obras en las distintas

BC:MC

sedes, será necesario añadir un atributo más: Fecha de Exposición.

ESTILO (Cod_Estilo, Descripción)

BC:MC

SEDE (Nombre, Dirección)

BC:MC

TRABAJAR_TECNICA (DNI, Cod_Técnica) BC:MC

EXPONER ( Nombre_Sede, Nombre_Obra, Fecha_Exposición)

TECNICA (Cod_Técnica, Descripción) BC:MC

OBRA (Nombre, Dimensiones, Valor Subasta, Valor Tasador, Cod_Estilo, Cod_Técnica )

Analizaremos a continuación las opciones de Modificiación y Borrado.

OBRA (Nombre, Dimensiones, Valor_Subasta, Valor_Tasador, DNI, Cod_Estilo, Cod_Técnica) Como nos interesa almacenar la información de las obras expuestas en las

BC:MC

distintas sedes, no se permitirá el borrado en cascada sobre la relación EXPONER. El borrado debe ser restringido. Sin embargo, la actualización se

EXPONER(Nombre_Sede, Nombre_Obra, Fecha_Exposicion)

realizará en cascada.

BC:MC SEDE (Nombre, Dirección) SEDE (Nombre, Dirección) BC:MC

EXPONER ( Nombre_Sede, Nombre_Obra, Fecha_Exposición) BC:MC

OBRA (Nombre, Dimensiones, Valor Subasta, Valor Tasador , Cod_Estilo, Cod_Técnica) Transformación de una interrelación N:M con opciones de borrado y actualización

171

172

Modelo Relacional

5. Introducción a las Formas Normales 6. Formas Normales

Dada una relación y un conjunto de dependencias funcionales. Si la relación no tiene un buen nivel de diseño, la normalización propone una descomposición

1FN

de la relación teniendo en cuenta que:

M La descomposición debe ser sin pérdida de información.

Se dice que una relación está en 1FN cuando no existen tuplas repetidas. Todas las relaciones tienen que estar, al menos en 1FN.

N Si es posible, deben mantenerse las dependencias funcionales originales.

Ejemplo: Supongamos la siguiente relación:

Dependencia funcional es la relación existente entre atributos. Las dependencias funcionales se representan con una flecha de la forma AJB y la lectura sería la siguiente, el atributo A determina a B o el atributo B es dependiente del atributo A.

TRABAJADORES (NOMBRE, TITULACIÓN)

TRABAJADORES (NOMBRE, TITULACIÓN)

NOMBRE

TITULACIÓN

NOMBRE

Miguel

Pintor

Miguel

TITULACIÓN

Pintor

Isabel

Industrial

Isabel

Industrial

¿Por qué se normalizan las bases de datos relacionales?

Felipe

Felipe

Felipe

Felipe

Para:

Felipe

Agrónomo

Felipe

Agrónomo

Miguel

Pintor

x Evitar la redundancia de los datos.

x Evitar problemas de actualización de los datos en las tablas.

x Proteger la integridad de los datos.

No cumple la 1FN ya que hay tuplas repetidas

Cumple la 1FN ya que no hay tuplas repetidas

Existen seis niveles de normalización de una relación (1FN, 2FN, 3FN, FNBC,

2FN

4FN y 5FN). Una relación se encuentra en uno u otro grado de normalización si cumple una serie de propiedades (restricciones).

Se dice que una relación se encuentra en 2FN si:

Las tres primeras formas normales la definió Codd y se basan en las

- Se encuentra en 1FN

dependencias funcionales que existen entre los atributos de la relación. Más

- Cada atributo de la relación, que no es clave primaria, tiene dependencia funcional completa respecto del atributo que es clave, siempre que esta clave esté compuesta por más de un atributo. Si la clave primaria está compuesta por un único atributo, ya estaría en 2FN.

tarde y debido a que todavía existían anomalías, redefinió la 3FN y la denominó Forma Normal de Boyce Codd (FNBC). Posteriormente, basándose en las dependencias multivaluadas y en combinación se definieron dos niveles más de normalización, la 4FN y 5FN respectivamente. La 5FN es el grado máximo de

Ejemplo: Supongamos la siguiente relación:

normalización que puede alcanzar una relación.

MATRÍCULA

(

DNI, ASIGNATURA, APELLIDOS, NOMBRE, NOTA, CURSO)

y Clave primaria compuesta por los atributos:

Las relaciones en 1FN tienen más redundancia de datos que los niveles

y Dependencias funcionales:

superiores, y por lo tanto, más anomalías a la hora de actualizar los datos.

DNI

+

DNI

determinan el

ASIGNATURA

ASIGNATURA

173

174

determinan una NOMBRE

determinan

y

NOTA

APELLIDOS

CURSO

DNI, ASIGNATURA

(DNI,ASIGNATURAJNOTA)

(DNIJ NOMBRE y DNIJ APELLIDOS)

(ASIGNATURA J CURSO)

Modelo Relacional

La relación no se encuentra en 2FN ya que existe una dependencia funcional no completa entre atributos de la relación que no forman parte de la clave primaria y la clave primaria: DNIJ NOMBRE

DNIJ APELLIDOS

ASIGNATURA

3FN Se dice que una relación está en 3FN si satisface la 2FN y cada atributo no

J CURSO

primario no depende de forma transitiva de la clave primaria, es decir, dependerán directamente de la clave primaria.

El DNI determina, por sí sólo al NOMBRE y al APELLIDOS, en este caso el campo ASIGNATURA no es imprescindible para la obtención de los atributos NOMBRE y APELLIDOS.

Ejemplo: Supongamos la siguiente relación: MATRÍCULA

Solución:

(ASIGNATURA, AULA, LUGAR)

y Clave primaria:

Para eliminar estos inconvenientes es necesario realizar un proceso de descomposición de la relación MATRÍCULA en dos relaciones que satisfagan la 2FN. La estructura de estas relaciones serían las siguientes:

ASIGNATURA

y Dependencias funcionales: ASIGNATURA

J AULA

AULA

J LUGAR

ASIGNATURA

J LUGAR

Esta relación no está en 2FN ya que en la dependencia funcional IMPARTE

(ASIGNATURA,

MATRÍCULA

(DNI,

ł La relación

CURSO)

no se cumple que el atributo

ASIGNATURA, NOMBRE, APELLIDOS, NOTA)

primaria

se encuentra en 2FN. La clave de esta relación es ASIGNATURA y el único atributo que no es clave primaria tiene una dependencia completa de la clave primaria (ASIGNATURAJ CURSO).

AULA

J

LUGAR

tenga una dependencia completa de la clave

y además existe una dependencia transitiva entre la clave LUGAR.

Solución:

ł La relación matrícula no se encuentra en 2FN ya que aún se

Descomposición de la relación

mantiene las dependencias funcionales no completas: DNIJ NOMBRE

ASIGNATURA

primaria y el atributo

IMPARTE

LUGAR

MATRÍCULA (ASIGNATURA, AULA, LUGAR)

en:

UBICACIONES (AULA, LUGAR)

DNIJ APELLIDOS

CLASES (ASIGNATURA, AULA)

Solución: Descomponer la relación MATRÍCULA (DNI, en: ALUMNOS

(DNI,

MATRICULACIÓN

Forma Normal de Boyce Codd (FNBC)

ASIGNATURA, NOMBRE, APELLIDOS, NOTA)

El antecedente de cada dependencia funcional debe ser clave.

NOMBRE, APELLIDOS)

(DNI,

Ejemplo: Supongamos la siguiente relación:

ASIGNATURA, NOTA) ALUMNOS

De la relación de partida hemos obtenido la siguiente descomposición: MATRÍCULA

(

(ASIGNATURA,

MATRÍCULA ALUMNOS

(DNI,

(DNI,

NOMBRE_APELLIDOS, DIRECCIÓN, LOCALIDAD, PROVINCIA) DNI

y Dependencias funcionales:

DNI, ASIGNATURA, APELLIDOS, NOMBRE, NOTA, CURSO)

IMPARTE

(DNI,

y Clave primaria:

Conclusión:

DNI

J NOMBRE_APELLIDOS

DNIJLOCALIDAD

LOCALIDADJPROVINCIA

CURSO)

ASIGNATURA, NOTA)

NOMBRE, APELLIDOS)

DNI

JDIRECCIÓN

DNIJPROVINCIA

Todas las dependencias funcionales cumplen que el antecedente es clave excepto la dependencia funcional: LOCALIDADJPROVINCIA Solución: Descomposición de la relación LOCALIDAD, PROVINCIA) en: 175

176

ALUMNOS

(DNI,

NOMBRE_APELLIDOS, DIRECCIÓN,

Modelo Relacional

ALUMNOS

(DNI,

LOCALIDADES

NOMBRE_APELLIDOS, DIRECCIÓN, LOCALIDAD)

PERSONAL

(

PROFESOR, MATERIA, CENTRO)

(LOCALIDAD, PROVINCIA)

La 4FN y 5FN tienen que ver con atributos multivaluados

4FN

PROFESOR

MATERIA

CENTRO

Juan Juanas

Informática

C1

Juan Juanas

Informática

C2

Susana Luís

Biología

C1

Susana Luís

Geología

C2

Susana Luís

Biología

C1

Es posible que existan anomalías de inserción, borrado y modificación en una relación provocados por motivos distintos a las dependencias funcionales que hemos

visto

hasta

ahora.

El

principal

motivo

son

las

Un profesor puede dar clase de distintas materias en centros distintos.

dependencias

multivaluadas. Este tipo de dependencias surgen al normalizar estructuras

El diseño de esta tabla sería correcto si no se realizaran ningún tipo de

que no están en 1FN presentando valores repetidos.

restricción más.

Ejemplo: Supongamos la siguiente relación:

Supongamos que en el enunciado para crear la base de datos nos piden que tengamos en cuenta la siguiente consideración: PERSONAL

(

DNI, NOMBRE, APELLIDOS, PROFESIÓN, DIRECCIÓN, TLF)

Cuando un profesor está autorizado para trabajar en un centro DNI

NOMBRE

APELLIDOS

PROFESIÓN

DIRECCIÓN

TLF

000000

Juan

Pérez

Juez, Pintor

Margarita,1

666666666

111111

Ana

Juanas

Fiscal, Pianista

Geranio,2

555555555

222222

Raquel

Martín

Profesora

Jacinto, 44

777777777

Observamos que existe una redundancia de datos en la tabla de

para impartir Informática, el profesor puede trabajar en dicho centro.

PROFESORES-MATERIAS

PERSONAL

(

PROFESOR, MATERIA)

PROFESOR

MATERIA

Juan Juanas

Informática

Juan Juanas

Informática

Susana Luís

Biología

Separar en dos tablas, en una de ellas quedarían reflejadas las

Susana Luís

Geología

profesiones del personal y en la otra sus datos personales.

Susana Luís

Biología

ya que por cada una de las titulaciones de cada persona se repiten todos sus datos personales. Solución:

PERSONAL

(

PROFESORES-CENTROS

PERSONAL

(

(

MATERIAS-CENTROS

DNI, PROFESIÓN)

(

PROFESOR

MATERIA

C1

Juan Juanas

C2

Susana Luís

C1

Susana Luís

C2

Susana Luís

C1

MATERIA, CENTRO)

PROFESOR

MATERIA

Informática

C1

DNI, NOMBRE, APELLIDOS, DIRECCIÓN, TLF)

5FN

Informática

C2

Biología

C1

Para que una relación esté en 5FN, deberá estar en 4FN y la relación no podrá

Geología

C2

ser descompuesta en otras, además se debe cumplir que toda dependencia

Biología

C1

multivaluada es consecuencia de la clave primaria. Ejemplo: Supongamos la siguiente relación:

177

178

PROFESOR, CENTRO)

Juan Juanas

DNI, NOMBRE, APELLIDOS, PROFESIÓN, DIRECCIÓN, TLF)

PROFESIONES

(

Modelo Relacional

Relación ADMINISTRADORES (DNI, NOMBRE, E-MAIL, AULA) (1) Dependencias funcionales obtenidas: DNI Æ nombre

Veamos a continuación si el modelo relacional obtenido en el Caso Práctico

DNI Æ e-mail

DNI Æ aula

(2) comprobar el estado de Normalización de la relación

“Mentor” se encuentra en forma normal de Boice-Codd.

1 FN El modelo relacional obtenido del caso práctico “Mentor” fue el siguiente: Se dice que una relación está en 1FN cuando no existen tuplas o registros repetidos.

ADMINISTRADORES (

La relación está en primera forma normal ya que al tener una clave primaria definida, el sistema no permitirá registros repetidos.

DNI , Nombre, e-mail, Aula)

2FN

BR/MC AULAS ( Código_Aula , Descripción, Dirección) BR/MC

Se dice que una relación se encuentra en 2FN si:

ALUMNOS ( DNI , Nombre, Dirección, Teléfono*, Nacionalidad, e-mail, aula)

- Se encuentra en 1FN

BC:MC

- Cada atributo de la relación, que no es clave primaria, tiene dependencia

MATRICULAR ( Alumno , Curso , F_Comienzo, F_Finalización)

funcional completa respecto del atributo que es clave, siempre que esta clave esté compuesta por más de un atributo. Si la clave primaria está

BR:MC

compuesta por un único atributo, ya estaría en 2FN.

CURSOS ( Nombre , WWW , Libro*) BC/MC

La relación está en 2FN ya que la clave primaria está compuesta por un único atributo.

ASOCIAR ( Tutor, Curso , Es_coordinador) BR/MC TUTORES ( DNI , Nombre,_completo,

3FN e-mail)

Se dice que una relación está en 3FN si satisface la 2FN y cada atributo no primario no depende de forma transitiva de la clave primaria, es decir, dependerán directamente de la clave primaria. Pasamos a estudiar cada una de las relaciones

La relación está en 3FN ya que todos los atributos dependen directamente de la clave primaria, no habiendo dependencias funcionales donde el antecedente no es clave primaria.

FNBC El antecedente de cada dependencia funcional debe ser clave primaria. Observamos que en todas las dependencias funcionales el antecedente es clave primaria luego la relación está en FNBC.

179

180

Modelo Relacional

Relación AULAS (CÓDIGO_AULA, DESCRIPCION, DIRECCIÓN ) (1) Dependencias funcionales obtenidas: CÓDIGO_AULAÆDESCRIPCIÓN

Relación MATRICULA (ALUMNO, CURSO, F_COMIENZO, F_FINALIZACIÓN )

CÓDIGO_AULA ÆDIRECCIÓN

(1) Dependencias funcionales obtenidas: ALUMNO, CURSO Æ F_COMIENZO

(2) comprobar el estado de Normalización de la relación 1 FN

La relación está en primera forma normal ya que al tener una

(2) comprobar el estado de Normalización de la relación

clave primaria definida, el sistema no permitirá registros repetidos. 2FN

3FN

FNBC

*

En

ALUMNO,CURSO->F_FINALIZACIÓN

1 FN

La relación está en primera forma normal ya que al tener una

La relación está en 2FN ya que la clave primaria está

clave primaria definida, el sistema no permitirá registros

compuesta por un único atributo.

repetidos. 2FN

La relación está en 3FN ya que todos los atributos dependen

Comprobamos que los atributos F_Comienzo y F_Finalización

directamente de la clave primaria, no habiendo dependencias

dependen de la totalidad de la clave, es decir, dependen tanto

funcionales entre atributos que no son clave primaria *

del alumno como del curso.* 3FN

Observamos que en todas las dependencias funcionales el

directamente de la clave primaria, no habiendo dependencias

antecedente es clave primaria luego la relación está en FNBC. este

caso

no

existe

ninguna

dependencia

dependencia

DESCRIPCION Æ DIRECCIÓN, por lo que la relación está en 3ª FN

La relación está en 3FN ya que todos los atributos dependen funcionales entre atributos que no son clave primaria**

entre FNBC

Observamos que en todas las dependencias funcionales el antecedente es clave primaria luego la relación está en FNBC.

Relación ALUMNOS (DNI, NOMBRE, DIRECCIÓN, TELÉFONO, NACIONALIDAD, E-MAIL, AULA ) * Recordemos que un alumno de mentor puede realizar diferentes cursos y

(1) Dependencias funcionales obtenidas: DNI ÆNOMBRE DNI ÆE-MAIL

DNI ÆDIRECCIÓN DNI ÆAULA

DNI ÆTELÉFONO

comenzará y finalizará cada uno de ellos en una determinada fecha, por lo

DNI ÆNACIONALIDAD

que dicha relación se encuentra en 2FN.

DNI ÆNOMBRE

** No exite ninguna dependencia entre los atributos F_Comienzo y F_Finalización, ya que la fecha de comienzo no determina la fecha de

(2) comprobar el estado de Normalización de la relación

finalización, ya que un alumno puede comenzar en una fecha determinada. 1 FN

La relación está en primera forma normal ya que al tener una

No podremos saber la fecha de finalización, ya que es en los cursos mentor

clave primaria definida, el sistema no permitirá registros

cada alumno lleva su propio ritmo de trabajo.

repetidos. 2FN

La relación está en 2FN ya que la clave primaria está Relación CURSOS ( NOMBRE, WWW, LIBRO )

compuesta por un único atributo. 3FN

(1) Dependencias funcionales obtenidas:

La relación está en 3FN ya que todos los atributos dependen

CURSOS ÆNOMBRE

directamente de la clave primaria, no habiendo dependencias funcionales entre atributos que no son clave primaria

CURSOSÆWWW

CURSOSÆLIBRO

(2) comprobar el estado de Normalización de la relación Observamos que en todas las dependencias funcionales el FNBC

antecedente es clave primaria luego la relación está en FNBC. 181

182

Modelo Relacional

1 FN

La relación está en primera forma normal ya que al tener una clave primaria definida, el sistema no permitirá registros

Relación TUTOR ( DNI, NOMBRE_COMPLETO, E-MAIL )

repetidos. (1) Dependencias funcionales obtenidas: 2FN

DNIÆNOMBRE_COMPLETO

La relación está en 2FN ya que la clave primaria está compuesta por un único atributo.

3FN

(2) comprobar el estado de Normalización de la relación

La relación está en 3FN ya que todos los atributos dependen directamente

de

la

clave

primaria,

no

habiendo

1 FN

dependencias funcionales entre atributos que no son clave

La relación está en primera forma normal ya que al tener una clave primaria definida, el sistema no permitirá registros

primaria *

repetidos.

Observamos que en todas las dependencias funcionales el FNBC

DNIÆE-MAIL

2FN

antecedente es clave primaria luego la relación está en

La relación está en 2FN ya que la clave primaria está compuesta por un único atributo.

FNBC. 3FN

La relación está en 3FN ya que todos los atributos dependen

* No existe ninguna dependencia entre funcional entre los atributos www y

directamente

libro

dependencias funcionales entre atributos que no son clave

de

la

clave

primaria,

no

habiendo

primaria * Observamos que en todas las dependencias funcionales el

Relación ASOCIAR ( TUTOR, CURSO, ES_COORDINADOR ) FNBC

(1) Dependencias funcionales obtenidas:

antecedente es clave primaria luego la relación está en FNBC.

TUTOR, CURSO ÆES_COORDINADOR

* En principio pudiera parecer que existe una dependencia funcional entre

(2) comprobar el estado de Normalización de la relación

atributos que no son clave primaria, de la forma: NOBRE_COMPLETOÆEMAIL.

1 FN

La relación está en primera forma normal ya que al tener una clave primaria definida, el sistema no permitirá registros

Sin embargo, esta dependencia no es real, ya que el nombre completo podría

repetidos.

repetirse en nuestra base de datos, en el caso de que existan dos personas con el mismo nombre y cada una de ellas tendrá un e-mail distinto:

2FN

Igualmente, comprobamos si el atributo Es_coordinador depende de la totalidad de la clave primaria, es decir,

DNI

NOMBRE_COMPLETO

E-MAIL

depende de Tutor y de Curso.* 3FN

La relación está en 3FN ya que en la relación hay un único

54258652

LUIS PEREZ PEREZ

[email protected]

42568745

MARIA FERNANDEZ RUIZ

[email protected]

41203657

CARMEN PRIETO PEREZ

[email protected]

41875964

LUIS PEREZ PEREZ

[email protected]

atributo y por lo tanto no podrá haber dependencias que incumplan la 3ª FN. Observamos que en todas las dependencias funcionales el FNBC

antecedente es clave primaria luego la relación está en FNBC.

* En este caso, la dependencia es correcta, ya que el hecho de ser coordinador vendrá determinado por el tutor y el curso. Es decir, un

Por lo tanto, podemos ver que nuestra relación se encuentra en 3ª FN

determinado tutor coordinará uno o más cursos. 183

184

Modelo Relacional

.

El modelo relacional para la gestión de una base de datos es el modelo más utilizado en la actualidad para modelar problemas reales y administrar datos dinámicamente. Su idea fundamental es el uso de «relaciones», cuya representación gráfica se realiza en forma de tabla. En las relaciones se pueden distinguir varios tipos de elementos: su nombre, los atributos que contiene y que representan las columnas de la tabla, y las tuplas o filas de la tabla. Además también podemos incluir los dominios que son aquellos conjuntos de donde los atributos toman los valores.

Reglas básicas para la transformación del modelo entidad/relación al modelo relacional:

- La primera de las reglas de transformación nos dice que toda entidad se transforma en una relación o tabla, y los atributos o características asociadas a ella pasan a ser atributos de la relación.

- La segunda regla de transformación nos indica que las interrelaciones cuyo tipo de correspondencia es N:M se transforman en una nueva relación cuyo nombre se corresponde con el nombre de la interrelación y donde la clave primaria se compone de los atributos identificadores de las dos entidades que relaciona.

- La tercera y última regla nos indica que la transformación de interrelaciones cuyo tipo de correspondencia es 1:N se traduce en una propagación de clave o en una nueva relación si la interrelación posee atributos

185

MÓDULO C

UNIDADES DIDÁCTICAS:

1. Algebra Relacional 2. SQL – Lenguaje de Consulta Estructurado

MÓDULO C OBJETIVOS

En esta unidad aprenderás:

Álgebra Relacional

x x

A conocer el álgebra relacional y sus operadores algebraicos y relacionales. A realizar las siguientes operaciones:

- Unión, intersección, diferencia y producto cartesiano (operadores algebraicos)

UNIDAD DIDÁCTICA 1

Índice de la unidad:

1. Qué es el álgebra relacional 2. Para qué sirve el álgebra relacional - Operadores algebraicos o boléanos - Operadores relacionales

- Selección, proyección, concatenación y división (operadores relacionales)

TÍTULO DE MÓDULO O BLOQUE

Algebra Relacional

1. Algebra Relacional

586

401

586

690

586

690 333

1.1 ¿Qué es álgebra relacional?

401

Se llama álgebra relacional a un conjunto de operaciones que pueden ser aplicadas sobre tablas relacionales y definen un pequeño lenguaje de

- Intersección (ŀ)

manipulación de datos que permite a los usuarios llevar a cabo tareas de

La relación resultante está compuesta por las tuplas que

consulta o manipulación de los datos.

aparecen en las dos relaciones de origen.

1.2 ¿Para qué sirve el álgebra relacional? Ejemplo: Dados dos esquemas de relación R (A B C) y S (E F H), obtener la intersección de R y S.

Para crear una relación o tabla a partir de una o varias relaciones utilizando para ello operadores relacionales. La nueva relación o tabla de salida contendrá solamente la información que hemos demandado a través de

R ( A B C)

operadores del álgebra relacional. Los operadores se dividen en dos grupos: - Operadores algebraicos o boléanos - Operadores relacionales

R ( E F H)

R

ŀS

179

333

179

345

179

586

586

401

690

586

- Diferencia (-) Los que están en la primera relación y no estén en la segunda

1.2.1 Operadores algebraicos o booleanos Para aplicar las operaciones de Unión, Intersección y diferencia, los

Ejemplo: Dados dos esquemas de relación R (A B C) y S

esquemas de la relación deben ser compatibles, es decir, deben tener

(E F H), obtener la diferencia de R y S.

el mismo número de atributos y los dominios de los atributos de las relaciones deben coincidir uno a uno.

R ( A B C)

Para el producto cartesiano los esquemas no tienen por qué ser compatibles - Unión (U) La relación resultante está compuesta por cada una de las tuplas de las relaciones de origen. En la relación resultante no

R ( E F H)

179

333

345

179

586

401

690

586

R

-S 345 690

aparecen tuplas repetidas. - Producto cartesiano (x) La relación resultante se formará por el producto de cada una

Ejemplo: Dados dos esquemas de relación R (A B C) y S

de las tuplas de la primera relación por todas las tuplas de la

(E F H), obtener la unión de R y S.

segunda relación. R ( A B C)

R ( E F H)

Si coincide algún atributo en ambas relaciones, se pondrá,

RUS

179

333

179

345

179

345

delante del atributo, el nombre de la relación correspondiente.

191

192

TÍTULO DE MÓDULO O BLOQUE

Algebra Relacional

Ejemplo: Dados dos esquemas de relación R (A B C) y S

756984B

Elena

4

(E F H), obtener el producto cartesiano de R y S.

R ( A B C)

R ( E F H)

179

333

345

179

586

401

R

x S (A B C E F H)

Operadores unarios

179333 179179

- Selección (ı)

179401

586

Obtiene tuplas de una relación que cumplan una determinada

179586

condición.

345333

Ejemplo: Obtener los alumnos matriculados en el curso

345179

1.

ı curso=1(ALUMNOS)

345401 345586 586333

D resultado

687698 P

Ana Huete

1

La condición podría ser múltiple. La selección se podría hacer

586179

sobre una tabla o sobre otra expresión.

586401 586586

- Proyección (š) Utilizado

Si coincide el nombre de un atributo en ambas relaciones, se pondrá,

delante

del

atributo,

el

nombre

de

la

para

obtener

columnas

de

una

relación

obtenido.

correspondiente. Ejemplo: Obtener los nombre de la tabla alumnos.

š nombre=1(ALUMNOS) D

Ejemplo: Dados dos esquemas de relación R (A B C) y S (A F C), obtener el producto cartesiano de R X S (R.A B R.C S.A F S.C)

resultado

Juan M. Kimla Antonio Pérez Ana Huete

1.2.2 Operadores Relacionales

Elena

Los operadores relacionales pueden ser unarios o binarios. Es unario cuando se aplica sobre una relación o tabla y binario si se aplica sobre dos. Los ejemplos se realizarán sobre la siguiente tabla: ALUMNOS (DNI, NOMBRE, CURSO) DNI

NOMBRE

CURSO

120546M

Juan M. Kimla

3

478965J

Antonio Pérez

2

687698P

Ana Huete

1

relación.

Automáticamente se eliminan las repeticiones del resultado

193

194

TÍTULO DE MÓDULO O BLOQUE

Algebra Relacional

Operadores binarios Ejemplo: Dados los siguientes esquemas de relación Obtener los pilotos capaces de manejar todos los aviones

- Concatenación (ZY) (Natural Join)

disponibles.

La concatenación equivale a un producto cartesiano más una selección Se queda con aquellas tuplas que en los atributos comunes a

PILOTOS (PILOTO, AVIÓN)

ambas tablas toman el mismo valor. En la relación de salida los

PILOTO

atributos comunes no aparecen duplicados. Cuando no hay atributos comunes en las

tablas donde se

Una combinación de dos relaciones es equivalente a: R ZYF S = ıF (R × S)

- División (/)

PILOTO

ZGT-090

Paula Parla

ZGT-090

ZGT-099

Gonzalo Ávila

ZGT-099

ZGT-190

Paula Parla

ZGT-190

Juan Toledo

ZGT-099

Paula Parla

ZGT-099

Paula Parla

ZGT-090

Gonzalo Ávila

ZGT-099

Paula Parla

ZGT-190

Juan Toledo

ZGT-099

Paula Parla

ZGT-099

PILOTOS – AVIONES(AVIÓN)

AVIÓN

ZGT-090 ZGT-099 ZGT-190

realiza la concatenación equivaldría a un producto cartesiano.

PILOTOS (PILOTO, AVIÓN)

AVIONES(AVIÓN)

AVIÓN

AVIONES

D

PILOTOS

Paula Parla

Para poder realizar la división, debe darse que los datos de avión tienen que estar incluidos como atributos en la relación de pilotos.

Suponiendo que tenemos los esquemas de relación R y S y deseamos realizar la operación R / S. La cabecera de la relación siempre será R-S

195

196

Algebra Relacional

Se llama álgebra relacional a un conjunto de operaciones que pueden ser aplicadas sobre tablas relacionales y definen un pequeño lenguaje de manipulación de datos que permite a los usuarios llevar a cabo tareas de consulta o manipulación de los datos.

Los operadores se dividen en dos grupos: - Operadores algebraicos o boléanos - Unión (U) - Diferencia (-) - Intersección - Producto cartesiano (x)

- Operadores relacionales - Selección (ı) - Proyección (š) - Concatenación (ZY) (Natural Join) - División (/)

197

MÓDULO C OBJETIVOS

En esta unidad aprenderás:

SQL – Lenguaje de Consulta Estructurado

UNIDAD DIDÁCTICA 2

Índice de la unidad:

1. Operaciones sobre las bases de datos - Operaciones de recuperación de datos - Operaciones de actualización de datos 2. Caso Práctico – “Recetas” 3. Repaso de Consultas SQL

x x

La sintaxis de las sentencias de inserción, borrado y actualización de los datos La sintaxis de las consultas en SQL que nos permitirán acceder a los datos de una o más tablas (join).

SQL – Lenguaje de Consulta Estructurado

Las operaciones que se pueden hacer sobre la base de datos son las

1. Operaciones sobre la Base de Datos

siguientes:

La parte dinámica del Modelo Relacional, al igual que la dinámica de cualquier modelo de datos, define las operaciones que se pueden hacer con la base de datos. Estas operaciones pueden ser de varios tipos: - Operaciones de recuperación de datos de la base de datos (consultas).

- Altas

INSERT INTO (Figura 2.50.)

- Operaciones de actualización de datos de la base de datos. Estas

- Bajas

DELETE FROM (Figura 2.51.)

operaciones son:

- Modificaciones

1. Inserción de tuplas en la base de datos.

UPDATE (Figura 2.52.)

- Consultas

SELECT

2. Modificación de tuplas (de algunos de sus valores) 3. Borrado de tuplas. Estas

operaciones

se

expresan

mediante

lenguajes

de

manipulación

relacionales. Los lenguajes de manipulación pueden dividirse en lenguajes navegacionales, en los que se debe indicar el camino para llegar al dato, y lenguajes de especificación, en los cuales se recupera la información mediante la

INSERT INTO PELÍCULA VALUES (1, ‘Entrevista con un Vampiro’, 1/5/1997)

imposición de condiciones y no indicando el camino. El lenguaje de manipulación de datos del SQL (LMD) es un lenguaje de especificación, que consiste en un conjunto de sentencias que nos permiten manipular la base de datos.

PELÍCULA Cod_película

En este apartado vamos, a través de un ejemplo, a introducir las sentencias

1

básicas del SQL para la manipulación de una base de datos.

Título Entrevista con un Vampiro

Fecha_estreno 1/5/1997

Supongamos una base de datos de un videoclub, en la que entre otros objetos se almacenan las películas y los actores que intervienen en ellas (Figura 2.49.).

Figura 2.50: Ejemplo de inserción de tuplas

ACTOR(Cod_actor, Nombre, Nacionalidad) PARTICIPA (Cod_película, Cod_actor, Fecha_inicio, Fecha_fin) PELÍCULA Cod_película, Título, Fecha_estreno)

Figura 2.49: Subesquema de la base de datos de un videoclub 201

202

SQL – Lenguaje de Consulta Estructurado

Mención especial debe hacerse a las consultas. El formato general de las consultas es:

Supongamos que tenemos la tabla PELÍCULA con las siguientes filas:

SELECT-FROM-WHERE, donde la cláusula WHERE es opcional, dependerá de si imponemos alguna condición sobre las tuplas a seleccionar o bien de si

PELÍCULA Cod_película

Título

1

Entrevista con un Vampiro

1/5/1997

2

Abre los Ojos

7/3/1998

necesitamos unir varias tablas para obtener un resultado. Las consultas las

Fecha_estreno

podemos dividir en consultas sobre una tabla y consultas sobre varias tablas. Supongamos que tenemos la ejemplar del esquema relacional del videoclub que aparece en la Figura 2.53:

DELETE FROM PELÍCULA VALUES (1, ‘Entrevista con un Vampiro’, 1/5/1997)

Cod_película 2

PARTICIPA

ACTOR

PELÍCULA Título Abre los Ojos

Fecha_estreno 7/3/1998

Figura 2.51 : Ejemplo de borrado de tuplas

Cod_actor Cod_película Fecha_inicio

Fecha_fin

Cod_actor

Nombre

Nacionalidad

1

TomCruise

Norteamericana

1

1

1/1/1996

1/3/1996

2

BradPitt

Norteamericana

2

1

4/3/1996

30/12/1996

3

PenélopeCruz

Española

3

2

1/5/1997

31/10/1997

4

JavierBardem

Española

4

2

1/5/1997

31/12/1997

PELÍCULA Cod_película

Supongamos que queremos modificar la fecha de la película ‘Abre los Ojos’:

Título

Fecha_estreno

1

EntrevistaconunVampiro

1/5/1997

2

AbrelosOjos

7/3/1998

UPDATE PELÍCULA SET Fecha_estreno=25/3/1998 WHERE Título =‘Abre los Ojos’

Figura 2.53: Ejemplar del videoclub

PELÍCULA Cod_película

Título

Fecha_estreno

1

Entrevista con un Vampiro

1/5/1997

2

Abre los Ojos

25/3/1998

- Consultas sobre una sola tabla: En este caso la condición WHERE únicamente se utiliza en el caso de que queramos restringir el conjunto de filas que se recupere. Como se puede apreciar en la Figura 2.54. la recuperación de las

Figura 2.52.: Ejemplo de modificación de tuplas

puede hacerse por el total de las columnas (atributos) de la tabla 203

204

SQL – Lenguaje de Consulta Estructurado

implicada en la consulta, o bien seleccionando aquellos atributos que queramos obtener en la consulta.

Selección de los actores y las películas en las que intervienen: SELECT Nombre

•Selección de todos los atributos de una tabla:

•Selección de ciertos los atributos de una tabla:

SELECT * FROM PELÍCULA

SELECT Título, Fecha_estreno FROM PELÍCULA

Cod _película

Título

Fecha_estreno

Título

FROM PELICULA, ACTOR, PARTICIPA WHERE PELICULA.Cod_pelicula = PARTICIPA.Cod_pelicula AND ACTOR.Cod_actor = PARTICIPA.Cod_actor

Fecha_estreno

1

Entrevista con un Vampiro

1/5/1997

Entrevista con un Vampiro

1/5/1997

2

Abre los Ojos

7/3/1998

Abre los Ojos

7/3/1998

Nombre

Titulo

Tom Cruise

Entrevista con un Vampiro

Brad Pitt

Entrevista con un Vampiro

Penélope Cruz

Abre los Ojos

Javier Bardem

Abre los Ojos

•Selección de ciertos los atributos de una tabla que cumplen una determinada condición: SELECT Nombre FROM ACTOR WHERE Nacionalidad = ‘Española’

Nombre Penélope Cruz Javier Bardem

Figura 2.54: Consultas sobre una única tabla

Selección de los actores que intervienen en la película “Entrevista con un Vampiro”:

- Consultas sobre varias tablas:

SELECT Nombre

En este caso la condición WHERE no es opcional. Se debe utilizar para

FROM PELICULA, ACTOR, PARTICIPA

unir las tablas1 que intervienen en la consulta. Esta unión se realiza por

WHERE PELICULA.Cod_pelicula = PARTICIPA.Cod_pelicula

la clave ajena de una tabla y la clave primaria de la tabla a la que referencia, como se muestra en la figura 2.55.

AND ACTOR.Cod_actor = PARTICIPA.Cod_actor AND Titulo = ‘Entrevista con un Vampiro’

1

Esta unión es lo que se denomina JOIN. 205

206

SQL – Lenguaje de Consulta Estructurado

Nombre

Tom Cruise

Brad Pitt

Para fijar los conocimientos del modelo relacional asimilados hasta el momento, vamos a diseñar una base de datos completa y a hacer ciertas

Penélope Cruz

consultas sobre ella. “Antonio es uno de tantos trabajadores que llega a casa cansado y

Javier Bardem

estresado. Desde hace algún tiempo alivia su estrés elaborando recetas de cocina que luego prueban sus familiares y amigos. Para ello se apoya en los múltiples manuales de cocina que ha ido Figura 2.55.: Ejemplo de consultas en las que intervienen varias tablas

coleccionando e incluyendo en su biblioteca desde que se inició en el arte culinario, y luego confecciona sus propias recetas. Tanto ha sido su interés que en la actualidad posee un gran número de recetas

Con lo explicado hasta ahora de la parte dinámica del Modelo Relacional

nuevas a modo de apuntes, imposible de organizar de una manera

hemos pretendido dar una visión general de las operaciones que se pueden

efectiva.

realizar sobre una base de datos, no obstante existen otras muchas funciones que se pueden aplicar sobre los atributos de una o varias tablas para recuperar

Los amigos de Antonio, en agradecimiento a tantas veladas de buena

información y que pueden ser consultadas en la bibliografía que aparece en el

comida, vino y compañía han decidido agradarle con una base de

curso.

datos que le gestione su maravilloso gran hobbie. Para ello deben controlar todas las recetas que posee, teniendo en cuenta que: Cada receta proviene de una idea original de un libro de cocina de la biblioteca de Antonio, y se desea almacenar su origen. Cada receta tiene un tipo (Sopas, Verduras, Carnes,...) e incorpora unos ingredientes de los que se desea saber su nombre y cantidad. Además cada receta contiene una breve explicación de cómo mezclar los ingredientes y obtener el producto final y el título de la receta original de la que proviene. Es bien sabido por los amigos de Antonio que de cada receta que él prueba, incorporando ciertos cambios, consigue nuevas recetas, por lo que sería interesante almacenar si cada receta es idea surgida de una receta original, o si por el contrario, proviene de una receta elaborada alguna vez ya por Antonio.

207

208

SQL – Lenguaje de Consulta Estructurado

Lo que los amigos de Antonio quieren es que cuando él quiera pueda

2. SOLUCIÓN AL CASO PRÁCTICO

consultar las recetas por tipo y por ingrediente además de poder En primer lugar debemos detectar cuáles son las relaciones base que

localizar el libro que le dio la idea de cada una de sus recetas.

aparecen en el enunciado y qué atributos contienen (Figura 2. 56.). Estos son:

También sería interesante saber qué receta proviene de alguna otra y cual no.

LIBRO. Esta relación debe contener al menos el Título de la obra y el autor. Como clave primaria podemos suponer el ISBN, que como todos sabemos es único por cada libro editado. - RECETA. De cada receta deberemos incluir el tipo , la descripción, el título original (título de la receta en la que se basa) y la fecha de creación. Además supondremos el atributo “Cod_receta” como clave primaria. - INGREDIENTE. En principio, de los ingredientes se desea almacenar el nombre y la cantidad que de él se ha empleado en cada receta, pero como un mismo ingrediente puede ser usado en distintas cantidades para varias recetas, de momento solo introduciremos el nombre como atributo de la relación INGREDIENTE y veremos posteriormente como reflejar la cantidad. Al igual que ocurriía en el caso de las recetas, consideraremos el atributo “Cod_ingrediente” como clave primaria.

LIBRO (ISBN, Título, Autor)

RECETA (Cod_receta, Descripción, Fecha_creación,Título_original )

INGREDIENTE (Cod_ingrediente, Nombre)

Figura 2. 56: Relaciones elementales de las base de datos de Antonio Una vez encontradas las relaciones base hay que analizar como se intrrelacionan en función de los supuestos realizados en el enunciado: De cada receta se desea saber de donde (de qué libro se ha sacado). Por lo tanto deberemos introducir en la relación RECETA el ISBN del libro.

209

210

SQL – Lenguaje de Consulta Estructurado

Parece claro que no pueda ser de otra manera pues si pensásemos en

“variación”, puede hacerse en cascada pues no va a afectar a la receta padre

introducir el código de receta en LIBRO, lo que significaría es que por cada libro

(Figura 2. 58).

Antonio sólo sacó una receta (recuérdese que en el modelo relacional no puede haber más de un valor en cada celda de una tabla). El ISBN introducido en la relación RECETA es clave ajena de ésta y referencia a la relación LIBRO.

RECETA(Cod_receta, Descripción, Fecha_creación,Título_original, ISBN, Cod_receta_padre) DR/UC

En cuanto a las opciones de borrado y modificación, el borrado debe ser restringido (DR), pues si eliminamos un libro no se debe permitir que se eliminen todas las recetas que Antonio sacó de él; la modificación puede ser en cascada (UC) para que los cambios se actualicen automáticamente (Figura 2. 57).

Figura 2. 58: Asociación entre RECETA original y no original

LIBRO (ISBN, Título, Autor) DR/UC RECETA (Cod_receta, Descripción, Fecha_creación,Título_original, ISBN)

Cada receta está compuesta por ciertos ingredientes (uno o varios) y además un mismo ingrediente, sin tener en cuenta las cantidades del mismo, puede utilizarse en serlo de varias recetas. Por tanto debemos introducir una nueva relación COMPUESTA cuyos

Figura 2.57: Asociación entre LIBRO y RECETA

atributos serán “Cod_receta”, “Cod_ingrediente” y “Cantidad”. La clave primaria Cada receta puede ser original, es decir sacada directamente de un

estará formada por los dos primeros atributos y de esta forma tendremos qué

libro de cocina, o bien ser una variación de una ya creada y este

cantidad de un mismo ingrediente se ha utilizado en cada receta y todas las

aspecto se desea recoger en la base de datos.

cantidades de cada uno de los ingredientes que intervienen en una misma receta, como puede verse en la Figura 2. 59.

Independientemente de que la receta sea original o variación, es una receta y

por

tanto

contendrá

los

mismos

atributos

que

cualquier

receta;

si

“Cod_receta” y “Cod_ingrediente” son, respectivamente, claves ajenas de

duplicásemos la relación RECETA y mantuviésemos una para las recetas

las relaciones RECETA e INGREDIENTE. Los borrados y modificaciones de ambas

originales y otra para las varaciones, provocaríamos redundancia, pues la

claves ajenas pueden hacerse en cascada, pues a lo único que afectaría sería a

definición de la relación RECETA se haría dos veces. Lo mejor en estos casos es

la relación existente entre la receta y sus ingredientes, pero no a la receta o al

añadir un atributo “Cod_receta_padre” en RECETA, que, en el caso de contener

ingrediente en sí.

valor, nos dirá de qué otra receta proviene (si no contiene ningún valor es porque la receta correspondiente a la fila en la que este atributo no tiene valor es una receta original). “Cod_receta_padre” es una clave ajena de la relación RECETA que se referencia a sí misma. Si eliminamos una receta que es una variación de otra, no debemos permitir que se borre la receta “padre”, por lo que el borrado debemos considerarlo restringido (DR), en cambio la modificación de una receta

211

212

SQL – Lenguaje de Consulta Estructurado

LIBRO

84-404-9666-4 84-324-8776-3 84-403-6999-5

DC/UC COMPUESTA ( Cod_ingrediente, Cod_receta, Cantidad) DC/UC

Autor

Título

ISBN

RECETA (Cod_receta, Descripción, Fecha_creación,Título_original, ISBN, Cod_receta_padre)

Guía Práctica de Cocina Oriental La Cocina Para Estar en Forma La Cocina Práctica

K.C.Chin Karlos Arguiñano Karlos Arguiñano

RECETA Cod_receta

INGREDIENTE Cod_ingrediente, Nombre) (

Tipo

Fecha creación

Título original

ISBN

Cod_receta_padre

Pasta

La pasta se cuece y se saltea con un poco de aceite. Mezclar la nata con las yemas de huevo batidas e incoporar a la pasta (a fuego lento). Fuera del fuego se le añade queso rellado y se gratina (2 min.)

8/3/1999

Cintas a la Crema

84-324-8776-3

8

Pasta

Rehogar mantequilla, anchoas y aceitunas. Añadir la pasta cocida y el brecol. Lonchas de queso por encima y gratinar (1,5 min.).

13/6/1999

Cintas con Brecol

84-324-8776-3

9

Pasta

Rehogar la verdura con el ajo. Incorporar la pasta cocida con las anchoas y las aceitunas. Colocar las lonchas de queso y gratinar (3 min.)

26/6/1999

Cintas con Brecol

84-403-6999-5

10

Verduras

Freir los aros de pimiento y colocar encima los puedrros cocidos. Cubrir con salsa bechamel. Espolvorear con queso rallado y gratinar (5 min.)

30/6/1999

Puerros con bechamel

84-324-8776-3

Figura 2. 59: Asociación entre RECETA e INGREDIENTE Tras haber analizado todos los supuestos del enunciado el esquema relacional correspondiente a la base de datos de recetas de cocina, queda como

Descripción

7

se refleja en la Figura 2. 60.

LIBRO (ISBN, Título, Autor)

3

-------

8

------

COMPUESTA

DR/UC

Cod_receta

RECETA (Cod_receta, Descripción, Fecha_creación,Título_original, ISBN, Cod_receta_padre) DR/UC

INGREDIENTE Cod_ingrediente 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

DC/UC COMPUESTA (Cod_ingrediente, Cod_receta, Cantidad) DC/UC INGREDIENTE Cod_ingrediente, Nombre) (

Figura 2.50: Esquema relacional de las Recetas de Cocina

Una vez realizado el diseño de la base de datos hay que gestionarla, es decir, insertar datos, modificar datos, y realizar consultas a la base de datos. Supongamos la siguiente ejemplar del esquema:

213

214

Nombre Cintas Yemas de huevo Queso rallado Nata líquida Brecol Anchoas Aceitunas negras Lonchas de queso Coliflor Espinacas Puerros Zanahorias Judías verdes Ajos Pimientos Bechamel

7 7 7 7 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 10 10 10 10

Cod_ingrediente 4 5 6 7 4 8 9 10 11 4 8 9 10 11 12 13 14 15 16 17 14 18 19 6

Cantidad 300 grs. 4 ---1 vasito 200 grs. 250 grs. 8 1 puñado 4 200 grs. 250 grs. 8 1 puñado 4 200 grs. 200 grs. 100 grs. 100 grs. 100 grs. 1 diente 6 6 medianos 1/2 litro 50 grs.

SQL – Lenguaje de Consulta Estructurado

Supongamos que queremos realizar las siguientes consultas;

- Consultas de unión: 1. Recetas en las que intervienen las ‘Lonchas de queso’

- Consultas de selección

2. Título de los libros , descripción y título original de las recetas de tipo ‘Verduras’

1. Libros de cocina de la biblioteca de Antonio.

3. Ingredientes y cantidad de los mismos utilizados en las recetas

2. Nombre y descripción de las recetas del tipo pasta realizadas en

anteriores a junio de 1999.

junio de 1999.

Ejemplo 1. Recetas en las que intervienen las ‘Lonchas

Ejemplo 1. Libros de cocina de la biblioteca de Antonio

de queso’

SELECT * FROM LIBRO SELECT Tipo, Descripción, Fecha_creación, Título_original, Cantidad FROM RECETA, COMPUESTA, INGREDIENTE WHERE Nombre = ‘Lonchas de queso’ AND INGREDIENTE.Cod_ingrediente = COMPUESTA. Cod_ingrediente AND COMPUESTA.Cod_receta = RECETA.Cod_receta

Resultado: LIBRO Autor

Título

ISBN 84-404-9666-4 84-324-8776-3 84-403-6999-5

Guía Práctica de Cocina Oriental La Cocina Para Estar en Forma La Cocina Práctica

K.C.Chin Karlos Arguiñano Karlos Arguiñano

Resultado:

13/6/1999 Cintas con Brecol

Cantida d 4

Pasta

26/6/1999 Cintas con Brecol

4

Descripción

Tipo

Ejemplo 2. Nombre y descripción de las recetas del tipo pasta realizadas en

junio de 1999

SELECT Descripción, Título_original FROM RECETA WHERE Tipo = ‘Pasta’ AND Fecha_ creación >= 1/6/1999

Resultado:

Fecha creación Título original

Pasta Rehogar mantequilla, anchoas y aceitunas. Añadir pl asta cocida y el brecol. Lonchas de queso encima y gratinar (1,5min.). Rehogar la verdura con el ajo. Incorporar la pasta cocida con las anchoas y las aceitunas. Colocar llonchas de queso y gratinar (3 min.)

Ejemplo 2. Título de los libros , descripción y título original de las recetas de tipo ‘Verduras’

RECETA Descripción

Título_original

Rehogar mantequilla, anchoas y aceitunas. Añadir la Cintas con Brecol pasta cocida y elbrecol. Lonchas de queso por encima y gratinar (1,5min.). Rehogar la verdura con el ajo. Incorporar la pasta cocida con las anchoas y las aceitunas. Colocar las lonchas de queso y gratinar (3min.)

SELECT Título, Descripción, Título_original FROM RECETA, LIBRO WHERE Tipo = ‘Verduras’ AND RECETA.ISBN = LIBRO.ISBN

Cintas con Brecol

Resultado:

215

216

SQL – Lenguaje de Consulta Estructurado

Título

Descripción

La Cocina Para Estar en Forma

Freir los aros de pimiento y colocar encima los puedrros cocidos. Cubrir con salsa bechamel. Espolvorear con queso rallado y gratinar (5 min.)

3.

Repaso de Consultas en SQL

Título original Puerros con bechamel

La sintaxis de las consultas en SQL es: SELECT [(*|ALL|DISTINCT| )| <expresión de función>] FROM <nombre_tabla> [[, nombre_tabla]...]

Ejemplo 3. Ingredientes y cantidad de los mismos

WHERE |

utilizados en las recetas anteriores a junio de 1999 [GROUP BY ] HAVING | SELECT Ingrediente, Cantidad FROM RECETA, COMPUESTA, INGREDIENTE WHERE Fecha_creación < ‘1/6/1999’ AND IGREDIENTE.Cod_ingrediente = COMPUESTA. Cod_ingrediente AND COMPUESTA.Cod_receta = RECETA.Cod_receta

ORDER BY <lista de atributos> [ASC|DESC] | <sentencia de consulta>

Resultado:

Nombre Cintas Yemas de huevo Queso rallado Nata líquida

donde:

ALL: selecciona todas las columnas

Cantidad 300 grs . 4 ---1 vasito

DISTINCT: suprime filas duplicadas Operadores para la condición de búsqueda: <, >, =, >=, <=, between, like, in,... y también los operadores lógicos: NOT, AND, OR.

Presentaremos en este apartado varios ejemplos de bases de datos con las tablas y sus valores. En estos ejemplos iremos aplicando distintas consultas de menor a mayor dificultad con el fin de que mediante estos ejemplos se aprenda las distintas posibilidades que ofrece SQL para realizar consultas a una base de datos.

217

218

SQL – Lenguaje de Consulta Estructurado

3.1 BD sobre las que se muestran las consultas En esta base de datos contemplamos la información de una empresa con datos de los empleados y los departamentos a los que pertenecen dichos

UNIVERSIDAD

empleados.

Tablas: 3.2 Valores para las tablas de las BD que nos servirán de ejemplo

Alumno(cod_matricula, nombre, ciudad, cod_grupo) Grupo(cod_grupo, curso, turno)

UNIVERSIDAD

Profesor(cod_profesor, nombre, ciudad, categoría, salario) Impartir(cod_grupo, cod_profesor, asignatura, horas)

Alumno cod_matricula 101 102 202 300 103

Base de datos en la que almacenamos los alumnos, los grupos donde se han matriculado estos, los profesores y los grupos donde imparten clase indicando la asignatura que imparten.

Nombre Juan Montero Alicia Cristobal Ana Vallejo Ignacio López Leticia Martínez

Grupo Cod_grupo

BIBLIOTECA

Ciudad Alcorcón Leganés Leganés Móstoles Alcorcón

cod_grupo 11 11 21 31 --

Turno

Curso

Tablas: 11 12 21 22 31

Autor(nombre, fecha_nac, nacionalidad) Escribe(nombre, cod_documento) Documento(cod_documento, titulo, tipo_documento, precio, num_copias)

Profesor Cod_profesor 1p 2p 3p 4p 5p 6p 7p

Almacenaremos la información relativa a documentos con los autores que los han escrito.

EMPRESA

Tablas: Empleado(nombre, numero_dept, salario, fecha_nac, ext_telefónica) Departamento(numero_dept, nombre) 219

220

1 1 2 2 3

Nombre D. Cuadra E. Nieto P. Martínez C. Nieto A. Sierra C. García J. Montero

M T M T T

Ciudad Madrid Las Rozas Alcorcón Madrid Madrid Madrid Madrid

Categoría T1 T2 T1 T2 T3 T1 T3

Salario 200.000 250.000 225.000 150.000 120.000 135.000 125.000

SQL – Lenguaje de Consulta Estructurado

Impartir Cod_grupo 11 11 21 31

Cod_profesor 1p 2p 1p 2p

Asignatura Intr. Informática SGBD Ficheros y BD Diseño de BD

Escribe Nombre Miguel de Cervantes Emily Bronte Isaac Asimov Christian Jacq Christian Jacq Ken Follet Paloma Martínez P. Isasi D. Borrajo

Horas 20 15 12 20

BIBLIOTECA

Autor Nombre Miguel de Cervantes Emily Bronte Isaac Asimov Christian Jacq Ken Follet Paloma Martínez P. Isasi D. Borrajo

Fecha_nac 9-10-1547 2-9-1818 23-4-1930 30-6-1947 5-8-1949 2-9-68 3-4- 66 23-8-65

Documento Cod_documento Título 11 El Quijote 12 Cumbres Borrascosas

Nacionalidad Española Inglesa Americana Francesa Inglesa Española Española Española

Tipo_documento Novela Novela

EMPRESA

Empleado Nombre Pablo Montero Beatriz Cristobal J.Luis Martín Almudena López Angel Vallejo Pedro García

Precio Num_copias 5000 22 4000 12

13 14

La Pirámide Asesinada Novela La Ley del Desierto Novela

2000 2000

15 10

15

Introducción a la Divulgativo ciencia Los Pilares de la Tierra Novela Gramáticas, Lenguajes Técnico y Autómatas

4500

13

6000 3500

7 6

16 17

Cod_documento 11 12 15 13 14 16 17 17 17

Departamento Numero_dept 11 13 14

221

222

Numero_dept 14 13 11 13 14 11

Salario 220.000 300.000 150.000 350.000 400.000 200.000

Fecha_nac 10-11-67 20-9-68 25-6-77 4-5-60 15-4-72 12-3-70

Nombre Contabilidad Marketing Informática

Ext_telefónica 6543 6577 6433 6422 6321 6323

SQL – Lenguaje de Consulta Estructurado

Ejemplo 2: seleccionar el nombre, la nacionalidad y la

3.3 Consultas sencillas (Sobre una sola tabla)

fecha de nacimiento de todos los autores ordenada por nacionalidad.

3.3.1 Selección de columnas

SELECT (|*) FROM <nombre_tabla>

SELECT nombre, nacionalidad, fecha_nac FROM autor ORDER BY nacionalidad

En la expresión del SELECT también pueden aparecer expresiones aritméticas (que dan lugar a columnas derivadas) con suma, resta, multiplicación y división con las prioridades habituales de la aritmética (izquierda a derecha, paréntesis, etc.)

Resultado:

Nombre Isaac Asimov Miguel de Cervantes Paloma Martínez P. Isasi D. Borrajo Christian Jacq Emily Bronte Ken Follet

Ejemplo 1: calcular la subida en un 15% en el precio de los documentos. SELECT titulo, tipo_documento, precio* 1,15 FROM documento

Nacionalidad

Fecha_nac

Americana Española Española Española Española Francesa Inglesa Inglesa

23-4-1930 9-10-1547 2-9-68 3-4- 66 23-8-65 30-6-1947 2-9-1818 5-8-1949

Resultado: Título El Quijote Cumbres Borrascosas La Pirámide Asesinada La Ley del Desierto

Tipo_documento Novela Novela Novela Novela

Precio *1,15 5750 4600 2300 2300

Introducción a la ciencia Los Pilares de la Tierra Gramáticas, Lenguajes y Autómatas

Divulgativo Novela Técnico

5175 6900 4025

3.3.3

Con restricción (seleccionando filas, WHERE) Hasta ahora se recuperaban todas las filas. Si se quiere restringir las filas seleccionadas se utiliza WHERE con las condiciones que deben cumplir las filas para ser visualizadas. Consulta simple: La condición está compuesta por un operador <, >, =, >=, <= , <>, que unen: - Dos columnas de la tabla

También pueden utilizarse expresiones alfanuméricas: lower (para poner los valores en minúsculas), upper (para ponerlos en mayúsculas), substr (extraer una subcadena de una cadena de caracteres), + (operador de concatenación), length (calcula la longitud de una cadena de caracteres), etc.

3.3.2

- Una columna y una constante (numérica, carácter, fecha) Las columnas que se referencian en la condición no tienen porque ser seleccionadas en el SELECT.

Ordenando Filas (ORDER BY)

Ejemplo 3: seleccionar los autores cuya nacionalidad sea española.

Las filas se almacenan en el mismo orden en que han sido insertadas. Para obtenerlas ordenadas se aplica ORDER BY. Se puede especificar ASC o DESC (por omisión es ASC).

SELECT nombre, nacionalidad, fecha_nac FROM autor WHERE nacionalidad = ‘española’ 223

224

SQL – Lenguaje de Consulta Estructurado

Resultado:

Ejemplo 6: alumnos cuyos números de matricula estén entre 200 y 400 (ambos inclusive).

Nombre Miguel de Cervantes Paloma Martínez P. Isasi D. Borrajo

Nacionalidad Española Española Española Española

Fecha_nac 9-10-1547 2-9-68 3-4- 66 23-8-65

SELECT * FROM alumno WHERE cod_matricula BETWEEN 200 AND 400 Resultado:

Consulta compleja: con operadores lógicos: NOT, AND, OR, IN, BETWEEN AND, LIKE, NULL, NOT NULL. ¡¡Cuidado con las prioridades!!

cod_matricula 202 300

nombre Ana Vallejo Ignacio López

ciudad Leganés Móstoles

cod_grupo 21 31

Ejemplo 4: seleccionar aquellos autores que no sean alemanes nacidos después de la segunda guerra mundial. Ejemplo 7: alumnos que no están asignados a un grupo SELECT * FROM alumno WHERE cod_grupo IS NULL

SELECT nombre FROM autor WHERE nacionalidad <> ‘alemana’ AND fecha_nac> ‘02/09/1945’

Resultado: Resultado:

Nombre Christian Jacq Ken Follet Paloma Martínez P. Isasi D. Borrajo

cod_matricula 103

SELECT nombre FROM autor WHERE nacionalidad IS NOT NULL Resultado:

SELECT * FROM alumno WHERE ciudad IN (‘Getafe’, ‘Leganés’)

Nombre Miguel de Cervantes Emily Bronte Isaac Asimov Christian Jacq Ken Follet Paloma Martínez P. Isasi D. Borrajo

Resultado:

nombre Alicia Cristobal Ana Vallejo

ciudad Alcorcón

Ejemplo 8: autores que tienen nacionalidad

Ejemplo 5: alumnos que viven en Getafe o Leganés

cod_matricula 102 202

nombre Leticia Martínez

ciudad Leganés Leganés

cod_grupo 11 21

225

226

cod_grupo --

SQL – Lenguaje de Consulta Estructurado

funciones son los valores pertenecientes a una o más columnas. Se pueden aplicar a uno o más grupos de filas de tabla (dependiendo de si se utiliza el GROUP BY o no).

Cuando no conocemos el conjunto exacto de caracteres que forman la constante para indicar la restricción se utiliza LIKE con unos caracteres especiales _ (un carácter) y % (cualquier número de caracteres)

Todas las funciones excluyen los valores nulos excepto COUNT(*) - AVG: calcula la media de los valores de la colección

Ejemplo 9: autores cuyo nombre empiece por I

- MAX: calcula el máximo. - MIN: calcula el mínimo

SELECT * FROM autor WHERE nombre LIKE ‘I%’

- SUM: calcula la suma de los valores de la colección - COUNT(*): halla cuántos valores hay en la colección incluyendo las nulas

Resultado:

Nombre Isaac Asimov

Fecha_nac 23-4-1930

- COUNT(columna): cuenta el número de filas que tienen valor distinto de nulo en la columna de cada grupo

Nacionalidad Americana

- COUNT(DISTINCT columna): cuenta el número de filas distintas que tienen valor distinto de nulo en la columna de cada grupo.

Se utilizará DISTINCT si no se quiere que aparezcan filas recuperadas iguales.

Ejemplo 10: seleccionar las distintas nacionalidades que

Ejemplo 11: hallar el número de empleados de la

existen en la tabla autores.

empresa.

SELECT DISTINCT nacionalidad FROM autor

SELECT COUNT (*) "Número de Empleados" FROM empleado

Resultado:

Resultado:

Nacionalidad Española Inglesa Americana Francesa

Número de Empleados 6

Ejemplo 12: hallar el salario medio, el mínimo, el

3.3.4

máximo de todos los empleados

Agrupando filas (GROUP BY y HAVING) Hasta ahora todas las consultas vistas devolvían un conjunto de filas que cumplían una serie de condiciones. Es posible agrupar las filas por determinados grupos de valores y aplicar funciones a cada grupo.

SELECT AVG(salario) "Media", MIN(salario) "Mínimo” MAX(salario) "Máximo" FROM empleado Resultado:

A las funciones que en vez de actuar sobre una solo fila actúan sobre un grupo de filas se las denomina funciones de grupo y devuelven un solo valor por cada grupo. El argumento de estas 227

228

SQL – Lenguaje de Consulta Estructurado

Media 270.000

Mínimo 150.000

Resultado:

Máximo 400.000

Tipo_documento Novela Divulgativo Técnico

Ejemplo 13: hallar el número de empleados y de extensiones telefónicas del departamento 14.

SUM(precio) 19.000 4500 3500

AVG(precio) 3800 4500 3500

Es posible indicar en el SELECT nombres de columnas con funciones de agregación siempre y cuando se utilicen esos nombres de columnas en el GROUP BY.

SELECT COUNT(*), COUNT(DISTINCT ext_telefonica) FROM empleado WHERE numero_dept=14.

Resultado: Ejemplo 16: calcular la suma de los precios y el precio

2 2

medio de los documentos agrupados por tipo de documento (novela, técnico, ...) Ejemplo 14: ¿cuántos empleados que han nacido antes

de 1970? SELECT SUM(precio), AVG(precio), tipo_documento FROM documento GROUP BY tipo_documento

SELECT COUNT(*) "Número de Empleados" FROM empleado WHERE fecha_nac< ‘01/01/1970’

Resultado:

Resultado:

Tipo_documento Novela Divulgativo Técnico

Número de Empleados 3 El grupo por defecto es la tabla entera. Si se quiere dividir la tabla en grupos se utiliza GROUP BY con la que se especifican la(s) columna(s) por las que se quiere agrupar.

SUM(precio) 19.000 4500 3500

AVG(precio) 3800 4500 3500

Si se quiere restringir los grupos de salida se utiliza la cláusula HAVING seguida de las condiciones que debe cumplir. El proceso que se sigue es el siguiente: la cláusula WHERE restringe las filas de la tabla, después se agrupan y sólo se seleccionan aquellas que cumplas las condiciones de la cláusula HAVING.

Ejemplo 15: calcular la suma de los precios y el precio medio de los documentos agrupados por tipo de documento (novela, divulgación, ...)

Ejemplo 17: número de grupos que existen en cada SELECT SUM(precio), AVG(precio) FROM documento GROUP BY tipo_documento

curso

SELECT curso, COUNT(*) FROM grupo 229

230

SQL – Lenguaje de Consulta Estructurado

GROUP BY curso Ejemplo 20: Hallar para cada categoría de profesorado

Resultado:

de

Madrid

el

sueldo

máximo

y

mínimo

ordenado

por

categoría, pero sólo de aquellas categorías que tengan

Curso 1 2 3

COUNT(*)

asociados más de dos profesores

2 2 1

SELECT categoría, MAX(salario), MIN(salario) FROM profesor WHERE ciudad=’Madrid’ GROUP BY categoría HAVING COUNT(categoría) > =2 ORDER BY categoría

Ejemplo 18: cursos que tienen un único grupo y que además es de tarde SELECT curso FROM grupo GROUP BY curso HAVING COUNT(*)=1 AND turno=’T’

Resultado:

Categoría T1 T3

Resultado:

MAX(salario) 200.000 125.000

MIN(salario) 135.000 120.000

Curso 3 3.4 Consultas basadas en más de una tabla Ejemplo 19: media del número de copias de los

3.4.1 Combinando tablas: Join

documentos agrupados por tipo de documento y que la media sea mayor que 10.

Hasta ahora sólo hemos seleccionado datos de una única tabla. Por medio de la combinación de tablas se pueden seleccionar datos de tablas diferentes.

SELECT AVG(num_copias), tipo_documento FROM documento GROUP BY tipo_documento HAVING AVG(num_copias)>10

La unión de dos tablas es otra tabla que tiene por columnas la unión de las columnas de las dos y por filas el producto cartesiano (cada fila de la primera tabla se concatena con todas y cada una de las filas de la otra). Tendrá que haber una condición de combinación para restringir las filas del producto cartesiano que realmente nos interesan.

Resultado:

Tipo_documento AVG(num_copias) Novela 13,2 Divulgativo 13

Tipos de combinación: x - Producto cartesiano: sin restricciones

El orden de ejecución es: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. 231

232

SQL – Lenguaje de Consulta Estructurado

x - Combinación común: la condición de combinación tiene un operado de igualdad.

SELECT nombre, nacionalidad, cod_documento FROM autor, escribe WHERE autor.nombre=escribe.nombre AND autor.nacionalidad='española'

x - Combinación no común: la condición tiene un operador que no es el de igualdad. x - Autocombinación: se combina una tabla consigo misma.

Resultado:

x - Combinación exterior: selecciona las filas de una tabla que no tienen correspondencia con alguna de la otra.

Nombre Miguel de Cervantes Paloma Martínez P. Isasi D. Borrajo

No influye el orden en que se especifican las columnas en el SELECT y las tablas en el FROM.

Nacionalidad Española Española Española Española

Cod_documento 11 17 17 17

Para las combinaciones no comunes se puede utilizar >, <, >=, <>, BETWEEN AND. Se pueden unir tantas tablas como se quiera pero siempre deberá haber n-1 condiciones de combinación para que la información sea coherente (n= número de tablas).

Ejemplo 21: Considerando las tablas AUTOR y ESCRIBE (un autor escribe varios documentos y un documento puede estar escrito por varios autores). Seleccionar el nombre de autor, nacionalidad y el código de los documentos escritos por él.

Ejemplo 22: Considerando las tablas AUTOR, ESCRIBE,

SELECT nombre, nacionalidad, cod_documento FROM autor, escribe WHERE autor.nombre=escribe.nombre

DOCUMENTO (un autor escribe varios documentos y un documento

puede

estar

escrito

por

varios

autores).

Seleccionar el nombre del autor, su fecha de nacimiento y Resultado:

nacionalidad, el código y el tipo de documento escritos por el autor así como el título de los mismos.

Nombre Miguel de Cervantes Emily Bronte Isaac Asimov Christian Jacq Christian Jacq Ken Follet Paloma Martínez P. Isasi D. Borrajo

Nacionalidad Española Inglesa Americana Francesa Francesa Inglesa Española Española Española

Cod_documento 11 12 15 13 14 16 17 17 17

SELECT nombre, fecha_nac, nacionalidad, cod_documento, título, tipo_documento FROM autor a, escribe b, documento c WHERE a.nombre=b.nombre AND b.cod_documento=c.cod_documento

Resultado: Nombre Miguel de Cervantes Emily Bronte

Puede darse otra condición aparte de la de combinación, por ejemplo:

Isaac Asimov Christian Jacq Christian Jacq 233

234

Fecha_nac Nacionalidad Cod_documen Título to 9-10-1547 Española 11 El Quijote 2-9-1818 Inglesa 12 Cumbres Borrascosas 23-4-1930 Americana 15 Introducción a la ciencia 30-6-1947 Francesa 13 La Pirámide Asesinada 30-6-1947 Francesa 14 La Ley del Desierto

Tipo_documento Novela Novela Divulgativo Novela Novela

SQL – Lenguaje de Consulta Estructurado

Ken Follet

5-8-1949

Inglesa

16

Paloma Martínez

2-9-68

Española

17

P. Isasi

3-4- 66

Española

17

D. Borrajo

23-8-65

Española

17

Los Pilares de la Tierra Gramáticas, Lenguajes y Autómatas Gramáticas, Lenguajes y Autómatas Gramáticas, Lenguajes y Autómatas

Novela

Pedro García

200.000 6321

Angel Vallejo

Técnico

La combinación exterior selecciona aquellas tuplas que no tienen correspondencia (en inglés se denomina OUTER JOIN), que puede ser LEFT OUTER JOIN, RIGHT OUTER JOIN Y FULL OUTER JOIN.

Técnico

Técnico

Ejemplo 24: seleccionar los autores con los documentos que han escrito. Se deben incluir también los autores que no

La autocombinación se realiza por medio de columnas que contienen la misma información. El resultado es otra tabla que tendrá por columnas dos veces las columnas de la tabla original y por filas el producto cartesiano de la tabla por sí misma.

hayan escrito documento alguno

SELECT nombre, cod_documento FROM autor, escribe WHERE autor.nombre=escribe.nombre(+)

Ejemplo 23: seleccionar para cada empleado de la empresa su nombre, salario y el nombre y la extensión

Resultado:

telefónica de su jefe. Supongamos la siguiente relación empleado extendida con la información de los jefes:

No existe ningún autor que no haya escrito documento, luego el resultado es el mismo que sin (+)

Empleado Nombre Pablo Montero Beatriz Cristobal J.Luis Martín Almudena López Angel Vallejo Pedro García

Numero_dept 14 13 11 13 14 11

Salario 220.000 300.000 150.000 350.000 400.000 200.000

Fecha_nac 10-11-67 20-9-68 25-6-77 4-5-60 15-4-72 12-3-70

Ext_telefónica 6543 6577 6433 6422 6321 6323

3.4.2 Operadores de conjunto (UNION, INTERSECT, EXPECT)

Jefe Beatriz Cristobal -Beatriz Cristobal Angel Vallejo -Angel Vallejo

Son los mismos que los de la Teoría de Conjuntos y combinan dos o más consultas en un mismo resultado. Los SELECT debe tener el mismo número de columnas y deben corresponderse en tipo. En estos operadores está implícita la cláusula DISTINCT y son incompatibles con ORDER BY.

SELECT emp.nombre, emp.salario, superior.nombre, superior.ext_telefónica FROM empleado emp, empleado superior WHERE emp.jefe=superior.nombre

7 - UNION: recupera todas las filas que han sido seleccionadas en los dos SELECT 8 - INTERSECT: devuelve las filas comunes que han sido seleccionadas por los mandatos SELECT

Resultado: Nombre Pablo Montero Beatriz Cristobal J.Luis Martín Almudena López Angel Vallejo

Salario 220.000 300.000 150.000 350.000 400.000

Ext_telefónica 6577 -6577 6321 --

9 - EXCEPT (MINUS): devuelve las filas que han sido seleccionadas por el primer mandato SELECT y no lo han sido por el segundo

Jefe Beatriz Cristobal -Beatriz Cristobal Angel Vallejo -235

236

SQL – Lenguaje de Consulta Estructurado

Ejemplo 25: Nombre de los autores de nacionalidad Resultado:

española e inglesa (obsérvese que si la UNION se realiza sobre la misma tabla, bastaría una condición OR).

Nombre D. Cuadra E. Nieto P. Martínez

SELECT nombre, nacionalidad FROM autor WHERE nacionalidad='española' UNION SELECT nombre, nacionalidad FROM autor WHERE nacionalidad='inglesa'

Ejemplo 27 : Seleccionar los nombres de de los profesores que no son coordinadores SELECT nombre FROM profesor MINUS SELECT nombre FROM coordinador

Resultado:

Nombre Miguel de Cervantes Paloma Martínez P. Isasi D. Borrajo Emily Bronte Ken Follet

Nacionalidad Española Española Española Española Inglesa Inglesa

Resultado:

Nombre C. Nieto A. Sierra C. García J. Montero

Supongamos que en el enunciado de la Universidad tenemos otra tabla denominada Coordinador de la siguiente forma:

Coordinador Cod_coord 1p 2p 3p

Nombre D. Cuadra E. Nieto P. Martínez

Ciudad Madrid Las Rozas Alcorcón

Categoría T1 T2 T1

Salario 200.000 250.000 225.000

Ejemplo 26 : Seleccionar los nombres de las personas que sean profesores y coordinadores. SELECT nombre FROM profesor INTERSECT SELECT nombre FROM coordinador 237

238

SQL – Lenguaje de Consulta Estructurado

El lenguaje de manipulación de datos del SQL (LMD) es un lenguaje de especificación, que consiste en un conjunto de sentencias que nos permiten manipular la base de datos.

Las operaciones que se pueden hacer sobre la base de datos son: Altas, bajas, modificaciones y consultas.

La sintaxis de las consultas en SQL es:

SELECT [(*|ALL|DISTINCT| )| <expresión de función>] FROM <nombre_tabla> [[, nombre_tabla]...] WHERE | [GROUP BY ] HAVING | ORDER BY <lista de atributos> [ASC|DESC] | <sentencia de consulta> donde: ALL: selecciona todas las columnas DISTINCT: suprime filas duplicadas Operadores para la condición de búsqueda: <, >, =, >=, <=, between, like, in,... y también los operadores lógicos: NOT, AND, OR.

239