Esercitazioni 03 Algebra + S Q L

1

A.A. 2005/2006 Basi di Dati e Laboratorio 1

Corso di Basi di Dati e Laboratorio

Esercitazione di algebra relazionale e SQLAlfio Ferrara - Stefano Montanelli


Simbologia

R1 * R2JOIN NATURALE

R1 < condizione ‘=‘ >R2EQUIJOIN

R1 < condizione >R2THETA JOIN

Π< attributi >(R)PROIEZIONE

σ< condizione >(R)SELEZIONE


Simbologia

R1(Z) ÷ R2(Y)con Z = X ∪ Y

DIVISIONE

R1 × R2PRODOTTOCARTESIANO

R1 – R2DIFFERENZA

R1 ∩ R2INTERSEZIONE

R1 ∪ R2UNIONE

2


Schema relazionale

• Schema relazionale

– CENTRO(NomeC, Regione, Area(N/C/S),Abitanti)

– FIUME(NomeF, Lunghezza)– ATTRAVERSAMENTI(NomeC, Fiume)


ATTRAVERSAMENTI

55.000Arno70.000Tevere100.000Ticino

1.000.000PoLUNGHEZZANOMEF

Schema relazionaleCENTRO

FIUME

500.000CToscanaFirenze110.000CToscanaPisa

1.000.000SCampaniaNapoli90.000NLombardiaPavia

900.000NPiemonteTorino120.000NLombardiaMantova

ABITANTIAREAREGIONENOMEC

ArnoFirenzeArnoPisaTicinoPavia

PoMantovaPoTorino

FIUMENOMEC


Domanda 1

– Formulare in algebra relazionale ottimizzatala seguente interrogazione:

– Trovare nome e lunghezza dei fiumi che attraversano centri del nord con più di 100000 abitanti

– CENTRO(NomeC, Regione, Area(N/C/S), Abitanti)– FIUME(NomeF, Lunghezza)– ATTRAVERSAMENTI( NomeC, Fiume)

3


Soluzione 1

Trovare nome e lunghezza dei fiumi che attraversano centri del nord con più di 100000 abitanti


ΠNomeF,Lunghezza ((FIUME) NomeF=Fiume

(ΠFiume ((ATTRAVERSAMENTI) *

(ΠNomeC (σAbitanti>100000 (σArea=‘N’ (CENTRO)))))))




Soluzione 1






PoMantovaPoTorino

FIUMENOMEC


Domanda 2


– Trovare nome e lunghezza dei fiumi che attraversano sia centri del Piemonte che della Lombardia


4


ΠNomeF,Lunghezza ((FIUME) NomeF=Fiume

((ΠFiume ((ATTRAVERSAMENTI) *

(ΠNomeC (σRegione=‘Piemonte’ (CENTRO)))))

∩

(ΠFiume ((ATTRAVERSAMENTI) *

(ΠNomeC (σRegione=‘Lombardia’ (CENTRO)))))))

Soluzione 2

Trovare nome e lunghezza dei fiumi che attraversano sia centri del Piemonte che della Lombardia



PoMantovaPoTorino

FIUMENOMEC

Soluzione 2








Domanda 3


– Trovare il nome dei fiumi che non attraversano centri del sud


5


Soluzione 3

Trovare il nome fiumi che non attraversano centri del sud


((ΠNomeF (FIUME))

-

(ΠFiume((ΠNomeC(σArea=‘S’ (CENTRO))) *

(ATTRAVERSAMENTI))))


55.000Arno

70.000Tevere100.000Ticino



PoMantovaPoTorino

FIUMENOMEC

Soluzione 3






Domanda 4


– Trovare il nome dei fiumi che attraversano tutte le città del centro


6


Soluzione 4

Trovare il nome dei fiumi che attraversano tutte le cittàdel centro


(ATTRAVERSAMENTI) ÷

(ΠNomeC(σArea=‘C’ (CENTRO)))


Soluzione 4








PoMantovaPoTorino

FIUMENOMEC


Schema Relazionale

– RUOLO(id, nome, descrizione)– GRUPPO(id, nome, finanziamento)– PERSONA(id, nome, cognome, ruolo)– APPARTENENZA(id_persona, id_gruppo)

7


Query 1

Formulare in SQL la seguente interrogazione:

Determinare nome, cognome delle persone che appartengono solo al gruppo “laboratorio”oppure solo al gruppo “progetto H”



Soluzione query 1

SELECT persona.id, persona.nome, persona.cognome, gruppo.nome AS gruppo

FROM persona, appartenenza, gruppoWHERE ((persona.id=appartenenza.id_persona) AND

(appartenenza.id_gruppo=gruppo.id) AND(gruppo.nome='laboratorio') AND NOT EXISTS(SELECT * FROM persona alias, appartenenza, gruppoWHERE ((persona.id=appartenenza.id_persona) AND

(appartenenza.id_gruppo=gruppo.id) AND (gruppo.nome<>'laboratorio') AND (persona.id=alias.id))))

Estrazione delle persone appartenenti solo al gruppo laboratorio (preparatoria alla query):


Soluzione query 1SELECT persona.id, persona.nome, persona.cognome,

gruppo.nome AS gruppoFROM persona, appartenenza, gruppoWHERE ((persona.id=appartenenza.id_persona) AND(appartenenza.id_gruppo=gruppo.id) AND (gruppo.nome='laboratorio') AND NOT EXISTS (SELECT * FROM persona alias, appartenenza, gruppo

WHERE ((persona.id=appartenenza.id_persona) AND (appartenenza.id_gruppo=gruppo.id) AND (gruppo.nome<>'laboratorio') AND (persona.id=alias.id))))

UNIONSELECT persona.id, persona.nome, persona.cognome,

gruppo.nome AS gruppoFROM persona, appartenenza, gruppoWHERE ((persona.id=appartenenza.id_persona) AND(appartenenza.id_gruppo=gruppo.id) AND (gruppo.nome=‘progetto H') AND NOT EXISTS (SELECT * FROM persona alias, appartenenza, gruppo

WHERE ((persona.id=appartenenza.id_persona) AND (appartenenza.id_gruppo=gruppo.id) AND (gruppo.nome<>' progetto H') AND (persona.id=alias.id))))

8


Query 2


Determinare ordinandoli alfabeticamente identificatore, nome, cognome delle persone che appartengono ad almeno due gruppi



Soluzione query 2

SELECT persona.id, persona.nome, persona.cognome, COUNT(*) AS Numerogruppi

FROM persona, appartenenza WHERE persona.id = appartenenza.id_persona GROUP BY persona.id, persona.nome, persona.cognome HAVING COUNT(*) > 1 ORDER BY persona.cognome, persona.nome

Determinare, ordinandoli alfabeticamente, identificatore, nome, cognome delle persone che appartengono ad almeno due gruppi



Query 3


Determinare identificatore, nome, cognome della persona che appartiene al maggior numero di gruppi


9


Soluzione query 3

SELECT DISTINCT persona.id, persona.nome, persona.cognome, COUNT(*) AS Numerogruppi

FROM persona, appartenenza WHERE persona.id = appartenenza.id_persona GROUP BY persona.id, persona.nome, persona.cognome HAVING (COUNT(*) >= ALL (SELECT COUNT(*)

FROM persona, appartenenza WHERE persona.id = appartenenza.id_persona GROUP BY persona.id))

Determinare identificatore, nome, cognome della persona che appartiene al maggior numero di gruppi


Esercitazioni 03 Algebra + S Q L

Business

Transcript of Esercitazioni 03 Algebra + S Q L