Chapter 5: Normalization of Database Tables

It reduces and controls data redundancies

Helps eliminate data anomalies where insertions, deletions, updates destroy the integrity of the database

At the conclusion of this process the database will be logically correct

Normalization works through a series of stages called normal forms

First normal form (1NF)

Second normal form (2NF)

Third normal form (3NF)

Fourth normal form (4NF)

Database Tables and Normalization

The Need for Normalization

Building project -- Project number, Name, Employees assigned to the project.

Employee -- Employee number, Name, Job classification

The company charges its clients by billing the hours spent on each project. The hourly billing rate is dependent on the employee’s position.

Periodically, a report is generated.

The table whose contents correspond to the reporting requirements is shown in Table 5.1.

Table 5.1

Figure 5.1: A table matching the report

Problems with the Figure 5.1

The project number is intended to be a primary key, but it contains nulls.

The table displays data redundancies.

The table entries invite data inconsistencies.

The data redundancies yield the following anomalies:

Update anomalies. Modifying JOB_Class for an employee requires multiple alterations

Addition anomalies: New employees must be assigned to a project

Deletion anomalies: If an employee leaves all information associated with the employee is lost. If the employee is the only one working on a particular project all information on that project is lost.

Conversion to First Normal Form

A relational table must not contain repeating groups.

Repeating groups can be eliminated by adding the appropriate entry in at least the primary key column(s).

This leads to a much wider, longer table

Until 1NF is achieved we cannot create a relational database at all.

1NF Evergreen Project

Dependency Diagram

The primary key components are bold, underlined, and shaded in a different color.

The arrows above entities indicate all desirable dependencies, in this table i.e., dependencies that are based on PK.

The arrows below the dependency diagram indicate less desirable dependencies -- partial dependencies (not based on entire PK) and transitive dependencies.

1NF Definition

All key attributes are defined.

There are no repeating groups in the table.

All attributes are dependent on the primary key.

Second Normal Form

It is in 1NF and

It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key.

Conversion to Second Normal Form

Writing each key component on a separate line, and then writing the original key on the last line and

Writing the dependent attributes after each new key.

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

It is in 2NF and

It contains no transitive dependencies

Conversion to Third Normal Form

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)

A table is in Boyce-Codd normal form (BCNF) if every determinant in the table is a candidate key.

(A determinant is any attribute whose value determines other values with a row.)

If a table contains only one candidate key, the 3NF and the BCNF are equivalent.

BCNF is a special case of 3NF.

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)

The company manages many projects.

Each project requires the services of many employees.

An employee may be assigned to several different projects.

Some employees are not assigned to a project and perform duties not specifically related to a project. Some employees are part of a labor pool, to be shared by all project teams.

Each employee has a (single) primary job classification. This job classification determines the hourly billing rate.

Many employees can have the same job classification.

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

We note at this point that we actually wish to keep track of the hours an employee is assigned to a project

Attribute ASSIGN_HOUR is assigned to the composite entity ASSIGN.

Adding ASSIGN_NUM as the primary key for ASSIGN allows multiple assignments by an employee to a project

“Manages” relationship is created between EMPLOYEE and PROJECT.

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

Conversion to 4NF

Denormalization

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.