Musterlösung

Westfälische Wilhelms-Universität Münster
Datenbanken
Musterlösung
Julia Wolters
Sommersemester 2009
Dies sind die kompletten Musterlösungen der Übungen, die sich aus den Mitschriften der
Übung und den gegebenen Musterlösungen im OpenUss ergeben
1
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 1
dateibasierte Datenhaltung
Speicherung der Daten in einem csvFormat und damit in jedem Editor verwendbar.
Durch Erlauben der Bearbeitung der
Datensätze ist keine allgemeine Sicherheit gegeben.
Datenbanksystem
Keine offene Sichtbarkeit der Speicherung möglich.
Hohe Datensicherheit vorhanden.
Große Datenmengen können effizient
a)
verwaltet werden.
inflexible und Fehleranfällige Datenhal- Einfache Bedienbarkeit.
tung
Datensätze können redundanz und in- Ermöglichung von redundanzfreier und
konsistente Daten enthalten
konsistenter Datenhaltung.
Verbindung der Datenbank mit einer
Verwaltungssoftware für Datenbanken.
keine parallelen Transaktionen möglich mehrere
parallele
Transaktionen
möglich
Nachteile von dateibasierter Datenhaltung
• partielle Inkonsistenz
– Integrität von Programm geprüft
– Redundanz
– keine Transaktionen
• Mehrfachbenutzung schwierig
• Abfragesprache (z.B. SQL) fehlt, Keine Ad-hoc-Abfrage
b) Der Einstieg in die Datenhaltung wird durch die dateibasierte Datenhaltung vereinfacht. Dadurch, dass keine bestimmten Programme benötigt werden, ist die Bearbeitung und Benutzung auf Standard–PC gegeben.
Dateibasierte Datenhaltung nützlich für:
• keine / einfacherere Anwendungen / kein komplexes DBMS
• begrenzte Ressourcen (z.B. embedded systems)
• kein Multiuserbetrieb notwendig
2
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 2
Geben Sie für die folgenden Begriffe jeweils eine möglichst kurze Deffnition und nach
Möglichkeit zusätzlich ein Beispiel an:
Datenbank Sammlung von (zusammenhängenden) Daten
Beispiel: CD–Sammlung, die alle (relevanten) Informationen zu den gesammelten
CDs enthält.
Datenbanksystem Verbindung einer Datenbank mit einer Verwaltungssoftware
Beispiel: MS Access, OO Base
Datenmodell Darstellung und Erfassung der in der Datenbank enthaltenen Informationen.
Beispiel: ER-Modell
Datenunabhängigkeit Unterscheidung zwischen physische (Implementierung) und logische (Anwendung) Datenunabhängigkeit.
Data Dictionary Datenwörterbuch. Zugriff auf die Datenbeschreibung der Datenbank,
enthält alle relevanten Informationen
DBA Datenbank-Administrator
DDL Data Definition Language – Sprache zur Datenbankdefinition
DML Data Manipulation Language – Sprache für Anfragen und Änderungsoptionen.
Indexstruktur realisiert Zugriff auf Dateien und Zugriffsstrukturen.
Redundanz Mehrfachvorhandensein von Informationen.
Sicht Verteilung der Zugriffsrechte.
Transaktion Zusammenfassung von Datenbank-Änderungen zu Funktionssicherheiten,
die als Ganzes ausgeführt werden sollen und deren Effekt bei Erfolg permanent
in der Datenbank gespeichert werden soll.
Julia Wolters
3
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 3
Erläutern Sie am Beispiel einer Literaturdatenbank den Unterschied zwischen dem Schema
und der Ausprägung einer Datenbank.
∼ ∼
∼ ∼
∼ ∼
∼ ∼
←−
∼ ←−
∼ ←−
4
Schema
Ausprägung
Ausprägung
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 4
Erläutern Sie die folgenden aus der Vorlesung bekannten Begriffe am Beispiel eines Arrays
der Größe m × n in einer Ihnen vertrauten Programmiersprache:
• Physikalische Ebene:
|
{z
m·n
}
n
• Konzeptionelle Ebene:
m
• logische Ebene: int[][] a = new int[m][n]
• Sichtebene: int[] sicht = a[b]
Julia Wolters
5
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 5
(a) Beschreiben Sie informell das konzeptionelle Schema einer Datenbank, die als Reservierungssystem für Flugreisen eingesetzt werden kann.
Flug: FlugNr, Startzeit, ZielZeit, ZielOrt, ...
Passagier: KundenNr, Name, Adresse
Buchung: FlugNr, KundenNr
(b) Identifizieren Sie nun unterschiedliche Benutzergruppen für diese Datenbank. Geben
Sie für jede Benutzergruppe eine eigene Sicht an. Welche Aspekte gilt es dabei zu
berücksichtigen?
(c) Formulieren Sie abschließend umgangssprachliche Beispiele für Anfragen und Aktualisierungen bezüglich dieser Datenbank für die unterschiedlichen Benutzergruppen.
6
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 6
Erläutern Sie die folgenden Begriffe jeweils kurz(!) allgemein und an einem Beispiel:
Beziehung Assoziation zwischen Entitäten
Beziehungsmenge Menge aller Beziehungen eines Beziehungstyps
Entität Basisobjekt mit einer in der realen Welt unabhängigen Existenz
Objekt der Anwendungsdomäne, vor anderen Objekten unterscheidbar
Entitätstyp definiert das Format von Objekten einer Entitätsmenge
Entitätsmenge Sammlung aller Entitäten eies Entitätstypen in einer Datenbank
Menge von Objekten mit gleichen Attibuten
Superschlüssel Menge von Attributen, deren Werte jede Entität der zugehörigen Entitätsmenge eindeutig identifizieren
Kandidatenschlüssel minimaler Superschlüssel
Primärschlüssel ausgezeichneter Kandidatenschlüssel
Relation (mathematisch) Teilmenge des Karthesischen Produkts
Rolle Verdeutlichen die Semantik der Beziehungen
Aufgabe eines Entitätstyps innerhalb ener Beziehung
Konzeptioneller Entwurf Erstellung eines konzeptionellen Schemas für die Datenbank
Globalansicht (ERM)
Logischer Entwurf Transformation des konzeptionellen Schemas in das Implementierungsdatenmodell
Konversion, Normalisierung in Ziel-DBMS
Physischer Entwurf Festlegung der internen Speicherstrukturen, Zugriffspfade und Dateiorganistion
Dateiformate, Indexstruktur, Denomalisierung
Julia Wolters
7
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 7
Sie kennen aus der Vorlesung ternäre Beziehungstypen. Die ersten beiden Abbildungen
stellen dar, wie ein ternärer Beziehungstyp durch die Einführung eines weiteren Entitätstyps aufgelöst werden kann. In der rechten Abbildung ist eine weitere Alternative zur
Auflösung des ternären Beziehungstyps durch drei binäre Beziehungstypen dargestellt.
Untersuchen Sie, ob diese zweite Alternative sinnvoll ist.
Lehrer
|
Klasse — 4 — Fach
Es gehen Informationen verloren
- welcher Lehrer unterichtet welche Klasse in welchem Fach?
A
R
R1
R2
R3
=
=
=
=
=
{A1 , A2 } , B = {B1 , B2 } , C = {C1 , C2 }
{(A1 , B1 , C1 ), (A1 , B2 , C2 ), (A2 , B1 , C2 ), (A2 , B2 , C1 )}
{(A1 , B1 ), (A1 , B2 ), (A2 , B1 ), (A2 , B2 )}
{(B1 , C1 ), (B1 , C2 ), (B2 , C1 ), (B2 , C2 )}
{(A1 , C1 ), (A1 , C2 ), (A2 , C1 ), (A2 , C2 )}
Ein Rückschluss von R1 , R2 und R3 nach R ist nicht möglich.
8
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 8
Wissenschaftliche Artikel werden unter anderem in speziellen Fachzeitschriften veröffentlicht. Betrachten Sie dazu das folgende vereinfachte Szenario:
• Ein Wissenschaftler hat einen Namen, eine Dienstadresse und ein Forschungsgebiet.
Er reicht Artikel alleine oder gemeinsam mit Kollegen solange zur Veröffentlichung
bei unterschiedlichen Zeitschriften ein, bis der Artikel veröffentlicht wird oder der
Wissenschaftler sein Vorhaben aufgibt.
• Ein Artikel erscheint in höchstens einer Zeitschrift. Dabei wird ein Artikel nur
veröfentlicht, wenn er zuvor von mehreren unterschiedlichen Wissenschaftlern begutachtet und für gut befunden wurde. Ein Artikel enthält gewöhnlich ein Literaturverzeichnis mit einer beliebigen Anzahl von Verweisen auf bereits früher veröffentlichte
Artikel.
• Eine Zeitschrift wird von einem oder mehreren Wissenschaftlern herausgegeben.
Ausgaben der Zeitschrift erscheinen mehrmals jährlich und jede dieser Ausgaben
ist genau einem Verlag zuzuordnen. Ein Verlag kann jedoch durchaus mehrere
Zeitschriften veröffentlichen, andererseits können verschiedene Ausgaben einer Zeitschrift bei verschiedenen Verlagen erscheinen.
(a) Arbeiten Sie alle Entitätstypen und zugehörige Attribute des obigen Beispiels heraus.
Bestimmen Sie zudem weitere erforderliche Attribute sowie Beziehungstypen und geben Sie zu jedem Entitätstyp einen Primärschlüssel an.
Diskriminator
Entitäten: Primärschlüssel, ::::::::::::::::
•
•
•
•
•
Wissenschaftler (Name, Dienstadresse, Forschungsgebiet
Zeitschrift (ISSN, Name)
Artikel (Titel, Erstdatum)
Verlag (Name, Adresse)
Ausgabe (::::::::::
Nummer, Augabejahr)
} schwache Entität
:::::::::::::
Beziehungen:
•
•
•
•
•
•
•
•
verfasst: Wissenschaftler - Artikel
begutachter: Wissenschaftler - Artikel
gibt-heraus: Wissenschaftler - Zeitschrift
verweist-auf: Artikel - Artikel (Literaturverzeichnis)
eingereicht-bei: Artikel - Zeitschrift
erscheint-in: Artikel - Ausgabe
von: Ausgabe - Zeitschrift
erscheint-bei: Ausgabe - Verlag
Julia Wolters
9
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
(b) Setzen Sie das Szenario in ein Entity–Relationship–Diagramm um.
Dienstadresse
Name
(0, n)
(0, n)
verfasst
verweist-auf
(0, n)
Forschungsgebiet
(0, n)
Wissenschaftler
gibt-heraus
(0, n)
(1, n)
istGut
begutachtet
Quelle
(0, n)
Referenz
(1, n)
(0, n)
Artikel
eingereicht-bei
(0, n)
Titel
Erstellungsdatum
(1, 1)
AusgabenNr
AusgabenJahr
von
(0, n)
Zeitschrift
ISSN
Ausgabe
Name
(0, 1)
(1, 1)
erscheint-in
SeitenzahlBeginn
(0, n)
SeitenzahlEnde
10
erscheint-bei
(0, n)
Verlag
Name
Adresse
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 9
Entwickeln Sie ein Entity–Relationship–Diagramm zur Modellierung eines kleinen Flughafens. Berücksichtigen Sie dabei zumindest folgende Informationen über die zu verwaltenden Daten des Flughafens:
• Der Flughafen besitzt mehrere Flugzeuge, welche jeweils eine eindeutige Registrierungsnummer besitzen. Jedes Flugzeug ist assoziiert mit einem festen Stellplatz in
einem Hangar, von welchem jeweils Nummer und Kapazität bekannt sind. Jedem
Flugzeug ist außerdem eindeutig ein Flugzeugtyp zugeordnet. Zu jedem Typ werden
spezifische Informationen wie Modellbezeichnung, Gewicht und Kapazität vermerkt.
• Ein Flughafen speichert die Daten mehrerer Personen (die über Name und Adresse
unterscheidbar sind und deren Geburtstage bekannt sind), u.a. die Daten von Piloten und Mitarbeitern. Letztere besitzen eine Personalnummer; zu einem Piloten
sollte seine Lizenznummer gespeichert werden und es sollte ersichtlich sein, welche
Flugzeugtypen er fliegen darf.
• Jedes Flugzeug muss gelegentlich gewartet werden. Das Datum, die Art und Dauer
einer Wartung, sowie das beteiligte Personal sollten vermerkt werden.
• Jedes Flugzeug hat mindestens einen Besitzer. Ein Besitzer kann dabei sowohl ein
Unternehmen als auch eine Privatperson sein.
Julia Wolters
11
Übung SS 2009
ModBezeichnung
Datenbanken
Musterlösungen
Kapazität
Gewicht
(0,n)
Flugzeugtyp
Mittwochs, 08 – 10 Uhr
Musterlösung
LizenzNr
darfFliegen
(1,n)
Name
Adresse
GebDatum
Pilot
(0,n)
o
Person
(0,n)
ist vom Typ
Datum
Dauer
PersonalNr
(1,1)
(0,n)
Flugzeug
wird gewartet
(1,1)
Wartung
RegNr
(1,n)
(0,n)
(1,1)
(0,n)
steht in
wartet
besitztP
besitztU
(0,n)
Mitarbeiter
(0,n)
(0,n)
Unternehmen
PlatzNr
Name
(0,n)
Nummer
Ort
Hangar
Kapazität
12
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 10
Betrachten Sie nochmals ternäre Relationen und deren Auflösung aus Aufgabe 7.
(a) Zeigen Sie, dass die gezeigte Auflösung der ternären Relation nicht äquivalent zur
ursprünglichen Relation ist.
ε = {e1 , e2 }
A = {a1 , a2 }
B = {b1 },
RA = {(e1 , a1 ), (e2 , a2 )},
RB = {(e1 , b1 )},
C = {c1 }
RC = {(e1 , c1 )}
(b) Geben Sie Constraints für die gezeigte Abbildung an, die gewährleisten, dass jede
Instanz von A, B, C, E, RA , RB , RC eine Entsprechung in der ursprünglichen ternären
Relation besitzt.
(c) Modifizieren Sie die Abbildung, um vollständige Teilnahme in der ternären Relation
darzustellen.
⇒
(d) Modifizieren Sie die Abbildung so, dass E zu einem schwachen Entitätstyp wird.
Julia Wolters
13
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 11
Ein System zur Tischreservierung in Restaurants sei wie folgt modelliert: Fügen Sie in
einem ersten Schritt die benötigten Attribute zum Diagramm hinzu. Gehen Sie nun von
der Situation aus, dass ein Stammgast seinen Lieblingstisch an mehreren Tagen reserviert
und geben Sie dafür explizit eine Beispielinstanz für die Beziehungsmenge reserviert an.
Welches Problem tritt auf und wie kann man es durch eine andere Modellierung lösen?
• temporäre Beziehung:
• zusätzlice Entität: Reservierung
Zusätzlicher Vorteil:
Man kann nur eine Reservierung in einem Zeitraum haben
14
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 12
Betrachten Sie das Konzept der Aggregation im E–R–Modell.
a) Beschreiben Sie das Konzept und typische Anwendungsgebiete der Aggregation im
Allgemeinen.
Behandeln von Beziehungen als abstrakte Entität ⇒ Beziehungen von Beziehungen“
”
b) Geben Sie zwei Beispiele an, in denen es sinnvoll ist, eine Aggregation bei der Erstellung
eines E–R–Diagramms anzuwenden.
c) Zeigen oder widerlegen Sie, dass sich jede Aggregation durch einen n–ären Beziehungstyp simulieren lässt. Falls die Aussage stimmt, gehen Sie auf möglicherweise entstehende Nachteile ein.
z.B. kann beide Aggregation nicht jede Buchung kontrolliert werden, das geht
bei dem ternären Beziehungen nicht.
d) Zeigen oder widerlegen Sie, dass sich jeder n–äre Beziehungstyp durch eine Aggregation
ersetzen lässt. Falls die Aussage stimmt, gehen Sie auf möglicherweise entstehende
Nachteile ein.
richtig, gezeigt durch Induktion
Julia Wolters
15
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Nachteil: viel mehr Beziehungstypen
16
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 13
Nehmen Sie an, dass sich zwei Firmen zu einer zusammenschließen. Beide Firmen besitzen
Datenbestände, die auf dem gleichen Entity-Relationship-Diagramm (Folie 56 aus Kapitel
2) beruhen. Welche Probleme können auftreten, wenn die bisher separat verwalteten Daten
in einer gemeinsamen Datenbank vereint werden sollen? Welche Lösungsmöglichkeiten
gibt es für diese Probleme?
• haben beide DBMS die gleiche Strucktur
• gleiche Personal-/ Abteilungs-/ Projekt Nr.
→ Nr neu vergeben
• gemeinsame Projekte
→ Dublikate entfernen (welche Abteilung erhält Projekt?)
• Angestellter in beiden Firmen
• Kinder mit Mutter in Firma 1 und Vater in Firma 2
→ Schema ändern
• Zusammenlegung der Abteilungen (wer wird Abteilungsleiter?)
Julia Wolters
17
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 14
Gehen Sie von einem einfachen Schema für eine Hoteldatenbank aus:
Hotel
Zimmer
Buchung
Gast
(HotelNr, HotelName, Stadt)
(ZimmerNr, HotelNr, Typ, Preis)
(HotelNr, GastNr, AnreiseDatum, AbreiseDatum, ZimmerNr)
(GastNr, GastName, GastAdresse)
Was wird durch die folgenden Ausdrücke der Relationalen Algebra abgefragt?
(a) ΠHotelN r (σP reis>50 (Zimmer))
Nr. aller Hotels mit Zimmerpreis > 50 ohne Dublikate
(b) σHotel.HotelN r=Zimmer.HotenN r (Hotel × Zimmer)
Alle Zimmer aller Hotels (mit jeweils der gleichen HotelNr.)
→ einfacher Join verwenden, Hoten 1 Zimmer
(c) ΠHotenN ame (Hotel 1Hotel.HotelN r=Zimmer.HotelN r. (σP reis>50 (Zimmer)))
Namen aller Hotels, die mindestens Zimmer haben mit Preis > 50
→ normaler Join würde ausreichen
(d) ΠZimmerN r (σHotelName=
Interconti“ (Hotel)
”
1 Zimmer 1 (σAnreiseDatum=01.06.2009 (Buchung)))
Nummer aller Zimmer in Hotels mit Namen Interconti“ für dieses eine Buchung mit
”
Anreisedatum 01.06.09 gibt.
Dublikate sind nicht erlaubt, werden bei gleicher ZimmerNr. in verschiedene Hotels
nur eine Nr. ausgegeben.
18
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 15
Betrachten Sie die Umsetzung des E-R-Diagramms für den Veröffentlichungsprozess wissenschaftlicher Artikel (Aufgabe 8 auf Blatt 2) auf folgendes Schema:
Wissenschaftler
Artikel
Ausgabe
Zeitschrift
Verlag
verfasst
begutachtet
verweist-auf
eingereicht-bei
erscheint-in
gibt-heraus
(Name, Dienstadresse, Forschungsgebiet)
(Titel, ErstDatum)
(AusgabenNr, AusgabenJahr, ZeitschriftISSN, VerlagName)
(ISSN, Name)
(Name, Adresse)
(Name, Dienstadresse, Titel, ErstDatum)
(Name, Dienstadresse, Titel, ErstDatum, istGut)
(QuellenTitel, QuellenErstDatum, ReferenzTitel, ReferenzErstDatum)
(Titel, ErstDatum, ZeitschriftISSN)
(Titel, ErstDatum, SeiteBeginn, SeiteEnde, AusgabenNr, AusgabenJahr, ZeitschriftISSN)
(Name, Dienstadresse, ZeitschriftISSN)
Geben Sie nun Ausdrücke der Relationalen Algebra an, die folgende Mengen als Ergebnisrelation besitzen.
Allgemeines Vorgehen:
1) Welche Relationen?
2) Datensätze?
hinschreiben, evtl. Join (bei unterschiedlichen Namen θ-Join)
Selektionen
3) welche Attribute?
Projektionen
Wie kann man optimieren? 4 eindeutige Namen
a) Alle Zeitschriften.
Zeitschrift
b) Die Namen aller Wissenschaftler, die bereits Artikel veröffntlicht haben.
ΠN ame (erscheint − in 1 verf asst)
c) Die Namen aller Wissenschaftler, die im Jahr 2000 gemeinsam mit Herrn Schmidt aus
München (Spezialgebiet Datenbanken) mindestens einen Artikel veröffentlicht haben.
Herr Schmidt
Artikel Schmidt ←− σName=
Schmidt“∧Dienstadresse= München“ (verf asst)
”
”
ΠN ame (verf asst − ArtikelSchmidt) 1 (ΠT itel,Erstdatum (
(ArtikelSchmidt 1 σAusgabejahr=2000 (erscheint − in))
{z
}
|
Alle Artikel die Herr Schmidt 2000 veröffentlicht hat
Julia Wolters
19
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
d) Die Namen aller Verlage, die jemals die Datenbanken-Zeitung“ herausgegeben haben.
”
ΠV erlagN ame (Ausgabe 1Zeitschrif tISSN =ISSN σName=
Datenbank-Zeitung“ (Zeitschrif t)
”
e) Verlagsname, Seitenzahlen und Autorenname, die zu einem im Jahr 1999 in der Datenbanken”
Zeitung“ veröffentlichten Artikel Geschichte von Datenbankensystemen“ gehören.
”
Name der Zeitung Datenbank-Zeitung“
Zeitschrift
”
AusgabeJahr 1999
Ausgabe, erscheint-in
Titel des Artikels
verfasst
(GeschichteArtikel ← σTitel= Geschichte von Datenbanksystemen“ (verf asst)
”
1 σAugabeJahr=1999 (erscheint − in)
1 ΠAusgabeN r,AusgabeJahr,Zeitschrif tISSN,verlagN ame (
Ausgabe 1Zeitschrif tISSN =ISSN σName= Datenbank-Zeitung“ (Zeitschrif t))
”
ΠV erlagN ame,SeiteBeginn,SeiteEnde,N ame (GeschichteArtikel)
f) Die Namen aller Wissenschaftler, die diesen Artikel begutachtet haben.
ΠN ame (Begutachtet 1 ΠT itel,erstdatum (GeschichteArtikel)
g) Die ISSN und den Verlagsnamen aller Zeitschriften mit Artikeln, die auf diesen Artikel
verweisen.
verweise
verweist-auf
ISSN,VerlagName
Ausgabe
ΠV erlagN ame,Zeitschrif tISSN (ΠQuellenT itel,QuellenErstdatum (verweist − auf )
1 Ref erenzT itel = T itel ∧ Ref erenzErstdatum = Erstdatum(GeschichteArtikel)
1 QuellenT itel = T itel ∧ QuellenErstdatum = Erstdatum
1 Ausgabe)
20
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 16
Gegeben seien V
zwei Relationenschemata R und S, Relationen r(R) und s(S) sowie ein
Prädikat θ =
(r.A = s.A). Beschreiben Sie allgemein die Schemata und ErgebnisreA∈R∩S
lationen für
(a) r × s
• besteht (in dieser Reihenfolge) aus allen Attributnamen aus r, sowie
allen Attributnamen aus S
• tritt ein Attributname in R als auch in S auf, so wird dieser in rA und sA
umbenannt
Ergebnisrelation: Kreuzprodukt aller Tupel aus r und s
Schema:
(b) r 1 s = ΠR∪S (σθ (r × s))
Schema: wie (a), alle doppelten Attributnamen werden entfernt
Ergebnisrelation: die Teilmenge des Kreuzproduktes, die θ erfüllt (Untersuchung von
Mehrfachnennung von Attributen wergen Π)
(c) r 1θ s = σθ (r × s)
Schema wie (a)
Ergebnisrelation: wie bei r 1 s, nur ohne die Unterschlagung der Mehrfachnennung
Gilt nur für Prädikate wie in der Aufgabenstellung!
Julia Wolters
21
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 17
Gegeben seien zwei Relationen r(R) und s(S) mit |r| = n > 0 sowie |s| = m > 0 Elementen. Geben Sie für die folgenden Ausdrücke an, wieviele Elemente die Ergebnisrelation
mindestens und höchstens enthalten kann. Die Schemata R und S seien dabei so de
niert, dass alle Ausdrücke gültig sind.
(a) πX (r)
(b) r ∪ s
(c) r ∩ s
(d) r × s
(e) r 1 s
(f) σP (r × s)
Mindestens
1
max(n,m)
0
n·m
0
0
(g) r ÷ s
0
höchstens
n
n+m
min(n,m)
n·m
n·m
n
·m
jn
k
m
r 1 s= σθ (r × s) wie (f)
(h) r 1θ πS (s × (r × s))
22
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 18
Gegeben seien zwei Relationenschemata R und S sowie Relationen r(R) und s(S). Unter
welchen Voraussetzungen liefern die beiden Ausdrücke in den folgenden Aufgabenteilen
jeweils identische Resultatrelationen?
attrn(P ) = in der Formel P auftretende Attributnamen
Y ⊕ Z = Schema, das aus dem kartesischen Produkt von Relationen zu Schemata Y bzw.
Z entsteht.
(a) X = Y ⊕ Z ∧ attrn(θ) ⊆ Y ⊕ Z
(b) attrn(P ) ⊆ X
(c) attrn(P ) ⊆ R ∩ (R ∩ S) = ∅
(d) attrn(P ) ⊆ S ∩ (R ∩ S) = ∅
(e) attrn(P ) ⊆ R ∩ S
(f) gilt immer
(g) X = Y ∧ domY = domZ ∧ attrn(P ) ⊆ Y
(h) attrn(P ) ⊆ R ⊕ Y ∧ attrnθ ⊆ R ⊕ Y ∧ X = R ⊕ Y
Julia Wolters
23
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 19
Seien im Folgenden r(R), s(S) und t(T) Relationen über den Schemata R, S und T. Dann
heißen r n s := πR (r 1 s) Semijoin von r mit s“ und rns := r − πR (r 1 s) Antisemijoin
”
”
von r mit s“ Zeigen oder widerlegen Sie nun die Allgemeingültigkeit folgender Aussagen.
(a) r 1 s, s 1 r verursachen unterschiedliche Reihenfolge, die ΠR∪S aber wieder vereinfachticht
(b) r − (rns) = r − (r − ΠR (r 1 s)) = ΠR (r 1 s) = r n s
(c) r n s = ΠR (r 1 s) = ΠR∩R (r) 1 ΠR∩S (s)
| {z }
=r
(d) (r n s) 1 s = r 1 ΠR∩S (s) o s = r o s
|
{z
}
=s
(e) (r n s) 1 (s n (r n s)) = r 1 ΠR∩S (s) 1 ΠR∩S (ΠR (r × s)) = r 1 s 1 ΠR∩S (r 1 s) =
r1s
(f) nicht allgemein gültig
Gegenbeispiel: R = (A,B), S = (B,C)
r = {(1, 1)}, s = {(1, 2), (2, 3)}
zz: ΠS (r × (s n r)) = ΠS (s × (r n s))
Links:
s n r = s 1 ΠS (s 1 r) = s 1 s = s = {(1, 2)}
r × (s n r) = {(1, 1), (1, 2)}
ΠS (r × (s n r)) = {(1, 2)}
Rechts:
r n s = {(1, 1)}
s × (r n s) = {(1, 2, 1, 1), (2, 3, 1, 1)}
ΠS (s × (r n s)) = {(1, 2), (2, 3)}
(g) nicht allgemein gültig
Gegenbeispiel: R = S = {A}, T = {B}
24
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
s = {(1)}, t = {(3)}, r = {(2)}
Rechts:
Links:
s×t
(s × t) n r
ΠS ((s × t) n r)
s ∩ ΠS ((s × t) n r)
r × t = {(2, 3)}
sn(r × t) = {(1)} = s
ΠS (sn(r × t)) = {(1)}
(h) r ∩ (r − (rns)) = r − (rns) = r − (r − ΠR (r 1 s)) = r − (r − (r n s))
|
{z
}
⊂r
Julia Wolters
25
=
=
=
=
{(1, 3)}
{}
{}
{}
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 20
Seien R=(A,B,C) ein Schema und r(R) und s(R) Relationen. Geben Sie zu folgenden
Ausdrücken der Relationalen Algebra jeweils äquivalente Ausdrücke des Tupelorientierten
Relationalen Kalküls an:
(a) πA (r) = {t | ∃q ∈ r(t[A] = q[A]}
(b) σB=42 (r) = {t | t ∈ r ∧ t[B] = 42}
(c) r ∪ s = {t | t ∈ r ∨ t ∈ s}
(d) r ∩ s = {t | t ∈ r ∧ t ∈ s}
(e) r − s = {t | t ∈ ∧¬(t ∈ s)}
(f) πA,B (r) 1 πB,C (s) = {t | ∃m ∈ r(∃n ∈ S(t[A] = m[B] ∧ t[B] = m[B] ∧ m[B] =
n[B] ∧ t[C] = n[C]))}
(g) r ÷ πC (s) = {t | ∀u ∈ S(∃w ∈ r(t[A] = w[A] ∧ t[B] = w[B] ∧ w[C] = u[C]))}
(h) πr.A (r 1r.B>s.B s) = {t | ∃m ∈ r(t[A] = n[A] ∧ m[B] > n[B]))}
26
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 21
Seien R=(A,B) und S=(A,C) Schemata sowie r(R) und s(S) Relationen. Geben Sie Ausdrücke der Relationalen Algebra an, die äquivalent zu den folgenden Ausdrücken des
Tupelorientierten Relationalen Kalküls sind:
(a) {t | ∃q ∈ r(t[A] = q[A] ∧ q[B] < 58)} ⇔ πA (σB<58 (r))
(b) {t | ∃q ∈ r, v ∈ s(t[A] = q[A] ∧ q[A] = v[A] ∧ t[B] = q[B] ∧ t[C] = v[C])g ⇔ r 1 S
(c) {t | ∃q1 , q2 ∈ r, v ∈ s(t[A] = v[A] ∧ q1 [A] = t[A] ∧ q2 [A] = v[C] ∧ q1[B] > q2 [B])} ⇔
πs.A (σs.c=r2 .A∧s.A=r1 .A∧r1 .B>r2 .B (s × pr1 (r) × pr2 (r)))
Julia Wolters
27
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 22
Untersuchen Sie, ob die folgenden Anfragen im Werteorientierten Relationalen Kalkül
sicher sind. Begründen Sie jeweils Ihre Aussage. Geben Sie zudem die Domäne der jeweiligen Anfrage an. dom(P): Werte in Relation in P, expliziete Werte in P
sicher: falls
(1)
(2)
(3)
alle Werte des Ergebnistupels liegen in dom(P)
∃xP1 (x)“: ∃ Wert aus dom(P1 ) der (P1 (x)) erfüllt ⇔ ∃x(P1 (x))
”
∀xP1 (x)“: ∀ Werte aus dom(P1 ) ist (P1 (x)) erfüllt ⇔ ∀x(P1 (x)) erfüllt ist.
”
(a) {hai| a = 0}
dom(P ) = {0} (expliziter Wert a = 0)
⇒ a = 0 ∈ dom(P ) (kein ∃“ oder ∀“ daher muss nur (1) erfüllt sein. ⇒ Anfrage
”
”
sicher
(b) {ha, b, ci|(a = c) ∧ (b = c)}
dom(P ) = {} (keine Relation, keine explizieten Werte)
⇒a∈
/ dom(P ) (b und c auch nicht) ⇒ Anfrage nicht sicher
(c) {hi, n, s, ci|hi, n, s, ci ∈ customer}
dom(P ) = values(customer)
⇒ i, n, s, c ∈ dom(P )
⇒ Anfrage sicher
(d) hai|b(ha, bi ∈ account ∨ (b > 0))}
dom(P ) = values(account) ∪ {0}
⇒ z.B. account = {}
⇒ a 6= dom(P )
⇒ Anfrage unsicher
(e) {hli|∀a(¬(hl, ai ∈ loan) ∧ (a > 500))}
dom(P ) = values(loan) ∪ {500} = dom(P1 ) = (¬(hl, ai ∈ loan ∧ (a > 500))
∀a(P1 ) immer falsch, wegen a = 500
⇒ Resultat ist leer
⇒ Anfrage sicher
(f) + (g) {hl, ni|∃a(hl, ai ∈ loan) ∨ ∃i, s, c(hi, n, s, ci ∈ customer)}
{hl, ni|∃a(hl, ai ∈ loan) ∧ ∃i, s, c(hi, n, s, ci ∈ customer)}
dom(P ) = values(loan) ∪ values(customer)
28
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
zu 1) Alle Komponenten von hl, ni ∈ dom(P )
zu 2) ∃a : hl, ni ∈ loan erfüllt ⇔ ∃ Tupel hl, ai ∈ loan, das hl, ai ∈ loan erfüllt.
∃i, s, chi, n, s, ci = customer erfüllt ⇔ ∃ Tupel λi, n, s, ci ∈ customer das hi, n, s, ci ∈
customer erfüllt
Julia Wolters
29
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 24
Superschlüssel: bestimmt eindeutig
Kandidatenschlüssel: minimaler Superschlüssel
Es sei r(R) eine Relation über dem Schema R. Zeigen oder widerlegen Sie:
(a) Sind K1 und K2 Kandidatenschlüssel für r, so auch K1 ∪ K2 .
K1 =
6 K2
K1 ( K1 ∪ K2
K1 Superschlüssel ⇒ K1 ∩ K2 nicht minimal, also kein Kandidatenschlüssel
(b) Sind K1 und K2 Kandidatenschlüssel für r, so auch K1 ∩ K2 .
K1 6= K2
K1 ∩K2 ( K1 ⇒ K1 ∩K2 kein Superschlüssel, also erst recht kein Kandidatenschlüssel
(c) Sind K1 und K2 Superschlüssel für r, so auch K1 ∩ K2 .
zz. ∀r(R) ∀t1 , t2 ∈ r:
ΠK1 ∪K2 (t1 ) = ΠK1 ∪K2 (t2 ) ⇒ t1 = t2
ΠK1 ∪K2 (t1 ) = ΠK1 ∪K2 (t2 ) ⇒ ΠK1 (t1 ) = ΠK2 (t2 ) ⇒ t1 = t2
(d) Sind K1 und K2 Superschlüssel für r, so auch K1 ∪ K2 .
Gegenbeispiel: R = (A,B,C,D)
K1 = (A), K2 = (B) Superschlüssel
K1 ∩ K2 = {} ist kein Superschlüssel
30
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 25
Funktionale Abhängigkeit:
α → β gilt auf R
⇔ ∀r(R) ∀t1 , t2 ∈ r : t1 [α] → t2 [α]
⇒ t1 [β] = t2 [β]
Erläutern Sie, wie mit Hilfe von funktionalen Abhängigkeiten die folgenden Einschränkungen ausgedrückt werden können:
Sei P K(r) der Primärschlüssel der Relation r
a) Eine 1:1-Beziehung existiert zwischen den Entitätsmenge account und customer.
PK(account) → PK(customer)
PK(customer) → PK(account)
b) Eine n:1-Beziehung existiert zwischen den Entitätsmenge account und customer.
PK(customer) → PK(account)
Julia Wolters
31
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 26
Betrachten Sie das folgende relationale Schema:
FAMILIE(Kind, Kind-GebJahr, Mutter, Mutter-GebJahr,
Vater, Vater-GebJahr, Hochzeitsjahr)
In Relationen zu diesem Schema seien für die darin enthaltenen Daten die folgenden
(vereinfachten) Annahmen gültig:
• Jede Person ist eindeutig identi
ziert durch ihren Namen, der als Attributwert vermerkt ist (in den Spalten Kind,
Mutter oder Vater).
• Werte in Kind-GebJahr, Mutter-GebJahr, Vater-GebJahr bezeichnen das Geburtsjahr der jeweiligen Person.
• Ein Paar (bestehend generell aus Mutter und Vater) kann sich nur ein einziges Mal
gegenseitig heiraten.
• Eine Person kann mehrfach heiraten, aber wegen der vorgeschriebenen Trennungszeit nicht mehrere Male in ein und demselben Jahr.
• Eine Person kann beliebig viele Kinder haben.
Folgern Sie aus diesen Annahmen nun die wesentlichen funktionalen Abhängigkeiten die
auf dem Schema FAMILIE gelten.
Kind → Kind GebJahr
Mutter → Mutter GebJahr
Vater → Vater GebJahr
Mutter, Vater → Hochzeitsjahr
Mutter, Hochzeitsjahr → Vater
Vater, Hochzeitsjahr → Mutter
32
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 27
Armstrong Axiome A1: Reflexivität: β ⊆ α ⇒ α → β
A2: Erweiterung: α → β ⇒ αγ → αγ
A3: Transititvität: α → β ∧ β → γ ⇒ α → γ
Kommutativ: α → βγ ⇔ αγβ
Beweisen Sie unter Benutzung der ArmstrongAxiome oder widerlegen Sie die Gültigkeit
folgender Regeln für funktionale Abhängigkeiten für ein beliebiges Relationenschema R
mit α, β, γ, δ ⊆ R:
a) Vereinigungsregel: (α → β ∧ α → γ) =⇒ (α → βγ)
)
α
z}|{
α → β A2 αα → βα A3
⇒
⇒ α → βγ
α → γ
αβ → γβ
b) Schnittregel: (αβ → δ ∧ βγ → δ) =⇒ (β → δ)
falsch, Gegenbeispiel:
A B C D
2 1 2 3 α = (A), β = (B), γ = (C), δ = (D)
3 1 3 4
c) Dekompositionsregel: (α → βγ) =⇒ (α → β ∧ α → γ)
α → βγ ∧
β ⊆ βγ A1 βγ → β A3 α → β
⇒
⇒
γ ⊆ βγ
βγ → γ
α→γ
d) Differenzregel: (α → β ∧ γ → δ) =⇒ ((α − γ) → (β − δ))
falsch, Gegenbeispiel:
A B C D α = (AB), β = (CD), γ = (B), δ = (D)
2 1 1 1
α − γ = (A)
6=
β − δ = (C)
2 2 2 2
e) Pseudosymmetrieregel: (α →∵ ∧γ → β) =⇒ (α → γ)
falsch, Gegenbeispiel:
A B C
1 2 1 α = (A), β = (B), γ = (C)
1 2 2
f) Pseudotransitivitätsregel: (α → β ∧ γβ → γ) =⇒ (αγ → δ)
α → β A2 αγ → βγ A3
⇒
⇒ αγ → δ
γβ → C
γβ → δ
Julia Wolters
33
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 28
Das Schema R = (A,B,C,D,E) werde zerlegt in (A,B,C) und (A,D,E). Zeigen Sie, dass
dies eine verlustlose Zerlegung darstellt, falls die folgenden funktionalen Abhängigkeiten
auf R gelten:
A → BC CD → E B → D E → A
Zerlegung: R = R1 ∪ R2
Verlustlos: r = ΠR1 (r) ./ ΠR2 (r)
(Hinreichendes Kriterium (HK)) R1 ∩ R2 → R1 ∨ R1 ∩ R2 → R2 ∈ F +
Abhängigkeitserhaltend: (F1 ∪ R2 )+ = F +
R
R1
R1 ∪ R2
A+
=
=
=
=
(A, B, C, D, E)
(A, B, C)
R2 = (A, D, E)
(A)
R ⊆ R1 ⇒ verlustlose Zerlegung
34
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 29
Gegeben seien ein Schema R=(A,B,C,D,E) und eine Menge funktionaler Abhängigkeiten
F = {A → BC, A → D, CD → E, B → D, E → A}.
R = (A, B, C, D, E)
F = {A → BC, A → D, CD → E, B → D, E → A}
a) Bestimmen Sie (BD)+ und F + .
BD+ = {B, D}
Superschlüssel bestimmen“:
”
A+ = R, CD+ = R, B + = {B, D}, E + = R, C + = {C}, D+ = {D}, BC + = R
Wo K + = R ist K Kandidatenschlüssel
F + = {α → β, αA → γ, αCD → γ, αE → γ, αBC → γ, αB → D | β ⊆ α ⊆ R, ∅ =
6 γ ⊆ R}
b) Sind folgende Zerlegungen von R abhängigkeitserhaltend und verlustlos?
(i) (A,B,C), (A,D,E)
R1 = (A, B, C), R2 = (A, D, E)
R1 ∩ R2 = (A), A+ = R ⊆ R1 ⇒ verlustlos
B → D ∈ F + aber (B → D) ∈
/ (F1 ∪ F2 )+
(ii) (A,B,C), (C,D,E))
R1 = (A, B, C), R2 = (C, D, E)
R1 ∩ R2 = (C) C + = {C} (HK ist erfüllt)
A
0
1
B
0
1
|
{z
R1
C
0
0
D
0
1
E
0
1
6=
}
|
ΠR1 (r) ./ ΠR2 (r)
A B C D E
0 0 0 0 0
0 0 0 0 1
1 1 0 0 0
1 1 0 1 1
{z
R2
}
⇒ keine Verlustlosigkeit
wie (i): B → D in F + aber (F1 ∪ F2 )+
Julia Wolters
35
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
(iii) (A,B,C,E), (B,D)
R1 = (A, B, C, E), R2 = (B, D)
R1 ∩ R2 = (B) B + {B, D} ⊇ R2 ⇒ verlustlos
CD → E ∈ F + aber nicht in (F1 ∪ F2 )+
⇒ nicht abhängigkeitserhalten
(iv) (A,B,C), (A,C,E)
R1 = (A, B, C), R2 = (A, C, E)
R1 ∪ R2 6= R ⇒ keine Zerlegung
36
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 30
Definitionen:
Was ist BCNF:
∀α → β ∈ F + gilt
(1)
α → β trivial, dh. β ⊆ α
(2) α ist Superschlüssel, dh. α+ = R
Was ist 3NF:
∀α → β ∈ F + gilt
(3)
(1), (2) oder
Jedes Attribut in β − α ist in Kandidatenschlüssel entalten.
Gegeben seien wie in Aufgabe 29 ein Schema R=(A,B,C,D,E) und eine Menge funktionaler
Abhängigkeiten F = {A → BC, A → D, CD → E, B → D, E → A}.
F + = {α → β, αA → γ, αE → γ, αCD → γ, αBC → γ, αB → βD | β ⊆ α ⊆ R, ∅ =
6 γ ⊆ R}
a) Geben Sie eine verlustlose BCNF–Zerlegung für R bezüglich F an. Ist diese abhängigkeitserhaltend?
Algorithmus
result = {R}
done
= false
berechne F +
while not done
if Ri in result nicht BCNF then
α → β die BCNF-Bedingung verletzt
result = (result - Ri ) ∪ ( Ri → β ) ∪ { α, β }};
else
done = true;
result = {R}, done = false, denn B → D nicht trivial und B kein Superschlüssel
→ Ri = R betrachte B → D
result = {(result − R) ∪ Ri − β ∪ {α, β}}
{z
} | {z } | {z }
|
{A,B,C,E}
∅
{B,D}
= {A, B, C, E} ∪ {B, D}
→
R1
→ in BCNF
= {A, B, C, E}
R2
→ in BCNF B Superschlüssel
= {B, D}
→ done := true
Wir haben Zerlegungen gefunden, dies ist verlustlos, aber nicht abhängigkeitserhaltent
(29.b.iii))!
Julia Wolters
37
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
b) Geben Sie ein Beispiel für ein relationales Schema R0 und eine Menge funktionaler
Abhängigkeiten F 0 an, sodass mindestens zwei verschiedene BCNF–Zerlegungen von
R bezüglich F existieren.
R0 = (A, B, C, D), F 0 = {A → B, C → B, B → C}
B ist kein Superschlüssel → Zerlegung notwendig
Z1 = {(A, B), (C, D), (B, D)}
Z2 = {(A, B), (C, D), (A, D)}
c) Bestimmen Sie Fc .
F ist F ohne überflüssige Attribute“
” c
α→β∈F
A ∈ α überflüssig in α ⇔ β ⊆ (α − {A})+ aus F
B ∈ β überflüssig in β ⇔ B ∈ α+ aus F 0 = {F − {α → β} ∪ {α → (β − B)}}
B → D, E → A }
CD
→ E},
F = { A → BC, A → D,
| {z
{z
}
|
|
C
D
→E
C + =C+E D+ =D+E
z
}|
{
A→
B
C
D
+
+
/
B ∈A
/
=ACDE C ∈A+=ABD
D∈A =ABCDED
Da D∈A+ ist D überflüssig.
F = {A → BC, CD → E, B → D, E → A}
d) Geben Sie eine verlustlose 3NF–Zerlegung für R bezüglich F an. Ist diese abhängigkeitserhaltend?
R bereits in 3NF
B → D erfüllt (3), denn D in Kandidatenschlüssel CD enthalten.
Algorithmus für 3NF-Zerlegung:
Für jede funktionale Abhängigkeit in Fc ein Schema, eins davon muss Kandidatenschlüssel enthalten.
FC
= {A → CV, CD → E, B → D, E → A}
⇒ {{A, B, C}, {C, D, E}, {B, D}, {A, E}}
ist 3-NF Zerlegung, dies ist verlustlos und abhängigkeitserhaltend
38
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 31
Im Jahr 1962 wurden in der Bundesrepublik Deutschland vierstellige Postleitzahlen eingeführt, die am 01.07.1993 durch fünfstellige Postleitzahlen ersetzt wurden. Informieren
Sie sich gegebenenfalls über die Einzelheiten und bearbeiten Sie die folgenden Aufgaben
unter Vernachlässigung der Besonderheiten für Postfach- und Großkunden. Gehen Sie zur
weiteren Vereinfachung davon aus, dass eine Postleitzahl höchstens einem Ort zugeordnet
ist.
a) Definieren Sie jeweils entsprechende Schemata, die alle erforderlichen Attribute für den
praktischen Einsatz von vier- und fünfstelligen Postleitzahlen besitzen.
Ralt = (Ort, P LZ)
Rneu = (Ort, Stadtteil, Straße, HausN r, P LZ)
b) Bestimmen Sie den Abschluss der funktionalen Abhängigkeiten über diesen Schemata.
+
Falt
= {α → β, Ortα → γ, P LZα → γ | β ⊆ α ⊆ Ralt , ∅ =
6 γ ⊆ Ralt }
+
Fneu = {α → β, (Ort, Stadtteil, Straße, HausN r)α → γ, P LZα → Ortβ | β ⊆ α ⊆
Rneu , ∅ =
6 γ ⊆ Rneu }
c) Bestimmen Sie eine kanonische Überdeckung der funktionalen Abhängigkeiten dieser
Schemata.
Faltc = {Ort → P LZ, P LZ → Ort}
Fneuc = {(Ort, Stadtteil, Straße, HausN r) → P LZ, P LZ → 0rt}
d) Bestimmen Sie mit Begründung den Typ der besten Normalform, in der sich Ihre
Schemata be
nden.
+
in BCNF
Ralt ist bzgl. Falt
α → β, trivial
Ortα → γ, Ortα ist Superschlüssel
P LZα → γ, P LZα ist Superschlüssel
+
Rneu ist bzgl. Fneu
in 3NF.
α → β, trivial
(Ort, Stadtteil, Straße, HausN r)α ist Superschlüssel
P LZα → Ortβ
P LZα ist kein Superschlüssel, α ist nicht trivial
⇒ nicht in BCNF
Ort ist in Kandidatenschlüssel (Ort, Stadtteil, Straße, HausN r) ⇒ in 3NF
Julia Wolters
39
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
e) Zerlegen Sie Ihre Schemata gegebenenfalls, um möglichst gute abhängigkeitserhaltende
Zerlegungen zu erhalten.
Ra lt ist bereits in bester“ Normalform
”
Behauptung: Es gibt keine abhängigkeitserhaltende Zerlegung von Rneu bzgl. Fneu ,
die besser“ als 3NF sind.
”
/ Fi ∀i
Beweis: (Ort, Stadtteil, Straße, HausN r) → P LZ ∈
⇒ (Ort, Stadtteil, Straße, HausN r) ∈
/ (F1 ∪ . . . ∪ Fn )+
+
keine nicht triviale Abhängigkeit α → β mit P LZ ∈
Außerdem existiert in Fneu
β ∧ P LZ ∈
/α ⇒A∈
/ (F1 ∪ . . . ∪ Fn )+
aber A ∈ F +
Jede Zerlegung ist also nicht abhängigkeitsherhaltend.
40
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 32
Gegeben sei das Schema einer einfachen Büchereidatenbank:
Bücher (BuchNr, ISBN, Titel, Autor)
Ausleihen (AusleihNr, LeserNr, LeserName, LeserGebDatum, BuchNr)
Beachten Sie dabei, dass von einem Buch durchaus mehrere Exemplare vorhanden sein
können.
(a) Erläutern Sie die Nachteile dieses Schemas.
Nachteile:
• Redundanz
– ISBN, Titel, Autor bei mehreren Exemplaren eines Buchs
– LeserName, LeserGebDatum bei jeder Ausleihe
• Verbessern Sie das Schema, indem Sie eine BCNF-Zerlegung berechnen. Stellen
Sie dazu die funktionalen Abhängigkeiten auf und wenden Sie dann den BCNFAlgorithmus ohne Berechnung von F + auf ein Schema an, das alle Attribute von
Bücher und Ausleihen enthält.
Leser kann ohne Ausleihe nicht existieren
(b) Algorithmus ohne F + für BCNF result = {R}
done in BCNF?
while (not done) do
done := true
for each Ri in result do
if(∃α ⊆ Ri mit α+ 6= α und Ri − α 6= ∅) then
β := (α+ − α) ∩ Ri
result = {( result -Ri ) ∪ {Ri − β} ∪ {αβ}}
done := false
F = { LeserNR
ISBN
BuchNR
AusleihNr
⇒
⇒
⇒
⇒
LeserName, LeserGebDatum;
Titel, Autor;
ISBN;
LeserNr, BuchNr }
result = Rn − {BuchNr, ISBN, Titel, AusleihNr, ...}
,→ done = false; z.B. BuchNr. → ISBN
done = true; Ri = R
α = BuchNr (α+ = { BuchNr, ISBN, Titel, Autor })
Julia Wolters
41
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
β = {α+ − α} ∩ Ri = { ISBN, Titel, Autor }
result = {AusleihN r, LeserN r, LeserN ame, LeserGebDatum, BuchN r}
∪{BuchN r, ISBN, T itel, Autor}
done = false
,→ Betrachte R1 := { AusleihNr, LeserNr, LeserName, LeserGebDatum, BuchNr }
α = LeserNr (α+ = { LeserNr, LeserName, LeserGebDatum })
β = { LeserName, LeserGebDatum }
result := {BuchN r, ISBN, T itel, Autor} ∪ {AusleihN r, LeserN r, BuchN r}
|
{z
} |
{z
}
R2
R3
∪{LeserN r, LeserN ame, LeserGebDatum}
|
{z
}
R4
,→ Betrachte R2 = {BuchN r, ISBN, T itel, Autor}
α = ISBN (α+ = { ISBN, Titel, Autor })
β = { Titel, Autor }
result := R3 ∪ R4 ∪ {BuchN r, ISBN } ∪ {ISBN, T itel, Autor}
{z
}
|
R5
Zerlegung ist R3 ∪ R4 ∪ R5
42
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 33
zu zeigen: M4 ( MBC ( M3 ( M1
Beweis:
M4 ( MBC M4 ⊆ MBC : R in 4NF: A: R nicht in BCNF ⇒ ∃α → β nicht trivial, α kein
Replikationsregel
Superschlüssel für R
=⇒
∃α →→ β nicht trivial, α kein Superschlüssel für
R ⇒ R nicht in 4NF M4 6= MBC : R = (A, B, C, D), D)(A →→ BC), R in BCNF, da alle funktionalen
Abhängigkeiten trivial, R nicht in 4NF, weil A →→ BC nicht trivial und A →→ BC
kein Superschlüssel
MBC ( M3 MBC ⊆ M3 : R in BCNF ⇒ ∀α → β ∈ F + α → β trivial, α Superschlüssel
⇒ ∀α → β ∈ F + α → β trivial oder α Superschlüssel oder ∀A ∈ β − α, A ∈ K, K
Kandidatenschlüssel ⇒ in 3NF
MBC 6= M3 : R = (A, B, C, D), F = (AD → CD, C → B), F + = {α → β, ABα →
β, Cα → Bβ|β ⊆ α ⊆ R, ∅ =
6 γ ⊆ R}
α → β trivial ABα → γ ⇒ ABα Superschlüssel Cα → Bβ ⇒ nicht in BCNF,
nicht trivial, Cα kein Superschlüssel ⇒ in 3NF, weil Bβ − Cα = B und B in AB
Kandidatenschlüssel enthalten.
M3 ( M1 M3 ⊆ M1 : klar, da nach Definition alle Relationen, die wir betrachten, in 1NF
sind.
M3 6= M1 : R = (A, B, C, D), F = {A → D, B → D, C → AB},
R in 1NF klar, B → D ∈ F + , nicht trivial, B kein Superschlüssel und D − B = D (
C Kandidatenschlüssel ⇒ R nicht in 3NF
Julia Wolters
43
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 34
zz Es gibt Joinabhängigkeiten, die nicht äquivalent zu einer mehrwertigen Abhängigkeit
sind.
R = (A, B, C) ∗ ((A, B), (B, C), (A, C))
A B C
1 3 6
2 3 5
1 4 5
1 3 5
Dies gilt, wenn r = Π(R1 (r)) 1 Π(R2 (r)) 1 Π(R3 (r))
A B
B C
1 3
3 6
ΠR1 (r) 1 ΠR2 (r):
2 3
3 5
4 5
1 4
A
1
2
1
1
B C
A
3 6
1
3 5 1
2
4 5
1
3 5
A B
C
1 3
5
= 2 3
5
1 4
6
1 3
C
6
5
5
5
R in 3 nicht leeren Teilmengen, Y, Z, W, partioniert, Y →→ Z gilt ⇔ ∀r(R) hy1 , z1 , w1 i ∈
r ∧ hy1 , z2 , w2 i ∈ r ⇒ hy1 , z1 , w2 i ∈ r ∧ hy1 , z2 , w1 i ∈ r
A →→ B : h1, 3, 5i ∈ r ∧ h1, 4, 5i ∈ r
3, 5i ∈ r ∧ h1, 4, 6i ∈ R ⇒ gilt nicht auf R
⇒h1,
Es gibt eine Normalform, die besser“ ist als 4NF
”
Projekt-Join-Normalform (PJNF, 5NF)
R ist PJNF bzgl. einer Menge funktionaler mehrwertige und Joinabhängigkeiten ⇔ ∀ ∗
(R1 , . . . , Rn ) mit Ri ⊆ R, R = R1 ∪ . . . Rn gilt
(1)
∗(R1 , . . . , Rn ) trivial oder
(2) jedes Ri ist Superschlüssel für R
MP JN F ( M4N F lässt sich nicht weiterverkleinern, ohne dass Informationen verloren
gehen.
44
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 35
(a) Nachteile des Schema KIGA
• Hohe Redundanz
(pro Kind z.B. Speicherung von Spiel, Tag, Zeit, Raum)
⇒ unübersichtlich, hoher Sortier- und Speicheraufwand und vor allem Aktualistätsaufwand
• Ein Kind ohne Gruppe kann nicht repräsentiert werden
(b) F + = {α → β, (N ame, V orname, GebDatum)α → (Gruppe, Raum)β, Kindα →
Raumβ, Kindα → Gruppeβ, (T ag, Zeit)α → (Spiel, Gruppe, Raum)β, (T ag, Zeit)α →
(Spiel, Raum)β, (T ag, Zeit)α → (Spiel, Gruppe)β, (Kind, T ag, Zeit) → γ}.
Gruppe →→ Spiel, Tag, Zeit
Gruppe →→ Name, Vorname, GebDatum, Raum
Gruppe, Raum →→ Spiel, Tag, Zeit
Gruppe, Raum →→ Name, Vorname, GebDatum
KIGA ist nicht in 3NF.
Gruppe → Raum nicht trivial, Gruppe kein Superschlüssel, Raum – Gruppe = Raum
in keinem Kandidatenschlüssek enthalten ⇒ KIGA ist 1NF
(c) Wende 4NF-Algorithmus an:
1. result = {KIGA}
betrachte Gruppe →→ Spiel, Tag, Zeit
Julia Wolters
45
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
2. result = {(Gruppe, Raum, Kind), (Spiel, Tag, Zeit, Gruppe)}
betrachte Gruppe →→ Name, Vorname, GebDatum
3. result = {(Gruppe, Kind),(Gruppe, Raum), (Spiel, Tag, Zeit, Gruppe)}
ER-Diagramm:
1 −− urspruengliches KIGA−Schema
2 CREATE TABLE kiga (
3
Name CHAR ( 1 5 ) ,
4
Vorname CHAR ( 1 5 ) ,
5
GebDatum CHAR ( 1 0 ) ,
6
Gruppe INTEGER NOT NULL ,
7
Raum INTEGER NOT NULL ,
8
Tag CHAR ( 1 2 ) ,
9
Zeit CHAR ( 6 ) ,
10
Spiel CHAR ( 1 2 ) NOT NULL ,
11
PRIMARY KEY ( Name , Vorname , GebDatum , Tag , Zeit ) ) ;
12
13
14
15
16
17 −− neues KIGA−Schema , resultierend aus Zerlegung oder ER−Entwurf und
Beispieldaten
18
19 CREATE TABLE Kind (
20
Name CHAR ( 1 5 ) ,
21
Vorname CHAR ( 1 5 ) ,
46
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Datenbanken
Musterlösungen
GebDatum CHAR ( 1 0 ) ,
Gruppe INTEGER ,
PRIMARY KEY ( Name , Vorname , GebDatum ) ,
FOREIGN KEY ( Gruppe ) REFERENCES GruppenEnt
ON DELETE SET NULL
ON UPDATE CASCADE ) ;
CREATE TABLE GruppenEnt (
Gruppe INTEGER ,
Raum INTEGER ,
PRIMARY KEY ( Gruppe ) ,
UNIQUE ( Raum ) ) ;
CREATE TABLE GruppenSpielTermin (
Gruppe INTEGER NOT NULL ,
Spiel CHAR ( 1 2 ) NOT NULL ,
Tag CHAR ( 1 2 ) ,
Zeit CHAR ( 6 ) ,
PRIMARY KEY ( Tag , Zeit )
FOREIGN KEY ( Gruppe ) REFERENCES GruppenEnt
ON DELETE CASCADE
ON UPDATE CASCADE ) ;
Julia Wolters
47
Übung SS 2009
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 36
1 −− Aufgabe 36
2
3 −− a )
4 SELECT ∗ FROM ACCOUNT ;
5
6 −− b )
7 SELECT customer_name , customer_street , customer_city
8 FROM customer NATURAL JOIN
9
( SELECT DISTINCT customer_name FROM depositor NATURAL JOIN account
NATURAL JOIN branch
10
WHERE branch_city = ’Brooklyn ’ ) AS temp1
11 UNION
12 SELECT customer_name , customer_street , customer_city
13 FROM customer NATURAL JOIN
14
( SELECT DISTINCT customer_name FROM borrower NATURAL JOIN loan
NATURAL JOIN branch
15
WHERE branch_city = ’Brooklyn ’ ) AS temp2 ;
16
17
18 −− c )
19 SELECT branch_name , AVG ( balance ) FROM account
20
GROUP BY branch_name
21
HAVING AVG ( balance ) >= ALL ( SELECT AVG ( balance )
22
FROM account GROUP BY branch_name ) ;
23
24 −− d )
25 −− Kredite
26 CREATE VIEW borrowingView AS
27 SELECT count ( customer_name ) , loan_number FROM borrower GROUP BY loan_number
;
28
29 CREATE VIEW loaningView AS
30 SELECT customer_name , loan_number , ( amount / count ) AS newamount FROM
borrower
31
NATURAL JOIN borrowingView NATURAL JOIN loan ;
32
33 CREATE VIEW loaningCityView AS
34 SELECT customer_city , SUM ( newamount ) AS sum_of_newamount
35 FROM loaningView NATURAL JOIN customer GROUP BY ( customer_city ) ;
36
37
38 −− Kontoguthaben
39 CREATE VIEW deposingView AS SELECT count ( customer_name ) , account_number
40 FROM depositor GROUP BY account_number ;
41
42 CREATE VIEW accountingView AS
48
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
43 SELECT customer_name , account_number , ( balance / count ) AS newbalance
44 FROM depositor NATURAL JOIN deposingView NATURAL JOIN account ;
45
46 CREATE VIEW accountingCityView AS
47 SELECT customer_city , SUM ( newBalance ) AS sum_of_newbalance
48 FROM accountingView NATURAL JOIN customer GROUP BY ( customer_city ) ;
49
50
51 −− Resultat
52 CREATE VIEW resultingView AS
53 ( SELECT ∗ FROM loaningCityView NATURAL FULL OUTER JOIN accountingCityView ) ;
Julia Wolters
49
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 37
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
−− Lösungen zur Vorlesung Datenbanken
−− Lösungsvorschlag zu Aufgabe 37
−−a )
SELECT ∗ FROM s ;
−−b )
SELECT DISTINCT A FROM r ;
−−c )
SELECT ∗ FROM r WHERE B = 4 2 ;
−−d )
SELECT ∗ FROM r , s ;
−−e )
SELECT DISTINCT A , F FROM r , s where C = D ;
−−f )
( SELECT ∗ FROM r1 ) UNION ( SELECT ∗ FROM r2 ) ;
−−g )
( SELECT ∗ FROM r1 ) INTERSECT ( SELECT ∗ FROM r2 ) ;
−−h )
( SELECT ∗ FROM r1 ) EXCEPT ( SELECT ∗ FROM r2 ) ;
−−i )
30 −−
31
32
33 −−j )
34
35
36 −−k )
37
38
39 −−l )
40
41 −−
42
SELECT A , r3 . B , r3 . C FROM ( r1 NATURAL JOIN ( SELECT DISTINCT B , C FROM
r2 ) AS r3 ) ;
oder
SELECT ∗ FROM ( r1 NATURAL JOIN ( SELECT DISTINCT B , C FROM r2 ) AS r3 )
;
SELECT ∗ FROM
r JOIN s ON A = D ;
SELECT ∗ FROM r JOIN s ON B = E WHERE A < D ;
SELECT DISTINCT ∗ FROM ( SELECT D FROM s ) AS s1 , ( SELECT E , F FROM s
) AS s2 ;
oder
SELECT ∗ from ( SELECT DISTINCT D FROM s ) AS s1 , ( SELECT DISTINCT E ,
F FROM s ) AS s2 ;
50
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
43 −−
44
Datenbanken
Musterlösungen
oder
SELECT DISTINCT s1 . D , s2 . E , s2 . F FROM s AS s1 , s AS s2 ;
1 −− Übungen zur Vorlesung Datenbanken
2 −− Beispielrelationen zur Aufgabenstellung von Aufgabe 37
3
4 drop table r ;
5 drop table s ;
6
7 drop table r1 ;
8 drop table r2 ;
9
10
11 create table r (
12
A
integer ,
13
B
integer ,
14
C
integer ) ;
15
16 create table s (
17
D
integer ,
18
E
integer ,
19
F
integer ) ;
20 create table r1 (
21
A
integer ,
22
B
integer ,
23
C
integer ) ;
24 create table r2 (
25
A
integer ,
26
B
integer ,
27
C
integer ) ;
28
29
30
31 insert into r values ( 4 2 , 4 2 , 4 2 ) ;
32 insert into r values ( 1 , 2 , 4 ) ;
33 insert into r values ( 2 , 2 , NULL ) ;
34 insert into r values ( 4 , 3 , 2 ) ;
35 insert into r values ( 2 , 5 , 9 ) ;
36 insert into r values ( 7 , 5 , 7 ) ;
37
38 insert into s values ( 4 , 5 , 6 ) ;
39 insert into s values ( 4 , 5 , 8 ) ;
40 insert into s values ( 5 , 5 , 8 ) ;
41
42 insert into r1 values ( 2 , 3 , 4 ) ;
43 insert into r1 values ( 7 , 6 , 7 ) ;
44 insert into r1 values ( 5 , 6 , 7 ) ;
45
Julia Wolters
51
Übung SS 2009
Übung SS 2009
46
47
48
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
insert into r2 values ( 2 , 3 , 4 ) ;
insert into r2 values ( 6 , 3 , 4 ) ;
insert into r2 values ( 5 , 6 , 7 ) ;
52
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 38
Zeigen oder widerlegen Sie ausgehend von den in der Vorlesung präsentierten Definitionen,
dass in SQL folgende Äquivalenzen gelten:
(a) <> all ⇔ not in
T <> all m ⇔
⇔
⇔
⇔
⇔
⇔
T 6= all m
∀t ∈ m(T 6= t)
¬∃t ∈ m¬(T 6= t)
¬(∃t ∈ m(T = t)
¬T in m
T not in m
(b) <> some ⇔ not in
Gegenbeispiel: T = {5}, m = {0, 2, 5}
T <> some m wahr T 6= 0, T 6= 2, 0, 2 ∈ m
T not in m falsch T = 5 ∈ m
not in
⇒<> some ⇔
(c) = some ⇔ in
T = some m ⇔ ∃t ∈ m T = t ⇔ T in m
Julia Wolters
53
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 39
Zeigen oder widerlegen Sie, dass die folgenden SQL-Anweisungen für eine Relation r über
dem Schema R = (A, B) jeweils äquivalent sind. Was gilt für eine Relation r über dem
Schema R = (A)?
r
A B
1
3
null 4
1
3
r
A
1
% 2,3 wäre hier gleich
null
2
• select count(∗) from r
=3
• select count(A) from r
=2
• select count(DISTINCT A) from r
=1
54
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 40
Richten Sie sich mit den Skripten Airport.sql bzw. AirportForMySQL.sql eine FlughafenDatenbank mit PostgreSQL oder MySQL ein und formulieren sie folgende Anfragen in
der jeweiligen SQL-Syntax:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
table
table
table
table
table
table
table
table
table
table
table
table
besitztVonUnternehmen cascade ;
besitztVonPerson cascade ;
darfFliegen cascade ;
wartet cascade ;
wartung cascade ;
unternehmen cascade ;
mitarbeiter cascade ;
pilot cascade ;
person cascade ;
flugzeug cascade ;
hangar cascade ;
flugzeugtyp cascade ;
create table flugzeugtyp (
modellbez
varchar ( 3 0 )
kapazitaet smallint ,
gewicht
int ) ;
create table hangar (
hangarNr
smallint
kapazitaet smallint ) ;
primary key ,
primary key ,
create table flugzeug (
regNr
char (10)
primary key ,
modellBez
varchar ( 3 0 )
references flugzeugtyp ,
hangarNr
smallint
references hangar ,
platzNr
int ,
unique ( hangarNr , platzNr ) ) ;
create table person (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
gebDatum
date ,
primary key ( name , adresse ) ) ;
create table pilot (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
lizenzNr
char (11) ,
primary key ( name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
Julia Wolters
55
Übung SS 2009
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
create table mitarbeiter (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
personalNr i n t ,
primary key ( name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
create table unternehmen (
name
varchar ( 2 4 )
primary key ) ;
create table wartung (
datum
date ,
dauer
interval hour to minute ,
regNr
char (10)
references flugzeug ,
primary key ( regNr , datum ) ) ;
create table wartet (
regNr
char (10) ,
datum
date ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( regNr , datum , name , adresse ) ,
foreign key ( regNr , datum ) references wartung ,
foreign key ( name , adresse ) references mitarbeiter ) ;
create table darfFliegen (
modellBez
varchar ( 3 0 )
references flugzeugtyp ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( modellBez , name , adresse ) ,
foreign key ( name , adresse ) references pilot ) ;
create table besitztVonPerson (
regNr
char (10)
references flugzeug ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( regNr , name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
create table besitztVonUnternehmen (
regNr
char (10)
references flugzeug ,
name
varchar ( 2 4 ) ,
primary key ( regNr , name ) ,
foreign key ( name )
references unternehmen ) ;
56
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
Datenbanken
Musterlösungen
insert
insert
insert
insert
into
into
into
into
hangar
hangar
hangar
hangar
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
flugzeug
flugzeug
flugzeug
flugzeug
flugzeug
flugzeug
flugzeug
flugzeug
insert into person
1955 -12 -13 ’ ) ;
insert into person
1967 -03 -20 ’ ) ;
insert into person
1971 -04 -11 ’ ) ;
insert into person
1923 -06 -17 ’ ) ;
insert into person
1973 -12 -22 ’ ) ;
insert into person
1944 -01 -11 ’ ) ;
insert into person
1948 -11 -01 ’ ) ;
insert into person
1950 -12 -31 ’ ) ;
insert into pilot
-23652 ’ ) ;
insert into pilot
-10200 ’ ) ;
insert into pilot
-20052 ’ ) ;
insert into pilot
Julia Wolters
values
values
values
values
(1 ,
(2 ,
(3 ,
(4 ,
values
values
values
values
values
values
values
values
values
values
values
values
values
values
Übung SS 2009
5) ;
6) ;
10) ;
8) ;
( ’Cessna -22 - C16 ’ , 3 , 5 0 0 ) ;
( ’188 Cessna Agwagon ’ , 8 , 1 7 2 0 ) ;
( ’Convair CV 990 ’ , 4 , 8 0 0 ) ;
( ’Convair F2Y Sea Dart ’ , 2 , 4 2 0 ) ;
( ’Saab Tunnan ’ , 4 , 1 2 0 0 ) ;
( ’Saab J 21R’ , 9 , 1 7 0 ) ;
( ’CA -22 -3100 ’ ,
( ’CA -34 -2346 ’ ,
( ’CA -10 -4431 ’ ,
( ’CA -21 -0234 ’ ,
( ’CO -09 -9563 ’ ,
( ’SA -01 -1823 ’ ,
( ’SA -45 -0034 ’ ,
( ’SA -21 -0234 ’ ,
’Cessna -22 - C16 ’ , 1 , 5 ) ;
’Cessna -22 - C16 ’ , 3 , 1 0 ) ;
’188 Cessna Agwagon ’ , 4 , 1 ) ;
’188 Cessna Agwagon ’ , 4 , 4 ) ;
’Convair F2Y Sea Dart ’ , 3 , 9 ) ;
’Saab Tunnan ’ , 1 , 1 ) ;
’Saab Tunnan ’ , 2 , 1 ) ;
’Saab J 21R’ , 2 , 3 ) ;
values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ , ’
values ( ’Grant Green ’ , ’Main Street 231 , Dallas ’ , ’
values ( ’Herbie Hancock ’ , ’4th Street 43, Dallas ’ , ’
values ( ’John Lee Hooker ’ , ’Brodway 121 , Yorktown ’ , ’
values ( ’Johnny Ray ’ , ’Uptown 332 , New York ’ , ’
values ( ’Miles Davis ’ , ’34 th Avenue 376 , New York ’ , ’
values ( ’Keith Jarret ’ , ’23 th Avenue 12, New York ’ , ’
values ( ’Joe Cool ’ , ’South Road 3, Santa Barbara ’ , ’
values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ , ’PP -CA
values ( ’Joe Cool ’ , ’South Road 3, Santa Barbara ’ , ’PL -CC
values ( ’Johnny Ray ’ , ’Uptown 332 , New York ’ , ’PP -CA
values ( ’Miles Davis ’ , ’34 th Avenue 376 , New York ’ , ’PP -
57
Übung SS 2009
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
SC -30000 ’ ) ;
insert into pilot values ( ’Keith Jarret ’ , ’23 th Avenue 12, New York ’ , ’PP SC -31020 ’ ) ;
insert into mitarbeiter values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ ,
34451) ;
insert into mitarbeiter values ( ’Grant Green ’ , ’Main Street 231 , Dallas ’ ,
54234) ;
insert into mitarbeiter values ( ’Herbie Hancock ’ , ’4th Street 43, Dallas ’ ,
43321) ;
insert into unternehmen values ( ’Dixie ’ ) ;
insert into unternehmen values ( ’Carlsberg ’ ) ;
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
Wartung
Wartung
Wartung
Wartung
Wartung
Wartung
values
values
values
values
values
values
( ’2005 -11 -15 ’ , ’2
( ’2005 -11 -16 ’ , ’2
( ’2005 -11 -23 ’ , ’2
( ’2005 -11 -19 ’ , ’6
( ’2005 -11 -20 ’ , ’3
( ’2003 -11 -21 ’ , ’4
insert into wartet values ( ’CA -22 -3100 ’ ,
Street 231 , Dallas ’ ) ;
insert into wartet values ( ’CA -22 -3100 ’ ,
th Street 43, Dallas ’ ) ;
insert into wartet values ( ’CA -22 -3100 ’ ,
Street 231 , Dallas ’ ) ;
insert into wartet values ( ’CA -22 -3100 ’ ,
th Street 43, Dallas ’ ) ;
hour 10 minute ’ , ’CA -22 -3100 ’ ) ;
hour 20 minute ’ , ’CA -22 -3100 ’ ) ;
hour 10 minute ’ , ’CA -22 -3100 ’ ) ;
hour 00 minute ’ , ’SA -21 -0234 ’ ) ;
hour ’ , ’SA -01 -1823 ’ ) ;
hour ’ , ’SA -01 -1823 ’ ) ;
’2005 -11 -15 ’ , ’Grant Green ’ , ’Main
’2005 -11 -16 ’ , ’Herbie Hancock ’ , ’4
’2005 -11 -16 ’ , ’Grant Green ’ , ’Main
’2005 -11 -23 ’ , ’Herbie Hancock ’ , ’4
insert into wartet values ( ’SA -01 -1823 ’ , ’2005 -11 -20 ’ , ’Herbie Hancock ’ , ’4
th Street 43, Dallas ’ ) ;
insert into wartet values ( ’SA -01 -1823 ’ , ’2003 -11 -21 ’ , ’Herbie Hancock ’ , ’4
th Street 43, Dallas ’ ) ;
insert into wartet values ( ’SA -21 -0234 ’ , ’2005 -11 -19 ’ , ’Grant Green ’ , ’Main
Street 231 , Dallas ’ ) ;
insert into darfFliegen values
7, Witchtown ’ ) ;
insert into darfFliegen values
Parkway 7, Witchtown ’ ) ;
insert into darfFliegen values
Parkway 7, Witchtown ’ ) ;
insert into darfFliegen values
7, Witchtown ’ ) ;
insert into darfFliegen values
Witchtown ’ ) ;
insert into darfFliegen values
( ’Cessna -22 - C16 ’ , ’Luther Vandros ’ , ’Parkway
( ’188 Cessna Agwagon ’ , ’Luther Vandros ’ , ’
( ’Convair CV 990 ’ , ’Luther Vandros ’ , ’
( ’Saab Tunnan ’ , ’Luther Vandros ’ , ’Parkway
( ’Saab J 21R’ , ’Luther Vandros ’ , ’Parkway 7,
( ’Convair CV 990 ’ , ’Joe Cool ’ , ’South Road
58
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
1
2
3
4
5
6
7
8
9
10
11
12
Datenbanken
Musterlösungen
3, Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
332 , New York ’ ) ;
insert into darfFliegen values
th Avenue 376 , New York ’ ) ;
insert into darfFliegen values
Avenue 376 , New York ’ ) ;
insert into darfFliegen values
Avenue 12, New York ’ ) ;
insert into darfFliegen values
Avenue 12, New York ’ ) ;
insert into darfFliegen values
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Parkway 7, Witchtown ’ ) ;
insert into besitztVonPerson
Brodway 121 , Yorktown ’ ) ;
insert into besitztVonPerson
Brodway 121 , Yorktown ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
Übung SS 2009
( ’Cessna -22 - C16 ’ , ’Joe Cool ’ , ’South Road 3,
( ’Saab Tunnan ’ , ’Joe Cool ’ , ’South Road 3,
( ’Saab J 21R’ , ’Joe Cool ’ , ’South Road 3,
( ’Convair CV 990 ’ , ’Johnny Ray ’ , ’Uptown
( ’Convair F2Y Sea Dart ’ , ’Miles Davis ’ , ’34
( ’Convair CV 990 ’ , ’Miles Davis ’ , ’34 th
( ’Cessna -22 - C16 ’ , ’Keith Jarret ’ , ’23 th
( ’188 Cessna Agwagon ’ , ’Keith Jarret ’ , ’23 th
( ’Convair CV 990 ’ , ’Keith Jarret ’ , ’23 th
values ( ’CA -22 -3100 ’ , ’Luther Vandros ’ , ’
values ( ’CA -21 -0234 ’ , ’John Lee Hooker ’ , ’
values ( ’SA -45 -0034 ’ , ’John Lee Hooker ’ , ’
values ( ’SA -01 -1823 ’ , ’Keith Jarret ’ , ’23 th
values ( ’SA -21 -0234 ’ , ’Keith Jarret ’ , ’23 th
values ( ’CO -09 -9563 ’ , ’Keith Jarret ’ , ’23 th
insert into besitztVonUnternehmen values ( ’CA -34 -2346 ’ , ’Dixie ’ ) ;
insert into besitztVonUnternehmen values ( ’CA -10 -4431 ’ , ’Carlsberg ’ ) ;
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
table
table
table
table
table
table
table
table
table
table
table
table
besitztVonUnternehmen cascade ;
besitztVonPerson cascade ;
darfFliegen cascade ;
wartet cascade ;
wartung cascade ;
unternehmen cascade ;
mitarbeiter cascade ;
pilot cascade ;
person cascade ;
flugzeug cascade ;
hangar cascade ;
flugzeugtyp cascade ;
Julia Wolters
59
Übung SS 2009
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
create table flugzeugtyp (
modellbez
varchar ( 3 0 )
kapazitaet smallint ,
gewicht
int ) ;
create table hangar (
hangarNr
smallint
kapazitaet smallint ) ;
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
primary key ,
primary key ,
create table flugzeug (
regNr
char (10)
primary key ,
modellBez
varchar ( 3 0 )
references flugzeugtyp ,
hangarNr
smallint
references hangar ,
platzNr
int ,
unique ( hangarNr , platzNr ) ) ;
create table person (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
gebDatum
date ,
primary key ( name , adresse ) ) ;
create table pilot (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
lizenzNr
char (11) ,
primary key ( name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
create table mitarbeiter (
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
personalNr i n t ,
primary key ( name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
create table unternehmen (
name
varchar ( 2 4 )
primary key ) ;
create table wartung (
datum
date ,
dauer
time ,
regNr
char (10)
references flugzeug ,
primary key ( regNr , datum ) ) ;
create table wartet (
60
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
Datenbanken
Musterlösungen
Übung SS 2009
regNr
char (10) ,
datum
date ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( regNr , datum , name , adresse ) ,
foreign key ( regNr , datum ) references wartung ,
foreign key ( name , adresse ) references mitarbeiter ) ;
create table darfFliegen (
modellBez
varchar ( 3 0 )
references flugzeugtyp ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( modellBez , name , adresse ) ,
foreign key ( name , adresse ) references pilot ) ;
create table besitztVonPerson (
regNr
char (10)
references flugzeug ,
name
varchar ( 2 4 ) ,
adresse
varchar ( 5 0 ) ,
primary key ( regNr , name , adresse ) ,
foreign key ( name , adresse ) references person ) ;
create table besitztVonUnternehmen (
regNr
char (10)
references flugzeug ,
name
varchar ( 2 4 ) ,
primary key ( regNr , name ) ,
foreign key ( name )
references unternehmen ) ;
insert
insert
insert
insert
into
into
into
into
hangar
hangar
hangar
hangar
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
flugzeugtyp
insert
insert
insert
insert
into
into
into
into
flugzeug
flugzeug
flugzeug
flugzeug
Julia Wolters
values
values
values
values
(1 ,
(2 ,
(3 ,
(4 ,
values
values
values
values
values
values
values
values
values
values
5) ;
6) ;
10) ;
8) ;
( ’Cessna -22 - C16 ’ , 3 , 5 0 0 ) ;
( ’188 Cessna Agwagon ’ , 8 , 1 7 2 0 ) ;
( ’Convair CV 990 ’ , 4 , 8 0 0 ) ;
( ’Convair F2Y Sea Dart ’ , 2 , 4 2 0 ) ;
( ’Saab Tunnan ’ , 4 , 1 2 0 0 ) ;
( ’Saab J 21R’ , 9 , 1 7 0 ) ;
( ’CA -22 -3100 ’ ,
( ’CA -34 -2346 ’ ,
( ’CA -10 -4431 ’ ,
( ’CA -21 -0234 ’ ,
61
’Cessna -22 - C16 ’ , 1 , 5 ) ;
’Cessna -22 - C16 ’ , 3 , 1 0 ) ;
’188 Cessna Agwagon ’ , 4 , 1 ) ;
’188 Cessna Agwagon ’ , 4 , 4 ) ;
Übung SS 2009
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
insert
insert
insert
insert
into
into
into
into
Datenbanken
Musterlösungen
flugzeug
flugzeug
flugzeug
flugzeug
( ’CO -09 -9563 ’ ,
( ’SA -01 -1823 ’ ,
( ’SA -45 -0034 ’ ,
( ’SA -21 -0234 ’ ,
’Convair F2Y Sea Dart ’ , 3 , 9 ) ;
’Saab Tunnan ’ , 1 , 1 ) ;
’Saab Tunnan ’ , 2 , 1 ) ;
’Saab J 21R’ , 2 , 3 ) ;
values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ , ’
insert into person
12 -13 -1955 ’ ) ;
insert into person
03 -20 -1967 ’ ) ;
insert into person
04 -11 -1971 ’ ) ;
insert into person
06 -17 -1923 ’ ) ;
insert into person
12 -22 -1973 ’ ) ;
insert into person
01 -11 -1944 ’ ) ;
insert into person
11 -01 -1948 ’ ) ;
insert into person
12 -31 -1950 ’ ) ;
insert into pilot
-23652 ’ ) ;
insert into pilot
-10200 ’ ) ;
insert into pilot
-20042 ’ ) ;
insert into pilot
SC -30000 ’ ) ;
insert into pilot
SC -31020 ’ ) ;
values
values
values
values
Mittwochs, 08 – 10 Uhr
Musterlösung
values ( ’Grant Green ’ , ’Main Street 231 , Dallas ’ , ’
values ( ’Herbie Hancock ’ , ’4th Street 43, Dallas ’ , ’
values ( ’John Lee Hooker ’ , ’Brodway 121 , Yorktown ’ , ’
values ( ’Johnny Ray ’ , ’Uptown 332 , New York ’ , ’
values ( ’Miles Davis ’ , ’34 th Avenue 376 , New York ’ , ’
values ( ’Keith Jarret ’ , ’23 th Avenue 12, New York ’ , ’
values ( ’Joe Cool ’ , ’South Road 3, Santa Barbara ’ , ’
values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ , ’PP -CA
values ( ’Joe Cool ’ , ’South Road 3, Santa Barbara ’ , ’PL -CC
values ( ’Johnny Ray ’ , ’Uptown 332 , New York ’ , ’PP -CA
values ( ’Miles Davis ’ , ’34 th Avenue 376 , New York ’ , ’PP values ( ’Keith Jarret ’ , ’23 th Avenue 12, New York ’ , ’PP -
insert into mitarbeiter values ( ’Luther Vandros ’ , ’Parkway 7, Witchtown ’ ,
34451) ;
insert into mitarbeiter values ( ’Grant Green ’ , ’Main Street 231 , Dallas ’ ,
54234) ;
insert into mitarbeiter values ( ’Herbie Hancock ’ , ’4th Street 43, Dallas ’ ,
43321) ;
insert into unternehmen values ( ’Dixie ’ ) ;
insert into unternehmen values ( ’Carlsberg ’ ) ;
insert
insert
insert
insert
into
into
into
into
wartung
wartung
wartung
wartung
values
values
values
values
( ’04 -11 -15 ’ , ’2:10:00 ’ , ’CA -22 -3100 ’ ) ;
( ’04 -11 -16 ’ , ’2:20:00 ’ , ’CA -22 -3100 ’ ) ;
( ’04 -11 -23 ’ , ’2:10:00 ’ , ’CA -22 -3100 ’ ) ;
( ’04 -11 -19 ’ , ’6:00:00 ’ , ’SA -21 -0234 ’ ) ;
62
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
Datenbanken
Musterlösungen
Übung SS 2009
insert into wartung values ( ’04 -11 -20 ’ , ’3:00:00 ’ , ’SA -01 -1823 ’ ) ;
insert into wartung values ( ’03 -11 -21 ’ , ’4:00:00 ’ , ’SA -01 -1823 ’ ) ;
insert into wartet values
Street 231 , Dallas ’ ) ;
insert into wartet values
Street 43, Dallas ’ ) ;
insert into wartet values
Street 231 , Dallas ’ ) ;
insert into wartet values
Street 43, Dallas ’ ) ;
( ’CA -22 -3100 ’ , ’04 -11 -15 ’ , ’Grant Green ’ , ’Main
( ’CA -22 -3100 ’ , ’04 -11 -16 ’ , ’Herbie Hancock ’ , ’4th
( ’CA -22 -3100 ’ , ’04 -11 -16 ’ , ’Grant Green ’ , ’Main
( ’CA -22 -3100 ’ , ’04 -11 -23 ’ , ’Herbie Hancock ’ , ’4th
insert into wartet values ( ’SA -01 -1823 ’ , ’04 -11 -20 ’ , ’Herbie Hancock ’ , ’4th
Street 43, Dallas ’ ) ;
insert into wartet values ( ’SA -01 -1823 ’ , ’04 -11 -21 ’ , ’Herbie Hancock ’ , ’4th
Street 43, Dallas ’ ) ;
insert into wartet values ( ’SA -21 -0234 ’ , ’04 -11 -19 ’ , ’Grant Green ’ , ’Main
Street 231 , Dallas ’ ) ;
insert into darfFliegen values
7, Witchtown ’ ) ;
insert into darfFliegen values
Parkway 7, Witchtown ’ ) ;
insert into darfFliegen values
Parkway 7, Witchtown ’ ) ;
insert into darfFliegen values
7, Witchtown ’ ) ;
insert into darfFliegen values
Witchtown ’ ) ;
insert into darfFliegen values
3, Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
Santa Barbara ’ ) ;
insert into darfFliegen values
332 , New York ’ ) ;
insert into darfFliegen values
th Avenue 376 , New York ’ ) ;
insert into darfFliegen values
Avenue 376 , New York ’ ) ;
insert into darfFliegen values
Avenue 12, New York ’ ) ;
insert into darfFliegen values
Avenue 12, New York ’ ) ;
Julia Wolters
( ’Cessna -22 - C16 ’ , ’Luther Vandros ’ , ’Parkway
( ’188 Cessna Agwagon ’ , ’Luther Vandros ’ , ’
( ’Convair CV 990 ’ , ’Luther Vandros ’ , ’
( ’Saab Tunnan ’ , ’Luther Vandros ’ , ’Parkway
( ’Saab J 21R’ , ’Luther Vandros ’ , ’Parkway 7,
( ’Convair CV 990 ’ , ’Joe Cool ’ , ’South Road
( ’Cessna -22 - C16 ’ , ’Joe Cool ’ , ’South Road 3,
( ’Saab Tunnan ’ , ’Joe Cool ’ , ’South Road 3,
( ’Saab J 21R’ , ’Joe Cool ’ , ’South Road 3,
( ’Convair CV 990 ’ , ’Johnny Ray ’ , ’Uptown
( ’Convair F2Y Sea Dart ’ , ’Miles Davis ’ , ’34
( ’Convair CV 990 ’ , ’Miles Davis ’ , ’34 th
( ’Cessna -22 - C16 ’ , ’Keith Jarret ’ , ’23 th
( ’188 Cessna Agwagon ’ , ’Keith Jarret ’ , ’23 th
63
Übung SS 2009
167
168
169
170
171
172
173
174
175
176
177
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
insert into darfFliegen values ( ’Convair CV 990 ’ , ’Keith Jarret ’ , ’23 th
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Parkway 7, Witchtown ’ ) ;
insert into besitztVonPerson
Brodway 121 , Yorktown ’ ) ;
insert into besitztVonPerson
Brodway 121 , Yorktown ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
insert into besitztVonPerson
Avenue 12, New York ’ ) ;
values ( ’CA -22 -3100 ’ , ’Luther Vandros ’ , ’
values ( ’CA -21 -0234 ’ , ’John Lee Hooker ’ , ’
values ( ’SA -45 -0034 ’ , ’John Lee Hooker ’ , ’
values ( ’SA -01 -1823 ’ , ’Keith Jarret ’ , ’23 th
values ( ’SA -21 -0234 ’ , ’Keith Jarret ’ , ’23 th
values ( ’CO -09 -9563 ’ , ’Keith Jarret ’ , ’23 th
insert into besitztVonUnternehmen values ( ’CA -34 -2346 ’ , ’Dixie ’ ) ;
insert into besitztVonUnternehmen values ( ’CA -10 -4431 ’ , ’Carlsberg ’ ) ;
1. Geben Sie die Registrierungsnummern und die Typen aller Flugzeuge zurück.
2. Bestimmen Sie alle Piloten, die Flugzeugtypen fliegen dürfen, deren Modellbezeichnung den Teilausdruck Cessna“ enthält.
”
3. Ermitteln Sie die Namen aller Piloten, deren Lizenznummern mit einer Doppelnull
endet.
4. Bestimmen Sie alle Flugzeuge, deren Registrierungsnummer mit CA“ beginnt oder
”
mindestens siebenstellig ist.
5. Geben Sie die Registriernummern aller Flugzeuge zurück, die zwischen dem 16.11.2006
und dem 23.11.2006 mehrfach gewartet wurden.
1
2
3
4
5
6
7
8
−− Übungen zur Vorlesung Datenbanken
−− Lösungsvorschlag zu Aufgabe 40
−− a )
SELECT regNr , flugzeugtyp FROM flugzeug NATURAL JOIN flugzeugtyp ;
−− b )
SELECT DISTINCT pilot . ∗ FROM pilot NATURAL JOIN darfFliegen WHERE modellBez
LIKE ’% Cessna %’ ;
9
10 −− c )
11 SELECT name FROM pilot WHERE lizenzNr LIKE ’%00 ’ ;
12
64
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
13 −− d )
14 SELECT ∗ FROM flugzeug WHERE regnr LIKE ’CA%’ OR char_length ( regnr ) >= 7 ;
15
−− alternativ :
16 SELECT ∗ FROM flugzeug WHERE regnr LIKE ’CA%’ OR regnr LIKE ’_______ %’ ;
17
18 −− e )
19 SELECT regNr FROM wartung
20
WHERE datum BETWEEN ’2005 -11 -16 ’ and ’2005 -11 -23 ’
21
GROUP BY regNr HAVING COUNT ( DATUM ) > 1 ;
Julia Wolters
65
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
1 −− Aufgabe 41
2
3 CREATE TABLE auftraege (
4
anfang TIMESTAMP NOT NULL ,
5
ende
TIMESTAMP ,
6
kunde VARCHAR ,
7
beschreibung VARCHAR ,
8
PRIMARY KEY ( anfang , kunde , beschreibung )
9 );
10
11 INSERT INTO auftraege VALUES ( ’2006 -12 -06 09:00 ’ , ’2006 -12 -06 12:00 ’ , ’
Mueller ’ , ’Nikolausen ’ ) ;
12 INSERT INTO auftraege VALUES ( ’2006 -12 -02 15:00 ’ , ’2006 -12 -02 15:32 ’ , ’Meier
’ , ’Foo ’ ) ;
13 INSERT INTO auftraege VALUES ( ’2006 -12 -08 12:38 ’ , ’2006 -12 -08 22:05 ’ , ’Meier
’ , ’Bar ’ ) ;
14 INSERT INTO auftraege VALUES ( ’2006 -12 -04 12:38 ’ , ’2006 -12 -05 11:11 ’ , ’Meier
’ , ’Foobar2 ’ ) ;
15 INSERT INTO auftraege VALUES ( ’2006 -04 -28 23:30 ’ , ’2006 -05 -02 10:00 ’ , ’Meier
’ , ’Foobar3 ’ ) ;
16 INSERT INTO auftraege VALUES ( ’2006 -04 -20 12:00 ’ , ’2006 -12 -01 12:00 ’ , ’
Muster AG ’ , ’Testen ’ ) ;
17
18 −− a ) Arbeitszeit pro Auftrag ausrechnen
19 DROP VIEW auftraege_dauer ;
20 CREATE VIEW auftraege_dauer AS
21
SELECT ∗ , ( ende − anfang ) AS dauer FROM auftraege ;
22
23 SELECT ∗ FROM auftraege_dauer ;
24
25 SELECT EXTRACT ( MINUTE FROM dauer ) as minuten , EXTRACT ( HOUR FROM dauer ) as
stunden ,
26
( CASE
27
WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 0
28
WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 15
29
WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 30
30
WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 45
31
ELSE 60
32
END ) AS minuten15 FROM auftraege_dauer ;
33
34 SELECT kunde , beschreibung , dauer , dauer + ( (
35
( CASE
36
WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 0
37
WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 15
38
WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 30
39
WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 45
40
ELSE 60
66
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
41
END ) − EXTRACT ( MINUTE FROM dauer ) ) ∗ interval ’1 minute ’ ) AS dauer15
42
FROM auftraege_dauer ;
43
44 −− b ) Auftragsdauer für jeden Auftraggeber aufaddieren
45
46 SELECT kunde , sum ( dauer ) FROM auftraege_dauer GROUP BY kunde ;
47
48 −− c ) Kunden mit Aufträgen zwischen 0 1 . 0 5 . 2 0 0 6 und 3 0 . 1 1 . 2 0 0 6
49
50 SELECT DISTINCT kunde from auftraege
51
WHERE ende >= DATE ’2006 -05 -01 ’ AND anfang <= DATE ’2006 -11 -30 ’ ;
52
53
−− oder
54
55 SELECT DISTINCT kunde from auftraege
56
WHERE ( anfang , ende ) OVERLAPS ( DATE ’2006 -05 -01 ’ , DATE ’2006 -11 -30 ’ ) ;
57
58 −− d ) Aufträge zwischen 2 0 : 0 0 und 0 6 : 0 0 Uhr oder an Wochenenden
59
60 SELECT ∗ FROM auftraege_dauer WHERE
61
−− Zwischen 20 und 6 Uhr ?
62
( extract ( hour from anfang ) between 20 and 2 4 ) or
63
( extract ( hour from anfang ) between 0 and 6 ) or
64
( extract ( hour from ende ) between 20 and 2 4 ) or
65
( extract ( hour from ende ) between 0 and 6 ) or
66
( extract ( dow from anfang ) < extract ( dow from ende ) ) or
67
( dauer > INTERVAL ’1 day ’ )
68
−− Anfang am Wochenende ?
69
or extract ( dow from anfang ) = 0 or extract ( dow from anfang ) = 6
70
−− Ende am Wochenende ?
71
or extract ( dow from ende ) = 0 or extract ( dow from ende ) = 6
72
−− Anfang und Ende in der Woche , aber in unterschiedlichen Wochen −
eigentlich unnötig
73
or (
extract ( dow from anfang ) between 1 and 5
74
and extract ( dow from ende ) between 1 and 5
75
and (
extract ( week from anfang ) < extract ( week from ende )
76
or extract ( year from anfang ) < extract ( year from ende ) ) ) ;
77
78 −− e ) Aufträge an denen gerade gearbeitet wird
79
80 SELECT ∗ FROM auftraege WHERE anfang < now ( ) AND ( now ( ) < ende OR ende IS
NULL ) ;
81
82 −− f ) Durchschnittliche Wochenarbeitszeit für abgeschlossene Aufträge
83
84 SELECT min ( anfang ) , max ( ende ) , max ( ende ) − min ( anfang ) AS zeitraum ,
85
extract ( days from ( max ( ende ) − min ( anfang ) ) ) as gesamttage ,
86
sum ( dauer ) AS gesamtdauer ,
Julia Wolters
67
Übung SS 2009
87
88
89
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
( sum ( dauer ) / extract ( days from ( max ( ende ) − min ( anfang ) ) ) ) ∗ 7 AS
wochendauer
FROM auftraege_dauer
WHERE ende < now ( ) AND ende IS NOT NULL ;
68
Julia Wolters
Aufgabe 42
Beispieldatenbank (zu E-R-Diagramm Folie II-56):
angestellter:
■
■
Ssn
1
7
■
Vorname Nachname
“Hans”
“Langeschon”
“Klaus”
“Schmidt”
Gehalt
1500
2000
angehöriger:
Name
“Barbara“
■
Grad
1
Gdatum
“2.2.1966”
arbeitet-für: AngestellterSsn
1
7
AngestellterSsn
7
AbteilungNummer
10
5
1
Aufgabe 42
■
Verletzungstypen mit Beispielen: Insert
•
•
•
•
1. Domain constraints
–
a.
–
b.
INSERT INTO angestellter
VALUES(34567, ‘Andrea’, ‘Hausmann’, - 3000);
INSERT INTO arbeitet-für VALUES(1, ‘keineZahl‘);
2. Referentielle Integritätsbedingungen
–
a.
–
b.
INSERT INTO angehöriger
VALUES(’Peter’, 1, ’31.01.1976’, 34567);
INSERT INTO arbeitet-für VALUES(34567, 10);
3. Schlüsselbedingungen
–
a.
–
b.
INSERT INTO angestellter
VALUES(null, ’Andrea’, ‘Hausmann’, 2500);
INSERT INTO angestellter
VALUES(1, ’Andrea’, ‘Hausmann’, 3000);
4. semantische Assertions
–
INSERT INTO angestellter
VALUES(45362, ‘Barbara’, ‘Langeschon’, 1500);
(„Verwandte von Angestellten dürfen nicht
eingestellt werden.“)
2
Aufgabe 42
■
Verletzungstypen mit Beispielen: Delete
•
2. Referentielle Integritätsbedingungen
–
DELETE FROM angestellter WHERE Ssn = 1;
•
4. semantische Assertions
–
DELETE FROM angehöriger
WHERE AngestellterSsn = 7;
(„Angehörige ersten Grades dürfen nicht
entfernt werden.“)
3
Aufgabe 42
■
Verletzungstypen mit Beispielen: Update
•
1. Domain constraints
– UPDATE angestellter SET Gehalt = -3000
WHERE Ssn = 1;
•
2. Referentielle Integritätsbedingungen
– UPDATE angestellter SET Ssn = 12 WHERE Ssn = 1;
•
3. Schlüsselbedingungen
– UPDATE angestellter SET P-Nr = null
WHERE Ssn = 1;
•
4. semantische Assertions
– UPDATE angestellter SET Gehalt = 1000
WHERE Ssn = 1;
("Zu drastische Kürzung" oder
"zu geringes Gehalt")
4
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 43
Betrachten und erläutern Sie folgende SQL-Anweisung:
create table hierarchie (
angestellter varchar(20) not null,
vorgesetzter varchar(20) not null,
primary key (angestellter),
foreign key (vorgesetzter) references hierarchie on delete cascade);
(a) Erklären Sie allgemein die Wirkung des Löschens eines Datensatzes aus der erzeugten
Tabelle der zu Grunde liegenden Relationalen Datenbank.
(b) Verdeutlichen Sie Ihre Aussage aus dem vorherigen Aufgabenteil, indem Sie ausführlich die Wirkung des Löschens des Datensatzes (Müller, Meier)“ aus folgender Tabelle
”
darstellen.
angestellter vorgesetzter
Schulz
Schulz
Meier
Schulz
Müller
Meier
Schmidt
Schulz
Tupelnikus
Müller
Relationica Tupelnikus
Tadagoliker
Müller
(a) Beim Löschen eines Tupels werden alle referenzierenden (nicht referenzierten) Tupel
auch gelöscht.
(b)
(1)
(2)
(3)
(2)
angestellter vorgesetzter
Schulz
Schulz
Meier
Schulz
Meier
Müller
Schmidt
Schulz
(
(
(
(
Tupelnikus
Müller
(
(
(
((
(((
Relationica
Tupelnikus
((((
(
(
(
(
Tadagoliker
Müller
((((
(
Julia Wolters
73
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 44
(Präsenzübung) Auf der Homepage zur Vorlesung steht das Gerüst eines Java-Programms
PersonenJDBC zur Verwendung von JDBC bereit. Mit Hilfe dieses Programms ist es
bisher nur möglich, Instanzen der bereitgestellen Klasse Person anzulegen. Erweitern Sie
das Programm nun wie folgt:
(a) Stellen Sie eine Verbindung zu einer Datenbank im bereitgestellten PostgreSQL- oder
MySQLDatenbanksystem her. Hinweise zum Aufruf des Programms finden Sie in der
Datei readme.txt.
(b) Erzeugen Sie in dieser Datenbank eine Tabelle Personendaten“, die der Speicherung
”
von Personendaten dient. Sorgen Sie dafür, dass diese Tabelle genau dann erzeugt
wird, wenn sie noch nicht in der Datenbank existiert.
(c) Speichern sie alle vorhandenen Objektdaten in dieser Tabelle und geben Sie ihren
Inhalt auf den Bildschirm aus. Löschen Sie anschließend alle Objekte und erzeugen
Sie diese dann erneut mit Hilfe der gespeicherten Datensätze. Geben Sie nun die neu
erzeugten Objekte aus.
(d) Führen Sie drei SQL-Anfragen auf der Datenbank durch und geben Sie das Resultat
jeweils aus.
(e) Löschen Sie alle Jacksons“ aus der Datenbank und fügen Sie zusätzlich die Person
”
namens Jackson, Five“, von der keine weiteren Daten bekannt sind hinzu. Diese
”
beiden Operationen sollen zusammen als eine Transaktion deklariert und ausgeführt
werden.
(f) Schließen Sie die Verbindung zur Datenbank.
1
2
3
4
5
6
7
8
9
10
11
12
import java . sql . ∗ ;
import java . util . GregorianCalendar ;
import java . util . StringTokenizer ;
/∗ ∗
∗ D i e s e K l a s s e s t e l l t d i e Verbindung zu e i n e r SQL−Datenbank ü b e r JDBC h e r
und f ü h r t
∗ O pe ra t io ne n a u f e i n e r e v e n t u e l l noch zu e r z e u g e n d e n T a b e l l e ”
p e r s o n e n d a t e n ” durch . Die Daten
∗ d e r T a b e l l e entstammen einem Array { @ l i n k PersonenJDBC . p e r s o n s } von {
@ l i n k Person}−Obj ekte n ,
∗ d i e j e w e i l s d i e Daten b e z ü g l i c h e i n e r Person e n t h a l t e n .
∗
∗ @author Henrik Blunck
∗ @author Jörg Mensmann
74
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Datenbanken
Musterlösungen
Übung SS 2009
∗ @version 1.1
∗/
p u b l i c c l a s s PersonenJDBC {
p u b l i c s t a t i c f i n a l String PSQL_DRIVER = "org. postgresql . Driver " ;
p u b l i c s t a t i c f i n a l String MYSQL_DRIVER = "com. mysql .jdbc. Driver " ;
private
private
private
private
Connection db ;
String dbName ;
String userName ;
String password ;
// Der Datenbestand , d e r i n d i e Datenbank i n t e g r i e r t werden s o l l
p r i v a t e Person [ ] persons = {
new Person ( " Jackson " , " Michael " , 1 7 3 2 3 4 5 , new GregorianCalendar
( 1 9 6 6 , 1 1 , 9 ) , Person . MALE ) ,
new Person ( " Bolton " , " Michael " , 5 3 2 2 3 4 5 , new GregorianCalendar
( 1 9 6 5 , 1 , 1 7 ) , Person . MALE ) ,
new Person ( " Gabriel " , "Peter " ,
4 6 3 2 3 4 5 , new GregorianCalendar
( 1 9 5 9 , 4 , 5 ) , Person . MALE ) ,
new Person ( " Jackson " , " Jennifer " , 2 3 2 3 3 4 5 , new GregorianCalendar
( 1 9 8 0 , 7 , 2 3 ) , Person . FEMALE ) ,
new Person ( " Hooker " , "John Lee" , 1 3 4 2 3 4 5 , new GregorianCalendar
( 1 9 1 6 , 3 , 7 ) , Person . MALE ) ,
new Person ( " Turner " , "Tina" ,
2 5 3 2 3 4 5 , new GregorianCalendar
( 1 9 5 8 , 1 , 1 7 ) , Person . FEMALE ) ,
new Person ( "Rush" ,
" Jennifer " , 6 1 3 2 3 4 5 , new GregorianCalendar
( 1 9 6 1 , 3 , 2 1 ) , Person . FEMALE )
};
p u b l i c PersonenJDBC ( ) {
}
/∗ ∗
∗ A b a r b e i t u n g d e r i n d e r A u f g a b e n s t e l l u n g angegebenen Anforderungen
∗/
p u b l i c v o i d doRequiredOperations ( ) {
// S t r i n g −V a r i a b l e , das j e w e i l s d i e SQL−S t a t e m e n t s a l s Z e i c h e n k e t t e
h ä l t
String sqlString ;
// Das R e s u l t S e t , das j e w e i l s d i e E r g e b n i s s e e i n z e l n e r Anfragen
h ä l t
ResultSet rs ;
b o o l e a n tableExists = t r u e ;
// Test , ob T a b e l l e mit Namen ” p e r s o n e n d a t e n ” b e r e i t s e x i s t i e r t
try {
// db . getMetaData ( ) l i e f e r t DatabaseMetaData−O b j e k t . g e t T a b l e s
(..) liefert
Julia Wolters
75
Übung SS 2009
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
// R e s u l t S e t , d a s s a l l e T a b e l l e n e n t h ä l t , d i e d i e mit den durch
d i e Parametern
// g e g e b e n e n K r i t e r i e n über einstim men . n e x t ( ) g i b t f a l s e zurück
, f a l l s kein
// n ä c h s t e s Element im R e s u l t S e t e x i s t i e r t e .
rs = db . getMetaData ( ) . getTables ( "" , "" , " personendaten " , n u l l ) ;
rs . beforeFirst ( ) ;
tableExists = rs . next ( ) ;
} c a t c h ( SQLException e ) {
reactOnSQLException ( e ) ;
}
// F a l l s T a b e l l e n n i c h t e x i s t i e r t e , e r z e u g e und f ü l l e s i e
i f ( ! tableExists ) {
// T a b e l l e n s c h e m a e r z e u g e n
try {
System . out . println ( " Erzeuge Tabellen ..." ) ;
constructPersonDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
}
// T a b e l l e f ü l l e n mit den Daten aus dem Person−Array p e r s o n s
try {
fillPersonDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// A u s l e s e n d e s gesamten D a t e n b e s t a n d e s d e r T a b e l l e ” p e r s o n e n d a t e n ”
und Ausgabe
try {
sqlString = " SELECT * FROM personendaten " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbank nach dem Einlesen des
Datenbestandes :" ) ;
printResultSet ( rs , 5 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Löschen d e s Person−Arrays und Neuanlegen mit den den Daten aus
d e r Personen−Datenbank
try {
persons = n u l l ;
persons = extractPersonsFromDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
76
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
Datenbanken
Musterlösungen
Übung SS 2009
reactOnSQLException ( sqlException ) ;
}
// Ausgabe d e r O b j e k t e i n p e r s o n s [ ]
System . out . println ( " Ausgabe der Objekte in persons []:" ) ;
f o r ( i n t i = 0 ; i < persons . length ; i++) {
persons [ i ] . printPerson ( ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen a l l e r Personen , deren Nachname mit ”J” b e g i n n t ,
g e o r d n e t nach
// a u f s t e i g e n d e n Nachnamen z u r ü c k .
try {
sqlString = " SELECT lastname , firstname FROM personendaten
WHERE " +
" lastname LIKE ’J%’ " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 2 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen und G e b u r t s d a t e n a l l e r Personen , d i e v o r 1960 g e b o r e n
s i n d , g e o r d n e t nach
// a u f s t e i g e n d e n G e b u r t s t a g e n z u r ü c k .
try {
sqlString = " SELECT lastname , firstname , dayofbirth FROM" +
" personendaten WHERE dayofbirth < ’1960 1 1’ order " +
" by dayofbirth " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 3 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen und G e b u r t s d a t e n a l l e r w e i b l i c h e n Personen , d i e
z w i s c h e n 1960 und 1970
Julia Wolters
77
Übung SS 2009
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
Mittwochs, 08 – 10 Uhr
Musterlösung
// g e b o r e n s i n d , g e o r d n e t nach a u f s t e i g e n d e n G e b u r t s t a g e n z u r ü c k .
try {
sqlString = " SELECT lastname , firstname , dayofbirth FROM" +
" personendaten WHERE dayofbirth >= ’1960 1 1’ " +
"and dayofbirth < ’1970 1 1’ and gender = ’female ’" +
" order by dayofbirth " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 3 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// E i n f ü g e n d e s genannten neuen E i n t r a g e s
try {
db . setAutoCommit ( f a l s e ) ;
sqlString = " DELETE FROM personendaten WHERE lastname = ’
Jackson ’" ;
executeUpdate ( sqlString ) ;
sqlString = " INSERT INTO personendaten values (’ Jackson ’, ’Five
’, 5, NULL , NULL)" ;
executeUpdate ( sqlString ) ;
db . commit ( ) ;
db . setAutoCommit ( t r u e ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
Datenbanken
Musterlösungen
// S c h l i e s s e n d e r Verbindung
try {
db . close ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
}
/∗ ∗
∗ L e g t d i e T a b e l l e ” p e r s o n d e n d a t e n ” i n d e r Datenbank an
∗
∗ @throws SQLException
∗/
p u b l i c v o i d constructPersonDatabase ( ) throws SQLException {
String sqlString ;
Statement stmt ;
// Erzeugen d e r T a b e l l e
78
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
Datenbanken
Musterlösungen
Übung SS 2009
sqlString = " CREATE TABLE personendaten ( lastname VARCHAR (20) ," +
" firstname VARCHAR (20) , ssn integer , dayofbirth CHAR (12) ," +
" gender CHAR (6) , PRIMARY KEY (ssn), CHECK " +
" ( gender in (’male ’,’female ’)));" ;
stmt = db . createStatement ( ) ;
stmt . execute ( sqlString ) ;
}
/∗ ∗
∗ Führt e i n e Anfrage an d i e v e rb un d en de Datenbank aus .
∗
∗ @param s q l S t r i n g Das a u s z u f ü h r e n d e SQL−S t a t e m e n t a l s S t r i n g .
∗ @return Das R e s u l t S e t , das das E r g b n i s d e r Anfrage an d i e Datenbank
e n t h ä l t .
∗ @throws SQLException
∗/
p u b l i c ResultSet executeQuery ( String sqlString ) throws SQLException {
Statement statement = db . createStatement ( ResultSet .
TYPE_SCROLL_INSENSITIVE ,
ResultSet . CONCUR_UPDATABLE
);
r e t u r n statement . executeQuery ( sqlString ) ;
}
/∗ ∗
∗ Führt e i n Update i n d e r verbundenden Datenbank aus .
∗
∗ @param s q l S t r i n g Das a u s z u f ü h r e n d e SQL−S t a t e m e n t a l s S t r i n g .
∗ @return Angabe ü b e r den E r f o l g d e s Updates
∗ @throws SQLException
∗/
p u b l i c i n t executeUpdate ( String sqlString ) throws SQLException {
Statement statement = db . createStatement ( ) ;
r e t u r n statement . executeUpdate ( sqlString ) ;
}
/∗ ∗
∗ F ü l l t d i e T a b e l l e ” p e r s o n e n d a t e n ” mit dem D a t e n b e s t a n d im
PersonenArray . Vorab werden
∗ d i e a l t e n T a b e l l e n i n h a l t e g e l ö s c h t .
∗
∗ @throws SQLException
∗/
p u b l i c v o i d fillPersonDatabase ( ) throws SQLException {
Statement stmt = db . createStatement ( ) ;
String sqlString = " DELETE FROM personendaten " ;
stmt . executeUpdate ( sqlString ) ;
f o r ( i n t i = 0 ; i < persons . length ; i++) {
Julia Wolters
79
Übung SS 2009
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
sqlString = " INSERT INTO personendaten VALUES (’"
+ persons [ i ] . getLastname ( ) + "’, ’"
+ persons [ i ] . getFirstname ( ) + "’, "
+ Integer . toString ( persons [ i ] . getSsn ( ) ) + ",’"
+ getSqlDateByJavaGregorianCalendar ( persons [ i ] .
getDayOfBirth ( ) ) + "’, ’"
+ persons [ i ] . getGender ( ) + " ’)" ;
stmt . executeUpdate ( sqlString ) ;
}
}
/∗ ∗
∗ K o n v e r t i e r t e i n O b j e k t vom Typ e i n O b j e k t vom Typ G r e g o r i a n C a l e n d a r
in
∗ e i n e n S t r i n g im SQL−Date−Format
∗
∗ @param c a l e n d a r
∗ @return e i n S t r i n g im SQL−Date−Format
∗/
p u b l i c s t a t i c String getSqlDateByJavaGregorianCalendar (
GregorianCalendar calendar ) {
String ret ;
ret = calendar . get ( java . util . Calendar . YEAR ) + " "
+ calendar . get ( java . util . Calendar . MONTH ) + " "
+ calendar . get ( java . util . Calendar . DAY_OF_MONTH ) ;
r e t u r n ret ;
}
/∗ ∗
∗ K o n v e r t i e r t e i n e n S t r i n g im SQL−Date−Format ( z .B. 1999 2 12) i n e i n
∗ O b j e k t vom Typ G r e g o r i a n C a l e n d a r .
∗
∗ @param s q l D a t e
∗
e i n S t r i n g im SQL−Date−Format
∗ @return e i n O b j e k t vom Typ G r e g o r i a n C a l e n d a r .
∗/
p u b l i c s t a t i c GregorianCalendar getJavaGreogorianCalendareBysqlDate (
String sqlDate ) {
i f ( sqlDate == n u l l ) r e t u r n new GregorianCalendar ( ) ;
else {
StringTokenizer tokenizer = new StringTokenizer ( sqlDate , " " ) ;
r e t u r n new GregorianCalendar ( Integer . parseInt ( tokenizer .
nextToken ( ) ) ,
Integer . parseInt ( tokenizer .
nextToken ( ) ) , Integer
. parseInt ( tokenizer . nextToken ( ) ) ) ;
}
}
80
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
Datenbanken
Musterlösungen
Übung SS 2009
/∗ ∗
∗ E r z e u g t aus dem I n h a l t d e r T a b e l l e ” p e r s o n e n d a t e n ” d e r Datenbank e i n
Array von I n s t a n z e n
∗ d e r K l a s s e Person . Ruft e x t r a c t P e r s o n s F r o m R e s u l t S e t ( ) a u f .
∗
∗ @return @throws SQLException
∗/
p u b l i c Person [ ] extractPersonsFromDatabase ( ) throws SQLException {
Statement statement = db . createStatement ( ResultSet .
TYPE_SCROLL_INSENSITIVE ,
ResultSet . CONCUR_UPDATABLE
);
String sqlString = " SELECT * FROM personendaten " ;
ResultSet rs = statement . executeQuery ( sqlString ) ;
Person [ ] ret = extractPersonsFromResultSet ( rs ) ;
r e t u r n ret ;
}
/∗ ∗
∗ E r z e u g t aus einem R e s u l t S e t −O b j e k t e i n Array von I n s t a n z e n d e r
Klasse
∗ Person . Wird von e x t r a c t P e r s o n s F r o m D a t a b a s e ( ) a u f g e r u f e n .
∗
∗ @param r s Das R e s u l t S e t , aus dem d i e Personendaten zu e x t r a h i e r e n
sind .
∗ @return Ein Array von Person−Obj ekte n , das d i e e x t r a h i e r t e n Daten
∗
e n t h ä l t .
∗ @throws SQLException
∗/
p u b l i c s t a t i c Person [ ] extractPersonsFromResultSet ( ResultSet rs )
throws SQLException {
i n t count = 0 ;
rs . beforeFirst ( ) ;
w h i l e ( ! rs . isLast ( ) ) {
rs . next ( ) ;
count++;
}
Person [ ] ret = new Person [ count ] ;
rs . beforeFirst ( ) ;
count = −1;
w h i l e ( ! rs . isLast ( ) ) {
rs . next ( ) ;
count++;
b o o l e a n female ;
i f ( rs . getString ( " gender " ) != n u l l && rs . getString ( " gender " ) .
equalsIgnoreCase ( Person . FEMALE_STRING ) ) {
Julia Wolters
81
Übung SS 2009
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
female = t r u e ;
} else {
female = f a l s e ;
}
Person person = new Person ( rs . getString ( " lastname " ) , rs .
getString ( " firstname " ) , rs . getInt ( "ssn" ) ,
getJavaGreogorianCalendareBysqlDate ( rs . getString ( " dayofbirth
" ) ) , female ) ;
ret [ count ] = person ;
}
r e t u r n ret ;
}
/∗ ∗
∗ Lö s c h t a l l e Tupel aus d e r T a b e l l e ” p e r s o n e n d a t e n ” d e r Datenbank .
∗
∗ @throws SQLException
∗/
p u b l i c v o i d clearPersonDatabase ( ) throws SQLException {
Statement stmt = db . createStatement ( ) ;
String sqlString = " DELETE FROM personendaten " ;
stmt . executeUpdate ( sqlString ) ;
}
/∗ ∗
∗ @ d e p r i c a t e d H e r s t e l l e n d e r Verbindung z u r Datenbank und S e t z e n d e r
die
∗
Verbindung c h a r a k t i e r i s i e r e n d e n A t t r i b u t e d e s
aufrufenden
∗
Objektes
∗ @param i n D r i v e r
∗
Der Name d e s T r e i b e r s . E n t s p r i c h t dem a b s o l u t e n
Klassenpfad
∗
der T r e i b e r k l a s s e
∗ @throws ClassNotFoundException
∗
f a l l s e i n f a l s c h e r T r e i b e r s p e z i f i z i e r t wurde
∗ @throws SQLException
∗
f a l l s beim Ansprechen d e r Datenbank F e h l e r von d i e s e r
∗
g e m e l d e t werden
∗/
p u b l i c v o i d establishAndConnect ( String inDriver )
throws ClassNotFoundException , SQLException {
Class . forName ( inDriver ) ;
db = DriverManager . getConnection ( dbName , userName , password ) ;
}
/∗ ∗
∗ G i b t e i n R e s u l t S e t i n T a b e l l e n f o r m a u f den B i l d s c h i r m aus .
82
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
Datenbanken
Musterlösungen
Übung SS 2009
∗
∗ @param r s Das R e s u l t S e t , das a u s g e g e b e n werden s o l l
∗ @param numOfColumns
∗
Die Anzahl d e r A t t r i b u t w e r t e ( d . h . S p a l t e n ) d e r
anzuzeigenden
∗
Tupel ( d . h . Z e i l e n )
∗ @throws SQLException
∗/
p u b l i c s t a t i c v o i d printResultSet ( ResultSet rs , i n t numOfColumns )
throws SQLException {
rs . beforeFirst ( ) ;
w h i l e ( ! rs . isLast ( ) && rs . next ( ) ) {
f o r ( i n t i = 1 ; i <= numOfColumns ; i++) {
System . out . print ( rs . getString ( i ) + " " ) ;
}
System . out . println ( ) ;
}
}
/∗ ∗
∗ Methode z u r Ausnahmebehandlung während d e s Versuches , mit d e r
Datenbank
∗ zu kommunizieren .
∗
∗ @param s q l E x c e p t i o n Die g e w o r f e n e Ausnahme
∗/
p r i v a t e v o i d reactOnSQLException ( SQLException sqlException ) {
b o o l e a n isFirstException = t r u e ;
w h i l e ( isFirstException | | ( sqlException . getNextException ( ) != n u l l
)) {
System . out
. println ( " Während der Arbeit mit der Datenbank trat
folgender Fehler auf: "
+ sqlException . toString ( )
+ " Code:"
+ sqlException . getErrorCode ( ) ) ;
System . out . println ( "Die Verbindung verblieb in folgendem Status
: "
+ sqlException . getSQLState ( ) ) ;
isFirstException = f a l s e ;
}
}
/∗ ∗
∗ @param a r g s
∗/
p u b l i c s t a t i c v o i d main ( String args [ ] ) {
PersonenJDBC personenJDBC = new PersonenJDBC ( ) ;
Julia Wolters
83
Übung SS 2009
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
// B e r e i t s t e l l e n d e r E i n g a b e p a r a m e t e r f ü r d i e Verbindung mit d e r
Datenbank ; p r ü f t
// vorab , ob e i n Passwort a l s A u f r u f p a r a m e t e r ü b e r g e b e n wurde
i f ( args . length == 3 ) {
personenJDBC . dbName = args [ 0 ] ;
personenJDBC . userName = args [ 1 ] ;
personenJDBC . password = args [ 2 ] ;
} e l s e i f ( args . length == 2 ) {
personenJDBC . dbName = args [ 0 ] ;
personenJDBC . userName = args [ 1 ] ;
// F a l l s k e i n Passwort ü b e r g e b e n wurde , s e t z e das Passwort a l s
leeren String
personenJDBC . password = "" ;
} else {
// F a l l s d i e P a r a m e t e r a n z a h l n i c h t s i n n v o l l i s t , beende das
Programm
// mit F e h l e r m e l d u n g
System . err
. println ( " Benutzung : java PersonenJDBC <dbname > <dbusername
> "
+ "[< dbuserpasswd >] \n" ) ;
System . err
. println ( " Beispiel : java PersonenJDBC jdbc: postgresql ://
padme .uni - muenster .de/ mustermanndb mustermann }\n" +
"
oder java PersonenJDBC jdbc: mysql :// cpc.uni muenster .de/ mustermanndb mustermann " ) ;
System . exit ( 1 ) ;
}
try {
i f ( personenJDBC . dbName . startsWith ( "jdbc: postgresql :" ) ) {
System . out . println ( " Benutze PostgreSQL ." ) ;
personenJDBC . establishAndConnect ( PersonenJDBC . PSQL_DRIVER ) ;
} e l s e i f ( personenJDBC . dbName . startsWith ( "jdbc: mysql :" ) ) {
System . out . println ( " Benutze MySQL ." ) ;
personenJDBC . establishAndConnect ( PersonenJDBC . MYSQL_DRIVER )
;
} else {
System . err . println ( " Unbekanntes Datenbankssystem .\n" ) ;
System . exit ( 1 ) ;
}
} c a t c h ( java . lang . ClassNotFoundException e ) {
System . out
. println ( " Verbindung mit der Datenbank fehlgeschlagen :
Treiber nicht gefunden . "
+ e . toString ( ) ) ;
} c a t c h ( SQLException sqlException ) {
84
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
436
437
438
439
440
441
442
Datenbanken
Musterlösungen
Übung SS 2009
personenJDBC . reactOnSQLException ( sqlException ) ;
}
// Führt d i e i n d e r A u f g a b e n s t e l l u n g genannten Anforderungen aus .
personenJDBC . doRequiredOperations ( ) ;
}
}
Julia Wolters
85
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 45
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
−− Aufgabe 45
−− geänderte Definition von depositor
create table depositor (
customer_name
c h a r ( 2 0 ) not n u l l ,
account_number c h a r ( 1 0 ) not n u l l ,
primary key ( customer_name , account_number ) ,
foreign key ( customer_name )
references customer
on delete cascade ,
foreign key ( account_number )
references account
on delete cascade
);
−− alle Konten mit Anzahl ihrer Inhaber
create view account_customer_count as
select account_number , count ( customer_id ) as customer_count
from depositor
group by account_number ;
−− Löschen eines Kunden verhindern , wenn es ein Konto mit Kontostand > 0
gibt , das nur diesem Kunden gehört
create trigger customer_delete1
before delete on customer
referencing old row as orow
f o r each row
when
( exists
( select account_number
from account natural join
account_customer_count natural join
depositor
where customer_id = orow . customer_id and
balance > 0 and customer_count = 1 ) )
begin
rollback work
end ;
32
33
34
35
36 −− Beim Löschen eines Kunden alle Konten mit Kontostand 0 löschen , die nur
diesem Kunden gehören
37 create trigger customer_delete2
38
before delete on customer
39
referencing old row as orow
40
f o r each row
41
begin
86
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
42
43
44
45
Übung SS 2009
delete from account
where account_number in
( select account_number
from account natural join
account_customer_count natural
join depositor
where customer_id = orow .
customer_id and balance = 0 and
customer_count = 1 )
46
47
Datenbanken
Musterlösungen
end ;
Julia Wolters
87
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 46
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
−− Aufgabe 46
−− Erzeugen der Sicht :
CREATE VIEW customersInBrooklyn AS
SELECT customer_name , customer_street , customer_city
FROM customer NATURAL JOIN
( SELECT DISTINCT customer_name
FROM depositor NATURAL JOIN account NATURAL JOIN branch
WHERE branch_city = ’Brooklyn ’ ) AS temp1
UNION
SELECT customer_name , customer_street , customer_city
FROM customer NATURAL JOIN
( SELECT DISTINCT customer_name
FROM borrower NATURAL JOIN loan NATURAL JOIN branch
WHERE branch_city = ’Brooklyn ’ ) AS temp2 ;
−− Entziehen aller Rechte an den an der Sicht beteiligten Tabellen :
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
ALL
ALL
ALL
ALL
ALL
ALL
ON
ON
ON
ON
ON
ON
customer FROM Joe ;
branch FROM Joe ;
depositor FROM Joe ;
account FROM Joe ;
borrower FROM Joe ;
loan FROM Joe ;
−− Vergeben eines Leserechtes für die erzeugte Sicht :
GRANT SELECT ON customersInBrooklyn TO Joe ;
−− analog für Mary
−− Bemerkung : Reihenfolge ist unerheblich , Revoken der Recht für Tabellen
wirkt
34 −−
sich nicht auf Rechte für Sichten aus .
88
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 47
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Aufgabe 4 7 :
c l a s s Person ( extent personen key name ) {
attribute struct Pname { string Vorname , string Nachname } name ;
relationship set<Person> bin−Mutter−von−Kind
inverse Person : : bin−Kind−von−Mutter ;
relationship set<Person> bin−Vater−von−Kind
inverse Person : : bin−Kind−von−Vater ;
relationship Person bin−Ehepartner−von
inverse Person : : bin−Ehepartner−von ;
relationship Person bin−Kind−von−Mutter
inverse Person : : bin−Mutter−von−Kind ;
relationship Person bin−Kind−von−Vater
inverse Person : : bin−Vater−von−Kind ;
relationship set<Person> bin−Kind−von
inverse Person : : bin−Elternteil−von−Kind ;
relationship set<Person> bin−Elternteil−von−Kind
inverse Person : : bin−Kind−von ;
};
Inverse Beziehung zur n:1− Beziehung "bin -Kind -von - Mutter " ist :
"bin -Mutter -von -Kind" ( 1 : n−Beziehung )
und nicht : bin−Elternteil−von−Kind"
(2:n- Beziehung )
select
struct (
nachname : p.name.Nachname ,
mutter : p. mutter .name ,
vater : p. vater .name
)
Julia Wolters
89
Übung SS 2009
47
48
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
from p in personen
where p.name. Vorname = ’Hans ’
90
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Aufgabe 48
(a)
• Entität = Ansammlung von Attributwerten
• Objekte enthalten Werte von Methoden (Zugang auf Werte sollte über Methoden
erfolgen)
• Gleichheitsbegriff
(b) OO : Unterschied auch bei gleichen Attributwerten (z.B. Speicheradressen) relationale: Gleichheit von zwei Tupeln bei Gleichheit aller Alttributwerte
(c) Geschindigkeit / Aufwand
• aufwändiger beim Erzeugen, Löschen, Modifizieren
• nötig wegen
– Transaktionsmanagement
– Sichherheitsmanagement
– Integritätssicherung
(d)
• Nicht möglich durch Programm, Objekte ja gerade zugreifbar über Referenz.
• 1. Lösung: Garbage Collection (mark and sweep)
– markiere Objekte wie folgt:
1. direkt durch das Programm referenzierte Objekte
2. von solchen Objekten referenzierte Objekte
– Lösche markierte Objekte
• 2. Lösung: Zähler von Referenzen
– jedes Objekt zählt die auf sich verwiesenen Objekte
– Erzeugen / Entfernen einer Referenz
→ Benachrichtigung und Aktualität des Zählers
Zähler = 0 ⇒ Objekt zerstören.
(e) weitere Elemente des OODBMS
• Transaktionsmanagement
• Sicherheitsmanagement
• Integritätssicherung
Julia Wolters
91
Übung SS 2009
Datenbanken
Musterlösungen
Mittwochs, 08 – 10 Uhr
Musterlösung
Aufgabe 49
Geben Sie in Stichpunkten an, in welchen Punkten ein objektorientiertes DBMS einem relationalen DBMS überlegen ist, welche dieser Punkte bereits ein objektrelationales DBMS
erfüllt und worin ein objektrelationales DBMS einem objektorientierten DBMS überlegen
ist. Nennen Sie jeweils ein typisches Anwendungsbeispiel.
Empfehlen Sie dann für jeden der folgenden Anwendungsbereiche eine der oben genannten
DBMSTechnologien und begründen Sie kurz Ihre Empfehlung.
(a) Entwicklung eines Systems zur Unterstützung der Konstruktionsabteilung im Maschinenbau. Hier werden vor allem Vektordaten erstellt, manipuliert und gespeichert.
Zusätzlich sind in der Arbeitsvorbereitung betriebsspezifische Informationen und Angaben zur Arbeitszeit hinzuzufügen.
• Komplexe Datentypen
• Viele Datentypen - spezifische Manipulation / komplexe Anfragen
→ Methoden für einzelne Objektklassen erwünscht, höhere Programmiersprache
für Anfragen
⇒ OO-DBMS
(b) Entwicklung eines Systems zur Unterstützung elektronisch abzugebener Stimmzettel
bei der nächsten Kommunalwahl. Unabhängig von Sicherheitsaspekten liegt die Aufgabe Ihrer Komponente insbesondere in der schnellen Analyse und Aufbereitung der
Wählerstimmen unter verschiedenen Gesichtspunkten.
• Simple Datentypen
• große Datenmenge, schnelle Analyse wichtig
⇒ relationale DBMS
(c) Entwicklung eines Systems zur Unterstützung von Jingles im Hörfunk. Zum einen
müssen in Anwendungsprogrammen erzeugte Tondateien abgespeichert und geladen
werden, zum anderen sollen bei Angabe eines Titels bestimmte vorher festgelegte
Ausschnitte eines Musikstücks aus der Datenbank geladen und abgespielt werden.
• große Datentypen
• Jingels enthalten Teile von Musikstücken → Verweise erwünscht zwecks Vermeidung von Redundanz
• Anfragen, Anforderungen sind simpel und fest vorgegeben
⇒ OR-DBMS
92
Julia Wolters
Mittwochs, 08 – 10 Uhr
Musterlösung
Datenbanken
Musterlösungen
Übung SS 2009
Thomas’ Schreibregel
• überflüssige Schreiboperationen werden nicht durchgeführt
• Transaktion Ti will write(A) ausführen
– T S(Ti ) < W-timestamp(A)
· der zu schreibende Wert ist veraltet
· der Wert muss nicht geschrieben werden
– ansonsten keine Änderungen
• resultierende Scheldules sind Sicht-serialisierbar
Aufgabe 52
Geben Sie zu dem folgenden Schedule für jede Schreib- und Leseoperation die jeweils
veränderten Werte der Zeitstempel für das Zeitstempelprotokoll an.Werden Transaktionen
zurückgesetzt?
1
2
3
4
5
6
7
8
9
T1
read(A)
T2
T3
read(A)
read(B)
read(B)
write(A)
read(A)
write(A)
write(B)
write(B)
Chronologische Reihenfolge:
1
2
3
4
5
6
7
8
9
Julia Wolters
TS(T1) = 1
TS(T2) = 2
TS(T3) = 3
R - TS(A) = 1
R - TS(A) = 2
R - TS(B) = 3
R - TS(B) = 3
W - TS(A) = 2
R - TS(A) = 3
W - TS(A) = 3
W - TS(B) = 3
TS(1) = 1 < R - TS(B) = 3
Möglichkeit 1: T1 zurücksetzten
Möglichkeit 2: Thomas’ Schreibregel
93