Übungsblatt 14 - Universität Mannheim

Universität Mannheim
Lehrstuhl für Praktische Informatik III
Norman May
D7 27, Raum 410
68131 Mannheim
Telefon: (0621) 181-2586
Email: [email protected]
Datenbanksysteme I, SS 2004
14. Übungsblatt
keine Besprechung – zur individuellen Übung
1. ER-Modellierung
Eine Firma stellt diverse Produkte her. Die Teile, aus denen die Produkte zusammengesetzt werden, erhält sie von bestimmten Zulieferern. Die Produkte werden
nach Fertigstellung an verschiedene Kunden in alle Welt verkauft. Die Verwaltung
der Produktlinien soll mit Hilfe einer relationalen Datenbank realisiert werden. Die
ersten Schritte des Datenbankentwurfs wurden bereits durchgeführt, Abbildung 1
zeigt das fertige E-R-Diagramm.
(a) (12 Minuten)
Setzen Sie das E-R-Diagramm in ein relationales Schema um. Beschreiben Sie
dabei kurz Ihre Vorgehensweise. Fassen Sie, wenn möglich, Relationen zusammen.
(b) (4 Minuten)
Sind Kardinalitäten im relationalen Schema verloren gegangen? Begründen Sie
Ihre Antwort.
2. Relationale Algebra und Kalkül
(je 6 Minuten)
Gegeben sei folgendes relationales Schema einer Personaldatenbank. ChefNr ist
Fremdschlüssel und entspricht dem Attribut PersonalNr.
• Angestellte(PersonalNr, Name, Gehalt, Beruf, AbteilNr, ChefNr, Wohnort)
• Abteilung(AbteilNr, Name, Ort)
(a) Übersetzen Sie folgende Anfrage in den Tupel- und Domänenkalkül:
σU :Gehalt>C.Gehalt (ρU (Angestellte) U.Chef N r=C.P ersonalN r ρC (Angestellte))
(b) Übersetzen Sie folgende Anfrage in die relationale Algebra und in den Domänenkalkül:
{a|a ∈ Abteilung ∧ ∃n ∈ Angestellte(a.AbteilN r = n.AbteilN r ∧ a.Ort =
n.W ohnort)}
1
Name
KundenNr
Adresse
Kunde
<1,*>
RechnungsNr
gibt auf
<1,1>
Bestellung
<1,1>
<1,*>
wohnt in
umfaßt
<0,*>
Kennzeichen
Datum
<0,*>
ProduktNr
Nation
Name
Produkt
Beschreibung
Kontinent
<0,*>
<1,100>
sitzt in
besteht aus
<1,1>
Zulieferer
FirmenID
<1,*>
<1,*>
stellt her
Adresse
<1,1>
Teil
TeilNr
Beschreibung
Name
Abbildung 1: Produktverwaltung
(c) Übersetzen Sie folgende Anfrage in die relationale Algebra und in den Tupelkalkül:
{[a, n]|∃o([a, n.o] ∈ Abteilung∧∃p, m, g, b, c, w([p, m, g, b, a, c, w] ∈ Angestellte∧
b =0 P rogrammierer 0 ))}
3. SQL-Anfragen
Die Fahrtenbücher eines Kurierdiensts werden in einer relationalen Datenbank gehalten. Die Datenbank besitzt folgendes Schema:
• Kurier(PersNr, Name, GebDatum)
• Fahrzeug(Kennzeichen, Typ, kmh)
• Distanz(von, nach, kmZahl)
• Fahrt(FahrtNr, Datum, von, nach, Dauer, Verbrauch, PersNr, Ken nzeichen)
Die Relation Kurier enthält allgemeine Angaben zu den beschäftigten Kurieren. In
der Relation Fahrzeug werden das Kennzeichen, der Typ und die Höchstgeschwin2
digkeit aller Fahrzeuge des Kurierdiensts gespeichert. Die Relation Distanz gibt den
Abstand zweier Städte in Kilometern an. In der Relation Fahrt wird registriert, welcher Kurier mit welchem Fahrzeug eine Fahrt zurückgelegt hat. Dabei wird auch die
Dauer in Stunden und der Benzinverbrauch in Litern gespeichert.
Formulieren Sie folgende Anfragen in SQL:
(a) (5 Minuten)
Geben Sie die Personalnummern und Namen aller Kuriere an, die am 15.03.2002
von Hamburg nach München unterwegs waren.
(b) (5 Minuten)
Geben Sie das Kennzeichen, den Typ und die Höchstgeschwindigkeit des schnellsten Fahrzeugs an.
(c) (8 Minuten)
Geben Sie die Personalnummern und Namen der Kurier aus, die bereits mit
Fahrzeugen vom Typ Renault Twingo“ oder Ford Ka“ unterwegs waren, aber
”
”
noch nicht mit einem BMW 750“.
”
(d) (12 Minuten)
Geben Sie die Personalnummer und den Namen des sparsamsten Kuriers aus.
Das ist der Kurier, der den kleinsten Liter pro km Verbrauch aufweist.
4. Normalformen
(a) (4 Minuten)
Gegeben sei ein Relationenschema R(A, B, C, D, E, F ) mit folgenden funktionalen Abhängigkeiten FR = {A → CD, B → AF, C → BE, }. In welcher
höchsten Normalform befindet sich R?
(b) (4 Minuten)
Gegeben sei ein Relationenschema S(A, B, C, D, E, F ) mit folgenden funktionalen Abhängigkeiten FS = {A → B, AE → F, BC → D, BD → E, D → C}.
In welcher höchsten Normalform befindet sich S?
(c) (4 Minuten)
Gegeben sei ein Relationenschema T (A, B, C, D, E, F ) mit folgenden funktionalen/mehrwertigen Abhängigkeiten FT = {A → B, B → C, C → DE, E →
F, ACD →
→ BEF }. In welcher höchsten Normalform befindet sich T ?
5. (8 Minuten)
In einer relationalen Datenbank werden folgende persönliche Daten zu den Angestellten gespeichert.
• Personal(PersNr, Name, GebDatum, Gehalt, PrivatTelNr)
Nicht alle Personen sind authorisiert, alle Daten einzusehen. Eine bestimmte Benutzergruppe bekommt nicht alle Daten zu jeder Person zu sehen, sondern nur eine
Auswahl an Attributen. Also z.B.:
3
PersNr
007
203
483
Name
James Bond
Gerhard Schröder
Hans Müller
GebDatum
12.03.1964
07.04.1944
09.12.1956
Gehalt
300.000
-
In der Relation GehaltSperre(PersNr) sind die Personalnummern der Personen aufgeführt, deren Gehalt ausgeblendet werden soll.
Wie realisieren Sie diese Zugriffsbeschränkung? Beschreiben Sie Ihre konkrete Implementierung.
6. Transaktionsverwaltung
In einem Datenbanksystem werden die drei Transaktionen T1 , T2 und T3 verzahnt
ausgeführt. Die (schreibenden) Zugriffe der Transaktionen auf die Datenelemente
A, B, C, D und E werden in einem Log-File mitprotokolliert. Es existieren keine
Sicherungspunkte, d.h. T1 , T2 und T3 sind die ersten Transaktionen die auf dem neu
gestarteten Datenbanksystem laufen.
Schritt
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
T1
T2
T3
BOT
r(B, b1 )
BOT
Log-Datei
[LSN, TA, Redo, Undo, PreviousLSN]
[#1, T2 , BOT, -, 0]
[#2, T1 , BOT, -, 0]
b 1 = b1 + 5
w(B, b1 )
[#3, T2 , B = B + 5, B = B − 5, #1]
Abbruchpunkt 1
BOT
[#4, T3 , BOT, -, 0]
r(B, b2 )
r(E, e1 )
Commit
b 2 = b2 + 4
w(B, b2 )
r(C, c1 )
c 1 = c1 − 3
w(C, c1 )
Commit
[#5, T2 , Commit, -, #3]
[#6, T1 , B = B + 4, B = B − 4, #2]
[#7, T1 , C = C − 3, C = C + 3, #6]
Abbruchpunkt 2
r(C, c2 )
e 1 = e1 + c2
w(E, e1 )
[#8, T3 , E = E + C, E = E − C, #4]
[#9, T1 , Commit, -, #7]
Commit
[#10, T1 , Commit, -, #8]
(a) (1 Minute)
In welche Phasen gliedert sich generell der Wiederanlauf?
(b)
i. (4 Minuten)
Angenommen an Abbruchpunkt 1 stürzt das System mit Verlust des Hauptspeicherinhalts ab. Geben Sie die einzelnen Schritte des Wiederanlaufs an.
4
ii. (4 Minuten)
Führen Sie den Wiederanlauf für einen Absturz an Abbruchpunkt 2 durch.
Gehen Sie dabei wie in Teil i. vor.
(c)
i. (3 Minuten)
Um welche Art von Historie handelt es sich bei obiger Ausführung der
Transaktionen T1 bis T3 ?
• serialisierbar
• rücksetzbar
• vermeidet kaskadierendes Rücksetzen
• strikt
• seriell
ii. (4 Minuten)
Könnte man bei derartigen Historien für die Undo-Operation auch BeforeImages benutzen? Begründen Sie Ihre Antwort.
5