Second Normal Form (2NF)

Second Normal Form (2NF)

The second normal form attempts to deal with the problems that are identified with the relation above that is in 1NF. The aim of second normal form is to ensure that all information in one relation is only about one thing.

A relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation.

To understand the above definition of 2NF we need to define the concept of key attributes. Each attribute of a relation that participates in at least one candidate key of is a key attribute of the relation. All other attributes are called non-key.

The concept of 2NF requires that all attributes that are not part of a candidate key be fully dependent on each candidate key. If we consider the relation

student (sno, sname, cno, cname)

and the functional dependencies

sno -> cname
cno -> cname

and assume that (sno, cno) is the only candidate key (and therefore the primary key), the relation is not in 2NF since sname and cname are not fully dependent on the key. The above relation suffers from the same anomalies and repetition of information as discussed above since sname and cname will be repeated. To resolve these difficulties we could remove those attributes from the relation that are not fully dependent on the candidate keys of the relations. Therefore we decompose the relation into the following projections of the original relation:

S1 (sno, sname)
S2 (cno, cname)
SC (sno, cno)

Use an example that leaves one relation in 2NF but not in 3NF.

We may recover the original relation by taking the natural join of the three relations.

If however we assume that sname and cname are unique and therefore we have the following candidate keys

(sno, cno)
(sno, cname)
(sname, cno)
(sname, cname)

The above relation is now in 2NF since the relation has no non-key attributes. The relation still has the same problems as before but it then does satisfy the requirements of 2NF. Higher level normalization is needed to resolve such problems with relations that are in 2NF and further normalization will result in decomposition of such relations.

Back

Contents