Databases Reference
In-Depth Information
(I remind you from Chapter 2 that the
Tutorial D
expression AND(
rx
,
bx
), where
rx
is a relational expression and
bx
is a boolean expression, returns TRUE if and only if
bx
evaluates to TRUE for every tuple in the relation denoted
by
rx
.)
So we have here a slightly unusual situation: To be specific, in the case of relvars LP and HP, part of the
predicate can, and should, be captured formally in the shape of explicit constraints. Indeed, the very fact that such
constraints need to be stated and enforced might be seen as militating against the design. But even if horizontal
decomposition is contraindicated at the logical level, there are still plenty of pragmatic reasons (having to do with
recovery, security, performance, and other such matters) for such a decomposition at the physical level; hence,
given that the logical and physical levels tend to be in lockstep, pretty much, in today's DBMSs─i.e., there's not
nearly as much data independence in those DBMSs as there ought to be─it follows that there are likely to be
pragmatic reasons for performing such a decomposition at the logical level as well, at least in current products.
Now, regardless of what you might think of the foregoing argument, at least there's nothing obviously bad
about the design of Fig. 14.2 (well, let's agree as much for the sake of the example, at any rate).
2
But suppose we
were to define relvar LP just a little differently; to be specific, suppose we defined it to contain those parts with
weight less than
or equal to
17.0 pounds (adjusting the predicate and constraint LPC accordingly, of course).
Fig. 14.3 below is a revised version of Fig. 14.2, showing what happens with this revised design. As you can see,
now there definitely is something bad; to be specific, the tuples for parts P2 and P3 now appear in both relvars in
Fig. 14.3 (in other words, there's now some redundancy). What's more, those tuples
must
appear in both relvars!
For suppose, contrariwise, that (say) the tuple for part P2 appeared in HP and not in LP. Then, noting that LP
contains no tuple for part P2, we could legitimately conclude from
The Closed World Assumption
─see Chapter
2─that it's not the case that part P2 weighs 17.0 pounds. But then we see from HP that part P2 in fact does weigh
17.0 pounds, and the database is thus inconsistent, because it contains a contradiction. (Inconsistency in a database
is highly undesirable, of course. As I show in
SQL and Relational Theory
, you can never trust the results you get
from an inconsistent database; in fact, you can get
absolutely any result whatsoever
─even results asserting
nonsensical things like 0 = 1─from such a database!)
┌─────┬───────┬───────┬────────┬────────┐
LP │ PNO │ PNAME │ COLOR │ WEIGHT │ CITY │
├═════┼───────┼───────┼────────┼────────┤
│ P1 │ Nut │ Red │ 12.0 │ London │
│ P2 │ Bolt │ Green │ 17.0 │ Paris │
│ P3 │ Screw │ Blue │ 17.0 │ Paris │
│ P4 │ Screw │ Red │ 14.0 │ London │
│ P5 │ Cam │ Blue │ 12.0 │ Paris │
└─────┴───────┴───────┴────────┴────────┘
┌─────┬───────┬───────┬────────┬────────┐
HP │ PNO │ PNAME │ COLOR │ WEIGHT │ CITY │
├═════┼───────┼───────┼────────┼────────┤
│ P2 │ Bolt │ Green │ 17.0 │ Paris │
│ P3 │ Screw │ Blue │ 17.0 │ Paris │
│ P6 │ Cog │ Red │ 19.0 │ London │
└─────┴───────┴───────┴────────┴────────┘
Fig. 14.3: Relvars LP (revised) and HP─sample values
Now, the problem with the design of Fig. 14.3 is easy to see: The predicates for LP and HP “overlap,” in the
sense that the very same tuple
t
can satisfy both of them. What's more, if
t
is such a tuple, and if at some given time
2
Actually there might be something bad. Consider, for example, what has to happen if the weight of part P1 is doubled.
