University Database Design: COMP1711 Data Modelling Assignment Part II

This document presents a comprehensive database design solution for an assignment focused on the Wheels4U case study. The solution begins with an analysis of the provided Entity-Relationship Diagram (ERD) and derives relations based on the relational data model. It identifies primary and foreign keys for each relation, justifying the choices made. The solution uses the Database Description Language (DBDL) to define each entity, including attributes, data types, and constraints. The document includes a detailed description of each entity like allocateddrivers, booking, client, company, depot, drivers, insurancepolicy, invoice, service, tariffs, and vehicle. The solution culminates in the transition of the logical model to SQL code, providing the CREATE TABLE statements and ALTER TABLE statements with constraints. The document also includes a bibliography of relevant database management system research papers.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN
Database Description Language:
allocateddrivers (bookingID, driverID);
PRIMARY KEY (bookingID,driverID);
FOREIGN KEY (bookingID) REFERENCES booking (BookingID);
FOREIGN KEY (driverID) REFERENCES drivers (DriverID);
The allocateddrivers entity is the weak entity. It does not have its own attributes to form the
primary key. It has one-to-many relation with both the booking and driver entity. For relation
with the booking entity, the participation is mandatory but for driver it is optional. The on-delete
is no action so that event after deleting the row, the database will hold the information and on-
update is cascade so that after updating the booking or driver primary key, the data will be
updated in this entity also.
booking (BookingID, bookingTime, bookingDate, PickUpDepot, DropOffDepot,
creditcardType, CreditCardNumber, ClientID, Distance, NumberOfDays, PolicyID,
RegistrationNumber, Cost);
PRIMARY KEY (BookingID);
FOREIGN KEY (ClientID) REFERENCES client (ClientID)
FOREIGN KEY (DropOffDepot) REFERENCES depot (DepotCode)
FOREIGN KEY (PickUpDepot) REFERENCES depot (DepotCode)
FOREIGN KEY (PolicyID) REFERENCES insurancepolicy (InsurancePolicyNo)
FOREIGN KEY (RegistrationNumber) REFERENCES vehicle (RegistrationNumber)

2DATABASE DESIGN
The booking entity is the strong entity. It has its own attributes to form the primary key. It has
one-to-many relation with client, depot, isurancepolicy and vehicle entity. All the relation are
optional participation. The on-delete is no action so that event after deleting the row, the
database will hold the information and on-update is cascade so that after updating the associated
entities, the data will be updated in this entity also.
client (ClientID, DriverLicense, ClientName, Address, PhoneNumber);
PRIMARY KEY (ClientID);
The client entity is the strong entity. The clientID is the primary key of client entity. It has many-
to-one relation with booking entity. All the relation is optional participation as new client just
registered into the database will have no booking data.
company (CompanyID, ClientID, CompanyName);
PRIMARY KEY (CompanyID);
FOREIGN KEY (ClientID) REFERENCES client (ClientID)
The company entity is the strong entity. It has its own attributes to form the primary key. It has
one-to-many relation with client entity. All the relation are mandatory participation as a customer
has to be associated with a company. The on-delete is no action so that event after deleting the
row, the database will hold the information and on-update is cascade so that after updating the
associated entities, the data will be updated in this entity also.
depot (DepotCode, Address, PhoneNumber, FaxNumber, Location);
PRIMARY KEY (DepotCode)

3DATABASE DESIGN
The depot entity is the strong entity. The DepotCode is the primary key of client entity. It has
many-to-one relation with booking entity. All the relation is optional participation as no vehicle
can be picked up or dropped off to a specific depot.
drivers (DriverID, DriverLicense, DriverName);
PRIMARY KEY (DriverID)
The `drivers` entity is the strong entity. The DriverID is the primary key of client entity. It has
many-to-one relation with booking entity. All the relation is optional participation as no vehicle
can be picked up or dropped off to a specific depot.
insurancepolicy (InsurancePolicyNo, PolicyType, Cost);
PRIMARY KEY (InsurancePolicyNo)
The insurancepolicy entity is the strong entity. The InsurancePolicyNo is the primary key of
client entity. It has many-to-one relation with booking entity. All the relation is mandatory
participation as an insurance policy has to be associated with a booking.
invoice (InvoiceID, BookingID, FinalCost, PayDate);
PRIMARY KEY (InvoiceID)
FOREIGN KEY (BookingID) REFERENCES booking (BookingID)
The invoice entity is the strong entity. It has its own attribute called InvoiceID to form the
primary key. It has one-to-one relation with booking entity. All the relation are mandatory
participation as an invoice has to be generated for every booking. The on-delete is no action so
that event after deleting the row, the database will hold the information and on-update is cascade
so that after updating the associated entities, the data will be updated in this entity also.

4DATABASE DESIGN
service (ServiceID, RegistrationNumber, serviceDate, ServiceDescription, Cost);
PRIMARY KEY (ServiceID)
FOREIGN KEY (RegistrationNumber) REFERENCES vehicle (RegistrationNumber)
The service entity is the strong entity. It has its own attribute called ServiceID to form the
primary key. It has many-to-one relation with vehicle entity. All the relation are optional
participation as a car may have never sent for service. The on-delete is no action so that event
after deleting the row, the database will hold the information and on-update is cascade so that
after updating the associated entities, the data will be updated in this entity also.
tariffs (TarriffCode, ResgistrationNumber, Conditions, DailyRental);
PRIMARY KEY (TarriffCode)
FOREIGN KEY (ResgistrationNumber) REFERENCES vehicle (RegistrationNumber)
The tariffs entity is the strong entity. It has its own attribute called TarriffCode to form the
primary key. It has many-to-one relation with vehicle entity it is because based on the condition,
the tariff for each vehicle can be different. All the relation are mandatory participation as a tariff
has to be related to a vehicle. The on-delete is no action so that event after deleting the row, the
database will hold the information and on-update is cascade so that after updating the associated
entities, the data will be updated in this entity also.
vehicle (RegistrationNumber, FleetMembershipNumber, Colour, Make, NumberOfDoors,
BodyStyle, TrimLevel);
PRIMARY KEY (RegistrationNumber)
UNIQUE KEY FleetMembershipNumber (FleetMembershipNumber)

5DATABASE DESIGN
The vehicle entity is the strong entity. The RegistrationNumber is the primary key of client
entity. The entity has also a unique key called FleetMembershipNumber. It has many-to-one
relation with booking entity. All the relation is optional participation as a vehicle may not have
been booked yet.
Transition to SQL:
CREATE DATABASE IF NOT EXISTS `wheels4u`;
USE `wheels4u`;
CREATE TABLE `allocateddrivers` (
`bookingID` int(11) NOT NULL,
`driverID` int(11) NOT NULL
);
CREATE TABLE `booking` (
`BookingID` int(11) NOT NULL,
`bookingTime` time NOT NULL,
`bookingDate` date NOT NULL,
`PickUpDepot` int(11) NOT NULL,

6DATABASE DESIGN
`DropOffDepot` int(11) NOT NULL,
`creditcardType` varchar(200) NOT NULL,
`CreditCardNumber` int(11) NOT NULL,
`ClientID` int(11) NOT NULL,
`Distance` decimal(12,2) NOT NULL,
`NumberOfDays` int(11) NOT NULL,
`PolicyID` int(11) NOT NULL,
`RegistrationNumber` int(11) NOT NULL,
`Cost` decimal(12,2) NOT NULL
);
CREATE TABLE `client` (
`ClientID` int(11) NOT NULL,
`DriverLicense` varchar(200) NOT NULL,
`ClientName` varchar(200) NOT NULL,
`Address` varchar(200) NOT NULL,
`PhoneNumber` varchar(200) NOT NULL
);

7DATABASE DESIGN
CREATE TABLE `company` (
`CompanyID` int(11) NOT NULL,
`ClientID` int(11) NOT NULL,
`CompanyName` varchar(200) NOT NULL
);
CREATE TABLE `depot` (
`DepotCode` int(11) NOT NULL,
`Address` varchar(200) NOT NULL,
`PhoneNumber` varchar(200) NOT NULL,
`FaxNumber` int(11) NOT NULL,
`Location` varchar(200) NOT NULL
);
CREATE TABLE `drivers` (
`DriverID` int(11) NOT NULL,
`DriverLicense` varchar(200) NOT NULL,

8DATABASE DESIGN
`DriverName` varchar(200) NOT NULL
);
CREATE TABLE `insurancepolicy` (
`InsurancePolicyNo` int(11) NOT NULL,
`PolicyType` varchar(200) NOT NULL,
`Cost` decimal(12,2) NOT NULL
);
CREATE TABLE `invoice` (
`InvoiceID` int(11) NOT NULL,
`BookingID` int(11) NOT NULL,
`FinalCost` decimal(12,2) NOT NULL,
`PayDate` date NOT NULL
);
CREATE TABLE `service` (
`ServiceID` int(11) NOT NULL,

9DATABASE DESIGN
`RegistrationNumber` int(11) NOT NULL,
`serviceDate` date NOT NULL,
`ServiceDescription` varchar(200) NOT NULL,
`Cost` decimal(12,2) NOT NULL
);
CREATE TABLE `tariffs` (
`TarriffCode` int(11) NOT NULL,
`ResgistrationNumber` int(11) NOT NULL,
`Conditions` varchar(200) NOT NULL,
`DailyRental` decimal(12,2) NOT NULL
);
CREATE TABLE `vehicle` (
`RegistrationNumber` int(11) NOT NULL,
`FleetMembershipNumber` int(11) NOT NULL,
`Colour` varchar(200) NOT NULL,
`Make` varchar(200) NOT NULL,

10DATABASE DESIGN
`NumberOfDoors` int(11) NOT NULL,
`BodyStyle` varchar(200) NOT NULL,
`TrimLevel` varchar(200) NOT NULL
);
ALTER TABLE `allocateddrivers`
ADD PRIMARY KEY (`bookingID`,`driverID`),
ADD KEY `driverID` (`driverID`);
ALTER TABLE `booking`
ADD PRIMARY KEY (`BookingID`),
ADD KEY `ClientID` (`ClientID`),
ADD KEY `DropOffDepot` (`DropOffDepot`),
ADD KEY `PickUpDepot` (`PickUpDepot`),
ADD KEY `PolicyID` (`PolicyID`),
ADD KEY `RegistrationNumber` (`RegistrationNumber`);
ALTER TABLE `client`

11DATABASE DESIGN
ADD PRIMARY KEY (`ClientID`);
ALTER TABLE `company`
ADD PRIMARY KEY (`CompanyID`),
ADD KEY `ClientID` (`ClientID`);
ALTER TABLE `depot`
ADD PRIMARY KEY (`DepotCode`);
ALTER TABLE `drivers`
ADD PRIMARY KEY (`DriverID`);
ALTER TABLE `insurancepolicy`
ADD PRIMARY KEY (`InsurancePolicyNo`);
ALTER TABLE `invoice`
ADD PRIMARY KEY (`InvoiceID`),
ADD KEY `BookingID` (`BookingID`);