2NF-Second Normal Form

Understanding Partial Dependency

Take the example of an employee table <employeeData> with the following columns: empID, fieldID, empName and fieldName. 

• As we read about Candidate Key just above, we know that empID and fieldID together form the candidate key in the given relation.
• In the following table, the prime key attributes will be empID and fieldID. The non-prime attributes empName and fieldName will be partially dependent if they functionally depend on part of a candidate key. 
• Since the empID can help us find out empName, empName becomes Partial Dependent, and the same goes for fieldName. 
• The <employeeData> relation, therefore, does not follow the Second Normal Form in Normalization and will make a bad database design.

Converting to Second Normal Form (2NF)

To convert the above-shown table to Second Normal Form, we will need to decompose the table and remove Partial Dependency:

<empData>

<empNames>

<fieldInfo>

Now our table is finally in the Second Normal Form of Database Normalization.

We can say that the normalization of 1NF relations to 2NF basically requires removing the partial dependencies. In case they exist, we must remove the partially dependent attribute(s) and place them in a new table with a copy of their determinant.

During the Second Normal Form, we are trying to reduce redundant data that is getting stored in the memory. To finally sum up 2NF:

• A relation must be in the first normal form in the DBMS before we change it to the Second Normal Form. 
• The table must not have a partial dependency, which exists when attributes depend on only a part of the primary key and not the complete composite primary key.
• We can remove partial dependency by dividing or decomposing the relation such that we remove the attribute that causes partial dependency and move it to another table.

Frequently Asked Questions

1. What is DBMS normalization?
   Ans: Normalization in databases refers to organizing data and involves creating relations and establishing links between them and keeping in mind efficiency, protection and flexibility in the relations.
    
2. What is the unnormalized form in databases?
   Ans: In database normalization, the unnormalized or non-first normal form is the simplest database model that doesn’t meet any normalization conditions. The UNF has a lot of data redundancy and is not considered very efficient.
    
3. Is the Second Normal Form considered a good database design?
   Ans: The second normal form does eliminate data redundancy, but the third normal form is normalization at its best. Almost every database designer tries to reach 3NF in database normalization.
    
4. What are the conditions for 2NF?
   Ans: For a relation to be in the second normal form, it must be in the first normal form and must not have any partial dependency on the primary key.

Key takeaways

In this article, we learned about the second normal form and how it is implemented to make tables or relations one step closer to being the most efficient. We also used examples to understand the concept better and learn to use our knowledge of the same. Keeping the two conditions for 2NF in mind, we will move on to the most efficient normalization: the Third Normal form.

You can find a lot of interesting modules on Database Management Systems at Coding Ninjas. Visit Coding Ninjas Studio to try and solve more problems for practice. Also, you can check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Also, check out - Anomalies In DBMS.

Share this blog with your friends if you found it helpful! Until then, All the best for your future endeavours, and Keep Coding.