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.
A table is said to be in Third Normal Form if it satisfies the following properties
- If it is in
second normal formand
- There is no
Learn more about Normalization here on this page.
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
- 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?
- 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
- 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