CREATE TABLE towns (
town_id INT,
town_name VARCHAR(30) NOT NULL,
CONSTRAINT PK_towns PRIMARY KEY(town_id)
);
CREATE TABLE airports (
airport_id INT,
airport_name VARCHAR(50) NOT NULL,
town_id INT NOT NULL,
CONSTRAINT PK_airports PRIMARY KEY(airport_id),
CONSTRAINT FK_airports_towns FOREIGN KEY(town_id) REFERENCES towns
(town_id)
);
CREATE TABLE airlines (
airline_id INT,
airline_name VARCHAR(30) NOT NULL,
nationality VARCHAR(30) NOT NULL,
rating INT DEFAULT 0,
CONSTRAINT PK_airlines PRIMARY KEY(airline_id)
);
CREATE TABLE customers (
customer_id INT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(1) NOT NULL CHECK (Gender='M' OR Gender='F'),
home_town_id INT NOT NULL,
CONSTRAINT PK_customers PRIMARY KEY(customer_id),
CONSTRAINT FK_customers_towns FOREIGN KEY(home_town_id)
REFERENCES towns(town_id)
);
INSERT INTO towns(town_id, town_name)
VALUES
(1, 'Sofia'),
(2, 'Moscow'),
(3, 'Los Angeles'),
(4, 'Athene'),
(5, 'New York');
INSERT INTO airports(airport_id, airport_name, town_id)
VALUES
(1, 'Sofia International Airport', 1),
(2, 'New York Airport', 5),
(3, 'Royals Airport', 1),
(4, 'Moscow Central Airport', 2);
INSERT INTO airlines(airline_id, airline_name, nationality, rating)
