/* creates the Branch table
    primary key 'BranchNo' */
CREATE TABLE Branch (
BranchNo NUMBER(3) CONSTRAINT pk_branch PRIMARY KEY ,
BranchName VARCHAR2(28) ,
BranchAddress VARCHAR2(40) ,
City VARCHAR2(18) ,
B_tel_no VARCHAR2(12) );
 
/* creates the staff table
    primary key 'StaffID'
    foreign key 'BranchNo' */
 
CREATE TABLE Staff (
StaffID NUMBER(3) CONSTRAINT pk_staff PRIMARY KEY ,
StaffName VARCHAR2(20) ,
BranchNo NUMBER(3) CONSTRAINT fk_BranchNo references Branch(BranchNo) ,
JobTitle VARCHAR2(20) );
 
/* creates the Customer table
    primary key 'CustomerNo' */
 
CREATE TABLE Customer (
CustomerNo NUMBER(3) CONSTRAINT pk_customer PRIMARY KEY ,
CustomerName VARCHAR2(20) ,
CustomerAddress VARCHAR2(48) ,
CustomerTelNo VARCHAR2(12) );
 
/* creates the Car table
    primary key 'CarRegNo' */
 
CREATE TABLE Car (
CarRegNo NUMBER(3) CONSTRAINT pk_car PRIMARY KEY ,
CarTypeNo VARCHAR2(28) ,
CarModel VARCHAR2(48) ,
Colour VARCHAR2(18) ,
Mileage NUMBER(7) );
 
/* creates the Type table
    primary key 'CarTypeNo' */
 
CREATE TABLE Type (
CarTypeNo NUMBER(3) CONSTRAINT pk_type PRIMARY KEY ,
TypeDescription VARCHAR2(48) ,
DailyCharge NUMBER(5,2) );
 
/* creates the HireContract table
    primary key 'Hire_Contract_No' & 'CarRegNo'
    foreign key 'CarRegNo' & 'CustomerNo' */
 
CREATE TABLE HireContract (
Hire_Contract_No NUMBER(3) ,
CarRegNo NUMBER(3) CONSTRAINT fk_CarRegNo references Car(CarRegNo),
CustomerNo NUMBER(3) CONSTRAINT fk_CustomerNo references Customer(CustomerNo),
StaffID NUMBER(3) ,
CarTypeNo VARCHAR2(3) ,
IssueDate DATE ,
DueReturnDate DATE ,
ActualReturnDate DATE ,
CarCost NUMBER(6,2) ,
CONSTRAINT pk_Hire PRIMARY KEY(Hire_Contract_No, CarRegNo) ,
CONSTRAINT ch_ActualReturnDate CHECK (ActualReturnDate > IssueDate) ,
CONSTRAINT ch_DueReturnDate CHECK (DueReturnDate > IssueDate) );