Gestión de Bases de Datos
Edición ISBN:
Imposible sin el apoyo de mis hijas Noelia y Sandra.
Sin “mis creadores” (José Luis y Josefineta) no hubiese sido factible.
Índice
TEMA 1.INTRODUCCIÓN A LA GESTIÓN DE BASES DE DATOS ... 11
TEMA 2.MODELO ENTIDAD – RELACIÓN ... 17
TEMA 3.MODELO RELACIONAL ... 49
TEMA 4.LENGUAJE DE DEFINICIÓN DE DATOS ... 75
TEMA 5.SQL – CONSULTAS SELECT ... 101
Tema 1.
INTRODUCCIÓN A LA GESTIÓN DE
BASES DE DATOS
1.- ¿Qué es una Base de Datos?
Podemos definir una Base de Datos como un conjunto de información que permanece en el tiempo. La Base de Datos va a estar gestionada y manipulada por un Sistema Gestor de Base de datos, de forma que los Usuarios de ese Sistema Gestor de Base de Datos van a poder: definir nuevas estructuras de esquemas de la Base de Datos, poder consultar o actualizar los datos.
Las Bases de Datos deben poder soportar el almacenamiento de grandes cantidades de información, pero también deben de tener medidas que garanticen su seguridad frente a accesos indeseados o accidentes.
Base de Datos
SGBD
2.- Sistemas Gestores de Bases de Datos (SGBD)
2.1.- Definición
Podemos definir un Sistema Gestor de Base de Datos (SGBD), como un conjunto de datos inter-relacionados, estructurados y organizados; y una colección de programas que permiten gestionar y poder acceder a esos datos.
El SGBD nos debe permitir realizar las siguientes operaciones o servicios:
• Crear la Base de Datos. Podremos especificar la estructura de nuestra Base de Datos, indicar los tipos de datos que la componen, indicar las restricciones de los mimos y poder indicar las distintas relaciones que existen entre los datos. Para estar operaciones usaremos el Lenguaje de Definición de Datos.
• Manipulación de los datos. Ya sea realizando consultas, actualizaciones o inserciones de los mismos (utilizando el Lenguaje de Manipulación de Datos).
• Controlar el acceso a los datos de los distintos usuarios mediante mecanismos de seguridad.
• Mantener la integridad de los datos, evitando así que puedan ser transformados o borrados por cambios no autorizados.
• Permitir la realización de copias de seguridad de nuestros datos, y también la restauración de los mismos.
2.2.- Componentes
Este conjunto de programas (SGBD) debe proporcionar una serie de posibilidades que nos va a permitir guardar y explotar la información de una manera eficiente. Como componentes más importantes podemos destacar:
2.2.1.- Lenguajes
Los lenguajes del SGBD van a permitir especificar los datos que van a formar parte de la Base de Datos, su estructura, las relaciones que habrá entre ellos, los controles de acceso a la misma, las reglas de integridad y las vistas de los usuarios.
Los principales lenguajes que nos vamos a encontrar son:
• Lenguaje de Definición de Datos (DDL), que utilizaremos para definir la estructura de la Base de datos, las vistas que tengan los usuarios de los datos y la forma en que se almacenan los mismos. En este lenguaje, se va a definir, lo que luego veremos que es el esquema conceptual y el esquema interno de la Base de Datos. Este lenguaje lo utilizarán los Administradores y los diseñadores de la Base de Datos.
• Lenguaje de Manipulación de Datos (DML), se utiliza para leer y actualizar los datos de la Base de Datos, siendo utilizado para realizar consultas, modificaciones, eliminaciones e inserciones.
2.2.2.- Seguridad
El SGBD debe proporcionar las siguientes herramientas necesarias para garantizar la seguridad e integridad de la información almacenada en nuestra Base de Datos.
• Garantizar la seguridad de los datos contra accesos no autorizados (ya sean fortuitos o malintencionados).
• Mecanismos para implantar restricciones de integridad en la Base de Datos. Protegiendo la información contra daños accidentales.
• Proporcionar mecanismos y herramientas para la realización de copias de seguridad y restauración de las mismas. Así mismo, debe permitir la planificación de las mismas en el tiempo.
• Controlar el acceso concurrente a los datos, y tener mecanismos de consistencia de los mismos cuando varios usuarios actualicen los datos a la vez.
2.2.3.- Usuarios
Distinguiremos entre los siguientes tipos de usuarios:
2. Diseñador: encargado de definir el contenido y la estructura de nuestra Base de Datos, así como las restricciones a la que deben estar sometidos los datos. También tienen la función de interactuar con los usuarios para ver sus necesidades de información.
3. Usuario final: realizaran básicamente las tareas de consulta y recuperación de datos, generación de los informes necesarios para su actividad y actualizar el contenido de la Base de Datos para mantener al día la misma.
2.2.4.- Administrador
Tiene una responsabilidad muy grande, ya que posee el máximo nivel de privilegios dentro de los usuarios. Una de sus tareas será la de crear los usuarios que tendrán acceso a la Base de Datos.
Así las principales tareas de un Administrador de la Base de Datos (DBA) serán: • Instalación del SGBD en el sistema informático de la empresa.
• Creación de las distintas Bases de Datos que se vayan a gestionar. • Creación y mantenimiento de la estructura de las Bases de Datos. • Gestión de las cuentas de usuario.
• Arrancar y parar cuando sea necesario el SGBD.
• Establecer políticas de acceso y protocolos de trabajo para los usuarios de la Base de Datos.
• Realizar tareas de explotación como: vigilancia del trabajo diario, comprobar cargas de acceso a los servidores, efectuar periódicamente copias de seguridad, realizar auditorías,…
Usuario final
Diseñador
3.- Arquitectura
El objetivo de tener una arquitectura a tres niveles era el de separar los programas de aplicación de la Base de Datos física. Así tenemos tres niveles de abstracción:
1. Nivel interno (físico). Es el más próximo al almacenamiento físico, es decir, como realmente se guardan los datos en el ordenador. Aquí se describe la estructura física de la base de datos mediante el esquema interno. Se detallará como se almacenan físicamente los datos: archivos, organización de los mismos, accesos,…
2. Nivel externo (visión). El que tienen los usuarios, y donde se describen los esquemas externos o vistas de los usuarios de la base de datos. Aquí se tendrá una visión particular de un usuario o de un grupo.
3. Nivel conceptual. En este nivel se describe toda la estructura de la base de datos para los usuarios mediante un esquema conceptual. Se tendrá las entidades, atributos, relaciones,...
4.- Modelado de Datos
Uno de los aspectos más interesantes de un SGBD es proporcionar a los usuarios una visión abstracta de los datos, es decir, que el usuario va a utilizar esos datos pero no sabrá cómo están almacenados físicamente.
Por eso los modelos de datos son el instrumento básico para entender esa abstracción. Son usados para la representación y resolución de problemas. Estos modelos de datos, se definen como el conjunto de herramientas conceptuales que
Nivel Externo
Nivel Conceptual
• Modelos lógicos basados en objetos, que se utilizan para describir los datos a nivel conceptual y externo. El más utilizado es el Modelo Entidad – Relación.
• Modelos lógicos basados en registros, que se usan para describir los datos en el nivel conceptual y físico. Se llaman así porque la base de datos está estructurada en registros, y cada registro define un número de campos o atributos. El modelo más aceptado de este tipo es el Modelo Relacional, que viene a representar los datos y las relaciones entre los datos mediante tablas, cuyas columnas tienen nombres únicos, donde las filas representan los registros y las columnas los campos de cada registro.
Tema 2.
MODELO ENTIDAD – RELACIÓN
1.- Introducción
El modelo Entidad / Relación (ME/R), propuesto por Peter P. Chen en sus artículos, se basa en entidades (cualquier objeto de interés para el universo descrito) que se relacionan o asocian entre sí.
El ME/R es un modelo de alto nivel, que nos permitirá representar el mundo que queremos con un lenguaje, una estructura más cercana a nosotros.
El ME/R lo utilizaremos dentro del proceso de creación de BD:
1. A partir de la realidad, estudiarla (investigando, entrevistando los usuarios,...) haciendo el ANÁLISIS de requerimientos (¿Qué se quiere?). El resultado será un conjunto de requerimientos redactados de forma concisa.
2. A partir del Análisis de requerimientos, diseñar el Esquema CONCEPTUAL de la BD con un modelo de alto nivel (ME/R).
3. A partir del Esquema conceptual, implementar la BD en un SGBD comercial (Relacional), obteniendo el esquema lógico.
2.- Ejemplo de partida
Se trata de una compañía, y el resultado del análisis de requerimientos es el siguiente:
• La compañía está organizada en departamentos. Cada uno tiene nombre único, número único y un empleado que la dirige. Nos interesa la fecha que empezó a dirigirlo.
• Cada departamento controla una serie de proyectos. Cada uno tiene nombre y número únicos.
• De cada empleado nos interesa el nombre (nombre y apellidos), DNI, dirección, teléfono, sueldo y fecha de nacimiento. Todo empleado está asignado a un deptº y tendrá un supervisor. Puede trabajar en más de un Proyecto y trabajará un determinado número de horas a la semana en cada proyecto.
• Queremos saber también los familiares de cada empleado, para administrar los temas de seguros. Queremos saber el nombre, fecha de nacimiento y parentesco con el empleado.
3.- Las Entidades del ME/R.
3.1.- ENTIDAD
Def.: “Una persona, lugar, cosa, concepto o suceso, real o abstracto, de interés para la empresa”
Por ejemplo, Los Empleados son Entidades.
Llamaremos TIPO DE ENTIDAD a la estructura genérica (Empleado) y OCURRENCIA de entidad a cada una de las realizaciones concretas de ese tipo de entidad (Antonio Rico).
Representación gráfica
Representaremos un tipo de entidad mediante un rectángulo etiquetado con el nombre del tipo de entidad:
3.2.- Atributo
Es cada una de las propiedades o características que tiene un tipo de entidad o de relación.
Por ejemplo, en la entidad Empleado tendremos los atributos nombre, DNI, dirección, teléfono, sueldo y fecha de nacimiento.
Una Ocurrencia de la entidad tendrá un “valor” para cada atributo, por ejemplo, Antonio Rico, 19.999.335, 925-22.33.45, 1.500 € y 15-6-90.
Para poder identificar cada ocurrencia de una entidad necesitamos que algún atributo (o conjunto de atributos) les represente unívocamente.
De los atributos que cumplen la condición anterior, triaríamos uno que denominaríamos Clave Principal. Y el resto serían Claves candidatas.
Imp.:”Todas la entidades han de tener una clave principal” (Es una de las restricciones del ME/R)
Departamento
Empleado
Familiar
Representación de la clave principal
Puede ser de dos formas:
Representaciones del Ejemplo
3.3.- Dominio
Es el conjunto de posibles valores que puede tomar un atributo.
P.e.: El dominio del atributo DNI seria los números enteros de 8 cifras.
Más de un atributo puede compartir el mismo dominio. Por ejemplo, si incluimos el atributo fecha de incorporación a la compañía en la entidad EMPLEADO, tiene el mismo dominio que fecha_n.
4.- Las Relaciones del ME/R
4.1.- Relaciones
Es una asociación o correspondencia entre entidades.
El Tipo de Relación será la estructura genérica del conjunto de relaciones existente entre dos o más tipos de entidad.
La Ocurrencia de Relación será la vinculación existente entre las ocurrencias concretas de cada uno de los tipos de entidad que intervienen en la Relación.
Representación de Relaciones
La Relación se representa por un rombo, con el nombre de la relación en su interior. Uniremos el rombo con ambas Entidades por medio de líneas.
Relaciones REFLEXIVAS
2 Entidades + de 1 relación
4.2.- Atributos de una Relación
Las relaciones también pueden tener atributos, igual que las entidades.
Por ejemplo, el nº de horas que un empleado trabaja en un proyecto es un atributo de la relación trabaja (no puede ser ni de Empleado ni de Proyecto). (En el ejemplo anterior lo hemos marcado en azul)
4.3.- Tipo de Relación
La CARDINALIDAD especifica el número de ocurrencias de una entidad que pueden intervenir en la relación por cada ocurrencia de la otra entidad.
Clases de Relaciones
Las distintas clases de Relaciones que podemos distinguir serian: 1:1 Como máximo una ocurrencia de cada.
1:N En una entidad una ocurrencia y en la otra muchas. M:N Hay más de una ocurrencia en cada entidad
Ejemplo de Clases de Relaciones
5.- Modelo Extendido
5.1.- Cardinalidad máxima y mínima
Cardinalidad máxima y mínima de una entidad que participa en una relación, son respectivamente el número máximo y mínimo de ocurrencias de esta entidad que están relacionadas con una ocurrencia de la otra entidad.
Ejemplo
5.2.- Entidades DÉBILES
No todas las entidades son iguales, en las normales (REGULARES) las ocurrencias tienen existencia propia.
En cambio, las entidades DEBILES, la existencia de ocurrencias dependen de la existencia de la ocurrencia de otra entidad.
(Si desaparece esta última, también desaparecen las dependientes)
Por ejemplo, los familiares de Antonio Rico podrían ser (Marta, mujer), (Isabel, hija) y (Pedro, hijo). Si desaparece el empleado Antonio Rico, también deberían de desaparecer sus familiares.
Representación Entidades Débiles
Se representan mediante un doble rectángulo:
Dependencia de EXISTENCIA
En el ejemplo anterior diremos que la entidad débil tiene una DEPENDENCIA EN EXISTENCIA.
Dependencia de IDENTIFICACIÓN
Si además de le dep. de existencia consideramos que para identificar una ocurrencia de la entidad Débil nos hace falta la clave de la entidad regular de la que depende, esta dependencia (todavía más restrictiva) la llamaremos DEPENDENCIA DE IDENTIFICACIÓN.
FAMILIAR
La cardinalidad mínima y máxima de la entidad regular en la relación con la débil es siempre de (1,1)
6.- Relaciones de grado mayor que 2
Las relaciones ternarias, debido a su complejidad, se pueden descomponer en tres binarias:
7.- Generalizaciones
Las generalizaciones, nos proporcionan un mecanismo de abstracción que permite especializar una entidad (que se denominará supertipo) en subtipos, o lo que es igual, generalizar los subtipos en el supertipo. La abstracción correspondiente a este tipo de relación entre entidades se denomina es_un. 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 “Animal”. Podremos identificar generalizaciones si encontramos una serie de atributos comunes a un conjunto de entidades. Estos atributos comunes describirán el supertipo y los atributos particulares permanecerán en los subtipos. Puede ocurrir que los subtipos no tengan atributos propios; en ese caso, sólo existirán subtipos si éstos van a participar en relaciones.
La representación de este tipo de relación, es un triángulo invertido, con la base paralela al rectángulo que representa el supertipo y conectado a este y a los subtipos. Las cardinalidades son siempre (1,1) en el supertipo y (0,1) en los subtipos.
EJERCICIOS
1.- Viviendas
Diseñar un modelo E/R que recoja información sobre municipios, viviendas y personas. Cada persona solo puede habitar en una vivienda y residir en un municipio, pero puede ser propietaria de más de una vivienda (para este ejercicio, suponemos que una vivienda solo puede tener un propietario). Nos interesa también la relación de las personas con su cabeza de familia.
2.- Universidad
Diseñar un modelo E/R que recoja información sobre una universidad. Se considera que:
• Los departamentos pueden estar en una única facultad o ser inter-facultativos, agrupando en este caso, cátedras que pertenecen a facultades distintas.
• Una cátedra pertenece a un único departamento. • Una cátedra está en una única facultad.
• Un profesor esta siempre asignado a un único departamento y adscrito a uno o más de una cátedra. Puede cambiar de cátedra pero no de departamento. Nos interesa la fecha en que un profesor se adscribe a una cátedra.
• Existen áreas de conocimiento, y todo departamento tendrá una única área de conocimiento.
3.- Red Bancaria
El análisis de requerimientos de una determinada red bancaria es el siguiente: • De cada banco nos interesa el nombre y la dirección de la sede social. Hay
un código distinto para cada entidad bancaria.
• Cada banco tiene distintas sucursales, que se identifica internamente por un código.
• Cada sucursal tiene asignadas una serie de cuentas corrientes, que se identifican internamente por un código. Una cuenta puede pertenecer a uno o más de un cliente. Es posible que cada cliente pueda hacer operaciones diferentes con la cuentas. Por ejemplo, mientras que dos clientes pueden ser titulares de una misma cuenta, solo uno tiene el poder de cerrarla.
• Cada cliente, que se identifica por su dni, puede tener más de una cuenta, y evidentemente privilegios distintos para cada una de ellas.
• Cada cuenta puede tener distintas domiciliaciones asociadas a ella.
• Las sucursales pueden otorgar préstamos a los clientes, que no estarán asociados a las cuentas. Cada préstamo se otorga a nombre de un único cliente, y a un cliente se le puede otorgar más de un préstamo.
4.- Mundial de futbol
La información que nos interesa de un campeonato mundial de futbol es la siguiente:
• Un jugador puede pertenecer a un único equipo, y no hay dos jugadores con el mismo nombre.
• En cada partido intervienen tres árbitros: el árbitro principal y los jueces de línea de las dos bandas.
• Un árbitro puede realizar una función en un partido y otra en otro. • Cada partido involucra dos equipos.
• Un jugador siempre pertenece al mismo equipo, no puede cambiar de equipo a lo largo del mundial.
5.- Mariposas
Realizar un modelo E/R que recoja información sobre las mariposas, los ejemplares son capturados bien para su observación, o bien para ser incluidos en una colección.
• Como cualquier orden natural, un ejemplar de mariposa pertenece a una especie única. Una especie pertenece a un único género, y un género a una familia natural única.
• Cada especie de mariposa tiene un único nombre científico, mientras que su nombre común, que depende de la zona donde se ha cogido, también nos interesa.
• Ya sea por la observación o para formar parte de una colección, primero se tiene que capturar el ejemplar de mariposa. Esta captura se realiza por una única persona.
• Una determinada persona solo puede ser propietaria de una colección, pero los ejemplares de esa colección pueden hacer sido capturadas por otras personas.
• Se quiere mantener información de las familias, géneros y especies de las mariposas independientemente de que no se hayan capturado ejemplares de los mismos.
• Una mariposa solo puede pertenecer a una colección, y una colección está formada al menos por un ejemplar.
6.- Biblioteca
Se quiere mantener información sobre una biblioteca, donde de cada libro se puede tener más de un ejemplar, que se pueden prestar, y también nos interesa mantener un histórico de préstamos. El análisis de requerimientos sería el siguiente:
• Cada libro (con un determinado título, ISBN, idioma, número de edificio y fecha de edición) trata solamente de un tema, está editado por una única editorial, puede estar escrito por uno o más autores, y dispondremos de unos cuantos ejemplares.
• Un ejemplar se puede prestar a un único lector (del cual tendremos Dni, Nombre, Dirección y Teléfono), y nos interesa la fecha en la que ha sido prestado. Un lector puede tener más de un libro prestado.
• En el histórico de préstamos cada ejemplar se habrá prestado a muchos lectores, y para poder gestionar el seguimiento nos interesa la fecha del préstamo y la de devolución.
7.- Red informática
Se desea mantener información sobre el material informático de una empresa. Del análisis de requerimientos se obtiene la siguiente información:
• La empresa tiene unas cuantas redes interconectadas entre ellas.
• La mayor parte de los ordenadores están conectados en red, aunque hay algunos que no están. También existen ordenadores que están conectados a más de una red (son puentes).
• Cada ordenador puede ser utilizado por uno o más empleados. Un empleado puede tener permiso para utilizar uno o más ordenadores. • Un único empleado es responsable de una red que la gestiona. Este
empleado puede tener bajo su responsabilidad más de una red.
• Cada ordenador puede tener conectado a una impresora como máximo. • Existen algunos ordenadores que pueden tener conectado unos cuantos
módems (se encargan de las comunicaciones de la empresa).
• Las aplicaciones de la empresa están almacenadas en los ordenadores y no están duplicadas (una aplicación está en un único ordenador). Los empleados que tienen acceso a un ordenador tendrán, por tanto, acceso también a las aplicaciones que contiene el ordenador. Además tendrán acceso a otras aplicaciones a través de la red. Cada aplicación tiene asignados permisos para los usuarios que pueden acceder a través de la red.
8.- Viajantes
Diseñar el modelo E/R correspondiente a un sistema de información de una empresa que vende gran variedad de productos. Para la venta de estos productos, dispone de un conjunto de viajantes que realizan visitas a los clientes ofreciéndoles sus productos.
La zona geográfica de influencia de la empresa está dividida en sectores no solapados que comprenden unas cuantas ciudades. Un viajante tiene asignados algunos de estos sectores. Eso no significa que tenga asignado sectores en exclusividad; más de un viajante puede tener asignado el mismo sector.
A un determinado cliente le pueden ofrecer productos distintos viajantes de la empresa, pero nunca el mismo producto.
Un producto puede ser ofrecido por distintos viajantes, pero siempre a distintos clientes. Un viajante no puede ofrecer todos los productos (solo algunos). A un cliente siempre le ofrece un producto el mismo viajante.
9.- Vuelta ciclista
Se pretende modelizar un sistema de información para controlar una prueba ciclista. Los ciclistas se inscriben en la prueba, en cuyo momento se les asigna un dorsal único, se anota su edad, nombre y nombre del equipo al que pertenece, así como el nombre del director del equipo.
La prueba se realiza por etapas, de cada una se anota el número, los kilómetros que la componen, la ciudad de salida, la ciudad de llegada (con unas breves reseñas históricas, económicas y geográficas) y los puertos por los que pasa la etapa (nombre, altura y categoría). Una etapa puede tener la misma ciudad como salida y como llegada.
Se quiere conocer que ciclista ha ganado en cada puerto. Quién ha ganado en cada etapa, así como quién lleva, en cada etapa, cada uno de los maillots de la prueba. De cada maillot se quiere conocer el tipo, color y premio.
Restricciones:
• Un puerto sólo está en una etapa.
• De una ciudad pueden salir varias etapas (por ejemplo, una contra-reloj que sale y finaliza en la misma ciudad, y al día siguiente sale otra etapa).
• A una ciudad pueden llegar varias etapas.
10.- Alquiler de vehículos
Se desea diseñar una base de datos sobre la información de las reservas de una empresa dedicada al alquiler de automóviles teniendo en cuenta que:
Un determinado cliente puede tener en un momento dado hechas varias reservas. De cada cliente se almacena su Dni, nombre, dirección y teléfono. Además dos clientes se diferencian por un código único.
Cada cliente puede ser avalado por otro cliente de la empresa.
Una reserva la realiza un único cliente pero puede involucrar a varios coches. Es importante registrar la fecha de inicio y final de la reserva, el precio del alquiler de cada uno de los coches, los litros de gasolina en el depósito en el momento de realizar la reserva, el precio total de la reserva y un indicador de si el coche o los coches han sido entregados.
No se mantienen los datos de reservas anteriores.
Todo coche tiene siempre asignado un determinado garaje que no puede cambiar. De cada coche se requiere la matrícula, el modelo, el color y la marca.
11.- Olimpiadas
Las sedes olímpicas se dividen en complejos deportivos. Los complejos deportivos se subdividen en aquellos en los que se desarrolla un único deporte y en los polideportivos. Los complejos deportivos tienen áreas designadas para cada deporte con un indicador de localización (ejemplo: centro, esquina-NE, etc.). Un complejo tiene una localización, un jefe de organización individual y un área total ocupada.
Los dos tipos de complejos (deporte único y polideportivo) tendrán diferentes tipos de información. Para cada tipo de sede se conservará el número de complejos junto con su presupuesto aproximado.
Cada complejo celebra una serie de eventos (ejemplo: la pista del estadio puede celebrar muchas carreras distintas). Para cada evento está prevista una fecha, duración, número de participantes, número de comisarios. Una lista de todos los comisarios se conservará junto con la lista de los eventos en los que esté involucrado cada comisario, ya sea cumpliendo la tarea de juez u observador. Tanto para cada evento como para el mantenimiento se necesitará cierto equipamiento (ejemplo: arcos, pértigas, barras paralelas, etc.).
EJERCICIOS PROPUESTOS
1.- Zoológico
La sección de reproducción experimental de un Zoo mantiene un registro de los animales y de los experimentos que realiza. El registro manual de datos relativos a los animales presenta la siguiente estructura:
Genero Especie Animal Nombre Sexo
Primate Gorila 001 Fred M
002 Goliat M 003 Rowna F Chimpancé 001 Larry M 002 Jody F 003 Kudo M 004 Tiny F
Ungulado Caballo 001 Imperioso M
002 Sofia F Burro 001 Negro M 002 Platero M 003 Daisy F Mula 001 Roberta N 002 Rayo N
Además cada especie se registra información del país de origen, para los nacidos en la sección de reproducción del zoo se desea conocer quiénes fueron sus padres y, finalmente, para cada animal se mantiene un registro de incidencias con una anotación por cada incidencia digna de mención (enfermedades, comportamientos anómalos, etc.…).
Los experimentos de reproducción están organizados dentro de proyectos, de los cuales se conoce el investigador que los dirige, el departamento que los organiza, los objetivos, el presupuesto y el patrocinador del mismo. Los investigadores no dependen de los departamentos, pero se sabe que un investigador puede patrocinar un proyecto.
De cada proyecto, se puede realizar uno o varios experimentos. Para los experimentos de reproducción pueden emplearse machos y hembras de la misma o diferentes especies, y, en este último caso la descendencia se registra como una nueva especie. Todos los animales nacidos en esta sección del zoo son el resultado
2.- Tenis
El objetivo de la base de datos es establece el diario de un equipo de participantes en el Torneo de Tenis Grand Slam. El sistema debe mecanizar todos los encuentros que se han desarrollado desde que existe el torneo.
El Grand Slam se compone de cuatro torneos anuales que se celebran en Gran Bretaña, Estados Unidos, Francia y Australia. En cada país se pueden desarrollar en distintos lugares (p.e.: en EE.UU puede desarrollarse en Forest Hill o en Flashing Meadows).
Cada partido tiene asociado un premio de consolación para el perdedor que dependerá de la fase en que se encuentre el torneo. (p.e.: el perdedor de octavos de final puede ganar 5.000 dólares). El ganador de la final recibirá el premio correspondiente al torneo.
Cada torneo tiene cinco modalidades: individual masculino, individual femenino, dobles masculino, dobles femenino y dobles mixtos.
También hay que tener en cuenta la nacionalidad de un jugador, de forma que este puede ser apátrida o tener varias nacionalidades.
El sistema debe dar respuesta a las siguientes preguntas:
• Dado un año y un torneo, composición y resultado de los partidos. • Lista de árbitros que participan en el torneo.
• Ganancias recibidas en premios por un jugador a lo largo del torneo. • Lista de entrenadores que han entrenado a un jugador a lo largo del torneo
3.- Campeonato de Ajedrez
El club de Ajedrez de Villatortas de Arriba, ha sido encargado de la organización de los próximos campeonatos mundiales que se celebrarán en la mencionada localidad. Por este motivo, desea llevar una base de datos toda la gestión relativa a participantes, alojamientos y partidas. Teniendo en cuenta que:
En el campeonato participan jugadores y árbitros: de ambos se requiere conocer el número de asociado, nombre, dirección, teléfono de contacto y campeonatos en los que han participado (como jugador o como árbitro). De los jugadores se precisa además el nivel de juego en una escala de 1 a 10.
• Ningún árbitro puede participar como jugador.
• Los países envían al campeonato un conjunto de jugadores y árbitros, aunque no todos los países envían participantes. Todo jugador y árbitro es enviado por un único país. Un país puede ser representado por otro país. • Cada país se identifica por un número correlativo según su orden alfabético
e interesa conocer además de su nombre, el número de clubes de ajedrez existente en el mismo.
• Cada partida se identifica por un número correlativo (Cod_P), la juegan dos jugadores y la arbitra un árbitro. Interesa registrar las partidas que juegan cada jugador y el color (blancas o negras) con el que se juega. Ha de tenerse en cuanta que un árbitro no puede arbitrar a jugadores enviados por el mismo país que le ha enviado a él.
• Todo participante participa en al menos una partida.
• Tanto jugadores como árbitros se alojan en uno de los hoteles en lo que se desarrollan las partidas, se desea conocer en qué hotel y en que fechas se ha alojado cada uno de los participantes. Los participantes pueden no permanecer en Villatortas durante todo el campeonato, sino acudir cuando tienen que jugar alguna partida alojándose en el mismo o distinto hotel. De cada hotel, se desea conocer el nombre, la dirección y el número de teléfono.
• El campeonato se desarrolla a lo largo de una serie de jornadas (año, mes, día) y cada partida tiene lugar en una de las jornadas aunque no tengas lugar partidas todas las jornadas.
• Cada partida se celebra en una de las salas de las que pueden disponer los hoteles, se desea conocer el número de entradas vendidas en la sala para cada partida. De cada sala, se desea conocer la capacidad y medios de que
dispone (radio, televisión, vídeo,…) para facilitar la retransmisión de los encuentros. Una sala puede disponer de varios medios distintos.
• De cada partida se pretende registrar todos los movimientos que la componen, la identificación de movimiento se establece en base a un número de orden dentro de cada partida: para cada movimiento se guardan la jugada (5 posiciones) y un breve comentario realizado por un experto.
4.- Organizaciones no gubernamentales
La coordinadora nacional de Organizaciones No Gubernamentales (ONG’s) desea mantener una base de datos de las asociaciones de este tipo que existen en nuestro país. Para ello necesita almacenar información sobre cada asociación, los socios que la componen, los proyectos que realizan y los trabajadores de las mismas.
De las asociaciones se desea almacenar su CIF, denominación, dirección y provincia, su tipo (ecologista, integración, desarrollo,…), así como si está declarada de utilidad pública por el Ministerio del Interior.
Cada asociación está formado por socios de los que se precisa conocer su DNI, nombre, dirección, provincia, fecha de alta en la asociación, la cuota mensual con que colabora y la aportación anual que realizan (que se obtendrá multiplicando la cuota mensual por los meses del año).
Los trabajadores de estas organizaciones pueden ser de dos tipos: asalariados y voluntarios. Cada trabajador se identifica por su DNI, tiene un nombre y una fecha de ingreso. Un socio no puede ser trabajador de la asociación.
Los asalariados son trabajadores que cobran un sueldo y ocupan cierto cargo en la asociación. Se desea almacenar la cantidad que éstos pagan a la seguridad social y el tanto por ciento de IRPF que se le descuenta.
Los voluntarios trabajan en la organización desinteresadamente, siendo preciso conocer su edad, profesión y las horas que dedican a la asociación a efectos de cálculo de estadísticas.
Las asociaciones llevas a cabo proyectos a los que están asignados sus trabajadores. Un trabajador puede trabajar en diferentes proyectos de un mismo país. De cada proyecto se desea almacenar su número de identificación dentro de la asociación, en qué país se lleva a cabo y en qué zona de éste, así como el objetivo que persigue y el número de beneficiarios a los que afecta. Un proyecto se compone a su vez de sub-proyectos (que tienen entidad de proyectos).
Tema 3.
MODELO RELACIONAL
1.- ORIGEN Y OBJETIVOS
En 1970 Codd creó el Modelo RELACIONAL, con una base matemática muy sólido (la de las relaciones), donde los datos se estructuran en forma de relaciones (tablas). Fue a partir de los años 80, cuando la tecnología lo permitió, con la salida de mejores producto, como por ejemplo el Oracle (1979). Entonces su implantación ha sido aplastante.
Objetivos del Modelo Relacional:
• Fidelidad, para originar esquemas que representen fielmente la información que existe en el dominio del problema.
• Independencia física, para la manera de guardar los datos no influya en su manipulación lógica.
• Independencia lógica, para que las vistas externas no se vean afectadas por cambios en el esquema conceptual de la B.D.
• Flexibilidad, para poder ofrecer los datos a cada usuario de la forma más adecuada a su aplicación.
• Uniformidad, las estructuras lógicas de los datos presentan un aspecto simple y uniforme (tablas).
• Sencillez, las características anteriores, unidas a unos lenguajes de usuario sencillos, hacen que el Modelo Relacional sea fácil de entender y de utilizar por el usuario final.
2.- ESTRUCTURA DEL MODELO RELACIONAL
El Elemento base del Modelo Relacional es la RELACIÓN, que será una tabla o matriz bidimensional con unas características o restricciones.
La Relación viene identificada por un NOMBRE (Empleado).
Las Filas, donde tenemos la información de las ocurrencias, se denominan TUPLAS. Las columnas, que serán las características que nos interesan de los individuos, se llamarán ATRIBUTOS (Campos).
El conjunto de posibles valores que puede coger un atributo lo llamaremos DOMINIO.
CARDINALIDAD de una relación es el número de tuplas (en el ejemplo 3). GRADO de una relación es el número de atributos (ejemplo 4).
Una Clave Candidata es un atributo o conjunto de atributos que identifican unívocamente cada tupla de la relación. De todas las Claves candidatas, elegiremos una que será la Clave Principal o clave primaria.
3.- RESTRICCIONES
Igual que en otros modelos, existen restricciones, estructuras u ocurrencias no permitidas.
Tipos de restricciones:
• INHERENTES: impuestas por el propio modelo.
• DE USUARIO (semánticas): en las cuales es el usuario quien prohíbe, porque el modelo se lo permite en determinadas circunstancias.
3.1.- Restricciones INHERENTES
Impuestas por el propio modelo. Consideramos las siguientes: • Valores atómicos: cada valor de la tabla ha de ser simple. • Tuplas distintas: no puede haber dos tuplas iguales.
• Clave Principal: ha de existir una clave principal o primaria que identifique de forma unívoca las tuplas. (por tanto, no podrá coger valores nulos y tampoco podrá repetirse).
3.2.- Restricciones de Usuario (SEMÁNTICAS)
Condiciones que ponemos para que el esquema de la BD, refleje lo mejor posible la realidad.
(a) Restricción de Dominio:
• El valor de un atributo ha de ser un valor atómico. Definiendo claramente el dominio, no aseguramos que el atributo no puede coger valores incorrectos.
• El dominio será de un tipo determinado, eligiendo de una gama extensa: entero corto, entero, entero largo, real, cadena de caracteres,...
• EMPLEADO (DNI: entero(8); Nombre: carácter(30); Dirección: carácter(30);Teléfono: entero(9); Sueldo: entero(6); Fecha_n: fecha) (b) Restricción de Clave:
Permite declarar uno o varios atributos como clave Principal o Primaria (Primary Key).
“La obligatoriedad de declarar una clave principal era una restricción inherente, pero lo que es una restricción de usuario es la elección de la clave principal, y la consecuencia de que no podrá coger valores nulos ni repetidos”. Aunque estas características también las puede tener otros atributos:
(c) Integridad Referencial:
Veamos un ejemplo:
Si en la tabla Familiar, tenemos un atributo Dni_emp) que es clave (primaria o candidata) de otra tabla (Empleado ==> DNI), todo valor de aquel atributo ha de concordar con un valor de la clave de Empleado (no podremos poner en familiar un Dni que no tenga ningún empleado en la empresa). Por lo tanto, Dni_emp es una
Clave Externa (clave ajena).
Las relaciones no tienen por qué ser distintas, pueden ser la misma. Así, si consideramos el supervisor, este ha de ser de la empresa.
• EMPLEADO ( Dni, Nombre, Dirección, Teléfono, Sueldo, Fecha_n, Supervisor)
• Supervisor es una clave externa, pero de la misma tabla. Representación de claves externas:
• EMPLEADO (Dni, Nombre, Dirección, Teléfono, Sueldo, Fecha_n)
• FAMILIAR (Dni_emp, Nombre, Parentesco)
Esto impedirá que introduzcamos valores no correctos o inexistentes. ¿Qué pasaría si borramos un empleado, o modificamos su Dni? ¿Qué hacemos con sus familiares? Podríamos hacer tres acciones:
• No dejar borrarlo o modificarlo (NO ACTION)
• Borrar también los familiares o cambiarlos en cascada (CASCADE) • Cambiar el valor de la clave externa al valor nulo o un valor
(d) Otras restricciones:
Otros SGBD, más potentes, permiten restricciones consistentes en comprobar una determinada condición después de una actualización:
• Verificación (CHECK): si la condición no se cumple después de la actualización, se deshace. Sirve muy bien para definir un dominio, entre otras cosas. Por ejemplo:
CHECK Sueldo >0
CHECK (Año(Fecha_n)<Año(Hoy)) and ((Año(Hoy)-Año(Fecha_n))<65)
• Disparador (TRIGGER): si se cumple la condición se ejecuta un procedimiento definido por el usuario. (Este concepto es más potente, ya que da una respuesta procedimental, donde se puede hacer cualquier cosa).
4.- TRANSFORMACIÓN DEL M E/R AL RELACIONAL.
4.1.- Entidades.
Entidades Normales:Toda entidad normal se transformará en una tabla, con todos sus atributos, que se consideran como simples. Se tría uno o varios como clave principal, y lo denotaremos subrayándolo.
• EMPLEADO (Dni, Nombre, Dirección, Teléfono, Sueldo, Fecha_n) • DEPARTAMENTO (Num_d, Nom_d)
• PROYECTO (Num_p, Nom_p)
clave externa de S, marcándolo con un doble subrayado. Si además, la dependencia es en Identificación, la clave externa formará parte de la clave principal.
FAMILIAR (Dni_e, Nom_f, Fecha_n, Parentesco)
4.2.- Relaciones 1:1
Por cada relación 1:1, que afecta a dos entidades S y T, se triará una de ellas, S, donde se incluirá como a clave externa la clave principal de T.
Siempre es conveniente elegir que S participe de forma total, es decir, que todas sus ocurrencias participan en la relación, y por tanto la cardinalidad mínima y máxima sea (1,1) -por cada ocurrencia de S hay como mínimo y como máximo una de T-
Entonces quedaría:
DEPARTAMENTO (Num_d, Nom_d, Director, Fecha)
Es mejor elegir la de participación total porque todos los departamentos tienen director, pero no todos los empleados son directores. (p.e.: si pusiéramos EMPELADO (Dni, ..., Dep_que_dirige), muchas veces estaría vacío).
4.3.- Relaciones 1:N
Por cada relación 1:N, que no sea una relación débil, entre S y T, donde S participa con un grado de cardinalidad 1, y T con un grado N, se incluye como una clave externa en T la clave principal de S, además de todos los atributos de la relación.
En el ejemplo:
• Para la relación pertenece incluiremos el atributo Departamento a EMPLEADO.
• Para la relación controla incluiremos el atributo Departamento a PROYECTO.
• Para la relación supervisa incluiremos el atributo Supervisor a EMPLEADO (es reflexiva).
• EMPLEADO (Dni, Nom-e, Dirección, Teléfono, Sueldo, Fecha-n, Departamento, Supervisor)
• PROYECTO (Num-p, Nom-p, Departamento)
4.4.- Relaciones M:N
Por cada relación M:N construiremos una nueva tabla, donde se incluirán como una clave externa las claves principales de las dos Entidades, y además su combinación constituirá (o formando parte de ella) la clave principal.
TRABAJA (Dni, Num-p, Horas)
4.5.- Relaciones Ternarias
En una relación ternaria o superior construiremos una nueva tabla, donde incluiremos como clave externas las claves principales de todas las entidades, además de los atributos de la relación.
Ejemplo:
• DEPARTAMENTO (Num-d, ...) • ARTICULO (Cod-art, ...) • PROVEEDOR (Cod-pr, ...)
• COMPRA (Num-d, Cod-art, Cod-pr, cantidad) • Esquema lógico del Ejemplo: EMPRESA
5.- ALGEBRA RELACIONAL
Hasta ahora hemos diseñado una B.D. Relacional, pero para lo que realmente queremos una B.D. es para consultarla, y obtener la información que nos interesa, manipularla.
Por medio del ALGEBRA RELACIONAL, haremos operaciones sobre las tablas, combinándolas, seleccionando lo que nos importa,..., en definitiva manipulándola. El resultado será una nueva tabla, que será el resultado final o servirá para hacer otra operación. Se dividirán en dos grupos:
• UNARIAS: afectan a una sola tabla. • BINARIAS: afectan a dos tablas.
5.1.- Operaciones UNARIAS.
1) Selección (select)Selecciona un subconjunto de las tuplas, aquellas que cumplen una condición determinada. Lo representaremos:
Por ejemplo:
S (EMPLEADO, (Sueldo > 200.000))
S (FAMILIAR, (Parentesco = ‘Hijo’ o Parentesco = ‘Hija’))
Como podemos observar el grado de la tabla resultante es el mismo, y el número de tuplas menor o igual.
El caso de encadenar selecciones, es el mismo que poner una condición compuesta: S ( S (R, (cond1)), (cond2) ) ≡ S (R, (cond1 i cond2))
Ejemplo.- Tenemos una relación R, compuesta port res atributos (A, B, y C), donde aplicamos una Selección para mostrar aquellas tuplas cuyo atributo A es mayor que 2. R S(R, (A>2)) A B C A B C 4 2 0 S(R, (A>2)) 4 2 0 3 5 7 3 5 7 0 0 3 2) Proyección
Selecciona un subconjunto de atributos (columnas), lo representaremos como:
P (tabla, (atributos)) o también π atributos (tabla)
Ejemplo:
P (EMPLEADO, (Nombre, Sueldo))
El grado será menor o igual. Pero el número de tuplas también puede ser menor o igual.
Las operaciones de selección y proyección se pueden encadenar: P ( S (EMPLEADO, (Departamento = 5), (Nombre, Sueldo))
Ejemplo P (R, (B,C)) B C 2 0 5 7 0 3
5.2.- Operaciones BINARIAS
Afectaran a dos tablas. En las dos primeras operaciones, Unión y Diferencia, las dos tablas han de tener los mismos atributos. En la otra, el Producto Cartesiano, no. 1) Unión (R U T)
En el resultado estarán las tuplas de R y las de T (si alguna estuviera en las dos, no se ha de repetir).
Ejemplo:
P ( S(EMPLEADO, (Departamento = 5)), (Dni)) U P ( S(EMPLEADO, (Departamento=5)), (Supervisor))
Mientras que los atributos no son iguales, sus dominios son compatibles, y por tanto se puede hacer la unión.
Ejemplo T R U T A B C A B C 0 0 3 R U T 4 2 0 4 5 0 3 5 7 3 3 7 0 0 3 2 1 1 4 5 0 3 3 7 2 1 1
2) Diferencia (R - T)
El resultado contendrá las tuplas que pertenecen a R y no pertenecen a T. Ejemplo R - T A B C 4 2 0 3 5 7 3) Producto Cartesiano (R x T)
Aquí no cabe que los atributos sean iguales. Tendremos los atributos de R y de T, y las tuplas serán todas las posibles combinaciones de las tuplas de las dos tablas.
Es decir, la primera de R combinada con todas las de T, la segunda de R combinada con todas las de T, ...
Si R tiene m filas y T tiene n, el producto cartesiano tendrá m* n. Ejemplo:
S ( R x T , (Num-d = Departamento))
Entonces tendremos una cosa muy útil, que es la información de los familiares con la del empleado. Esto es una cosa habitual que definiremos una operación que realiza exactamente esto reunión o join.
Ejemplo V R x V A B R.A R.B R.C V.A V.B 1 2 R x V 4 2 0 1 2 3 1 4 2 0 3 1 3 5 7 1 2 3 5 7 3 1 0 0 3 1 2 0 0 3 3 1
5.3.- OPERACIONES DERIVADAS.
Las operaciones que veremos ahora se pueden deducir de las anteriores.
1) Intersección (R ∩ T)
Entrarán en el resultado todas la tuplas presentes en las dos relaciones. R y T han de tener los mismos atributos (mejor dicho, atributos de dominios compatibles)
Esta operación es equivalente a: R ∩ T = R - (R-T) Ejemplo
R ∩ T
A B C
0 0 3
2) División (R/T)
R actúa como dividendo, y T como divisor. Se ha de cumplir que los atributos de T sean un subconjunto (estricto) de los de R. Llamaremos t a los atributos de T y ra los de R, y se cumple que t < r. Llamaremos q a los atributos de R que no pertenecen a T, es decir, q = r - t.
El resultado de la división tendrá los atributos q, y aparecerá una tupla fq cuando,
por cualquier tupla de T ft existe una tupla de R fr que es la combinación de la de T y
la del resultado: fr = ft + fq
La utilización de la división es muy ocasional, en consultas del estilo del ejemplo (trabajadores que trabajan en todos los proyectos,...)
Ejemplo
Z R / Z
A B C
3) Reunión (R * T)
La operación de REUNIÓN (JOIN), también llamada unión natural, es de las más importantes del álgebra relacional.
Es el resultado de realizar el producto cartesiano entre las dos tablas, y después aplicar una selección preestablecida que se denomina selección o condición de la reunión.
La condición puede ser cualquiera, pero habitualmente será una igualdad entre dos atributos del mismo nombre (o mismo dominio).
La reunión más utilizada será una reunión entre dos tablas con la condición de igualdad entre la clave de una, y la clave externa de otra (que hace de referencia a la primera). En estos casos no cabe que estén presentes los dos atributos que se utilizan para la igualdad, con uno es suficiente. Esta reunión es la REUNIÓN NATURAL.
Ejemplo
Y R U T
A B C D R.A R.B=Y.B R.C Y.A Y.C Y.D
5 7 3 2 R * Y(R.B=Y.B) 3 5 7 4 6 3
4 5 6 3 3 5 7 6 0 1
2 0 3 1 0 0 3 2 3 1
EJERCICIOS
1.- Viviendas
PERSONA
(DNI, Nombre, CodVivienda,…, DNICabFamilia, CodMunicipio)VIVIENDA
(CodVivienda, Descripcion,… DNIPropietario)MUNICIPIO
(CodMunicipio, Descripcion)2.- Universidad
AREACONOCIMIENTO
(CodArea, Descripcion)DEPARTAMENTO
(Codigo, Nombre, CodArea)UBICACIÓN
(CodDep, CodFac)FACULTAD
(CodFac, Descripcion,…)CATEDRA
(CodCatedra, Nombre,…, CodFac)ADSCRIPCION
(CodProf, CodCatedra)3.- Red Bancaria
BANCO
(CodBanco, Nombre, Direccion,…)SUCURSAL
(CodSuc, Descripcion, CodBanco)CTACTE
(CodCta, Descripcion, CodSuc)POSESION
(CodCta, CodPri, DNICli)CLIENTE
(DNICli, Nombre,…)PRIVILEGIO
(CodPri, Descripcion)PRESTAMO
(CodPrestamo, Descripcion,…)CONCESION
(CodPrestamo, CodSuc, DNICli)4.- Mundial de futbol
JUGADOR
(Nombre,…, CodEquipo)EQUIPO
(CodEquipo, Nombre,…)INVOLUCRA
(CodPartido, Equipo1, Equipo2)PARTIDO
(CodPartido, Descripcion,…)INTERVIENEN
(CodPartido, CodFuncion, CodArb)FUNCION
(CodFuncion, Descripcion)5.- Mariposas
COLECCIÓN
(CodColeccion, Nombre,…)PERSONA
(DNI, Nombre,…)EJEMPLAR
(CodEjemplar, Descripcion,…, CodEspecie)POSEE
(CodColeccion, DNI, CodEjemplar)ESPECIE
(CodEspecie, Descripcion,…, NombreCientifico, CodGenero)NOMBRE_CIENTIFICO
(NombreCientifico)ZONA
(CodZona, Nombre)NOMBRE_COMUN
(CodNombreComun, Nombre)TIENE
(CodEspecie, CodZona, CodNombreComun)GENERO
(CodGenero, Descripcion,…, CodFamilia)FAMILIA
(CodFamilia, Nombre)6.- Biblioteca
LIBRO
(Titulo, ISBN, Idioma, NumEd, FechaEdicion, CodTema,CodEditiorial)
TEMA
(CodTema, Descripcion)EDITORIAL
(CodEditorial, Nombre)AUTOR
(DNI, Nombre)ESCRITOR
(DNI, ISBN)EJEMPLAR
(NumEjemplar, ISBN, DNILector, FechaPrestamo)LECTOR
(DNI, Nombre, Direccion, Telefono)7.- Red informática
RED
(NumRed, Nombre, DNIResp)CONEXIÓN_RED
(NumRed1, NumRed2)EMPLEADO
(DNI, Nombre,…)ORDENADOR
(NumEquipo, Descripcion, Caracterisitcas,…)CONEXIÓN_ORD
(NumEquipo, NumRed)UTILIZA
(DNIEmp, NumEquipo)MODEM
(NumModem, Descripcion, NumEquipo)IMPRESORA
(NumImpr, Descripcion, NumEquipo)APLICACIÓN
(CodApli, Descripcion, NumEquipo)PERMISO
(CodPermiso, Descripcion)ASIGNADO
(CodPermiso, CodApli, DNIEmp)8.- Viajantes
CIUDAD
(Codigo, Nombre,…, CodSector)SECTOR
(CodSector, Nombre,…)VIAJANTE
(DNIViajante, Nombre,…)ASIGNADO
(CodSector, DNIViajante)PRODUCTO
(CodProd, Descripcion,…)CLIENTE
(CodCli, Nombre)9.- Vuelta ciclista
EQUIPO
(NomEquipo, Director)CICLISTAS
(Dorsal, Nombre,…, NomEquipo)ETAPAS
(Numero, Km, CiudadSal, CiudadLLeg, NumDorsalGanador)PUERTO
(NombrePuerto, Categoria, Altura, EtapaNum, NDorsalGanador)CIUDAD
(Nombre, ReseñaHistorica, Cultural, Economica)MAILLOTS
(Tipo, Color, Premio)LLEVA
(Dorsal, NumEtapa, CodigoMaillot)10.- Alquiler de vehículos
CLIENTES
(CodCliente, DNI, Nombre, Direccion, Telefono, CodAvalista)RESERVA
(NumReserva, FechaFin, FechaInicio, Precio, Litros, PrecioTotal,Entregados, CodCliente, CodAgencia)
AGENCIAS
(CodAgencia, Descripcion)COCHES
(CodCoche, Matricula, Descripcion, NumResersva, CodGaraje)11.- Olimpiadas
SEDE
(CodSede, Descripcion, Presupuesto, NumComplejos)COMPLEJO
(CodComplejo, Nombre, Jefe, Localizacion, AreaTotal,CodSede)
UNIDEPORTIVO
(CodComplejo, DatosUnideportivo,…)POLIDEPORTIVO
(CodComplejo, DatosPolideportivo,…)AREA
(CodArea, Nombre, Localizacion, CodComplejo)EVENTO
(CodEvento, Descripcion, Fecha, Equipamiento, duración,NumParticipantes, NumComisarios)
PARTICIPA
(CodEvento, CodComisario)COMISARIO
(CodComisario, Nombre)JUEZ
(CodComisario, DatosJuez,…)OBSERVADOR
(CodComisario, DatosObservador,…)12.- Ejercicios de Algebra Relacional
Dado las relaciones R, T y V, realizar las siguientes operaciones de Álgebra Relacional: R T V A B C A B C A B C D 6 0 2 5 4 0 5 0 2 6 1 3 0 4 5 2 0 2 5 5 6 1 6 0 4 2 1 6 7 6 0 3 0 5 4 5 7 2 3 3 2 0 3 1
Solución. T-R (T - R) * V ((T - R).B = V.B) A B C (T-R).A (T - R).B = V.B (T-R).C V.A V.C V.D 4 5 2 2 5 5 1 6 0 5 2 5 5 6 0 7 7 3 3 1 1
13.- Ejercicios de Algebra Relacional
Con las mismas relaciones que en el ejercicio anterior R, T y V, realizar la siguiente operación: P ( S( (R U T), (A > 3) ) x T, ( A, T.B ) ) R U T S( (R U T), (A > 3) A B C A B C 6 0 2 5 4 5 2 4 0 5 0 2 5 5 2 6 1 3 1 6 0 6 5 4 5 4 0 2 5 2 3 1 6
S( (R U T), (A > 3) ) x T A B C T.A T.B T.C 6 5 4 5 6 5 4 5 6 5 4 5 6 5 4 5 4 0 2 5 4 0 2 5 4 0 2 5 4 0 2 5 2 3 1 6 2 3 1 6 2 3 1 6 2 3 1 6 0 0 0 0 4 4 4 4 5 5 5 5 2 2 2 2 0 0 0 0 2 2 2 2 5 5 5 5 5 5 5 5 6 6 6 6 1 1 1 1 6 6 6 6 0 0 0 0 P ( S( (R U T), (A > 3) ) x T, ( A, T.B ) ) A T.B 6 5 4 6 5 4 6 5 4 0 0 0 2 2 2 5 5 5
14.- Ejercicios de Algebra Relacional
Obtener las relaciones Y y Z, que aplicando las operaciones siguientes S ( P ( Y, (A, B, D)), (D<4)) - Z nos de el siguiente resultado:
S ( P ( Y, (A, B, D)), (D<4)) Z A B D A B D 4 3 1 6 9 1 0 2 1 6 1 5 1 8 1 P ( Y, (A, B, D)) Y A B D A B C D E 4 3 1 1 2 6 9 1 7 2 0 2 1 5 4 4 3 1 1 2 6 9 1 7 2 2 1 1 3 7 0 2 1 5 4 2 2 7 4 2
EJERCICIOS PROPUESTOS
1.- Modelo Relacional
Realizar el Modelo Relacional de los ejercicios propuestos en el tema anterior (Modelo Entidad Relación).
2.- Algebra Relacional
Con las relaciones T, R y V realizar la siguiente operación: ( P ( T, (A, C, D) U R ) x S ( V, (B < 8) ) T R V A B C D A B C A B 8 8 3 4 4 6 2 8 5 5 1 3 0 0 0 2 1 4 9 3 3 4 8 2 6 4 6 2 9 3 5
3.- Algebra Relacional
Obtener las relaciones, T, Z, V y R, dónde aplicando la siguiente operación nos de el resultado: S ( T x Z, (A > 5)) – P (V U R, (A, B, C)) A B C 8 8 6 6 4 2 4 2 3 1 3 1
4.- Algebra Relacional
A partir de las siguientes relaciones R, T y V realizar la siguiente operación: P (R * T (R.B = T.B) / V, (A, B)) U S (R / V, (A > 3)) T R V A B C D A B C C 4 5 0 3 2 6 8 2 0 9 0 3 7 5 0 1 5 7 5 1 8 5 3 8 3 2 6 2 3 4 0 4 2 4 4
5.- Algebra Relacional
Inventarse los siguientes 3 ejercicios:
a) Proponer un ejercicio de Álgebra Relacional con 3 operaciones con las relaciones que creas oportunas (una de esas operaciones tiene que ser una Reunión).
b) Lo mismo que el anterior, pero con 5 operaciones (una de ellas tiene que ser una resta, y otra un producto cartesiano).
c) Partiendo de la relación resultado final (la relación que obtenemos al final), que sea el resultado de 4 operaciones de álgebra relacional, obtener las tablas originales (una de esas operaciones relacionales tiene que ser una división).
Tema 4.
LENGUAJE DE DEFINICIÓN DE DATOS
1.- Introducción
Una vez que ya hemos diseñado nuestro diseño Lógico de la Base de Datos (Modelo Entidad / Relación y Modelo Relacional), pasamos a crear el diseño Físico. En primer lugar, tendremos que decidir qué Sistema Gestor de Bases de Datos utilizaremos. En nuestro caso, será el SQL Server de Microsoft.
Para poder explicar el diseño Físico, utilizaremos el ejemplo de una Clínica, dónde se quiere obtener información de los Pacientes que van a realizar visitas a la clínica, y también de las Visitas que se hacen a la misma.
El Modelo e-r de la Clínica sería el siguiente:
De dicho modelo podemos deducir que 1 paciente puede realizar MUCHAS visitas, pero que 1 visita sólo será realizada por 1 paciente, y de ahí la relación 1:N entre ambas entidades.
Partiendo de este modelo e-r obtendríamos el siguiente Modelo Relacional: PACIENTES (DNI, Nombre, Dirección, CP, Ciudad, FechaNac) VISITAS (Nvisita, Fecha, DniPaciente, Concepto, Importe)
Pero para pasar al diseño Físico, todavía nos hace falta un poco más de información, como por ejemplo, el tipo de datos que corresponde a cada atributo, las restricciones que podemos encontrarnos, los valores predeterminados, … Por este motivo necesitamos la siguiente información adicional:
PACIENTES
Tipo de datos ObservacionesDNI Alfanumérico (9) Se podrá guardar números (8) y texto (1) para la
letra. Además, el campo será clave principal de la tabla.
Nombre Alfanumérico (40) Dejaremos 40 caracteres para el nombre del
paciente. Dicho nombre no podrá quedarse vacío, y se deberá rellenar obligatoriamente.
Dirección Alfanumérico (50) Este campo sí que puede quedarse vacío
CP Alfanumérico (5) Se guardará como alfanumérico y no como texto,
porque algunos códigos postales empiezan por 0, y tampoco necesitamos realizar operaciones con dicho campo. Puede contener valores nulos
Ciudad Alfanumérico (15) Puede contener valores nulos, y por defecto, para los registros nuevos almacenará el valor de Alicante.
FechaNac Fecha Se puede dejar vacío.
VISITAS
Tipo de datos ObservacionesNvisita Numérico Guardará un número correlativo, y cada visita tendrá
un número distinto. Además, será clave principal de la tabla.
Fecha Fecha No podrá dejarse vacío.
DniPaciente Alfanumérico (9) Del mismo tipo que el campo con el que está
relacionado de la tabla Pacientes. No podrá dejarse vacío.
Concepto Alfanumérico (200) Contendrá el motivo por el cual se realiza la visita. Tampoco podrá dejarse vacío.
Importe Moneda Campo monetario, que recogerá el importe que el
paciente debe de abonar. Dicho importe no puede ser inferior a 50 €, y tampoco podrá dejarse vacío.
Para poder empezar a crear el diseño Físico de nuestra base de datos, necesitamos conocer con más detalle los tipos de datos que dispone SQL Server para la definición de datos.
Sólo veremos los que nos resulten más importantes para nuestro diseño y los que consideremos básicos para el diseño de bases de datos.
1.1.- Tipos de datos
Los tipos de datos más utilizados son: a) NUMÉRICOS
Dentro de los datos numéricos podemos distinguir varias categorías: ENTEROS
Almacenan números sin decimales. Incluyendo los positivos (1, 2, 3,…), el 0 y los negativos (-1, -2, -3,…). Básicamente utilizaremos 4 tipos de datos:
Tipo de datos Almacenamiento Mínimo y máximo
Bit 1/8 de byte 0 a 1
Tinyint 1 byte 0 a 255
Smallint 2 bytes -32.768 a 32767
Int 4 bytes -2.147.483.648 a 2.147.483.647
El tipo de datos BIT se utiliza para campos de tipo verdadero/falso, estado,…Hay que tener en cuenta que este tipo de datos puede tener el valor nulo, con lo que se puede encontrar en tres estados: 0, 1 y nulo.
Hay que tener en cuenta también que el tipo de datos TINYINT no permite números negativos.
DECIMALES y MONETARIOS
Los números decimales servirán para almacenar valores con parte decimal. La información contable y financiera se expresará con los tipos de datos monetarios. Los más importantes son:
Tipo de datos Espacio Precisión Escala Mínimo y máximo
Decimal 5 bytes 9 0-5 -999.999.999 a 999.999.999
Numeric 9 bytes 19 0-9 -1019+1 a 1019-1
Money 8 bytes Aprox. 18 4 -922.337.203.685.477,5808 a
922.337.203.685.477,5807
Smallmoney 4 bytes Aprox. 9 4 -2.147.483.648 a 2.147.483.647
La Precisión es el número máximo de dígitos que se almacenan en el tipo de datos. Por ejemplo, el número 21.543,98 tendrá siete dígitos. En la precisión se tendrá en cuenta todos los dígitos antes y después de la coma decimal. La Escala es el número
b) CADENAS o ALFANUMÉRICOS
En este tipo de datos se guardara informaciones tales como direcciones, nombres, conceptos,… Este tipo de datos almacenará letras, números y símbolos. Los tipos de datos cadena son:
Tipo de cadena Carácter Unicode
Fijo Char nChar
Variable Varchar nVarchar
Los tipos de datos Unicode están diseñados para el texto y símbolos de los diferentes idiomas (árabe, chino, japonés,…). Los tipos de datos Carácter se utilizan para cadenas empleadas en páginas de códigos específicas. Los tipos Carácter son más eficientes, ya que representan un byte por carácter, mientras que los Unicode utilizan dos por carácter.
Los tipos de datos Fijo, utilizan una cantidad de espacio fija, independientemente del valor del dato. Por ejemplo, si tengo una campo definido como Char(12), y la palabra que voy a guardar es “Antonio”, aunque tenga 7 letras en el campo se guardarán los 12 definidos en el tipo de datos.
Así, los tipos de datos de longitud Variable, ajustan su espacio de almacenamiento al valor que del campo, pero esto requiere dos bytes adicionales para controlar la longitud del valor. En el ejemplo anterior, para almacenar la palabra “Antonio”, emplearíamos 9 bytes, 2 para controlar la longitud y 7 para la palabra.
Los tipos de datos Char y VarChar soportan hasta 8.000 caracteres por campo, y los Unicode la mitad (hasta 4.000 caracteres).
Si queremos almacenar textos largos en estos tipos de datos cadena, utilizaremos VarChar (max), para cuando el tamaño supere los 8.000 bytes. Estos datos pueden almacenar hasta 2.147.483.647 caracteres (2 gigabytes).
c) FECHA y HORA
Muchas veces nos encontraremos con la necesidad de guardar atributos de fecha hora (fecha de la factura, fecha y hora de una visita,…). Para ello contamos con dos tipos de datos.
Tipo Tamaño Máximo y mínimo
DateTime 8 bytes 1-ene-1753 a 31-dic-999
2.- Creación y Administración de tablas (modo
gráfico)
Una vez ya definido el modelo e-r y el modelo relacional, junto con los tipos de datos de cada atributo, estaremos en disposición de implementar en un sistema gestor de base de datos las distintas tablas.
Para ver su funcionamiento, utilizaremos la herramienta SQL Management Studio, que pone a nuestra disposición SQL Server.
Veremos cómo crear gráficamente las tablas Pacientes y Visitas, de nuestra base de datos Clínica.
Para crear la Base de datos Pacientes, pulsaremos botón derecho sobre Base de Datos, y seleccionaremos Nueva Base de Datos…
Nos aparecerá el siguiente cuadro de texto, donde escribiremos el nombre de la base de datos: CLINICA.
