Databases Reference
In-Depth Information
EXAMPLE 2: INFORMATION HIDING
Let's get back to the case in which S and P are both base relvars. Suppose now that (as in our
original suppliers-and-parts database, in fact) a city can be represented in base relvar S and not
base relvar P or the other way around; in other words, some cities have suppliers but no parts or
vice versa). Then the join SCP of S and P loses information, inasmuch as it's no longer
guaranteed that S and P are equal to the projections of that join on the corresponding attributes.
In fact, of the constraints mentioned in the section before last, the only ones that still apply are
the following (and this time I'll give them in prose form instead of formal syntax):
The usual key constraints all hold—{SNO} is a key for S, {PNO} is a key for P, and
{SNO,PNO} is a key for SCP.
At any given time, the current value of SCP is equal to the join of the current values of S
and P.
The FDs {SNO} → {CITY} and {PNO} → {CITY} and the MVDs {CITY} →→ {SNO}
and {CITY} →→ {PNO} hold in SCP.
(Certain inclusion dependencies hold too, but as with the second example in Chapter 6, I won't
bother to spell out the details here.)
A concrete example of a database value satisfying the foregoing conditions can be obtained
by extending Fig. 7.1 to reinstate the usual tuples for supplier S5 and part P3 (see Fig. 7.2, and
observe in particular that the relation shown as the current value of relvar SCP in that figure is
the same as it was in Fig. 7.1).
S P SCP
┌─────┬────────┐ ┌─────┬────────┐ ┌─────┬────────┬─────┐
│ SNO │ CITY │ │ PNO │ CITY │ │ SNO │ CITY │ PNO │
├═════┼────────┤ ├═════┼────────┤ ├═════┼────────┼═════┤
│ S1 │ London │ │ P1 │ London │ │ S1 │ London │ P1 │
│ S2 │ Paris │ │ P2 │ Paris │ │ S1 │ London │ P4 │
│ S3 │ Paris │ │ P3 │ Oslo │ │ S1 │ London │ P6 │
│ S4 │ London │ │ P4 │ London │ │ S2 │ Paris │ P2 │
│ S5 │ Athens │ │ P5 │ Paris │ │ S2 │ Paris │ P5 │
└─────┴────────┘ │ P6 │ London │ │ S3 │ Paris │ P2 │
└─────┴────────┘ │ S3 │ Paris │ P5 │
│ S4 │ London │ P1 │
│ S4 │ London │ P4 │
│ S4 │ London │ P6 │
└─────┴────────┴─────┘
Fig. 7.2: A revised version of Fig. 7.1
