Die Olympia-Datenbank
(das Standard-Beispiel aus dem Praktikum)
UML-Klassendiagramm
Relationales Datenbankschema (ohne Domänen)
sportler: id, name, vorname*, geburtstag, land, geschlecht
{PRIMARY KEY (id)}
{UNIQUE (name, vorname*, geburtstag, land)}
{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) REFERENCES sportler (id)}
{FOREIGN KEY (w_id) REFERENCES wettkampf (id)}
{CHECK (platz > 0)} -- genauer: {CHECK (platz IS NULL OR platz > 0)}
{CHECK (NOT(doping IS TRUE AND disqualifiziert IS FALSE))}
{CHECK (NOT(disqualifiziert IS TRUE AND platz IS NOT NULL))}
Alternative Formulierungen der Check-Constraints zur Disqualifikation:
{CHECK (NOT(doping IS TRUE) OR (disqualifiziert IS TRUE))}
{CHECK (NOT(disqualifiziert IS TRUE) OR (platz IS NULL))}
Die folgende Formulierung ist vermutlich am verständlichsten, allerdings gibt es in SQL keinen IF-Operator.
{CHECK (IF doping IS TRUE THEN disqualifiziert IS TRUE)}
{CHECK (IF disqualifiziert IS TRUE THEN platz IS NULL)}
| olympia1_create.sql | (Datenbank gemäß Modell 1 erstellen) |
| olympia_aufgabe_1.sql | (Praktikumsaufgabe Olympia Teil 1) |
| olympia1_aufgabe_1_loesung.sql | (Lösungsvorschläge zu Aufgabe 1) |
| olympia_aufgabe_2.sql | (Praktikumsaufgabe Olympia Teil 2) |
| olympia1_aufgabe_2_loesung.sql | (Lösungsvorschläge zu Aufgabe 2) |
| olympia_aufgabe_3.sql | (Praktikumsaufgabe Olympia Teil 3) |
| olympia1_aufgabe_3_loesung.sql | (Lösungsvorschläge zu Aufgabe 3) |
| olympia_aufgabe_4.sql | (Praktikumsaufgabe Olympia Teil 4) |
| olympia1_aufgabe_4_loesung.sql | (Lösungsvorschläge zu Aufgabe 4) |
Robustes Olympia-DB-Datenmodell
UML-Klassendiagramm
Relationales Datenbankschema (ohne Domänen)
enum_land: id, kuerzel, name_de, name_en, name_fr
{PRIMARY KEY (id)}
{UNIQUE (kuerzel)}
{UNIQUE (name_de)}
{UNIQUE (name_en)}
{UNIQUE (name_fr)}
enum_sportart: id, name
{PRIMARY KEY (id)}
{UNIQUE (name)}
enum_wettkampfart: id, sa_id, name
{PRIMARY KEY (id)}
{FOREIGN KEY (sa_id) REFERENCES enum_sportart (id)}
{UNIQUE (sa_id, name)}
enum_sportstaette: id, name
{PRIMARY KEY (id)}
{UNIQUE (name)}
e_sportler: id, name, vorname*, geburtstag, l_id, geschlecht
{PRIMARY KEY (i)}
{FOREIGN KEY (l_id) REFERENCES enum_land (id)}
{UNIQUE (name, vorname*, geburtstag, land)}
e_wettkampf: id, wa_id, geschlecht, spst_id, beginn*
{PRIMARY KEY (id)}
{FOREIGN KEY (wa_id) REFERENCES enum_wettkampfart (id)}
{FOREIGN KEY (spst_id) REFERENCES enum_sportstatte (id)}
{UNIQUE (wa_id, geschlecht)}
r_teilnahme: s_id, w_id, platz*, doping*, disqualifiziert
{PRIMARY KEY (s_id, w_id)}
{FOREIGN KEY (s_id) REFERENCES e_sportler (id)}
{FOREIGN KEY (w_id) REFERENCES e_wettkampf (id)}
{CHECK (platz > 0)} -- genauer: {CHECK (platz IS NULL OR platz > 0)}
{CHECK (NOT(doping IS TRUE AND disqualifiziert IS FALSE))}
{CHECK (NOT(disqualifiziert IS TRUE AND platz IS NOT NULL))}
Folgende Bedingungen können nur mit Hilfe von Constraint-Tiggern definiert werden:
{CHECK (platz <= (SELECT COUNT(*)
FROM r_teilnahme t
WHERE t.w_id = NEW.w_id
AND t.platz IS NOT NULL
)
)
}
{CHECK ((SELECT geschlecht FROM e_wettkampf w WHERE w.id = NEW.w_id)
IN
('x', (SELECT geschlecht FROM e_sportler s WHERE s.id = NEW.s_id))
)
}
Die erste Bedingung stellt sicher, dass niemand einen Platz belegt, der größer als die Anzahl der Teilnehmer ist, und die zweite Bedingung stellt sicher, dass Frauen nicht an Männerwettbewerben und Männer nicht an Frauenwettbewerben teilnehmen können.
| olympia2_create_no_trigger.sql | (Datenbank gemäß Modell 2 ohne Constraint-Tigger erstellen) |
| olympia2_create.sql | (Datenbank gemäß Modell 2 erstellen) |
| olympia_aufgabe_1.sql | (Praktikumsaufgabe Olympia Teil 1) |
| olympia2_aufgabe_1_loesung.sql | (Lösungsvorschläge zu Aufgabe 1) |
| olympia_aufgabe_2.sql | (Praktikumsaufgabe Olympia Teil 2) |
| olympia2_aufgabe_2_loesung.sql | (Lösungsvorschläge zu Aufgabe 2) |
| olympia_aufgabe_3.sql | (Praktikumsaufgabe Olympia Teil 3) |
| olympia2_aufgabe_3_loesung.sql | (Lösungsvorschläge zu Aufgabe 3) |
| olympia_aufgabe_4.sql | (Praktikumsaufgabe Olympia Teil 4) |
| olympia2_aufgabe_4_loesung.sql | (Lösungsvorschläge zu Aufgabe 4) |
Robustes Olympia-DB-Datenmodell mit Views zur Vereinfachung
UML-Klassendiagramm
Relationales Datenbankschema (ohne Domänen)
Das relationale Datenbankschema das zweiten Modell wird eins-zu-eins übernommen.
Das relationale Datenbankschema des ersten Modells wird mit Hilfe von Updatable Views auf die Tabellen des zweiten Modells abgebildet.
Darüber hinaus gibt es noch drei weitere Views, die Joins zwischen zwei bzw. drei der Tabellen sportler, wettkampf und teilnahme auf eine einzige virtuelle Tabelle abbilden.
| olympia3_create.sql | (Datenbank gemäß Modell 3 erstellen) |
| olympia_aufgabe_1.sql | (Praktikumsaufgabe Olympia Teil 1) |
| olympia1_aufgabe_1_loesung.sql olympia2_aufgabe_1_loesung.sql olympia3_aufgabe_1_loesung.sql | (Lösungsvorschläge zu Aufgabe 1) |
| olympia_aufgabe_2.sql | (Praktikumsaufgabe Olympia Teil 2) |
| olympia1_aufgabe_2_loesung.sql olympia2_aufgabe_2_loesung.sql olympia3_aufgabe_2_loesung.sql | (Lösungsvorschläge zu Aufgabe 2) |
| olympia_aufgabe_3.sql | (Praktikumsaufgabe Olympia Teil 3) |
| olympia1_aufgabe_3_loesung.sql olympia2_aufgabe_3_loesung.sql olympia3_aufgabe_3_loesung.sql | (Lösungsvorschläge zu Aufgabe 3) |
| olympia_aufgabe_4.sql | (Praktikumsaufgabe Olympia Teil 4) |
| olympia1_aufgabe_4_loesung.sql olympia2_aufgabe_4_loesung.sql olympia3_aufgabe_4_loesung.sql | (Lösungsvorschläge zu Aufgabe 4) |