Second Normal Form

To grasp the second normal form, it's essential to familiarize ourselves with the concept of a composite primary key: a key formed by combining two or more attributes within a table. The goal of a composite key is to uniquely identify each row based on the combined values of multiple columns. As touched upon in the prior section, attributes comprising the composite primary key are termed key attributes.

Example

Consider the Employee-Department table. Let's say we have an Employee table with columns EmployeeID and DepartmentID. Given that each employee might work in multiple departments and each department can house numerous employees, a composite key combining both columns serves as the table's primary key.

Rules for the Second Normal Form:

• The table must meet the First Normal Form criteria;
• All non-key attributes (i.e., columns not included in the primary key) must be fully dependent on the primary key.

To comprehend the latter rule, it's pivotal to clarify the meaning of "fully dependent". A column is fully dependent on the primary key if its dependency lies with the entire primary key, not just a portion of it.

In our example, the table adheres to the first normal form with a composite key made up of EmployeeID and DepartmentID. However, it doesn't meet the second normal form: while the salary is contingent upon both the employee id and the department id, the department head's role is solely dependent on the department id and remains unaffected by the employee's identity.

The Name and Surname attributes are reliant on the EmployeeID. Hence, they should be housed in a distinct table. As such, some attributes are dependent only on a segment of the composite key.

This setup can lead to a modification anomaly: for instance, if we eliminate row 2, we inadvertently discard information about the department's head since no other rows carry this specific department id.

Normalization

Let's proceed to normalize! In the second normal form:

The updated database ensures no attributes hinge on just a segment of the composite key: the table aligns with the second normal form, guaranteeing we retain vital information even if rows from the initial table are deleted.

Note

A table in the first normal form without a composite key inherently satisfies the requirements of the second normal form.