Datenbanken 6: Normalisierung

SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Datenbanken 6: Normalisierung
Ronald Ortner
27. III. 2017
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
SQL
Nachbesprechung Zwischentest
korrelierte Subqueries
Ronald Ortner
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Nachbesprechung Zwischentest
unvollständiges GROUP BY
Subquery statt WHERE
Bedingungen in Subquery
Ronald Ortner
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
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
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Funktionale Abhängigkeit
Zur Erinnerung:
Definition (funktionale Abhängigkeit)
Gegeben sei ein abstraktes relationales Datenbankschema R
sowie (Mengen von) Attribute(n) α, β in R.
Wir sagen, dass β funktional abhängig 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 β.
Idee:
Legt man einen Wert für α fest, so ist auch der Wert für β festgelegt.
α → β bedeutet, dass β durch α eindeutig bestimmt wird.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Funktionale Abhängigkeit
Zur Erinnerung:
Definition (funktionale Abhängigkeit)
Gegeben sei ein abstraktes relationales Datenbankschema R
sowie (Mengen von) Attribute(n) α, β in R.
Wir sagen, dass β funktional abhängig 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 β.
Achtung:
Für unsere Belange ist nur diese Art von Abhängigkeit relevant.
Andere Formen von Abhängigkeit (z.B. Korrelation) spielen keine Rolle
und dürfen nicht mit funktionaler Abhängigkeit verwechselt werden!
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Anomalien in schlecht designten Datenbankschemata
Betrachten das folgende Datenbankschema zum Speichern von
Studenteninformation:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
.
.
.
Franz Huber
Franz Huber
.
.
.
150420
150111
.
.
.
Datenbanken
IT I
.
.
.
2
4
.
.
.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Anomalien in schlecht designten Datenbankschemata
Betrachten das folgende Datenbankschema zum Speichern von
Studenteninformation:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
.
.
.
Franz Huber
Franz Huber
.
.
.
150420
150111
.
.
.
Datenbanken
IT I
.
.
.
2
4
.
.
.
Das ist ein schlecht designtes Datenbankschema, weil ...
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Update-Anomalie
Das ist ein schlecht designtes Datenbankschema, weil ...
... wenn Studenteninformation aktualisiert wird, müssen mehrere
Zeilen geändert werden:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
.
.
.
Franz Huber
Franz Huber
.
.
.
150420
150111
.
.
.
Datenbanken
IT I
.
.
.
2
4
.
.
.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Update-Anomalie
Das ist ein schlecht designtes Datenbankschema, weil ...
... wenn Studenteninformation aktualisiert wird, müssen mehrere
Zeilen geändert werden:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
.
.
.
Adolf Huber
Adolf Huber
.
.
.
150420
150111
.
.
.
Datenbanken
IT I
.
.
.
2
4
.
.
.
fehleranfällig
schlechtere Performance bei Aktualisierungen
speichert redundante Information
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Einfüge-Anomalie
Das ist ein schlecht designtes Datenbankschema, weil ...
... wenn neue Studenteninformation eingefügt wird, ist keine
entsprechende Lehrveranstaltungsinformation verfügbar:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
1635001
.
.
.
Franz Huber
Franz Huber
Anna Schmied
.
.
.
150420
150111
NULL
.
.
.
Datenbanken
IT I
NULL
.
.
.
2
4
NULL
.
.
.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Lösch-Anomalie
Das ist ein schlecht designtes Datenbankschema, weil ...
... wenn Informationen von Studenten gelöscht werden, gehen
Informationen über Lehrveranstaltungen verloren und umgekehrt:
M_Nr
Name
Lva_Nr
Lva
Note
1335123
1335123
.
.
.
Franz Huber
Franz Huber
.
.
.
150420
150111
.
.
.
Datenbanken
IT I
.
.
.
2
4
.
.
.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Lösch-Anomalie
Das ist ein schlecht designtes Datenbankschema, weil ...
... wenn Informationen von Studenten gelöscht werden, gehen
Informationen über Lehrveranstaltungen verloren und umgekehrt:
M_Nr
.
.
.
Name
.
.
.
Lva_Nr
.
.
.
Lva
.
.
.
Note
.
.
.
Das passiert auch, wenn z.B. Informationen von “alten” Prüfungen
gelöscht werden.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Zerlegung von Relationen
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Zerlegung von Relationen
Anomalien treten auf, wenn Informationen, die nicht
“zusammenpassen”, in dieselbe Tabelle/Relation gesteckt werden.
Idee der Normalisierung:
Zerlege Relation R in kleinere Relationen R1 , . . . Rn .
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Zerlegung von Relationen
Anomalien treten auf, wenn Informationen, die nicht
“zusammenpassen”, in dieselbe Tabelle/Relation gesteckt werden.
Idee der Normalisierung:
Zerlege Relation R in kleinere Relationen R1 , . . . Rn .
Dabei soll die Zerlegung gewisse Eigenschaften erfüllen:
Verlustfreiheit:
R kann aus R1 , . . . Rn rekonstruiert werden.
Erhaltung von FDs:
Die funktionalen Abhängigkeiten von R bleiben in R1 , . . . Rn
erhalten.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit
Betrachten Zerlegung von R in zwei Relationen R1 , R2 .
Um Verlustfreiheit zu haben, benötigen wir zumindest R = R1 ∪ R2 .
Darüber hinaus möchten wir:
Definition (Verlustfreiheit)
Die Zerlegung von R in R1 , R2 ist verlustfrei, wenn für Realisierungen
R von R (und für entsprechende Zerlegungen R1 , R2 ):
R ist der natürliche Join von R1 und R2 , d.h. R = R1 o
n R2 .
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Betrachten die folgende Relation/Tabelle:
Ronald Ortner
Gast
Gasthof
Bier
R.Ortner
F.Huber
R.Ortner
Gösserbräu
Zur Post
Zur Post
Gösser
Gösser
Murauer
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Betrachten die folgende Relation/Tabelle:
Gast
Gasthof
Bier
R.Ortner
F.Huber
R.Ortner
Gösserbräu
Zur Post
Zur Post
Gösser
Gösser
Murauer
Betrachten folgende Zerlegung:
Besucher: {[Gast, Gasthof]}
Getränke: {[Gast, Bier]}
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Betrachten die folgende Relation/Tabelle:
Gast
Gasthof
Bier
R.Ortner
F.Huber
R.Ortner
Gösserbräu
Zur Post
Zur Post
Gösser
Gösser
Murauer
Besucher:
Ronald Ortner
Gast
Gasthof
R.Ortner
F.Huber
R.Ortner
Gösserbräu
Zur Post
Zur Post
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Betrachten die folgende Relation/Tabelle:
Gast
Gasthof
Bier
R.Ortner
F.Huber
R.Ortner
Gösserbräu
Zur Post
Zur Post
Gösser
Gösser
Murauer
Getränke:
Ronald Ortner
Gast
Bier
R.Ortner
F.Huber
R.Ortner
Gösser
Gösser
Murauer
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Der Join der beiden Relationen Besucher und Getränke enthält zwei
zusätzliche (falsche) Einträge:
Ronald Ortner
Gast
Gasthof
Bier
R.Ortner
R.Ortner
F.Huber
R.Ortner
R.Ortner
Gösserbräu
Gösserbräu
Zur Post
Zur Post
Zur Post
Gösser
Murauer
Gösser
Murauer
Gösser
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Beispiel
Der Join der beiden Relationen Besucher und Getränke enthält zwei
zusätzliche (falsche) Einträge:
Gast
Gasthof
Bier
R.Ortner
R.Ortner
F.Huber
R.Ortner
R.Ortner
Gösserbräu
Gösserbräu
Zur Post
Zur Post
Zur Post
Gösser
Murauer
Gösser
Murauer
Gösser
Diese Zerlegung ist nicht verlustfrei.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Ein Kriterium für Verlustfreiheit
Man kann ein Kriterium für Verlustfreiheit über funktionale
Abhängigkeiten(FDs) angeben:
Kriterium für Verlustfreiheit
Eine Zerlegung von R mit FDs F in R1 , R2 ist verlustlos, wenn
zumindest eine der beiden folgenden Bedingungen gilt:
(R1 ∩ R2 ) → R1 ist in F + ,
(R1 ∩ R2 ) → R2 ist in F + .
In unserem Beispiel gibt es nur die FD {Gast}→{Gasthof,Bier}, aber es
gelten weder {Gast}→{Gasthof} noch {Gast}→{Bier}.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Verlustfreiheit: Weiteres Beispiel
Betrachten folgende Relation:
Vater
.
.
.
Mutter
.
.
.
Kind
.
.
.
Die Zerlegung in Relationen
Väter: {[Vater, Kind]}
Mütter: {[Mutter, Kind]}
ist verlustlos, da beide FDs {Kind}→{Vater} und {Kind}→{Mutter}
gelten.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Erhaltung von Abhängigkeiten
Gegeben: Relation R mit FDs F und Zerlegung in R1 , . . . , Rn .
Im Prinzip können FDs auch dann überprüft werden, wenn diese durch
die Zerlegung ’auseinandergerissen’ werden:
Bilde Join der Realisierungen R1 , . . . Rn .
Überprüfe FDs.
Besser ist allerdings:
FDs können in den Relationen R1 , . . . , Rn überprüft werden.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Abhängigkeitserhaltung
D.h., wir hätten gerne:
Definition (Abhängigkeitserhaltung)
Gegeben sei eine Relation R mit FDs F und eine Zerlegung von R in
R1 , . . . , Rn , wobei jedes Ri die FDs Fi hat. Diese Zerlegung ist
abhängigkeitserhaltend, wenn
F ≡ (F1 ∪ . . . ∪ Fn ).
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Abhängigkeitserhaltung: Beispiel
Stadt
Bundesland
Strasse
PLZ
Bruck
Leoben
Bruck
Stmk
Stmk
NÖ
Mittergasse
Franz Josef Straße
Hauptstraße
8600
8700
2460
Wir nehmen an:
Es gibt keine zwei Städte mit gleichem Namen im gleichen
Bundesland.
Die PLZ ändert sich nicht innerhalb derselben Straße.
Verschiedene Städte haben unterschiedliche PLZ.
Städte gehören eindeutig zu einem Bundesland.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Abhängigkeitserhaltung: Beispiel
Stadt
Bundesland
Straße
PLZ
Bruck
Leoben
Bruck
Stmk
Stmk
NÖ
Mittergasse
Franz Josef Straße
Hauptstraße
8600
8700
2460
Betrachten folgende Zerlegung:
Städte: {[PLZ, Stadt, Bundesland]}
Straßen: {[PLZ, Straße]}
Diese Zerlegung ist verlustlos, da FD {PLZ}→{Stadt, Bundesland} gilt.
Allerdings, kann die FD {Stadt,Bundesland,Straße}→{PLZ} keiner der
neuen Relationen zugeordnet werden.
Diese Zerlegung ist nicht abhängigkeitserhaltend.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Zerlegung von Relationen
Normalisierung: Ausblick
Wir kennen inzwischen:
funktionale Abhängigkeiten (FDs)
Eigenschaften guter Zerlegungen
(Verlustlosigkeit, Abhängigkeitserhaltung)
Ronald Ortner
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Normalisierung: Ausblick
Wir kennen inzwischen:
funktionale Abhängigkeiten (FDs)
Eigenschaften guter Zerlegungen
(Verlustlosigkeit, Abhängigkeitserhaltung)
Wir hätten gerne:
guten Weg, um eine gegebene Relation R in kleinere Relationen
zu zerlegen
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Normalisierung: Ausblick
Wir kennen inzwischen:
funktionale Abhängigkeiten (FDs)
Eigenschaften guter Zerlegungen
(Verlustlosigkeit, Abhängigkeitserhaltung)
Wir hätten gerne:
guten Weg, um eine gegebene Relation R in kleinere Relationen
zu zerlegen
Wir machen dies über:
Schritt-für-Schritt Normalisierung
Mit jedem Schritt erreichen wir eine höhere Normalisierungsstufe
(1 bis 4).
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Normalisierung: Ausblick
Wir hätten gerne:
guten Weg, um eine gegebene Relation R in kleinere Relationen
zu zerlegen
Wir machen dies über:
Schritt-für-Schritt Normalisierung
Mit jedem Schritt erreichen wir eine höhere Normalisierungsstufe
(1 bis 4).
Normalisierung erreicht:
verlustlose Zerlegung
manchmal auch abhängigkeitserhaltende Zerlegung
In den entstehenden Relationen gibt es nur mehr interessante
FDs zwischen Schlüssel- und Nichtschlüsselattributen.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zerlegung von Relationen
Normalisierung
Alternatives Material zu Normalisierung:
Kapitel 2 von
Andreas Meier: “Relationale und postrelationale Datenbanken”,
Springer (innerhalb der MUL frei als pdf zum Download)
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Erste Normalform
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Erste Normalform
Erste Normalform
Definition (1NF)
Eine Relation ist in 1NF, wenn alle Attribute atomar sind.
Diese Voraussetzung haben wir bereits gemacht.
Beispiel einer Tabelle/Relation, die nicht in 1NF:
Ronald Ortner
Vater
Mutter
Kinder
Karl
Ronald
Andreas
Maria
Elisabeth
Marianne
{Fritz, Franziska}
{Hannes, Kathrin}
{Sophia}
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Erste Normalform
Erste Normalform – Beispiel
Beispiel einer Tabelle/Relation, die nicht in 1NF:
Vater
Mutter
Kinder
Karl
Ronald
Andreas
Maria
Elisabeth
Marianne
{Fritz, Franziska}
{Hannes, Kathrin}
{Sophia}
→ kann leicht in 1NF konvertiert werden:
Ronald Ortner
Vater
Mutter
Kind
Karl
Karl
Ronald
Ronald
Andreas
Maria
Maria
Elisabeth
Elisabeth
Marianne
Fritz
Franziska
Hannes
Kathrin
Sophia
SQL
Überblick Datenbankdesign
Anomalien
Zweite Normalform
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform
Definition (2NF)
Eine Relation ist in 2NF, wenn sie in 1NF ist und jedes
Nichtschlüsselattribut voll funktional abhängig von jedem
Kandidatenschlüssel ist.
(Zur Erinnerung: β ist voll funktional abhängig von α, wenn α → β mit
α minimal.)
Intuition: Jedes Attribut im Schlüssel ist wichtig.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Wie sieht Schlüssel in dieser Tabelle aus?
D.h. welche Werte müssen festgelegt werden, um eine Zeile eindeutig
zu identifizieren?
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Wie sieht Schlüssel in dieser Tabelle aus?
D.h. welche Werte müssen festgelegt werden, um eine Zeile eindeutig
zu identifizieren?
Legt man Matrikelnummer, Lehrveranstaltungsnummer sowie Datum
fest, kann es zu den gegebenen Werten nur eine Zeile in der Tabelle
geben.
{MNr,LvaNr,Datum} bildet (Super-)Schlüssel der Tabelle.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname:
Nname:
Stkz:
Studium:
Lva:
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname:
Stkz:
Studium:
Lva:
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz:
Studium:
Lva:
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz: MNr
Studium:
Lva:
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz: MNr
Studium: MNr
Lva:
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz: MNr
Studium: MNr
Lva: LvaNr
Note:
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz: MNr
Studium: MNr
Lva: LvaNr
Note: MNr, LvaNr, Datum
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Note
Listen für jedes Nichtschlüsselattribut jene Schlüsselattribute auf, von
denen es voll funktional abhängt:
Vname: MNr
Nname: MNr
Stkz: MNr
Studium: MNr
Lva: LvaNr
Note: MNr, LvaNr, Datum
Diese Tabelle ist nicht in 2NF!
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Zweite Normalform
Zweite Normalform – Beispiel
bekommen 2NF, indem Attribute, die von derselben Menge von
Schlüsselattributen abhängen, in einer Tabelle zusammengefasst
werden:
MNr
LvaNr
Lva
Vname
Nname
MNr
Stkz
Studium
LvaNr
Datum
Note
Falls es kein Nichtschlüsselattribut gibt, das vom gesamten Schlüssel
abhängt, gibt es eine zusätzliche Tabelle, die nur die Schlüsselwerte
enthält!
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Dritte Normalform
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Dritte Normalform
Definition (3NF)
Eine Relation ist in 3NF, wenn sie in 1NF ist und für jede FD α → β
mindestens eine der folgenden Bedingungen gilt:
1
β ⊆ α, d.h. die FD α → β ist trivial,
2
β ist in einem Kandidatenschlüssel enthalten,
3
α ist ein Superschlüssel.
Insbesondere ist eine Relation nicht in 3NF, wenn es FDs zwischen
Attributen gibt, die nicht Teil eines (Kandidaten-/Super-)Schlüssels
sind.
Intuition: wollen keine FDs zwischen Nichtschlüsselattributen
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Dritte Normalform – Beispiel
Beispiel.
MNr
LvaNr
Ronald Ortner
Lva
Vname
Nname
MNr
Stkz
Studium
LvaNr
Datum
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Dritte Normalform – Beispiel
Beispiel.
MNr
LvaNr
Lva
Vname
Nname
MNr
Stkz
Studium
LvaNr
Datum
Note
Die FD Stkz → Studium erfüllt keine der Bedingungen zur 3NF..
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Dritte Normalform – Beispiel
Beispiel.
Zerlegung, die 3NF erfüllt:
MNr
Vname
LvaNr
Ronald Ortner
Lva
Nname
Stkz
MNr
Stkz
LvaNr
Datum
Studium
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Synthesealgorithmus für 3NF
Gegeben: Relation R, Menge F von FDs
Wir wollen: verlustlose und abhängigkeitserhaltende Zerlegung
R1 , . . . , Rn , sodass jedes Ri in 3NF ist
Algorithmus:
1
Bestimme kanonische Überdeckung Fc von F (siehe Unit 5).
2
Lege für jede FD α → β in Fc eine Relation Rα := α ∪ β an
(und bestimme entsprechende Menge von FDs Fα ).
3
Wenn eine der Relationen Rα einen Kandidatenschlüssel von R
enthält, sind wir fertig.
Ansonsten lege weitere Relation Rκ für einen
Kandidatenschlüssel κ von R an (mit Fκ = ∅).
4
Lösche Relationen Rα0 , die in anderen Relationen Rα enthalten
sind, d.h. Rα0 ⊆ Rα .
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Synthesealgorithmus – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Studium
Funktionale Abhängigkeiten:
MNr → Vname, Nname, Stkz, Studium
Stkz → Studium
LvaNr → Lva
MNr, LvaNr, Datum → Note
Ronald Ortner
LvaNr
Lva
Datum
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Synthesealgorithmus – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Studium
Kanonische funktionale Abhängigkeiten:
MNr → Vname, Nname, Stkz
Stkz → Studium
LvaNr → Lva
MNr, LvaNr, Datum → Note
Ronald Ortner
LvaNr
Lva
Datum
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Synthesealgorithmus – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Tabellen:
MNr, Vname, Nname, Stkz
Stkz, Studium
LvaNr, Lva
MNr, LvaNr, Datum, Note
Ronald Ortner
Studium
LvaNr
Lva
Datum
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Dritte Normalform
Synthesealgorithmus – Beispiel
Betrachten folgende Tabelle mit Studenteninformationen:
(Annahme: nur ein Studium pro Student)
Vname
Nname
MNr
Stkz
Studium
LvaNr
Lva
Datum
Tabellen:
MNr, Vname, Nname, Stkz
Stkz, Studium
LvaNr, Lva
MNr, LvaNr, Datum, Note
Die letzte Tabelle enthält Kandidatenschlüssel der ursprünglichen
Tabelle, also sind wir fertig.
Ronald Ortner
Note
SQL
Überblick Datenbankdesign
Anomalien
Boyce-Codd Normal Form
Outline
1
SQL
2
Überblick Datenbankdesign
3
Anomalien
4
Datenbank Normalisierung
Zerlegung von Relationen
5
Normalisierung
Erste Normalform
Zweite Normalform
Dritte Normalform
Boyce-Codd Normal Form
Ronald Ortner
Datenbank Normalisierung
Normalisierung
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
Boyce-Codd Normalform
Definition (BCNF)
Eine Relation ist in BCNF, wenn sie in 1NF ist und für jede FD α → β
mindestens eine der folgenden eine der folgenden Bedingungen gilt:
1
β ⊆ α, d.h. die FD α → β ist trivial,
2
α ist ein Superschlüssel.
Unterschied zu 3NF:
β darf nicht mehr Teil eines Kandidatenschlüssels sein.
Intuition: Wie für 3NF wollen wir für BCNF keine FDs zwischen
Nichtschlüsselattributen haben (inkl. Attribute, die Teil eines
Kandidatenschlüssels sind, aber nicht als Primärschlüssel verwendet
werden).
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
Boyce-Codd Normalform – Beispiel
Beispiel.
MNr
LvaNr
Semester
Semesterabk
WS 2015/16
W15
Datum
Note
Die Relation zwischen Semester und Semesterabk ist 1:1,
sodass { MNr, LvaNr, Semester, Datum } ein
Kandidatenschlüssel ist.
Wir haben FDs:
{Semester}→{Semesterabk} und
{Semesterabk}→{Semester}
Weder Semster noch Semesterabk ist Superschlüssel, noch
sind die FDs trivial.
Tabelle ist nicht in BCNF
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
Boyce-Codd Normalform – Beispiel
Lösung: neue Tabelle für Semester
MNr
LvaNr
Semesterabk
Semesterabk
Ronald Ortner
Datum
Semester
Note
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
Dekompositionsalgorithmus für BCNF
Gegeben: Relation R, Menge F von FDs
Wir wollen: Zerlegung R1 , . . . , Rn in BCNF
Dekompositionsalgorithmus:
Initialisiere Z := {R};
while (es gibt Ri in Z , das nicht in BCNF) do
1
Finde FD α → β in Ri mit α ∩ β = ∅ und α 6→ Ri .
2
Zerlege Ri in Ri1 := α ∪ β und Ri2 := Ri − β.
3
Ersetze Ri in Z durch Ri1 und Ri2 , d.h.
Z := (Z − {Ri }) ∪ {Ri1 , Ri2 }.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
3NF vs. BCNF
Der Synthesealgorithmus garantiert Zerlegung, die
verlustlos,
abhängigkeitserhaltend und
in 3NF ist.
Der Dekompositionsalgorithmus garantiert Zerlegung, die
verlustlos und
in BCNF ist.
Ronald Ortner
SQL
Überblick Datenbankdesign
Anomalien
Datenbank Normalisierung
Normalisierung
Boyce-Codd Normal Form
3NF vs. BCNF
Der Synthesealgorithmus garantiert Zerlegung, die
verlustlos,
abhängigkeitserhaltend und
in 3NF ist.
Der Dekompositionsalgorithmus garantiert Zerlegung, die
verlustlos und
in BCNF ist.
Unglücklicherweise gilt:
In einigen Fällen ist eine BCNF Zerlegung nicht
abhängigkeitserhaltend.
In diesen Fällen gibt man sich mit 3NF zufrieden.
Ronald Ortner