Database Systems Assignment: Normalization, ERD, & Schema

This assignment focuses on database design, specifically covering the process of normalization from 1NF to 3NF. The student has developed a dependency diagram and then proceeds to decompose the given data through the different normal forms, explaining the transformations at each step. The solution includes the final schema design based on 3NF, considered adequate for database development, along with a Crows Foot ERD. The assignment concludes with a bibliography of relevant sources. The assignment provides a practical example of applying normalization principles to achieve an efficient and well-structured database design.

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.