Databases Reference
In-Depth Information
each of those relvars. Suppose the relations in
Y
and
Z
have headings
A
and
B
, respectively; then the relvars that
result from those ungroupings will have headings
XA
and
XB
, respectively.
10
Now normalize those relvars in the
usual way, replacing them by BCNF projections. Then those BCNF projections will “automatically” be in 4NF. In
other words, MVDs that cause a relvar
not
to be in 4NF shouldn't arise in practice, if the foregoing procedure is
followed.
It's interesting to note, incidentally, that in his famous 1970 paper (see Appendix C), Codd gave an example
in which he actually followed the foregoing procedure, and he touched on it again, briefly, in another paper the
following year (“Normalized Data Base Structure: A Brief Tutorial,” Proc. 1971 ACM SIGFIDET Workshop on
Data Description, Access, and Control, San Diego, Calif., November 11th-12th, 1971; again, see Appendix C). But
I don't think he ever mentioned it again, at least not in writing (because it was so intuitively obvious, perhaps).
Note:
In case you find the foregoing discussion too abstract, take
R
to be a relvar with heading {CNO,T,X},
where T and X are relation valued and contain relations with headings {TNO} and {XNO}, respectively. Separating
the RVAs gives us relvars with headings {CNO,T} and {CNO,X}, respectively. Ungrouping then gives us relvars
with headings {CNO,TNO} and {CNO,XNO}, respectively─which is precisely what we want, of course, in the
CTX example.
12.9
First of all, we'll presumably need three relvars for representatives, areas, and products, respectively:
R { RNO , ... } KEY { RNO }
A { ANO , ... } KEY { ANO }
P { PNO , ... } KEY { PNO }
Next, we can represent the relationships (a) between sales representatives and sales areas and (b) between
sales representatives and products by relvars like this:
RA { RNO , ANO } KEY { RNO , ANO }
RP { RNO , PNO } KEY { RNO , PNO }
Every product is sold in every area. So if we introduce a relvar
AP { ANO , PNO } KEY { ANO , PNO }
to represent the relationship between areas and products, then we have the following constraint:
CONSTRAINT C1 AP = JOIN { A { ANO } , P { PNO } } ;
(The join here is actually a cartesian product.) Note that this constraint implies that AP isn't in 4NF. In fact, AP
doesn't give us any information we can't obtain from the other relvars. To be precise, the following EQDs hold:
AP { ANO } = A { ANO }
AP { PNO } = P { PNO }
But let's assume for the moment that relvar AP is included in our design anyway.
No two representatives sell the same product in the same area. In other words, given an {ANO,PNO}
combination, there's exactly one responsible sales representative, RNO, and so we can introduce a relvar
10
We might have to do some attribute renaming first, if any attribute in either
A
or
B
has the same name as some attribute in
X
.
