B1

3º Ingeniero Técnico en Informática de Sistemas Asignatura: Fundamentos de Bases de Datos Facultad de Informática Curso

Views 953 Downloads 39 File size 127KB

Report DMCA / Copyright

DOWNLOAD FILE

Recommend stories

Citation preview

3º Ingeniero Técnico en Informática de Sistemas Asignatura: Fundamentos de Bases de Datos

Facultad de Informática Curso: 2007/2008

PRÁCTICA B1. Consultas en SQL Objetivo • Construir, depurar y ejecutar sentencias SQL de obtención de información almacenada en una base de datos. Contenidos Alquiler de coches Se ha diseñado una base de datos para almacenar y gestionar la información empleada por una empresa dedicada al alquiler de automóviles, teniendo en cuenta los siguientes aspectos: La empresa dispone de un conjunto de coches para su alquiler. Se necesita conocer la matrícula, marca y modelo, el color y el precio de alquiler de cada coche. Además cada coche está asignado a un determinado garaje, que no puede cambiar. De cada garaje interesa conocer su código, denominación y dirección. Los datos que interesa recoger de cada cliente son el DNI, nombre, dirección, ciudad y número de teléfono; además, los clientes se diferencian por un código interno de la empresa. Si un cliente desea solicitar el alquiler de algún coche, se pone en contacto con una de las agencias de la empresa de alquiler y realiza una reserva. De cada agencia interesa conocer su código y nombre. Un mismo cliente puede haber realizado varias reservas a lo largo del tiempo, y por supuesto algunas de esas reservas pueden tener sus fechas solapadas. Como hemos indicado, en cada reserva participa una agencia, que no tiene que ser la misma para las distintas reservas de un mismo cliente. Una misma reserva puede incluir uno o varios coches. De cada reserva interesa registrar la fecha de inicio (cuándo deben entregarse los vehículos al cliente), la fecha de finalización (la fecha prevista de devolución por parte del cliente), el coste (€) total de la reserva y un indicador de si los coches (todos los alquilados mediante la misma reserva) han sido devueltos, en cuyo caso la reserva se considera cerrada. Además, para cada vehículo interesa recoger los litros de combustible que contiene en el momento de su entrega al cliente. El coste final de alquiler de cada coche se obtiene multiplicando su precio de alquiler por los días que el cliente desea reservarlo. El coste total de una reserva se obtiene sumando los costes finales de alquiler de los coches que incluye dicha reserva.

Un esquema de base de datos relacional correspondiente al anterior enunciado contiene las tablas que se indican en la página siguiente. Los atributos que aparecen subrayados son las claves primarias, y los que aparecen en cursiva son claves externas. Para cada clave externa se indica a qué relación y atributos concretos hace referencia. El esquema de la base de datos está creado en la base de datos Oracle de prácticas, ha sido cargado con datos adecuados, y está accesible desde las cuentas de usuario de los grupos de prácticas de esta asignatura.

3º Ingeniero Técnico en Informática de Sistemas. Fundamentos de Bases de datos. Práctica B1. Consultas en SQL - 1/4

Esquema “ALQUILER DE COCHES”, en el modelo relacional de datos COCHE ( matricula, marca, modelo, color, garaje, precio_alquiler ) Clave externa:

garaje

→ GARAJE(codigo)

GARAJE ( codigo, nombre, direccion ) CLIENTE ( dni, codigo, nombre, direccion, ciudad, telefono ) AGENCIA ( codigo, nombre ) RESERVA ( numero, fecha_inicio, fecha_fin, cliente, agencia, coste_total, cerrada ) Claves externas:

cliente → CLIENTE(codigo) agencia → AGENCIA(codigo)

LISTA_RESERVA ( reserva, coche, litros_inicio, coste_coche ) Claves externas:

reserva coche

→ RESERVA(numero) → COCHE(matricula)

Diagrama Referencial del esquema “ALQUILER DE COCHES” La siguiente representación gráfica del esquema relacional de base de datos será de ayuda durante la resolución de los ejercicios propuestos. Cada flecha indica una referencia de clave externa, y está dirigida hacia la relación referenciada. AGENCIA GARAJE

COCHE

RESERVA

CLIENTE

LISTA_RESERVA

En primer lugar, se aconseja ejecutar el comando DESC de SQL*Plus sobre cada tabla del esquema, para observar las características de las columnas que la componen. Después, es conveniente ejecutar la sentencia SELECT * FROM... sobre cada una de las tablas, a fin de visualizar los datos almacenados en las mismas.

Consultas de entrenamiento (no hay que entregar estos ejercicios) Antes de intentar resolver los ejercicios cuyas sentencias se deben entregar en el informe de esta práctica, se recomienda resolver las siguientes consultas, cuya dificultad es baja y media. Ello resultará útil para comprender mejor el esquema de bases de datos “Alquiler de Coches” y, sobre todo, para adquirir destreza con SQL y SQL*Plus de Oracle. 1.

Reservas (numero) realizadas por los clientes de ‘Murcia’.

2.

Resumen de las reservas (numero, fecha_inicio, fecha_fin, coste_total) pendientes de devolución, ordenado por fecha de finalización.

3.

Agencias (codigo, nombre) que participan en reservas aún no devueltas.

4.

Número de reservas en las que ha participado cada coche (coche, cuantas_reservas).

5.

Clientes (codigo, nombre) que hayan realizado alguna reserva mediante la agencia con código 2 y que hayan realizado en total más de 3 reservas.

6.

Para cada cliente, obtener el número de reservas realizadas y el precio medio de todas ellas (cliente, cuantas_reservas, precio_medio).

7.

Agencias (codigo, nombre) que no participan en ninguna reserva con fecha de inicio posterior al 15 de enero de 2004.

8.

Coches (matrícula, marca, modelo) reservados más de 3 veces.

9.

Matrícula del vehículo (coche) que más veces ha sido reservado.

10. Coches (matrícula, marca, modelo) que nunca han sido reservados.

3º Ingeniero Técnico en Informática de Sistemas. Fundamentos de Bases de datos. Práctica B1. Consultas en SQL - 2/4

Consultas que se debe entregar Cada ejercicio ha de resolverse mediante una única sentencia SELECT escrita en SQL de Oracle, la cual, por supuesto, puede incluir varias SELECT anidadas o ser el resultado de la unión, intersección, etc. de diversas SELECT. En cada ejercicio se indica qué columnas deben aparecer en el resultado, incluyendo nombres nuevos para columnas cuyo valor se calcula, por ejemplo, con una función. Es necesario utilizar SQL*Plus para escribir las sentencias, depurarlas, ejecutarlas y comprobar su resultado. Para acortar y unificar la escritura de los SELECT, los pseudónimos o alias que se usarán en la cláusula FROM serán los siguientes: GARAJE G, COCHE C, CLIENTE T, AGENCIA A, RESERVA R, LISTA_RESERVA L

1. Reservas (numero, fecha_inicio, fecha_fin, coche, marca, modelo) de coches del garaje con código ‘G1’ realizadas a través de la agencia con el código 4, ordenado por número de reserva. 2. Primera reserva que se inició y período de duración (reserva, fecha_inicio, cuantos_dias). 3. Garajes (código, nombre) de los que nunca se ha reservado ningún coche. 4. Clientes (dni, nombre) que hayan reservado algún coche de la marca ‘Volkswagen’ más de 2 veces. Se debe considerar las reservas individuales de los coches, es decir, si una misma reserva consiste en el alquiler de varios coches de esta marca, debe contabilizarse cada uno de ellos. 5. Coche que más veces ha sido reservado, indicando cuántas veces (matricula, marca, modelo, cuantas_reservas). 6. Coche que más veces ha sido reservado por el mismo cliente, indicando cuántas veces ha sido reservado (coche, cliente, cuantas_reservas). 7. Última reserva realizada por cada cliente (codigo, numero, fecha_inicio). También deben aparecer los clientes que no tienen ninguna reserva, mostrando NULL en las columnas numero y fecha_inicio. El listado ha de estar ordenado por código de cliente. 8. Para cada cliente que ha hecho menos de 3 reservas (cliente), mostrar la suma total del coste de las reservas realizadas por dicho cliente (suma_coste). Se debe considerar las reservas globales, es decir, si una reserva consiste en el alquiler de varios coches, sólo se contabiliza una vez. 9. Reservas (numero) que sólo incluyen un coche (coche), es decir, con un único coche en la lista de reservas, y mediante qué agencia se ha contratado (agencia), ordenadas por nº de reserva. 10. Clientes que sólo han hecho reservas a través de una única agencia (cliente, agencia), por orden de código de cliente. 11. Clientes (dni, nombre) que han hecho reservas por medio de todas las agencias. 12. Para cada coche (matricula), indicar cuántas veces se ha reservado (cuantas_reservas) y cuál es la fecha de inicio de la reserva más reciente (fecha_inicio). En el resultado también deben aparecer los coches que no han sido reservados ninguna vez, mostrando en la columna cuantas_reservas un 0, y un NULL en la fecha_inicio. El resultado debe estar ordenado por matrícula. 13. Agencias (codigo, nombre) que participan en más de 3 reservas cuya duración es de una semana o más. 14. Número y coste de la reserva más cara realizada para cada marca y modelo de coche (reserva, marca, modelo, coste_coche). Se debe considerar las reservas individuales de los coches. 15. Obtener el coche que ha sido reservado mediante la agencia 1 con la mayor cantidad de litros de combustible al inicio de la reserva, mostrando además la matrícula, marca y modelo del vehículo, la cantidad de litros de combustible de inicio y el nombre del garaje al que está asignado el coche (matricula, marca, modelo, litros_inicio, nombre).

3º Ingeniero Técnico en Informática de Sistemas. Fundamentos de Bases de datos. Práctica B1. Consultas en SQL - 3/4

Fecha de entrega Lunes, 26 de Noviembre de 2007.

Documentación que se debe entregar La entrega se realizará mediante SUMA, antes de la fecha límite indicada. En el área privada (“Mis Contenidos”) de la asignatura, todos los miembros de cada grupo crearán una carpeta denominada “practicas”. Dentro de esta carpeta, crearán una subcarpeta que se denominará “b1-consultas”. En esta carpeta (insistimos: cada miembro de cada grupo) depositará el informe de realización de la práctica, junto con el conjunto de guiones (scripts) SQL. El formato de estos ficheros será el siguiente: - Informe de realización de la práctica llamado “fbdsXXX-b1” (.doc o .rtf), cuyas páginas han de estar numeradas y que debe incluir...  Portada, que muestre estos datos: - asignatura, curso académico (2007/08) y convocatoria (junio, septiembre, febrero), - identificador (B1) y nombre de la práctica (Consultas en SQL), - nombre del grupo de prácticas (fbdsXXX, sustituyendo XXX por el número de grupo asignado), - nombre y apellidos de cada componente del grupo, - dirección de correo electrónico de cada integrante del grupo de prácticas.  Para cada uno de los 15 ejercicios deberá aparecer lo siguiente: - número de ejercicio y enunciado del ejercicio. - comentarios y aclaraciones que se consideren necesarios. - Comandos SQL*Plus necesarios (si es el caso) para dar formato al resultado de la consulta. - texto de la consulta (SELECT) con las sangrías adecuadas y fácilmente legible. - filas devueltas, formateadas de manera que resulten legibles. - número de filas devueltas por la consulta. Se pueden presentar varias soluciones alternativas de un mismo ejercicio. En ese caso, para cada una de ellas se indicará el texto de la consulta (SELECT), las filas del resultado y el nº de filas devueltas. Puede indicarse la alternativa que se considera más adecuada.

- Guiones SQL (scripts). Ha de crearse un guión (fichero de texto plano) por cada ejercicio, llamado ‘b1-nn.sql’ (nn es el nº de ejercicio), que contendrá… - número de ejercicio. - comandos de SQL*Plus necesarios para dar formato al resultado de la consulta. - texto de la consulta (SELECT) con las sangrías adecuadas y fácilmente legible. - NO DEBE APARECER el resultado de la ejecución de la consulta. Es posible ayudarse del comando SPOOL de SQL*Plus para la elaboración del informe así como del contenido de los guiones (scripts) SQL.

Criterios de evaluación obligatorio entregar vía SUMA el informe de la práctica y todos los guiones (scripts SQL). No se corregirá la práctica si no se ha presentado la documentación completa (por ejemplo, si sólo se entregan algunos ejercicios y otros se dejan en blanco, o si se entregan sólo algunos scripts).  La lectura del informe ha de ser suficiente para la comprensión total del trabajo realizado en la práctica (sin necesidad de recurrir a los guiones).  No es suficiente con que “los SELECT funcionen”, esto es, que devuelvan las filas correctas, sino que se valorará el estilo en la resolución de cada consulta, las explicaciones dadas y la organización de la documentación explicativa (informe) de la práctica.  Importante: es obligatorio que los resultados que se presenten en el informe correspondan a la ejecución de las consultas sobre el esquema ya existente en la base de datos de prácticas de la Facultad (y que es común a todos los grupos de prácticas).  Es

3º Ingeniero Técnico en Informática de Sistemas. Fundamentos de Bases de datos. Práctica B1. Consultas en SQL - 4/4