CREATE TABLE Customer (Customer_No CHAR(4) NOT NULL, Title CHAR(4) NULL, First_Name VARCHAR2(15) NOT NULL, Surname VARCHAR2 (15) NOT NULL, House_No VARCHAR2 (15) NOT NULL, Street_Name VARCHAR2 (30) NULL, Town_Name VARCHAR2 (30) NULL, Postcode CHAR(8) NOT NULL, Phone_No CHAR(12) NOT NULL UNIQUE, Primary key (Customer_No) ); INSERT INTO Customer VALUES (1, 'Mr', 'Keith', 'Lanegan', '2', 'Kinnertone Close', 'Wrexham', 'LL137YT', '01978815234') INSERT INTO Customer VALUES (2, 'Mrs', 'Janice', 'Lawrence', '18', 'Talbot Street', 'Chester', 'CH12FD', '01244674081') INSERT INTO Customer VALUES (3, 'Mr', 'Miles', 'Grewcock', '138', 'Wordsworth Crescent', 'Wrexham', 'LL146BD', '01978375476') INSERT INTO Customer VALUES (4, 'Mrs', 'Petra', 'Griffiths', '15', 'Famau View', 'Buckley', 'CH76SS', '01244390566') INSERT INTO Customer VALUES (5, 'Mr', 'John', 'Evans', '3', 'Bridgend', 'Mickletrafford', 'CH29AC', '01513390411') INSERT INTO Customer VALUES (6, 'Mrs', 'Ruby', 'Fantham', '40', 'Delamere Park', 'Cuddington', 'CH83XT', '01606883362') INSERT INTO Customer VALUES (7, 'Mr', 'Peter', 'Leaney', '69', 'Oakley Way', 'Cefnybedd', 'LL238ST', '01948661004') INSERT INTO Customer VALUES (8, 'Mrs', 'Edna', 'Joplin', '8', 'Foxfields', 'Windsor', 'CH665DB', '01948660004') INSERT INTO Customer VALUES (9, 'Mr', 'Andrew', 'Hamilton', '65', 'Hawthorns Rd', 'Saughall', 'CH61BD', '01244880095') INSERT INTO Customer VALUES (10, 'Mrs', 'Marge', 'Jackson', '12', 'Ryecroft', 'Elton', 'CH87BS', '01513552313') INSERT INTO Customer VALUES (11, 'Mr', 'Bernie', 'O Brein', '6', 'Tenkesbury Close', 'Great Sutton', 'CH658AB', '01513481896') INSERT INTO Customer VALUES (12, 'Mrs', 'Virginia', 'Robertson', '50', 'Woodlane', 'Hawrden', 'CH667NS', '01244532987'); CREATE TABLE Vehicle ( Vehicle_No CHAR(10) NOT NULL, Registration CHAR(7) NULL UNIQUE, Vehicle_Type VARCHAR2(20) NOT NULL, Seat_No NUMBER(2) NOT NULL constraint SeatRange CHECK (Seat_No BETWEEN 3 and 10), Model VARCHAR2(15) NOT NULL, Make VARCHAR2(15) NULL, Hourly_Hire_Rate NUMBER(6,2) NULL Constraint HourlyHireRange CHECK (Hourly_Hire_Rate between 10 and 500), primary key (Vehicle_No) ); INSERT INTO Vehicle VALUES ('1', 'P732XDT','Classic Car', 5, 'Jaguar', 'XK8', 95.00); INSERT INTO Vehicle VALUES (2, 'L128DGC', 'Rolls Royce', 7, 'Rolls Royce', 'Phantom 4', 145.00) INSERT INTO Vehicle VALUES (3, 'U894TFG', 'Carrige', 6, 'Glass Landau', NULL, 300.00) INSERT INTO Vehicle VALUES (4, 'V972PPD', 'Classic Car', 5, 'Mercedes', 'S100', 75.00) INSERT INTO Vehicle VALUES (5, 'F236KVT', 'Rolls Royce', 6, 'Rolls Royce', 'Silver Cloud', 150.00) INSERT INTO Vehicle VALUES (6, 'D238TYD', 'Carrige', 6, 'Open Backed Carrige', NULL, 320.00) INSERT INTO Vehicle VALUES (7, 'B276PPB', 'Classic Car', 4, 'Hillman', 'Husky', 105.00) INSERT INTO Vehicle VALUES (8, 'T611LXY', 'Rolls Royce', 5, 'Rolls Royce', 'Wraith', 175.00) INSERT INTO Vehicle VALUES (9, 'PSBD359', 'Carrige', 6, 'Continental', 'Phaeton', 250.00); CREATE TABLE Staff (Staff_No CHAR(5) NOT NULL, Title CHAR(4) NOT NULL, First_Name VARCHAR2 (15) NOT NULL, Surname VARCHAR2 (15) NOT NULL, House_Number VARCHAR2 (15) NOT NULL, Street VARCHAR2 (30) NOT NULL, Town VARCHAR2 (30) NOT NULL, Postcode CHAR(8) NOT NULL UNIQUE, Contact_Number CHAR(12) NOT NULL, National_Insurance_No CHAR(10) NOT NULL UNIQUE, Driving_License_No CHAR(16) NOT NULL UNIQUE, Date_Joined_Company DATE NULL, Hourly_Rate DECIMAL(8,2) NOT NULL constraint HourlyRateRange CHECK (Hourly_Rate between 0 and 50), Position VARCHAR2(30) NOT NULL, Primary key (Staff_No)); INSERT INTO Staff VALUES (1, 'Mr', 'Glenn', 'Callings', '4', 'Manor Close', 'Neston', 'Ch669JM', '01513361187','BP998723S', 'dhaus132456d12df',TO_DATE('11-02-98', 'DD-MM-YYYY'), 30.00, 'Driver') INSERT INTO Staff VALUES (2, 'Mrs', 'Davenport', 'Crowxley', '11', 'WestBury Way', 'Saltney', 'CH46TB', '01244683898', 'AB112233C','dashu65784d12we',TO_DATE('05-06-02', 'DD-MM-YYYY'), 35.00,'Driver') INSERT INTO Staff VALUES (3, 'Mr', 'Steven', 'Eccles', 'Olde Glebe Farmhouse', 'Church Street', 'Malpas', 'CH668DE', '01948861243', 'CH668457D', 'dhasuy45781s73ew', TO_DATE('11-11-02', 'DD-MM-YYYY'), 40.00, 'Driver') INSERT INTO Staff VALUES (4, 'Mrs', 'Betty', 'Fish', '21', 'Barton Park', 'Fardon', 'CH623TM', '01948861243', 'TY661133G','plshu456127s41it',TO_DATE('06-05-04', 'DD-MM-YYYY'), '38.00' , 'Driver') INSERT INTO Staff VALUES (5, 'Mr', 'George', 'Goltz', '79', 'Lache Lane', 'Chester', 'CH15BJ', '01244677017', 'XB889977B','jkdsew453267d8ew', TO_DATE('09-09-06', 'DD-MM-YYYY'), 45.00, 'Driver') INSERT INTO Staff VALUES (6, 'Mr', 'Leon', 'Jones', '7', 'Adelaide Road', 'Blacon', 'CH29AA', '01244376706', 'DB117766A','zxider23782d45ko',TO_DATE('06-02-96', 'DD-MM-YYYY'), 42.00, 'Driver') INSERT INTO Staff VALUES (7, 'Mr', 'John', 'Martin', '21', 'Malpas Crescent', 'Hawrden', 'CH61TT', '01244531908', 'EC221177C','okrper12382d45ko',TO_DATE('08-03-03', 'DD-MM-YYYY'), 47.00, 'Driver') INSERT INTO Staff VALUES (8, 'Mr', 'Stave', 'Mongey', '3', 'Wenlock Lane', 'Great Sutton', 'CH658SP', '01513560905', 'BD662233S','trider12382d45ko',TO_DATE('02-08-03', 'DD-MM-YYYY'), 37.00, 'Driver') INSERT INTO Staff VALUES (9, 'Mrs', 'Stephenie', 'Nielb', '19', 'South Street', 'Chester', 'CH15AF', '01244345228', 'XT135467Y','grider12632d45yo',TO_DATE('01-07-99', 'DD-MM-YYYY'), 35.00, 'Driver') INSERT INTO Customer VALUES (10, 'Mr', 'Bernie', 'Cliffdon', '24', 'Kynaston Drive', 'Saltney', 'CH64SP', '01244671671', 'JE892466P', 'gfdser16382d45yo', TO_DATE('02-09-04', 'DD-MM-YYYY'), 30.00, 'Driver'); CREATE TABLE Driving_Skills (StaffNumber CHAR(5) NOT NULL, Classic_Cars CHAR(4) default 'No' constraint Classic_CarsYesOrNo CHECK (Classic_Cars='Yes' Or Classic_Cars='No'), Rolls_Royce CHAR(4) default 'No' constraint Rolls_RoyceYesOrNo CHECK (Rolls_Royce='Yes' Or Rolls_Royce='No'), Horse_And_Carriage CHAR(4) default 'No' constraint Horse_And_CarriageYesOrNo CHECK (Horse_And_Carriage='Yes' Or Horse_And_Carriage='No'), foreign key (StaffNumber) references Staff(Staff_No)); INSERT INTO Driving_Skills VALUES (1, 'Yes', 'No', 'No') INSERT INTO Driving_Skills VALUES (2, 'No', 'Yes', 'No') INSERT INTO Driving_Skills VALUES (3, 'No', 'No', 'Yes') INSERT INTO Driving_Skills VALUES (4, 'Yes', 'Yes', 'No') INSERT INTO Driving_Skills VALUES (5, 'No', 'Yes', 'Yes') INSERT INTO Driving_Skills VALUES (6, 'Yes', 'No', 'Yes') INSERT INTO Driving_Skills VALUES (7, 'Yes', 'Yes', 'Yes') INSERT INTO Driving_Skills VALUES (8, 'Yes', 'Yes', 'No') INSERT INTO Driving_Skills VALUES (9, 'No', 'Yes', 'No') INSERT INTO Driving_Skills VALUES (10, 'Yes', 'No', 'No'); CREATE TABLE Order_Record (Order_No CHAR(4) NOT NULL, Customer_No CHAR(4) NOT NULL, Date_Order_Submitted DATE NOT NULL, Date_Order_Due DATE NOT NULL, Half_Due_Date DATE NOT NULL, Deposit_Paid CHAR(4) default 'No' constraint DepositPaidYesOrNo CHECK (Deposit_Paid='Yes' Or Deposit_Paid='No'), Total_Cost DECIMAL(6,2) NULL, primary key (Order_No), foreign key (Customer_No) references Customer(Customer_No) ); INSERT INTO Order_Record Values (1, 1, TO_DATE('08-05-07', 'DD-MM-YYYY'), TO_DATE('06-08-07', 'DD-MM-YYYY'), TO_DATE('06-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (2, 3, TO_DATE('10-07-07', 'DD-MM-YYYY'), TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('15-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (3, 2, TO_DATE('01-07-07', 'DD-MM-YYYY'), TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('15-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (4, 5, TO_DATE('28-06-07', 'DD-MM-YYYY'), TO_DATE('24-08-07', 'DD-MM-YYYY'), TO_DATE('24-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (5, 4, TO_DATE('05-07-07', 'DD-MM-YYYY'), TO_DATE('20-09-07', 'DD-MM-YYYY'), TO_DATE('20-10-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (6, 7, TO_DATE('10-08-07', 'DD-MM-YYYY'), TO_DATE('27-09-07', 'DD-MM-YYYY'), TO_DATE('27-10-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (7, 8, TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('28-09-07', 'DD-MM-YYYY'), TO_DATE('28-10-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (8, 6, TO_DATE('28-09-07', 'DD-MM-YYYY'), TO_DATE('18-10-07', 'DD-MM-YYYY'), TO_DATE('18-11-07', 'DD-MM-YYYY'), 'Yes',45) INSERT INTO Order_Record VALUES (9, 12, TO_DATE('18-09-07', 'DD-MM-YYYY'), TO_DATE('22-10-07', 'DD-MM-YYYY'), TO_DATE('22-11-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (10, 11, TO_DATE('22-09-07', 'DD-MM-YYYY'), TO_DATE('22-10-07', 'DD-MM-YYYY'), TO_DATE('22-11-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (11, 9, TO_DATE('25-09-07', 'DD-MM-YYYY'), TO_DATE('25-10-07', 'DD-MM-YYYY'), TO_DATE('25-11-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (12, 10, TO_DATE('18-10-07', 'DD-MM-YYYY'), TO_DATE('29-10-07', 'DD-MM-YYYY'), TO_DATE('29-11-07', 'DD-MM-YYYY'), 'Yes', 45); create table Vehicles_Ordered (Vehicle_No CHAR(10) NOT NULL, Staff_No CHAR(5) NOT NULL, Order_No CHAR(4) NOT NULL, Hire_Start_Time DATE NOT NULL, Hire_End_Time DATE NOT NULL, Passenger_No NUMBER(2) NOT NULL constraint PassengerNumberRange CHECK (Passenger_No BETWEEN 0 and 10), Location1_House_No VARCHAR2(30) NOT NULL, Location1_Street_Name VARCHAR2(30) NULL, Location1_Town VARCHAR2(30) NULL, Location1_Postcode CHAR(8) NOT NULL, Location1_Start_Time DATE NOT NULL, Location1_End_Time DATE NOT NULL, Location2_House_No VARCHAR2(30) NOT NULL, Location2_Street_Name VARCHAR2(30) NULL, Location2_Town VARCHAR2(30) NULL, Location2_Postcode CHAR(8) NOT NULL, Location2_Start_Time DATE, Location2_End_Time DATE, Location3_House_No VARCHAR2(30), Location3_Street_Name VARCHAR2(30) NULL, Location3_Town VARCHAR2(30) NULL, Location3_Postcode CHAR(8), primary key (Vehicle_No, Staff_No), foreign key (Vehicle_No) references Vehicle(Vehicle_No), foreign key (Staff_No) references Staff(Staff_No), foreign key (Order_No) references Order_Record(Order_No) ); INSERT INTO Order_Record Values (1, 1, TO_DATE('08-05-07', 'DD-MM-YYYY'), TO_DATE('06-08-07', 'DD-MM-YYYY'), TO_DATE('06-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (2, 3, TO_DATE('10-07-07', 'DD-MM-YYYY'), TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('15-09-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (3, 2, TO_DATE('01-07-07', 'DD-MM-YYYY'), TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('15-09-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (4, 5, TO_DATE('28-06-07', 'DD-MM-YYYY'), TO_DATE('24-08-07', 'DD-MM-YYYY'), TO_DATE('24-09-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (5, 4, TO_DATE('05-07-07', 'DD-MM-YYYY'), TO_DATE('20-09-07', 'DD-MM-YYYY'), TO_DATE('20-10-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (6, 7, TO_DATE('10-08-07', 'DD-MM-YYYY'), TO_DATE('27-09-07', 'DD-MM-YYYY'), TO_DATE('27-10-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (7, 8, TO_DATE('15-08-07', 'DD-MM-YYYY'), TO_DATE('28-09-07', 'DD-MM-YYYY'), TO_DATE('28-10-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (8, 6, TO_DATE('28-09-07', 'DD-MM-YYYY'), TO_DATE('18-10-07', 'DD-MM-YYYY'), TO_DATE('18-11-07', 'DD-MM-YYYY'), 'Yes',45) INSERT INTO Order_Record VALUES (9, 12, TO_DATE('18-09-07', 'DD-MM-YYYY'), TO_DATE('22-10-07', 'DD-MM-YYYY'), TO_DATE('22-11-07', 'DD-MM-YYYY'), 'Yes', 45); INSERT INTO Order_Record VALUES (10, 11, TO_DATE('22-09-07', 'DD-MM-YYYY'), TO_DATE('22-10-07', 'DD-MM-YYYY'), TO_DATE('22-11-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (11, 9, TO_DATE('25-09-07', 'DD-MM-YYYY'), TO_DATE('25-10-07', 'DD-MM-YYYY'), TO_DATE('25-11-07', 'DD-MM-YYYY'), 'Yes', 45) INSERT INTO Order_Record VALUES (12, 10, TO_DATE('18-10-07', 'DD-MM-YYYY'), TO_DATE('29-10-07', 'DD-MM-YYYY'), TO_DATE('29-11-07', 'DD-MM-YYYY'), 'Yes', 45);