Die Olympia-Datenbank
(das Standard-Beispiel aus dem Praktikum)

Olympia-DB: ModellUML-Klas­sen­dia­gramm

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

Alter­na­ti­ve For­mu­lie­run­gen der Check-Cons­traints zur Dis­qua­li­fi­ka­ti­on:

{CHECK (NOT(doping IS TRUE) OR (disqualifiziert IS TRUE))} 
{CHECK (NOT(disqualifiziert IS TRUE) OR (platz IS NULL))}

Die fol­gen­de For­mu­lie­rung ist ver­mut­lich am ver­ständ­lichs­ten, aller­dings gibt es in SQL kei­nen IF-Ope­ra­tor.

{CHECK (IF doping IS TRUE THEN disqualifiziert IS TRUE)} 
{CHECK (IF disqualifiziert IS TRUE THEN platz IS NULL)}

olympia1_create.sql(Daten­bank gemäß Modell 1 erstel­len)
olympia_aufgabe_1.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 1)
olympia1_aufgabe_1_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 1)
olympia_aufgabe_2.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 2)
olympia1_aufgabe_2_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 2)
olympia_aufgabe_3.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 3)
olympia_aufgabe_4.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 4)
olympia1_aufgabe_4_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 4)

Robustes Olympia-DB-Datenmodell

Olympia-DB: Robustes Datenmodell (UML)UML-Klas­sen­dia­gramm

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

Fol­gen­de Bedin­gun­gen kön­nen nur mit Hil­fe von Cons­traint-Tig­gern defi­niert wer­den:

{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 ers­te Bedin­gung stellt sicher, dass nie­mand einen Platz belegt, der grö­ßer als die Anzahl der Teil­neh­mer ist, und die zwei­te Bedin­gung stellt sicher, dass Frau­en nicht an Män­ner­wett­be­wer­ben und Män­ner nicht an Frau­en­wett­be­wer­ben teil­neh­men kön­nen.

olympia2_create_no_trigger.sql(Daten­bank gemäß Modell 2 ohne Cons­traint-Tig­ger erstel­len)
olympia2_create.sql(Daten­bank gemäß Modell 2 erstel­len)
olympia_aufgabe_1.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 1)
olympia2_aufgabe_1_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 1)
olympia_aufgabe_2.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 2)
olympia2_aufgabe_2_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 2)
olympia_aufgabe_3.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 3)
olympia_aufgabe_4.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 4)
olympia2_aufgabe_4_loesung.sql(Lösungs­vor­schlä­ge zu Auf­ga­be 4)

Robustes Olympia-DB-Datenmodell mit Views zur Vereinfachung

Olympia-DB: Robustes Datenmodell mit Views(UML)UML-Klas­sen­dia­gramm

Relationales Datenbankschema (ohne Domänen)

Das rela­tio­na­le Daten­bank­sche­ma das zwei­ten Modell wird eins-zu-eins über­nom­men.
Das rela­tio­na­le Daten­bank­sche­ma des ers­ten Modells wird mit Hil­fe von Updata­ble Views auf die Tabel­len des zwei­ten Modells abge­bil­det.
Dar­über hin­aus gibt es noch drei wei­te­re Views, die Joins zwi­schen zwei bzw. drei der Tabel­len sportler, wettkampf und teilnahme auf eine ein­zi­ge vir­tu­el­le Tabel­le abbil­den.

olympia3_create.sql(Daten­bank gemäß Modell 3 erstel­len)
olympia_aufgabe_1.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 1)
olympia1_aufgabe_1_loesung.sql
olympia2_aufgabe_1_loesung.sql
olympia3_aufgabe_1_loesung.sql
(Lösungs­vor­schlä­ge zu Auf­ga­be 1)
olympia_aufgabe_2.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 2)
olympia1_aufgabe_2_loesung.sql
olympia2_aufgabe_2_loesung.sql
olympia3_aufgabe_2_loesung.sql
(Lösungs­vor­schlä­ge zu Auf­ga­be 2)
olympia_aufgabe_3.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 3)
olympia_aufgabe_4.sql(Prak­ti­kums­auf­ga­be Olym­pia Teil 4)
olympia1_aufgabe_4_loesung.sql
olympia2_aufgabe_4_loesung.sql
olympia3_aufgabe_4_loesung.sql
(Lösungs­vor­schlä­ge zu Auf­ga­be 4)