rem
rem  +---------------------------------------------------------------------+
rem  |  TRAVELAGENT.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Travel Agent Database	   	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Country(
countryNo NUMBER(8) NOT NULL, 
countryName VARCHAR2(40) NOT NULL UNIQUE,
PRIMARY KEY (countryNo)
);

CREATE TABLE Resort(
resortNo NUMBER(8) NOT NULL, 
resortName VARCHAR2(40) NOT NULL, 
distanceFromAirport NUMBER(4) NOT NULL, 
timeFromAirport NUMBER(4, 2) NOT NULL, 
countryNo NUMBER(8) NOT NULL,
PRIMARY KEY (resortNo),
CONSTRAINT countrynumber FOREIGN KEY (countryNo) REFERENCES Country(countryNo)
);

CREATE TABLE Branch(
branchNo NUMBER(8) NOT NULL, 
branchStreet VARCHAR2(40) NOT NULL, 
branchCity VARCHAR2(15) NOT NULL, 
branchState VARCHAR2(15) NOT NULL, 
branchZipCode VARCHAR2(9) NOT NULL, 
branchTelNo VARCHAR2(20) NOT NULL UNIQUE, 
branchFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
managerEmployeeNo NUMBER(8),
PRIMARY KEY (branchNo)
);

CREATE TABLE Employee(
employeeNo NUMBER(8) NOT NULL, 
firstName VARCHAR2(30) NOT NULL, 
middleName VARCHAR2(30), 
lastName VARCHAR2(30) NOT NULL, 
address VARCHAR2(80) NOT NULL, 
workTelExt VARCHAR2(4) NOT NULL, 
homeTelNo VARCHAR2(20) NOT NULL, 
empEmailAddress VARCHAR2(100), 
socialSecurityNumber VARCHAR2(12) NOT NULL UNIQUE, 
DOB DATE NOT NULL, 
position VARCHAR2(20) NOT NULL, 
sex CHAR NOT NULL, 
salary NUMBER(8,2 ) NOT NULL, 
dateStarted DATE NOT NULL, 
branchNo NUMBER(8) NOT NULL,
PRIMARY KEY (employeeNo),
CONSTRAINT branchnumber FOREIGN KEY (branchNo) REFERENCES Branch(branchNo)
);

ALTER TABLE Branch
ADD CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo);

CREATE TABLE Customer(
customerNo NUMBER(8) NOT NULL, 
customerName VARCHAR2(50) NOT NULL, 
customerStreet VARCHAR2(40) NOT NULL, 
customerCity VARCHAR2(15) NOT NULL, 
customerState VARCHAR2(15) NOT NULL,
customerZipCode VARCHAR2(9), 
custTelNo VARCHAR2(20) NOT NULL UNIQUE, 
custFaxNo VARCHAR2(20), 
nationality VARCHAR2(40), 
sex CHAR NOT NULL, 
DOB DATE NOT NULL, 
passportNo NUMBER(8) NOT NULL UNIQUE,
PRIMARY KEY (customerNo)
);

CREATE TABLE Operator(
operatorNo NUMBER(8) NOT NULL, 
operatorName VARCHAR2(50) NOT NULL, 
operatorType CHAR NOT NULL, 
operatorStreet VARCHAR2(40) NOT NULL, 
operatorCity VARCHAR2(15) NOT NULL, 
operatorState VARCHAR2(15) NOT NULL, 
operatorZipCode VARCHAR2(9) NOT NULL, 
operTelNo VARCHAR2(20) NOT NULL UNIQUE, 
operFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
contactName VARCHAR2(50), 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100) NOT NULL,
PRIMARY KEY (operatorNo)
);

CREATE TABLE Apartment(
apartmentNo NUMBER(8) NOT NULL, 
apartmentName VARCHAR2(50) NOT NULL, 
apartmentType CHAR NOT NULL, 
apartmentDescription VARCHAR2(40) NOT NULL, 
apartmentRating VARCHAR2(5) NOT NULL, 
apartmentStreet VARCHAR2(40) NOT NULL, 
apartmentCity VARCHAR2(15) NOT NULL, 
apartmentState VARCHAR2(15) NOT NULL, 
apartmentCountry VARCHAR2(40) NOT NULL, 
apartmentZipCode VARCHAR2(9) NOT NULL, 
noOfRooms NUMBER(1) NOT NULL, 
operatorNo NUMBER(8) NOT NULL, 
resortNo NUMBER(8) NOT NULL,
PRIMARY KEY (apartmentNo),
CONSTRAINT operatornumber FOREIGN KEY (operatorNo) REFERENCES Operator(operatorNo),
CONSTRAINT resortnumber FOREIGN KEY (resortNo) REFERENCES Resort(resortNo)
);

CREATE TABLE Facility(
facilityNo NUMBER(8) NOT NULL, 
description VARCHAR2(40) NOT NULL, 
additionalCharge NUMBER(5, 2) NOT NULL,
PRIMARY KEY (facilityNo)
);

CREATE TABLE ApartFacility(
apartmentNo NUMBER(8) NOT NULL, 
facilityNo NUMBER(8) NOT NULL, 
comments VARCHAR2(40) NOT NULL,
PRIMARY KEY (apartmentNo, facilityNo),
CONSTRAINT apartmentnumber FOREIGN KEY (apartmentNo) REFERENCES Apartment(apartmentNo),
CONSTRAINT facilitynumber FOREIGN KEY (facilityNo) REFERENCES Facility(facilityNo)
);

CREATE TABLE Hotel(
hotelNo NUMBER(8) NOT NULL, 
hotelName VARCHAR2(50) NOT NULL, 
hotelStreet VARCHAR2(40) NOT NULL, 
hotelCity VARCHAR2(15) NOT NULL,
hotelState VARCHAR2(15) NOT NULL, 
hotelCountry VARCHAR2(40) NOT NULL, 
hotelZipCode VARCHAR2(40) NOT NULL, 
hotelTelNo VARCHAR2(20) NOT NULL, 
hotelFaxNo VARCHAR2(20), 
hotelType CHAR NOT NULL, 
hotelDescription VARCHAR2(40) NOT NULL, 
hotelRating VARCHAR2(5) NOT NULL, 
hotelMangerName VARCHAR2(50) NOT NULL, 
operatorNo NUMBER(8) NOT NULL, 
resortNo NUMBER(8) NOT NULL,
PRIMARY KEY (hotelNo),
CONSTRAINT operatornumber1 FOREIGN KEY (operatorNo) REFERENCES Operator(operatorNo),
CONSTRAINT resortnumber1 FOREIGN KEY (resortNo) REFERENCES Resort(resortNo)
);

CREATE TABLE HotelFacility(
hotelNo NUMBER(8) NOT NULL, 
facilityNo NUMBER(8) NOT NULL, 
comments VARCHAR2(50) NOT NULL,
PRIMARY KEY (hotelNo, facilityNo),
CONSTRAINT hotelnumber FOREIGN KEY (hotelNo) REFERENCES Hotel(hotelNo),
CONSTRAINT facilitynumber1 FOREIGN KEY (facilityNo) REFERENCES Facility(facilityNo)
);


CREATE TABLE Flight(
flightNo NUMBER(8) NOT NULL, 
planeType VARCHAR2(20) NOT NULL, 
seatCapacity NUMBER(3) NOT NULL, 
airportDepart VARCHAR2(40) NOT NULL, 
departTime NUMBER(4, 2) NOT NULL, 
airportArrive VARCHAR2(40) NOT NULL, 
arriveTime NUMBER(4, 2) NOT NULL, 
operatorNo NUMBER(8) NOT NULL,
PRIMARY KEY (flightNo),
CONSTRAINT operatornumber2 FOREIGN KEY (operatorNo) REFERENCES Operator(operatorNo)
);

CREATE TABLE Holiday(
holidayNo NUMBER(8) NOT NULL, 
status CHAR NOT NULL, 
dateBooked DATE NOT NULL, 
cateringType CHAR NOT NULL, 
startDate DATE NOT NULL, 
finishDate DATE NOT NULL, 
invoiceNo NUMBER(8) NOT NULL, 
totalCost NUMBER(7, 2) NOT NULL, 
dateSent DATE, 
datePaid DATE, 
bookCustomerNo NUMBER(8) NOT NULL, 
hotelNo NUMBER(8) NOT NULL, 
apartmentNo NUMBER(8) NOT NULL, 
inwardFlightNo NUMBER(8) NOT NULL, 
inwardNoOfSeats NUMBER(4) NOT NULL, 
outwardFlightNo NUMBER(8) NOT NULL, 
outwardNoOfSeats NUMBER(4) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
branchNo NUMBER(8) NOT NULL,
PRIMARY KEY (holidayNo),
CONSTRAINT bookcustomernumber FOREIGN KEY (bookCustomerNo) REFERENCES customer(customerNo),
CONSTRAINT hotelnumber1 FOREIGN KEY (hotelNo) REFERENCES Hotel(hotelNo),
CONSTRAINT apartmentnumber1 FOREIGN KEY (apartmentNo) REFERENCES Apartment(apartmentNo),
CONSTRAINT inflightnumber FOREIGN KEY (inwardFlightNo) REFERENCES Flight(flightNo),
CONSTRAINT outflightnumber FOREIGN KEY (outwardFlightNo) REFERENCES Flight(flightNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT branchnumber1 FOREIGN KEY (branchNo) REFERENCES Branch(branchNo)
);

CREATE TABLE CustomerParty(
customerNo NUMBER(8) NOT NULL, 
holidayNo NUMBER(8) NOT NULL,
PRIMARY KEY (customerNo, holidayNo),
CONSTRAINT customernumber FOREIGN KEY (customerNo) REFERENCES Customer(customerNo),
CONSTRAINT holidaynumber FOREIGN KEY (holidayNo) REFERENCES Holiday(holidayNo)
);