DEV Community

mrcaption49
mrcaption49

Posted on

Normalization | 3 NF

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
Enter fullscreen mode Exit fullscreen mode

Office Table:

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

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

Top comments (0)