/*
** Rutgers University, CS336 Fall 2019, Group 18 Project, Relational Schema
** Joseph Doll, Eric Kim, Michala Rose, Kieran Stokes
**
*/

/* Table schema below */
CREATE TABLE Airports(
	id CHAR(3),
	PRIMARY KEY(id)
);

CREATE TABLE Airlines(
    id CHAR(2),
    PRIMARY KEY(id)
);

CREATE TABLE Aircraft(
    id CHAR(3),
    PRIMARY KEY(id)
);

CREATE TABLE Seats(
	seat_num CHAR(3),
    seat_class CHAR(10),
	craft_id CHAR(3),
    PRIMARY KEY(seat_num, craft_id),
    FOREIGN KEY(craft_id) REFERENCES Aircraft (id) ON DELETE CASCADE
);

CREATE TABLE Owned_Craft(
	craft_id CHAR(3),
	airline_id CHAR(2),
	PRIMARY KEY(craft_id),
	FOREIGN KEY(craft_id) REFERENCES Aircraft(id),
	FOREIGN KEY(airline_id) REFERENCES Airlines(id)
);

CREATE TABLE Tickets(
	ticket_num CHAR(20),
	booking_fee DECIMAL(6,2),
	round_trip BOOLEAN,
	issue_date DATE,
	total_fare DECIMAL(10,2),
	PRIMARY KEY(ticket_num)
	/*needs customer foreign key*/
);

CREATE TABLE Flights(
	flight_num CHAR(4),
	operating_airline CHAR(2),
	flying_aircraft CHAR(3),
	flight_type CHAR(10),
	depart_time TIME,
	arrive_time TIME,
	PRIMARY KEY(operating_airline, flight_num),
	FOREIGN KEY(operating_airline) REFERENCES Airlines(id) ON DELETE CASCADE,
	FOREIGN KEY(flying_aircraft) REFERENCES Aircraft (id) ON DELETE CASCADE
	/*not sure what fare_first and fare_economy are at the moment*/
	/*needs days of operation*/
);

CREATE TABLE Trips(
	assigned_seat CHAR(3),
	for_ticket CHAR(20),
	airline CHAR(2),
	flight_num CHAR(2),
	date DATE,
	meal CHAR(20), /*not sure how big it should be*/
	FOREIGN KEY(assigned_seat) REFERENCES Seats (seat_num) ON DELETE CASCADE,
	FOREIGN KEY(for_ticket) REFERENCES Tickets (ticket_num) ON DELETE CASCADE,
	FOREIGN KEY(airline, flight_num) REFERENCES Flights (operating_airline, flight_num) ON DELETE CASCADE
);