CREATE TABLE IF NOT EXISTS fluggesellschaft( fluggesellschaft_id SERIAL PRIMARY KEY, fluggesellschaft VARCHAR NOT NULL UNIQUE, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS land( land_id SERIAL PRIMARY KEY, land_de VARCHAR NOT NULL UNIQUE, country_en VARCHAR NOT NULL UNIQUE, pays_fr VARCHAR NOT NULL UNIQUE, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ort( ort_id SERIAL PRIMARY KEY, ort VARCHAR NOT NULL, plz VARCHAR NOT NULL, land_id INTEGER, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_land_id FOREIGN KEY(land_id) REFERENCES land(land_id) ); CREATE TABLE IF NOT EXISTS adresse( adress_id SERIAL PRIMARY KEY, strasse VARCHAR NOT NULL, hausnummer VARCHAR NOT NULL, adresszusatz VARCHAR, ort_id INTEGER NOT NULL, CONSTRAINT fk_ort_id FOREIGN KEY(ort_id) REFERENCES ort(ort_id) , lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS flughafen( flughafen_id SERIAL PRIMARY KEY, flughafen VARCHAR NOT NULL, adress_id INTEGER NOT NULL, CONSTRAINT fk_adress_id FOREIGN KEY(adress_id) REFERENCES adresse(adress_id), lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS flug( flug_id SERIAL PRIMARY KEY, zielflughafen_id INTEGER NOT NULL, CONSTRAINT fk_zflughafen_id FOREIGN KEY(zielflughafen_id) REFERENCES flughafen(flughafen_id), anschlussflughafen_id INTEGER, CONSTRAINT fk_aflughafen_id FOREIGN KEY(anschlussflughafen_id) REFERENCES flughafen(flughafen_id), fluggesellschaft_id INTEGER NOT NULL, CONSTRAINT fk_fluggesellschaft_id FOREIGN KEY(fluggesellschaft_id) REFERENCES fluggesellschaft(fluggesellschaft_id), start TIMESTAMP NOT NULL, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS anbieter( anbieter_id SERIAL PRIMARY KEY, anbieter VARCHAR NOT NULL, adress_id INTEGER NOT NULL, CONSTRAINT fk_adress_id FOREIGN KEY(adress_id) REFERENCES adresse(adress_id), lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS gepaeck( gepaeck_id SERIAL PRIMARY KEY, gepaeckart VARCHAR NOT NULL, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS kunde( kunden_id SERIAL PRIMARY KEY, vorname VARCHAR NOT NULL, nachname VARCHAR NOT NULL, geburtsdatum DATE NOT NULL, adress_id INTEGER NOT NULL, CONSTRAINT fk_adress_id FOREIGN KEY(adress_id) REFERENCES adresse(adress_id), staatsangehoerigkeit INTEGER NOT NULL, CONSTRAINT fk_staats_id FOREIGN KEY(staatsangehoerigkeit) REFERENCES land(land_id), zweite_staatsangehoerigkeit INTEGER, CONSTRAINT fk_zstaats_id FOREIGN KEY(zweite_staatsangehoerigkeit) REFERENCES land(land_id), geburtsort INTEGER NOT NULL, CONSTRAINT fk_orts_id FOREIGN KEY(geburtsort) REFERENCES ort(ort_id), lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS kunde_flug_anbieter_gepaeck( kunden_id INTEGER NOT NULL, CONSTRAINT fk_kunden_id FOREIGN KEY(kunden_id) REFERENCES kunde(kunden_id), flug_id INTEGER NOT NULL, CONSTRAINT fk_flug_id FOREIGN KEY(flug_id) REFERENCES flug(flug_id), anbieter_id INTEGER NOT NULL, CONSTRAINT fk_anbieter_id FOREIGN KEY(anbieter_id) REFERENCES anbieter(anbieter_id), gepaeck_id INTEGER, CONSTRAINT fk_gepaeck_id FOREIGN KEY(gepaeck_id) REFERENCES gepaeck(gepaeck_id), gewicht NUMERIC(3,1) NOT NULL, lastupdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); /*FLUGGESELLSCHAFTEN*/ INSERT INTO fluggesellschaft(fluggesellschaft, lastupdated) VALUES ('German Airways', NOW()); INSERT INTO fluggesellschaft(fluggesellschaft, lastupdated) VALUES ('Ryan Air', NOW()); INSERT INTO fluggesellschaft(fluggesellschaft, lastupdated) VALUES ('Polish Airlines', NOW()); INSERT INTO fluggesellschaft(fluggesellschaft, lastupdated) VALUES ('Lufthansa', NOW()); INSERT INTO fluggesellschaft(fluggesellschaft, lastupdated) VALUES ('Dakar Airways', NOW()); /*LÄNDER*/ INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Kanada', 'Canada', 'Canada', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Deutschland', 'Germany', 'Allemange', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Schweden', 'Sweden', 'Suède', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Dänemark', 'Denmark', 'Danemark', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Frankreich', 'France', 'France', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Kuba', 'Cuba', 'Cuba', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Kongo', 'Congo', 'Congo', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Finnland', 'Finland', 'Finlande', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Polen', 'Poland', 'Pologne', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Portugal', 'Portugal', 'Portugal', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('England', 'England', 'Angleterre', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Vereinigte Staaten', 'United States', 'États-Unis', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Australien', 'Australia', 'Australie', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('China', 'China', 'Chine', NOW()); INSERT INTO land(land_de, country_en, pays_fr, lastupdated) VALUES ('Sued-Korea', 'South-Korea', 'Corée du Sud', NOW()); /*ORTE*/ INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Arnsberg','59823', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Augsburg','89176', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Berlin','12234', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Espoo','21507', 8, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Forssa','30100', 8, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Calden','64465', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('München','33429', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Warschau','62477', 9, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Köln','98087', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Vellmar','35125', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Kopenhagen','34667', 4, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Porto','89764', 10, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Bernkastel-Kues','12335', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('London','46765', 11, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Ehlen','34317', 2, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Sydney','21546', 13, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('New York','12578', 12, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Orlando','58653', 12, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Seuol','23685', 15, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Shanghai','21356', 14, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Los Angeles','25379', 12, NOW()); INSERT INTO ort(ort, plz, land_id, lastupdated) VALUES ('Rachel','96576', 12, NOW()); /*ADRESSEN*/ INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Großkopf', 4, 'Wohnung 69', 1, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Foersterstrasse', 56, '/', 2, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Westendstrasse', 92, '/', 3, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Oyi Keilasatama', 21, 'App. 1', 4, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Tapulikuja', 56, '/', 5, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Marcel Grove', 78, '/', 16, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Tamara Tunnel', 8985, '/', 17, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Mikel Manors', 7475, '/', 18, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Flat Smith Circle', 52, '/', 14, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('압구정로', 6297, '/', 19, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Abernathy Plains', 1150, '/', 21, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Nam Tek', 64, '/', 20, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Buren Circle Suite', 7274, '/', 22, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Karl-Ludwig-Rohde-Weg', 72, '/', 6, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Harro-Witt-Allee', 113, '/', 9, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Kjærgade', 7, '/', 11, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Bennett Mill', 5, '/', 21, NOW()); INSERT INTO adresse(strasse, hausnummer, adresszusatz, ort_id, lastupdated) VALUES ('Merzweg', '12c', '/', 2, NOW()); /*GEPÄCK*/ INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Koffer groß', NOW()); INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Koffer mittel', NOW()); INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Koffer klein', NOW()); INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Handgepäck groß', NOW()); INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Handgepäck mittel', NOW()); INSERT INTO gepaeck(gepaeckart, lastupdated) VALUES ('Handgepäck klein', NOW()); /*FLUGHÄFEN*/ INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('Kingsford Smith', 6, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('JFK', 7, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('OIA', 8, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('Heathrow', 9, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('Incheon', 10, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('LAX', 11, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('Pudong IA', 12, NOW()); INSERT INTO flughafen(flughafen, adress_id, lastupdated) VALUES ('Area 51', 13, NOW()); /*ANBIETER*/ INSERT INTO anbieter(anbieter, adress_id, lastupdated) VALUES ('Herkules Flight', 14, NOW()); INSERT INTO anbieter(anbieter, adress_id, lastupdated) VALUES ('ToGo ÖkoFlights', 15, NOW()); INSERT INTO anbieter(anbieter, adress_id, lastupdated) VALUES ('Star Alliance', 16, NOW()); INSERT INTO anbieter(anbieter, adress_id, lastupdated) VALUES ('Star Alliance', 17, NOW()); INSERT INTO anbieter(anbieter, adress_id, lastupdated) VALUES ('Reisebüro Rosi Müller', 18, NOW()); /*FLÜGE*/ INSERT INTO flug(zielflughafen_id, anschlussflughafen_id, fluggesellschaft_id, start, lastupdated) VALUES (1, null, 1,'2022-08-23 18:42:02', NOW()); INSERT INTO flug(zielflughafen_id, anschlussflughafen_id, fluggesellschaft_id, start, lastupdated) VALUES (2, 6, 2,'2022-08-23 19:42:02', NOW()); INSERT INTO flug(zielflughafen_id, anschlussflughafen_id, fluggesellschaft_id, start, lastupdated) VALUES (3, null, 3,'2022-08-23 20:42:02', NOW()); INSERT INTO flug(zielflughafen_id, anschlussflughafen_id, fluggesellschaft_id, start, lastupdated) VALUES (4, null, 4,'2022-08-23 21:42:02', NOW()); INSERT INTO flug(zielflughafen_id, anschlussflughafen_id, fluggesellschaft_id, start, lastupdated) VALUES (5, 7, 5,'2022-08-23 22:42:02', NOW()); /*KUNDEN*/ INSERT INTO kunde(vorname, nachname, geburtsdatum, adress_id, staatsangehoerigkeit, zweite_staatsangehoerigkeit, geburtsort, lastupdated) VALUES ('Ulrich', 'Blotzek', '1960-10-05', 1, 1, 2, 11, NOW()); INSERT INTO kunde(vorname, nachname, geburtsdatum, adress_id, staatsangehoerigkeit, zweite_staatsangehoerigkeit, geburtsort, lastupdated) VALUES ('Peter', 'Schutter', '1980-01-01', 2, 3, 6, 12, NOW()); INSERT INTO kunde(vorname, nachname, geburtsdatum, adress_id, staatsangehoerigkeit, zweite_staatsangehoerigkeit, geburtsort, lastupdated) VALUES ('Albert', 'Wojack', '1955-06-15', 3, 4, 1, 13, NOW()); INSERT INTO kunde(vorname, nachname, geburtsdatum, adress_id, staatsangehoerigkeit, zweite_staatsangehoerigkeit, geburtsort, lastupdated) VALUES ('Veera', 'Virtanen', '1992-12-30', 4, 2, 3, 14, NOW()); INSERT INTO kunde(vorname, nachname, geburtsdatum, adress_id, staatsangehoerigkeit, zweite_staatsangehoerigkeit, geburtsort, lastupdated) VALUES ('Paula', 'David', '1999-04-28', 5, 5, 7, 15, NOW()); /*kunde_flug_anbieter_gepaeck*/ INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (1, 1, 1, 1, 21, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (2, 2, 2, 4, 12, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (2, 2, 2, 4, 10, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (3, 3, 3, 6, 5, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (3, 3, 3, 6, 4, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (4, 4, 4, 1, 18, NOW()); INSERT INTO kunde_flug_anbieter_gepaeck( kunden_id, flug_id, anbieter_id, gepaeck_id, gewicht, lastupdated) VALUES (5, 5, 5, 6, 3, NOW());