Normalization in DBMS

Second Normal Form (2NF) and Third Normal Form (3NF)

In the previous article, we studied First Normal Form (1NF), where we ensured:

  • Atomic values
  • No repeating groups
  • Single value per cell

However, even after converting a table into 1NF, redundancy and anomalies may still exist. To remove them further, we use Second Normal Form (2NF) and Third Normal Form (3NF).

These normal forms are based on Functional Dependencies.

1. Second Normal Form (2NF)

1.1 Definition

A relation is in Second Normal Form (2NF) if:

  1. It is already in 1NF.
  2. It has no Partial Dependency.

This means that every non-prime attribute must depend on the whole candidate key, not just part of it.

1.2 What is Partial Dependency?

Partial Dependency occurs when:

  • The primary key is composite (made of more than one attribute), and
  • A non-prime attribute depends only on part of the composite key.

1.3 Example of Partial Dependency

Consider the following table:

Student_ID

Course_ID

Student_Name

Course_Name

101

C1

Aditi

DBMS

101

C2

Aditi

OS

102

C1

Rahul

DBMS

Primary Key: (Student_ID, Course_ID)

Functional Dependencies:

Student_ID → Student_Name
Course_ID → Course_Name
(Student_ID, Course_ID) → Student_Name, Course_Name

Here:

  • Student_Name depends only on Student_ID.
  • Course_Name depends only on Course_ID.

These are partial dependencies because they depend on only part of the composite key.

So this table is in 1NF but not in 2NF.

1.4 Converting to 2NF

To remove partial dependency, we decompose the table.

Step 1: Create Student Table

Student_ID

Student_Name

101

Aditi

102

Rahul

Step 2: Create Course Table

Course_ID

Course_Name

C1

DBMS

C2

OS

Step 3: Create Enrollment Table

Student_ID

Course_ID

101

C1

101

C2

102

C1

Now:

  • All non-prime attributes depend on the whole key.
  • No partial dependency exists.

The relation is in 2NF.

2. Third Normal Form (3NF)

Even after achieving 2NF, redundancy can still exist due to transitive dependency.

2.1 Definition

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

  1. It is in 2NF.
  2. It has no Transitive Dependency.

In simple terms:

No non-prime attribute should depend on another non-prime attribute.

2.2 What is Transitive Dependency?

Transitive Dependency occurs when:

X → Y
Y → Z

Therefore:

X → Z

Here, Z depends on X indirectly through Y.

2.3 Example of Transitive Dependency

Consider the table:

Employee_ID

Department_ID

Department_Name

1

D1

Sales

2

D2

HR

3

D1

Sales

Primary Key: Employee_ID

Functional Dependencies:

Employee_ID → Department_ID
Department_ID → Department_Name

Here:

Department_Name depends on Department_ID, not directly on Employee_ID.

So:

Employee_ID → Department_Name (indirectly)

This is a transitive dependency.

The table is in 2NF but not in 3NF.

2.4 Converting to 3NF

To remove transitive dependency, decompose the table.

Step 1: Employee Table

Employee_ID

Department_ID

1

D1

2

D2

3

D1

Step 2: Department Table

Department_ID

Department_Name

D1

Sales

D2

HR

Now:

  • No non-prime attribute depends on another non-prime attribute.
  • Transitive dependency is removed.

The relation is in 3NF.

3. Key Difference Between 2NF and 3NF

Normal Form

Removes

2NF

Partial Dependency

3NF

Transitive Dependency

Important points:

  • 2NF focuses on composite keys.
  • 3NF applies even if the primary key is single.
  • 3NF ensures better data integrity than 2NF.

Summary

Second Normal Form (2NF):

  • Must be in 1NF.
  • Removes partial dependency.
  • Ensures full functional dependency on the whole key.

Third Normal Form (3NF):

  • Must be in 2NF.
  • Removes transitive dependency.
  • Prevents non-prime attributes from depending on other non-prime attributes.

After 3NF, most practical database designs are sufficiently normalized. However, there are cases where 3NF is not enough. In the next article, we will study Boyce-Codd Normal Form (BCNF) and understand how it improves upon 3NF.