PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "airports" (
"City" TEXT,
"AirportCode" TEXT UNIQUE,
"AirportName" TEXT,
"Country" TEXT,
PRIMARY KEY ("AirportCode")
);

CREATE TABLE "flights" (
"Airline" INT,
"FlightNo" INT,
"SourceAirport" TEXT,
"DestAirport" TEXT,
PRIMARY KEY ("Airline", "FlightNo"),
FOREIGN KEY ("SourceAirport") REFERENCES "airports"("AirportCode"),
FOREIGN KEY ("DestAirport") REFERENCES "airports"("AirportCode")
);





INSERT INTO "airports" VALUES ("New York", "JFK", "John F. Kennedy International Airport", "USA");
INSERT INTO "airports" VALUES ("Los Angeles", "LAX", "Los Angeles International Airport", "USA");
INSERT INTO "airports" VALUES ("Chicago", "ORD", "OHare International Airport", "USA");
INSERT INTO "airports" VALUES ("London", "LHR", "Heathrow Airport", "UK");
INSERT INTO "airports" VALUES ("Paris", "CDG", "Charles de Gaulle Airport", "France");
INSERT INTO "airports" VALUES ("Tokyo", "NRT", "Narita International Airport", "Japan");
INSERT INTO "airports" VALUES ("Dubai", "DXB", "Dubai International Airport", "UAE");
INSERT INTO "airports" VALUES ("Frankfurt", "FRA", "Frankfurt Airport", "Germany");
INSERT INTO "airports" VALUES ("Sydney", "SYD", "Sydney Airport", "Australia");
INSERT INTO "airports" VALUES ("Beijing", "PEK", "Beijing Capital International Airport", "China");
INSERT INTO "airports" VALUES ("Dallas", "DFW", "Dallas/Fort Worth International Airport", "USA");
INSERT INTO "airports" VALUES ("Atlanta", "ATL", "Hartsfield-Jackson Atlanta International Airport", "USA");
INSERT INTO "airports" VALUES ("Denver", "DEN", "Denver International Airport", "USA");
INSERT INTO "airports" VALUES ("Miami", "MIA", "Miami International Airport", "USA");
INSERT INTO "airports" VALUES ("Toronto", "YYZ", "Toronto Pearson International Airport", "Canada");
INSERT INTO "airports" VALUES ("Singapore", "SIN", "Singapore Changi Airport", "Singapore");
INSERT INTO "airports" VALUES ("Amsterdam", "AMS", "Amsterdam Airport Schiphol", "Netherlands");
INSERT INTO "airports" VALUES ("Madrid", "MAD", "Adolfo Suárez Madrid–Barajas Airport", "Spain");
INSERT INTO "airports" VALUES ("Rome", "FCO", "Leonardo da Vinci–Fiumicino Airport", "Italy");
INSERT INTO "airports" VALUES ("Mexico City", "MEX", "Mexico City International Airport", "Mexico");
INSERT INTO "flights" VALUES ("101", "1001", "JFK", "LAX");
INSERT INTO "flights" VALUES ("101", "1002", "LAX", "JFK");
INSERT INTO "flights" VALUES ("102", "2001", "ORD", "LHR");
INSERT INTO "flights" VALUES ("102", "2002", "LHR", "ORD");
INSERT INTO "flights" VALUES ("103", "3001", "CDG", "NRT");
INSERT INTO "flights" VALUES ("103", "3002", "NRT", "CDG");
INSERT INTO "flights" VALUES ("104", "4001", "DXB", "FRA");
INSERT INTO "flights" VALUES ("104", "4002", "FRA", "DXB");
INSERT INTO "flights" VALUES ("105", "5001", "SYD", "PEK");
INSERT INTO "flights" VALUES ("105", "5002", "PEK", "SYD");
INSERT INTO "flights" VALUES ("106", "6001", "JFK", "ORD");
INSERT INTO "flights" VALUES ("106", "6002", "ORD", "JFK");
INSERT INTO "flights" VALUES ("107", "7001", "LAX", "DFW");
INSERT INTO "flights" VALUES ("107", "7002", "DFW", "LAX");
INSERT INTO "flights" VALUES ("108", "8001", "ATL", "DEN");
INSERT INTO "flights" VALUES ("108", "8002", "DEN", "ATL");
INSERT INTO "flights" VALUES ("109", "9001", "MIA", "YYZ");
INSERT INTO "flights" VALUES ("109", "9002", "YYZ", "MIA");
INSERT INTO "flights" VALUES ("110", "10001", "SIN", "AMS");
INSERT INTO "flights" VALUES ("110", "10002", "AMS", "SIN");
INSERT INTO "flights" VALUES ("111", "11001", "MAD", "FCO");
INSERT INTO "flights" VALUES ("111", "11002", "FCO", "MAD");
INSERT INTO "flights" VALUES ("112", "12001", "MEX", "JFK");
INSERT INTO "flights" VALUES ("112", "12002", "JFK", "MEX");
INSERT INTO "flights" VALUES ("113", "13001", "LAX", "LHR");
INSERT INTO "flights" VALUES ("113", "13002", "LHR", "LAX");
INSERT INTO "flights" VALUES ("114", "14001", "ORD", "CDG");
INSERT INTO "flights" VALUES ("114", "14002", "CDG", "ORD");
INSERT INTO "flights" VALUES ("115", "15001", "NRT", "DXB");
INSERT INTO "flights" VALUES ("115", "15002", "DXB", "NRT");
INSERT INTO "flights" VALUES ("116", "16001", "FRA", "SYD");
INSERT INTO "flights" VALUES ("116", "16002", "SYD", "FRA");
INSERT INTO "flights" VALUES ("117", "17001", "PEK", "DFW");
INSERT INTO "flights" VALUES ("117", "17002", "DFW", "PEK");
INSERT INTO "flights" VALUES ("118", "18001", "ATL", "MIA");
INSERT INTO "flights" VALUES ("118", "18002", "MIA", "ATL");
INSERT INTO "flights" VALUES ("119", "19001", "DEN", "SIN");
INSERT INTO "flights" VALUES ("119", "19002", "SIN", "DEN");
INSERT INTO "flights" VALUES ("120", "20001", "YYZ", "MAD");
INSERT INTO "flights" VALUES ("120", "20002", "MAD", "YYZ");
INSERT INTO "flights" VALUES ("121", "21001", "AMS", "FCO");
INSERT INTO "flights" VALUES ("121", "21002", "FCO", "AMS");
INSERT INTO "flights" VALUES ("122", "22001", "MEX", "LAX");
INSERT INTO "flights" VALUES ("122", "22002", "LAX", "MEX");
INSERT INTO "flights" VALUES ("123", "23001", "JFK", "LHR");
INSERT INTO "flights" VALUES ("123", "23002", "LHR", "JFK");
INSERT INTO "flights" VALUES ("124", "24001", "ORD", "NRT");
INSERT INTO "flights" VALUES ("124", "24002", "NRT", "ORD");
INSERT INTO "flights" VALUES ("125", "25001", "CDG", "DXB");
INSERT INTO "flights" VALUES ("125", "25002", "DXB", "CDG");
INSERT INTO "flights" VALUES ("126", "26001", "FRA", "PEK");
INSERT INTO "flights" VALUES ("126", "26002", "PEK", "FRA");
INSERT INTO "flights" VALUES ("127", "27001", "SYD", "ATL");
INSERT INTO "flights" VALUES ("127", "27002", "ATL", "SYD");
INSERT INTO "flights" VALUES ("128", "28001", "DFW", "DEN");
INSERT INTO "flights" VALUES ("128", "28002", "DEN", "DFW");
INSERT INTO "flights" VALUES ("129", "29001", "MIA", "SIN");
INSERT INTO "flights" VALUES ("129", "29002", "SIN", "MIA");
INSERT INTO "flights" VALUES ("130", "30001", "YYZ", "AMS");
INSERT INTO "flights" VALUES ("130", "30002", "AMS", "YYZ");
INSERT INTO "flights" VALUES ("131", "31001", "MAD", "MEX");
INSERT INTO "flights" VALUES ("131", "31002", "MEX", "MAD");
INSERT INTO "flights" VALUES ("132", "32001", "FCO", "JFK");
INSERT INTO "flights" VALUES ("132", "32002", "JFK", "FCO");