Übungsblatt 11 zur Vorlesung Datenbanksysteme I

Christian-Albrechts-Universität Kiel
Institut für Informatik
Lehrstuhl Technologie der Informationssysteme
Prof. Dr. B. Thalheim
Dipl.-Inf. P. Schmidt, Dipl.-Inf. K. Jannaschk
SS 2008
Übungsblatt 11
Übungen zur Vorlesung Datenbanksysteme I
Übungsblatt 11
Aufgabe 1 (8 Punkte)
Als Anlage finden Sie ein HERM-Schema, in dem bereits folgende Aussagen modelliert sind:
Es gibt Veranstaltungen. Konzerte sind Veranstaltungen. Jede Veranstaltung kann Teil einer Veranstaltungsreihe (z.B. Konzerttour) sein. Eine Veranstaltung findet an einem bestimmten Ort und
an einem bestimmten Datum statt. Auf Konzerten treten verschiedene Künstler auf. Für Veranstaltungen werden Tickets in verschiedenen Preiskategorien angeboten. Kunden können Tickets kaufen.
Die Datentypen der Attribute seien atomar und geeignet gewählt. Die Rollenbezeichner wurden
weggelassen.
Ergänzen Sie das HERM-Schema geeignet, um die folgenden Aussagen zu modellieren. Geben Sie
für jeden neuen Entity-Typ mindestens ein Attribut an.
• Veranstaltungsreihen werden thematisch in Kategorien (z.B. Rock/Pop, Jazz, etc.) eingeteilt.
Eine Veranstaltungsreihe ist eindeutig einer Kategorie zugeordnet. Einer Kategorie können
beliebig viele Veranstaltungsreihen zugeordnet werden. Die Kategorien selbst sind hierarchisch
geordnet: Eine Kategorie kann beliebig viele Unterkategorien und höchstens eine Oberkategorie besitzen.
• Sportveranstaltungen sind Veranstaltungen. Fußballspiele sind Sportveranstaltungen.
• In einem Fußballspiel spielen zwei Mannschaften gegeneinander. Zu einer Mannschaft gehören mindestens 11 Spieler und mindestens 1 Trainer. Jeder Spieler und jeder Trainer gehört
maximal einer Mannschaft an.
• Spieler einer Mannschaft können in einem Fußballspiel an einer bestimmten Position eingesetzt werden. Eingesetzte Spieler können Tore schießen.
Vergessen Sie nicht, die Schlüssel der neuen Entity-Typen, die Datentypen der neuen Attribute
(sofern nicht atomar) sowie die Kardinalitäten der neuen Beziehungstypen anzugeben.
Aufgabe 2 (4 Punkte)
Zum gegebenen HERM-Schema aus Aufgabe 1 sei die folgende Umsetzung in ein relationales Schema
definiert. Die Datentypen aller Attribute seien atomar und geeignet gewählt.
{
Veranstaltungsreihe({RTitel, Beschreibung}),
Veranstaltung({VTitel, Datum, Zeit, RTitel, OBez}),
Konzert({VTitel}),
Ort({OBez,Anfahrt}),
Künstler({KName,Bild}),
tritt_auf({KName,VTitel}),
Preiskategorie({VTitel,PKat,Preis}),
Ticket({VTitel,PKat,SerienNr}),
Kunde({KNr,Name,Anschrift}),
Kauf({VTitel,PKat,SerienNr,KNr})
}
Die Fremdschlüssel seien entsprechend dem HERM-Schema gewählt.
Ergänzen Sie dieses relationale Schema gemäß den Erweiterungen des HERM-Schemas aus Aufgabe
1. Jedes Relationenschema soll sich mindestens in der 1. Normalform befinden. Geben Sie neue
Schlüssel und Fremdschlüsselbeziehungen an.
Aufgabe 3 (7 Punkte)
Formulieren Sie folgende Anfragen an das Schema aus Aufgabe 2 mit Hilfe der relationalen Algebra.
Es sind nur die Operationen ∪, ∩, \, ./, σ, π, %, ÷ erlaubt.
a. Gib Ort und Datum aller Veranstaltungen, die Teil der Veranstaltungsreihe mit dem Titel
MTV Campus Invasion“ sind.
”
b. Gib die Namen aller Kunden, die ein Ticket für ein Konzert in Berlin gekauft haben, bei dem
Seeed“ auftreten.
”
c. Gib Titel, Ort und Datum der Veranstaltung, für die die teuersten Tickets angeboten werden.
d. Gib die Namen der Künstler, die bei allen Veranstaltungen der MTV Campus Invasion“
”
auftreten.
Aufgabe 4 (7 Punkte)
Formulieren Sie in SQL folgende Anfragen an das Schema aus Aufgabe 2. Hinweis: Bei Bedarf
dürfen Sie DB2-Spezifika benutzen.
a. Gib alle Orte, an denen Shakira“ auftritt.
”
b. Wieviel Geld hat der Kunde mit der Nummer 134 bisher für Konzerttickets ausgegeben?
c. Gib Titel, Ort und Datum der Veranstaltung, für die die meisten Tickets verkauft wurden.
d. Treten ’Linkin Park’ im Jahr 2007 in Hamburg auf? Die Antwort auf diese Frage soll eine
Tabelle mit einer einzigen Zeile und einer einzigen Spalte sein und die Werte ’ja’ oder ’nein’
enthalten.
Aufgabe 5 (8 Punkte)
Gegeben sei die unten angegebene Java-Klasse. Ergänzen Sie die Methode printSuspiciousCustomers, so dass diese die Daten (KNr, Name, Anschrift) derjenigen Kunden per JDBC aus der
Datenbank liest und auf der Konsole ausgibt, für die gilt:
• Der Kunde hat für alle Konzerte eines Künstlers Tickets gekauft, und/oder
• der Kunde hat für ein beliebiges Konzert mehr als 5 Tickets gekauft.
Formulieren Sie die Anfrage in einem einzigen SQL-Statement.
(Hinweis: Zusätzlich zu den Integritätsbedingungen im Schema dürfen Sie davon ausgehen, dass
alle Künstler auf mehreren Konzerten auftreten.)
package i s e . db1 ;
import j a v a . s q l . ∗ ;
c l a s s EventDB
{
/∗ . . . ∗/
/∗∗
∗ @param conn e i n e o f f e n e Verbindung z u r Datenbank
∗/
p u b l i c v o i d p r i n t S u s p i c i o u s C u s t o m e r s ( Connection conn )
throws SQLException
{
System . out . p r i n t l n ( ” V e r d a e c h t i g e Kunden : ”) ;
/∗ Ergaenzen S i e h i e r . . . ∗/
}
}
Aufgabe 6 (6 Punkte)
Gegeben sei ein Relationenschema R({A, B, C, D, E, F, G, H, I}) mit folgenden funktionalen Abhängigkeiten:
AB → CD
D → E
H → FG
BE → D
A → BC
B → DE
a. Bestimmen Sie alle Schlüssel zu R.
b. Geben Sie unter Benutzung des Synthesealgorithmus eine verlustfreie und abhängigkeitstreue
Zerlegung des Relationenschemas R in 3NF an.
c. Zusätzlich sei die mehrwertige Abhängigkeit A I gegeben. Überführen Sie ihr entstandenes
Schema in die vierte Normalform.
Bitte geben Sie bei Ihrer Abgabe Ihre Übungsgruppe an!
Abgabe bis Donnerstag, den 10.7.2008 12 Uhr im Schrein Eingang Herrmann-Rodewald-Str. 3.
Viel Erfolg!
.................
Beschreibung
Veranstaltungsreihe
RTitel
(1,*)
(0,1)
...........
......
... ...
......
.
KName
Künstler
(0,*)
tritt
auf
(1,*)
Bild
Konzert
... ..
......
.
.................
Datum Zeit
Anfahrt
Veranstaltung
VTitel
(1,1)
findet
statt
......
..........
..
...
...
..
...
.
...
...
...
...
..
...
..
...
.
.
...
.
..... ..... ..
..... .............
.....
.....
.....
.....
....
.....
.....
.....
.....
.....
.....
.....
.....
.....
.
.
.
.
.
....
.........
....
.....
....
.....
.....
.....
.....
....
.....
.....
......................
.
.
.
.
.
.
.
.
.
..... ........
....
..........
.....
.....
.....
..... .........
.
.
.
.
.
.
.
.
.
.
..... .....
.
.....
...
..........
..... .........
.....
..... .....
.....
......
.....
.....
.....
.....
....
.....
.....
.....
.....
........
.....
.....
.
.
.
.
.....
...
.....
.....
.....
.....
.....
....
.....
.....
.....
.....
.....
.....
..... ........
..........
..
Teil
von
..
..........
..... ........
.....
.....
.....
.....
.....
.....
....
.....
.....
.....
.
.
.
.....
....
....
.........
.....
.....
.....
.....
.....
.....
.....
....
.....
.....
.
.
.
.....
.
.....
.....
..... .........
......
(0,*)
........
..... ........
.....
.....
.....
.....
.....
.....
.....
.....
....
.....
.....
.....
.
.
.
.....
...
.....
.....
.
.
.
...
.
......
.....
.....
.....
.....
....
.....
.....
.....
.
.
.
.
.....
.....
.....
.....
.
.....
.
.
.....
...
..... .........
..........
..
.
.....
... ...
Ort
OBez
PKat
Preis
Preiskategorie
....
..... ......
..... ........
.....
.....
....
.....
.....
.....
.....
.....
.....
.....
.....
.....
.
.
.
.....
....
.....
.....
.
.
..... ..
.
.
... .....
.........
.............
.....
....
.....
.....
.....
.....
.....
.....
.....
.....
.....
.
.
.
.....
...
.....
.....
.
.
.
.
....
...
..........
..........
..... .................. ..
..
.....
SerienNr
Ticket
...
..... .....
..... .........
.....
.....
.....
.....
.....
.....
.....
.....
.....
.....
....
.....
.....
.....
.
.
.
.
.... .......
.........
..............
.....
....
.....
.....
.....
.....
.....
....
.....
.....
.
.....
.
.
.....
.....
.....
..... ....
.
.
.
.
..... ....
..
..........
..
(0,1)
Kauf
...
..... .....
..... .........
.....
.....
.....
.....
.....
.....
.....
.....
.....
.....
....
.....
.....
.....
.
.
.
.
....
.........
....
.....
....
.....
.....
.....
.....
.....
....
.....
.....
.
.
.....
.
.
.....
....
.....
.....
.
.
.
.
..... ....
..........
..
(0,*)
Name
Kunde
KNr
...........
......
Anschrift