Blatt 13

Lehrstuhl für Praktische Informatik III
Prof. Dr. Carl-Christian Kanne
Email: [email protected]
Norman May
B6, 29, Raum C0.05
68131 Mannheim
Telefon: (0621) 181–2517
Email: [email protected]
Datenbanksysteme 1
Sommersemester 06
13. Übungsblatt
Aufgabe 1
relationales Modell
Im folgenden ist ein (unvollständiges) konzeptuelles Schema und ein zugehöriges
(lückehaftes) ER-Diagramm abgebildet. Vervollständigen Sie jedes Schema mit den
Informationen aus dem jeweils anderen Schema.
lückenhaftes relationales Schema:
• B(b1 , b2 , b3 )
• D(c1 , c2 , d1 )
• J(c1 , c2 , j1 , j2 )
• R(c1 , a1 , b1 , r1 )
• T(b1 , c1 , f1 )
lückenhaftes ER-Schema:
a1
a2
A
N
S
f1
c1
c2
C
F
f2
f3
1
Lösung
vollständiges relationales Schema:
• B(b1 , b2 , b3 )
• D(c1 , c2 , d1 )
• J(c1 , c2 , j1 , j2 )
• R(c1 , a1 , b1 , r1 )
• T(b1 , c1 , f1 )
• F(f1 , f2 , f3 )
• A(c1 , a1 , a2 )
volständiges ER-Schema:
a1
r1
a2
N
A
R
b3
B
T
S
1
N
1
C
c2
M
b2
M
N
c1
b1
f1
F
f2
f3
is−a
D
d1
J
j1
j2
Aufgabe 2
SQL
2
In einer Videothek wird eine relationale Datenbank zur Verwaltung der Videokassetten eingesetzt. In der Relation
• Kassette(Nummer, Filmtitel, Leihgebühr)
wird die Nummer der Kassette, der Titel des Films und die Leihgebühr pro Tag
in D-Mark festgehalten.
• Ausleihe(Name, Kassettennummer, Leihdauer)
wird der Name der ausleihenden Person, die ausgeliehenen Kassettennummern
sowie die Anzahl der Tage, die eine Kassette bereits ausgeliehen ist, gespeichert.
• FilmInfo(Filmtitel, Schauspieler)
wird vermerkt, welche Schauspieler in welchem Film mitwirken.
Formulieren Sie folgende Anfragen in SQL:
Aufgabe 2 a)
Geben Sie alle Filmtitel an, die in dieser Videothek geführt werden. Beachten Sie, daß
einige Kassetten mit demselben Filmtitel bespielt sein können.
Lösung
SELECT DISTINCT Filmtitel
FROM Kassette
Aufgabe 2 b)
Geben Sie die Namen aller Kunden an, die eine Kassette schon länger als zehn Tage
ausgeliehen haben.
Lösung
SELECT Name
FROM Ausleihe
WHERE Leihdauer > 10
Aufgabe 2 c)
Geben Sie die Namen aller Kunden an, die sich Filme ausgeliehen haben, in denen
Sharon Stone mitspielt. Geben Sie in dieser Anfrage auch die Kassettennummer sowie
die jeweilige Leihdauer an.
Lösung
3
SELECT a.Name, a.Leihdauer, a.Kassettennummer
FROM Ausleihe a, FilmInfo f, Kassette k
WHERE f.Filmtitel = k.Filmtitel
AND k.Nummer = a.Kasssettennummer
AND f.Schauspieler LIKE ’%Sharon Stone%’
Aufgabe 2 d)
Geben Sie den maximalen täglichen Gesamtumsatz (Summe aller Leihgebühren) an,
der in dieser Videothek erreicht werden kann.
Lösung
SELECT SUM(Leihgebuehr)
FROM Kassette
Aufgabe 2 e)
Geben Sie für alle Kunden die Summe der zu zahlenden Leihgebühren bei Rückgabe
sämtlicher aktuell ausgeliehener Kassetten an. Sortieren Sie die Liste alphabetisch nach
dem Kundennamen.
SELECT a.Name, SUM(a.Leihdauer * k.Leihgebuehr)
FROM Ausleihe a, Kassette k
WHERE k.Nummer = a.Kasssettennummer
GROUP BY a.Name
ORDER BY a.Name
Aufgabe 3
relationale Algebra und Kalkulus
Aufgabe 3 a)
Geben Sie einen äquivalenten Ausdruck zu (R 1R.A=S.B S) 1S.C=T.D T an, der nur
aus Kreuzprodukten und Selektionen besteht.
Lösung
(σS.C=T.D (σR.A=S.B (R × S)) × T )
Aufgabe 3 b)
4
Geben Sie einen äquivalenten Ausdruck zu (R ⋉R.A=S.B S) an, der nur aus Joins und
Projektionen besteht.
Lösung
πR.∗ (R 1R.A=S.B S)
Aufgabe 3 c)
Übersetzen Sie den folgenden Ausdruck des Domänenkalküls in die relationale Algebra.
{a|a ∈ Abteilung ∧ ∃n ∈ Angestellte(a.AbteilNr = n.AbteilNr∧
a.Ort = n.W ohnort)}
Lösung
πa.∗ ((ρa (Abteilung)) ⋉a.AbteilN r=n.AbteilN r∧a.Ort=n.W ohnort (ρn (Angestellte)
Aufgabe 4
relationale Entwurfstheorie
Gegeben sei ein Relationenschema R(A, B, C, D, E, F ) mit funktionalen Abhängigkeiten FR . Lösen Sie in jeder Teilaufgabe folgende Teilprobleme:
• Geben Sie alle Kandidatenschlüssel von R an.
• In welcher höchsten Normalform ist R?
• Zerlegen Sie R verlustlos in Teilrelationen, so dass die Teilrelationen mindestens
in 3. Normanform sind. Wenn R bereits in der 3. Normalform ist, machen Sie
deutlich, daß keine Zerlegung nötig ist.
Aufgabe 4 a)
FR = {A → B; C → D; E → F }.
Lösung
• Kandidatenschlüssel: ACE
• höchste Normalform: 1NF
• Zerlegung in 3NF: (A,B), (C,D), (E,F), (A,C,E)
5
Aufgabe 4 b)
FR = {A, B → C; B, C → D; C, D → E; D, E → F }.
Lösung
• Kandidatenschlüssel: AB
• höchste Normalform: 2NF
• Zerlegung in 3NF: (A,B,C), (B,C,D), (C,D,E), (D,E,F)
Aufgabe 4 c)
FR = {A → B; A, B → C; A, B, C → D; A, B, C, D → E; A, B, C, D, E → F }.
Lösung
• Kandidatenschlüssel: A
• höchste Normalform: 4NF (die letzten 4 FDs sind nicht links-reduziert)
• Zerlegung in 3NF: keine Zerlegung nötig
Aufgabe 4 d)
FR = {A → B, C, D, E, F ; B → C, D, E, F ; C → D, E, F ; D → E, F ; E → F }.
Lösung
• Kandidatenschlüssel: A
• höchste Normalform: 2NF
• Zerlegung in 3NF: (A,B), (B,C), (C,D), (D,E), (E,F)
Aufgabe 5
Fehlerbehandlung
6
Aufgabe 5 a)
Nennen Sie drei Fehlerklassen, die in der Recovery behandelt werden und erläutern
Sie diese kurz.
Lösung
• lokaler Fehler in einer noch nicht festgeschrieben Transaktion
• Fehler mit Hauptspeicherverlust
• Fehler mit Hintergrundspeicherverlust
Aufgabe 5 b)
Erläutern Sie kurz den Unterschied zwischen den Strategien force und ¬ force beim
Einbringen von Änderungen einer Transaktion.
Lösung
Die force-Strategie erzwingt das ausschreiben aller von einer Transaktion geänderten
Seiten, wenn die Transaktion ein commit ausführt, die ¬ force-Strategie erzwingt dies
nicht.
Bei Verwendung der force-Strategie muß niemals ein Redo ausgeführt werden. Somit
müssen auch keine Redo-Informationen protokolliert werden.
Aufgabe 5 c)
Erläutern Sie kurz, was sich hinter dem WAL-Prinzip verbirgt.
Lösung
WAL steht für “write-ahead-logging”. Das WAL verlangt die Einhaltung von 2
Regeln:
1. Bevor eine Transaktion festgeschrieben wird (commit), müssen alle “zu ihr gehörenden” Log-Einträge ausgeschrieben werden.
2. Bevor eine modifizierte Seite ausgekagert werden darf, müssen alle Log-Einträge,
die zu dieser Seite gehören, ausgeschrieben werden.
Aufgabe 5 d)
Welcher Mechanismus der Recovery stellt die Idempotenz der Redo-Phase sicher.
7
Lösung
Auf jeder Seite wird die LSN der letzten Änderung der Seite eingetragen. Alle Änderungen mit kleinerer LSN sind damit auch auf der Seite enthalten. Somit muß ein Redo
noch ausgeführt werden. Änderungen mit größerer LSN sind noch nicht in der Datenbasis. Somit muß kein Redo ausgeführt werden.
Aufgabe 6
Mehrbenutzersynchronisation
Abbildung 1 zeigt die Beziehungen von Historienklassen zueinander. Zeigen Sie,
daß alle Mengen in diesem Diagramm nicht leer sind, d.h. geben Sie Beispiele für die
Historien H1 bis H9 an.
alle Historien
H9
RC
SR
H8
ACA
H7
ST
H6
serielle Historien
H1
H2
H3
H4
H5
Abbildung 1: Beziehungen von Historienklassen zueinander
Lösung
H1 : r8 [x] w8 [x] r8 [y] r9 [y] r9 [x] w9 [y] c9 w8 [y] c8
H2 : r8 [x] w8 [x] r8 [y] r9 [y] r9 [x] w9 [y] w8 [y] c8 c9
H3 : r1 [x] r10 [x] w1 [x] w4 [x] c1 c4 w10 [x] c10
H4 : r6 [x] r6 [y] r7 [y] w6 [x] w6 [y] c6 w7 [y] c7
H5 : r1 [x] w1 [x] c1 r2 [y] w2 [y] c2
8
H6 : r1 [x] w1 [x] r3 [y] c1 w3 [x] c3
H7 : r1 [x] w1 [x] w4 [x] c1 c4 r5 [x] w5 [y] c5
H8 : r1 [x] w1 [x] r5 [x] w5 [y] c1 c5
H9 : r1 [x] w1 [x] r5 [x] w5 [y] c5 c1
9