CS145 Assignment #7

Due Wednesday, December 1, 1999

Step 7 of Your PDA

Your program should consist of a continuous loop in which:

1.

A list of at least five alternative options is offered to the user. (an additional alternative should be quit.)

2.

The user selects an alternative.

3.

The system prompts the user for appropriate input values.

4.

The system accesses the database to perform the appropriate queries and/or modifications.

5.

Data or an appropriate acknowledgment is returned to the user.

1. Look up the price of a given beer at a given bar.
2. Find the bar with the lowest price for a given beer.
3. Given a drinker, find all the other drinkers that frequent at least one bar in common.
4. Add a new beer to the Beers relation.
5. Increase all the prices at a given bar by a given amount.
6. Quit.

We are not expecting anything fancy in the way of interface. For example, a menu printed via printf is OK. Also, handling of SQL errors can be quite simple. Either write an sqlerror routine that just prints the error message from Oracle or copy the error handler from a sample program (of course you should acknowledge this borrowing just as you would acknowledge any other use of someone else's work in your homework or project).

Hand in your program and a script showing the program running. Each of the options should be exercised at least once in your script.

Problem Set

1. (10 pts.) For the ``auction'' database first introduced in HW4:

        Bids(auctionID, bidder, price, quantity)
        Auctions(auctionID, seller, item, quantity, expires)
        Ratings(seller, stars)
   

   Design an equivalent ODL schema. You may assume that in Bids, a bidder may place several bids for one auction, but all bids by one bidder will have different prices. Indicate keys and extents.

2. (10 pts.) Using the auction DB above, write the following queries in Datalog. You may wish to define several ``helper'' IDB predicates.

   a)

   Find all the bidders on auctions for item ``Beanie Baby.''

   b)

   Find all the sellers who have both 1-star and 5-star ratings.

   c)

   Find the highest price bid for any auction where the item is ``Beanie Baby.''

3. (10 pts.) Below is a simple ``auction'' database, involving bidders and auctions in which there is a single item offred in each auction (unlike the more complex ``Dutch'' style auction in the first two problems).

        interface Auction (extent Auctions key aID) {
            attribute integer aID;
            attribute string item;
            relationship Set<Bid> bids inverse Bid::theAuction;
        }
   
        interface Bid (extent Bids) {
            attribute integer amount;
            relationship Bidder theBidder inverse Bidder::placedBids;
            relationship Auction theAuction inverse Auction::bids;
        }
   
        interface Bidder (key name extent Bidders) {
            attribute string name;
            attribute integer limit;
            relationship Set<Bid> placedBids inverse Bid::theBidder;
        }
   

   Write the following queries in OQL:

   a)

   Find the bidders and auctions such that the bidder has placed a bid above the bidder's limit.

   b)

   Find the names of the bidders in auctions for Beanie Babies.

   c)

   For each auction find the highest bid and the name of the bidder who placed that bid.

4. (10 pts.) Here is yet another representation of a simple auction. This time, we use a relation Bids(bidID, bidder, amount) to represent single bids. Note that we introduce the notion of a ``bid ID'' to serve as a key for these bids, a device we have not used in any previous representation of auctions. We also use two relations to represents bids and their history. Initial(auctionID, bidID) gives the ID of the initial bid for each auction, and Raises(bidID, prevBidID) tells for each subsequent bid the ID of the previous bid for the same auction.

   a)

   Write in recursive Datalog the query that gives, for each bid the auction in which it is a bid.

   b)

   Write your answer to (a) in SQL3.

   c)

   Write in Datalog the query that asks for all ``good'' auctions, meaning that there is no bid whose amount is less than that of the previous bid.