• Programming work: Programming work must be submitted electronically by Thursday at 11:59pm. Programming work submitted after the deadline but less than 24 hours late (i.e., by Friday 11:59pm) will be accepted but penalized 10%, and programming work submitted more than 24 hours but less than 48 hours late (i.e., by Saturday 11:59pm) will be penalized 30%. No programming work will be accepted more than 48 hours late.

• For emergencies: Since emergencies do arise, each student is allocated four "chits" for turning in CS145 project work or challenge problems late with no penalty. Please see the Late Policy on the Assigned Work page for a detailed description of chits.

• Honor Code reminder: For more detailed discussion of the Stanford Honor Code as it pertains to CS145, please see the Assigned Work page under Honor Code. In summary: You must indicate on your written and programming assignments any assistance (human or otherwise) that you received. Any assistance received that is not given proper citation will be considered a violation of the Honor Code. In any event, you are responsible for understanding and being able to explain on your own all material that you submit.

• Reminder: Projects must be completed individually.

Overview

Part A: Current Time

To fully test your functionality, and to simulate the true operation of an online auction system in which auctions close as time passes, we suggest that you maintain a fictitious "current time" in your database. Add a new one-attribute table to your AuctionBase schema. This table should at all times contain a single row (i.e., a single value) representing the "current time," which can be updated to represent time passing. (It's up to you whether you also want to permit backward time-travel.) Initialize the table by inserting the current time for the initial state of your database: Dec-20-01 00:00:01.

Note: If you want to input and output dates in Oracle using the same format as in the original XML auction data files, issue the command:

  ALTER SESSION SET NLS_DATE_FORMAT='MON-DD-YY HH24:MI:SS';

Part B: PL/SQL

For this part, you will be writing a procedure that computes the "bidding closure" for a specified seller. More precisely, for a particular seller we wish to construct the set of all bidders who placed a bid on one of the seller's items, or (recursively) on an item being sold by any of the bidders in this set. For example, if we wish to compute the recursive bidding closure of seller Homestar, and Marzipan placed a bid on one of Homestar's items and Pompom placed a bid on one of Marzipan's items, then Marzipan and Pompom would be in the recursive bidding closure of Homestar. Note that Homestar is not part of this closure.

Your procedure should take as an argument the user id of a seller, and insert the user id's of the individuals in the bidding closure into a relation. Be sure to test your procedure thoroughly. When you are confident that your procedure is working correctly, run your procedure on user 'westbur' and create a transcript of your output on the large data set (with the full list of individuals in westbur's bidding closure). Save this transcript as closure.script and save your procedure as closure.sql. Please include the command to create your closure relation as part of closure.sql.

Correctness check: Your 'westbur' query should return 87 rows on the large data set.

Part C: Constraints and Triggers

Please remember that constraints and triggers in Oracle do not conform to the SQL-99 standard, the lecture notes, or the textbook.

If the data in your AuctionBase system at a given point in time represents a correct state of the real world, a number of constraints are expected to hold. To get you started, here are a few possible examples, some of which depend on a particular schema:

• In every auction the number-of-bids field (if included) corresponds to the actual number of bids.
• The item-id in every bid corresponds to an actual item.
• No auction may have a bid before its start time or after its end time.
• There are no bids after the current time.
• A user may not bid on an item he or she is offering. (This one is a judgment call.)
• All sellers and bidders must exist as users. (Whether this one makes sense depends on your relational schema.)
• Etc.

1. Specify in English every constraint you believe should hold in a correct AuctionBase database, i.e., in a database that represents a viable state of the real world. Consider only constraints that go beyond what is already enforced in the schema, e.g., you do not need to specify that ratings are integers, if indeed you implemented them as such. However, other than simple schema-enforced type constraints, you should list every constraint you can think of.

2. Choose at least five of your constraints from part (1) to implement in your system. Use the simplest possible mechanism to enforce each constraint, e.g., do not use triggers to enforce a unique key constraint or a simple attribute-level check. You should choose your five (or more) constraints so that at least one is implemented using referential integrity, at least one is implemented as a CHECK constraint, and at least two require triggers.

   For all cases other than triggers and referential integrity with an ON DELETE option, an error is raised automatically whenever a constraint is violated. For triggers, you may as part of the trigger(s) program a way of "repairing" a violated constraint automatically, or you may simply raise an error with the function RAISE_APPLICATION_ERROR as described in the Constraints and Triggers help document.

3. Add the constraints and triggers to your full AuctionBase database. The three steps we suggest you take are:

   1. Use the ALTER TABLE command to add key (UNIQUE), referential integrity, and CHECK constraints. See the Oracle 9i SQL and Constraints and Triggers help documents for examples. The ALTER TABLE command will fail if you try to add a constraint that does not hold on the current database.

   2. Unlike constraints, when you add a trigger to the database, the condition part of the trigger is not checked (i.e., there is no "retroactive" firing of the new trigger). Thus, any of your constraints that are enforced by triggers must be checked separately at the time you install your triggers, in order to ensure that the database begins in a consistent state. From that point on, the triggers should ensure that the constraints continue to hold whenever the database is modified. Write and execute a set of queries to check that all constraints being enforced by triggers hold in the current state of the database. Often the easiest way to write constraint-checking queries is to create queries that return an empty result if and only if the corresponding constraint holds.

      At this point, if any constraints do not hold, then either your constraints are inappropriate or your database is in an inconsistent state. Stop now and fix the problem!

   3. Create all of your triggers using the CREATE TRIGGER command described in the Constraints and Triggers help document. (If you get an error message "Warning: Trigger created with compilation errors.", type "SHOW ERRORS" to see what's wrong.)

   Note that if you recreate your entire database from scratch, you may create your tables with the key, referential integrity, and CHECK constraints already in place, in which case these constraints will be checked on data load. You probably will need to be careful that you load your tables in the correct order for referential integrity checking, or you can use deferred checking as described in the Constraints and Triggers help document. If you take this approach, you still need to follow steps 2 and 3 above for triggers.

4. For each constraint, demonstrate at least two database modifications relevant to the constraint: at least one that violates the constraint and one that doesn't. Similarly, for each trigger, demonstrate at least two database modifications that activate the trigger (i.e., that match the trigger's ON event): at least one where the trigger condition is satisfied and the trigger action executes, and one where the trigger condition is not satisfied so the trigger action does not execute.

5. Are there any database modifications that can violate more than one of your constraints simultaneously? If so, demonstrate what happens.

A note about transactions

Part C Submission

Following the English list, you will illustrate the five (or more) constraints that you chose to implement. For each constraint, repeat the English description and then list all items (a) through (e) as follows, carefully labelled:

(a) The Oracle constraint definition(s) (CREATE TRIGGER command(s), ALTER TABLE command(s), etc.). If the constraint is specified in the schema definition then include the entire CREATE TABLE command. List the commands in this part, but do not run them.

(b) One or more sample database modifications (SQL statements) that are relevant to the constraint but do not violate it. Include any commands you used to defer constraint checking or to disable/enable triggers. List the commands in this part, but do not run them.

(c) One or more sample database modifications (SQL statements) that are relevant to the constraint and do violate it. Include any commands you used to defer constraint checking or to disable/enable triggers. List the commands in this part, but do not run them.

(d) A transcript showing:

1. The successful definition of the constraint using your part (a) commands.
2. That the existing large database satisfies the constraint, or that you were able to reload the database with the constraint in place. (For example, for a constraint that all bid prices are positive, you might run the query "SELECT * FROM Bids WHERE Price <= 0" and show that the result is empty.)

3. The execution of the updates in parts (b) and (c).

  [English descriptions of all of the constraints you can think of]

  [Constraint 1]
  (a) [...]
  (b) [...]
  (c) [...]
  (d) 1. [...]
      2. [...]
      3. [...]

  [Constraint 2]
  (a) [...]
  (b) [...]
  (c) [...]
  (d) 1. [...]
      2. [...]
      3. [...]

    ...

What to submit

From your submission directory, execute the command

/usr/class/cs145/bin/submit

Follow the instructions exactly. If you must resubmit, run the command

/usr/class/cs145/bin/submit -replace

If you have any problems, please send mail to cs145@cs.stanford.edu. Remember that points may be deducted if you do not follow the submission procedures exactly as specified, including file naming and contents.