Normalisation

The purpose of normalisation is to remove redundancy and prevent conflict.

1st Normal Form (1NF) – Every field can have only one value.
No repeating value and no repeating groups.

From wikipedia:

A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.

2nd Normal Form (2NF) – All non-key fields must not be dependent on the key fields. This is to prevent conflict if a non-key field is changed which is tied to one column of a composite key.

3rd Normal Form (3NF) – All non-key fields must not be dependent on another non-key field.

From wikipedia:

A memorable statement of Codd’s definition of 3NF, paralleling the traditional pledge to give true evidence in a court of law, was given by Bill Kent: “[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key.” A common variation supplements this definition with the oath: “so help me Codd”.

Requiring existence of “the key” ensures that the table is in 1NF; requiring that non-key attributes be dependent on “the whole key” ensures 2NF; further requiring that non-key attributes be dependent on “nothing but the key” ensures 3NF.

Good data warehouse design normally uses 3NF.

HOWEVER, there are times when this rule is broken which is quite common in business intelligence data warehouse. This is called denormalisation and is for both convenience of use and for better performance.

Leave a Reply

Your email address will not be published.