airports-db.sql

DROP DATABASE IF EXISTS airports; /* Deletion of the previous version of the db */


CREATE DATABASE airports; /* Database creation */


/* Connection to the db */
\c airports



/* The inserts have been AI-generated */

CREATE TABLE CITIES (
    CityName VARCHAR(20) PRIMARY KEY,
    Country CHAR(3) NOT NULL
);

-- Inserting 20 rows of cities
INSERT INTO CITIES (CityName, Country) VALUES
('New York', 'USA'),
('Los Angeles', 'USA'),
('London', 'GBR'),
('Manchester', 'GBR'),
('Paris', 'FRA'),
('Marseille', 'FRA'),
('Berlin', 'DEU'),
('Hamburg', 'DEU'),
('Rome', 'ITA'),
('Milan', 'ITA'),
('Sydney', 'AUS'),
('Melbourne', 'AUS'),
('Tokyo', 'JPN'),
('Osaka', 'JPN'),
('Toronto', 'CAN'),
('Vancouver', 'CAN'),
('Madrid', 'ESP'),
('Barcelona', 'ESP'),
('Singapore', 'SGP'),
('Bangkok', 'THA');






CREATE TABLE AIRPLANE_TYPES (
    AircraftType VARCHAR(2) PRIMARY KEY,
    Capacity INTEGER NOT NULL
);

-- Inserting 10 rows of airplane types
INSERT INTO AIRPLANE_TYPES (AircraftType, Capacity) VALUES
('A', 150),
('B', 200),
('C', 180),
('D', 250),
('E', 220),
('F', 180),
('G', 190),
('H', 210),
('I', 230),
('J', 240);





CREATE TABLE FLIGHTS (
    FlightId INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    DayOfWeek CHAR(2) NOT NULL,
    DepartureCityName VARCHAR(20) REFERENCES CITIES(CityName) NOT NULL,
    ArrivalCityName VARCHAR(20) REFERENCES CITIES(CityName) NOT NULL,
    AircraftType VARCHAR(2) REFERENCES AIRPLANE_TYPES(AircraftType)
);


DO $$
DECLARE
    departure_city_name VARCHAR(20);
    arrival_city_name VARCHAR(20);
    aircraft_type VARCHAR(2);
    day_of_week CHAR(2);
    i INT;
BEGIN
    -- Loop over each departure city
    FOR departure_city_name IN 
        SELECT CityName FROM CITIES 
        LOOP
            -- Loop to insert 5 flights for each departure city
            FOR i IN 1..25 LOOP
                -- Select random arrival city
                SELECT CityName INTO arrival_city_name
                FROM CITIES
                WHERE CityName != departure_city_name
                ORDER BY random()
                LIMIT 1;
                
                -- Select random aircraft type
                SELECT AircraftType INTO aircraft_type
                FROM AIRPLANE_TYPES
                ORDER BY random()
                LIMIT 1;

                -- Randomly select day of the week
                day_of_week := CASE 
                                  WHEN random() < 0.2 THEN 'MO'
                                  WHEN random() < 0.4 THEN 'TU'
                                  WHEN random() < 0.6 THEN 'WE'
                                  WHEN random() < 0.8 THEN 'TH'
                                  ELSE 'FR'
                              END;

                -- Insert flight
                INSERT INTO FLIGHTS (DayOfWeek, DepartureCityName, ArrivalCityName, AircraftType)
                VALUES (day_of_week, departure_city_name, arrival_city_name, aircraft_type);
            END LOOP;
        END LOOP;
END$$;