Solving a relational query: part 1

I have not found the answer to this, so thought I’d go through the steps.

Assume we have a relational database with these tables:

USER (USER_ID, NAME, EMAIL)
LOT (LOT_ID, SLLER_ID, TYPE, DESCRIPTION, RESERVE_PRICE, START_DATE, END_DATE)
BID (BID_ID, LOT_ID, BIDDER_ID, BID_DATE, AMOUNT)
FEE (LOT_ID, AMOUNT)

What is the relational algebra that finds “the description and reserve price of any lot where all bids have been less than the reserve price”.

I have been struggling with this and came up with this:

\Pi_{(DESCRIPTION,RESERVE\_PRICE)}\ \sigma_{(\Pi_{(LOT\_ID=(\Pi_{LOT\_ID}(LOT\Join BID)\ -\ \Pi_{LOT\_ID}(LOT\Join BID-\sigma_{(AMOUNT<RESERVE\_PRICE)}(LOT\Join BID))})(LOT)}

But that will not work as will return any lot where a bid has been made for less than the reserve price.

I am sure this is a question of relational division but have not yet been able to get my mind round the right way to formulate it.

8 thoughts on “Solving a relational query: part 1

  1. Are you open to suggestions, or trying to sort through it without help?

    Also (hope this is not a spoiler of any kind), does the trivial case of no bids count as all bids less than reserve price? (Or does the scenario guarantee at least one bid on all lots?)

  2. I would welcome any assistance, certainly!

    In fact I wrote the blog because I was using WordPress to process the LaTeX to send to my lecturer (who offered to look over worked examples as part of exam revision) when I realised my proposed solution was wrong (I was going to ask him is the answer really so complex in any case?). So I thought I’d turn it into a blog that might aid fellow students or even provoke some help.

    I am pretty certain lots with no bids don’t count.

    If you can help I guess I am buying you a second pint🙂

  3. Well, the relational algebra notation makes my eye water, so I’ll just give you an insight in pidgin SQL (my SQL is a bit rusty) and let you (a) do all the work and (b) decide if it’s worth a pint. I think the key is to select bids greater than (or equal to) the reserve price and then skip those lots. So something along the lines of

    select DESCRIPTION, RESERVE_PRICE from LOT where LOT_ID not in (select unique LOT.LOT_ID from LOT, BID where LOT.LOT_ID = BID.LOT_ID and AMOUNT >= RESERVE_PRICE)

Comments are closed.