Databases Reference
In-Depth Information
ST { SNO , STATUS } KEY { SNO }
TC { STATUS , CITY } KEY { STATUS , CITY }
Note:
Relvar ST is exactly as it was in Example 1. Relvar TC, by contrast, though it has
the same heading as relvar CT in Example 2, has different semantics (as is reflected by the fact
that it has a different key); for that reason, I've given it a different name in order—I hope—to
reduce confusion. Sample values are shown in Fig. 5.3. Observe that I've changed the status of
supplier S2 back to 10, since the FD {CITY} → {STATUS} no longer holds.
S ST TC
┌─────┬────────┬────────┐ ┌─────┬────────┐ ┌────────┬────────┐
│ SNO │ STATUS │ CITY │ │ SNO │ STATUS │ │ STATUS │ CITY │
├═════┼────────┼────────┤ ├═════┼────────┤ ├════════┼════════┤
│ S1 │ 20 │ London │ │ S1 │ 20 │ │ 20 │ London │
│ S2 │ 10 │ Paris │ │ S2 │ 10 │ │ 10 │ Paris │
│ S3 │ 30 │ Paris │ │ S3 │ 30 │ │ 30 │ Paris │
│ S4 │ 20 │ London │ │ S4 │ 20 │ │ 30 │ Athens │
│ S5 │ 30 │ Athens │ │ S5 │ 30 │ └────────┴────────┘
└─────┴────────┴────────┘ └─────┴────────┘
Fig. 5.3: Relvars S, ST, and TC—sample values
As indicated, the sole keys for these relvars are {SNO} for ST and {STATUS,CITY} (the
entire heading) for TC. Note that, in contrast to Examples 1 and 2, relvars ST and TC together
correspond to a
lossy
, not a nonloss, decomposition of relvar S. In other words, S isn't equal to
the join of ST and TC,
12
and the two designs—S by itself and the combination of ST and TC—
are thus not information equivalent (to be specific, information regarding which suppliers are in
which cities is lost in the latter design).
Be that as it may, let's begin as usual by assuming that all three relvars are base ones. Here
then are the predicates for ST and TC:
ST:
Supplier SNO is under contract and has status STATUS.
TC:
Some supplier is under contract and has status STATUS and is located in city CITY.
Aside:
Here, mainly just to stress the fact that the decomposition of relvar S of which
relvar TC is a part is lossy, I depart from my usual practice in this chapter and give the
predicate for that relvar in quantified form (“Some supplier ...”).
End of aside
.
12
That's because ST and TC aren't obtained from S in accordance with any JD that holds in S. Once again, see
Database Design
and Relational Theory
for further explanation.
