RelationalDBDesign RelationalDBDesign



Third Normal Form   «Prev  Next»
Lesson 7 Normalization beyond 3NF
Objective Describe normalization beyond Third Normal Form

Normalization beyond Third Normal Form

Normalizing a table to 3NF will remove most of the anomalies from your tables, but there are a few that can slip through the cracks.
It is possible to create tables where a field can determine more than one value in another field. For example, if a CD contained stories written by more than one author and read by more than one reader, a CDNo value could determine more than one Author value and more than one Reader value. Records from such a table are shown in the following illustration.

CD table consisting of columns 1) CDNo 2) ReaderID 3) AuthorID

Note the table is flawed in several ways:
  1. Deleting the author from a CD destroys all information about the CD's reader(s).
  2. Deleting the reader from a CD destroys all information about the CD's author(s).
  3. You must know at least one author to enter information about a CD's readers.
  4. You must know at least one reader to enter information about a CD's authors.
  5. If a CD has more than one record associated with it, either an author name or a reader name is repeated in every record relating to that CD.

These anomalies are a result of the relation having multi-valued dependencies[1]. That is, in the preceding relation, a CDNo value can determine more than one Author value and more than one Reader value. Eliminating multivalued dependencies is the goal of Fourth Normal Form (4NF).[2]
4NF is really part of advanced database design and is beyond the scope of this course.


Your Design Goal

When designing a database, your goal is to normalize its tables to 3NF. Except in special circumstances, like those described above, 3NF will be sufficient to ensure you don't lose any important data and that your database will update, delete, and modify data efficiently. There are normal forms above 4NF (called Fifth Normal Form and domain/key normal form), but they are highly theoretical and not reasonable design goals when normalizing the tables of a database. The next lesson discusses denormalization.

[1]multi-valued dependency: A condition in which a primary-key value in one table determines multiple values in two or more tables.
[2]Fourth Normal Form (4NF): A relation is in 4NF if it all of its determinants are candidate keys and it contains no multi-valued dependencies.