PROGETTAZIONE CONCETTUALE DI BASI DI DATI: INTRODUZIONE E MODELLO ER Marco Brambilla .
Teoria della Progettazione delle Basi di Dati Relazionaliangelo.montanari/teoriaProg.pdf · Teoria...
Transcript of Teoria della Progettazione delle Basi di Dati Relazionaliangelo.montanari/teoriaProg.pdf · Teoria...
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 1
'
&
$
%
Teoria della Progettazionedelle Basi di Dati Relazionali
Angelo Montanari
Dipartimento di Matematica e InformaticaUniversita di Udine
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 2
'
&
$
%
Introduzione
Proprieta richieste (forme normali)
Algoritmi (normalizzazione)
Concetto base: dipendenza dei dati (vincolo sull’insieme dellepossibili istanze di un dato schema di relazione)
Dato uno schema di relazione R, se un attributo A (risp., uninsieme di attributi X) determina un attributo B (risp., un insiemedi attributi Y ) diremo che vi e una dipendenza funzionale di B
da A (risp., di Y da X) o che A (risp., X) determinafunzionalmente B (risp., Y )
Si noti che la proiezione di R su A,B (risp., X,Y ) definisce unafunzione f : A → B (risp., X → Y ).
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 3
'
&
$
%
Ridondanze e anomalie
Un esempio.
INFO FORN(NOME FORN, IND FORN, NOME PROD, PREZZO)
vs.
FORNITORI(NOME FORN, IND FORN)
FORNISCE(NOME FORN, NOME PROD, PREZZO)
Problemi con la prima soluzione (la relazione universale):
• RIDONDANZA
• ANOMALIE DI AGGIORNAMENTO, INSERIMENTO eCANCELLAZIONE
Osservazione: legame tra dipendenze e ridondanza
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 4
'
&
$
%
Le dipendenze funzionali - 1
Definizione. Una dipendenza funzionale tra un insieme diattributi X e un insieme di attributi Y di uno schema di relazioneR(T ), con X,Y ⊆ T , denotata X → Y , e un vincolo di integritasulle istanze della relazione
Definizione. Un’istanza r ∈ R(T ) soddisfa X → Y
(equivalentemente, X → Y vale in r) se per ogni coppia di tuplet1, t2 ∈ r, se t1[X] = t2[X], allora t1[Y ] = t2[Y ]
Osservazione: in ogni istanza valida r di R(T ), ΠXY (r) e unafunzione con dominio X e codominio Y
Definizione. Dato un insieme F di dipendenze funzionali su R(T ),si dice che r e un’istanza valida di R(T ), rispetto a F , sesoddisfa tutte le dipendenze funzionali X → Y ∈ F
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 5
'
&
$
%
Le dipendenze funzionali - 2
Significato delle dipendenze funzionali e loro legame con la nozionedi chiave
Caso 1. R rappresenti un tipo di entita
Caso 2. R rappresenti una relazione molti a uno
Validita universale delle dipendenze funzionali (per ogni istanzavalida)
Istanze che soddisfano le dipendenze funzionali (istanze valide) eistanze che violano le dipendenze funzionali
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 6
'
&
$
%
Dipendenze derivate (semantica)
Ragionamento sulle dipendenze funzionali (un esempio,transitivita)
Implicazione logica tra dipendenze funzionali (nozione semantica)
Definizione. Dati una relazione R(T ) e un insieme di dipendenzefunzionali F (abbreviato R〈T, F 〉), diciamo che F implicalogicamente X → Y (F |= X → Y ) se ogni istanza valida r delloschema R〈T, F 〉 soddisfa X → Y
Definizione. La chiusura di un insieme di dipendenzefunzionali F (denotata F+) e l’insieme delle dipendenze funzionalilogicamente implicate da F :
F+ = {X → Y | F |= X → Y }
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 7
'
&
$
%
Dipendenze funzionali e chiavi
La nozione di chiave puo essere riespressa in termini di dipendenzefunzionali
X ⊆ T e chiave di R〈T, F 〉 se e solo se
1. X → A1A2 . . . An, con T = {A1, A2, . . . , An}, e in F+ (ladipendenza di tutti gli attributi da X appartiene a F o elogicamente implicata da F )
2. per nessun sottoinsieme proprio Y ⊂ X, Y → A1A2 . . . An
appartiene a F+
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 8
'
&
$
%
Un calcolo delle dipendenze funzionali
Gli assiomi di Armstrong
• Riflessivita (dipendenze banali) Se Y ⊆ X ⊆ T , allora X → Y
(in particolare, X → X)
Osservazione: tali dipendenze valgono per ogni relazione(dipendono da T , non da F )
• Aumento Se X → Y e Z ⊆ T , allora XZ → Y Z (dove XZ
abbrevia X ∪ Z)
• Transitivita Se X → Y e Y → Z, allora X → Z
Teorema. Gli assiomi di Armstrong sono corretti (se f e derivabileda F mediante gli assiomi di Armstrong, allora f e implicatologicamente da F , abbreviato F ` f implica F |= f)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 9
'
&
$
%
Dipendenze derivate (sintassi)
Definizione. Una derivazione di una dipendenza funzionale f daF (denotato F ` f) e una sequenza finita f1, . . . , fm di dipendenzefunzionali, dove fm = f e ogni dipendenza fi appartiene a F o eottenuta da dipendenze in {f1, . . . , fi−1} usando una regola diinferenza valida (assiomi di Armstrong o regole di inferenza da essiottenute)
Si noti che ogni sottosequenza iniziale f1, . . . , fk di f1, . . . , fm, conk ≤ m, e a sua volta una derivazione, ossia F ` fk per ognik = 1, . . . ,m
Esempio. Dati R(A,B, C, D) e F = {A → C, B → D}, dimostrareche AB e una superchiave per R, ossia che F |= AB → ABCD
(sfruttiamo la correttezza degli assiomi di Armstrong)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 10
'
&
$
%
Regole derivate
Alcune regole derivate di particolare interesse:
• Unione
Se X → A1, . . . , X → An, allora X → A1 . . . An
(si consideri, ad esempio, il caso n = 2)
• Pseudo-Transitivita
Se X → Y e Y W → Z, allora XW → Z
• Decomposizione
Se X → Y e Z ⊆ Y , allora X → Z
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 11
'
&
$
%
Chiusura di un insieme di attributi
Definizione. Dato uno schema R〈T, F 〉 e un insieme di attributiX ⊆ T , la chiusura di X rispetto a F (denotata X+
F o, se non visono ambiguita, semplicemente X+) e l’insieme di attributi{A ∈ T | F ` X → A}Legame tra derivazione di dipendenze funzionali e chiusura diinsiemi di attributi
Teorema (fondamentale). F ` X → Y se e solo se Y ⊆ X+
Teorema. Gli assiomi di Armstrong sono completi (F |= f implicaF ` f)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 12
'
&
$
%
Determinazione delle chiusure - 1
Calcolare F+ e troppo costoso (gia le dipendenze banali...)
Un primo algoritmo (chiusura lenta) per il calcolo della chiusura diun insieme di attributi X rispetto ad F (X+)
algoritmo CHIUSURA LENTAinput R〈T, F 〉, X ⊆ T
output X+
beginX+ := X;while X+ e cambiato do
for each W → V ∈ F with W ⊆ X+ and V 6⊆ X+ doX+ := X+ ∪ V
end
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 13
'
&
$
%
Determinazione delle chiusure - 2
Esempio. Sia dato uno schema R〈T, F 〉, con T = {A,B,C, D,E,
G,H, I} e F = {ADG → GI,ACH → ADG,BC → AD, CE →ACH}. Inoltre, sia X+
j il valore della variabile X+ alla fine dellaj-esima iterazione del ciclo while
La chiusura di BCE viene calcolata nel seguente modo:
X+0 (= X) = BCE
X+1 = BCE ∪AD ∪ACH = ABCDEH
X+2 = ABCDEH ∪ADG = ABCDEGH
X+3 = ABCDEGH ∪GI = ABCDEGHI(= T )
L’algoritmo termina con BCE+ = T (BCE e superchiave)
Teorema. L’algoritmo chiusura lenta termina sempre ed e correttoe completo
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 14
'
&
$
%
Determinazione delle chiusure - 3
Un secondo algoritmo (chiusura veloce) per il calcolo della chiusuradi un insieme di attributi X rispetto ad F (X+)
algoritmo CHIUSURA VELOCE
input R〈T, F 〉, X ⊆ T
output X+
begin
# inizializzazione delle liste num(f) e L(A)
for each f = W → V ∈ F do
num(f) := |W |;for each A ∈ W do
aggiungi f a L(A);
# inizializzazione delle variabili
X+ := X;
NATT := X;
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 15
'
&
$
%
Determinazione delle chiusure - 4
while NATT 6= ∅ do # ciclo principale
scegli A da NATT ;
NATT := NATT \ {A};for each f = W → V ∈ L(A) do
num(f) := num(f)− 1;
if num(f) = 0 then
∆ := V \X+;
NATT := NATT ∪∆;
X+ := X+ ∪∆
end
Osservazione. Mentre ∆ in X+ := X+ ∪∆ puo essere sostituito da V , e
fondamentale non inserire in NATT attributi gia presi in esame
Teorema. L’algoritmo chiusura veloce termina sempre ed e correttoe completo
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 16
'
&
$
%
Determinazione delle chiusure - 5
Esempio. Sia dato uno schema R〈T, F 〉, con T = {A,B,C, D,E} eF = {f1 = DB → E, f2 = B → C, f3 = A → B}Inizializzazione:
num(f1) = 2, num(f2) = 1, num(f3) = 1
(nella tabella sottostante i valori correnti di num(f1), num(f2) e num(f3) sono
riportati nella quarta, quinta e sesta colonna, rispettivamente)
L(A) = {f3}, L(B) = {f1, f2}, L(C) = ∅, L(D) = {f1}, L(E) = ∅
X+ NAT T A L(A) f1 f2 f3 f ∆ X+ NAT T
AD AD A f3 2 1 0 f3 B ADB BD
ADB BD B f1, f2 1 0 0 f2 C ADBC CD
ADBC CD C ∅ 1 0 0 ∅ ∅ ADBC D
ADBC D D f1 0 0 0 f1 E ADBCE E
ADBCE E E ∅ 0 0 0 ∅ ∅ ADBCE ∅
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 17
'
&
$
%
Insiemi di dipendenze equivalenti
Definizione. Due insiemi di dipendenze F e G sugli attributi T diuna relazione R sono equivalenti (F ≡ G) se e solo se F+ = G+.Diciamo che F e una copertura di G, e viceversa G e una coperturadi F , se F ≡ G
Dalla definizione segue immediatamente che F ≡ G se e solo seF+ ⊆ G+ e G+ ⊆ F+
E possibile dimostrare che F+ ⊆ G+ se e solo se F ⊆ G+ e,analogamente, che G+ ⊆ F+ se e solo se G ⊆ F+
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 18
'
&
$
%
Coperture minimali (o canoniche) - 1
Insiemi di dipendenze minimali (coperture minimali/canoniche)
Definizione. Sia F un insieme di dipendenze funzionali.
1. Data X → Y ∈ F , X contiene un attributo estraneo (oridondante) A se e solo se (F \ {X → Y }) ∪ {X \A → Y } ≡ F ,ossia se e solo se X \A → Y ∈ F+
2. X → Y ∈ F e una dipendenza ridondante se e solo seF \ {X → Y } ≡ F , ossia se e solo se X → Y ∈ (F \ {X → Y })+
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 19
'
&
$
%
Coperture minimali (o canoniche) - 2
3. F e una copertura canonica se e solo se:
a. ogni parte destra di una dipendenza ha un unico attributo
b. le dipendenze non contengono attributi estranei
c. non vi sono dipendenze ridondanti
Nota bene: l’ordine delle operazioni a, b e c e fondamentale
Teorema. Per ogni insieme di dipendenze F , esiste una coperturacanonica (non necessariamente unica)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 20
'
&
$
%
Calcolo della copertura minimale: l’algoritmo - 1
Algoritmo polinomiale per il calcolo di una coperturaminimale/canonica (si assuma che ogni dipendenza funzionaleabbia un unico attributo nella parte destra)
algoritmo CALCOLO DELLA COPERTURA CANONICA
input Insieme di dipendenze funzionali F
output G copertura canonica di F
begin
G := F ;
for each X → Y ∈ G with |X| > 1 do
begin
Z := X;
for each A ∈ X do if Y ⊆ (Z \ {A})+F then Z := Z \ {A};G := (G \ {X → Y }) ∪ {Z → Y }end;
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 21
'
&
$
%
Calcolo della copertura minimale: l’algoritmo - 2
for each X → Y ∈ G do
if Y ⊆ X+G\{X→Y } then G := G \ {X → Y }
end
Osservazione. Un insieme di dipendenze funzionali puo avere piucoperture canoniche
Esempio. Per l’insieme F = {AB → C, A → B, B → A}, siaF = {B → C,A → B,B → A} che F = {A → C, A → B, B → A}sono coperture canoniche
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 22
'
&
$
%
Decomposizione di schemi - 1
Definizione. Dato uno schema R〈T, F 〉, ρ = {R1(T1), . . . , Rk(Tk)} euna decomposizione di R se e solo se ∪iTi = T
Non tutte le decomposizioni conservano i dati.
Esempio. Si consideri una relazione con schema R(P, T, C), conassociato l’insieme F = {T → C,C → P} di dipendenze funzionali,che memorizza informazioni relative ai proprietari (P ) di case (C)dotate di uno o piu telefoni (T ). Sia data la seguente istanza di R
P T C
p1 t1 c1
p1 t2 c2
p1 t3 c2
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 23
'
&
$
%
Decomposizione di schemi - 2
Esempio (continua). Supponiamo di decomporre lo schema nelledue relazioni R1(P, T ) e R2(P,C). Le corrispondenti istanze sonor1 = ΠP,T (R)
P T
p1 t1
p1 t2
p1 t3
e r2 = ΠP,C(R)
P C
p1 c1
p1 c2
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 24
'
&
$
%
Decomposizione di schemi - 3
Esempio (continua). Per ricostruire l’istanza iniziale occorrefondere le due istanze della decomposizione attraverso un naturaljoin. Il risultato che si ottiene non coincide, pero, con l’istanzadata: ha piu tuple (e viola le dipendenze)
P T C
p1 t1 c1
p1 t1 c2
p1 t2 c1
p1 t2 c2
p1 t3 c1
p1 t3 c2
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 25
'
&
$
%
Decomposizioni lossless join
Definizione. Una decomposizione ρ = {R1(T1), . . . , Rk(Tk)} diR〈T, F 〉 preserva i dati (e lossless join) se e solo se per ogniistanza r ∈ R che soddisfa F , r = ΠT1(r) ./ . . . ./ ΠTk
(r)
In generale, vale solo il seguente teorema:
Teorema. Sia mρ(r) = ΠT1(r) ./ . . . ./ ΠTk(r). Data una
decomposizione ρ = {R1(T1), . . . , Rk(Tk)} qualunque di R〈T, F 〉,per ogni istanza r ∈ R si ha che r ⊆ mρ(r)
Alcune (altre) proprieta interessanti (ad esempio, r ⊆ s implicamρ(r) ⊆ mρ(s))
Un punto di vista diverso: le tuple isolate (tuple che possono essererappresentate solo attraverso alcune delle relazioni componenti)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 26
'
&
$
%
Controllo dei lossless join: un algoritmo - 1
algoritmo CONTROLLO DEI LOSSLESS JOIN
input Uno schema relazionale R〈T, F 〉, con T = {A1, . . . , An},e una scomposizione ρ = {R1(T1), . . . , Rk(Tk)}
output ρ e/non e una scomposizione lossless join
metodo inizializzazione
costruire una tabella con n colonne e k righe, dove la colonna
j corrisponde all’attributo Aj e la riga i allo schema Ri;
porre il simbolo aj nella posizione (i, j) se Aj ∈ Ti,
altrimenti porre bij ;
elaborazione
si considerino ordinatamente e ripetutamente le dipendenze
X → Y ∈ F fino a quando esse non provocano piu variazioni
nella tabella
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 27
'
&
$
%
Controllo dei lossless join: un algoritmo - 2
per ogni dipendenza X → Y e ogni coppia di righe, se le
due righe coincidono su X, allora renderle uguali su Y
e propagare le variazioni
(dati due simboli da uguagliare, se uno dei due e aj ,
sostituire l’altro con aj ; se sono bij e blj , sostituire blj
con bij , o viceversa, a discrezione;
quando due simboli sono uguagliati, uguagliare tutte le
occorrenze di tali simboli nella tabella)
risultato
se al termine dell’elaborazione, esiste almeno una riga
con tutte a (riga a1, . . . , an), la decomposizione e lossless
join, altrimenti non lo e
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 28
'
&
$
%
Un esempio - 1
Esempio. Siano dati lo schema R〈T, F 〉, con T = {A,B,C, D,E} eF = {A → C, DE → C,B → C, CE → A,C → D}, e ladecomposizione ρ = {R1({A, D}), R2({A,B}), R3({B,E}),R4({C, D, E}), R5({A, E})}
A B C D E
a1 b12 b13 a4 b15
a1 a2 b23 b24 b25
b31 a2 b33 b34 a5
b41 b42 a3 a4 a5
a1 b52 b53 b54 a5
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 29
'
&
$
%
Un esempio - 2
Esempio (continua). Con l’applicazione delle dipendenze A → C,DE → C (non produce effetti) e B → C, si ottiene la tabella:
A B C D E
a1 b12 b13 a4 b15
a1 a2 b13 b24 b25
b31 a2 b13 b34 a5
b41 b42 a3 a4 a5
a1 b52 b13 b54 a5
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 30
'
&
$
%
Un esempio - 3
Esempio (continua). Con l’applicazione delle dipendenze CE → A
e C → D, si ottiene la tabella:
A B C D E
a1 b12 b13 a4 b15
a1 a2 b13 a4 b25
a1 a2 b13 a4 a5
b41 b42 a3 a4 a5
a1 b52 b13 a4 a5
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 31
'
&
$
%
Un esempio - 4
Esempio (continua). All’iterazione successiva, grazie agli effettidelle dipendenze DE → C, B → C e CE → A, si ottiene la tabella:
A B C D E
a1 b12 a3 a4 b15
a1 a2 a3 a4 b25
a1 a2 a3 a4 a5
a1 b42 a3 a4 a5
a1 b52 a3 a4 a5
L’applicazione delle dipendenze funzionali non produce piu effetti.C’e una riga con tutte a: decomposizione lossless join
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 32
'
&
$
%
Risultati - 1
Teorema. L’algoritmo proposto termina ed e corretto e completo.
La terminazione e immediata (e sufficiente osservare come ogniapplicazione di una dipendenza che produce effetti riduca il numerodi valori diversi presenti nella tabella)
E, inoltre, facile mostrare che se la tabella finale non contiene unariga di tutte a, allora la decomposizione non e lossless join (latabella finale stessa costituisce un controesempio: essa puo esserevista come un’istanza r della relazione, che soddisfa tutte ledipendenze, tale che (a1, . . . , an) 6∈ r e (a1, . . . , an) ∈ mρ(r);l’ultima appartenenza segue dalla definizione di decomposizione edalle modalita di inizializzazione ed elaborazione della tabella)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 33
'
&
$
%
Risultati - 2
Osservazione. L’esecuzione dell’algoritmo puo essere terminata nonappena viene prodotta una riga con tutte a (una volta generata,una tale riga non puo essere piu rimosssa)
Un caso particolare:
Teorema. Sia ρ = {R1(T1), R2(T2)} una decomposizione di R〈T, F 〉.ρ e una decomposizione lossless join se e solo se T1 ∩ T2 → T1 ∈ F+
oppure T1 ∩ T2 → T2 ∈ F+ (o entrambe)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 34
'
&
$
%
Decomposizioni che preservano le dipendenze - 1
(Contro)esempio. Consideriamo nuovamente la relazioneR(P, T, C), con associato l’insieme di dipendenzeF = {T → C,C → P}, che memorizza informazioni relative aiproprietari (P ) di case (C) dotate di uno o piu telefoni (T ), e larelativa istanza
P T C
p1 t1 c1
p1 t2 c2
p1 t3 c2
Sia data la decomposizione ρ = {R1({P, T}), R2({T, C})}
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 35
'
&
$
%
Decomposizioni che preservano le dipendenze - 2
Controesempio (continua). Le corrispondenti istanze sonor1 = ΠP,T (R)
P T
p1 t1
p1 t2
p1 t3
e r2 = ΠT,C(R)
T C
t1 c1
t2 c2
t3 c2
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 36
'
&
$
%
Decomposizioni che preservano le dipendenze - 3
Controesempio (continua). La decomposizione preserva i datiperche PT ∩ TC(= T ) → TC e derivabile da F (aumento di T → C
con T ) e, quindi, per la correttezza degli assiomi di Armstrong,appartiene a F+
La decomposizione non conserva, pero, la dipendenza C → P , doveC ∈ R2 e P ∈ R1.
Conseguenze della perdita di dipendenze: inserimentiinconsistenti
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 37
'
&
$
%
Un esempio di inserimento inconsistente
Esempio. Si supponga di voler inserire nella base di dati il fatto chela casa c2 ha un nuovo telefono t4 intestato alla persona p2.
Se applicato alla relazione R, un tale inserimento verrebbe proibitoperche violerebbe la dipendenza C → P (la casa c2 e gia associataalla persona p1 e il telefono va intestato all’unico proprietario dellacasa)
Se applicato alle relazioni R1 ed R2, l’inserimento va a buon fine,perche non viola le dipendenze associate ai due schemi (ladipendenza C → P e stata persa per effetto della decomposizione)
Decomposizione alternativa che preserva dati e dipendenze:ρ = {R1({T,C}), R2({C,P})}
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 38
'
&
$
%
Proiezione di insiemi di dipendenze
Definizione. Dati R〈T, F 〉 e Ti ⊆ T , la proiezione di F su Ti,denotata ΠTi(F ), e l’insieme di dipendenze
{X → Y ∈ F+ | X, Y ⊆ Ti}
Nota Bene: la definizione utilizza l’insieme F+ non l’insieme F
Esempio. Si consideri la relazione R〈T, F 〉, con T = {A,B, C} eF = {A → B, B → C, C → A}. Si ha che ΠAB(F ) ={A → B,B → A} (piu le dipendenze banali e dipendenzeimmediatamente derivabili, quale, ad esempio, A → AB) eΠAC(F ) = {A → C,C → A} (piu le dipendenze banali edipendenze immediatamente derivabili).
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 39
'
&
$
%
Un algoritmo per il calcolo delle proiezioni
Un algoritmo banale (di complessita esponenziale) per il calcolodelle proiezioni di un insieme di dipendenze
algoritmo CALCOLO DELLE PROIEZIONI
input R〈T, F 〉 e Ti ⊆ T
output una copertura della proiezione di F su Ti
begin
for each Y ⊆ Ti do
begin
Z := Y +F
restituisci Y → (Z ∩ Ti)
end
end
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 40
'
&
$
%
Conservazione delle dipendenze: formalizzazione
Definizione. Una decomposizione ρ = {R1(T1), . . . , Rk(Tk)} di unarelazione R〈T, F 〉 preserva le dipendenze se e solo se∪k
i=1ΠTi(F ) ≡ F
Esempio. Si consideri lo schema di relazione R〈T, F 〉, doveT = {A,B, C} e F = {A → B, B → C,C → A}. Dato cheAC 6⊆ AB and AC 6⊆ BC, uno potrebbe essere indotto a pensareche la decomposizione ρ = {R1({A,B}), R2({B, C})} non preservile dipendenze (la dipendenza C → A non viene proiettata ne su R1
ne su R2).
Cosı non e: da {A → B, B → A} ⊆ ΠAB(F ) e {B → C,
C → B} ⊆ ΠBC(F ), segue ΠAB(F ) ∪ΠBC(F ) ` C → A
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 41
'
&
$
%
Proiezioni e calcolo della chiusura
Un algoritmo (di complessita polinomiale) per il calcolo dellachiusura di un insieme di attributi rispetto all’unione delleproiezioni di un insieme di dipendenze
algoritmo CALCOLO DELLA CHIUSURA
input R〈T, F 〉, X ⊆ T e ρ = {R1(T1), . . . , Rk(Tk)}output X+
G , dove G = ∪ki=1ΠTi(F )
begin
X+G := X;
while (X+G e cambiato) do
for each i := 1 to k do
X+G = X+
G ∪ ((X+G ∩ Ti)
+F ∩ Ti)
end
Nota bene: l’insieme G non viene esplicitamente determinato
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 42
'
&
$
%
Controllo della conservazione delle dipendenze
Un algoritmo (di complessita polinomiale) per stabilire se una datascomposizione preserva o meno le dipendenze associate ad una datarelazione
algoritmo CONTROLLO DELLA CONSERVAZIONE DELLE
DIPENDENZE
input una decomposizione ρ = {R1(T1), . . . , Rk(Tk)} di R〈T, F 〉output ρ e/non e una decomposizione che preserva le dipendenze
begin
for each X → Y ∈ F do
if Y 6⊆ X+G then termina con NO
termina con SI
end
dove X+G e determinato con l’algoritmo per il calcolo della chiusura
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 43
'
&
$
%
Un esempio - 1
Esempio. Sia data lo schema di relazione R〈T, F 〉, dove T = {A,B,
C, D} e F = {A → B,B → C, C → D, D → A}. Data la circolaritadelle dipendenze, e facile vedere come ogni attributo determinitutti gli altri.
Controlliamo se la decomposizione ρ = {R1({A,B}), R2({B, C}),R3({C, D})} preserva o meno le dipendenze utilizzando l’algoritmoproposto.
Le prime tre dipendenze sono banalmente preservate.Concentriamo la nostra attenzione sulla quarta, verificando seA ∈ D+
G, dove G = ΠAB(F ) ∪ΠBC(F ) ∪ΠCD(F )
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 44
'
&
$
%
Un esempio - 2
Iniziamo il calcolo di D+G, ponendo D+
G = {D}. Il corpo del cicloeseguito per R1 non modifica D+
G perche {D} ∪ (({D} ∩ {A,B})+F∩{A,B}) = {D}. Lo stesso accade per R2 perche {D} ∪ (({D}∩{B, C})+F ∩{B, C}) = {D}. Per R3 si ha:
D+G = {D} ∪ (({D} ∩ {C, D})+F ∩ {C, D})
= {D} ∪ (({D})+F ∩ {C,D})= {D} ∪ ({A,B, C,D} ∩ {C, D})= {C, D}
Analogamente, nel passo successivo, eseguendo il corpo del ciclo perR2 su D+
G = {C,D} produce D+G = {B, C,D}. Infine, nel terzo
passo si ottiene D+G = {A,B, C,D}, da cui risulta vero che A ∈ D+
G.
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 45
'
&
$
%
Conservazione di dati e dipendenze
Come confermato dagli esempi precedenti, conservazione dei dati econservazione delle dipendenze sono due proprieta indipendenti:
• vi possono essere decomposizioni lossless join che nonpreservano le dipendenze
• vi possono essere decomposizioni che preservano le dipendenze,ma non sono lossless join
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 46
'
&
$
%
Forme normali - 1
Normalizzazione dei dati: processo attraverso il quale schemi direlazione insoddisfacenti, ossia schemi che presentano ridondanze epossono, quindi, causare anomalie di aggiornamento, sonodecomposti in schemi di relazione piu piccoli che possiedono leproprieta volute
Forme normali: strumento per la valutazione della qualita dischemi di relazione individuali. Permettono di stabilire se unoschema si trova o meno nella forma (normale) voluta. In caso diviolazione, lo schema puo essere decomposto in modo che gli schemidelle relazioni componenti rispettino la forma (normale) voluta
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 47
'
&
$
%
Forme normali - 2
Prima forma normale (1NF), basata sulla definizione di relazione
Seconda forma normale (2NF), terza forma normale (3NF) eforma normale di Boyce-Codd (BCNF), tutte basate sulla nozionedi dipendenza funzionale
Quarta forma normale, basata sulla nozione di dipendenzamultivalore
Quinta forma normale, basata sulla nozione di join-dipendenza
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 48
'
&
$
%
Forme normali - 3
Osservazione fondamentale
in generale non e sufficiente garantire che il processo didecomposizione generi relazioni nella forma normale voluta; occorreanche garantire che:
1. la decomposizione conservi i dati (sia lossless join)
2. la decomposizione preservi le dipendenze
Definizione. Dato uno schema di relazione R〈T, F 〉, un attributoA ∈ T si dice primo se e solo se appartiene ad almeno una chiave;altrimenti, si dice non primo
Il problema di stabilire se un attributo e primo o meno eNP-completo
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 49
'
&
$
%
Prima forma normale - 1
Eliminazione degli attributi multivalore
Relazione non in 1NF:
DIPARTIMENTO DNUMERO DNOME MANAGER DSEDE
con dipendenze:
DNUMERO → DNOME, DNUMERO → MANAGER, DNUMERO → DSEDE(?)
Istanza di relazione:
DIPARTIMENTO DNUMERO DNOME MANAGER DSEDE
5 Ricerca RSS.. {Roma, Bologna}4 Sviluppo VRD.. V enezia
1 Direzione BNC.. Bologna
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 50
'
&
$
%
Prima forma normale - 2
Relazione in 1NF (con ridondanza):
DIPARTIMENTO DNUMERO DNOME MANAGER DSEDE
5 Ricerca RSS.. Roma
5 Ricerca RSS.. Bologna
4 Sviluppo VRD.. V enezia
1 Direzione BNC.. Bologna
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 51
'
&
$
%
Prima forma normale - 3
Eliminazione degli attributi strutturati
Relazione IMP PROG (Impiegato Progetto) con attributi CF(codice fiscale), INOME (nome impiegato) e PROG (progetto a cuil’impiegato dedica il maggior numero di ore, con indicazione delnumero di ore) non in 1NF:
IMP PROG CF INOME PROG(PNUMERO,ORE)
con dipendenze:
CF → INOME, CF → P ROG(?)
L’attributo strutturato PROG puo essere visto come una relazioneannidata
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 52
'
&
$
%
Prima forma normale - 4
Istanza di relazione:
IMP PROG CF INOME PROG(PNUMERO,ORE)
RSS.. Rossi Antonio (1,18)
VRD.. Verdi Filippo (3,36)
BNC.. Bianchi Livio (3,12)
Relazione in 1NF (senza ridondanze):
IMP PROG CF INOME PNUMERO ORE
RSS.. Rossi Antonio 1 18
VRD.. Verdi Filippo 3 36
BNC.. Bianchi Livio 3 12
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 53
'
&
$
%
Prima forma normale - 5
Eliminazione degli attributi strutturati e multivalore
Relazione IMP PROG (Impiegato Progetto) con una relazioneannidata PROG multivalore non in 1NF:
IMP PROG CF INOME PROG(PNUMERO,ORE)
con dipendenze:
CF → INOME, CF → P ROG(?)
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 54
'
&
$
%
Prima forma normale - 6
Istanza di relazione:
IMP PROG CF INOME PROG(PNUMERO,ORE)
RSS.. Rossi Antonio {(1,18), (2,18)}VRD.. Verdi Filippo {(3,36)}BNC.. Bianchi Livio {(2,12),(3,12),(5,12)}
Relazione IMP PROG in 1NF
IMP PROG CF INOME PNUMERO ORE
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 55
'
&
$
%
Seconda forma normale - 1
La nozione di dipendenza funzionale parziale e totale
Una dipendenza funzionale X → Y e una dipendenza funzionaletotale se la rimozione di un qualsiasi attributo A da X rende ladipendenza non piu valida (∀A(X \ {A} 6→ Y ))
Una dipendenza funzionale X → Y e una dipendenza funzionaleparziale se qualche attributo di X puo essere rimosso senzapregiudicare la validita della dipendenza (∃A(X \ {A} → Y ))
Definizione. Uno schema di relazione R〈T, F 〉 e in seconda formanormale (2NF) se ogni attributo non primo A ∈ T dipendetotalmente da ogni chiave di R
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 56
'
&
$
%
Seconda forma normale - 2
Sia dato lo schema di relazione (non in 2NF):
IMP PROG CF PNUMERO ORE INOME PNOME PSEDE
con dipendenze:
CF P NUMERO → ORE
CF → INOME
P NUMERO → P NOME P SEDE
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 57
'
&
$
%
Seconda forma normale - 3
Decomposizione in 2NF:
IP1 CF PNUMERO ORE
con dipendenze:
CF P NUMERO → ORE
IP2 CF INOME
con dipendenze:
CF → INOME
IP3 PNUMERO PNOME PSEDE
con dipendenze:
P NUMERO → P NOME P SEDE
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 58
'
&
$
%
Terza forma normale - 1
La nozione di dipendenza funzionale transitiva e non transitiva
Una dipendenza funzionale X → Y e una dipendenza funzionaletransitiva se esiste un insieme di attributi Z che non e contenutoin alcuna chiave ed e tale che valgono sia X → Z che Z → Y
Definizione. Uno schema di relazione R〈T, F 〉 e in terza formanormale (3NF) se ogni attributo non primo A ∈ T dipendetotalmente da ogni chiave di R (R e in 2NF) e dipende in modonon transitivo da ogni chiave di R
Definizione (alternativa). Uno schema di relazione R〈T, F 〉 e interza forma normale (3NF) se e solo se, per ogni dipendenzafunzionale non banale X → A ∈ F+, X e superchiave o A e primo
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 59
'
&
$
%
Terza forma normale - 2
Sia dato lo schema di relazione (non in 3NF):
IMP DIP CF INOME DNASCITA IND DNUMERO DNOME MANAGER
con dipendenze:
CF → INOME DNASCIT A IND DNUMERO
DNUMERO → DNOME MANAGER
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 60
'
&
$
%
Terza forma normale - 3
Decomposizione in 3NF:
ID1 CF INOME DNASCITA IND DNUMERO
con dipendenze:
CF → INOME DNASCIT A IND DNUMERO
IP2 DNUMERO DNOME MANAGER
con dipendenze:
DNUMERO → DNOME MANAGER
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 61
'
&
$
%
Un esempio di normalizzazione - 1
Sia dato lo schema di relazione (non in 2NF):
APPEZZAMENTO PROP ID CONTEA APP ID AREA VALORE ALIQUOTA
con dipendenze:
P ROP ID → CONT EA AP P ID AREA V ALORE ALIQUOT A
CONT EA AP P ID → P ROP ID AREA V ALORE ALIQUOT A
CONT EA → ALIQUOT A
AREA → V ALORE
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 62
'
&
$
%
Un esempio di normalizzazione - 2
Decomposizione in 2NF:
APPEZZAMENTO1 PROP ID CONTEA APP ID AREA VALORE
con dipendenze:
P ROP ID → CONT EA AP P ID AREA V ALORE
CONT EA AP P ID → P ROP ID AREA V ALORE
AREA → V ALORE
APPEZZAMENTO2 CONTEA ALIQUOTA
con dipendenze:
CONT EA → ALIQUOT A
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 63
'
&
$
%
Un esempio di normalizzazione - 3
Decomposizione in 3NF:
APPEZZAMENTO11 PROP ID CONTEA APP ID AREA
con dipendenze:
P ROP ID → CONT EA AP P ID AREA
CONT EA AP P ID → P ROP ID AREA
APPEZZAMENTO12 AREA VALORE
con dipendenze:
AREA → V ALORE
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 64
'
&
$
%
Controllo della 3NF - 1
Come verificare se uno schema di relazione e in 3NF senza generareF+?
Valgono il seguente teorema e l’immediato corollario
Teorema. Uno schema di relazione R〈T, F 〉 e in 3NF se e solo se,per ogni dipendenza funzionale X → A1 .. An ∈ F e per ognii ∈ {1, .., n}, Ai ∈ X oppure X e una superchiave oppure Ai eprimo
Corollario. Uno schema di relazione R〈T, F 〉, con F coperturacanonica, e in 3NF se e solo se, per ogni dipendenza funzionaleelementare X → A ∈ F , X e una superchiave oppure A e primo
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 65
'
&
$
%
Controllo della 3NF - 2
E’ comunque necessario determinare l’insieme di attributi primi..
Proposizione. Il problema di decidere se uno schema di relazione ein 3NF e NP-completo
Esempio. Consideriamo lo schema di relazione
R〈{PREFISSO, NUMERO, LOCALITA′, NOMEABBONATO,
V IA}, {PREFISSO NUMERO → LOCALITA′, PREFISSO
NUMERO → NOMEAABONATO, PREFISSO NUMERO →V IA, LOCALITA′ → PREFISSO}〉 che descrive gli abbonati al
telefono. Le chiavi della relazione sono (PREFISSO NUMERO) e
(LOCALITA′, NUMERO)
Esiste, pero, una ridondanza: ogni volta che si inserisce un nuovo
numero telefonico di una certa localita, occorre ripetere l’informazione
sul prefisso
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 66
'
&
$
%
La forma normale di Boyce-Codd (BCNF)
Definizione. Uno schema di relazione R〈T, F 〉 e nella formanormale di Boyce-Codd (BCNF) se e solo se per ognidipendenza funzionale non banale X → Y ∈ F+, X e unasuperchiave.
Come verificare se uno schema di relazione e in BCNF senzagenerare F+?
Valgono il seguente teorema e l’immediato corollario
Teorema. Uno schema di relazione R〈T, F 〉 e in BCNF se e solo se,per ogni dipendenza funzionale non banale X → Y ∈ F , X e unasuperchiave
Corollario. Uno schema di relazione R〈T, F 〉, con F coperturacanonica, e in BCNF se e solo se, per ogni dipendenza funzionaleelementare X → A ∈ F , X e una superchiave
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 67
'
&
$
%
Ottimalita di BCNF
Teorema. In una relazione in BCNF, nessun valore puo esseredeterminato a partire da altri utilizzando le dipendenze funzionali
Il teorema stabilisce l’ottimalita della BCNF rispetto ai vincoliimposti dalle dipendenze funzionali
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 68
'
&
$
%
Un secondo esempio di normalizzazione - 1
Sia dato lo schema di relazione (non in BCNF):
APPEZZAMENTO1A PROP ID CONTEA APP ID AREA
con dipendenze:
P ROP ID → CONT EA AP P ID AREA
CONT EA AP P ID → P ROP ID AREA
AREA → CONT EA
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 69
'
&
$
%
Un secondo esempio di normalizzazione - 2
Decomposizione in BCNF:
APPEZZAMENTO1A1 PROP ID APP ID AREA
con dipendenze:
P ROP ID → AP P ID AREA
APPEZZAMENTO1A2 AREA CONTEA
con dipendenze:
AREA → CONT EA
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 70
'
&
$
%
BCNF e conservazione delle dipendenze
Decomposizione in BCNF di uno schema di relazione econservazione delle dipendenze non sono obiettivi che possanoessere sempre raggiunti in modo congiunto
R A B C
con dipendenze:
A B → C
C → B
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 71
'
&
$
%
Normalizzazione di schemi in BCNF - 1
Un algoritmo esponenziale che preserva i dati, ma non ledipendenze, e il seguente
algoritmo DECOMPOSIZIONE IN BCNF
input R〈T, F 〉 con F copertura canonica
output ρ = {R1, . . . , Rm} decomposizione di R in BCNF che preserva i dati
begin
ρ := {R1〈T, F 〉}; n := 1;
while esiste Ri〈Ti, Fi〉 ∈ ρ non in BCNF a causa di X → A do
begin
n := n + 1;
T ′ := X+; F ′ := ΠT ′(Fi); T′′ := Ti −A; F ′′ := ΠT ′′(Fi);
ρ := (ρ \Ri〈Ti, Fi〉) ∪ {Ri〈T ′, F ′〉, Rn〈T ′′, F ′′〉}end;
end
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 72
'
&
$
%
Normalizzazione di schemi in BCNF - 2
La conservazione dei dati da parte dell’algoritmo precedente seguedal seguente teorema
Teorema. Sia ρ = {R1, . . . , Rm} una decomposizione di R〈T, F 〉 chepreserva i dati (rispetto a F ) e sia σ = {S1, S2} unadecomposizione di R1 che preserva i dati (rispetto a ΠT1(F )).Allora la decomposizione ρ′ = {S1, S2, R2, . . . , Rm} preserva i dati(rispetto a F )
Osservazione. Esistono anche algoritmi polinomiali didecomposizione in BCNF, ma vengono poco utilizzati percheproducono schemi poco naturali e fortemente decomposti
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 73
'
&
$
%
Decomposizioni di schemi in 3NF - 1
Un algoritmo polinomiale che preserva i dati e le dipendenzealgoritmo DECOMPOSIZIONE IN 3NF
input una relazione R〈T, F 〉output una decomposizione ρ = {R1, . . . , Rm} in 3NF
che preserva i dati e le dipendenze
begin
Passo 1. Trova una copertura canonica G di F e poni ρ = ∅Passo 2. Sostituisci in G ogni insieme di dipendenze X → A1,
. . . , X → Ak con la dipendenza X → A1, . . . , Ak
Passo 3. Per ogni dipendenza X → Y ∈ G, inserisci uno schema
con attributi XY in ρ (X e detta chiave sintetizzata)
Passo 4. Elimina da ρ ogni schema contenuto in un altro
Passo 5. Se nessuno schema in ρ ha come insieme di attributi
una superchiave di R, aggiungi uno schema con attributi W ,
con W chiave di R, a ρ
end
Teoria della Progettazione delle Basi di Dati Relazionali Complementi di Basi di Dati 74
'
&
$
%
Decomposizioni di schemi in 3NF - 2
Teorema. Dato uno schema R〈T, F 〉, l’algoritmo proposto produceuna decomposizione ρ = {R1, . . . , Rm} di R che preserva i dati e ledipendenze
Teorema. Dato uno schema R〈T, F 〉, l’algoritmo proposto produceuna decomposizione ρ = {R1, . . . , Rm} di R in schemi in 3NF(rispetto alle proiezioni di F su Ti, con i = 1, . . . , m)