Normalisierung

Kapitel 6
6
Normalisierung
Seite 1
Normalisierung
Dieses Kapitel behandelt Lehrstoff, der in der 9. Jahrgangsstufe im Lehrplan
nicht vorgesehen ist. Es liefert jedoch das theoretische Hintergrundwissen zu
Kapitel 2 Die Datenbank Buchausleihe und ist somit ausschließlich für
interessierte Schüler gedacht, die sich mit der Theorie der Datenbanken
intensiver beschäftigen wollen.
6.1
Effizienz
Eine weitere Möglichkeit, eine relationale Datenbank widerspruchsfrei zu
verwalten, wird durch die so genannte Normalisierung erreicht. Mit der
Normalisierung kannst du sicherstellen sicher, dass die Datenorganisation einer
relationalen Datenbank effizient ist.
Um den Begriff Effizienz zu verdeutlichen betrachte zuerst folgende äußerst
uneffiziente Datenbank:
Schüler
SchülerNr
1
1
4
4
7
11
13
16
18
Name
Poddey T.
Poddey T.
Schwaner N.
Schwaner N.
Vierling M.
Burkert F.
Weiss G.
Kunz K.
Schurian I.
KursNr
1264
1565
1564
1264
1264
1265
1265
1264
1265
Kurs
Französisch 1
Französisch 2
Spanisch 3
Französisch 1
Französisch 1
Französisch 2
Französisch 2
Französisch 1
Französisch 2
Lehrer
Heck
Steppan
Heck
Heck
Heck
Abbildung 6.1: Tabelle Schüler
Unter Effizienz einer Datenbank versteht man u.a.
1.
Fehlen von Redundanz
Ein System enthält Redundanz, wenn die gleichen Daten an mehr als einer
Stelle vorkommen. Neben der Vergeudung von Speicherplatz eröffnet
diese mehrfache Speicherung auch die Möglichkeit inkonsistenter und
mehrdeutiger Daten.
(Änderungs-Anomalien, Einfüge-Anomalien)
2.
Minimaler Einsatz von NULL-Werten
Im obigen Beispiel existieren drei Datensätze mit einem NULL-Wert für
Lehrer. Vielleicht gibt es keinen Lehrer für diesen Kurs, oder der Name ist
einfach noch nicht eingetragen, oder dies ist ein Kurs im selbstständigen
Lernen, der ohne Lehrer auskommt.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 2
einfach noch nicht eingetragen, oder dies ist ein Kurs im selbstständigen
Lernen, der ohne Lehrer auskommt.
NULL ist ein spezieller Wert, der keinem anderen Wert gleicht, nicht
einmal sich selbst. Wir können nicht einmal behaupten, zwei Schüler mit
NULL-Einträgen für Lehrer hätten für jenes Attribut den gleichen Wert.
3.
Kein Datenverlust
Wenn der Schüler Schwaner N. seinen Kurs verlässt, würden alle
Informationen über den Kurs Spanisch 3 verloren gehen.
(Lösch-Anomalien)
Weitere Ziele der Datenorganisation beschreibt Matzke in seinem Skript S. 3.
Übung 6.1:
Überlege Dir an Hand der oben angesprochenen Effizienz, warum diese Tabelle
nicht sinnvoll angelegt wurde.
6.2
Schlüssel
In einer Tabelle sind alle Datensätze eindeutig identifizierbar. Das bedeutet, dass
sich diese Datensätze in mindestens einem Attribut unterscheiden.
Dazu betrachte noch einmal die obige Tabelle Schüler in Abbildung 6.1. Nicht
geeignet als eindeutiges Identifizierungsmerkmal ist das einzelne Attribut
SchülerNr, da es für verschiedene Datensätze den gleichen Wert annehmen
kann. Man erkennt, dass auch die anderen Attribute einzeln nicht geeignet sind.
Ebenfalls nicht besonders nützlich zur eindeutigen Identifizierung ist die Menge
aller Attribute (SchülerNr, Name, KursNr, Kurs, Lehrer). Diese Menge enthält
Attribute, die man entfernen könnte, ohne die eindeutige Identifizierbarkeit
jedes Datensatzes aufzugeben.
Du musst nach einer Menge von Attributen suchen, die sich nicht mehr weiter
reduzieren lässt, und trotzdem jedem Datensatz eine eindeutige Identität
verleiht.
Zur eindeutigen Identifizierbarkeit eines Datensatzes wird der Begriff
Identifikationsschlüssel eingeführt.
Ein Identifikationsschlüssel (Kandidatenschlüssel) besteht aus einem Attribut
oder aus einer minimalen, nicht mehr reduzierbaren Kombination von
Attributen, welche jede Entität einer Entitätsmenge eindeutig identifiziert.
Übung 6.2:
Suche in der Tabelle Schüler alle Identifikationsschlüssel.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 3
Wie bereits erwähnt, eignet sich in der Tabelle Schüler kein einzelnes Attribut
als Identifikationsschlüssel. Somit muss nach zweielementigen Mengen von
Attributen gesucht werden, die jeden Datensatz eindeutig identifizieren. Möglich
wären folgende Kombinationen:
(SchülerNr, KursNr), (Name, KursNr), (SchülerNr, Kurs), (Name, Kurs),
(SchülerNr, Lehrer), (Name, Lehrer)
Bemerkung:
Es existieren auch dreielementige Mengen von Attributen wie z.B. (Name,
KursNr, Lehrer). Diese Kombination bildet jedoch keinen
Identifikationsschlüssel, weil jeder Lehrer seine eigene KursNr hat. Somit ist das
Attribut KursNr in dieser Menge überflüssig, die Kombination lässt sich weiter
reduzieren und ist somit nicht minimal.
Wenn du nach Identifikationsschlüsseln in einer Tabelle suchst, solltest du dir
immer die Frage stellen, ob zur eindeutigen Identifizierung eines Datensatzes
wirklich alle Attribute benötigt werden oder ob du mit weniger Attributen
auskommst. Ein Identitätsschlüssel soll sich nicht mehr reduzieren lassen.
Alle Attribute, die Teil des Identifikationsschlüssels sind, nennt man SchlüsselAttribute. Attribute, die nicht am Identifikationsschlüssel beteiligt sind, heißen
Nichtschlüssel-Attribute.
Hat eine Tabelle mehrere Identifikationsschlüssel, so musst du einen von ihnen
als Primärschlüssel auswählen.
Ein Primärschlüssel ist eine minimale Menge von Attributen, die eindeutig die
Datensätze einer Tabelle (Objekte einer Klasse) identifiziert.
Bemerkung:
Beim Anlegen einer Tabelle hast du bemerkt, dass ACCESS nach einem
Primärschlüssel fragt.
Es ist sicher sinnvoll, einen Primärschlüssel zu wählen, dessen Attributwerte
a)
Zahlen sind (leichtere Handhabung) und
b) sich so wenig wie möglich ändern (keine Telefonnummer, etc).
Um nun Beziehungen zwischen den Tabellen zu definieren, wird ein
Fremdschlüssel benötigt.
Eine Spalte in einer Tabelle, die den Primärschlüssel einer anderen Tabelle
beinhaltet, wird Fremdschlüssel genannt.
Bemerkung:
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 4
In den bisherigen Datenbanken hast du als Primärschlüssel immer einen
künstlichen Schlüssel verwendet und diesen mit der Endung ID gekennzeichnet
(z.B. PersonID). Dieser nummeriert die Datensätze einfach durch und hat als
Wertebereich AutoWert. Somit identifiziert dieser eindeutig jeden Datensatz
einer Tabelle. Die Verwendung eines solchen Primärschlüssels vereinfachte die
Erstellung einer Tabelle, ist jedoch mit deinem jetzigen Wissen nicht unbedingt
notwendig. Den zugehörigen Fremdschlüssel bezeichnest du dann am besten mit
der Endung Nr (z.B PersonNr).
6.3
Normalisierung
Das Normalisierungsverfahren wird in der Regel dazu eingesetzt, eine
Datenbank zu verbessern, die bereits im Klassenmodell entworfen worden ist.
Hierbei charakterisieren die verschiedenen Normalformen Schritte, die
nacheinander ausgeführt werden. Von praktischer Bedeutung sind hier nur die
ersten drei Normalformen:
Normalform Beschreibung
Erläuterung
1. NF
Atomisierung
Alle Attribute sind atomar, d.h. sie lassen sich
nicht in weitere Attribute unterteilen
2. NF
Funktionale
Abhängigkeit
Die Tabelle ist bereits in der 1.NF und alle
Nichtschlüssel-Attribute sind voll funktional
abhängig vom Primärschlüssel.
3. NF
Transitive
Abhängigkeit
Die Tabelle ist bereits in der 2.NF und alle
Nichtschlüssel-Attribute dürfen nicht transitiv
von einem Schlüssel abhängen.
Das hört sich sehr kompliziert an. Deswegen musst du versuchen, an Hand von
einigen Beispielen diese Definitionen zu verstehen. Wie bereits gesagt wird die
Normalisierung im Allgemeinen dazu eingesetzt, eine Datenbank effizient zu
machen. Um nun die drei Normalformen besser zu verdeutlichen, werde ich
ohne den Entwurf eines Klassenmodells mit einer Datenbank Buchausleihe
beginnen.
Buchausleihe (Rohdaten)
PersonID Name
Adresse
1
Vierling Röntgenstr. 26
86343
Königsbrunn
2
Geiger
Lechallee 8
86399
Gymnasium Königsbrunn
ISBN
3-431-02644
3-7627-3733-9
Titel
Algebra7
Geometrie8
Ausleihdatum
21.03.2003
21.03.2003
3-7627-3737-1
3-7627-3735-5
Geometrie10
Geometrie9
20.04.2003
24.07.2003
Ralph Henne
Kapitel 6
Normalisierung
3
McRae
4
Hackl
Bobingen
Alpenstr. 15
86343
Königsbrunn
Edelweißring 49
86343
Königsbrunn
Seite 5
3-431-02962-0
3-7627-3733-9
3-431-02962-0
Algebra9
Geometrie8
Algebra9
24.07.2003
26.07.2003
30.10.2003
3-431-02644
Algebra7
29.09.2003
Abbildung 6.2: Datenbank Buchausleihe (Rohdaten)
In dieser Tabelle ist eine Buchausleihe festgehalten. Eine Analyse des
Ausleihvorgangs ergab folgende Informationsstruktur:
In dieser Bücherei gibt es von jedem Titel jeweils nur ein Buch. Bücher werden
von Personen ausgeliehen. Eine Person kann mehrere Bücher ausleihen. Ein und
dasselbe Buch kann auch mehrmals ausgeliehen werden. (Zwischenzeitliche
Rückgabe wird natürlich vorausgesetzt, dies ist aber in diesem Datenbankmodell
nicht erkennbar.) Eine Person besitzt nur einen Hauptwohnsitz.
Wichtiger Hinweis:
Bevor die Normalisierungsschritte der in Abb. 6.2 dargestellten Datenbank
Buchausleihe(Rohdaten) einzeln besprochen werden, möchte ich auf Folgendes
hinweisen
In diesem Kapitel wird zu Übungszwecken vorausgesetzt, dass die Datenbank
Buchausleihe(Rohdaten) vollständig ist, also keine weiteren Datensätze
hinzukommen. Diese Voraussetzung erleichtert für den Anfang das Verständnis
der einzelnen Normalisierungsschritte. Mit den Schülern wird im Unterricht
jedoch diskutiert, ob die von mir dargestellten funktionalen Abhängigkeiten in
der Praxis sinnvoll gewählt sind oder auch bestehen bleiben, falls neue
Datensätze hinzukommen.
Man sollte sich bei der Normalisierung einer Datenbank immer überlegen, ob
bei Hinzufügen von weiteren Datensätzen die funktionalen Abhängigkeiten
bestehen bleiben.
6.4
1.Normalform
Alle Attribute sind atomar, d.h. sie lassen sich nicht in weitere Attribute
unterteilen
Betrachte zuerst einen Ausschnitt der oben dargestellten Tabelle
Buchausleihe(Rohdaten):
Eine Person leiht ein Buch aus. Sie besitzt die Attribute Name und Adresse.
Sinnvoll wäre auch ein Attribut PersonID (ist hier aber nicht unbedingt
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 6
notwendig, solange ein Name nur einmal vorkommt; aber wer weiß, ob das
immer so bleibt?).
Person
PersonID
Name
Adresse
Abbildung 6.3: Tabelle Person
Du kannst feststellen, dass das Attribut Adresse drei Unterkategorien PLZ, Ort
und Straße besitzt. Die 1.Normalform verlangt, dass das Attribut Adresse durch
die Attribute PLZ, Ort und Straße ersetzt wird. Somit wird vermieden, dass in
einer Tabellenspalte mehrere Werte auftreten.
Person (1.NF)
PersonID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
PLZ
86343
86399
86343
86343
Ort
Königsbrunn
Bobingen
Königsbrunn
Königsbrunn
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring 49
Abbildung 6.4: Tabelle Person (1.NF)
Damit ist die Tabelle „Person“ bereits in der 1.Normalform, obwohl das Attribut
Straße zusammengesetzt ist aus Straße und Hausnummer. Das Attribut kann
man trotzdem elementar nennen, da man die beiden Teile niemals getrennt
voneinander benötigt.
Aber auch das Ausleihdatum wie „21.03.2003“ bezeichnet man als elementar,
obwohl sie Tag, Monat und Jahr enthält, die man doch gelegentlich getrennt
voneinander benötigt. In so einem Fall benutzt man dann spezielle Funktionen,
die aus dem Datum den Tag, den Monat oder das Jahr ermitteln. Das Datum
gehört trotzdem als Ganzes zusammen, sonst lassen sich beispielsweise
Rückgabetermine, Ausleihzeit, usw. nicht berechnen.
Übung 6.3:
Überführe die Tabelle Buchausleihe(Rohdaten) in die 1Normalform.
Betrachte nun die Attribute ISBN, Titel und Ausleihdatum der Tabelle
Buchausleihe (Rohform). Auch diese enthalten mehr als einen Wert. Du musst
also weitere Datensätze in unsere Tabelle einfügen. Die Tabelle Buchausleihe
liegt nun aber vollständig in der 1.Normalform vor:
Buchausleihe (1.NF)
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
P-ID Name
PLZ
1
Vierling 86343
1
Vierling 86343
2
Geiger
2
Ort
Straße
Seite 7
ISBN
3-431-02644
86399
Königsbrunn Röntgenstr.
26
Königsbrunn Röntgenstr.
26
Bobingen
Lechallee 8
Geiger
86399
Bobingen
Lechallee 8
3-7627-3735-5
2
Geiger
86399
Bobingen
Lechallee 8
3-431-02962-0
3
McRae
86343
Königsbrunn Alpenstr. 15 3-7627-3733-9
3
McRae
86343
Königsbrunn Alpenstr. 15 3-431-02962-0
4
Hackl
86343
Königsbrunn Edelweiß49
3-7627-3733-9
3-7627-3737-1
3-431-02644
Titel
Ausl.
datum
Algebra7
21.03.
2003
Geometrie8 21.03.
2003
Geometrie10 20.04.
2003
Geometrie9 24.07.
2003
Algebra9
24.07.
2003
Geometrie8 26.07.
2003
Algebra9
30.10.
2003
Algebra7
29.09.
2003
Abbildung 6.5: Datenbank Buchausleihe in der 1.Normalform
Diese Atomisierung erzeugt leider auch Redundanz. In der Tabelle Buchausleihe
(1.NF) sind die Namen, PLZ, … als auch Titel redundant, weil diese bei jeder
Buchausleihe wiederholt werden.
Übung 6.4:
Suche in der Tabelle „Buchausleihe(1.NF)“ alle Identifikationsschlüssel.
Das Attribut P-ID kann nun nicht mehr als Identifikationsschlüssel (und somit
auch nicht mehr als Primärschlüssel) verwendet werden. Zur eindeutigen
Identifizierung benötigt man nun als Schlüssel eine Kombination von Attributen,
z.B. (P-ID, ISBN).
Du benötigst eine bessere („höhere“) Normalform. Durch diese weiteren
Normalisierungsschritte kannst du die Redundanzen wieder beseitigen.
6.5
2.Normalform
Die Tabelle ist in der 1.NF und alle Nichtschlüssel-Attribute sind voll funktional
abhängig vom Primärschlüssel.
Um die 2.Normalform verstehen zu können, muss der Begriff der vollen
funktionalen Abhängigkeit verdeutlicht werden. Hiermit wird eine spezielle
Beziehung zwischen den Attributen einer Tabelle beschrieben.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 8
Sei X eine Menge von Attributen einer Tabelle und A eine weitere Menge von
Attributen der gleichen Tabelle. A ist genau dann von X funktional abhängig,
wenn es zu beliebigen Wert von X genau einen Wert von A gibt. Wir verwenden
die Notation X " A.
Bemerkung:
Das Heft
!
Arbeitskreis „Datenbanken im Unterricht“
Datenbank
Zentralstelle für Computer im Unterricht
Augsburg 2000
ersetzt auf Seite 47 den Begriff „genau einen Wert“ durch „höchstens einen
Wert“.
Nimm als Beispiel in der Tabelle Buchausleihe(1.NF) das Attribut Name. Laut
der gegebenen Informationsstruktur besitzt jede Person genau einen
(Haupt)Wohnsitz. Also gehört zu jedem Wert von Namen genau ein Wert von
Ort.
Notiere:
Analog gilt:
Name " Ort
Name " PLZ
Name " Straße
Verkürzt kannst du schreiben: ! Name " PLZ, Ort, Straße
!
Übung 6.5:
!
Suche in der Tabelle Buchausleihe(1.NF)
alle funktionale Abhängigkeiten
!
analog obigem Beispiel (von einem einzigen Attribut funktional abhängig).
Übung 6.6:
Welches Attribut eignet sich als Identifikationsschlüssel?
Die Übung 6.6 zeigt, dass die funktionale Abhängigkeit von nur einelementigen
Attributmengen nicht ausreichend ist. Deswegen wirst du nun die funktionale
Abhängigkeit von mehrelementigen Attributmengen untersuchen:
Notiere:
Analog gilt:
Verkürzt kannst du schreiben:
Übung 6.7:
Gymnasium Königsbrunn
Name, Titel
Name, Titel
Name, Titel
……
!
Name,
! Titel
!
!
" Ausleihdatum
" ISBN
" Straße
" P-ID, PLZ, … ,Ausleihdatum
Ralph Henne
Kapitel 6
Normalisierung
Seite 9
Suche in der Tabelle Buchausleihe(1.NF) alle funktionale Abhängigkeiten
analog obigem Beispiel (von einer mehrelementigen Attributmenge funktional
abhängig).
Übung 6.8:
Welche Attributmenge eignet sich als Identifikationsschlüssel?
In der Tabelle Buchausleihe(1.NF) eignen sich nun mehrere zusammengesetzte
Attributmengen als Identifikationsschlüssel, beispielsweise der Schlüssel (P-ID,
ISBN). Bei zusammengesetzten Schlüsseln muss man den Begriff der vollen
funktionalen Abhängigkeit einführen.
Voll funktional abhängig bedeutet bei einem Mehrfeld-Schlüssel, dass ein
Nichtschlüssel-Attribut nicht bereits von einem Teil des Schlüssels funktional
abhängt.
Da du nun also einen zusammengesetzten Schlüssel hast, muss diese Tabelle auf
ihre volle funktionale Abhängigkeit überprüft werden, um sie in die
2.Normalform zu überführen. Jede Kombination aus P-ID und ISBN bestimmt
eindeutig einen Namen, eine PLZ, … , ein Ausleihdatum.
Es gelten also die funktionalen Abhängigkeiten:
(P-ID, ISBN) " Name
(P-ID, ISBN) " PLZ
…
(P-ID, ISBN) " Titel
!
(P-ID, ISBN) " Ausleihdatum
!
Verkürzt können wir schreiben:
(P-ID,
! ISBN) " Name, PLZ, Ort, Straße, Titel, Ausleihdatum
!
Allerdings
hast du bereits in Übung 6.5 festgestellt, dass Name, PLZ, Ort und
Straße funktional abhängig sind von P-ID, also von einem Teil des Schlüssels.
Ebenso
! ist der Titel funktional abhängig von ISBN, also auch von einem Teil des
Schlüssels.
P-ID " Name, PLZ, Ort, Straße
ISBN " Titel
Dies ist ein Widerspruch zur Definition der vollen funktionalen Abhängigkeit.
! Somit ist also Buchleihe (1.NF) nicht in der 2.Normalform.
!
Bemerkung:
Tabellen mit nur einem Schlüsselattribut sind automatisch in der 2.Normalform.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 10
Um also eine Tabelle in die 2.Normalform zu überführen, entfernst du zunächst
alle Attribute, die vom Primärschlüssel nur partiell abhängen. Mit diesem
Attributen und denen, von denen sie abhängen, als Primärschlüssel bildest du
eine neue Tabelle. Die Attribute, aus denen du den Primärschlüssel der neuen
Tabelle machst, verbleiben auch in der alten Tabelle als Teile des dortigen
zusammengesetzten Primärschlüssels.
Dieses oben dargestellte „Rezept“ zur Überführung in die 2. Normalform werde
ich mit Hilfe der Tabelle Ausleihe(1.NF) verdeutlichen. Diese Tabelle stellt
einen Teil des Beispiels der Buchausleihe dar.
Ausleihe (1.NF)
PersonNr ISBN
1
3-431-02644
1
3-7627-3733-9
2
3-7627-3737-1
2
3-7627-3737-5
2
3-431-02962-0
3
3-7627-3733-9
3
3-431-02962-0
4
3-431-02644
Titel
Algebra7
Geometrie8
Geometrie10
Geometrie9
Algebra9
Geometrie8
Algebra9
Algebra7
Ausleihdatum
21.03.2003
21.03.2003
20.04.2003
20.04.2003
24.07.2003
26.07.2003
30.10.2003
29.09.2003
Abbildung 6.6: Tabelle Ausleihe (1.NF)
In dieser Tabelle kann kein einzelnes Attribut als Schlüsselattribut verwendet
werden, da kein Attribut jeden Datensatz eindeutig identifiziert.
Erst eine Kombination aus PersonNr und z.B. ISBN identifiziert einen Datensatz
eindeutig und kann als Primärschlüssel dienen.
Eine weitere Untersuchung ergibt jedoch, dass der Titel nicht voll funktional
vom Primärschlüssel (PersonNr, ISBN) abhängt. Der Titel hängt nur von der
ISBN abhängt, also nur von einem Teil des Primärschlüssels. Ändert sich also
nur die PersonNr, dann ändert sich nicht der Titel, wie man am Buch
Geometrie8 sehen kann. Somit verstößt diese Tabelle gegen die 2.Normalform.
Übung 6.9:
Untersuche das Attribut Ausleihdatum.
Gemäß dem obigen Rezept entfernst du das Attribut Titel aus der Tabelle
Ausleihe und bildest zusammen mit dem Attribut ISBN als Primärschlüssel eine
neue Tabelle Buch. Die ISBN verbleibt auch in der alten Tabelle als Teil des
dortigen zusammengesetzten Primärschlüssels.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Ausleihe (2.NF)
PersonNr ISBN
1
3-431-02644
1
3-7627-3733-9
2
3-7627-3737-1
…
…
Buch (2.NF)
ISBN
3-431-02644
3-7627-3733-9
3-7627-3737-1
…
Seite 11
Ausleihdatum
21.03.2003
21.03.2003
20.04.2003
…
Titel
Algebra7
Geometrie7
Geometrie10
…
Abbildung 6.7: Überführung der Tabelle Ausleihe in die 2.Normalform
Man kann dieses Beispiel in die 2.Normalform überführen, indem man die
ursprüngliche Tabelle in zwei neue Tabellen aufspaltet.
Bemerkung:
Das Attribut ISBN ist in der Tabelle Buch (2.NF) der Primärschlüssel und in der
Tabelle Ausleihe (2.NF) der zugehörige Fremdschlüssel.
Übung 6.10:
Die Tabelle Buchausleihe (1.NF) besitzt (wie bereits beschrieben) den
zusammengesetzten Primärschlüssel (P-ID, ISBN). Untersuche, welche weiteren
Attribute nicht voll funktional vom Primärschlüssels abhängen.
Diese Überlegungen kannst du nun auf die Tabelle Buchausleihe (1. NF)
anwenden:
Person (2.NF)
P-ID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
PLZ
86343
86399
86343
86343
Ort
Königsbrunn
Bobingen
Königsbrunn
Königsbrunn
Ausleihe (2.NF)
P-Nr
ISBN
1
3-431-02644
1
3-7627-3733-9
Gymnasium Königsbrunn
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring49
Ausleihdatum
21.03.2003
21.03.2003
Ralph Henne
Kapitel 6
Normalisierung
2
2
2
3
3
4
3-7627-3737-1
3-7627-3735-5
3-431-02962-0
3-7627-3733-9
3-431-02962-0
3-431-02644
Buch (2.NF)
ISBN
3-431-02644
3-7627-3733-9
3-7627-3737-1
3-7627-3735-5
3-431-02962-0
Seite 12
20.04.2003
24.07.2003
24.07.2003
26.07.2003
30.10.2003
29.09.2003
Titel
Algebra7
Geometrie8
Geometrie10
Geometrie9
Algebra9
Abbildung 6.8: Datenbank Buchausleihe in der 2.Normalform
Beachte, dass sich die Redundanzen in den Tabellen Person und Buch aufgelöst
haben.
Bemerkung:
Das Attribut P-ID ist in der Tabelle Person (2.NF) der Primärschlüssel und in
der Tabelle Ausleihe (2.NF) ist P-Nr der zugehörige Fremdschlüssel.
6.6
3.Normalform
Die Tabelle ist in der 2. NF und alle Nichtschlüssel-Attribute dürfen nicht
transitiv von einem Schlüssel abhängen.
Um die 3.Normalform verstehen zu können, muss der Begriff der transitiven
Abhängigkeit verdeutlicht werden. Im Prinzip wird mit der 3.Normalform
erreicht, dass die funktionale Abhängigkeit auch zwischen NichtschlüsselAttributen beseitigt wird.
Wenn Attribut B von A funktional abhängt und Attribut C von B funktional
abhängt, so heißt C transitiv abhängig von A (falls nicht A von B abhängt).
/ A)
Kurz bedeutet transitiv: A " B " C (wobei B "
Bemerkung:
Matzke gibt in seinem
Skript
S. 17 folgende Definition:
!
!
!
Eine Tabelle befindet sich in der 3.Normalform, wenn (sie in der 2.Normalform
ist und) alle Nichtschlüssel-Attribute voneinander funktional unabhängig sind.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 13
Jedoch zeigt die Übung 6.12 ein Beispiel, in dem trotz der funktionalen
Abhängigkeit von Nichtschlüssel-Attributen die 3.Normalform nicht verletzt
wird.
Wenn du eine transitive Abhängigkeit entdeckst, musst du die betroffenen
Nichtschlüssel-Attribute entfernen. Dann erzeugst du eine neue Tabelle aus
diesen entfernten Nichtschlüssel-Attributen und demjenigen NichtschlüsselAttribut, von dem diese entfernten Nichtschlüssel-Attribute funktional
abhängen. Letzteres wird der Primärschlüssel der neuen Tabelle und verbleibt
als Fremdschlüssel in der ursprünglichen Tabelle.
Dieses oben dargestellte „Rezept“ zur Überführung in die 3. Normalform werde
ich mit Hilfe der Tabelle Person (2.NF) verdeutlichen. Diese Tabelle stellt
einen Teil des Beispiels der Buchausleihe dar.
Person (2.NF)
P-ID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
PLZ
86343
86399
86343
86343
Ort
Königsbrunn
Bobingen
Königsbrunn
Königsbrunn
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring49
Abbildung 6.9: Tabelle Person (2.NF)
Als Primärschlüssel wurde das Attribut P-ID gewählt. Nun gelten folgende
funktionale Abhängigkeiten:
P-ID " Name, PLZ, Ort, Straße
PLZ " Ort
Du entdeckst in dieser Tabelle also die transitive Abhängigkeit:
! P-ID " PLZ " Ort , wobei PLZ "
/ P-ID
!
Bemerkung:
Die funktionale Abhängigkeit PLZ " Ort ist in dieser Tabelle sicherlich
!
!
! angemerkt, keine weiteren Datensätze hinzufügen
richtig, wenn man, wie bereits
will. Untersucht man jedoch das Postleitzahlenbuch, so stellt man fest, dass hier
die funktionale Abhängigkeit (Ort, Straße) " PLZ gilt. Man sollte bei der
!
Normalisierung immer die künftig möglichen Attributwerte im Auge haben.
Die Tabelle Person (2.NF) befindet sich also noch nicht in der 3.Normalform,
!
denn Ort hängt transitiv von P-ID ab.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 14
Gemäß dem obigen Rezept entfernst du das betroffene Attribut Ort aus der
Tabelle Person (2.NF). Aus diesem entfernten Nichtschlüssel-Attribut Ort und
aus dem Nichtschlüssel-Attribut PLZ, von dem Ort funktional abhängt, bildest
du eine neue Tabelle Ort (3.NF). PLZ ist in dieser neuen Tabelle der
Primärschlüssel und verbleibt als Fremdschlüssel in der ursprünglichen Tabelle,
die ich nun mit Person (3.NF) bezeichne.
Person (3.NF)
P-ID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring49
PLZ
863431
86399
86343
86343
Ort (3.NF)
PLZ
Ort
86343
Königsbrunn
86399
Bobingen
Abbildung 6.10: Überführung der Tabelle Person (2.NF) in die 3.Normalform
Da Ausleihe (2.NF) und Buch (2.NF) jeweils nur eine Nicht-Schlüsselspalte
besitzen, sind diese automatisch in der 3.Normalform. Somit ist die Datenbank
Buchausleihe komplett in die 3.Normalform übergeführt worden:
Person (3.NF)
P-ID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring49
PLZ
863431
86399
86343
86343
Ort (3.NF)
PLZ
Ort
86343
Königsbrunn
86399
Bobingen
Ausleihe (3.NF)
ISBN
3-431-02644
3-7627-3733-9
3-7627-3737-1
3-7627-3735-5
3-431-02962-0
3-7627-3733-9
3-431-02962-0
Gymnasium Königsbrunn
PersonNr
1
1
2
2
2
3
3
Ausleihdatum
21.03.2003
21.03.2003
20.04.2003
24.07.2003
24.07.2003
26.07.2003
30.10.2003
Ralph Henne
Kapitel 6
Normalisierung
3-431-02644
4
Buch (3.NF)
ISBN
3-431-02644
3-7627-3733-9
3-7627-3737-1
3-7627-3735-5
3-431-02962-0
Seite 15
29.09.2003
Titel
Algebra7
Geometrie8
Geometrie10
Geometrie9
Algebra9
Abbildung 6.11: Datenbank Buchausleihe in der 3.Normalform
Bemerkungen
1.
Die Beziehung zwischen Ort(3.NF) und Person(3.NF) wird über das
Attribut PLZ hergestellt. Das bisheriges Vorgehen war, dass in der Tabelle
ein zusätzliches Attribut OrtID als Primärschlüssel eingeführt wurde, das
mit dem Fremdschlüssel OrtNr (anstelle PLZ) verknüpft war:
Person
P-ID Name
1
Vierling
2
Geiger
3
McRae
4
Hackl
Straße
Röntgenstr. 26
Lechallee 8
Alpenstr. 15
Edelweißring49
OrtNr
1
2
1
1
Ort
OrtID
1
2
PLZ
86343
86399
Ort
Königsbrunn
Bobingen
2.
Nach dem bisherigen Vorgehen sollte in der Tabelle Buch der
Primärschlüssel BuchID heißen. Die ISBN kann jedoch auch als
Primärschlüssel verwendet werden, da jedes Buch seine separate Nummer
erhält. Jedoch ist der Datentyp in diesem Fall nicht mehr AutoWert. Man
muss also selbst aufpassen, dass aus Versehen keine doppelten ISBN
eingegeben werden.
3.
Zum Glück wird dir in ACCESS diese Aufgabe abgenommen. Wenn du in
Access ein Attribut als Schlüssel festlegst, weigert sich es sich, wenn Du
zweimal den gleichen Wert eingibst.
Falls man sich in anderen Datenbanken dessen nicht sicher ist, sollte man
einen AutoWert BuchID als Primärschlüssel verwenden.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 16
Die Ausgangstabelle
Buchausleihe (Rohdaten)
PersonID Name
Adresse
1
Vierling Röntgenstr. 26
86343
Königsbrunn
2
Geiger
Lechallee 8
86399
Bobingen
3
McRae
Alpenstr. 15
86343
Königsbrunn
4
Hackl
Edelweißring 49
86343
Königsbrunn
ISBN
3-431-02644
3-7627-3733-9
Titel
Algebra7
Geometrie8
Ausleihdatum
21.03.2003
21.03.2003
3-7627-3737-1
3-7627-3735-5
3-431-02962-0
3-7627-3733-9
3-431-02962-0
Geometrie10
Geometrie9
Algebra9
Geometrie8
Algebra9
20.04.2003
24.07.2003
24.07.2003
26.07.2003
30.10.2003
3-431-02644
Algebra7
29.09.2003
Abbildung 6.12: Datenbank Buchausleihe (Rohdaten)
wurde mit Hilfe der drei Normalisierungsformen in die vier Tabellen Person,
Ort, Ausleihe und Buch übergeführt. Dadurch hast du nun einen
Ausleihvorgang erzeugt, der auch durch folgendes Klassendiagramm
beschrieben werden kann.
Abbildung 6.13: Klassendiagramm der Datenbank Buchausleihe
Hinweis:
Matzke schreibt auf S.15:
Bei einem sauberen Entwurf nach dem Klassenmodell und Anwendung der
Abbildungsregeln befinden sich die erzeugten Tabellen häufig schon in der
3 Normalform.
Jedoch sind Klassendiagramme leichter und intuitiver zu handhaben als die
Normalformen. Deswegen wirst du Tabellen in Datenbanken hauptsächlich mit
Hilfe von Klassendiagrammen erstellen. Du solltest jedoch immer wieder
überprüfen, ob die Tabellen den Normalformen genügen.
Übung 6.11:
Welcher Normalform widerspricht die Speicherung von Geburtsdatum und Alter
von Personen?
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 17
Übung 6.12:
Überprüfe in beiden Tabellen auf 3.NF. In beiden Tabellen gibt es eine
Abhängigkeit von Nichtschlüssel-Attributen. Aber nur in einer liegt eine
transitive Abhängigkeit vor, so dass man diese Tabelle aufspalten muss.
Fahrzeug
Kennzeichen
A-CW27
A-BM2332
A-XY1010
A-OH234
A-BE303
Fabrikat
Golf III
Sprinter
Golf III
Astra
Golf IV
Fahrzeug
Kennzeichen
A-CW27
A-BM2332
A-XY1010
A-OH234
A-BE303
FahrgestNr
W…2196
W…9270
W…8244
W…3062
W…1196
Hersteller
VW
DaimlerChrysler
VW
Opel
VW
Hersteller
VW
DaimlerChrysler
VW
Opel
VW
Übung 6.13:
Wende auf die Rohdaten der Tabelle SpieltIn (Auszug einer Datenbank eines
Sportvereins) die Regeln der Normalisierung. Überführe diese Tabellen
a)
b)
c)
in die 1.Normalform
in die 2.Normalform
in die 3.Normalform
SpieltIn
MitgliedNr
1
3
6
4
MannschNr
1
3
1
1
SeitWann
04.05.2001
11.07.1999
12.01.2000
08.11.2000
Alter
23
19
22
22
Funktion
Sturm
Verteidigung
Sturm
Torwart
Die Tabelle SpieltIn des Sportvereins hat einen kombinierten Schlüssel aus
MitgliedNr und MannschaftNr. Der Verein möchte auch festhalten, seit wann
ein Mitglied in einer Mannschaft spielt, wie alt es ist und welche Funktion es in
der Mannschaft hat. Teste oben stehende Tabelle auf die Normalformen.
Gymnasium Königsbrunn
Ralph Henne
Kapitel 6
Normalisierung
Seite 18
Übung 6.14
Nimm deinen Stundenplan und übertrage ihn in eine datenbanktaugliche
Tabelle. In dieser Datenbank sollen die folgenden Daten erfasst werden:
Fach, Lehrer, (Wochen)Tag, Stunde, Beginn (der Stunde).
Die drei NF sollen erfüllt sein! Zeichne ein Klassendiagramm.
Übung 6.15:
Wende auf die Rohdaten eines Sportvereins die Regeln der Normalisierung.
Überführe diese Tabelle
a)
b)
c)
in die 1.Normalform
in die 2.Normalform
in die 3.Normalform
und erstelle hinterher ein Klassendiagramm.
Sportverein (Rohdaten)
M-Nr
Name
1
Heinz Gruber
Ge
m
2
Udo Meier
m
3
Frieda Baum
w
4
Ali Berg
m
Adresse
Hauptstraße 7,
86343 Königsbrunn
Bierweg 35,
86199 Augsburg
Lerchenweg 4,
86161 Augsburg
Kirchgasse 7,
86343 Königsbrunn
Sportarten(Beitrag)
F(50.-) H(40.-)
F(50.-) L(55.-)
L(55.-)
F(50.-) V(40.-)
Übung 6.16:
Wende auf die Rohdaten einer Volkshochschule die Regeln der Normalisierung.
Überführe diese Tabelle
a)
b)
c)
in die 1.Normalform
in die 2.Normalform
in die 3.Normalform
und erstelle hinterher ein Klassendiagramm.
Volkshochschule(Rohdaten)
KursNr
Kurs
Dozent
VSF217
Französisch1 LeClerc
Tel
08212345
VSF218
08212345
Französisch2
Gymnasium Königsbrunn
LeClerc
Tag
Mo
Do
Di
Di
Zeit
19:00
19:00
17:00
18:00
Raum
E12
138
E12
E12
Ralph Henne
Kapitel 6
Normalisierung
VSF219
VSS13
VSS14
VSS15
Französisch3
Spanisch1
Spanisch2
Spanisch3
Simpson
Rodriguez
Rodriguez
Lopez
08213451
08213541
08213541
08215544
Seite 19
Di
Mo
Mo
Mi
19:00
17:00
19:00
17:00
138
139
139
E12
Übung 6.17:
Wende auf den folgenden Auszug eines Chemikalien-Katalogs die Regeln der
Normalisierung. Überführe diese Tabelle
a)
b)
c)
in die 1.Normalform
in die 2.Normalform
in die 3.Normalform
und erstelle hinterher ein Klassendiagramm.
Chemikalien (Auszug aus einem Katalog)
FN
Formel
MG
Bezeichnung
12540 C6H6
78.1147 Benzene, puriss.
BRN
969212
12549
C6H6
78.1147
Benzene, for HPLC
969212
12550
C6H6
78.1147
Benzene, ACS
969212
12560
C6H6
78.1147
Benzene, purum
969212
82700
C5H5N
79.1023
Pyridine, for Uvspectroscopy
103233
82701
56380
C5H5N
C5H5NO
79.1023
95.1017
Pyridine, for sequence analysis
2-Hydroxypyridine, pract.
103233
105757
56390
C5H5NO
95.1017
3-Hydroxypyridine, purum
105699
56400
C5H5NO
95.1017
4-Hydroxypyridine, techn.
105800
82811
C5H5NO
95.1017
Pyridine-N-oxide, pract.
105257
Gymnasium Königsbrunn
Preise
5,0ml:48,50;
10ml:83,30
1,0l:48,50;
2,5l:101,50
500ml:24,70;
1,0l:48,20;
2,5l:103,40
1,0l:23,60;
2,5l:42,50
250ml:92,10;
1,0l:343,40
250ml:177,90
100g:62,30;
500g:289,10
25g:17,80;
100g:60,20
25g:35,60;
100g:130,90
100g:37,90;
500g:159,00
Ralph Henne