Datenbanksysteme I - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
L. Rudenko, Dr. F. Wenzel
WS 2016/2017
16. Jan. 2017
Übungsblatt 11
Datenbanksysteme I
Aufgabe 1: Umsetzung von SQL in Relationale Algebra (Hausaufgabe)
Beschreiben Sie, was mit den folgenden Anfragen gemeint ist und setzen Sie die Anfragen in
Relationale Algebra um (kanonische Form).
a)
SELECT Name
FROM P r o f e s s o r e n , V o r l e s u n g e n
WHERE P e r s N r = g e l e s e n V o n AND T i t e l = ' W i s s e n s c h a f t s t h e o r i e ' ;
b)
SELECT DISTINCT p . Name
FROM S t u d e n t e n s , h ö r e n h , V o r l e s u n g e n v , P r o f e s s o r e n p
WHERE s . MatrNr = h . MatrNr AND h . V o r l N r = v . V o r l N r AND v . g e l e s e n V o n = p . P e r s N r
AND s . Name = ' C a r n a p ' ;
c)
SELECT Name
FROM S t u d e n t e n
WHERE S e m e s t e r >= (SELECT MAX( S e m e s t e r )
FROM S t u d e n t e n ) ;
d)
SELECT P e r s N r , Name
FROM P r o f e s s o r e n
WHERE P e r s N r = ANY (SELECT DISTINCT g e l e s e n V o n
FROM V o r l e s u n g e n ) ;
Aufgabe 2: Auswirkungen der Seitengröße in B-Bäumen(Präsenzaufgabe)
In dieser Aufgabe soll die Abhängigkeit der Parameter k und h eines B-Baumes von der festgelegten Seitengröße näher betrachtet werden. Im folgenden wird von n = 103 Sätzen mit jeweils 300
Bytes ausgegangen.
a) Berechnen Sie den Parameter k für eine feste Höhe h = 1 für maximale Seitengrößen von 2KB,
4KB, 8KB und 16 KB. Wie ändert sich dabei der Verzweigungsfaktor relativ zur Seitengröße?
b) Berechnen Sie die Höhe h eines entsprechenden B-Baumes mit festem k = 10 für Seitengrößen
von 2KB, 4KB, 8KB und 16KB.
c) In einem B-Baum der Klasse Γ(10, 1) sollen obige Sätze gespeichert werden. Wie groß müsste
die Seitengröße dementsprechend mindestens sein?
1
Aufgabe 3: Query-Optimierung (Hausaufgabe)
Gegeben sind die Relationen R, S und T:
R (A,B)
S (A,B)
T (C)
Betrachten Sie folgenden relationalen Ausdruck:
πA,B (σ(A<B)∧(B=C) ((R ∩ S) × T )
Beachten Sie dabei R ∩ S = R \ (R \ S)
a) Zeichnen Sie diesen algebraischen Ausdruck als unoptimierten kanonischen Operatorbaum.
b) Optimieren Sie diesen Operatorbaum anhand des Hill-Climbing-Algorithmus aus der Vorlesung. Geben Sie dabei die Zwischenstände nach Schritt 2, 3 und 5 an.
Aufgabe 4: Kostenbasierte Query-Optimierung (Präsenzaufgabe)
Gegeben seien folgende Relationen einer Vorlesungsdatenbank:
Vorlesung (VNummer, Titel)
Literatur (VNummer, Bestand, Bedarf, Preis)
Weiterhin sind folgende Metadaten in der Datenbank gespeichert:
• Anzahl Tupel: TV orlesung = 1200 und TLiteratur = 800
• Anzahl Blöcke: BLiteratur = 50 und BV orlesung = 75
• Vorhandene Indexe:
– Nicht-Cluster-Index auf Literatur(Bestand) mit IBestand = 20
– Nicht-Cluster-Index auf Literatur(Bedarf)
– Nicht-Cluster-Index auf Literatur(VNummer)
– Primär-(Cluster-)Index auf Vorlesung(VNummer)
a) Bestimmen Sie die Größen IV N ummer , GV N ummer und HV N ummer des Kostenmodells aus der
Vorlesung für einen passenden Zugriff auf das Attribut VNummer der Relation Vorlesung.
b) Ermitteln Sie mit Hilfe von Algorithmus Selektionsoptimierung aus der Vorlesung die kostengünstigste Ausführung der folgenden SQL-Query:
SELECT *
FROM Literatur
WHERE Bestand = 30 AND Bedarf <= 15;
2
c) Wie hoch ist die Ausführungszeit der besten bzw. schlechtesten Variante von Aufgabe b) bei
einer durchschnittlichen Blockzugriffszeit von 8ms ?
d) Betrachten Sie folgende Query:
SELECT * FROM Vorlesung, Literatur;
Bestimmen Sie die:
i) Ergebniskosten für das kartesische Produkt.
ii) Einlesekosten für den Nested-Loop-Algorithmus für das kartesische Produkt mit 60 verfügbaren
DB-Cache-Blöcken.
e) Wie groß sollte der DB-Cache bei den jeweiligen Varianten von Aufgabe d) mindestens sein
bei einer angenommenen Blockgröße von 4 KB?
f) Wie hoch wäre die jeweilige Ausführungszeit der Anfrage in Aufgabe d) bei einer Blockzugriffszeit von 8ms?
g) Betrachten Sie folgende Query:
SELECT *
FROM Vorlesung, Literatur
WHERE Literatur.VNummer = Vorlesung.VNummer;
Welche der folgenden drei Varianten der Join-Berechnungen ist für diese Anfrage am kostengünstigsten?
i) Nested-Loop-Join (M = 60, J = 800)
ii) Sortierungs-Join (M = 60; nach VNummer sortiert)
iii) Index-Join (J = 800). Bestimmen Sie die Image-Größe I des Attributes VNummer geeignet.
Aufgabe 5: Query-Optimierung (Präsenzaufgabe)
a) Gegeben seien die Relationen
R(A, B)
S(A, B)
T(C, D)
U(C, D)
Betrachten wir nun folgenden Term dargestellt in (unoptimierter) relationaler Algebra:
πA (σB=C ∧ C=′ P eter′ ((R ∪ S) × (T ∪ U )))
i) Zeichnen Sie diesen Ausdruck als initialen Operatorbaum.
ii) Optimieren Sie diesen Operatorbaum gemäß dem Hill-Climbing-Algorithmus aus der Vorlesung und geben Sie den Baum nach Schritt 2, 3 und 5 an.
b) Gegeben seien die Relationen R( p, f ) und S( p, f ). Bestimmen Sie die günstigste Join-Variante
für den Join R o
nR.p=S.f S. Als Verfahren stehen zur Auswahl:
3
i) 1-Index-Join
ii) 2-Index-Join
Es gelte:
• Es besteht eine referentielle Integrität von S.f auf R.p.
• Es besteht ein (Primär-)Cluster-Index auf R.p und S.p.
• Es besteht ein Nicht-Cluster-Index auf S.f .
• Die Image-Größe für S.f ist K = 10.
• R sowie S sind als kompakter Heap gespeichert.
Eine Auswertung der Statistiken der Relationen R und S hat folgende Daten ergeben :
TR = TS = 20, BR = BS = 5, J = 2
4