DB10-Normalformen - schmiedecke.info

Datenbanken 1
für Medienformatiker
WS 06
Num
mer
Bezeichn
ung
1
Untertasse
Grös
se
14
Inh
alt
0
Funkti
on
-1
2
Teetasse
12
0.2
1
3
10
0.15
1
4
Kaffeetass
e
Milchkanne
30
1
1
5
Schale
15
0.25
1
6
Schale
25
1.2
1
7
Schale
35
3.3
1
8
Teekanne
25
2
1
Dek
or
Ros
e
Ros
e
Ros
e
Wic
htel
Fisc
h
Fisc
h
neu
tral
Fisc
h
Glasu
r
graub
lau
graub
lau
graub
lau
türkis
steing
rau
steing
rau
siena
graub
lau
Pr
eis
6
0
9.5
1
8.5
1
12
0
15
0
25
0
42
0
11
0
Angebot_I
D
Gehoert
_zu
0
Angebot_I
D
Produk
t
Produ
kt
Mar
kt
1
1
1
2
2
1
Mark
3 t
1
1
1
2
2
1
3
Angebot_I
4
D
3
Produk
4
t
1
Mark
2
t
1
1
1
2
2
1
3
3
1
4
4
2
4
3.4. Anomalien und Normalformen
Anomalien
Normalisierung
3Standort 1
Berlin-2
Spandau
BerlinSpandau
BerlinSpandau
Standort
Berlin-Kladow
4
BerlinSpandau
BerlinSpandau
BerlinSpandau
Berlin-Kladow
Standort
BerlinSpandau
BerlinSpandau
BerlinSpandau
BerlinKladow
Anomalien
•
Unzulässige Zustände des DB-Schemas:
– Verletzungen der referentiellen Integrität
– Widersprüche
•
Abgewendet durch das DBMS:
– Verletzungen der referentiellen Integrität
•
Problematisch:
– Widersprüche zwischen "echten" Attributen
(NSA – Nicht-Schlüssel-Attribute)
– Grund: Redundanz
– Teilweise "versteckte" Redundanz: im Entwurf nicht unmittelbar ersichtlich
Spender
Termin
• Name
• Adresse
• Telefon
• Blutgruppe
• Datum
• Zeit
• Spender
• ErreichbarUnter
(c) schmiedecke 06
DbMz9-Normalformen
...planmäßig ist die TelefonNummer des Spenders 2x
in der Datenbank. Was ist,
wenn sie sich ändert?
2
Anomalien
Strukturen in einem DB-Schema, die durch DML-Operationen zu
Widersprüchen in der Datenbasis führen können, heißen
Anomalien.
• Insert-Anomalie:
– Einfügen neuer Datensätze erfordert Daten, die eigentlich aus einem
anderen Zusammenhang stammen
• Udate-Anomalie:
– Änderung eines Datensatzes führt zu Widersprüchen in anderen
Datensätzen
• Delete-Anomalie:
– Beim Löschen gehen Daten verloren, die erhalten bleiben sollten.
(c) schmiedecke 06
DbMz9-Normalformen
3
Normalformenlehre
(nach Codd)
• Ziel:
– redundanzarme Relationen
– Relationen mit strengem Sachzusammenhang
• Vorgehen:
– kaskadierte Transformationen des DB-Schemas
– sukzessive Entfernung von Redundanzen
– Transformation von einer Normalform in die nächste
• Transformationsprinzip:
–
–
–
–
Separierung redundanter Informationen in extra Relationen.
Dualitätsforderung: kein Informationsverlust durch Umformung
Hoher Normalisierungsgrad – geringe Redundanz
Preis: zusätzliche Relationen, Performanceverlust bei Zugriffen (viele
Joins)
•(c) schmiedecke
Realistischer
Normalisierungsgrad:
3NF
06
DbMz9-Normalformen
4
1. Normalform (1NF)
Beispiel 1
Termin
Zeit
Platz
Verwendung
Name
Vorname
PLZ
Ort
Tel.
04.04.04
8:00
B
Vollblut
Meier
Sepp
13465
Berlin
030-8080808
0177-8080808
04.04.04
8:30
B
Vollblut
Schulze
Lisa
123353
Berlin
030-4445566
0331-678967
02.03.04
12:00
P
Plasma
Petersen
Jutta
31224
Peine
05171-5544
05171-5599
0176-2221122
Anomalien:
Attributwerte von Tel. sind nicht einzeln bearbeitbar
konsistente Änderung einer Handynummer in allen betroffenen Tupeln gar nicht
durchführbar.
1.Normalform:
In einer Relation gibt es nur atomare Attribute
(c) schmiedecke 06
DbMz9-Normalformen
5
1. Normalform (1NF)
Beispiel 1
Termin
Zeit
Platz
Verwendung
Name
Vorname
PLZ
Ort
Tel.
04.04.04
8:00
B
Vollblut
Meier
Sepp
13465
Berlin
030-8080808
0177-8080808
04.04.04
8:30
B
Vollblut
Schulze
Lisa
123353
Berlin
030-4445566
0331-678967
02.03.04
12:00
P
Plasma
Petersen
Jutta
31224
Peine
05171-5544
05171-5599
0176-2221122
1.Normalform:
In einer Relation gibt es nur atomare Attribute
Transformation:
- Strukturierte Attribute: Aufteilung auf mehrere Spalten
- Listenattribute: eigene Tabelle
Termin
Zeit
Platz
Verwendung
Name
Vorname
PLZ
Ort
04.04.04
8:00
B
Vollblut
Meier
Sepp
13465
Berlin
04.04.04
8:30
B
Vollblut
Schulze
Lisa
123353
Berlin
(c) schmiedecke 06
Termin
Zeit
Platz
Tel.
04.04.04
8:00
B
030-8080808
04.04.04
8:00
B
0177-8080808
DbMz9-Normalformen
6
1. Normalform (1NF)
Beispiel 2
SpenderNr
Name
Telefon
Blutgr.
Termin
KonsNr
Charge
Abn
.
Freigabe
2884
Marten Harms
033099-22233
0177-777777
A+
16.09.05
Q123S456
1609061
Sz
Frey
2885
Sonja Selig
0178-226688
030-225588
A-
16.09.05
R877A054
1609061
Sz
Pohl
2886
Sakina Schmidt
0163-333555
0
19.09.05
M123F123
1909053
Gk
Pohl
Anomalien:
Attributwerte von Tel. sind nicht einzeln bearbeitbar
konsistente Änderung einer Handynummer in allen betroffenen Tupeln gar nicht
durchführbar.
1.Normalform:
In einer Relation gibt es nur atomare Attribute
(c) schmiedecke 06
DbMz9-Normalformen
7
1. Normalform (1NF)
Beispiel 2
SpenderNr
Name
Telefon
Blutgr.
Termin
KonsNr
Abn.
Freigabe
2884
Marten Harms
033099-22233
0166-554433
A+
16.09.05
Q123S456
Sz
Frey
2885
Sonja Selig
0178-226688
A-
16.09.05
R877A054
Sz
Pohl
2886
Sakina Schmidt
0163-333555
0
19.09.05
M123F123
Gk
Pohl
1.Normalform:
In einer Relation gibt es nur atomare Attribute
Transformation:
- Strukturierte Attribute: Aufteilung auf mehrere Spalten
- Listenattribute: eigene Tabelle
Spender
2884
2884
SpenderNr
Name
Blut
gr.
Termin
KonsNr
Abn.
Freigabe
2884
Marten Harms
A+
16.09.05
Q123S456
Sz
Frey
2885
Sonja Selig
A-
16.09.05
R877A054
Sz
Pohl
2886
Sakina Schmidt
0
19.09.05
M123F123
Gk
Pohl
Telefon
033099-22233
0166-554433
(c) schmiedecke 06
DbMz9-Normalformen
8
Funktionale Abhängigkeit
Datenabhängigkeiten weisen auf Redundanzen hin:
•
Funktionale Abhängigkeit:
xy (y ist funktional abhängig von x),
wenn alle Tupel mit gleichem x auch in y übereinstimmen
d.h. y ist x eindeutig zugeordnet.
Spender Blutgruppe
Alle Attribute sind vom Primärschlüssel funktional abhängig
•
Voll-funktionale Abhängkeit:
sei x' eine beliebige Teilmenge von x, dann ist y voll-funktional abhängig
von x, wenn gilt:
xy
und es gibt kein x' so dass x'y
(z.B. Abhängigkeit vom gesamten Primärschlüssel)
(Spender, Datum) Konserve
(c) schmiedecke 06
DbMz9-Normalformen
9
2. Normalform (2NF)
Beispiel 1
Termin
Zeit
Platz
Verwendung
Name
Vorname
PLZ
Ort
04.04.04
8:00
B
Vollblut
Meier
Sepp
13465
Berlin
04.04.04
8:30
B
Vollblut
Schulze
Lisa
13353
Berlin
02.03.04
12:00
P
Plasma
Petersen
Jutta
31224
Peine
Problem:
Platz B ist nur für Vollblut-Spenden, Platz P nur für Plasma.
Damit tritt die Verwendung redundant auf.
2.Normalform:
Die Relation ist in der 1NF, und alle Nicht-Schlüssel-Attribute sind vom
gesamten Primärschlüssel voll-funktional abhängig.
(c) schmiedecke 06
DbMz9-Normalformen
10
2. Normalform (2NF)
Beispiel 1
Termin
Zeit
Platz
Verwendung
Name
Vorname
PLZ
Ort
04.04.04
8:00
B
Vollblut
Meier
Sepp
13465
Berlin
04.04.04
8:30
B
Vollblut
Schulze
Lisa
13353
Berlin
02.03.04
12:00
P
Plasma
Petersen
Jutta
31224
Peine
2.Normalform:
Die Relation ist in der 1NF, und alle Nicht-Schlüssel-Attribute sind vom
gesamten Primärschlüssel voll-funktional abhängig.
Transformation:
Auslagerung des Primärschlüsselanteils und des funktional abhängigen NSA.
Termin
Zeit
Platz
Name
Vorname
PLZ
Ort
04.04.04
8:00
B
Meier
Sepp
13465
Berlin
04.04.04
8:30
B
Schulze
Lisa
13353
Berlin
(c) schmiedecke 06
DbMz9-Normalformen
Platz
Verwendung
B
Vollblut
P
Plasma
11
2. Normalform (2NF)
Beispiel 2
SpenderNr
Name
Blutgr.
Termin
KonsNr
Charge
Abn.
Freigabe
2884
Marten Harms
A+
16.09.05
Q123S456
1609061
Sz
Frey
2885
Sonja Selig
A-
16.09.05
R877A054
1609061
Sz
Pohl
2886
Sakina Schmidt
0
19.09.05
M123F123
1909053
Gk
Pohl
Problem: Blutgruppe ist Eigenschaft von Spender, nicht von (Spender, Termin).
Tritt redundant bei jeder Spende desselben Spenders auf.
( Praktisch nicht problematisch, da nicht veränderlich. Allerdings müssten
genauere Bestimmungen nachgetragen werden.)
2.Normalform:
Die Relation ist in der 1NF, und alle Nicht-Schlüssel-Attribute sind vom
gesamten Primärschlüssel voll-funktional abhängig.
(c) schmiedecke 06
DbMz9-Normalformen
12
2. Normalform (2NF)
Beispiel 2
SpenderNr
Name
Blutgr.
Termin
KonsNr
Charge
Abn.
Freigabe
2884
Marten Harms
A+
16.09.05
Q123S456
1609061
Sz
Frey
2885
Sonja Selig
A-
16.09.05
R877A054
1609061
Sz
Pohl
2886
Sakina Schmidt
0
19.09.05
M123F123
1909053
Gk
Pohl
2.Normalform:
Die Relation ist in der 1NF, und alle Nicht-Schlüssel-Attribute sind vom
gesamten Primärschlüssel voll-funktional abhängig.
Transformation:
Auslagerung des Primärschlüsselanteils und des funktional abhängigen NSA.
SpenderNr
Name
BG
2884
Marten Harms
A+
2885
Sonja Selig
A-
(c) schmiedecke 06
Termin
Spender
KonsNr
Charge
Abn.
Freigabe
16.09.05
2884
Q123...
1609...
Sz
Frey
16.09.05
2885
R877...
1609..
Sz
Pohl
DbMz9-Normalformen
13
Vorteil einwertiger Primärschlüssel
• Einwertige Primärschlüssel bedingen Einhaltung der 2NF
(funktional abhängig = voll funktional abhängig)
• Surrogatschlüssel anstelle mehrwertiger Primärschlüssel erzwingen
2NF
• aber Probleme bei 3NF...
(c) schmiedecke 06
DbMz9-Normalformen
14
Transitive Abhängigkeit
• Direkte Abhängigkeit:
funktionale Abhängigkeit vom Primarschlüssel
• Transitive Abhängkeit:
(zusätzliche) funktionale Abhängigkeit von Nicht-SchlüsselAttributen.
Transitive Abhängigkeit bedeutet, dass Änderungen von NSA
Folgeänderungen erforderlich machen
- andernfalls entsteht ein widersprüchlicher Zustand.
(c) schmiedecke 06
DbMz9-Normalformen
15
3. Normalform (3NF)
Beispiel 1
Termin
Zeit
Platz
Name
Vorname
PLZ
Ort
04.04.04
8:00
B
Meier
Sepp
13465
Berlin
04.04.04
8:30
B
Schulze
Lisa
13353
Berlin
02.03.04
12:00
P
Petersen
Jutta
31224
Peine
3.Normalform:
Die Relation ist in der 2NF, und es existieren keine transitiven
Abhängigkeiten unter Nicht-Schlüssel-Attributen.
Transformation:
Auslagerung in eine extra Relation.
Termin
Zeit
Platz
Name
Vorname
PLZ
04.04.04
8:00
B
Meier
Sepp
13465
04.04.04
8:30
B
Schulze
Lisa
13353
(c) schmiedecke 06
DbMz9-Normalformen
PLZ
Ort
13465
Berlin
13353
Berlin
31224
Peine
16
3. Normalform (3NF)
Beispiel 2
SpenderNr
Termin
KonsNr
Charge
Abn.
Freigabe
2884
16.09.05
Q123S456
1609061
Sz
Frey
2885
16.09.05
R877A054
1609061
Sz
Frey
2886
19.09.05
M123F123
1909053
Gk
Pohl
Problem:
Abnehmer hängt funktional von der Charge ab (PersonalwechselChargenwechsel), ebenso die
Freigabe.
Transitive Abhängigkeit vom Primärschlüssel
Insert-Anomalie:
Bei jedem Neu-Eintrag müssen Charge, Abnehmer und Freigabe "passen"
Update-Anomalie:
Ein irrtümlich falsch aus dem Schichtplan entnommener Abnahmeeintrag muss bei allen
Konserven der Charge geändert werden.
Delete-Anomalie: -
3.Normalform:
Die Relation ist in der 2NF, und es existieren keine funktionalen
(c) schmiedecke
06
DbMz9-Normalformen
17
Abhängigkeiten
unter Nicht-Schlüssel-Attributen
(transitiven Abhängigkeiten).
3. Normalform (3NF)
Beispiel 2
SpenderNr
Termin
KonsNr
Charge
Abn.
Freigabe
2884
16.09.05
Q123S456
1609061
Sz
Frey
2885
16.09.05
R877A054
1609061
Sz
Frey
2886
19.09.05
M123F123
1909053
Gk
Pohl
3.Normalform:
Die Relation ist in der 2NF, und es existieren keine funktionalen
Abhängigkeiten unter Nicht-Schlüssel-Attributen (transitiven
Abhängigkeiten).
Transformation:
Auslagerung der funktionalen Abhängigkeit in eine eigene Relation.
Charge
Abn.
Fregabe
1609061
Sz
Frey
1909053
Gk
Pohl
(c) schmiedecke 06
SpenderNr
Termin
KonsNr
Charge
2884
16.09.05
Q123S456
1609061
2885
16.09.05
R877A054
1609061
2886
19.09.05
M123F123
1909053
DbMz9-Normalformen
18
3. Normalform (3NF)
Beispiel 2 – Schritt 2
SpenderNr
Termin
KonsNr
Charge
2884
16.09.05
Q123S456
1609061
2885
16.09.05
R877A054
1609061
2886
19.09.05
M123F123
1909053
Weiteres Problem:
Charge ist funktional abhängig von der Konservennummer.
(Grund: Konservennummer ist Schlüsselkandidat)
Unmittelbare Transformation:
Auslagerung in Extra-Relation
KonsNr
Charge
Q123S456
1609061
R877A054
1609061
M123F123
1909053
Naheliegend:
"Schlüsseltausch" - aber dann existieren funktional.Abh. vom alten Primärschlüssel...
SpenderNr
Termin
KonsNr
Charge
Freigabe
2884
16.09.05
Q123S456
1609061
Frey
2885
16.09.05
R877A054
1609061
Frey
M123F123
1909053
(c) schmiedecke 06
2886
19.09.05
DbMz9-Normalformen
Pohl
Fazit:
Mehrere Schlüsselkandidaten
nicht 3NF ....
19
Problem Surrogatschlüssel
• Surragotschlüssel anstelle natürlicher Primärschlüssel führen immer
zur Verletzung der 3NF.
• Update-Anomalie:
Veränderungen des natürlichen Primärschlüssels können ohne
zugehörige Änderungen der anderen NSA erfolgen.
(c) schmiedecke 06
DbMz9-Normalformen
20
Denormalisierung
•
Weitere Normalisierungsstufen führen zu einer weiter gehenden
Aufsplittung der Informationsbasis
Unübersichtliches Logischens Schema und
Performanceverlust bei Zugriffen.
•
Realistisch wird bis zur 3NF normalisiert.
•
Aus Effizienzgründen wird teilweise eine gezielte Denormalisierung
vorgenommen - auf Kosten der Integritätssicherheit.
•
Alternativ ermöglichen Views ein Arbeiten mit scheinbar denormalisierten
Tabellen.
(c) schmiedecke 06
DbMz9-Normalformen
21
Denormalisierung
Beispiel 2
SpenderNr
Termin
KonsNr
Charge
2884
16.09.05
Q123S456
1609061
2885
16.09.05
R877A054
1609061
2886
19.09.05
M123F123
1909053
Problem:
Die fehlende Angabe der Blutgruppe in der Konserventabelle erfordert einen
Join bei jedem Zugriff.
Lösung:
Teilweise Denormalisierung – Redundanz in Kauf genommen
SpenderNr
Termin
KonsNr
Charge
BG
2884
16.09.05
Q123S456
1609061
A+
2885
16.09.05
R877A054
1609061
A-
2886
19.09.05
M123F123
1909053
0
(c) schmiedecke 06
DbMz9-Normalformen
22
Weblink
sehr gute Darstellung der Normalisierung unter
http://cis.cs.tu-berlin.de/Lehre/WS-0203/Sonstiges/dbpages/FolienVL/vl04-4.pdf
(c) schmiedecke 06
DbMz9-Normalformen
23
Relationale Operationen
•
•
•
•
•
•
Das zweite wichtige Thema im Zusammenhang mit der Theorie des
Relationalen Datenmodells:
Grundlegende Mengenoperationen, die den Abfragen zugrunde liegen.
Die mengentheoretische Kombination von Tabellen ermöglicht den Aufbau
von temporären Tabellen in Abfragen.
Besonders wichtig und praktisch relevant sind dabei vor allem die JOINS
(so in die SQL eingegangen):
Full Join, Right/Left Join, Natural Join
Ähnlich mächtig ist das Zusammenfassen mehrerer gleichstrukturierter
Abfrageergebnisse zu einer Zwischentabelle mit Hilfe des
Vereinigungsoperators Union.
Alle Operationen lassen sich auch mit Hilfe von Select und Where
nachbilden – sie ermöglichen nur eine elegantere und damit lesbarere
Formulierung.
(c) schmiedecke 06
DbMz9-Normalformen
24
Relationenalgebra
• Klassische Mengenoperationen (Vereinigung, Durchschnitt, etc.)
können auf Relationen angewendet werden,
• wenn sie vereinigunsverträglich sind,
d.h. vom selben Grad und je Attribut typverträglich.
• Die Algebra ist abgeschlossen, d.h. das Ergebnis einer
Relationenoperation ist wieder eine Relation.
• Zweck: Ableitung von Relationen aus Relationen als Grundlage für
Anfragen.
∪ Stammspender
Neuspender
Spender
SpenderNr
Name
SpenderNr
Name
SpenderNr
Name
2884
Marten Harms
2653
Bert Winter
2884
Marten Harms
2885
Sonja Selig
2544
Renate Sommer
2885
Sonja Selig
2653
Bert Winter
2544
Renate Sommer
(c) schmiedecke 06
DbMz9-Normalformen
25
Operationen der
Relationenalgebra
SQL-Befehl
UNION
MINUS
INTERSECT
FROM
SELECT
WHERE
WHERE
JOIN
(c) schmiedecke 06
DbMz9-Normalformen
26
... und damit:
Schluss für heute
☺
Was noch fehlt:
Trigger (und PLSQL)
ODBC
Entwurfskonzepte