Musterlösung 10 - auf Matthias

Lösungsvorschlag 10. Zettel DBS SoSe 09
Diese Lösung erhebt keinen Anspruch auf Vollständig- oder Richtigkeit.
Aufgabe 2 (Concurrency Control) 1 8 Punkte
a) Betrachten Sie folgende Historie:
r1(a), r1(b), r2(a), r2(b), w2(a), c2, w1(b), c1
Konfliktpaare: r1(a) w2(a) , r2(b) w1(b)
Konfliktgraph: T1 ↔ T2
Nicht serialisierbar...
(2) Geben Sie die Ausgabe für das strikte 2PL an. Geben Sie dort, wo es im
Protokoll vorgesehen ist, die entsprechenden Locks und Unlocks an. Die
Locks werden schrittweise vergeben, d.h. bei read und write Zugriff erst
ein Read-Lock, dann ein Write-Lock.
- Kein Consistent-Read, keine ReadOnly-Transaktionen
TA1
TA2
rlock(a)
r1(a)
rlock(b)
r1(b)
rlock(a)
r2(a)
rlock(b)
r2(b)
wlock(a) blocked
wlock(b) blocked
DEAD LOCK
(3) Geben Sie die Ausgabe für das Snapshot Isolation Protokoll für obige
Historie an. Geben Sie die Ausgabe für die Variante „first-committer-wins“
an.
TA1
TA2
r1(a0)
r1(b0)
r2(a0)
r2(b0)
w2(a2)
c2
w1(b1)
c1
b) Betrachten Sie folgende Historie:
r1(a), r2(a), w2(a), c2, w1(a), c1
(1) Geben Sie die Ausgabe für das strikte 2PL für obige Historie an. Geben
Sie dort, wo es im Protokoll vorgesehen ist, die entsprechenden Locks und
Unlocks an. Die Locks werden schrittweise vergeben, d.h. bei read und
write Zugriff erst ein Read-Lock, dann ein Write-Lock.
- Kein Consistent-Read, keine ReadOnly-Transaktionen
TA1
TA2
rlock(a)
r1(a)
rlock(a)
r2(a)
w-lock(a) blocked
wlock(a) blocked
DEAD LOCK
(2) Geben Sie die Ausgabe für das Snapshot Isolation Protokoll für obige
Historie an. Geben Sie die Ausgabe für die Variante „first-committer-wins“
an.
TA1
TA2
r1(a0)
r2(a0)
w2(a2)
c2
w1(a1) abort*
• TA1 wird abgebrochen, weil TA2 schon commited hat.
Aufgabe 3 (Relationale Algebra, SQL) 18 Punkte
Gegeben seien die folgenden Relationen (Primary Keys sind unterstrichen):
• Lieferanten (lnr, lname, status, sitz)
• Teile (tnr, tname, farbe, preis)
• Projekte (pnr, pname, ort)
• Lief_Teile_Proj (lnr, tnr, pnr, zeit, menge)
Projektion = proj a,b,c
Selektion = sel p
Join = IXI p
a) Alle Projekte, die von Lieferant "Wegner & Co" mit grünen Teilen beliefert
werden
proj pnr, pname, ort (((Lief_Teile_Proj IxI tnr=tnr
sel farbe=“grün“ (Teile)) IxI lnr=lnr
sel lname=“Wegner & Co“ (Lieferanten)) IxI pnr=pnr
Projekte)
SELECT p.pname, p.ort
FROM Lieferanten l, Teile t, Projekte p, Lief_Teile_Proj ltp
WHERE l.lnr=ltp.lnr
AND t.tnr=ltp.tnr
AND p.pnr=ltp.pnr
AND t.farbe=“grün“
AND l.lname=“Wegner & Co“;
b) Namen der Lieferanten, die kein Projekt in Berlin beliefern.
proj lname (Lieferanten \ ((Lieferanten IxI lnr=lnr
Lief_Teile_Proj) IxI pnr=pnr
sel ort=“Berlin“ (Projekte)))
SELECT l2.lname
FROM Lieferanten l2
WHERE l2.lnr NOT IN (SELECT l.lnr
FROM Lieferanten l, Projekte p, Lief_Teile_Proj ltp
WHERE l.lnr=ltp.lnr
AND p.pnr=ltp.pnr
AND p.ort=“Berlin“);
c) Anzahl und Gesamtwert aller gelieferten Teile von Projekt "Motor 34"
SELECT COUNT(*) as Anzahl, SUM(t.preis) as Gesamtwert
FROM Teile t, Lief_Teile_Proj ltp, Projekte p
WHERE t.tnr=ltp.tnr
AND p.pnr=ltp.pnr
AND p.pname=“Motor 34“;
d) Die Namen aller Projekte mit der Anzahl der bisher geliefertern Teile, wenn
der Gesamtwert der gelieferten Teile > 10000 ist.
SELECT p.pname, SUM(ltp.menge) as Anzahl
FROM Teile t, Lief_Teile_Proj ltp, Projekte p
WHERE t.tnr=ltp.tnr
AND p.pnr=ltp.pnr
GROUP BY p.pname
HAVING SUM(t.preis*ltp.menge)>10000;
Aufgabe 4 (Funktionale Abhängigkeiten, Normalformtheorie) 14 Punkte
Gegeben sei folgende Relation R(A, B, C):
A
B C
test
A
3
test
B
4
test
C
3
a) Geben Sie alle nicht-trivialen funktionalen Abhängigkeiten an, die sich
aus der Relation ergeben.
B → A, B → C, C → A
AB → C, BC → A
b) Gegeben sei eine Relation mit fünf Attributen. Für jedes Attribut der Relation
gibt es keine zwei doppelten Werte, d.h. alle Werte sind für jedes Attribut
unterschiedlich.
Zur Erinnerung:
Definition: Nichtschlüsselattribut
Jedes Attribut, das Bestandteil keines Kandidatenschlüssels ist,
wird als Nichtschlüsselattribut bezeichnet.
(1) Ist die Relation in 2. und 3. Normalform? Begründen Sie kurz ihre Antwort.
- Ja, sie ist in 2. und 3. Normalform, weil sich die Einschränkungen der 2. und 3.
Normalform nur auf Nichtschlüsselattribute beziehen. In der oben genannten Relation gibt
es keine Nichtschlüsselattribute.
(2) Wie sehen die Kandidatenschlüssel der Relation aus?
- Alle 5 Attribute sind Kandidatenschlüssel.
c) Zeigen Sie: Eine Relation in 3. Normalform mit genau 2 Schlüsselkandidaten,
von denen einer aus einem Attribut besteht, ist in BCNF. (Hinweis: Führen Sie
die Annahme, die Relation sei nicht in BCNF zum Widerspruch.)
Gegeben: R ist Relation, a, B sind Schlüsselkandidaten von R, a besteht aus einem Attribut.
B evtl. aus mehr {b1, … bn}. Sei C die Menge der Nichtschlüsselattribute von R.
Annahme: R ist in 3NF
zu zeigen: R ist auch in BCNF
Beweis (per Widerspruch):
damit R nicht in BCNF ist, muss es eine nicht-triviale funktionale Abhängigkeit X → a in R
geben, für die gilt:
(i) X ist kein Superschlüssel von R.
Nehme f.A. f: X → Y in R an, die nicht trivial ist und X kein Superschlüssel von R.
Da R in 3NF, folgt daraus, dass Y prim ist, also in einem Kandidatenschlüssel enthalten ist.
Fall (1): Y = a
Da es nur 2 Schlüsselkandidaten gibt und f nicht-trivial ist, muss X = B sein. Damit
ist X Superschlüssel → Widerspruch zur Annahme.
Fall (2): Y ist Element von B:
Da es nur 2 Superschlüssel gibt und f nicht-trivial ist, muss Y = a sein.
(Ausformuliert: Gäbe es ein Element c, für das gilt c → Y, dann wäre B \ Y vereinigt mit c
auch ein Schlüsselkandidat der Relation. Das allerdings wurde in der Aufgabenstellung
ausgeschlossen.) → Widerspruch zur Annahme.
→ R ist in BCNF
Aufgabe 5 (Data-Warehousing) 20 Punkte
a) SQL spare ich mir mal, stattdessen nur das relationale Schema:
person(id, geschlecht, geburtstjahr, studiengang)
vorlesung(titel, bereich, grund-haupt)
zeitpunkt(id, datum, semester, quartal, jahr)
pruefungsleistung(zp_id, p_id, vl_titel, note)
grund-haupt kann nur die Werte „Grundstudium“ oder „Hauptstudium“ enthalten.
b) Vergleich der Durschnittsnote von Studentinnen im Bereich technische
Informatik mit der Durchschnittsnote aller Studenten jeweilig für die erfassten
Jahre.
Umformuliert:: Für jedes Jahr ist die Durchschnittsnote aller Mädchen im Bereich
Technische Informatik gesucht im Vergleich zur Durchschnittsnote aller Studenten in allen
Bereichen.
SELECT zp.jahr, p.geschlecht, v.bereich, AVG(pl.note) AS Schnitt
FROM person p, pruefungsleistung pl, zeitpunkt zp, Vorlesung v
WHERE p.id = pl.p_id
AND zp.id = pl.zp_id
AND v.titel = pl.vl_titel
GROUP BY ROLLUP(zp.jahr, p.geschlecht, v.bereich)
HAVING ((p.geschlecht=“weiblich“) AND (v.bereich=“technische Informatik“))
OR ((p.geschlecht IS NULL) AND (v.bereich IS NULL));
Das Having-Prädikat filtert die interessanten Zeilen heraus. Das Ergebnis sähe so aus (nur
die grünen Zeilen):
jahr
geschlecht
bereich
Schnitt
2008
männlich
Technische Inf
1,7
2008
weiblich
Technische Inf
2,3
2008
null
Technische Inf
2,0
2008
männlich
Angew. Inf
2,3
2008
weiblich
Angew. Inf
1,7
2008
null
Angew. Inf
2,0
...
2008
weiblich
null
1,7
2008
männlich
null
1,9
2008
null
null
1,8
2009
männlich
Technische Inf
1,7
2009
weiblich
Technische Inf
2,0
2009
null
Technische Inf
2,0
c) Vergleich der Durchschnittsnote der einzelnen Vorlesung mit den
Durchschnittsnoten der Studenten bestimmter Studiengänge jeweilig für die
erfassten Semester.
SELECT zp.semester, vl.titel, p.studiengang, AVG(pl.note) AS Schnitt
FROM person p, pruefungsleistung pl, zeitpunkt zp, Vorlesung v
WHERE p.id = pl.p_id
AND zp.id = pl.zp_id
AND v.titel = pl.vl_titel
GROUP BY ROLLUP(zp.semester, vl.titel, p.studiengang);
Ergebnis liefert auch folgende Zeile, das nehmen wir jetzt mal so hin :)
semester
titel
studiengang
Schnitt
11
null
null
1,7
d) Vergleich der Durchschnittsnoten im Haupt- und Grundstudium jeweilig für die
Geschlechter.
SELECT p.geschlecht, vl.grund-haupt, AVG(pl.note) AS Schnitt
FROM person p, pruefungsleistung pl, Vorlesung v
WHERE p.id = pl.p_id
AND v.titel = pl.vl_titel
GROUP BY ROLLUP(vl.grund-haupt, p.geschlecht);