Database Reference
In-Depth Information
one, or more committees. As an initial relational design for this situation, suppose you chose the following
unnormalized table:
Faculty (FacultyNum, (StudentNum), (CommitteeCode) )
The single Faculty table has a primary key of FacultyNum (the number that identifies the faculty
member) and two separate repeating groups, StudentNum (the number that identifies the student) and
CommitteeCode (the code that identifies the committee, such as ADV for Advisory committee, PER for
Personnel committee, and CUR for Curriculum committee). To convert this table to first normal form,
you might be tempted to remove the two repeating groups and expand the primary key to include both
StudentNum and CommitteeCode. That solution would give the following table:
174
Faculty (FacultyNum, StudentNum, CommitteeCode)
Samples of the table with repeating groups and with the repeating groups removed appear in Figure 5-15.
Faculty
FacultyNum StudentNum CommitteeCode
123
12805
24139
ADV
HSG
PER
444
57384
HSG
CUR
456
24139
36273
37573
Faculty
FacultyNum StudentNum CommitteeCode
123
123
123
123
123
123
444
456
456
456
12805 ADV
12805 HSG
12805 PER
24139 ADV
24139 HSG
24139 PER
57384
HSG
24139
CUR
36273
CUR
37573
CUR
FIGURE 5-15
Incorrect way to remove repeating groups—relation is not in fourth normal form
You already may have suspected that this approach has some problems. If so, you are correct. It is a
strange way to normalize the original table. Yet it is precisely this approach for removing repeating groups
that leads to the problems concerning multivalued dependencies. You will see how this table should have
been normalized to avoid the problems altogether. For now, however, you
'
ll examine this table to see what
kinds of problems are present.
The first thing you should observe about this table is that it is in third normal form because no groups
repeat, no column is dependent on only a portion of the primary key, and no determinants exist that are not
candidate keys. There are several problems, however, with this third normal form table.
1. Update. Changing the CommitteeCode for faculty member 123 requires more than one change.
If this faculty member changes from an Advisory committee member to a Curriculum committee
