The Second Normal Form (2NF)
Definition: A relation is in 2NF if it is in 1NF and each non-key attribute is fully dependent on each candidate key of the relation.
Some of the points that should be noted here are:
- A relation having a one attribute key has to be in 2NF.
- In the case of composite key, partial dependency on key that is part of the key is not allowed.
- 2NF tries to make sure that information in one relation is about one thing
- Non-key attributes are those that are not part of any candidate key.
Let us now reconsider, which describes the FDs of the relation to the relation STUDENT (Enrolmentno, Sname, Address, Cno, Cname, Instructor, Office). These FDs can also be written as:
Enrolmentno → Sname, Address (1)
Cno → Cname, Instructor (2)
Instructor → Office (3)
The key attributes of the relation are (Cno + Enrolmentno). Rest of the attributes are non-key attributes. For the 2NF decomposition, we are concerned with the FDs (1) and (2) as above as they relate to partial dependence on the key that is (Cno +Enrolmentno). As these dependencies illustrates that relation in not in 2NF and therefore suffer from all the three anomalies and redundancy troubles as many non-key attributes can be derived from partial key attribute. To change the relation into 2NF, let us use FDs. As per FD (1) the Enrolment number uniquely verify student name and address, so one relation should be:
STUDENT1 (Enrolmentno, Address, Sname)
Now as per FD (2) we can decompose the relation more, but what about the attribute 'Office'?
We find in FD (2) that Course code (Cno) attribute uniquely shows the name of instructor (refer to FD 2(a)). Also the FD (3) means that name of the instructor uniquely shows office number. This can be written as:
Cno → Instructor (2 (a)) (without Cname)
Instructor → Office (3)
⇒ Cno → Office (This is transitive dependency)
Therefore, FD (2) now can be rewritten as:
Cno → Cname, Instructor, Office (2')
This FD, now provides us the second decomposed relation:
COU_INST (Cno, Cname, Instruction, Office)
Therefore, the relation STUDENT has been decomposed into two relations:
STUDENT1 (Enrolmentno, Sname, Address) COU_INST (Cno, Cname, Instruction, Office)
Is the decomposition into 2NF finish now?
No, how would you join the two relations formed above any way? Please note we have super FDs as, because (Cno +Enrolmentno) is the primary key of the relation STUDENT:
Enrolmentno, Cno → ALL ATTRIBUTES
Every attributes except for the key attributes that are Cno and Enrolmentno, Though, are covered on the right side of the FDs (1) (2) and (3), therefore, making the FD as redundant. But in any situation we have to have a relation that connects the two decomposed relations. This relation would cover any attributes of Super FD that have not been covered by the key attributes and the decomposition. Therefore, we need to make a joining relation as:
COURSE_STUDENT (Enrolmentno, Cno)
So, the relation STUDENT in 2NF form would be:
STUDENT1 (Enrolmentno, Sname, Address) 2NF(a)
COU_INST (Cno, Cname, Instruction, Office) 2NF(b)
COURSE_STUDENT (Enrolmentno, Cno) 2NF(c)