3 Normal Form

The Third Normal Form (3NF) is a concept in database normalization that builds upon the First Normal Form (1NF) and the Second Normal Form (2NF). It addresses issues related to transitive dependencies in a relational database. To be in 3NF, a table must satisfy the following conditions:

1. It must already be in 2NF, which means it contains only atomic values (1NF) and eliminates partial dependencies.

2. It should not have transitive dependencies, which occur when non-key attributes (attributes not part of the primary key) depend on other non-key attributes.

In simpler terms, 3NF eliminates redundancy and ensures that all non-key attributes are functionally dependent only on the primary key, not on other non-key attributes.

Here's an example to illustrate 3NF:

Consider a table called Employees with the following attributes:

EmployeeID	EmployeeName	Department	DepartmentManager
1	Alice	HR	Bob
2	Bob	IT	Carol
3	Carol	HR	David

In this table:

• EmployeeID is the primary key.

• EmployeeName is functionally dependent on EmployeeID.

• Department is functionally dependent on EmployeeID.

• DepartmentManager is functionally dependent on EmployeeName (indirectly via Department).

The issue here is a transitive dependency: DepartmentManager depends on EmployeeName, which, in turn, depends on EmployeeID. In 3NF, non-key attributes should depend solely on the primary key, and not transitively through other non-key attributes.

To bring this table into 3NF, you should split it into three separate tables: one for employees, one for departments, and one for department managers. Here's how it might look in 3NF:

Table: Employees

EmployeeID	EmployeeName	DepartmentID
1	Alice	1
2	Bob	2
3	Carol	1

Table: Departments

DepartmentID	Department
1	HR
2	IT

Table: DepartmentManagers

DepartmentID	ManagerID
1	2
2	3

In this revised structure, each table adheres to 3NF. Non-key attributes, such as EmployeeName and DepartmentManager, depend only on the primary keys (EmployeeID, DepartmentID, and ManagerID), eliminating transitive dependencies and reducing data redundancy. This design makes the database more efficient, easier to maintain, and less prone to update anomalies.