Databases Reference
In-Depth Information
EXERCISES
6.1 Relvar SJT from the section “An Unfortunate Conflict” is subject to the FD {S,J} → {T}. Write a
CONSTRAINT statement in
Tutorial D
to express the multirelvar constraint that replaces this FD if we decompose
SJT into its projections TJ on {T,J} and TS on {T,S}.
6.2 (
Repeated from the body of the chapter.
) Suppose relvar RX2A′ from section “... And Another” is
decomposed into its projections on {CLASS,STATUS} and {CITY,STATUS}. As noted in that section, an
appropriate multirelvar constraint will now have to be separately stated and enforced. What does that constraint
look like?
6.3
The following relvar is intended to represent a set of United States addresses:
ADDR { STREET , CITY , STATE , ZIP }
A typical tuple might look like this (
Tutorial D
syntax):
TUPLE { STREET '1600 Pennsylvania Ave.' ,
CITY 'Washington' , STATE 'DC' , ZIP '20500' } }
Assume, not entirely unreasonably, that the following FDs hold in this relvar and are irreducible:
{ STREET , CITY , STATE }
→
{ ZIP }
{ ZIP }
→
{ CITY , STATE }
How would you decompose this relvar?
6.4 Show the effects of applying the 3NF procedure to relvars RX1, RX3, and RX2 (note the sequence here!)
from the body of the chapter.
6.5 Here's a predicate:
Star S plays role R in movie M, which was directed by director D and released in year Y;
further, star S was born on date B and therefore has zodiac sign Z and Chinese zodiac C, and Z and C together
determine S's horoscope H
. Give a set of FDs that capture the foregoing state of affairs. State any assumptions you
make regarding “business rules” that might be in effect. Also, apply the BCNF procedure to obtain an appropriate
set of BCNF relvars. Does that procedure lose any FDs?
