Datenbanken I - HA Normalisierung Aufgaben und Loesungen PDF

Preview

Summary

Aufgaben mit Lösungen Normalisierung...

Description

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Eine kurze Normalisierungs-Übung zur Selbstkontrolle In den folgenden Beispielen sind jeweils Tabellen angegeben, die sich in 1NF befinden und damit generell einer Bearbeitung durch eine Datenbank zugänglich sind. Im Ausgangszustand befinden sich diese Tabellen jedoch nicht in 3NF. Durch vorhandene Redundanzen und Anomalien sind es folglich keine guten Datenmodelle und damit für die Bearbeitung durch eine Datenbank nicht geeignet. Versuchen Sie zunächst selbst, durch Normalisierung die Güte zu verbessern und kontrollieren Sie dann das Ergebnis.

Folie 1

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 1 Redundanzen durch funktionale Abhängigkeit der Bezeichnung von Art_Nr.

Art_Nr

Bezeichnung

Besteller

B_Datum

0105

Zucker

Meyer

03.10.2009

200

0105

Zucker

Schulze

03.10.2009

180

0237

Mehl

Meyer

05.10.2009

250

0105

Zucker

Müller

06.10.2009

150

2356

Salz

Moppel

06.10.2009

080

2356

Salz

Meyer

07.10.2009

060

0105

Zucker

Meyer

08.10.2009

100

Menge

Folie 2

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 3

Übung zur Normalisierung 1 Redundanzen durch funktionale Abhängigkeit der Bezeichnung von Art_Nr, also eine Verletzung der 2NF. Art_Nr

Bezeichnung

Besteller

B_Datum

0105

Zucker

Meyer

03.10.2009

200

0105

Zucker

Schulze

03.10.2009

180

0237

Mehl

Meyer

05.10.2009

250

0105

Zucker

Müller

06.10.2009

150

2356

Salz

Moppel

06.10.2009

080

2356

Salz

Meyer

07.10.2009

060

0105

Zucker

Meyer

08.10.2009

100

Art_Nr

Bezeichnung

0105

Zucker

0237

Mehl

2356

Salz

Menge

• Die Spalte Bezeichnung wird gemeinsam mit der Schlüsselspalte Art_Nr in eine neue Tabelle gespeichert. • Doppelte Zeilen (Tupel) in der neuen Tabelle werden eliminiert.

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 1 Tabellen sind auch in 3NF, da Menge voll funktional abhängig vom Primärschlüssel Art_Nr||Besteller||B_Datum und keine transitiven Abhängigkeiten.

Art_Nr

Besteller

B_Datum

0105

Meyer

03.10.2009

200

0105

Schulze

03.10.2009

180

0237

Meyer

05.10.2009

250

0105

Müller

06.10.2009

150

2356

Moppel

06.10.2009

080

2356

Meyer

07.10.2009

060

0105

Meyer

08.10.2009

100

Art_Nr

Bezeichnung

0105

Zucker

0237

Mehl

2356

Salz

Menge

Tabelle Bestellung (3NF) Art_Nr ist Fremdschlüssel mit Referenz zu Artikel

Tabelle Artikel (3NF)

Folie 4

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 2 Redundanzen durch mehrfache Wiederholung des Attributs Sprache für eine Person (volle funktionale Abhängigkeit des Grades von Pers_Nr, Sprache, aber ...).

Pers_Nr

Name

Vorname

Sprache

Grad

0105

Marder

Monika

englisch

2

0105

Marder

Monika

russisch

3

0234

Hund

Hugo

französisch

1

0325

Stier

Siegfried

spanisch

3

0325

Stier

Siegfried

französisch

3

0325

Stier

Siegfried

portugisisch

2

2345

Antilope

Anita

polnisch

3

Folie 5

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 6

Übung zur Normalisierung 2 Redundanzen durch mehrfache Wiederholung des Attributs Sprache für eine Person (keine volle funktionale Abhängigkeit des Namens und Vornamens vom Primärschlüssel), also eine Verletzung der 2NF. Pers_Nr

Name

Vorname

Sprache

Grad

0105

Marder

Monika

englisch

2

0105

Marder

Monika

russisch

3

0234

Hund

Hugo

französisch

1

0325

Stier

Siegfried

spanisch

3

0325

Stier

Siegfried

französisch

3

0325

Stier

Siegfried

portugisisch

2

2345

Antilope

Anita

polnisch

3

Name

Vorname

0105

Marder

Monika

0234

Hund

Hugo

0325

Stier

Siegfried

2345

Antilope

Anita

Pers_Nr

• Die Spalten Name, Vorname werden gemeinsam mit der Schlüsselspalte Pers_Nr in eine neue Tabelle gespeichert. • Doppelte Zeilen (Tupel) in der neuen Tabelle werden eliminiert.

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 2 Redundanzen durch mehrfache Wiederholung des Attributs Sprache für eine Person (keine volle funktionale Abhängigkeit des Namens und Vornamens vom Primärschlüssel) wurde beseitigt. Beide normalisierten Tabellen befinden sich bereits in 3NF.

Tabelle Sprachen (3NF) Pers_Nr

Sprache

0105

englisch

2

0105

russisch

3

0234

französisch

1

0325

spanisch

3

0325

französisch

3

0325

portugisisch

2

2345

polnisch

3

Grad

Tabelle Person (3NF) Pers_Nr

Name

Vorname

0105

Marder

Monika

0234

Hund

Hugo

0325

Stier

Siegfried

2345

Antilope

Anita

Pers_Nr ist Fremdschlüssel mit Referenz zu Person

Folie 7

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 3 Redundanzen bzw. Anomalien durch funktionale Abhängigkeit des Attributs Leiter von Abt und durch mehrfache Wiederholung der Attributsgruppe Sprache, Grad für jede PersNr. Pers_Nr

Name

Vorname

Geschl

Sprache

Grad

Abt

Leiter

023

Muh

Magda

W

engl

2

34

Meier

023

Muh

Magda

W

franz

1

34

Meier

145

Wau

Harald

M

34

Meier

437

Miau

Irene

W

engl

2

56

Busse

437

Miau

Irene

W

russ

3

56

Busse

759

Quak

Andre

M

engl

3

56

Busse

854

Piep

Ursel

w

franz

2

78

Works

Folie 8

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 9

Übung zur Normalisierung 3 Redundanzen durch funktionale Abhängigkeit des Attributs Leiter von Abt und durch mehrfache Wiederholung der Attributsgruppe Sprache, Grad für jede PersNr. Pers_Nr

Name

Vorname

Geschl

Sprache

Grad

Abt

Leiter

023

Muh

Magda

w

engl

2

34

Meier

023

Muh

Magda

w

franz

1

34

Meier

145

Wau

Harald

m

34

Meier

437

Miau

Irene

w

engl

2

56

Busse

437

Miau

Irene

w

russ

3

56

Busse

759

Quak

Andre

m

engl

3

56

Busse

854

Piep

Ursel

w

franz

2

78

Works

Pers_Nr

Name

Vorname

Geschl

Abt

Leiter

023

Muh

Magda

w

34

Meier

145

Wau

Harald

m

34

Meier

437

Miau

Irene

w

56

Busse

759

Quak

Andre

m

56

Busse

854

Piep

Ursel

w

78

Works

• Die Spalten Name, Vorname, Geschl, Abt, Leiter werden gemeinsam mit der Schlüsselspalte Pers_Nr in eine neue Tabelle gespeichert. • Die Zeile 145 wird in der alten Tabelle gestrichen (keine Fremdsprache). • Doppelte Zeilen (Tupel) in der neuen Tabelle werden eliminiert.

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 10

Übung zur Normalisierung 3 Redundanzen durch funktionale Abhängigkeit des Attributs Leiter von Abt und durch mehrfache Wiederholung der Attributsgruppe Sprache, Grad für jede PersNr wurde beseitigt.

Tabelle Sprachen (3NF)

Pers_Nr

Sprache

Grad

023

engl

2

023

franz

1

437

engl

2

437

russ

3

759

engl

3

854

franz

2

Pers_Nr

Name

Vorname

Geschl

Abt

Leiter

023

Muh

Magda

w

34

Meier

145

Wau

Harald

m

34

Meier

437

Miau

Irene

w

56

Busse

759

Quak

Andre

m

56

Busse

854

Piep

Ursel

w

78

Works

Pers_Nr ist Fremdschlüssel mit Referenz zu Person

Tabelle Person (2NF) Aber es existiert noch eine transitive Abhängigkeit der Spalte Leiter von Abt!

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 11

Übung zur Normalisierung 3 Redundanzen durch transitive Abhängigkeit der Spalte Leiter von der Spalte Abt(eilung).

Tabelle Sprachen (3NF)

Tabelle Person (2NF)

Pers_Nr

Sprache

Grad

Pers_Nr

Name

Vorname

Geschl

Abt

Leiter

023

engl

2

023

Muh

Magda

w

34

Meier

023

franz

1

145

Wau

Harald

m

34

Meier

437

engl

2

437

Miau

Irene

w

56

Busse

437

russ

3

759

Quak

Andre

m

56

Busse

759

engl

3

854

Piep

Ursel

w

78

Works

854

franz

2 Abt

Leiter

34

Meier

56

Busse

78

Works

Tabelle Abteilung (3NF) • Die transitiv abhängige Spalte Leiter wird gemeinsam mit der Bezugsspalte Abt in eine neue Tabelle gespeichert. • Abt wird Primärschlüssel. • Doppelte Zeilen (Tupel) in der neuen Tabelle werden eliminiert.

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 12

Übung zur Normalisierung 3 Redundanzen durch transitive Abhängigkeit der Spalte Leiter von der Spalte Abt(eilung) wurden beseitigt.

Sprachen (3NF)

Abteilung (3NF)

Person (3NF)

Pers_Nr

Sprache

Grad

Pers_Nr

Name

Vorname

Abt

Geschl

Abt

Leiter

023

engl

2

023

Muh

Magda

34

w

34

Meier

023

franz

1

145

Wau

Harald

34

m

56

Busse

437

engl

2

437

Miau

Irene

56

w

78

Works

437

russ

3

759

Quak

Andre

56

m

759

engl

3

854

Piep

Ursel

78

w

854

franz

2

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Übung zur Normalisierung 4 Überprüfen Sie bitte selbst, wodurch die Redundanzen verursacht werden. ImmatrNr

Name

StudGang SGLeiter

Kurs 6HmesterNote

2317

Frohsinn, S.

WI

Wolf

MAT12

S06

1,7

2317

Frohsinn, S.

WI

Wolf

INF06

W06

5,0

2317

Frohsinn, S.

WI

Wolf

INF06

S07

2,3

2317

Frohsinn, S.

WI

Wolf

BW_12

W07

2,0

3564

Trübsal, F.

BW

Fuchs

BW_08

W07

3,3

4567

Dickfell, K.

WI

Wolf

MAT12

S08

3,0

4567

Dickfell, K.

WI

Wolf

INF03

S08

2,7

8540

Klarsicht, A.

BW

Fuchs

BW_12

S08

1,7

8540

Klarsicht, A.

BW

Fuchs

INF13

W08

2,0

Die Attribute SGLeiter bezeichnen den Studiengangsleiter; StudGang den Studiengang.

Folie 13

HTW Berlin Prof. Dr. Zschockelt

Datenmodellierung/Datenbanken

(04)RDM-Normalisierung-Uebung.ppt

Folie 14

Übung zur Normalisierung 4 Die Lösung besteht in den nachfolgenden 3 Tabellen in 3NF. Bitte ermitteln Sie den Lösungsweg!

Student

Studiengang

ImmatrNr

Name

StudGang

StudGang

SGLeiter

2317

Frohsinn, S.

WI

WI

3564

Trübsal, F.

BW

BW

4567

Dickfell, K.

8540

Klarsicht, A.

Noten Kurs

Sem

Note

Wolf

ImmatrN r

Fuchs

2317

MAT12

S06

1,7

WI

2317

INF06

W06

5,0

BW

2317

INF06

S07

2,3

2317

BW_12

W07

2,0

3564

BW_08

W07

3,3

4567

MAT12

S08

3,0

4567

INF03

S08

2,7

8540

BW_12

S08

1,7

8540

INF13

W08

2,0

Die Spalte Semester aus der Ursprungstabelle wurde in Sem umbenannt. So etwas ist möglich....