An entity is in the second normal form if all of its attributes depend on the whole (primary) key. In relational terms every column in a table must be functionally dependent on the whole primary key of that table. Functional dependency indicates that a link exists between the values in two different columns.
If the value of an attribute depends on a column the value of the attribute must change if the value in the column changes. The attribute is a function of the column. The following explanations make this more specific:
If the table has a one-column primary key the attribute must depend on that key. If the table has a composite primary key the attribute must depend on the values in all its columns taken as a whole not on one or some of them. If the attribute also depends on other columns they must be columns of a candidate key; that is columns that are unique in every row. If you do not convert your model to the second normal form you risk data redundancy and difficulty in changing data. To convert first-normal-form tables to second-normal-form tables remove columns that are not dependent on the primary key.
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.
