Normalization of Entities and Attributes for Driver Trip’s Database
Normalization of Entities and Attributes for Driver Trip’s Database
Being one of the largest trucking companies, Huffman Trucking holds 800 trucks, 2100 Trailers, and 925 drivers. This means the need for a strong and reliable database arises. Also, this creates many issues with storing data and organizing it. The purpose of normalization is to alleviate and anomalies, reduce any redundancies, and to create proper relationships between the tables within the database. This is done through the use of three forms of normalization.
1st Normalization Form
The First Normalization Form (1NF) simple focuses on creating and modifying the tables of a database into their most simple form, and ensuring they meet the following,
All columns within the table are unique.
The order of the rows and columns doesn’t matter
Rows do not contain the same values in each field.
Each column has only one type of data within its fields.
Each column only holds one value.
There is no repeating data among the columns.
1NF of Five New Tables
Separating the Driving Log into multiple tables allows for more accurate, comprehensive, and real time information to be stored. Creation of the following tables was designed to further organization of the Driving Log Table:
Trip Log
Trip Log Non-Driving
Truck
Trailer
Each of these tables was derived from the original Drivers Log table by separating out the original table into multiple sections. The first was the Trip table which is designed to draw the four other tables together. It holds the Primary key of TRIP_ID which is the unique key that identifies the table. This also ties each trip to its respective drivers through the Driver and Co-driver ID fields.
The Trip log and Trip log ND are designed to respectively log the times of the trip both driving and not driving. These two tables compile all of the driving start and end times into more manageable tables. Through use of their fields of Trip Log ID and Trip Log ND ID, these two tables tie into the TRIP table to create a complete entry for each trip.
The last is to tie the equipment to the entries. This is done by the Trailer ID which identifies the trailer used as well as calls the information on that trailer. Also, the Truck ID is used to identify the truck that was used for the trip.
Creating these new tables allows for the vulnerabilities and chances of anomalies