Dr. Brigitte Mathiak Kapitel 7 Relationale Entwurfstheorie.

Dr. Brigitte Mathiak

Kapitel 7

Relationale Entwurfstheorie

Datenbanken für Mathematiker, WS 11/12 Kapitel 7: Relationale Entwurfstheorie 2

Lernziele

• Charakterisierung "guter" relationaler Schemata:

- jede Relation entspricht genau einer Objektmenge - eventuell unter Einbezug von N:1- oder 1:1-Relationships - oder genau einer Relationship-Menge zwischen Objekten

- Redundanz ist eliminiert, alle Informationen sind repräsentierbar, und es treten keinerlei "Änderungsanomalien" auf

a) Änderungen können bei Beachtung der Primärschlüssel- und Fremdschlüsselbedingung keine Inkonsistenzen hervorrufen

b) alle Informationen lassen sich unter Wahrung der Primärschlüssel- und Fremdschlüsselbedingung (ohne "Kunstgriffe") einfügen

c) Informationen können einzeln wieder gelöscht werden, ohne die Primärschlüssel oder Fremdschlüsselbedingung zu verletzen

• Ausnahmen wenn „schlechte“ Schemata sinnvoll sind

Erste Beispiel: „Schlechtes“ Datenbankschema

Warum ist das eine schlechte Idee?

Jeder für sich mit Zettel und Stift; 5 min


ProfVorlPersNr Name Titel

2125 Sokrates Ethik, Mäeutik, Logik

2126 Russel Erkenntnistheorie, Wissenschaftstheorie, Bioethik

2127 Kopernikus n.a.

… … …

Zu wenig Struktur

Pro Zelle darf immer nur ein Fakt stehen, sonst können diese nicht einzeln abgefragt werden.

… sonst können sie nicht einzeln referenziert werden. … sonst können keine Zusatzinformationen zu den Fakten

gespeichert werden. … sonst werden Einfüge- und Löschoperationen einzelner Fakten

zu komplexen Stringoperationen.

(Siehe auch die Probleme, die bei Aufgabenblatt 1 entstanden sind)

Wenn in jeder Zelle nur ein Fakt steht, heißt das

1. Normalform



Wie komme ich in die Erste Normalform?

Beispiel (falsch):

In 1 NF

Eltern

Vater Mutter Kinder

Johann Martha {Else, Lucie}

Johann Maria {Theo, Josef}

Heinz Martha {Cleo}

Eltern

Vater Mutter Kind

Johann Martha Else

Johann Martha Lucie

Johann Maria Theo

Johann Maria Josef

Heinz Martha Cleo

Aber …

… manchmal kann es sinnvoll sein, die Daten doch einfach so zu speichern. Zum Beispiel:

• Die einzelnen Fakten stehen getrennt gar nicht zur Verfügung• müssten erst aufwändig getrennt werden • werden getrennt nicht benötigt (Achtung!!! Könnte sich ändern)Weiterhin: Im letzten Kapitel haben wir gesehen, dass es einen Mismatch

zwischen relationalem Datenmodell und objektorientierten Datenmodell besteht.

Es gibt Datenbanken (objektrelationale), die es erlauben direkt Objekte und Listen zu speichern und dann auch darauf zuzugreifen.

Nachteil: Unterobjekte können nicht mehr direkt zugegriffen werden, es muss über die Oberobjekte navigiert werden. Das Schema wird komplexer, Anfragen werden schwieriger...



Exkurs: NF2-Relationen

Non-First Normal-Form-RelationenGeschachtelte Relationen (Oracle: Nested Tables)

ElternVater Mutter Kinder

KName KAlter

Johann Martha Else 5

Lucie 3

Johann Maria Theo 3

Josef 1

Heinz Martha Cleo 9


Beispiel 2: "Schlechte" Relationenschemata

ProfVorlPersNr Name Rang Raum VorlNr Titel SWS

2125 Sokrates C4 226 5041 Ethik 4

2125 Sokrates C4 226 5049 Mäeutik 2

2125 Sokrates C4 226 4052 Logik 4

... ... ... ... ... ... ...

2132 Popper C3 52 5259 Der Wiener Kreis 2

2137 Kant C4 7 4630 Die 3 Kritiken 4


Beispiel 2: "Schlechte" Relationenschemata

Update-AnomalienSokrates zieht um, von Raum 226 in R. 338. Was passiert?

Einfüge-AnomalienNeue/r Prof ohne Vorlesungen?

LöschanomalienLetzte Vorlesung einer/s Profs wird gelöscht? Was passiert?

ProfVorlPersNr Name Rang Raum VorlNr Titel SWS

2125 Sokrates C4 226 5041 Ethik 4

2125 Sokrates C4 226 5049 Mäeutik 2

2125 Sokrates C4 226 4052 Logik 4

... ... ... ... ... ... ...

2132 Schlick C3 52 5259 Der Wiener Kreis 2

2137 Kant C4 7 4630 Die 3 Kritiken 4

Beispiel 2: „Bessere" Relationenschemata

Sokrates zieht um, von Raum 226 in R. 338. Was passiert?Neue/r Prof ohne Vorlesungen?Letzte Vorlesung einer/s Profs wird gelöscht? Was passiert?

Mit den aufgeteilten Relationen treten die Anomalien nicht mehr auf.

Woher kann man wissen wann man aufteilen muss und wie und wann nicht?


ProfessorenPersNr Name Rang Raum

2125 Sokrates C4 226

2126 Russel C4 232

2127 Kopernikus C3 310

2133 Popper C3 52

2134 Augustinus C3 309

VorlesungenVorlNr Titel SWS gelesen

Von

5001 Grundzüge 4 2137

5041 Ethik 4 2125

5043 Erkenntnistheorie 3 2126

5049 Mäeutik 2 2125

4052 Logik 4 2125


Funktionale Abhängigkeiten

Schema R = {A, B, C, D}Ausprägung Q

Seien R, Rgesprochen:bestimmtgenau dann wenn

RA B C D

a4 b2 c4 d3

a1 b1 c1 d1

a1 b1 c1 d2

a2 b2 c3 d2

a3 b2 c4 d3

{A} {B}

{C, D} {B}

Nicht: {B} {C}

Notationskonvention:

CD B


Beispiel

Stammbaum

Kind Vater Mutter Opa Oma

Sofie Alfons Sabine Lothar Linde

Sofie Alfons Sabine Hubert Lisa

Niklas Alfons Sabine Lothar Linde

Niklas Alfons Sabine Hubert Lisa

... ... ... Lothar Martha

… … … … …


Beispiel

Kind Vater, MutterKind, Opa OmaKind, Oma Opa

Stammbaum

Kind Vater Mutter Opa Oma

Sofie Alfons Sabine Lothar Linde

Sofie Alfons Sabine Hubert Lisa

Niklas Alfons Sabine Lothar Linde

Niklas Alfons Sabine Hubert Lisa

... ... ... Lothar Martha

… … … … …


Bestimmung funktionaler Abhängigkeiten

Professoren: { PersNr, Name, Rang, Raum, Ort, Straße, PLZ, Vorwahl, Bland, EW, Landesregierung }

{PersNr} { Name, Rang, Raum, Ort, Straße, PLZ, Vorwahl, Bland, EW, Landesregierung }

{Ort, BLand} { EW, Vorwahl } {PLZ} { Bland, Ort, EW } {Bland, Ort, Straße} { PLZ } {Bland} { Landesregierung } {Raum} { PersNr }

Falle: {Straße} {PLZ} gilt nicht, auch wenn es im konkreten Datensatz gilt

Beispiel: Hauptstraße Funktionale Abhängigkeiten gelten nicht nur für den konkreten

Datensatz, sondern sind von der Semantik der Relation abhängig Weiteres Beispiel: {Vorname} {Geschlecht} Gegenbeispiel: Kim


Graphische Darstellung der funktionalen Abhängigkeiten

Landesregierung

Rang

Name

Straße

Ort

BLand

PersNr

Raum

Vorwahl

PLZ

EW


Normalformen: informeller Überblick

1NF: „Geometrische Form“ der TupelDie Zahl der Attribute in allen Tupeln der Relation ist identisch

2NF, 3NF: Beziehungen zwischen Schlüsselattributen und Nicht-Schlüsselattributen (funktionale Abhängigkeiten)

2NF: verletzt, wenn ein nicht-Schlüsselattribut nicht von allen Schlüsselattributen funktional abhängt (Der Schlüssel ist aufteilbar)

3NF: verletzt, wenn ein nicht-Schlüsselattribut funktionale Abhängigkeit von einem anderen Nicht-Schlüsselattribut aufweist (Es gibt Unterschlüssel)

2NF und 3NF sind erfüllt, wenn jedes Attribut entweder Teil des Schlüssels ist oder funktional von dem ganzen Schlüssel (und nur von ihm) abhängt

BCNF: Verschärfung, um Anomalien durch teilweise überlappende Schlüsselkandidaten (möglich in 3NF) auszuschließen

jede Determinante (Menge von Attributen, von denen einige anderen Attribute voll funktional abhängen) ist Schlüsselkandidat

4NF: beschreibt mehrwertige AbhängigkeitenEs darf nicht mehrere, voneinander unabhängige, 1:n-Beziehungen in einer Relation geben

5NF: Relation lässt sich nicht weiter aufspalten, ohne dass Information verloren geht


Schlüssel

R ist ein Super-Schlüssel, falls folgendes gilt: R

ist voll funktional abhängig vongenau dann wenn gilt

und kann nicht mehr verkleinert werden, d.h.

Notation für volle funktionale Abhängigkeit: R ist ein Kandidaten-Schlüssel, falls folgendes gilt:

R

Attribut B heißt Schlüsselattribut, wenn B in einem von Kandidaten-Schlüsseln R vorkommt


Schlüsselbestimmung

Kandidaten-schlüssel von Städte: {Name,BLand} {Name,Vorwahl}

Keine Kandidaten-Schlüssel: {Vorwahl}, {Name}

Städte

Name BLand Vorwahl EW

Frankfurt Hessen 069 650000

Frankfurt Brandenburg 0335 84000

München Bayern 089 1200000

Mülheim-Kärlich RLP 0261 10416

Koblenz RLP 0261 106681


Zweite Normalform (2NF)

Eine Relation R mit zugehörigen FDs FR ist in zweiter Normalform, falls jedes Nichtschlüssel-Attribut AR voll funktional abhängig ist von jedem Kandidatenschlüssel der Relation.

Informell: Jedes Attribut hängt vom ganzen Schlüssel ab.

Studentenbelegung ist nicht in 2NF {MatrNr} {Name} {MatrNr} {Semester} {MatrNr,VorlNr} {Name, Semester}

StudentenBelegung

MatrNr VorlNr Name Semester

26120 5001 Fichte 10

27550 5001 Schopenhauer 6

27550 4052 Schopenhauer 6

28106 5041 Carnap 3

28106 5259 Carnap 3

... ... … ...


Zweite Normalform: Beispiel

Einfügeanomalie: Was macht man mit Studenten, die keine Vorlesungenen hören?

Updateanomalien: Wenn z.B. Carnap ins vierte Semester kommt, muss man sicherstellen, dass beide Tupel geändert werden.

Löschanomalie: Was passiert wenn Fichte ihre einzige Vorlesung absagt?

Lösung: Zerlegung in zwei Relationen hören: {[MatrNr, VorlNr]} Studenten: {[MatrNr, Name, Semester]}

Beide Relationen sind in 2NF – erfüllen sogar noch „höhere“ Gütekriterien ~ Normalformen.

MatrNr

VorlNr

Name

Semester


Dritte Normalform (3NF)

Ein Relationenschema R ist in dritter Normalform, wenn für jede für R geltende funktionale Abhängigkeit der Form Bmit B R mindestens eine von drei Bedingungen gilt:

B , d.h., die FD ist trivial oder

Das Attribut B ist ein Schlüsselattribut oder

ist Superschlüssel von R

Informell: Die Nicht-schlüssel dürfen nicht untereinander abhängig sein.

Studenten

MatrNr Fachbereich Standort Name Semester

26120 Informatik Koblenz Fichte 4

27550 Informatik Koblenz Meyer 8

27551 Psychologie Landau Carnap 5

Studenten ist nicht in 3NF {Fachbereich} {Standort}

Wie kann man die 3NF immer erreichen?

Ziel: Zerlegung von R mit vorgegebenen FDs F

in R1, ..., Rn , wobei folgende Kriterien erfüllt sind:

Zerlegung von R in R1, ..., Rn erfolgt verlustlos.

Zerlegung R1, ..., Rn ist abhängigkeitserhaltend.

Alle R1, ..., Rn sind in 3NF

Synthesealgorithmus



Zerlegung (Dekomposition) von Relationen

Korrektheitskriterien für die Zerlegung von Relationenschemata:

1. Verlustlosigkeit- Die in der ursprünglichen Relationenausprägung Q des Schemas R

enthaltenen Informationen müssen aus den Ausprägungen Q1, ..., Qn der neuen Relationenschemata R1, .., Rn rekonstruierbar sein.

2. Abhängigkeitserhaltung- Die für R geltenden funktionalen Anhängigkeiten

müssen auf die Schemata R1, ..., Rn übertragbar sein.


Kriterien für die Verlustlosigkeit einer Zerlegung

Die Zerlegung von R in R1 und R2 ist verlustlos,

falls für jede mögliche (gültige) Ausprägung Q gilt:

Hinreichende Bedingung für die Verlustlosigkeit einer Zerlegung

RR1

R2


Biertrinker-Beispiel

Biertrinker

Lokal Gast Bier

Einstein Gniffke Pils

Einstein Anders Hefeweizen

Mephisto Gniffke Hefeweizen


Biertrinker-Beispiel: Verlustige ZerlegungBiertrinker

Lokal Gast Bier




Besucht := [Lokal, Gast] (Biertrinker)Lokal Gast

Einstein Gniffke

Einstein Anders

Mephisto Gniffke

Trinkt := [Gast, Bier] (Biertrinker)Gast Bier

Gniffke Pils

Anders Hefeweizen

Gniffke Hefeweizen

Besucht |×| Trinkt

Lokal Gast Bier


Einstein Gniffke Hefeweizen


Mephisto Gniffke Pils



Erläuterung des Biertrinker-Beispiels

Unser Biertrinker-Beispiel war eine "verlustige" Zerlegung und dementsprechend war die hinreichende Bedingung verletzt. Es gilt nämlich nur die eine nicht-triviale funktionale Abhängigkeit {Lokal,Gast} {Bier}

Wohingegen keine der zwei möglichen, die Verlustlosigkeit garantierenden FAs gelten {Gast} {Bier} {Gast} {Lokal}

Das liegt daran, dass die Leute (insbesondere Gniffke) in unterschiedlichen Lokalen unterschiedliches Bier trinken. In demselben Lokal aber immer das gleiche Bier

- (damit sich die Kellner darauf einstellen können?)


Verlustfreie Zerlegung

Eltern

Vater Mutter Kind

Johann Martha Else

Johann Maria Theo

Heinz Martha Cleo

Väter := [Vater, Kind] (Eltern)

Vater Kind

Johann Else

Johann Theo

Heinz Cleo

Mütter := [Mutter, Kind] (Eltern)

Mutter Kind

Martha Else

Maria Theo

Martha CleoVäter |×| Mütter

Vater Mutter Kind

Johann Martha Else

Johann Maria Theo

Heinz Martha Cleo


Erläuterung der verlustfreien Zerlegung der Eltern-Relation

Eltern: {[Vater, Mutter, Kind]}Väter: {[Vater, Kind]}Mütter: {[Mutter, Kind]}

Verlustlosigkeit ist garantiertEs gilt nicht nur eine der hinreichenden FAs, sondern gleich beide

{Kind} {Mutter} {Kind} {Vater}

Also ist {Kind} natürlich auch der Schlüssel der Relation Eltern

Die Zerlegung von Eltern ist zwar verlustlos, aber auch ziemlich unnötig, da die Relation in sehr gutem Zustand (~Normalform) ist


Abhängigkeitsbewahrung

R ist zerlegt in R1, ..., Rn FR = (FR1 ... FRn) bzw. FR

+ = (FR1 ... FRn)+

Beispiel für Abhängigkeitsverlust: PLZverzeichnis: {[Straße, Ort, Bland, PLZ]}

Annahmen Orte werden durch ihren Namen (Ort) und das Bundesland (Bland)

eindeutig identifiziert Innerhalb einer Straße ändert sich die Postleitzahl nicht Postleitzahlengebiete gehen nicht über Ortsgrenzen und Orte,

nicht über Bundeslandgrenzen hinweg

Daraus resultieren die FDs {PLZ} {Ort, BLand} {Straße, Ort, BLand} {PLZ}

Betrachte die Zerlegung Straßen: {[PLZ, Straße]} Orte: {[PLZ, Ort, BLand]}


Zerlegung der Relation PLZverzeichnis

PLZverzeichnis

Ort BLand Straße PLZ

Frankfurt Hessen Goethestraße 60313

Frankfurt Hessen Galgenstraße 60437

Frankfurt Brandenburg Goethestraße 15234

Straßen := [PLZ, Straße] (PLZverzeichnis)

PLZ Straße

15234 Goethestraße

60313 Goethestraße

60437 Galgenstraße

Orte := [Ort, BLand] (PLZverzeichnis)

Ort BLand PLZ

Frankfurt Hessen 60313


Frankfurt Brandenburg 15234

Die FD {Straße, Ort, BLand} {PLZ} ist im zerlegten Schema nicht mehr enthalten Einfügen inkonsistenter Tupel möglich



PLZverzeichnis


Frankfurt Hessen Goethestrasse 60313

Frankfurt Hessen Galgenstrasse 60437

Frankfurt Brandenburg Goethestrasse 15234


PLZ Straße

15234 Goethestrasse

60313 Goethestrasse

15235 Goethestrasse

60437 Galgenstrasse


Ort BLand PLZ







Strassen |×| Orte







PLZ Straße

15234 Goethestrasse

60313 Goethestrasse

15235 Goethestrasse

60437 Galgenstrasse


Ort BLand PLZ





Die FD {Straße, Ort, BLand} {PLZ} wird verletzt


Boyce-Codd-Normalform (BCNF)

BCNF = eine weitere Verschärfung der 3NF.

Ein Relationenschema R mit FDs F ist in BCNF, wenn für jede für R geltende funktionale Abhängigkeit der Form F gilt:

entweder , d.h., die Abhängigkeit ist trivial, oder ist ein Superschlüssel von R

Man kann jede Relation verlustlos in BCNF-Relationen zerlegenManchmal lässt sich dabei die Abhängigkeiterhaltung aber

nicht erzielen


Beispiel: Relation Städte ist in 3NF, aber nicht in BCNF

Städte: {[Ort, BLand, Ministerpräsident/in, EW]}

Geltende FDs: {Ort, BLand} {EW} {BLand} {Ministerpräsident/in} {Ministerpräsident/in} {BLand}

Schlüsselkandidaten: {Ort, BLand} {Ort, Ministerpräsident/in}


Dekomposition der Relation Städte in BCNF-Relationen

Städte: {[Ort, BLand, Ministerpräsident/in, EW]}Geltende FAs:

{BLand} {Ministerpräsident/in} {Ort, BLand} {EW} {Ministerpräsident/in} {BLand}

Ri1: Regierungen: {[BLand, Ministerpräsident/in]}

Ri2: Städte: {[Ort, BLand, EW]}

Zerlegung ist verlustlos und auch abhängigkeitserhaltend


Dekomposition des PLZverzeichnis in BCNF-Relationen

PLZverzeichnis: {[Straße, Ort, Bland, PLZ]}

Funktionale Abhängigkeiten: {PLZ} {Ort, BLand} {Straße, Ort, BLand} {PLZ}

Betrachte die Zerlegung Orte: {[PLZ, Ort, BLand]} Straßen: {[PLZ, Straße]}

Diese Zerlegung ist verlustlos aber Nicht abhängigkeitserhaltend {Straße, Ort, BLand} {PLZ} wird weder in Orte noch in Straßen

komplett erfasst



PLZverzeichnis






PLZ Straße

15234 Goethestrasse

60313 Goethestrasse

15235 Goethestrasse

60437 Galgenstrasse


Ort BLand PLZ







Strassen |×| Orte







PLZ Straße

15234 Goethestrasse

60313 Goethestrasse

15235 Goethestrasse

60437 Galgenstrasse


Ort BLand PLZ





Die FA {Straße, Ort, BLand} {PLZ} wird verletzt

Aber…

… manchmal möchte man doch, dass die Fakten in einer Relation zusammen stehen.

Warum???


Data Warehouse (ganz kurz)

Das Data Warehouse dient dem Reporting von wirtschaftsrelevanten Daten an z.B. das Management.

Ziel: Zu bestimmten Zeitpunkten alle relevanten Daten aus der Datenbank zu ziehen und dann für statistische Untersuchungen zur Verfügung zu stellen

Diese Daten werden typischerweise in nur ganz wenige Relationen geschrieben (Stichwort Sternschema),

• da sie sich nicht ändern werden• da man sich so Joins bei den Abfragen

spart• da man so automatisch statische

Analysen laufen lassen kann

Datenbanken für Mathematiker, WS 11/12 Kapitel 7: Relationale Entwurfstheorie 41Aus Wikipedia (Stern_Schema)

Triplifizierung (vereinfacht)

Vor- und Nachteile?Es ist alles in einer TabelleEs ist alles maximal normalisiert


TripleStore

Subjekt Prädikat Objekt

Professor hatPrädikat hatRaum

Sokrates istInstanzVon Professor

Sokrates hatRaum 226

hatRaum hatObjektTyp integer

Sokrates hältVorlesung Ethik

Ethik hatSWS 4

hört hatSubjektTyp Mensch

Professor istEin Mensch

Student istEin Mensch

hört hatObjektTyp Vorlesung

Carnap hört Ethik

Carnap istInstanzVon Student

… … …

Triplifizierung (vereinfacht)

Woher weiß ich, ob die Daten überhaupt stimmen?Woher weiß ich, was Daten sind und was zur Struktur gehört?


TripleStore

Subjekt Prädikat Objekt

Professor hatPrädikat hatRaum


Sokrates hatRaum 226

hatRaum hatObjektTyp integer

Sokrates hältVorlesung Ethik

Ethik hatSWS 4

hört hatSubjektTyp Mensch

Professor istEin Mensch

Student istEin Mensch

hört hatObjektTyp Vorlesung

Carnap hört Ethik

Carnap istInstanzVon Student

… … …

TripleStoreSubjekt Prädikat Objekt

Professor istInstanz nein


Sokrates istInstanz ???

Triplifizierung (und verwandte Methoden z.B. DOM)

• Wird eingesetzt, wenn die Datenstruktur nicht bekannt ist oder sich noch ändern kann

• Eindeutiger Vorteil ist die Vernetzbarkeit. Jemand anders kann seinen eigenen TripleStore einfach ankoppeln und schon hat man doppelt so viele Daten

• Ein Nachteil ist die Überprüfung von Konsistenz• Was ist in dem Zusammenhang überhaupt eine Inkonsistenz?• A istInstanzVon B und B istInstanzVon A (schwierig)• A istKindVon B und B istKindVon C und C istKindVon A

(schwierig zu berechnen)• Das Problem ist verwandt mit Logik

• Weiterer Nachteil ist die Navigierbarkeit: • Beispiel: Gib alle Personen aus (erfordert transitive Hülle)

• Es ist deutlich einfacher, wenn man die Prädikate und Typen von vornherein festlegt, leider auch weniger flexibel


Fazit

Vorteile der Normalisierung• Es können keine Anomalien auftreten

Nachteil der Normalisierung• Unter Umständen werden Anfragen langsamer, da mehr Joins

notwendig sind

• Es gibt verschiedene Standardmodelle, die nicht normalisiert oder sogar übernormalisiert sind

• Vor- und Nachteile sind im Einzelfall abzuwägen

• Im Normalfall (z.B. in der Prüfung) gilt 3 NF ist am Besten


Dr. Brigitte Mathiak Kapitel 7 Relationale Entwurfstheorie.

Documents

Transcript of Dr. Brigitte Mathiak Kapitel 7 Relationale Entwurfstheorie.