Third Normal Form (3NF)

A relation is said to be in Third Normal Form (3NF) if:

• It is already in Second Normal Form (2NF).
• It does not contain any transitive dependency.
• Every non-key attribute depends only on the primary key.

3NF helps reduce data redundancy and improves data integrity by removing attributes that depend on other non-key attributes.

What is Transitive Dependency?

A transitive dependency occurs when a non-key attribute depends on another non-key attribute instead of depending directly on the primary key.

For example:

A → B and B → C

Then C is transitively dependent on A through B.

To achieve 3NF, all transitive dependencies must be removed.

Conditions for Third Normal Form

A relation is in 3NF if, for every non-trivial functional dependency X → Y, at least one of the following conditions is true:

• X is a super key.
• Y is a prime attribute (part of a candidate key).

Example of a Table Not in 3NF

Consider the following EMPLOYEE_DETAIL table:

Key Information

Candidate Key: {EMP_ID}

Non-Prime Attributes:

• EMP_NAME
• EMP_ZIP
• EMP_STATE
• EMP_CITY

Why is the Table Not in 3NF?

In the above table:

• EMP_ID → EMP_ZIP
• EMP_ZIP → EMP_STATE, EMP_CITY

This means:

• EMP_STATE and EMP_CITY depend on EMP_ZIP.
• EMP_ZIP depends on EMP_ID.

Therefore:

• EMP_STATE and EMP_CITY are transitively dependent on EMP_ID.

This violates the rule of Third Normal Form.

Converting the Table into 3NF

To remove the transitive dependency, move EMP_STATE and EMP_CITY into a separate table.

EMPLOYEE Table

EMPLOYEE_ZIP Table

Why is the New Design in 3NF?

After decomposition:

• EMP_NAME and EMP_ZIP depend directly on EMP_ID in the EMPLOYEE table.
• EMP_STATE and EMP_CITY depend directly on EMP_ZIP in the EMPLOYEE_ZIP table.
• No non-key attribute depends on another non-key attribute.

Therefore, both tables satisfy the requirements of Third Normal Form (3NF).

Anomalies in Third Normal Form

Even if the relation in 3NF, it still suffers from insertion, deletion and updation anomalies. So before discussing the next higher normal form, we will explain these anomalies.

To discuss the various anomalies, we will consider the STAFF relation that holds information about the staff, the equipment key they have been allocated and the language in which they are fluent.

STAFF (@S_Name + @Equipment + @Language) where @symbol tells that it is a primary key.

STAFF Relation:

In the above visualization it shows that it is not a relation. In order to represent it as a relation in 1NF, we need to convert it to the form as shown in the following table.

STAFF Relation:

In the above relation, every STAFF has two independent sets of features associated with it.

The STAFF relation has a primary key composed of S_Name, Equipment and Language. There is no transitive dependency, so the relation STAFF is in 3NF. But it stills suffers from the insertion, deletion and updation anomalies which are explained as follows.

1. Insertion Anomaly

Suppose an Anurag learns a new language Japanese then we will have to insert two new records into the STAFF relation. Similarly, if the Equipment values corresponding to staff Anurag changes in numbers from 2 to 5, then with introduction of new language Japanese we will have to insert multiple records into the STAFF relation which results in redundancy.

2. Deletion Anomaly

Let us suppose that the name of the staff changed from Anurag to Anuraj here sue to some reasons then multiple records need to be updated which may result in inconsistency of data.

3. Updation Anomaly

Let us suppose that an staff Kapil has his equipment PC deallocated, then all the information about his languages skills would be lost due to deletion of these records which may result in loss of vital information.

All these anomalies are encountered due to the presence of multivalued dependency which is removed in fourth normal form. The concept of multivalued dependency and 4NF will be explained later.