Databases Reference
In-Depth Information
Example 5
Let employee E1 be represented in relvar EMP and let the JOB value for E1 be “Programmer” (so employee E1 is
represented in relvar PGMR as well). Then the proposition
E1 is a programmer
is clearly represented explicitly in
two different ways.
Example 6
For a supplier who does have at least one of the three properties (name, status, and city), this example is essentially
the same as Example 4, mutatis mutandis. (For a supplier with none of those properties, there's no redundancy.)
Example 7
The proposition
Employee E5 is in department D3
is represented both explicitly by a tuple in EMPDEPT and
implicitly by the combination of (a) the proposition
Every employee is in exactly one department
(which is
effectively represented by the pertinent foreign key definition) and (b) the lack of tuples in EMPDEPT representing
the propositions
Employee E5 is in department D1
and
Employee E5 is in department D2
.
Example 8
The proposition
Employee E4 is unsalaried
is represented both explicitly by a tuple in UNSALARIED and
implicitly by the combination of (a) the proposition
Every employee has a known salary or an unknown salary or is
unsalaried
(which should again represented by a certain declared integrity constraint) and (b) the lack of a tuple for
employee E4 in either EARNS or SALARY_UNK.
Example 9
Now this is an interesting one. Earlier, I said the following:
The redundancies ... are obvious: For example, the fact that student S1 is enrolled in course C1, the fact that course C1 is
tutored by tutor T1, and the fact that tutor T1 tutors student S1 are all represented more than once in the sample value
shown in [Fig. 15.7].
I also said the predicate was
Tutor TNO tutors student SNO on course CNO.
But if the redundancies really
are as stated, the predicate can't be quite that simple. Instead, it has to be something like this:
Student SNO is enrolled in course CNO
and
Course CNO is tutored by tutor TNO
and
Tutor TNO tutors student SNO
and
Tutor TNO tutors student SNO on course CNO.
A more complete design would thus involve relvars as follows:
S {SNO,...}, C {CNO,...}, and T {TNO,...}, representing students, courses, and teachers, respectively
SC {SNO,CNO,...}, CT {CNO,TNO,...}, and TS {TNO,SNO,...}, showing which students are enrolled in
which courses, which courses are tutored by which tutors, and which tutors tutor which students, respectively
