Databases Reference

In-Depth Information

Since {CNO,TNO,XNO} is a key, the relvar is subject to the following functional dependency─

{ CNO , TNO , XNO }
→
{ DAYS }

─which is an “arrow out of a key.”

So DAYS depends on all three of CNO, TNO, and XNO, and it can't appear in a relvar with anything less

than all three.

Hence there's no (nontrivial) decomposition of the relvar into projections that applies at all─the relvar is in

5NF.
Note:
A decomposition is trivial if and only if it's based on dependencies (FDs or JDs) that are

themselves trivial in turn, and nontrivial if and only if it isn't trivial. Trivial FDs were discussed in Chapters

4 and 5; trivial JDs are discussed in the next chapter.

Hence there's certainly no decomposition into projections that can remove the redundancies, a fortiori.

EXERCISES

9.1 (
Repeated from the body of the chapter.
) Check that joining any pair of the binary relations shown in Fig. 9.2

yields a result containing a “spurious” tuple (i.e., a tuple not appearing in Fig. 9.1) and that joining the third binary

relation to that intermediate result then eliminates that spurious tuple.

9.2 Write a
Tutorial D
CONSTRAINT statement to express the JD that holds in relvar SPJ as discussed in the

body of the chapter.

9.3 Design a database for the following. The entities to be represented are sales representatives, sales areas, and

products. Each representative is responsible for sales in one or more areas; each area has one or more responsible

representatives. Each representative is responsible for sales of one or more products, and each product has one or

more responsible representatives. Each product is sold in one or more areas, and each area has one or more products

sold in it. Finally, if representative
r
is responsible for area
a
, and product
p
is sold in area
a
, and representative
r

sells product
p
, then
r
sells
p
in
a
.

9.4

Give an example from your own work environment, if possible, of a relvar in BCNF but not 5NF.