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 ) ;