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.