Database Reference
In-Depth Information
5.5.2 Second Normal Form (2NF): Eliminating Partial Dependencies Definition —
A
relation is in second normal form if and only if it is in first normal form and every non-key
attribute is fully functionally dependent on the primary key
In Second Normal Form (2NF) all non-key values must be fully functionally dependent on
the primary key. To move from first normal form to second normal form, remove part-key
dependencies. No partial dependencies are allowed in 2NF. A partial dependency exists
when a field is fully dependent on a part of a composite primary key. 2NF does the follow-
ing:
1. Because normalization rules are cumulative so the table must be in 1NF.
2. Each column in a table must depend on the whole key for that table. Non-key fields those
are not completely and individually dependent on the primary key, are not allowed.
3. Partial dependencies must be removed. It is of special type of functional dependency that
exists when a field is fully dependant on a part of a composite primary key.
4. Form a new table to separate the partially dependent part of the primary key and its de-
pendent fields.
2NF performs a similar task to that of 1NF, but forms a table where repeating values rather
than repeating fields are removed to a new table. The result is a many-toone relationship is
created between the original and the newly created tables. The newly created tables gets a
primary key consisting of a single field.
Let us take an example of the table named Results. We have a two-part compound key
SubjectName and StudentID. All the items are required of the key to tell us what is the
MarksObt from Results table. SubjectName has no influence on the StudentName. Stu-
dentID alone finds StudentName. We break out the determinant and dependent data-items
into their own table. This moves to a decomposition of the tables as follows:
