2NF-Second Normal Form

Reet Maggo
Last Updated: May 13, 2022

Introduction

Normalization refers to organising data within a database by creating tables, establishing relationships between them in order to protect the data and make the database more efficient and flexible. The second step in database normalization (after First Normal Form or 1NF) is the Second Normal Form or 2NF. A table can be in the Second Normal Form if it satisfies the following two conditions:

  • The table must be in 1NF or First Normal Form.
  • There must be no partial dependency, meaning all non-key attributes must fully depend on the primary key. (We will discuss partial dependency in detail in this article.)


The First Normal Form only eliminates repeating groups and not redundancy, while the Second Normal Form is all about full functional dependency and eliminates any partial dependency on primary keys. But what is partial dependency?

Let us first learn about some important terms needed to understand the topic better.

Key terms to understand for 2NF

Attribute: In relational databases, an attribute is a describing characteristic defining items under a single category in the cells of a column.

Candidate key: The candidate key can either be an attribute (column) or a group of attributes that uniquely identify every data item, i.e., all individual records in a relation or a table. We could say that the candidate key is a super key to records in the table.

Prime-attributes: The column in any candidate key will make a prime attribute.

Non-prime attributes: All other columns in a relation that do not occur in any candidate key are non-prime attributes.

Dependency: In a relation, dependency is a constraint defining a relationship between two or more attributes. Dependency occurs when data stored in the same table can be used to uniquely determine some other information stored in the table. There are various types of dependencies in relational databases including Functional Dependency, Fully-functionally Dependency, Transitive Dependency and Partial Dependency. 

For the Second Normal Form, we mainly need to learn about partial dependency, and when we study 3NF, we will learn about transitive dependency in detail.

Understanding Partial Dependency

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

empID

fieldID

empName

fieldName

E236

f08

Harvey

marketing

E109

f12

Emmy

design

E215

f29

Debby

strategy

E236

f03

Harvey

hiring

 

  • 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>

empID

fieldID

E236

f08

E109

f12

E215

f29

E187

f03

<empNames>

empID

empName

E236

Harvey

E109

Emmy

E215

Debby

<fieldInfo>

fieldID

fieldName

f08

marketing

f12

design

f29

strategy

f03

hiring

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 CodeStudio 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.

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

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think