rem
rem  +---------------------------------------------------------------------+
rem  |  CLIENTTRANSPORTATION.SQL 					   |
rem  |                                                                     |
rem  |  Create tables for the Client Transportation Database	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Office(
officeNo NUMBER(8) NOT NULL, 
officeAddress VARCHAR2(80) NOT NULL, 
officeTelNo VARCHAR2(20) NOT NULL UNIQUE, 
officeFaxNo VARCHAR2(20) NOT NULL UNIQUE,
PRIMARY KEY (officeNo)
);

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),
officeNo NUMBER(8) NOT NULL,
PRIMARY KEY (clientNo),
CONSTRAINT officenumber FOREIGN KEY (officeNo) REFERENCES Office(officeNo)
);

CREATE TABLE ClientOrder(
orderNo NUMBER(8) NOT NULL, 
dateOrder DATE NOT NULL, 
collectionDate DATE NOT NULL, 
collectionAddress VARCHAR2(80) NOT NULL, 
deliveryDate DATE NOT NULL, 
deliveryAddress VARCHAR2(80) NOT NULL, 
loadWeight NUMBER(6, 1) NOT NULL, 
loadDescription VARCHAR2(40) NOT NULL, 
clientNo NUMBER(8) NOT NULL,
PRIMARY KEY (orderNo),
CONSTRAINT clientnumber FOREIGN KEY (clientNo) REFERENCES Client(clientNo)
);

CREATE TABLE Trailer(
trailerVehLicenseNo VARCHAR2(10) NOT NULL, 
trailerDescription VARCHAR2(40) NOT NULL, 
trailerLength NUMBER(4, 1) NOT NULL, 
maxCarryingWeight NUMBER(6, 1) NOT NULL, 
officeNo NUMBER(8) NOT NULL,
PRIMARY KEY (trailerVehLicenseNo),
CONSTRAINT officenumber1 FOREIGN KEY (officeNo) REFERENCES Office(officeNo)
);

CREATE TABLE Unit(
unitVehLicenseNo VARCHAR2(10) NOT NULL, 
unitDescription VARCHAR2(40) NOT NULL, 
maxPayLoad NUMBER(6, 1) NOT NULL, 
officeNo NUMBER(8) NOT NULL,
PRIMARY KEY (unitVehLicenseNo),
CONSTRAINT officenumber2 FOREIGN KEY (officeNo) REFERENCES Office(officeNo)
);

CREATE TABLE TransportReqts(
orderNo NUMBER(8) NOT NULL, 
transportReqPartNo NUMBER(1) NOT NULL, 
unitVehLicenseNo VARCHAR2(10) NOT NULL, 
trailerVehLicenseNo1 VARCHAR2(10) NOT NULL, 
trailerVehLicenseNo2 VARCHAR2(10) NOT NULL,
PRIMARY KEY (orderNo, transportReqPartNo),
CONSTRAINT unitvehlicensenumber FOREIGN KEY (unitVehLicenseNo) REFERENCES Unit(unitVehLicenseNo),
CONSTRAINT trailerlicensenumber1 FOREIGN KEY (trailerVehLicenseNo1) REFERENCES Trailer(trailerVehLicenseNo),
CONSTRAINT trailerlicensenumber2 FOREIGN KEY (trailerVehLicenseNo2) REFERENCES Trailer(trailerVehLicenseNo)
);