Database Normalization: Second Normal Form

Second normal form is achieved when each attribute in a record is functionally dependent on the entire key of that record.

Candidate Keys

The key of a normalized record must have the following characteristics:

·        It must uniquely identify the record for each occurrence of the record.

·        It must not contain redundant data, i.e., no attribute in the key can be eliminated without destroying the unique identification of the key.

Sometimes, more than one attribute or group of attributes could be the key of a record.  Alternate choices are called candidate keys.  Only one candidate key can be designated the primary key.
 
With the introduction of candidate keys, a more comprehensive definition of second normal form is as follows:

A record is in second normal form if it is in first normal form and every non-key attribute of the record is fully functionally dependent on each candidate key for the record.

Description of Functional Dependency

Functional Dependency is defined as:

Attribute X of a relation is functionally dependent on attribute Y (the key) of that relation if attribute X has only one value associated with a specific value for attribute Y.

In other words, if the value of attribute Y, the key, is known, the associated value of attribute X can be determined.  For example, in an employee record, the EMPLOYEE START DATE is functionally dependent on EMPLOYEE NUMBER.  For each EMPLOYEE NUMBER there is only one EMPLOYEE START DATE.  To access the EMPLOYEE START DATE, you need to know the EMPLOYEE NUMBER, the key.

An attribute can be functionally dependent on a group of attributes rather than only on an attribute.
 

Full Functional Dependency

If the key of a relation is a concatenation of attributes, attributes are said to be fully functionally dependent on the key if they are functionally dependent on the whole key, and not on a subset of it.

Third Rule of Normalization

Each attribute in a relation must be dependent on the entire key that uniquely identifies the relation and not on a partial key.

Applying the Rules to Achieve Second Normal Form

 
To normalize data into second normal form, apply the third rule by:

·        removing any attributes which are dependent on part of the key only,

·        forming a new relation for that attribute, identified by the specific part of the original key.

In general, to achieve second normal form, every attribute in a record should be dependent on the entire key.  If an attribute is not dependent on the entire key, the attribute is split off to form a separate relation.

Problem with a Record Not in Second Normal Form

The basic problems with the design of the example Inventory Record are:

·        The warehouse address is repeated in every record that refers to a part stored in that warehouse.

·        If the address of the warehouse changes, every record referring to a part stored in that warehouse must be updated.

·        Because of the redundancy, the data might become inconsistent, with different records showing different addresses for the same warehouse.

·        If, at some point in time, there are no parts stored in the warehouse, there may be no record in which to keep the warehouse's address.