Question

create table sql query and insert data sql query for the below -

VehicleType(make, model, doors, body,trim)

Vehicle(regNum, fleetNum, colour, make,model)

ScheduledService(date,kilometrage,regNum,depotId)

ServiceHistory(date,cost,description,regNum,depotId)

Records(make,model,tariffId,rentalPrice)

DailyTariff(tariffId, conditions)

Invoice(invoiceId,qualityCheck,datePaid,finalCost,date)

Insurance(insuranceId, policyType, cost)

Insures(insuranceId,date,policyNumber)

HiredVehicle(date, cardType, cardNo, kilometrage, days,tariffId,clientId,depotId,regNum)

Depot(depotId, street,postcode,phone,fax)

Booking(startDate,hiredays,colour,depotId,make,model,clientId)

Client(clientId,street,postcode,phone)

PersonalClient(clientId, fName, lName, title, driversNum)

CompanyClient(clientID, cname)

underlined are primary keys and italicised are foreign keys.

Answer 1

If you have any doubts, please give me comment...

CREATE TABLE VehicleType(

make VARCHAR(30),

model VARCHAR(30),

doors INT,

body VARCHAR(50),

trim VARCHAR(50),

PRIMARY KEY(make, model)

);

CREATE TABLE Vehicle(

regNum VARCHAR(10),

fleetNum INT,

colour VARCHAR(20),

make VARCHAR(30),

model VARCHAR(30),

PRIMARY KEY(regNum),

FOREIGN KEY(make, model) REFERENCES VehicleType(make, model)

);

CREATE TABLE ScheduledService(

date DATE,

kilometrage INT,

regNum VARCHAR(10),

depotId INT,

PRIMARY KEY(date, kilometrage, regNum, depotId)

);

CREATE TABLE ServiceHistory(

date DATE,

cost FLOAT,

description VARCHAR(255),

regNum VARCHAR(10),

depotId INT,

PRIMARY KEY(date, cost, description, regNum, depotId)

);

CREATE TABLE Records(

make VARCHAR(30),

model VARCHAR(30),

tariffId INT,

rentalPrice FLOAT(5,2)

PRIMARY KEY(make, model, tariffId),

)

CREATE TABLE DailyTariff(

tariffId INT,

conditions VARCHAR(100)

FOREIGN KEY(make, model) REFERENCES VehicleType(make, model)

);

CREATE TABLE Invoice(

invoiceId INT NOT NULL PRIMARY KEY,

qualityCheck INT,

datePaid DATE,

finalCost FLOAT(5,2),

date DATE

);

CREATE TABLE Insurance(

insuranceId INT NOT NULL PRIMARY KEY,

policyType VARCHAR(20),

cost FLOAT(5,2)

);

CREATE TABLE Insures(

insuranceId INT,

date DATE,

policyNumber INT,

PRIMARY KEY(insuranceId, policyNumber, date)

);

CREATE TABLE HiredVehicle(

date DATE,

cardType VARCHAR(10),

cardNo INT,

kilometrage INT,

days INT,

tariffId INT,

clientId INT,

depotId INT,

regNum INT

);

CREATE TABLE Depot(

depotId INT NOT NULL PRIMARY KEY,

street VARCHAR(30),

postcode CHAR(5),

phone VARCHAR(15),

fax VARCHAR(15)

);

CREATE TABLE Client(

clientId INT NOT NULL PRIMARY KEY,

street VARCHAR(50),

postcode CHAR(5),

phone VARCHAR(15)

);

CREATE TABLE PersonalClient(

clientId INT NOT NULL PRIMARY KEY,

fName VARCHAR(100),

lName VARCHAR(100),

title VARCHAR(5),

driversNum VARCHAR(15)

FOREIGN KEY(clientId) REFERENCES Client(clientId)

);

CREATE TABLE CompanyClient(

clientId INT NOT NULL PRIMARY KEY,

cname VARCHAR(100),

FOREIGN KEY(clientId) REFERENCES Client(clientId)

);

CREATE TABLE Booking(

startDate DATE,

hiredays INT,

colour VARCHAR(20),

depotId INT,

make VARCHAR(30),

model VARCHAR(30),

clientId INT,

FOREIGN KEY(make, model) REFERENCES VehicleType(make, model),

FOREIGN KEY(clientId) REFERENCES Client(clientId)

);