Gruppe *Demo*
Bitte tragen Sie sofort und leserlich Namen, Studienkennzahl und Matrikelnummer ein und legen Sie Ihren Studentenausweis bereit.
PR ¨UFUNG AUS DATENBANKSYSTEME VO 181.146 13. 12. 2004
Kennnr. Matrikelnr. Familienname Vorname
Arbeitszeit: 120 Minuten. Aufgaben sind auf den Angabebl¨attern zu l¨osen; Zusatzbl¨atter werden nicht gewertet.
Achtung! Das ist keine Pr¨ ufung in voller L¨ange; hier soll nur auf die Beispiel- arten vorbereitet werden, die den gr¨oßten Neuheitswert haben!
Aufgabe 1: (25)
Gegeben ist eine Uni-Datenbank mit Relationen Professoren(PersNr, Name) (kurzprof), Studenten(MatrNr, Na- me, Sem) (kurzs), Vorlesungen(VorlNr, SWS, Titel) (kurzv), und Pruefen(PersNr, MatrNr, VorlNr, Note) (kurz prf). Nehmen Sie an, daß |prof|= 800, |s|= 38000, |v|= 2000, und|prf|= 50000. Nehmen Sie auch an, daß die durchschnittlichen Tupelgr¨oßen f¨ur prof, s, v, und prf 50, 50, 100, und 25 Bytes sind. Die Seitengr¨oße betr¨agt 1024 Bytes, und Hauptspeicher-Puffergr¨oße betr¨agt 20 Seiten. Es ist die Anfrage
select *
from Professoren prof, Studenten s, Vorlesungen v, Pruefen prf where prof.PersNr = prf.PersNr
and v.VorlNr = prf.VorlNr and s.MatrNr = prf.MatrNr and s.Sem = 1
and v.SWS = 2
auszuf¨uhren (dh. gesucht sind Pr¨ufungen von erstsemestrigen Studenten f¨ur Vorlesungen, die zwei Semesterwo- chenstunden “wert” sind, mit allen dazugeh¨origen Informationen ¨uber Professoren, Studenten und Vorlesungen).
Sch¨atzen Sie dazu die Selektivit¨aten der drei Join-Conditions sels/prf, selv/prf, und selprof /prf aufgrund der nat¨urlicherweise bestehenden 1:n Beziehungen ab.
• selprof /prf = 1/800 = 0.00125 . . . .
• selv/prf = 1/2000 = 0.0005 . . . .
• sels/prf = 1/38000≈2.63∗10−5 . . . .
Weiters sind die Selektivit¨aten sels.Sem= 0.1 und selv.SW S = 0.4 anzunehmen.
Auf der folgenden Seite ist nun ein Anfrageplan f¨ur die obenstehende Query angegeben. Berechnen Sie dazu f¨ur jeden Knoten im Operatorbaum die Anzahl der Tupel im Resultat, die Tupelgr¨osse, die Anzahl der Seiten im Resultat, und die gesch¨atzten Kosten. F¨ur Joinoperationen ist auch noch die passende Kostenformel anzugeben.
Tragen Sie Ihre Berechnungen in die Tabelle auf der n¨achsten Seite ein.
*Demo*–1
13: NL-./
11: [Zwischenspeichern]
10: NL-./
6:[Zwischenspeichern]
5: NL-./
3:[Zwischenspeichern]
2:σsem=1
1: Access(s)
4: prf
9:[Zwischenspeichern]
8: σsws=2
7: Access(v)
12: prof
Knoten# Anzahl Tupel- Anzahl Kostenformel Kosten
i Tupel gr¨oße Seitenbi (Page I/O)
1
38000 . . . . . 50 . . . . 1900 . . . . - . . . . 1900 . . . . 2
3800 . . . . 50 . . . . 190 . . . . (pipelined) . . . . 0 . . . . 3
3800 . . . . 50 . . . . 190 . . . . - . . . . 190 . . . . 4
50000 . . . . . 25 . . . . 1250 . . . . - . . . . - . . . . 5
5000 . . . . 75 . . . . 385 . . . . b3+ 1 +db3/19e ∗(b4−1) . . . . 12681 . . . . . 6
5000 . . . . 75 . . . . 385 . . . . - . . . . 385 . . . . 7
2000 . . . . 100 . . . . 200 . . . . - . . . . 200 . . . . 8
800 . . . . 100 . . . . 80 . . . . (pipelined) . . . . 0 . . . . 9
800 . . . . 100 . . . . 80 . . . . - . . . . 80 . . . . 10
2000 . . . . 175 . . . . 400 . . . . b9+ 1 +db9/19e ∗(b6−1) . . . . 2001 . . . . 11
2000 . . . . 175 . . . . 400 . . . . - . . . . 400 . . . . 12
800 . . . . 50 . . . . 40 . . . . - . . . . - . . . . 13
2000 . . . . 225 . . . . 500 . . . . b12+ 1 +db12/19e ∗(b11−1) . . . . 1238 . . . .
*Demo*–2
Aufgabe 2: (10) Beantworten Sie zum vorherigen Beispiel folgende Fragen:
1. Ist die angegebene Join-Ordnung optimal? × ja nein
2. K¨onnte man (zumindest) einen der “[Zwischenspeichern]”-Operatoren entfernen? ja × nein 3. Verbessert sich die Kostenabsch¨atzung, wenn alle (Nested Loop) Joins durch Hash-Joins ersetzt werden?
× ja nein
4. (Fortsetzung der letzten Frage:) Was sind die Gesamtkosten f¨ur die Anfrage, wenn alle Joins durch Hash- Joins ersetzt werden? (Page I/O)
1900 + 190 + 3∗(b3+b4) + 385 + 200 + 80 + 3∗(b6+b9) + 400 + 3∗(b11+b12) = 15590
Aufgabe 3: (25)
Gegeben ist die folgende Historie von Transaktionen: Zu Beginn ist der relevante Datenbestand in der Datenbank A= 40, B= 50, und C= 0.
Schritt T1 T2 Log
[LSN, TA, PageID, Redo, Undo, PrevLSN] or hLSN, TA, PageID, Redo, PrevLSN, UndoNextLSNi
1 BOT
[#1, T1, BOT, 0] . . . .
2 w(A,100)
[#2, T1, PA, A+=60, A-=60, #1] . . . .
3 BOT
[#3, T2, BOT, 0] . . . .
4 r(A, a2)
5 r(C, c1)
6 w(C, c1−10)
[#4, T1, PC, C-=10, C+=10, #2] . . . .
7 commit
[#5, T1, commit, #4] . . . .
8 r(C, c2)
9 w(B, a2+c2−10)
[#6, T2, PB, B+=30, B-=30, #3] . . . .
10 (commit)
11
h#7, T2, PB, B-=30, #6, #3i . . . . 12
h#8, T2, (BOT), #7, 0i . . . . 13
14
*Demo*–3
(a) Ist diese Historie serialisierbar? × ja nein (b) Wenn ja, in welcher Reihenfolge? T 1 . . . vor T 2 . . . . (c) Wenn nein, durch das Streichen wievieler Operationen wird die Transaktion serialisierbar? (Es ist das Minimum
anzugeben.) 0 Transaktionen.
(d) Wenn Sie Zeile 7 (T1-Commit) an das Ende der Historie (Zeile 11) verschieben, ist dann die Historie noch
r¨ucksetzbar? ja × nein
(e) Tragen Sie unter der Annahme, daß das Datenbanksystem nach Zeile 9 (vor Zeile 10) abst ¨urzt, das Recovery- Log zu dieser Historie in die rechte Spalte ein. Geben Sie in Zeilen 11 bis 14 die Log-Eintraege f ¨ur die Recovery ein.
Was sind die Werte vonA,B, undCnach der Recovery?A: 100 . . . ;B : 50 . . . ;C: -10 . . . .
Musterl¨osungen werden am 13.12.2004 auf die Vorlesungshomepage gestellt!
Gesamtpunkte: 60
*Demo*–4
