/*CREATE DATABASE Flights;*/

/*CREATE TABLE Airline(
code INT CONSTRAINT pk_airline PRIMARY KEY,
name VARCHAR(30) UNIQUE NOT NULL,
COUNTRY VARCHAR(30) NOT NULL,
);*/

/*CREATE TABLE Airport(
code INT NOT NULL,
name VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
CONSTRAINT pk_airport_nc PRIMARY KEY (name, COUNTRY)
);*/

/*CREATE TABLE Airplane(
code INT CONSTRAINT pk_airplane_code PRIMARY KEY,
type VARCHAR(30) NOT NULL,
seats INT NOT NULL CONSTRAINT ck_seats CHECK (seats >= 0),
year DATE
);*/

CREATE TABLE Flight(
fnumber INT,
airline_operator INT NOT NULL CONSTRAINT fk_flight_airline REFERENCES Airline(code),
dep_airport INT NOT NULL CONSTRAINT fk_flight_airport1 REFERENCES Airport(code),
arr_airport INT NOT NULL CONSTRAINT fk_flight_airport2 REFERENCES Airport(code),
flight_time DATETIME NOT NULL,
flight_duration TIME NOT NULL,
airplane INT NOT NULL CONSTRAINT fk_flight_airplane REFERENCES Airplane(code)
);