Normalization

Normalization

Normalization is a database design technique which begins by examining the relationships (called functional dependencies) between attributes.

Uses a series of tests (described as normal forms) to help identify the optimal grouping for these attributes to ultimately identify a set of suitable relations that supports the data requirements of the enterprise.

The purpose of normalization is to identify a suitable set of relations that support the data requirements of an enterprise. The characteristics of a suitable set of relations include the following:

Ø  The minimal number of attributes necessary to support the data requirements of the enterprise.

Ø  Attributes with a close logical relationship

Ø  Minimal redundancy

Normalization of purchase order system

First find out attributes of the system

Key as PO-NO

List all the attribute in one relation add applicable values.

First Normal Form - 1NF

A relation is in First Normal Form (1NF) if ALL its attributes are ATOMIC.

i.e.

If there are no repeating groups.

If each attribute is a primitive.

e.g. integer, real number, character string, but not lists or sets

non-decomposable data item

single-value

Within a single purchase order we could find several part numbers, part descriptions and part quantities. Hence, parts ordered can be decomposed.

1NF - Actions Required

1) Examine for repeat groups of data

2) Remove repeat groups from relation

3) Create new relation(s) to include repeated data

4) Include key of the 0NF to the new relation(s)

5) Determine key of the new relation(s)

Second Normal Form - 2NF

• 2NF is based on the concept of full functional dependency.

• A functional dependency X->Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

A relation is in 2NF if it is in 1NF and every non-key attribute is dependent on the whole key

Deals with the relationship between non-key and key fields

It is relevant when the key is composite, i.e. consists of several fields

Part Description is depended only on Part No, which is part of the key of PO-PART.

2NF - Actions Required

If entity has a concatenated key

1)Check each attribute against the whole key

2)Remove attribute and partial key to new relation

3)Optimize relations

    

Third Normal Form - 3NF

A relation is in 3NF if it is in 2NF and each non-key attribute is only dependent on the

whole key, and not dependent on any non-key attribute.

i.e. no transitive dependencies

Supplier name is a non-key field depended on another non-key field (i.e. the supplier no) in

addition to be depended on the key purchase order no.

3NF - Actions Required

1)Check each non-key attribute for dependency against other non-key fields

2)Remove attribute depended on another non-key attribute from relation

3)Create a new relation comprising the attribute and non-key attribute which it depends on

4)Determine key of the new relation