---

CREATE DATABASE avialinii;
\c avialinii;

CREATE TABLE routes (routes_id SERIAL PRIMARY KEY, number_routes INT NOT NULL, 
airport_of_departure VARCHAR(50), destination airport VARCHAR(50), 
price ticket INT NOT NULL, flight duration INT NOT NULL);

CREATE TABLE flight (flight_id SERIAL PRIMARY KEY, number_flight INT NOT NULL, 
data_flight INT NOT NULL, flight_information VARCHAR(50));

CREATE TABLE passengers (passengers_id SERIAL PRIMARY KEY, 
passport_number INT NOT NULL, full_name VARCHAR(50), adres INT NOT NULL, 
telefon INT NOT NULL);

CREATE TABLE airplanes (airplanes_id SERIAL PRIMARY KEY, number INT NOT NULL, 
model VARCHAR(100), data INT NOT NULL, service_life INT NOT NULL, 
readiness VARCHAR(100), 
routes_id INT REFERENCES routes(routes_id) ON DELETE CASCADE, 
flight_id INT REFERENCES flight(flight_id) ON DELETE CASCADE,
passengers_id INT REFERENCES passengers(passengers_id) ON DELETE CASCADE,
ship_commanders_id INT REFERENCES ship_commanders(ship_commanders_id) ON DELETE CASCADE);

CREATE TABLE ship_commanders (ship_commanders_id SERIAL PRIMARY KEY, 
nomer INT NOT NULL, full_name VARCHAR(100), adres VARCHAR(50), telephon INT NOT NULL, 
years INT NOT NULL);