Integrity Rules and Normalization - Database Systems - page 73

We may be tempted to introduce a timestamp on each tuple, and therefore modify
the definition of Course as follows:

Course {CourseNo, CourseName, CourseCred, EffectiveDate}

Figure 4-6 provides some sample data for the Course relation. By introducing
the attribute EffectiveDate , we have actually introduced a new set of concerns as
summarized below:

1. If we assume that the FD CourseNo → {CourseName,

CourseCred, EffectiveDate} is (still) in vogue, then Course
is in 5NF. However, in this case, if the CourseName or
CourseCred of a given Course tuple changes at a given
effective date, there is no way of showing what it was before,
unless we create a new course and assign a new CourseNo .
In either case, this clearly, is undesirable.

2. Suppose we assume the FDs CourseNo → CourseName and

[CourseNo, EffectiveDate] → CourseCred.Then, the relation is
not in 2NF, and therefore needs to be decomposed into two
decompositions:

CourseDef {CourseNo, CourseName} and

CourseTimeStamp {CourseNo, EffectiveDate, CourseCred}

Both of these relations would now be in 5NF. However, if we
now desire to change the CourseName of a course for a given
effective date, we cannot represent this in the current schema.

3. We could introduce a surrogate (say CourseRef ) into relation

Course , and key on the surrogate, while ignoring the FDs
stated in (1) and (2) above. In this case, Course would be in
violation of 3NF, and if we attempt to decompose, we would
revert to the situation in case (2) above.

Figure 4-6. Sample Data for the Course Relation