Normalization Case

Normalization

Prior to the completion of Change Request 4 and 5, the database it has become necessary to be normalized, with designated primary keys and relationships built between tables. It is typically necessary to normalize a database to the 3rd Normal Form. Anything after that is not essential for most databases.

Normalization means to get rid of duplicate columns by creating separate tables and grouping related data. 1NF, or first normal form means to identify a unique primary key. The primary key is the resources by which tables will be related, or joined together. 2NF, or second normal form, is accomplished when each row in each table is primarily dependent on the designated primary key of each table. In other words, within each table there is no row that can stand or act autonomously of the primary key row. 3NF, or third normal form, means the table has met the necessities for 1NF, 2NF, and redundant data has been eliminated.

The PARTS_CATALOG contains only one candidate key, which is PartID. The description, Type, and Manufacturer would be another possible candidate key, but we cannot take for granted that the same vendor would not name two items the same. For this reason we stay with just the one candidate key, PartID

The PARTS_INVENTORY_PURCHASES table also is fully normalized. TransactionID is a foreign key that is still the only candidate in this table. Although this table serves no use because all of this data in it can be obtained from the PARTS_PURCHASING_HISTORY table, we will leave it in the database for now.

The PARTS_INVENTORY_ISSUES table also uses the foreign TransactionID as its key. There is also no other candidate key in this table. For this table to be more efficient, I would suggest creating a new Autonumber key, and leave the TransactionID in the table for linking purposes with the other two tables.

The VEHICLES_table contains all of the entities and attributes for that table, which include the VIN

number being the primary key, type ID, class code, put into service date, gross weight, mileage,

purchase price, accumulated depreciation, taken out of service date, and capacity. The MAINTENANCE_WORK ORDER table contains vehicle which is the primary key, work order ID, part ID, assignment TO, date started, date completed, and total hours of completed maintenance. The possible candidates for each table