Erläuterungen Übungsblatt 4

Übungsblatt 4
Erläuterungen
Wintersemester 16/17
DBIS
1
Aufgabe 1
Relationales Datenbankmodell
Wintersemester 16/17
DBIS
2
Überführung Schritt 1 - Entitytypen und deren Attribute übernehmen
Kunde(KundenNr, Name, Adresse)
Pkw(FahrzeugNr, Farbe, Baujahr)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*, Telefon)
Wintersemester 16/17
DBIS
3
Überführung Schritt 1 - Entitytypen und deren Attribute übernehmen
Kunde(KundenNr, Name, Adresse)
Pkw(FahrzeugNr, Farbe, Baujahr)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*, Telefon)
Problem: Nichtatomare Attribute
Wintersemester 16/17
DBIS
4
Überführung Schritt 2 – Nichtatomare Attribute auflösen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
Wintersemester 16/17
DBIS
5
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
1:1-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
Wintersemester 16/17
DBIS
6
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
1:N-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
Wintersemester 16/17
DBIS
7
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
1:N-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
Wintersemester 16/17
DBIS
8
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
Wintersemester 16/17
DBIS
9
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
Wintersemester 16/17
DBIS
10
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
hasst(KundenNr, HSt)
Wintersemester 16/17
DBIS
11
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
hasst(KundenNr, HSt)
bevorzugt(KundenNr, HSt)
Wintersemester 16/17
DBIS
12
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
hasst(KundenNr, HSt)
bevorzugt(KundenNr, HSt)
beteiligt_an(Mutter,Tochter)
Wintersemester 16/17
DBIS
13
Überführung Schritt 4 – Logische Prüfung des Modells
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
hasst(KundenNr, HSt)
bevorzugt(KundenNr, HSt)
beteiligt_an(Mutter,Tochter)
Wintersemester 16/17
DBIS
14
Überführung Schritt 4 – Logische Prüfung des Modells
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
Die KartenNr in PKW?!
hasst(KundenNr, HSt)
 Eigene Relation
bevorzugt(KundenNr, HSt)
beteiligt_an(Mutter,Tochter)
für Miete semantisch
sinnvoll
Wintersemester 16/17
DBIS
15
Überführung Schritt 3 – Nach und nach die Beziehungen auflösen
N:M-Beziehungen
Kunde(KundenNr, Name)
hat_Adresse(KundenNr, Strasse, Ort, PLZ)
Pkw(FahrzeugNr, Farbe, Baujahr, KartenNr, KdnNr, HSt, WSt)
Hersteller(Name, Hauptsitz)
Werkstatt(Name, FaxNr*)
hat_telefon(WerkstattName, TelefonNr)
empfiehlt(HSt,WSt)
hasst(KundenNr, HSt)
Telefonnummern-Sharing?!
bevorzugt(KundenNr, HSt)
beteiligt_an(Mutter,Tochter)
Wintersemester 16/17
DBIS
16
Überführung Schritt 4 – Kardinalitäten durch Optionalität retten
• „Nulloption“ auf der N-Seite in 1:N-Beziehungen bereits
vorhanden
• „Nulloption“ in N:M bereits vorhanden
 PKW.KdNr bleibt als sinnvoll übrig
• Weitere Attribute optional vorstellbar (eigenes Modell überprüfen):
• PKW.Farbe?
• Vermietung.KartenNr?
• Attribute in hat_Adresse?
• Hersteller.Hauptsitz?
Wintersemester 16/17
DBIS
17
Überführung Schritt 5 – Kardinalitäten (und anderes) durch
Integritätsbedingungen retten
• In Vermietung darf ein konkreter Kunde nur höchstens dreimal
auftreten
• In beteiligt_an darf ein konkreter Hersteller nicht gleichzeitig als
Mutter und Tochter auftreten (Gewährleistung von Zyklenfreiheit)
• In Werkstatt.Telefon sollte jede Telefonnummer nur einmal
auftreten?!? (und andere Miniwelt-Spezifika)
Wintersemester 16/17
DBIS
18
Aufgabe 2
Stücklisten
Wintersemester 16/17
DBIS
19
Überführung in relationales Modell
Wintersemester 16/17
DBIS
20
Überführung in relationales Modell
Bauteil(Bezeichnung, Preis)
Stückliste(Oberteil, Unterteil, Anzahl)
Wintersemester 16/17
DBIS
21
Relationsschema und Relationen
Bauteil
Bezeichnung
Wintersemester 16/17
Preis
Stückliste
DBIS
Oberteil Unterteil Anzahl
22
Relationsschema und Relationen
Bauteil
Bezeichnung
Preis
A
1,00
B
2,00
C
3,00
D
2,00
E
4,00
X
5,00
Y
5,50
Z
9,99
Wintersemester 16/17
Stückliste
DBIS
Oberteil Unterteil Anzahl
23
Relationsschema und Relationen
Bauteil
Bezeichnung
Preis
Stückliste
Oberteil Unterteil Anzahl
A
1,00
X
A
1
B
2,00
X
B
1
C
3,00
X
Z
1
D
2,00
B
E
7
E
4,00
A
C
4
X
5,00
A
D
8
Y
5,50
A
E
1
Z
9,99
E
Z
3
D
Y
5
C
Y
4
Wintersemester 16/17
DBIS
24
Referenzielle Integrität I
• Im Allgemeinen nur solche Instanzen einer Datenbank erlaubt, deren
Relationen die definierten Integritätsbedingungen erfüllen.
• Integritätsbedingungen sind deklarativ: Sie definieren die zulässigen
Instanzen, ohne Aussagen zur konkreten Umsetzung.
• Z.B. Fremdschlüsselbedingungen
• keine defekten Verweise zwischen den Tupeln in den Tabellen
 Referentielle Integrität
• Z.B. Trigger
• Regeln für Aktionen zur Gewährleistung der Integrität
• Auch für Behandlung von Verletzungen.
Wintersemester 16/17
DBIS
25
Referenzielle Integrität II
• „Definition der Umsetzung“ über referential Actions: Mögliche referentielle
Aktionen, wenn das Tupel, auf das sich der Fremdschlüssel bezieht,
gelöscht (ON DELETE) bzw. geändert (ON UPDATE) wird:
Wintersemester 16/17
DBIS
26
Referenzielle Integrität III
• Anwendbarkeit im Beispiel
Wintersemester 16/17
DBIS
27
Aufgabe 3
Flüsse
Wintersemester 16/17
DBIS
28
Lösung mit zwei Relationen
Wintersemester 16/17
DBIS
29
Lösung mit zwei Relationen
Fluss(Name, Länge)
mündet_in(Hauptfluss, Nebenfluss, Ort)
Sind in dem Modell Flüsse darstellbar, die in keinen anderen
Fluss münden?
Nullwerte sind schon wegen der Modellierung nicht erlaubt!
Wintersemester 16/17
DBIS
30
Lösung mit einer Relation
Fluss(Name, Länge, Einmündungsfluss, Ort)
Sind in dem Modell Flüsse darstellbar, die in keinen anderen
Fluss münden?
Fluss(Name, Länge, Einmündungsfluss*, Ort*)
Wintersemester 16/17
DBIS
31
Aufgabe 4
Relationen
Wintersemester 16/17
DBIS
32
Zu a)
• Kreuzprodukt (kartesisches Produkt) sind alle
Kombinationsmöglichkeiten der Domänenwerte, aus
MatrNr x Fach x Semester
• Summe: 2 * 2 * 3 = 12 Elemente
• Ergebnis:
(4711, Informatik, 1), (4711, Informatik, 2), (4711, Informatik, 3),
(4711, Biologie, 1), (4711, Biologie, 2), (4711, Biologie, 3),
(79005, Informatik, 1), (79005, Informatik, 2), (79005, Informatik, 3),
(79005, Biologie, 1), (79005, Biologie, 2), (79005, Biologie, 3)
Wintersemester 16/17
DBIS
33
Zu b)
• Die Kombinationsmöglichkeiten berechnen
sich zum Beispiel mit 2n, wobei n die Anzahl
der Elemente des kartesischen Produktes ist.
• 212 = 4096
Wintersemester 16/17
DBIS
34
Zu c)
• MatrNr als Schlüssel  MatrNr muss eindeutig sein
• Ein Beispiel in Mengenschreibweise:
R={(4711,Informatik,1), (79005, Informatik,2)}
• Ein Beispiel als Tabelle:
Wintersemester 16/17
4711
Informatik
1
79005
Informatik
2
DBIS
35