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

Kommunikation und Datenhaltung

3. Übung zur Datenhaltung Relationaler Entwurf & DDL

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

A Agenda d

• E Ergänzung ä zur V Vorlesung: l Normalformen • Ergänzungen zum zweiten Übungsblatt Ü p g des dritten Übungsblatts g • Besprechung

Kommunikation und Datenhaltung, Sommersemester 2009

2

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

N Normalformen: lf 1NF

• Relation in erster Normalform, wenn alle Attribute der Relation atomar. • Als Attributwerte sind nur Standarddatentypen yp wie integer oder string erlaubt. • Nicht erlaubt sind mengenwertige Ausprägungen von Konstruktoren wie array oder set.

Kommunikation und Datenhaltung, Sommersemester 2009

3

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

N Normalformen lf 1NF • Wir legen fest: • Ist ein Relationenschema in 2NF, 3NF, BCNF oder 4NF, so ist es auch in 1NF! • Die folgenden Definitionen bezüglich 2NF, gehen immer davon 3NF, BCNF und 4NF g aus, dass das zugehörige Relationenschema in 1NF ist!

• Um eine Entscheidung treffen zu können, ob ein Relationenschema in 1NF ist, muss bekannt sein, ob alle Attribute des Schemas atomar sind! Kommunikation und Datenhaltung, Sommersemester 2009

4

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

N Normalformen: lf 2NF • Relation in 2NF 2NF, wenn keine partiellen Abhängigkeiten zwischen Schlüsseln des Relationenschemas und NichtNicht Primattributen bestehen. • Beispiel • R = ABCD • Schlüssel: Schlüssel A, A B • Funktionale Abhängigkeiten: • AB → C •B → D

Nicht-Primattribut Nicht Primattribut D abhängig von B B. B allerdings nur Teil des Schlüssels! Daher nicht in 2NF.

Kommunikation und Datenhaltung, Sommersemester 2009

5

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

N Normalformen: lf 3NF • Eine e Relation e at o ist st in 3 3NF,, wenn e es keine e e transitiven Abhängigkeiten K → X → Y (K Schlüssel, Y Nicht-Primattribut) gibt. • Verbundtreue und abhängigkeitsbewahrende Zerlegung in 3NF immer möglich. • Beispiel für transitive Abhängigkeit: • R = ABC • Schlüssel: S hlü l A • Funktionale Abhängigkeiten: • A→B • B→C

Nicht Primattribut C transitiv Nicht-Primattribut abhängig von A (Schlüssel). Daher nicht in 3NF.

Kommunikation und Datenhaltung, Sommersemester 2009

6

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

N Normalformen: lf 3NF

• Eine Relation ist in 3NF, wenn es keine transitiven Abhängigkeiten K → X → Y (K Schlüssel, Y Nicht-Primattribut) gibt. • Wählt X als Teil eines Schlüssels, so sieht man, dass partielle Abhängikeiten ein Spezialfall der t transitiven iti sind. i d • 2NF wird somit von 3NF impliziert.

Kommunikation und Datenhaltung, Sommersemester 2009

7

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

N Normalformen: lf BCNF • • • • •

Eine Relation ist in BCNF, wenn kein Attribut transitiv von einem Schlüssel abhängt. 3NF betrachtet nur transitive Abhängigkeiten von NichtPrimattributen. Primattributen BCNF widmet sich Redundanzen innerhalb der Schlüsselattribute. Verbundtreue Zerlegung in BCNF immer möglich. Nicht notwendigerweise aber eine abhängigkeitsbewahrende! Beispiel für transitive Abhängikeit von einem Schlüssel: • R = ABX, Schlüssel: A, B • Funktionale Abhängigkeiten: • AB → X Primattribut B transitiv • X → B abhängig vom Schlüssel AB. Daher nicht in BCNF.

Kommunikation und Datenhaltung, Sommersemester 2009

8

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

N Normalformen: lf BCNF • Definition BCNF • ∄ A ∈ R: A transitiv abhängig von einem Schlüssel

• Alternative Def. der 3NF (äquivalent zu der in Vorlesung vorgestellten Def Def.): ): • ∄ A ∈ R: A transitiv abhängig von einem Schlüssel ∧ A ist Nicht-Primattribut Nicht Primattribut in R

• Klar ersichtlich: • Relation in BCNF ⇒ Relation in 3NF

Kommunikation und Datenhaltung, Sommersemester 2009

9

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

N Normalformen: lf 4NF • Eine e Relation e at o ist st in 4NF,, wenn e es keine e e mehrwertigen, nicht-trivialen Abhängigkeiten zwischen Attributen einer Relation gibt. • Abhängigkeit X →→ Y nicht-trivial, wenn außer X und Y noch weitere Attribute im zugehörigen R l ti Relationenschema h enthalten th lt sind. i d • Man kann zeigen: • Ist I t eine i Relation R l ti iin 4NF 4NF, so iistt sie i auch h iin BCNF. • Verbundtreue Zerlegung einer beliebigen Relation in 4NF möglich. Kommunikation und Datenhaltung, Sommersemester 2009

10

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

N Normalformen lf iin d der Üb Übersicht i h • Mathematisch beweisbar (gegeben unsere Definition bezüglich 1NF): • • • •

Relation in 2NF Relation in 3NF Relation in BCNF Relation in 4NF

⇒ ⇒ ⇒ ⇒

Relation in 1NF Relation in 2NF Relation in 3NF Relation in BCNF

1NF 2NF 3NF BCNF

4NF

Kommunikation und Datenhaltung, Sommersemester 2009

11

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

A Agenda d

• E Ergänzung ä zur V Vorlesung: l Normalformen • Ergänzungen zum zweiten Übungsblatt Ü p g des dritten Übungsblatts g • Besprechung

Kommunikation und Datenhaltung, Sommersemester 2009

12

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

K di li ä Kardinalitäten b beii d dreistelliger i lli B Beziehung i h Ein Produktionsplan p sieht vor,, von wann bis wann welche Episode p p produziert wird,, welche Personen beteiligt sind und in welchem Studio gedreht wird. An einer Produktion ist mindestens eine Person beteiligt. Zu einem Zeitpunkt findet eine Produktion immer nur in einem Studio statt. Während einer Produktion können (Teile für) verschiedene Episoden produziert werden werden, mindestens wird aber Material für eine Episode gedreht gedreht. Name

Nummer

Name

Geburtstag

ID Episode

PP

1 .. N Startzeit

Alt Alter 1 .. N

Endzeit 1 Studio

Person

K Korrektur: kt Zu einem Zeitpunkt ist eine Person an der Produktion einer Episode in genau einem Studio beteiligt. Wir halten also Person und Episode UND die Attribute der Beziehung fest, um die Kardinalität von Studio in der Beziehung g PP zu bestimmen.

Anmerkung:

Größe

Längengrad

Breitengrad

Kommunikation und Datenhaltung, Sommersemester 2009

PP = Produktionsplan

13

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

S hlü Schlüsselwahl l hl Setzen Sie alle Entityy und Beziehungstypen g yp kapazitätserhaltend p in das Relationenmodell um und geben Sie die notwendigen Schlüssel an. Verwenden Sie dazu die Notation aus der Vorlesung. Hier Angabe von K wichtig, da es die Information enthält, dass beide Attribute Kandidaten für Schlüssel sind und daher eindeutig sein müssen. Eines der Attribute wird zum Primärschlüssel gewählt (in diesem Fall PersonA_ID), das andere Attribut ist aber immer noch Schlüssel(kandidat) und muss eindeutig sein.

verheiratet (PersonA_ID, PersonB_ID) K={{PersonA_ID}, {PersonB_ID}}

Kommunikation und Datenhaltung, Sommersemester 2009

P PersonID ID Name

Schauspieler 0..1

0..1

verheiratet

14

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

V Verschmelzung h l Name

AgenturID

Warum das möglicherweise keine gute Idee ist, wird in Aufgabe 2d) behandelt. Sollte in der Klausur eine Frage kommen, kommen welche Entity-Typen und Beziehungstypen (sinnvollerweise) miteinander verschmolzen werden können, ziehen Sie bitte nur 1:1- Beziehungen und 1:NBeziehungen in Betracht. Welche Nachteile aber eine Verschmelzung mit einer Entität mit sich bringt, die nicht zwingend in die Relation eingeht, sollte Ihnen klar sein.

PersonID

Agentur 0..1

Vertrag N Schauspieler

Name

Schauspieler (PersonID, Schauspielername, AgenturID, Agenturname) Kommunikation und Datenhaltung, Sommersemester 2009

15

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

N h il V Nachteile Verschmelzung h l PersonID

Schauspielername p

AgenturID g

Agenturname g

1

Al Pacino

1

Some Agency

2

John Travolta

NULL

NULL

3

Joaquin Phoenix

NULL

NULL

4

J Jessica i Alb Alba

1

S Some A Agency

5

Julia Roberts

NULL

NULL

• • • •

Jeder NULL-Wert kostet Speicherplatz Kenntnis über Datenbestand nötig, für sinnvolle Entscheidung, ob Verschmelzung oder nicht Redundanzen im Datenbestand (Agenturname) Weniger schöne Modellierung: Person und Agentur verschiedene Dinge, nun aber eine Entität Kommunikation und Datenhaltung, Sommersemester 2009

16

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

A Agenda d

• E Ergänzung ä zur V Vorlesung: l Normalformen • Ergänzungen zum zweiten Übungsblatt Ü p g des dritten Übungsblatts g • Besprechung

Kommunikation und Datenhaltung, Sommersemester 2009

17

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

A f b 1 Aufgabe Gegeben g sei eine Datenbasis zur Mensa, die eine einzige g Relation R mit folgenden Attributen enthält: Linie (A), Datum (B), Hauptgericht (C), Anzahl Essensausgabestellen (D), Salat (E) und Beilage (F). Alle Attribute sind atomar, also nicht mengenwertig. Es existieren folgende funktionalen Abhängigkeiten: Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

Kommunikation und Datenhaltung, Sommersemester 2009

18

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

• Welches Attribut hängt funktional von keinem anderen ab? • A steht nur auf der linken Seite!

Kommunikation und Datenhaltung, Sommersemester 2009

19

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

• Hülle A+ =

Kommunikation und Datenhaltung, Sommersemester 2009

20

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

• A+ = { A, D } • Funktional hängen von A also nur A selbst und D ab. A alleine bestimmt nicht alle Attribute (A, B, C, D, E, F). Kommunikation und Datenhaltung, Sommersemester 2009

21

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

• Auch B hängt funktional von keinem anderen Attribut ab. • Hülle B+ = Kommunikation und Datenhaltung, Sommersemester 2009

22

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

S hlü Schlüsselfindung lfi d Funktionale Abhängigkeiten gg AB A B F

→ → → →

C D EF E

• B+ = { B, E, F } • Auch B bestimmt nicht alle Attribute A A, B B, C, D, E und F. Kommunikation und Datenhaltung, Sommersemester 2009

23

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d Funktionale Abhängigkeiten gg AB A B F

→ → → →

C D EF E

• Außer A oder B weiteres Attribut nur auf linker Seite? • Nein! • Bestimmen A und B alle Attribute? • AB+ = Kommunikation und Datenhaltung, Sommersemester 2009

24

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

A f b 1a: Aufgabe 1 S Schlüsselfindung hlü lfi d

• A oder B alleine bestimmen nicht alle Attribute. • A und B bestimmen alle Attribute der Relation: AB+ = { A, B, C, D, E, F } • A und B lassen sich nicht aus anderen Attributen ableiten. • Daher AB Schlüssel.

Kommunikation und Datenhaltung, Sommersemester 2009

25

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

A f b 1b Aufgabe 1b: Hö Höchste h N Normalform lf Funktionale Abhängigkeiten gg AB A B F

→ → → →

C D EF E

• 1NF? • Ja, da nach Aufgabenstellung alle Attribute atomar

• 2NF? • Gib Gibt es partielle i ll Abhä Abhängigkeiten i k i zwischen i h Schlüsseln des Relationenschemas und weiteren Attributen? Kommunikation und Datenhaltung, Sommersemester 2009

26

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

A f b 1b Aufgabe 1b: Hö Höchste h N Normalform lf • Relation in 2NF 2NF, wenn keine partiellen Abhängigkeiten zwischen Schlüsseln des Relationenschemas und Nicht NichtPrimattributen bestehen.

Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

D hängt funktional vom Primattribut A ab ab. Damit hängt D partiell von einem Schlüssel ab, nämlich dem Schlüssel AB! D selbst ist kein Primattribut Primattribut. Daher nicht in 2NF! Auch B → EF verstößt gegen diese B di Bedingung.

Kommunikation und Datenhaltung, Sommersemester 2009

27

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

A f b 1b Aufgabe 1b: Hö Höchste h N Normalform lf

• Da Relation nicht in 2NF, aber in 1NF, ist 1NF die höchste Normalform Normalform, in der die Relation ist.

Kommunikation und Datenhaltung, Sommersemester 2009

28

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

A f b 1c: Aufgabe 1 3NF Geben Sie eine verbundtreue und abhängigkeitstreue gg Zerlegung g g von R an, deren Relationen in dritter Normalform sind.

• Ausgangssituation g g • • •

R = ABCDEF 1 NF, da alle Attribute nicht mengenwertig Menge funktionaler Abhängigkeiten: Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

Kommunikation und Datenhaltung, Sommersemester 2009

29

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

A f b 1c: Aufgabe 1 3NF Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

D hängt funktional vom Primattribut A ab ab. Damit hängt B partiell von einem Schlüssel ab, nämlich dem Schlüssel AB! D selbst ist kein Primattribut Primattribut. Daher nicht in 2NF! Auch B → EF verstößt gegen diese B di Bedingung.

• Elimination der rechten Seite der partiellen Abhängigkeit und Kopie der linken Seite: • R1 = ABC • R2 = AD • R3 = BEF

• R1,, R2,, R3 nun in 2NF. Kommunikation und Datenhaltung, Sommersemester 2009

30

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

A f b 1c: Aufgabe 1 3NF Funktionale Abhängigkeiten AB A B F

→ → → →

C D EF E

Transitive Abhängigkeit des NichtNicht Primattributs E vom Primattribut B: B→F→E ֜ Nicht in 3NF

• Elimination von E und Kopie von F: • R3 R3_1 1 = BF • R3_2 = FE

• R1, R1 R2, R2 R3_1, R3 1 R3_2 R3 2 nun in 3NF 3NF.

Kommunikation und Datenhaltung, Sommersemester 2009

31

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

A f b 1d: Aufgabe 1d BCNF • Zerlegte Relationen in BCNF? • • • •

R1 = ABC R2 = AD R3_1 = BF R3_2 = FE

• Ja, da in allen Relationen kein Attribut transitiv abhängig bhä i von einem i anderen d Att Attribut ib t iist! t! • R1, R2, R3 ist nicht in BCNF!

Kommunikation und Datenhaltung, Sommersemester 2009

32

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

A f b 2 Aufgabe 2a: U Updateanomalie d li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

Elvis Costello

25.08.1954

57834

My Aim Is True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

Geben Sie ein Beispiel, wie es bei dieser Relation zu einer U d Updateanomalie li kkommen kkann.

Kommunikation und Datenhaltung, Sommersemester 2009

33

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

A f b 2 Aufgabe 2a: U Updateanomalie d li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

12.12.2000

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

Elvis Costello

25.08.1954

57834

My Aim Is True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

UPDATE Musik SET Geburtstag = ‘12.12.2000’ ‘ ’ WHERE Album = ‘At San Quentin’ Kommunikation und Datenhaltung, Sommersemester 2009

34

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

A f b 2 Aufgabe 2a: U Updateanomalie d li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

12.12.2000

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

Elvis Costello

25.08.1954

57834

My Aim Is True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

• Zu einer Person liegen nun mehrere Angaben über d G den Geburtstag b t t vor!! • Inkonsistenzen im Datenbestand die Folge. Kommunikation und Datenhaltung, Sommersemester 2009

35

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

A f b 2b Aufgabe 2b: Updateanomalien U d li Neben potentiellen Updateanomalien bringen die redundanten Einträge weitere Nachteile mit sich: • Erhöhter Speicherbedarf wegen der redundant zu speichernden Informationen. • Leistungseinbußen L i t i b ß b beii Ä Änderungen, d d da mehrere Einträge abgeändert werden müssen. ü Kommunikation und Datenhaltung, Sommersemester 2009

36

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

A f b 2c: Aufgabe 2 Ei Einfügeanomalien fü li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

Elvis Costello

25.08.1954

57834

My Aim Is True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

Kann es zu Einfügeanomalien kommen?

Kommunikation und Datenhaltung, Sommersemester 2009

37

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

A f b 2c: Aufgabe 2 Ei Einfügeanomalie fü li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

El i C Elvis Costello t ll

25 08 1954 25.08.1954

57834

M Ai My Aim IIs T True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

2222

Mi h l JJackson Michael k

29 08 1958 29.08.1958

NULL

NULL

NULL

INSERT INTO Musik (‘ PersonID ’, ‘ Person ’, ‘ Geburtstag ’) VALUES (‘ 2222 ’, ‘ Michael Jackson ’, ‘ 29.08.1958 ’) Kommunikation und Datenhaltung, Sommersemester 2009

38

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

A f b 2c: Aufgabe 2 Ei Einfügeanomalien fü li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

El i C Elvis Costello t ll

25 08 1954 25.08.1954

57834

M Ai My Aim IIs T True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

2222

Mi h l JJackson Michael k

29 08 1958 29.08.1958

NULL

NULL

NULL

• Um die Person Michael Jackson in die Datenbank einzutragen müssen Angaben über Alben gemacht einzutragen, werden (wieder viele NULL-Werte die Folge). • Ja, Ja es kann zu Einfügenomalien kommen kommen. Kommunikation und Datenhaltung, Sommersemester 2009

39

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

A f b 2d Aufgabe 2d: Lö Löschanomalien h li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

Elvis Costello

25.08.1954

57834

My Aim Is True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

Ist es möglich, Informationen über das Album Stadtaffe zu löschen, ohne d IInterpreten Peter den P t Fox F aus der d R Relation l i zu entfernen? f ?

Kommunikation und Datenhaltung, Sommersemester 2009

40

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

A f b 2d Aufgabe 2d: Lö Löschanomalien h li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

El i C Elvis Costello t ll

25 08 1954 25.08.1954

57834

M Ai My Aim IIs T True

1977

7342

Peter Fox

03.09.1971

85989

Stadtaffe

2008

DELETE FROM Musik WHERE Person = ‘ Peter Fox’ Würde auch alle Informationen über dessen Alben löschen!

Kommunikation und Datenhaltung, Sommersemester 2009

41

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

A f b 2d Aufgabe 2d: Lö Löschanomalien h li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

El i C Elvis Costello t ll

25 08 1954 25.08.1954

57834

M Ai My Aim IIs T True

1977

7342

Peter Fox

03.09.1971

NULL

NULL

NULL

UPDATE Musik SET AlbumID = NULL, Album = NULL, Jahr = NULL WHERE Person = ‘ Peter Fox’

Kommunikation und Datenhaltung, Sommersemester 2009

42

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

A f b 2d Aufgabe 2d: Lö Löschanomalien h li PersonID

Person

Geburtstag

AlbumID

Album

Jahr

1234

Johnny Cash

26.02.1932

42382

At Folsom Prison

1968

1234

Johnny Cash

26.02.1932

45556

At San Quentin

1969

1234

Johnny Cash

26.02.1932

42536

American Recordings

1994

3456

El i C Elvis Costello t ll

25 08 1954 25.08.1954

57834

M Ai My Aim IIs T True

1977

7342

Peter Fox

03.09.1971

NULL

NULL

NULL

• Unintuitiv • Fehleranfällig • Prinzipiell aber möglich, Informationen über Personen zu löschen, ohne deren Alben mitzulöschen Kommunikation und Datenhaltung, Sommersemester 2009

43

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

A f b 3a: Aufgabe 3 S Schemaerweiterung h i Name

AgenturID

Spruch

Agentur

SpruchID

Catchphrase

0..1

N Beginn

Vertrag

Ende

benutzt

Provision PersonID Name

N M Schauspieler 0 1 0..1

spielt

N

1 Rolle

0 1 0..1

verheiratet

RollenID

Name

Ort Kommunikation und Datenhaltung, Sommersemester 2009

44

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

A f b 3b: Aufgabe 3b DDL Name

AgenturID g

A Agentur t

Kommunikation und Datenhaltung, Sommersemester 2009

45

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

A f b 3b: Aufgabe 3b DDL Name

AgenturID g

A Agentur t

CREATE TABLE Agentur ( Name VARCHAR(100), AgenturID INTEGER, PRIMARY KEY (Agent (AgenturID) rID) ); Kommunikation und Datenhaltung, Sommersemester 2009

46

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

A f b 3b: Aufgabe 3b DDL PersonID

Schauspieler

Name

CREATE TABLE Schauspieler ( Name VARCHAR(100), PersonID INTEGER, PRIMARY KEY (PersonID) ); Kommunikation und Datenhaltung, Sommersemester 2009

47

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

A f b 3b: Aufgabe 3b DDL CREATE TABLE Vertrag ( Beginn DATE, CHECK (Beginn NOT BETWEEN (SELECT Beginn FROM Vertrag V WHERE PersonID = V.PersonID) AND (SELECT Ende […])), Ende DATE, CHECK (Ende NOT BETWEEN […]), Provision DECIMAL(5, 2), CHECK (Provision>0 AND Provision 0), FOREIGN KEY (PersonID) REFERENCES Schauspieler (PersonID) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (AgenturID) REFERENCES Agentur (AgenturID) ON UPDATE CASCADE ON DELETE RESTRICT ); Kommunikation und Datenhaltung, Sommersemester 2009

48

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

A f b 3b: Aufgabe 3b DDL CREATE TABLE verheiratet ( Schauspieler Ort VARCHAR(100) NOT NULL, 0..1 0..1 PersonA INTEGER, PersonB INTEGER, , verheiratet PRIMARY KEY (PersonA), UNIQUE (PersonB), FOREIGN O G KEY ( (PersonA) e so ) REFERENCES Schauspieler (PersonID) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (PersonB) REFERENCES Schauspieler (PersonID) ON UPDATE CASCADE ON DELETE RESTRICT, CHECK(NOT EXISTS( (SELECT PersonA_ID FROM verheiratet) INTERSECT (SELECT PersonB PersonB_ID ID FROM verheiratet) )) ); Kommunikation und Datenhaltung, Sommersemester 2009

Ot Ort

49

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

Ill Illustration i IIntegritätsbedingungen i ä b di

CHECK( NOT EXISTS( (SELECT PersonA_ID FROM verheiratet) INTERSECT (SELECT PersonB_ID FROM verheiratet) ) )

verhindert

PRIMARY KEY PersonA_ID, UNIQUE P PersonB_ID B ID

verhindert

P PersonA_ID A ID

P PersonB_ID B ID

P PersonA_ID A ID

P PersonB_ID B ID

1

2

2

1

2

3

3

1

Kommunikation und Datenhaltung, Sommersemester 2009

50

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

A f b 3b: Aufgabe 3b DDL Rolle

RollenID

Name

CREATE TABLE Rolle ( Name VARCHAR(100), RollenID INTEGER, PRIMARY KEY (RollenID) ); Kommunikation und Datenhaltung, Sommersemester 2009

51

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

A f b 3b: Aufgabe 3b DDL PersonID

Schauspieler

M

spielt

N

1 Rolle

Name CREATE TABLE spielt ( PersonID INTEGER, RollenID INTEGER, INTEGER PRIMARY KEY (PersonID, RollenID), FOREIGN KEY (PersonID) REFERENCES Schauspieler (PersonID) ON UPDATE CASCADE, FOREIGN KEY (RollenID) REFERENCES R Rolle ll (RollenID) (R ll ID) ON UPDATE CASCADE ); Kommunikation und Datenhaltung, Sommersemester 2009

RollenID

Name

52

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

A f b 3b: Aufgabe 3b DDL Spruch p

SpruchID p

C t h h Catchphrase

CREATE TABLE Catchphrase ( Spruch VARCHAR(100), SpruchID INTEGER, PRIMARY KEY (Spr (SpruchID) chID) ); Kommunikation und Datenhaltung, Sommersemester 2009

53

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

A f b 3b: Aufgabe 3b DDL Spruch CREATE TABLE benutzt( SpruchID INTEGER, RollenID INTEGER, PRIMARY KEY (SpruchID), FOREIGN KEY (SpruchID) REFERENCES Catchphrase (SpruchID) ON UPDATE CASCADE, FOREIGN KEY (RollenID) REFERENCES Rolle (RollenID) ON UPDATE CASCADE );

Catchphrase N

benutzt

N

RollenID Kommunikation und Datenhaltung, Sommersemester 2009

SpruchID

1 Rolle

Name 54

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

A f b 3 Aufgabe 3c: IIndex d Erzeugen g Sie mittels eines SQL-Befehls eine Indexstruktur, die das Aufinden von Schauspielern erleichtert, denen Agenturen eine besonders hohe Provision abverlangen. CREATE INDEX ProvisionIndex ON Vertrag (Provision desc);

Kommunikation und Datenhaltung, Sommersemester 2009

55

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

I d – Illustration Index Ill t ti z

Index für (Provision): 80

((79), (1,3))

((81), (1,2))

((55), (2,2))

((85), (2,4))

((53), (2,1))

((90), (1,4))

((20), (1,1))

((90), (2,3))

Vertrag (AgenturID, SchauspielerID, Beginn, Ende, Provision) : 1, 100, 01.01.2002, 01.01.2005, 20

10, 500, 01.01.2002, 01.01.2005, 53

5, 200, 01.01.1999, 01.10.1999, 81

20, 200, 01.01.1998, 01.10.2007, 55

3, 100, 01.01.2009, 01.01.2011, 79

30, 100, 01.05.2009, 01.11.2011, 90

4, 400, 01.01.2002, 01.01.2005, 90

40, 300, 01.07.2004, 01.01.2005, 85

Kommunikation und Datenhaltung, Sommersemester 2009

56