Esercizio no.6:soluzione

I Professori (P) sono classificati per nome e per numero di telefono. Gli allievi (A) sono classificati per nome e per indirizzo di studio (liceo,ragioneria,meccanica..).

E' evidente una relazione n:n fra le due entità; un professore può seguire più allievi ed ogni allievo può avere più professori.
Le regole di derivazione impongono la creazione di una terza tabella che noi chiamiamo lezione (L) che come informazione aggiuntiva potrebbe contenere la data della lezione effettuata.

Dobbiamo però considerare le specifiche del problema che prevedono dei costi differenziati a secondo del numero di ore di cui è costituita la lezione stessa.

Si può in questo caso utilizzare una struttura relazionale ternaria.
Alla relazione L viene associata una terza entità Ore (O) contenente il listino prezzi che caratterizza ogni singola lezione prenotata
Si ottiene dunque il diagramma concettuale riportato sopra e ne consegue i diagramma logico riportato a fianco.

CREATE TABLE P (
idp COUNTER PRIMARY KEY,
nome CHAR(20),
tel CHAR(12));

CREATE TABLE A (
ida COUNTER PRIMARY KEY, nome CHAR(20),
ist CHAR(12));

CREATE TABLE O (
ido INTEGER PRIMARY KEY,
costo INTEGER);

CREATE TABLE L (
idl COUNTER PRIMARY KEY,
data date,
xidp INTEGER REFERENCES P(idp), xido INTEGER REFERENCES O(ido), xida INTEGER REFERENCES A(ida));

Con queste quattro query si ottiene il diagramma logico riportato sopra; la query L va ovviamente eseguita per ultima. Possiamo testare il db coi seguenti dati

A
ida	nome	ist
1	alpha	liceo
2	beta	liceo
3	gamma	geometra

P
idp	nome	tel
1	bianchi	123
2	rossi	456
3	verdi	789

O
ido	costo
1	10
2	15
3	20

L
idl	data	xidp	xido	xida
1	01/01/2001	2	2	2
2	01/01/2001	2	1	1
3	01/01/2001	3	1	1
4	02/01/2001	1	3	3
5	02/01/2001	1	1	2
6	03/01/2001	2	1	1
7	03/01/2001	3	2	1
8	03/01/2001	1	1	3
9	03/01/2001	2	2	3

q1:Elenca gli alunni seguiti da uno specifico insegnante in uno specifico giorno. ( nel nostro caso l'insegnante 'bianchi' il giorno 2 )

SELECT A.nome
FROM P, L, A
WHERE P.idp=L.xidp AND
A.ida=L.xida AND
P.nome='bianchi' AND
L.data=#01/02/01#;

nome

gamma

beta

q2:Individua il nome e il tel. dei professori che devono seguire uno specifico alunno in un dato giorno ( nel nostro caso l'alunno Alpha il giorno 3)

SELECT P.nome, P.tel
FROM P, L, A
WHERE P.idp=L.xidp AND
A.ida=L.xida AND
A.nome='alpha' AND
L.data=#01/03/01#;

nome	tel
rossi	456
verdi	789

q3:Calcola le ore eseguite dal uno specifico insegnante in totale ( nel nostro caso da Rossi )

SELECT SUM(L.xido) AS TOT
FROM P, L
WHERE P.idp=L.xidp AND
P.nome='rossi';

TOT

q4:Calcola le ore e il compenso delle lezioni per uno specifico alunno in uno specifico giorno. ( nel nostro caso per l'alunno Gamma il giorno 3)

SELECT SUM(L.xido) AS ORE, SUM(O.costo) AS TOT
FROM A, L, O
WHERE A.idA=L.xidA AND
O.ido=L.xido AND
A.nome='gamma' AND
L.data=#1/3/2001#;

ORE	TOT
3	25