SOLUZIONI COMMENTATE
Nel database Biblioteca vi sono le seguenti tabelle:
Utenti := < id, nome, cognome, indirizzo,città, tel_fisso,cellulare >
Prestiti := < id, id_libro, id_utente, data_ora_prestito, data_ora_restituzione>
Libri := < id, titolo, descrizione, data_pubblicazione>
Autori:=<id,nome,cognome>
Autori_Libri := <id_autore,id_libro>
1) Creazione tabelle:
Di seguito sono riportati gli statement di creazione di MySQL dove figura KEY al posto di INDEX e CONSTRAINT <nome indice chiave esterna> prima di FOREIGN KEY.
CONSTRAINT significa letteralmente VINCOLO . Infatti serve a specificare un vincolo referenziale
CREATE TABLE `biblioteca`.`autori` (
`id` int(10) unsigned NOT NULL auto_increment, `nome` varchar(45) NOT NULL,
`cognome` varchar(45) NOT NULL, PRIMARY KEY (`id`),
KEY `nome_cognome` (`nome`,`cognome`) )
CREATE TABLE `biblioteca`.`libri` (
`id` int(10) unsigned NOT NULL auto_increment, `titolo` varchar(100) NOT NULL,
`descrizione` varchar(500) NOT NULL, `data_pubblicazione` date NOT NULL, PRIMARY KEY (`id`)
)
CREATE TABLE `biblioteca`.`utenti` (
`id` int(10) unsigned NOT NULL auto_increment, `nome` varchar(45) NOT NULL,
`cognome` varchar(45) NOT NULL, `indirizzo` varchar(100) NOT NULL, `città` varchar(45) NOT NULL, `tel_casa` varchar(10) NOT NULL, `cellulare` varchar(10) NOT NULL, PRIMARY KEY (`id`),
KEY `nome_cognome` (`nome`,`cognome`) )
OSSERVAZIONE:
Notare che si sono definiti degli indici su nome e cognome utente e nome e cognome autore perchè si presume che si effettueranno frequentemente ricerche per utente o per autore (specificando sia nome che cognome).
Ha senso definire degli indici su questi campi perchè sono relativamente brevi e la ricerca viene effettuata confrontando le stringhe a partire dal primo carattere, mentre nel caso del titolo o della descrizione dei libri si
ricercano parole “all'interno” della stringa per cui gli indici, che si basano sull'ordinamento dei valori e sull'associazione di valori numerici alle stringhe, non porterebbero i vantaggi desiderati(sarebbe più conveniente creare un indice delle singole parole alla maniera di Google o Word: un'altro “storage engine” di MySQL(quello che si utilizza qui è InnoDB che permette di specificare vincoli referenziali), MySQL ISAM fa proprio questo con i campi Text e Blob con indici detti FULLTEXT vedi al riguardo Differenze My ISAM e InnoDB). Per ulteriori dettagli vedi INDICI (par.2.4) Database Open Office
CREATE TABLE `biblioteca`.`prestiti` (
`id` int(10) unsigned NOT NULL auto_increment, `id_libro` int(10) unsigned NOT NULL,
`id_utente` int(10) unsigned NOT NULL, `data_ora_prestito` datetime NOT NULL, `data_ora_restituzione` datetime ,
PRIMARY KEY (`id`), KEY `fk_utente` (`id_utente`), KEY `fk_libro` (`id_libro`),
CONSTRAINT `fk_libro` FOREIGN KEY (`id_libro`) REFERENCES `libri` (`id`) ON DELETE RESTRICT
ON UPDATE CASCADE,
CONSTRAINT `fk_utente` FOREIGN KEY (`id_utente`) REFERENCES `utenti` (`id`) ON DELETE RESTRICT
ON UPDATE CASCADE )
OSSERVAZIONE IMPORTANTE: notare che data_ora_restituzione NON ha il vincolo NOT NULL perchè quando prendo in prestito un libro non immetto un valore per data_ora_restituzione che rimane pari a NULL fino al momento della restituzione.
CREATE TABLE `biblioteca`.`autori_libri` ( `id_autore` int(10) unsigned NOT NULL, `id_libro` int(10) unsigned NOT NULL, KEY `FK_autore` (`id_autore`),
KEY `FK_libri` (`id_libro`),
CONSTRAINT `FK_autore` FOREIGN KEY (`id_autore`) REFERENCES `autori` (`id`) ON DELETE RESTRICT
ON UPDATE CASCADE,
CONSTRAINT `FK_libri` FOREIGN KEY (`id_libro`) REFERENCES `autori` (`id`) ON DELETE RESTRICT
ON UPDATE CASCADE ) ;
OSSERVAZIONE IMPORTANTE:
I vincoli sull'update per le tabelle autori_libri e prestiti sono in effetti superflui poichè essendo la chiave primaria un semplice contatore autoincrementato è improbabile che venga modificata (avrebbe senso se la chiave primaria fosse inserita direttamente in fase di immissione dei dati es. CODICE FISCALE).
Andrebbe benissimo anche l'opzione ON UPDATE RESTRICT.
In effetti in questo caso poiché le opzioni di default di MySQL sono proprio RESTRICT su DELETE e su UPDATE potrei fare a meno di specificare i vincoli referenziali.
Tuttavia all'esame e al compito dovete comunque commentare questa omissione per far capire al docente che
sapete cos'è un vincolo referenziale, quando e perchè viene applicato e perchè eventualmente può essere omesso.
ESERCIZIO 2)
Quando devo cercare qualcosa all'interno di una stringa(una sequenza di caratteri ASCII) devo ricorrere a LIKE:
ESERCIZIO 3)
E' possibile risovere il problema posto in maniere differenti:
Prima Soluzione)
Creo una tabella di “supporto” che contenga il numero di libri scritti dai vari autori. Posso ottenere questa tabella nel seguente modo:
CREATE TABLE libriscritti(
SELECT id_autore ,COUNT(*) as num_libri FROM autori_libri
GROUP BY id_autore HAVING num_libri>2 );
Il motivo per cui ho creato questa tabella è che viene richiesto il nome e cognome di ogni autore e questi non posso metterli direttamente nella SELECT se nella GROUP BY metto solo id_autore (vedi subito dopo il titolo “Raggruppamenti” Guida SQL).
Procedo pertanto come segue: mi salvo il risultato della SELECT precedente in una tabella di “appoggio”
che chiamo libriscritti ed effettuo una GIUNZIONE tra questa tabella e autori. In ultimo effettuo la PROIEZIONE su nome e cognome della tabella autori .
SELECT titolo,descrizione FROM libri
WHERE (descrizione LIKE '%divulgazione%') AND (descrizione LIKE '%universo%');
SELECT nome, cognome FROM autori, libriscritti WHERE (autori.id = libriscritti.id_autore)
Seconda Soluzione)
Non ho bisogno di creare effettivamente un'altra tabella, posso semplicemente mettere la SELECT di prima all'interno della clausola FROM e le assegno come alias libriscritti come mostrato di seguito
SELECT nome, cognome
FROM autori,(SELECT id_autore ,COUNT(*) as num_libri FROM autori_libri GROUP BY id_autore HAVING num_libri>2) AS libriscritti
WHERE (autori.id = libriscritti.id_autore)
Terza Soluzione)
E' una soluzione ancora più semplice delle precedenti. Posso raggruppare in base a 3 attributi invece che ad uno solamente:
1. Prendo anche la tabella autori ed effettuo la giunzione con la tabella autori_libri
2. Raggruppo in base agli attributi: id_autore, nome e cognome (in questa maniera si ottenengono gli stessi gruppi ottenuti effettuando il raggruppamento solo in base a id_autore perchè nome e cognome risulteranno gli stessi a parità di id_autore e pertanto gli attributi aggiunti nella clausola GROUP BY non modificano la composizione del gruppo)
In finale ottengo:
SELECT nome, cognome FROM autori_libri, autori
WHERE (autori.id = autori_libri.id_autore )
GROUP BY id_autore,nome,cognome HAVING COUNT(*)>2
Le tre soluzioni appena analizzate sono riassunte nel seguente riquadro:
ESERCIZIO 4)
Si parte dall'individuazione delle tabelle che contengono i dati di interesse:
Da un lato devo visualizzare nome e cognome degli utenti quindi avrò bisogno di utenti, dall'altro mi serve data_ora_prestito che si trova nella tabella prestiti.
Come sempre quando si ha a che fare con più di una tabella si deve effettuare la GIUNZIONE(JOIN) ( più esattamente L'INNER JOIN ) tra le tabelle ovvero filtrare solo i record effettivamente associati delle 2 tabelle dal “miscuglio” del prodotto cartesiano.
FROM utenti,prestiti mi restituirebbe, infatti, il prodotto cartesiano delle 2 tabelle ovvero tutte le possibili coppie utente,prestito anche se l'utente non ha effettuato il prestito in questione!!!
Allo scopo di associare ogni utente con il prestito che ha effettuato mi serve il legame espresso dall'uguaglianza chiave esterna = chiave primaria.
In questo modo si ottiene una tabella con tutti gli attributi di utenti e prestiti CREATE TABLE libriscritti(
SELECT id_autore ,COUNT(*) as num_libri FROM autori_libri
GROUP BY id_autore HAVING num_libri>2 );
SELECT nome, cognome FROM autori,libriscritti
WHERE (autori.id = libriscritti.id_autore)
oppure:
SELECT nome, cognome
FROM autori,(SELECT id_autore ,COUNT(*) as num_libri FROM autori_libri GROUP BY id_autore HAVING num_libri>2) AS libriscritti
WHERE (autori.id = libriscritti.id_autore)
oppure:
SELECT nome, cognome FROM autori_libri, autori
WHERE (autori.id = autori_libri.id_autore )
GROUP BY id_autore,nome,cognome HAVING COUNT(*)>2
SELECT * FROM utenti,prestiti WHERE (utenti.id=prestiti.id_utente)
A questo punto però devo ulteriormente “filtrare” (RESTRIZIONE) i risultati ottenuti aggiungendo la condizione su data e ora del prestito ovvero aggiungendo un'altra condizione nella clausola WHERE.
NOW() mi ritorna data e ora corrente, se sottraggo 1 MESE( - INTERVAL 1 MONTH.) dal valore restituito da NOW() avrò la data (e l'ora) di un mese fa: data_ora_prestito deve essere successiva( > ) alla data così ricavata.
Rimane solo da effettuare la PROIEZIONE sulle colonne nome e cognome(ovvero visualizzare solo questi attributi). Non serve specificare il nome della tabella utenti perchè in prestiti non ci sono attributi omonimi per cui non si hanno problemi di ambiguità.
In finale avremo:
ESERCIZIO 5)
Quello che mi serve è associare libri, prestiti, utenti per scoprire chi ha preso in prestito cosa.
La GIUNZIONE tra le tabelle viene effettuata, come al solito, uguagliando le chiavi esterne alle chiavi primarie associate( effettuo una RESTRIZIONE(WHERE) del PRODOTTO CARTESIANO Libri x Utenti x Prestiti ).
Alla luce di ciò, posso iniziare con lo scrivere qualcosa del genere:
SELECT *
FROM libri,utenti,prestiti
WHERE (libri.id=prestiti.id_libro) AND (utenti.id=prestiti.id_utente)
Una volta effettuata la GIUNZIONE tra le tabelle è necessario effettuare un'ulteriore RESTRIZIONE dell'insieme dei risultati per ottenere solo le righe che mi interessano: quelle relative a Mario Rossi (aggiungo delle condizioni nella clausola WHERE con AND).
Inoltre si devono visualizzare solo gli attributi (ovvero le colonne) di libri(PROIEZIONE) dunque nella SELECT nome,cognome
FROM utenti,prestiti
WHERE (utenti.id=prestiti.id_utente) AND (data_ora_prestito > NOW() - INTERVAL 1 MONTH)
SELECT drovremo specificare libri.* al posto di * , in finale otterremo:
ESERCIZIO 6)
Per prima cosa mi devo ricavare CHI HA SCRITTO COSA , mi servono pertanto i dati contenuti nella tabella autori, libri e nella tabella di associazione autori_libri, quindi avrò l'esigenza di effettuare la solita GIUNZIONE tra libri e autori_libri da un lato e autori e autori_libri dall'altro, ottenendo come risultato:
SELECT *
FROM autori,libri,autori_libri
WHERE (libri.id=autori_libri.id_libro) AND (autori.id=autori_libri.id_autore)
Questa query mi fornisce come risultato una tabella in cui per ogni riga ho gli attributi delle 3 tabelle, tra gli altri: nome e cognome dell'autore, data di pubblicazione,titolo del libro (scritto dal medesimo autore: ho infatti associato i libri con i loro effettivi autori), se uno stesso libro ha più autori vi saranno più righe per tale libro una per ogni co-autore.
A questo punto devo filtrare i risultati in base al nome e cognome dell'autore che sto cercando: Stephen Hawking e ordinare i risultati in base alla data di pubblicazione (dalla data maggiore ovvero più recente a quella minore ).
SELECT *
FROM autori,libri,autori_libri
WHERE (libri.id=autori_libri.id_libro) AND (autori.id=autori_libri.id_autore) AND (nome='Stephen') AND (cognome = 'Hawking')
ORDER BY data_pubblicazione DESC
Poichè non mi servono infatti tutti gli attributi ma solamente il titolo del libro, effettuo una PROIEZIONE (cioè visualizzo solo alcune colonne) sul solo attributo titolo e visto che sono richiesti solamenti i 3 libri più recenti limito i risultati ai primi 3 attraverso la clausola LIMIT come mostrato di seguito:
SELECT libri.*
FROM libri,utenti,prestiti
WHERE (libri.id=prestiti.id_libro) AND (utenti.id=prestiti.id_utente) AND (utenti.nome='Mario') AND (utenti.cognome='Rossi')
ESERCIZIO 7)
Devo innanzitutto determinare la durata di ogni prestito: questo significa effettuare la differenza tra data_ora_restituzione e data_ora_prestito per ogni prestito.
A questo scopo utilizzo la funzione TIMESTAMPDIFF (vedi esempi in soluzioni di Esercizi_SQL2) e specifico che intendo calcolare tale differenza in giorni specificando DAY come primo argomento della funzione(anche se non è specificato dal problema è abbastanza plausibile che l'unità temporale richiesta sia questa) . Ottengo tale risultato attraverso la seguente query:
SELECT TIMESTAMPDIFF(DAY,data_ora_prestito,data_ora_restituzione) AS durata_in_giorni FROM prestiti
Quello che voglio però è la media e a questo punto è immediato calcolarla attraverso la funzione AVG, in finale avrò
ESERCIZIO 8)
Questa volta mi servono i dati che si trovano sia in prestiti (data e ora prestito, data e ora restituzione) che in utenti(nome, cognome), pertanto dovrò effettuare una GIUNZIONE tra le 2 tabelle prestiti e utenti. Come al solito avremo
SELECT *
FROM utenti,prestiti
WHERE (utenti.id=prestiti.id_utente)
Nella tabella risultante ho sia gli attributi di prestiti che di utenti, quindi ho data_ora_prestito e data_ora_restituzione, ma anche nome e cognome utente, insomma tutto quello che mi serve per effettuare
SELECT AVG(TIMESTAMPDIFF(DAY,data_ora_prestito,data_ora_restituzione)) AS durata_media_in_giorni FROM prestiti
SELECT titolo
FROM autori,libri,autori_libri
WHERE (libri.id=autori_libri.id_libro) AND (autori.id=autori_libri.id_autore) AND (nome='Stephen') AND (cognome = 'Hawking')
ORDER BY data_pubblicazione DESC LIMIT 3;
la query desiderata.
Effettuo come prima il calcolo della durata del prestito con TIMESTAMPDIFF e filtro in base a nome e cognome, in finale avremo:
Qui dovendo calcolare la media per un unico utente è superfluo usare GROUP BY.
ESERCIZIO 9)
I dati che mi servono si trovano in utenti e prestiti (se volessi visualizzare anche i libri non restituiti dovrei aggiungere anche libri ma per il momento non ci complichiamo la vita!).
Come al solito si parte dal trovare CHI HA PRESO IN PRESTITO COSA effettuando la solita GIUNZIONE tra utenti e prestiti nel modo mostrato di seguito:
SELECT *
FROM prestiti,utenti
WHERE (prestiti.id_utente = utenti.id);
Questa tabella contiene tutti gli attributi delle due tabelle e in ogni riga ci sono sia la data e l'ora di restituzione sia il nome e cognome dell'utente che ha effettuato quel prestito insieme a tutti gli altri attributi.
Quello che devo fare è effettuare un' ulteriore RESTRIZIONE( aggiungere un'altra condizione nella clausola WHERE ) restringendo l'insieme dei risultati alle righe per cui data_ora_restituzione non è specificata. A questo scopo utilizzo la funzione IS NULL.
Inoltre devo imporre che il prestito sia stato effettuato ALMENO due mesi fa, per cui data_ora_prestito deve essere precedente a NOW() - INTERVAL 2 MONTH
E' richiesto di visualizzare solamente nome e cognome dell'utente quindi devo effettuare una PROIEZIONE sulle colonne nome e cognome ottenendo:
SELECT nome,cognome FROM prestiti,utenti
WHERE (prestiti.id_utente = utenti.id) AND (data_ora_restituzione IS NULL) AND (data_ora_prestito < NOW() - INTERVAL 2 MONTH)
Se un utente ha preso in prestito più libri però, il suo nome figurerà più volte nella tabella dei risultati(una per ogni libro preso in prestito).
SELECT AVG(TIMESTAMPDIFF(DAY,data_ora_prestito,data_ora_restituzione) ) AS durata_media_in_giorni
FROM utenti,prestiti
WHERE (utenti.id=prestiti.id_utente) AND (nome='Mario') AND (cognome='Rossi')
Ho bisogno di eliminare i duplicati attraverso la clausola DISTINCT ottenendo:
ESERCIZIO 10)
Prima Soluzione)
Per determinare quali sono i libri più richiesti posso iniziare raggruppando i prestiti effettuati per ogni libro(GROUP BY id_libro) e applicando la funzione COUNT(ovvero contandoli)... utilizzo un ALIAS per rendere tutto più chiaro. Otterrò una tabella con due sole colonne id_libro e num_prestiti.
SELECT id_libro,COUNT(*) as num_prestiti FROM prestiti
GROUP BY id_libro
Ordinando le righe per valori di num_prestiti decrescenti (la prima riga è quella per cui ho il valore di num_prestiti più grande e poi via via decrescendo man mano che scendo verso il basso) e limitando i risultati visualizzati ai primi 10 (LIMIT 10) otterrei l'elenco, ordinato in base al numero di prestiti effettuati, dei 10 libri più letti:
SELECT id_libro,COUNT(*) as num_prestiti FROM prestiti
GROUP BY id_libro
ORDER BY num_prestiti DESC LIMIT 10
C'è solo un problema... ciò che è richiesto è il titolo dei libri non l'id!. Procedo pertanto come segue:
A questa tabella associo l'ALIAS TOP10 ed effettuo una GIUNZIONE tra questa tabella e la tabella libri, visualizzando solo la colonna titolo che è quella che mi interessa:
SELECT titolo FROM libri,
(SELECT id_libro,COUNT(*) as num_prestiti FROM prestiti
GROUP BY id_libro
ORDER BY num_prestiti DESC LIMIT 10) AS TOP10 WHERE (libri.id = TOP10.id_libro);
SELECT DISTINCT nome,cognome FROM prestiti, utenti
WHERE (prestiti.id_utente=utenti.id)
AND (data_ora_restituzione IS NULL)
AND (data_ora_prestito < NOW() - INTERVAL 2 MONTH)
Seconda Soluzione)
Oppure posso effettuare subito la giunzione con libri e raggruppare in base a: id_libro e titolo ricordando che l'aggiunta dell'attributo titolo non modifica la composizione dei gruppi poiché tutti i libri che hanno lo STESSO ID_LIBRO avranno necessariamente lo STESSO TITOLO.
La query diventa:
SELECT titolo FROM prestiti, libri
WHERE (libri.id = prestiti.id_libro) GROUP BY id_libro,titolo
ORDER BY COUNT(*) DESC LIMIT 10
Notare che COUNT(*) viene messo nella clausola ORDER BY poiché devo visualizzare solamente il titolo dei libri (e non il numero di prestiti effettuati per ogni libro).
MySQL 5.0 interpreta correttamente questa istruzione mentre versioni precedenti NO
Riassumendo:
SELECT titolo FROM libri,
(SELECT id_libro,COUNT(*) as num_prestiti FROM prestiti
GROUP BY id_libro
ORDER BY num_prestiti DESC LIMIT 10) AS TOP10 WHERE (libri.id = TOP10.id_libro);
Oppure:
SELECT titolo FROM prestiti, libri
WHERE (libri.id = prestiti.id_libro) GROUP BY id_libro,titolo
ORDER BY COUNT(*) DESC LIMIT 10
