7. ¨Ubungsblatt - Universität Konstanz

Universität Konstanz
WS 2007/08
Lehrstuhl DBIS
Informationsmanagement
Prof. Dr. Marc H. Scholl / Svetlana Mansmann / Halldór Janetzko / Sören Schubert
7. Übungsblatt
(Ausgeteilt am: 03.12.2007, Abgabe: 10.12.2007,
P
20 Punkte)
Aufgabe 1: SQL-ANFRAGEN
(10 Punkte)
Damit du SQL-Befehle auf einem echten DBMS ausführen kannst, haben wir ein Benutzerkonto auf
einem relationalen DBMS PostgreSQL eingerichtet. Die Datenbank luft auf dem Uni-Server namens
phobos29.inf.uni-konstanz.de und kann übers Web unter
http://phobos29.inf.uni-konstanz.de/pgAdmin/ erreicht werden.
Logge dich um diese Aufgabe zu lösen auf der Webseite als Benutzer nobody mit Passwort www ein.
Mit diesem Konto dürfen Anfragen gestellt, aber keine Daten geändern werden. Benutze das Navigationsmenü auf der linken Seite um zu einzelnen Tabellen zu navigieren.
Die Datenbankinstanz für diese Aufgabe heißt cddb und die Tabellen liegen im Schema public. Die
Daten stammen aus freedb, einem kostenlosen Informationsdienst zu Musik-CDs im Internet. In den
Tabellen sind Daten von CD-Covers (Album und Interpreter, Genre und die Songs) gespeichert und
wie folgt strukturiert (Tabellennamen sind fett markiert und Attributnamen in Klammern aufgelistet).
albums (albumid, album)
artist2album (artist2albumid, artistid, albumid)
artists (artistid, artist)
cds (cdid, artist2albumid, ayear, genreid, discid)
cdtracks (songid, cdid, track)
genres (genreid, genre)
songs (songid, song)
Benutze den Link SQL oben rechts, um ein neues Fenster zu öffnen, in dem SQL-Anfragen eingeben
und ausgeführt werden können.
a) Führe folgende SQL-Anfragen aus und beschreibe sie in narürlicher Sprache (z.B. alle Alben
”
von Michael Jackson aus den 90er“). Kopiere die erhaltenen Treffer in eine Textdatei. Abzugeben sind die Beschreibungen der Anfragen sowie die erhaltenen Treffer. Sollten dir einige
der verwendeten SQL-Konstrukte unbekannt sein, scheue dich nicht, ihre Definitionen in der
PostgreSQL-Dokumentation unter
http://www.postgresql.org/files/documentation/pdf/8.1/postgresql-8.1-A4.pdf nachzuschlagen.
i) SELECT a1.albumid, ar1.artist, ar2.artist
FROM artist2album AS a1, artist2album AS a2,
artists AS ar1, artists AS ar2
WHERE a1.albumid=a2.albumid AND a1.artistid!=a2.artistid AND
a1.artistid=ar1.artistid AND a2.artistid= ar2.artistid
LIMIT 10
ii) SELECT ar1.artist, ar2.artist
FROM artists AS ar1, artists AS ar2
WHERE ar1.artist LIKE ’%’ || ar2.artist AND ar1.artist != ar2.artist
LIMIT 20
Bitte wenden!
−−−−−−−−−−→
-
iii) SELECT * FROM genres
WHERE genre LIKE ’%rock%’ AND genre NOT LIKE ’%pop%’
ORDER BY genre DESC
LIMIT 20
iv) SELECT DISTINCT track FROM cdtracks
ORDER BY track
v) SELECT AVG(ayear) AS avg_year
FROM cds c
WHERE ayear BETWEEN 1900 AND 2007
vi) SELECT DISTINCT album, ayear, track, song
FROM albums, artist2album, artists, cds, cdtracks, songs
WHERE artist=’mozart’ AND ayear>1700 AND
albums.albumid=artist2album.albumid AND
artist2album.artistid = artists.artistid AND
cds.artist2albumid=artist2album.artist2albumid AND
cdtracks.cdid= cds.cdid AND cdtracks.songid=songs.songid
ORDER BY ayear, album, track
LIMIT 30
b) Nun sollst du eigene SQL-Anfragen formulieren und ausführen, um folgende Fragen zu beantworten (Abzugeben sind die Anfragen sowie die jeweils erhaltenen Treffer, wobei die Anzahl der
Treffer je Anfrage auf 10 beschränkt werden soll).
i) Erstelle eine alphabetisch sortierte duplikatfreie Liste aller Genres von ABBA’s Alben (alle
Namen in cddb sind kleingeschrieben, z.B. abba).
ii) Gib alle Jahre aus (ohne Duplikate), in denen es CDs im Genre new age oder newage gab.
Aufgabe 2: DATENBANKENTWURF
(10 Punkte)
Ein ER-Schema für ein Flugbuchungssystem soll entworfen werden. Kunden sollen dort die typischen
Vorgänge eines Reservierungssystems durchführen können (Fluginformationen (Tarife, Strecken, freie
Plätze, Flugzeugtyp, Flugzeit, Zwischenstopps) abrufen, Reservierungen durchführen). Konstruiere
das ER-Schema und kennzeiche darin Schlüsselattribute und Kardinalitäten der Beziehungstypen.
Berücksichtige dabei folgende Anforderungen.
a) Eine Flugstrecke kann von mehreren Airlines bedient werden.
b) Ein Flug kann aus mehreren Teilstrecken bestehen (z.B. Zürich - San Francisco = Zürich - Atlanta, Atlanta - San Francisco).
c) Nicht jedes Flugzeug kann aufgrund seiner Größe/Bauart auf jedem Flughafen landen.
d) Für ein und denselben Flug kann es verschiedene Tarife geben (z.B. abhängig von Buchungsklasse, Buchungszeitraum, ...). Außerdem kann es für bestimmte Tarife Restriktionen geben
(z.B. Ticket kann nicht umgetauscht werden).
e) Jeder Flughafen ist durch einen dreistelligen Airportcode eindeutig bestimmt.
f) Der Kunde soll bei der Reservierung sehen können, mit welchem Flugzeugtyp er fliegt.
g) Bei der Buchung muss der Kunde seinen Namen, eine Telefonnummer und eine Kreditkartennummer angeben. Die Bearbeitung der Buchung (Rechnungs-/Ticketversandt) braucht nicht im
ER-Diagramm modelliert werden.
-