Jan-Christoph Kalo Florian Plötzky
Institut für Informationssysteme
Technische Universität Braunschweig www.ifis.cs.tu-bs.de
9. Große Übung
Normalisierung
Aufgabe 1
• Gegeben sei die Relation R(A, B, C, D, E, F) mit den funktionalen Abhängigkeiten:
𝐹 = {∅ → C , A → B , B, E → D , D, E → B , {D} → {F}}
A B C D E F
Ermitteln Sie den Abschluss der Attribute X = {B, E} unter F
Aufgabe 1.1
1. Aus {B, E} lässt sich {D} folgern also {X, F}
+= {B, E, D}
2. Ferner gilt D → {F} also {X, F}
+= {B, E, D, F}
Wenn ∅ → {C} gilt, so ist C konstant und damit ableitbar, es ergibt sich also letztendlich {X, F}
+= {B, C, D, E, F}
Bonusfrage: Handelt es sich bei X um einen Candidate oder Super Key für R?
R(A, B, C, D, E, F)
F = {∅ → C , {A} → {B}, {B, E} → {D}, D, E → B , {D} → {F}}
Aufgabe 1.2
Sei X
1= {A, B, E}. Handelt es sich bei X
1um einen gültigen Primary Key für R? Falls nicht, welche Schritte müssten
unternommen werden um dies zu erreichen.
Primary Key: ein ausgewählter Candidate Key. Wir müssen also entweder zeigen, dass X
1ein Candidate Key ist oder ihn dazu machen.
R(A, B, C, D, E, F)
F = {∅ → C , {A} → {B}, {B, E} → {D}, D, E → B , {D} → {F}}
Aufgabe 1.2
Aus 1.1: {X, F}
+= {B, C, D, E, F} und damit war X weder Candidate noch Super Key.
• Aber X
1= X ∪ {A} und damit {X
1, F}
+= {A, B, C, D, E, F}
• Also ist X
1= A, B, E ein Super Key
• Ist es auch ein Candidate Key?
Nein weil {A} → {B} und damit ist X
1nicht irreduzibel.
R(A, B, C, D, E, F)
F = {∅ → C , {A} → {B}, {B, E} → {D}, D, E → B , {D} → {F}}
Aufgabe 1.2
Einfache Lösung: Streiche B aus X
1also X
1= A, E
Gegenprobe: {X
1, F}
+= {A, B, C, D, E, F} und damit:
1. Ist X
1eindeutig und irreduzibel und somit ein Candidate Key
2. Können wir X
1als Primary Key setzen
R(A, B, C, D, E, F)
F = {∅ → C , {A} → {B}, {B, E} → {D}, D, E → B , {D} → {F}}
Aufgabe 1.3
R(A, B, C, D, E, F)
F = {∅ → C , {A} → {B}, {B, E} → {D}, D, E → B , {D} → {F}}
Gegeben seien nun R
1(A, C, D, E, F) und R
2(A, B). Ist R
1, R
2eine verlustfreie Zerlegung (lossless decomposition) von R?
Ja. Wegen {A} → {B} lässt sich R nach dem Satz von Heath in
die beiden obigen Relationen zerlegen. Zerlegungen nach dem
Satz von Heath sind immer verlustfrei.
Aufgabe 2
Gegeben sei folgende Relation:
Raumbuchung(Name, RaumNr, Datum) Mit den funktionalen Abhängigkeiten:
F = ൛ ൟ
Name → RaumNr , RaumNr, Datum → Name
Liegt die Relation in BCNF vor? Falls nicht,
normalisieren Sie weiter bis zur BCNF.
• 1. Normalform erfüllt
• Candidate Keys
– C = Name, Datum , RaumNr, Datum
• 2. Normalform erfüllt (alle Attribute sind Schlüsselattribute)
• 3. Normalform erfüllt (wieder keine Nicht- Schlüsselattribute, ergo keine transitiven Abhängigkeiten)
Aufgabe 2
Raumbuchung(Name, RaumNr, Datum)
F = Name → RaumNr , RaumNr, Datum → Name
• BCNF?
Aufgabe 2
Raumbuchung(Name, RaumNr, Datum)
F = Name → RaumNr , RaumNr, Datum → Name C = Name, Datum , RaumNr, Datum
Name, Datum ∩ RaumNr, Datum = Datum ≠ ∅ Und damit ist die BCNF nicht erfüllt, da mindestens zwei paarweise verschiedene Candidate Keys existieren, die
mindestens ein gemeineinsames Schlüsselelement aufweisen.
Name RaumNr Datum
• Also weiter:
– Auch hier müssen wir einen Schlüssel „brechen“
– Wir wählen hier Name → RaumNr und wenden den Satz von Heath an
• Raumbuchung
1(Name, RaumNr), Raumbuchung
2(Name, Datum)
• Natural Join hält:
– Raumbuchung = Raumbuchung1⋈ Raumbuchung2
Aufgabe 2
Raumbuchung(Name, RaumNr, Datum)
F = Name → RaumNr , RaumNr, Datum → Name C = Name, Datum , RaumNr, Datum
Aufgabe 3
• Schema: R(A,B,C,D,E,F,G,H,I)
• Funktionale Abhängigkeiten
– 𝐻 → {𝐺, 𝐷}
– 𝐸 → 𝐷
– 𝐻, 𝐷 → {𝐶, 𝐸}
– {𝐵, 𝐷} →A
1. Bestimmen Sie die Schlüsselkandidaten 2. Überführen Sie das Schema in 3NF
3. Ist das Schema automatisch in BCNF?
Aufgabe 3
• Schema: R(A,B,C,D,E,F,G,H,I)
• Funktionale Abhängigkeiten
– 𝐻 → {𝐺, 𝐷}
– 𝐸 → 𝐷
– 𝐻, 𝐷 → {𝐶, 𝐸} 𝐻 → {𝐶, 𝐸}
– {𝐵, 𝐷} →A
Aufgabe 3
• Schema: R(A,B,C,D,E,F,G,H,I)
• Funktionale Abhängigkeiten
– 𝐻 → {𝐺, 𝐷, 𝐶, 𝐸}
– 𝐸 → 𝐷
– {𝐵, 𝐷} →A
A B C D E F G H I
Aufgabe 3 - Schlüsselkandidatne
Funktionale Abhängigkeiten
– F = {𝐻 → 𝐺, 𝐷 , 𝐸 → 𝐷, 𝐻 → 𝐶, 𝐸 , {𝐵, 𝐷} →A}
1. Finden Sie die Schlüsselkandidaten
• B, F, H, I tauchen auf keiner rechten Seite auf
– Jeder Schlüsselkandidat enthält mindestens B, F, H, I
• ( 𝐵, 𝐹, 𝐻, 𝐼 , 𝐹)
+ausrechnen
– Closure = {B, F, H, I}
– Anwendung von 𝐻 → 𝐺, 𝐷 . Closure = {B, D, F, G, H, I}
– Anwendung von H → 𝐶, 𝐸 . Closure = {B, C, D, E, F, G, H, I}
– Anwendung von {B, D} → A. Closure = {𝐀, 𝐁, 𝐂, 𝐃, 𝐄, 𝐅, 𝐆, 𝐇, 𝐈}
• ( 𝐵, 𝐹, 𝐻, 𝐼 , 𝐹)
+enthält alle Attribute von R
– B,F,H,I ist der einzige Schlüsselkandidat
Aufgabe 3
– Nicht in 2.NF: A hängt nicht vom gesamten Schlüssel ab
• Sondern nur von der Schlüsselmenge {B} und transitiv von H über {D}.
• Überführen in die 2. NF über Zerlegung mit {𝐵, 𝐷} →A
– Der Schlüssel von B,D,A ändert sich!
– Nicht in 2.NF: C, E, G, D hängen nur von H ab
A B C D E F G H I
A
B D B C D E F G H I
B F H I
C D E G H
Aufgabe 3
– Nicht in 3. NF: D hängt transitiv von H ab
• Überführen in die 3. NF über Zerlegung mit E → 𝐷
– (D,E) braucht einen neuen Schlüssel!
C D E G H B D A B F H I
C E G H D E
Aufgabe 3
• Die Zerlegung ist auch in BCNF!
A
B D B F H I
C E G H D E
