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.)
In (a), can the number of items on sale in an auction decrease
and cause an existing bid for that auction to exceed
Answer Yes. If that happens, the change should be rejected.
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
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.
In (b), I tried implementing it in Oracle, but I can't seem to avoid the
mutating table error (described in
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.