Blatt 5 - DBIS Informatik

Universität Innsbruck - Institut für Informatik
Prof. Günther Specht, R. Binna, N. Krismer, M. Tschuggnall
12. November 2012
Proseminar Datenbanksysteme
Übungsblatt 5
Gegeben sei folgendes Schema, welches zur Speicherung eines Flugverwaltungssystems
dient:
flugplan
buchung
flughafen
flugnr CHAR(8)
von SMALLINT(6)
flughafen_id SMALLINT(6)
nach SMALLINT(6)
iata CHAR(3)
abflug TIME
icao CHAR(4)
ankunft TIME
name VARCHAR(50)
fluglinie_id SMALLINT(6)
buchung_id INT(11)
flug_id INT(11)
sitzplatz CHAR(4)
passagier_id INT(11)
preis DECIMAL(10,2)
Indexes
Indexes
montag TINYINT(1)
dienstag TINYINT(1)
mittwoch TINYINT(1)
donnerstag TINYINT(1)
samstag TINYINT(1)
sonntag TINYINT(1)
Indexes
flug
fluglinie
freitag TINYINT(1)
flug_id INT(11)
fluglinie_id SMALLINT(6)
flugnr CHAR(8)
iata CHAR(3)
von SMALLINT(6)
firmenname VARCHAR(20)
nach SMALLINT(6)
heimat_flughafen SMALLINT(6)
abflug DATETIME
Indexes
ankunft DATETIME
fluglinie_id SMALLINT(6)
passagier
passagier_id INT(11)
passnummer CHAR(9)
vorname VARCHAR(100)
nachname VARCHAR(100)
Indexes
flugzeug_id INT(11)
Indexes
passagierdetails
passagier_id INT(11)
geburtsdatum DATE
geschlecht CHAR(1)
wetterdaten
flugzeug
datum DATE
zeit TIME
station INT(11)
temp DECIMAL(3,1)
feuchtigkeit DECIMAL(4,1)
luftdruck DECIMAL(10,2)
wind DECIMAL(5,2)
wetter ENUM(...)
windrichtung SMALLINT(6)
Indexes
benutzer VARCHAR(15)
flug_id INT(11)
flugnr_alt CHAR(8)
flugnr_neu CHAR(8)
von_alt SMALLINT(6)
nach_alt SMALLINT(6)
nach_neu SMALLINT(6)
abflug_alt DATETIME
ankunft_alt DATETIME
abflug_neu DATETIME
ankunft_neu DATETIME
flugzeug_id_alt INT(11)
typ_id INT(11)
flugzeug_id_neu INT(11)
bezeichnung VARCHAR(50)
fluglinie_id_alt SMALLINT(6)
beschreibung TEXT
fluglinie_id_neu SMALLINT(6)
Indexes
kapazitaet INT(11)
datum DATETIME
von_neu SMALLINT(6)
flugzeug_typ
flugzeug_id INT(11)
flug_log
kommentar VARCHAR(200)
typ_id INT(11)
mitarbeiter
mitarbeiter_id INT(11)
fluglinie_id INT(11)
strasse VARCHAR(100)
ort VARCHAR(100)
plz SMALLINT(6)
land VARCHAR(100)
emailadresse VARCHAR(120)
telefonnummer VARCHAR(30)
Indexes
Indexes
vorname VARCHAR(100)
nachname VARCHAR(100)
geburtsdatum DATE
geschlecht CHAR(1)
strasse VARCHAR(100)
ort VARCHAR(100)
plz SMALLINT(6)
land VARCHAR(100)
emailadresse VARCHAR(120)
telefonnummer VARCHAR(30)
gehalt DECIMAL(8,2)
abteilung ENUM(...)
benutzername VARCHAR(20)
passwort CHAR(32)
Indexes
flughafen_geo
flughafen_id SMALLINT(6)
stadt VARCHAR(50)
land VARCHAR(50)
breite DECIMAL(11,8)
laenge DECIMAL(11,8)
geolokation POINT
Indexes
Machen Sie sich mit dem Schema vertraut und verwenden Sie zum Lösen der Aufgaben
das Webtool MyWebSQL, erreichbar unter http://dbis-ue.uibk.ac.at/mywebsql.
Proseminar Datenbanksysteme – Übungsblatt 5
Aufgabe 1 (SQL - Einfache Abfragen)
a) Geben Sie den Namen der Fluglinien mit den Kürzeln (iata) ’AU’, ’BR’ und ’CZ’
aus.
b) Welche Mitarbeiter (Vorname, Nachname, Gehalt) verdienen mehr als 2900 Euro?
Sortieren Sie die Ausgabe absteigend nach dem Gehalt.
c) Wieviele Personen können alle Flugzeuge aller Fluglinien insgesamt befördern?
d) Zeigen Sie die E-Mail-Adressen aller Mitarbeiter, die zwischen 1.1.1990 und 1.1.1996
geboren wurden.
e) Welche Flughäfen (Name) haben keinen internationalen Flughafencode (iata)?
f) Geben Sie alle männlichen Mitarbeiter aus, deren Vorname mit einem ’A’ beginnt
und mit einem ’m’ oder ’n’ endet. (Das Geschlecht wird mit dem einzelnen Zeichen
’m’ bzw. ’w’ gekennzeichnet).
Aufgabe 2 (SQL - Erweiterte Abfragen)
a) Welche Fluglinie (Name) hat ihren Heimatflughafen in Jerusalem?
b) Welcher Mitarbeiter (Vorname, Nachname, Gehalt) verdient am meisten? - Lösen
Sie die Aufgabe einmal unter Verwendung einer Subquery und einmal unter Verwendung des Befehls LIMIT.
c) Wieviele Fluglinien (Firmenname) fliegen am Montag oder Donnerstag?
d) Welche Fluglinien (Firmenname) fliegen am Montag und Donnerstag? - Lösen Sie
die Aufgabe einmal mit und einmal ohne Verwendung von Joins.
e) Welche Fluglinie (Firmenname) fliegt nicht am Montag? - Verwenden Sie einen
Outer Join.
f) Berechnen Sie die mittlere Feuchtigkeit, den maximalen Luftdruck und die mittlere
Windstärke der Wetterstationen 2 und 3.
g) Berechnen Sie den durchschnittlichen Preis aller ’B’ - Sitzplätze (Sitzplatz, Durchschnittspreis). Ordnen Sie zuerst nach Preisen (absteigend) und dann nach Sitzplätzen (aufsteigend), und geben Sie nur diejenigen aus, deren Durchschnittspreis
unter 250$ liegt.
h) Berechnen Sie innerhalb einer Query, wieviele Personen alle Flugzeuge der Linie
Thailand Airlines“ , ”Senegal Airlines” bzw. ”Namibia Airlines”jeweils insgesamt
”
befördern können. Berechnen Sie dabei die Einzelergebnisse für jede Airline (also
nicht die Gesamtsumme aller drei). Geben Sie schlussendlich den Namen der Airline, den Namen des Heimatflughafens, das zugehörige Land des Heimatflughafens
2
Proseminar Datenbanksysteme – Übungsblatt 5
und die jeweilige maximale Anzahl der Passagieren an, wenn die Gesamtzahl der
möglichen Passagiere größer als 5000 ist.
Aufgabe 3 (SQL - Komplexe Anfragen)
Betrachten Sie alle Flüge von ’AAA’ (iata) nach ’ZBO’ (iata), die 2010 und 2011 durchgeführt wurden. Ziel der Aufgabe ist es nun, unbeliebte Sitzplätze auf diesen Flügen zu
finden: Geben Sie deshalb konkret alle Sitzplätze aus, auf denen in allen Flügen zusammen weniger als 6 Personen gesessen sind.
Geben Sie zusätzlich die jeweilige Anzahl, die Nachnamen und das Geschlecht der
Personen aus, die auf den jeweiligen Sitzen gesessen sind. Fügen Sie am Ende noch für
jeden Passagier die Gesamtanzahl seiner bisher getätigten Buchungen an (unabhängig
von Datum, Abflugs- oder Ankunftsort). Sortieren Sie die Ergebnisse nach der Anzahl
der Personen auf den Sitzen.
Die Ausgabe sollte also wie folgt aussehen:
+-----------+--------+----------------------------------------------+
| Sitzplatz | Anzahl | Passagiere
|
+-----------+--------+----------------------------------------------+
| 1F
|
1
| Wolowitz/m (14)
|
| 14D
|
2
| Hofstadter/m (200), Cooper/m (1)
|
| 7A
|
3
| Schmidt/m (23), Mueller/w (7), Huber/w (145) |
| ...
|
... | ...
|
+-----------+--------+----------------------------------------------+
Hinweis: Verwenden Sie die MySQL-Befehle GROUP CONCAT und CONCAT für die zusammengesetzte Ausgabe.
Laden Sie Ihre Lösungen bis spätestens Montag, den 19.11.2012 9:00 im U2L unter
http://u2l-informatik.uibk.ac.at/ hoch.
3