Name: Musterlösung Seite 2 Aufgabe 1 (10 Punkte) Beantworten

Name: Musterlösung
Seite 2
Aufgabe 1 (10 Punkte)
Beantworten Sie die Fragen in dieser Aufgabe mit einer kurzen, prägnanten Antwort.
1. Wie nennt man ein Attribut, das in keinem Schlüsselkandidaten enthalten ist?
Nichtprimärattribut
2. Mit welchen zwei Schlüsselwörtern wird in SQL eine Funktion bezeichnet, die einen
Rückgabewert liefert?
stored function
3. Wie heißt eine Historie, wenn alle Schritte einer Transaktion nacheinander ablaufen?
seriell
4. Mit welchem SQL-Befehl können Sie die Tabelle tablename komplett leeren?
delete from tablename
5. Mit welcher Klausel erreicht man bei der Definition eines Fremdschlüssels in SQL,
dass Änderungen des Primärschlüssels auf den Fremdschlüssel propagiert werden?
on update cascade
6. Nennen Sie die drei Phasen des Wiederanlaufs nach einem Fehler mit Verlust des
Hauptspeichers.
1. Analyse, 2. Redo, 3. Undo
7. Nennen Sie eine Form der physikalischen Datenorganisation, bei der die Operation
LOOKUP besonders effizient ist.
Hashing, ISAM oder B*Baum
17. Juli 2007
Name: Musterlösung
Seite 3
8. Mit welchem Schlüsselwort definiert man in SQL einen Sicherungspunkt innerhalb
einer Transaktion?
(define) savepoint
9. Nennen Sie einen typischen Fehler, der bei unkontrolliertem Mehrbenutzerbetrieb
auftreten kann.
Lost Update, Dirty Read oder Phantomproblem
10. Wann sind zwei Mengen von funktionalen Abhängigkeiten äquivalent?
Wenn sie die gleich Hülle besitzen
17. Juli 2007
Name: Musterlösung
Seite 4
Aufgabe 2 (8 Punkte)
Die folgende Abbildung zeigt eine Menge Datensätze, die mithilfe der Index-Sequential
Access Method (ISAM) verwaltet wird. Dabei können in einem Index-Block bis zu vier
Schlüssel/Adresspaare verwaltet werden. Ein Datenblock kann zwei Datensätze enthalten,
wobei aus Gründen der Übersichtlichkeit nur der Schlüssel angegeben ist.
Indexeinstiegstabelle
1110
1
1
Bert
Finchen
Bert
Grobi
Ernie
1
0
Finchen
1
0
Grobi
a) Fügen Sie die vier Schlüssel Oscar, Samson, Tiffy, Kermit in die oben angegebene
Index-Struktur ein und zeichnen Sie anschließend ihren Aufbau: (4 Punkte)
Indexeinstiegstabelle
1111
1
1
Bert
Grobi
Finchen
Bert
Ernie
1
0
Finchen
1
1
Kermit
Grobi
1
1000
1
1
Oscar
0
Oscar
Samson
Samson
Tiffy
17. Juli 2007
Name: Musterlösung
Seite 5
b) Löschen Sie nun aus der Index-Struktur aus a) den Schlüssel Finchen und fügen
Sie anschließend den Schlüssel Bibo ein. Zeichnen Sie danach den Aufbau erneut:
(4 Punkte)
Indexeinstiegstabelle
1111
1
1
Bert
Grobi
Ernie
Bert
Bibo
1
0
Ernie
1
1
Kermit
Grobi
1
1000
1
1
Oscar
0
Oscar
Samson
Samson
Tiffy
17. Juli 2007
Name: Musterlösung
Seite 6
Aufgabe 3 (10 Punkte)
Für die Modellierung eines Zugauskunftsystems seien folgende Entity-Typen gegeben:
Entity-Typ
Bahnhöfe
Städte
Züge
Passagiere
Attribute
Name, AnzGleise
Name, PLZ, Bundesland
ZugNr, Länge, AnzWaggons
Kundennr, Name
Zwischen den Entity-Typen seien folgende Beziehungen gegeben:
1. Jeder Bahnhof liegt in einer Stadt.
2. Eine Stadt kann bis zu drei Bahnhöfe besitzen.
3. Jeder Zug hat einen ausgewiesenen Start- und Zielbahnhof mit Abfahrt- und Ankunftzeit, d.h. er fährt auf einer festen Route.
4. Mit jeder Zwischenstation zerfällt die Route aller Züge in einzelne Teilstrecken. Die
Teilstrecken werden durch einen Relationship-Typen dargestellt, der jedem Zug für
jedes Teilstück den Start- und Zielbahnhof mit Ankunft- und Abfahrtzeit zuordnet.
5. Passagiere können in Zügen Plätze reservieren. Eine Reservierung wird mit der Klasse
und der Platznr. gekennzeichnet.
Erstellen Sie aus den obigen Angaben ein ER-Diagramm und geben Sie für die Beziehungen zwischen den Entity-Typen den Komplexitätsgrad in der (min, max)-Notation
an. Markieren Sie die Schlüssel. Falls ein Entity-Typ an einer Relation mehr als einmal
beteiligt ist, machen Sie kenntlich, welcher Entity-Typ welche Rolle spielt:
Gleisanzahl
Name
Bahnhoefe
(0,*)
starten
bei
PLZ
(1,1)
liegen in
(0,3)
(0,*)
(0,*) (0,*)
von
nach
Staedte
Bundesland
Name
enden
bei
verbinden
(1,1)
Ankunftszeit
(1,1)
ZugNr
AnzWaggons
(1,*)
Zuege
Laenge
Abfahrtszeit
(0,*)
reservieren
Klasse
PlatzNr
(0,*)
Passagiere
Name
KundenNr
17. Juli 2007
Name: Musterlösung
Seite 7
Aufgabe 4 (12 Punkte)
Gegeben sei das folgende ER-Modell zur Modellierung eines Ausschnitts aus einer Bank:
Kontonr.
Guthaben
Konto
Name
*
besitzt
1
Kundennr.
Kunde
Dispokredit
GebDatum
1
1
gebucht_auf
Auszug_von
*
Datum
Buchung
*
*
gelistet_auf
*
Kontoauszug
Betrag
Startdatum
Enddatum
Saldo
Buchungsnr.
Erstellungsdatum
Ein Kunde wird durch seine Kundennr. eindeutig identifiziert. Ein Kunde kann mehrere
Konten besitzen, ein Konto hat genau einen Inhaber. Einem Konto werden viele Buchungen zugeordnet. Zu einem Konto können viele Kontoauszüge gehören, allerdings bezieht
sich ein Kontoauszug immer nur auf ein Konto. Kontoauszüge enthalten viele Buchungen,
die ihrerseits auf vielen Kontoauszügen stehen dürfen. Ein Kontoauszug wird durch sein
Erstellungsdatum eindeutig identifiziert.
a) Markieren Sie oben im ER-Diagramm die Schlüssel der Entity-Typen. Markieren
Sie außerdem die schwachen Entity-Typen. Ergänzen Sie die Charakterisierung der
Beziehungstypen hinsichtlich ihrer Funktionalität. (5 Punkte)
b) Überführen Sie das ER-Diagramm in ein relationales Schema. Wie lauten die Relationen (ohne Wertebereich)? Markieren Sie die Schlüssel der Relationen. (4 Punkte)
Kunde:
Konto:
Buchung:
Kontoauszug:
besitzt:
gebucht auf:
gelistet auf:
Auszug von:
{[Kundennr., GebDatum, Name]}
{[Kontonr., Guthaben, Dispokredit]}
{[Buchungsnr., Datum, Betrag]}
{[Erstellungsdatum, Startdatum, Enddatum, Saldo]}
{[Kontonr., Kundennr.]}
{[Buchungsnr., Kontonr.]}
{[Buchungsnr., Erstellungsdatum]}
{[Erstellungsdatum, Kontonr.]}
c) Verfeinern Sie das relationale Schema, soweit wie möglich. Wie lauten die veränderten
Relationen? (3 Punkte)
Konto:
Buchung:
Kontoauszug:
gelistet auf:
{[Kontonr., Guthaben, Dispokredit, Kundennr.]}
{[Buchungsnr., Datum, Betrag, Kontonr.]}
{[Erstellungsdatum, Startdatum, Enddatum, Saldo, Kontonr.]}
{[Buchungsnr., Erstellungsdatum, Kontonr.]}
17. Juli 2007
Name: Musterlösung
Seite 8
Aufgabe 5 (8 Punkte)
Gegeben seien die folgenden Relationen einer Datenbank für Segel-Regatten:
Bootsklasse:
Wettfahrt:
Teilnehmer:
Plazierung:
{[Klasse, Bauart]}
{[FahrtNr, Name, Datum, Zeit]}
{[SegelNr, Bootsname, Bootsklasse, Baujahr, Eigentümer]}
{[SegelNr, FahrtNr, Platz]}
Formulieren Sie die folgenden Anfragen in der Relationenalgebra:
a) Wie lauten Bauart und Baujahr des Bootes mit der SegelNr ’GER4711’ ? (2 Punkte)
n Bootsklasse)
ΠBauart,Baujahr (σSegelN r=0 GER47110 (ρKlasse←Bootsklasse(T eilnehmer)) o
b) Welche Teilnehmer haben noch keine Wettfahrt gewinnen können? (3 Punkte)
T eilnehmer − Πsch(T eilnehmer) (σP latz=1 (P latzierung) o
n T eilnehmer)
c) Welche Boote (SegelNr) nahmen an allen Wettfahrten teil? (3 Punkte)
ΠSegelN r (P latzierung ÷ ΠF ahrtN r (W ettf ahrt))
17. Juli 2007
Name: Musterlösung
Seite 9
Aufgabe 6 (11 Punkte)
Gegeben seien die folgenden Relationen aus Aufgabe 4:
Bootsklasse:
Wettfahrt:
Teilnehmer:
Plazierung:
{[Klasse, Bauart]}
{[FahrtNr, Name, Datum, Zeit]}
{[SegelNr, Bootsname, Bootsklasse, Baujahr, Eigentümer]}
{[SegelNr, FahrtNr, Platz]}
Formulieren Sie folgende Anfragen in SQL:
a) Welches Boot (Bootsname) belegte bei der Wettfahrt ’Dümmer-Regatta’ den 10.
Platz? (2 Punkte)
select
from
where
and
and
and
t.Bootsname
Wettfahrt w, Platzierung p, Teilnehmer t
t.SegelNr = p.SegelNr
w.FahrtNr = p.FahrtNr
w.Name = ’Duemmer-Regatta’
p.Platz = 10
b) Wieviele Boote haben an der Wettfahrt ’Dümmer-Regatta’ teilgenommen?
(2 Punkte)
select
from
where
and
count(*)
Wettfahrt w, Platzierung p
w.FahrtNr = p.FahrtNr
w.Name = ’Duemmer-Regatta’
c) An welchen Rennen (Name) nahm das älteste Boot im Starterfeld teil? (3 Punkte)
select
from
where
and
and
w.Name
Wettfahrt
t.SegelNr
w.FahrtNr
t.Baujahr
w, Platzierung p, Teilnehmer t
= p.SegelNr
= p.FahrtNr
= (select min(Baujahr) from Teilnehmer)
17. Juli 2007
Name: Musterlösung
Seite 10
d) An welchen Wettfahrten haben ausschließlich Holzboote (Bauart = ’Holz’) teilgenommen? (4 Punkte)
Hinweis: Bestimmen Sie zunächst alle Wettfahrten, an denen Boote teilgenommen
haben, die nicht aus Holz sind.
select wf.Name
from Wettfahrt wf
where not exists
(select *
from Bootsklasse b, Wettfahrt w, Platzierung p, Teilnehmer t
where w.FahrtNr = p.FahrtNr
and p.SegelNr = t.SegelNr
and t.Bootsklasse = b.Klasse
and b.Bauart != ’Holz’i
and wf.name = w.name)
-- alternativ
select Name
from Wettfahrt
where FahrtNr not in
(select w.FahrtNr
from Bootsklasse b, Wettfahrt w, Platzierung p, Teilnehmer t
where w.FahrtNr = p.FahrtNr
and p.SegelNr = t.SegelNr
and t.Bootsklasse = b.Klasse
and b.Bauart != ’Holz’)
17. Juli 2007
Name: Musterlösung
Seite 11
Aufgabe 7 (5 Punkte)
Gegeben seien die folgenden Relationen einer Universitätsdatenbank:
Studierende:
Dozenten:
Vorlesungen:
lesen:
hoeren:
{[MatrikelNr, Name, Semester]}
{[PersNr, Name]}
{[VorlNr, Titel]}
{[PersNr, VorlNr]}
{[MatrikelNr, VorlNr]}
Wie lauten folgende SQL-Anfragen umgangssprachlich?
a) SELECT
FROM
WHERE
AND
AND
AND
DISTINCT s.Name
Studierende s, hoeren h, lesen l, Dozenten d
d.Name = ’Koester’
d.PersNr = l.PersNr
l.VorlNr = h.VorlNr
h.MatrikelNr = s.MatrikelNr
Umgangssprachlich: (2 Punkte)
Wie heißen die Hörer der Vorlesungen von Dozent ’Koester’ ?
b) SELECT Titel
FROM Vorlesungen
WHERE VorlNr NOT IN
(SELECT v.*
FROM Vorlesungen v, hoeren h, Studierende s
WHERE v.VorlNr = h.VorlNr
AND h.MatrikelNr = s.MatrikelNr
AND s.Name = ’Schulze’)
Umgangssprachlich: (3 Punkte)
Welche Vorlesungen werden nicht von Student ’Schulze’ gehört?
17. Juli 2007
Name: Musterlösung
Seite 12
Aufgabe 8 (8 Punkte)
Zeichnen Sie den folgenden B*-Baum (k=2) jeweils
a) nach dem Einfügen von 3, 23 und 41 (3 Punkte)
b) nach dem Einfügen von 19 (2 Punkte)
c) nach dem Löschen von 37 und 43 (3 Punkte)
einmal neu.
2
2
7
5
7
29
11
13
29 31 37 43
a) Nach dem Einfügen von 3, 23 und 41:
2
3
5
7
2
7
11 13
23
29
37
29
31
37
41
43
b) Nach dem Einfügen von 19:
2
2
2
3
5
7
7
29
13
11
29
13 19 23
37
29
31
37
41
37
41
43
c) Nach dem Löschen von 31 und 43:
2
2
2
3
5
7
7
13
13
11
13 19 23
29
29
17. Juli 2007
Name: Musterlösung
Seite 13
Aufgabe 9 (5 Punkte)
Gegeben sei folgende fehlerhafte XML-Datei klausur.xml mit Zeilennummern:
1 <?xml version="1.0"/>
2 <objekte xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="klausur.xsd">
3
<objektA>42</objekt>
4
<objektB>4711</objektB>
5
<objektC>
6
<objektD>sechs</objektD>
7
<objektE>sieben</objektE>
8
</objektC>
9
<objektE>
10 </objekte>
und folgende XML-Schema Definition (XSD) klausur.xsd:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="objekte">
<xs:complexType>
<xs:sequence>
<xs:element name="objektA" type="xs:integer"/>
<xs:element name="objektB" type="xs:string"/>
<xs:element name="objektC">
<xs:complexType>
<xs:sequence>
<xs:element name="objektD" type="objekttyp" maxOccurs="2"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="objektE" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:simpleType name="objekttyp">
<xs:restriction base="xs:string">
<xs:pattern value="eins|drei|fuenf|sieben|neun"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
Aufgabenstellung siehe nächste Seite
17. Juli 2007
Name: Musterlösung
Seite 14
Die XML-Datei klausur.xml enthält semantische und syntaktische Fehler. Finden Sie die
Fehler und korrigieren Sie die XML-Datei, so dass sie syntaktisch korrekt ist und bzgl. der
Schema-Definition erfolgreich validiert werden kann. Dabei dürfen einzelne Bestandteile,
aber keine ganzen Zeilen gelöscht werden.
Hinweis: Zeile 2 ist korrekt.
Notieren Sie die entsprechende Zeilennummer und die korrigierte Zeile:
Zeilennr.
korrigierte Zeile
1
<?xml version="1.0" ?>
3
<objektA>42</objektA>
6
<objektD>drei</objektD>
7
<objektD>sieben</objektD>
9
<objektE/>
17. Juli 2007
Name: Musterlösung
Seite 15
Aufgabe 10 (7 Punkte)
Gegeben sei das Relationenschema
R = {A, B, C, D, E, F }
mit den funktionalen Abhängigkeiten
B
D
AF
AC
→
→
→
→
D
ABF
C
BD
a) Nennen Sie alle Schlüsselkandidaten für R.
Die Schlüsselkandidaten lauten: BE, DE, AEF und ACE
Von allen ’linken Seiten’ sind alle Attribute der Relationb bis auf E funktional
abhängig. Da E in keiner funktionalen Abhängigkeit enthalten ist, muss es in jedem
Schlüsselkandidaten vorkommen.
b) R ist in der 1. Normalform. Ist R auch in der 2. Normalform? Begründen Sie Ihre
Antwort.
Da es keine Nichtprimärattribute gibt, ist die Relation in 2. Normalform.
c) Ist R in der 3. Normalform? Begründen Sie wieder Ihre Antwort.
Da es keine Nichtprimärattribute gibt, ist die Relation auch in 3. Normalform.
17. Juli 2007
Name: Musterlösung
Seite 16
Aufgabe 11 (4 Punkte)
Die wesentlichen Eingenschaften von Transaktionen werden mit dem Akronym ’ACID’
zusammengefasst. Nennen Sie diese Eigenschaften und charakterisieren Sie diese kurz.
Eigenschaft
atomicy
consistency
isolation
durability
Erklärung
Eine Transaktion stellt eine nicht weiter zerlegbare Einheit dar mit
dem Prinzip alles-oder-nichts.
Nach Abschluss einer Transaktion liegt wieder ein konsistenter Zustand in der Datenbank vor.
Nebenläufig ausgeführte Transaktionen dürfen sich nicht beeinflussen.
Änderungen durch eine erfolgreich abgeschlossene Transaktion bleiben
dauerhaft in der Datenbank.
17. Juli 2007
Name: Musterlösung
Seite 17
Aufgabe 12 (6 Punkte)
Betrachten Sie die drei Schedules mit den jeweiligen Transaktionen T1 , T2 , T3 :
Schedule 1
T1 : BOT
T2 : BOT
T3 : BOT
T1 : lockX(c)
T2 : lockX(b)
T2 : lockX(c)
T3 : lockX(a)
T3 : lockX(b)
T1 : lockX(a)
...
Schedule 2
T1 : BOT
T2 : BOT
T3 : BOT
T1 : lockX(b)
T2 : lockX(a)
T3 : lockX(b)
T2 : lockX(c)
T1 : lockX(c)
T2 : unlockX(a)
...
Schedule 3
T1 : BOT
T2 : BOT
T3 : BOT
T1 : lockX(a)
T2 : lockX(a)
T3 : lockX(b)
T2 : lockX(b)
T1 : unlockX(a)
T3 : lockX(a)
...
Zeichnen Sie den Wartegraphen zur Situation am Ende eines jeden Schedules. Liegt zu
diesem Zeitpunkt ein Deadlock vor?
Schedule 1: Wartegraph:
T1
T2
T3
Deadlock?
N
Ja
Nein
Schedule 2: Wartegraph:
T1
T2
T3
Deadlock?
Ja
Schedule 3: Wartegraph:
N
Nein
T1
T2
T3
Deadlock?
N
Ja
Nein
17. Juli 2007
Name: Musterlösung
Seite 18
Aufgabe 13 (6 Punkte)
In der u.a. Tabelle befindet sich in der Spalte ’Operation’ eine Historie mit drei verzahnt
ausgeführten Transaktionen, die jeweils lesend oder schreibend auf die drei Ressourcen a,
b und c zugreifen.
Diese Historie soll mit einem Zeitstempel-basierten Scheduler verarbeitet werden. Stellen
Sie die Verarbeitung dar, indem Sie den Wert eines Zeitstempels an die entsprechende
Stelle in der Tabelle eintragen, wenn er sich durch eine Operation geändert hat. Vermerken
Sie außerdem in der Spalte ’Bemerkung’, wenn in einem Schritt eine Operation ignoriert
wurde oder eine Transaktion zurückgesetzt wurde.
Gehen Sie davon aus, dass T1 zum Zeitpunkt 1, T2 zum Zeitpunkt 2 und T3 zum Zeitpunkt
3 in die Datenbank eingebracht wird.
Operation T Sr (a)
T1 : r(a)
T Sw (a)
T Sr (b)
T Sr (c)
3
3
3
T2 : w(c)
T3 : r(c)
T1 : w(a)
Bemerkung
2
T3 : w(c)
T3 : r(b)
T Sw (c)
1
T2 : w(b)
T3 : r(a)
T Sw (b)
wird ignoriert
3
T1 wird abgebr.
T2 : r(a)
17. Juli 2007