Übungsblatt 10 - auf Matthias

Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
10. Übungszettel
(wird nicht korrigiert)
Einführung in Datenbanksysteme
Datenbanken für die Bioinformatik
Heinz Schweppe, Jürgen Broß, Katharina Hahn
Hinweis:
Dieser Übungszettel wird nicht korrigiert.
Vorbereitung für die Klausur.
Aufgabe 1 (Entwurf)
Er dient lediglich als
20 Punkte
Betrachten Sie folgende Beschreibung einer Getränkemarktkette:
Die Getränkemarktkette besteht aus mehreren Filialen (mit eindeutiger ID, Name,
Adresse), in denen die Angestellten Abteilungen zugeordnet sind. Abteilungen
werden nach identifizierenden Namen unterschieden, wie z.B. Finanzen,
Personalangelegenheiten, Ladenpersonal. Jede Filiale hat mehrere Abteilungen und
jede Abteilung mindestens einen Angestellten. Ein Angestellter kann auch
gleichzeitig in mehreren Abteilungen arbeiten.
Für jeden Angestellten müssen eine spezielle ID, der Name, die Adresse (bestehend
aus Straße und Ort) und die Abteilung gespeichert werden. Für jede Filiale müssen
der Name, die Angestellten, die Managerin und die vertriebenen Artikel gespeichert
werden. Die Managerin ist auch eine Angestellte, es werden jedoch zusätzliche
Informationen über ihre Qualifizierung gespeichert. Für jeden Artikel sind die
Bezeichnung, der Lieferant und der Barcode zu speichern. Für jeden Lieferanten
sind der Name und die angebotenen Produkte zu speichern. Ein Artikel wird immer
nur von einem bestimmten Lieferant geliefert.
a) Entwerfen Sie ein ER-Diagramm (UML Notation) für den beschriebenen
Sachverhalt. Identifizieren Sie alle notwendigen Entitäten und Beziehungen.
Tragen Sie die Kardinalitäten in Min-Max Notation ein. Kennzeichnen Sie die
Schlüssel der Entitäten. Fügen Sie keine künstlichen Schlüssel ein.
Aufgabe 2 (Concurrency Control)
18 Punkte
a) Betrachten Sie folgende Historie:
r1(a), r1(b), r2(a), r2(b), w2(a), c2, w1(b), c1
(1) Bestimmen Sie alle Konfliktpaare und zeichnen Sie den Konfliktgraphen.
Ist diese Historie serialisierbar? Falls ja, geben Sie einen äquivalenten
serialisierbaren Ausführungsplan an.
(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.
Ausgabe 18.06.2009
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
(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.
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.
(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.
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)
Formulieren Sie die folgenden Anfragen in relationaler Algebra und in SQL:
a) Alle Projekte, die von Lieferant "Wegner & Co" mit grünen Teilen beliefert
werden.
b) Namen der Lieferanten, die kein Projekt in Berlin beliefern.
c) Anzahl und Gesamtwert aller gelieferter Teile von Projekt "Motor 34"
d) Die Namen aller Projekte mit der Anzahl der bisher geliefertern Teile, wenn
der Gesamtwert der gelieferten Teile > 10000 ist.
Aufgabe 4 (Funktionale Abhängigkeiten, Normalformtheorie) 14 Punkte
a) Gegeben sei folgende Relation R(A, B, C):
A
B C
test A 3
test B 4
test C 3
Geben Sie alle nicht-trivialen funktionalen Abhängigkeiten an, die sich
aus der Relation ergeben.
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.
Ausgabe 18.06.2009
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
(1) Ist die Relation in 2. und 3. Normalform? Begründen Sie kurz ihre Antwort.
(2) Wie sehen die Kandidatenschlüssel der Relation aus?
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.)
Aufgabe 5 (Data-Warehousing)
20 Punkte
Betrachten Sie folgende Beschreibung zur Auswertung der Prüfungsleistung von
Studierenden.
•
Zu jeder Person wird ein künstlicher Schlüssel (ID), das Geschlecht,
Geburtsjahr und der Studiengang gespeichert.
•
Zu jeder Prüfungsleistung wird die Person (ID), das Datum der Leistung, die
Note und die entsprechende Veranstaltung, für die die Prüfungsleistung
abgelegt wurde, gespeichert.
•
Zu jeder Vorlesung wird der Titel, ihr Bereich (Technische Informatik,
Praktische Informatik, Theoretische Informatik, …) und ob diese für
Studierende im Grund- oder Hauptstudium angeboten wird, gespeichert.
(Nehmen Sie der Einfachheit halber an, dass Veranstaltung entweder dem
Grund- oder dem Hauptstudium zugeordnet sind.)
•
Zusätzlich wird noch die Zeit, zu der die Prüfungsleistung abgelegt wurde,
abgespeichert. Dazu wird zu einer Zeit das Datum, Semester, Quartal und
das Jahr separat abgelegt.
a) Erstellen Sie für die beschriebene Datenbank ein Stern-Schema mit Hilfe von
SQL-Anweisungen (CREATE TABLE)
Folgende Fragestellunge sollen untersucht werden:
b) Vergleich der Durschnittsnote von Studentinnen im Bereich technische
Informatik mit der Durchschnittsnote aller Studenten jeweilig für die erfassten
Jahre.
c) Vergleich der Durchschnittsnote der einzelnen Vorlesung mit den
Durchschnittsnoten der Studenten bestimmter Studiengänge jeweilig für die
erfassten Semester.
d) Vergleich der Durchschnittsnoten im Haupt- und Grundstudium jeweilig für die
Geschlechter.
Geben Sie zu den Fragestellungen jeweils die SQL Statements mit Hilfe des CUBE
bzw. des ROLLUP Operators an.
Zusatzaufgabe
Diese Fragestellungen sind nicht in der vorgegebenen Zeit zu lösen, allerdings ist ihr
Inhalt relevant für die Klausur. Beantworten Sie jede Frage möglichst kurz, evtl.
anhand eines kleinen Beispiels.
•
Was leistet der GROUP BY CUBE-Operator? Z.B. Welche Ergebnisrelation
erhält man, wenn man GROUP BY CUBE (a1, a2, a3) angibt (unter der
Ausgabe 18.06.2009
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
•
•
•
•
•
•
•
•
•
•
•
Sommersemester 2009
Annahme, dass die Aggregatfunktion AVG(value) AS Wert im SELECTStatement verwendet wurde)? Geben Sie die Spalten der Relation an und
deuten Sie durch * als Eintrag an, welche Gruppierungen in der Ausgabe zu
erwarten sind.
Warum gibt es beim Vorabanfordern von Sperren (Preclaiming) keine
Deadlocks?
Was versteht man unter einer Löschanomalie?
Warum muss ein Primärschlüssel immer NOT NULL deklariert werden?
Welche Werte kann ein Attribut a1 einnehmen, das als Fremdschlüssel auf
ein Attribut a2 einer anderen Tabelle deklariert wird?
Welchen Vorteil hat striktes 2PL gegenüber der nicht-strikten Variante?
Welche Auswirkungen hat ein Index auf ein DBMS? Erläutern Sie die
Auswirkungen bei SELECT, INSERT, DELETE und UPDATE.
Was bedeutet Serialisierbarkeit?
Wann ist ein Ausführungsplan
serialisierbar?
Welche Operatoren gibt es in der relationalen Algebra?
Wie kann man eine n:m Beziehungen zwischen 2 Entitäten im ER-Modell im
relationalen Modell ausdrücken?
Erläutern Sie die Begriffe Precision und Recall, die zur Evaluation im
Information Retrieval verwendet werden.
Was besagt die Thomas-Write-Rule?
Ausgabe 18.06.2009