Esercizio no.2:soluzione

Se consideriamo un'istantanea del sistema, un utente può prendere in prestito un certo numero di libri, un altro utente può prendere più di un libro fra quelli non scelti dal primo utente. In questo caso si configura una relazione uno-molti fra le due entità. Fra l'entità L (libro) e l'entità U (utente).

ma se pensiamo ad uno sviluppo nel tempo di questo servizio, il secondo utente può prendere in prestito libri riconsegnati dal primo utente. In questo caso il legame fra le due entità è di tipo molti-a-molti.

viene messa in evidenza la relazione P (prestito) che in pratica attualizza nel tempo il legame fra le due entità. Come ovvio essa diventerà una tabella a tutti gli effetti che conterrà le chiavi esterne pertinenti alle tabelle Libro e Utente, inoltre sarà dotata di campi Data per monitorare gli ingressi e le uscite dei vari libri dalla biblioteca.

Si crea un file Access di nome arbitrario. La prima tabella creata si chiama U (Utente).

idU	Contatore chiave primaria
Cognome	Testo
nome	Testo

La Seconda tabella si chiama L (Libro).

idL	Testo Chiave Primaria
Titolo	Testo
Autore	Testo

La tabella principale verrà chiamata P (Prestito).

idP	Contatore chiave primaria
xidU	Numerico
xidL	Testo
inizio	data in cifre
fine	data in cifre

In questa ultima tabella, si notano le due chiavi esterne xidU ed xidL che devono essere di un formato di dati compatibile con le chiavi primarie correlate.

Popoliamo le tabelle partendo da quelle dotate solo della chiave primaria e compilando da ultimo solo quella contenente le chiavi esterne; questo perchè non è possibile inserire dei dati in una tabella secondaria ( che contenga chiavi esterne ) se non esiste un record correlato nella tabella principale cui fanno riferimento le chiavi esterne.

L
idL	Titolo	Autore
AQ123	Algoritms	Sedgewitch
BP482	Pascal	Wirth
DW834	Transistors	Coppelli
HT937	Microprocessori	Carroll
HY237	Diodi	Stortoni
TR827	Compilatori	Ranieri

dopo la tabella L dei libri, inseriamo i record per la tabella U, degli utenti

U
idU	Cognome	Nome
1	Rossi	Mario
2	Verdi	Andrea
3	Bianchi	Massimo
4	Corrada	Fabio
5	Vallieri	Sara
6	Garavaglia	Marco
7	Esposito	Marzia

Infine la tabella P dei prestiti

P
idP	xidL	xidU	inizio	fine
1	DW834	6	12/12/2003	15/12/2003
2	AQ123	6	10/01/2004	12/01/2004
3	BP482	7	19/01/2004	21/01/2004
4	HY237	5	15/01/2004	22/01/2004
5	TR827	4	01/02/2004	03/02/2004
6	TR827	3	02/02/2004	03/02/2004
7	HY237	6	03/03/2004	18/03/2004
8	AQ123	1	01/04/2004	05/05/2004
9	BP482	2	02/04/2004	23/04/2004
10	DW834	3	10/04/2004	15/04/2004
11	HT937	4	18/04/2004	10/05/2004
12	TR827	6	05/05/2004	10/06/2004
13	AQ123	5	07/05/2004	09/06/2004
14	HY237	5	15/05/2004
15	BP482	3	16/05/2004
16	DW834	6	12/06/2004
17	TR827	5	18/06/2004

Lo schema logico è il seguente:

q1: Tutti i libri prestati a all'utente Vallieri in ordine cronologico.

SELECT L.titolo,P.inizio,P.fine
FROM L,P,U
WHERE L.idL=P.xidL AND U.
idU=P.xidU AND U.cognome='vallieri'
ORDER BY P.inizio;

titolo	inizio	fine
Diodi	15/01/2004	22/01/2004
Algoritms	07/05/2004	09/06/2004
Diodi	15/05/2004
Compilatori	18/06/2004

q2: Individua i primi tre lettori che hanno letto più libri.

SELECT TOP 3 U.cognome, COUNT(P.xidU) AS TOT
FROM L, P, U
WHERE L.idL=P.xidL AND
U.idU=P.xidU
GROUP BY U.cognome
ORDER BY COUNT(P.xidU) DESC;

cognome	TOT
Garavaglia	5
Vallieri	4
Bianchi	3

q3: Individua tutti i possessori dei libri non ancora rientrati e il titolo degli stessi.

SELECT P.idP, U.Cognome, L.Titolo
FROM L, U, P
WHERE L.idL=P.xidL AND
U.idU=P.xidU AND
P.fine Is Null;

idP	Cognome	Titolo
14	Vallieri	Diodi
15	Bianchi	Pascal
16	Garavaglia	Transistors
17	Vallieri	Compilatori

q4: Dare lo storico dei libri chiesi in prestito da un utente indicando il periodo.

SELECT P.idP, P.inizio, P.fine, L.Titolo
FROM P, L, U
WHERE L.idL=P.xidL AND
U.idU=P.xidU AND
U.Cognome=[Cognome:];

idP	inizio	fine	Titolo
5	01/02/2004	03/02/2004	Compilatori
11	18/04/2004	10/05/2004	Microprocessori

q5: Fai la classifica dei libri maggiormente prestati.

SELECT L.titolo, Count(P.xidL) AS TOT
FROM P,L
WHERE L.idL=P.xidL AND
P.xidL IS NOT NULL
GROUP BY L.titolo
HAVING Count(P.xidL)
ORDER BY Count(P.xidL) DESC;

titolo	TOT
Compilatori	4
Transistors	3
Pascal	3
Diodi	3
Algoritms	3
Microprocessori	1

q6: Individua prestiti la cui durata supera i 15gg.

SELECT P.idP, U.Cognome, L.Titolo
FROM P,L,U
WHERE L.idL=P.xidL AND
U.idU=P.xidU AND
DATEDIFF('d',P.inizio,P.fine)>15;

idP	Cognome	Titolo
8	Rossi	Algoritms
9	Verdi	Pascal
11	Corrada	Microprocessori
12	Garavaglia	Compilatori
13	Vallieri	Algoritms