DATABASE DESIGN 1: NORMALIZATION - Concepts of Database Management - page 176

Faculty

FacultyNum StudentNum CommitteeCode
123 12805 ADV

123 12805 HSG

123 12805 PER

123 24139 ADV

176

123 24139 HSG

123 24139 PER

444 57384 HSG

456 24139 CUR

456 36273 CUR

456 37573 CUR

FacStudent FacCommittee

FacultyNum StudentNum FacultyNum CommitteeCode

123 123 12805 ADV

123 24139 123 HSG

444 57384 123 PER

456 24139 444 HSG

456 36273 456 CUR

456 37573

FIGURE 5-16 Conversion to fourth normal form

Figure 5-17 summarizes the four normal forms.

Normal Form Meaning/Required Conditions Notes

First normal form No repeating groups exist

Second normal form First normal form and no non- Automatically second normal form

key column is dependent on only if the primary key contains only a

a portion of the primary key single column

Third normal form Second normal form and the only Actually Boyce-Codd normal

determinants are candidate keys form (BCNF)

Fourth normal form Third normal form and no

multivalued dependencies exist

FIGURE 5-17 Normal forms

AVOIDING THE PROBLEM WITH MULTIVALUED DEPENDENCIES

Any table that is not in fourth normal form suffers some serious problems, but there is a way to avoid dealing
with the issue. You should have a design methodology for normalizing tables that prevents this situation from
occurring in the first place. You already have most of such a methodology in place from the discussion of the
first normal form, second normal form, and third normal form normalization processes. All you need is a
slightly more sophisticated method for converting an unnormalized table to first normal form.