Descarga la aplicación para disfrutar aún más
Esta es una vista previa del archivo. Inicie sesión para ver el archivo original
Base de Datos/Ejercicios/01 - Modelo Entidad_Relación.pdf Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 A partir de los siguientes requisitos identificar los conjuntos de entidades y sus atributos, así como conjuntos de relaciones y cardinalidades y representarlos mediante un diagrama entidad relación. 1) Una organización bancaria necesita gestionar mediante una base de datos información relativa a diferentes aspectos de la gestión de los principales aspectos de su estructura y los productos que comercializa. El banco está organizado en sucursales. Cada sucursal está ubicada en una ciudad, está identificada por un código único y tiene asociada una denominación. Los clientes del banco se identifican mediante número de identificación fiscal. El banco almacena cada nombre de cliente y dirección. Los clientes pueden tener cuentas y pueden solicitar préstamos. Cada cliente tiene al menos una cuenta. Los empleados del banco se identifican mediante un identificador de empleado único. La administración del banco almacena el nombre y número de teléfono de cada empleado, los nombres de los subordinados del empleado, y el identificador de empleado del jefe del empleado. El banco también mantiene registro de la fecha de comienzo del contrato del empleado. Cada cliente tiene asignado un empleado en el banco como asesor personal. El banco ofrece dos tipos de cuentas: cuentas de ahorro y cuentas corrientes. Las cuentas pueden asociarse a más de un cliente y un cliente puede tener más de una cuenta. Cada cuenta tiene asignado un único número de cuenta. El banco mantiene un registro del saldo de cada cuenta y la fecha más reciente en que la cuenta fue accedida por cada cliente titular de dicha cuenta. Un préstamo tiene lugar en una sucursal particular y puede estar asociado a uno o más clientes. Un préstamo se identifica mediante un identificador de préstamo único. Para cada préstamo el banco mantiene registro del importe del préstamo y de los pagos del préstamo. Aunque un número de pago del préstamo no identifica de forma única un pago entre todos los préstamos del banco, un número de pago identifica un pago particular para un préstamo específico. Para cada pago se almacenan la fecha y el importe. 2) Los ayuntamientos de los municipios de nuestra comunidad autónoma desean mantener información actualizada de las viviendas ubicadas en zonas urbanas. Se desea diseñar una base de datos que incluya las características de las viviendas, su ubicación, propietarios, etc. A finales de año, el ayuntamiento de cada municipio debe cobrar a cada propietario un impuesto por las viviendas que son de su propiedad en la actualidad. Para ello, deberá emitirse un recibo para cada vivienda donde figure el nº de registro catastral de la vivienda, la dirección donde se ubica la vivienda (calle, numero y piso), el número de metros cuadrados y el DNI y nombre del propietario (aunque la propiedad de una vivienda puede ser compartida por varias personas, a efectos de cobro de impuestos consideramos sólo a uno de ellos), además del importe de impuesto. Este Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 recibo se le remitirá a la dirección del propietario, que por supuesto no tiene por qué coincidir con la de la vivienda de la que debe pagar el impuesto. El propietario puede realizar el pago del impuesto de dos maneras: en efectivo o a través de la cuenta bancaria que el propietario haya indicado al ayuntamiento; en este último caso en el recibo figurará la cuenta de cargo. El importe del impuesto de cada vivienda depende de múltiples factores que deben considerarse en su cálculo. Entre ellos están el municipio y la zona urbana donde se ubica la vivienda, los m2 de la vivienda y el precio de tasación de la vivienda. A estos efectos cada provincia consta de una serie de municipios, de los que hay que mantener su nombre, el área y perímetro y la provincia a la que pertenecen; además para identificar cada municipio se utiliza un código único a nivel regional. Cada municipio está dividido en una serie de zonas urbanas claramente delimitadas. A la hora de calcular el impuesto debe usarse el precio medio del m2 en esa zona urbana. 3) La Dirección General de Tráfico desea mantener cierta información del parque de vehículos nacional con el fin de realizar la gestión de las infracciones de tráfico. En una primera fase se desea recopilar información acerca de las marcas y modelos que existen en el mercado. Cada marca proporciona la dirección social en España. Así mismo para cada marca se recogen los nombres de modelos de vehículos disponibles y la potencia de cada uno. Cada marca se codifica con un código y a cada modelo le corresponde un código proporcionado por la marca. Cuando un vehículo nuevo se matricula se registra la información de la marca y el modelo del coche, bastidor, fecha de matriculación, así como los datos del propietario. De éste deben conocerse: nif, apellidos, nombre, fecha de nacimiento y domicilio completo (calle, nº, municipio, provincia y código postal). Hay que tener en cuenta que en la DGT se desea mantener información actualizada del propietario, por lo que si en algún momento se produce un cambio de propietario debe actualizarse éste en la base de datos, sin perder información de la historia de los propietarios anteriores junto con las fechas que indican el período de propiedad, por si acaso se necesitan para tramitar multas antiguas. Por otra parte, cuando una persona comete una infracción y se le impone una multa, el agente toma nota de una serie de datos. En primer lugar los datos de la persona infractora: nif, nombre, apellidos, fecha de nacimiento y domicilio completo (calle, nº, municipio, provincia y código postal). Si en la infracción ha intervenido un vehículo, se necesitan además, los datos de su matrícula, marca y modelo del vehículo. Hay que señalar que las multas se imponen a personas, no a vehículos, ya que por ejemplo, podría imponerse una multa a un peatón o a un ocupante de un vehículo. También deben constar en la multa la fecha, el número de registro personal del agente que ha puesto la multa, el artículo que ha infringido la persona infractora, el lugar exacto donde ha ocurrido la infracción (carretera, kilómetro concreto y dirección) y el importe de la multa. De los agentes tan solo se registra la información correspondiente a la unidad a la que pertenece y su número de registro personal. Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 Cada infracción cometida se identifica con un número de expediente único y da lugar a una única multa. 4) La crisis económica ha llevado a los gestores de sanidad a tomar la decisión de analizar con lupa el gasto farmacéutico de los ciudadanos. Para ello han encargado un nuevo sistema de gestión de prescripciones médicas que requerirá el diseño de una nueva base de datos. A continuación se indica los principales requerimientos del sistema sanitario actual que deben ser consideradas a la hora de llevar a cabo dicho diseño. Los usuarios del sistema sanitario (pacientes) se identifican mediante su número de afiliación a la seguridad social. Además de este dato se necesita disponer del nombre, la dirección y la edad de los mismos. Los médicos se identifican por su número de colegiado y también se requiere conocer su nombre y años en activo. Los médicos pueden ser especialistas, de atención primaria o de urgencias. Los médicos de urgencias prestan su servicio en un hospital que necesitamos conocer. De los especialistas se registrará su especialidad y de los médicos de atención primaria el centro de salud en el que atienden a sus pacientes y la hora de inicio de la consulta. Los centros de salud se identifican por un código único. También se conoce la dirección de cada uno de los centros. Todos los pacientes tienen asignado un médico de atención primaria específico, sin embargo los especialistas o médicos de urgencias que le atenderán en caso de requerirlo podrán variar de una consulta a otra (es decir, no tienen asignado un traumatólogo, cardiólogo… específicos). Cualquier médico puede prescribir medicamentos a los pacientes mediante una receta. El sistema de gestión necesitará disponer de cierta información que se incluye en las mismas: el paciente al que se le ha prescrito el medicamento, el código de identificación de la receta, la fecha en la que se realizo la prescripción, el medicamento, las unidades prescritas del mismo, el médico que la firma y la farmacia donde se dispenso. Las compañías farmacéuticas, cuyos productos pueden ser prescritos, se registrarán en el sistema con su CIF y razón social. Cada medicamento se identificará con su código de barras y se almacenará la información asociada a los mismos (nombre comercial, composición, precio y la compañía farmacéutica que lo comercializa). Los medicamentos se dispensan en las farmacias de las que se conoce el número de licencia, la dirección y número de teléfono. Todas las farmacias dispensan todos los medicamentos de las diferentes compañías autorizadas. 5) Necesitamos una base de datos para gestionar una empresa de preparación de comidas. En la empresa trabajan empleados de los que se necesita saber su nombre, número de la SS, puesto que ocupan (cocinero, pinche, limpiador…) y al mando de que empleado están. La empresa elabora diferentes platos, cada uno de los cuales tiene un código, una descripción (cocido madrileño, torrijas, conejo a la cazadora…) y un precio. Para la elaboración de cada plato se necesita saber los ingredientes necesarios (identificables Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 mediante su nombre), así como la cantidad de cada uno de ellos y dónde encontrarlos en las cocinas. Los ingredientes se encuentran dispuestos en diferentes ubicaciones que se identifican mediante un nombre (cámara frigorífica A, cámara frigorífica B, despensa principal, congelador…) y están divididas en estantes numerados de los que se debe conocer su capacidad. Naturalmente, dos ubicaciones distintas pueden tener estantes con la misma numeración. Además, necesitamos saber que empleados son capaces de preparar cada plato (al menos uno). Todos los empleados deben ser capaces de participar en la elaboración de al menos un plato y no exceden de ocho. También registraremos a los clientes de los que se sabrá su número de teléfono, email, así como los pedidos que han realizado, los cuales se identifican mediante un número. Cada pedido contendrá la fecha en la que se realizó, los platos encargados y la cantidad de cada uno de ellos. 6) Una empresa de logística desea implementar una base de datos relacional que cumpla los siguientes requisitos: La empresa logística ofrece sus servicios de transporte a diferentes empresas bajo un contrato que se suscribe durante un periodo de tiempo. Un contrato se identifica por su referencia, y puede incluir varios trayectos (al menos uno) indicando la fecha de cada trayecto y el vehículo concreto que lo va a realizar. Los trayectos son identificados por un origen y un destino y se conoce el número de Km. La empresa de logística cuenta con vehículos de transporte que pueden cubrir determinados trayectos. Un vehículo tiene una matrícula y un tipo (marca). Cada trayecto está cubierto por al menos un vehículo y como mucho por cuatro vehículos distintos y su coste viene determinado por el vehículo que lo recorra. 7) El museo de la ciudad necesita gestionar las visitas guiadas de grupos y para ello se debe implementar una base de datos relacional que cumpla los siguientes requisitos: Los grupos pueden solicitar reservas de los recorridos que ofrece el museo de la ciudad. Cada grupo de visitantes que desee una vista guiada indicará el recorrido, la fecha y el número de personas del grupo. El museo les asignará un identificador de reserva y uno de los guías que pueden cubrir el recorrido solicitado. De cada guía se conoce su nombre y los idiomas que habla. Un recorrido, identificado mediante una referencia y con una duración determinada, consiste en una visita a un subconjunto de salas del museo en un orden predeterminado. Cada sala tiene asignados un código único y un nombre. Cada guía cubre al menos un recorrido y como mucho tres. Todos los recorridos tienen asignado al menos un guía. 8) Un parque de atracciones desea implementar una base de datos relacional que cumpla los siguientes requisitos: Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 El parque de atracciones abre una cantidad de días al año que debe conocerse de antemano, así como la hora de apertura y cierre, y el número de entradas ya vendidas para dicho día. Debe tener una lista de atracciones ofertadas con un nombre único, un aforo máximo, un tipo (maquinismo, infantil, etc.), el número de encargados necesarios para su funcionamiento y un número de ubicación de la atracción en un mapa que se ofrece a los visitantes. Debe contratar a los encargados, conociendo su DNI, nombre, apellido, fecha de nacimiento y fecha de contrato. Cada día de apertura se debe conocer que encargados se ocupan de cada atracción y cuantas horas está cada encargado en cada atracción. En el mismo día de apertura, un encargado no podrá tener asignados más de tres atracciones. Los encargados son eventuales y eligen los días de apertura que quieren trabajar (no necesariamente todos). Se contrata el mantenimiento de las atracciones a varias empresas, cada una de las cuales se especializa en mantener algunas. De cada empresa de debe saber el CIF, el nombre y un teléfono de contacto. Cada atracción debe tener asignada al menos una empresa de mantenimiento. Se hacen ventas online de entradas que incluyen la tarjeta del cliente (necesaria para la recogida), número de entradas, la fecha de compra, la fecha de la entrada y un descuento variable para cada venta. Con una tarjeta sólo se puede comprar entradas como máximo una vez para un día concreto, aunque se puede modificar el número de entradas de cada día. 9) Estamos encargados de gestionar los pedidos online de una cadena de supermercados. Para ello hemos decidido diseñar una base de datos que nos permita automatizar los diferentes aspectos asociados a los mismos. Después de reunirnos con los encargados de diseñar la aplicación que se usará para la gestión de estos pedidos hemos recopilado la siguiente información acerca de los datos que necesitan manejar y las restricciones que se deberán aplicar. Los clientes podrán realizar pedidos online si previamente se han registrado en el sistema. Durante el registro deberán indicar diferentes datos. Obligatoriamente deberán proporcionar su nombre y apellidos, correo electrónico (se utilizará como usuario para conectarse a la aplicación), password, dirección de entrega de los pedidos (tipo de vía, nombre, número, piso y letra) y el número de la tarjeta de crédito con la que realizarán el pago de los pedidos. Opcionalmente se podrá proporcionar un número de teléfono y el número de socio (en caso de disponer del Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 mismo). Aquellos clientes que dispongan de número de socio podrán obtener cheques descuento para sus compras. La empresa ofrece diferentes productos, que se identifican por un código de barras y tienen asociada una descripción y un precio. Estos productos pueden ser de dos tipos, frescos o envasados. Los productos pueden estar de oferta. Los usuarios podrán confeccionar un pedido en diferentes sesiones, durante las cuales podrán incluir y eliminar productos en su “carrito”. Para cada producto deberán indicar el número de unidades (productos envasados) o el peso (productos frescos) que desean recibir. Cuando completen el pedido, los artículos incluidos en el carrito generarán un nuevo pedido y el carrito se vaciará, permitiendo comenzar a registrar un nuevo pedido. El pedido, que tendrá un identificador único, incluirá cada uno de los productos incluidos en el carrito, la fecha de entrega y el importe total del pedido. Los clientes que sean socios del supermercado podrán ser obsequiados con cheques descuento que serán aplicados a sus pedidos. Los cheques de cada cliente se identifican mediante un número y tienen asociada una fecha de caducidad y un importe de descuento. Aunque un número de cheque de un cliente no identifica de forma única un cheque entre los de todos los clientes, si identifica un cheque de un cliente. Finalmente los pedidos deberán ser asignados a los repartidores para su entrega. Los repartidores se identifican mediante un número de empleado y tienen asignadas varias zonas de reparto que corresponden a códigos postales. 10) Nos han encargado la gestión de reservas online de restaurantes de la empresa Cometelo Todo. Para ello hemos debemos diseñar una base de datos que nos permita automatizar los diferentes aspectos asociados a dichas reservas. Después de reunirnos con los encargados de la aplicación web hemos recopilado la siguiente información acerca de los datos que necesitan manejar y las restricciones que se deberán aplicar. Los clientes podrán realizar reservas online si previamente se han registrado en el sistema. Durante el registro deberán indicar diferentes datos. Obligatoriamente deberán proporcionar su correo electrónico (se utilizará como usuario para conectarse a la aplicación), password y código postal. Opcionalmente se podrá proporcionar un número de teléfono. Asimismo los usuarios tendrán asociada una categoría (Esporádico, Frecuente o Experto) dependiendo de las reservas que hagan. Se podrán realizar reservas de los restaurantes adheridos al sistema. Los restaurantes deberán estar completamente identificados, por lo que deberán facilitar el NIF, el nombre comercial del restaurante, la ubicación (calle, ciudad y código postal), así como el tipo de comida que sirven. Los tipos de comida están codificados mediante una palabra única que los describe: Asiática, Tradicional, Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 Mexicana, Italiana, Internacional, Mediterránea…. Esta lista podrá ampliarse a lo largo del tiempo. Los restaurantes podrán ofrecen una o más ofertas que los usuarios podrán solicitar al hacer la reserva. Dichas ofertas pueden ser de un descuento del 20% o el 50% sobre el precio de carta o un menú de 18, 20 o 30 euros. Todos deberán ofrecer la opción de reserva a la carta. El restaurante puede ofrecer varias ofertas simultáneamente o ninguna. En este último caso solo se podrá reservar para comer a la carta. Las reservas estarán identificadas mediante una referencia única. Al realizar la reserva los usuarios deberán indicar la fecha, el número de comensales, la hora y la oferta a la que desea acogerse en caso de que el restaurante elegido ofrezca alguna. Por otra parte los restaurantes podrán acceder a las reservas para indicar si el cliente ha acudido o no al restaurante en la fecha y hora señaladas. Finalmente, los usuarios podrán dar su opinión para valorar tres aspectos del restaurante para el que hicieron la reserva: Calidad, Servicio y Precio. 11) La regulación aérea requiere implementar una base de datos relacional que cumpla los siguientes requisitos: Los aeropuertos son identificados por un código, tienen un nombre y un país. Se debe conocer el nombre de las compañías que operan en cada aeropuerto. En cada aeropuerto opera al menos una compañía y como mucho 10. Los vuelos vienen identificados por un número de vuelo y una fecha. En el mismo día no puede haber más de un vuelo con el mismo número, pero algunas compañías ofrecen vuelos diarios que repiten número en distintas fechas. Los vuelos tienen un número fijo de plazas, un aeropuerto de origen y un aeropuerto de destino y son ofrecidos por una determinada compañía. Los billetes se venden indicando obligatoriamente el asiento, el número de pasaporte y el vuelo al que pertenecen, así como su importe. 12) La biblioteca de la universidad desea implementar una base de datos relacional que cumpla los siguientes requisitos: La biblioteca proporciona acceso a artículos de investigación publicados en diferentes revistas de ámbito internacional. Cada artículo se identifica por su DOI (Digital Object Identifier) y se conoce su título, un conjunto de palabras clave, sus autores y la revista en la que se han publicado. Ejercicios Modelo Entidad Relación. Bases de Datos. Grado de Ingeniería Informática. 2016/2017 Un artículo tiene como máximo 4 autores de los que se conoce su nombre, correo electrónico y afiliación. Tanto el correo electrónico como la afiliación de un autor pueden diferir entre los distintos artículos en los que participa. Las revistas se identifican mediante su ISSN (International Standard Serial Number) y publican volúmenes periódicamente. Cada volumen tiene asociado un número único y la fecha de publicación (mes y año). Diferentes revistas pueden publicar un volumen con el mismo número en la misma fecha. Para cada artículo se debe conocer el volumen de la revista en el que ha sido publicado. Por último, cada revista es publicada por una editorial con un nombre único. La editorial puede publicar diferentes revistas. Base de Datos/Ejercicios/03 - Álgebra Relacional.pdf Facultad de Informática. Ingenieŕıa en Informática / del Software / de Computadores. Bases de datos. Curso 2016-2017. Ejercicios de álgebra relacional. Ejercicio 1. Considera el siguiente modelo relacional y expresa en algebra relacional las peticiones que se indican: CLIENTES(NCliente, Nombre, Dirección, Teléfono, Población) PRODUCTOS(CodProducto, Descripción, Precio) VENTAS(CodProducto, NCliente, Cantidad, IdVenta) La tabla de clientes almacena información sobre cada posible cliente de nuestra empresa. En la tabla de productos almacenamos información sobre cada producto de la empresa. La tabla de ventas relaciona a las dos anteriores utilizando el atributo codProducto para indicar el producto que se ha vendido, y el atributo NCliente para indicar el cliente al que vendimos el producto. 1. Realizar una consulta que muestre el nombre de los clientes de Palencia. 2. Indicar el código y descripción de los productos cuyo código coincida con su descripción 3. Obtener el nombre de los clientes junto con el identificador de venta y la cantidad vendida, de aquellos productos de los que se vendieron más de 500 unidades. 4. Nombre de los clientes que no han comprado nada. 5. Nombre de los clientes que han comprado todos los productos de la empresa 6. Identificador de las ventas cuya cantidad supera a la cantidad vendida en la venta número 18 7. Códigos de productos que no se han comprado nunca en Palencia 8. Códigos de productos que se han vendido tanto en Palencia como en Valladolid 9. Poblaciones a las que hemos vendido todos nuestros productos Imaginemos que añadimos la tabla de facturas que se relaciona con la de ventas, de modo que a la tabla de ventas le añadimos el no de Factura con la que se relaciona. En la tabla de factura indicamos la fecha, el número y si se pago o no (un 1 significa pagado, un 0 que no está pagada). Cada factura se corresponde con varias ventas y con un solo cliente, para lo cual se vaŕıa el diseño: FACTURA(NFactura, Fecha, Pagada, NCliente) VENTAS(CodProducto, NFactura, Cantidad, IdVenta) 10. Obtener el nombre de los clientes que tienen alguna factura sin pagar 11. Clientes que han pagado todas sus facturas Ejercicio 2. Considera el siguiente modelo relacional y expresa en álgebra relacional las peticiones que se indican: PATRONES(Idpatron, Nombre, Rating, Edad) BARCOS(Idbarco, NombreBarco, Color) RESERVAS(Idpatron, Idbarco, Fecha) 1. Realizar una consulta que muestre el nombre de los patrones que han reservado el barco 103. 2. Realizar una consulta que muestre el nombre de los patrones que han reservado un barco rojo. 3. Obtener el color de los barcos reservados por patrones mayores de 45 años. 4. Obtener el nombre de los patrones que han reservado al menos un barco. 5. Nombre de los patrones que han reservado un barco rojo o verde. 1 6. Nombre de los patrones que han reservado un barco rojo y verde. 7. Obtener el nombre de los patrones mayores de 20 años que no han reservado un barco rojo. 8. Obtener el nombre de los patrones que han reservado todos los barcos. Ejercicio 3. Considera el modelo relacional de la base de datos Trabajo que se indica a continuación e indica una expresión del álgebra relacional para cada una de las consultas propuestas. empleado (nombre-persona, calle, ciudad) trabaja (nombre-persona, nombre-compañı́a, sueldo) compañı́a (nombre-compañı́a, ciudad) jefe (nombre-persona, nombre-jefe) 1. Averiguar los nombres de todos los empleados que trabajan para el ”Banco Importante”. 2. Averiguar el nombre de todos los empleados de esta base de datos que no trabajan para el ”Banco Importante”. 3. Averiguar el nombre y la ciudad de residencia de todos los empleados que trabajan para el ”Banco Importante”. 4. Averiguar el nombre, la calle y ciudad de residencia, de todos los empleados que trabajan para el ”Banco Importante” y ganan más de 10.000e anuales. 5. Averiguar el nombre de todos los empleados de esta base de datos que viven en la misma ciudad que la compañ́ıa para la que trabajan. 6. Averiguar el nombre de todos los empleados que ganan más que cualquier empleado del ”Banco Pequeño”. 7. Supóngase que las compañ́ıas pueden estar ubicadas en varias ciudades. Buscar todas las compañ́ıas con sede en todas las ciudades en las que tiene sede el ”Banco Pequeño”. 2 Base de Datos/Ejercicios/04.1 - SQL(I).pdf Facultad de Informática. Ingenieŕıa en Informática / del Software / de Computadores. Bases de datos. Curso 2016-2017. Ejercicios de SQL (I): DDL y sentencias DML de modificación de datos. Ejercicio 1. Implementa en SQL el siguiente esquema de base de datos correspondiente a una cadena de bibliotecas: Publicación(ISBN, Tı́tulo, Idioma, NEditorial) (Con clave externa referenciada a Editorial) Editorial(Nombre, Dirección, Teléfono) Socio(NCarnet, Nombre, DNI, email, Distrito) (Con clave externa referenciada a Biblioteca) Revista(ISBN,Periodo) (Con clave externa referenciada a Publicación) Libro(ISBN, Edición, Fecha) (Con clave externa referenciada a Publicación) Clasifica(ISBN, IdTema) (Con claves externas referenciadas a Publicación y Tema) Tema(IdTema, Descripción) Biblioteca(Distrito) Ejemplar Libro(ISBN, Distrito, Número, FechaCompra, NSocio∗, FechaP∗) (Con claves externas referenciadas a Libro, Biblioteca y Socio) Suscripción(ISBN, Distrito, FechaSuscripción) (Con claves externas referenciadas a Revista y Biblioteca) Ejemplar Revista(ISBN, Distrito, Número, FechaCompra, NSocio∗, FechaP∗) (Con claves externas referenciadas a Suscripción y Socio) Ejercicio 2. Una vez implementados los esquemas de relaciones, inserta las siguientes filas en el orden en el que aparecen: Publicación = {〈 ’978-84-782-9085-7’, ’Fundamentos de Sistemas de Bases de Datos’, ’Español’, ’Pearson Educación SA’〉} Editorial = {〈 ’Pearson Educación SA’, ’Ribera del Loira, 28. 28042 Madrid’, 911234567〉} ¿Qué mensaje aparece como resultado de las inserciones? ¿Cómo se puede corregir? Ejercicio 3. Inserta datos en las diferentes tablas en el orden apropiado para evitar que se produzcan errores. 1 Base de Datos/Ejercicios/04.2 - Script Aerolinea.sql drop table vuelo cascade constraints; drop table avion cascade constraints; drop table empleado cascade constraints; drop table certificado cascade constraints; create table vuelo( flno number(4,0) primary key, origen varchar2(20), destino varchar2(20), distancia number(6,0), salida date, llegada date, precio number(7,2)); create table avion( aid number(9,0) primary key, nombre varchar2(30), autonomia number(6,0)); create table empleado( eid number(9,0) primary key, nombre varchar2(30), salario number(10,2)); create table certificado( eid number(9,0), aid number(9,0), primary key(eid,aid), foreign key(eid) references empleado, foreign key(aid) references avion); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (99.0,'Los Angeles','Washington D.C.',2308.0,to_date('04/12/2005 09:30', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 09:40', 'dd/mm/yyyy HH24:MI'),235.98); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (13.0,'Los Angeles','Chicago',1749.0,to_date('04/12/2005 08:45', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 08:45', 'dd/mm/yyyy HH24:MI'),220.98); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (346.0,'Los Angeles','Dallas',1251.0,to_date('04/12/2005 11:50', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 07:05', 'dd/mm/yyyy HH24:MI'),225-43); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (387.0,'Los Angeles','Boston',2606.0,to_date('04/12/2005 07:03', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 05:03', 'dd/mm/yyyy HH24:MI'),261.56); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (7.0,'Los Angeles','Sydney',7487.0,to_date('04/12/2005 05:30', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 11:10', 'dd/mm/yyyy HH24:MI'),278.56); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (2.0,'Los Angeles','Tokyo',5478.0,to_date('04/12/2005 06:30', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 03:55', 'dd/mm/yyyy HH24:MI'),780.99); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (33.0,'Los Angeles','Honolulu',2551.0,to_date('04/12/2005 09:15', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 11:15', 'dd/mm/yyyy HH24:MI'),375.23); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (34.0,'Los Angeles','Honolulu',2551.0,to_date('04/12/2005 12:45', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 03:18', 'dd/mm/yyyy HH24:MI'),425.98); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (76.0,'Chicago','Los Angeles',1749.0,to_date('04/12/2005 08:32', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 10:03', 'dd/mm/yyyy HH24:MI'),220.98); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (68.0,'Chicago','New York',802.0,to_date('04/12/2005 09:00', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 12:02', 'dd/mm/yyyy HH24:MI'),202.45); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (7789.0,'Madison','Detroit',319.0,to_date('04/12/2005 06:15', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 08:19', 'dd/mm/yyyy HH24:MI'),120.33); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (701.0,'Detroit','New York',470.0,to_date('04/12/2005 08:55', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 10:26', 'dd/mm/yyyy HH24:MI'),180.56); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (702.0,'Madison','New York',789.0,to_date('04/12/2005 07:05', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 10:12', 'dd/mm/yyyy HH24:MI'),202.34); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (4884.0,'Madison','Chicago',84.0,to_date('04/12/2005 10:12', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 11:02', 'dd/mm/yyyy HH24:MI'),112.45); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (2223.0,'Madison','Pittsburgh',517.0,to_date('04/12/2005 08:02', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 10:01', 'dd/mm/yyyy HH24:MI'),189.98); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (5694.0,'Madison','Minneapolis',247.0,to_date('04/12/2005 08:32', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 09:33', 'dd/mm/yyyy HH24:MI'),120.11); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (304.0,'Minneapolis','New York',991.0,to_date('04/12/2005 10:00', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 01:39', 'dd/mm/yyyy HH24:MI'),101.56); INSERT INTO VUELO (FLNO, ORIGEN, DESTINO, DISTANCIA, SALIDA, LLEGADA, PRECIO) VALUES (149.0,'Pittsburgh','New York',303.0,to_date('04/12/2005 09:42', 'dd/mm/yyyy HH24:MI'), to_date('04/12/2005 12:09', 'dd/mm/yyyy HH24:MI'),1165.00); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('1','Boeing 747-400','8430'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('2','Boeing 737-800','3383'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('3','Airbus A340-300','7120'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('4','British Aerospace Jetstream 41','1502'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('5','Embraer ERJ-145','1530'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('6','SAAB 340','2128'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('7','Piper Archer III','520'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('8','Tupolev 154','4103'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('16','Schwitzer 2-33','30'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('9','Lockheed L1011','6900'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('10','Boeing 757-300','4010'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('11','Boeing 777-300','6441'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('12','Boeing 767-400ER','6475'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('13','Airbus A320','2605'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('14','Airbus A319','1805'); Insert into AVION (AID,NOMBRE,AUTONOMIA) values ('15','Boeing 727','1504'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('242518965','James Smith','120433'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('141582651','Mary Johnson','178345'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('11564812','John Williams','153972'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('567354612','Lisa Walker','256481'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('552455318','Larry West','101745'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('550156548','Karen Scott','205187'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('390487451','Lawrence Sperry','212156'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('274878974','Michael Miller','99890'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('254099823','Patricia Jones','24450'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('356187925','Robert Brown','44740'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('355548984','Angela Martinez','212156'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('310454876','Joseph Thompson','212156'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('489456522','Linda Davis','27984'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('489221823','Richard Jackson','23980'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('548977562','William Ward','84476'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('310454877','Chad Stewart','33546'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('142519864','Betty Adams','227489'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('269734834','George Wright','289950'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('287321212','Michael Miller','48090'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('552455348','Dorthy Lewis','152013'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('248965255','Barbara Wilson','43723'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('159542516','William Moore','48250'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('348121549','Haywood Kelly','32899'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('90873519','Elizabeth Taylor','32021'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('486512566','David Anderson','43001'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('619023588','Jennifer Thomas','54921'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('15645489','Donald King','18050'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('556784565','Mark Young','205187'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('573284895','Eric Cooper','114323'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('574489456','William Jones','105743'); Insert into EMPLEADO (EID,NOMBRE,SALARIO) values ('574489457','Milo Brooks','20'); Insert into CERTIFICADO (EID,AID) values ('11564812','2'); Insert into CERTIFICADO (EID,AID) values ('11564812','10'); Insert into CERTIFICADO (EID,AID) values ('90873519','6'); Insert into CERTIFICADO (EID,AID) values ('141582651','2'); Insert into CERTIFICADO (EID,AID) values ('141582651','10'); Insert into CERTIFICADO (EID,AID) values ('141582651','12'); Insert into CERTIFICADO (EID,AID) values ('142519864','1'); Insert into CERTIFICADO (EID,AID) values ('142519864','2'); Insert into CERTIFICADO (EID,AID) values ('142519864','3'); Insert into CERTIFICADO (EID,AID) values ('142519864','7'); Insert into CERTIFICADO (EID,AID) values ('142519864','10'); Insert into CERTIFICADO (EID,AID) values ('142519864','11'); Insert into CERTIFICADO (EID,AID) values ('142519864','12'); Insert into CERTIFICADO (EID,AID) values ('142519864','13'); Insert into CERTIFICADO (EID,AID) values ('159542516','5'); Insert into CERTIFICADO (EID,AID) values ('159542516','7'); Insert into CERTIFICADO (EID,AID) values ('242518965','2'); Insert into CERTIFICADO (EID,AID) values ('242518965','10'); Insert into CERTIFICADO (EID,AID) values ('269734834','1'); Insert into CERTIFICADO (EID,AID) values ('269734834','2'); Insert into CERTIFICADO (EID,AID) values ('269734834','3'); Insert into CERTIFICADO (EID,AID) values ('269734834','4'); Insert into CERTIFICADO (EID,AID) values ('269734834','5'); Insert into CERTIFICADO (EID,AID) values ('269734834','6'); Insert into CERTIFICADO (EID,AID) values ('269734834','7'); Insert into CERTIFICADO (EID,AID) values ('269734834','8'); Insert into CERTIFICADO (EID,AID) values ('269734834','9'); Insert into CERTIFICADO (EID,AID) values ('269734834','10'); Insert into CERTIFICADO (EID,AID) values ('269734834','11'); Insert into CERTIFICADO (EID,AID) values ('269734834','12'); Insert into CERTIFICADO (EID,AID) values ('269734834','13'); Insert into CERTIFICADO (EID,AID) values ('269734834','14'); Insert into CERTIFICADO (EID,AID) values ('269734834','15'); Insert into CERTIFICADO (EID,AID) values ('274878974','10'); Insert into CERTIFICADO (EID,AID) values ('274878974','12'); Insert into CERTIFICADO (EID,AID) values ('310454876','8'); Insert into CERTIFICADO (EID,AID) values ('310454876','9'); Insert into CERTIFICADO (EID,AID) values ('355548984','8'); Insert into CERTIFICADO (EID,AID) values ('355548984','9'); Insert into CERTIFICADO (EID,AID) values ('356187925','6'); Insert into CERTIFICADO (EID,AID) values ('390487451','3'); Insert into CERTIFICADO (EID,AID) values ('390487451','13'); Insert into CERTIFICADO (EID,AID) values ('390487451','14'); Insert into CERTIFICADO (EID,AID) values ('548977562','7'); Insert into CERTIFICADO (EID,AID) values ('550156548','1'); Insert into CERTIFICADO (EID,AID) values ('550156548','12'); Insert into CERTIFICADO (EID,AID) values ('552455318','2'); Insert into CERTIFICADO (EID,AID) values ('552455318','7'); Insert into CERTIFICADO (EID,AID) values ('552455318','14'); Insert into CERTIFICADO (EID,AID) values ('556784565','2'); Insert into CERTIFICADO (EID,AID) values ('556784565','3'); Insert into CERTIFICADO (EID,AID) values ('556784565','5'); Insert into CERTIFICADO (EID,AID) values ('567354612','1'); Insert into CERTIFICADO (EID,AID) values ('567354612','2'); Insert into CERTIFICADO (EID,AID) values ('567354612','3'); Insert into CERTIFICADO (EID,AID) values ('567354612','4'); Insert into CERTIFICADO (EID,AID) values ('567354612','5'); Insert into CERTIFICADO (EID,AID) values ('567354612','7'); Insert into CERTIFICADO (EID,AID) values ('567354612','9'); Insert into CERTIFICADO (EID,AID) values ('567354612','10'); Insert into CERTIFICADO (EID,AID) values ('567354612','11'); Insert into CERTIFICADO (EID,AID) values ('567354612','12'); Insert into CERTIFICADO (EID,AID) values ('567354612','15'); Insert into CERTIFICADO (EID,AID) values ('573284895','3'); Insert into CERTIFICADO (EID,AID) values ('573284895','4'); Insert into CERTIFICADO (EID,AID) values ('573284895','5'); Insert into CERTIFICADO (EID,AID) values ('574489456','6'); Insert into CERTIFICADO (EID,AID) values ('574489456','8'); Insert into CERTIFICADO (EID,AID) values ('574489457','7'); commit; Base de Datos/Ejercicios/04.2 - Script Libreria.sql drop table Cliente cascade constraints; drop table Pedido cascade constraints; drop table Autor cascade constraints; drop table Autor_Libro cascade constraints; drop table Libro cascade constraints; drop table Libros_Pedido cascade constraints; create table Cliente (IdCliente CHAR(10) PRIMARY KEY, Nombre VARCHAR(25) NOT NULL, Direccion VARCHAR(60) NOT NULL, NumTC CHAR(16) NOT NULL); create table Pedido (IdPedido CHAR(10) PRIMARY KEY, IdCliente CHAR(10) NOT NULL REFERENCES Cliente on delete cascade, FechaPedido DATE NOT NULL, FechaExped DATE); create table Autor ( idautor NUMBER PRIMARY KEY, Nombre VARCHAR(25)); create table Libro (ISBN CHAR(15) PRIMARY KEY, Titulo VARCHAR(60) NOT NULL, Año CHAR(4) NOT NULL, PrecioCompra NUMBER(6,2) DEFAULT 0, PrecioVenta NUMBER(6,2) DEFAULT 0); create table Autor_Libro (ISBN CHAR(15), Autor NUMBER, CONSTRAINT al_PK PRIMARY KEY (ISBN, Autor), CONSTRAINT libroA_FK FOREIGN KEY (ISBN) REFERENCES Libro on delete cascade, CONSTRAINT autor_FK FOREIGN KEY (Autor) REFERENCES Autor); create table Libros_Pedido( ISBN CHAR(15), IdPedido CHAR(10), Cantidad NUMBER(3) CHECK (cantidad >0), CONSTRAINT lp_PK PRIMARY KEY (ISBN, idPedido), CONSTRAINT libro_FK FOREIGN KEY (ISBN) REFERENCES Libro on delete cascade, CONSTRAINT pedido_FK FOREIGN KEY (IdPedido) REFERENCES Pedido on delete cascade); insert into Cliente values ('0000001','Margarita Sanchez', 'Arroyo del Camino 2','1234567890123456'); insert into Cliente values ('0000002','Angel Garcia', 'Puente Viejo 13', '1234567756953456'); insert into Cliente values ('0000003','Pedro Santillana', 'Molino de Abajo 42', '1237596390123456'); insert into Cliente values ('0000004','Rosa Prieto', 'Plaza Mayor 46', '4896357890123456'); insert into Cliente values ('0000005','Ambrosio Perez', 'Corredera de San Antonio 1', '1224569890123456'); insert into Cliente values ('0000006','Lola Arribas', 'Lope de Vega 32', '2444889890123456' ); insert into Pedido values ('0000001P','0000001', TO_DATE('01/12/2011'),TO_DATE('03/12/2011')); insert into Pedido values ('0000002P','0000001', TO_DATE('01/12/2011'),null); insert into Pedido values ('0000003P','0000002', TO_DATE('02/12/2011'),TO_DATE('03/12/2011')); insert into Pedido values ('0000004P','0000004', TO_DATE('02/12/2011'),TO_DATE('05/12/2011')); insert into Pedido values ('0000005P','0000005', TO_DATE('03/12/2011'),TO_DATE('03/12/2011')); insert into Pedido values ('0000006P','0000003', TO_DATE('04/12/2011'),null); insert into Autor values (1,'Matilde Asensi'); insert into Autor values (2,'Ildefonso Falcones'); insert into Autor values (3,'Carlos Ruiz Zafon'); insert into Autor values (4,'Miguel de Cervantes'); insert into Autor values (5,'Julia Navarro'); insert into Autor values (6,'Miguel Delibes'); insert into Autor values (7,'Fiodor Dostoievski'); insert into lIBRO values ('8233771378567', 'Todo bajo el cielo', '2008', 9.45, 13.45); insert into lIBRO values ('1235271378662', 'La catedral del mar', '2009', 12.50, 19.25); insert into lIBRO values ('4554672899910', 'La sombra del viento', '2002', 19.00, 33.15); insert into lIBRO values ('5463467723747', 'Don Quijote de la Mancha', '2000', 49.00, 73.45); insert into lIBRO values ('0853477468299', 'La sangre de los inocentes', '2011', 9.45, 13.45); insert into lIBRO values ('1243415243666', 'Los santos inocentes', '1997', 10.45, 15.75); insert into lIBRO values ('0482174555366', 'Noches Blancas', '1998', 4.00, 9.45); insert into Autor_lIBRO values ('8233771378567',1); insert into Autor_lIBRO values ('1235271378662',2); insert into Autor_lIBRO values ('4554672899910',3); insert into Autor_lIBRO values ('5463467723747',4); insert into Autor_lIBRO values ('0853477468299',5); insert into Autor_lIBRO values ('1243415243666',6); insert into Autor_lIBRO values ('0482174555366',7); insert into Libros_Pedido values ('8233771378567','0000001P', 1); insert into Libros_Pedido values ('5463467723747','0000001P', 2); insert into Libros_Pedido values ('0482174555366','0000002P', 1); insert into Libros_Pedido values ('4554672899910','0000003P', 1); insert into Libros_Pedido values ('8233771378567','0000003P', 1); insert into Libros_Pedido values ('1243415243666','0000003P', 1); insert into Libros_Pedido values ('8233771378567','0000004P', 1); insert into Libros_Pedido values ('4554672899910','0000005P', 1); insert into Libros_Pedido values ('1243415243666','0000005P', 1); insert into Libros_Pedido values ('5463467723747','0000005P', 3); insert into Libros_Pedido values ('8233771378567','0000006P', 5); commit; Base de Datos/Ejercicios/04.2 - SQL (II).pdf Ejercicios SQL DML. Bases de datos. 2016/2017 1. Considerando la base de datos AllTheBooks construye consultas para recuperar la siguiente información: 1. Lista de libros disponibles con su autor y año de publicación ordenada por este último. 2. Lista de libros disponibles publicados después del año 2000. 3. Lista de Clientes que han realizado algún pedido 4. Lista de clientes que han adquirido el libro con ISBN= 4554672899910. 5. Lista de los clientes y los libros adquiridos por ellos cuyo nombre (del cliente) contenga ‘San’. 6. Lista de Clientes que hayan comprado libros de más de 10 euros. 7. Clientes y fecha de pedidos que han realizado que no han sido expedidos aun. 8. Lista de clientes que NO han comprado libros de precio superior a 10 euros 9. Lista de libros vendidos con precio superior a 30 euros o publicados antes del año 2000 10. Clientes que han hecho más de un pedido el mismo día. 11. Lista de títulos de libros vendidos y cantidad. 12. Lista de Clientes junto al importe total gastado en la librería 13. Ganancias obtenidas por la librería con las ventas 14. Lista de importe total de pedidos por fecha, que se hayan realizado después del 01/12/2011 y no hayan sido expedidos 15. Pedidos con importe superior a 100 euros 16. Pedidos con importe total que contengan más de un titulo 17. Pedidos con importe total que contengan más de 4 libros (ejemplares) 18. Lista de libros más caros. 19. Libros de los que no se haya vendido ningún ejemplar o cuyo beneficio sea inferior a 5 euros 20. Clientes que hayan comprado más de un ejemplar de un título en alguna ocasión 21. Lista de Nombre de cliente, numero de pedido, isbn y título de libros adquiridos. Si no han adquirido ningún libro mostrar el nombre del cliente también. 2. Diseña consultas para recuperar la siguiente información de la base de datos FlyWithOther 1. Código y nombre de los pilotos certificados para pilotar aviones Boeing. 2. Código de aviones que pueden hacer el recorrido de Los Ángeles a Chicago sin repostar. 3. Pilotos certificados para operar con aviones con una autonomía superior a 3000 millas pero no certificados para aviones Boeing. 4. Empleados con el salario más elevado. 5. Empleados con el segundo salario más alto. 6. Empleados con mayor número de certificaciones para volar. 7. Empleados certificados para 3 modelos de avión. 8. Nombre de los aviones tales que todos los pilotos certificados para operar con ellos tengan salarios superiores a 80.000 euros. 9. Para cada piloto certificado para operar con más de 3 modelos de avión indicar el código de empleado y la autonomía máxima de los aviones que puede pilotar. 10. Nombre de los pilotos cuyo salario es inferior a la ruta más barata entre Los Ángeles y Honolulu. 11. Mostrar el nombre de los aviones con autonomía de vuelo superior a 1.000 millas junto con la media salarial de los pilotos certificados. 12. Calcular la diferencia entre la media salarial de todos los empleados (incluidos los pilotos) y la de los pilotos. 13. Listar el nombre y los salarios de los empleados (no pilotos) cuyo salario sea superior a la media salarial de los pilotos. 14. Nombre de los pilotos certificados solo para modelos con autonomía superior a 1.000 millas. Observación: La tabla empleado contiene información relativa a pilotos y otros trabajadores. Sólo los pilotos están certificados para pilotar algún modelo de avión. Base de Datos/Ejercicios/05 - EjemploPLSqlAnonimo.sql -- ---------------------------------------------------- -- Ejemplo de bloque anonimo plsql. -- ---------------------------------------------------- -- Recorre con un bucle LOOP un cursor y muestra el -- resultado en la consola. -- ---------------------------------------------------- SET SERVEROUTPUT ON; DECLARE CURSOR cLibrosAutores IS SELECT l.titulo, a.nombre FROM libro l JOIN autor_libro al on l.ISBN=al.ISBN JOIN autor a ON a.IDAUTOR=al.AUTOR; v_nombre AUTOR.NOMBRE%TYPE; v_titulo libro.titulo%TYPE; BEGIN OPEN cLibrosAutores; LOOP FETCH cLibrosAutores INTO v_titulo, v_nombre; EXIT WHEN cLibrosAutores%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_nombre || ',' || v_titulo); END LOOP; C LOSE cLibrosAutores; END; Base de Datos/Ejercicios/05 - EjemploPLSqlFuncion.sql -- ---------------------------------------------------- -- Ejemplo de funcion plsql y bloque anonimo de llamada. -- ---------------------------------------------------- SET SERVEROUTPUT ON; CREATE OR REPLACE FUNCTION cuadrado(x NUMBER) RETURN NUMBER IS res NUMBER; BEGIN res := x*x; RETURN res; END; / DECLARE V NUMBER; RES NUMBER; BEGIN V := 18; RES := cuadrado(V); DBMS_OUTPUT.PUT_LINE('EL CUADRADO DE ' || to_char(V) || ' ES: ' || to_char(RES)); END; Base de Datos/Ejercicios/06 - EjemploTriggerDeFila.sql -- ---------------------------------------------------- -- Ejemplo de triggers de fila -- ---------------------------------------------------- set serveroutput on; CREATE OR REPLACE TRIGGER antesDeInsertLibro BEFORE INSERT ON Libro REFERENCING NEW AS nuevoLibro FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('Se va a insertar el libro: ' || :nuevoLibro.Titulo); END; insert into libro values ('11111','Lo que el viento se llevó',1937,15,18); commit; rollback; delete from libro where isbn='11111'; CREATE OR REPLACE TRIGGER antesDeUpdateLibro BEFORE UPDATE OF precioCompra,precioVenta ON Libro FOR EACH ROW WHEN (NEW.precioCompra < OLD.precioCompra OR NEW.precioVenta < OLD.precioVenta) BEGIN IF (:NEW.precioCompra != :OLD.precioCompra) THEN DBMS_OUTPUT.PUT_LINE('OJO: se va a cambiar el precio de compra de ' || :OLD.precioCompra || ' a ' || :NEW.precioCompra); END IF; IF (:NEW.precioVenta != :OLD.precioVenta) THEN DBMS_OUTPUT.PUT_LINE('OJO: se va a cambiar el precio de venta de ' || :OLD.precioVenta || ' a ' || :NEW.precioVenta); END IF; END; / UPDATE libro SET precioCompra = 13 WHERE isbn = '11111'; UPDATE libro SET precioVenta = 13 WHERE isbn = '11111'; Base de Datos/Ejercicios/06 - Triggers.pdf Ejercicios PL/SQL. Bases de datos. 2016/2017 1. Crea las siguientes tablas: CREATE TABLE Contratos(Referencia VARCHAR(10) PRIMARY KEY, Empresa VARCHAR(100), Fecha DATE, NumTrayectos NUMBER(2,0)); CREATE TABLE Trayectos(Referencia VARCHAR(10) REFERENCES Contratos ON DELETE CASCADE, Origen VARCHAR(50), Destino VARCHAR(50), Vehículo VARCHAR(20), PRIMARY KEY (Referencia, Origen, Destino)); a. Escribir un procedimiento que reciba una referencia de contrato (que se asume que existe) como parámetro de entrada y actualice su contador de trayectos (NumTrayectos) con el número que tiene registrados en la tabla Trayectos y lo imprima por consola. Se debe declarar una excepción que se lance para dar un mensaje si la referencia no tiene trayectos. b. Crear un trigger que mantenga actualizado el atributo redundante NumTrayectos al insertar y borrar en la tabla (se asume que antes de la actualización este atributo es consistente). 2. Crea las siguientes tablas: CREATE TABLE Empleados( DNI CHAR(9) PRIMARY KEY, Nombre VARCHAR(100), CodDept CHAR(5) REFERENCES Departamentos on delete set NULL, Salario NUMBER(4,0)); CREATE TABLE Departamentos(CodDept CHAR(5) PRIMARY KEY, Nombre VARCHAR(100)); CREATE TABLE Cambios(IdCambio VARCHAR(10) PRIMARY KEY, Usuario VARCHAR(12), SalarioAnt NUMBER(4,0), SalarioNew NUMBER(4,0)); a. Implementar un trigger que registre en la tabla Cambios cualquier modificación que se produzca en el salario de un empleado, indicando el usuario en la que se realizó. El identificador se obtendrá de una secuencia denominada SEQCambios. b. Escribir un procedimiento almacenado que liste por departamento el nombre y salario de cada empleado cuyo salario sea inferior a la media del departamento. Incluir el total de dichos salarios por departamento Ejercicios PL/SQL. Bases de datos. 2016/2017 3. Crea las siguientes tablas: Create table Autor ( DNI CHAR(9) PRIMARY KEY, Nombre VARCHAR(50) NOT NULL, Apellido VARCHAR(50) NOT NULL); Pais VARCHAR(30) NOT NULL); NumArticulos NUMBER(3,0) NOT NULL); Create table Revista ( ISSN VARCHAR(9) PRIMARY KEY, Nombre VARCHAR(100) NOT NULL); Create table Articulo ( DOI CHAR(30) PRIMARY KEY, Titulo VARCHAR(100) NOT NULL, ISSNRevista VARCHAR(9) NOT NULL REFERENCES Revista(ISSN) ON DELETE CASCADE, NumAutores NUMBER(1,0) NOT NULL, CHECK NumAutores BETWEEN 1 AND 4); Create table Firma ( DNI CHAR(9) NOT NULL REFERENCES Autor, DOI CHAR(30) NOT NULL REFERENCES Articulo ON DELETE CASCADE, PRIMARY KEY(DNI, DOI)); a. Escribir un procedimiento almacenado que reciba por argumento el nombre de una revista y muestre por consola los datos de la revista (ISSN, Nombre) y los nombres y apellidos de todos sus autores. Si no tiene autores debe indicar ‘No tiene autores’. b. Implementar un trigger que mantenga actualizada la columas NúmAutores. 4. Crea las siguientes tablas: Create table Aeropuerto( Codigo CHAR(6) PRIMARY KEY, Nombre VARCHAR(30) NOT NULL, Pais VARCHAR(30)NOT NULL); Create table Vuelo( Numero CHAR(6), Fecha DATE, Origen CHAR(6) NOT NULL REFERENCES Aeropuerto on delete set NULL, Destino CHAR(6) NOT NULL REFERENCES Aeropuerto on delete set NULL, Importe NUMBER(6,2), Plazas NUMBER(3) DEFAULT 100, primary key (numero,fecha), unique (fecha, origen, destino), check(origen<>destino)); Create table Billetes( Numero CHAR(6), Fecha DATE NOT NULL, Pasaporte CHAR(10) NOT NULL, PRIMARY KEY(Numero, fecha, pasaporte), FOREIGN KEY(Numero, fecha) REFERENCES vuelo); Ejercicios PL/SQL. Bases de datos. 2016/2017 Create table Ventas( Numero CHAR(6), Fecha DATE, Importe NUMBER(6,2), Vendidos NUMBER(3) DEFAULT 0, primary key (Numero, Fecha), foreign key (Numero, Fecha) REFERENCES Vuelo); a. Escribir un procedimiento almacenado que reciba por argumentos una fecha, los códigos de un aeropuerto de origen y uno de destino y un pasaporte y registre un billete en el primer vuelo en el que haya plazas libres. En caso de que no haya vuelos disponibles se informará mediante un mensaje. b. Implementar un trigger que registre en la tabla Ventas el número total de billetes vendidos y el importe total de las ventas para cada vuelo. En el caso de devolución de un billete tan solo se reintegrará un importe fijo de 150€, no el importe total del billete. 5. Ejecutar las siguientes instrucciones: drop table ComisionCC; drop table deposito; drop table log; create table ComisionCC(cc char(20), importe number(10,2)); create table deposito(cc char(20)); create table log( msg varchar(50)); Diseñar un trigger asociado a la operación delete de la tabla ComisionCC, de modo que si la cuenta del registro que se borre se encuentra en la tabla deposito indique en log un mensaje que indique la cc, el importe y el texto “Deposito asociado”. En caso contrario el texto indicará “Cliente preferente” Hacer las siguientes pruebas para comprobar el funcionamiento: insert into Comisioncc values ('12345678900987654321',13.9); insert into Comisioncc values('12345123131333344321',13.0); insert into Comisioncc values ('37423462487654321478',13.9); insert into deposito values ('37423462487654321478'); delete from ComisionCC; 6. Ejecutar las siguientes instrucciones: drop table Records; drop table Marcas; create table Records(prueba number primary key, tiempo number); create table Marcas(prueba number, fecha date, tiempo number, primary key (prueba,fecha)); Diseñar un trigger asociado a la operación de inserción de la tabla Marcas, de modo que si el tiempo de la prueba que se inserte es un nuevo record se actualice el registro correspondiente en la tabla Records. Ejercicios PL/SQL. Bases de datos. 2016/2017 Realiza las siguientes pruebas delete from Marcas; delete from Records; insert into Marcas values (1, to_date(‘01/02/2013’),3.8); insert into Marcas values (1, to_date(‘02/02/2013’),4.2); insert into Marcas values (1, to_date(‘03/02/2013’),3.5); 7. Ejecutar las siguientes instrucciones: drop table Libros cascade constraints; drop table Ejemplares cascade constraints; create table Libros(isbn char(13) primary key, copias integer); create table Ejemplares(signatura char(5) primary key, isbn char(13) not null, FOREIGN KEY (isbn) REFERENCES Libros); Escribir un trigger asociado a la inserción de filas en Ejemplares, de forma que si el isbn no aparece en Libros, se cree una fila en Libros con dicho isbn y copias con valor 1, de forma que se evite el error por la violación de la foreign key. En caso de existir, el número de ejemplares se incrementará en uno. Prueba insertando Ejemplares que satisfagan ambas condiciones. Base de Datos/Ejercicios/07 - EjemploTransacciones.txt -- ---------------------------------------------------- -- Ejemplo 1. -- ---------------------------------------------------- DROP TABLE Movimientos; DROP TABLE Cuentas; CREATE TABLE Cuentas ( Cta VARCHAR2(10) PRIMARY KEY, Titular VARCHAR2(10), Saldo NUMBER(10,2) ); CREATE TABLE Movimientos ( Cta VARCHAR2(10) REFERENCES Cuentas, NumMto NUMBER(10,0), Fecha DATE, Importe NUMBER(10,2) NOT NULL, CONSTRAINT movimientos_pk PRIMARY KEY (Cta, NumMto) ); INSERT INTO Cuentas VALUES ('37','012345678X',1500.00); INSERT INTO Cuentas VALUES ('44','012345678X',2300.00); DECLARE vImporte NUMBER := 400; vExisteDestino NUMBER; vSaldo Cuentas.Saldo%TYPE; BEGIN SELECT Saldo INTO vSaldo FROM Cuentas WHERE Cta = '37'; IF vSaldo > vImporte THEN -- INICIO DE TRANSACCION. -- Cargo en la cta. de origen. INSERT INTO Movimientos VALUES ('37', 4, SYSDATE, -vImporte); UPDATE Cuentas SET Saldo = Saldo - vImporte WHERE Cta = '37'; -- Si existe la cta. destino, abono en la cta. destino. SELECT COUNT(*) INTO vExisteDestino FROM Cuentas WHERE Cta = '44'; IF vExisteDestino > 0 THEN INSERT INTO Movimientos VALUES ('44',3,SYSDATE,vImporte); UPDATE Cuentas SET Saldo = Saldo + vImporte WHERE Cta='44'; COMMIT; -- FIN DE TRANSACCION: confirma los cambios. ELSE ROLLBACK; -- FIN DE TRANSACCION: deshace los cambios. END IF; END IF; END; / -- ---------------------------------------------------- -- Ejemplo 2. -- ---------------------------------------------------- -- ¿Cuál es el estado de la BD después de ejecutar las siguientes sentencias? DROP TABLE empl; CREATE TABLE empl ( NIF VARCHAR2(9) PRIMARY KEY, NOMBRE VARCHAR2(20), SALARIO NUMBER(8,2) ); INSERT INTO empl VALUES ('10A','Jorge Perez',3000.11); ROLLBACK; INSERT INTO empl VALUES ('30C','Javier Sala',2000.22); INSERT INTO empl VALUES ('30C','Soledad Lopez',2000.33); INSERT INTO empl VALUES ('40D','Sonia Moldes',1800.44); INSERT INTO empl VALUES ('50E','Antonio Lopez',1800.44); COMMIT; INSERT INTO empl VALUES ('70C','Soledad Martin',2000.33); SELECT * FROM empl; -- (verlo en cada una de las sentencias anteriores) -- ¿Cuál es el estado de la BD visible desde otras sesiones?} -- (desde otra conexion) SELECT * FROM empl; -- ---------------------------------------------------- -- Ejemplo 3. -- ---------------------------------------------------- -- ¿Cuál es el estado de la BD después de ejecutar las siguientes sentencias? SET TRANSACTION NAME 'sal_update'; UPDATE empl SET salario = 7000 WHERE NIF= '30C'; SAVEPOINT after_salario; UPDATE empl SET salario = 12000 WHERE NIF= '40D'; ROLLBACK TO SAVEPOINT after_salario; UPDATE empl SET salario = 11000 WHERE NIF= '40D'; COMMIT; Base de Datos/Temario/01 - Modelo Entidad-Relación.pdf Diseño conceptual: Modelo entidad-relación Bases de Datos Curso 2016-2017 Jesús Correas – jcorreas@ucm.es Departamento de Sistemas Informáticos y Computación Universidad Complutense de Madrid (Basado en material docente creado por Mercedes Garćıa Merayo) Jesús Correas (DSIC - UCM) 1 / 32 Bibliograf́ıa Bibliograf́ıa básica: I R. Elmasri, S.B. Navathe. Fundamentals of Database Systems (6a Ed). Addison-Wesley, 2010. (en español: Fundamentos de Sistemas de Bases de Datos (5a Ed). Addison-Wesley, 2007). Caṕıtulos 3 y 4 (5a ed. en español) o 7 y 8 (6a ed.). Bibliograf́ıa complementaria: I A. Silberschatz , H. F. Korth, S. Sudarshan. Fundamentos de bases de datos (5a Ed), McGraw-Hill, 2006. Caṕıtulo 6. (es especial el modelo EER: Sección 6.7). Jesús Correas (DSIC - UCM) 2 / 32 Contenido Introducción. El modelo entidad-relación. Entidades y atributos. I Superclaves y claves candidatas. Entidades débiles. I Tipo de entidad. Diagramas ER. Relaciones. I Atributos de relación. I Tipo de relación. I Grado de una relación. I Relaciones recursivas. I Restricciones: cardinalidad y participación. I Restricciones y entidades débiles. Diagramas ER extendidos. I Especialización y generalización. Herencia de atributos. I Agregaciones. Jesús Correas (DSIC - UCM) 3 / 32 Introducción. El modelo entidad-relación. Este modelo nos permite representar la información de una BD en el nivel conceptual. Se obtiene a partir del análisis inicial del sistema, pero este proceso no es directo. I Un buen diseño requiere estudiar las necesidades del sistema y la información de análisis. El diseño de una BD debe ser conciso, fácil de comprender y mantener, y debe permitir un diseño lógico y f́ısico eficiente. Se pueden obtener varios diseños candidatos para un mismo análisis. Los elementos fundamentales del modelo ER son: I Entidades, tipos de entidad. I Atributos. I Relaciones, tipos de relación y restricciones sobre las relaciones. El modelo ER se representa mediante los diagramas ER. Jesús Correas (DSIC - UCM) 4 / 32 Introducción. El modelo entidad-relación. Los pasos a seguir para diseñar el modelo ER son: I Elegir los tipos de entidad y atributos. I Elegir los tipos de relación. I Definir las restricciones. Vamos a introducir los elementos del modelo con un ejemplo: I Debemos diseñar una base de datos para gestionar una empresa de preparación de platos preparados. I Algunos de los requerimientos para el sistema son los siguientes: Jesús Correas (DSIC - UCM) 5 / 32 Introducción. Ejemplo introductorio. Requerimientos: I En la empresa trabajan empleados de los que se necesita saber su nombre, número de la SS, puesto que ocupan (cocinero, pinche, limpiador...), teléfonos (fijo y móviles) y edad. I La empresa tiene varios establecimientos. I Existe una relación jerárquica entre los empleados. I La empresa elabora diferentes platos, que tienen un código, una descripción (cocido madrileño, torrijas, etc.) y un precio. I Cada plato está compuesto por ingredientes en cierta cantidad. I Cada empleado debe ser capaz de preparar entre uno y 8 platos. I Debemos saber los platos que sabe preparar cada empleado. I Necesitamos la información de los clientes (teléfono, email, pedidos que han realizado). I Cada pedido se identifica por un número, y debemos mantener la siguiente información: fecha del pedido, platos solicitados, cantidad. I Se debe conocer el proveedor que proporciona cada ingrediente en cada establecimiento, etc. Jesús Correas (DSIC - UCM) 6 / 32 Entidades Son los elementos básicos del modelo ER. Representan una “cosa” del mundo real, distinguible de todas las demás. Puede ser un objeto f́ısico (un veh́ıculo) o conceptual (un curso, un préstamo). Una entidad determinada se define mediante un conjunto de atributos: propiedades que lo describen. Un tipo de entidad define un conjunto de entidades que comparten los mismos atributos. Cada entidad tiene sus propios valores para cada atributo. Ejemplo: Una entidad “empleado” con los siguientes atributos: NSS: 1234567890 Nombre: Andrés Sánchez Garćıa Puesto: Pinche Edad: 25 Teléfono: 600123456 Jesús Correas (DSIC - UCM) 7 / 32 Atributos Cada atributo tiene asociado un dominio: conjunto de valores que puede tomar. Cada entidad de un tipo de entidad tiene asociado un valor a cada atributo. Los atributos pueden ser de distintos tipos: I simples (atómicos) o compuestos (se pueden descomponer en atributos más pequeños, ej. nombre y apellidos). I monovalorados o multivalorados (pueden tomar varios valores para una misma entidad, ej. color de un coche con varios tonos). I primitivos o derivados (se pueden calcular a partir de otros atributos o entidades, ej. fecha de nacimiento y edad). Si una entidad no tiene un valor para un atributo, toma el valor nulo (ej., si un empleado no tiene teléfono). Jesús Correas (DSIC - UCM) 8 / 32 Superclaves, claves candidatas, clave primaria Una superclave es un subconjunto de los atributos de un tipo de entidad que identifican uńıvocamente a cada entidad. I Cualquier superconjunto de una superclave también es superclave. I Interesa encontrar un conjunto de atributos lo mas pequeño posible que nos permita identificar uńıvocamente cada entidad. Una clave candidata es una superclave que no contiene ningún subconjunto que sea superclave. La clave primaria de una entidad es una clave candidata seleccionada por el diseñador. Ejemplo: Entidad empleado: I {NIF}, {NSegSocial}, {Teléfono} (móvil), {NIF, Nombre}, {NSegSocial, Nombre, Edad},... I No son superclaves: {Edad}, {Nombre, Edad}... I Claves candidatas: {NIF}, {NSegSocial}, {Teléfono} Normalmente cada entidad tiene un atributo que es clave. Un tipo de entidad es débil si no dispone de clave. Jesús Correas (DSIC - UCM) 9 / 32 Diagramas ER Representación gráfica de la estructura de la BD en un nivel conceptual. Elementos básicos: I Tipos de entidades: Se representan mediante una caja. F Para las entidades débiles se utiliza una caja doble. I atributos: Se representan mediante una elipse conectada con una ĺınea a su entidad. F Los atributos de la clave aparecen subrayados. F En los atributos multivalorados se utiliza una elipse doble. F Cada componente de un atributo compuesto se representa como atributo suyo (conectado mediante una ĺınea). F Los atributos derivados se representan mediante una elipse discontinua. I Tipos de relaciones: Se representan mediante un rombo unido con ĺıneas a las entidades asociadas. Jesús Correas (DSIC - UCM) 10 / 32 Ejemplo de diagrama ER: entidades y atributos NOMBRE COMPLETO EMPLEADO NOMBRE APELLIDO 1 APELLIDO 2 NSS EDAD PUESTO FEC.NAC. PLATO INGREDIENTE CODIGO PRECIO DESCRIPCION DESCRIPCION CODIGO TELEFONO EMPLEADO: {〈0101,(Juan,Garcı́a,Pérez),21,{666111222,911234567},Pinche,01.01.95〉, 〈0202,(José,Sanz,Sanz), 26, {666123231}, Metre, 01/01/90〉,...} PLATO: {〈PL001, Tortilla, 8.50e〉, 〈PL002, Flan, 3.00e〉, ...} INGREDIENTE: {〈IN001, Patata〉, 〈IN002, Huevo〉, 〈IN003, Leche〉, ...} Jesús Correas (DSIC - UCM) 11 / 32 Relaciones En las entidades del ejemplo existen relaciones impĺıcitas. Por ejemplo: entre un plato y sus ingredientes. ¿Se podŕıa resolver haciendo que ingrediente fuera atributo de plato? Un plato tiene varios ingredientes. Posibles soluciones: (1) Repetir el plato para cada uno de los ingredientes: 〈PL001, Tortilla, 8.50e, IN001, patatas 〉 〈PL001, Tortilla, 8.50e, IN002, huevos 〉 → redundancia. (2) Hacer el atributo ingrediente multivaluado: 〈PL001, Tortilla, 8.50e, {〈IN001, patata〉,〈IN002, huevo〉} 〉 〈PL002, Flan de huevo, 3.00e, {〈IN003, leche〉,〈IN002, huevo〉} 〉 F Cada ingrediente tiene atributos → redundancia. F El mismo ingrediente puede estar en varios platos → redundancia. I Además, un ingrediente no podŕıa estar en la BD si no está asociado a ningún plato. Solución: refinar el diseño y establecer relaciones entre las entidades. Jesús Correas (DSIC - UCM) 12 / 32 Relaciones Una relación es una asociación entre entidades. Un tipo de relación entre tipos de entidades E1, . . . ,En define las asociaciones entre entidades de cada tipo de entidad. Es una relación matemática: un subconjunto del producto cartesiano E1 × · · · × En: {(e1, . . . , en) | e1 ∈ E1, . . . , en ∈ En} Cada elemento de este conjunto es una instancia del tipo de relación. Ejemplo: Se puede definir una relación contiene entre las entidades plato e ingrediente: PLATO INGREDIENTECONTIENE Jesús Correas (DSIC - UCM) 13 / 32 Atributos de relaciones Cada plato puede contener un ingrediente dado en una cantidad diferente: cantidad es un atributo de la relación. Una relación puede tener atributos. cantidad PLATO INGREDIENTECONTIENE Los atributos solo deben asociarse a una relación si no se pueden añadir a ninguna de las entidades participantes: fecha apertura CLIENTE ES_TITULAR codigo saldo NIF nombre domicilio CUENTA Jesús Correas (DSIC - UCM) 14 / 32 Atributos de relaciones Cada plato puede contener un ingrediente dado en una cantidad diferente: cantidad es un atributo de la relación. Una relación puede tener atributos. cantidad PLATO INGREDIENTECONTIENE Los atributos solo deben asociarse a una relación si no se pueden añadir a ninguna de las entidades participantes: fecha apertura CLIENTE ES_TITULAR codigo saldo NIF nombre domicilio CUENTA Jesús Correas (DSIC - UCM) 14 / 32 Relaciones binarias y ternarias Las relaciones se pueden establecer entre dos o más entidades. Lo vemos con un ejemplo: Se quiere que la BD guarde información sobre las compras de ingredientes a proveedores en cada establecimiento. Intervienen tres entidades: I ingrediente, I establecimiento, con atributos codigo y direccion, I proveedor, con atributos CIF, RazonSocial y direccion. Debemos establecer relaciones entre estas entidades para conocer los proveedores que abastecen de cada ingrediente en cada establecimiento. Podemos crear dos relaciones: I abastece entre proveedor y establecimiento, I proporciona entre proveedor e ingrediente. Jesús Correas (DSIC - UCM) 15 / 32 Relaciones binarias y ternarias Con las relaciones abastece y proporciona no es posible contestar a la siguiente pregunta: ¿qué proveedor proporciona un ingrediente dado a un establecimiento determinado? I Cada relación contiene información por separado, pero no se puede combinar directamente. ingrediente: {(IN001, Patata), (IN002, Huevo), (IN003, Leche), ...} establecimiento: {(ES001, (C/Ponzano 3, 28001, Madrid)), (ES002, (C/Real s/n, 28140, Colmenar)),...} proveedor: {(CIF01, Granja Laurel, (C/Laurel s/n, 28200, Alcorcón)), (CIF02, Agrı́cola Roma, (Av/La Paz 30, 46400, Teruel)),...} Para resolver este problema se debe establecer una relación entre las tres entidades. El grado de una relación es el número de entidades que participan. Jesús Correas (DSIC - UCM) 16 / 32 Relaciones recursivas. También se pueden representar relaciones recursivas. En una relación recursiva un tipo de entidad participa en la relación varias veces con diferentes roles. Las ĺıneas que unen la entidad con la relación se etiquetan con cada rol. Por ejemplo: la relación supervisa entre un empleado (el jefe) y otro empleado (el subordinado). SUPERVISA EMPLEADO SUBORDINADOJEFE Jesús Correas (DSIC - UCM) 17 / 32 Restricciones: cardinalidad y participación. En los diagramas ER se pueden definir restricciones sobre las entidades que participan en una relación. Se utilizan para limitar las entidades que pueden participar en el conjunto de relaciones. Ejemplos: “Un plato contiene ingredientes (al menos uno).” “Cada empleado debe ser capaz de preparar entre uno y 8 platos.” Hay dos tipos principales de restricciones: I Restricciones de cardinalidad (o “razón de cardinalidad”). I Restricciones de participación. Jesús Correas (DSIC - UCM) 18 / 32 Restricciones de cardinalidad. Especifica el número máximo de relaciones en el que la entidad puede aparecer en el conjunto de relaciones. La razón de cardinalidad en una relación binaria entre A y B es el número de entidades a las que la otra entidad se puede asociar mediante un tipo de relación: I Uno a uno: Cada entidad de A se asocia a lo sumo con una entidad de B y viceversa. I Uno a varios (1:N) de A hacia B: Cada entidad de A se asocia con cero o más entidades de B, y cada entidad de B se asocia a lo sumo con una entidad de A. I Varios a varios (M:N): Cada entidad de A se asocia con cero o más entidades de B, y viceversa. Sea R una relación binaria entre E1 y E2. La cardinalidad de E1 es n (n puede ser 1 o N) si: fijada una entidad e2 ∈ E2 cualesquiera, se verifica que existen a lo sumo n entidades e1 ∈ E1 tales que 〈e1, e2〉 ∈ R. Jesús Correas (DSIC - UCM) 19 / 32 Restricciones de cardinalidad. Ejemplos. Uno a uno: Cada entidad de A se asocia a lo sumo con una entidad de B y viceversa. Ejemplo: relación dirige entre empleado y departamento (un dpto. solo puede tener un director y un empleado puede ser director de a lo sumo un dpto.). DEPARTAMENTOEMPLEADO DIRIGE 1 1 DEPARTAMENTOEMPLEADO DIRIGE Uno a varios: Cada entidad de A se asocia con cero o más entidades de B, y cada entidad de B se asocia a lo sumo con una entidad de A. Ejemplo: relación realiza entre cliente y pedido (un cliente puede hacer varios pedidos pero un pedido solo puede ser hecho por un cliente). CLIENTE REALIZA PEDIDO 1 N CLIENTE REALIZA PEDIDO Jesús Correas (DSIC - UCM) 20 / 32 Restricciones de cardinalidad. Ejemplos. Varios a varios: Cada entidad de A se asocia con cero o más entidades de B, y viceversa. Ejemplo: relación contiene entre plato e ingrediente (un plato puede contener varios ingredientes, y un ingrediente puede aparecer en varios platos). PLATO INGREDIENTECONTIENE PLATO INGREDIENTECONTIENE N M Jesús Correas (DSIC - UCM) 21 / 32 Restricciones de cardinalidad en relaciones ternarias. Una relación ternaria (A,B,C) puede tener cuatro razones de cardinalidad diferentes: (M,N,Q), (M,N,1), (N,1,1) y (1,1,1). Ejemplo: En una relación ternaria entre establecimiento, ingrediente y proveedor: I Si “todos los proveedores pueden suministrar a cualquier establecimiento varios ingredientes y un ingrediente puede ser suministrado a un establecimiento por varios proveedores”: es una relación (M,N,Q) I Pero si “un establecimiento solo puede ser abastecido de un ingrediente por un solo proveedor”: es una relación (M,N,1) Sea R una relación k-aria. La cardinalidad de Ei es n (n puede ser 1 o N) si: fijados e1 ∈ E1, . . . , ei−1 ∈ Ei−1, ei+1 ∈ Ei+1, . . . , ek ∈ Ek cualesquiera, se verifica que existen a lo sumo n entidades ei ∈ Ei tales que 〈e1, . . . , ei−1, ei , ei+1, ..., ek〉 ∈ R. Jesús Correas (DSIC - UCM) 22 / 32 Restricciones de participación. La restricción de participación indica si cada elemento de un tipo de entidad debe participar obligatoriamente en la relación o no. Corresponde al número ḿınimo de instancias de la relación en las que una entidad debe participar. Hay dos tipos: I participación total (1): cada entidad del tipo de entidad debe participar en al menos una instancia de la relación. Ejemplo: Todo pedido es realizado por un cliente. I participación parcial (0): algunas entidades del tipo de entidad pueden no participar en ninguna instancia de la relación. Ejemplo: Puede haber ingredientes que no formen parte de ningún plato. Se representan en el diagrama ER mediante una doble ĺınea si la participación es total, o una ĺınea simple si es parcial. Notación alternativa: cada ĺınea de la relación contiene un par (ḿın,máx) que indica el número de veces ḿınimo y máximo que aparece la entidad en la relación. Jesús Correas (DSIC - UCM) 23 / 32 Restricciones de participación. Ejemplos. Entre cliente y pedido: “un cliente puede realizar varios pedidos (o ninguno), un pedido está asociado obligatoriamente a un (y solo un) cliente”1: CLIENTE REALIZA PEDIDO (0,N) (1,1) CLIENTE REALIZA PEDIDO Entre plato e ingrediente: “Todo plato tiene al menos un ingrediente, pero puede haber ingredientes no contenidos en ningún plato”: PLATO INGREDIENTECONTIENE (0,N)(1,N) PLATO INGREDIENTECONTIENE 1Observa que con esta notación la N está en el otro lado de la relación (Elmasri, sec. 3.7.4). Jesús Correas (DSIC - UCM) 24 / 32 Tipos de entidad débiles. Un tipo de entidad débil es la que no tiene suficientes atributos para formar una clave primaria. Debe estar asociado siempre a otra entidad, denominada entidad identificadora o propietaria, mediante una relación identificadora. La relación identificadora debe ser: I Una relación uno a varios (de la entidad identificadora a la entidad débil). I La participación de la entidad débil debe ser total. El tipo de entidad débil śı debe tener atributos que formen una clave parcial que discrimine entre las distintas entidades débiles que se corresponden con cada entidad identificadora. Jesús Correas (DSIC - UCM) 25 / 32 Tipos de entidad débiles. En el diagrama ER se representan mediante una caja doble y un rombo doble. Ejemplo: “Los ingredientes se almacenan en estantes situados en distintas ubicaciones (frigoŕıfico,
Compartir