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
Cooling System Replacement
Water Pump
Cooling System Replacement
Radiator
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
Cooling System Replacement
2587945
3758476
3481234
2547984
Oil Replacement
24185
14268
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