Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Kommunikation und Datenhaltung

Relationaler Entwurf

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Überblick über den Datenhaltungsteil •

Einleitung • •

Motivation und Grundlagen Architektur von Datenbanksystemen

•

Datenbankanfragen • Relationenmodell und Relationenalgebra • Relationale Datenbanksprachen (SQL)

•

Datenbankentwurf • ER- und EER-Modell • Abbildung von ER-Modellen auf das Relationenmodell • Relationaler Entwurf • Sprachen zur Datenbankdefinition

•

Transaktionsverwaltung

•

Anfrageoptimierung

•

Datenbankanwendungsentwicklung

Kommunikation und Datenhaltung, Sommersemester 2009

2

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

3

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Relationaler DB-Entwurf: Überblick Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Thema dieses Kapitels: Was ist ein guter logischer Datenbankentwurf? Darstellung anhand des Relationenmodells. • Ziel, genauer: Vermeidung von Redundanzen durch Aufspalten von Relationenschemata, ohne gleichzeitig • semantische Informationen zu verlieren (Abhängigkeitstreue), • die Möglichkeit zur Rekonstruktion der Relationen zu verlieren (Verbundtreue).

• Redundanzvermeidung durch Normalformen. Kommunikation und Datenhaltung, Sommersemester 2009

4

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Universalrelation Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Begrifflichkeit, die weiter hinten gebraucht wird. • Gegeben Menge von Relationen R1, ..., Rn, ist R= R1 R2 ... Rn die Universalrelation von R1, ..., Rn. • Beispiel: PANr PLZ

PLZ Ort

Ort Bundesland

Schema der Universalrelation: PANr PLZ Ort Bundesland

• Universalschlüssel – Schlüssel der Universalrelation. Kommunikation und Datenhaltung, Sommersemester 2009

5

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

6

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Funktionale Abhängigkeiten – Beispiel • Relation: Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Invnr 0007 1201 1201

Titel Dr. No Objektbanken Objektbanken

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl

• Was wäre bessere Modellierung? Invnr Titel 0007 Dr. No 1201 Objektbanken

ISBN 3-125 3-111

Kommunikation und Datenhaltung, Sommersemester 2009

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl 7

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Funktionale Abhängigkeiten I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Definition: Funktionale Abhängigkeit einer Relation zwischen Attributmengen X und Y (X  Y), wenn in jedem Tupel der Relation der Attributwert unter den X-Komponenten den Attributwert unter den Y-Komponenten festlegt. • Funktionale Abhängigkeit (kurz: FD, von functional dependency), Schreibweise: X  Y

Kommunikation und Datenhaltung, Sommersemester 2009

8

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Funktionale Abhängigkeiten II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Im Beispiel auf vorangegangener Folie: ISBN  Titel • Nicht: ISBN  Autor • Auch mehrere Attribute auf der linken Seite möglich. • Aus Definition folgt trivialerweise: ISBN → ISBN, Titel → Titel etc.

Kommunikation und Datenhaltung, Sommersemester 2009

9

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Funktionale Abhängigkeiten III Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Im Folgenden unterschiedliche Notationen mit gleicher Bedeutung (X, Y, Z Attributmengen): • XYZ • XYZ

• ‚X  Y Z‘ also dasselbe wie ‚X  Z Y‘.

Kommunikation und Datenhaltung, Sommersemester 2009

10

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Schlüssel als Spezialfall Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Schlüssel Spezialfall der funktionalen Abhängigkeit. • Für Beispiel auf folgender Folie PANr  Vorname, Nachname, PLZ, Ort, GebDatum

• Immer: PANr  PANr, dann gesamtes Schema auf rechter Seite. • Schlüssel X liegt vor, wenn für Relationenschema R FD X  R gilt, und X minimal.

Kommunikation und Datenhaltung, Sommersemester 2009

11

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Schlüssel im Beispiel Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Personen PANr 4711 5588 6834 8832 9999

Vorname Andreas Gunter Michael Tamara Christa

Pers_Telefon PANr 4711 4711 5588 5588 9999

Nachname PLZ Heuer 18209 Saake 39106 Korn 39104 Jagellovsk 38106 Loeser 69121

Ort DBR MD MD BS HD

GebDatum 31.10.1958 05.10.1960 24.09.1974 11.11.1973 10.05.1969

Telefon 038203-12230 0381-498-3401 0391-345677 0391-5592-3800 06221-400177

Kommunikation und Datenhaltung, Sommersemester 2009

12

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

FDs enthalten semantische Information I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Festlegung der FDs Bestandteil des Schemaentwurfs. • FDs beschreiben Anwendungsszenario genauer (beinhalten semantische Information).

Kommunikation und Datenhaltung, Sommersemester 2009

13

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

FDs enthalten semantische Information II • Beispiel: Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Vorlesung

ID Professor Titel Jahr Raum

• Titel  Professor • Titel, Jahr  Professor • "Raum ändert sich nicht über die Zeit." • "Raum kann sich von Jahr zu Jahr ändern, nicht aber im Laufe des Jahres."

Kommunikation und Datenhaltung, Sommersemester 2009

14

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Schlüssel vs. FDs Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Kommerzielle DBMSe unterstützen Überprüfung von FDs i. Allg. nicht. • Schlüssel dagegen schon. • Überprüfung der Schlüsseleigenschaft mit Index-Unterstützung effizient möglich.

Kommunikation und Datenhaltung, Sommersemester 2009

15

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

FDs vs. Schlüsselabhängigkeiten Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Ziel des Datenbankentwurfs: Alle gegebenen funktionalen Abhängigkeiten in „Schlüsselabhängigkeiten“ umformen, ohne dabei semantische Informationen zu verlieren.

Kommunikation und Datenhaltung, Sommersemester 2009

16

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

17

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Lokale Integritätsbedingung – Beispiel Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

•

r

•

r‘

• •

r, r‘REL({Name, Alter, Haarfarbe}) bn sei Festlegung, daß Name Schlüssel von R.

•

bn(r) = true, bn(r‘) = false

•

rSAT{Name, Alter, Haarfarbe}({bn}), r‘SAT{Name, Alter, Haarfarbe}({bn}).

Name Andreas Gunter Michael

Alter 43 42 25

Haarfarbe blond blond schwarz

Name Andreas Andreas Michael

Alter 43 42 25

Haarfarbe blond blond schwarz

Kommunikation und Datenhaltung, Sommersemester 2009

18

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitbarkeit von FDs I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

r A a1 a2 a3 a4

B b1 b1 b2 b1

C c1 c1 c1 c1

• Es gilt A  B und B  C. • Dann gilt auch A  C. • Nicht ableitbar C  A oder C  B.

Kommunikation und Datenhaltung, Sommersemester 2009

19

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitung von FDs II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Notation im Folgenden: F Menge von FDs, f einzelne FD. • Gilt für f über R SATR(F)  SATR(f), dann impliziert F die FD f. • Abkürzung: F |= f (manchmal auch ‚F  f‘) • Beispiel: SAT({A,B,C})({AB, B C})  SAT({A,B,C})({AC}) r A a1 a2 a3

B b1 b1 b2

C c1 c1 c1

Kommunikation und Datenhaltung, Sommersemester 2009

r‘ A a1 a2 a3 a3

B b1 b1 b2 b1

C c1 c1 c1 c1 20

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitung von FDs III Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel von eben: F = {A  B, B  C} |= A  C • Gilt F |= X  X (für beliebige F und X)? Ja, denn SATR(F)  REL(R) = SATR({XX})

Kommunikation und Datenhaltung, Sommersemester 2009

21

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitung von FDs IV Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Hüllenbildung: FR+ := {f | (f FD über R)  F |= f} • Beispiel: • F = {A  B, B  C} • A  C  F{A,B,C}+ • Weiteres Beispiel: F = {PNr  PLZ, PLZ  Stadt, Stadt  Land} F{PNr, PLZ, Stadt, Land}+ = ?

Kommunikation und Datenhaltung, Sommersemester 2009

22

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitungsregeln I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Ziel im Folgenden: Berechnung der Hülle. • Formulierung von Ableitungsregeln, die Implikation simulieren. • Konkrete Regeln: Name Regel R Reflexivität {}  XX A Akkumulation {X  YZ, Z  VW}  X  YZV P Projektivität {X  YZ}  XY

Kommunikation und Datenhaltung, Sommersemester 2009

23

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitungsregeln II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Illustration Reflexivität: X → X, XY → X etc. triviale funktionale Abhängigkeiten! • Illustration Projektivität {X  YZ}  X  Y ISBN  Autor Verlag  ISBN  Autor.

Kommunikation und Datenhaltung, Sommersemester 2009

24

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitungsregeln III • Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Illustrationen Akkumulation {X  YZ, Z  VW}  X  YZV • {ISBN  Verlag Autor, Autor  Straße Ort}  ISBN  Verlag Autor Straße • {ISBN  Verlag Autor, Autor  Straße Ort}  ISBN  Verlag Autor Straße Ort • {ISBN  Verlag Autor, Autor  Straße Ort}  ISBN  Verlag Autor

Kommunikation und Datenhaltung, Sommersemester 2009

25

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Ableitungsregeln IV Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Wünschenswerte Eigenschaften. RAP-Regeln haben diese Eigenschaften: • gültig (sound), • vollständig (complete), • unabhängig (independent) oder auch bezüglich  minimal. (Keine Ableitungsregel kann weggelassen werden, ohne Vollständigkeit zu verletzen.)

Kommunikation und Datenhaltung, Sommersemester 2009

26

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Membership-Problem I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Kann eine bestimmte FD X  Y aus der vorgegebenen Menge F abgeleitet werden, d.h. wird sie von F impliziert? • Membership-Problem: „X  Y  F+ ?“ • Naives Vorgehen: F+ erzeugen; dann prüfen, ob X  Y  F+. • Im Folgenden effizienteres Vorgehen.

Kommunikation und Datenhaltung, Sommersemester 2009

27

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Membership-Problem II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Hülle einer Attributmenge X bezüglich F ist XF* := {A | X  A  F+}. • Illustration: F = {PNr  PLZ, PLZ  Stadt, Stadt  Land} • {Stadt}F* = {Stadt, Land} • {PLZ}F* = {PLZ, Stadt, Land} • {PLZ, Stadt}F* = {PLZ, Stadt, Land}

Kommunikation und Datenhaltung, Sommersemester 2009

28

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Membership-Problem III Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Das Membership-Problem „X  Y  F+ ?“ kann nun durch das modifizierte Problem Membership-Problem (2): „Y  XF* ?“ in linearer Zeit (linear in der Anzahl Attribute) gelöst werden.

Kommunikation und Datenhaltung, Sommersemester 2009

29

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

RAP-Algorithmus I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Ziel: Lösung des Membership-Problems: „X  Y  F+ ?“ Durch „Y  XF* ?“ • Regeln: Abk. R A P

Name Reflexivität Akkumulation Projektivität

Regel {}  X  X {X  YZ, Z  VW}  X  YZV {X  YZ}  X  Y

• Abarbeitung der Regeln in Reihenfolge R, A, P. • F = {A  B, B  C}. Gilt A  C  F+ ? Kommunikation und Datenhaltung, Sommersemester 2009

30

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

RAP-Algorithmus II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Ziel: Lösung des Membership-Problems: „X  Y  F+ ?“ F = {A  B, B  C}. Gilt A  C  F+ ? • Algorithmus: 1. Setze X* := X (R-Regel für X). 2. Gibt es FD fi := Xi  Yi  F mit Xi  X*? 3. Wenn ja, dann wird X* gemäß X* :=X*  Yi vergrößert (A-Regel). 4. Führe Schritt 2 und 3 so lange aus, bis X* stabil (Hülle). 5. Ist Y  X*, dann ist X  Y  F+. (P-Regel)

Kommunikation und Datenhaltung, Sommersemester 2009

31

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Überdeckungen Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• F heißt äquivalent zu G oder ‚F Überdeckung von G‘ (kurz: F  G) falls F+ = G+.

Kommunikation und Datenhaltung, Sommersemester 2009

32

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Diskussion Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Zusammenfassung des bisher Gesagten: • Hülle einer Menge F von FDs • welche FDs impliziert F? • Wie überprüft man, ob bestimmte FD in der Hülle enthalten? • Warum diese Überlegungen? • Wichtig für Schema-Umformungen zur Vermeidung der Redundanzen. • Wurde hier noch nicht angesprochen, kommt aber.

Kommunikation und Datenhaltung, Sommersemester 2009

33

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

34

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Bücher-Relation mit Redundanzen I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Relation: Invnr Titel 0007 Dr. No 1201 Objektbanken 1201 Objektbanken

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl

• Was wäre bessere Modellierung?

Invnr Titel 0007 Dr. No 1201 Objektbanken

ISBN 3-125 3-111

Kommunikation und Datenhaltung, Sommersemester 2009

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl 35

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Bücher-Relation mit Redundanzen II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Redundanzen in Basisrelationen unerwünscht: • Belegen unnötigen Speicherplatz (eher unwichtig), • Information redundant. • Widersprüchliche Eingaben für identische Informationen möglich. • Änderung muß diese Information parallel in allen ihren Vorkommen verändern; in relationalen Systemen nur schwer realisierbar.

Kommunikation und Datenhaltung, Sommersemester 2009

36

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Updateanomalie Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Vorlesung VNr Bez 123 Kommunikation und Datenhaltung 456 Verteilte Datenhaltung 789 WorkflowManagement  



PANr 321

Name Büro E-Mail Böhm 367 boehm

321

Böhm 367

boehm

432

Mülle

muelle

370

Beispiel: Büro von Böhm ändert sich. Ändern mehrerer Einträge erforderlich.  aufwendig,  fehleranfällig. Lässt sich durch Aufspalten vermeiden!

Kommunikation und Datenhaltung, Sommersemester 2009

37

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Einfügeanomalie Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Vorlesung VNr Bez 123 Kommunikation und Datenhaltung 456 Verteilte Datenhaltung 789 WorkflowManagement   

PANr 321

Name Büro E-Mail Böhm 367 boehm

321

Böhm 367

boehm

432

Mülle

muelle

370

Beispiel: Neuer Dozent, ohne Vorlesung. Null-Werte. Was ist mit dem Schlüssel unserer Relation?

Kommunikation und Datenhaltung, Sommersemester 2009

38

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Löschanomalie Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Vorlesung VNr Bez 123 Kommunikation und Datenhaltung 456 Verteilte Datenhaltung 789 WorkflowManagement 



PANr 321

Name Büro E-Mail Böhm 367 boehm

321

Böhm 367

boehm

432

Mülle

muelle

370

Beispiel: Mülle hält Vorlesung Workflow-Management nicht mehr. Löschen des Tupels  ‚Mülle-Information‘ geht verloren.

Kommunikation und Datenhaltung, Sommersemester 2009

39

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

40

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Transformations- und Schemaeigenschaften Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Eigenschaften, die wir erreichen wollen. • Relationenschemata, Schlüssel und Fremdschlüssel so wählen, dass • alle Anwendungsdaten aus den Basisrelationen hergeleitet werden können (Verbundtreue), • nur semantisch sinnvolle und konsistente Anwendungsdaten dargestellt werden können (Abhängigkeitstreue), und • die Daten möglichst nicht-redundant dargestellt werden.

Kommunikation und Datenhaltung, Sommersemester 2009

41

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Bücher-Daten – Darstellungen Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Relation: Invnr Titel 0007 Dr. No 1201 Objektbanken 1201 Objektbanken

ISBN 3-125 3-111 3-111

• Alternative Modellierung:

Invnr Titel 0007 Dr. No 1201 Objektbanken

ISBN 3-125 3-111

Autor James Bond Heuer Scholl ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl

• Basisrelation vs. Anwendungsdaten. Kommunikation und Datenhaltung, Sommersemester 2009

42

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Abhängigkeitstreue Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Allgemein: Menge der erfaßten Abhängigkeiten äquivalent zur Menge der im System darstellbaren Abhängigkeiten (etwa Schlüssel und Fremdschlüssel); Schlüsseleigenschaft vom DBMS leicht abprüfbar. • Hier spezieller: Menge der FDs äquivalent zur Menge der Schlüsselabhängigkeiten. D. h. ohne semantische Information zu verlieren.

Kommunikation und Datenhaltung, Sommersemester 2009

44

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Abhängigkeitstreue: Beispiel Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

•

Attribute: PLZ(P), Ort(O), Straße(S), Hausnr.(H),

• •

funktionale Abhängigkeiten F: OSH  P, P  O Datenbankschema S: (OSHP, {OSH})

•

Menge der Schlüsselabhängigkeiten von S OSH  OSHP nicht äquivalent zu F; S ist nicht abhängigkeitstreu.

•

Beispiel: Ort Bonames N.-Eschbach

•

Straße Frankfurter Landstr. Frankfurter Landstr.

Hausnr. 100 152

PLZ 6000 6000

Hält Schlüsselabhängigkeit ein, aber nicht F.

Kommunikation und Datenhaltung, Sommersemester 2009

45

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Abhängigkeitstreue formal Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• S={(R1, K1), ..., (Rp, Kp)} lokal erweitertes Datenbankschema, F Menge von Abhängigkeiten. • S charakterisiert vollständig F (oder: ist abhängigkeitstreu bezüglich F) genau dann, wenn F  {K  R | (R, K)  S, K  K}. • Vgl. vorangegangene Folie: Wir können F nicht aus Schlüsseleigenschaften herleiten.

Kommunikation und Datenhaltung, Sommersemester 2009

46

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Verbundtreue I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Originalrelation soll aus zerlegten Relationen mit natürlichem Verbund zurückgewonnen werden können.

Kommunikation und Datenhaltung, Sommersemester 2009

47

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Beispielrelationen zur Verbundtreue I • Originalrelation: A B C Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

FDs: AB, CB

1 4

2 2

3 5

• Dekomposition A B (abhängigkeitstreu): 1 2 4

B 2 2

2

• Verbund (nicht verbundtreu):

Kommunikation und Datenhaltung, Sommersemester 2009

A 1 4 1 4

B 2 2 2 2

C 3 5 C 3 5 5 3 48

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Beispielrelationen zur Verbundtreue II • Originalrelation: A B C Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

FDs: AB, BC

1 4

2 2

3 3

• Dekomposition A B (abhängigkeitstreu): 1 2 4

B 2

C 3

2

• Verbund (verbundtreu): A B C 1 4

Kommunikation und Datenhaltung, Sommersemester 2009

2 2

3 3

49

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Verbundtreue formal Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Definition: Dekomposition einer Attributmenge X in X1, ..., XP mit

X =  Xi p i=1

heißt verbundtreu ( -treu, lossless) bezüglich einer Menge von Abhängigkeiten G über X genau dann, wenn rSATX(G): X1(r) ... Xp(r)=r gilt. • Verbundtreue ist Eigenschaft des Schemas, der FDs und der Zerlegung.

Kommunikation und Datenhaltung, Sommersemester 2009

52

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Kriterien für Verbundtreue I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Einfaches Kriterium für zwei Relationenschemata: Dekomposition von X in X1 und X2 ist verbundtreu bezüglich F, wenn X1  X2  X1  F+ oder X1  X2  X2  F+.

•

Beweis: • Negation der Aussage: Wenn nicht verbundtreu, dann gilt keine jener FDs. • O.b.d.A. ABC aufspalten in AB, BC. Nach Join Tupel abc, der nicht in Ausgangsrelation war. In Ausgangsrelation waren also abc‘ sowie a‘bc. D. h. keine jener FDs gilt.

Kommunikation und Datenhaltung, Sommersemester 2009

53

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Kriterien für Verbundtreue II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Allgemeineres Kriterium: G Menge funktionaler Abhängigkeiten. Dann gilt für eine abhängigkeitstreue Zerlegung:  i  {1, ..., p}: Xi  X  G+  Die Dekomposition von X in X1, ..., Xp ist verbundtreu bezüglich G. •

Minimale Teilmenge von Xi: Universalschlüssel, da es gesamte Menge X funktional bestimmt.

•

Beispiel erster Fall (F = {A  B, C  B}): AC der einzige Universalschlüssel, in keinem Relationenschema enthalten.

•

Beispiel zweiter Fall (F‘ = {A  B, B  C}): Universalschlüssel A.

Kommunikation und Datenhaltung, Sommersemester 2009

54

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Transformationseigenschaften Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Transformationseigenschaft

Kurzcharakteristik

Abhängigkeitstreue

alle gegebenen Abhängigkeiten sind durch Schlüssel repräsentiert die Originalrelationen können durch Verbund der Basisrelationen wiedergewonnen werden

Verbundtreue

Kommunikation und Datenhaltung, Sommersemester 2009

55

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

56

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Schema-Eigenschaften Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Eigenschaften, die wir erreichen wollen. • Relationenschemata, Schlüssel und Fremdschlüssel so wählen, daß • alle Anwendungsdaten aus den Basisrelationen hergeleitet werden können (Verbundtreue), • nur semantisch sinnvolle und konsistente Anwendungsdaten dargestellt werden können (Abhängigkeitstreue), und • die Daten möglichst nicht-redundant dargestellt werden.

Kommunikation und Datenhaltung, Sommersemester 2009

57

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Erste Normalform • Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Erste Normalform (1NF): Nur atomare Attribute in Relationenschemata

Nicht in erster Normalform (Autoren mengenwertig):

Invnr 0007 1201

Titel ISBN Dr. No 3-125 Objektbanken 3-111

Autoren {James Bond} {Heuer, Scholl}

In erster Normalform:

Invnr 0007 1201 1201

Titel Dr. No Objektbanken Objektbanken

ISBN 3-125 3-111 3-111

Kommunikation und Datenhaltung, Sommersemester 2009

Autor James Bond Heuer Scholl 58

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zweite Normalform I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Zweite und weitere Normalformen: Aufgrund der Struktur von Abhängigkeiten Redundanzen entdecken. • Partielle Abhängigkeit liegt vor, wenn ein Attribut funktional schon von einem Teil des Schlüssels abhängt. • Zweite Normalform: 1NF + Keine partiellen Abhängigkeiten zwischen einem Schlüssel und weiteren Nicht-Primattributen. (Primattribut – Attribut, das Bestandteil des Schlüssels ist.)

Kommunikation und Datenhaltung, Sommersemester 2009

59

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zweite Normalform II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel: Invnr Titel ISBN Autor 0007 Dr. No 3-125 James Bond 1201 Objektbanken 3-111 Heuer 1201 Objektbanken 3-111 Scholl • Schlüssel: Autor, ISBN. • Was kann passieren? • Mehrere Titel für das gleiche Buch abbildbar. • Autor nicht bekannt/nicht vorhanden – Buch kann nicht erfaßt werden.

Kommunikation und Datenhaltung, Sommersemester 2009

60

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zweite Normalform III Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel: Invnr Titel 0007 Dr. No 1201 Objektbanken 1201 Objektbanken

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl

• Was wäre bessere Modellierung?

Invnr Titel 0007 Dr. No 1201 Objektbanken

ISBN 3-125 3-111

Kommunikation und Datenhaltung, Sommersemester 2009

ISBN 3-125 3-111 3-111

Autor James Bond Heuer Scholl 61

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zweite Normalform IV Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel: • ISBN → Titel, Invnr, und ISBN, Autor → Invnr, Titel, ISBN, Autor • ISBN und Autor bilden Schlüssel. • Titel, Invnr hängen aber allein von ISBN ab. • Zweite Normalform erreichen durch Elimination der rechten Seite der partiellen Abhängigkeit und Kopie der linken Seite.

Kommunikation und Datenhaltung, Sommersemester 2009

62

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zweite Normalform – weiteres Beispiel MatrNr 26120 27550 27550 28106 28106 28106 28106 ...

Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• • • •

VorlNr 5001 5001 4052 5041 5052 5216 5259 ...

Name Fichte Schopenhauer Schopenhauer Carnap Carnap Carnap Carnap ...

Semester 10 6 6 3 3 3 3 ...

Was für Redundanzen? Was für Schlüssel? Was für FDs? Was für partielle Abhängigkeiten? Normalisierungsergebnis?

Kommunikation und Datenhaltung, Sommersemester 2009

63

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dritte Normalform I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Dritte Normalform: 1NF + Keine transitiven Abhängigkeiten zwischen einem Schlüssel und weiteren Nicht-Primattributen. • Transitive Abhängigkeit: Schlüssel K bestimmt Attributmenge X funktional, diese aber auch eine Attributmenge Y Transitive Abhängigkeit K  X  Y •

Formal: Relationenschema R, X  R und F eine FD-Menge über R. A є R heißt transitiv abhängig von X bzgl. F genau dann, wenn es ein Y  R gibt mit X  Y, !(Y  X), Y  A, !(A є XY)

Kommunikation und Datenhaltung, Sommersemester 2009

64

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dritte Normalform II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel: PANr KB EB PP RR

PLZ 61352 61352 61352 61348

Ort Bad Homburg Bad Homburg Bad Homburg Bad Homburg

• Ist diese Relation in 2NF? • Information, daß zur PLZ '61352' der Ort 'Bad Homburg' gehört, ist redundant. • PANr  PLZ und PLZ  Ort Kommunikation und Datenhaltung, Sommersemester 2009

65

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dritte Normalform III • Beispiel: Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

PANr KB EB PP RR

PLZ 61352 61352 61352 61348

Ort Bad Homburg Bad Homburg Bad Homburg Bad Homburg

• Bessere Repräsentation: PLZ PANr PLZ 61352 KB 61352 61348 EB 61352 PP 61352 RR 61348 Kommunikation und Datenhaltung, Sommersemester 2009

Ort Bad Homburg Bad Homburg

66

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dritte Normalform IV Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Dritte Normalform erreichen durch Elimination von Y und Kopie von X. • Transitive Abhängigkeit K  X  Y

Kommunikation und Datenhaltung, Sommersemester 2009

67

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Boyce-Codd Normalform (BCNF) I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Definition: Relationenschema R mit FDs F ist in BCNF, wenn es in 1NF ist und für jede FD  →  eine der folgenden Bedingungen gilt:    (d. h. triviale Abhängigkeit) oder  Superschlüssel von R. • „Superschlüssel“ einer Relation R nennt man eine Attributmenge , für die gilt  → R. • Ugs.: Ein Superschlüssel ist also ein Schlüssel, der nicht zwangsweise minimal ist bzgl. der Attributmenge 

Kommunikation und Datenhaltung, Sommersemester 2009

68

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Boyce-Codd Normalform (BCNF) II • Beispiel: Städte(Ort, BLand, MinPräs, EW) Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

fd3

fd2 fd1

Schlüssel: {Ort, BLand} und {Ort, MinPräs}

• 3NF? • Redundanz – Info, wer welches BL regiert. Beispiel:

Ort Mannheim Heidelberg Karlsruhe

BLand BW BW BW

MinPräs Oettinger Oettinger Oettinger

Kommunikation und Datenhaltung, Sommersemester 2009

EW 220000 120000 200000 69

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

BCNF – Zerlegung I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Dekompositionsalgorithmus • Nichttriviale FD, die gerade Gegenstand der Betrachtung ist:  →   sollte maximal sein. • Ergebnis nach aktuellem Schritt:

Ri1



Ri2



Ri - (  )

Ri1:    Ri2: Ri  

• Offensichtlich verlustfrei: • Schnittmenge  ist Schlüssel für Ri1. Kommunikation und Datenhaltung, Sommersemester 2009

70

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

BCNF – Zerlegung II • Anwendung auf Beispiel ‚Städte‘ von eben? Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

  • Ri: Städte(Ort, BLand, MinPräs, EW) Schlüssel: fd3 fd2

{Ort, BLand} und {Ort, MinPräs}

fd1

• Ri1: Regierungen(BLand, MinPräs) Ri2: Städte(Ort, BLand, EW) • Ri1, Ri2 in BCNF – MinPräs ist Schlüssel von Ri1 (fd3). Kommunikation und Datenhaltung, Sommersemester 2009

71

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

BCNF – Verlust der Abhängigkeitstreue • Illustration: Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

fd1

PLZVerzeichnis(Straße, Ort, BLand, PLZ) fd2 • fd2 verletzt BCNF. • Zerlegung entlang fd2 hat Ergebnis • Orte(PLZ, Ort, BLand) • Straßen(Straße, PLZ),

• fd1 geht durch Zerlegung verloren (in der Praxis selten) • Dies ist auf jeden Fall zu vermeiden • Wir lassen es bei 3NF Kommunikation und Datenhaltung, Sommersemester 2009

72

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Minimalität • Global Redundanzen (innerhalb DB) vermeiden Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Normalformen vermeiden Redundanzen innerhalb einer Relation

• Andere Kriterien (wie Normalformen) mit möglichst wenig Relationenschemata erreichen. • Beispiel: Attributmenge ABC, FD-Menge {A  B, B  C} PANr

PLZ

Ort

• Datenbankschemata in dritter Normalform: • S = {(AB, {A}), (BC, {B})} • S‘ = {AB, {A}), (BC, {B}), (AC, {A})} redundant! Kommunikation und Datenhaltung, Sommersemester 2009

73

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Schema-Eigenschaften Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Schemaeigenschaft 1NF 2NF

3NF

BCNF Minimalität

Kurzcharakteristik nur atomare Attribute keine partielle Abhängigkeit eines Nicht-Primattributes von einem Schlüssel keine transitive Abhängigkeit eines Nicht-Primattributes von einem Schlüssel alle FDs sind trivial oder deren linke Seite ist Superschlüssel minimale Anzahl von Relationenschemata, die die anderen Eigenschaften erfüllt

Kommunikation und Datenhaltung, Sommersemester 2009

74

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

75

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Transformationseigenschaften Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Erreichen von Normalformen durch Zerlegung von Relationenschemata. • Dabei beachten: • Nur semantisch sinnvolle und konsistente Anwendungsdaten darstellen (Abhängigkeitstreue), • alle Anwendungsdaten sollen aus Basisrelationen hergeleitet werden können (Verbundtreue).

Kommunikation und Datenhaltung, Sommersemester 2009

76

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Entwurfsverfahren I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Universum U, d. h. Menge von Attributen, und FD-Menge F gegeben, • Ziel: Lokal erweitertes Datenbankschema S={(R1, K1), ..., (Rp, Kp)} berechnen mit 1. S charakterisiert vollständig F („Abhängigkeitstreue“), 2. S ist in 3NF bezüglich F, 3. Dekomposition von U in R1, ..., Rp ist verbundtreu bezüglich F, 4. Minimalität, d. h. S‘: S‘ erfüllt 1, 2, 3, und |S‘| < |S|.

Kommunikation und Datenhaltung, Sommersemester 2009

77

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Entwurfsverfahren II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Datenbankschemata schlecht entworfen, wenn nur eins dieser vier Kriterien nicht erfüllt.

Kommunikation und Datenhaltung, Sommersemester 2009

78

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition: Start • Zunächst: Klassisches Dekompositionsverfahren Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Erreicht nur 3NF und Verbundtreue. • Im Anschluss: Syntheseverfahren, welches alle 4 Eigenschaften erfüllt

• Prinzip: Immer wenn transitive Abhängigkeit X  Y  Z, wird Relation ‚an dieser Stelle‘ zerlegt. • Start: Initiales Relationenschema R mit allen Attributen und einer von erfassten Abhängigkeiten implizierten Schlüsselmenge • Attributmenge U und eine FD-Menge F, • suche alle K  U mit K minimal, für die K  UF+ gilt (K(F)) („von Abhängigkeiten implizierte Schlüsselmenge“), • (U, K(F)) initiales Relationenschema.

Kommunikation und Datenhaltung, Sommersemester 2009

79

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition: Start – Beispiel I • U={PANr, PLZ, Ort, Land, Staat}, Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• F = {PANr  PLZ, PLZ  Ort, Ort  Land, Land  Staat} • Es gilt also z. B. auch: PANr  Land • Offensichtlich: K={{PANr}} • (U, K(F)) = ({PANr, PLZ, Ort, Land, Staat}, {{PANr}})

Kommunikation und Datenhaltung, Sommersemester 2009

80

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition: Normalisierung Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Normalisierungsschritt: Falls K  X  Y mit Y Nicht-Primattribut, eliminiere Y aus R und erzeuge neues Relationenschema XY. • R = (R, K) und F über R gegeben, • Falls R in 3NF ist: fertig. • Sonst: Schritt wiederholen. •

Wie müsste man obigen Ablauf ändern, um BCNF zu erzeugen?

Kommunikation und Datenhaltung, Sommersemester 2009

81

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition: Normalisierung Beispiel II • U={PANr, PLZ, Ort, Land, Staat}, Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• F = {PANr  PLZ, PLZ  Ort, Ort  Land, Land  Staat}, also z.B. auch: PANr  Land • (U, K(F)) = ({PANr, PLZ, Ort, Land, Staat}, {{PANr}}) • ‚K  X  Y‘ sei jetzt ‚PANr  Land  Staat‘ • Neue Relationen: • R1={Land, Staat}, • R2={PANr, PLZ, Ort, Land}, • Vorgehen wiederholt sich (mit R2), nächste transitive FD z. B. PANr  Ort  Land.

Kommunikation und Datenhaltung, Sommersemester 2009

82

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition: Diskussion • Vorteile: Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• 3NF, • Verbundtreue.

• Nachteile: • Abhängigkeitstreue und Minimalität nicht, • Reihenfolgeabhängig (s. folgende Folie), • NP-vollständig (Schlüsselsuche).

Kommunikation und Datenhaltung, Sommersemester 2009

83

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition – Beispiel Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Illustriert Reihenfolgeabhängigkeit und Verlust der Abhängigkeitstreue • {A  B, B  C, C  D} • A  B  D zuerst betrachten. • R1=(BD, {B}), R2=(BC, {B}), R3=(AB, {A}), • Abhängigkeit C  D ist verloren. •

Wie sieht das Ergebnis aus, wenn wir A  C  D zuerst betrachten?

Kommunikation und Datenhaltung, Sommersemester 2009

84

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Dekomposition – weiteres Beispiel Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Dieses Beispiel zeigt, dass mit Dekomposition nicht-minimale Zerlegung möglich. • ABCD; F={A  B, B  C, B  D}. • Wie sieht minimale Zerlegung aus? • Wie sieht nicht-minimale Zerlegung aus? • Wie muss Dekomposition ablaufen, damit nicht-minimale Zerlegung?

Kommunikation und Datenhaltung, Sommersemester 2009

85

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Jetzt: Leistungsfähigerer Ansatz • Prinzip: Synthese formt Original-FD-Menge F in resultierende Menge von Schlüsselabhängigkeiten G so um, dass F  G gilt. • Dazu wird F in mehreren Schritten manipuliert (Illustration auf nächster Folie)

• Abhängigkeitstreue ‚im Verfahren verankert‘. • 3NF, Minimalität und verbundtreue wird auch erreicht, reihenfolgeunabhängig.

Kommunikation und Datenhaltung, Sommersemester 2009

86

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Vergleich Dekomposition – Synthese Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

U, FDs F

FDs F

R, K

FDs F‘

…

…

R‘1, K‘1 ... R‘m, K‘m

FDs F“

…

R1, K1 ... Rn, Kn

R1, K1 ... Rn, Kn

Dekomposition

Synthese

Kommunikation und Datenhaltung, Sommersemester 2009

87

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Übersicht Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Überführung der FD-Menge F in eine äquivalente Menge von Schlüsselabhängigkeiten in den folgenden Schritten 1. Eliminiere Redundanzen durch Entfernen überflüssiger FDs 2. Eliminiere Redundanzen durch Entfernen überflüssiger Attribute 3. Fasse FDs zu „Äquivalenzklassen“ zusammen 4. Bilde zu jeder Äquivalenzklasse ein Schema 5. If (keines der Schemata aus Schritt 4 enthält einen Schlüssel von R) then Erzeuge weiteres Relationenschema mit Attributen aus R, die Schlüssel bilden

Kommunikation und Datenhaltung, Sommersemester 2009

88

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Schritt 1 & 2 Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Eliminiere Redundanzen durch Entfernen überflüssiger FDs. • FDs aus F überflüssig, wenn redundant, d. h. F – {f}  F

• Eliminiere Redundanzen durch Entfernen überflüssiger Attribute • Attribut A aus X  Y є F überflüssig, wenn A entfernt werden kann (aus X oder aus Y), ohne dass sich die Hülle von F ändert, d.h. F – {X  Y} + {X – {A}  Y}  F oder F – {X  Y} + {X  Y – {A}}  F

Kommunikation und Datenhaltung, Sommersemester 2009

89

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Schritt 3 & 4 • Fasse FDs zu „Äquivalenzklassen“ zusammen Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• FDs in eine Klasse, die gleiche linke Seiten haben, d.h., Ci = {Xi  Ai1, Xi  Ai2, …} • FDs in eine Klasse, die äquivalente linke Seiten haben, d.h., wenn sich ihre linken Seiten gegenseitig funktional bestimmen

• Bilde zu jeder Äquivalenzklasse ein Schema • RCi = {Xi + {Ai1} + {Ai2} + …}

Kommunikation und Datenhaltung, Sommersemester 2009

90

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Beispiele I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• • • •

F={A  B, AB  C, A  C, B  A, C  E}. Redundante FD: A  C, Überflüssiges Attribut: B in AB  C. Resultierende FDs: A  B, A  C, B  A, C  E. Zu Äquivalenzklasse zusammenfassen.

• Syntheseergebnis: (ABC, {{A}, {B}}), (CE, {C})

Kommunikation und Datenhaltung, Sommersemester 2009

91

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Beispiele II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Beispiel von vorhin: ABCD; {A  B, B  C, B  D} • Ungeschicktes Vorgehen bei Dekomposition: • A  B  D zuerst betrachten. • R1=(BD, {B}), R2=(BC, {B}), R3=(AB, {A}), • Abhängigkeit C  D ist verloren.

• ‚Linke Seiten‘ von Klassen von FDs beim Synthese-Algorithmus: A, B, C. • Ergebnis des Synthesealgorithmus?

Kommunikation und Datenhaltung, Sommersemester 2009

92

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Beispiele III Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• F={Name  Alter, Abteilung  Leiter}, Universalrelation: (Name Alter Abteilung Leiter {Name Abteilung})

Name NH NH GS

Alter 29 29 29

Abteilung Marketing DBS DBS

Leiter RR KB KB

• Syntheseergebnis ohne Schritt 5: • (Name Alter, {Name}) • (Abteilung Leiter, {Abteilung}) Kommunikation und Datenhaltung, Sommersemester 2009

93

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Syntheseverfahren Beispiele III (cont‘d) Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

Name NH NH GS •

Alter 29 29 29

Leiter RR KB KB

Syntheseergebnis ohne Schritt 5: (Name Alter, {Name})

Name Alter NH 29 GS 29 •

Abteilung Marketing DBS DBS

(Abteilung Leiter, {Abteilung})

Abteilung Leiter Marketing RR DBS KB

Nicht verbundtreu! •

Problem (informell): Schlüssel der Universalrelation „zerrissen“

 Schritt 5 (Universalschlüssel als eigene Relation, um Zusammenhang Name – Abteilung festzuhalten) Kommunikation und Datenhaltung, Sommersemester 2009

94

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

95

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten – Motivation Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Relation, die Weine mit jeweiligen Jahrgängen und passenden Gerichten speichert WName Chardonnay Chardonnay Chardonnay Chardonnay Shiraz Shiraz Shiraz Shiraz

Jahrgang 2002 2002 2003 2003 2003 2003 2004 2004

Gericht Geflügel Fisch Fisch Geflügel Wild Lamm Wild Lamm

• Ist die Relation in 2NF? • Ist die Relation in 3NF? Kommunikation und Datenhaltung, Sommersemester 2009

96

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Intuition: Eine Belegung von X bestimmt eine Menge von Belegungen von Y, unabhängig von anderen Attributen • Multi-Value Dependency (MVD) X →→Y: Innerhalb einer Relation r wird einer Belegung von X immer eine Menge von Belegungen in Y zugeordnet, unabhängig von den Werten der restlichen Attribute von r • Folge: Immer wenn die Belegung von X vorkommt, kommt diese in Kombination mit allen davon abhängenden Belegungen von Y vor  Redundanz

Kommunikation und Datenhaltung, Sommersemester 2009

97

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten I Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluß

• Formal – r genügt der MVD X  Y genau dann, wenn t1, t2r: [(t1t2  t1(X)=t2(X))  t3r: t3(X)=t1(X)  t3(Y)=t1(Y) t3(Z)=t2(Z)] • Illustration: • Es gelte MVD WName  Gericht. • Wenn (Chardonnay, 2002, Geflügel) und (Chardonnay, 2003, Fisch) in Relation, dann auch (Chardonnay, 2002, Fisch) und (Chardonnay, 2003, Geflügel).

• Vierte Normalform: Keine nicht-trivialen MVDs •

Nicht mehr mit funktionalen Abhängigkeiten beschreibbar

Kommunikation und Datenhaltung, Sommersemester 2009

98

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten II Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• MVD X  Y ist trivial, wenn keine weiteren Attribute im zugehörigen Relationenschema.

Kommunikation und Datenhaltung, Sommersemester 2009

99

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten + Normalisierung Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

WName Chardonnay Chardonnay Chardonnay Chardonnay Shiraz Shiraz Shiraz Shiraz

•

Gericht Geflügel Fisch Fisch Geflügel Wild Lamm Wild Lamm

Natürliche Darstellung des Sachverhalts: WName Chardonnay Chardonnay Shiraz Shiraz

•

Jahrgang 2002 2002 2003 2003 2003 2003 2004 2004

Jahrgang 2002 2003 2003 2004

WName Chardonnay Chardonnay Shiraz Shiraz

Gericht Geflügel Fisch Wild Lamm

Vierte Normalform

Kommunikation und Datenhaltung, Sommersemester 2009

100

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Mehrwertige Abhängigkeiten Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

WName Chardonnay Chardonnay Chardonnay Chardonnay Shiraz Shiraz Shiraz Shiraz





Jahrgang 2002 2002 2003 2003 2003 2003 2004 2004

Gericht Geflügel Fisch Fisch Geflügel Wild Lamm WName Wild Lamm Chardonnay Chardonnay Chardonnay Shiraz Shiraz Shiraz

Existenz einer MVD – Schema-Designer muß es sich überlegen.

Jahrgang 2002 2002 2003 2003 2003 2004

Gericht Geflügel Fisch Fisch Wild Lamm Lamm

Schema-Designer muß sich überlegen, wie Weltausschnitt beschaffen ist.

Kommunikation und Datenhaltung, Sommersemester 2009

101

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Agenda • • • • • • • • •

Einleitung Funktionale Abhängigkeiten Funktionale Abhängigkeiten - Ableitung Anomalien Transformationseigenschaften Schemaeigenschaften Entwurfsverfahren Weitere Abhängigkeiten Schluss

Kommunikation und Datenhaltung, Sommersemester 2009

102

Institut für Programmstrukturen und Datenorganisation (IPD) Lehrstuhl für Systeme der Informationsverwaltung, Prof. Böhm

Zusammenfassung Einleitung FDs FD - Ableitung Anomalien Transformation Schema Entwurf Weiteres Schluss

• Thema dieses Kapitels: Guter Entwurf. • Redundanzen sind stoßend. • Ziel: Transformation eines Schemas, das Redundanzen zulässt, in eins, das diese Eigenschaft nicht hat. Transformation selbst muss diversen Anforderungen genügen (Äquivalenz). • Unterschiedliche Arten der Redundanz und entsprechende Normalformen. • Normalisierungsalgorithmen wurden besprochen.

Kommunikation und Datenhaltung, Sommersemester 2009

103