Databases Reference
In-Depth Information
Of course, the rules as just stated are loose in the extreme; nevertheless, I think this rough and
ready formulation should help you hang on to the big picture as we struggle through all of the
detailed discussions to follow in this chapter and the next two.
EXAMPLE 1: INFORMATION EQUIVALENCE
Because join is so important, and because there are so many issues we need to discuss in
connection with it, I've decided to split my treatment of the topic into three separate chapters. In
this first one, I want to limit my attention to what might be regarded as the simplest possible
case: viz., the case in which the join in question is a one to one join specifically. As usual, I'll
base my discussions on some simple examples.
My first example is effectively the inverse of the first of the projection examples from the
previous chapter. (As noted in that chapter, there's a tight connection between join views and
projection views in general.) To be specific, suppose we're given base relvars ST and SC,
looking like this (in outline):
ST { SNO , STATUS } KEY { SNO }
SC { SNO , CITY } KEY { SNO }
(As in Chapter 5, I ignore attribute SNAME for simplicity.) Now suppose we define the join of
these two relvars, ST JOIN SC, as a view S:
S { SNO , STATUS , CITY } KEY { SNO }
Further, let's assume this join is strictly one to one, in the sense that every tuple in ST joins
to exactly one tuple in SC and vice versa. In other words, we have information equivalence—the
design consisting of ST and SC taken together and the design consisting of just view S are
clearly information equivalent. Sample values are shown in Fig. 6.1 (of course, that figure is
identical to Fig. 5.1 in Chapter 5, but now ST and SC are base relvars and S is a view).
S ST SC
┌─────┬────────┬────────┐ ┌─────┬────────┐ ┌─────┬────────┐
│ SNO │ STATUS │ CITY │ │ SNO │ STATUS │ │ SNO │ CITY │
├═════┼────────┼────────┤ ├═════┼────────┤ ├═════┼────────┤
│ S1 │ 20 │ London │ │ S1 │ 20 │ │ S1 │ London │
│ S2 │ 10 │ Paris │ │ S2 │ 10 │ │ S2 │ Paris │
│ S3 │ 30 │ Paris │ │ S3 │ 30 │ │ S3 │ Paris │
│ S4 │ 20 │ London │ │ S4 │ 20 │ │ S4 │ London │
│ S5 │ 30 │ Athens │ │ S5 │ 30 │ │ S5 │ Athens │
└─────┴────────┴────────┘ └─────┴────────┘ └─────┴────────┘
Fig. 6.1: Relvars S, ST, and SC—sample values
