Die Olympia-Datenbank: Anfragen (Teil 1)

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-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. Datum und Uhr­zeit kön­nen Sie mit der Funk­ti­on to_char for­ma­tie­ren.

Für die Lösung der Auf­ga­ben kön­nen Ihnen auch noch die SQL-Funk­tio­nen LIKE und COALESCE gute Diens­te leis­ten.

Das Datenmodell

Gege­ben sei 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)}

Die Bedeu­tung der Attri­bu­te der Tabel­le wettkampf sieht man sehr am ein­fachs­ten an zwei Bei­spiel­stu­peln:

INSERT INTO wettkampf(id, sportart, wettkampfart, geschlecht, sportstaette, beginn)
VALUES
(1, 'Judo',      'bis 57kg',   'w', 'Halle 1',        TIMESTAMP '2004-08-14 16:30'),
(2, 'Schwimmen', '400m Lagen', 'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-14 17:30')
;

Im Attri­but teilnahme.platz wird nach dem jewei­li­gen Wett­kampf der Platz ein­ge­tra­gen, den der jewei­li­ge Teil­neh­mer belegt.

Im Attri­but teilnahme.doping wird nach einem Doping­test das Ergeb­nis ein­ge­tra­gen (doping IS TRUE).

Im Attri­but teilnahme.disqualifiziert wird ein­ge­tra­gen, ob der Sport­ler im zuge­hö­ri­gen Wett­kampf dis­qua­li­fi­ziert wur­de (z.B. wegen Dopings oder wegen unsport­li­chen Ver­hal­tens).

Bei der Berech­nung von Datum- und Zeit­an­ga­ben hält sich Post­greS­QL sehr schön an den SQL99-Stan­dard: Date/​Time Func­tions and Ope­ra­tors, Datums­for­ma­tie­rung.

Aufgabe

  1. Erstel­len Sie eine Olym­pia-Daten­bank. Ihnen ste­hen vier mög­li­che Daten­ban­ken zur Ver­fü­gung:
    • Ver­wen­den Sie Ihre eige­ne Olym­pia-Daten­bank. In die­sem Fall müs­sen Sie in den SQL-Anfra­gen die Namen der Sport­ler aller­dings geeig­net anpas­sen.
    • Ver­wen­den Sie olympia1_create.sql (simp­le Ver­si­on).
    • Ver­wen­den Sie olympia2_create.sql (robus­te Ver­si­on).
    • Ver­wen­den Sie olympia3_create.sql (robus­te Ver­si­on plus simp­le Ver­si­on mit­tels Views).

    Dem Daten­bank­sche­ma, das in der robus­ten Ver­si­on defi­niert wird, liegt ein wesent­lich kom­ple­xe­res Daten­mo­dell zugrun­de: olympia2_uml.png. Der Vor­teil die­ser Ver­si­on ist, dass vie­le Daten­feh­ler dadurch ver­mie­den wer­den, dass vier Enu­me­ra­ti­on-Tabel­len ein­ge­führt und zwei wei­te­re, ziem­lich kom­ple­xe Check-Cons­traints mit­tels Cons­traint-Tig­gern defi­niert wur­den. Sie kön­nen also kei­ne Län­der, Sport­ar­ten, Wett­kampf­ar­ten oder Sport­stät­ten ein­ge­ben kön­nen, die zuvor nicht erfasst wur­den. Außer­dem kön­nen Frau­en nur an Frau­en­wett­be­wer­ben teil­neh­men und Män­ner an Män­ner­wett­be­wer­ben. Dar­über hin­aus wird sicher­ge­stellt, dass nie­mand einen Platz bele­gen kann, den es auf­grund der Teil­neh­mer­an­zahl gar nicht gibt. Der Nach­teil die­ses Modells ist, dass die Lösun­gen der SQL-Auf­ga­ben, die Sie bear­bei­ten sol­len, deut­lich auf­wän­di­ger sind (wesent­lich mehr Joins).

    Sie kön­nen auch das drit­te Daten­mo­dell ver­wen­den, das die­sel­ben Tabel­len wie das zwei­te Modell defi­niert sowie sämt­li­che Tabel­len des ers­ten Modells mit­tels Updata­ble Views auf das robus­te Daten­mo­dell abbil­det: olympia3_uml.png.

    Da das drit­te Modell die­sel­ben Tabel­len defi­niert wie das zwei­te Modell, funk­tio­nie­ren auch alle DML-Befeh­le (SELECT, INSERT, DELETE, UPDATE), die für das zwei­te Modell defi­niert wur­den, auch für das drit­te Modell. Die Updata­ble Views haben zur Fol­ge, dass auch fast alle
    DDL-Anwei­sun­gen, die für das ers­te Modell defi­niert wur­den, hier eben­falls funk­tio­nie­ren. Nur die Befeh­le, bei denen ver­sucht wird einem Spie­ler eine nicht-exis­tie­ren­des Land oder einem Wett­kampf eine nicht exis­tie­ren­de Wett­kampf­art oder Sport­stät­te zuzu­wei­sen, schla­gen (im Gegen­satz zur Model-1-Daten­bank) fehl.

  2. Lösen Sie die Auf­ga­ben, die Sie in der Datei olympia_aufgabe_1.sql vor­fin­den. Kopie­ren Sie die­se Datei auf Ihren Rech­ner und tra­gen Sie Ihre Lösun­gen in die­se 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.

    Ich wür­de Ihnen raten, das drit­te Modell zu ver­wen­den, und die Anfra­gen zunächst für das ers­te Modell zu lösen. Um das Wesen des Joins bes­ser zu ver­ste­hen, soll­ten Sie anschlie­ßend die­sel­ben Anfra­gen für das zwei­te Modell for­mu­lie­ren. Und zu guter Letzt kön­nen Sie ver­su­chen, eini­ge der Lösun­gen, die Sie für das ers­te Modell for­mu­liert haben, noch zu ver­ein­fa­chen, indem Sie die zusätz­li­chen Views sportler_teilnahme, wettkampf_teilnahme und sportler_teilnahme_wettkampf ver­wen­den.