ehemaligen Klausur

Prof. Dott.-Ing. Roberto Zicari
Datenbanken und Informationssysteme
Institut für Informatik Fachbereich Informatik und Mathematik
Klausur Grundlagen der Datenbanksysteme I
zum Erwerb einer Studienleistung / eines Leistungsscheins
WS 2007/2008
08. Februar 2008
Name:
Vorname:
Matrikelnummer:
Hinweise:
1. Füllen Sie bitte sofort die oben verlangten Angaben aus. Auf allen übrigen Blättern tragen Sie
bitte Ihren Namen ein. Nicht in dieser Weise gekennzeichnete Blätter können von der Wertung
ausgeschlossen werden.
2. Lose Blätter sind nicht erlaubt. Lassen Sie daher diesen Stapel geheftet und bearbeiten Sie die
Aufgaben direkt auf den Blättern. Sollte der ausreichend eingeräumte Platz dennoch einmal nicht
genügen, so verweisen Sie bitte auf die anhängenden Zusatzblätter.
3. Außer gewöhnlichen Stiften — nicht in rot und auch kein Bleistift! — sind keine Hilfsmittel
zugelassen. Insbesondere wird von der Klausur ausgeschlossen, wer währenddessen elektronische
Geräte wie Organizer, Handys oder ähnliches benutzt.
4. Die Klausur besteht aus 8 Aufgaben, die nicht nach Schwierigkeitsgrad geordnet sind. Bitte verschaffen Sie sich zunächst einen überblick, bevor Sie die einzelnen Aufgaben bearbeiten.
In der Klausur können insgesamt 180 Punkte erreicht werden.
5. Die Dauer der Klausur beträgt 180 Minuten. Bitte bleiben Sie nach Ablauf dieser Zeit an Ihrem
Platz, bis alle Klausuren eingesammelt sind. Sollten Sie mehr als 15 Minuten früher fertig sein, so
geben Sie Ihre Arbeit bitte bei der Klausuraufsicht ab und verlassen leise den Saal.
Viel Erfolg!
Nachfolgende Tabelle bitte nicht ausfüllen.
Aufgabe
Punkte
1
2
3
4
5
6
7
8
Gesamt
Note
Klausur DB1 WS 2007/2008, Name:
Aufgabe 1: Multiple Choice
2
(10 Punkte)
Kreuzen Sie für jede der folgender Aussagen an, ob diese wahr oder falsch ist.
Bewertung: Für jedes richtige Kreuz gibt es einen halben Punkt, für jedes falsche Kreuz wird ein
halber Punkt abgezogen. Insgesamt gibt es nicht weniger als 0 Punkte.
1. Die einzelnen Sichten auf eine Datenbank werden auf der Konzeptuellen Ebene
beschrieben.
wahr
falsch
2. (r − r) ⊲⊳ (r ∪ r) ≡ ∅
wahr
falsch
3. Das Data Dictionary enthält die Metadaten der Datenbank.
wahr
falsch
4. SQL ist eine reine Data Manipulation Language (DML).
wahr
falsch
5. Der Schnitt zweier Relationen lässt sich durch die Differenz ausdrücken.
wahr
falsch
6. Bei einem zusammengesetzten Schlüssel ist jedes Attribut eindeutig.
wahr
falsch
7. Synonymen und Homonymen sind Homonymen.
wahr
falsch
8. Die Nebenläufigkeitskontrolle (concurrency control) regelt der TransaktionsManager.
wahr
falsch
9. Eine Relation kann als Tabelle visualisiert werden: Die Spalten repräsentieren die
Tupel.
wahr
falsch
10. SQL: Eine View ist keine Relation.
wahr
falsch
11. Der Anfrage Prozessor führt Optimierung von Anfragen durch.
wahr
falsch
12. Der Selektionsoperator ist kommutativ.
wahr
falsch
13. Der Verbundoperator ist kommutativ.
wahr
falsch
14. Ein Relationenschema R ist eine endliche Menge von Attributnamen.
wahr
falsch
15. r ⊲⊳ s entspricht r × s, falls R ∪ S = {}.
wahr
falsch
16. Klassifikation ist ein anderes Wort für Vererbung.
wahr
falsch
17. Das Ergebnis eines algebraischen Ausdrucks ist immer eine Relation.
wahr
falsch
18. SQL: Der union-Operator folgt nicht dem Ansatz der Vielfachmenge.
wahr
falsch
19. Die Division ist eine Grundoperation der Relationalen Algebra.
wahr
falsch
20. Wenn X ⊇ R und X → R, dann ist X ein Oberschlüssel für R.
wahr
falsch
Klausur DB1 WS 2007/2008, Name:
3
Lösung:
1. FALSCH Sichten werden auf der Sichten-Ebene (view-level) beschrieben.
2. RICHTIG (r − r) ⊲⊳ (r ∪ r) ≡ ∅
3. RICHTIG Die Schemainformationen nach der Übersetzung von DDL-Aussagen werden in einem
besonderen File, dem Data Dictionary gespeichert. Diese Daten über Daten¿ werden als Metadaten
bezeichnet.
4. FALSCH SQL enthält sowohl DDL als auch DML Ausdrücke.
5. RICHTIG Der Schnitt zweier Relationen lässt sich durch die Differenz ausdrücken, denn es gilt:
r ∩ s = r − (r − s)
6. FALSCH Ein Schlüssel identifiziert eine Entität. Er besteht aus einer Menge von Attributen, deren
Werte alle Instanzen einer Entität eindeutig bestimmen.
7. FALSCH Synonyme sind verschiedene Begriffe mit gleicher Bedeutung. Homonyme sind Begriffe mit
mehreren Bedeutungen.
8. RICHTIG Transaktions-Manager - Nebenläufigkeitskontrolle (concurrency control).
9. FALSCH Eine Relation kann als Tabelle visualisiert werden: Die Spalten repräsentieren die Attribute.
10. FALSCH Eine View ist eine Relation.
11. RICHTIG Anfrage Prozessor übersetzt die Ausdrücke der Anfragesprache in Low-Level Anweisungen, die der Datenbank Manager versteht; führt Optimierung von Anfragen durch.
12. RICHTIG Selektionsoperator ist kommutativ.
13. RICHTIG Der Verbundoperator ist kommutativ.
14. RICHTIG Ein Relationenschema R ist eine endliche Menge von Attributnamen {A1, A2 , ..., An}.
15. RICHTIG r ⊲⊳ s entspricht r × s, falls R ∪ S = {}.
16. FALSCH Die Klassifikation definiert ein Konzept für eine Klasse von Objekten der realen Welt, die
durch gemeinsame Eigenschaften gekennzeichnet sind.
17. RICHTIG Ergebnis eines algebraischen Ausdrucks ist immer eine Relation.
18. RICHTIG Der union-Operator folgt, im Gegensatz zu den meisten anderen SQL-Konstrukten, nicht
dem Ansatz der Vielfachmenge!
19. FALSCH Division ist eine Grundoperation der Relationalen Algebra.
20. RICHTIG Wenn X ⊇ R und X → R, dann ist X ein Oberschlüssel für R.
Klausur DB1 WS 2007/2008, Name:
Aufgabe 2: Konzeptuelles Modell
4
( Punkte)
Wegen den schwierigen Koalitionsverhandlungen nach der Wahl in Hessen, wurde nun beschlossen, die
Monarchie wieder einzuführen. Um den Vorgang besser organisieren zu können, wurden folgende Anforderungen aufgestellt:
Um die neue Ordnung einfach zu halten sollen die Menschen in Hessen nur noch in Adelige und Untertanen unterteilt werden. Einzelne Menschen sollen voneinander unterscheidbar sein, durch Namen,
Adresse und eine eindeutige ID. Die Adeligen herrschen über die Untertanen.
Um noch Reste der Demokratie zu erhalten, soll die ”‘Regierungszeit” der Adeligen auf vier Jahre begrenzt sein. Untertanen sollen außerdem die Möglichkeit gegeben sein, bei den Adeligen als Diener zu
arbeiten. Unter den Diener gibt es leitende und untergebene Diener. Jeder Diener hat einen bestimmten
Aufgabenbereich und wird entsprechen entlohnt. Jeder Adelige wohnt in mindestens einem Schloss in
Hessen, dass eine Postleitzahl, Telefonnummer und eine bestimmte Anzahl von Zimmer hat.
a) Erstellen Sie aus der obigen Anforderung ein vollständiges ER-Diagramm mit Hilfe der Bottom-up
Methode. Erläutern Sie dabei Ihre Vorgehensweise.
b) Welche Anforderungen lassen sich nicht mit einem ER-Diagramm modellieren. Nennen Sie eine
alternative Darstellungssmöglichkeit.
Klausur DB1 WS 2007/2008, Name:
5
Lösung:
a)
"
!
#
'
(
$%&
)(
+ !
b) Amtszeit auf vier Jahr begrenzt.
Lösung: Business Rule: Ein Adeliger darf nicht länger als vier Jahre regieren.
*&
)
Klausur DB1 WS 2007/2008, Name:
Aufgabe 3: Relationales Modell
6
(10 Punkte)
a) Ist die Abbildung eines ER-Diagramms auf ein relationales Schema immer eindeutig? Begründen
Sie Ihre Antwort kurz.
b) Wandeln Sie folgendes relationales Schema in ein ER-Diagramm um. Achten Sie dabei auf korrekte
Beziehungen und Kardinalitäten. Fremdschlüssel sind kursiv dargestellt, Schlüssel unterstrichen.
Manager(id, vorname, name, schauspieleranzahl),
Schauspieler(id, name, mangerid, gehalt, ortname),
Spielt in(schauspielerid , filmtitel , von, bis),
Ort(name, plz, landname),
Land(name, bevoelkerungsanzahl),
Film(titel, jahr, ortname, einnahmen)
Klausur DB1 WS 2007/2008, Name:
7
Lösung:
a) Die Abbildung eines ER-Diagramms auf ein relationales Schema ist nicht immer eindeutig (z.B. wg.
Generalisierung).
$
!
b)
"#
Klausur DB1 WS 2007/2008, Name:
Aufgabe 4: SQL und Relationale Algebra
8
(30 Punkte)
Folgendes Schema speichert zu Schauspielern ihre Manager, Filmverträge (Attribute: von, bis) und ihr
Heimatland. Zu Filmen wird das Jahr und der Ort der Erstaufführung gespeichert. Fremdschlüssel sind
kursiv dargestellt, Schlüssel unterstrichen.
Manager(id, vorname, name, schauspieleranzahl),
Schauspieler(id, name, mangerid, gehalt, ortname),
Spielt in(schauspielerid , filmtitel , von, bis),
Ort(name, plz, landname),
Land(name, bevoelkerungsanzahl),
Film(titel, jahr, ortname, einnahmen)
a) Fügen Sie den Film mit Namen ”I am Legend” aus dem Jahr 2008 mit Einnahmen von 500 Millionen
mittels SQL in die Datenbank ein.
b) Formulieren Sie für folgende Anfragen ein Äquivalent in SQL:
- Wieviele Schauspieler betreut der Manager ”Rolf Miller” ?
- Wie heißt der Schauspieler mit dem höchsten Gehalt?
- Welche Filme wurden nach dem Jahr 1990 in einem Land mit einer ”bevoelkerungsanzahl”
von mehr als 100 Millionen gedreht?
- Hat der Manager ”Rolf Miller” einen Schauspieler betreut, der im Film ”Die Simpsons” mitgespielt hat?
- Wie groß ist die Bevölkerung der beiden Länder Japan und Madagaskar zusammen?
- Welcher Film hat Einnahmen über 1 Millionen eingespielt? Geben Sie diese absteigend alphabetisch sortiert aus.
- Welches Gehalt haben Schauspieler, die im Film ”Titanic” mitspielten, im Durchschnitt?
c) Löschen Sie den Film ”Große Langeweile” mittels SQL aus der Datenbank. Kann es hierbei zu
Problemen kommen? Begründen Sie Ihre Antwort.
d) Beschreiben Sie in Worten das Ergebnis folgender Anfrage:
SELECT S . name
FROM S c h a u s p i e l e r S
WHERE S . i d IN (
SELECT ∗
FROM S p i e l t i n SIN
WHERE SIN . s c h a u s p i e l e r i d = S . i d
AND ( SIN . von > 3 1 . 0 1 . 2 0 0 6
OR SIN . b i s > 3 1 . 0 1 . 2 0 0 6 ) )
e) Formulieren Sie für folgende Anfragen ein Äquivalent in relationaler Algebra:
- Geben Sie die Schauspieler an, die in allen Filmen mitgespielt haben, die ihre Erstaufführung
(Attribut: ortname) in Frankfurt hatten. Verwenden Sie den Divisionsoperator ÷ !
- Geben Sie die Namen der Schauspieler an, die schon mindestens einmal einen Film-Vertrag
hatten, der mehr als 100 Millionen Einnahmen hatte.
Klausur DB1 WS 2007/2008, Name:
9
Lösung:
a) INSERT INTO FILM (titel, jahr, ortname, einnahmen)
VALUES (”I am Legend”, 2008, ”I am Legend”, 500000000)
b)
- SELECT schauspieleranzahl
FROM MANAGER
WHERE vorname = ”Rolf” AND name = ”Miller”
- SELECT name
FROM SCHAUSPIELER
WHERE gehalt = (SELECT max(gehalt) from SCHAUSPIELER)
oder
SELECT name, max(gehalt) as gmax
FROM SCHAUSPIELER
WHERE gehalt = gmax
- SELECT titel
FROM FILM F, ORT O, LAND L
WHERE F.jahr > 1990
AND L.bevoelkerungsanzahl > 50000000
AND O.landname = L.name
AND F.ortname = O.name
- SELECT SIN.schauspielerid
FROM MANAGER M , SPIELT IN SIN
WHERE M.vorname = ”Rolf” AND M.name = ”Miller”
AND SIN.filmtitel = ”Die Simpsons”
- SELECT SUM (bevoelkerungsanzahl)
FROM LAND
WHERE name = ”Madagaskar” OR name = ”Japan”
- SELECT titel
FROM FILM
WHERE einnahmen > 1000000 ORDER BY (titel) DES
- SELECT AVG(S.gehalt)
FROM SCHAUSPIELER S, SPIELT IN SIN
WHERE SIN.titel = ”Titanic”
AND SIN.schauspielerid = S.id
c) DELETE FILM
WHERE NAME=”Grosse Langeweile”
Problem: Es wird nur der Film in der DB Film geloescht. In SPIELT IN koennten aber noch
die Schauspieler fuer den Film gespeichert sein. Dann entstehen Anomalien; FS hat einen ungueltigen Wert.
Klausur DB1 WS 2007/2008, Name:
10
d) Welche Schauspieler hatte nach 2006 einen Film-Vertag?
oder: Welche Schauspieler hat in einem Film gespielt der nach 31.1.2006 begonnen hat oder nach
31.1.2006 immer noch laeuft.
e)
-
E = πSIN.schauspielerid (σSIN.f ilmtitel=F.titel ((SP IELT IN × F ILM ))
ALLE FEHLT:
Select SIN.schauspielerid
from SPIELT IN SIN, FILM F,
WHERE SIN.filmtitel = F.titel
AND SIN.id IN (
SELECT * FROM FILM
WHERE F.ortname=”Frankfurt”)
- E = πS.name (σSIN.schauspielerid=S.id ∧ SIN.f ilmtitel=F.titel ∧ F.einnahmen>100000000
(SCHAU SP IELER × SP IELT IN × F ILM )))
Select S.name
from SCHAUSPIELER S
WHERE S.id IN(
SELECT * FROM SPIELT IN SIN, FILM F
WHERE SIN.schauspielerid=S.id
AND SIN.filmtitel = F.titel
AND F.einnahmen > 100000000)
Klausur DB1 WS 2007/2008, Name:
Aufgabe 5: Logische Optimierung
11
( Punkte)
Gegeben seien die Relationen q(ACD), r(BCD) und s(BEF). Optimieren Sie unter Verwendung der Operatorbäume die folgenden Anfragen. Geben Sie bitte auch alle Ihre Zwischenschritte mit an.
E = πCD (σA>a∧B=b (πACD (σF >f ∧A>a (q ⊲⊳ (r ⊲⊳ s)))))
Klausur DB1 WS 2007/2008, Name:
Lösung:
12
Klausur DB1 WS 2007/2008, Name:
19
Klausur DB1 WS 2007/2008, Name:
20
Aufgabe 6: Normalformen
( Punkte)
Die Frankfurter Schneiderei ”‘Nadel & Faden” nutzt die unten stehende Datenbank, um ihre Produkte
zu verwalten. Leider stellte sich mit der Zeit heraus, dass das Datenmodell nicht gut durchdacht ist.
hersteller
Kleber
Kleinert
nummer
123
124
245
Fabrikant
adresse
plz
Hauptstrasse 12 60321
Nebenstrasse 12 20453
artikelnummer
{123, 124, 125}
{235, 123}
Artikel
name
farbe
Rock
{Blau, Rot, Grün}
Pullover
{Schwarz}
Jeans
{Blau, Schwarz}
a) Nennen und erläutern Sie zwei Anomalien, die im obigen Schema auftreten können.
b) Bringen Sie das Schema, wenn nötig, in die erste Normalform.
c) Bringen Sie das Schema, wenn nötig, in die zweite Normalform.
d) Bringen Sie das Schema, wenn nötig, in die dritte Normalform.
Klausur DB1 WS 2007/2008, Name:
21
Lösung:
ERSTE NORMALFORM:
hersteller
Kleber
Kleber
Kleber
Kleinert
Kleinert
nummer
123
123
123
124
245
245
Fabrikant
adresse
plz
Hauptstrasse 12 60321
Hauptstrasse 12 60321
Hauptstrasse 12 60321
Nebenstrasse 12 20453
Nebenstrasse 12 20453
Artikel
name
Rock
Rock
Rock
Pullover
Jeans
Jeans
farbe
Blau
Rot
Grün
Schwarz
Blau
Schwarz
artikelnummer
123
124
125
235
123
Klausur DB1 WS 2007/2008, Name:
ZWEITE NORMALFORM:
FDs: hersteller → adresse
hersteller → plz
hersteller → adresse, plz
adresse → plz
nummer → name
Schlüssel Fabrikant:
hersteller, adresse, plz, artikelnummer
hersteller, artikelnummer → adresse, plz
Schlüssel Artikel:
nummer, name, f arbe
nummer, f arbe → name Voll FD?:
Schlüssel Fabrikant: adresse und plz nur von hersteller abhängig
Schlüssel Artikel: name nur von nummer abhängig
Fabrikant
hersteller artikelnummer
Kleber
123
Kleber
124
Kleber
125
Kleinert
235
Kleinert
123
hersteller
Kleber
Kleinert
Adresse
adresse
Hauptstrasse 12
Nebenstrasse 12
Artikelfarbe
nummer
farbe
123
Blau
123
Rot
123
Grün
124
Schwarz
245
Blau
245
Schwarz
Artikelname
nummer
name
123
Rock
124
Pullover
245
Jeans
plz
60321
20453
22
Klausur DB1 WS 2007/2008, Name:
DRITTE NORMALFORM:
Keine transitiven Abhängigkeiten: Schema ist in dritter Normalform.
23
Klausur DB1 WS 2007/2008, Name:
24
Aufgabe 7: Transaktionen
( Punkte)
Gegeben sei folgender Schedule für die Transaktionen T1 , T2 und T3 .
T1
READ(X)
X:=10
T2
T3
READ(Y)
Y:=Y*3
READ(Z)
Z:=0+1
READ(Y)
READ(X)
X:=Y+10
WRITE(Y)
READ(X)
Z:=X+5
WRITE(X)
X:=Y+3
WRITE(X)
WRITE(Y)
WRITE(Z)
a) Geben Sie einen entsprechenden seriellen Schedule an.
b) Erweitern Sie den Schedule um richtige Lock/Unlock Operationen des zwei-Phasen-Sperrprotokolls
(2PL).
c) Gegeben sei folgende Baumstruktur der Objekte X,Y und Z. Ändern Sie Transaktion T3 so ab, dass
T3 das Baumprotokoll erfüllt.
Klausur DB1 WS 2007/2008, Name:
25
Lösung:
a) Sseriell : T1 , T2 , T3
b)
T1
R-LOCK(X)
READ(X)
X:=10
T2
T3
R-LOCK(Y)
READ(Y)
Y:=Y*3
R-LOCK(Z)
READ(Z)
Z:=0+1
R-LOCK(Y)
READ(Y)
R-LOCK(X)
READ(X)
X:=Y+10
W-LOCK(Y) (wait T1 )
R-LOCK(X)
Z:=X+5
W-LOCK(X)
DEADLOCK - abort T1
WRITE(Y)
X:=Y+3
W-LOCK(X) (wait T3 )
W-LOCK(Z)
WRITE(Z)
UNLOCK(X)
UNLOCK(Z)
WRITE(X)
UNLOCK(Y)
UNLOCK(X)
R-LOCK(X)
READ(X)
X:=10
R-LOCK(Y)
READ(Y)
X:=Y+10
W-LOCK(X)
WRITE(X)
W-LOCK(Y)
WRITE(Y)
Klausur DB1 WS 2007/2008, Name:
26
Aufgabe 8: Optimistic Concurrency Control
( Punkte)
a) Für die Produktdatenbank in einem Supermarkt mit 14 Kassen soll ein Verfahren für die Nebenläufigkeitskontrolle ausgewählt werden (2PL oder optimistisches Concurrency Control). Für welches
würden Sie sich entscheiden? Begründen Sie Ihre Antwort kurz.
b) Wandeln Sie folgenden Schedule S1 in einen Schedule um, der auf dem optimistischen Verfahren
beruht. Denken Sie dabei auch an die Angabe der Zeitstempel. Neu gestartete Transaktionen
werden an den existierenden Schedule angehängt. Sollten mehrere Transaktionen neu gestartet
werden müssen, wird jede Aktion im Wechsel durchgeführt.
Anmerkung: Weitere Blätter mit der vorgedruckten Tabelle können Sie sich geben lassen!
S1 : READ2 (y), READ1 (x), W RIT E1 (x), W RIT E2 (y), READ2 (y), W RIT E3 (x),
READ2 (x), W RIT E1 (x), READ3 (z), COM M IT1 , COM M IT3 , COM M IT2
T1
T2
T3
t(T1 )
t(T2 )
t(T3 )
tr (x)
tr (y)
tr (z)
tw (x)
tw (y)
tw (z)
Klausur DB1 WS 2007/2008, Name:
27
Lösung:
T1
T2
T3
t(T1 )
100
t(T2 )
110
t(T3 )
120
tr (x)
0
R(y)
R(x)
W(x)
tr (y)
0
110
tr (z)
0
tw (x)
0
100
100
W(y)
R(y)
R(x)
110
110
110
W(x)
140
150
abort T1 , T2
120
W(x)
R(z)
120
R(y)
R(x)
W(x)
150
140
140
W(y)
150
W(x)
140
R(y)
R(x)
tw (y)
0
150
140
tw (z)
0