Open In App

Second Normal Form (2NF)

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

First Normal Form (1NF) does not eliminate redundancy, but rather, it’s that it eliminates repeating groups. Instead of having multiple columns of the same kind of data in a record, (0NF or Unnormalized form) you remove the repeated information into a separate relation and represent them as rows. This is what constitutes 1NF. 

Second Normal Form

The second Normal Form (2NF) is based on the concept of fully functional dependency. The second Normal Form applies to relations with composite keys, that is, relations with a primary key composed of two or more attributes. A relation with a single-attribute primary key is automatically in at least 2NF. A relation that is not in 2NF may suffer from the update anomalies. To be in the second normal form, a relation must be in the first normal form and the relation must not contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes that are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. In other words,

A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).

Note – If the proper subset of the candidate key determines a non-prime attribute, it is called partial dependency. The normalization of 1NF relations to 2NF involves the removal of partial dependencies. If a partial dependency exists, we remove the partially dependent attribute(s) from the relation by placing them in a new relation along with a copy of their determinant. Consider the examples given below.

Example-1: Consider the table as follows below.

STUD_NO            COURSE_NO        COURSE_FEE
1 C1 1000
2 C2 1500
1 C4 2000
4 C3 1000
4 C1 1000
2 C5 2000

{Note that, there are many courses having the same course fee. } Here, COURSE_FEE cannot alone decide the value of COURSE_NO or STUD_NO; COURSE_FEE together with STUD_NO cannot decide the value of COURSE_NO; COURSE_FEE together with COURSE_NO cannot decide the value of STUD_NO; Hence, COURSE_FEE would be a non-prime attribute, as it does not belong to the one only candidate key {STUD_NO, COURSE_NO} ; But, COURSE_NO -> COURSE_FEE, i.e., COURSE_FEE is dependent on COURSE_NO, which is a proper subset of the candidate key. Non-prime attribute COURSE_FEE is dependent on a proper subset of the candidate key, which is a partial dependency and so this relation is not in 2NF. To convert the above relation to 2NF, we need to split the table into two tables such as : Table 1: STUD_NO, COURSE_NO Table 2: COURSE_NO, COURSE_FEE

       Table 1                                    Table 2
STUD_NO COURSE_NO COURSE_NO COURSE_FEE
1 C1 C1 1000
2 C2 C2 1500
1 C4 C3 1000
4 C3 C4 2000
4 C1 C5 2000
2 C5

Note – 2NF tries to reduce the redundant data getting stored in memory. For instance, if there are 100 students taking C1 course, we dont need to store its Fee as 1000 for all the 100 records, instead once we can store it in the second table as the course fee for C1 is 1000. 

Example-2: Consider following functional dependencies in relation  R (A,   B, C,   D )

AB -> C  [A and B together determine C]
BC -> D [B and C together determine D]

Answer:

First, we can check if there are any partial dependencies. A partial dependency occurs when a non-prime attribute (not part of any candidate key) depends on only part of a candidate key.

The candidate keys for relation R can be determined by finding the closure of each attribute:

AB determines every keys.

Now, let’s check for partial dependencies:

There are no partial dependencies in this relation because each non-prime attribute (C and D) depends on the whole candidate key(s) it is part of (AB and BC, respectively).

Therefore, the relation R is already in 3rd Normal Form (3NF) because it satisfies the conditions of 1st Normal Form (1NF) and 2nd Normal Form (2NF) and does not have any transitive dependencies.

Conclusion

In conclusion, 2NF is a fundamental concept of database normalization that helps remove partial dependencies in your relational database. Following 2NF rules helps organize your database to avoid anomalies and ensure data integrity, making it easier to store and retrieve data.


Last Updated : 07 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads