edutecnica

Esercizio no.3:soluzione

Un proprietario (P) può possedere più immobili, un immobile ha un solo proprietario.

si tratta di una relazione 1:n (uno-molti)

Nel testo si parla di annessi (A) e si intuisce che che ogni immobile (I) può essere dotato di molteplici annessi, mentre ogni annesso fa capo ad un singolo immobile.

Il diagramma concettuale che ci serve (dotato di campi) può essere il seguente.

Arriviamo dunque alle query di creazione:

CREATE TABLE P (
IdP COUNTER PRIMARY KEY,
Cognome CHAR(20),
Nome CHAR(20)); 		CREATE TABLE I (
IdI COUNTER PRIMARY KEY,
XidP INTEGER REFERENCES P(IdP),
Tipo CHAR(20),
Vani Integer,
Costo Long,
Sup Integer,
Anno integer); 		CREATE TABLE A (
XidI INTEGER
REFERENCES I(IdI),
Tipo CHAR(20),
Sup Integer);

Ecco i valori da inserire nella tabella P (dei proprietari) e I degli immobili

IdP Cognome Nome
1 Rossi               Mario              
2 Verdi               Giovanni           
3 Gialli              Andrea             
4 Neri                Marco           
5 Bianchi             Walter             
6 Magenta             Maria              

IdI XidP Tipo Vani Costo Sup Anno
1 1 Villa               8 600.000 190 1980
2 1 Appartamento        4 320.000 90 1990
3 2 Appartamento        5 400.000 120 1995
4 3 Capannone 5 800.000 500 1993
5 4 Villa               4 450.000 110 1991
6 4 Villa               5 350.000 100 1984
7 5 Capannone           6 950.000 750 1998
8 5 Appartamento        3 250.000 75 1989
9 6 Villa               7 620.000 180 1997
10 6 Appartamento        4 380.000 100 1999

ed ecco l'ultima tabella da compilare: la tabella A degli annessi.

XidI Tipo Sup
1 Garage              15
1 Giardino            200
1 Rimessa             90
2 Cantina             30
2 Box                 12
3 Posto Auto          15
3 Soffitta            30
4 Rimessa             100
7 Posto Auto          150
7 Rimessa             50
8 Box                 25
8 Cantina             40
9 Giardino            250
10 Box                 15
10 Cantina             50
10 Giardino 20

Dopo aver popolato in via preliminare le suddette tabelle eseguiamo le query

q1: vogliamo sapere quanti box sono presenti nel database
   
SELECT COUNT(*) AS TOTALE
FROM A
WHERE A.Tipo='BOX';
TOTALE
3

q2: vogliamo sapere quante ville hanno a disposizione un giardino.
   
SELECT COUNT(*) AS TOTALE
FROM I, A
WHERE A.XidI=I.IdI AND
A.Tipo='Giardino';
TOTALE
2

q3: vogliamo sapere quanti mq di superficie dispone il sig.Bianchi solo sugli immobili.
   
SELECT SUM(Sup) AS TOT
FROM I, P
WHERE P.idP=I.XidP AND
P.Cognome='Bianchi';
TOT
825

q4: elencare tutti i possessori di una villa.
   
SELECT DISTINCT P.Cognome, I.Tipo
FROM A,I,P
WHERE A.XidI=I.idI AND
P.idP=I.XidP AND
I.Tipo='Villa';
Cognome Tipo
Magenta     Villa
Rossi Villa

q5: Elencare tutti i possessori di un appartamento con Box.
   
SELECT DISTINCT P.Cognome, I.Tipo, A.Tipo
FROM A, I, P
WHERE A.XidI=I.idI AND
P.idP=I.XidP AND
A.Tipo='Box';
Cognome I.Tipo A.Tipo
Bianchi Appartamento Box
Magenta Appartamento Box
Rossi Appartamento Box

q6: Elencare tutti i possessori di immobili la cui data di costruzione successiva al 1996.
   
SELECT P.Cognome, I.Tipo, I.Anno
FROM I,P
WHERE P.idP=I.XidP AND
I.Anno>1996;
Cognome Tipo Anno
Bianchi Capannone 1998
Magenta Villa 1997
Magenta Appartamento 1999

q7: Produrre una vista dei proprietari di appartamenti con a fianco il numero di vani complessivi disponibili.
   
SELECT P.Cognome, SUM(I.Vani)
AS TOT
FROM I,P
WHERE P.idP=I.XidP AND
I.Tipo='Appartamento'
GROUP BY P.Cognome;
Cognome TOT
Bianchi 3
Magenta 4
Rossi 4
Verdi 8

q8: Produrre una vista dei proprietari la cui somma dei mq degli immobili posseduti sia superiore a 400 mq.
   
SELECT P.Cognome, SUM(I.Sup) AS TOT
FROM I,P
WHERE P.idP=I.XidP
GROUP BY P.Cognome
HAVING SUM(I.Sup)>=400;
Cognome TOT
Bianchi 825
Gialli 500