Solution 6

October 5, 2005

Total score: 25 points

1. Problem 5.20 (10 points)

Relational expression (5 points)

π_{StudId,P rofId}(T ranscript ⊳⊲ T eaching)/π_{P rofId}(σ_{Dept=′MUS′}(P rofessor))

SQL (5 points)

   SELECT R.StudId
   FROM Transcript R
   WHERE NOT EXISTS (
              (SELECT P.ProfId
               FROM Professor P
               WHERE P.Dept = 'MUS')
               EXCEPT
              (SELECT T.ProfId
               FROM Teaching T
               WHERE T.CrsCode = R.CrsCode
               AND T.Semester = R.Semester)
                        )

2. Problem 5.22 (10 points)

Relational algebral expression (5 points)

Define

LoosingAccounts = π_{Acct#,BrokerId}(Account) − π_{Acct#,BrokerId}(σ_{Gain>0}(Account))

The answer is then

σ_{Name}(Broker ⊳⊲ (π_{Id}(Broker) − π_{BrokerId}(LoosingAccounts)))

SQL (5 points)

   SELECT B.Name
   FROM Broker B
   WHERE NOT EXISTS (
                        SELECT A.Acct#
                        FROM Account A
                        WHERE A.BrokerId = B.Id AND A.Gain <= 0)
                       )

3. Problem 5.24 (10 points)

a) (5 points)

SELECT C.Id
FROM Customer C
WHERE
NOT EXISTS (
(SELECT H.Address                                      (i)
FROM   House H
WHERE  H.AgentId = '007' AND
NOT EXISTS
(
(SELECT P.Feature                                (ii)
FROM Preference P
WHERE P.CustId = C.Id)
EXCEPT
(SELECT A.Feature                                (iii)
FROM Amenity A
WHERE A.Address = H.Address)
)
)

Note: I took the view that a person is interested in a house if all of his preferences are listed in the features of the house.

The SELECT (starting in line (iii)) gets all the features of the house at the address ’H.Address’; The SELECT (starting in line (ii)) gets all the features preferred by the customer C.Id; The set difference of the above two selects gives us the features that are not offerred by the house but are preferred

by the customer.

The result of the SELECT (starting in line (i)) is all the houses that are listed with 007 and are not interested by the customer. If this set is not empty then the customer is not interested in some houses listed with 007 and hence should not be included in the final answer.

b) (5 points)

CREATE VIEW ClientOf007(CustId) AS
SELECT C.Id
FROM Customer C
WHERE
NOT EXISTS (
(SELECT H.Address
FROM   House H
WHERE  H.AgentId = '007' AND
NOT EXISTS
(
(SELECT P.Feature
FROM Preference P
WHERE P.CustId = C.Id)
EXCEPT
(SELECT A.Feature
FROM Amenity A
WHERE A.Address = H.Address)
)

     )
SELECT P.Feature, COUNT(*)
FROM Preference P, ClientOf007 C
WHERE P.CustId = C.CustId
GROUP BY P.Feature
HAVING COUNT(*) > 3