1 / 54

Databases I Normaliseren

definitieve versie 2002. Databases I Normaliseren. Martin Caminada / Wiebren de Jonge Vrije Universiteit, Amsterdam. Overzicht. Reeds behandeld: welke vormen van redundantie zijn er en hoe herken je ze? Te behandelen: hoe moet je redundantie vermijden? wat zijn de valkuilen?.

curry
Download Presentation

Databases I Normaliseren

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. definitieve versie 2002 Databases INormaliseren Martin Caminada / Wiebren de JongeVrije Universiteit, Amsterdam

  2. Overzicht • Reeds behandeld: • welke vormen van redundantie zijn eren hoe herken je ze? • Te behandelen: • hoe moet je redundantie vermijden? • wat zijn de valkuilen?

  3. Voorbeeld Normaliseren (1/2) F = { sofinr  naam, sofinr  adres, sofinr  gdatum }Key: {sofinr, vmiddel}FD’s in F zijn allen r_partiëel, dus 1NF (en geen 2NF)

  4. Introductie Normaliseren (5/5) F = {sofinr  naam, sofinr  adres, sofinr  gdatum} Key R1: {sofinr} Key R2: {sofinr, vmiddel}F1 = F F2 = R1 is in BCNF R2 is in BCNF (dus het hele schema is in BCNF)

  5. Spurious tupels en dangling tupels • Spurious tupelszijn een serieus probleem:Het is niet meer mogelijk om vanuit de decompositie de oorspronkelijke relatie te reconstrueren • Dangling tupels zijn geen probleem:Het betekent simpelweg dat in de decompositieinformatie kan worden opgeslagen die bij het joinen verloren kan gaan. M.a.w.: de decompositie biedt je extra mogelijkhedenom informatie op te slaan.

  6. Lossless join eigenschap • {R1, R2, …, Rn} is een decompositie van een relationeel schema R indien: ni=1 Ri = R (Dus indien alle Ri’s samen de attributen van R bevatten) • Definitie van “lossless”:Zij R een relationeel schema, zij C een verzameling constraints en zij {R1, R2, …, Rn} een decompositie van R.{R1, R2, …, Rn} heeft de lossless join eigenschap m.b.t. C d.e.s.d.a.voor iedere extensie r van R die aan C voldoet geldt: r = R1(r) join R2(r) join … join Rn(r)

  7. Eigenschappen “join van projecties” • “join van projecties” kan soms meer tupels teruggeven, maar nooit minder • als je de “join van projecties” opnieuw projecteert,krijg je hetzelfde resultaat als direct na het projecteren van de oorspronkelijke relatie • meerdere keren uitvoeren van “join van projecties” levert precies hetzelfde resultaat op als één keer “join van projecties”(S1, P1, J1) (S2, P1, J2) (S1, P1) (P1, J1)(S2, P1) (P1, J2) (S1, P1, J1) (S2, P1, J2)(S1, P1, J2) (S2, P1, J1)

  8. Controleren lossless-join (als 2 proj.) • Zij D = {R1, R2} een decompositie van R en zij F een verzameling FD’s. D is een lossless join decompositie m.b.t. F d.e.s.d.a.1) (R1 R2)  (R1 - R2)  F+, of2) (R1 R2)  (R2 - R1)  F+ • merk op dat: (R1 R2)  (R1 - R2)  F+  (R1 R2)  R1  F+“”: augmentatie met R1 R2 (voeg links en rechts R1 R2 toe) “”: decompositie-regel (je laat attributen weg rechts van de “”) • dus, D is een lossless join decompositie m.b.t. F d.e.s.d.a.1) (R1 R2)  R1  F+, of2) (R1 R2)  R2  F+

  9. Voorbeeld Database DPD_EMP E#DPD_N REL EMP_N BDATE D# E2 Barbara wife Joe 1968-04-04 D1 E3 Mary daughter Jack 1969-09-03 D1 E3 Sue wife Jack 1969-09-03 D1 E4 Tom son Will 1971-03-21 D2 E4 Mary wife Will 1971-03-21 D2 DEPENDENT EMPLOYEE E#DPD_N RELE# EMP_N BDATED# E2 Barbara wife E2 Joe 1968-04-04 D1 E3 Mary daughter E3 Jack 1969-09-03 D1 E3 Sue wife E4 Will 1971-03-21 D2 E4 Tom son E4 Mary wife

  10. Vb. lossless-join (splitsing in 2 projecties) • dus, D is een lossless join decompositie m.b.t. F d.e.s.d.a.1) (R1 R2)  R1  F+, of2) (R1 R2)  R2  F+ • DPD_EMP(E#, DPD_N, REL, EMP_N, BDATE, D#)met F = { E#, DPD_N  REL, E#  EMP_N, E#  BDATE, E#  D# }DEPENDENT(E#, DPD_N, REL)EMPLOYEE(E#, EMP_N, BDATE, D#)EMPLOYEE  DEPENDENT = {E#} en E#  E#, EMP_N, BDATE, D# (dus lossless)

  11. Vb. lossless-join (met n projecties) (1/3) • DPD_EMP_DPM(E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET)met F = { E#, DPD_N  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D#, DPM_N  BUDGET }DEPENDENT(E#, DPD_N, REL)EMPLOYEE(E#, EMP_N, BDATE, D#)DEPARTMENT(D#, DPM_N, BUDGET)

  12. Vb. lossless-join (met n projecties) (2/3) DPD_EMP_DPM(E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET)met F = { E#, DPD_N  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D#, DPM_N  BUDGET } E# DPD_N REL EMP_N BDATE D# DPM_N BUDGET DEPENDENT a1 a2 a3 b14 b15 b16 b17 b18 EMPLOYEE a1 b22 b23 a4 a5 a6 b27 b28 DEPARTMENT b31 b32 b33 b34 b35 a6 a7 a8

  13. Vb. lossless-join (met n projecties) (3/3) DPD_EMP_DPM(E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET)met F = { E#, DPD_N  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D#, DPM_N  BUDGET } E# DPD_N REL EMP_N BDATE D# DPM_N BUDGET DEPENDENT a1 a2 a3 a4 a5 a6 a7 a8 EMPLOYEE a1 b22 b23 a4 a5 a6 a7 a8 DEPARTMENT b31 b32 b33 b34 b35 a6 a7 a8

  14. Algoritme lossless-join Heeft een decompositie R1, …, Rk van R de lossless-join eigenschap m.b.t. een verzameling FD’s F? • maak een tabel met k rijen (voor iedere R1, …, Rk een rij)en n kolommen (voor ieder attribuut A1, …, An een kolom) • zet in rij i en kolom j het symbool aj (als Aj  Ri) of het symbool bij (als Aj Ri). doe dit voor iedere i en j. • “verwerk” nu telkens opnieuw alle FD’s X  Y  F totdat • ofwel één van de rijen gelijk is geworden aan a1, …, an • ofwel de tabel niet meer gewijzigd kan worden • “verwerken” van een FD X  Y:telkens als een aantal rijen ‘dezelfde waarden voor X’ hebben, geef ze dan ook ‘dezelfde waarden voor Y’ (en kies hiervoor indien mogelijk een aj) let op: telkens als je een symbool wijzigt, moet je alleinstanties van dat symbool wijzigen (ook in alle andere rijen). • decompositie is lossless  er is uiteindelijk een rij gelijk aan a1…an

  15. Voorbeeld lossless join algoritme (1/4) R = {A,B,C,D,E} = ABCDE R1=AD R2=AB R3=BE R4=CDE R5=AE F = {AC, BC, CD, DEC, CEA} A B C D E R1a1 b12 b13 a4 b15 R2 a1 a2 b23 b24 b25 R3 b31 a2 b33 b34 a5 R4 b41 b42 a3 a4 a5 R5 a1 b52 b53 b54 a5

  16. Voorbeeld lossless join algoritme (2/4) R1=AD R2=AB R3=BE R4=CDE R5=AE F = {AC, BC, CD, DEC, CEA} tussenresultaat na het verwerken van de eerste drie FD’s: A B C D E R1a1 b12 b13 a4 b15 R2 a1 a2 b13 a4 b25 R3 b31 a2 b13 a4 a5 R4 b41 b42 a3 a4 a5 R5 a1 b52 b13 a4 a5

  17. Voorbeeld lossless join algoritme (3/4) R1=AD R2=AB R3=BE R4=CDE R5=AE F = {AC, BC, CD, DEC, CEA} eindresultaat: A B C D E R1a1 b12 a3 a4 b15 R2 a1 a2 a3 a4 b25 R3 a1 a2 a3 a4 a5 R4 a1 b42 a3 a4 a5 R5 a1 b52 a3 a4 a5

  18. Voorbeeld lossless join algoritme (4/4) opmerking We hebben de FD’s “toevallig” in een gunstige volgorde verwerkt zodat we na één ronde al klaar zijn.In het algemeen moet de gehele verzameling van FD’s meerdere malen doorlopen worden.(net zolang totdat er een rij a’s uitkomt, of er niets meer in de tabel veranderd kan worden)

  19. Vb. 1: Dependency Preserving ? (1/3) R = {Stad, Straat, Huisnr, Postcode, Vraagprijs} F = { {Stad, Straat, Huisnr}  Postcode, {Stad, Straat, Huisnr}  Vraagprijs, {Postcode, Huisnr}  Vraagprijs, Postcode  Stad, Postcode  Straat} Stad Straat Huisnr Postcode Vraagprijs Amsterdam Westerstr 31 1015 MK 500 000 Den Haag Laan 237 2512 DT 400 000 Den Haag Hoefkade 30 2526 CA 150 000 Appingedam Broerstr 8 9901 EK 200 000 Appingedam Broerstr 12 9901 EK 225 000 Keys: { {Stad, Straat, Huisnr}, {Postcode, Huisnr} }

  20. Vb. 1: Dependency Preserving ? (2/3) R = {Stad, Straat, Huisnr, Postcode, Vraagprijs} F = { {Stad, Straat, Huisnr}  Postcode, {Stad, Straat, Huisnr}  Vraagprijs, {Postcode, Huisnr}  Vraagprijs, Postcode  Stad, Postcode  Straat} R1 R2 PostcodeHuisnr Vraagprijs Postcode Stad Straat 1015 MK 31 500 000 1015 MK Amsterdam Westerstr 1212 DT 237 400 000 1212 DT Den Haag Laan 2526 CA 30 150 000 2526 CA Den Haag Hoefkade 9901 EK 8 200 000 9901 EK Appingedam Broerstaat 9901 EK 12 225 000

  21. Vb. 1: Dependency Preserving ? (3/3) R = {Stad, Straat, Huisnr, Postcode, Vraagprijs} F = { {Stad, Straat, Huisnr}  Postcode, {Stad, Straat, Huisnr}  Vraagprijs, {Postcode, Huisnr}  Vraagprijs, Postcode  Stad, Postcode  Straat} R1 = {Postcode, Huisnr, Vraagprijs}R2 = {Postcode, Stad, Straat} er geldt: R1  R2 = Postcode en Postcode  {Stad, Straat}  F+dus de decompositie heeft de lossless-join eigenschap echter, de volgende FD’s zijn beide “tussen wal en schip gevallen”: • {Stad, Straat, Huisnr}  Postcode • {Stad, Straat, Huisnr}  Vraagprijs (dus join nodig om ze te checken, want niet afleidbaar uit rest; zie vb.2)

  22. Vb. 2: Dependency Preserving ? R = {A, B, C} = ABC (keys: {A} = A) F = {A  B, (r_sleutel) A  C, (r_sleutel) B  C} (r_transitief) R1 = AB (= {A, B}) R2 = BC (= {B, C}) er geldt: R1R2 = B en BBC  F+ (dus lossless-join eigenschap) de FD AC lijkt “tussen wal en schip te vallen”, doch: • AC volgt uit AB en BC • AB kan efficiënt in R1 gecontroleerd worden • BC kan efficiënt in R2 gecontroleerd worden Dus: als eenmaal AB en BC gecontroleerd zijn (en OK bevonden) dan is automatisch ook AC gecontroleerd/OK.

  23. Dependency Preserving • Z(F) = { X  Y  F+ | XY  Z}(projectie van F op een verzameling attributen Z) • let op: Z(F) = Z(F+) • een decompositie {R1, R2, … Rk}is dependency preservingd.e.s.d.a. F  ( ki=1 Ri(F) )+(Eigenlijk: d.e.s.d.a. F+ = ( ki=1 Ri(F) )+Echter: F+ = G+  F  G+ èn G  F+ en de tweede eis G  F+, oftewel: (ki=1 Ri(F)) F+, volgt in dit geval uit de definitie van projectie. )

  24. Vb. bij definitie Dependency Preserving (1/3) F = {AB, AC, BC} R1 = {A,B} = AB R2 = {B,C} = BC • als deze decompositie is dependency preserving is,dan zou moeten gelden: F  (R1(F)  R2(F) )+dus er zou moeten gelden: F  (R1(F+)  R2(F+) )+ • eerst maar eens F+ uitrekenen...

  25. Vb. bij definitie Dependency Preserving (2/3) F+ = {AA, ABA, ACA, ABCA AB, BB, ABB, BCB, ACB, ABCB AC, BC, CC, ABC, BCC, ACC, ABCC AAB, ABAB, ACAB, ABCAB ABC, BBC, ABBC, BCBC, ACBC, ABCBC AAC, ABAC, ACAC, ABCAC AABC, ABABC, ACABC, ABCABC}

  26. Vb. bij definitie Dependency Preserving (3/3) F = {AB, AC, BC} R1 = {A,B} = AB R2 = {B,C} = BC • als deze decompositie is dependency preserving is,dan zou moeten gelden: F  (R1(F)  R2(F) )+ • R1(F) = {AA, AB, AAB, BB, ABA, ABB, ABAB} • R2(F) = {BB, BC, BBC, CC, BCB, BCC, BCBC} • dus er zou moeten gelden • A B  (R1(F)  R2(F) )+ (klopt, want A B  R1(F) ) • B C  (R1(F)  R2(F) )+ (klopt, want B C  R2(F) ) • A C  (R1(F)  R2(F) )+ (klopt, want uit A B  R1(F) en BC  R2(F) volgt AC  (R1(F)  R2(F) )+ ) • dus: deze decompositie is dependency preserving

  27. Nog een vb. bij def Dependency Preserving F = { {Stad, Straat, Huisnr}  Postcode, {Stad, Straat, Huisnr}  Vraagprijs, {Postcode, Huisnr}  Vraagprijs, Postcode  Stad, Postcode  Straat} R1 = {Postcode, Huisnr, Vraagprijs}R2 = {Postcode, Stad, Straat} • als deze decompositie is dependency preserving is,dan zou moeten gelden: F  (R1(F)  R2(F) )+echter, er geldt o.a.: {Stad, Straat, Huisnr}  Vraagprijs  (R1(F)  R2(F) )+ • dus: deze decompositie is niet dependency preserving

  28. Normaalvormen een database-ontwerp (db-schema) R1, R2, …, Rn is in ?NF t.o.v. een verzameling FD’s F d.e.s.d.a. iedere relatie Ri in ?NF is t.o.v. Ri(F)

  29. Gewenste eigenschappen database-ontwerp • hoge normaalvorm, want anders potentiële redundantie; • lossless-join eigenschap, want anders verlies van info; • dependency preserving, want anders heeft DBMS veel werk, i.e. joins nodig, om de opgelegde constraints (zoals FD’s) af te dwingen. helaas… • BCNF + dependency preserving + lossless: soms onmogelijk • BCNF + dependency preserving: soms onmogelijk • BCNF + lossless: altijd mogelijk • 3NF + dependency preserving + lossless: altijd mogelijk

  30. Tussenstand • We weten nu hoe we in een relatie-schema en/of db-schema potentiële redundantie kunnen herkennen (normaalvorm bepalen) • Van een decompositie kunnen we nu beoordelen: • hoe goed in de decompositie redundantiewordt voorkomen (via normaalvorm bepalen) • of de decompositie dezelfde informatie kan bevatten alsde oorspronkelijke tabel (via “lossless join” algoritme) • of de FD’s ( {“gewenste constraints”}) in de decompositieop een efficiënte manier kunnen worden gecontroleerd (via “dependency preserving” algoritme; wordt niet behandeld) Hoe vind je een decompositie die hieraan voldoet?(zonder alle mogelijke decomposities te testen)

  31. Twee decompositie-algoritmes • 3NF decompositie algoritme (lossless + dependency preserving) • BCNF decompositie algoritme(lossless, doch niet noodzakelijk dependency preserving) In dit college gaan we uitsluitend het 3NF algoritme behandelen! (BCNF algoritme valt dit jaar buiten de tentamenstof, o.a. i.v.m. complexiteit van projecteren van F+en van testen “dependency preserving” eigenschap)

  32. 3NF decompositie algoritme (lossless + d.p.) Gegeven een relatie-schema R en een verzameling FD’s F: 1) Bepaal een minimal cover van F en noem die m.c. G. 2) Als er een FD in G is die alle attributen bevat (dus: een X  A met X  {A} = R) dan is R al in 3NF.Zo niet, splits dan R op in alle relatie-schema’s XiAi die corresponderen met een afhankelijkheid Xi  Ai in G 3) Telkens als Xi = Xj mogen we de twee bijbehorende schema’s (XiAi en XjAj) samenvoegen tot XiAiAj. 4) Om de lossless-join eigenschap te verzekeren voegen we één relatie-schema X toe, waarbij X een sleutel moet zijn van R. 5) Verwijder eventueel een aantal overbodige schema’s(± schema’s die bevat zijn in een ander schema).

  33. Voorbeeld 3NF decompositie algoritme (1/5) • DPD_EMP_DPM = {E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET}met F = { {E#, DPD_N}  REL, E#  {EMP_N, BDATE, D#}, D#  {BUDGET, DPM_N}, DPM_N  {D#, BUDGET} } • Stap 1: Bepaal een minimal cover G = { {E#, DPD_N}  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D# }

  34. Voorbeeld 3NF decompositie algoritme (2/5) • DPD_EMP_DPM = {E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET} G = { {E#, DPD_N}  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D#, } • Stap 2: Splitsen in losse relatieschema’s (voor iedere FD in G een relatieschema) {E#, DPD_N, REL}, {D#, DPM_N}, {E#, EMP_N}, {D#, BUDGET}, {E#, BDATE}, {DPM_N, D#}, {E#, D#}

  35. Voorbeeld 3NF decompositie algoritme (3/5) • DPD_EMP_DPM = {E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET} {E#, DPD_N, REL}, {D#, DPM_N}, {E#, EMP_N}, {D#, BUDGET}, {E#, BDATE}, {DPM_N, D#}, {E#, D#} • Stap 3: Samenvoegen van schema’s {E#, DPD_N, REL}, {E#, EMP_N, BDATE, D#}, {D#, DPM_N, BUDGET}, {DPM_N, D#}

  36. Voorbeeld 3NF decompositie algoritme (4/5) • DPD_EMP_DPM = {E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET}G = { E#, DPD_N  REL, D#  DPM_N, E#  EMP_N, D#  BUDGET, E#  BDATE, DPM_N  D#, E#  D# } • Stap 4: Relatie-schema voor een sleutel toevoegen {E#, DPD_N, REL} {E#, EMP_N, BDATE, D#} {D#, DPM_N, BUDGET} {DPM_N, D#} {E#, DPD_N}

  37. Voorbeeld 3NF decompositie algoritme (5/5) • DPD_EMP_DPM = {E#, DPD_N, REL, EMP_N, BDATE, D#, DPM_N, BUDGET} {E#, DPD_N, REL}, {E#, EMP_N, BDATE, D#}, {D#, DPM_N, BUDGET}, {DPM_N, D#}, {E#, DPD_N} • Stap 5: Verwijderen overbodige relatie-schema’s (± schema’s die in een ander relatie-schema bevat zijn) {E#, DPD_N, REL} (=dependent) {E#, EMP_N, BDATE, D#} (=employee) {D#, DPM_N, BUDGET} (=department)

  38. Hogere normaalvormen: 4NF (1/3) voorbeeld: dating-bureau houdt van iedere zoekende de volgende informatie bij: naam, hobby’s, huisdieren • personen kunnen meerdere hobby's en huisdieren hebben • er is geen verband tussen hobby’s en huisdieren ZOEKENDE_HOBBY ZOEKENDE_HUISDIER NAAM HOBBYNAAM HUISDIER Truus bridgen Truus kat Truus puzzelen Truus goudvis Teun sjoelen Teun Hond Teun Kanarie

  39. Hogere normaalvormen: 4NF (2/3) Stel, je stopt al deze informatie in slechts één tabel.Kun je dan d.m.v. FD’s vooraf detecteren dat je hiermeeredundantie kunt introduceren? (antwoord: nee, want F = ) ZOEKENDE NAAMHOBBYHUISDIER Truus bridgen kat Truus bridgen goudvis Truus puzzelen kat Truus puzzelengoudvis Teun sjoelen hond Teun sjoelen kanarie

  40. Hogere normaalvormen: 4NF (3/3) ZOEKENDE NAAMHOBBYHUISDIER Truus bridgen kat Truus bridgen goudvis Truus puzzelen kat Truus puzzelengoudvis Teun sjoelen hond Teun sjoelen kanarie Multi-valued dependencies (MVD’s): 1) naam --->> hobby 2) naam --->> huisdier 4NF: gebaseerd op MVD’s (ZOEKENDE wel in BCNF, doch niet in 4NF)

  41. Hogere normaalvormen: 5NF (=PJNF) • Join Dependency (JD): een constraint die inhoudt dat de relatie is gelijk aan de join van een aantal projecties (voor iedere extensie) • N.B.: {FD’s}  {MVD’s}  {JD’s} • 5NF (=PJNF): gebaseerd op èchte join dependencies • voorbeeld join dependency (SPJ voorbeeld): “Als een supplier iets levert aan een bepaald project, dan levert hij aan dat project ook alles wat hij kan leveren en wat bij dat project gebruikt wordt.”Als deze join dependency geldt in het SPJ-voorbeelddan is SPJ niet in 5NF. De decompositie {SP, PJ, JS} is dan wel in 5NF èn lossless.

More Related