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.
