This is based on a solution to a similar problem found in Database Management Systems, Third Edition, though, unfortunately, that book prints a solution that is not relational algebra at all, as it relies on the order in which results are returned (relational algebra is a form of set algebra and sets have no order).
(Rename the attributes in TEMP1 – this is the step that is broken in the book)
As Paul Rubin pointed out the SQLin the last post was broken – here’s some that, I think (please do not let it be wrong a second time!), by relying on SQL’s coercion of a tuple to a scalar, would work (Paul proposed an even more ambitions way of doing this in a single join):
SELECT DISTINCT U.NAME, U.EMAIL
FROM USER U, BID B, LOT L
WHERE U.USER_ID = B.BIDDER_ID AND
B.AMOUNT > 100 AND B.LOT_ID = L.LOT_ID
AND (SELECT COUNT(*) FROM
BID, LOT WHERE
BID.LOT_ID = B.LOT_ID AND BID.BIDDER_ID = U.USER_ID
AND BID.LOT_ID = LOT.LOT_ID) > 1
Still no clue as to how to do this with relational algebra though.
This is starting to depress me, now, because I thought I was full on top of all this material until I tried this question.
So, going back to the database specified in part 1 – I now need to find the relational algebra (not the SQL, I have done that) for: “find the name and email of any user who has made multiple bids for a lot including a bid exceeding £100”.
Various ideas have crossed my mind and some bits are easy eg gets a relation from which we coul;d extract users who bid more than £100 for anything, but how can I get it all to string together.
I think the SQL that works is:
SELECT DISTINCT U.NAME, U.EMAIL FROM USER U, BID B, LOT L WHERE U.USER_ID = B.BIDDER_ID AND B.AMOUNT > 100 AND B.LOT_ID = L.LOT_ID AND L.LOT_ID IN ( SELECT COUNT(*) > 1 FROM LOT, BID WHERE LOT.LOT_ID = L.LOT_ID AND BID.LOT_ID = LOT.LOT_ID AND BID.BIDDER_ID = U.USER_ID)