TIA Formativa
Tipos de consultas
Caso de estudio “Biblioteca”
Por:
Jeison Esteban Arrubla Arango
ORIENTACIONES:
Se tienen los siguientes requerimientos:
Diseñar una base de datos que permita la gestión de préstamos, reservas y devolución de materiales de una biblioteca.
Sobre un conjunto de entrevistas con los usuarios la información de la biblioteca se resume de la siguiente forma:
1. La biblioteca cuenta con tipos de materiales como revistas, periódicos, libros y material audiovisual.
2. Para los materiales nos interesa conocer su título o nombre, año de creación y valor.
3. Las reservas se hacen por materiales siendo importante guardar la fecha de la reserva, el material y el usuario que la hace.
4. Los libros tienen ejemplares.
5. Para los préstamos se tiene en cuenta el código del libro, el ejemplar, el usuario, la fecha entrega y la fecha devolución.
6. Los usuarios se clasifican en varios tipos: estudiantes, docentes, empleados, etc. y pertenecen a más de una dependencia es el caso de un usuario profesor que puede pertenecer a sistemas y a judicial.
�
2
CASO DE ESTUDIO: RESERVA, PRÉSTAMO Y DEVOLUCIÓN DE MATERIALES DE UNA BIBLIOTECA
Las siguientes tablas componen la base de datos:
�
3
BASE DE DATOS : RESERVA, PRÉSTAMO Y DEVOLUCIÓN DE MATERIALES DE UNA BIBLIOTECA
Para la realización de esta tarea tenga en cuenta:
Las consultas que debe realizar son las siguientes:
Consultas simples
1. Mostrar los datos de todos los usuarios Alba, Juan y Camila con un estado vigente.
2. Mostrar los datos de todos los materiales con un valor entre 30.000 y 80.000 y con un año mayor que 1.998
Consultas simples de varias tablas
3. Mostrar los datos de todos los materiales con un valor mayor que 35.000 y se han prestado
4. Mostrar los datos de todos los materiales libros o revistas con un año mayor que 1.999 y se han reservado a usuarios de las dependencias Sistemas o judicial.
�
4
INSTRUCCIONES
Consultas de agrupación o resumen
5. Mostrar los datos de los materiales con un valor mayor que 30.000 y que se han reservado más de una vez.
6. Mostrar los datos de los usuarios que han prestado materiales más de 10 veces.
Subconsultas o anidamientos de consultas
7. Mostrar los datos de todos los materiales con un valor mayor que 35.000 que no se han prestado
8. Mostrar los datos de todos los usuarios con estado vigente y no han prestado materiales con año menor a 2.000.
9. Mostrar los datos de los materiales que tienen un precio mayor que los materiales tipo audiovisual o revista.
�
�
5
INSTRUCCIONES
USE DBBiblioteca2016;
-- consulta simple
-- Mostrar los datos de todos los materiales con un valor entre 30.000 y 80.000 y con un año mayor que 1.998
SELECT *
FROM [tblMaterial]
WHERE [Valor] BETWEEN 30000 AND 80000
AND [anio] > 1998;
�
6
CONSULTAS SIMPLES
Mostrar los datos de todos los materiales con un valor mayor que 35.000 y se han prestado
SELECT m.[Cod_material], m.[Valor]
FROM [tblMaterial] m
INNER JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]
WHERE m.[Valor] > 35000;
Para obtener estos resultados modificamos los registros de la tabla, ya que no habían materiales que cumplieran con las condiciones de la consulta. A continuación se muestran los registros de la tabla tblMaterial
7
CONSULTAS SIMPLES DE VARIAS TABLAS
Mostrar los datos de los materiales con un valor mayor que 30.000 y que se han reservado más de una vez.
SELECT m.[Cod_material], m.[Valor], COUNT(r.[Cod_reserva]) AS [Numero_Reservas]
FROM [tblMaterial] m
INNER JOIN [tblReserva] r ON m.[Cod_material] = r.[Cod_Material]
WHERE m.[Valor] > 30000
GROUP BY m.[Cod_material], m.[Valor]
HAVING COUNT(r.[Cod_reserva]) > 1;
Agregamos algunas reservas para que la consulta genere resultados:
8
CONSULTAS DE AGRUPACIÓN O RESUMEN
Mostrar los datos de todos los materiales con un valor mayor que 35.000 que no se han prestado
SELECT m.[Cod_material], m.[Valor]
FROM [tblMaterial] m
LEFT JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]
WHERE m.[Valor] > 35000
AND p.[Cod_Material] IS NULL;
9
SUBCONSULTAS O ANIDAMIENTOS
Obtener un informe que muestre los materiales que han sido prestados más de dos veces, junto con la cantidad de veces que han sido reservados, y filtrarlos por aquellos cuyo valor es mayor a 50,000. Además, debe incluir el nombre del tipo de material al que pertenecen (por ejemplo, libros, películas, etc.) y ordenarlos de manera descendente por la cantidad de veces que han sido reservados.
SELECT m.[Cod_material],
m.[Nombre_material],
m.[Valor],
tm.[NombreTipo_Material],
COUNT(p.[Cod_Prestamo]) AS Total_Prestamos,
COUNT(r.[Cod_reserva]) AS Total_Reservas
FROM [tblMaterial] m
INNER JOIN [tblTipo_Material] tm ON m.[CodTipo_Material] = tm.[CodTipo_Material]
LEFT JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]
LEFT JOIN [tblReserva] r ON m.[Cod_material] = r.[Cod_Material]
WHERE m.[Valor] > 50000
GROUP BY m.[Cod_material], m.[Nombre_material], m.[Valor], tm.[NombreTipo_Material]
HAVING COUNT(p.[Cod_Prestamo]) > 2
ORDER BY Total_Reservas DESC;
10
CONSULTA PROPUESTA