Schema.txt

CREATE TABLE Airport(
	Country VARCHAR ( 15 ) NOT NULL,
	IATA VARCHAR ( 3 ),
	State VARCHAR ( 10 ) NOT NULL,
	apName VARCHAR ( 25 ) NOT NULL,
	PRIMARY KEY (IATA)
) ;

CREATE TABLE Customer
(
	FirstName VARCHAR (500) NOT NULL,
	LastName VARCHAR (500) NOT NULL,
  	Email VARCHAR (100) NOT NULL,
	Address VARCHAR (500) NOT NULL,
	hIATA VARCHAR (3) NOT NULL,
	PRIMARY KEY (Email),
	FOREIGN KEY (hIATA ) REFERENCES Airport(IATA) ON UPDATE CASCADE ON DELETE CASCADE
); 

CREATE TABLE CreditCard
(
	Email VARCHAR (100) NOT NULL,
	creditCardNumber BIGINT,
	paymentAddress VARCHAR (500),
	PRIMARY KEY (creditCardNumber),
	CHECK ( creditCardNumber IS NOT NULL OR paymentAddress IS NOT NULL ),
	FOREIGN KEY (Email) REFERENCES Customer(Email) ON UPDATE CASCADE ON DELETE CASCADE
) ;

CREATE TABLE Airline
(
  	AirlineCode VARCHAR (2) NOT NULL,
  	IATA VARCHAR (3) NOT NULL,
  	fullname VARCHAR(200) NOT NULL,
  	country_of_origin VARCHAR(500) NOT NULL,
  	PRIMARY KEY (AirlineCode, iata),
  	FOREIGN KEY (IATA) REFERENCES Airport(IATA)
);

CREATE TABLE Flight
(
	AirlineCode VARCHAR (2),
	FlightNumber INTEGER,
	Iata VARCHAR (3),
	Date TIMESTAMP,
	Diata VARCHAR (3),
	Ddate TIMESTAMP,
	FSeat INTEGER,
	ESeat INTEGER,
	PRIMARY KEY (AirlineCode, FlightNumber),
CHECK ( AirlineCode IS NOT NULL OR FlightNumber IS NOT NULL OR Date IS NOT NULL),
FOREIGN KEY (AirlineCode, iata) REFERENCES Airline(AirlineCode, iata)
);
CREATE TABLE Price
(
	AirlineCode VARCHAR (2),
	FlightNumber INTEGER,
	FirstClassPrice NUMERIC,
	EconomyClassPrice NUMERIC,
	PRIMARY KEY (AirlineCode, FlightNumber),
FOREIGN KEY (AirlineCode, FlightNumber) REFERENCES  Flight (AirlineCode, FlightNumber),
	CHECK (FirstClassPrice > EconomyClassPrice ),
CHECK ( FirstClassPrice IS NOT NULL OR EconomyClassPrice IS NOT NULL) 
);

CREATE TABLE Booking
(
	bookingNumber INTEGER NOT NULL,
	FlightNumber INTEGER NOT NULL,
	Airlinecode VARCHAR (2),
	creditCardNumber BIGINT NOT NULL,
	seatClass VARCHAR(10) NOT NULL, 
	Email VARCHAR(50) NOT NULL,
	PRIMARY KEY (bookingNumber,email,flightnumber,airlinecode),
	FOREIGN KEY (creditCardNumber) REFERENCES Creditcard(creditCardNumber),
	FOREIGN KEY (FlightNumber, airlinecode) REFERENCES Flight(FlightNumber, airlinecode),
	FOREIGN KEY (Email) REFERENCES Customer(Email) ON UPDATE CASCADE
);