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

• 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