Second Normal Form (2NF)

A relation is said to be in Second Normal Form (2NF) if:

• It is already in First Normal Form (1NF).
• Every non-key attribute is fully functionally dependent on the entire primary key.
• There are no partial dependencies.

In simple words, a non-key attribute should depend on the complete primary key, not on only a part of it.

What is Partial Dependency?

A partial dependency occurs when a non-key attribute depends on only a portion of a composite primary key instead of the whole key.

To achieve 2NF, all partial dependencies must be removed.

Example of a Table Not in 2NF

Suppose a school stores information about teachers and the subjects they teach. A teacher can teach multiple subjects.

TEACHER Table

In this table, the combination of (TEACHER_ID, SUBJECT) forms the primary key because a teacher can teach multiple subjects.

However, TEACHER_AGE depends only on TEACHER_ID and not on the complete primary key (TEACHER_ID, SUBJECT).

This is called a partial dependency, which violates the rules of 2NF.

Converting the Table into 2NF

To remove the partial dependency, the table is divided into two smaller tables.

TEACHER_DETAIL Table

TEACHER_SUBJECT Table

Why is the New Design in 2NF?

In the TEACHER_DETAIL table:

• TEACHER_AGE depends completely on TEACHER_ID.

In the TEACHER_SUBJECT table:

• SUBJECT depends on the combination of the teacher and subject record.

Since all non-key attributes now depend on their entire primary key and no partial dependency exists, both tables satisfy the requirements of Second Normal Form (2NF).

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.

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

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

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