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
{PRIMARY KEY: id}
{UNIQUE: name, vorname*, geburtstag, land, geschlecht}
{CHECK: geschlecht IN ('w', 'm')}

wettkampf: id, sportart, wettkampfart, geschlecht, sportstaette, beginn*
{PRIMARY KEY: id}
{UNIQUE: sportart, wettkampfart, geschlecht}
{CHECK: geschlecht IN ('w', 'm', 'x')}

teilnahme: s_id, w_id, platz*, doping*, disqualifiziert
{PRIMARY KEY: s_id, w_id}
{FOREIGN KEY: s_id -> sportler: id}
{FOREIGN KEY: w_id -> wettkampf: id}
{CHECK: platz > 0}
{CHECK: NOT(doping IS TRUE AND disqualifiziert IS FALSE)}
{CHECK: 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

Erstel­len Sie eine Olym­pia-Daten­bank. Ver­wen­den Sie olympia1_create.sql.

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.

Hausaufgabe 1

Dem Daten­bank­sche­ma, das in der robus­ten Ver­si­on olympia2_uml.png defi­niert wird, liegt ein wesent­lich kom­ple­xe­res Daten­mo­dell zugrun­de. 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, 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).

Lösen Sie die Auf­ga­ben, die Sie in der Datei olympia_aufgabe_1.sql vor­fin­den, für die Vari­an­te 2 des Olym­pia-Daten­mo­dells: olympia2_create.sql

Mus­terlö­sung: olympia1_aufgabe_2_loesung.sql

Hausaufgabe 2

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. Das heißt, die­ses Modell ver­eint die Vor­tei­le der bei­den zuvor defi­nier­ten Model­le. Alle Anfra­gen, die Sie für das ers­te oder das zwei­te Modell defi­niert haben, funk­tio­nie­ren auch in einer Daten­bank, die das drit­te Modell rea­li­siert. (Beach­ten Sie: Die Fra­gen für die ers­te Modell ver­sa­gen beim zwei­ten Modell und umge­kehrt.) 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.

Ein wei­te­rer Vor­teil des drit­ten Modells ist, dass es zahl­rei­che Views gibt, mit denen sich eine Anfra­gen deut­lich ein­fa­cher for­mu­lie­ren las­sen. Genau dies ist Inhalt des zwei­ten Teils der Haus­auf­ga­be.

Lösen Sie die Auf­ga­ben, die Sie in der Datei olympia_aufgabe_1.sql vor­fin­den, mit Hil­fe der Views, die in Vari­an­te 3 des Olym­pia-Daten­mo­dells defi­niert wur­den: olympia3_create.sql

Mus­terlö­sung: olympia3_aufgabe_1_loesung.sql