Datenbanksysteme I - Institut für Informatik Augsburg

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, M. Endres, T. Preisinger
WS 2008/2009
23. Jan. 2009
Übungsblatt 13
Datenbanksysteme I
Hinweis: Dieses Übungsblatt besteht aus zwei Teilen. Auf den ersten beiden Seiten finden Sie ausgewählte ehemalige
Klausuraufgaben, auf der dritten Seite Aufgaben zur Normalformtheorie. Die Aufgaben zur NF setzen Kapitel
10 des Vorlesungsskriptes voraus.
Wir wünschen für die Klausur viel Erfolg!
Aufgabe 1: SQL, Klausur WS 07/08
Gegeben ist das Schema einer Universitäts-Datenbank. Auf die Angabe der Domänen wurde verzichtet. Um die Lesbarkeit zu erleichtern, wird bei Personenbezeichnungen die männliche Form verwendet.
Professoren
Assistenten
Studenten
Vorlesungen
voraussetzen
hören
prüfen
(PersNr, Name, Rang, Raum, GebDatum)
(PersNr, Name, Fachgebiet, Boss)
(MatrNr, Name, Semester, GebDatum)
(VorlNr, Titel, SWS, gelesenVon)
(Vorgänger, Nachfolger)
(MatrNr, VorlNr)
(MatrNr, VorlNr, PersNr, Note)
• Unterstrichene Attribute sind Primärschlüssel, kursiv geschriebene Attribute sind Fremdschlüssel.
• gelesenVon referenziert PersNr von Professoren.
• Vorgänger sowie Nachfolger referenzieren VorlNr in Vorlesungen.
Formulieren Sie die folgenden Anfragen in SQL.
a) Listen Sie mit einer SQL-Anfrage die Namen aller Assistenten und Professoren auf.
b) Welche direkten Vorgänger-Vorlesungen (VorlNr) muss man hören, um die Vorlesung Der Wiener Kreis zu verstehen?
c) Bestimmen Sie alle Prüfungen (MatrNr, Note), deren Note besser als durchschnittlich ist.
d) Finden Sie alle Professoren, die keine Vorlesungen halten.
e) Finden Sie alle Studenten, die älter als der jüngste Professor sind.
f) Suchen Sie die Studenten, die mindestens eine Vorlesung über Themen mit Ethik gehört haben. Dabei sollen Vorlesungen berücksichtigt werden, die das Wort Ethik bzw. ethik enthalten.
Aufgabe 2: Queryoptimierung, Klausur WS 06/07
Gegeben sei folgendes DB-Schema:
Buch (ISBN, Titel, Jahr, Seiten, Preis)
Autor (AutorID, Nachname, Vorname, Adresse)
Veroeffentlichung (ISBN, AutorID)
a) Geben Sie für folgende Anfrage eine einfache SQL-Query an:
Bestimmen Sie die Autoren (Nachname und Vorname), die ein Buch mit dem Titel ’DB’ veröffentlicht haben.
b) Transformieren Sie Ihre SQL-Anfrage in unoptimierte Relationale Algebra. Verwenden Sie kartesische Produkte
anstelle von Join-Operatoren.
1
c) Geben Sie für den Ausdruck aus Teilaufgabe b) den initialen Operatorbaum an.
d) Führen Sie die Optimierung der Anfrage mit Hilfe des Hill-Climbing Algorithmus aus. Sie müssen keine Zwischenschritte angeben.
Aufgabe 3: Serialisierbarkeit, Klausur WS 07/08
Gegeben seien die Datenbankobjekte A und B, die Transaktionen T1, T2, T3, T4 und ein Schedule S. Dabei sind
RLOCKs Lesesperren und XLOCKs exklusive Sperren.
S
=
(
XLOCK(A, T3),
UNLOCK(A, T3),
RLOCK(A, T1),
XLOCK(B,T3),
RLOCK(A, T2),
UNLOCK(B, T3),
XLOCK(B, T1),
UNLOCK(A,T2),
UNLOCK(A,T1),
XLOCK(A,T4),
UNLOCK(B,T1),
RLOCK(B,T2),
UNLOCK(A,T4),
UNLOCK(B,T2)
)
a) Handelt es sich bei S um einen legalen Schedule? Begründung.
b) Erfüllt obiger Schedule S die 2-Phasen-Eigenschaft?
c) Bestimmen Sie den Serialisierungsgraph zu S nach der Vorlesung. Ist S serialisierbar?
Aufgabe 4: Relationale Algebra und Tupelkalkül, Klausur WS 05/06
Gegeben sei folgendes relationales Datenbankschema:
Konzert (Titel, Konzerthaus, Datum, Dirigent)
Konzertstück (Titel, Komponist)
reserviert (Konzerthaus, Datum, Besuchername, Preis)
spielt (Konzerthaus, Datum, Titel)
Formulieren Sie folgende Anfragen in der jeweils angegebenen Anfragespache:
a) Tupelkalkül:
Ermitteln Sie Titel und Dirigenten von Konzerten, die im Konzerthaus ”Prinzregententheater” aufgeführt werden.
b) Relationale Algebra:
Ermitteln Sie Titel und Dirigent der Konzerte, bei denen Stücke des Komponisten ”Mozart” aufgeführt wurden.
c) Relationale Algebra:
Ermitteln Sie die Komponisten, deren Konzertstücke der Konzertbesucher ”Hans Meier” in den von ihm reservierten Konzerten gehört hat.
2
Aufgabe 5: Normalformtheorie
Betrachten Sie folgende Relation:
Student (MatrikelNr, Name, Vorname)
Es sollen folgende funktionalen Abhängigkeiten gelten:
Vorname → Name
Vorname → MatrikelNr
a) Nennen Sie alle möglichen Schlüsselkandidaten.
b) Wieviele Studenten mit dem Vornamen Alfons können aufgenommen werden?
c) Können zwei Studenten mit dem Namen Huhn vorhanden sein?
d) Kann anhand einer Matrikelnummer ein Student eindeutig bestimmt werden?
e) Nennen Sie funktionale Abhängigkeiten für Relation, die tatsächlich der Realität entsprechen, und beantworten Sie
dann die Fragen a) bis d) nochmals.
Aufgabe 6: Normalformtheorie: Verlustfreie Zerlegung
Gegeben ist die Relation R (L, T, A, O, K) mit folgenden funktionalen Abhängigkeiten:
• L, T → A
• L→O
• O→K
Betrachten Sie die Zerlegung von R in R1 (L, T, A), R2 (L, O) und R3 (O, K). Handelt es sich hierbei um eine
verlustfreie Zerlegung? Überprüfen Sie dies mit Hilfe von Algorithmus 383 aus dem DB-Skript.
Aufgabe 7: Normalformtheorie: Überführung in 3. NF und BCNF
Betrachten Sie folgendes Relationenschema:
ProfessorenAllerlei
(PersNr, Name, Rang, Raum, VorlNr, VorlTag,
Hörsaal, AssiPersNr, AssiName, DiplomandenMatrNr)
a) Bestimmen Sie die FDs.
b) Bestimmen Sie den/die Schlüsselkandidaten.
c) Bringen Sie obige Relation in die 3. NF.
d) Erfüllt das gerade erhaltene 3NF-Schema auch die BCNF? Wenn nein, überführen Sie das Schema in die BCNF.
Aufgabe 8: Normalformtheorie: Zerlegung in BCNF
Überführen Sie die Relation
Rechnungen
(RNr, ArtikelNr, Datum, Gesamtpreis, Menge, KundenNr,
KundenName, Kundenadresse, Artikelbezeichnung, EKPreis, VKPreis)
in die Boyce-Codd-Normalform.
Beachten Sie: Eine Rechnung kann mehrere Artikel beinhalten.
3