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
.