ESERCIZI DI BASI DI DATI Raffaella Gentilini Indice 1

ESERCIZI DI BASI DI DATI
Raffaella Gentilini
Indice
1.
2.
Algebra e Calcolo Relazionale
Soluzioni
1
5
1. Algebra e Calcolo Relazionale
Esercizio 1. Sia dato il seguente schema relazionale:
• persona (id persona, nome, cognome)
• f ilm (id f ilm, id regista, titolo, genere, anno): dove id regista e’ una
chiave esterna che fa riferimento a persona;
• partecipazione (id attore,id f ilm, ruolo): dove id attore ed id f ilm sono
chiavi esterne che fanno riferimento rispettivamente a persona e f ilm;
• cinema (id cinema,nome,indirizzo)
• proiezione (id cinema,id f ilm,giorno): dove id cinema e id f ilm sono
chiavi esterne che fanno riferimento rispettivamente a cinema e f ilm.
Con rifermento allo stato della base di dati riportato di seguito, si illustri il risultato
delle interrogazioni in algebra relazionale 1.1–1.3.
id cinema
• relazione cinema
02
01
03
04
nome
indirizzo
S. Angelo
Via Lucida 6 Perugia
Zenith
Via Bonfigli 11 Perugia
Multisala Clarici
Corso Cavour 84 Foligno
Multiplex Giometti Strada Centova Perugia
1
2
ESERCIZI DI BASI DI DATI
• relazione persona
id persona
nome
cognome
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
Kidman
Bettany
Watson
Skarsgard
Travolta
L. Jackson
Willis
Irons
Spader
Hunter
Arquette
Wayne
von Trier
Tarantino
Cronenberg
Mazursky
Jones
Glen
Eastwood
Spacey
Mendes
Jolie
Nicole
Paul
Emily
Stellan
John
Samuel
Bruce
Jeremy
James
Holly
Rosanna
John
Lars
Quentin
David
Paul
Grace
John
Clint
Kevin
Sam
Angelina
• relazione partecipazione
id attore
id film
ruolo
01
02
03
04
05
06
07
08
09
10
11
04
16
19
20
20
05
05
04
04
03
03
03
02
01
01
01
05
07
08
09
10
Grace
Tom Edison
Bess
Jan
Vincent Vega
Jules Winnfield
Butch Coolidge
Beverly
James Ballard
Helen Remington
Gabrielle
Chuck
May Day
John Wilson
Jim Williams
Lester Burnham
ESERCIZI DI BASI DI DATI
id cinema
id film
giorno
02
02
02
02
01
02
01
04
03
02
03
04
01
02
04
03
01
02
02
02
02
01
02
02
02
05
05
05
04
01
07
04
03
06
02
03
03
06
04
06
06
02
08
01
03
06
03
09
10
11
01/05/2002
02/05/2002
03/05/2002
02/12/1996
07/05/1996
09/05/1985
02/08/1996
08/04/1994
02/12/1990
08/12/1990
06/11/1994
06/11/1994
05/07/1980
02/09/1996
01/08/2002
09/11/1960
12/03/1988
01/02/1989
11/05/1997
05/07/1994
01/08/2002
02/03/1994
02/12/2008
03/10/2000
02/03/2004
• relazione proiezione
• relazione f ilm
3
id film
id regista
01
02
03
04
05
06
07
08
10
15
15
14
13
13
12
18
19
21
titolo
genere
Crash
drammatico
Faux-Semblants
commedia
Pulp Fiction
poliziesco
Breaking the Waves
drammatico
Dogville
drammatico
Alamo
western
Pericolosamente tua
spionaggio
Chasseur blanc, coeur noir drammatico
American Beauty
drammatico
anno
1996
1988
1994
1996
2002
1960
1985
1989
1999
4
ESERCIZI DI BASI DI DATI
1.1 (Πtitolo,genere σanno≤1988 FILM) × PERSONA
1.2 Πtitolo,nome,cognome (FILM ./id regista=id persona PERSONA)
1.3 (Πnome,cognome,titolo (FILM ./ PROIEZIONE ./id attore=id persona PERSONA))÷
Πtitolo (FILM ./id regista=id persona (σcognome=Lars PERSONA))
Esercizio 2. Con riferimento allo schema relazionato dell’esercizio precedente, si
esprimano in algebra relazionale le seguenti interrogazioni:
2.1 Elencare i titoli dei film drammatici mantenuti nella base di dati.
2.2 Definire la lista dei film proiettati al cinema Zenith.
2.3 Elencare nome e cognome di ogni regista presente nella base di dati.
2.4 Quali sono i nominativi (nome e cognome) degli attori?
2.5 Quali sono i nominativi (nome e cognome) degli artisti che sono tanto attori
quanto registi?
2.6 Quali film sono stati proiettati prima del 2002?
2.7 Elencare i film diretti da Lars von Trier.
2.8 Definire la lista dei registi che hanno diretti sia film drammatici che commedie.
2.9 Elencare i film interpretati da Nicole Kidman e proiettati al cinema Zenith.
2.10 Elencare gli attori che non hanno mai interpretato ruoli in film drammatici.
2.11 Elencare gli attori che hanno interpretato qualche film proiettato al cinema
Zenith dopo l’anno 2002.
2.12 Elencare i cinema che hanno proiettato tutti i film.
2.13 Elencare gli attori apparsi in ogni cinema mantenuto nella base di dati.
2.14 Elencare i registi piu’ produttivi, ovvero i registi che hanno diretto il maggior numero di film.
Esercizio 3. Sia dato il seguente schema relazionale:
• f ornitore (id f or, F nome, reddito, citta)
• componente (id comp, Cnome, colore, peso, citta)
• progetto (id prog, P nome, citta)
• f ornisce (id f or,id comp, id prog, quantita)
Definiamo originale un prodotto fornito da un solo fornitore. Esprimere in algebra
relazionale le seguenti interrogazioni:
3.1 Determinare i nomi dei fornitori che non forniscono alcun componente
originale.
3.2 Determinare i nomi dei fornitori che forniscono solo componenti originali.
3.3 Determinare i nomi dei fornitori che forniscono al piu’ un componente non
originale.
3.4 Determinare i nomi dei fornitori che forniscono almeno un componente
originale.
3.5 Determinare le coppie di nomi di fornitori tali che l’intersezione dei componenti da loro forniti isa vuota.
3.6 Determinare le citta’ in cui risiedono almeno due fornitori con reddito
maggiore o uguale a 100, escludendo le citta cuinon e’ associato alcun
progetto.
3.7 Per ogni citta, si determini il massimo ed il minimo dei pesi dei componenti
ad esse associati.
3.8 Si determino le citta’ cui sono associati due o piu’ componenti, ma no non
piu’ di un progetto.
ESERCIZI DI BASI DI DATI
5
3.9 Si determinino i componenti di peso massimo.
3.10 Si determinino i componenti di peso minimo.
3.11 Si determini il reddito dei fornitori che gestiscono tutti i progetti cui viene
fornito (da loro a da altri fornitori) almeno un componente di colore rosso.
Esercizio 4. Si riconsideri la soluzione dell’esercizio precedente, nell’ipotesi di voler
utilizzare l’operatore di divisione e le funzioni aggregate solo se strettamente necessari (i.e di voler esprimere le interrogazioni 3:13:11 nell’agebra relazionale di base).
Esercizio 5. Si consideri lo schema di basi di dati che contiene le seguenti relazioni:
• deputati (codice, cognome, nome, commissione, provincia, collegio)
• collegi (provincia, numero, nome)
• province (sigla, nome, regione)
• regioni (codice,nome)
• commissioni (numero, nome, presidente)
Formulare in algebra relazionale e nel calcolo relazionale sulle tuple le seguenti
interrogazioni:
5.1 Trovare nome e cognome dei presidenti di commissioni cui partecipa almeno
un deputato eletto in unaprovincia della Sicilia
5.2 Trovare nome e cognome dei deputati della commissione bilancio.
5.3 Trovare nome, cognome e provincia di elezione dei deputati della commissione di bilancio
5.4 Trovare nome, cognome, provincia e regione di elezione dei deputati della
commissione di bilancio
5.5 Trovare le regioni in cui vi sia un singolo collegio, indicando il nome ed il
cognome del deputato ivi eletto.
5.6 Trovare i collegi della stessa regione in cui siano stati eletti deputati con lo
stesso nome proprio.
2. Soluzioni
Esercizio 3: Soluzione. Nelle soluzioni proposte, gli operatori di divisione e funzioni
aggregate verranno utilizzati solo se strettamente necessari.
3.1 Determinare i nomi dei fornitori che non forniscono alcun componente
originale.
for ← Πid for,id comp (fornisce)
for1(id for1, id comp1) ← for
//componenti non originali
comp non orig ← Πid comp (for ./id for6=id for1∧id comp=id comp1 for1)
T1 ← comp non orig × (Πid for (for))
fornitori che forniscono almeno un componente originale
Bad ← Πid for (for) − T1
//fornitori che non forniscono alcun componente originale
R ← ΠFnome (Fornitore ./ (Πid for (for) − Bad))
3.2 Determinare i nomi dei fornitori che forniscono solo componenti originali.
for ← Πid for,id comp (fornisce)
for1(id for1, id comp1) ← for
//fornitori di almeno un componente non originale
Bad ← Πid for (for ./id for6=id for1∧id comp=id comp1 for1)
6
ESERCIZI DI BASI DI DATI
//fornitori che forniscono solo componenti originali
Ris ← ΠFnome (Fornitore ./ (Πid for (forn) − bad))
3.3 Determinare i nomi dei fornitori che forniscono al piu’ un componente non
originale.
for ← Πid for,id comp (fornisce)
for1(id for1, id comp1) ← for
//fornitori di almeno un componente non originale
T ← Πid for,id comp (for ./id for6=id for1∧id comp=id comp1 for1)
T1(id for1, id comp1) ← T
//fornitori di almeno due componenti non originali
Bad ← Πid for (T ./id for6=id for1∧id comp=id comp1 T1 )
//nomi dei fornitori che forniscono al piu’ un componente non originale
R ← ΠFnome (Fornitore ./ (Πid for (forn) − bad))
3.4 Determinare i nomi dei fornitori che forniscono almeno un componente
originale.
for ← Πid for,id comp (fornisce)
for1(id for1, id comp1) ← for
// componenti non soggetti a copyright
T ← Πid comp (for ./id for6=id for1∧id comp=id comp1 for1)
// componenti soggetti a copyright ovvero originali
T1 ← Πid comp (componente − T)
//fornitori che forniscono almeno un componente originale
R ← ΠFnome (fornitore ./ Πid for (fornisce ./ T1))
3.5 Determinare le coppie di fornitori tali che l’intersezione dei componenti da
loro forniti sia vuota.
for ← Πid for,id comp (fornisce)
for1(id for1, id comp1) ← for
//coppie di fornitori che forniscono almeno un componente uguale
Bad ← Πid for,id for1 (for ./id for6=id for1∧id comp=id comp1 )
idfor ← Πid for (fornitore)
idfor1(id for1) ← for
coppie id for ← for × for1
R ← coppie id for − Bad
3.6 Determinare le citta’ in cui risiedono almeno due fornitori con reddito
maggiore o uguale a 100, escludendo le citta cui non e’ associato alcun
progetto.
T ← Πid for,citta (σreddito≥100 (fornitore))
T1(id for1, citta) ← T
R ← Πcitta (progetto) ./ Πcitta (T ./id for6=id for1∧citta=citta1 T1)
3.7 Per ogni citta, si determini il massimo ed il minimo dei pesi dei componenti
ad esse associati.
T(c, p) ← Πcitta,peso (componente)
T1(c1, p1) ← ttT
BadMax ← Πc,p (T ./c=c1∧p<p1 T 1)
GoodMax ← (c, pmax) ← cp − BadMax
BadMin ← Πc,p (T ./c=c1∧p>p1 T 1)
GoodMin ← (c, pmax) ← cp − BadMin
ESERCIZI DI BASI DI DATI
7
R ← GoodMin ./ GoodMax
3.8 Si determino le citta’ cui sono associati due o piu’ componenti, ma non piu’
di un progetto.
T(id comp, c) ← Πid comp,citta (componente)
T1(id comp1, c1) ← T
2comp ← Πc (T ./c=c1∧id comp6=id comp1 T 1)
P(p, c) ← Πid prog,citta (progetto)
P1(p1, c1) ← P
Bad ← Πc (P ./c=c1∧p6=p1 P 1)
R ← 2comp − Bad
3.9 Si determinino i componenti di peso massimo.
T(id, p) ← Πid componente,peso (componente)
T1(id1, p1) ← T
BadMax ← Πid,p (T ./p<p1 T 1)
GoodMax ← T − BadMax
3.10 Si determinino i componenti di peso minimo.
T(id, p) ← Πid componente,peso (componente)
T1(id1, p1) ← T
BadMin ← Πid,p (T ./p>p1 T 1)
GoodMin ← T − BadMin
3.11 Si determini il reddito dei fornitori che gestiscono tutti i progetti cui viene
fornito (da loro a da altri fornitori) almeno un componente di colore rosso.
prod rossi ← Πid comp (fornisce ./ (σcolore=0 rosso0 (componente)))
for ← Πid for,id prod (fornisce)
tutte coppie ← Πid f or (fornisce) × prod rossi
Bad ← Πid for (tutte coppie − for)
Good ← Πid for (fornisce) − Bad
R ← Πid for,reddito (Good ./ fornitore)
Esercizio 4: Soluzione. : Si consideri la soluzione dell’esercizio 3, in cui l’operatore
di divisione e le funzioni aggregate sono state usate solo se strettamente nec- essari.