SQL- Normalization: Third Normal Form (3NF)

Database normalization is a technical term to improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency. This post is based on the Second Normal Form. Please visit the Second Normal Form of the Database Normalization at Database Normalization - Second Normal Form.

Database Normalization- Third Normal Form (3NF)
A database table is said to be in 3NF if it is in 2NF and all non- keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.

The process of converting the table into 3NF is as follows:

1.       Remove the transitive dependencies (A type of functional dependency where a field is functionally dependent on the Field that is not the primary key. Hence its value is determined, indirectly by the primary key).

2.      Make separate table for transitive dependent Field.

What is Transitive?

When something is transitive, then a meaning or relationship is the same in the middle as it is across the whole.  If it helps think of the prefix trans as meaning “across.”  When something is transitive, then if something applies from the beginning to the end, it also applies from the middle to the end.

Dependence

An object has a dependence on another object when it relies upon it. In the case of databases, when we say that a column has a dependence on another column, we mean that the value can be derived from the other.  For example, product price is dependent on the product cost.

Apply the Model to 3NF Standards

Since the columns identified in red aren’t completely dependent on the table’s primary key, it stands to reason they belong elsewhere.  In both cases, we need to move these columns into the new tables.

In the case of Employee Department and Department Name, a Department Master was created.  A foreign key was then added to Employee Master so we can still describe in which Department an Employee is based.

With these changes made the data model, in second normal form, is shown below.



3NF of above 2NF tables is as follows:

Reservation Master
Project Code	Project Name	Project Manager	Project Budget
PC001	Reservation	Mike Towery	$125,000
PC002	Attendance	Chris Gray	$155,000
PC003	Human Resources	Kimmy Wang	$225,000
Primary Key= Project Code

Reservation Staff
Project Code	Emp Id
PC001	E0001
PC002	E0002
PC003	E0003
PC001	E0004
PC002	E0005
PC003	E0006
PC001	E0007
PC002	E0008
PC003	E0009
Composite Key  ( Unique Key) = Project Code + Emp Id

Employee Master
Emp Id	Employee Name	Dept Id	EmployeeRate
E0001	Ryan Arjun	D001	240
E0002	Tony Towery	D002	230
E0003	Lucy Gray	D003	250
E0004	Will Smith	D001	245
E0005	Chao Milk	D002	225
E0006	Chris Gyal	D003	210
E0007	Bill Gray	D001	190
E0008	Red Bill	D002	210
E0009	Tom Ramsay	D003	200
Primary Key = Emp Id

Department Master
Dept Id	Department Name
D001	Database
D002	Testing
D003	IT
Primary Key = Dept Id

Now, you can see in the above example, how does normalization work on your data table and improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency.

We have distributed a single business data table into four data tables by maintaining the relationship among them with the help of the primary and foreign key relationship.