Homework #5 FAQ

Question 1

Question: How can you declare Auctions.seller to be a foreign key referencing Ratings.seller? The latter isn't a key.
Answer: Sorry -- that was my (Ullman's) mistake. You need to enforce the condition as a check, and you can't really protect against the situation where the last rating of a given seller is deleted, while the seller still appears in Auctions. Let's just assume that there are no deletions to Ratings.

Question: What is the key for Ratings?
Answer: Remember that SQL uses bag notation, so duplicate tuples are allowed. Since multiple people are allowed to rate the same bidder, and some of these ratings may have the same number of stars, there is in fact no key (which is okay.)

Question 2

Question: In (c), do we assume that there is only one rating per seller?
Answer: No; as in HW4, there can be several ratings per seller. Interpret this question as asking if the average rating of a seller is one star or less. Since there are presumably no ratings below 1 star, that is the same as asking if the average is exactly one star, but I would say <=, just in case there is incorrect data, e.g., someone tried to rate them 0 stars even though they are not supposed to. (That sort of ``don't assume users will obey your assumptions'' approach to life is a good one in general.) Note that if there are no ratings at all for a given seller, then the condition is not met, and there will be no constraint on this seller. Thus, you cannot use a construct like 1 >= ALL (SELECT rating ... .

Question In (a), can the number of items on sale in an auction decrease and cause an existing bid for that auction to exceed the number available?
Answer Yes. If that happens, the change should be rejected.

Question In (c), are we supposed to worry about the case in which a seller has more than 5 auctions and his rating drops below 1, due to changes in Ratings?
Answer No. Your constraint should only ensure that if a seller is rated below 1, then any *new* Auctions for that seller do not cause the total no. of auctions for that seller to exceed 5.

Question 3

Question: Are we supposed to handle these questions for updates, inserts, deletes, or all of the above?
Answer: Whatever you think is appropriate. If you can defend your design choice, and your defense makes sense, then we'll accept it (although you have to state your choice on your original paper, not when you come back for a regrade...)

Question: In (b), I tried implementing it in Oracle, but I can't seem to avoid the mutating table error (described in Jun Yang's questions page).
Answer: It turns out that there is no good way (at least that we could figure out) to avoid this error in a way that handles all cases. So, for the purposes of this assignment, you may assume that this error doesn't occur. Thus, if your answer is right in all other aspects, you will get full credit even if it can't actually run on Oracle in real life. However, this does NOT mean that you can ignore other Oracle limitations (i.e. where you can and cannot use subqueries, where you can use before, after, and instead of, etc.). You will be marked down for anything else that is illegal in Oracle, with the exception of something that causes a mutating table error. Of course, your other option is to use SQL3 instead.

Last modified: Mon Nov 8 15:32:40 PST 1999