Normalization | 3 NF

The Third Normal Form (3NF) in database normalization is a condition that ensures data integrity and eliminates redundancy by organizing tables in a way that every non-key column is directly dependent only on the primary key.

For a table to be in 3NF, it must satisfy the following conditions:

It must be in Second Normal Form (2NF) (meaning it is in 1NF and there are no partial dependencies of non-prime attributes on a composite primary key).
There should be no transitive dependencies. This means that non-key columns should not depend on other non-key columns.

Transitive Dependency Explanation:

A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key. In simple terms, a transitive dependency is an indirect relationship between the primary key and non-key attributes.

Sure! Here's a simpler analogy for understanding transitive dependency:

Imagine you have a table of employees:

Primary Key: EmployeeID
Non-key attributes: EmployeeName, OfficeLocation, OfficePhoneNumber

Transitive Dependency:

In this table:

EmployeeIDdetermines OfficeLocation(e.g., Employee ID tells us where the employee is located).
OfficeLocationdetermines OfficePhoneNumber(e.g., knowing the office location, you can find the office phone number).
Here, OfficePhoneNumberdepends on OfficeLocation, and OfficeLocationdepends on EmployeeID.
This creates an indirect (transitive) dependency of OfficePhoneNumber on EmployeeID through OfficeLocation.

Simpler Analogy:

Think of it like a chain: if A → B (A determines B) and B → C (B determines C), then A indirectly determines C. That’s a transitive dependency.

To fix it, we separate the data into two tables:

Employee Table:

EmployeeID (PK) EmployeeName    OfficeLocationID (FK)
1   Alice   1
2   Bob 2

Office Table:

OfficeLocationID (PK)   OfficeLocation  OfficePhoneNumber
1   New York    123-456-7890
2   San Francisco   987-654-3210

Now, the transitive dependency is removed, and the database is better structured!