Teoria della Normalizzazione Basi di Dati 2010/11

1. Teoria della NormalizzazioneBasi di Dati 2010/11

2. Obiettivo — Sviluppare una metodologia che permetta di: Decidere se un particolare schema di relazione è un buon schema Nel caso che uno schema di relazione R non soddisfi i criteri di bontà, decomporlo in un insieme di schemi di relazione {R1, R2, ..., Rn} tali che ogni Ri sia un buon schema la decomposizione sia senza perdite Il nostro approccio è basato su: dipendenze funzionali dipendenze multivalore

3. Dipendenze funzionali Esprimono vincoli sulla ammissibilità delle istanza delle relazioni Stabiliscono che, all’interno di ogni tupla, i valori di alcuni attributi determinino i valori di altri attributi Generalizzazione del concetto di chiave

4. Dipendenze funzionali (cont.) Sia R uno schema di relazione sull’insieme X di attributi, siano inoltre  X e  X Vale la dipendenza funzionale  su R se e solo se per ogni istanza di r di R, ogni coppia di ennuple t1and t2 di r aventi gli stessi valori per gli attributi in , ha gli stessi valori per gli attributi in . Formalmente: t1[] = t2 [] t1[] = t2 [] Esempio: Considerando la seguente istanza r dello schema R(A,B) si osserva che: ABNON vale. 3 4 1 5 3 7

5. Ricordiamo che: K è una superchiave per uno schema R(X) se e solo se K X K è chiave candidata per R(X) se e solo se K X, e per nessun K si ha X le dipendenze funzionali permettono di esprimere vincoli non esprimibili tramite la nozione di chiave. Ad esempio: consideriamo lo schema: Vendita (nomeCliente, codiceMerce, nomeProduttore, ammontare). Desideriamo che valgano le seguenti dipendenze: codiceMerceammontare codiceMerce  nomeProduttore ma non desideriamo che valga: codiceMerce nomeCliente

6. Dipendenze funzionali Una dipendenza funzionale èbanale se è sempre soddisfatta da ogni possibile istanza di una relazione. In generale, se vale, allora la DF è banale

7. Chiusura di un insieme di dipendenze funzionali Dato un insieme F di dipendenze funzionali, vi possono essere altre dipendenze funzionali logicamente implicate da F. Ad es., se valgono A  B e B  C, possiamo inferire che vale A  C L’insieme F+ di tutte le dipendenze funzionali logicamente implicate da F è detto chiusura di F. Possiamo trovare tutti gli elementi diF+applicando gli assiomi di Armstrong (dove , e  sono insiemi di attributi): se , allora (riflessività) se , allora (arricchimento) se , e , allora (transitività) Proprietà: queste regole di inferenza sono corrette (generano solo dipendenze valide) e complete (generano tutte le dipendenze valide)

8. Esempio R = (A, B, C, G, H, I)F = { A B, A C, CG H, CG I, B H} alcuni membri di F+ sono: A H per transitività da A B e B H AG I arricchendo A C con G, per ottenere AG CG e poi utilizzando la transitività con CG I CG HI da CG H e CG I Questa è una applicazione della union rule. Tale regola può essere giustificata in base a: la definizione di dipendenza funzionale, oppure sfruttando arricchimento di CG I per ottenere CG  CGI, arricchimento diCG H per ottenere CGI HI, e infine transitività

9. Calcolo di F+ • Algoritmo di calcolo della chiusura di un insieme di dipendenze funzionali F: F+ = Frepeatfor each dipendenza funzionale f in F+ applica riflessività e arricchimento a f eaggiungi ad F+ le dipendenze ottenutefor each coppia di dipendenze f1 e f2 in F+iff1 e f2 possono essere combinate utilizzando la transitività then aggiungi ad F+ le dip. ottenuteuntil F+ non cambia

10. Calcolo di F+ Possiamo velocizzare/semplificare il calcolo di F+ utilizzando ulteriori regole di inferenza: Se valgono e , allora vale anche (unione) Se vale , allora valgono anche e (decomposizione) Se valgono e , allora vale anche (pseudotransitività) Esercizio: Ricavare le precedenti regole a partire dagli assiomi di Armstrong.

11. Chiusuradi un insieme di attributi Dato un insieme di attributi  si definisce la chiusuradi rispetto aF (denotata con +) l’insieme di tutti gli attributi che sono funzionalmente determinati da attributi in  utilizzando le dipendenza in F. Avremo che:è in F+ se e solo se + Algoritmo che computa + rispetto a F: result := ;while (ci sono cambiamenti in result) do for each in F do begin if result then result := result end

12. Esempio R(X) = (A, B, C, G, H, I) (quindi X = ABCGHI ) F = {A B, A C, CG H, CG I, B H} (AG)+ 1. result = AG 2. result = ABCG (da A C e A  B) 3. result = ABCGH (da CG H e CG AGBC) 4. result = ABCGHI (da CG I e CG AGBCH)

13. Usare la chiusura di attibuti… Viene sfruttata in diversi contesti: • per verificare se un insieme di attributi è una superchiave: • per verificare se  è superchiave si calcola +.  è superchiave se +contiene tutti gli attributi di R(X). • per verificare se vale una dipendenza funzionale: • per verificare se vale  (ovvero se appartiene a F+) basta verificare se vale +. • cioè, si calcola +, e si verifica se contiene tutti gli attributi di . • calcolo della chiusura di F • per ogni X, si calcola la chiusura +, e per ogni Y+, generiamo la dipendenza Y.

14. Copertura Minimale Un insieme F di dipendenze funzionali può contenere dipendenze ridondanti, ovvero che possono essere ottenute dalle altre dipendenze di F Esempio: A  C è ridondante in {A  B, B  C, A  C} Anche degli attributi di una dipendenza funzionale potrebbero essere ridondanti: A destra: {A  B, B  C, A  CD} può essere semplificata in {A  B, B  C, A  D} A sinistra: {A  B, B  C, AC  D} può essere semplificata in {A  B, B  C, A  D} Intuitivamente, una copertura minimale di F è un insieme “minimale” di dipendenze funzionali equivalente a F e privo di dipendenze e attributi ridondanti

15. Copertura Minimale Piu' formalmente, uninsiemeF di DF è minimale se e solo se: Ogni DF in F ha come parte destraun solo attributo Non e' possibile sostituire una DF X → A di F con una DF Y → A, dove Y e'un sottoinsieme proprio di X, e avere ancora un insieme di DF equivalente ad F Non e' possibile rimuovere una DF da F e avere ancora un insieme di DF equivalente ad F. Una copertura minimale di un insieme di DF E e' un insieme minimale di DF F equivalente ad E, ovvero tale che F+ = E+ .

16. Algoritmo di Copertura Minimale Ricerca di una copertura minimale F per un insieme di DF E: Si imposti F:=E Si sostituisca ogni DF X → {A1,...,An} in F con le n DF X → A1, ...., X → An Per ogni DF X → A in F, per ogni attributo B in X: Se B e' ridondante nella DF X → A, ovvero se F e' equivalente ad {F – {X → A}} U { (X – {B}) → A}, allora si sostituisca X → A con X - {B} → A in F. Per ogni DF rimanente X → A in F: Se F – {X → A} e' equivalente ad F allora si rimuova X → A da F.

17. Come verificare ridondanza attributi? Sia F un insieme di DF. Consideriamo la DF X → Y in F e l'attributo B  X: Per verificare se B  X è ridondante: Calcoliamo la chiusura ({X} – B)+ rispetto a F Verifichiamo se ({X} – B)+ contiene Y; se sì, allora B è ridondante (e può essere eliminato)

18. Esempio R = (A, B, C)F = {A BC, B C, A B, ABC} Dopo l'esecuzione del passo (2) dell'algoritmo si ha: F ={A B, A → C, BC, AB → C} Eseguiamo il passo (3). A è ridondante in ABC ? Verifichiamo se la chiusura di B rispetto ad F contiene C Sì: Infatti B+={B,C}. Dunque F diventa {A B, A C, B → C} Eseguiamo il passo (4). A C è implicata logicamente da A B e B C (usando la transitivita'), e puo' dunque essere eliminata da F Una copertura canonica (o minimale) è: {A B, B C }

19. Forme normali basate su Dipendenze Funionali 1NF: Prima forma normale (2NF: Seconda forma normale) 3NF: Terza forma normale BCNF: Forma normale di Boyce-Codd Normalizzare uno schema di relazione R = Decomporre (opportunamente) R in schemi che siano “in forma normale”

20. Normalizzare sfruttando le dipendenze funzionali Decomponendo uno schema di relazione R sfruttando un insieme di dipendenze funzionali F in un insieme di schemi R1, R2,.., Rn vogliamo: Decomposizione Lossless-join(senza perdita informazione) Minimizzare la ridondanza: le relazioni Ridovrebbero essere o in Boyce-Codd Normal Form o in Third Normal Form. Conservare le dipendenze: Se Fi è l’insieme delle dipendenze in F+ che includono solo attributi in Ri allora la decomposizione deve essere “dependency preserving”, cioé (F1 F2  …  Fn)+ = F+ altrimenti, il controllo delle violazioni delle dipendenza funzionali (dello schema originario) comporterebbe la computazione esplicita di operazioni di join (sono le più costose).

21. Esempio R = (A, B, C)F = {A B, B C) può essere decomposto in due modi diversi R1 = (A, B), R2 = (B, C) decomposizione senza perdite conserva le dipendenze R1 = (A, B), R2 = (A, C) decomposizione senza perdite non conserva le dipendenze: (non posso controllare se viene violato il vincolo B C senza calcolare R1 R2)

22. Verificare la conservazione delle dipendenze • Per verificare se la dipendenza  è preservata in una decomposizione di R in R1, R2, …, Rn applichiamo il seguente test (le chiusure di attributi sono fatte rispetto a F) • result = while (result cambia) dofor eachRinella decomposizionet = (result Ri)+ Riresult = result t • Se result contiene tutti gli attributi in , allora la dipendenza funzionale è preservata. • Applicheremo il test su tutte le dipendenze in F. • Questa procedura impiega un tempo polinomiale, mentre un tempo esponenziale viene impiegato dalla computazione di F+e di (F1 F2  …  Fn)+

23. Boyce-Codd Normal Form è banale (ovvero, )  è superchiave di R(X) Uno schema di relation R(X) è in BCNF rispetto a un insieme F di dipendenze funzionali, se per ogni dipendenza in F+ della forma , con X and X,almeno una delle seguenti condizioni vale:

24. Esempio R(X) = (A, B, C)F = {AB B  C}Chiave = {A} R non è in BCNF Decomposizione: R1 = (A, B), R2 = (B, C) R1e R2 sono in BCNF la decomposizione è senza perdite e preserva le dipendenze

25. Test per BCNF Per verificare se una dipendenza funzionale non banale causa una violazione della BCNF 1. computare + (la chiusura di ), e 2. verificare se include tutti gli attributi di R, cioè se + è superchiave per R. Test semplificato: per verificare se uno schema R è in BCNF, è sufficiente verificare solo che le dipendenze del dato insieme F non violano la BCNF (invece che controllate tutte le dipendenze in F+). Infatti: se nessuna delle dipendenze in F causa una violazione della BCNF, allora nessuna delle dipendenze in F+ causa una violazione della BCNF. Tuttavia, utilizzare solo F è scorretto quando si effettua il test su una relazione della decomposizione di R. Esempio: consideriamo R (A, B, C, D), con F = { A  B, B  C} decomponiamo R in R1(A,B) e R2(A,C,D) nessuna delle dipendenze in F contiene solo attributi di (A,C,D), quindi potremmo credere che R2 soddisfi BCNF. tuttavia, la dipendenza AC in F+ mostra che R2 non è in BCNF.

26. Algoritmo per la decomposizione in BCNF result := {R};done := false;while (not done) do if (esiste uno schema S in result che non è in BCNF)then beginsi determini una DF  su S che violi BCNF;result := (result – S)  {(S – )}  {( )};end else done := true; Risultato: ogni Sè in BCNF, e la decomposizione è senza perdite

27. Esempio R = (nomeDitta, città, indirizzo, nomeCliente, codiceMerce, ammontare) F = {nomeDitta città indirizzo codiceMerce ammontare nomeDitta} Key = {nomeCliente, codiceMerce} Decomposizione R1 = (nomeDitta, città, indirizzo) R2 = (nomeDitta, nomeCliente, codiceMerce, ammontare ) R3 = (nomeDitta, codiceMerce, ammontare ) R4 = (nomeCliente, codiceMerce) Decomposizione finale R1, R3, R4

28. Test BCNF per la decomposizione Per verificare se uno schema Ridi una decomposizione di R è in BCNF si opera come segue: o verificare se Ri è in BCNF rispetto alla restrizione di F su Ri (cioé, tutte le dip. funz. in F+ che contengono solo attributi di Ri) oppure effettuare sull’insieme originale di dip. funz. F su R, il seguente test: per ogni insieme di attributi Ri, verificare che + o non includa attributi di Ri- , o includa tutti gli attributi di Ri. se la condizione è violata da qualche  in F, si dimostra che la dip. funz.  (+ - ) Rivale in Ri, e Ri viola la BCNF. Le dipendenze di questo tipo saranno sfruttate per decomporre ulteriormente lo schema Ri

29. BCNF e conservazione delle dipendenze R = (J, K, L)F = {JK L L K}due chiavi candidate: JK e JL R non è in BCNF ogni possibile decomposizione di R non preserva JK L Non è sempre possibile ottenere una BCNF che conservi le dipendenze. Esempio:

30. Third Normal Form: motivazioni Ci sono casi in cui BCNF non preserva le dipendenza, mentre è necessario avere una procedura efficiente per impedire le violazioni delle dip. funz. Soluzione: definire una forma normale più debole. ammettere della ridondanza (con i conseguenti svantaggi; vedremo esempio) ma garantire che le dip. funz. possano essere controllate sulle relazioni (decomposte) senza computare alcun join. Proprietà: esiste sempre una decomposizione in 3NF che conserva le dipendenze.

31. Third Normal Form Uno schema R è in 3NF se per ogni  in F+vale almeno una delle seguenti condizioni:  è banale (cioé, )  è superchiave di R ogni attributo A in  –  è contenuto in una chiave candidata di R. (Nota: attributi diversi possono essere contenuti in chiavi differenti) Una relazione in BCNF è anche in 3NF. La terza condizione è il rilassamento della BCNF che assicura la conservazione delle dipendenze.

32. 3NF (Cont.) Esempio R = (J, K, L)F = {JK L, L K} due chiavi candidate: JK e JL R è in 3NF JK L JK è superchiaveL K K è contenuto in una chiave candidata la decomposizione in BCNF ha i due schemi (JL) e (LK) verificare il rispetto della dip. funz. JK L richiederebbe un join c’è ridondanza in questo schema altro esempio: Vendite (nomeProduttore, nomeCliente, nomeRappresentante) nomeRappresentante  nomeProduttore nomeProduttore nomeCliente  nomeRappresentante

33. Test per la 3NF Buona notizia: dobbiamo controllare solo le dip. funz. in F, non è necessario controllare tutte le dip. in F+. Utilizziamo la chiusura di attributi per verificare se per una data dip. funz. ,  è superchiave. Se  non è superchiave, dovremmo verificare se ogni attributo in  è contenuto in una chiave candidata di R. Ma: questo test è costoso perché impone di calcolare tutte le chiavi candidate si dimostra infatti che il test di 3NF è un problema NP-hard TUTTAVIA, la decomposizione in 3NF può essere calcolata in tempo polinomiale

34. Algoritmo di decomposizione in 3NF Sia Guna copertura canonica di F; Per ogni parte sinistra X di una DF in G, si definisca uno schema di relazione D con attributi { X U {A1}U {A2} U … U {Ak} } dove X→ A1, …, X→Ak sono le sole dipendenze di G con X come parte sinistra (X e' la chiave di questa relazione). Se nessuno degli schemi di relazione in D contiene una chiave di R, allora si crei un ulteriore schema di relazione D contenente attributi che formano una chiave di R. Si eliminino le relazioni ridondanti (proiezioni di altre relazioni).

35. Algoritmo di decomposizione in 3NF (Cont.) Si dimostra che l’algoritmo visto è tale che è corretto ogni schema Riè in 3NF la decomposizione conserva le dipendenze ed è senza perdite

36. Esempio Schema dato: R (nomeDitta, nomeCliente, nomeImpiegato, numeroUfficio) dipendenze funzionali:nomeImpiegato nomeDitta numeroUfficio nomeCliente nomeDitta nomeImpiegato chiave: {nomeCliente, nomeDitta}

37. Applichiamo l’algoritmo... Il passo 2 inserisce i seguenti schemi nella decomposizione: S (nomeImpiegato, nomeDitta, numeroUfficio) T (nomeCliente, nomeDitta, nomeImpiegato) Dato che T contiene una chiave candidata per R, abbiamoconcluso la decomposizione

38. Comparazione di BCNF e 3NF Per ogni dato schema è sempre possibile calcolare una 3NF senza perdite che conserva le dipendenze Per ogni dato schema è sempre possibile calcolare una BCNF senza perdite potrebbe non preservare tutte le dipendenze

39. Comparazione di BCNF e 3NF (Cont.) Esempio di problemi dovuti alla ridondanza ammessa dalla 3NF: R = (J, K, L)F = {JK L, L K} J L K j1 j2 j3 null l1 l1 l1 l2 k1 k1 k1 k2 Uno schema in 3NF ma non in BCNF comporta: • ripetizione di informazioni (ad es., la coppia di dati l1, k1) • necessita l’impiego di valori nulli (ad es., per rappresentare la correlazione tra l2, e k2 quando non ci siano corrispondenti valori per J).

40. Obiettivi della progettazione Obiettivi del progetto di database relazionali sono: BCNF. Decomposizioni senza perdite. Conservazione delle dipendenze. Se questo non è raggiungibile, possiamo scegliere se rinunciare alla conservazione di (alcune) dipendenze ammettere la ridondanza dovuta a 3NF SQL (“di base”) fornisce un modo diretto per imporre delle generiche dipendenze funzionali nella definizione degli schemi; ma solo le dipendenze dovute a superchiavi. Le altre dip.funz. possono essere imposte tramite l’uso di asserzioni, tuttavia queste sono più costose da valutare. Quindi anche se scegliamo una decomposizione che preserva le dipendenze, non abbiamo un modo diretto/efficiente per imporle/valutarle in SQL.

41. Dipendenze Multivalore Esistono schemi che sono in BCNF ma che appaiono non sufficientemente normalizzati Consideriamo lo schema lezione(corso, docente, libro) tale che (c,d,l) lezione significa che il docente d ha la qualifica per insegnare il corso c, e l è il libro di testo utilizzato in c per ogni corso si memorizzano tutti gli insegnanti che hanno titolo a insegnare quel corso e l’insieme dei libri di quel corso (indipendentemente da quale docente insegna realmente il corso).

42. Non ci sono dip. funz. non banali e lo schema è BCNF MA CI SONO anomalie di inserzione!!! – ad es., se Sara è un nuovo docente di database, si devono inserire le DUE tuple (database, Sara, DB Concepts) (database, Sara, Ullman) Dipendenze Multivalore (Cont.) corso docente libro database database database database database database operating systems operating systems operating systems operating systems Avi Avi Hank Hank Sudarshan Sudarshan Avi Avi Jim Jim DB Concepts Ullman DB Concepts Ullman DB Concepts Ullman OS Concepts Shaw OS Concepts Shaw lezione

43. Sarebbe meglio decomporre comunque in: Dipendenze Multivalore (Cont.) corso docente database database database operating systems operating systems Avi Hank Sudarshan Avi Jim insegna corso libro database database operating systems operating systems DB Concepts Ullman OS Concepts Shaw adotta Vedremo che questo schema è in quarta forma normale (4NF)

44. Dipendenze Multivalore (MVDs) Sia R(X) uno schema e siano X e X. Una dipendenza multivalore  sussiste in R, se in ogni sua istanza r, per ogni coppia di tuple t1 e t2 in r tali che t1[] = t2 [], esistono le tuple t3 e t4 in r tali che: t1[] = t2 [] = t3 [] = t4[] t3[] = t1 [] t3[X – ] = t2[X – ] t4 [] = t2[] t4[X – ] = t1[X – ]

45. MVD (Cont.) Visualmente si può rappresentare questa condizione di esistenza di  in questo modo:

46. Esempio Nell’esempio precedente: corso  docente corso  libro La definizione formale che abbiamo dato esprime la situazione in cui ad un particolare valore di Y (corso) è associato un insieme di valori distinti per Z (docente) e un insieme di valori distinti per W (libro); inoltre questi due insiemi sono indipendenti l’uno dall’altro. Nota: se Y Z allora Y Z

47. Teoria delle MVD La seguente legge deriva dalla definizione di dip. multivalore: Se , allora  Ovvero, ogni dip. funz. è anche una dip. Multivalore La chiusuraD+ di un insieme di dipendenze D è l’insieme di tutte le dipendenze funzionale o multivalore implicate logicamente da D. Possiamo calcolare D+ sfruttando le definizioni di dipendenze funzionale e multivalore. Vedremo ora un insieme di regole di inferenza per le dipendenze multivalore, similmente a quanto visto con gli assiomi di Armstrong.

48. Regole di inferenza per le D.M.V. Questo è un insieme corretto e completo di regole di inferenza per le dip. multivalore (nota che una dip. funzionale è una dip. multivalore). Sia X l’insieme di tutti gli attributi di R, e sianoX : riflessività (DF): se , allora  arricchimento (DF): se , allora  transitività (DF): se , e , allora  complementazione (DMV): se , allora  (X - ) arricchimento (DMV): se  , allora  transitività (DMV): se , , allora  ( - ) replicazione: se , allora  coalescenza: se eed esiste disgiunto da  tale che  allora 

49. Quarta forma normale Uno schema R(X) è in 4NF rispetto ad un insieme di dipendenze (funzionali o multivalore) D se per tutte le dipendenze multivalore in D+ della forma , con X and X, vale almeno una delle seguenti condizioni:  è banale (cioé,  oppure  = X)  è una superchiave per lo schema R(X) Una relazione in 4NF è anche in BCNF

50. Algoritmo di Decomposizione in 4NF result: = {R};done := false;calcola D+; while (not done) if (esiste uno schema Ri in result che non sia in 4NF) then begin sia  una delle dip. multivalore di D+ non banali che causa la violazione della 4NF su Ri. Siaresult := (result - Ri)  (Ri- )  (, ); end else done:= true; Nota: ogni Riè in 4NF e la decomposizione è senza perdite