Basic and Advanced Normal Forms

The usual catalog of relational normal forms lists five of them. They are called, reasonably enough, first, second, third, fourth and fifth normal forms (abbreviated here as 1NF, 2NF and so on). These normal forms fall into two groups. The normal forms in the first group are defined in terms of functional dependencies – a concept that will be explained later. Those in the second group are defined in terms of multi-valued dependencies and lossless joins. I will call the first group the “basic” normal forms and the second group the “advanced” normal forms.
Practitioners often claim that if a data model is in 3NF, it is “fully normalized.” This claim appears to be based on the belief that violations of 4NF or 5NF are only found in academic discussions of normalization and do not occur in real-world data models. But violations of 4NF and 5NF do appear in real-world data models, although less frequently than violations of the basic normal forms.
When violations of the advanced normal forms do occur, they are generally difficult to recognize. But as with all the normal forms, we must still either remove the violations from the data model or else write maintenance code to insure that they do not cause inconsistencies in the database. If we do not remove the violation of the normal form, then we must write maintenance code, which insures that all inserts, updates and deletes, apply to all of the multiple copies of the same fact. In this way, the database is maintained in a consistent state, in spite of the fact that the same fact is recorded multiple times. If we cannot even recognize violations of 4NF and 5NF, then we will not be able to either remove the flaw from our data models or write maintenance code to protect the database against inconsistencies.
In this series, I will review only the basic normal forms, leaving discussion of the advanced normal forms for a later series. In addition to 1NF, 2NF and 3NF, I will review an “improved” 3NF, called Boyce-Codd normal form (BCNF), which is where the line should be drawn between the basic and the advanced normal forms. This is important to understand because the frequently occurring, easy-to-recognize violations of normalization are based on the redundant expression of functional dependencies, and a database in 3NF may still contain such violations. A database in BCNF, however, is guaranteed to contain no violations based on functional dependencies.

Posted by Unknown Print this post

Labels: Basic Database Normalization