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

Name: Musterlösung
Seite 2
Aufgabe 1 (8 Punkte)
Beantworten Sie die Fragen in dieser Aufgabe mit einer kurzen, prägnanten Antwort.
1. Wodurch erhält man bei der konzeptuellen Modellierung ein redundanzfreies Schema?
Konsolidierung
2. Was bedeutet Redundanz?
Informationen werden doppelt gespeichert
3. Mit welchem SQL-Befehl entfernen Sie die Tabelle Person aus der Datenbank Uni?
drop table Person
4. Welches Prinzip gilt beim Schreiben von Log-Informationen?
WAL-Prinzip
5. Welche Eigenschaft muss ein Superschlüssel erfüllen, um Schlüsselkandidat zu sein?
Minimalität
6. In welchem Zustand befindet sich eine festgeschriebene Transaktion?
persistent oder dauerhaft
7. Wie heißt ein Schedule, in dem alle Schritte einer Transaktion hintereinander ausgeführt werden?
seriell
8. Angenommen, eine Datenbank stürzt ab. Welche Voraussetzung darf eine Transaktion
nicht erfüllen, um beim Wiederanlauf ein Loser zu sein?
Die Transaktion kann im LOG-File ein commit vorweisen
12. Oktober 2009
Name: Musterlösung
Seite 3
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
Barney
Barney
1
Homer
Krusty
Burns
0
Homer
1
0
Krusty
a) Fügen Sie die vier Schlüssel Marge, Moe, Smithers, Maggie in die oben angegebene
Index-Struktur ein und zeichnen Sie anschließend ihren Aufbau: (4 Punkte)
Indexeinstiegstabelle
1111
1
1
Barney
Barney
1
Krusty
Homer
Burns
0
Homer
1
1
Maggie
Krusty
1
1000
1
1
Moe
Marge
0
Marge
Moe
Smithers
12. Oktober 2009
Name: Musterlösung
Seite 4
b) Löschen Sie nun aus der Index-Struktur aus a) den Schlüssel Homer und fügen
Sie anschließend den Schlüssel Bart ein. Zeichnen Sie danach den Aufbau erneut:
(4 Punkte)
Indexeinstiegstabelle
1111
1
1
Barney
Barney
1
Krusty
Burns
Bart
0
Burns
1
1
Maggie
Krusty
1
1000
1
1
Moe
Marge
0
Marge
Moe
Smithers
12. Oktober 2009
Name: Musterlösung
Seite 5
Aufgabe 3 (10 Punkte)
Für die Modellierung eines Versicherungsunternehmens seien folgende Entity-Typen gegeben:
Entity-Typ
Kunde
Außendienstmitarbeiter
Innendienstmitarbeiter
Abteilung
Versicherungsvertrag
Attribute
Name, Kundennr., Adresse
PersNr., Name, Bezirk
PersNr., Name, Raumnr., Durchwahl
Nr., Bereich
Nr., Datum, Typ, Versicherungssumme, Laufzeit
Zwischen diesen Entity-Typen existieren folgende Beziehungen:
Die Innendienstmitarbeiter sind immer einer festen Abteilung zugeordnet, in der sie eine
bestimmte Funktion wahrnehmen. In einem Bezirk ist ein Außendienstmitarbeiter für die
dortigen Kunden zuständig. Ein Kunde kann beliebig viele Versicherungsverträge abschließen, die Vertragsnummer ist dabei nur pro Kunde eindeutig. Die Verträge werden je nach
Typ von einer bestimmten Abteilung des Innendienstes bearbeitet.
Erstellen Sie aus den obigen Angaben ein ER-Diagramm. Verwenden Sie Generalisierung
zur Strukturierung Ihrer Entity-Typen und markieren Sie ggf. schwache Entity-Typen.
Markieren Sie die Schlüssel und ergänzen Sie die Charakterisierung der Beziehungstypen
hinsichtlich ihrer Funktionalität.
Rang
Fachgebiet
Name
Aerzte
PersNr
behandeln
N
is_a
Personal
N
PatientenNr
Pfleger
M
Patienten
arbeiten
Qualifikation
Name
N
Krankheit
AnzBetten
1
StationsNr
Stationen
Name
1
auf
N
Zimmer
RaumNr
1
liegen_auf
Aufnahmedatum
Entlassungsdatum
12. Oktober 2009
Name: Musterlösung
Seite 6
Aufgabe 4 (8 Punkte)
Gegeben sei das folgende ER-Modell zur Modellierung eines Krankenhausverwaltungssystems:
Rang
Fachgebiet
Name
Aerzte
PersNr
Personal
behandeln
N
is_a
N
PatientenNr
Pfleger
arbeiten_auf
M
Patienten
Qualifikation
Name
N
Krankheit
AnzBetten
1
StationsNr
Stationen 1
Name
sind_auf
N
Zimmer
RaumNr
1
liegen_auf
Aufnahmedatum
Entlassungsdatum
a) Markieren Sie oben im ER-Diagramm die Schlüssel der Entity-Typen und ergänzen
Sie die Charakterisierung der Beziehungstypen hinsichtlich ihrer Funktionalität.
(2 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)
Patienten
Ärzte
Pfleger
Stationen
Zimmer
arbeiten auf:
behandeln:
sind auf:
liegen auf:
{[Name, PatientenNr., Befund]}
{[PersNr., Name, Fachgebiet, Rang]}
{[PersNr., Name, Qualifikation]}
{[StationsNr., Name]}
{[RaumNr., AnzBetten]}
{[PersNr., StationsNr.]}
{[PersNr., PatientenNr.]}
{[RaumNr., StationsNr.]}
{[PatientenNr., RaumNr.]}
c) Verfeinern Sie das relationale Schema, soweit wie möglich. Wie lauten die veränderten Relationen? (2 Punkte)
Die Relationen arbeiten auf, sind auf und liegen auf können zu den passenden
Entitytypen hinzugefügt werden.
Patienten
Ärzte
Pfleger
Zimmer
{[Name, PatientenNr., Befund, RaumNr.]}
{[PersNr., Name, Fachgebiet, Rang, StationsNr.]}
{[PersNr., Name, Qualifikation, StationsNr.]}
{[RaumNr., AnzBetten, StationsNr.]}
12. Oktober 2009
Name: Musterlösung
Seite 7
Aufgabe 5 (9 Punkte)
Gegeben seien die folgenden Relationen einer Universitätsdatenbank:
Studenten:
Dozenten:
Vorlesungen:
lesen:
hören:
{[MatrNr, Name, Semester]}
{[PersNr, Name]}
{[VorlNr, Titel]}
{[VorlNr, PersNr]}
{[MatrNr, VorlNr]}
Formulieren Sie die folgenden Anfragen in der Relationenalgebra:
a) In welchen Semestern sind die Hörer der Vorlesungen von Prof. Hastig? (3 Punkte)
ΠSemester (Studenten o
n hoeren o
n lesen o
n σN ame=0 Hastig0 (Dozent))
b) Welche Vorlesungen (Titel) werden nicht von Schmidthuber gehört? (3 Punkte)
ΠT itel (V orlesungen − Πsch(V orlesungen)(σN ame=0 Schmidthuber0 (Studenten) o
n hoeren o
n
V orlesungen))
c) Welche Studenten (Name) hören alle Vorlesungen, die Prof. Hastig liest? (3 Punkte)
ΠN ame (Studenten o
n (hoeren ÷ ΠV orlN r (lesen o
n σN ame=0 Hastig0 (Dozent))))
12. Oktober 2009
Name: Musterlösung
Seite 8
Aufgabe 6 (11 Punkte)
Gegeben seien die folgenden Relationen einer Datenbank, die das weltweite Vorkommen
von freilebenden Tierarten modelliert sowie den Bestand der Tierarten in verschiedenen
Zoos:
Tierart:
Vorkommen:
Land:
Bestand:
Zoo:
{[TName, Gewicht, Gattung]}
{[TName, LName]}
{[LName, Kontinent]}
{[TName, Stadt]}
{[Stadt, LName]}
Formulieren Sie die folgenden Anfragen in SQL:
a) Wie lauten die Namen aller Tierarten der Gattung “Raubkatze”, die in deutschen
Zoos gehalten werden? (2 Punkte)
select
from
where
and
and
and
distinct t.TName
Tierart t, Bestand b, Zoo z
t.Gattung = ’Raubkatze’
z.LName = ’Deutschland’
t.TName = b.TName
b.Stadt = z.Stadt
b) Wie lauten die Namen der Länder, in denen es keine freilebenden Raubkatzen gibt?
(3 Punkte)
select LName
from Land
where LName not in
(select v.LName
from Vorkommen v, Tierart t
where t.Gattung = ’Raubkatze’
and t.TName = v.TName)
12. Oktober 2009
Name: Musterlösung
Seite 9
c) Liste die Anzahl der unterschiedlichen Tierarten pro Zoo in Europa absteigend sortiert nach der Anzahl. (3 Punkte)
select
from
where
and
and
group
order
z.Stadt, count(b.TName) as Anzahl
Zoo z, Bestand b, Land l
z.Stadt = b.Stadt
z.LName = l.LName
l.Kontinent = ’Europa’
by z.Stadt
by Anzahl desc
d) In welchen Städten liegen Zoos, in denen nur Tierarten gehalten werden, die im
gleichen Land nicht in freier Wildbahn vorkommen? (3 Punkte)
select Stadt
from Zoo
where Stadt not in
(select z.Stadt
from Zoo z, Bestand b, Vorkommen v
where z.Stadt = b.Stadt
and z.LName = v.LName
and b.TName = v.TName)
12. Oktober 2009
Name: Musterlösung
Seite 10
Aufgabe 7 (7 Punkte)
Sei folgender B*-Baum mit k = 2 gegeben.
4
4
9
7
9
19
11
14
19 20 24 27
a) Fügen Sie nacheinander die Schlüssel 6, 17 und 26 in den Baum ein und zeichnen Sie
ihn erneut.
28
1
1
1
4
5
7
10
10
14
20
20
28
22
25
33
28
30
33
34
b) Fügen Sie nacheinander die Schlüssel 15 und 10 in den Baum ein und zeichnen Sie
ihn erneut.
1
1
1
4
5
7
10
20
10
20
14
20
28
22
28
33
34
c) Löschen Sie nacheinander die Schlüssel 20 und 27 aus dem Baum und zeichnen Sie
ihn erneut.
1
1
4
5
7
10
10
14
22
22
28
34
12. Oktober 2009
Name: Musterlösung
Seite 11
Aufgabe 8 (6 Punkte)
Sei ein B*-Baum gegeben mit k = 2 und drei Ebenen.
a) Wieviele Elemente kann der Baum maximal enthalten? (1 Punkt)
64 Elemente
b) Wieviele Elemente kann der B*-Baum minimal enthalten, wenn bislang in den Baum
nur Elemente eingefügt wurden, d.h. wenn noch keine Löschoperationen durchgeführt
wurden? (3 Punkte)
11 Elemente
c) Wieviele Elemente kann der B*-Baum minimal enthalten, wenn bereits Löschoperationen durchgeführt wurden? (2 Punkte)
8 Elemente
12. Oktober 2009
Name: Musterlösung
Seite 12
Aufgabe 9 (6 Punkte)
Gegeben sei folgende durch die Punkte A = (10, 60), B = (90, 80), C = (70, 70),
D = (20, 40), E = (30, 30), F = (50, 20) und G = (40, 50) partitionierte Datenfläche:
y
100
90
80
B
70
60
50
40
C
A
G
D
30
E
20
F
10
10 20 30 40 50 60 70 80 90 100 x
Zeichnen Sie den zugehörigen 2-d-Baum mit den jeweiligen Buchstaben und Schlüsseln in
den Knoten und den Diskriminierungsbedingungen.
D (20, 40)
y <= 40
y > 40
F (50, 20)
C (70, 70)
x <= 50
x <= 70
E (30, 30)
A (10, 60)
x > 70
B (90, 80)
y <= 60
G (40, 50)
12. Oktober 2009
Name: Musterlösung
Seite 13
Aufgabe 10 (7 Punkte)
Gegeben sei folgende XML-Datei:
<?xml version="1.0" ?>
<catalogue>
<book type="broschiert" lang="de">
<!-- bestellt -->
<authors>
<author sex="m">
<firstname>Oliver</firstname>
<lastname>Vornberger</lastname>
</author>
</authors>
<title edition="10">Datenbanksysteme</title>
<isbn />
<publisher>Selbstverlag der Universitaet Osnabrueck</publisher>
<price currency="EUR">7,50</price>
</book>
<book type="broschiert" lang="de">
<!-- vergriffen -->
<authors>
<author sex="m">
<firstname>Alfons</firstname>
<lastname>Kemper</lastname>
</author>
<author>
<firstname>Andre</firstname>
<lastname>Eickler</lastname>
</author>
<author>
<firstname>Martin</firstname>
<lastname>Wimmer</lastname>
</author>
</authors>
<title edition="4">Datenbanksysteme - Eine Einfuehrung</title>
<isbn>3-486-25706-4</isbn>
<publisher>Oldenbourg</publisher>
<price currency="EUR">39,80</price>
</book>
<book type="gebunden" lang="en">
<!-- bestellt -->
<authors>
<author sex="m">
<firstname>Elliotte Rusty</firstname>
<lastname>Harold</lastname>
</author>
<author sex="m">
<firstname>Scott</firstname>
<lastname>Means</lastname>
</author>
</authors>
<title edition="1">XML in a Nutshell</title>
<isbn>0-596-00058-8</isbn>
<publisher>O’Reilly</publisher>
<price currency="USD">29,95</price>
</book>
</catalogue>
Aufgabenstellung: siehe nächste Seite
12. Oktober 2009
Name: Musterlösung
Seite 14
Geben Sie für die folgenden Abfragen einen absoluten XPath-Ausdruck an. (4 Punkte)
a) Liste die Preise aller Bücher auf.
//price/text()
b) Liste die Nachnamen der Autoren auf, deren Geschlecht mit “m” angegeben ist.
//author[@sex = ’m’]/lastname//text()
Hinweis: Das Zeichen für den Zeilenumbruch lautet: 

Geben Sie eine XQuery-Anfrage an, die die Verlage zeilenweise nach der Anzahl der Autoren
aufsteigend sortiert ausgibt. (3 Punkte)
for $b in //book
order by count($b/authors/author)
return concat($b/publisher/text(), ’
’)
12. Oktober 2009
Name: Musterlösung
Seite 15
Aufgabe 11 (8 Punkte)
Gegeben sei das Relationenschema
R = {A, B, C, D, E, F }
mit den funktionalen Abhängigkeiten
A
AD
AF
CE
D
→
→
→
→
→
F
C
D
AB
E
a) Nennen Sie alle Schlüsselkandidaten für R. (4 Punkte)
Die Schlüsselkandidaten lauten: A, CE und CD
b) Sei R in der 1. Normalform. Ist R auch in der 2. Normalform? Begründen Sie Ihre
Antwort. (2 Punkte)
R ist in 2. Normalform, da die beiden Nichtprimärattribute B und F weder von C
noch von D oder E einzeln funktional abhängig ist.
c) Ist R in der 3. Normalform? Begründen Sie wieder Ihre Antwort. (2 Punkte)
Es gibt nur zwei Nichtprimärattribute B und F . Da diese direkt von Schlüsselkandidaten abhängen, können keine transitiven Abhängigkeiten dazwischen existieren.
12. Oktober 2009
Name: Musterlösung
Seite 16
Aufgabe 12 (6 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.
12. Oktober 2009
Name: Musterlösung
Seite 17
Aufgabe 13 (6 Punkte)
Betrachten Sie die drei Schedules mit den 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(a)
T2 : lockX(a)
T3 : lockX(b)
T3 : lockX(a)
T1 : unlockX(a)
T2 : lockX(b)
Schedule 3
T1 : BOT
T2 : BOT
T3 : BOT
T1 : lockX(b)
T2 : lockX(a)
T3 : lockX(b)
T2 : lockX(c)
T1 : lockX(c)
T2 : unlockX(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?
N
Ja
Nein
Schedule 3: Wartegraph:
T1
T2
T3
Deadlock?
Ja
N
Nein
12. Oktober 2009