CodReg

6. ESERCITAZIONI: Algebra relazionale e QUERY
E’ dato il seguente diagramma E/R di esempio:
CodReg NomeReg
Regione
CodProv
Ha
1
Ha
N
E’Avuta
NomeProv
Possiede
1
Provincia
Possiede
1
Risiede
CodComm
NomeComm
Presiede
Commissione
1
E’Presieduta
E’Posseduto
1
N
Presiede
Deputato
Elegge
N
Elegge
1
E’Eletto
N
Collegio
Cap
CodDep
Indirizzo
Cognome
Città
Nome
CodColl
NomeColl
NumColl
l
Si ricava il seguente SCHEMA RELAZIONALE (MAPPING DEL DIAGRAMMA ER)
Regione (CodReg, NomeReg)
Provincia (CodProv, NomeProv, CodReg1)
con l’attributo “CodReg1” FOREIGN KEY sull’attributo “CodReg” della relazione “Regione”
VRCodReg (Regione)  VRCodReg1 (Provincia) deriva dalla TOTALITA’ dell’associazione diretta “Ha”
VRCodReg (Regione)  VRCodReg1 (Provincia) deriva dalla TOTALITA’ dell’associazione inversa “E’Avuta”
Collegio (CodColl, NumColl, NomeColl, CodProv1)
con l’attributo “CodProv1” FOREIGN KEY sull’attributo “CodProv” della relazione “Provincia”
VRCodProv1 (Collegio)  VRCodProv (Provincia) deriva dalla TOTALITA’ dell’associazione inversa “E’Posseduto”
Deputato (CodDep, Cognome, Nome, Indirizzo, Cap, Città, CodComm1, CodProv2, CodColl1, NumColl1)
con l’attributo “CodComm1” FOREIGN KEY sull’attributo “CodComm” della relazione “Commissione”
con l’attributo “CodProv2” FOREIGN KEY sull’attributo “CodProv” della relazione “Provincia”
con gli attributi “CodColl1” e “NumColl1” FK sugli attributi “CodColl” e “NumColl” della relazione “Collegio”
VRCodColl, NumColl (Collegio)  VRCodColl1, NumColl1 (Deputato) deriva dalla TOTALITA’ dell’associazione diretta “Elegge”
VRCodColl, NumColl (Collegio)  VRCodColl1, NumColl1 (Deputato) deriva dalla TOTALITA’ dell’ass. inversa “E’Eletto”
VRCodProv2 (Deputato)  VRCodProv (Provincia) deriva dalla TOTALITA’ dell’associazione diretta “Risiede”
Commissione (CodComm, NomeComm)
VRCodComm (Commissione)  VRCodComm1 (Deputato) deriva dalla TOTALITA’ dell’ass. inversa “E’Presieduta”
Si ricava il seguente SCHEMA TABELLE IN SQL (TRADUZIONE DELLO SCHEMA RELAZIONALE IN SQL)
Lo schema di base di dati di riferimento assegnato tradotto in SQL sarà:
TABLE Regione
(
CodReg
CHAR (3) NOT NULL,
NomeReg
CHAR (50) NOT NULL,
PRIMARY KEY (CodReg)
);
TABLE Provincia
(
CodProv
CHAR (2) NOT NULL,
NomeProv
CHAR 50) NOT NULL,
CodReg1
CHAR (3) NOT NULL,
PRIMARY KEY (CodProv),
FOREIGN KEY (CodReg1) REFERENCES Regione (CodReg)
);
TABLE Collegio
(
CodColl
CHAR (2) NOT NULL,
NumColl
INT NOT NULL,
NomeColl
CHAR (50) NOT NULL,
CodProv1
CHAR (2)
PRIMARY KEY (CodColl, NumColl),
FOREIGN KEY (CodProv1) REFERENCES Provincia (CodProv)
);
TABLE Commissione
(
CodComm
CHAR (3) NOT NULL,
NomeComm
CHAR (50) NOT NULL,
PRIMARY KEY (CodComm),
);
TABLE Deputato
(
CodDep
CHAR (4) NOT NULL,
Cognome
CHAR (30) NOT NULL,
Nome
CHAR (30) NOT NULL,
Indirizzo
CHAR (50),
Cap
CHAR (5),
Città
CHAR (30),
CodComm1
CHAR (3),
CodProv2
CHAR (2) NOT NULL,
CodColl1
INT NOT NULL,
NumColl1
INT NOT NULL,
PRIMARY KEY (CodDep),
FOREIGN KEY (CodComm1) REFERENCES Commissione (CodComm),
FOREIGN KEY (CodProv2) REFERENCES Provincia (CodProv),
FOREIGN KEY (CodColl1, NumColl1) REFERENCES Collegio (CodColl, NumColl)
);
Sia data, inoltre la seguente istanza di esempio della base di dati di riferimento in un certo istante di
tempo t:
Regione CodReg
BAS
PIE
CAM
PUG
TRE
NomeReg
Basilicata
Piemonte
Campania
Puglia
Trentino
Provincia CodProv
PZ
TO
BA
BZ
NA
Collegio CodColl
PZ
PZ
TO
TO
Deputato
NomeProv
Potenza
Torino
Bari
Bolzano
Napoli
NumColl
1
2
1
2
CodReg1
BAS
PIE
PUG
TRE
CAM
NomeColl
Melfi
Lagonegro
Settimo Torinese
Ivrea
CodProv1
PZ
PZ
TO
TO
CodDep
Cognome
Nome
Indirizzo
Cap
Città
SG01
RM02
FF03
SA04
BR05
BR06
Silvestro
Rossi
Filippi
Stabile
Biase
Verdi
Giuseppe
Mario
Francesca
Giuseppe
Roberto
Andrea
Via Po, 5
Via Riace, 1
Via Busi, 2
Via Isotta, 4
Via Berg, 5
Via Piave, 7
80125
39100
10010
85100
70126
80125
Napoli
Bolzano
Torino
Potenza
Bari
Napoli
Commissione CodComm
BIL
AFF
CUL
CodComm
1
NULL
CUL
NULL
AFF
NULL
BIL
CodProv
2
NA
BZ
TO
PZ
BA
NA
CodColl
1
PZ
TO
TO
PZ
PZ
PZ
NumColl
1
1
2
1
2
1
2
NomeComm
Bilancio
Affari Costituzionali
Cultura
TESTO DELLE QUERY: Risolvere le seguenti interrogazioni utilizzando, se possibile, l’algebra relazionale e
tradurle in SQL:
Q1: Visualizzare i nomi delle commissioni (livello di difficoltà: basso).
Q2: Elencare i nomi delle commissioni in ordine alfabetico (livello di difficoltà: basso).
Q3: Visualizzare i cognomi ed i nomi dei deputati (livello di difficoltà: basso).
Q4: Visualizzare i cognomi ed i nomi dei deputati che risiedono a Napoli (livello di difficoltà:
medio).
Q5: Visualizzare il cognome dei deputati che risiedono a Napoli e che si chiamano “Giuseppe”
(livello di difficoltà: medio).
Q6: Estrarre i nomi e i cognomi dei deputati eletti in collegi della provincia di Potenza (livello
di difficoltà: basso).
Q7: Estrarre i nomi e i cognomi dei deputati eletti con l’indicazione del collegio nel quale è
avvenuta l’elezione ordinati per Cognome in senso crescente e Nome descrescente(livello di
difficoltà: medio)
Q8: Estrarre i nomi e i cognomi dei deputati eletti con l’indicazione del collegio nel quale è
avvenuta l’elezione e che sono nati a Napoli (livello di difficoltà: medio).
Q9: Estrarre una tabella contenente tutti i nomi delle province e tutti i nomi delle regioni
(livello di difficoltà: basso).
Q10: Estrarre i nomi di tutte le commissioni con i cognomi e i nomi dei relativi presidenti,
chiamando gli attributi del risultato rispettivamente NomeCommissione, CognomePresidente,
NomePresidente (livello di difficoltà: medio).
Q11: Elencare i nomi e cognomi dei deputati eletti nelle regioni del centro Italia che non siano
presidenti di commissione. (livello di difficoltà: medio)
Q12: Estrarre una tabella contenente, per ogni provincia, il numero di deputati eletti nei collegi
di quella provincia (livello di difficoltà: medio)
SOLUZIONI
Q1: Visualizzare i nomi delle commissioni (livello di difficoltà: basso).
SQL
SELECT NomeComm
FROM Commissione
Algebra relazionale

NomeComm
(Commissione)
Q2: Elencare i nomi delle commissioni in ordine alfabetico DECRESCENTE(livello di
difficoltà: basso).
SQL
SELECT NomeComm
FROM Commissione
ORDER BY NomeComm DESC
Q3: Visualizzare i cognomi ed i nomi dei deputati (livello di difficoltà: basso).
SQL
SELECT Cognome, Nome
FROM Deputato
Algebra relazionale

Cognome, Nome
(Deputato)
Q4: Visualizzare i cognomi ed i nomi dei deputati che risiedono a Napoli (livello di
difficoltà: medio)
SQL
SELECT Cognome, Nome
FROM Deputato
WHERE (CodProv2 = “NA”)
Algebra relazionale

Cognome, Nome
(CodProv2 = “NA” (Deputato))
Q5: Visualizzare il cognome dei deputati che risiedono a Napoli e che si chiamano
“Giuseppe” (livello di difficoltà: medio)
SQL
SELECT Cognome, Nome
FROM Deputato
WHERE (CodProv2 = “NA”) AND (Nome = “Giuseppe”)
Algebra relazionale

Cognome, Nome
(CodProv2 = “NA” AND Nome = “Giuseppe” (Deputato) )
Q6: Estrarre i nomi e i cognomi dei deputati eletti con l’indicazione del collegio nel quale è
avvenuta l’elezione (livello di difficoltà: medio).
SQL
SELECT Cognome, Nome, NomeColl
FROM Deputato INNER JOIN Collegio ON (CodColl1 = CodColl) AND (NumColl1 = NumColl)
o anche
SELECT Cognome, Nome, NomeColl
FROM Deputato, Collegio
WHERE (CodColl1 = CodColl) AND (NumColl1 = NumColl)
Algebra relazionale

Cognome, Nome, NomeColl
(Deputato

Collegio)
(CodColl1=CodColl) AND (NumColl1=NumColl)
Q7: Estrarre i nomi e i cognomi dei deputati eletti con l’indicazione del collegio nel quale è
avvenuta l’elezione ordinati per Cognome in senso crescente e Nome descrescente(livello
di difficoltà: medio).
SQL
SELECT Cognome, Nome, NomeColl
FROM Deputato INNER JOIN Collegio ON (CodColl1 = CodColl) AND (NumColl1 = NumColl)
ORDER BY Cognome ASC , Nome DESC
O anche
SELECT Cognome, Nome, NomeColl
FROM Deputato, Collegio
WHERE (CodColl1 = CodColl) AND (NumColl1 = NumColl)
ORDER BY Cognome ASC , Nome DESC
Q8: Estrarre i nomi e i cognomi dei deputati eletti con l’indicazione del collegio nel quale è
avvenuta l’elezione e che sono nati a Napoli (livello di difficoltà: medio).
SQL
SELECT Cognome, Nome, NomeColl
FROM Deputato INNER JOIN Collegio ON (CodColl1 = CodColl) AND (NumColl1 = NumColl)
WHERE (Città = “Napoli”)
O anche
SELECT Cognome, Nome, NomeColl
FROM Deputato, Collegio
WHERE (CodColl1 = CodColl) AND (NumColl1 = NumColl) AND (Città = “Napoli”)
Algebra relazionale

Cognome, Nome, NomeColl
( Città = “Napoli” (Deputato

Collegio) )
(CodColl1=CodColl) AND (NumColl1=NumColl)

Cognome, Nome, NomeColl
(Città = “Napoli” (Deputato)

Collegio)
(CodColl1=CodColl) AND (NumColl1=NumColl)
Q9: Estrarre una tabella contenente tutti i nomi delle province e tutti i nomi delle regioni
(livello di difficoltà: basso).
SQL
SELECT Regione.NomeReg, Provincia.NomeProv
FROM Regione INNER JOIN Provincia ON Regione.CodReg=Provincia.CodReg1
ORDER BY Regione.NomeReg, Provincia.NomeProv;
Q10: Estrarre i nomi di tutte le commissioni con i cognomi e i nomi dei relativi presidenti,
chiamando gli attributi del risultato rispettivamente NomeCommissione,
CognomePresidente, NomePresidente (livello di difficoltà: medio).
SQL
SELECT Commissione.NomeComm AS NomeCommissione, Deputato.Cognome AS CognomePresidente,
Deputato.Nome AS NomePresidente
FROM Deputato INNER JOIN Commissione ON Deputato.CodComm1=Commissione.CodComm
ORDER BY Commissione.NomeComm;
Indirizzo
Q11: Elencare i nomi e cognomi dei deputati eletti nelle regioni del sud Italia Campania,
Basilicata e Puglia che non siano presidenti di commissione. (livello di difficoltà: medio)
SQL
SELECT Deputato.Cognome, Deputato.Nome
FROM Deputato INNER JOIN (Provincia INNER JOIN Regione ON
Provincia.CodReg1=Regione.CodReg) ON Deputato.CodProv2=Provincia.CodProv
WHERE (((Regione.NomeReg='Basilicata') Or (Regione.NomeReg='Campania') Or
(Regione.NomeReg='Puglia')) And (CodComm1 IS NULL));
Q12: Estrarre una tabella contenente, per ogni provincia, il numero di deputati eletti nei
collegi di quella provincia (livello di difficoltà: medio)
SQL
SELECT Provincia.NomeProv, COUNT(*) AS NumeroDeputatiEletti
FROM Deputato INNER JOIN Provincia ON Deputato.CodProv2=Provincia.CodProv
GROUP BY Provincia.NomeProv;