Databases Reference
In-Depth Information
All of that being said, I must make it clear that I don't want the foregoing comments to be seen as any kind of
attack. As I said in Chapter 8, I believe anything less than a fully normalized design is strongly contraindicated.
But the fact remains that normalization (“the scientific part of design”) as such really doesn't do as much of the job
as we'd like─and so it's good to be able to say that now there's a tiny piece of additional science available to us.
That's what the topic of orthogonality is all about.
Note:
The concept of orthogonality has evolved over time. As a result, portions of this chapter are at odds,
somewhat, with previous writings─mostly by myself─on this same subject. What's more, I very much doubt
whether this chapter is the last word, either. I do believe the chapter is accurate as far as it goes; however, further
refinements to the material might well be possible, and desirable.
Caveat lector.
A MOTIVATING EXAMPLE
For simplicity, suppose the FD {CITY} →{STATUS} does
not
hold in relvar S (
please note that
I'll stay with this
assumption throughout the present chapter
). Consider the following decomposition of that relvar:
SNC { SNO , SNAME , CITY }
KEY { SNO }
STC { SNO , STATUS , CITY }
KEY { SNO }
Sample values are shown in Fig. 14.1. As the figure shows, this decomposition is hardly very sensible (in particular,
note that the fact that a given supplier is located in a given city appears twice), and yet it abides by all of the
normalization principles─both projections are in 5NF; the decomposition is nonloss; dependencies are preserved;
and both projections are needed in the reconstruction process.
SNC STC
┌─────┬───────┬────────┐ ┌─────┬────────┬────────┐
│ SNO │ SNAME │ CITY │ │ SNO │ STATUS │ CITY │
├═════┼───────┼────────┤ ├═════┼────────┼────────┤
│ S1 │ Smith │ London │ │ S1 │ 20 │.London │
│ S2 │ Jones │ Paris │ │ S2 │ 30 │ Paris │
│ S3 │ Blake │ Paris │ │ S3 │ 30 │ Paris │
│ S4 │ Clark │ London │ │ S4 │ 20 │ London │
│ S5 │ Adams │ Athens │ │ S5 │ 30 │ Athens │
└─────┴───────┴────────┘ └─────┴────────┴────────┘
Fig. 14.1: Relvars SNC and STC─sample values
Intuitively, the problem with the foregoing design is obvious: The tuple (
s
,
n
,
c
) appears in SNC if and only if
the tuple (
s
,
t
,
c
) appears in STC; equivalently, the tuple (
s
,
c
) appears in the projection of SNC on SNO and CITY if
and only if that very same tuple (
s
,
c
) appears in the projection of STC on SNO and CITY. To state the matter a
trifle more formally, we can say the design is subject to the following equality dependency (EQD)─
CONSTRAINT ... SNC { SNO , CITY } = STC { SNO , CITY } ;
─and this EQD makes the redundancy explicit.
To repeat, however, the foregoing design abides by
all
of the well established principles of normalization. It
follows that those principles by themselves aren't enough─we need something else to tell us what's wrong with this
design (something else
formal
, that is; we all know what's wrong with it informally). To put the matter another
