3-NF form in DBMS

3-NF form in DBMS

 

In this article, we will learn about the 3-NF form in DBMS.

Normalization is a process of making a table optimized by eliminating problems caused due to insertion updates and deletion.

Definition:

A table is said to be in Third Normal Form if it satisfies the following properties

  1. If it is in second normal form and
  2. There is no transitive dependency.

 

Learn more about Normalization here on this page.

3-NF form in DBMS

What is a transitive dependency?

When non-prime attribute depends on another non-prime attribute rather than depending upon the prime attributes or primary key

Employee table

Emp-idNameProject-idDate-Completion
64Arun10120-4-21
65Sindhu10230-4-21
66Trishaank10120-4-21
67Vamshi10310-2-21

 

  • In the employee table, we have dependency as shown
    Project id -> date completion
  • Project id rather than depending on the primary key of the table employee ID it is depending on a non-prime attribute date completion

The table is in First Normal Form because it does not have any multivalued attributes
The table is in the second form because there is no composite and no chances of any partial dependency.

But still, the stables office from the insert, update and delete anomalies

  • Insert anomaly: Project ID cannot be assigned until an employee is assigned to it
  • Update anomaly: Updating the values of date completion requires multiple updates because it is stored redundantly table
  •  Delete anomaly: The deletion of employee results in the deletion of entire project data even if there is even a single employee in that project the tables suffers from
  • Even if the table is in second Normal Form it suffers from all the anomalies because of the presence of a transitive dependency as shown

Note: Table with any kind anomaly will have a transitive dependency.

 

How to eliminate transitive dependency?

Solution steps

  •  Transitive dependency is converted into a separate  table
  • The determinant of each transitive dependency becomes a primary key attribute of the corresponding table
  • Add primary key of each table resulted from each transitive dependency as the foreign key in an original table along with all remaining attributes

we have a transitive dependency [ project id -> date completion ]

  • Break the original employee table in two tables where one table project consists of transitive dependency second table employees consistently meaning attributes
  • In a newly formed projects table would contain project ID, date completion and determinant project ID would serve as a primary key attribute of this newly formed table

Project table

Project-idDate-Completion
 10120-4-21
10230-4-21
10310-2-21

 

Employee table 

Emp-idNameProject-id
64Arun101
65Sindhu102
66Trishaank101
67Vamshi103

 

  • In the newly formed employees, table project id is derived as a foreign key attribute from the original employee table along with the remaining attributes.

 

Now compare this newly formed  tables the previous problem table

  • Project ID could be added without the employee information
  • Deletion of employee data would not result in the deletion of project data in the project table, updating the values of project-completion would not result in multiple updates because it is free from redundancy