USE AIRPORT
GO





DROP TABLE AIRPORT
CREATE TABLE AIRPORT
(
Airport_code varchar(50) NOT NULL,
City varchar(50) NOT NULL,
State varchar(50) NOT NULL,
Name varchar (50) NOT NULL,
CONSTRAINT PK_AIRPORT PRIMARY KEY (Airport_code)
)



DROP TABLE AIRPLANE_TYPE
CREATE TABLE AIRPLANE_TYPE
(
Company varchar(50) NOT NULL,
Type_name varchar(50) NOT NULL,
Max_seats varchar(50) NOT NULL,
CONSTRAINT PK_AIRPLANE_TYPE PRIMARY KEY (Type_name)
)



DROP TABLE AIRPLANE
CREATE TABLE AIRPLANE
(
Type_name varchar(50),
Airplane_id varchar(50) NOT NULL,
Total_no_of_seats varchar(50) NOT NULL,
CONSTRAINT PK_AIRPLANE PRIMARY KEY (Airplane_id),

CONSTRAINT FK_AIRPLANE FOREIGN KEY (Type_name) REFERENCES AIRPLANE_TYPE (Type_name)
	ON UPDATE CASCADE
	ON DELETE NO ACTION
)



DROP TABLE FLIGHT
CREATE TABLE FLIGHT
(
Number int,
Airline varchar(50) NOT NULL,
Weekdays varchar(100) NOT NULL,
CONSTRAINT PK_FLIGHT PRIMARY KEY (Number)
)



DROP TABLE SEAT
CREATE TABLE SEAT
(
Date date NOT NULL,
Seat_no int NOT NULL,
CONSTRAINT PK_SEAT UNIQUE (Seat_no),

CONSTRAINT FK_SEAT FOREIGN KEY (Date) REFERENCES LEG_INSTANCE (Date)
	ON UPDATE CASCADE
	ON DELETE NO ACTION
)


DROP TABLE FLIGHT_LEG
CREATE TABLE FLIGHT_LEG
(
Number int NOT NULL,
Leg_no int NOT NULL,
Scheduled_dep_time time NOT NULL,
Scheduled_arr_time time NOT NULL,
CONSTRAINT PK_FLIGHT_LEG UNIQUE (Leg_no),
CONSTRAINT FK_FLIGHT_LEG FOREIGN KEY (Number) REFERENCES FLIGHT (Number)
	ON UPDATE CASCADE
	ON DELETE NO ACTION
)



DROP TABLE LEG_INSTANCE
CREATE TABLE LEG_INSTANCE
(
Airport_code varchar(50) NOT NULL,
Dep_time time NOT NULL,
Arr_time time NOT NULL,
Leg_no int NOT NULL,
Airplane_id varchar(50) NOT NULL,
Date date NOT NULL,
No_of_avail_seats int NOT NULL,
CONSTRAINT PK_LEG_INSTANCE UNIQUE (Date),

CONSTRAINT FK_LEG_INSTANCE FOREIGN KEY (Airplane_id) REFERENCES AIRPLANE (Airplane_id)
	ON UPDATE CASCADE
	ON DELETE NO ACTION,

CONSTRAINT FK_INSTANCE_OF FOREIGN KEY (Leg_no) REFERENCES FLIGHT_LEG (Leg_no)
	ON UPDATE CASCADE
	ON DELETE NO ACTION,

CONSTRAINT FK_DEPARTS_ARRIVES FOREIGN KEY (Airport_code) REFERENCES AIRPORT (Airport_code)
	ON UPDATE CASCADE
	ON DELETE NO ACTION,
)



DROP TABLE FARE
CREATE TABLE FARE
(
Restrictions varchar(50) NOT NULL,
Amount decimal(5,2) NOT NULL,
Code int NOT NULL,
CONSTRAINT PK_FARE UNIQUE (Code),
CONSTRAINT FK_FARE FOREIGN KEY (Code) REFERENCES FLIGHT (Number)
	ON UPDATE CASCADE
	ON DELETE NO ACTION
)




DROP TABLE AIRPLANE_AIRPORT
CREATE TABLE AIRPLANE_AIRPORT
(
Type_name varchar(50) NOT NULL,
Airport_code varchar(50) NOT NULL,
CONSTRAINT PK_CAN_LAND PRIMARY KEY (Type_name,Airport_code),
CONSTRAINT FK_AIRPLANE_TYPE FOREIGN KEY (Type_name) REFERENCES AIRPLANE_TYPE (Type_name)
	ON UPDATE CASCADE
	ON DELETE CASCADE,

CONSTRAINT FK_AIRPORT FOREIGN KEY (Airport_code) REFERENCES AIRPORT (Airport_code)
	ON UPDATE CASCADE
	ON DELETE CASCADE,
)