CS145 Assignment #5

Due Wednesday, November 10, 1999

Step 5 of Your PDA

  1. (10 pts.) For each of the relation schemas of your PDA, indicate

    2. (a)
    A suitable key for the relation.
    (b)
    Any foreign key (referential integrity) constraints that you expect will hold for the relation.

    Modify your database schema to include the declaration of keys for all relations and at least one foreign-key constraint for some relation (even if you decided that no such constraints should logically hold -- we assume almost every PDA will have some natural foreign-key constraints). Show us the resulting database schema and the result of successfully declaring these relations to the database system.

  2. (15 pts.) Add two attribute-based and two tuple-based CHECK constraints to relations of your database schema. Remember that these constraints are more limited in Oracle 8 than in the SQL2 definition; see The Non-SQL2 Guide for details. Show the revised schema, its successful declaration, and the response of Oracle to inserts that violate the constraints. You may combine this part with the previous part if you like, to avoid repeating the schema.

  3. (15 pts.) Write three PL/SQL programs (See the PL/SQL Guide) to perform operations on your PDA database. Each should be nontrivial, illustrating a feature or features such as local variables, multiple SQL statements, loops, and branches. In addition, at least one should involve a cursor. We encourage you to be imaginative. However, here are some sorts of things you might try if you can't think of something more interesting:

    a)
    Compute some aggregate value from a relation and use that value to modify values in that or another relation.
    b)
    Create a new relation and load it with values computed from one or more existing relations.
    c)
    Enforce a constraint by searching your database for violations and fixing them in some way.

    Hand in a listing of your programs and scripts showing them working. You should demonstrate that the programs had their intended effect by querying (before and after) some relation of your PDA that was changed by the program. These queries may be included in the file that holds your PL/SQL programs for convenience.

  4. (10 pts.) Write two PL/SQL stored functions or procedures. At least one should involve more than one SQL statement; you need not follow the other ``nontriviality'' conditions mentioned in (1). Each should use one or more parameters in a significant way.

    Hand in listings of your code and scripts showing them called at least once each. Also, show in the script the results of queries that demonstrate the functions have had their intended effect.

  5. (10 pts.) Write two Oracle Triggers. See The PL/SQL Guide for a synopsis of Oracle triggers. You should also check The Non-SQL2 Guide for some important restrictions on triggers.

    Hand in your code and a script showing the triggers declared. Also, the script should show, for each trigger, the effect of two database modifications. One modification should trigger the trigger, and the other not. Show in the script queries that demonstrate that the trigger has an effect in the first case and not in the second.

Problem Set

The written part of this assignment is based on the same relations as in the last part of Problem Set 4: 

     Bids(auctionID, bidder, price, quantity)
     Auctions(auctionID, seller, item, quantity, expires)
     Ratings(seller, stars)
  1. (15 pts.) Write schemas for these relations in SQL2. Decide on appropriate types for the attributes. Declare keys as appropriate (your judgement is called for). Declare foreign keys so that a bid must be on an auction whose ID exists in Auctions and a seller in an auction must have a rating in Ratings. Also, require that:

  2. (15 pts.) Show how to add to your schema from problem (1) the following attribute-based checks, tuple-based checks, or SQL2 assertions, as appropriate.

    (a)
    A bid cannot be for more than the total quantity of items available in the auction for which the bid is made.

    (b)
    The quantity in any auction cannot be negative. (This constraint does not address the question of whether a bid can be for a negative number.)

    (c)
    If a seller is rated ``1 star,'' then they cannot hold more than 5 auctions.

  3. (10 pts.) Write the following triggers in either SQL3 standard notation or in the Oracle trigger language (tell us which!).

    (a)
    When a new auction is entered into the Auctions table, the seller is listed with a bid of 0 for the entire quantity in the Bids table.

    (b)
    A bid in an auction for which an equal or larger bid already exists is rejected. Note: Check out Jun Yang's FAQ Guide for an explanation of how Oracle rejects modifications. However, it is also possible to clean up (i.e., reverse) the modification after it is made, rather than rejecting it.