• Submission: Programming work is submitted electronically and must be submitted by Thursday at 11:59 PM. Programming work submitted after the deadline but less than 24 hours late (i.e., by Friday 11:59 PM) 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:59 PM) will be penalized 30%. No programming work will be accepted more than 48 hours late.

• For emergencies: Since emergencies do arise, each student is allowed a total of four unpenalized late days (four periods up to 24 hours each) for programming work together with challenge problems, although no single assignment may be more than two days late. See the Assigned Work page for more information.

• 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.

• 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';

Create a file named time.sql that contains your commands for creating and initializing your current time table.

Part B: PL/SQL

For this part, you will be writing a procedure that computes the "bidding closure" for a specified seller. More precisely, given a particular seller as a parameter, 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 Jennifer, and Beverly placed a bid on one of Jennifer's items, and Qi placed a bid on one of Beverly's items, then Beverly and Qi would be in the recursive bidding closure of Jennifer. Note that Jennifer is not part of this closure.

Your procedure, called compute_closure, should take as an argument the user id of a seller, and it should insert into a relation called my_closure the user id's of the individuals in the seller's bidding closure.

Please create a command file named closure.sql containing the command to create your closure relation, your PL/SQL procedure, and a command for invoking your procedure with some parameter. To facilitate the grading process, your closure.sql file must have the following format:

    drop procedure compute_closure;
    drop table my_closure;

    create table my_closure  ( ... );

    create procedure compute_closure ( ... )
    ...
    end;
    .
    run;

    begin
        compute_closure( ... );
    end;
    .
    run;

    select count(*) from my_closure;

    drop procedure compute_closure;
    drop table my_closure;

    quit;

Correctness check: When you run your procedure for seller 'westbur' using the large data set, your bidding closure should contain 87 rows.

Part C: Constraints and Triggers

Please be very careful to note that constraints and triggers as supported in Oracle do not conform to the SQL-99 standard, the lecture notes, or the textbook. You will need to read the help document carefully.

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.)

• Specify in English every constraint you believe should hold in a correct AuctionBase database using your schema, 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. Put your list of English constraints in a text file named constraints.txt

• Choose at least four of your English constraints 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 four (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, raise an error with the function RAISE_APPLICATION_ERROR as described in the Constraints and Triggers help document.

• Add the constraints and triggers to your full AuctionBase database. Use the following steps to create and initially check your constraints, and to create the appropriate files for submission.

  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. For each of these constraints, find at least one modification where the constraint fails and one modification where the constraint holds. When you have successfully tested your constraint, for our grading purposes please create one command file for each constraint with the following format:

         alter table ... drop constraint ... ;
         alter table ... add constraint ... ;
         insert/delete/update ... ; /* modification where the constraint fails */
         insert/delete/update ... ; /* modification where the constraint holds */
         alter table ... drop constraint ... ;
         quit;
     

     You should fill in the details of the constraint and the modifications. Make sure you put the modification that fails the constraint first. Some notes:
     • Dates: If your modification involves dates that are not in the Oracle default format, add a command for altering the default date format in your command file.
     • File naming: Put your referential integrity constraint in a command file named referential.sql. Put your check constraint in a command file named check.sql. If you are submitting additional referential integrity or check constraints (no extra credit for these!), name the files referentialN.sql and checkN.sql for N=1,2,3,...

  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. In addition to creating triggers, you must write and execute a set of queries to check that all constraints you are enforcing by triggers hold in a given state of the database. Each constraint-checking query should return an empty result if and only if the corresponding constraint holds.

     Run your constraint-checking queries. 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 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.) A constraint being enforced by triggers may require only one trigger for correct enforcement, or it may require more than one trigger, depending on what type of database changes can cause the constraint to become violated.

     For each constraint, find at least one modification where a trigger is activated, detects a constraint violation, and raises an error. Also find at least one modification where one or more triggers are activated but the constraint holds so no error is raised -- for each activated trigger, the WHEN condition fails, or the trigger action detects that the constraint holds.

     When you have fully tested your triggers, create a separate command file for each of your constraints that is being enforced by triggers. The command file should have the following format:

         drop trigger ... ;
         drop trigger ...; /* if constraint requires more than one trigger */
         ...
         create trigger ...;
         create trigger ... ;
         ...
         select  ... ; /* query verifying constraint on current database */
         insert/delete/update ... ; /* modification that fails */
         insert/delete/update ... ; /* modification that succeeds */
         drop trigger ... ;
         drop trigger ...;
         ...
         quit;
     

     You should fill in the details of the trigger statements, constraint-checking query, and database modifications. Make sure your select query for verifying the constraint on the current database returns an empty result if and only if the constraint holds. Remember to put the modification that fails the constraint first. Name the command files for your two constraints enforced by triggers trigger1.sql and trigger2.sql. If you submit additional constraints enforced by triggers (no extra credit!) call the files trigger3.sql, trigger4.sql, etc.

A note about transactions

Having trouble?

What to submit

One exception is that you may submit additional referential, check, and trigger command files as described earlier.

Important: If you decided to change your database schema while doing this part of the project, you must also resubmit ALL of the relevant files from Project Part 1: parser, Makefile, runParser script, control files, etc.. Refer back to the Project Part 1 Specification for details on the files to submit.

As usual, from your submission directory execute the script:

  /afs/ir/class/cs145/bin/submit-project

Note: Points will be deducted if you do not follow the submission procedures exactly as specified, including which files to include (and which not to include), file naming, and file contents. Remember to allow sufficient time to prepare your submission once your work is complete.