Notes > Database Systems > Relational Normalization / Normal Forms
Being familiar with the process of Normalization will lead to good design practices. The concept of Normalization and Normal Forms are automatically considered by most database designers. Understanding how to use them in a step by step process is useful with helping to understand what the different Normal Forms mean and how they apply to real databases. It is also useful when analysing relations that have been designed by another individual.
Normalization is defined as a technique for producing a set of well designed relations that measure up to a set of requirements which are outlined in various levels of normalization (or Normal Forms). Normalization also reduces information redundancy. The concept of normalization was first developed and documented by E. F. Codd (1972).
The most commonly used normal forms are as follows:
- First (1NF)
- Second (2NF)
- Third (3NF)
- Boyce Codd (BCNF)
Fourth (4NF) and Fifth (5NF) Normal Forms exist but are not commonly employed in typical database design. 1NF is essential for a relational database design. 3NF is recommended to prevent the most common update anomalies. A higher Normal Form does not necessarily mean a better design though. Normalizing relations to a higher normal form may result in poorer performance.
Normalization has the underlying aim of minimising information redundancy, avoiding data inconsistency and preventing insertion, deletion, and modification anomalies.
First Normal Form (1NF)
Relations can only be in 1NF if each row and column intersection (cell) contains a single atomic value. A primary key will be determined at this initial stage of normalization also. It may be necessary that the primary key is made up of more than one attribute.
Second Normal Form (2NF)
For relations to be in 2NF they must first be in 1NF. They must also have no partial dependencies . A partial dependency occurs when the primary key is made up of more than one attribute (i.e. it is a composite primary key) and there exists an attribute (which is a non-primary key attribute) that is dependant on only part of the primary key.
These partial dependencies can be removed by removing all of the partially dependent attributes into another relation along with a copy of the determinant attribute (which is part of the primary key in the original relation)
Third Normal Form (3NF)
Getting a relation to 3NF involves removing any transitive dependencies. Therefore, a relation in 3NF must be in 1NF and 2NF and it must have no non-primary key attributes which are transitively dependent upon the primary key.
Search for "Relational Normalization / Normal Forms" on:
eBay (UK) |
Search for "Relational Normalization / Normal Forms" on the rest of Computing Students: Relational Normalization / Normal Forms