Die Olympia-Datenbank: Anfragen (Teil 4)

Lösen Sie die fol­gen­de Auf­ga­be mit Post­greS­QL.

Für spe­zi­el­le Syn­tax­fra­gen steht Ihnen die offi­zi­el­le Online-Doku­men­ta­ti­on von Post­greS­QL zur Ver­fü­gung.

Die Daten­ty­pen wer­den in Kapi­tel 8 des Post­greS­QL-Manu­als beschrie­ben. Die Datums­ty­pen DATE und TIMESTAMP fin­den Sie im Post­greS­QL-Manu­al in den Abschnit­ten Date/​Time Types und Date/​Time Func­tions and Ope­ra­tors.

Das Datenmodell

Gege­ben sei wie­der fol­gen­de Daten­bank zur Spei­che­rung von olym­pi­schen Wett­be­wer­ben:

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 Olym­pia-Daten­bank soll ein – etwas unkon­ven­tio­nel­ler, aber aus­sa­ge­kräf­ti­ge­rer – Medail­len­spie­gel erstellt wer­den:

Für eine Gold­me­dail­le erhält ein Land drei Punk­te, für eine Sil­ber­me­dail­le zwei und für eine Bron­ze­me­dail­le einen Punkt. Die Punk­te las­sen sich ganz ein­fach aus der Plat­zie­rung berech­nen: 4 - platz.

Erstel­len Sie eine geschlos­se­ne SQL-Que­ry (d.h. ohne Views), die die vier punkt­stärks­ten Län­der ermit­telt – ver­se­hen mit der Rang­num­mer und der Abwei­chung der Punkt­zahl vom Durch­schnitt der Punkt­zahl aller Teil­neh­mer­län­der.

Bei­spiel:

rang land punk­te abwei­chung
1 GER 123 87.5
2 USA 121 85.5
2 CHI 121 85.5
4 RUS 99 63.5

Sie soll­ten bei der Lösung der Auf­ga­be schritt­wei­se vor­ge­hen. Defi­nie­ren Sie für jedes Zwi­schen-Ergeb­nis eine eige­ne View und ver­wen­den Sie die­se für die nächs­ten Zwi­schen-Ergeb­nis­se. Erset­zen Sie ganz zum Schluss jeden View-Auf­ruf durch die jewei­li­ge View-Defi­ni­ti­on.

Die ein­zel­nen Teil­schrit­te sind in der Datei olympia_aufgabe_4.sql ent­hal­ten. Kopie­ren Sie die­se Datei auf Ihren Rech­ner und tra­gen Sie Ihre Lösun­gen in die Datei ein.

Ach­ten Sie bit­te dar­auf, dass Ihre SQL-Anfra­gen für jeden gül­ti­gen Daten­be­stand genau die gewünsch­ten Infor­ma­tio­nen als Ergeb­nis aus­ge­ben, nicht mehr und nicht weni­ger.

Tipp

Es ist etwas umständ­lich, Ergeb­nis­tu­pel in SQL durch­zu­num­me­rie­ren. Sehen Sie sich das Bei­spiel counter.sql an.