Datenbanken Unit 5: Funktionale Abhängigkeit

Organisatorisches
SQL
Datenbanken
Unit 5: Funktionale Abhängigkeit
Ronald Ortner
21. III. 2017
Ronald Ortner
Funktionale Abhängigkeit
Organisatorisches
Outline
1
Organisatorisches
2
SQL
3
Funktionale Abhängigkeit
Ronald Ortner
SQL
Funktionale Abhängigkeit
Organisatorisches
Outline
1
Organisatorisches
2
SQL
3
Funktionale Abhängigkeit
Ronald Ortner
SQL
Funktionale Abhängigkeit
Organisatorisches
SQL
Funktionale Abhängigkeit
Organisatorisches
Morgen Zwischentest in den UE. Fragen?
Wissensüberprüfung zu JOINs nächste Woche.
Abschlusstest am 14.6. (Termine im MU Online nachgetragen)
Beispiel 3.20 neu gewertet
Ronald Ortner
Organisatorisches
Outline
1
Organisatorisches
2
SQL
3
Funktionale Abhängigkeit
Ronald Ortner
SQL
Funktionale Abhängigkeit
Organisatorisches
SQL
Funktionale Abhängigkeit
SQL – Häufige Fehler
Verwenden Sie NIE ein GROUP BY ohne entsprechende
Aggregatfunktion!
Subqueries (nicht korreliert):
- Lässt sich Subquery alleine ausführen?
- Welches Ergebnis gibt Subquery?
Ronald Ortner
Organisatorisches
Outline
1
Organisatorisches
2
SQL
3
Funktionale Abhängigkeit
Ronald Ortner
SQL
Funktionale Abhängigkeit
Organisatorisches
SQL
Funktionale Abhängigkeit
Datenbankdesign bisher:
zeichne Entitäten, Relationen, und Attribute in ein ER-Diagramm
leite aus dem ER-Diagramm ein Datenbankschema ab
Nun beschäftigen wir uns mit einem verfeinerten Zugang
(’Normalisierung’).
Die Grundlage dafür ist der Begriff der funktionalen Abhängigkeit.
Ronald Ortner
Organisatorisches
SQL
Abstrakte Schemata und ihre Realisierung
Bisher waren wir ein wenig schlampig.
Im folgenden unterscheiden wir oft genauer zwischen
abstrakten relationalen Datenbankschemata
(' Tabellenstruktur ohne Daten)
Notation: R
Realisierungen R solcher Schemata
(' die Daten in den Tabellen)
Notation: R
Ronald Ortner
Funktionale Abhängigkeit
Organisatorisches
SQL
Funktionale Abhängigkeit
Funktionale Abhängigkeit
Definition (funktionale Abhängigkeit)
Gegeben sei ein abstraktes relationales Datenbankschema R,
sowie (Mengen von) Attribute(n) α, β in R.
Wir sagen, dass β funktional abhängig (FD) von α ist, wenn für alle
Realisierungen R of R:
Immer wenn zwei Tupel (Zeilen) dieselben Werte für α haben, so
haben sie auch gleiche Werte für β.
Notation: α → β
Beispiele:
{ name} → { region, area, population, gdp} in cia
{ jahr, monat} → { tmin, tmax, gmin, sun, rain} in sowe
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Funktionale Abhängigkeit: Ein Beispiel
A
a4
a1
a1
a2
a3
Es gelten:
{A} → {B}
{A} → {C}
{C,D} → {B}
{B} 6→ {C}
Ronald Ortner
B
b2
b1
b1
b2
b2
C
c4
c1
c1
c3
c4
D
d3
d1
d2
d2
d3
Organisatorisches
SQL
Funktionale Abhängigkeit
Überprüfen funktionaler Abhängigkeit
Das Überprüfen, ob eine FD α → β für eine Realisierung R gilt, ist
einfach:
Sortiere R nach α.
Überprüfe ob Tupel mit gleichen α-Werten auch gleiche β-Werte
haben.
[→ Aufwand fürs Sortieren: O(n log n) für n Zeilen]
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Triviale funktionale Abhängigkeiten
Eine FD heißt trivial, wenn sie in allen möglichen Realisierungen gilt.
Charakterisierung trivialer FDs
Jede triviale FD ist von der Form α → β mit β ⊆ α.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Schlüssel und funktionale Abhängigkeiten
FDs sind eine Verallgemeinerung des Schlüssel-Konzepts.
→ Schlüssel lassen sich über FDs definieren:
Definition ((Super-)Schlüssel)
Für ein abstraktes relationales Datenbankschema R ist α ⊆ R ein
(Super-)Schlüssel, wenn
α → R.
Trivialerweise bildet die Menge aller Attribute in R einen
(Super-)Schlüssel für R (weil R → R).
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Volle funktionale Abhängigkeit
Um Kandidatenschlüssel von (Super-)Schlüsseln unterscheiden zu
können, führen wir den Begriff der vollen funktionalen Abhängigkeit
ein:
Definition (volle funktionalen Abhängigkeit)
β ist voll funktional abhängig von α, wenn
1
α → β, und
2
α ist minimal, d.h. entfernt man irgendein Attribut A aus α, so
bricht die FD zusammen, d.h. α − A 6→ β.
Notation:
Ronald Ortner
α→β
˙
Organisatorisches
SQL
Funktionale Abhängigkeit
Schlüssel und funktionale Abhängigkeiten II
Definition (Kandidatenschlüssel)
Für ein abstraktes relationales Datenbankschema R ist α ⊆ R ein
Kandidatenschlüssel, wenn
α→R.
˙
Ein Primärschlüssel ist ein beliebig gewählter Kandidatenschlüssel.
Es ist unwichtig, welcher gewählt wird.
Es ist aber wichtig, dass der gewählte Schlüssel durchgehend
verwendet wird (etwa als Fremdschlüssel in anderen Tabellen).
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Eigenschaften von FDs
Beispiel: Studierendentelefonbuch:
{[matrnr, name, strasse, plz, stadt, vorwahl, telnr]}
FDs:
{matrnr} → {name, strasse, plz, stadt}
{plz} → {vorwahl}
{matrnr} → {vorwahl}
Anmerkung: Die dritte Relation scheint aus den anderen beiden zu
folgen...
Ronald Ortner
Organisatorisches
SQL
Eigenschaften von FDs: Armstrong Axiome
Armstrong Axiome:
Reflexivität:
Wenn β ⊆ α, dann α → β.
Insbesondere gilt α → α.
Ronald Ortner
Funktionale Abhängigkeit
Organisatorisches
SQL
Eigenschaften von FDs: Armstrong Axiome
Armstrong Axiome:
Reflexivität:
Wenn β ⊆ α, dann α → β.
Insbesondere gilt α → α.
Verstärkung:
Wenn α → β, dann auch αγ → βγ.
(Notation: αγ steht für α ∪ γ.)
Ronald Ortner
Funktionale Abhängigkeit
Organisatorisches
SQL
Eigenschaften von FDs: Armstrong Axiome
Armstrong Axiome:
Reflexivität:
Wenn β ⊆ α, dann α → β.
Insbesondere gilt α → α.
Verstärkung:
Wenn α → β, dann auch αγ → βγ.
(Notation: αγ steht für α ∪ γ.)
Transitivität:
Wenn α → β und β → γ, dann auch α → γ.
Ronald Ortner
Funktionale Abhängigkeit
Organisatorisches
SQL
Funktionale Abhängigkeit
Eigenschaften von FDs: Der Abschluss
Definition (Abschluss)
Sei R eine Relation und F eine Menge von FDs in R.
Der Abschluss F + von F ist die Menge aller FDs, die logisch aus den
FDs in F folgen.
Korrektheit und Vollständigkeit der Armstrong Axiome
Alles, was aus F mithilfe der Armstrong Axiome abgeleitet werden
kann, ist in F + (Korrektheit).
Alle FDs in F + können aus F mithilfe der Armstrong Axiome
abgeleitet werden (Vollständigkeit).
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Noch mehr Eigenschaften von FDs
Mithilfe der Armstrong Axiome können weiters folgende Eigenschaften
abgeleitet werden:
Vereinigung:
Wenn α → β und α → γ, dann α → βγ.
Dekompositionsregel:
Wenn α → βγ, dann α → β und α → γ.
Pseudotransitivität:
Wenn α → β und βγ → δ, dann auch αγ → δ.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Eigenschaften von FDs: Zurück zum Beispiel
Beispiel: Studierendentelefonbuch:
{[matrnr, name, strasse, plz, stadt, vorwahl, telnr]}
FDs:
{matrnr} → {name, strasse, plz, stadt}
{plz} → {vorwahl}
{matrnr} → {vorwahl}
Ableitung der dritten FD aus den anderen beiden:
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Eigenschaften von FDs: Zurück zum Beispiel
Beispiel: Studierendentelefonbuch:
{[matrnr, name, strasse, plz, stadt, vorwahl, telnr]}
FDs:
{matrnr} → {name, strasse, plz, stadt}
{plz} → {vorwahl}
{matrnr} → {vorwahl}
Ableitung der dritten FD aus den anderen beiden:
Durch (wiederholte) Dekomposition folgt aus (1):
{matrnr} → {plz}
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Eigenschaften von FDs: Zurück zum Beispiel
Beispiel: Studierendentelefonbuch:
{[matrnr, name, strasse, plz, stadt, vorwahl, telnr]}
FDs:
{matrnr} → {name, strasse, plz, stadt}
{plz} → {vorwahl}
{matrnr} → {vorwahl}
Ableitung der dritten FD aus den anderen beiden:
Durch (wiederholte) Dekomposition folgt aus (1):
{matrnr} → {plz}
Zusammen mit (2) folgt mit Transitivität (3).
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Bestimmung funktional abhängiger Attribute
Gegeben: Attribute α, Menge F von FDs
+
Wollen: alle Attribute αF
, die von α funktional abhängig sind
Algorithmus:
+
Initialisiere αF
:= {α}, change:=true;
while (change) do{
change=false;
for each FD β → γ in F do{
+
if (β ⊆ αF
) then{
+
+
αF := αF
∪ γ;
change=true;
}}}
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Bestimmung funktional abhängiger Attribute
Beispiel:
Sei F = {C → DA, A → BC, E → ABC, F → BC, CD → BF }.
Berechne A+
F.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Bestimmung funktional abhängiger Attribute
Beispiel:
Sei F = {C → DA, A → BC, E → ABC, F → BC, CD → BF }.
Berechne A+
F.
A+
F = {A, B, C, D, F }
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Bestimmung funktional abhängiger Attribute
Beispiel:
Sei F = {C → DA, A → BC, E → ABC, F → BC, CD → BF }.
Berechne A+
F.
A+
F = {A, B, C, D, F }
Der Algorithmus kann auch dazu verwendet werden, um
(Super-)Schlüssel κ zu finden:
Wenn κ+ = R, dann ist κ ein (Super-)Schlüssel von R.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Äquivalenz und kanonische Überdeckung
Definition (Äquivalenz)
Zwei Mengen F, G von FDs heißen äquivalent (F ≡ G), wenn
F + = G +.
Intuitiv: F, G sind äquivalent, wenn dieselben FDs ableitbar sind.
Problem: F + ist im allgemeinen recht groß und unstrukturiert.
Lösung: Wähle für jedes F ein spezielles G mit F ≡ G.
(So ein G heißt kanonische Überdeckung.)
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Kanonische Überdeckung
Definition (kanonische Überdeckung)
Sei F eine Menge von FDs. Fc heißt kanonische Überdeckung von F,
wenn
1
Fc ≡ F (d.h., Fc+ = F + )
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Kanonische Überdeckung
Definition (kanonische Überdeckung)
Sei F eine Menge von FDs. Fc heißt kanonische Überdeckung von F,
wenn
1
2
Fc ≡ F (d.h., Fc+ = F + )
In allen FDs α → β aus Fc enthalten weder α noch β redundante
Attribute, d.h.:
1
2
Ronald Ortner
für alle A in α: (Fc − {α → β}) ∪ {(α − A) → β} ≡
6 Fc
für alle B in β: (Fc − {α → β}) ∪ {α → (β − B)} ≡
6 Fc
Organisatorisches
SQL
Funktionale Abhängigkeit
Kanonische Überdeckung
Definition (kanonische Überdeckung)
Sei F eine Menge von FDs. Fc heißt kanonische Überdeckung von F,
wenn
1
2
Fc ≡ F (d.h., Fc+ = F + )
In allen FDs α → β aus Fc enthalten weder α noch β redundante
Attribute, d.h.:
1
2
3
für alle A in α: (Fc − {α → β}) ∪ {(α − A) → β} ≡
6 Fc
für alle B in β: (Fc − {α → β}) ∪ {α → (β − B)} ≡
6 Fc
Für jede FD α → β in Fc gibt es keine andere FD der Form α → γ
in Fc .
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Kanonische Überdeckung
Definition (kanonische Überdeckung)
Sei F eine Menge von FDs. Fc heißt kanonische Überdeckung von F,
wenn
1
2
Fc ≡ F (d.h., Fc+ = F + )
In allen FDs α → β aus Fc enthalten weder α noch β redundante
Attribute, d.h.:
1
2
3
für alle A in α: (Fc − {α → β}) ∪ {(α − A) → β} ≡
6 Fc
für alle B in β: (Fc − {α → β}) ∪ {α → (β − B)} ≡
6 Fc
Für jede FD α → β in Fc gibt es keine andere FD der Form α → γ
in Fc .
Anmerkung: Bedingung 3 kann durch wiederholte Zusammenfassung
von FDs α → β, α → γ to α → βγ erreicht werden.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung
Gegeben: Menge F of FDs
Wollen: kanonische Überdeckung Fc
Algorithmus:
1
Führe für jede FD α → β in F Linksreduktion aus:
Überprüfe für alle A in α, ob A redundant, d.h., β ⊆ (α − A)+
F.
In diesem Fall ersetze α → β durch (α − A) → β.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung
Gegeben: Menge F of FDs
Wollen: kanonische Überdeckung Fc
Algorithmus:
1
Führe für jede FD α → β in F Linksreduktion aus:
Überprüfe für alle A in α, ob A redundant, d.h., β ⊆ (α − A)+
F.
In diesem Fall ersetze α → β durch (α − A) → β.
2
Führe für jede FD α → β in F Rechtsreduktion aus:
Überprüfe für alle B in β, ob B redundant, d.h., B in
+
αF
−{α→β})∪{α→(β−B)} . In diesem Fall ersetze α → β durch
α → (β − B).
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung
Gegeben: Menge F of FDs
Wollen: kanonische Überdeckung Fc
Algorithmus:
1
Führe für jede FD α → β in F Linksreduktion aus:
Überprüfe für alle A in α, ob A redundant, d.h., β ⊆ (α − A)+
F.
In diesem Fall ersetze α → β durch (α − A) → β.
2
Führe für jede FD α → β in F Rechtsreduktion aus:
Überprüfe für alle B in β, ob B redundant, d.h., B in
+
αF
−{α→β})∪{α→(β−B)} . In diesem Fall ersetze α → β durch
α → (β − B).
3
Entferne FDs α → ∅ aus F. (Diese können in Schritt 2 entstehen.)
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung
Gegeben: Menge F of FDs
Wollen: kanonische Überdeckung Fc
Algorithmus:
1
Führe für jede FD α → β in F Linksreduktion aus:
Überprüfe für alle A in α, ob A redundant, d.h., β ⊆ (α − A)+
F.
In diesem Fall ersetze α → β durch (α − A) → β.
2
Führe für jede FD α → β in F Rechtsreduktion aus:
Überprüfe für alle B in β, ob B redundant, d.h., B in
+
αF
−{α→β})∪{α→(β−B)} . In diesem Fall ersetze α → β durch
α → (β − B).
3
Entferne FDs α → ∅ aus F. (Diese können in Schritt 2 entstehen.)
4
Fasse FDs α → β1 , α → β2 ,. . . , α → βk zusammen zu
α → β1 β2 . . . βk .
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung:
Beispiel: F = {A → B, B → C, AB → C}
In Schritt 1 des Algorithmus ersetzen wir AB → C durch A → C,
da C in A+
F : C folgt aus A → B und B → C, wenn A gegeben.
Dann F = {A → B, B → C, A → C}.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung:
Beispiel: F = {A → B, B → C, AB → C}
In Schritt 1 des Algorithmus ersetzen wir AB → C durch A → C,
da C in A+
F : C folgt aus A → B und B → C, wenn A gegeben.
Dann F = {A → B, B → C, A → C}.
In Schritt 2 ersetzen wir A → C durch A → ∅, da C redundant:
C in A+
{A→B,B→C,A→∅} .
Dann F = {A → B, B → C, A → ∅}.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung:
Beispiel: F = {A → B, B → C, AB → C}
In Schritt 1 des Algorithmus ersetzen wir AB → C durch A → C,
da C in A+
F : C folgt aus A → B und B → C, wenn A gegeben.
Dann F = {A → B, B → C, A → C}.
In Schritt 2 ersetzen wir A → C durch A → ∅, da C redundant:
C in A+
{A→B,B→C,A→∅} .
Dann F = {A → B, B → C, A → ∅}.
In Schritt 3 entfernen wir A → ∅.
Dann F = {A → B, B → C}.
Ronald Ortner
Organisatorisches
SQL
Funktionale Abhängigkeit
Berechnung der kanonischen Überdeckung:
Beispiel: F = {A → B, B → C, AB → C}
In Schritt 1 des Algorithmus ersetzen wir AB → C durch A → C,
da C in A+
F : C folgt aus A → B und B → C, wenn A gegeben.
Dann F = {A → B, B → C, A → C}.
In Schritt 2 ersetzen wir A → C durch A → ∅, da C redundant:
C in A+
{A→B,B→C,A→∅} .
Dann F = {A → B, B → C, A → ∅}.
In Schritt 3 entfernen wir A → ∅.
Dann F = {A → B, B → C}.
Schritt 4 lässt F unverändert, sodass die kanonische
Überdeckung Fc = {A → B, B → C}.
Ronald Ortner