ESERCIZI NORMALIZZAZIONE Es 1 Si consideri lo schema di relazione R = (A,B,C,D) e il seguente insieme di dipendenze funzionali:

1. A -> B 2. BC -> D 3. A -> C

Rispondere ai seguenti quesiti motivando le risposte date: a) Determinare la chiave o le chiavi dello schema di relazione R. b) Determinare se R e’ in 3NF o BCNF. c) Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze

funzionali Es 2 Si consideri R(A,B,C,D); per ciascuno dei seguenti gruppi di dipendenze funzionali

1. ABC→D, D→A; 2. AB→C, AB→D, C→A, D→B;

determinare: a) Tutte le chiavi di R. b) Se R è in 3NF. c) Se R è in BCNF; nel caso in cui non lo fosse, discutere l’esistenza (ed eventualmente proporre)

di una decomposizione che preservi le dipendenze funzionali. Es 3 Si consideri lo schema di relazione R(A,B,C,D,E,F) e il seguente insieme di dipendenze funzionali F su tale schema:

1. A → B 2. ABCD → EF 3. EF → C

a) Mostrare una relazione su tale schema che contenga almeno due tuple e verifichi le dipendenze, ed una

relazione che contenga almeno due tuple e non le verifichi. b) Determinare se l’insieme delle dipendenze è minimale. Se non lo è, generare l’insieme di dipendenze

minimale equivalente a quello dato. c) Determinare le chiavi della relazione. d) Lo schema è in BCNF? È il 3NF? Giustificare le risposte. e) Mostrare una decomposizione lossless join dello schema in 3NF che preservi le dipendenze. f) Mostrare una decomposizione lossless join dello schema in BCNF che preservi le dipendenze. g) Mostrare una decomposizione lossless join dello schema in BCNF che non preservi le dipendenze.

SOLUZIONE ESERCIZI NORMALIZZAZIONE Sol1 Si consideri lo schema di relazione R = (A,B,C,D) e il seguente insieme di dipendenze funzionali:

1. A -> B 2. BC -> D 3. A -> C

Rispondere ai seguenti quesiti motivando le risposte date: a) Determinare la chiave o le chiavi dello schema di relazione R.

Lo schema ha come chiave l’attributo A (A+=ABCD) b) Determinare se R e’ in 3NF o BCNF.

La relazione non è in 3NF (dunque neanche in BCNF) a causa di BC -> D c) Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze

funzionali Una possibile decomposizione che preserva i dati e conserva le dipendenze funzionali è R1(A,B,C) e R2(B,C,D) (applicare algoritmo scomposizione)

Sol2 Si consideri R(A,B,C,D); per ciascuno dei seguenti gruppi di dipendenze funzionali

1. ABC→D, D→A; 2. AB→C, AB→D, C→A, D→B;

determinare: a) Tutte le chiavi di R.

1:abc, bcd 2:ab, bc, cd, ad

b) Se R è in 3NF. 1:Sì (verificare la definizione) 2:Sì

c) Se R è in BCNF; nel caso in cui non lo fosse, discutere l’esistenza (ed eventualmente proporre) di una decomposizione che preservi le dipendenze funzionali. 1:No per D→A; spezzando R in AD e BCD non si preserva ABC→D quindi non esiste una decomposizione BCNF 2:No per C→A e D→B; spezzando R in AC e BCD non si preservano AB→C e AB→D, inoltre BCD non e’ in BCNF per D→B; spezzando R in AC, BD e CD dovremmo considerare anche ABC e ABD per preservare le dipendenze ma sia ABC che ABD non sono in BCNF. Quindi non esiste una scomposizione BCNF

Sol3 Si consideri lo schema di relazione R(A,B,C,D,E,F) e il seguente insieme di dipendenze funzionali F su tale schema:

1. A → B 2. ABCD → EF 3. EF → C

a) Mostrare una relazione su tale schema che contenga almeno due tuple e verifichi le dipendenze, ed una relazione che contenga almeno due tuple e non le verifichi.

A B C D E F a1 b1 c1 d1 e1 f1 a2 b2 c2 d2 e2 f2

verifica

A B C D E F a1 b1 ... ... ... … a1 b2 ... ... ... ...

viola A B b) Determinare se l’insieme delle dipendenze è minimale. Se non lo è, generare l’insieme di dipendenze minimale equivalente a quello dato. Non ci sono dipendenze ridondanti B è ridondante in ABCD EF (B ∈ACD+), quindi la sostituisco con ACD EF L’insieme minimale è {A B, ACD E, ACD F, EF C}

c) Determinare le chiavi della relazione. AD non compare mai a dx, quindi ogni chiave contiene AD AD+= ABD (AD da solo non è chiave) ACD+= ABCDEF (ACD è chiave) ADEF+= ABCDEF (ADEF è chiave) Le uniche due chiavi sono ACD e ADEF d) Lo schema è in BCNF? È il 3NF? Giustificare le risposte. A B viola BCNF perché A non è (super) chiave

A B viola anche la 3NF perché B non è un attributo primo Quindi lo schema non e’ 3NF né BCNF

e) Mostrare una decomposizione lossless join dello schema in 3NF che preservi le dipendenze.

Applico algoritmo per 3NF (la scomposizione risultate preserverà sempre le dipendenze – proprietà dell’algoritmo) Lo schema risultate è AB A B chiave A ACDE ACD E chiave ACD ACDF ACD F chiave ACD EFC EF C chiave EF Questo schema è BCNF

f) Mostrare una decomposizione lossless join dello schema in BCNF che preservi le dipendenze. Lo schema proposto al punto e) g) Mostrare una decomposizione lossless join dello schema in BCNF che non preservi le dipendenze. Applico algoritmo per BCNF

ABCDEF

ABA-->B

K: A

ACDEFACD --> EF

EF --> C

K:ACD

CEFEF --> C

K: EF

ADEF0

K: ADEF

EF --> C viola

La decomposizione non preserva le dipendenze. Perdo la dipendenza ACD EF. Infatti, se F e’ l’insieme delle dipendenze associate alle foglie dell’albero precedente, in F, ACD+ non contiene EF

ESERCIZI SQL + RELAZIONALE Es 1 Si consideri il seguente schema relazionale, relativo agli spot pubblicitari proposti da varie emittenti televisive (pk sottolineate, Attru: attributo soggetto al vincolo unique, Attrnn: attributo soggetto al vincolo not null): EMITTENTE(CodE, NomeEu,Tipo) SPOT(CodS,NomeSnn,Prodottonn,TipoProdottonn) TRASMETTE(CodEfk->Emittente.CodE,CodSfk->Spot.CodS,duratann) Nello schema precedente, l’unità di misura del campo durata è in secondi. Rispondere ai seguenti quesiti: • Posso avere due emittenti con lo stesso nome ? • Posso avere due spot relativi allo stesso prodotto ? • Posso avere due spot relativi allo stesso tipo di prodotto ? • Posso avere due spot della stessa durata ? • Può uno stesso spot essere trasmesso da emittenti diverse ? • Può uno stesso spot essere trasmesso dalla stessa emittente ? Formulare le seguenti operazioni in SQL: a) Determinare le emittenti regionali che trasmettono almeno uno spot sulla pasta Barilla, di durata

superiore a 30 secondi. b) Determinare per ogni spot il numero totale di emittenti che lo trasmettono e la durata media. c) Determinare per ogni emittente il nome dello spot trasmesso più lungo, insieme al prodotto a cui si

riferisce. d) Determinare per ogni emittente i nomi degli spot relativi ad autovetture, di durata superiore alla durata

media degli spot trasmessi dall’emittente. e) Si supponga che venga vietata la trasmissione di spot di telefonia cellulare di durata superiore a 40

secondi. Aggiornare la base di dati per tenere conto di questa nuova normativa. Es 2 Si consideri il seguente schema relazionale: Aereo(ACodice, Modello, Autonomia) Impiegato(ICodice, Nome, Mansione, Stipendio) Abilitazione(ICodice,ACodice) Voli(VCodice, Da, A, Distanza, OraPartenza, OraArrivo) Si formulino le seguenti interrogazioni sia in algebra relazionale che in SQL:

a) trovare i nomi dei piloti abilitati a pilotare aerei il cui modello è “Boeing” b) trovare i codici degli impiegati con il salario più alto c) trovare i codici degli impiegati con il secondo salario più alto d) trovare il nome dei piloti abilitati a pilotare aeroplani con autonomia maggiore di 2000 miglia

che non siano abilitati a pilotare alcun aereo il cui modello è “Boeing” Es 3 Si consideri il seguente schema relazionale: MUSEI (Nome, Cittànn, Nazionenn, GiornoChiusurann) ARTISTI (Nome, Nazionalitànn, DataNascitann, DataMorte)

OPERE (Codice, Titolonn, Statonn, Artistann, Museonnfk->Musei.Nome)

Rispondere ai seguenti quesiti utilizzando il linguaggio SQL: a) trovare il codice, il titolo e lo stato delle opere di Leonardo conservate alla “National Gallery” b) trovare il nome dell’artista ed il titolo delle opere conservate alla “Galleria degli Uffizi” o alla

“National Gallery” c) trovare le città in cui son conservate opere di Leonardo d) trovare il nome dell’artista ed il titolo delle opere di artisti italiani contemporanei conservate nei

musei di Londra e) trovare il numero di opere di artisti italiani conservate in ciascun museo di Londra f) trovare il nome dei musei di Londra che non conservano opere di Leonardo g) trovare il nome dei musei di Londra che conservano solo opere di Leonardo h) trovare i musei che conservano almeno 10 opere di artisti spagnoli del ventesimo secolo i) trovare per ogni museo il numero di opere divise per la nazionalità degli artisti

SOLUZIONE ESERCIZI SQL + RELAZIONALE Sol 1 SOLUZIONE QUESITI • Posso avere due emittenti con lo stesso nome ? No a causa del vincolo unique su NomeE • Posso avere due spot relativi allo stesso prodotto ? Sì • Posso avere due spot relativi allo stesso tipo di prodotto ? Sì • Posso avere due spot della stessa durata ? Sì • Può uno stesso spot essere trasmesso da emittenti diverse ? Sì • Può uno stesso spot essere trasmesso dalla stessa emittente ? No in quanto dovrei inserire una seconda tupla con lo stesso valore di chiave primaria CodE,CodS nella relazione Trasmette SOLUZIONE SQL a) Determinare le emittenti regionali che trasmettono almeno uno spot sulla pasta Barilla, di durata superiore a 30 secondi. SELECT DISTINCT NomeE FROM Emittente NATURAL JOIN Spot NATURAL JOIN Trasmette WHERE Prodotto=’Pasta Barilla’ AND Durata > 30 AND Tipo = ‘Regionale’; b) Determinare per ogni spot il numero totale di emittenti che lo trasmettono e la durata media. SELECT CodS, COUNT(*), AVG(Durata) FROM Trasmette GROUP BY CodS; c) Determinare per ogni emittente il nome dello spot trasmesso più lungo, insieme al prodotto a cui si riferisce. SELECT NomeE, NomeS, Prodotto FROM Trasmette NATURAL JOIN Spot NATURAL JOIN Emittente E WHERE Durata > = (SELECT MAX(Durata) FROM Trasmette WHERE CodE=E.CodE);

d) Determinare per ogni emittente i nomi degli spot relativi ad autovetture, di durata superiore alla durata media degli spot trasmessi dall’emittente. SELECT NomeE, NomeS FROM Trasmette NATURAL JOIN Spot NATURAL JOIN Emittente E WHERE TipoProdotto = ‘auto’ AND Durata > = (SELECT AVG(Durata) FROM Trasmette WHERE CodE=E.CodE); e) Si supponga che venga vietata la trasmissione di spot di telefonia cellulare di durata superiore a 40 secondi. Aggiornare la base di dati per tenere conto di questa nuova normativa. DELETE TRASMETTE WHERE durata >= 40 AND CodS IN (SELECT CodS FROM Spot WHERE TipoProdotto=’Telefonia Cellulare’); Sol 2 Si consideri il seguente schema relazionale: Aereo(ACodice, Modello, Autonomia) Impiegato(ICodice, Nome, Mansione, Stipendio) Abilitazione(ICodice,ACodice) Voli(VCodice, Da, A, Distanza, OraPartenza, OraArrivo) Si formulino le seguenti interrogazioni sia in algebra relazionale (P=proiezione, S=selezione) che in SQL: a) trovare i nomi dei piloti abilitati a pilotare aerei il cui modello è “Boeing”

R1:Picodice(Smodello=’boeing’(Aereo|><|Abilitazione)) R2:Pnome(R1|><|Impiegato) select i.nome from impiegato i, aereo a, abilitazione b where a.acodice=b.acodice and i.icodice=b.icodice and a.modello=’boeing’

b) trovare i codici degli impiegati con il salario più alto

I1:Impiegato I2:Impiegato R1:PI2.icodice(I1|><|I1.stipendio>I2.stipendioI2) Picodice(Impiegato)-R1 select i.icodice from impiegato i where i.stipendio=(select max(j.stipendio) from impiegato j)

c) trovare i codici degli impiegati con il secondo salario più alto

I1:Impiegato I2:Impiegato R1:PI2.icodice(I1|><|I1.stipendio>I2.stipendioI2) I3:Impiegato|><|R1

I4:Impiegato|><|R1 R2:PI4.icodice(I3|><|I3.stipendio>I4.stipendioI4) R1-R2 select i.icodice from impiegato i where i.stipendio=(select max(j.stipendio)

from impiegato j where j.stipendio<>(select max(k.stipendio)

from impiegato k))

d) trovare il nome dei piloti abilitati a pilotare aeroplani con autonomia maggiore di 2000 miglia che non siano abilitati a pilotare alcun aereo il cui modello è “Boeing”

R1:Picodice(Sautonomia>2000(Aereo|><|Abilitazione)) Pnome(Impiegato|><|(R1-(Picodice(Smodello=’boeing’(Aereo|><|Abilitazione))))) select i.icodice, i.nome from impiegato i, aereo a, abilitazione b where a.acodice=b.acodice and i.icodice=b.icodice and a.autonomia>2000 and i.icodice not in (select k.icodice

from aereo j, abilitazione k where j.acodice=k.acodice and j.modello=’boeing’)

Sol3 Si consideri il seguente schema relazionale: MUSEI (Nome, Cittànn, Nazionenn, GiornoChiusurann) ARTISTI (Nome, Nazionalitànn, DataNascitann, DataMorte) OPERE (Codice, Titolonn, Statonn, Artistannfk->Artisti.Nome, Museonnfk->Musei.Nome)

Rispondere ai seguenti quesiti utilizzando il linguaggio SQL:

a) trovare il codice, il titolo e lo stato delle opere di Leonardo conservate alla “National Gallery” select o.codice, o.titolo, o.stato from opere o where o.artista=’leonardo’ and o.museo=’national gallery’;

b) trovare il nome dell’artista ed il titolo delle opere conservate alla “Galleria degli Uffizi” o alla “National Gallery”

select o.artista, o.titolo from opere o where o.museo=’galleria degli uffizi’ or o.museo=’national gallery’;

c) trovare le città in cui son conservate opere di Leonardo

select distinct m.città from opere o, musei m where o.artista=’leonardo’ and o.museo=m.nome

d) trovare il nome dell’artista ed il titolo delle opere di artisti italiani contemporanei conservate nei musei di Londra

select a.nome, o.titolo from artisti a, opere o, musei m where a.nome=o.artista and o.museo=m.nome and a.nazionalità=’italia’ and m.città=’londra’ and a.datamorte is null;

e) trovare il numero di opere di artisti italiani conservate in ciascun museo di Londra

select m.nome, count(*) as numopere from artisti a, opere o, musei m where a.nome=o.artista and o.museo=m.nome and a.nazionalità=’italia’ and m.città=’londra’ group by m.nome;

f) trovare il nome dei musei di Londra che non conservano opere di Leonardo

select m.nome from musei m where m.città=’londra’ and not exist (select * from opere o where o.nome=’leonardo’ and m.nome=o.museo);

g) trovare il nome dei musei di Londra che conservano solo opere di Leonardo

select m.nome from musei m where m.città=’londra’ and not exist (select * from opere o where o.nome<>’leonardo’ and m.nome=o.museo);

h) trovare i musei che conservano almeno 10 opere di artisti spagnoli del ventesimo secolo

select o.museo from artisti a, opere o where a.nome=o.artista and a.nazionalità=’spagna’ and a.datanascita>=’1/1/1900’ group by o.museo having count(*)>9;

i) trovare per ogni museo il numero di opere divise per la nazionalità degli artisti

select o.museo, a.nazionalità, count(*) as numopere from artisti a, opere o where a.nome=o.artista group by o.museo, a.nazionalità;

ESERCIZI PROGETTAZIONE Si vuole sviluppare una base di dati per gestire la vendita di biglietti per concerti e spettacoli teatrali.

Ogni concerto è caratterizzato da un identificatore, il nome del cantante e alcune informazioni generali. Ogni spettacolo teatrale è caratterizzato da un identificatore, il titolo dello spettacolo, la durata, il nome del regista e il nome del protagonista.

Concerti e spettacoli teatrali sono tenuti in luoghi che possono essere teatri o stadio. Ogni teatro o stadio è caratterizzato da un identificatore, un nome, un indirizzo, un numero di telefono e il numero complessivo di posti disponibili. Per i teatri, si vuole inoltre mantenere informazioni circa l’orario di apertura del botteghino. Mentre i concerti possono essere tenuti in stadi e teatri, gli spettacoli teatrali si svolgono esclusivamente nei teatri.

Le performance (concerto o spettacolo teatrale) si svolgono in un certo luogo (teatro o stadio) in una certa data, con una certa ora di inizio. La stessa performance può essere svolta nello stesso luogo o luoghi diversi, in date e/o orari diversi.

Per ogni performance tenuta in un certo luogo in una certa data, ad una certa ora, si mantengono informazioni circa i posti disponibili. In particolare, per ogni luogo (teatro o stadio), un posto è caratterizzato da un numero di fila e un numero di posto. Per ogni posto si vuole inoltre sapere se il posto è libero o occupato.

Motivando adeguatamente le scelte: a) Effettuare la progettazione concettuale dello scenario descritto producendo il relativo schema ER. b) Precisare eventuali vincoli di integrità non rappresentabili nello schema ER proposto e la tipologia delle eventuali generalizzazioni. c) Generare lo schema ristrutturato corrispondente allo schema ER definito al punto a). d) Tradurre lo schema concettuale in uno schema logico del modello relazionale evidenziando esplicitamente ogni tipo di vincolo utile a rappresentare lo scenario in esame.

SOLUZIONE ESERCIZI PROGETTAZIONE

(t,e)

(0,N)

(1,N)

(1,1)(1,1)

(0,N)

(1,1)

Titolo Durata Regista

TEATRO STADIO

Indirizzo

TotPosti

NomeLUOGHI

ID

Telefono

OraABott

PERFORMANCE

SPETTACOLO

CONCERTO

S. TEATRALE

INPOSTI

Info (1,n)

ProtagonistaID

SI FA

PER

(t,e)

OraIDataLibero/occupatoNumFila NumPosto

Vincoli:

• se uno spettacolo è teatrale allora il luogo in cui si svolge è un teatro • i posti assegnati per una performance devono essere minore o uguale del totale dei posti del luogo

in cui la performance si deve tenere La ristrutturazione considerata consiste nell’eliminazione delle entità figlie delle gerarchie di generalizzazione totale ed esclusive presenti nello schema e nell’eliminazione dell’attributo multivalore Info dell’entità concerto.

Titolo (0,1)Durata (0,1)

Regista (0,1)

IndirizzoTotPosti Nome

LUOGHI

ID

TelefonoOraABott (0,1)

SPETTACOLO

ProtagonistaID

Tipo

INFOPER(0,N) (1,N)

Nota

Tipo

(0,N)

(1,N)

(1,1)(1,1)

(0,N)

(1,1)PERFORMANCE INPOSTI

SI FA

PER

OraIDataLibero/occupatoNumFila NumPosto

Vincoli aggiuntivi su schema ristrutturato

• Spettacolo.Tipo ∈ {‘concerto’,’teatrale’} • Spettacolo.Titolo ≠ NULL sse Spettacolo.Tipo = ‘teatrale’. Stessa cosa per Durata e Regista • Luoghi.Tipo Tipo ∈ {‘teatro,stadio} • Luoghi.OraABott ≠ NULL sse Luoghi.Tipo = ‘teatro’

Luoghi(ID,Tiponn,TotPostinn,Nomenn,Indirizzonn,Telefononn,OraABott)

Performance(Luogofk->Luoghi.ID,Data,OraI,Spettacolonnfk->Spettacolo.ID)

Posti(Datafk->Performance.Data,Luogofk->Performance.Luogo,OraIfk->Performance.OraI,NumPosto,NumFila,Libero/Occupatonn)

Spettacolo(ID,Tiponn,Titolo,Durata,Regista,Protagonistann)

InfoPer(Spettacolofk->Spettacolo.ID,Notafk->Info.Nota)

Info(Nota) Vincoli aggiuntivi su schema relazionale

• , | . .i INFO p INFOPER i nota p nota∀ ∈ ∃ ∈ =

• , | . .s SPETTACOLO p PERFORMANCE s ID p Spettacolo∀ ∈ ∃ ∈ =