# Second Normal Form (2NF)

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_FEE1                     C1                  10002                     C2                  15001                     C4                  20004                     C3                  10004                     C1                  10002                     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 2STUD_NO            COURSE_NO          COURSE_NO                COURSE_FEE     1                 C1                  C1                       10002                 C2                  C2                       15001                 C4                  C3                       10004                 C3                  C4                       20004                 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]`

In this case, we can see that the relation R has a composite candidate key {A,B} as AB->C. Therefore, A and B together uniquely determine the value of C. Similarly, BC -> D shows that B and C together uniquely determine the value of D.

The relation R is already in 1NF because it does not have any repeating groups or nested relations.

However, we can see that the non-prime attribute D is functionally dependent on only part of a candidate key, BC. This violates the 2NF condition.

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