Database Reference
In-Depth Information
member, you would need to change the CommitteeCode from ADV to CUR in rows 1 and 4 of
the table. After all, it doesn
t make sense to say that the committee is ADV when associated with
student 12805 and CUR when associated with student 24139. The same committee is served on
by the same faculty member. The faculty member does not serve on one committee when advis-
ing one student and a different committee when advising another student.
2. Additions. Suppose faculty member 555 joins the faculty at Marvel College. Also suppose that
this faculty member does not yet serve on a committee. When this faculty member begins advis-
ing student 44332, you have a problem because CommitteeCode is part of the primary key. You
need to enter a fictitious CommitteeCode in this situation.
3. Deletions. If faculty member 444 no longer advises student 57384 and you delete the appropri-
ate row from the table, you lose the information that faculty member 444 serves on the Housing
committee (HSG).
'
175
These problems are similar to those encountered in the discussions of both second normal form and
third normal form, but there are no functional dependencies among the columns in this table. A given faculty
member is not associated with one student, as he or she would be if this were a functional dependence. Each
faculty member, however, is associated with a specific collection of students. More importantly, this associa-
tion is independent of any association with committees. This independence is what causes the problem. This
type of dependency is called a multivalued dependency.
Definition:
In a table with columns A, B, and C, there is a multivalued dependence of column B on column
A (also read as
) when each value for A is associated
with a specific collection of values for B and, further, this collection is independent of any values for C. This
is usually written as follows:
Bismultidependent on A
or
A multidetermines B
A →→
B
Definition:
A table (relation) is in fourth normal form (4NF) when it is in third normal form and there are
no multivalued dependencies.
As you might expect, converting a table to fourth normal form is similar to the normalization process
used in the treatments of second normal form and third normal form. You split the third normal form table
into separate tables, each containing the column that multidetermines the others, which, in this case, is
FacultyNum. This means you replace
Faculty (FacultyNum, StudentNum, CommitteeCode)
with
FacStudent (FacultyNum, StudentNum)
FacCommittee (FacultyNum, CommitteeCode)
Figure 5-16 shows samples of these tables. As before, the problems have disappeared. There is no prob-
lem with changing the CommitteeCode ADV to CUR for faculty member 123 because the committee code
occurs in only one place. To add the information that faculty member 555 advises student 44332, you need
to add a row to the FacStudent table
t matter whether this faculty member serves on a committee.
Finally, to delete the information that faculty member 444 advises student 57384, you need to remove a row
from the FacStudent table. In this case, you do not lose the information that this faculty member serves on
the Housing committee.
it doesn
'
Search WWH ::




Custom Search