ERD-Entwurf und Normalisierung

Entity-Relationship-Diagramm-Entwurf
und Normalisierung
Holger Jakobs [email protected]
2004-07-21
Inhaltsverzeichnis
1 Aufgabe des Entity-Relationship-Modells
1
2 Grundbegriffe des ER-Modells
2.1 Entity . . . . . . . . . . . .
2.2 Abhängiges Entity . . . . .
2.3 Schwaches Entity . . . . . .
2.4 Entity Type und Entity Set
2.5 Attribut . . . . . . . . . . .
2.6 Beziehung . . . . . . . . . .
2.7 Beziehungstyp . . . . . . . .
2.8 Integritätsregeln . . . . . . .
2.9 Schlüssel . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
1
1
2
2
3
3
3
4
4
5
3 ER-Diagramme (Chen)
3.1 1:N-Beziehung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 M:N-Beziehung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3 Notationsvarianten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6
7
7
8
4 Umsetzung ERD in Relationen
4.1 Nicht optimierte Relationen . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2 Optimierung der Relationen . . . . . . . . . . . . . . . . . . . . . . . . . .
8
9
9
5 Relationen → Generatordatei
5.1 Primärschlüssel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2 Fremdschlüssel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.3 Weitere Integritätsbedingungen . . . . . . . . . . . . . . . . . . . . . . . .
10
10
10
10
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
2 GRUNDBEGRIFFE DES ER-MODELLS
6 Normalisierung
6.1 Die 1. Normalform – 1NF . . . . . . .
6.2 Die 2. Normalform – 2NF . . . . . . .
6.3 Die 3. Normalform – 3NF . . . . . . .
6.4 Die Boyce-Codd-Normalform – BCNF
6.5 Die 4. Normalform – 4NF . . . . . . .
6.6 Die 5. Normalform — 5NF . . . . . . .
6.7 Hinweise . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
11
11
12
13
14
15
15
16
1 Aufgabe des Entity-Relationship-Modells
Ziel des Entity-Relationship-Modells (ERM) ist es, die zu speichernden Daten und ihre Beziehung untereinander zu beschreiben, was mit Hilfe eines Entity-Relationship-Diagramms
(ERD) geschieht. Die hierzu notwendige Analyse erfolgt aus fachlogischer Sicht, d. h. es ist
mindestens so viel Fachkenntnis über den Anwendungsbereich notwendig wie Informatikwissen. In den Übungsaufgaben wird die Analyse ersetzt durch eine Beschreibung des zu
erstellenden ERMs.
Ergebnis ist ein konzeptionelles Modell, das anschließend umgesetzt wird in Relationenentwürfe. Aus diesen wiederum erzeugt man – im Falle einer SQL-Datenbank – SQLKommandos create table ..., um die passenden Tabellen anzulegen, in denen die Daten
gespeichert werden sollen.
2 Grundbegriffe des ER-Modells
2.1 Entity
Ein Entity (manchmal auf Deutsch auch als Entität bezeichnet) ist eine eigenständige
Einheit, die durch Eigenschaften beschrieben und im Rahmen des betrachteten Modells
identifiziert werden kann. Das Identifizierungsmerkmal wird als Schlüssel bezeichnet. Gibt
es mehrere mögliche Identifizierungsmerkmale, so bezeichnet man alle diese als Schlüsselkandidaten. Einen davon wählt man als Schlüssel, der möglichst kurz sein sollte. Bietet
sich kein geeigneter Schlüsselkandidat an, so erfindet man einen künstlichen Schlüssel“,
”
i. a. in Form einer laufenden Nummer (z. B. Kundennummer).
Der Begriff Entity im Sinne des ERMs ist synonym mit dem Begriff Objekt im Sinne
der objektorientierten Analyse. Ein Entity kann z. B. sein:
• ein Gegenstand (z. B. ein bestimmtes Kraftfahrzeug)
• eine Person (z. B. ein Kunde oder ein Lieferant)
• ein Artikel
• ein Vertrag
2
2 GRUNDBEGRIFFE DES ER-MODELLS
2.2 Abhängiges Entity
• ein Ereignis
Im relationalen Modell (im Gegensatz zum objektorientierten) wird ein Entity durch eine
Menge von Eigenschaften (Attributen) beschrieben und identifiziert. Eine Eigenschaft hat
einen Bezeichner (Attributname, z. B. Wohnort) und einen Wert (Attributwert, z. B. Köln).
Neue Entities können grundsätzlich beliebig erzeugt werden, wobei auf die Eindeutigkeit
des Schlüssels geachtet werden muss. Die Eigenschaften eines Entities können nachträglich
geändert werden, wobei die Änderung des Schlüssels nicht immer erlaubt ist. Ein Entity
kann gelöscht werden, sofern keine besonderen Restriktionen dies verbieten.
2.2 Abhängiges Entity
Ein Entity heißt abhängig, wenn die Existenz in dem betrachteten Modell von einem anderen Entity abhängt. Beim Löschen eines Entities sind die von ihm abhängigen Entities
ebenfalls zu löschen, oder aber das Löschen muss verhindert werden. Das Entity, von dem
andere Entities abhängig sind, heißt Eltern-Entity. Beispiele:
• Positionen einer Bestellung, die entfallen, wenn die Bestellung storniert wird.
• Mehrwertsteuersatz, der existieren muss, wenn es Artikel mit diesem Steuersatz gibt.
Das Löschen des Mehrwertsteuersatzes ist verboten, solange noch zugehörige Artikel
existieren.
• Strafen eines Spielers in einem Sportverein sind von dem Spieler abhängig und können
ohne ihn nicht existieren.
2.3 Schwaches Entity
Eine weitere Untergruppe von Entities ist die der schwachen Entities. Diese treten immer dann auf, wenn es Teilmengenbeziehungen gibt, oder in einem Entity eine Art Typ
beschrieben wird, von dem es dann mehrere Exemplare gibt. Hauptmerkmal ist, dass der
Schlüssel eines Entity-Typen auch Schlüssel oder Schlüsselteil eines anderen (des schwachen) Entities ist, weil es nicht allein durch seine eigenen Attributwerte identifiziert werden
kann.
Ein Beispiel hierfür ist Buchtitel, dem das Buchexemplar als schwaches Entity untergeordnet ist. Das Buchexemplar wird identifiziert durch den Schlüssel des Buchtitels und
hat darüber hinaus noch einen weiteren Schlüsselbestandteil, z. B. eine laufende Nummer.
Von einem Titel hat eine größere Bücherei sicherlich mehrere Exemplare, die aber alle eine
andere laufende Nummer haben, vielleicht in verschiedenen Zweigstellen stehen oder von
verschiedenen Büchereibenutzern ausgeliehen sind, weshalb die ledigliche Speicherung der
Anzahl der Exemplare nicht genügt.
Ein weiteres Beispiel ist Niederlassung eines Kunden, die ohne das passende Entity Kunde
nicht existieren kann und die Kundennummer zu Identifikation benötigt. Es kann sich z. B.
um eine Lieferanschrift handeln.
3
2.4 Entity Type und Entity Set
2 GRUNDBEGRIFFE DES ER-MODELLS
Noch ein Beispiel ist Angestellter, dem Manager als schwaches Entity untergeordnet ist.
Hier herrscht eine Teilmengenbeziehung. Die Beziehung ist eine sogenannte is-a-Beziehung,
denn jeder Manager ist ein Angestellter (hat aber weitere Eigenschaften, z. B. eine Beziehung zu der Abteilung, die er leitet), aber nicht jeder Angestellter ist ein Manager. Der
Schlüssel für beide Arten von Entities ist gleich, aber die Eigenschaften sind verschieden.
2.4 Entity Type und Entity Set
Ein Entity Type ist eine abstrakte Zusammenfassung von Entities, die in dem betrachteten
Modell durch dieselben Attribute beschrieben werden. Jedes mögliche Entity ist durch einen
Entity Type beschreibbar, gehört also zu einer ganz bestimmten Menge von Entities (Entity
Set).
Ein Entity Set sind alle Entities, die zu einem bestimmten Zeitpunkt vom selben Entity
Type in einer Datenbank vorhanden sind, z. B. alle Artikel oder alle Kunden.
Die Entity Types sollten über längere Zeit gleich bleiben, während sich die einzelnen
Entities ständig ändern. Die Entity Types sind sozusagen der Rahmen, in dem sich die Änderungen bewegen. Vergleichbar sind Entity Types mit den Beschreibungen von Stukturen
(struct) in C bzw. Klassen (class) in C++ oder Java.
2.5 Attribut
Eigenschaften von Entities werden durch Attribute beschrieben. Jedes Entity hat für alle
seine Attribute je einen bestimmten Attributwert (oder den speziellen Wert NULL, der
besagt, dass der Wert entweder nicht vorhanden oder unbekannt ist).
Ein Attribut ist von einem bestimmten Datentyp, der in diesem Zusammenhang als
Domain bezeichnet wird. Dies beschreibt den zulässigen Wertebereich.
Entities beschreiben immer nur einen kleinen Ausschnitt aus der Realität, weshalb es
wichtig ist, festzulegen, welche Attribute in unserem Modell interessieren. Die Attribute, die
der Arbeitgeber über eine Person speichert, sind sicherlich andere als die, die der Zahnarzt
oder die Bank über dieselbe Person gespeichert hat. Sicherlich gibt es Überschneidungen –
Widersprüche sollten nicht vorkommen –, aber es interessieren in einem bestimmten Modell
eben andere Eigenschaften als in einem anderen. Daher ist ein Entity auch nicht identisch
mit dem Gegenstand der Realität, den es (teilweise) beschreibt.
2.6 Beziehung
Entities stehen teilweise zueinander in Beziehung, auch Relationship genannt. Diese Beziehungen sind aber nicht willkürlich und kreuz und quer, sondern bestehen zwischen ganz
bestimmten Entity-Types. Die meisten Beziehungen sind zweistellig, beziehen also genau
zwei Entities ein. Innerhalb einer Beziehung spielen die beiden (oder ggf. auch mehr) Entities jeweils eine bestimmte Rolle. Oft ist die Angabe der Rolle entbehrlich, weil dies
unmittelbar einleuchtet.
4
2 GRUNDBEGRIFFE DES ER-MODELLS
2.7 Beziehungstyp
Beziehungen kann es auch zwischen Entities desselben Entity Sets geben, beispielsweise
zwischen Mitarbeitern. Solche Beziehungen nennt man rekursive Beziehungen. Hierbei ist
die Angabe der Rolle oft notwendig, denn ob eine Person einer anderen vorgesetzt ist oder
umgekehrt, ist nicht unmittelbar klar, aber von großer Wichtigkeit. Bei der Beziehung
ist befreundet mit“ sollte das wiederum eher unwichtig sein. Ebenfalls rekursiv sind die
”
Beziehungen ist Vorgesetzter von (zwischen Mitarbeitern), zitiert (zwischen Büchern) und
ist Teil von (zwischen Bauteilen/-gruppen).
Manchmal gibt es sogar ganz besondere Einschränkungen für Beziehungen. Beispielsweise
kann (und muss) eine Person nur je genau eine männliche und eine weibliche Person haben,
die zu ihr die Beziehung ist Elternteil von“ haben.
”
Mehrstellige Beziehungen sind beispielsweise gegeben, wenn ein Mitarbeiter eine Schulung besucht und die Kosten einem bestimmten Projekt zugeordnet werden. Es sind drei
Entities beteiligt: Mitarbeiter, Schulung und Projekt. So etwas wird auch als BeziehungsBeziehung abgebildet, d. h. der Mitarbeiter hat eine Beziehung nimmt teil“ zum Kurs.
”
Diese Beziehung stellt wiederum das Entity Kursteilnahme dar, das eine Beziehung wird
”
bezahlt von“ zum Projekt hat.
Ob man Beziehungen in Aktiv- oder Passiv-Form darstellt, hängt von der Betrachtungsweise ab und ist in der Regel egal. Nur muss man in uneindeutigen Fällen auf die Rollenbezeichnungen achten, so dass keine Missverständnisse auftreten können.
Auch Beziehungen können Eigenschaften haben. So kann bei verheiratet mit das Hochzeitsdatum gespeichert werden. Würde man es beim Ehemann und der Ehefrau separat
speichern, so hätte man es mit Redundanz zu tun, denn derselbe Wert wäre doppelt gespeichert. Bei einer Fehleingabe wäre es also möglich, dass dort verschiedene Werte stehen.
Aus diesem Grunde scheut der Informatiker die Redundanz wie der Teufel das Weihwasser.
Natürlich können Daten immer noch falsch sein, aber zumindest nicht widersprüchlich.
2.7 Beziehungstyp
Beziehungstypen, auch Relationship Type genannt, beschreiben eine Klasse von Beziehungen, in denen jeweils dieselben Rollen (und ggf. dieselben Attribute) vorkommen.
Es handelt sich also um eine abstrakte Zusammenfassung von vielen, gleichartigen Beziehungen. Im ERD werden also Beziehungstypen dargestellt, aber man spricht oft nur von
Beziehungen (Relationships).
2.8 Integritätsregeln
Neben der reinen Beschreibung von Daten enthält ein Datenmodell auch Integritätsregeln.
Diese bestehen aus der Festlegung von:
• Schlüsseln
• anderen, eindeutigen Attributkombinationen (Schlüsselkandidaten, Alternate Keys)
• Muss-Beziehungen
5
2.9 Schlüssel
2 GRUNDBEGRIFFE DES ER-MODELLS
• Wertebereichen für die einzelnen Attribute (z. B. nicht-negativ)
• Einschränkungen von Attributwerten in Abhängigkeit von anderen Attributwerten
(z. B. Eintrittsjahr in den Verein muss größer oder gleich dem Geburtsjahr sein, oder
Einkaufspreis muss kleiner sein als der Verkaufspreis)
• Einschränkungen von Beziehungskardinalitäten in Abhängigkeit von Attributwerten
(z. B. darf ein Kurs nur so viele Teilnehmer aufnehmen wie der zugeordnete Raum
Plätze hat, oder die Anzahl der Patienten in einem Krankenzimmer darf nicht größer
sein als die Anzahl der Betten)
Darüber hinaus kann es noch ganz spezielle Integritätsregeln geben, die man keiner der
obigen Kategorien direkt zuordnen kann. Diese hängen von der fachlogischen Sicht ab und
müssen zunächst natürlichsprachlich formuliert werden.
2.9 Schlüssel
Jedes Entity muss durch einen Schlüssel eindeutig identifiziert werden. Daher ist der Schlüssel aus den Attributen entsprechend zu wählen. Sollte sich kein passender, garantiert eindeutiger Schlüssel finden lassen, so erfindet man einen künstlichen“ Schlüssel – im allge”
meinen eine laufende Nummer. Hierbei ist auf folgende Kriterien zu achten:
• Eindeutigkeit
• genügender Umfang, d. h. für jedes neue Entity muss ein neuer Schlüssel gefunden
werden können, weil er direkt bei Aufnahme in die Datenbank mitgegeben werden
muss
• Kürze, denn der Schlüssel kann nach Umsetzung des Entwurfs in eine relationale
Datenbank auch in vielen anderen Tabellen der Datenbank vorkommen, dort als
Fremdschlüssel (siehe Kapitel 5.2 auf Seite 10)
Falls ein Schlüssel über diese Eigenschaften hinaus auch noch sprechend oder klassifizierend
sein kann, umso besser. Die obigen Kritieren haben aber stärkeres Gewicht. Für Schlüssel
gilt das Minimalprinzip, d. h. der Schlüssel darf nicht mehr Attribute haben als unbedingt
notwendig. Beispiele für Schlüssel:
• Kfz-Kennzeichen (ggf. einschließlich Länderkennzeichen)
• Fahrgestellnummer eines Kraftfahrzeug
• Signatur eines Buches in einer Bibliothek
• ISBN (International Standard Book Number) eines Buchtitels
• EAN (Europäische Artikelnummer), der Barcode auf Lebensmittelverpackungen
6
3 ER-DIAGRAMME (CHEN)
• Personalausweisnummer (innerhalb eines Landes eindeutig)
• Sozialversicherungsnummer
• Telefonnummer (einschließlich Länder- und Ortsvorwahl)
• E-Mailadresse
• MAC-Adresse einer Ethernet-Netzwerkkarte
3 Entity-Relationship-Diagramme (nach Chen)
Chen hat Mitte der 70er Jahre nicht nur die Begriffe des Entity-Relationship-Modells eingeführt, sondern auch eine grafische Darstellung entworfen. Diese ist inzwischen auf vielfältige
Weise geändert und ergänzt worden, teilweise auf widersprüchliche Art. Eine wichtige Ergänzung ist die genauere Angabe der Kardinalität von Beziehungen, die Chen nur zwischen
0 und N unterschieden hat. Besser ist es, anzugeben, wieviele Beziehungen mindestens vorhanden sein müssen (meist 0 oder 1), und wieviele höchstens vorhanden sein können (meist
1 oder N, d. h. beliebig viele).
Falls notwendig kann man auch eine Zahl angeben, die größer als 1 ist, aber dennoch
nicht beliebig hoch sein darf. Gelegentlich kommt es auch vor, dass die Anzahl Beziehungen
von einem Attributwert abhängt, aber so etwas ist nicht direkt im Diagramm darstellbar,
sondern gehört in den Bereich der Nebenbedingungen, auch Integritätsregeln genannt (siehe
Kapitel 2.8 auf Seite 4).
3 Attribute
Entity-Relationship-Diagramme bestehen aus Entities (eigentlich Entity Sets oder Entity Types), die als Rechtecke dargestellt werden und den
Namen des Entities im Rechteck tragen. Um das Rechteck herum werden
Ellipsen gruppiert, die für je ein Attribut stehen. Das Attribut bzw. die
Entity−Type
Attribute, die den Schlüssel bilden, sind unterstrichen.
Die Ellipsen für Schlüsselattribute von schwachen Entities, die vom
Eltern-Entity übernommen wurden, werden gestrichelt gezeichnet, um
sie entsprechend zu kennzeichnen.
Relationship−Type
Für Entities sollte man als Namen Substantive im Singular verwenden, z. B. Kunde,
Mitarbeiter, Buch.
Zwischen den einzelnen Entities bestehen Beziehungen (eigentlich Relationship Sets oder
Relationship Types), die durch Rauten dargestellt sind. Es gibt Verbindungslinien zwischen
Rechtecken und Rauten, d. h. niemals direkt zwischen Rechtecken. An den Verbindungslinien sind Zahlen angebracht, die die Kardinalität der Beziehung darstellen. Oft ist es
statt einer Zahl (1 oder N) die genaue Kardinalitätsangabe mit einer Mindest- und einer
Höchstangabe, durch Komma getrennt.
Für Beziehungen sollte man Verben in der dritten Person Singular Präsens verwenden,
z. B. bucht, ist Vorgesetzter von, leitet.
Im ERD-Beispiel (siehe Abbildung 1) ist beschrieben, dass es Artikel, Mehrwertsteuersätze und Lieferanten gibt.
7
3.1 1:N-Beziehung
3 ER-DIAGRAMME (CHEN)
Bezeich
ANR
VKPreis
Artikel
LNR
Gewicht
liefert
0,N
Ort
Lieferant
0,M
0,N
Liefer−
zeit
EK−Preis
Telefon
unterliegt
1,1
Prozent
MWStSatz
MNR
Abbildung 1: Beispiel für ein Entity-Relationship-Diagramm
Der Entity-Type Artikel hat vier Attribute, von denen die Artikelnummer (ANR) der
Schlüssel ist. Die übrigen sind beschreibende Attribute, aber keine identifizierenden.
Der Entity-Type Lieferant hat drei Attribute, von denen die Lieferantennummer (LNR)
der Schlüssel ist. Die übrigen sind beschreibende Attribute, aber keine identifizierenden.
Der Entity-Type MWStSatz hat zwei Attribute, von denen die Mehrwertsteuersatznummer (MNR) der Schlüssel ist. Das Attribut Prozent ist nur bescheibend, aber nicht
identifizierend.
Dies ist natürlich nur ein kleiner Ausschnitt aus der Realität, was aber genügt, um die
beiden häufigsten Beziehungstypen darzustellen: 1:N-Beziehung und M:N-Beziehung.
3.1 1:N-Beziehung
Zwischen Artikel und MWStSatz gibt es eine 1,1:0,N-Beziehung, da jeder Artikel exakt einen
Mehrwertsteuersatz haben muss. Andererseits gehört zu jedem Mehrwertsteuersatz kein,
einer oder mehrere Artikel. Genau dies wird durch die Kardinalitätsangabe ausgesagt. Das
N steht für eine nicht näher bekannte Obergrenze, ebenso das unten verwendete M.
3.2 M:N-Beziehung
Die Beziehung zwischen Artikel und Lieferant dagegen ist eine 0,N:0,M-Beziehung, weil beide
Entities unabhängig voneinander existieren können. Gleichzeitig ist es für jedes einzelne
Entity möglich, eine Beziehung zu jeweils mehreren Entities des anderen Entity-Types zu
haben. Schließlich kann ein Lieferant mehrere Artikel liefern, und jeder Artikel kann auch
von mehreren Lieferanten bezogen werden.
Tatsächlich ist bei dieser Beziehung sogar einiges an Information vorhanden, denn der
Einkaufspreis (EKPreis) ist nicht allein vom Artikel oder vom Lieferanten abhängig, sondern
von beiden, d. h. er ergibt sich erst durch die Beziehung zwischen diesen. Gleiches gilt auch
für die Lieferzeit. Hier hängen Attribute also an der Beziehung (der Raute).
8
4 UMSETZUNG ERD IN RELATIONEN
3.3 Notationsvarianten
Beziehungsart
numerisch
MC-Notation Krähenfußnotation
einfach, optional
0,1 0..1
C
mehrfach, optional
0,N 0..*
MC
einfach, obligatorisch
1,1 1..1
1
mehrfach, obligatorisch 1,N 1..*
M
Abbildung 2: Übersicht über die Notationsformen der Kardinalitätsangaben
3.3 Notationsvarianten
Leider gibt es inzwischen sehr viele Notationsvarianten für die Kardinalität von Beziehungen, teils in Textform, teils grafisch, siehe Abbildung 2.
Zu beachten ist, dass teilweise die numerische Notation an den jeweils anderen Enden,
also vertauscht, geschrieben wird. Dieser anderen Notation schließen wir uns nicht an,
sondern bleiben bei der von Chen vorgeschlagenen, präzisieren lediglich die Kardinalitätsangabe. Auf diese Weise bleibt die Notation auch der Krähenfußnotation ähnlicher.
Bei der Krähenfußnotation wird oft die Raute weggelassen und die Rollen werden ausführlich in Aktiv- und Passivform an das jeweilige Ende der Linie geschrieben.
Mitarbeiter
wird be−
treut von
Teil
betreut
Kunde
oder
ist
Teil von
enthält
Abbildung 3: Beispiele für ERDs in Krähenfußnotation
4 Umsetzung eines ER-Diagramms in Relationen
Nach der Erstellung des ERDs und der Notation von dort nicht direkt abbildbaren Integritätsregeln kann man mit der Umsetzung in ein Relationenmodell beginnen. Dieser Vorgang
ist ein schematischer und erfordert keine Kreativität. Es gibt sogar Programme zum Erstellen von ERDs, die die Umsetzung in Relationen (und weiter in eine Generatordatei)
übernehmen.
4.1 Nicht optimierte Relationen
Jedes Entity wird in eine Relation umgewandelt, jede Eigenschaft wird zu einem Attribut der Relation. Der Schlüssel des Entities wird zum Primärschlüssel der Relation. Es
entstehen die Entity-Relationen.
9
4.2 Optimierung der Relationen
4 UMSETZUNG ERD IN RELATIONEN
Ebenso wird zunächst jede Beziehung in eine Relation umgewandelt. Die Attribute werden ebenfalls übernommen. Der Primärschlüssel einer Beziehungsrelation besteht aus den
Schlüsseln der an der Beziehung beteiligten Entities. Bei 1:N-Relationen genügt es, den
Schlüssel des über das N verbundenen Entities zu übernehmen. Beispielsweise wäre beim
Beispiel 1 Kunde hat evtl. mehrere Aufträge“ die eindeutige Auftragsnummer der Schlüssel
”
der Beziehungsrelation. Bei M:N-Relation ergibt sich immer zwangsläufig ein zusammengesetzter Schlüssel, beispielsweise ist der Schlüssel der Beziehungsrelation zwischen Lieferant
und Artikel die Kombination aus Lieferantennummer und Artikelnummer.
Die aus den Entity-Relationen übernommenen Schlüssel sind in den Beziehungsrelationen als Fremdschlüssel zu kennzeichnen, z. B. unterstrichelt, unterschlängelt oder in einer
anderen Farbe unterstrichen als die Primärschlüssel.
Die Umsetzung des ERDs aus Abbildung 1 auf Seite 7 ergibt die in Abbildung 4 dargestellten Relationen.
4.2 Optimierung der Relationen
Der erste Relationenentwurf lässt sich optimieren, indem man die Beziehungsrelationen, deren Primärschlüssel mit dem einer Entity-Relation übereinstimmt, mit der Entity-Relation
zu einer Relation zusammenfasst.
M:N-Relationen kann man durch die Optimierung nicht entfernen. Schließlich sind alle Entities und alle M:N-Beziehungen zu je einer Relation geworden, während die 1:NBeziehungen wegoptimiert wurden. Die weiteren Integritätsbedingungen werden übernommen.
Die optimierten Relationen unseres kleinen Beispiels sehen aus wie in Abbildung 5 auf
der nächsten Seite dargestellt. Dabei ist festzuhalten, dass nur eine der beiden Beziehungsrelationen wegoptimiert werden konnte.
Entity-Relationen
Artikel (ANR, Bezeich, Gewicht, VKPreis)
Lieferant (LNR, Ort, Telefon)
MWStSatz (MNR, Prozent)
Beziehungs-Relationen (Fremdschlüssel so
gekennzeichnet)
::
unterliegt (:::::
ANR, ::::::
MNR)
Lieferzeit, EK-Preis)
liefert (:::::
ANR, LNR,
:::::
Abbildung 4: Aus ER-Diagramm umgesetzte Relationen
10
5 RELATIONEN → GENERATORDATEI
5 Umsetzung von Relationen in eine Generatordatei
Um ein Relationenmodell in eine Generatordatei umsetzen zu können, muss man nun die
passenden Datentypen auswählen aus der Menge von Datentypen, die die Zieldatenbank
zur Verfügung stellt. Ganzzahlen, gebrochene Zahlen, Texte, Datums- und Zeitwerte sollten
von jeder Datenbank angeboten werden.
Aus jeder Relation wird eine Tabelle, die mit Hilfe von create table tablename angelegt wird. Wertebereicheinschränkungen können in SQL als Check-Constraint festgelegt
werden (z. B. keine negativen Werte, oder ein Attribut muss größer sein als ein anderes).
5.1 Primärschlüssel
Die Primärschlüssel für die Tabellen kann man direkt aus den Primärschlüsseln der Relationen übernehmen. Weitere Indexe aus Performance-Gründen kann man zusätzlich anlegen. Beim Einfügen von Daten kosten sie Zeit, beim Lesen von Daten bringen sie einen
Performance-Gewinn. Ebenso können sie beim Verarbeiten von Daten die Menge der beim
Mehrfachzugriff zu sperrenden Daten verringern, so dass eine höhere Parallelität der Verarbeitung möglich wird.
5.2 Fremdschlüssel
Durch Angabe der Fremdschlüsselbeziehungen wird die Inklusionsabhängigkeit abgebildet.
Je nach Datenbank kann man die Reaktion auf eine Fremdschlüsselverletzung selbst festlegen: Operation verhindern, entsprechende korrespondierende Werte auf NULL setzen oder
korrespondierende Tupel (mit-)löschen.
Eine Datenbank ohne die Möglichkeit, Fremdschlüssel einzurichten und zu überwachen,
kann man heutzutage getrost als unbrauchbar bezeichnen.
5.3 Weitere Integritätsbedingungen
Weitere Integritätsbedingungen, die nicht über Schlüssel und Check-Constraints abgebildet
werden können, müssen über Prozeduren und Regeln eingefordert werden. Das ist nicht bei
Entity-Relationen
MNR)
Artikel (ANR, Bezeich, Gewicht, VKPreis, ::::::
Lieferant (LNR, Ort, Telefon)
MWStSatz (MNR, Prozent)
Beziehungs-Relationen (Fremdschlüssel so
gekennzeichnet)
::
liefert (:::::
ANR, MNR,
Lieferzeit,
EK-Preis)
::::::
Abbildung 5: Optimierte Relationen
11
6 NORMALISIERUNG
allen Datenbanken möglich (leider).
Der SQL-Standard sieht zwar bereits Prozeduren und Regeln vor, aber viele Datenbankhersteller haben diese bereits vor Verabschiedung des Standards auf jeweils unterschiedliche
Weise eingeführt, so dass nun mehrere Varianten hierfür existieren.
6 Normalisierung
Normalisierung ist ein Prozess innerhalb des Datenbankentwurfs, bei dem geprüft wird, ob
Daten redundanz- und anomaliefrei gespeichert werden können. Im Rahmen dieses Vorgangs werden die vorhandenen Daten auf diverse Tabellen verteilt, um die Konsistenz der
Daten zu erreichen. Sie wird angewendet, wenn man alte Datenbestände in ordentliche
Relationen überführen möchte, oder aber wenn man einen Entwurf wie in den vorigen Kapiteln gezeigt gemacht hat und diesen überprüfen möchte. Es gibt verschiedene Arten von
Anomalien“:
”
• Einfüge-Anomalie: Daten können nur gemeinsam mit anderen Daten eingegeben werden, die sachlich aber nicht zwingend gemeinsam auftreten.
• Lösch-Anomalie: Wenn bestimmte Daten gelöscht werden, gehen auch andere Daten
verloren.
• Aktualisierungs-Anomalie: Bei Änderungen an Daten können Widersprüche auftreten. Das ist eine typische Folge von redundanten Daten.
Im Zusammenhang mit Normalisierung benötigt man folgende Begriffe:
• funktionale (einwertige) Abhängigkeit X—>Y Mit der Ausprägung von X ist genau
ein Wert von Y festgelegt.
• mehrwertige Abhängigkeit X—Y Zu einem Wert von X ist unabhängig von den
anderen Attributen eine wohlbestimmte (nicht beliebige) Menge von Werten von Y
gegeben – verteilt über mehrere Tupel. Ist die Menge von einer Mächtigkeit größer als 1, so spricht man von echter mehrwertiger Abhängigkeit; jede funktionale
Abhängigkeit ist eine mehrwertige Abhängigkeit, wenn auch keine echte.
6.1 Die 1. Normalform – 1NF
Eine in 1. Normalform befindliche Relation ist dadurch gekennzeichnet, dass sie keine
Attribute mit Attributwerten aufweist, die sich aus mehreren Elementen zusammensetzen.
Also: Im Kreuzungspunkt einer Zeile und einer Spalte befindet sich immer höchstens ein
Wert. Man sagt dazu auch: Alle Nicht-Schlüssel-Attribute sind funktional abhängig vom
Schlüssel (wozu sie eindeutig sein müssen). Man sagt auch: Jedes Attribut verfügt über
elementare Ausprägungen.
12
6 NORMALISIERUNG
6.2 Die 2. Normalform – 2NF
keine Relation
NAME
Hugo
Anna
Berta
AUTO
K-CM 773
BM-A 17, BM-B 28
NULL
1. NF
NAME
Hugo
Anna
Anna
Berta
AUTO
K-CM 773
BM-A 17
BM-B 28
NULL
Abbildung 6: Überführung einer Tabelle in eine 1NF-Relation
Übrigens: Falls eine Tabelle mehrere Elemente in einer Zelle enthält, gilt sie überhaupt
nicht als Relation.
Ein Beispiel ist Abbildung 6 dargestellt. Um aus einer Tabelle eine Relation in erster
Normalform zu machen, muss man ggf. die Anzahl der Tupel erhöhen. Hierbei werden oft
die Schlüsselattribute wiederholt, so dass der Schlüssel nicht mehr eindeutig ist. Dann sind
weitere Attribute in den Schlüssel aufzunehmen: Nur die Kombination aller Schlüsselattribute muss eindeutig sein.
6.2 Die 2. Normalform – 2NF
Eine in 2. Normalform befindliche Relation ist dadurch gekennzeichnet, dass jedes nicht
dem Schlüssel angehörende Attribut funktional abhängig ist vom Gesamtschlüssel, aber
nicht von Schlüsselteilen. Also: Eine Relation ist in 2NF, wenn sie in 1NF ist und jedes
nicht dem Schlüssel angehörende Attribut voll funktional abhängig ist vom Schlüssel.
Hinweis: Die 2. Normalform kann nur dann verletzt werden, wenn eine Relation einen zusammengesetzten Schlüssel und mindestens ein nicht dem Schlüssel angehörendes Attribut
hat.
Mit Hilfe der Relation BELEGGAST (GastID, ZimmerNr, Tag, Name, Vorname) werden Informationen über Gäste (eigentlich nur GastID, Name, Vorname) und ihre tägliche
Zimmerbelegung gespeichert. Schlüssel sind (GastID, Tag) oder (ZimmerNr, Tag). Diese
Relation hat folgende Mängel:
• Einfüge-Anomalie: Ohne Zuweisung einer Zimmerbelegung ist es nicht möglich, Informationen über einen Gast aufzunehmen (es sei denn, man lässt NULL-Werte zu).
• Lösch-Anomalie: Mit dem Entfernen (historischer) Belegungen gehen gleichzeitig die
Daten über die betroffenen Gäste verloren.
• Aktualisierungs-Anomalie: Änderungen der Daten zu einem Gast bei einer Belegung
führt zu einem Widerspruch zu den Daten bei allen anderen gespeicherten Belegungen
desselben Gastes.
Die Daten des Gastes (Name, Vorname) sind von dessen GastID abhängig. GastID ist
jedoch kein Schlüssel der Relation, sondern nur Teil des Schlüssels. Die sich bei der not-
13
6.3 Die 3. Normalform – 3NF
SIGNATUR
WISS03
BELL45
ZEIT65
TITEL
Die Erde
Es
Unions
AUSWEISNR
1001
1220
1001
6 NORMALISIERUNG
NAME
Hugo
Anna
Hugo
AUSLEIHDATUM
16.06.
15.08.
18.06.
RUECKGABE
16.07.
15.09.
18.07.
Abbildung 7: Beispiel für einen Verstoß gegen die 2NF
wendigen Aufteilung ergebenden Relationen sind GAST (GastID, Name, Vorname) und
BELEGUNG (GastID, ZimmerNr, Tag). Die ursprüngliche Relation lässt sich als natürlicher Join aus diesen beiden darstellen.
Die in Abbildung 7 gezeigte Tabelle muss ebenfalls in zwei Relationen aufgeteilt werden
(natürlich kann es auch sein, dass eine Tabelle in mehr als zwei Tabellen aufgeteilt werden
muss).
Grundsätzlich dient die 2NF dazu, die Daten aus einer Tabelle, die sich auf verschiedene
Dinge beziehen (oben auf Gast und auf eine Zimmerbelegung) in verschiedene Tabellen
aufzuteilen. Als Kriterium gilt die funktionale Abhängigkeit, denn von der GastID sind nur
die zum Gast gehörigen Attribute direkt funktional abhängig. Also müssen genau diese in
eine separate Tabelle.
6.3 Die 3. Normalform – 3NF
Eine in 3. Normalform befindliche Relation ist dadurch gekennzeichnet, dass sie der 2NF
genügt und dass keine funktionalen Abhängigkeiten zwischen nicht dem Schlüssel angehörenden Attributen vorkommen. Also: Eine Relation ist in 3NF, wenn sie in 2NF ist und
keine transitiven Abhängigkeiten aufweist.
Die Relation RECHNUNG (RechnungID, GastID, Name, PLZ, Ort, Straße, Betrag)
enthält Daten über Gäste und ihre Rechnungen. Schlüssel ist (RechnungID). Diese Relation
befindet sich in der zweiten Normalform. Trotzdem gibt es Mängel:
• Einfüge-Anomalie: Informationen über einen neuen Gast können erst mit Ausstellung
der Rechnung aufgenommen werden.
• Lösch-Anomalie: Beim Löschen historischer Rechnungen gehen die Daten über die
betroffenen Gäste verloren.
• Aktualisierungs-Anomalie: Änderungen der Daten zu einem Gast bei einer Belegung
führt zu einem Widerspruch zu den Daten bei allen anderen gespeicherten Rechnungen desselben Gastes.
Grund ist auch hier (wie oben), dass Informationen über Gäste redundant gehalten werden. Sie beziehen sich hier nur mittelbar (über die GastID) auf die Rechnung. Die Daten
14
6 NORMALISIERUNG
6.4 Die Boyce-Codd-Normalform – BCNF
zum Gast (Name, PLZ, Ort, Straße) sind funktional abhängig von GastID, einem NichtSchlüssel-Attribut. Diese Abhängigkeit nennt man transitiv abhängig (Schlüssel —> NichtSchlüssel —> weiteresAttribut, hier beispielsweise RechnungID —> GastID —> Name).
Auch diese Anomalie lässt sich durch Aufspalten der Tabelle in mehrere Tabellen ausschließen. Das Nicht-Schlüssel-Attribut, von dem andere Attribute funktional abhängig
sind, wird zum Schlüssel einer neuen Tabelle, das alle von ihm abhängigen Attribute enthält. Die alte Tabelle verliert genau diese Attribute, behält nur den Schlüssel der neuen
Tabelle.
Hier ergäben sich die Relationen RECHNUNG (RechnungID, GastID, Betrag) mit RechnungID als Schlüssel und GAST (GastID, Name, PLZ, Ort, Straße) mit GastID als Schlüssel. Die ursprüngliche Relation lässt sich als natürlicher Join dieser beiden Relationen
darstellen.
Diese 3NF genügt in fast allen Fällen der Praxis.
6.4 Die Boyce-Codd-Normalform – BCNF
Auch wenn Relationen in 3NF grundsätzlich anomaliefrei sind, können noch versteck”
te“ Anomalien auftauchen, wie dieses Beispiel zeigt: Unser Hotel bietet gewisse ServiceLeistungen an, die in Veranstaltungsgruppen Sightseeing“, Kultur“ und Sport“ eingeteilt
”
”
”
sind. Zur Vermeidung von Konkurrenzsituationen findet pro Termin höchstens eine Veranstaltung aus einer Gruppe statt. Das Angebot wird in einer Relation SERVICE (VGruppe,
Termin, Veranstaltung) gespeichert. SERVICE befindet sich mit dem gewählten Schlüssel
(VGruppe, Termin) in 3NF.
Trotzdem besitzt sie den Nachteil, dass z. B. die Aufnahme einer Veranstaltung, die noch
keiner Gruppe zugeordnet ist, nicht funktioniert. Das bedeutet eine Einfüge-Anomalie.
Außerdem muss die Umbenennung einer Veranstaltungsgruppe (z. B. Sightseeing“ in Be”
”
sichtigungen“) mehrfach nachgehalten werden, was bei nicht durchgängiger Handhabung
zu Problemen führt (Aktualisierungs-Anomalie).
Grund ist, dass man für SERVICE einen besseren Schlüssel, nämlich (Termin, Veranstaltung) wählen kann. Dann zeigt sich auch die funktionale Abhängigkeit Veranstaltung
—> VGruppe, die offenlegt, dass die Relation nun nicht der 2NF genügt.
Tatsächlich ist die BCNF eine (logische) Erweiterung der 3NF, weil sie voraussetzt,
dass zusätzlich auch keine Abhängigkeiten eines Schlüsselteils von einem Nicht-Schlüssel
vorkommen dürfen, wie das bei der ersten Schlüsselfestlegung dieses Beispiels der Fall war.
Es sollen also gar keine Attribute von Nicht-Schlüsseln abhängig sein (nicht nur andere
Nicht-Schlüssel).
Die obige Tabelle gehört also wieder in zwei Tabellen aufgespalten: VERANSTALTUNG
(Veranstaltung, VGruppe) mit Veranstaltung als Schlüssel und ANGEBOT (Termin, Veranstaltung), wo das gesamte Tupel Schlüssel ist. Die ursprüngliche Tabelle lässt sich wieder
als natürlicher Join dieser beiden darstellen.
Problem: Die Normalisierung führt hier zu einem Verlust der automatischen Prüfung,
dass an einem Termin aus einer Veranstaltungsgruppe nur eine Veranstaltung stattfinden
15
6.5 Die 4. Normalform – 4NF
6 NORMALISIERUNG
kann. Vor der Normalisierung hat der gewählte Schlüssel dies sichergestellt. Jetzt müssen wir dies in einer Nebenbedingung festlegen, von der wir nur hoffen können, dass das
Datenbanksystem sie abbilden kann.
6.5 Die 4. Normalform – 4NF
Noch immer sind wir mit der Normalisierung nicht zu Ende, obwohl jetzt alle funktionalen
Abhängigkeiten abgeklopft sind. Gegeben sei eine Tabelle ANGEBOT (TourNr, Ziel, Tag)
mit dem kompletten Tupel als Schlüssel. Es handelt sich um Sightseeing-Touren mit festen
Zielen, bei denen eine Tour pro Tag nur einmal durchgeführt werden kann. Wieder sind
Anomalien möglich:
• Einfüge-Anomalie: Ohne die Vereinbarung eines ersten Termins (Tag) ist es nicht
möglich, eine neue Tour in den Bestand aufzunehmen.
• Lösch-Anomalie: Werden historische Touren gelöscht, gehen die Daten über deren
Ziele verloren.
• Aktualisierungs-Anomalie: Die Ziele der Touren werden bei jedem Tag neu aufgeführt.
Ändert man das Ziel, sind die Daten widersprüchlich.
Das Problem liegt darin, dass zwei völlig unterschiedliche Aspekte über die Touren beschrieben werden: die Ziele einerseits und die Termine andererseits. Es gibt zwei mehrwertige
Abhängigkeiten in einer Tabelle: TourNr — Ziel und TourNr — Tag.
Die verletzte Regel für die 4NF lautet: Jede Abhängigkeit X — Y besitzt die Eigenschaft X enthält den Schlüssel der Relation“. Jedes Attribut darf also nur vom Ge”
samtschlüssel abhängig sein. Das ist hier offensichtlich nicht der Fall, denn TourNr enthält
nicht den Schlüssel, sondern ist nur ein Teil davon. Also muss wieder aufgeteilt werden.
Die sich ergebenenden Relationen sind TOURZIEL (TourNr, Ziel) und TOURTERMIN
(TourNr, Tag). Die frühere Relation lässt sich problemlos als Join dieser beiden darstellen.
6.6 Die 5. Normalform — 5NF
Was fehlt denn nun noch? Bislang haben wir uns immer mit Relationen beschäftigt, die
sich als Join von zwei ihrer Projektionen darstellen lassen. Tatsächlich gibt es aber auch
Relationen, die sich lediglich als Join von mehr als zwei ihrer Projektionen darstellen
lassen. Unser Hotel bietet jetzt Touren an, die von anderen Unternehmen durchgeführt
werden und von einem Mitarbeiter unseres Hauses begleitet werden. Dabei gelten folgende
Regeln (siehe auch Abbildung 8 auf der nächsten Seite):
• Einem Unternehmen sind bestimmte Touren zugewiesen, beschrieben in TOUREN
(Unternehmen, TourNr).
• Jeder Mitarbeiter betreut ein bestimmtes Touren-Angebot: BETREUUNG (MitarbID, TourNr).
16
6 NORMALISIERUNG
TOUREN
Unternehmen
Ikarus-Reisen
Ikarus-Reisen
Dallas-Tours
Dallas-Tours
Rheinfall-Reisen
Rheinfall-Reisen
6.7 Hinweise
T
TourNr
T1
T2
T2
T3
T1
T3
BETREUUNG
MitarbID TourNr
25
T2
25
T3
26
T2
26
T1
35
T3
38
T1
TOURORG
Unternehmen
TourNr
Dallas-Tours
T2
Dallas-Tours
T3
Dallas-Tours
T2
Rheinfall-Reisen T1
Rheinfall-Reisen T3
Ikarus-Reisen
T1
Ikarus-Reisen
T2
B
KONTAKT
K
MitarbID Unternehmen
25
Dallas-Tours
26
Dallas-Tours
26
Ikarus-Reisen
35
Rheinfall-Reisen
38
Rheinfall-Reisen
O
MitarbID
25
25
26
38
35
26
26
Abbildung 8: Beispiel für die 5. Normalform
• Darüber hinaus werden Mitarbeiter nur im Zusammenhang mit bestimmten Unternehmen eingesetzt: KONTAKT (MitarbID, Unternehmen).
Wer mit wem welche Touren durchführen kann, wird in TOURORG (TourNr, Unternehmen, MitarbID) festgehalten, das der natürliche Join von TOUREN, und KONTAKT ist
und den Schlüssel (TourNr, Unternehmen, MitarbID) hat. Diese Relation liegt offensichtlich
in 4NF vor.
Die drei ursprünglichen Tabellen sind jeweils Projektionen von TOURORG. TOURORG
gestattet es jedoch nicht, neue Zuordnungen zwischen Mitarbeiter und Touren, Mitarbeiter
und Unternehmen oder Unternehmen und Touren einzutragen, was eine Einfüge-Anomalie
darstellt. Die fünfte Normalform verlangt, dass in einer Relation ausschließlich triviale JoinAbhängigkeiten vorhanden sind. Die Tatsache, dass die eine Tabelle aus mehreren anderen
synthetisiert worden ist, zeigt schon, dass man Beispiele, die gegen 5NF verstoßen, während
sie 4NF erfüllen, regelrecht konstruieren muss. Die praktische Bedeutung der 5NF ist gering.
6.7 Hinweise
Die Normalisierung ist ein rein formaler Prozess, der lediglich strukturelle Defekte aufdeckt, aber nicht sicherstellt, dass der Entwurf problemadäquat durchgeführt worden ist.
Außerdem gibt es manchmal durchaus Wahlmöglichkeiten, wie man eine Relation in höher
normalisierte Relationen zerlegt. Hier ist dann der gesunde Menschenverstand gefordert –
und Erfahrung ist auch von Vorteil.
17
6.7 Hinweise
6 NORMALISIERUNG
Nicht jeder Verstoß gegen die Normalisierung muss unbedingt ein Fehler sein. Es gibt
Fälle, in denen man eine kontrollierte Redundanz in den Daten hat und haben will, um die
Abfragegeschwindigkeit zu erhöhen. Es ist aber immer darauf zu achten, dass der Datenbestand nicht widersprüchlich werden darf, was bei redundanten Daten eine nicht-triviale
Aufgabe ist.
$RCSfile: ERD-Entwurf_und_Normalisierung.tex,v $ $Date: 2004/07/21 11:07:27 $ $Revision: 1.6 $
18