Databases Reference
In-Depth Information
EXAMPLE 9
This example is due to Hugh Darwen. It's based on a real life situation that arises in connection with the Open
University in the U.K. We're given a relvar that looks like this:
SCT { SNO , CNO , TNO }
KEY { SNO , CNO , TNO }
The predicate is:
Tutor TNO tutors student SNO on course CNO.
Fig. 15.7 shows a sample value for this relvar.
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 the figure.
15
(Note that the JD
{{SNO,CNO},{CNO,TNO},{TNO,SNO}} does
not
hold in relvar
SCT.)
┌─────┬─────┬─────┐
SCT │ SNO │ CNO │ TNO │
├═════┼═════┼═════┤
│ S1 │ C1 │ T1 │
│ S1 │ C1 │ T2 │
│ S2 │ C1 │ T1 │
│ S2 │ C1 │ T2 │
│ S1 │ C2 │ T1 │
│ S2 │ C2 │ T1 │
└─────┴─────┴─────┘
15.7: Relvar SCT─sample value
Now, one tactic we might consider for reducing redundancy in examples like this one is to make use of
surrogate keys (surrogates for short).
16
For example, we might introduce an attribute X, say, whose values serve as
surrogates for (SNO,CNO) pairs, as illustrated in Fig. 15.8. (Observe from that figure that I've made {X} the
primary key for relvar XSC. However, the combination {SNO,CNO} is still a key too, of course.)
┌─────┬─────┬─────┐ ┌─────┬─────┐
XSC │ X │ SNO │ CNO │ XT │ X │ TNO │
├═════┼─────┼─────┤ ├═════┼─────┤
│
x1
│ S1 │ C1 │ │
x1
│ T1 │
│
x2
│ S2 │ C1 │ │
x1
│ T2 │
│
x3
│ S1 │ C2 │ │
x2
│ T1 │
│
x4
│ S2 │ C2 │ │
x2
│ T2 │
└─────┴─────┴─────┘ │
x3
│ T1 │
│
x4
│ T1 │
└─────┴─────┘
Fig. 15.8: Using surrogates for (SNO,CNO) combinations
15
You might not agree that those repetitions constitute redundancy. If you don't, however, I ask you to hold your objections for now─I'll be
taking a much closer look at this example later in the chapter.
16
As a matter of fact, Codd advocated the use of surrogates in his RM/T discipline in connection with
all
entity types. In this recommendation he
was following the pioneering work of Patrick Hall, John Owlett, and Stephen Todd in their paper “Relations and Entities,” in G. M. Nijssen (ed.),
Modelling in Data Base Management Systems
(North-Holland/Elsevier Science, 1975).
