Klausur Datenmanagement im SS 2009

-1-
Klausur Datenmanagement im SS 2009
Themensteller: Prof. Dr. Jörg Becker
Die Bearbeitungszeit für die Klausur beträgt 120 Minuten. Bearbeiten Sie alle 5 Aufgaben.
Sie können maximal 120 Punkte erreichen. Nutzen Sie im Klausurheft bitte Vorder- und
Rückseiten. Das Aufgabenheft umfasst 11 Seiten.
AUFGABE 1: MODELLIERUNG MIT ERM
(35 PUNKTE)
Ein städtisches Krankenhaus möchte Patientenverwaltungssystem mittels einer relationalen
Datenbank unterstützen. Sie werden damit beauftragt, die fachkonzeptionelle Spezifikation
der Datenstrukturen zu erstellen. Der Chef des Hospitals gibt Ihnen dazu die folgenden Informationen:
„Vielleicht sage ich zunächst etwas zu unseren Verwaltungsstrukturen. Wir teilen unser Personal generell in zwei Kategorien: Verwaltungspersonal und medizinisches Personal. Medizinisches Personal wird für einen bestimmten Zeitraum einer Station zugeordnet, d.h. in dieser
Zeit arbeiten die Leute in der jeweiligen Station. Diese Mitarbeiter können im Zeitverlauf
anderen Stationen zugeordnet werden. Bei medizinischem Personal wird noch weiter unterschieden in Pflegepersonal und Ärzte. Einer Station wird immer ein Arzt als medizinische
Leitung zugeordnet, über verschiedene Zeiträume kann diese Rolle durch unterschiedliche
Ärzte wahrgenommen werden.
Kommen Patienten zu einem Zeitpunkt zu uns, sprechen wir von einer Aufnahme. Einer Aufnahme können mehrere Untersuchungen folgen, mindestens jedoch eine – die Untersuchungen beziehen sich immer auf genau eine Aufnahme. Jede Untersuchung wird von einem Arzt
durchgeführt, darüber hinaus soll für alle Untersuchungen der Zeitpunkt und der jeweilige
Befund festgehalten werden. Aus einer Untersuchung kann eine Therapie resultieren, das
muss jedoch nicht sein. Die verordnete Therapie muss jedoch genau auf die entsprechende
Untersuchung verweisen, zu der sie verordnet wurde. Bei den Therapien unterscheiden wir
medikamentöse Behandlung und die Operation. Oftmals ist auch beides Bestandteil einer
Therapie, darüber hinaus gibt es noch andere Therapieformen (z.B. ReHa-Maßnahmen usw.).
Eine Operation wird immer von mindestens einem Arzt durchgeführt.
Patienten können stationär aufgenommen werden. Im System heißt das: ein Patient belegt ein
Bett für einen bestimmten Zeitraum. Alle Betten sind jeweils genau einer Station zugeordnet,
eine Station kann keine (Ambulanz) bis beliebig viele Betten haben. Eine Station kann darüber hinaus über Operationssäle verfügen. Alle Operationen finden entsprechend immer in
genau einem solchen einer Station zugeordneten OP-Saal statt.“
-2a) Erstellen Sie mit Hilfe der oben ausgeführten Informationen ein Datenmodell in der EntityRelationship-Notation.
Beachten Sie dabei folgende Richtlinien:
•
Auf die Modellierung von Attributen kann verzichtet werden.
•
Zeit(raum)abhängige Typen sind so zu modellieren, dass sichergestellt wird, dass die
Zeit zum Primärschlüsselbestandteil des jeweiligen Typen wird.
•
Generalisieren Sie sinnvoll. Geben Sie dabei immer den Typ der Generalisierung/Spezialisierung an.
•
Relationshiptypen sind grundsätzlich zu benennen. Auf eine Benennung kann jedoch
verzichtet werden, wenn aus dem Relationshiptyp bei der Überführung in ein
Relationenschema keine Tabelle resultiert.
•
Das Modell sollte zur besseren Lesbarkeit in Teilmodelle gegliedert werden. Dabei
sind redundant modellierte Knoten mit einer gemeinsamen Nummer als identisch zu
kennzeichnen (siehe Bild).
(30 Punkte)
b) Das folgende ERM repräsentiert eine Datenbankstruktur, die in einer SQL-Datenbank implementiert wurde. Die Datenbank wurde so implementiert, dass die referentielle Integrität
gemäß dem dargestellten Modell jederzeit garantiert ist.
150
C
(0,1)
250
A
40
(0,n)
X
(1,n)
Folgendes ist Ihnen über die Daten in der Datenbank bekannt:
B
-3• Tabelle A enthält genau 250 Datensätze
• Tabelle B enthält genau 40 Datensätze
• Tabelle C enthält genau 150 Datensätze
Beantworten Sie folgende Fragen:
I) Wie viele Datensätze enthält die Tabelle X mindestens und wie viele Datensätze kann
sie höchstens enthalten?
(2 Punkte)
II) Aus der Tabelle B werden 10 Datensätze gelöscht. Wie viele Datensätze enthält die
Tabelle X nach dieser Transaktion minimal bzw. maximal?
(3 Punkte)
-4-
AUFGABE 2: MODELLÜBERFÜHRUNG
(20 PUNKTE)
a) Die Transformationsregeln zur Überführung eines ERMs in ein Relationenschema sind
nicht immer eindeutig. Teilweise gibt es mehrere Möglichkeiten, ein ERM in ein
Relationenschema zu überführen. Gegeben ist das folgende ERM:
Stellen Sie zwei unterschiedliche Transformationsmöglichkeiten für dieses ERM in
Relationenschemata dar, die zu unterschiedlichen Anzahlen von Tabellen führen. Nennen Sie
dabei für jede Tabelle den Namen, unterstreichen Sie Primärschlüsselattribute, kennzeichnen
Sie Fremdschlüsselattribute mit einer Raute (#) und machen Sie deutlich auf welche Tabelle
sich der Fremdschlüssel bezieht. Geben Sie auch entsprechende Constraints an, sofern diese
notwendig sind.
(4 Punkte)
b) Stellen sie kurz (in einem Satz) die Vor- bzw. Nachteile der unterschiedlichen Möglichkeiten aus a) dar.
(1 Punkt)
c) Gegeben ist das folgende ERM:
-5Aus Gründen der Übersichtlichkeit wurden die Attribute der einzelnen Entity- und Relationshiptypen in einer Tabelle zusammen gefasst, dabei sind alle mit * gekennzeichneten Attribute Pflichtfelder. Fremdschlüssel sind nicht in der Tabelle erfasst:
Objekttyp
Attribut
Beschreibung
Autobauer
ABId*
Fortlaufende Nummer, die jeden ‚
Autobauer identifiziert
Name*
Der Name der Firma
aktiv*
Gibt an, ob der Autobauer noch aktiv
Autos baut, oder nicht mehr im Geschäft ist
Gründungsdatum
Das Gründungsdatum des Autobauers
FId*
Fortlaufende Nummer, die jedes
Fahrzeug eindeutig identifiziert
Bezeichnung*
Typ-Bezeichnung des Fahrzeugs
KW
Die Leistung des Fahrzeugs (in Kilowatt)
Verbrauch
Der Kraftstoffverbrauch des Fahrzeugs in l/100 km.
EId*
Fortlaufende Nummer, die jeden Eigentümer eindeutig identifiziert
Name*
Der Name des Eigentümers
ETyp
Der Eigentümertyp. Ein Eigentümer
kann entweder „privat“, „gemeinnützig“ oder „gewerblich“ sein.
Tel
Telefonnummer des Eigentümers
Fahrzeug
Eigentümer
Überführen Sie die Elemente dieses Modells in Relationen (Tabellen). Entscheiden Sie, welche Elemente des Modells zu Tabellen werden. Nennen Sie dabei für jede Tabelle den Namen, unterstreichen Sie Primärschlüsselattribute, kennzeichnen Sie Fremdschlüsselattribute
mit einer Raute (#) und machen Sie deutlich auf welche Tabelle sich der Fremdschlüssel bezieht. Geben Sie auch entsprechende Constraints an, sofern diese notwendig sind. (6 Punkte)
d) Formulieren Sie für die Relationen aus c) geeignete CREATE-STATEMENTS in SQL.
Vergeben Sie dabei die notwendigen Fremdschlüssel, achten Sie auf referentielle Integrität
und explizieren Sie dabei ihre Überlegungen, auch wenn es sich um SQL-Default-Werte handelt. Wählen Sie für die Spalten der Tabellen geeignete Datentypen. Trigger und Constraints
müssten NICHT definiert werden.
(9 Punkte)
-6-
AUFGABE 3: DATENBANKNORMALISIERUNG
(30 PUNKTE)
Die Müller Cargo Tier KG ist ein mittelständischer Logistikdienstleister, der sich auf Transporte verschiedenster Art spezialisiert hat. Um für die Auftragsplanung einen Überblick über
die verschiedenen Frachtarten sowie die zu deren Beförderung zur Verfügung stehenden
Fahrzeugtypen und Fahrer zu bekommen, ist die Entwicklung einer webbasierten, Datenbanklösung geplant. Der zuständige Projektleiter unterbreitet Ihnen einen Vorschlag für ein
Relationenschema und gibt Ihnen noch zusätzlich die folgenden Hinweise.
Zentrales Anliegen der Datenbanklösung ist es, den Disponenten darüber Auskunft zu geben,
welche Fahrzeugtypen für die Beförderung der verschiedenen Frachtarten zur Verfügung
stehen und welche Fahrer hierfür in Frage kommen. Von Fahrern werden hierzu Namen sowie die Fahrzeugtypen erfasst, die von diesen gelenkt werden dürfen. Ein Fahrzeugtyp kann
dabei von mehreren Fahrern gelenkt werden. Ein Fahrer kann ebenfalls die Berechtigung für
mehrere Fahrzeugtypen haben. Ein Fahrzeugtyp kann für den Transport mehrerer Frachtarten
verfügbar sein. Eine Frachtart wird über die FrachtID identifiziert. Darüber hinaus wird das
Frachtgut als Bezeichnung sowie die Gefahrenklasse benötigt. Die Gefahrenklasse ist insbesondere für die Bestimmung des Lagerortes und der Lagerdauer einer jeweiligen Frachtart
von besonderem Interesse. Für die Beförderung einer Frachtart können mehrere Fahrzeugtypen zur Verfügung stehen. Einem Fahrer ist es möglich, Fahrzeugtypen mit unterschiedlichen Frachtarten zu lenken. Eine Frachtart kann selbstverständlich auch von mehreren Fahrern unter Verwendung von verschiedenen Fahrzeugtypen befördert werden.
Bitte beachten Sie, dass Ihnen mit den Tabellen 1-3 jeweils nur einen Ausschnitt aus dem
Gesamtdatenbestand zur Verfügung steht. Alle Teilaufgaben können unabhängig von einander bearbeitet werden. Einzelne Datensätze müssen nur angegeben werden, wenn diese zur
Darstellung des Lösungsweges bzw. der Lösung erforderlich sind.
a) Definieren Sie den Begriff der ersten Normalform. Bitte erläutern Sie, ob die Tabelle 1 in
der ersten Normalform vorliegt. Überführen Sie diese Tabelle ggf. in Relationen der ersten
Normalform.
(4 Punkte)
Tabelle 1
-7b) Definieren Sie den Begriff der zweiten Normalform. Bitte erläutern Sie, ob die Tabelle 2 in
der zweiten Normalform vorliegt. Überführen Sie diese Tabelle ggf. in Relationen der zweiten
Normalform.
(3 Punkte)
FahrzeugID
FrachtID
Gefahrenklasse
LKW01
LKW02
LKW10
LKW99
LKW68
LKW01
A102
A225
A678
A153
A225
A678
unkritisch
unkritisch
ultrakritisch
kritisch
unkritisch
ultrakritisch
Tabelle 2
c) Definieren Sie den Begriff der dritten Normalform. Bitte erläutern Sie, ob die Tabelle 3 in
der dritten Normalform vorliegt. Überführen Sie diese Tabelle ggf. in Relationen der dritten
Normalform.
(3 Punkte)
Tabelle 3
d) Die beiden nachfolgend gegebenen Modelle in ERM-Notation beschreiben jeweils Beziehungen zwischen den Entitäten Fahrer, Frachtart und Fahrzeugtyp. Beschreiben Sie die inhaltliche Aussage der beiden Modelle und stellen Sie den inhaltlichen Unterschied zwischen
Modell 1 und Modell 2 heraus.
(4 Punkte)
Modell 1
Fahrer
Modell 2
(0,n)
F-K-R-ZuO
(0,m)
Fahrzeugtyp
Fahrzeugtyp
Frachtart
(0,m)
(0,m)
(0,n)
Frachtart
Fahrerlaubnis
(0,n)
Fahrer
(0,n)
Beförderungserlaubnis
Gehen Sie im Weiteren abweichend von Aufgabe a) bis d) davon aus, dass Ihnen jeweils der
vollständige Datenbestand vorliegt. Die nachfolgend gegebenen Tabellen stellen die Beziehungen zwischen den drei Entitäten Fahrer, Frachtart und Fahrzeugtyp dar.
-8e) Definieren Sie den Begriff der vierten Normalform. Bitte erläutern Sie, ob die Tabelle 4 in
der vierten Normalform vorliegt. Überführen Sie diese Tabelle ggf. in Relationen der vierten
Normalform.
(5 Punkte)
FahzeugID
FahrerID FrachtID
LKW01
LKW10
LKW99
LKW68
LKW02
LKW10
LKW02
LKW68
P010
P120
P140
P050
P010
P140
P120
P050
A225
A678
A859
A225
A225
A859
A678
A325
Tabelle 4
f) Definieren Sie den Begriff der fünften Normalform. Erläutern Sie das Prinzip, welches der
Prüfung auf fünfte Normalform zu Grunde liegt, anhand von Modellen in ERM-Notation. Beschreiben Sie die inhaltliche Aussage der einzelnen Modelle und stellen Unterschiede zwischen diesen heraus. Beziehen Sie sich dabei auf das Beispiel in Tabelle 5.
(6 Punkte)
Tabelle 5
g) Bitte erläutern Sie, ob Tabelle 5 in der fünften Normalform vorliegt und überführen sie
diese ggf. in Relationen der fünften Normalform. Beschreiben Sie hierbei Ihr Vorgehen. Sie
können davon ausgehen, dass sich die Tabelle bereits in der vierten Normalform befindet.
(5 Punkte)
-9-
AUFGABE 4: STRUCTURED QUERY LANGUAGE
(25 PUNKTE)
Gegeben ist das in Abb. 4.1. dargestellte ERM als fachliche Spezifikation eines datenbankbasierten Systems zur Bestellungsabwicklung in einem Imbissstand. Es handelt sich hierbei um
einen vereinfachten Ausschnitt. Die angebotenen Gerichte setzen sich ausschließlich aus einer
Wurst- und einer Pommes-Komponente zusammen. Jede Wurst besitzt einen spezifischen
Schärfegrad, der durch eine natürliche Zahl repräsentiert wird. Eine Übersicht über das zugrunde liegende Datenbankschema gibt Abb. 4.2.
Abb. 4.1: Datenschema in ER-Notation
Abb. 4.2: Datenschema in EER-Notation
- 10 Formulieren Sie SQL-Abfragen, welche die folgenden Fragestellungen beantworten. Die Ergebnismenge der jeweiligen Abfragen soll vollständig und ausschließlich die gewünschten
Informationen beinhalten. Die Abfragen sollen nicht komplexer als nötig sein (Es sollen
z. B. nur die Tabellen verknüpft werden, die für das Ergebnis notwendig sind.).
a) Formulieren Sie einen SQL-Befehl, der eine neue Bestellung des Stammkunden „Stefan
Schmerz“ hinzufügt. Der Kunde (der im System die kundenID 666 besitzt) gab die Bestellung
am 2009-01-01 um 10:00 Uhr ab.
(2 Punkte)
b) Formulieren Sie einen SQL-Befehl, der alle Würste löscht, welche einen Schärfegrad
unter 5 aufweisen.
(2 Punkte)
c) Beim Einpflegen der Wurst „Zorn Gottes“, die unter der wurstID 777 gespeichert wurde,
wurde fälschlicherweise der Schärfegrad 1 eingetragen. Der richtige Schärfegrad beträgt jedoch 8! Schreiben Sie einen SQL-Befehl, der diesen gefährlichen Fehler korrigiert. (2 Punkte)
d) Schreiben Sie einen SQL-Befehl, der eine Liste mit Bezeichnungen der Würste ausgibt,
welche einen Schärfegrad von mindestens 5 aufweisen.
(2 Punkte)
e) Geben Sie den durchschnittlichen Wert aller bisher abgegebenen Bestellungen aus. (Hinweis: Der Wert einer Bestellung ergibt sich aus der Summe der Preise der bestellten Gerichte,
die jeweils mit der bestellten Menge multipliziert werden. Wenn z. B. ein Gericht für 3,50 €
und zwei Gerichte für 3,00 € bestellt wurden, dann beträgt der Wert dieser Bestellung
1 * 3,50 € + 2 * 3,00 € = 9,50 €)
(5 Punkte)
f) Sie wollen wissen, für welche Schärfen sich der Kunde mit dem Namen „Flinsch“ interessiert. Basierend darauf, wie häufig dieser Kunde Würste mit der jeweiligen Schärfe bestellt
hat, geben Sie eine Liste der Schärfegrade mit der jeweiligen Bestellhäufigkeit aus. (4 Punkte)
g) Wie viele Kunden haben bereits die schärfsten angebotenen Würste bestellt und welchen
Schärfegrad weisen diese auf? Schreiben Sie einen SQL-Befehl, der diese Information liefert.
(5 Punkte)
h) Formulieren Sie eine SQL-Abfrage, die eine Liste von Kunden ausgibt, welche im System zwar gepflegt sind, aber bisher noch keine Bestellung abgegeben haben.
(3 Punkte)
- 11 -
AUFGABE 5: DATENBANKANOMALIEN UND TRANSAKTIONEN
(10 PUNKTE)
a) Welche Anomalien bei konkurrierenden Zugriffen auf Daten sind Ihnen bekannt? Nennen
Sie diese und beschreiben Sie sie kurz.
(4 Punkte)
b) Geben Sie die Ergebnisse folgender Transaktionen sowohl bei einer seriellen, als auch bei
einer parallelen Ausführung an. Nennen Sie ggf. den Namen der jeweiligen Ausführungsanomalie:
(6 Punkte)
b1)
Datenbank: A = 10
TRANSAKTION 1
write 7 -> A
TRANSAKTION 2
read x2 <- A
print x2
commit
rollback
------------------------------------------------------b2)
In einer Datenbank sind 150 Ausdauerläufer eingetragen.
TRANSAKTION 1
read l <- count(Läufer)
TRANSAKTION 2
insert into Läufer
commit
read m <- sum(MaxDistanz)
M = m / l
print M
commit
------------------------------------------------------b3)
Datenbank: A = 10
TRANSAKTION 1
read x1 <- A
TRANSAKTION 2
read x2 <- A
X1 = x1 + 5
X2 = x2 + 10
write X1 -> A
write X2 -> A
commit
commit
-------------------------------------------------------