Huffman Trucking Fleet Maintenance Table Normalization

Huffman Trucking Fleet Maintenance Table Normalization

Previously, the ongoing Huffman Trucking Fleet Truck Maintenance project had described the tables and attributes to be included in their new database. Next, relationships and cardinalities were introduced to describe how each table interacts with each other. In addition, an Access database was created to hold the tables and describe the relationship. In the final phase of the project, the tables will be normalized.

Normalization includes up to seven steps. For this project, only three normalizations will be conducted. Typically, the third normal form is considered normalized. The normalization will increase data accuracy, maintain data integrity, and prevent data redundancies. These benefits allow for a more efficient and effective database.

First Normal Form

First Normal Form (1NF) requires that all multi-valued columns are removed and any redundant columns and rows are removed. The VENDORS table requires normalization to 1NF. Order Contact and Billing Contact are personal names in multi-value format. Therefore, they will be broken down to OrderFName, OrderLName, BillFName, and BillLName.

Next, PARTS_CATALOGUE only had one issue towards 1NF. The Manufacturer attribute was eliminated because it is a VENDORS alias therefore avoiding repeated elements as mandated by the first normal form. However, all of the attributes are in single value form.

The tables PARTS_PURCHASING_HISTORY, PARTS_INVENTORY_ISSUES and PARTS_INVENTORY_PURCHASES will need to be put into 1NF. For PARTS_PURCHASING_HISTORY, QtyPurchased and OrderQty are redundant columns, so OrderQty is removed. While the column FOB is not clear, it is assumed to contain the necessary value requirements to Huffman Trucking.

For PARTS_INVENTORY_ISSUES and PARTS_INVENTORY_PURCHASES, the TransactionID and PartID make up the primary key. However, these two tables share redundant columns, TransactionID and PartID, with PARTS_PURCHASING_HISTORY as well as each other. Next, PARTS_INVENTORY_ISSUES and PARTS_INVENTORY_PURCHASES share redundant columns PurchasePrice and Quantity. The Quantity column is also value-wise redundant with PARTS_PURCHASING_HISTORY's QtyPurchased. Finally, the tables' column PurchasePrice is also value-wise redundant with PARTS_PURCHASING_HISTORY's Price. Therefore, the tables should be combined with PARTS_PURCHASING_HISTORY and removed. To resolve Quantity, QtyPurchased will be renamed Quantity in the PARTS_PURCHASING_HISTORY table. Also, to resolve PurchasePrice, Price will be renamed PurchasePrice.

Both VEHICLE_TYPES and VEHICLES are in 1NF. They both have columns that are atomic. Moreover, they do not have any repeating values. The Description column in VEHICLE_TYPES is multi-valued, but because it will not be searched by a single value it does not need to be broken down further.

The TIRE_MAINTENANCE

...