Databases Reference
In-Depth Information
CITY, STATE, and ZIP. (Of course, that result is still only in 2NF, not even BCNF, and it still suffers from
redundancy.)
We might conclude from this example that unwrapping TVAs is a good idea. But is it enough of a good idea
to be enshrined as a
principle
of good design?
9
EXAMPLE 2
Codd would probably have prohibited the design of Example 1 on the grounds that values of attribute ADDR aren't
“atomic” (though I'm not aware that he ever explicitly addressed the question of tuple valued attributes as such in
any of his writings). Now, I don't agree with this position myself, for reasons I've explained in detail in
SQL and
Relational Theory
and elsewhere─but the point isn't worth fighting over, because we can obviously replace that
tuple valued attribute by an attribute of type CHAR as shown in Fig. 15.2. Codd would surely have allowed that
revised design, and yet it suffers from redundancies precisely analogous to those in Example 1.
NADDR
┌──────┬───────────────────────────┐
│ NAME │ ADDR
/* type CHAR */
│
├══════┼───────────────────────────┤
│ Jack │ 1 Main St., SFO, CA 94100 │
│ Jill │ 2 Main St., SFO, CA 94100 │
└──────┴───────────────────────────┘
Fig. 15.2: Revised relvar NADDR (ADDR is text valued)─sample value
And if you don't like this example, consider what could happen if attribute ADDR is of some user defined
type (ADDRESS, say) instead of type CHAR.
EXAMPLE 3
Redundancies similar to those in Example 2 can arise in connection with attributes of type DATE, if those attributes
include the day of the week as well as a calendar date (as in, for example, “Tuesday, January 18th, 2011”).
EXAMPLE 4
My next example is an extremely simple version of the familiar employees-and-programmers example, in which all
programmers are employees but some employees aren't programmers (as in Exercise 5.7). Note that some people
would say that employees and programmers in this example correspond to an
entity supertype
and an
entity subtype
,
respectively. Be that as it may, here's the conventional design:
EMP { ENO }
KEY { ENO }
9
In his topic
An Introduction to Relational Database Theory
(Ventus, 2010), Hugh Darwen suggests that it might be, and that we might consider
a
wrap-unwrap normal form
in this connection. He also suggests in that same topic that ungrouping RVAs is a good idea, too, and that we might
thus also consider a
group-ungroup normal form
accordingly.
