Second Normal Form (2NF)

• In the 2NF, relational must be in 1NF.
• In the second normal form, all non-key attributes are fully functional dependent on the primary key

Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject.

TEACHER table

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_SUBJECT table:

Anomalies in Second Normal Form

Even if the relation in 2NF, it still suffers from insertion, deletion and updation anomalies. So before discussing the third normal form, we will explain these anomalies.

To discuss the various anomalies, we will consider the STUDENT relation that holds information about students and teachers.

In the above table, Stu_Id is the primary key which acts as the roll number of the student.

Since the STUDENT relation is composed of only one attribute which acts as a primary key (Stu_Id) so it is in 2NF. But it suffers from the insertion, deletion and updation anomalies which are explained as follows.

Insertion anomaly: Suppose that we want to insert a a new record with some information about a new teacher who has not yet been assigned a personal student. But this insertion record is not allowed because the primary key Stu_Id contains a nullvalue which is not possible as it is against the entity integrity rule.

For Example: Suppose that we want to insert information about a new teacher ‘Mayank’ having Teach_Id = ‘206’ Teach_Qual = ‘MCA‘who has not yet been allotted any student. This is not possible as Stu_Id will contain a null value.

Deletion anomaly: Suppose that a student whose Stu_Id = 1768 decides to leave the college, so we would have to delete this tuple from the STUDENT relation.

As we can see from the relation that this particular student is the last student of the teacher whose Teach_Id = ‘205’. Thus on deleting this tuple, the information about the teacher would also be deleted. This may lead to vital information. This is the deletion anomaly.

There would be no deletion problem if the student who decides to leave the college is not the last student of the particular teacher.

For Example: Deleting student record with Stu_Id = 2523 will not lead to deletion of teacher information whose Teach_Id = ‘201’ because it is present elsewhere.

Updation Anomaly: The second normal form also suffers from updation anomaly.

For Example: The value of the qualifications of the teacher i.e. Teach_Qual whose Teach_Id = ‘204’ is updated from MCA to Ph.D. This would be quite a big problem as the updation in the tuple will have to be made where ever this information reoccurs. Although this relation is having few tuples so it would be quite a big problem here but normally a teacher, teaches many students. So in case of huge databases it will be a big problem and may lead to inconsistencies as human are prone to errors.

The above considerations leads us to a conclusion that relation in 2NF have undesirable data manipulation properties hence bringing a relation to 2NF would not terminate logical database design. Further transformations are needed to eliminate these kinds of anomalies from an original relation. So this brings us to a concept of the Third normal form.