Explain about 2NF with an example in DBMS

In the 2NF, relation must be in 1NF. In the second normal form, all the non-key attributes are fully functional dependent on the primary key. Or a relation is in 2NF, when it is in 1NF and there is no partial dependency.

Example

Consider a relation R (rollno, name, subjectcode, subjectname, duration)

F: {rollno -> name, subjectcode -> (subjectname, duration).

{rollno, subjectcode}+ = {rollno, name, subjectcode, subjectname, duration}

=> {rollno, subjectcode } is candidate key.

The above table is in 1NF because no multivalued attributes are present. But it is not in 2NF because the following two partial dependencies are present.

Rollno->name is a partial dependency {since name depend on apart of key}

Subjectcode->{subjectname, duration} {since subjectname and duration depend on a part of key}

Anomalies

The table also suffers with three anomalies, which are as follows −

• Insertion anomaly − We cannot insert a new course such as 'PHP' to the table unless we have a student who has to take the subject.

• Updation anomaly − If we change the subject from C to C++ we have to make changes in more than one place otherwise the table will be inconsistent.

• Deletion anomaly − If Rakhi is deleted from the table we also lose information we had on the "database" subject.

To convert the given table to 2NF we need to decompose the table first.

Decomposition for 2NF − To overcome these anomalies table R should be divided to smaller tables.

If partial dependency is X->Y then divide R into R1(X+) and R2(R-Y+)

Rollno -> rollno+ = {rollno, name}

R2 =R-name+ = {rollno, subjectcode, subjectname, duration} 

Subjectcode -> {subjectname, duration} is a partial dependency [since subjectname and duration depend on part of the key].

So divide R2 table onto R3 and R4 as −

R3=subjectcode+= {subjectcode, subjectname, duration}

R4= R2- {subjectname, duration}+ = {rollno, subjectcode}.

It is represented diagrammatically as shown below −

R1

R3

R4

The above three tables are free from all anomalies. Let's see clearly why there are no anomalies.

• No insertion anomaly: Now a new course "php" can be inserted to the subject table without any student information.

• No deletion anomaly: If we delete the rakhi record from both R1 and R4 then it does not have any effect. Because "database" is untouched in the subject table.

• No updation anomaly: To change any subject only one change is needed in the subject table.