2NF in DBMS

Second Normal Form (2NF) is a step further in database normalization that builds on the foundation established by First Normal Form (1NF) . A table is in 2NF if it satisfies the following two conditions:

  1. It is in 1NF: The table must first meet all the requirements of First Normal Form (1NF). 1NF ensures that each cell in a table contains a single, indivisible value.

  2. No Partial Functional Dependency:  A relation R is in 2NF if no proper subset of any candidate key functionally determines a non-prime attribute. In simple wording, we can say, A relation R is in 2NF if a non-prime attribute cannot be determined by a proper subset of any candidate key

Violation of 2NF in DBMS

 

To understand this topic, you must know about

When a Relation is always in 2NF in DBMS

When any one of the following cases is true and table in also in 1NF, then we can say that the table is in 2NF

Relation in always in 2NF in DBMS

  • Case 01: Relation R is in first normal form, and R has no non-prime attribute;
  • Case 02: Relation R is in first normal form, and every candidate key of R consists of just a single attribute.

As 2NF eliminates only partial dependencies, and in both cases, partial dependency cannot occur.

Procedure to check if R is in 2NF or not

Here is a three-step procedure to check whether the relation is in 2NF or not.

Step 1: Find all candidate keys. & Find Prime attributes and non-prime attributes.
Step 2: Find Proper Subsets of candidate keys.
Step 3: Find if a proper subset of some candidate key determines a non-prime attribute. If yes, then R is not in 2NF; else R is in 2NF.

2NF in DBMS Examples

Let’s explain some examples of 2NF in DBMS

Example 01: 2NF in DBMS

Suppose R(A, B, C, D, E) is a relational schema with the following set of functional dependencies:

  • A → B
  • B → E
  • C→ D

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {A,C}
  • Prime attributes: {A} , {C}
  • Non-prime attributes: {B}, {D}, {E}

Check First FD: A → B

As “A” is a proper subset of the candidate key, which determines the non-prime attribute “B”. So, this FD is not in 2NF.

Important: If we find any single FD that violates the 2NF, then we can say the relation is not in 2NF.

Result

As the first FD is not in 2NF, the relation R cannot be in 2NF

Example 02: 2NF in DBMS

Suppose R(A, B, C, D, E) is a relational schema with the following set of functional dependencies:

  • AB → CDE
  • CD → ABE

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {A,B}, {C,D}
  • Prime attributes: {A}, {B}, {C}, {D}
  • Non-prime attributes: {E}

Check First FD: AB → CDE

As “AB” is a candidate key (not a proper subset of the candidate key), no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Check Second FD: CD→ ABE

As “CD” is a candidate key (not a proper subset of the candidate key), no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Result

As all  FDs are in 2NF, the relation R is also in 2NF

Example 03: 2NF in DBMS

Suppose R(A, B, C, D) is a relational schema with the following set of functional dependencies:

  • A → C
  • B → A
  • A → D
  • AD → C

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {B}
  • Prime attributes: {B}
  • Non-prime attributes: {A}, {C}, {D}

As we know, when a candidate key contains a single attribute, no proper subset of that candidate key exists. Therefore, that table (relation) must be in 2NF.

Result

No need to check any FD, because the candidate key contains a single attribute. So, the relation is in 2NF

Example 04: 2NF in DBMS

Suppose R(A, B, C, D) is a relational schema with the following set of functional dependencies:

  • C → D
  • CD → A
  • AB → C
  • BD → A

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {A,B}, {C,B}, {B,D}
  • Prime attributes: {A}, {B}, {C}, {D}
  • Non-prime attributes: None

As we know, when there is no non-prime attribute exist then that table (relation) must be in 2NF.

Result

No need to check any FD, because no non-prime attribute exists. So, the relation is in 2NF

Example 05: 2NF in DBMS

Suppose R(A, B, C, D) is a relational schema with the following set of functional dependencies:

  • B → ACD
  • ACD → B

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {B}, {A,C,D}
  • Prime attributes: {A}, {B}, {C}, {D}
  • Non-prime attributes: None

As we know, when there is no non-prime attribute exist then that table (relation) must be in 2NF.

Result

No need to check any FD, because no non-prime attribute exists. So, the relation is in 2NF

Example 06: 2NF in DBMS

Suppose R(A, B, C, D) is a relational schema with the following set of functional dependencies:

  • A → B
  • B → C
  • C → A

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {A,D}, {B,D}, {C,D}
  • Prime attributes: {A, B, C, D}
  • Non-prime attributes: None

As we know, when there is no non-prime attribute exist then that table (relation) must be in 2NF.

Result

No need to check any FD, because no non-prime attribute exists. So, the relation is in 2NF

Example 07: 2NF in DBMS

Suppose R(A, B, C, D, E, F) is a relational schema with the following set of functional dependencies:

  • AB → C
  • C→ D
  • B → AE

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {B,F}
  • Prime attributes: {B}, {F}
  • Non-prime attributes: {A}, {C}, {D}, {E}

Check First FD: AB → C

As “AB” is not a proper subset of the candidate key, no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Check Second FD: C → D

As “C” is not a proper subset of the candidate key, no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Check third FD: B→ AE

As “B” is a proper subset of the candidate key, and its RHS contains non-prime attributes. So, this FD is not in 2NF.

Result

As the third FDs not in 2NF, the relation R is not in 2NF

Example 08: 2NF in DBMS

Suppose R(A, B, C, D, E) is a relational schema with the following set of functional dependencies:

  • AB→ CD
  • D→ A
  • BC → DE

Find out whether the relation R is in 2NF or not?

Solution:

First, we calculate the followings

  • Candidate key: {A,B}, {C,B}, {B,D}
  • Prime attributes: {A}, {B}, {C}, {D}
  • Non-prime attributes: {E}

Check First FD: AB → CD

As “AB” is a candidate key, no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Check Second FD: D → A

As “D” is a proper subset of the candidate key, but RHS of FD does not contain non-prime attributes. So, this FD is not in 2NF.

Check third FD: BC→ DE

As “BC” is a candidate key, no need to check its RHS for non-prime attributes. So, this FD is in 2NF.

Result

As all FDs are in 2NF, the relation R is in 2NF

Convert Table to 2NF (Decompose a relation into 2NF)

To convert a relation into Second Normal Form (2NF), it must first be in First Normal Form (1NF). Next, remove all partial functional dependencies by ensuring that every non-prime attribute is fully functionally dependent on the entire candidate key, not on any proper subset of a composite candidate key.

Rule to Decompose a Table into 2NF

  • Create a separate relation only for each partial functional dependency.
  • Then, keep one relation that preserves the candidate key(s) and the remaining attributes.

Here is an algorithm for converting a relation in 2NF when it is not in 2NF

Step 1: Ensure the Relation is in 1NF

Step 2: Find All Candidate Keys and identify all prime and non-prime attributes.

Step 3: Identify Partial Functional Dependencies

For each functional dependency X → Y:

  • Check whether X is a proper subset of any candidate key.
  • Check whether Y contains one or more non-prime attributes.

If both conditions are true, then X → Y is a partial functional dependency.

Step 4: Create a New Relation for Each Partial Dependency

For every partial dependency X → Y, create a new relation R₁(X, Y)

where

  • X becomes the primary key.
  • Y contains the non-prime attributes fully dependent on X.

Step 5: Remove the Dependent Attributes from the Original Relation

After creating a new relation for a partial dependency:

  • Remove the dependent attribute(s) (Y) from the original relation because they have already been placed in a new relation.
  • Keep all candidate key attributes in the original relation.
  • Keep all remaining attributes that have not been moved to another relation.

Repeat this process for every partial functional dependency.

Step 6: Verify the Result

Ensure that:

  • Every non-prime attribute is fully functionally dependent on every candidate key.
  • No partial functional dependency remains.
  • Each decomposed relation is in 2NF.

Decompose a relation into 2NF – Example 01

Given Relation is R(A, B, C, D, E) with the following Functional Dependencies (FDs)

  • AB → CDE
  • A → C
  • B → D

Step 1: Ensure the Relation is in 1NF

The relation contains only atomic values. Therefore, R is in 1NF.

Step 2: Find All Candidate Keys with Prime Attributes and Non-prime Attributes

  • Candidate Key: AB
  • Prime Attributes: A, B
  • Non-prime Attributes: C, D, E

Step 3: Identify Partial Functional Dependencies

Check each functional dependency.

FD 1: AB → CDE

  • AB is the complete candidate key. So, there is no partial functional dependency in the first FD

FD 2: A → C

  • A is a proper subset of the candidate key AB, and C is a non-prime attribute. Therefore, A → C is a partial functional dependency.

FD 3: B → D

  • B is a proper subset of the candidate key AB.
  • D is a non-prime attribute.

Therefore, B → D is also a partial functional dependency.

Step 4: Create a New Relation for Each Partial Dependency

  • From A → C, create: R₁(A, C) where, Primary Key, A
  • From B → D, create: R₂(B, D), where  Primary Key, B

Step 5: Remove the Dependent Attributes from the Original Relation

Remove C and D from the original relation because they have been moved to new relations. The remaining relation is: R₃(A, B, E) where Primary Key: AB

Step 6:  Final Relations

The original relation R(A, B, C, D, E) has been successfully decomposed into 2NF by removing the partial functional dependencies A → C and B → D. Final 2NF Decomposition is given below

Relation Primary Key Attributes
R₁ A A, C
R₂ B B, D
R₃ AB A, B, E

Decompose a Relation into 2NF – Example 02

Given Relation: R(A, B, C, D, E, F) with the following Functional Dependencies (FDs):

  • AB → C
  • C → D
  • B → AE

Step 1: Ensure the Relation is in 1NF

The relation contains only atomic values. Therefore, R is in 1NF.

Step 2: Find All Candidate Keys with Prime Attributes and Non-prime Attributes

  • Candidate Key: BF
  • Prime Attributes: B, F
  • Non-prime Attributes: A, C, D, E

Step 3: Identify Partial Functional Dependencies

Check each functional dependency.

FD 1: AB → C

  • AB is not a proper subset of the candidate key BF. Therefore, AB → C is not a partial functional dependency.

FD 2: C → D

  • C is not a proper subset of the candidate key BF. Therefore, C → D is not a partial functional dependency.

FD 3: B → AE

  • B is a proper subset of the candidate key BF, and A and E are non-prime attributes. Therefore, B → AE is a partial functional dependency.

Step 4: Create a New Relation for Each Partial Dependency

From B → AE, create: R₁(B, A, E), where Primary Key: B

Step 5: Remove the Dependent Attributes from the Original Relation

Remove A and E from the original relation because they have been moved to a new relation.

The remaining relation is:

R₂(B, C, D, F), where Primary Key: BF

Step 6: Final Relations

The original relation R(A, B, C, D, E, F) has been successfully decomposed into 2NF by removing the partial functional dependency B → AE.

The remaining relation still contains the functional dependencies:

  • AB → C
  • C → D

These are not partial functional dependencies, so they are not removed during 2NF decomposition.

Final 2NF Decomposition

Relation Primary Key Attributes
R₁ B B, A, E
R₂ BF B, C, D, F

Decompose a Relation into 2NF – Example 03

Given Relation is R(A, B, C, D, E) with the following Functional Dependencies (FDs)

  • AB → C
  • D → E

Step 1: Ensure the Relation is in 1NF

The relation contains only atomic values. Therefore, R is in 1NF.

Step 2: Find All Candidate Keys with Prime Attributes and Non-prime Attributes

  • Candidate Key: ABD
  • Prime Attributes: A, B, D
  • Non-prime Attributes: C, E

Step 3: Identify Partial Functional Dependencies

Check each functional dependency.

FD 1: AB → C

  • AB is a proper subset of the candidate key ABD. C is a non-prime attribute. Therefore, AB → C is a partial functional dependency.

FD 2: D → E

  • D is a proper subset of the candidate key ABD. E is a non-prime attribute. Therefore, D → E is also a partial functional dependency.

Step 4: Create a New Relation for Each Partial Dependency

  • From AB → C, create: R₁(A, B, C) where Primary Key: AB
  • From D → E, create: R₂(D, E) where Primary Key: D

Step 5: Remove the Dependent Attributes from the Original Relation

  • Remove C and E from the original relation because they have been moved to new relations. The remaining relation is: R₃(A, B, D) where Primary Key: ABD

Step 6: Final Relations

The original relation R(A, B, C, D, E) has been successfully decomposed into 2NF by removing the partial functional dependencies AB → C and D → E. Final 2NF Decomposition is given below.

Relation Primary Key Attributes
R₁ AB A, B, C
R₂ D D, E
R₃ ABD A, B, D

Decompose a Relation into 2NF – Example 04

Given Relation is R(A, B, C, D, E) with the following Functional Dependencies (FDs)

  • A → B
  • B → E
  • C → D

Step 1: Ensure the Relation is in 1NF

The relation contains only atomic values. Therefore, R is in 1NF.

Step 2: Find All Candidate Keys with Prime Attributes and Non-prime Attributes

  • Candidate Key: AC
  • Prime Attributes: A, C
  • Non-prime Attributes: B, D, E

Step 3: Identify Partial Functional Dependencies

Check each functional dependency.

FD 1: A → B

  • A is a proper subset of the candidate key AC, and B is a non-prime attribute. Therefore, A → B is a partial functional dependency.

FD 2: B → E

  • B is not a subset of the candidate key AC. Therefore, B → E is not a partial functional dependency. It is a transitive functional dependency.

FD 3: C → D

  • C is a proper subset of the candidate key AC, and D is a non-prime attribute. Therefore, C → D is also a partial functional dependency.

Step 4: Create a New Relation for Each Partial Dependency

  • From A → B, create: R₁(A, B) where, Primary Key: A
  • From C → D, create: R₂(C, D) where, Primary Key: C

Step 5: Remove the Dependent Attributes from the Original Relation

Remove B and D from the original relation because they have been moved to new relations. The remaining relation is: R₃(A, C, E) where Primary Key: AC

Step 6: Final Relations

The original relation R(A, B, C, D, E) has been successfully decomposed into 2NF by removing the partial functional dependencies A → B and C → D. The functional dependency B → E is not removed in this step because it is a transitive functional dependency, which is handled in Third Normal Form (3NF).

The final 2NF Decomposition is given below.

Relation Primary Key Attributes
R₁ A A, B
R₂ C C, D
R₃ AC A, C, E

Decompose a Relation into 2NF – Example 05

Given Relation R(A, B, C, D, E, F, G, H, I, J) with the following Functional Dependencies (FDs)

  • AB → C
  • AD → GH
  • BD → EF
  • A → I
  • H → J

Step 1: Ensure the Relation is in 1NF

The relation contains only atomic values. Therefore, R is in 1NF.

Step 2: Find All Candidate Keys with Prime Attributes and Non-prime Attributes

  • Candidate Key: ABD
  • Prime Attributes: A, B, D
  • Non-prime Attributes: C, E, F, G, H, I, J

Step 3: Identify Partial Functional Dependencies

Check each functional dependency.

FD 1: AB → C

  • AB is a proper subset of the candidate key ABD, and C is a non-prime attribute. Therefore, AB → C is a partial functional dependency.

FD 2: AD → GH

  • AD is a proper subset of the candidate key ABD, and G and H are non-prime attributes. Therefore, AD → GH is a partial functional dependency.

FD 3: BD → EF

  • BD is a proper subset of the candidate key ABD, and E and F are non-prime attributes. Therefore, BD → EF is also a partial functional dependency.

FD 4: A → I

  • A is a proper subset of the candidate key ABD, and I is a non-prime attribute. Therefore, A → I is also a partial functional dependency.

FD 5: H → J

  • H is not a subset of the candidate key ABD. Therefore, H → J is not a partial functional dependency. It is a transitive functional dependency, which will be removed during 3NF decomposition, not 2NF.

Step 4: Create a New Relation for Each Partial Dependency

  • From AB → C, create: R₁(A, B, C), where Primary Key: AB
  • From AD → GH, create: R₂(A, D, G, H), where Primary Key: AD
  • From BD → EF, create: R₃(B, D, E, F), where Primary Key: BD
  • From A → I, create: R₄(A, I), where Primary Key: A

Step 5: Remove the Dependent Attributes from the Original Relation

Remove C, E, F, G, H, and I from the original relation because they have been moved to new relations. Do not remove J, because it is not part of a partial functional dependency. It depends on H, not directly on a subset of the candidate key.

The remaining relation is: R₅(A, B, D, J) where Primary Key: ABD

Step 6: Final Relations

The original relation R(A, B, C, D, E, F, G, H, I, J) has been successfully decomposed into 2NF by removing the partial functional dependencies AB → C, AD → GH, BD → EF, and A → I.

The dependency H → J remains because it is a transitive functional dependency, which will be removed when converting the relation into 3NF.

Final 2NF Decomposition

Relation Primary Key Attributes
R₁ AB A, B, C
R₂ AD A, D, G, H
R₃ BD B, D, E, F
R₄ A A, I
R₅ ABD A, B, D, J

How 2NF Removes Redundancy (with Example)

Redundancy means storing the same information multiple times in a table. This wastes storage and can lead to update, insertion, and deletion anomalies.

Second Normal Form (2NF) removes redundancy caused by partial functional dependencies.

Example

Suppose we have the following relation:

R(StudentID, CourseID, StudentName, CourseName, Instructor)

Functional Dependencies:

  • (StudentID, CourseID) → StudentName, CourseName, Instructor
  • StudentID → StudentName
  • CourseID → CourseName, Instructor

Step 1: Find the Candidate Key

The candidate key is:

{StudentID, CourseID}

  • Prime attributes: StudentID, CourseID
  • Non-prime attributes: StudentName, CourseName, Instructor

Step 2: Identify Partial Dependencies

There are two partial dependencies:

  • StudentID → StudentName
  • CourseID → CourseName, Instructor

Since non-prime attributes depend on only part of the composite key, the relation is not in 2NF.

Original Table (Contains Redundancy)

StudentID CourseID StudentName CourseName Instructor
S101 C101 Ali DBMS Ahmed
S101 C102 Ali OS Bilal
S102 C101 Sara DBMS Ahmed
S102 C103 Sara Networks Kamran

Redundant Data

Notice that:

  • Ali is repeated every time StudentID = S101.
  • Sara is repeated every time StudentID = S102.
  • DBMS and Ahmed are repeated for every student enrolled in C101.

This is called data redundancy.

Step 3: Decompose into 2NF

Student Table for the FD: StudentID → StudentName

StudentID StudentName
S101 Ali
S102 Sara

Course Table for the FD: CourseID → CourseName, Instructor

CourseID CourseName Instructor
C101 DBMS Ahmed
C102 OS Bilal
C103 Networks Kamran

Enrollment Table for Candidate Key: {StudentID, CourseID}

StudentID CourseID
S101 C101
S101 C102
S102 C101
S102 C103

No non-prime attributes remain, so there are no partial dependencies.

How Redundancy is Removed

Before 2NF:

If Ali enrolls in 10 courses, his name is stored 10 times.

StudentID CourseID StudentName
S101 C101 Ali
S101 C102 Ali
S101 C103 Ali
… … Ali

Similarly, the course DBMS and instructor Ahmed are repeated for every student taking that course.

After 2NF

Student Table: Student information is stored only once.

StudentID StudentName
S101 Ali
S102 Sara

Course Table: Course information is stored only once.

CourseID CourseName Instructor
C101 DBMS Ahmed
C102 OS Bilal
C103 Networks Kamran

Enrollment only stores the relationship.

StudentID CourseID
S101 C101
S101 C102
S102 C101
S102 C103

Benefits of Removing Redundancy in 2NF

  • Less storage space: Duplicate data is eliminated.
  • Easy updates: Changing a student’s name or course instructor requires updating only one row.
  • No update anomaly: Data remains consistent after updates.
  • No insertion anomaly: New students or new courses can be added independently.
  • No deletion anomaly: Deleting an enrollment does not accidentally remove student or course information.

Key Idea

2NF removes redundancy by moving attributes that depend on only part of a composite candidate key into separate tables. As a result, each piece of information is stored only once, reducing duplication and maintaining data consistency.