Die Olympia-Datenbank: Anfragen (Teil 1)

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 Postgres-Manuals beschrieben. Die Datumstypen DATE und TIMESTAMP finden Sie im PostgreSQL-Manual in den Abschnitten Date/Time Types und Date/Time Functions and Operators. Datum und Uhrzeit können Sie mit der Funktion to_char formatieren.

Für die Lösung der Aufgaben können Ihnen auch noch die SQL-Funktionen LIKE und COALESCE gute Dienste leisten.

Das Datenmodell

Gegeben sei 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)}

Die Bedeutung der Attribute der Tabelle wettkampf sieht man sehr am einfachsten an zwei Beispielstupeln:

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 Attribut teilnahme.platz wird nach dem jeweiligen Wettkampf der Platz eingetragen, den der jeweilige Teilnehmer belegt.

Im Attribut teilnahme.doping wird nach einem Dopingtest das Ergebnis eingetragen (doping IS TRUE).

Im Attribut teilnahme.disqualifiziert wird eingetragen, ob der Sportler im zugehörigen Wettkampf disqualifiziert wurde (z.B. wegen Dopings oder wegen unsportlichen Verhaltens).

Bei der Berechnung von Datum- und Zeitangaben hält sich PostgreSQL sehr schön an den SQL99-Standard: Date/Time Functions and Operators, Datumsformatierung.

Aufgabe

  1. Erstellen Sie eine Olympia-Datenbank. Ihnen stehen vier mögliche Datenbanken zur Verfügung:
    • Verwenden Sie Ihre eigene Olympia-Datenbank. In diesem Fall müssen Sie in den SQL-Anfragen die Namen der Sportler allerdings geeignet anpassen.
    • Verwenden Sie olympia1_create.sql (simple Version).
    • Verwenden Sie olympia2_create.sql (robuste Version).
    • Verwenden Sie olympia3_create.sql (robuste Version plus simple Version mittels Views).

    Dem Datenbankschema, das in der robusten Version definiert wird, liegt ein wesentlich komplexeres Datenmodell zugrunde: olympia2_uml.png. Der Vorteil dieser Version ist, dass viele Datenfehler dadurch vermieden werden, dass vier Enumeration-Tabellen eingeführt und zwei weitere, ziemlich komplexe Check-Constraints mittels Constraint-Tiggern definiert wurden. Sie können also keine Länder, Sportarten, Wettkampfarten oder Sportstätten eingeben können, die zuvor nicht erfasst wurden. Außerdem können Frauen nur an Frauenwettbewerben teilnehmen und Männer an Männerwettbewerben. Darüber hinaus wird sichergestellt, dass niemand einen Platz belegen kann, den es aufgrund der Teilnehmeranzahl gar nicht gibt. Der Nachteil dieses Modells ist, dass die Lösungen der SQL-Aufgaben, die Sie bearbeiten sollen, deutlich aufwändiger sind (wesentlich mehr Joins).

    Sie können auch das dritte Datenmodell verwenden, das dieselben Tabellen wie das zweite Modell definiert sowie sämtliche Tabellen des ersten Modells mittels Updatable Views auf das robuste Datenmodell abbildet: olympia3_uml.png.

    Da das dritte Modell dieselben Tabellen definiert wie das zweite Modell, funktionieren auch alle DML-Befehle (SELECT, INSERT, DELETE, UPDATE), die für das zweite Modell definiert wurden, auch für das dritte Modell. Die Updatable Views haben zur Folge, dass auch fast alle
    DDL-Anweisungen, die für das erste Modell definiert wurden, hier ebenfalls funktionieren. Nur die Befehle, bei denen versucht wird einem Spieler eine nicht-existierendes Land oder einem Wettkampf eine nicht existierende Wettkampfart oder Sportstätte zuzuweisen, schlagen (im Gegensatz zur Model-1-Datenbank) fehl.

  2. Lösen Sie die Aufgaben, die Sie in der Datei olympia_aufgabe_1.sql vorfinden. Kopieren Sie diese Datei auf Ihren Rechner und tragen Sie Ihre Lösungen in diese 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.

    Ich würde Ihnen raten, das dritte Modell zu verwenden, und die Anfragen zunächst für das erste Modell zu lösen. Um das Wesen des Joins besser zu verstehen, sollten Sie anschließend dieselben Anfragen für das zweite Modell formulieren. Und zu guter Letzt können Sie versuchen, einige der Lösungen, die Sie für das erste Modell formuliert haben, noch zu vereinfachen, indem Sie die zusätzlichen Views sportler_teilnahme, wettkampf_teilnahme und sportler_teilnahme_wettkampf verwenden.