Database Design

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.