Databases Reference
In-Depth Information
The only significant difference if programmers could have an arbitrary number of language skills is that
relvar PGMR would be “all key” (i.e., its sole key would be {ENO,LANG}).
5.8
Yes, they are (of course!).
CHAPTER 6
6.1
CONSTRAINT ...
COUNT ( JOIN { TJ , TS } ) =
COUNT ( ( JOIN { TJ , TS } ) { S , J } ) ;
Or, using the alternative style for constraints described in the answer to Exercise 4.8:
CONSTRAINT ... JOIN { TJ , TS } KEY { S , J } ;
6.2 Let LT and CT be the projections of RX2A′ on {CLASS,STATUS} and {CITY,STATUS}, respectively.
Then (a) {CLASS} and {CITY} will be foreign keys in RX2B′, referencing LT and CT, respectively, and (b) the
following multirelvar constraint will also hold:
CONSTRAINT ... WITH ( LTX := LT RENAME { STATUS AS X } ,
CTY := CT RENAME { STATUS AS Y } ) :
AND ( JOIN { RX2B′ , LTX , CTY } , X = Y ) ;
6.3 The first of the given FDs means {STREET,CITY,STATE} is a key; the second means the relvar isn't in
BCNF. However, if we use Heath's Theorem to decompose it (on the basis of the FD {ZIP} → {CITY,STATE})
into BCNF projections as follows─
ZCT { ZIP , CITY , STATE }
KEY { ZIP }
ZR { ZIP , STREET }
KEY { ZIP , STREET }
─then we lose the FD {STREET,CITY,STATE} → {ZIP}. As a result, relvars ZCT and ZR can't be independently
updated. (
Subsidiary exercise:
Develop some sample values for ZCT and ZR to illustrate this point.) Of course, if
we don't perform this decomposition, there'll be some redundancy; to be specific, the fact that a given zip code
corresponds to a particular city and state will appear several times. But does that redundancy cause problems?
Given that the zip code for a given city and state doesn't change very often, the answer is “possibly, but not very
often.” (On the other hand, it's not true to say zip codes
never
change.)
6.4
Here's an irreducible cover for RX1:
{ SNO , PNO }
→
{ QTY }
{ SNO }
→
{ CITY }
{ CITY }
→
{ STATUS}
The 3NF procedure yields {SNO,PNO,QTY}, {SNO,CITY}, and {CITY,STATUS}.
Next, RX3. An irreducible cover:
