AG Datenbanken und Informationssysteme 4. Übungsblatt

Datenbankanwendung
AG Datenbanken und Informationssysteme
Wintersemester 2008 / 2009
Prof. Dr.-Ing. Dr. h. c. Theo Härder
Fachbereich Informatik
Technische Universität Kaiserslautern
http://wwwlgis.informatik.uni-kl.de/cms/
4. Übungsblatt
Für die Übung am Freitag, 21. N ovember 2008,
von 11:45 bis 13:15 Uhr in 46/280.
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
PERS2 (PNR, ANR, Name, Büro-Nr, Tel-Nr)
"Zerlegung"/Umformung von GEH-HIST in
GEH-HIST2 (PNR, Datum, Gehalt)
[ PERS_IN_ABT2 (PNR, ANR) ]
"Zerlegung"/Umformung von BÜRO1 in
BÜRO2 (Büro-Nr, ANR, Fläche)
"Zerlegung"/Umformung von TEL1 in
TEL2 (Tel-Nr, Büro-Nr, ANR)
- ABT1 ist bereits in 2NF.
- Relation PERS_IN_ABT2 ist in PERS2 bereits abgebildet und damit redundant
Aufgabe 1: N ormalisierung von Relationen
Gegeben sei folgende unnormalisierte Relation:
ABT (ANR, AName, Budget,
PERS (PNR, Name, Büro-Nr, Tel-Nr, GEH-HISTORIE (Datum, Gehalt)),
BÜRO (Büro-Nr, Fläche, TEL (Tel-Nr)))
mit den relationenwertigen Attributen PERS, GEH-HISTORIE, BÜRO und TEL.
Gegeben sind folgende funktionalen Abhängigkeiten:
ANR → AName, Budget
PNR → Name, ANR, Büro-Nr, Tel-Nr
Tel-NR → Büro-Nr
Büro-Nr → ANR, Fläche
PNR, Datum → Gehalt
Überführen Sie die Relation schrittweise in 1NF, 2NF und 3NF.
Lösung:
Aus dem Aufgabentext ergeben sich noch die beiden FAen:
Überführung in 1NF (Relationen mit atomaren Attributwerten mittels „copy down the key“):
ABT1 (ANR, AName, Budget)
PERS1 (ANR, PNR, Name, Büro-Nr, Tel-Nr)
GEH-HIST1 (ANR, PNR, Datum, Gehalt)
BÜRO1 (ANR, Büro-Nr, Fläche)
TEL1 (ANR, Büro-Nr, Tel-Nr)
Überführung in 3NF (Entfernen transitiver Abhängigkeiten):
Transitive Abhängigkeitskette in PERS1: PNR → Tel-Nr → Büro-Nr → ANR.
Zerlegung von PERS2 in:
PERS3 (PNR, Name, Tel-Nr)
TEL_IN_BÜRO3 (Tel-Nr, Büro-Nr)
BÜRO_IN_ABT3a (Büro-Nr, ANR)
Zerlegung von TEL2 in:
TEL3 (TelNr, BüroNr)
BÜRO_IN_ABT3b (Büro-Nr, ANR)
- BÜRO2, ABT1 und GEH-HIST2 sind bereits in 3NF.
- Relationen TEL_IN_BÜRO3 und TEL3 sind redundant.
- Relationen BÜRO_IN_ABT3a und BÜRO_IN_ABT3b sind redundant.
ACHTUNG: Die Kette von transitiven Abhängigkeiten in PERS2 kann durch verschiedene Teilschritte zerlegt werden. Hierdurch kann zusätzlich z.B. die Relation TEL_IN_ABT
(TelNr, ANR) entstehen. Diese Lösung wäre auch ein Schema in 3NF. Die Aufnahme der
Relation sollte jedoch vermieden werden, weil die in ihr abgebildete Abhängigkeit bereits
durch die Relationen TEL_IN_BÜRO3 und BÜRO_IN_ABT3 abgebildet ist.
Ergebnisschema in 3NF:
Überführung in 2NF (jedes Nicht-Primärattribut voll funktional von jedem Schlüsselkandidaten abhängig):
Momentan sind die gewählte Primärschlüssel von BÜRO1, GEH-HIST1, TEL1 und
PERS1 nicht minimal:
"Zerlegung"/Umformung von PERS1 in
ABT1 (ANR, AName, Budget)
PERS3 (PNR, Name, Tel-Nr)
GEH-HIST2 (PNR, Datum, Gehalt)
BÜRO2 (Büro-Nr, ANR, Fläche)
BÜRO_IN_ABT3 (Büro-Nr, ANR)
TEL_IN_BÜRO3 (TelNr, BüroNr)
Seite 1
Seite 2
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
Aufgabe 2: N ormalisierung von Relationen in BCN F
Gegeben seien R = {VERKÄUFER, KUNDE, DATUM, ARTIKEL, HERSTELLER, LIEFERADR,
PREIS} mit folgender Relation:
BESTELLUNG (VERKÄUFER, KUNDE, DATUM, ARTIKEL, HERSTELLER,
LIEFERADR, PREIS)
und eine FA-Menge
F = { KUNDE → VERKÄUFER,
KUNDE, DATUM → LIEFERADR, ARTIKEL,
DATUM, ARTIKEL, HERSTELLER → PREIS,
ARTIKEL → HERSTELLER,
LIEFERADR → KUNDE }.
Normalisieren Sie die Relation BESTELLUNG bzgl. F, bis alle Relationen in BCNF sind. Ist die Zerlegung in BCNF-Relationen sinnvoll? Begründen Sie Ihre Antwort.
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
(3) ARTIKEL (A, H) mit A als Schlüsselkandidat und H als Nicht-Primärattribut.
ARTIKEL ist in BCNF (einziger Determinant A ist Schlüsselkandidat).
f) Schließlich prüft man noch, ob BESTELLUNG2 (K, D, A, L) in BCNF ist:
Sie ist nicht in BCNF, weil der Determinant L (L → K) kein Schlüsselkandidat ist. Die Relation
wird in folgende BCNF-Relationen zerlegt werden:
(1) BESTELLUNG3 (K, D, A) mit KD als Schlüsselkandidat und A als Nicht-Primärattribut
(2) LIEFERADRESSE (L, K) mit L als Schüsselkandidat und K als Nicht-Primärattribut.
Die Zerlegung von BESTELLUNG2 in BCNF ist allerdings nicht sinnvoll (zu streng), weil KD → L
dann nicht bewahrt werden kann und extern geprüft werden muss.
Aufgabe 3: 4N F am Beispiel Kredite
Gegeben sei folgende Relation:
Lösung:
Sei
KREDITE (KONTONR, LFD_KREDITNR, DATUM, NAME, ADRESSE)
V = VERKÄUFER, K = KUNDE, D = DATUM, A = ARTIKEL, L = LIEFERADR, P = PREIS,
H = HERSTELLER
und F = {K → V, KD → LA, DAH → P, A → H, L → K}
a) Man bestimmt zuerst alle Schüsselkandidaten in B:
Schlüsselkandidaten sind KD und DL. Denn für KD gilt {KD}+ = R und {K}+ = {KV} <> R bzw.
{D}+ = {D} <> R. Analog für DL gilt {DL}+ = R und {L}+ = {LKV} <> R bzw. {D}+ = {D} <> R.
b) Demnach ergeben sich die Nicht-Primärattribute:
Die Nicht-Primärattribute sind A, H, P und V (alle Attribute, die nicht zu einem Schlüsselkandidaten gehören).
In dieser Relation sollen folgende Abhängigkeiten gelten:
• Ein Kunde kann mehrere Konten haben.
• Ein Kunde kann mehrere Adressen haben.
• Ein Konto kann mehrere Besitzer haben.
• Auf einem Konto können mehrere Kredite aufgenommen werden.
• Ein Kredit auf einem Konto läuft seit einem festgelegten Datum
Folgendes Beispiel erfüllt diese Bedingungen:
Man untersucht BESTELLUNG (V, K, D, A, H, L, P) bzgl. der Normalformen:
KONTONR
c) BESTELLUNG ist in 1NF, da alle Attribute atomare Werte besitzen.
100
1
01.01.2002
Müller
KL
100
1
01.01.2002
Maier
MA
100
1
01.01.2002
Maier
SB
100
2
01.03.2002
Müller
KL
100
2
01.03.2002
Maier
MA
100
2
01.03.2002
Maier
SB
110
1
15.11.2001
Müller
KL
110
2
01.02.2002
Müller
KL
120
1
01.06.2002
Maier
MA
120
1
01.06.2002
Maier
SB
d) BESTELLUNG ist nicht in 2NF, weil sowohl K → V als auch L → V, d.h. das Nicht-Primärattribut V ist von KD bzw. DL nicht voll funkional abhängig.
Daher zerlegt man BESTELLUNG in die 2NF Relationen:
(1) BESTELLUNG1 (K, D, A, H, L, P) mit KD und DL als Schlüsselkandidaten sowie A, H und
P als Nicht -Primärattribute.
(2) KUNDE (K, V) mit K als Schlüsselkandidat und V als Nicht-Primärattribut.
KUNDE ist in BCNF (einziger Determinant K ist auch Schlüsselkandidat).
e) Man prüft, ob BESTELLUNG1 (K, D, A, H, L, P) in 3NF:
BESTELLUNG1 ist nicht in 3NF, da H transitiv abhängig von KD ist (KD → A → H) und P transitiv abhängig von KD (KD → LA, da A → H gilt KD → DAH → P). Daher überführt man
BESTELLUNG1 in folgende Relationen in 3NF:
(1) BESTELLUNG2 (K, D, A, L) mit KD und DL als Schlüsselkandidaten sowie A als Nicht Primärattribut.
(2) PREIS (D, A, P) mit DA als Schlüsselkandidat und P als Nicht-Primärattribut.
PREIS ist in BCNF (einziger Determinant DA ist Schlüsselkandidat).
LFD_KREDITNR
DATUM
NAME
ADRESSE
a) Welche Abhängigkeiten bestehen in dieser Relation?
b) Wie lässt sich die Relation in eine 4NF zerlegen?
Seite 3
Seite 4
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
Lösung:
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
Folgende FAen seien erhoben worden:
a) Mehrwertige Abhängigkeiten sind direkt aus Aufgabenstellung abzulesen:
NAME -->> KONTONR, ADRESSE
(Kontonr, Adresse mwtig abh. von Name)
KONTONR -->> NAME, LFD_KREDITNR
(Name, Lfd_Kreditnr mwtig abh. von Kontonr)
funktionale Abhängigkeit:
KONTONR, LFD_KREDITNR -> DATUM
b) Zerlegung in die 4NF-Relationen:
R1 (KON TON R, LFD_KREDITN R, DATUM)
R2 (KON TON R, N AME)
R3 (N AME, ADRESSE)
mit den Inhalten:
KONTONR
LFD_KREDITNR
100
1
DATUM
KONTONR
NAME
NAME
ADRESSE
01.01.2002
100
Müller
Müller
KL
100
2
01.03.2002
100
Maier
Maier
MA
110
1
15.11.2001
110
Müller
Maier
SB
110
2
01.02.2002
120
Mayer
120
1
01.06.2002
Die durch die Zerlegung entstandenen Relationen sind laut Definition in 4NF, denn R1, R2 und
R3 sind in BCNF und bei R1, R2 und R3 liegen keine mehrwertigen Abhängigkeiten vor.
Aufgabe 4: Synthese von Relationen in 3N F
Die Beziehungen der Miniwelt, einem anwendungsbezogenen Ausschnitt der realen Welt, sollen im
Hinblick auf das Informationsbedürfnis der verschiedenen Anwendungen einer Datenbank unabhängig von ihrer physischen Realisierung im konzeptionellen Schema (logisches Datenmodell) beschrieben und dargestellt werden. Im Relationenmodell sind Relationen in 3NF zur Vermeidung bestimmter
Anomalien beim Änderungsdienst als logische Datenstrukturen besonders gut geeignet.
Ein Weg zur Ableitung dieser logischen Datenstrukturen ist die Relationensynthese. Ausgangspunkt
dafür ist die Beschreibung der Miniwelt durch Attribute und FAen, die entsprechend dem Informationsbedarf der geplanten Anwendungen zu erheben sind. Attribute und FAen dienen als Erhebungsprimitive für den IST-Zustand. Alle möglichen FAen, die gegebenenfalls aus der erhobenen Menge
abgeleitet sind, bilden eine transitiv abgeschlossene Menge, die sich als gerichteter Graph darstellen
lässt. Für den Entwurf von Relationen interessiert eine nichtredundante Überdeckung der FAen der
transitiven Hülle, die eine redundanzfreie logische Datenstruktur garantiert. Aus dieser minimalen
Menge von FAen lassen sich schrittweise Relationen in optimaler 3NF erzeugen.
NAME, GEB, ADR
MATRNR
MATRNR
FACHR
FG
PROF
FBNAME
DEKAN
FBNR
FBADR
VORL
→
→
→
→
→
→
→
→
→
→
→
MATRNR, SEMANZ
NAME, GEB, ADR, SEMANZ
FACHR, FBNR
FBNAME
FBNAME, FACHR
FACHR, FG, FBNR
DEKAN
FBNR
FBADR
FBNAME
FBNR, STDANZ
a) Stellen Sie die Menge der FAen durch einen Graph dar.
b) Der erhaltene FA-Graph hat Zyklen. Da jede FA-Beziehung eines der am Zyklus beteiligten Attribute für jedes Attribut des Zyklus gilt, kann der Zyklus separiert und durch ein Ersatzattribut (Ersatzknoten) im FA-Graph ersetzt werden. Zeichnen Sie den reduzierten FA-Graph nach
Elimination der Zyklen.
c) Für jeden Zyklus kann isoliert eine minimale Überdeckung gefunden werden. Finden Sie für die
Zyklen geeignete minimale Überdeckungen.
d) Finden Sie eine minimale Überdeckung für den reduzierten FA-Graph und ersetzen sie die Ersatzattribute wieder durch eine geeignete minimale Überdeckung des entsprechenden Zyklus. Damit haben sie eine minimale Überdeckung für das Anwendungsbeispiel gefunden.
e) Bilden Sie aus den FAen der gefundenen minimalen Überdeckung Relationen in 3NF und diskutieren Sie das Ergebnis.
Lösung:
a) Stellen Sie die Menge der erhobenen FAen durch einen Graph dar.
NAME, GEB, ADR
SEMANZ
STDANZ
PROF
FG
MATRNR
FACHR
FBNR
VORL
Für folgendes Anwendungsbeispiel ist die Relationensynthese für Relationen in optimaler 3NF durchzuführen:
Es seien in einer Miniwelt folgende Attribute, die die Entities Student (STUD), Fachbereich (FB),
Vorlesung (VL), Professor (PR), Forschungsgruppe (FG) und Fachrichtung (FR) beschreiben, bekannt:
Name (NAME), Matrikelnummer (MATRNR), Geburtsdatum (GEB), Adresse (ADR), Semesteranzahl (SEMANZ), Fachbereichsnummer (FBNR), Fachbereichsname (FBNAME), Dekan (DEKAN),
Fachbereichsadresse (FBADR), Professor (PROF), Vorlesung (VORL), Fachrichtung (FACHR),
Forschungsgruppe (FG) und Stundenanzahl (STDANZ).
FBNAME
DEKAN
FBADR
Seite 5
Seite 6
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 4
b) Der erhaltene FA-Graph hat Zyklen. Da jede FA-Beziehung eines der am Zyklus beteiligten Attribute für jedes Attribut des Zyklus gilt, kann der Zyklus separiert und durch ein Ersatzattribut (Ersatzknoten) im FA-Graph ersetzt werden. Zeichnen Sie den reduzierten FA-Graph nach
Elimination der Zyklen.
SEMANZ
PROF
FG
E1
FACHR
STDANZ
E2
VORL
c) Für jeden Zyklus kann isoliert eine minimale Überdeckung gefunden werden. Finden Sie für die
Zyklen geeignete minimale Überdeckungen.
E1: (MATRNR → (NAME GEB ADR)), ((NAME GEB ADR) → MATNR)
E2: (FBNR → FBADR), (FBADR → FBNAME), (FBNAME → DEKAN), (DEKAN → FBNR)
d) Finden Sie eine minimale Überdeckung für den reduzierten FA-Graph und ersetzen sie die Ersatzattribute wieder durch eine geeignete minimale Überdeckung des entsprechenden Zyklus. Damit haben sie eine minimale Überdeckung für das Anwendungsbeispiel gefunden.
SEMANZ
STDANZ
PROF
FG
E1
FACHR
E2
VORL
e) Bilden Sie aus den FAen der gefundenen minimalen Überdeckung Relationen in 3NF. Diskutieren
Sie das Ergebnis.
Wähle für jeden Zyklus ein repräsentatives Element: E1:MATRNR, E2: FBNR
R1: (E1 SEMANZ FACHR) = (MATRN R NAME GEB ADR SEMANZ FACHR)
R2: (FACHR "Element aus (E2)")= (FACHR FBNR)
R3: (PROF FG)
R4: (FG FACHR)
R5: (VORL "Element aus (E2)" STDANZ) = (VORL FBNR STDANZ)
R6: (FBNR, FBADR)
R7: (FBADR, FBNAME)
R8: (FBNAME, DEKAN)
Zu diskutieren wäre evtl.:
R6’: (E2) = (FBN R FBNAME FBADR DEKAN) R6-R8 entfallen
R3’: (PROF FG FACHR FBNR) ==> unter der Voraussetzung, dass Relation sehr stabil
(Schreibweise: Primärschlüssel, Eindeutigkeit)
Seite 7
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !