Third Normal Form (3NF) in RDBMS

Third Normal Form (3NF) is a higher level of normalization in relational databases, building upon Second Normal Form (2NF). It ensures that data is organized in a way that eliminates transitive dependencies, meaning that a non-key attribute should not be transitively dependent on another non-key attribute.

What is Third Normal Form?

A table is in third normal form if it satisfies the following conditions:

1. It is in second normal form.

2. There are no transitive dependencies.

A transitive dependency occurs when a non-key attribute is dependent on another non-key attribute through a chain of dependencies.   

Understanding 3NF with Examples

Example 1: Violation of 3NF Consider a Products table:

Product ID	Product Name	Supplier ID	Supplier City
1	Laptop	101	New York
2	Smartphone	102	Los Angeles
3	Tablet	101	New York

In this example, Supplier City is transitively dependent on Supplier ID. The dependency chain is: Supplier ID -> Supplier City. This violates third normal form.

Example 2: Adherence to 3NF To normalize the Products table, we can create two separate tables:

Suppliers Table:

Supplier ID	Supplier City
101	New York
102	Los Angeles

Products Table:

Product ID	Product Name	Supplier ID
1	Laptop	101
2	Smartphone	102
3	Tablet	101

Now, both tables are in third normal form. The Supplier City attribute is directly dependent on the primary key of the Suppliers table, and there are no transitive dependencies.

Why is 3NF Important?

• Data Integrity: Adhering to 3NF helps maintain data integrity by preventing update anomalies, insertion anomalies, and deletion anomalies.

• Normalization: 3NF is a step towards achieving Boyce-Codd Normal Form (BCNF), the highest level of normalization.

• Query Performance: Normalized data can often lead to better query performance, especially for complex queries.

• Data Consistency: By eliminating transitive dependencies, 3NF helps ensure data consistency across the database.

In conclusion, Third Normal Form is an essential concept in relational database design. By understanding and applying 3NF, you can create well-structured and efficient databases that are easier to manage and maintain.