Skip to content

Second normal form

The next stage of structure refinement takes your relations from first normal form to second normal form (2NF). For a relation to be in 2NF it must also fulfil the criteria for 1NF. For this reason, the official definition often starts "A relation is in 2NF if it is in 1NF and ...". This can be a source of confusion, but it simply means that we already know the relation is in 1NF and now we are refining it further.

Verifying that your relations are in 2NF is concerned with partial dependencies. A partial dependency arises when there are attributes in the relation that are functionally dependent on part of a composite primary key but not all of it. This leads to the observation that if your 1NF relation does not have a composite primary key, then it is already in 2NF and there is nothing more to do at this stage.

In the example of consultants and projects, the primary key is composite, and we therefore need to check for partial dependencies. The dependency diagram can help with this:

cons_proj(proj_code, proj_name, start_date, end_date, cons_id, cons_name, grade, daily_rate, days)

Functional dependencies

Notice that the only attribute which is functionally dependent on both of the attributes in the primary key is days. All of the others are dependent on either proj_code or cons_id. The majority of dependencies in the diagram are therefore partial dependencies. Toe resolve them, look at each one in turn and apply this procedure:

  1. Identify the group of attributes that make up the partial dependency
  2. Copy them into a new relation
  3. Identify the primary key in the new relation
  4. Mark the corresponding attribute in the original entity as a foreign key
  5. In the original relation, remove the non-key attributes from the partial dependency

The result is shown below. Note that the first two relations shown correspond to the two partial dependencies in the original relation. The third relation shown below is what is left in the original relation when the partial dependencies are removed.

Second normal form

We now have three relations which satisfy the requirements of 1NF, and in addition have no partial dependencies. This structure, which could be written as shown below, is in 2NF.

consultant(cons_id, cons_name, grade, daily_rate)

project(proj_code, proj_name, start_date, end_date)

cons_proj(proj_code, cons_id, days)