Descrivere e realizzare un db per la gestione di una biblioteca, considerando che gli utenti possano prelevare dei libri,
tenerli per un cesto periodo di tempo e poi riconsegnarli. Effettuare le seguenti query:
Tutti i libri prestati ad un utente specifico in ordine cronologico.
Individua i primi tre lettori che hanno letto più libri.
Individua tutti i possessori dei libri non ancora rientrati e il titolo degli stessi.
Dare lo storico dei libri chiesi in prestito da un utente indicando il periodo.
Fai la classifica dei libri maggiormente prestati.
Individua prestiti la cui durata supera i 15gg.
Analisì della realtà
Il Database in oggetto presenta la realtà di una biblioteca nella quale sono presneti degli oggetti riconoscibili
attraverso la lettura del testo. I libri presenti nella bilbioteca potranno esssere prelevati dagli utenti, i quali
potranno tenerli per una certa quantità di tempo, per poi successivamente riconsegnarli alla struttura. Dall'analisi si
possono quindi dedurre le seguenti entità:
Libro
Titolo
Autore
Cliente
Nome
Cognome
Dalla lettura delle richieste possiamo notare che le due entità avranno una relazione
molti-a-molti per quanto riguarda il prestito, che non dipende da nessuno dei due oggetti. Conoscendo questa relazione
quindi, poissiamo aggiungere alla lista delle entità la tabella:
Prestito
La quale terrà traccia delle date di inizio e fine prestito di ogni libro, con annesso l'ID del cliente.
Con queste tre tabelle otteniamo lo schema E/R:
CREATE TABLE Libro (
idLibro int(11) AUTO_INCREMENT,
Titolo varchar(50) NOT NULL,
Autore varchar(50),
PRIMARY KEY (idLibro)
);
CREATE TABLE Cliente (
idCliente int(11) AUTO_INCREMENT,
Nome varchar(50) NOT NULL,
Cognome varchar(50) NOT NULL,
PRIMARY KEY (idCliente)
);
CREATE TABLE Prestito (
idPrestito int(11) AUTO_INCREMENT,
idLibroP int(11),
idClienteP int(11),
dataInizio date NOT NULL,
dataFine date,
PRIMARY KEY (idPrestito),
FOREIGN KEY (idLibroP) REFERENCES Libro(idLibro)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (idClienteP) REFERENCES Cliente(idCliente)
ON DELETE SET NULL
ON UPDATE CASCADE
);
-- INSERT INTO Libro(Titolo, Autore) VALUES ("The Hobbit", "J. R. R. Tolkien");
-- INSERT INTO Cliente(Nome, Cognome) VALUES ("Alessio", "Cucinotta");
-- INSERT INTO Prestito(idLibroP, idClienteP, dataInizio, dataFine) VALUES (1, 1 , '2023-04-25', '2023-05-07');
INSERT INTO Libro (Titolo, Autore) VALUES
('Il nome della rosa', 'Umberto Eco'),
('1984', 'George Orwell'),
('Il signore degli anelli', 'J.R.R. Tolkien'),
('Il giovane Holden', 'J.D. Salinger'),
('Lolita', 'Vladimir Nabokov'),
('Orgoglio e pregiudizio', 'Jane Austen'),
('Il processo', 'Franz Kafka'),
('Moby Dick', 'Herman Melville'),
('La divina commedia', 'Dante Alighieri'),
('Guerra e pace', 'Lev Tolstoj');
INSERT INTO Cliente (Nome, Cognome) VALUES
('Mario', 'Rossi'),
('Luca', 'Bianchi'),
('Paolo', 'Verdi'),
('Giuseppe', 'Neri'),
('Marta', 'Gialli'),
('Sara', 'Viola'),
('Carla', 'Arancioni'),
('Giovanni', 'Rosa'),
('Elena', 'Celesti'),
('Roberto', 'Marroni');
INSERT INTO Prestito (idLibroP, idClienteP, dataInizio, dataFine) VALUES
(1, 2, '2022-01-01', '2022-02-01'),
(2, 3, '2022-01-02', '2022-02-02'),
(3, 4, '2022-01-03', '2022-02-03'),
(4, 5, '2022-01-04', '2022-02-04'),
(5, 6, '2022-01-05', '2022-02-05'),
(6, 7, '2022-01-06', '2022-02-06'),
(7, 8, '2022-01-07', '2022-02-07'),
(8, 9, '2022-01-08', '2022-02-08'),
(9, 10, '2022-01-09', '2022-02-09'),
(10, 1, '2022-01-10', '2022-02-10');
Tutti i libri prestati ad un utente specifico in ordine cronologico.
SELECT Libro.Titolo, Prestito.dataInizio, Prestito.dataFine
FROM Libro, Cliente, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
AND Cliente.idCliente = Prestito.idClienteP
AND Cliente.Nome = "Alessio"
AND Cliente.Cognome = "Cucinotta"
ORDER BY Prestito.dataInizio;
Individua i primi tre lettori che hanno letto più libri.
SELECT Cliente.Nome, Cliente.Cognome, COUNT(Prestito.idClienteP) AS Totale
FROM Libro, Cliente, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
AND Cliente.idCliente = Prestito.idClienteP
GROUP BY Cliente.Cognome
ORDER BY Totale DESC
LIMIT 3;
Individua tutti i possessori dei libri non ancora rientrati e il titolo degli stessi.
SELECT Cliente.Nome, Cliente.Cognome, Libro.Titolo, Prestito.dataInizio
FROM Libro, Cliente, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
AND Cliente.idCliente = Prestito.idClienteP
AND Prestito.dataFine < CURDATE() OR Prestito.dataFine = Null;
Dare lo storico dei libri chiesi in prestito da un utente indicando il periodo.
SELECT Libro.Titolo, Libro.Autore, Prestito.dataInizio, prestito.dataFine
FROM Libro, Cliente, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
AND Cliente.idCliente = Prestito.idClienteP
AND Cliente.Nome = "Alessio"
AND Cliente.Cognome = "Cucinotta"
AND Prestito.dataInizio >= '2023-02-01'
AND Prestito.dataFine <= '2023-06-01';
Fai la classifica dei libri maggiormente prestati.
SELECT Libro.Titolo, Libro.Autore, COUNT(Prestito.idLibroP)
FROM Libro, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
GROUP BY Libro.Titolo
HAVING COUNT(Prestito.idLibroP)
ORDER BY COUNT(Prestito.idLibroP);
Individua prestiti la cui durata supera i 15gg.
SELECT Prestito.idPrestito, Cliente.Cognome, Libro.Titolo
FROM Libro, Cliente, Prestito
WHERE Libro.idLibro = Prestito.idLibroP
AND Cliente.idCliente = Prestito.idClienteP
AND DATEDIFF(dataFine, dataInizio) > 15;
