Databases Reference
In-Depth Information
way, the normalization discipline provides a set of formal principles to guide us in our attempts to reduce
redundancy, but that set of principles by itself is inadequate, as the example plainly shows. We need another
principle. In other words, as I've said more than once in this topic already, we need more science.
A SIMPLER EXAMPLE
In order to see what the principle we need might look like, let's consider a simpler example. As you know,
normalization as such─in particular, normalization as used in the example of the previous section─has to do with
“vertical” decomposition of relvars (meaning decomposition via projection). But “horizontal” decomposition (that
is, decomposition via restriction) is clearly possible, too. Consider the design illustrated in Fig. 14.2, in which the
parts relvar P has been split horizontally─in fact, partitioned─into two relvars, one (“light parts,” LP) containing
parts with weight less than 17.0 pounds and the other (“heavy parts,” HP) containing parts with weight greater than
or equal to 17.0 pounds. (I assume for definiteness that WEIGHT values represent weights in pounds avoirdupois.)
┌─────┬───────┬───────┬────────┬────────┐
LP │ PNO │ PNAME │ COLOR │ WEIGHT │ CITY │
├═════┼───────┼───────┼────────┼────────┤
│ P1 │ Nut │ Red │ 12.0 │ London │
│ 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.2: Relvars LP and HP─sample values
The predicates are as follows:
LP:
Part PNO is named PNAME, has color COLOR and weight WEIGHT (which is less than 17.0), and is
stored in city CITY.
HP:
Part PNO is named PNAME, has color COLOR and weight WEIGHT (which is greater than or equal
to 17.0), and is stored in city CITY.
Note that the original relvar P can be recovered by taking the (disjoint) union of relvars LP and HP.
Why might we want to perform such a horizontal decomposition? Frankly, I'm not aware of any good
logical reason for doing so, though of course that's not to say no such reason exists. Be that as it may, observe that
we can, and should, state two constraints that apply to these relvars:
CONSTRAINT LPC AND ( LP , WEIGHT < 17.0 ) ;
CONSTRAINT HPC AND ( HP , WEIGHT
≥
17.0 ) ;
