← prevnext →
Second Normal Form
(2NF)
o In the 2NF, relational must be in
1NF.
o 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
ADVERTISEMENT
TEACHER_I SUBJECT TEACHER_AG
D E
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
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.
ADVERTISEMENT
To convert the given table into 2NF, we decompose it
into two tables:
TEACHER_DETAIL table:
TEACHER_ID TEACHER_AGE
25 30
47 35
83 38
TEACHER_SUBJECT table:
ADVERTISEMENT
TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer
Anomalies in Second Normal
Form
ADVERTISEMENT
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.
Stu_Id Stu_Nam Teach_I Teach_Nam
e d e
2523 Anurag 201 Mohan
3712 Raju 202 Ravi
4906 Raman 203 Mahima
2716 Jyoti 204 Anjali
1768 Meetali 205 Sonia
In the above table, Stu_Id is the primary key which
acts as the roll number of the student.
ADVERTISEMENT
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.
ADVERTISEMENT
Stu_Id Stu_Nam Teach_I Teach_Nam
e d e
2523 Anurag 201 Mohan
3712 Raju 202 Ravi
4906 Raman 203 Mahima
2716 Jyoti 204 Anjali
1768 Meetali 205 Sonia
NULL NULL 206 Mayank
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.
ADVERTISEMENT
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.
ADVERTISEMENT
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.
Stu_Id Stu_Nam Teach_I Teach_Nam
e d e
3712 Raju 202 Ravi
4906 Raman 203 Mahima
2716 Jyoti 204 Anjali
1768 Meetali 205 Sonia
NULL NULL 206 Mayank
Updation Anomaly: The second normal form also
suffers from updation anomaly.
ADVERTISEMENT
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.
ADVERTISEMENT
Next TopicDBMS 3NF
← prevnext →
