Second Normal Form in DBMS

Introduction

Have you heard something like normalization? Do you know about second normal forms or 2nf? If not, then don’t worry. Board Inifinity got your back.

In this article, we will cover the 2nf or second normal form in normalization. We will discuss how this comes into the picture when we talk about normalization. So basically, specific guidelines for database management system design have been devised to structure tables more effectively and reduce anomalies. A table's normal form is the state in which it is set up (or a stage of normalization). The first normal form (or 1NF), second normal form (or 2NF), and third normal form are the three stages of normal forms (or 3NF). Let us understand what 2nf is.

2NF: What is It?

The foundation of Second Normal Form (2NF) is the idea of full functional dependency. Relationships with composite keys, or those having a primary key made up of two or more qualities, fall under the second normal form. Relationships with single-attribute main keys are always in at least 2NF. The update anomalies may affect a relation that is not a 2NF relation.

A relation must be in the 1NF(first normal form) to be in the second normal form, and it cannot have any partial dependencies. Suppose there is no partial dependence between any non-prime attribute (i.e., characteristics that are not included in any candidate key) and any appropriate subset of the table’s candidate key. In that case, the relation is in the 2NF state.

On the other hand, when a relation is in First Normal Form, and every attribute other than the primary key is completely dependent on the primary key, the relation is in the Second Normal Form (2NF).

A partial dependency exists when the correct subset of a candidate key determines a non-prime attribute.

Removal of partial dependencies is required for 1NF relations to be normalized to 2NF. If there is a partial dependency, we move the partially dependent attribute(s) to a new relation along with a duplicate of their determinant and remove them from the relation.

Let us understand 2NF with the help of an example.

Example

Consider the student table, which is mentioned below:

Note that, many courses have the same course name.

Here,

Course_Name cannot alone decide the value of Course_ID or Student_ID;

Course_Name together with Student_ID cannot decide the value of Course_ID;

Course_Name together with Course_ID cannot decide the value of Student_ID;

Hence,

Course_Name that would be a non-prime attribute, because it belongs to only one candidate key {Student_ID, Course_ID};

However, Course_ID -> Course_Name, or that Course_Name is reliant on Course_ID, a suitable subset of the candidate key as a result of the partial dependency between the non-prime attribute Course_Name and a proper subset of the candidate key, this relation is not in 2NF.

To convert the table into the second normal form we need to split the two tables as like mentioned below:

Table 1: Student_ID, Course_ID

Table 2: Course_ID, Course_Name

Table 1

Table 2

2NF aims to lessen the amount of redundant data that is stored in memory. For instance, if 100 students are enrolled in Co1, we do not need to record the Course_Name as Science for each of the 100 records; instead, we can record the information in the second table as Science for Co1.

Conclusion

In this article, we have discussed 2NF, or the second normal form in normalization. We discussed what it is and why we need it with the help of an example of a student table.