Databases Reference
In-Depth Information
( SELECT PAY
FROM EMP AS EY
WHERE EY.ENO = EX.ENO
AND MONTH = 'Dec' ) AS DEC_PAY
FROM EMP AS EX
Defining the second in terms of the first (again in outline):
SELECT ENO , 'Jan' AS MONTH , JAN_PAY AS PAY FROM EMP
UNION
...
UNION
SELECT ENO , 'Dec' AS MONTH , DEC_PAY AS PAY FROM EMP
CHAPTER 9
9.1 Joining SP and PJ is discussed in the body of the chapter. Joining PJ and JS yields the spurious tuple
(S2,P2,J1), which is then eliminated because there's no (S2,P2) tuple in SP. Joining JS and SP yields the spurious
tuple (S1,P2,J2), which is then eliminated because there's no (P2,J2) tuple in PJ.
9.2 CONSTRAINT ... SPJ = JOIN { SPJ { SNO , PNO } ,
SPJ { PNO , JNO } ,
SPJ { JNO , SNO } } ;
9.3
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 }
Now, 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 . This is a 3-way cyclic rule. So if we were to have a relvar RAP looking like this─
RAP { RNO , ANO , PNO } KEY { RNO , ANO , PNO }
(with the obvious predicate)─then the following JD would hold in that relvar:
{ { RNO , ANO } , { ANO , PNO } , { PNO , RNO } }
The relvar would thus be subject to redundancy. So let's replace it by its three binary projections:
RA { RNO , ANO } KEY { RNO , ANO }
AP { ANO , PNO } KEY { ANO , PNO }
PR { PNO , RNO } KEY { PNO , RNO }
(Now there are several equality dependencies that need to be stated and enforced─e.g., the projections R{RNO},
RA{RNO}, and PR{RNO} must always be equal─but the details are straightforward and I omit them here.)
Next, each representative is responsible for sales in one or more areas, and each area has one or more
responsible representatives. But this information is already contained in relvar RA, and nothing more is necessary.
Similarly, relvar AP takes care of the facts that each area has one or more products sold in it and each product is sold

Search WWH ::

Custom Search