DROP TABLE IF EXISTS teilnahme CASCADE;
DROP TABLE IF EXISTS wettkampf CASCADE;
DROP TABLE IF EXISTS sportler  CASCADE;

CREATE TABLE sportler
(
 s_id            INTEGER     NOT NULL,
 name            VARCHAR(50) NOT NULL,
 vorname         VARCHAR(50),
 geburtstag      DATE        NOT NULL,
 geschlecht      CHAR(1)     NOT NULL,
 land            CHAR(3)     NOT NULL,
 PRIMARY KEY (s_id),
 UNIQUE      (name, vorname, geburtstag, land),
 CHECK       (geschlecht IN ('m', 'w'))
);

CREATE TABLE wettkampf
(
 w_id            INTEGER     NOT NULL,
 sportart        VARCHAR(50) NOT NULL,  
 wettkampfart    VARCHAR(50) NOT NULL,  
 geschlecht      CHAR(1)     NOT NULL,  
 sportstaette    VARCHAR(50) NOT NULL,  
 beginn          TIMESTAMP   NOT NULL,
 PRIMARY KEY (w_id),
 UNIQUE      (sportart, wettkampfart, geschlecht),
 CHECK       (geschlecht IN ('m', 'w'))
);

CREATE TABLE teilnahme
(
 s_id            INTEGER NOT NULL,
 w_id            INTEGER NOT NULL,
 platz           INTEGER,
 doping          BOOLEAN,
 disqualifiziert BOOLEAN NOT NULL DEFAULT FALSE,
 PRIMARY KEY (s_id, w_id),
 FOREIGN KEY (s_id) REFERENCES sportler,
 FOREIGN KEY (w_id) REFERENCES wettkampf,
 CHECK (platz > 0),
 CHECK (NOT (doping AND NOT disqualifiziert)),
 CHECK (NOT (disqualifiziert AND platz IS NOT NULL))
);




INSERT INTO sportler(s_id, name, vorname, geburtstag, geschlecht, land)
VALUES
  (1,  'Völker',                'Sandra',     DATE '1974-04-01', 'w', 'GER'),
  (2,  'van Almsick',           'Franziska',  DATE '1978-04-05', 'w', 'GER'),
  (3,  'Harstick',              'Sara',       DATE '1981-01-01', 'w', 'GER'),
  (4,  'Buschschulte',          'Antje',      DATE '1978-12-27', 'w', 'GER'),
  (5,  'Filippi',               'Alessia',    DATE '1987-06-23', 'w', 'ITA'),
  (6,  'Angelopoulou',          'Vasiliki',   DATE '1987-05-30', 'w', 'GRE'),
  (7,  'Rutschow-Stomporowski', 'Katrin',     DATE '1975-04-02', 'w', 'GER'),
  (8,  'Karsten',               'Ekaterina',  DATE '1972-06-02', 'w', 'BLR'),
  (9,  'Fedotova',              'Irina',      DATE '1970-01-01', 'w', 'RUS'),
  (10, 'Boenisch',              'Yvonne',     DATE '1980-12-29', 'w', 'GER'),
  (11, 'Yukhareva',             'Natalya',    DATE '1970-01-01', 'w', 'RUS'),
  (12, 'Cavazzuti',             'Cinzia',     DATE '1970-01-01', 'w', 'ITA'),
  (13, 'Harel',                 'Barbara',    DATE '1970-01-01', 'w', 'FRA'),
  (14, 'Xeteylu',               'Leidisyuri', DATE '1970-01-01', 'w', 'CUB'),
  (15, 'Tuuxaru',               'Iliisquix',  DATE '1970-01-01', 'm', 'CUB'),
  (16, 'Maier',                 'Josef',      DATE '1980-01-01', 'm', 'GER'),
  (17, 'Maier',                 'Josef',      DATE '1982-01-01', 'm', 'GER'),
  (18, 'Dede',                  null,         DATE '1970-01-01', 'm', 'GER')
;

INSERT INTO wettkampf (w_id, sportart, wettkampfart, geschlecht, sportstaette, beginn)
VALUES
  ( 1, 'Judo',      'bis 57kg',           'w', 'Halle 1',        TIMESTAMP '2004-08-14 16:30'),
  ( 2, 'Judo',      'bis 80kg',           'm', 'Halle 2',        TIMESTAMP '2004-08-14 16:30'),
  ( 3, 'Schwimmen', '400m Lagen',         'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-14 17:30'),
  ( 4, 'Schwimmen', '100m Schmetterling', 'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-15 15:30'),
  ( 5, 'Schwimmen', '100m Brust',         'w', 'Schwimmhalle 2', TIMESTAMP '2004-08-16 12:15'),
  ( 6, 'Schwimmen', '100m Brust',         'm', 'Schwimmhalle 2', TIMESTAMP '2004-08-16 14:15'),
  ( 7, 'Schwimmen', '200m Brust',         'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-17 12:15'),
  ( 8, 'Schwimmen', '200m Brust',         'm', 'Schwimmhalle 1', TIMESTAMP '2004-08-17 14:15'),
  ( 9, 'Rudern',    'Einer',              'w', 'Ruderstrecke',   TIMESTAMP '2004-08-19 19:30'),
  (10, 'Rudern',    'Einer',              'm', 'Ruderstrecke',   TIMESTAMP '2004-08-19 19:30')
;


INSERT INTO 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, FALSE),
  ( 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)
;

INSERT INTO teilnahme (s_id, w_id, platz, doping, disqualifiziert)
VALUES
  (14,  5, NULL, TRUE,  TRUE),
  (15,  6, NULL, TRUE,  TRUE),
  (17,  6, NULL, FALSE, TRUE)
;
