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:
-
EmployeeIDdeterminesOfficeLocation(e.g., Employee ID tells us where the employee is located). OfficeLocationdeterminesOfficePhoneNumber(e.g., knowing the office location, you can find the office phone number).Here,
OfficePhoneNumberdepends onOfficeLocation, andOfficeLocationdepends onEmployeeID.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!
Top comments (0)