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:

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

2. No Transitive Dependency: There should be no transitive dependency, which means non-key attributes should not depend on other non-key attributes.

Key Concepts of 3NF

1. 2NF Compliance: The table must first be in 2NF, which means it must meet the 1NF and 2NF criteria, with no partial dependencies.

2. No Transitive Dependency: A transitive dependency occurs when a non-key attribute depends on another non-key attribute. In 3NF, we remove such transitive dependencies. Specifically, non-prime attributes (attributes not part of the candidate key) should depend directly on the primary key and not on other non-prime attributes.

What is a Transitive Dependency?

A transitive dependency occurs when:

• A non-prime attribute A depends on another non-prime attribute B, and B depends on the primary key C.

• In other words, A indirectly depends on C through B.

Example of a Transitive Dependency

Let’s consider the following table Employee:

Functional Dependencies:

• EmployeeID → EmployeeName: An employee’s ID determines their name.

• DepartmentID → DepartmentName: A department ID determines the department name.

• DepartmentID → Manager: A department ID determines the manager.

Transitive Dependency:

• EmployeeID → DepartmentID → DepartmentName, so EmployeeID → DepartmentName through the DepartmentID.

• EmployeeID → DepartmentID → Manager, so EmployeeID → Manager through DepartmentID.

In this table, DepartmentName and Manager depend on DepartmentID, and DepartmentID depends on EmployeeID. Therefore, DepartmentName and Manager are transitively dependent on EmployeeID.

To make this table 3NF, we need to remove the transitive dependency.

Converting to 3NF

To convert the table to 3NF, we decompose it into smaller tables by removing the transitive dependencies:

1. Employee Table: This table will store employee details without the department-related columns.

   EmployeeID	EmployeeName	DepartmentID
   101	John	D01
   102	Jane	D02
   103	Jake	D01

2. Department Table: This table will store department details, including the manager and department name.

   DepartmentID	DepartmentName	Manager
   D01	HR	Alice
   D02	IT	Bob

Now, in these two tables:

• There is no transitive dependency: EmployeeID only determines EmployeeName and DepartmentID, while DepartmentID determines DepartmentName and Manager in the Department table.

• The non-key attributes in both tables depend directly on the primary key, not on other non-key attributes.

Note: both tables are joined using “DepartmentID”

Conditions for a Relation to be in 3NF

A relation is in 3NF if, for each functional dependency (FD) X → Y, the following conditions are met:

1. The LHS (left-hand side) of the functional dependency X → Y must either be a candidate key or a super key.

2. The RHS (right-hand side) of the functional dependency X → Y must be a prime attribute (i.e., part of the candidate key).

Example 1: Checking if a Relation is in 3NF

Consider the following relation R (ABCD) with the functional dependencies (FDs):

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

Step-by-Step Solution:

1. Candidate Key (CK): {AB}

2. Prime Attributes: {A, B} (attributes that are part of the candidate key).

3. Non-Prime Attributes: {C, D} (attributes that are not part of the candidate key).

Analyzing the Functional Dependencies:

1. FD 1: AB → C

   • LHS (AB) is a candidate key (valid for 3NF).

   • This functional dependency satisfies the condition for 3NF.

2. 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.

Conclusion:

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 following relation R (ABCD) with the functional dependencies (FDs):

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

Step-by-Step Solution:

1. Candidate Keys (CK): {AB, DB} (candidate keys).

2. Prime Attributes: {A, B, D} (attributes that are part of the candidate key).

3. 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.

2. 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.

Conclusion:

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

When is a Table in 3NF?

1. The table must satisfy 2NF (i.e., it must be in 1NF and 2NF).

2. There should be no transitive dependency. Every non-key attribute must depend directly on the primary key.

Benefits of 3NF

1. Reduces Redundancy: By eliminating transitive dependencies, 3NF reduces data duplication and storage space.

2. Improves Data Integrity: In 3NF, updates are easier and less error-prone since non-key attributes are not dependent on other non-key attributes.

3. Efficient Data Updates: With fewer redundancies and more direct dependencies, updating data becomes more efficient and avoids anomalies.

4. Simplifies Queries: The absence of unnecessary data and transitive dependencies results in cleaner and more straightforward queries.

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.