Databases Reference
In-Depth Information
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 advising stu-
dent 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).
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 mem-
ber is not associated with one student, as he or she would be if this were a functional dependence. Each fac-
ulty member, however, is associated with a specific collection of students. More importantly, this association
is
independent
of any association with committees. This independence is what causes the problem. This type
of dependency is called a multivalued dependency.
169
Definition:
In a table with columns A, B, and C, there is a
multivalued dependence
of column B on column
A (also read as “B is
multidependent
on A” or “A
multidetermines
B”) 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 usu-
ally written as follows:
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
encountered 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—it doesn'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.
