Angabe zum dritten Beispiel des Labor ¨ubungsteils aus

Angabe zum dritten Beispiel des
Laborübungsteils aus Datenmodellierung SS07
Michael Jackl, Markus Pichlmair, Katrin Seyr, Fang Wei
[email protected]
4. Mai 2007
1 Allgemeines
In diesem Übungsteil sollten Sie das Erstellen von SQL-Anfragen üben. Dazu wird
unter http://websql.dbai.tuwien.ac.at (SS07 FIXED) ein Interface
zum Zugriff auf eine Postgres Datenbank (v.8.1) zur Verfügung gestellt. Falls Sie
lieber auf Ihrer eigenen Datenbank arbeiten, können Sie die Testdaten für dieses
Beispiel auf der Webseite als daten.zip herunterladen.
Bitte versuchen Sie nach Möglichkeit, die Beispiele eigenständig zu lösen, denn in
der Praxis (und bei der Prüfung) sind Sie auch auf sich alleine gestellt.
2 Das Abgabegespräch
1. Sie müssen sich über den LVAManager zu einem Abgabegespräch anmelden.
2. Sie müssen die ersten zwölf der untenstehenden Anfragen lösen. Weiters
müssen Sie fünf weitere Anfragen lösen, die Sie frei wählen können.
3. Sie kommen mit Studentenausweis und den Unterlagen auf USB Stick im
Informatiklabor zu der von Ihnen reservierten Zeit vorbei, und absolvieren
das Abgabegespräch. (Achtung: bitte kein docx, plain text bevorzugt.)
4. Sie haben maximal 20 Minuten für Ihre Abgabe.
5. Sie können maximal 15 Punkte bei der Abgabe erreichen. Sie werden dabei zu drei von Ihren siebzehn Anfragen befragt. Dabei können Sie jeweils
maximal fünf Punkte erreichen, wobei nicht nur die Korrektheit der Abfrage
überprüft wird, sondern Sie müssen die Abfragen auch erklären und modifizieren können. Weiters wird erwartet, dass Sie wissen, was die von Ihnen
1
in einem SQL Statement verwendeten Ausdrücke bedeuten bzw. was sie bewirken.
6. Die Tutoren tragen ein Protokoll des Abgabegesprächs ein. Die erreichte
Gesamtpunktezahl ist im LVAManager für Sie ersichtlich.
3
Sport und Ernährung - Die Datenbank
Sport und eine gesunde Ernährung bilden die Basis für sowohl körperliches als
auch geistiges Wohlbefinden. Sport und eine gute Ernährung sind auch notwendig, um den täglichen Herausfoderungen gewachsen zu sein und immer wieder
Spitzenleistungen zu erbringen. Wir haben eine Datenbank erstellt, um einerseits
ein bisschen zum Nachdenken über Bewegung und Ernährung anzuregen, und um
andererseits auch SQL zu lernen :-)
3.1 Beschreibung
Personen haben eine eindeutige ID, einen Namen, eine Adresse, eine Telefonnummer und ein Geburtsdatum. Betreuer und Sportler sind Personen. Betreuer haben
zusätzlich ein Gehalt. Es ist außerdem bekannt, ob sie einen Führerschein besitzen
oder nicht. Für Sportler ist das Gewicht und die Nationalität von Bedeutung.
Sportler können zu einem Team gehören. Ein Team besteht aus mindestens
zwei Sportlern. Eindeutig identifiziert wird das Team durch einen Namen. Jedes
Team wird von Betreuern in einer bestimmten Sportart betreut und kann auch mehrere Sportarten betreiben.
Eine Sportart wird identifiziert durch eine ID. Außerdem wird ein Name gespeichert und wieviel die Ausrüstung pro Jahr kostet. Zu jeder Sportart ist der Kalorienverbrauch pro halber Stunde bekannt. Er ist abhängig von der Sportart und
zusätzlich vom Gewicht des Sportlers. (z.B. beträgt beim Joggen der Verbrauch
einer 70kg schweren Person n kcal, einer 90 kg schweren Person m kcal). Es ist
bekannt, welcher Sportler welchen Sport betreibt, außerdem ist die Lieblingssportart einiger Sportler bekannt.
Speisen werden durch eine eindeutige ID identifiziert. Es werden Name und
die Kalorien gespeichert. Eine Mahlzeit ist abhängig vom Sportler, von der Speise
und dem Datum.
3.2 Das ER-Diagramm
Das entsprechende ER Modell sieht wie folgt aus:
2
[0,*]
speiseid
[1,1]
m_s
kcal
pid
gewicht
Speise
[1,1]
Mahlzeit
[0,*]
isst
name
name
nationalitaet
Person
datum
adresse
[0,*]
lieblingssport
[0,1]
Sportler
telefon
[0,*]
GebDatum
teamkomp
said
name
[2,*]
Sportart
[0,*]
[0,*]
betreibt
name
Team
kosten
[1,*]
[1,*]
[0,*]
betreut
[0,*]
Betreuer
[1,1]
gewicht
gehalt
kcal
fuehrerschein
Verbrauch
3.3 Das Relationenmodell
Person(pid, name, adresse, telefon, gebDatum)
Betreuer(Person.pid, fuehrerschein, gehalt)
Sportler(Person.pid, gewicht, nationalitaet)
Sportart(said, name, kosten)
Verbrauch(Sportart.said, gewicht, kcal)
Betreibt(Sportler.pid, Sportart.said)
Lieblingssport(Sportler.pid, Sportart.said)
Team(name)
Teamkomp(name, Sportler.pid)
Betreut(Team.name, Sportart.said, Betreuer.pid)
Speise(speiseid, name, kcal)
Mahlzeit(Speise.speise, Sportler.pid, datum)
3
3.4 Die SQL-Anfragen
1. Geben Sie den Namen und die Kalorien aller Speisen aus.
2. Geben Sie den Namen und die Kosten aller Sportarten aus und sortieren Sie
die Liste nach den Kosten.
3. Geben Sie den Namen und Kosten jener Sportarten aus, die weniger als 500
Euro im Jahr kosten. Sortieren Sie die Liste nach den Kosten.
4. Geben Sie für jede Sportart (Namen der Sportart) den Verbrauch für eine
Person mit 70kg Gewicht aus. Sortieren Sie die Liste absteigend nach dem
Verbrauch.
5. Wieviele Betreuer haben einen Führerschein?
6. Erstellen Sie folgende Statistik: Geben Sie eine Liste aller Sportler (es reicht
wenn Sie die ID ausgeben) aus, die Anzahl der von ihnen jeweils betriebenen
Sportarten und die durchschnittlichen Kosten, die für jeden anfallen. Falls
ein Sportler keine Sportart betreibt, dann setzen Sie bitte die Kosten auf “0”
(Keyword COALESCE)
7. Geben Sie jene Sportler (ID und Name) mit einem Gewicht unter 80kg aus,
die selbst nicht Betreuer sind.
8. Welcher Betreuer (ID und Name), der nach 1989 geboren wurde, verdient
das meiste Gehalt?
9. Geben Sie eine Liste aller Sportler (Namen und ID) aus. Wenn sie eine teure
Lieblingssportart (= Kosten über 500 Euro) betreiben, sollen auch der Name
der Lieblingssportart und die Kosten dazu ausgegeben werden.
10. Um wieviel kg wiegen österreichische Sportler durchschnittlich mehr als
deutsche Sportler?
11. Geben Sie den Namen jener Sportler aus, die alle Sportarten ausüben.
12. Geben Sie die beliebtesten (=am meisten gegessenen) Speisen und deren
Kalorien aus.
13. Gesucht sind jene Sportarten, die von mehr als der Hälfte der Sportler betrieben werden.
14. Geben Sie eine Liste aller Betreuer aus zusammen mit der Anzahl der Teams,
die sie betreuen.
4
15. Geben Sie den Namen jener Sportler aus, die in keinem Team Mitglied sind.
16. Geben Sie den Namen jener österreichischen Sportler aus, für welche keine
Lieblingssportart bekannt ist, die aber dennoch eine Sportart betreiben.
17. Geben Sie für jeden Betreuer (Name und ID) die Anzahl der von ihm betreuten Personen aus.
18. Geben Sie eine Liste aller Sportler (Namen) aus und, sofern vorhanden, die
Lieblingssportart (Name) und den damit verbundenen Verbrauch aus. (Achtung! Der Verbrauch ist abhängig von dem Körpergewicht des Sportlers.)
19. Geben Sie alle Personen aus, für die folgende Bedingungen erfüllt sind: Sie
sind nach 1989 geboren. Sie sind keine Betreuer. Sie sind italienische Sportler. Sie sind nicht Mitglied eines Teams. Sie haben noch nie “Bier” getrunken.
20. Geben Sie eine Liste aller Sportler, bei denen eine Lieblingssportart bekannt
ist aus, wieviele Apfelstrudel sie ohne zuzunehmen essen dürfen, wenn sie
eine halbe Stunde ihrer Lieblingssportart nachgehen.
21. Geben Sie den Namen jener Personen aus, die ausschließlich ein Team betreuen und auch Mitglied in dem Team sind. Der Name des Teams soll ebenso ausgegeben werden.
22. Geben Sie die Namen jener Speisen aus, die maximal einmal pro Person
gegessen wurden, es jedoch mindestens zwei solche Personen gibt.
23. Geben Sie alle Paare (Namen) von Sportlern aus, die Tennis als Sportart
betreiben.
24. Geben Sie für jede Speise (Name), die weniger als 100 kcal hat, an, wieviele
Speisen es gibt, die mehr Kalorien haben.
5