DROP TABLE IF EXISTS r_teilnahme CASCADE; DROP TABLE IF EXISTS e_wettkampf CASCADE; DROP TABLE IF EXISTS e_sportler CASCADE; DROP TABLE IF EXISTS enum_land CASCADE; DROP TABLE IF EXISTS enum_sportstaette CASCADE; DROP TABLE IF EXISTS enum_wettkampfart CASCADE; DROP TABLE IF EXISTS enum_sportart CASCADE; DROP DOMAIN IF EXISTS D_GESCHLECHT CASCADE; /***************************************************************************** * Datentypen und Tabellen *****************************************************************************/ -- CREATE DOMAIN IF NOT EXISTS D_UNTAINTED -- AS -- VARCHAR CHECK (value !~ '[<>"'';]|--|/\*'); DO $$ BEGIN CREATE DOMAIN D_UNTAINTED AS VARCHAR CHECK (value !~ '[<>"'';]|--|/\*'); EXCEPTION WHEN duplicate_object THEN null; END $$; CREATE DOMAIN D_GESCHLECHT AS CHAR(1) CHECK (VALUE IN ('w', 'm', 'x')); CREATE TABLE enum_sportart (id INTEGER NOT NULL, name D_UNTAINTED NOT NULL, CONSTRAINT enum_sportart_pk PRIMARY KEY (id), CONSTRAINT enum_sportart_unique_name UNIQUE (name) ); CREATE TABLE enum_wettkampfart (id INTEGER NOT NULL, sa_id INTEGER NOT NULL, name D_UNTAINTED NOT NULL, CONSTRAINT enum_wettkampfart_pk PRIMARY KEY (id), CONSTRAINT enum_wettkampfart_unique_sa_id_name UNIQUE (sa_id, name), CONSTRAINT enum_wettkampfart_fk_enum_sportart_id FOREIGN KEY (sa_id) REFERENCES enum_sportart(id) ); CREATE TABLE enum_sportstaette (id INTEGER NOT NULL, name D_UNTAINTED NOT NULL, CONSTRAINT enum_sportstaette_pk PRIMARY KEY (id), CONSTRAINT enum_sportstaette_unique_name UNIQUE (name) ); CREATE TABLE enum_land (id INTEGER NOT NULL, kuerzel CHAR(3) NOT NULL, name_de D_UNTAINTED NOT NULL, name_en D_UNTAINTED NOT NULL, name_fr D_UNTAINTED NOT NULL, CONSTRAINT enum_land_pk PRIMARY KEY (id), CONSTRAINT enum_land_unique_kuerzel UNIQUE (kuerzel), CONSTRAINT enum_land_unique_name_de UNIQUE (name_de), CONSTRAINT enum_land_unique_name_en UNIQUE (name_en), CONSTRAINT enum_land_unique_name_fr UNIQUE (name_fr) ); CREATE TABLE e_sportler (id INTEGER NOT NULL, name D_UNTAINTED NOT NULL, vorname D_UNTAINTED, geburtstag DATE NOT NULL, l_id INTEGER NOT NULL, geschlecht D_GESCHLECHT NOT NULL, CONSTRAINT e_sportler_pk PRIMARY KEY (id), CONSTRAINT e_sportler_unique_name_vorname_geburtstag_land UNIQUE (name, vorname, geburtstag, l_id), CONSTRAINT e_sportler_fk_enum_land_id FOREIGN KEY (l_id) REFERENCES enum_land(id), CONSTRAINT sportler_check_geschlecht CHECK (geschlecht <> 'x') ); CREATE TABLE e_wettkampf (id INTEGER NOT NULL, wa_id INTEGER NOT NULL, geschlecht D_GESCHLECHT NOT NULL, spst_id INTEGER NOT NULL, beginn TIMESTAMP NOT NULL, CONSTRAINT wettkampf_pk PRIMARY KEY (id), CONSTRAINT wettkampf_unique_sportart_wettkampfart_geschlecht UNIQUE (wa_id, geschlecht), CONSTRAINT wettkampf_fk_enum_wettkampfart_id FOREIGN KEY (wa_id) REFERENCES enum_wettkampfart(id), CONSTRAINT wettkampf_fk_enum_sportstaette_id FOREIGN KEY (spst_id) REFERENCES enum_sportstaette(id) ); CREATE TABLE r_teilnahme (s_id INTEGER NOT NULL, w_id INTEGER NOT NULL, platz INTEGER, doping BOOLEAN, disqualifiziert BOOLEAN NOT NULL DEFAULT FALSE, CONSTRAINT teilnahme_pk PRIMARY KEY (s_id, w_id), CONSTRAINT teilnahme_fk_sportler_id FOREIGN KEY (s_id) REFERENCES e_sportler(id), CONSTRAINT teilnahme_fk_wettkampf_id FOREIGN KEY (w_id) REFERENCES e_wettkampf(id), CONSTRAINT t_teilnahme_platz_groesser_0 CHECK (platz > 0), CONSTRAINT t_teilnahme_doper_werden_disqualifiziert CHECK (NOT (doping IS TRUE AND disqualifiziert IS FALSE)), CONSTRAINT t_teilnahme_disqualifizierte_belegen_keinen_platz CHECK (NOT (disqualifiziert IS TRUE AND platz IS NOT NULL)) ); /***************************************************************************** * Beispielsdaten *****************************************************************************/ INSERT INTO enum_sportart(id, name) VALUES (1, 'Judo'), (2, 'Schwimmen'), (3, 'Rudern'), (4, 'Boxen'), (5, 'Tennis') ; INSERT INTO enum_wettkampfart(id, sa_id, name) VALUES (1, 1, 'bis 57kg'), (2, 1, 'bis 80kg'), (3, 2, 'Lagen 400m'), (4, 2, 'Schmetterling 100m'), (5, 2, 'Brust 100m'), (6, 2, 'Brust 200m'), (7, 3, 'Kajak-Einer'), (8, 4, 'bis 57kg'), (9, 4, 'bis 80kg'), (10, 5, 'Einzel Finale'), (11, 5, 'Mixed Finale') ; INSERT INTO enum_sportstaette(id, name) VALUES (1, 'Halle 1'), (2, 'Halle 2'), (3, 'Schwimmhalle 1'), (4, 'Schwimmhalle 2'), (5, 'Ruderstrecke'), (6, 'Center Court') ; INSERT INTO enum_land(id, kuerzel, name_de, name_en, name_fr) VALUES (1, 'BLR', 'Weißrußland', 'Belarus', 'Biélorussie'), (2, 'CUB', 'Kuba', 'Cuba', 'Cuba'), (3, 'FRA', 'Frankreich', 'France', 'France'), (4, 'GER', 'Bundesrepublik Deutschland', 'Federal Republic of Germany', 'République fédérale d’Allemagne'), (5, 'GRE', 'Griechenland', 'Greece', 'Grèce'), (6, 'ITA', 'Italien', 'Italy', 'Italie'), (7, 'RUS', 'Russische Föderation', 'Russian Federation', 'Fédération de Russie ') ; INSERT INTO e_sportler(id, name, vorname, geburtstag, geschlecht, l_id) VALUES (1, 'Völker', 'Sandra', DATE '1974-04-01', 'w', 4), (2, 'van Almsick', 'Franziska', DATE '1978-04-05', 'w', 4), (3, 'Harstick', 'Sara', DATE '1981-01-01', 'w', 4), (4, 'Buschschulte', 'Antje', DATE '1978-12-27', 'w', 4), (5, 'Filippi', 'Alessia', DATE '1987-06-23', 'w', 6), (6, 'Angelopoulou', 'Vasiliki', DATE '1987-05-30', 'w', 5), (7, 'Rutschow-Stomporowski', 'Katrin', DATE '1975-04-02', 'w', 4), (8, 'Karsten', 'Ekaterina', DATE '1972-06-02', 'w', 1), (9, 'Fedotova', 'Irina', DATE '1970-01-01', 'w', 7), (10, 'Boenisch', 'Yvonne', DATE '1980-12-29', 'w', 4), (11, 'Yukhareva', 'Natalya', DATE '1970-01-01', 'w', 7), (12, 'Cavazzuti', 'Cinzia', DATE '1970-01-01', 'w', 6), (13, 'Harel', 'Barbara', DATE '1970-01-01', 'w', 3), (14, 'Xeteylu', 'Leidisyuri', DATE '1970-01-01', 'w', 2), (15, 'Tuuxaru', 'Iliisquix', DATE '1970-01-01', 'm', 2), (16, 'Maier', 'Josef', DATE '1980-01-01', 'm', 4), (17, 'Maier', 'Josef', DATE '1982-01-01', 'm', 4), (18, 'Dede', NULL, DATE '1970-01-01', 'm', 4), (19, 'Graf', 'Steffi', DATE '1969-06-14', 'w', 4), (20, 'Becker', 'Boris', DATE '1967-11-22', 'm', 4) ; INSERT INTO e_wettkampf (id, wa_id, geschlecht, spst_id, beginn) VALUES ( 1, 1, 'w', 1, TIMESTAMP '2004-08-14 16:30'), ( 2, 2, 'm', 2, TIMESTAMP '2004-08-14 16:30'), ( 3, 3, 'w', 3, TIMESTAMP '2004-08-14 17:30'), ( 4, 4, 'w', 3, TIMESTAMP '2004-08-15 15:30'), ( 5, 5, 'w', 4, TIMESTAMP '2004-08-16 12:15'), ( 6, 5, 'm', 4, TIMESTAMP '2004-08-16 14:15'), ( 7, 6, 'w', 3, TIMESTAMP '2004-08-17 12:15'), ( 8, 6, 'm', 3, TIMESTAMP '2004-08-17 14:15'), ( 9, 7, 'w', 5, TIMESTAMP '2004-08-19 19:30'), (10, 7, 'm', 5, TIMESTAMP '2004-08-19 19:30'), (11, 8, 'w', 1, TIMESTAMP '2004-08-17 16:30'), (12, 9, 'm', 2, TIMESTAMP '2004-08-17 16:30'), (13, 10, 'w', 6, TIMESTAMP '2004-08-20 20:00'), (14, 10, 'm', 6, TIMESTAMP '2004-08-21 20:00'), (15, 11, 'x', 6, TIMESTAMP '2004-08-22 20:00') ; INSERT INTO r_teilnahme (s_id, w_id, platz, doping) VALUES ( 1, 3, 1, FALSE), ( 1, 4, 1, FALSE), ( 1, 5, 1, FALSE), ( 1, 7, 1, FALSE), ( 2, 3, 2, NULL), ( 2, 5, 8, FALSE), ( 2, 9, 1, NULL), ( 3, 3, 1, NULL), ( 4, 3, 1, FALSE), ( 4, 4, 1, FALSE), ( 4, 5, 2, FALSE), ( 5, 3, 1, FALSE), ( 5, 5, 2, FALSE), ( 6, 5, 1, FALSE), ( 8, 5, 1, FALSE), (11, 5, 1, FALSE), (13, 5, 2, FALSE), (13, 4, NULL, FALSE), (16, 10, NULL, FALSE), (19, 13, 1, FALSE), (20, 14, 1, FALSE), (19, 15, 1, FALSE), (20, 15, 1, FALSE) ; INSERT INTO r_teilnahme (s_id, w_id, platz, doping, disqualifiziert) VALUES (14, 5, NULL, TRUE, TRUE), (15, 6, NULL, TRUE, TRUE), (17, 6, NULL, FALSE, TRUE) ;