Airport Database Schema

airport_database.sql

-- Airport database

-- Table for airports
CREATE TABLE airports (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    arrivals INT NOT NULL,
    departures INT NOT NULL
);

-- Table for pilots
CREATE TABLE pilots (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    -- language JSON NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(255)
);

-- Lookup table for languages
CREATE TABLE languages (
    id SERIAL PRIMARY KEY,
    language TEXT NOT NULL,
    iso_639_code VARCHAR(2) NOT NULL
);

-- Table for pilot_language
CREATE TABLE pilot_language (
    pilot_id INT PRIMARY KEY,
    language_id INT PRIMARY KEY
    FOREIGN KEY (pilot_id) REFERENCES pilots (id),
    FOREIGN KEY (language_id) REFERENCES languages (id)
);

-- Table for flights
CREATE TABLE flights (
    id SERIAL PRIMARY KEY,
    origin_airport_id INT NOT NULL,
    destination_airport_id INT NOT NULL,
    departure_time TIMESTAMP NOT NULL,
    arrival_time TIMESTAMP NOT NULL,
    flight_duration INTERVAL NOT NULL,
    flight_number VARCHAR(255) NOT NULL,
    pilot_id INT NOT NULL,
    FOREIGN KEY (pilot_id) REFERENCES pilots (id),
    FOREIGN KEY (origin_airport_id) REFERENCES airports (id),
    FOREIGN KEY (destination_airport_id) REFERENCES airports (id)
);

-- Table for passengers
CREATE TABLE passengers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(255) NOT NULL,
    nationality VARCHAR(255) NOT NULL,
    miles_travelled INT NOT NULL,
    passport_number VARCHAR(255) NOT NULL
);

-- Table for aircraft
CREATE TABLE aircraft (
    id SERIAL PRIMARY KEY,
    model VARCHAR(255) NOT NULL,
    manufacturer VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL,
    capacity INT NOT NULL
);

-- Table for cargo
CREATE TABLE cargo (
    id SERIAL PRIMARY KEY,
    weight INT NOT NULL,
    description TEXT NOT NULL,
    flight_id INT NOT NULL,
    cost INT NOT NULL,
    FOREIGN KEY (flight_id) REFERENCES flights (id)
);

-- Table for airlines
CREATE TABLE airlines (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    headquarters VARCHAR(255) NOT NULL,
    number_of_passengers INT NOT NULL,
    passengers INT NOT NULL,
    FOREIGN KEY (passengers) REFERENCES passengers (id)
);