Datenbanken

fbi
h_da
Datenbanken
Kapitel 3: Relationenmodell
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 1
Relationenmodell
fbi
h_da
Inhalte des Kapitels
• Der Übergang vom ER-Modell zum Relationenmodell
• Die Codd‘schen Normalformen
• Die Implementierung von Vererbungshierarchien
Lernziele
• Der Übergang von der Analysephase zur Designphase soll im SW-Lifecycle
auch auf die Datenmodellierung angewandt werden können.
• Die Anwendung der Codd‘schen Normalformen soll strategisch eingeordnet
werden können.
• Die unterschiedlichen Implementierungen von Vererbungshierarchien sollen
in ihren strategischen Auswirkungen verstanden werden und die
Restriktionen von RDBMS bzgl. der Implementierung von Vererbungshierarchien bekannt sein.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 2
Das Relationenmodell
•
•
fbi
h_da
Die Grundlage des von Edgar F. Codd 1970 vorgestellten Relationalen
Datenbankmodells bildet der Relationenbegriff aus der Mathematik:
Eine n-stellige (n-äre) Relation in einer Menge M ist eine Beziehung, die
zwischen je n Elementen aus M besteht oder nicht – vorstellbar als eine
Menge von Tupeln gleicher Länge n ( auch n-Tupel genannt):
A1
A2
A3
A4
…
An
( x 1 , x 2 , x 3 , x 4 , … , xn )
( y 1 , y 2 , y 3 , y 4 , … , yn )
( z1 , z2 , z3 , z4 , … , zn )
…
→ Für die Darstellung von Relationen bietet sich die Tabellendarstellung an:
Die Spaltenbezeichner einer solchen Tabelle nennt man Attribute und die
einzelnen Zeilen Tupel bzw. Datensätze.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 3
fbi
h_da
Vom ER-Modell zum Relationenmodell
Analysesicht
bietet an
Verlag
VerlagID
...
Verlagszugehörigkeit
wird verlegt bei
Buch
ISBN
...
ER-Modell
ER-Modell
In der Analysesicht (ER-Modell) modellieren wir
• Entity-Typen, deren Eigenschaften wir als
• Attribute bezeichnen, sowie die
• Beziehungstypen der Entity-Typen untereinander, versehen mit
Kardinalitäten und Bezeichnern.
In der Analysesicht
• sollten alle Bezeichner der Sprache des Endanwenders entsprechen,
• können ggf. schon generische Datentypen für Attribute ergänzt werden.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 4
fbi
h_da
Vom ER-Modell zum Relationenmodell
Analysesicht
•
Die Analysesicht (ER-Modell) enthält noch keinerlei Informationen über die
systemspezifischen Implementierungsdetails des Datenmodells.
Designsicht
•
In der Designphase wird ein Relationenmodell modelliert, dessen Basis
alle Informationen des konzeptionellen ER-Modells sind, erweitert um
spezifische Implementierungsdetails für das Ziel-DBMS.
→ Für das Klassendiagramm in der Analyse- bzw. Designsicht gelten die oben
genannten Eigenschaften analog (vgl. OOA/OOD).
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 5
fbi
h_da
Vom ER-Modell zum Relationenmodell
Beim Übergang vom ER- zum Relationenmodell werden
• Entity-Typen zu Tabellen (Relationen),
• Attribute zu Spalten,
• system-spezifische Datentypen für die Spalten werden ergänzt.
Verlag
VerlagsID int <pk>
...
?
Buch
ISBN varchar2(15) <pk>
...
RelationenRelationenmodell
modell
→ Wie erfolgt die Implementierung der Beziehungstypen?
•
Machen Sie sich zunächst noch einmal klar, wie Beziehungen im UML-Klassendiagramm beim Übergang von der OOA zur OOD implementiert werden!
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 6
Vom ER-Modell zum Relationenmodell
fbi
h_da
•
Beziehungstypen (Relationships) werden entsprechend ihrer Kardinalitäten
als physische Referenzen implementiert.
→ Dies erfolgt bei relationalen Datenbanken durch die Ergänzung von Fremdschlüssel (-Spalten) (foreign keys). Die Referenzierung über Fremdschlüssel ist eine werte-basierte Referenzierung! Sie erfolgt – im Unterschied zu
objektorientierten Systemen – nicht über physische Adressen!
Designsicht
Verlag
VerlagsID int <pk>
...
Referenz
RelationenRelationenmodell
modell
Buch
ISBN varchar(15) <pk>
VerlagsID int <fk>
...
Die VerlagsD ist als FremdschlüsselSpalte die physische Implementierung
der Beziehung aus dem ER-Modell und
dient zur Referenzierung des assoziierten
Verlag-Datensatzes.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 7
fbi
h_da
Vom ER-Modell zum Relationenmodell
•
Man spricht von referentieller Integrität, wenn zu jedem Zeitpunkt die
Referenz eines Primärschlüsselwertes durch den Wert einer Fremdschlüsselspalte gewährleistet ist.
Verlag
•
Buch
VID <pk>
VerlagBez
ISBN <pk>
123
234
‘ABC ‘
‘XYZ ‘
3-548-…
0-122-…
2-456-…
9-768-…
3-672-…
0-398-…
Titel
VID <fk>
‘A…. ‘
‘B…. ‘
‘C…. ‘
‘D…. ‘
‘E…. ‘
‘F…. ‘
123
123
234
567
234
123
Welche Veränderungen an den beiden Tabellen würden darüber hinaus zu
einer Verletzung der referentiellen Integrität führen?
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 8
Relationenmodell
fbi
h_da
9 Der Übergang vom ER-Modell zum Relationenmodell
•
Die Codd‘schen Normalformen
•
Die Implementierung von Vererbungshierarchien
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 9
fbi
h_da
Die Codd‘schen Normalformen – CNF
Modellierungsregeln und Strukturen der relationalen Datenmodellierung zur
Vermeidung von Redundanzen sind
•
die Codd‘sche Normalformen und
•
die Supertype- / Subtype-Hierarchien (vgl. auch Kapitel 2).
•
Anfang der 70er Jahre formulierte Edgar F. Codd die Coddschen
Normalformen (NF) im Rahmen der Untersuchungen seines
Relationenmodells.
•
Die Coddschen NF bilden eine Reihe von Regeln, die eingehalten werden
müssen bzw. sollten um Redundanzen von Daten zu vermeiden.
Nachteile von Redundanzen:
•
erhöhter Speicherbedarf → erhöhte Kosten
•
Gefahr von Inkonsistenz (“Speicheranomalien”)
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 10
Die Codd‘schen Normalformen
fbi
h_da
Beispiel für Speicheranomalien:
Mitarbeiter - MA
MA_NR
MA_NAME
MA_ABT_NR
MA_ABT_NAME
135241
...
11
Vertrieb Inland
23789
...
21
Personalwesen
...
...
...
...
16543
...
11
Vertrieb Ausland
38765
...
34
Einkauf
Speicheranomalie
•
•
•
•
Codd formulierte insgesamt fünf Normalformen 1NF, 2NF, ..., 5NF, sowie
die Normalform nach Boyce/Codd, die mit BCNF bezeichnet wird.
In der Praxis haben die ersten drei Normalformen die größte Bedeutung.
Jede Normalform xNF, x ≥ 1, fordert, dass die vorhergehende NF bereits
erfüllt ist.
Die eigentlichen Vorschriften jeder NF können jedoch unabhängig voneinander betrachtet werden, denn jede einzelne Vorschrift beschäftigt sich
mit einem anderen Bereich der Datenmodellierung.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 11
Hörsaalbeispiel zu den CNF
fbi
h_da
Datenmodell „Werkstattauftrag“
Entity-Typen→
→Relationen
Relationen(?)
(?)
Entity-Typen
AUFTRAG(ANR
(ANR––Auftragsnr,
Auftragsnr,ADAT
ADAT––Auftragsdatum,
Auftragsdatum,TERM
TERM––Liefertermin,
Liefertermin,AFO
AFO––Arbeitsfolgenr,
Arbeitsfolgenr,
•• AUFTRAG
VZ––Vorgabezeit
Vorgabezeitpro
proArbeitsfolge)
Arbeitsfolge)
VZ
Arbeitsplatznr,KST
KST––Kostenstelle,
Kostenstelle,KAP
KAP––Kapazität)
Kapazität)
ARBEITSPLATZ(APZ
(APZ––Arbeitsplatznr,
• •ARBEITSPLATZ
Teilenr,TBEZ
TBEZ––Teilebezeichnung)
Teilebezeichnung)
TEIL(TNR
(TNR––Teilenr,
• •TEIL
Beziehungstypen→
→Referenzen
Referenzen(?)
(?)
Beziehungstypen
Zujedem
jedemAuftrag
Auftraggibt
gibtes
esmehrere
mehrereArbeitsfolgen.
Arbeitsfolgen.
••Zu
VerschiedeneArbeitsfolgen
Arbeitsfolgenkönnen
könnenam
amselben
selbenArbeitsplatz
Arbeitsplatzbearbeitet
bearbeitetwerden,
werden,
• •Verschiedene
eineArbeitsfolge
Arbeitsfolgekann
kannjedoch
jedochnur
nuran
aneinem
einemArbeitsplatz
Arbeitsplatzbearbeitet
bearbeitetwerden.
werden.
eine
JederArbeitsplatz
Arbeitsplatzenthält
enthältAngaben
Angabenüber
überdie
dieKapazität
Kapazitätund
unddie
diezuständige
zuständigeKostenstelle.
Kostenstelle.
• •Jeder
Eskann
kannsein,
sein,dass
dassein
einAuftrag
Auftragmehr
mehrals
alseine
eineTeileposition
Teilepositionbenötigt,
benötigt,und
undein
einTeil
Teilkann
kannfür
für
• •Es
mehrals
alseinen
einenAuftrag
Auftragbenötigt
benötigtwerden.
werden.
mehr
→
Die Rolle der CNF beim Übergang vom ER-Modell zum Relationenmodell
wird anhand des Datenmodells „Werstattauftrag“ verdeutlicht.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 12
fbi
h_da
Darstellung von Attributbeziehungen
•
Im Zusammenhang mit der Untersuchung von CNF ist es notwendig, die
Beziehungen von Attributen innerhalb eines Entity-Typ bzw. von Spalten
innerhalb einer Relation zu betrachten.
→
Es bietet sich hierfür an, Entity-Typen (Relationen) durch Rechtecke und
Attribute (Spalten) durch Ovale innerhalb dieser Rechtecke zu kennzeichnen.
TEIL
→
TBEZ
TNR
…
Die Abhängigkeit des Wertes eines Attributes A1 vom Wert eines
Attributes A2 kann nun mit Hilfe von Pfeilen dargestellt werden:
TNR
Schestag
TBEZ
Der Attributwert einer Teilebezeichnung TBEZ
wird durch den Attributwert des Primärschlüsselattributes TNR eindeutig festgelegt.
Datenbanken (Bachelor)
Kapitel 3 - 13
Die erste Codd‘sche Normalform – 1NF
•
fbi
h_da
Im Gegensatz zu allen anderen Codd‘schen Normalformen muss die 1NF
immer erfüllt sein:
"In einer Relation darf jedes Attribut maximal eine Ausprägung haben."
•
Enthält ein Entity-Typ Attribute mit mehr als einer Ausprägung, so müssen
die mehrfach vorkommenden Attribute in einem eigenen Entity-Typ ausgelagert werden (im Hörsaalbeispiel: “Arbeitsfolgedaten“).
Auftrag
ANR
ADAT
TERM
Zu jedem Auftrag gibt es
• mehrere Arbeitsfolgen (AFO), und
• zu jeder Arbeitsfolge eine Vorgabezeit (VZ).
Schestag
Datenbanken (Bachelor)
AFO
VZ
mehrwertige
Attribute
Kapitel 3 - 14
1NF – attributive Entitäten
fbi
h_da
Auslagerung von Attributen
•
Jede Ausprägung des neuen Entity-Typ muss eindeutig identifizierbar sein.
→ Zusätzlich zum Primärschlüssel des ursprünglichen Entity-Typs wird ein
zweites Schlüsselelement benötigt, durch das die unterschiedlichen
Ausprägungen des mehrwertigen Attributes unterschieden werden können.
→
Eine Entität, die bei Attributen mit mehrfachem Vorkommen aus der
Erfüllung der 1NF resultiert, benötigt immer mehr als ein Schlüsselelement
zur Identifizierung:
– Primärschlüssel des Entity-Typ, aus dem der neue Entity-Typ
ausgegliedert wurde als Fremdschüsselspalte, plus
– Schlüsselbestandteil, der zur Unterscheidung der Ausprägung des
neuen Entity-Typs dient.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 15
fbi
h_da
1NF – attributive Entitäten
•
•
Ausgelagerte Entitäten nennt man auch abhängig (dependent) oder
attributive bzw. schwache Entitäten (weak Entities, vgl. Kapitel 2).
Im Gegensatz dazu nennt man Entitäten, deren Primärschlüssel (komponente) unabhängig ist von Primärschlüsseln anderer Entitäten, fundamentale oder Kern-Entität.
•
Es können - wenn nötig - beliebig oft attributive Entitäten von attributiven
Entitäten gebildet werden.
Auflösung bei Verletzung der 1NF
Kernentität E1
PK1
hat
(i) gehört zu
attributive Entität
PK2
attributive Entität
PK1 foreign key, PK2 <pk>
als Referenz auf E1 PK1 <pk, fk1>
und Primärschlüssel-Komponente
Kernentität E1
PK1 <pk>
Schestag
Datenbanken (Bachelor)
ER-Modell
Relationenmodell
Kapitel 3 - 16
fbi
h_da
1NF – attributive Entitäten
•
Durch die Relationship “gehört zu” wird das Attribut PK1 Primärschlüssel
der attributiven Entität und leistet somit einen Beitrag zur Identifizierung
dieser Entität.
•
Man spricht deshalb von identifizierenden Relationships und bezeichnet
diese mit (i) oder nennt sie auch abhängig (dependent – vgl. Power
Designer).
•
Eine Beziehung, die nicht identifizierend ist, nennt man referenzierende
Beziehung und bezeichnet diese mit (r).
→
Wird die zusammengesetzte PK-Komponente durch einen einfachen, synthetischen
Schlüssel ersetzt, ist die Relationship (technisch) keine abhängige Relationship
mehr!
Aus wie viel Spalten und welchen Schlüsseln besteht dann die attributive Entität?
→
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 17
fbi
h_da
1NF – attributive Entitäten
•
Hörsaalbeispiel: Die attributive Entität „Arbeitsfolge“ ist entstanden
durch Auslagern der mehrwertigen Attribute aus der Kernentität
„Auftrag“:
Auftrag
Zu jedem Auftrag gibt es
• mehrere Arbeitsfolgen (AFO), und
• zu jeder Arbeitsfolge eine Vorgabezeit (VZ).
ANR
ADAT
TERM
AFO
VZ
mehrwertige Attribute,
ANR ADAT
1
2
...
Schestag
TERM
1NF
AFO VZ
2.3.01 31.5.01 1,2 15,25
15.4.01 30.9.01 1,2,3 30,30,30
Datenbanken (Bachelor)
Kapitel 3 - 18
fbi
h_da
1NF – attributive Entitäten
Normalisierung durch Auslagern der mehrwertigen Attribute:
Auftrag
ANR
ADAT
TERM
hat
(i) gehört zu
Arbeitsfolge
AFO
VZ
ER-Modell
Tabellen bzw.
Relationen
Auftrag
ANR
ADAT
TERM
Schestag
Relationenmodell
Arbeitsfolge
AFO
ANR
VZ
Datenbanken (Bachelor)
ANR ADAT
1
2
...
TERM
2.3.01 31.5.01
15.4.01 30.9.01
ANR
1
1
2
2
2
...
AFO VZ
1
2
1
2
3
15
25
30
30
30
Kapitel 3 - 19
1NF – Auflösung von n:m-Beziehungen
fbi
h_da
n:m-Beziehung im „Werkstattauftrag“
Auftrag
benötigt
wird benötigt für
Teil
n:m-Beziehung
•
Um aus der Tabelle Auftrag in die Tabelle Teil zu referenzieren, würde
man deren Primärschlüssel TNR als Fremdschüssel-Spalte in der Tabelle
Auftrag implementieren.
→ In der Tabelle Auftrag kann das Attribut TNR mehrfach vorkommen.
•
Entsprechend müsste auch in der Tabelle Teil ein Attribut ANR mehrfach
vorkommen.
→
Beides würde zu einer Verletzung der 1NF führen.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 20
fbi
h_da
1NF – Auflösung von n:m-Beziehungen
Lösungsansatz
•
Ausgliederung von TNR aus Auftrag in eine attributive Entität
Teile_pro_Auftrag mit den Primärschlüssel-Komponenten ANR (über
die Beziehung) und TNR.
•
Ausgliederung von ANR aus Teil in eine attributive Entität
Aufträge_pro_Teil mit den Primärschlüssel-Komponenten TNR (über die
Relationship) und ANR.
n:m
Auftrag
Teil
(i)
Teile_pro
_Auftrag
(i)
Aufträge_
pro_Teil
Zwei Entitäten mit identischem
Schlüssel sind identisch.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 21
fbi
h_da
1NF – Auflösung von n:m-Beziehungen
•
→
•
•
)
Schlüsselkomponenten werden nicht nach Relationship oder Attribut
unterschieden.
Es sind zwei attributive Entitäten mit gleichen Schlüsselkomponenten
entstanden.
Beide Entitäten sind identisch und können zu einer (abhängigen) Entität
zusammengefasst werden.
Diese Entität hat zwei Schlüsselkomponenten, die beide jeweils Primärschlüssel von Kern-Entitäten sind. Deshalb kann die Entität nicht attributiv
sein.
Man nennt solche Entitäten assoziative Entitäten, denn sie „repräsentieren“ die Assoziation (Beziehung) zwischen den beiden Kern-Entitäten.
Als Synonym für assoziative Entität wird wiederum oft die Bezeichnung schwache Entität
(weak entity) verwendet, da sie – genau wie attributive Entitäten – nicht ohne die referenzierten
Kern-Entitäten existieren können.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 22
fbi
h_da
1NF – Auflösung von n:m-Beziehungen
E1
E2
(r)
PK1
E1
Auflösung einer n:m-Beziehung
PK2
E1_E2
(i)
(i)
PK1
E2
PK2
ER-Modell
E1
E1_E2
PK1=PK1
PK1
E2
PK2=PK2
PK1
PK2
Relationenmodell
Schestag
PK2
Eine assoziative Entität kann auch mehr als zwei
Kern-Entitäten verbinden.
Sie enthält dann mehr als zwei Primär- bzw. Fremdschlüssel-Komponenten, nämlich genau so viele wie
die Anzahl der Kern-Entitäten, die sie verbindet.
Datenbanken (Bachelor)
Kapitel 3 - 23
fbi
h_da
1NF - Stückliste
•
Den Sonderfall der n:m-Beziehung einer Entität auf sich selbst bezeichnet
man als Stückliste oder auch part-of-Structure. Man spricht auch von
selbstreferenzierenden n:m-Beziehungen.
•
Der Begriff Stückliste kommt aus der Fertigungsindustrie.
Dort ist man, z.B. zur Generierung von Montage- und DemontageProtokollen, interessiert an Informationen darüber,
–
welche anderen Teile in einem bestimmten Teil enthalten sind, bzw.
–
in welchen anderen Teilen ein bestimmtes Teil enthalten ist.
•
Bezüglich der Rollen in den Beziehungen einer Stückliste spricht man
auch von parents und children (vgl. auch Folien 3-28 und 3.29).
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 24
fbi
h_da
1NF - Stückliste
Stückliste „Fahrrad“
MB_543
GABEL
MB_538
FAHRRAD
MB_541
SATTEL
MB_540
RAHMEN
MB_550
POLSTER
MB_556
LENKER
MB_542
ANTRIEB
MB_540
RAHMEN
MB_551
STANGE
MB_539
REIFEN
MB_557
STANGE
MB_549
LAGER
MB_546
MANTEL
MB_544
SCHLAUCH
MB_545
FELGE
MB_547
SPEICHE
MB_554
PEDAL
MB_553
KETTE
MB_552
KETTENRAD
MB_555
KURBEL
MB_548
VENTIL
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 25
1NF - Stückliste
fbi
h_da
Stückliste „Fahrrad“
- Ausschnitt besteht
bestehtaus
aus
ist
istBestandteil
Bestandteilvon
von
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 26
fbi
h_da
1NF - Stückliste
•
Bei der Auflösung einer selbstreferenzierenden n:m-Beziehung verfährt
man ganz analog der Auflösung von n:m-Beziehungen durch Einfügen
einer assoziativen Entität:
Teil
Teil
TNR
TBEZ
TNR
TBEZ
besteht
aus
(i)
(i)
Stückliste
ist Bestandteil von
ER-Modell
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 27
1NF - Stückliste
•
fbi
h_da
Die assoziative Entität Stückliste enthält beim Übergang zum Relationenmodell zwei Primärschlüssel-Komponenten vom Typ TNR, die durch
unterschiedliche Bezeichner eindeutig werden. PTNR referenziert auf die
parents in der Beziehung (ist enthalten in), CTNR auf die children (enthält).
Teil
TNR
TBEZ
Teil.TNR =
Stückliste.CTNR
Teil.TNR =
Stückliste.PTNR
Stückliste
PTNR
CTNR
PTNR = Parent-Teilenummer
CTNR = Child-Teilenummer
Relationenmodell
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 28
fbi
h_da
Die Daten der Fahrrad-Stückliste
•
Um die Fahrradstückliste zu implementieren, sind als zwei Relationen
(Tabellen) notwendig:
TEIL
Schestag
STÜCKLISTE
TNR
TBEZ
Einzelpreis
PTNR
CTNR
MB_538
Fahrrad
...
MB_538
MB_539
2
MB_539
Reifen
…
MB_538
MB_540
1
MB_540
Rahmen
…
MB_538
MB_541
1
MB_541
Sattel
...
MB_538
MB_542
1
MB_542
Antrieb
…
MB_539
MB_544
1
MB_543
Gabel
…
MB_539
MB_545
1
MB_544
Schlauch
…
MB_539
MB_546
1
MB_545
Felge
…
MB_539
MB_547
18
MB_546
Mantel
…
MB_539
MB_548
1
MB_547
Speiche
…
MB_539
MB_549
1
MB_548
Ventil
…
…
…
MB_549
Lager
…
…
…
…
Datenbanken (Bachelor)
Anzahl
…
Kapitel 3 - 29
Relationenmodell
fbi
h_da
9 Der Übergang vom ER-Modell zum Relationenmodell
•
Die Codd‘schen Normalformen
9 1 NF
– 2 NF
– 3 NF
•
Die Implementierung von Vererbungshierarchien
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 30
fbi
h_da
Die zweite Codd‘sche Normalform – 2NF
•
Die zweite Coddsche Normalform ist eine (optionale) Regel zur Vermeidung von Redundanzen - im Gegensatz zur ersten Coddschen Normalform, die immer erfüllt sein muss.
•
Die Überprüfung der 2NF ist nur relevant, wenn ein Primärschlüssel mit
mehr als einer Schlüssel-Komponente vorliegt:
Untersucht wird, ob jedes Attribut, das nicht Primärschlüssel-Komponente
ist, auch tatsächlich von allen Primärschlüssel-Komponenten abhängig ist:
"Jedes Attribut einer Entität bzw. jede Spalte einer Tabelle muss von allen
Primärschlüssel-Komponenten voll funktional abhängig sein."
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 31
fbi
h_da
2 NF – ein Beispiel
Beispiel „Benotung von Studenten”
•
Voraussetzung: Jede Lehrveranstaltung wird von genau einem Dozenten
gehalten und findet in genau einem Raum statt.
MatrikelNR
LVID
Bezeichnung
Dozent
Raum
Note
Pirmärschlüssel
•
Jeder Datensatz zur gleichen “LVID“ enthält als redundante
Informationen die Attribute “Bezeichnung“, “Dozent“ und “Raum“!
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 32
2 NF – ein Beispiel
fbi
h_da
Beispiel „Benotung von Studenten” (Fortsetzung)
Verletzung der 2NF
•
Die Attribute “Bezeichnung”, “Dozent” und “Raum” sind nicht funktional
abhängig von der primary key-Komponente “Matrikel-Nummer”.
Vorgehen bei der Normalisierung zur 2NF
•
Bei der Definition einer Entität mit mehreren Primärschlüssel-Komponenten muss jedes Attribut daraufhin untersucht werden, ob es wirklich von
der Gesamtheit aller Primärschlüssel-Komponenten abhängt oder nur von
einem Teil dieser Komponenten.
→
Attribute, die bereits durch einen Teil des Primärschlüssels
identifiziert werden, lagert man in eine eigene Entität aus.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 33
fbi
h_da
2 NF – ein Beispiel
Beispiel „Benotung von Studenten” (Fortsetzung)
Lehrveranstaltung
LVID
Bezeichnung
Dozent
Raum
Bewertung
MatrikelNR
→
LVID
Lehrveranstaltung.LVID
=
Bewertung.LVID
Note
“Lehrveranstaltung” wird zur Kern-Entität mit der attributiven Entität
“Bewertung”, die auch assoziative Entität zu den Kern-Entitäten
“Lehrveranstaltung” und “Student” mit Primärschlüssel “MatrikelNr” sein
könnte (bzw. entsprechende Relationen im Relationenmodell).
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 34
2 NF
fbi
h_da
•
Bei Normalisierung zur 2NF entstehen oft Kern-Entitäten zu attributiven
bzw. assoziativen Entitäten.
•
Die Einhaltung der 2NF muss also besonders bei der Bildung assoziativer
Entitäten beachtet werden:
→
Jedes Attribut einer solchen Entität darf nicht alleine von einer der
beteiligten Kern-Entitäten abhängen.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 35
Relationenmodell
fbi
h_da
9 Der Übergang vom ER-Modell zum Relationenmodell
•
Die Codd‘schen Normalformen
9 1 NF
9 2 NF
– 3 NF
•
Die Implementierung von Vererbungshierarchien
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 36
fbi
h_da
Die dritte Codd‘sche Normalform – 3NF
•
Die 3NF beschäftigt sich mit der transitiven Abhängigkeit von Attributen
zum Primärschlüssel der Entität (oder zu candidate keys).
•
Definition: Ein Attribut b ist transitiv abhängig (indirekt abhängig) von
einer Schlüssel-Komponente s, wenn es ein Attribut a gibt, so dass gilt:
a ist abhängig von s und b ist abhängig von a, also ist auch b (transitiv)
abhängig von s.
s
a
b
"Jedes Attribut einer Entität E, das nicht zu einem Primärschlüssel
(oder candidate key) von E gehört, hängt direkt, also nicht transitiv,
von diesem Schlüssel ab."
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 37
fbi
h_da
3 NF – ein Beispiel
Beispiel: Die Entität "Bestellung"
BNR
BDAT
LNR
LNAME
LADR
→ Die Attribute LNAME und LADR sind transitiv abhängig vom
Primärschlüssel-Attribut BNR, und zwar über das Attribut LNR.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 38
fbi
h_da
3 NF – ein Beispiel
Vorgehen bei der Normalisierung zur 3NF
•
•
Attribute, zu denen transitive Abhängigkeit besteht, werden in eine eigene
Entität ausgegliedert.
Im Beispiel ist dies die Entität Lieferant mit dem Schlüsselelement LNR.
Das Attribut LNR, das in der Entität Lieferant Primärschüssel ist, wird im
Relationenmodell zum Fremdschlüssel in der Tabelle Bestellung:
Bestellung
BNR
BDAT
LNR
Bestellung.LNR
=
Lieferant.LNR
Lieferant
LNR
Schestag
LNAME
LADR
Datenbanken (Bachelor)
Kapitel 3 - 39
Die ersten drei Codd‘schen Normalformen
fbi
h_da
Zusammenfassung
•
Im Relationenmodell ist die 1NF immer erforderlich.
•
Eine vollständige Normalisierung bzgl. aller anderen NF ist jedoch nicht
immer wünschenswert.
•
Der Grad der Normalisierung sollte den tatsächlichen Gegebenheiten
angepasst werden, z.B. den Zugriffshäufigkeiten in ihrer semantischen
Abhängigkeit.
•
Die Gewährleistung referentieller Integrität kann durch die Normalisierung
vor allem bei schreibenden Zugriffen erreicht werden, bei lesenden
Zugriffen ist diese grundsätzlich nicht gefährdet, und man kann zur
Verbesserung der Performance auf Normalisierung verzichten (man
spricht dann auch von Denormalisierung).
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 40
Denormalisierung
fbi
h_da
Beispiel
•
Die Entität Kunde enthalte das Schlüsselattribut KNR (Kundennummer)
und die Nicht-Schlüsselattribute PLZ und ORT.
→ Verletzung der 3NF, da ORT ausschließlich abhängig ist von PLZ, also
nur indirekt abhängig vom Schlüsselattribut KNR.
•
Trotzdem würde man hier nicht unbedingt normalisieren, da die
semantische Zusammengehörigkeit bei fast jedem Zugriff auf die PLZ
auch einen zusätzlichen Zugriff auf eine ausgelagerte Entität ORT
bedeuten würde
→ zusätzliche Joins, I/Os, Performanceverluste (vgl. auch Kapitel 4 und 5).
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 41
Relationenmodell
fbi
h_da
9 Der Übergang vom ER-Modell zum Relationenmodell
9 Die Codd‘schen Normalformen
9 1 NF
9 2 NF
9 3 NF
•
Die Implementierung von Vererbungshierarchien
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 42
Die Implementierung von Vererbungshierarchien
fbi
h_da
•
Vererbungshierarchien können im ER-Modell dargestellt werden mit den
Constraints
– disjoint und
– overlapping
für einzelne Spezialisierungsebenen (vgl. Kapitel 2).
•
Beim Übergang zum Relationenmodell können vier verschiedene
Implementierungsvarianten gewählt werden, die sich dadurch unterscheiden,
– welche Entity-Typen der Hierarchie als Tabellen implementiert werden
und
– welche Attribute vom Supertyp zum Subtyp vererbt werden.
Grundsätzlich gilt:
•
Ein Subtyp erbt immer mindestens den Primärschlüssel vom Supertyp.
Dieser wird in der Doppelfunktion Primär- und Fremdschlüssel in der
entsprechenden Tabelle implementiert.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 43
Vererbungshierarchien – eine Beispielhierarchie
Supertype-/Subtype-Hierarchie: Mitarbeiter
fbi
h_da
Mitarbeiter
PersNr
•
Mitarbeiter ist Supertype von
Sekretärin, Ingenieur, Techniker,
•
Ingenieur ist Supertype von
KfZ-Ing., Elektro-Ing.
•
etc. ...
IST-EIN (IS-A)
Sekretärin
Techniker
Ingenieur
...
...
...
IST-EIN (IS-A)
→
Im folgenden werden für diese
Beispielhierarchie die drei unterschiedlichen Implementierungsstrategien vorgestellt:
Schestag
Datenbanken (Bachelor)
KfZ-Ing.
...
Elektro-Ing.
...
Kapitel 3 - 44
fbi
h_da
Vererbung – Implementierungsstrategie 1
•
Der Supertyp und alle Subtypen werden im Relationenmodell als Tabellen
generiert, und
nur das Primärschlüssel-Attribut wird vom Supertyp an die Subtypen
vererbt.
•
Attribute
des Supertyp
Mitarbeiter
PNR <pk>
Name
...
Typ
Ingenieur
PNR <pk, fk>
Sparte
erbt PK des Supertyp
und enthält als zusätzliche
Spalten die speziellen
Attribute des Subtyp
→
Diese Implementierungsstrategie empfiehlt sich, wenn applikatorisch
sowohl häufig nur auf Mitarbeiter-Stammdaten (Supertyp) als auch auf
spezielle Mitarbeiter-Daten (Subtyp) zugegriffen wird.
→
Nachteil?
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 45
Vererbung – Implementierungsstrategie 2
•
•
Der Supertype und alle Subtypen werden im Relationenmodell als
Tabellen generiert, und
alle Attribute werden vom Supertype an die Subtypen vererbt.
Attribute
des Supertypes
→
→
fbi
h_da
Mitarbeiter
PNR <pk>
Name
...
Typ
Ingenieur
PNR <pk, fk>
Name
...
Typ
Sparte
alle Attribute
des Supertyp
Attribute
des Subtyp
Diese Implementierungsform empfiehlt sich dann, wenn zusätzlich zu den
speziellen Daten des Subtyp applikatorisch fast immer auch die
Mitarbeiter-Stammdaten benötigt werden.
Nachteil?
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 46
fbi
h_da
Vererbung – Implementierungsstrategie 3
•
→
Nur die Subtypes werden im Relationenmodell als Tabellen generiert.
Dann müssen alle Attribute vom Supertyp an jeden Subtyp vererbt und
dort als Spalten implementiert werden.
Ingenieur
PNR <pk>
Name
...
Typ
Sparte
→
→
Schestag
alle Attribute
des Supertyp
Attribute
des Subtyp
Diese Strategie empfiehlt sich, wenn auf den Supertyp alleine, im
Beispiel also auf die Mitarbeiter-Stammdaten, „fast nie“ zugegriffen
wird.
Nachteil?
Datenbanken (Bachelor)
Kapitel 3 - 47
fbi
h_da
Vererbung – Implementierungsstrategie 4
•
→
Nur der Supertyp wird im Relationenmodell als Tabelle generiert.
Dann müssen alle Attribute der Subtypes vom Supertyp verwaltet und
dort als Spalten implementiert werden.
Mitarbeiter
PNR <pk>
Name
...
Typ
Sparte_Ing
A1_Tec
A2_Tec
A1_xxx
…
→
→
Schestag
alle Attribute
des Supertyp …
… und alle Attribute
aller Subtypes
Diese Strategie empfiehlt sich, wenn (fast) ausschließlich auf die
Gesamtheit aller Mitarbeiter-Daten zugegriffen werden muss.
Nachteil?
Datenbanken (Bachelor)
Kapitel 3 - 48
Zusammenfassung
•
Der Übergang vom ER-Modell zum Relationenmodell findet beim Übergang
von der Analyse- zur Designphase statt:
–
–
–
–
–
•
•
•
•
fbi
h_da
das Ziel-DBMS wird bekannt gegeben,
Entity-Typen werden zu Relationen bzw. Tabellen,
Attribute werden zu Spalten,
entsprechende Datentypen werden den Spalten zugeordnet,
Beziehungen werden durch Fremdschlüsselspalten implementiert.
Die erste Codd‘sche Normalform ist obligatorisch und bewirkt, dass eine
Beziehung immer nur „in Richtung der 1“ implementiert werden kann!
Eine weitere Konsequenz der ersten Codd‘schen Normalform ist, dass eine
n:m-Beziehung beim Übergang zum Relationenmodell immer aufgelöst
werden muss in zwei 1:n-Beziehungen.
Die 2NF und 3NF können optional zur Vermeidung von Redundanzen
berücksichtigt werden – oft ist es aus Gründen der Performance jedoch
besser, nicht vollständig zu normalisieren bzw. zu denormalisieren.
Unter vier verschiedenen Implementierungsvarianten für Vererbungshierarchien wird unter Berücksichtigung der Prozesse auf den Daten gewählt.
Schestag
Datenbanken (Bachelor)
Kapitel 3 - 49
Datenbanken
9
Einführung
9
Semantische Datenmodellierung
9
Relationenmodell
4.
Interne Datenorganisation
5.
SQL - Structured Query Language
6.
Datenbank-Anwendungsentwicklung
7.
Transaktionsmanagement
8.
Rückblicke und Ausblicke
Schestag
Datenbanken (Bachelor)
fbi
h_da
Kapitel 3 - 50