Die Olympia-Datenbank: Anfragen (Teil 4)

Lösen Sie die folgende Aufgabe mit PostgreSQL.

Für spezielle Syntaxfragen steht Ihnen die offizielle Online-Dokumentation von PostgreSQL zur Verfügung.

Die Datentypen werden in Kapitel 8 des PostgreSQL-Manuals beschrieben. Die Datumstypen DATE und TIMESTAMP finden Sie im PostgreSQL-Manual in den Abschnitten Date/Time Types und Date/Time Functions and Operators.

Das Datenmodell

Gegeben sei wieder folgende Datenbank zur Speicherung von olympischen Wettbewerben:

Datenbankschema

sportler:  id, name, vorname*, geburtstag, land, geschlecht
           {PK: id}
           {UNIQUE: name, vorname*, geburtstag, land, geschlecht}
           {geschlecht IN ('w', 'm')}

wettkampf: id, sportart, wettkampfart, geschlecht, sportstaette, beginn*
           {PK: id}
           {UNIQUE: sportart, wettkampfart, geschlecht}
           {geschlecht IN ('w', 'm')}

teilnahme: s_id, w_id, platz*, doping*, disqualifiziert
           {PK: s_id, w_id}
           {FK: s_id -> sportler: id}
           {FK: w_id -> wettkampf: id}
           {platz > 0}
           {NOT(doping IS TRUE AND disqualifiziert IS FALSE)}
           {NOT(disqualifiziert IS TRUE AND platz IS NOT NULL)}

Aufgabe

Für Ihre Olympia-Datenbank soll ein – etwas unkonventioneller, aber aussagekräftigerer – Medaillenspiegel erstellt werden:

Für eine Goldmedaille erhält ein Land drei Punkte, für eine Silbermedaille zwei und für eine Bronzemedaille einen Punkt. Die Punkte lassen sich ganz einfach aus der Platzierung berechnen: 4 - platz.

Erstellen Sie eine geschlossene SQL-Query (d.h. ohne Views), die die vier punktstärksten Länder ermittelt – versehen mit der Rangnummer und der Abweichung der Punktzahl vom Durchschnitt der Punktzahl aller Teilnehmerländer.

Beispiel:

rang land punkte abweichung
1 GER 123 87.5
2 USA 121 85.5
2 CHI 121 85.5
4 RUS 99 63.5

Sie sollten bei der Lösung der Aufgabe schrittweise vorgehen. Definieren Sie für jedes Zwischen-Ergebnis eine eigene View und verwenden Sie diese für die nächsten Zwischen-Ergebnisse. Ersetzen Sie ganz zum Schluss jeden View-Aufruf durch die jeweilige View-Definition.

Die einzelnen Teilschritte sind in der Datei olympia_aufgabe_4.sql enthalten. Kopieren Sie diese Datei auf Ihren Rechner und tragen Sie Ihre Lösungen in die Datei ein.

Achten Sie bitte darauf, dass Ihre SQL-Anfragen für jeden gültigen Datenbestand genau die gewünschten Informationen als Ergebnis ausgeben, nicht mehr und nicht weniger.

Tipp

Es ist etwas umständlich, Ergebnistupel in SQL durchzunummerieren. Sehen Sie sich das Beispiel counter.sql an.