Homework #4 FAQ

PDA

Question: I tried it with and without indexes, and it is slower with indexes. What is wrong?
Answer: Possibly nothing is wrong, but try to find an example of a query that shows the difference. An index is most useful if it helps you focus on a tiny fraction of a relation. For example, a query that asks for a tuple by stating its key value in the WHERE clause (e.g., WHERE name = 'Bud') can use an index on the key to zero in on the one tuple with that key value, while without it you have to go through all the tuples. Even if this tuple is then joined with tuples of another relation, the index may be helpful, and if there is another index on the other relation that helps the system zero in on the matching tuples, then you should notice an improvement.

However, we'll accept whatever your data shows. Just tell us what queries you used, what indexes you created, and what the timings were.

Question 1

Question: Can I see an example of how the winners and prices are determined?
Answer: Yes indeed. Suppose 10 identical beenie babies are being auctioned, and the bids are:

     bidder  bid    quantity
      A      $10     6
      B      $ 8     4
      C      $ 7     5
      D      $ 6     8

The lowest price at which there are bids for 11 or more is $7. Thus, the 10 beenie babies are sold for $7 each. A gets 6 and B gets 4 of them, paying $42 and $28, respectively.

Now, suppose D's bid were raised to $9. The sale price would now be $9, since 11 or more are asked for at that price, but at no higher price are 11 bid for. A, the highest bidder, would get all 6 he bid for, paying $54. The remaining 4 would go to D for $36.

Now take the scenario when C also bids $8 (and D's bid is $6). The final price here is $8, since 11 or more beanie babies are taken at that price. A still gets 6, but then the remaning 4 are divided among B and C, in proportion to the number that they bid for. In this case, there are 4 beanie babies left to split among 9 bids. So B would end up with 4*(4/9) beanie babies and C would end up with 4*(5/9) beanie babies. Don't worry that these fractions don't necessarily make sense in real life, they would be resolved by some arbitrary method that you don't have to worry about.

Question 2

Question: I can't even begin to figure out part (b).
Answer: That's not a question. :-) However, it might help to write a few view definitions that compute important quantities or sets, e.g., a view that returns the price at which the goods are sold. Note that, in principle, once you have written views and used them in queries, you could expand the view uses inside your query and make a single query that would be legal SQL2.

Question 3

Question: What is the key for the Auctions relation?
Answer: You can assume that auctionID is a key. In otherwords, each auction is only hosted by one seller for one item, but note that they could be selling different quantaties of that item. For example, auction #1234 might be held where the seller is "CS145 TA", the item is "final solutions", the quantity is 20, and it expires 10/20/99. However, if "CS145 TA" also wanted to sell something else, they would do it under a different acutionID number.

Question: In part (c), do we change Sally's bids?, or does she enter new bids?
Answer: Good question. Actually, we'll accept either. That is, you can perform this operation either as an UPDATE or as an INSERT. If you choose the former, note that SQL allows a subquery in a SET clause. To check out the complete syntax of UPDATE, go to sqlplus and say help update.

In general, we believe that it is permitted for the same bidder to have two or more bids in the same auction. For example, you might want to make sure you get at least one of those beenie babies, so you bid $1000 for one, but you would take all 10 if you could get them at a reasonable price, so you bid $600 for 9. (Hmm... those things sold for $5.95 originally, so the notion of ``reasonable price'' may require some explanation.)

Question:In part d, what should the format of our result be?
Answer: The output of part d should be a single number (you want the average over all the sellers, not each seller's individual average). Pay attention to the "Note" on this problem, or you'll probably get it wrong...

Last modified: Mon Oct 25 15:13:26 PDT 1999