Databases Reference
In-Depth Information
EXAMPLE 1
It's my claim that design theory as a field of investigation is, in general, still wide open. To bolster this claim, in
this section and the next few I want to give some examples of designs that (a) are fully normalized and fully
orthogonal (at least in most cases) and yet (b) still suffer from various redundancies (again, in most cases).
For my first example, consider the following simple relvar, which represents a set of names and addresses
(the predicate is
Person NAME resides at address ADDR
):
NADDR { NAME , ADDR }
KEY { NAME }
Suppose attribute ADDR in this relvar is
tuple valued
, where the tuples in question have attributes STREET,
CITY, STATE, and ZIP. (Yes, tuple valued attributes or TVAs are legal, just as relation valued attributes or RVAs
are legal─see Chapter 4─and for much the same reasons.) A sample value for this relvar is shown in Fig. 15.1.
NADDR
┌──────┬─────────────────────────────────────┐
│ NAME │ ADDR
/* tuple valued */
│
├══════┼─────────────────────────────────────┤
│ │┌────────────┬──────┬───────┬───────┐│
│ Jack ││ STREET │ CITY │ STATE │ ZIP ││
│ │├────────────┼──────┼───────┼───────┤│
│ ││ 1 Main St. │ SFO │ CA │ 94100 ││
│ │└────────────┴──────┴───────┴───────┘│
│ │┌────────────┬──────┬───────┬───────┐│
│ Jill ││ STREET │ CITY │ STATE │ ZIP ││
│ │├────────────┼──────┼───────┼───────┤│
│ ││ 2 Main St. │ SFO │ CA │ 94100 ││
│ │└────────────┴──────┴───────┴───────┘│
└──────┴─────────────────────────────────────┘
Fig. 15.1: Relvar NADDR (ADDR is tuple valued)─sample value
Assume now for the sake of the example, as we did in Exercise 6.2, that whenever two ADDR values have
the same ZIP component, they also have the same CITY and STATE components. Then the foregoing design
clearly involves some redundancy. Yet there's no violation of normalization here; in particular, the functional
dependency
{ ZIP }
→
{ CITY , STATE }
does
not
hold. (Why not?
Answer:
Because FDs are defined to hold among attributes, not among components of
attributes.)
That said, let me now point out that the foregoing FD does hold in the result of replacing NADDR by the
result of the following expression:
NADDR UNWRAP ( ADDR )
Note:
The
Tutorial D
UNWRAP operator effectively replaces some tuple valued attribute by a set of attributes, one
for each component of that TVA. Thus, the foregoing expression returns a result with attributes NAME, STREET,
