CREATE TABLE trainer ( id INTEGER PRIMARY KEY, vorname TEXT NOT NULL, nachname TEXT NOT NULL ); CREATE TABLE spieler ( id INTEGER PRIMARY KEY, vorname TEXT NOT NULL, nachname TEXT NOT NULL, gebdatum DATE NOT NULL, trikotnr INTEGER NOT NULL, spielt_fuer INTEGER NOT NULL, FOREIGN KEY (spielt_fuer) REFERENCES team(id) ); CREATE TABLE team ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, gruendungsjahr INTEGER NOT NULL, trainer_id INTEGER NOT NULL, heimat_stadion_id INTEGER NOT NULL, FOREIGN KEY (trainer_id) REFERENCES trainer(id), FOREIGN KEY (heimat_stadion_id) REFERENCES stadion(id) ); CREATE TABLE turnier ( id INTEGER PRIMARY KEY, titel TEXT, datum DATE NOT NULL, stadion_id INTEGER NOT NULL, FOREIGN KEY (stadion_id) REFERENCES stadion(id) ); CREATE TABLE stadion ( id INTEGER PRIMARY KEY, name TEXT, adresse TEXT NOT NULL ); CREATE TABLE nimmt_teil_an ( team_id INTEGER NOT NULL, turnier_id TEXT NOT NULL, platzierung INTEGER, PRIMARY KEY (team_id, turnier_id), FOREIGN KEY (team_id) REFERENCES team(id), FOREIGN KEY (turnier_id) REFERENCES turnier(id) ); -- Trainer INSERT INTO trainer (id, vorname, nachname) VALUES (1, 'Trixi', 'Train'), (2, 'Wim', 'Winner'), (3, 'Willi', 'Werfer'); -- stadion INSERT INTO stadion (id, name, adresse) VALUES (5, 'Frisbee-Arena am Oberteich', 'Schönberg, MV'), (12, 'Discadium', 'Gadebusch, MV'), (14, 'Euroimmun-Arena', 'Dassow, MV'); -- Team INSERT INTO team (id, name, gruendungsjahr, trainer_id, heimat_stadion_id) VALUES (24, 'Frisbee-Club Schönberg', 1999, 1, 5), (25, '1. FC Gadebusch', 1980, 2, 12), (27, 'Disc Destroyers Dassow', 1985, 3, 14); -- Spieler INSERT INTO spieler (id, vorname, nachname, gebdatum, spielt_fuer, trikotnr) VALUES (1, 'Florian', 'Flyman', '2000-05-15', 27, 10), (2, 'Frieda', 'Flip', '1998-11-23', 24, 7), (3, 'Lars', 'Lasso', '2002-02-10', 24, 5); -- Turnier INSERT INTO turnier (id, titel, datum, stadion_id) VALUES (2, 'Ultimate Schönberg-Cup 2024', '2024-05-25', 5), (3, 'NWM Open 2024', '2024-07-10', 14); -- Teilnahme INSERT INTO nimmt_teil_an (team_id, turnier_id, platzierung) VALUES (24, 2, 1), (25, 2, 3), (27, 2, 1), (24, 3, 2), (25, 3, 1);