DROP SCHEMA IF EXISTS airtraffic CASCADE;
CREATE SCHEMA airtraffic;
SET SCHEMA 'airtraffic';

/*
DROP TABLE IF EXISTS airtraffic.Airports CASCADE;
DROP TABLE IF EXISTS airtraffic.FlightCodes CASCADE;
DROP TABLE IF EXISTS airtraffic.Flights CASCADE;
*/

CREATE TABLE airtraffic.Airports(
  code TEXT PRIMARY KEY,
  city TEXT NOT NULL
);

CREATE TABLE airtraffic.FlightCodes (
  code TEXT PRIMARY KEY,
  airlineName TEXT NOT NULL
);

-- I use this one instead.
CREATE TABLE airtraffic.Flights (
  departureAirport TEXT REFERENCES airtraffic.Airports(code),
  destinationAirport TEXT REFERENCES airtraffic.Airports(code),
  departureTime INT CHECK (departureTime >=0 AND departureTime < 24),
  arrivalTime INT CHECK (arrivalTime >=0 AND arrivalTime < 24 and arrivalTime > departureTime),
  code TEXT REFERENCES airtraffic.FlightCodes(code),
  PRIMARY KEY (code)
);


INSERT INTO airtraffic.Airports(code, city) VALUES ('ABZ', 'Aberdeen');
INSERT INTO airtraffic.Airports(code, city) VALUES ('AMS', 'Amsterdam');
INSERT INTO airtraffic.Airports(code, city) VALUES ('ARN', 'Stockholm');
INSERT INTO airtraffic.Airports(code, city) VALUES ('CDG', 'Paris');
INSERT INTO airtraffic.Airports(code, city) VALUES ('CPH', 'Copenhagen');
INSERT INTO airtraffic.Airports(code, city) VALUES ('DEL', 'New Delhi');
INSERT INTO airtraffic.Airports(code, city) VALUES ('FRA', 'Frankfurt');
INSERT INTO airtraffic.Airports(code, city) VALUES ('GOT', 'Gothenburg');
INSERT INTO airtraffic.Airports(code, city) VALUES ('MUC', 'Munich');
INSERT INTO airtraffic.Airports(code, city) VALUES ('NGO', 'Nagoya');
INSERT INTO airtraffic.Airports(code, city) VALUES ('PAR', 'Paris');

INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49732', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','CPH',13,14,'SK49732');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49733', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','ARN',13,14,'SK49733');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49734', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','ARN',15,16,'SK49734');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49735', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','CPH',14,15,'SK49735');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49736', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CPH','FRA',15,17,'SK49736');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49738', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CPH','ABZ',16,18,'SK49738');

INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38002', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('FRA','MUC',15,16,'LH38002');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38003', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('FRA','GOT',15,17,'LH38003');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38004', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','FRA',18,20,'LH38004');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38005', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','MUC',18,20,'LH38005');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38007', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('FRA','CPH',18,19,'LH38007');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38008', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('FRA','ARN',19,21,'LH38008');

INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9009', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','GOT',5,7,'AF9009');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9010', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','GOT',8,10,'AF9010');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9011', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','CDG',8,10,'AF9011');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9012', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','CDG',11,13,'AF9012');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9013', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','AMS',10,11,'AF9013');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9014', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','AMS',15,16,'AF9014');

INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36197', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','AMS',17,19,'KL36197');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36198', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('ABZ','AMS',10,12,'KL36198');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36199', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('ABZ','AMS',13,15,'KL36199');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36200', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('ABZ','AMS',17,19,'KL36200');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36201', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('AMS','ABZ',13,15,'KL36201');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36202', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('AMS','ABZ',16,18,'KL36202');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36203', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('AMS','ABZ',18,20,'KL36203');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36206', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('GOT','AMS',10,12,'KL36206');

INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('SK49737', 'Scandinavian Airlines System');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CPH','NGO',15,23,'SK49737');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('LH38006', 'Lufthansa Cargo');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('FRA','NGO',1,11,'LH38006');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9015', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','DEL',15,21,'AF9015');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('AF9016', 'Air France');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('CDG','NGO',15,23,'AF9016');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36204', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('AMS','NGO',14,22,'KL36204');
INSERT INTO airtraffic.FlightCodes(code, airlineName) VALUES ('KL36205', 'KLM Royal Dutch Airlines');
INSERT INTO airtraffic.Flights(departureAirport, destinationAirport, departureTime, arrivalTime, code)VALUES ('AMS','DEL',1,9,'KL36205');
