1 / 52

Databases I Functionele Afhankelijkheden en Normaalvormen

definitieve versie 2002. Databases I Functionele Afhankelijkheden en Normaalvormen. Martin Caminada / Wiebren de Jonge Vrije Universiteit, Amsterdam. De eerstvolgende colleges. meer over relationeel database design: functionele afhankelijkheden normaalvormen

teigra
Download Presentation

Databases I Functionele Afhankelijkheden en Normaalvormen

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 IFunctionele Afhankelijkhedenen Normaalvormen Martin Caminada / Wiebren de JongeVrije Universiteit, Amsterdam

  2. De eerstvolgende colleges • meer over relationeel database design: • functionele afhankelijkheden • normaalvormen • dependency preserving opsplitsing • lossless join eigenschap • diverse praktische aspecten (H16) database design is meer dan alleen “naïeve” (E)ER-mapping • een kijkje onder de motorkap van een DBMS (opslagstructuren, query optimizing, transactions, recovery, …) • non-relationele data-modellen (hiërarchisch)

  3. Functionele afhankelijkheden • gegeven • een relatieschema R met attributen A1, A2, …, An • X  {A1, A2, …, An} • Y  {A1, A2, …, An} • Y is functioneel afhankelijk van X (notatie: X  Y) indien de volgende constraint geldt: t1,t2 (t1[X] = t2[X]  t1[Y] = t2[Y])dus:als twee tupels dezelfde X-waarden hebbendan moeten ze ook altijd dezelfde Y-waarden hebben.N.B. “dezelfde X-waarden” betekent “dezelfde combinatie van waarden voor de attributen in X”

  4. Voorbeeld functionele afhankelijkheden (1/2) Gegeven de volgende relatie: R A B C a e i a f i b g k c g k gelden de volgende functionele afhankelijkheden? • A  B • B  C

  5. Voorbeeld functionele afhankelijkheden (2/2) Gelden de volgende relatie: EMPLOYEE ENR NAME BDATEDNR E1 John 1964-08-28 D1 E2 Joe 1968-04-04 D1 E3 Jack 1969-09-03 D1 E4 Will 1971-03-21 D2 E5 Bridget 1972-01-22 D2 Gelden de volgende functionele afhankelijkheden? • ENR  NAME • DNR  NAME • BDATE  NAME

  6. Functionele afhankelijkheden • Een functionele afhankelijkheid is een constraint. • Het voldoen aan een functionele afhankelijkheid is afhankelijk van de betekenis van de gegevens. • Het is niet genoeg als een functionele afhankelijkheid in één extensie opgaat. Vereist is dat hij in elke extensie opgaat! • Een functionele afhankelijkheid is daarmee een eigenschap van de intensie.

  7. Keys • notatie: • F |= X  Y (X  Y volgt uit F) • F+ def { X  Y | F |= X  Y} • gegeven: • een relatieschema R met attributen A1, A2, …, An • een verzameling functionele afhankelijkheden F • een verzameling X  {A1, A2, …, An} X is een sleutel van R indien: 1) X  A1, A2, …, An  F+ èn 2) YX: (YX  Y  A1, A2, …, An  F+)

  8. Keys • begrippen: • superkey • key, candidate key • primary key • alternate key, alternative key, secondary key (deze laatste benaming liever niet !)

  9. Armstrong’s afleidingsregels Gegeven: de verzameling U van alle attributen in relatie-schema R • reflexiviteit:als Y  X  U dan X  Y • augmentatie:als X  Y en Z  U dan XZ  YZ • transitiviteit:als X  Y en Y  Z dan X  Z Bewijs transitiviteit: Stel, t1[X] = t2[X] voor twee willekeurige tupels t1 en t2. Uit X  Y volgt dan dat t1[Y] = t2[Y]. Uit Y  Z volgt dan dat t1[Z] = t2[Z]. Dus, als t1[X] = t2[X] dan ook t1[Z] = t2[Z] (d.w.z. X  Z) Armstrongs afleidingsregels zijn gezond en volledig.

  10. Overige afleidingsregels • decompositie-regel:als X  Y en Z  Y dan X  Z • verenigings-regel:als X  Y en X  Z dan X  YZ • pseudo-transitiviteits-regelals X  Y en WY  Z dan WX  Z Bewijs verenigings-regel m.b.v. Armstrong’s afleidingsregels: 1) X  Y (gegeven) 2) X  Z (gegeven) 3) X  YX (augmentatie van 1 met X; augmentatie met Z geeft XZ  YZ, dus met X geeft X  YX) 4) XY  ZY (augmentatie van 2 met Y) 5) X  YZ (transitiviteit op 3 en 4; YX = XY en ZY=YZ)

  11. Functionele afhankelijkheden: |- versus |= • notatie: • F |- X  Y betekent:X  Y is m.b.v. Armstrong’s afleidingsregels afleidbaar uit F • X+Fdef { A | F |- X  A} • afleidbaar uit definitie van X+F: F |- X  Y  Y  XF+ • omdat Armstrong’s afleidingsregels gezond en volledig zijn (m.a.w. |-  |=) geldt ook: • { A | F |- X  A } (def X+) = { A | F |= X  A } • { X  Y | F |= X  Y} (def F+) = { X  Y | F |- X  Y} • F |= X  Y  Y  XF+

  12. Drie keer hetzelfde • X  Y  F+ • F |- X  Y (volgt uit “|-  |=” en uit definitie van F+) • Y  X+F (volgt uit hulpstelling afgeleid uit def X+F) N.B.: F+ def { X  Y | F |= X  Y} X+Fdef { A | F |- X  A}

  13. F  G (1/2) • F def G indien F+ = G+ • gegeven • R met attributen A, B en C • F = {A  B, B  C, C  A} • G = {A  C, B  A, C  B} geldt hier F  G ? Eerste aanpak: bereken F+ en G+. F+ = {AA, BA, CA, ABA, BCA, ACA, ABCA AB, BB, C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, BAB, CAB, ABAB, BCAB, ACAB, ABCAB ABC, BBC, CBC, ABBC, BCBC, ACBC, ABCBC AAC, BAC, CAC, ABAC, BCAC, ACAC, ABCAC AABC,BABC, CABC, ABABC, BCABC, ACABC, ABCABC} Hier zit dus erg veel werk aan vast...

  14. F  G (2/2) • Het kan ook eenvoudiger: • merk op: F+= G+  F+  G+  G+  F+ • hulpstelling: G+  F+  G  F+bewijs:“”: G+  F+  G  F+ (triviaal)“”: G  F+  G+  F+ (want G  H  G+  H+, dus ook G  F+  G+  F++, en F++ = F+) • G  F+ kan getest worden zonder F+ uit te rekenendoor voor iedere XY  G te testen of Y  X+F .Immers, omdat Y  X+F  F |= X  Y  XY  F+testen we dan voor iedere XY  G of XY  F+ .

  15. Voorbeeld B+ • gegeven F = {A  B, B  C, C  A} • wat is hier B+ ?B+ = BCA (= ABC)Algoritme 14.1 boek:X+ := X;repeat oldX+ := X+; for each functional dependency Y  Z in F do if Y  X+ then X+ := X+  Z;until (X+ = oldX+)

  16. Voorbeeld F  G • gegeven • R met attributen A, B en C • F = {A  B, B  C, C  A} • G = {A  C, B  A, C  B} geldt hier F  G ? geldt F  G+ ?- A  B: A+G = ABC, dus B  A+G- B  C: B+G = ABC, dus C  B+G- C  A: C+G = ABC, dus A  C+G geldt G  F+ ?- A  C: A+F = ABC, dus C  A+F- B  A: B+F = ABC, dus A  B+F- C  B: C+F = ABC, dus B  C+F Dus F  G+  G  F+ dus F  G

  17. Minimal Cover • Een verzameling f.a.’s F heet minimaal als: • de rechterkant van iedere f.a.uit slechts één attribuut bestaat • er geen enkele f.a. is met een overbodig attribuut aan de linkerkant • er geen enkele overbodige f.a. is • een f.a. X  A heeft een “overbodig” attribuut B (B  X) aan de linkerkant indien je A ook vanuit de overige attributen kan afleiden (dus als A  Z+F met Z = X-B) • een f.a. X  A is “overbodig” in F wanneer je vanuit X nog steeds A kunt afleiden, zonder gebruik te maken van X  A(dus als A  X+H met H = F-{X  A})

  18. Algoritme 14.2 boek Finding a minimal cover G for F 1. set G := F 2. replace each functional dependency X  {A1, A2, …, An} in Gby the n functional dependencies X  A1, X  A2, …, X  An 3. for each functional dependency X  A in G for each attribute B that is an element of X if G-{XA}{X-B A}  G then replace X  A with (X - {B})  A in G 4. for each remaining functional dependency X  A in G if G-{XA}  G then remove X  A from G

  19. Opmerking 1: Algoritme 14.2 boek Vraag: wanneer geldt: G-{XA}{X-B A}  G ? Antwoord: d.e.s.d.a. G-{XA}{X-B A}  G+ en G (G-{XA}{X-B A})+ G-{XA}{X-B A}  G+ d.e.s.d.a. X-BA  G+ dus als G |- X-B  A G (G-{XA}{X-B A})+ d.e.s.d.a.XA  (G-{XA}{X-BA})+dus als G-{XA}{X-B A} |- X  Aen vanuit X-B  A kun je met augmentatie afleiden dat X  AB en dus ook X  A dus: G-{XA}{X-BA}  G  G |- X-B  A

  20. Opmerking 2: Algoritme 14.2 boek Vraag: wanneer geldt: G-{XA}  G ? Antwoord: d.e.s.d.a. G-{XA}  G+ en G (G-{XA})+ G-{XA}  G+dit is hoe dan ook waar, want G-{XA}  G dus ook G-{XA}  G+ G (G-{XA})+ d.e.s.d.a.X A  (G-{XA})+dus als G-{X  A} |- X  A dus: G-{XA}  G  G-{X  A} |- X  A

  21. Algoritme 14.2 (implementatie I) Finding a minimal cover G for F 1. set G := F 2. replace each functional dependency X  {A1, A2, …, An} in Gby the n functional dependencies X  A1, X  A2, …, X  An 3. for each functional dependency X  A in G for each attribute B that is an element of X if G |- (X-B)  Athen replace X  A with (X - B)  A in G 4. for each remaining functional dependency X  A in G if G - {XA} |- X  A then remove X  A from G voordeel: valt vaak snel “met het blote oog” in te zien

  22. Voorbeeld Minimal Cover (impl. I) (1/5) • Gegeven een relatie schema R = ABCDE met F = { AB  DE, ABC  BC, A  C, A  D, B  D, D  E, E  C }Bepaal een minimal cover van F.

  23. Voorbeeld Minimal Cover (impl. I) (2/5) Eerst stap 1: alleen enkelvoudige attributen aan de rechterkant. AB  DE (opsplitsen in ABD en ABE) ABC  BC (opsplitsen in ABCB en ABCC) A  C A  D B  D D  E E  C

  24. Voorbeeld Minimal Cover (impl. I) (3/5) Nu stap 2: geen overbodige attributen aan de linkerkant. 1) AB  D AD (6) dus F:=F-{ABD}{AD}2) AB  E uit (7) en (8) volgt BE (transitiviteit) dus F:=F-{AB E}{BE}3) ABC  B BB (reflexiviteit) dus F:=F-{ABCB}{BB}4) ABC  C CC (reflexiviteit) dus F:=F-{ABCC}{CC}5) A  C6) A  D7) B  D8) D  E9) E  C

  25. Voorbeeld Minimal Cover (impl. I) (4/5) Tot slot stap 3: geen overbodige afhankelijkheden 1) A  D2) B  E uit (6) en (7) volgt B E (transitiviteit) dus F:=F-{BE}3) B  B B B (reflexiviteit) dus F:=F-{BB} 4) C  C C C (reflexiviteit) dus F:=F-{CC} 5) A  C uit (1) en (7) volgt A E (transitiviteit) uit A E en (8) volgt A  E (transitiviteit) dus F:=F-{AC}6) B  D7) D  E8) E  C

  26. Voorbeeld Minimal Cover (impl. I) (5/5) • Resulterende minimal cover (eindantwoord): A  D B  D D  E E  C

  27. Algoritme 14.2 (implementatie II) Finding a minimal cover G for F 1. set G := F 2. replace each functional dependency X  {A1, A2, …, An} in Gby the n functional dependencies X  A1, X  A2, …, X  An 3. for each functional dependency X  A in G for each attribute B that is an element of X if A  (X-B)+Gthen replace X  A with (X - B)  A in G 4. for each remaining functional dependency X  A in G if AX+G-{xA} then remove X  A from G voordeel: valt relatief makkelijk te programmeren

  28. Voorbeeld Minimal Cover (impl. II) (1/5) • Gegeven een relatie schema R = ABCDE met F = { AB  DE, ABC  BC, A  C, A  D, B  D, D  E, E  C }Bepaal een minimal cover van F.

  29. Voorbeeld Minimal Cover (impl. II) (2/5) AB  DE (opsplitsen in ABD en ABE) ABC  BC (opsplitsen in ABCB en ABCC) A  C A  D B  D D  E E  C Eerst stap 1: alleen enkelvoudige attributen aan de rechterkant.

  30. Voorbeeld Minimal Cover (impl II) (3/5) AB  D (A+F = ACDE dus DA+F) F:=F-{ABD}{AD}AB  E (B+F = BDEC dus EB+F) F:=F-{AB E}{BE}ABC  B (B+F = BDEC dus BB+F) F:=F-{ABCB}{BB}ABC  C (C+F = C dus CC+F) F:=F-{ABCC}{CC} A  C A  D B  D D  E E  C Nu stap 2:geen overbodige attributen aan de linkerkant.

  31. Voorbeeld Minimal Cover (impl II) (4/5) A  D (A+F-{AD} = AC dus D A+F-{AD} )B  E (B+F-{BE} = BDEC dus E B+F-{BE} ) F:=F-{BE}B  B (B+F-{BB} = BDEC dus B B+F-{BB} ) F:=F-{BB} C  C (C+F-{CC} = C dus C C+F-{CC} ) F:=F-{CC} A  C (A+F-{AC} = ADEC dus C A+F-{AC} ) F:=F-{AC}B  D (B+F-{BD} = B dus D B+F-{BD} )D  E (D+F-{DE} = D dus E D+F-{DE} )E  C (E+F-{EC} = E dus C E+F-{EC} ) Tot slot stap 3:geen overbodige afhankelijkheden

  32. Voorbeeld Minimal Cover (impl. II) (5/5) • Resulterende minimal cover (eindantwoord): A  D B  D D  E E  C

  33. Ander voorbeeld minimal cover F={ A B, F={ A  C, B C, B  A, C A, C  B, A C, A  B, B A, B  C C B } C  A } De resulterende minimal cover kan afhangen van de volgorde waarin je de regels behandelt. Bij één en dezelfde F kan dus meer dan één minimal cover horen.

  34. Nog enkele definities • een f.a. X  Y  F+ is triviaal d.e.s.d.a. Y  X • een f.a. X  Y  F+ is volledig d.e.s.d.a. AX: (X-A)  Y  F+(dus geen overbodige attributen aan de linkerkant) • relevante f.a.: een niet-triviale, volledige f.a. met aan de rechterkant slechts één attribuut • sleutelafh.: een f.a. X  A waarbij X een sleutel is • r_sleutelafh: relevante sleutelafh

  35. Introductie Normaliseren (1/5)

  36. Introductie Normaliseren (2/5)

  37. Introductie Normaliseren (3/5)

  38. Introductie Normaliseren (4/5)

  39. Introductie Normaliseren (5/5)

  40. Redundantie Problemen voortkomend uit redundantie: • potentiële inconsistentie (oplossing: f.a.’s afdwingen) • onnodig beslag op disk-ruimte • update anomalieën Schema’s met (potentiële) redundantie: • hoe herken je ze? • normaalvormen • hoe los je het op? • splitsen • wat zijn de valkuilen? • lossless join • dependency preserving

  41. Normaalvormen: 1NF R = {Sofi, Naam, Adres, Gdatum, {Vervoermiddelen}} Sofi Naam Adres Gdatum Vervoersmiddelen 351.72.069 Kok Markt 23 1-2-1934 {fiets,auto,motorfiets} 816.45.926 Smit Spui 13 5-6-1978 {fiets} 465.99.810 Kok Kerkstr 7 2-3-1943 {auto} Attribuut “Vervoersmiddelen” is multi-valued en schendt daarmee 1NF (en tevens de definitie van het relationele model) Ieder relationeel schema van een tabel die aan de eisen van het relationele model voldoet (en dus geen multi-valued attributen bevat), is automatisch in 1NF.

  42. Normaalvormen: 2NF (1/2) R = {Stad, Straat, Huisnr, Vraagprijs, Stadspop} F = {Stad, Straat, Huisnr  Vraagprijs, Stad  Stadspop} Stad Straat Huisnr Vraagprijs Stadspop Amsterdam Westerstr 31 500 000 734 000 Den Haag Laan 237 400 000 442 000 Den Haag Hoefkade 30 150 000 442 000 Appingedam Brink 8 200 000 12 000 Appingedam Brink 12 225 000 12 000 Key: {Stad, Straat, Huisnr} “schuldige” f.a.: Stad  Stadspop

  43. Normaalvormen: 2NF (2/2) R = {Stad, Straat, Huisnr, Vraagprijs, Stadspop} F = {Stad, Straat, Huisnr  Vraagprijs, Stad  Stadspop} Key: {Stad, Straat, Huisnr} “schuldige” f.a.: Stad  Stadspop r_partiële f.a.:een relevante f.a. X  A waarbij X een echte deelverzameling is van een of andere sleutel 2NF: geen r_partiële f.a.’s naar niet-primaire attributen, i.e. naar attributen die niet tot een of andere key behoren.

  44. Normaalvormen: 3NF (1/2) R = {ISBN, Titel, Auteur, Tel_Auteur} F = { ISBN  Titel, ISBN  Auteur, Auteur  Tel_Auteur} ISBN Titel Auteur Tel_Auteur 1234 Het Stenen Bruidsbed Mulisch 06 23456789 2345 De Ontdekking v/d Hemel Mulisch 06 23456789 3456 Als je begrijpt wat ik bedoel Toonder +353 1 2345678 4567 Zoals mijn goede vader zei Toonder +353 1 2345678 Key: {ISBN} “schuldige” f.a.: Auteur  Tel_Auteur

  45. Normaalvormen: 3NF (2/2) R = {ISBN, Titel, Auteur, Tel_Auteur} F = {ISBN  Titel, ISBN  Auteur, Auteur  Tel_Auteur} Key: {ISBN} “schuldige” f.a.: Auteur  Tel_Auteur r_transitieve f.a.:een relevante f.a. X  A waarbij X geen deelverzameling is van enige sleutel (dus geen sleutel en geen echte deelverz) 3NF: geen r_partiële of r_transitieve f.a.’s naar niet-primaire attributen, i.e. naar attributen die niet tot een of andere key behoren.

  46. Drie soorten relevante f.a.’s Zij X  Y een relevante f.a.(d.w.z. een niet-triviale, volledige f.a. met enkelvoudige rechterkant) • als er tenminste één key S is met X = S,dan is X  Y een r_sleutelafh. • als er tenminste één key S is met X  S èn X  S,dan is X  Y een r_partiële afh. • als er geen enkele key S is met X = S of (X  S èn X  S),dan is X  Y een r_transitieve afh. Dus: een relevante f.a. is ofwel een r_sleutelafh. ofwel een r_partiële afh. ofwel een r_transitieve afh.

  47. Normaalvormen: BCNF (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} } “schuldige” f.a.’s: Postcode  Stad en Postcode  Straat

  48. Normaalvormen: BCNF (2/3) R = {Stad, Straat, Huisnr, Postcode, Vraagprijs} F = {Stad, Straat, Huisnr  Postcode, Stad, Straat, Huisnr  Vraagprijs Postcode, Huisnr  Vraagprijs Postcode  Stad Postcode  Straat} Keys: { {Stad, Straat, Huisnr}, {Postcode, Huisnr} } “schuldige” f.a.’s: • Postcode  Stad • Postcode  Straat BCNF: geen r_partiële of r_transitieve f.a.’s(ook niet naar key-attributen)

  49. Normaalvormen: BCNF (3/3) 4 equivalente beschrijvingen van BCNF: • geen r_partiële of r_transitieve f.a.’s(ook niet naar key-attributen) • dus, voor iedere relevante f.a. X  A  F+ geldt: X  A is een r_sleutelafh (een relevante f.a. met waarbij X een sleutel) • dus, voor iedere relevante f.a. X  A  F+ geldt dat X een sleutel is • dus, voor iedere niet-triviale f.a. X  A  F+ geldt dat X een supersleutel is

  50. Overzicht normaalvormen Zij R een relatie en F een verzameling f.a.’s. Zij F’ een verzameling f.a.’s equivalent met F z.d.d.: 1. voor iedere X  {A1, A2, …, An}  F bestaat er eenX  A1  F’, X A2  F’, … , X An  F’ (stap 1 minimal cover) 2. de linkerkanten van F zijn geminimaliseerd (stap 2 minimal cover) 3. eventuele triviale afhankelijkheden zijn verwijderd. Merk op: F’ bevat uitsluitend relevante f.a.’s • R is in 2NF t.o.v. F als:F’ geen r_partiële f.a. naar een niet-primair attribuut bevat. • R is in 3NF t.o.v. F als:F’ geen r_partiële f.a. naar een niet-primair attribuut bevat enook geen r_transitieve f.a. naar een niet-primair attribuut bevat. • R is in BCNF t.o.v. F als:F’ helemaal geen r_partiële en geen transitieve f.a.’s heeft (ook niet naar primaire attributen)

More Related