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
{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 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
Erstellen Sie eine Olympia-Datenbank. Verwenden Sie olympia1_create.sql.
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.
Hausaufgabe 1
Dem Datenbankschema, das in der robusten Version olympia2_uml.png definiert wird, liegt ein wesentlich komplexeres Datenmodell zugrunde. 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, 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).
Lösen Sie die Aufgaben, die Sie in der Datei olympia_aufgabe_1.sql vorfinden, für die Variante 2 des Olympia-Datenmodells: olympia2_create.sql
Hausaufgabe 2
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. Das heißt, dieses Modell vereint die Vorteile der beiden zuvor definierten Modelle. Alle Anfragen, die Sie für das erste oder das zweite Modell definiert haben, funktionieren auch in einer Datenbank, die das dritte Modell realisiert. (Beachten Sie: Die Fragen für die erste Modell versagen beim zweiten Modell und umgekehrt.) 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.
Ein weiterer Vorteil des dritten Modells ist, dass es zahlreiche Views gibt, mit denen sich eine Anfragen deutlich einfacher formulieren lassen. Genau dies ist Inhalt des zweiten Teils der Hausaufgabe.
Lösen Sie die Aufgaben, die Sie in der Datei olympia_aufgabe_1.sql vorfinden, mit Hilfe der Views, die in Variante 3 des Olympia-Datenmodells definiert wurden: olympia3_create.sql
