create table airport( 
	apid 		CHAR(3) not null, 
	name	 	VARCHAR(25) not null, 
	country	 	VARCHAR(25) not null, 
	city	 	VARCHAR(25) not null,
	PRIMARY KEY (apid)
		);

create table aircraftType( 
	type		INTEGER not null, 
	avgSpeed 	INTEGER, 
	maxSpeed 	INTEGER,
	PRIMARY KEY (type) 
	);

create table aircraft( 
	acid		INTEGER not null, 
	type 		INTEGER, 
	PRIMARY KEY (acid), 
	FOREIGN KEY (type) REFERENCES aircraftType ON DELETE RESTRICT 
	);

create table company(
	cid		VARCHAR(2) not null,
	city		VARCHAR(20),
	PRIMARY KEY (cid)
	);

create table employee( 
	eid		INTEGER not null, 
	company		VARCHAR(2),
	PRIMARY KEY (eid),
	FOREIGN KEY (company) REFERENCES company ON DELETE SET NULL
	);

create table pilot ( 
	eid	INTEGER not null, 
	rank	VARCHAR(20) not null CHECK (rank IN ('Captain', 
			'Navigator', 'First Officer')), 
	PRIMARY KEY (eid), 
	FOREIGN KEY (eid) REFERENCES employee ON DELETE CASCADE 
	);

create table flightExpertise ( 
	eid INTEGER not null, 
	type INTEGER not null, 
	PRIMARY KEY (eid, type), 
	FOREIGN KEY (eid) REFERENCES pilot ON DELETE CASCADE, 
	FOREIGN KEY (type) REFERENCES aircraftType ON DELETE CASCADE 
	);

create table flightAttendant ( 
	eid 		INTEGER not null, 
	firstLanguage 	VARCHAR(50) not null, 
	secondLanguage  VARCHAR(50), 
	PRIMARY KEY (eid), 
	FOREIGN KEY (eid) REFERENCES employee ON DELETE CASCADE 
	);

create table flight( 
	company 	VARCHAR(2) not null, 
	flightNo	INTEGER not null, 
	departTime 	TIME not null, 
	arrivalTime	TIME not null, 
	fromAirport	CHAR(3) not null,
	toAirport	CHAR(3) not null, 
	PRIMARY KEY (company, flightNo), 
	FOREIGN KEY (toAirport)   REFERENCES airport (apid) ON  DELETE RESTRICT, 
	FOREIGN KEY (fromAirport) REFERENCES airport (apid) ON  DELETE RESTRICT, 
	CONSTRAINT destinationCheck CHECK (fromAirport <> toAirport), 
	CONSTRAINT timeCheck CHECK (departTime  < arrivalTime)
	);

create table flightImplementation ( 
	company 	VARCHAR(2) not null, 
	flightNo	INTEGER not null, 
	date		DATE not null, 
	mainPilot	INTEGER not null, 
	plane		INTEGER not null, 
	PRIMARY KEY (company, flightNo, date), 
	FOREIGN KEY (company, flightNo) REFERENCES flight ON DELETE CASCADE, 
	FOREIGN KEY (plane) REFERENCES aircraft (acid) ON DELETE RESTRICT, 
	FOREIGN KEY (mainPilot) REFERENCES pilot (eid) ON DELETE RESTRICT 
		);

create table crew ( 
	company		VARCHAR(2) not null, 
	flightNo	INTEGER not null, 
	date		DATE not null, 
	eid		INTEGER not null, 
	PRIMARY KEY (company, flightNo, date, eid), 
	FOREIGN KEY (eid) REFERENCES employee ON DELETE CASCADE, 
	FOREIGN KEY (company, flightNo, date) REFERENCES flightImplementation ON DELETE CASCADE 
	);