rem
rem  +---------------------------------------------------------------------+
rem  |  VEHICLERENTAL.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Vehicle Rental Database	   		   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Client(
clientNo NUMBER(8) NOT NULL, 
clientName VARCHAR2(50) NOT NULL UNIQUE, 
clientStreet VARCHAR2(40) NOT NULL, 
clientCity VARCHAR2(15) NOT NULL, 
clientState VARCHAR2(15) NOT NULL, 
clientZipCode VARCHAR2(9), 
clientTelNo VARCHAR2(20) NOT NULL UNIQUE, 
clientFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
clientWebAddress VARCHAR2(100), 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (clientNo)
);

CREATE TABLE Outlet(
outletNo NUMBER(8) NOT NULL, 
outletStreet VARCHAR2(40) NOT NULL, 
outletCity VARCHAR2(15) NOT NULL, 
outletState VARCHAR2(15) NOT NULL, 
outletZipCode VARCHAR2(9) NOT NULL, 
outletTelNo VARCHAR2(20) NOT NULL UNIQUE, 
outletFaxNo VARCHAR2(20) NOT NULL UNIQUE,
managerEmployeeNo NUMBER(8),
PRIMARY KEY (outletNo)
);

CREATE TABLE Employee(
employeeNo NUMBER(8) NOT NULL, 
title VARCHAR2(10) 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, 
outletNo NUMBER(8) NOT NULL,
PRIMARY KEY (employeeNo),
CONSTRAINT outletnumber FOREIGN KEY (outletNo) REFERENCES Outlet(outletNo)
);

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

CREATE TABLE Vehicle(
vehLicenseNo VARCHAR2(10) NOT NULL, 
vehicleMake VARCHAR2(15) NOT NULL, 
vehicleModel VARCHAR2(15) NOT NULL, 
color VARCHAR2(15) NOT NULL, 
noDoors NUMBER(1) NOT NULL, 
capacity NUMBER(4) NOT NULL, 
hireRate NUMBER(6, 2) NOT NULL, 
outletNo NUMBER(8) NOT NULL,
PRIMARY KEY (vehLicenseNo),
CONSTRAINT outletnumber1 FOREIGN KEY (outletNo) REFERENCES Outlet(outletNo)
);

CREATE TABLE FaultReport(
vehLicenseNo VARCHAR2(10) NOT NULL, 
dateChecked DATE NOT NULL, 
timeChecked NUMBER(4, 2) NOT NULL, 
comments VARCHAR2(50) NOT NULL, 
employeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (vehLicenseNo, dateChecked),
CONSTRAINT vehiclelicensenumber FOREIGN KEY (vehLicenseNo) REFERENCES Vehicle(vehLicenseNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE RentalAgreement(
rentalNo NUMBER(8) NOT NULL, 
dateStart DATE NOT NULL, 
timeStart NUMBER(4, 2) NOT NULL, 
dateReturn DATE, 
timeReturn NUMBER(4, 2), 
mileageBefore NUMBER(8) NOT NULL, 
mileageAfter NUMBER(8), 
policyNo NUMBER(15) NOT NULL UNIQUE, 
insuranceCoverType VARCHAR2(10) NOT NULL, 
insurancePremium NUMBER(5, 2) NOT NULL, 
clientNo NUMBER(8) NOT NULL, 
vehLicenseNo VARCHAR2(10) NOT NULL,
PRIMARY KEY (rentalNo), 
CONSTRAINT clientnumber FOREIGN KEY (clientNo) REFERENCES Client(clientNo),
CONSTRAINT vehiclelicensenumber1 FOREIGN KEY (vehLicenseNo) REFERENCES Vehicle(vehLicenseNo)
);