Risky Situations

Normalizing a database takes place in several iterative steps. At the completion of each step, the database design is further optimized. These steps are named "normal forms". In the business environment, there are usually three normal forms of optimizations. Although there are higher normal forms in existence, these are implemented for statistical or theoretical uses. Normalizing is an important aspect of database design but it can sometimes degrade performance as "more resources are required by the database system to respond to end-user queries" (Coronel, Morris, Rob, 2013, Database Systems: Design, Implementation, and Management, p.181)

First Normal Form (1NF)

The first normal form eliminates repeating groups within a table. A repeating group happens when there are "multiple entries of the same type for any single key attribute occurrence" (Coronel, Morris, Rob, 2013, Database Systems: Design, Implementation, and Management, p.186). For example, in the original ERD diagram for Huffman Trucking, the maintenance descriptions table had three attributes: Maintenance ID (PK), Type of maintenance, and Parts Used. Because databases tables can only store one entity per row, multiple lines would need to be created for each maintenance ID and Type of maintenance if multiple parts were used. The same problem occurs in the Maintenance work order table where the "Parts Necessary" attributes forces the primary key (work order ID) to repeat itself. the following table illustrates this concept.

Table 1-1: Repeating Groups in the Maintenance Description Table

Maintenance ID (PK) Type of maintenance Parts Used

101 Cooling System Replacement Water Pump

101 Cooling System Replacement Radiator

101 Cooling System Replacement Thermostat

To eliminate the problem, there needs to be more granularity added to the table. For example, the following table illustrates how adding granularity removes repeating groups:

Table 1-2: Adding granularity to remove repeating groups

Maintenance ID Type of maintenance Part1_ID Part2_ID Part3_ID Part4_ID

101 Cooling System Replacement 2587945 3758476 3481234 2547984

102 Oil Replacement 24185 14268 NULL NULL

Not all water pumps are the same, they vary by vehicle model. For this reason, it is better to use part ID numbers to specify exactly which part will be used for each maintenance activity. By adding multiple columns for the parts, there is no need to repeat the maintenance ID in multiple rows. The Part1_ID attribute will not accept a null value but the rest of the part IDs will. This