Conversion Of Database Form 2022

In this task, you will perform normalization on a crude dataset so that it could be stored as a relational database. MetroCabs is a private taxi company that offers cab hire services to their regular business clients. You are provided the following data about some of their recent services. The data includes important details on which client hired a taxi on given dates. MetroCabs has a small number of full-time drivers who drive different cars based on vehicle availability. Client ID Client Name Cab Hire Time Cab ID Cab Details Pickup Address Driver Name Driver's License Number 124 Shaun 5/09/2019 14:20 26 Subaru Impreza 2015 6 Kambara Dr, Mulgrave Jeffrey Etheridge *******19 7/09/2019 11:00 11 Toyota Camry 2007 14 Gainsborough Ave, Wheelers Hill Sam Harper *******73 12/09/2019 16:45 18 Honda Civic 2009 6 Kambara Dr, Mulgrave 345 Patrick 6/09/2019 20:10 34 Ford Falcon 2010 52 Brunswick Rd, Mitcham Sanjay Ugle *******24 6/09/2019 22:30 8 Columbia Ave

Running head: DATABASE
DATABASE SYSTEMS
Name of the Student:
Name of the University:
Author Note

1
Dependency Diagram
Given data is described in the table provided below:

2

3
The development of the dependency diagram has been done based on the data provided
above. The dependency diagram provides the details of the dependency of a particular entity on
the other entities in the system.
The following dependencies can be utilized from the dependency diagram developed
above:
ClientID -> ClientName
CabHireTime, ClientID -> PickUpAddress, CabID, DriverName
DriverName -> DriverLicenceNumber
CabID -> CabDetails

4
Step by step decompositions
Step 1: first normal form
In the first normal form the duplicates in the data are resolved and it is made sure that
atomicity is maintained in the data which is to be processed. The data designed in the first
normal form is provided in the table provided below:

5

6
Hire (clientID (pk), ClientName, CabHireTime, CabID, CabDetails, PickUpAddress,
DriverName, DriverLicenseNumber)
Step 2: second normal form
In the second stage the data obtained has been decomposed into the second normal form.
This form is related to the fully functional dependencies and the normal form is applicable for
the composite keys of the system and the relationship is formed with primary key of two or more
attributes. To be automatically considered into the second normal form the relationship should be
having at least a single-attribute primary key.

7
Hire (clientID (pk), ClientName, CabHireTime, CabID, PickUpAddress, DriverName (fk))
Driver (DriverName (pk), DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
For the conversion of the provided data to 2NF from 1NF it is important that the partial
dependencies in the data set is removed. In case of the partial dependencies the partially
dependent attribute is removed and a new relation along with the copy of the determinant was
replaced.
Step 3: third normal form
The relations to be developed in the third normal form the transitive dependencies for the
attributes which were non-prime should already be in a second normal form. In the third normal
form at least any one of the condition is required to be maintained for the system:
The attribute on which the other is dependent should be a super key.
The other attribute should be a prime attribute.

8
Hire (HireID (pk), CabHireTime, CabID (fk), DriverName (fk), AddressID (pk))
Driver (DriverName (pk), DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
Client (ClientID (pk), ClientName)
Address (AddressID (pk), ClientID (fk), PickUpAddress)
For the conversion of the data from the second formal form to the third normal form the
transitive dependencies in the data is to be removed. The removal is done by placement of the
attributes in a new relation along with keeping a copy of the determinant.

9
Final schema design
The final schema design has been based on the third normal form. This because the third
normal form is considered as the adequate normalization. Hence the schema derived below can
be used for further development of the data base.
Hire (HireID (pk), CabHireTime, CabID (fk), DriverName (fk), AddressID (pk))
Driver (DriverName (pk), DriverLicenseNumber)
Cab (CabID (pk), CabDetails)
Client (ClientID (pk), ClientName)
Address (AddressID (pk), ClientID (fk), PickUpAddress)

10
Crows Foot ERD

11
Bibliography
Boissier, M., Meyer, C. A., Djürken, T., Lindemann, J., Mao, K., Reinhardt, P., ... & Uflacker,
M. (2016, October). Analyzing data relevance and access patterns of live production
database systems. In Proceedings of the 25th ACM International on Conference on
Information and Knowledge Management (pp. 2473-2475). ACM.
van Renen, A., Leis, V., Kemper, A., Neumann, T., Hashida, T., Oe, K., ... & Sato, M. (2018,
May). Managing non-volatile memory in database systems. In Proceedings of the 2018
International Conference on Management of Data (pp. 1541-1555). ACM.