Consider the relation schema of Exercise 5.24. Write the following queries using TRC and DRC.

a. Find all customers who are interested in every house listed with Agent “007”.
b. Using the previous query as a view, retrieve a set of tuples of the form feature, customer where each tuple in the result shows a feature and a customer who wants it such that
Only the customers who are interested in every house listed with Agent “007” are considered; and
The number of customers interested in “feature” is greater than 2. (If this number is 2 or less, the corresponding tuple feature, customer is not added to the result.)

This part of the query cannot be conveniently expressed by TRC or DRC because they lack the counting operator. However it is possible nevertheless (and is not hard).

a. TRC:



{C.Id, C.Name | Customer(C) AND ?H ? House

       (H.AgentId = ’007’ ?

              ?P ? Preference ?A ? Amenity

                         (H.Address = A.Address AND P.CustomerId = C.Id ?

                                 P.Feature= A.Feature )) }

DRC:



{Id, Name | Customer(Id, Name) AND

        ?Addr ?OwnerId (House(Addr, OwnerId, ’007’) ?

                              ?Feature(Preference(Id, Feature) ?

                                               Amenity(Addr, Feature))) }

b. Let us denote the result of the above query as ClientOf007 and use it as a view.
TRC:



{P.Feature,C.CustName |

       Preference(P) AND ClientOf007(C) AND P.CustId=C.CustId AND

       ?P2 ? Preference ?C2 ? ClientOf007

           ?P3 ? Preference ?C3 ? ClientOf007

                 (P.Feature = P2.Feature AND P.Feature = P3.Feature AND

                  P2.CustId = C2.CustId AND P3.CustId = C3.CustId

                  P.CustId = P2.CustId AND P.CustId = P3.CustId

                        AND P2.CustId = P2.CustId) }

DRC:



{Feature,CustName |

        Preference(CustId,Feature) AND ClientOf007(CustId,CustName)

        AND ?CId2 ? ClientOf007.CustId ?CId3 ? ClientOf007.CustId

                 (Preference(CId2,Feature) AND Preference(CId3,Feature)

                  AND CustId = CId2 AND CustId = CId3 AND CId2 = CId3)