Skip to content

Instantly share code, notes, and snippets.

@AlexDev404
Last active February 28, 2024 14:26
Show Gist options
  • Save AlexDev404/a6367fec92409d48f05d0fe41bafb71e to your computer and use it in GitHub Desktop.
Save AlexDev404/a6367fec92409d48f05d0fe41bafb71e to your computer and use it in GitHub Desktop.
Airport Database Schema
-- 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)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment