3NF in DBMS

Third Normal Form (3NF) is a further step in database normalization that ensures data is stored efficiently by eliminating transitive dependencies. A table is in 3NF if it satisfies the following conditions:

• It is in 2NF: The table must first meet all the requirements of Second Normal Form (2NF).

• No Transitive Dependency: There should be no transitive dependency, which means a non-prime attribute should not depend on another non-prime attribute.

Conditions for a Relation to be in 3NF

A relation is in Third Normal Form (3NF) if it satisfies the following conditions:

Condition 01: The relation must already be in 1NF and 2NF.

Condition 02: For Every Functional Dependency (X → Y), at Least One of the Following Must Be True

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

Example 1: Checking if a Relation is in 3NF

Consider the relation R (ABCD) which is already in 2NF with the functional dependencies (FDs):

• FD = {AB → C, C → D}

Step-by-Step Solution

• Candidate Key (CK): {AB}
• Prime Attributes: {A, B} (attributes that are part of the candidate key).
• Non-Prime Attributes: {C, D} (attributes that are not part of the candidate key).

Analyzing the Functional Dependencies

• FD 1: AB → C

  • LHS (AB) is a candidate key (valid for 3NF).
  • This functional dependency satisfies the condition for 3NF.

• FD 2: C → D

  • LHS (C) is not a candidate key, but the RHS (D) is a non-prime attribute.
  • Since D is a non-prime attribute, this FD violates 3NF.

Result:

Since FD 2 does not satisfy the condition for 3NF, the relation is not in 3NF.

Example 2: Checking if Another Relation is in 3NF

Consider the relation R (ABCD) which is already in 2NF with the functional dependencies (FDs):

• FD = {AB → CD, D → A}

Step-by-Step Solution:

• Candidate Keys (CK): {AB, DB} (candidate keys).
• Prime Attributes: {A, B, D} (attributes that are part of the candidate key).
• Non-Prime Attribute: {C} (attributes that are not part of the candidate key).

Analyzing the Functional Dependencies:

1. FD 1: AB → CD

• • LHS (AB) is a candidate key (valid for 3NF).
  • This functional dependency satisfies the condition for 3NF.

1. FD 2: D → A

• • LHS (D) is not a candidate key, but RHS (A) is a prime attribute (since A is part of the candidate key).
  • This functional dependency satisfies the condition for 3NF.

Result:

Since all the functional dependencies in the relation meet the conditions for 3NF, this relation is in 3NF.

When is a Table in 3NF?

• The table must satisfy 2NF (i.e., it must be in 1NF and 2NF).
• There should be no transitive dependency. Every non-key attribute must depend directly on the primary key.

Example: A Table in 2NF but Not in 3NF

Consider the following relation:

Student Relation

Functional Dependencies (FDs)

• StudentID → StudentName, DepartmentID
• DepartmentID → DepartmentName

Step 1: Find the Candidate Key

Candidate Key = StudentID

• Prime Attribute = StudentID
• Non-prime Attributes = StudentName, DepartmentID, DepartmentName

Step 2: Check for 2NF

A relation is in 2NF if:

• It is in 1NF.
• There is no partial dependency.

Since the candidate key (StudentID) consists of only one attribute, partial dependency is impossible.

Therefore, the relation is in 2NF.

Step 3: Check for 3NF

Given FDs:

• StudentID → StudentName, DepartmentID
• DepartmentID → DepartmentName

Consider the FD:

DepartmentID → DepartmentName

• LHS (DepartmentID) is not a super key.
• RHS (DepartmentName) is not a prime attribute.

This violates the formal definition of 3NF.

Also,

StudentID → DepartmentID → DepartmentName

So, DepartmentName depends on another non-prime attribute (DepartmentID) instead of directly on the candidate key.

This is a transitive dependency.

Therefore, the relation is NOT in 3NF.

Converting the Relation into 3NF

To remove the transitive dependency, decompose the relation into two tables.

Table 1: Student

Functional Dependency:

• StudentID → StudentName, DepartmentID

Candidate Key = StudentID

This table satisfies 3NF because the determinant is a candidate key.

Table 2: Department

Functional Dependency:

• DepartmentID → DepartmentName

Candidate Key = DepartmentID

This table also satisfies 3NF because the determinant is a candidate key.

Verification

Student Table

FD:

• StudentID → StudentName, DepartmentID
• LHS is a candidate key.

Therefore, the Student table is in 3NF.

Department Table

FD:

• DepartmentID → DepartmentName
• LHS is a candidate key.

Therefore, the Department table is also in 3NF.

Benefits of Third Normal Form (3NF)

Third Normal Form (3NF) improves database design by removing transitive dependencies. This makes the database more efficient, accurate, and easier to maintain.

Conclusion

Third Normal Form (3NF) is an important step in database normalization, as it removes transitive dependencies, ensuring that non-prime attributes depend only on the primary key. This leads to reduced data redundancy, improved data integrity, and better performance for updates and queries.

• A table is in 3NF if:

  1. It is in 2NF.

  2. There are no transitive dependencies.

By converting to 3NF, we ensure that the database is logically organized and optimized for data consistency and query efficiency.