Access Database Design and Programming, Second Edition

Normal Forms

Those who make a study of database design have identified a number of special forms, or properties , or constraints that a table scheme may possess, in order to achieve certain desired goals, such as minimizing redundancy. These forms are called normal forms. There are six commonly recognized normal forms, with the inspired names:

• First normal form (or 1NF)

• Second normal form (or 2NF)

• Third normal form (or 3NF)

• Boyce Codd normal form (or BCNF)

• Fourth normal form (or 4NF)

• Fifth normal form (or 5NF)

We will consider the first four of these normal forms, but only informally. Each of these normal forms is stronger than its predecessors. Thus, for instance, a table scheme that is in third normal form is also in second normal form. While it is generally desirable for the table schemes in a database to have a high degree of normalization, as we will see in this chapter, the situation is not as simple as it may seem.

For instance, requiring that all table schemes be in BCNF may, in some cases, cause some loss of information about the various relationships between the table schemes. In general, it is possible to manipulate the data to achieve third normal form for all table schemes, but this may turn out to be far more work than it is worth.

The plain fact is that forcing all table schemes to be in a particular normal form may require some compromises. Each individual situation (database) must be examined on its own merit. It is impossible to make general rules that apply in all situations. ...