Chapter 5: Normalization of Database Tables

Normalization is a formal process for assigning attributes to entities.

Normalization works through a series of stages called normal forms

These result in an increasing number of narrower tables. The highest level of normalization is not always desirable for performance reasons.

Database Tables and Normalization

The Need for Normalization

Case of a Construction Company

Table 5.1


 
 

Figure 5.1: A table matching the report


 
 

Problems with the Figure 5.1

Conversion to First Normal Form

1NF Evergreen Project


 
 

Dependency Diagram

In order to normalize a database it is essential to understand its dependencies. Dependencies are based on the Business Rules that apply.
 
 

1NF Definition

The term first normal form (1NF) describes the tabular format in which:

Second Normal Form

A table is in 2NF if: (It is still possible for a table in 2NF to exhibit transitive dependency; that is, one or more attributes may be functionally dependent on nonkey attributes.)

Conversion to Second Normal Form

Starting with the 1NF format, the database can be converted into the 2NF format by
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR)
ASSIGN (PROJ_NUM, EMP_NUM, HOURS)
 

 

2NF Conversion Results


 
 

Third Normal Form

A table is in Third Normal Form (3NF) if:
 

Conversion to Third Normal Form

Create a separate table with for each set of attributes in a transitive functional dependence relationship.
PROJECT (PROJ_NUM, PROJ_NAME)

ASSIGN (PROJ_NUM, EMP_NUM, HOURS)

EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)

JOB (JOB_CLASS, CHG_HOUR)

Result


 
 

Boyce-Codd Normal Form (BCNF)

Figure 5.7: A figure in 3NF but Not BCNF


 
 

Figure 5.8: Conversion of 3NF to BCNF

 

 
 


 
 

Database Design and Normalization Example: (Construction Company)

Summary of Operations:

Two Initial Entities: (1NF)

PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, 
          JOB_DESCRIPTION, JOB_CHG_HOUR)

 
 

Three Entities After Transitive Dependency Removed

 

 

PROJECT (PROJ_NUM, PROJ_NAME)

EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE)

JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR)

Modified ERD

 

 
 


 
 

Creation of the Composite Entity ASSIGN

PROJECT (PROJ_NUM, PROJ_NAME, EMP_NUM)

EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)

JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR)

ASSIGN (ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
 

 

ERD for Example Project


 
 

Relational Schema


 
 

Higher-Level Normal Forms: 4NF Definition

A table is in 4NF if it is in 3NF and has no multiple sets of multivalued dependencies.


 
 

Conversion to 4NF


 
 

Denormalization

Normalization is only one of many database design goals.

Normalized (decomposed) tables require additional processing, reducing system speed.

Normalization purity is often difficult to sustain in the modern database environment. The conflict between design efficiency, information requirements, and processing speed are often resolved through compromises that include denormalization.