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