• Submission and Late Policy: Programming work is submitted electronically and must be submitted by Monday at 11:59pm. Programming work submitted after the deadline but less than 24 hours late (i.e., by Tuesday 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 Wednesday 11:59pm) will be penalized 30%. No programming work will be accepted more than 48 hours late. Since emergencies do arise, each student is allowed a total of four unpenalized late days (four periods up to 24 hours each) for challenge problems, query assignments, and programming work, but 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 all of your submitted work 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.

Important: Activate CGI Service for Part 3

Another Important Reminder

As discussed for Project Part 1, it is critical that your MySQL tables use the "InnoDB" storage engine -- otherwise this part of your project will not work properly. InnoDB is the default storage engine for Windows installations of MySQL, but not for Linux, and therefore not for the Stanford machines. Make sure to include engine=innodb with every create table command.

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.

Create a file named time.sql that contains your commands for creating and initializing your current time table and for selecting the current time. Your file should have the following form:

    drop table if exists Time;
    create table Time(currenttime datetime) engine=innodb;
    insert into Time values (str_to_date(...));
    select date_format(...) from Time;
    quit

• Do not put a semicolon after quit, but do put a newline after quit.
• Be sure to use the datetime type in your Time table. For more info, see Date and Time Functions in the MySQL Manual.
• In the str_to_date and date_format functions, use the format string '%b-%d-%y %H:%i:%s'.
• Do not remove the select date_format... line from your file; we rely on it for grading.

Part B: Procedures

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, you are 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, in computing the recursive bidding closure of seller Jennifer, if Karl placed a bid on one of Jennifer's items, and Haider placed a bid on one of Karl's items, then Karl and Haider 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.

Create a command file named closure.sql containing the command to create your closure relation, your MySQL procedure, and a command for invoking your procedure with parameter 'westbur'. To facilitate the grading process, your closure.sql file must have the following format:

     drop procedure if exists compute_closure;
     drop table if exists my_closure;
     create table my_closure (...);
     set @@max_sp_recursion_depth=255; # enables recursion
     delimiter | 
     create procedure compute_closure (...)
     begin
        ...
     end |
     delimiter ; 
     call compute_closure(...);
     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

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.
• No auction may have two bids at the same 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. Also do not include non-null constraints in your list -- you would probably need to enumerate a lot of them. Other than non-null constraints and 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.

• In your constraints list, mark with a "K" those that can be implemented using primary key or unique constraints.

• In your constraints list, mark with an "R" those that can be implemented using referential-integrity (foreign key) constraints.

• Choose at least three of the remaining constraints in your list to implement using triggers. (If you don't have three constraints in addition to keys and referential-integrity, your list is probably incomplete.) Mark the chosen constraints with a "T". Feel free to implement more than three, but you will not receive extra credit.

• 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 all constraints marked "K" or "R" to your database. (You may have included some of these constraints already in your table definitions from Part 1 -- for those, simply make a note in constraints.txt.) The format for adding these types of constraints is:

        alter table <table-name> add constraint <name> primary key (<columns>)
        alter table <table-name> add constraint <name> unique (<columns>)
        alter table <table-name> add constraint <name> foreign key (<columns>) references <reference-table> (<columns>)
     

     The <name> is included to facilitate dropping the constraint and must be unique across all constraints. For referential-integrity constraints, you will need to decide whether you wish to specify CASCADE or SET NULL, or use the default RESTRICT, for each of DELETE and UPDATE. See the ALTER TABLE and FOREIGN KEY Constraints pages in the MySQL Manual provide full details.
     Important: The "alter table" command will fail if you try to add a constraint that does not hold on the current database. 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!
     When you have successfully tested your constraints, for each one choose one modification where the constraint is violated, and one modification where constraint-checking is activated but the constraint holds. Then create the following files for submission:
     • File constraints_add.sql should add all of the "K" and "R" constraints to your database. It should have the following format:

           # description: <constraint1-description>
           alter table <table-name> add constraint <constraint1-name> ...;
           
           # description: <constraint2-description>
           alter table <table-name> add constraint <constraint2-name> ...;
           ...
           quit
       

       Each constraint description is a short comment and need not be more than a single line.

     • File constraints_good.sql should contain one modification for each of your "K" and "R" constraints. The modification should be relevant to the constraint but should not cause a violation. The file should have the following format:

           insert/delete/update ...; /* modification for constraint1 */
           insert/delete/update ...; /* undo the modification */
           
           insert/delete/update ...; /* modification for constraint2 */
           insert/delete/update ...; /* undo the modification */
           ...
           quit
       

       For grading purposes (and also useful for testing), it is important that your modifications are undone to leave the database in its original, intact state. All modifications should be undone completely within this file, even if doing so involves executing multiple commands.

     • File constraints_bad.sql should contain one modification for each of your "K" and "R" constraints, causing the constraint to be violated. The file should have the following format:

           insert/delete/update ...; /* modification violating constraint1; should produce an error */
           insert/delete/update ...; /* modification violating constraint2; should produce an error */
           ...
           quit
       

       Since each of these modifications violates a constraint and generates an error, it should not be necessary to undo any modifications to leave the database in its original state.

     • File constraints_drop.sql should remove each of the constraints you added in constraints_add.sql:

           alter table <table-name> drop ...; /* drop constraint1 */
           alter table <table-name> drop ...; /* drop constraint2 */
           ...
           quit
       

  2. For your "T" constraints, unlike keys and referential-integrity, no condition is checked on the current database when a trigger is created. Thus, 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. Thus, in addition to creating triggers, you must write separate "constraint-checking queries" to check that the 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. Again, 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. 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 marked "T", create one or more triggers using the "create trigger" command described in the MySQL Triggers help document. If a trigger discovers that a constraint is violated, it should raise an error, as described in the MySQL Triggers help document.

     For each "T" constraint, find at least one modification where a trigger is activated, detects a constraint violation, and either repairs the constraint or raises an error. Also find at least one modification where one or more triggers are activated, but the trigger action determines that the constraint still holds.

     When you have fully tested your triggers, create two command files for each of them, named triggerN_add.sql and triggerN_drop.sql for N=1,2,3. (Let N=4,5,... for your extra constraints if you have them, but no extra credit for these.)

     MySQL trigger limitations: As discussed in the MySQL Triggers help document, MySQL allows only one trigger per combination of {BEFORE|AFTER}, {INSERT|UPDATE|DELETE}, and <table_name>. It's simplest if the three constraints you pick do not require any of the exact same triggering events. If they do, you will need to enforce multiple constraints in the same trigger(s). In that case, please include the same trigger(s) in multiple triggerN_add.sql and triggerN_drop.sql files, for our grading purposes.

     • Files triggerN_add.sql should have the following format. Make sure your select query for verifying the constraint on the current database returns an empty result if and only if the constraint holds.

           # description: <constraint_description>
           drop trigger if exists <trigger_name>;
           delimiter | 
           create trigger <trigger_name>
           {before|after} {insert|update|delete} ON <table_name>
           for each row
           begin
              ...
           end |
           delimiter ;
           ... /* add more triggers as needed  */ 
           select  ... ; /* query verifying constraint on current database */
           insert/delete/update ... ; /* modification that violates constraint; should fix constraint or produce an error */
           quit
       

     • Files triggerN_drop.sql should have the following format:

           insert/delete/update ... ;  /* modification that activates trigger but constraint still holds */
           insert/delete/update ... ;  /* undo the modification */
           drop trigger <trigger_name>;
           ... /* drop additional triggers */
           quit
       

       In triggerN_drop.sql, even though the modification does not violate the constraint, make sure to undo it so that we are able to retest your code from the original state if necessary.

Having trouble?

What to submit

Again, your submission directory should contain only these listed files. (One exception is that you may submit additional triggerN_add and triggerN_drop command files as described earlier.) As usual, from your submission directory execute the script:

  /usr/class/cs145/bin/submit-project

If you changed Part 1: If you changed any of your tables from what you submitted in Project Part 1, then you will need to resubmit your files from Part 1, so that we can properly test and grade your Part 2. Please make a prominent note in constraints.txt, and include your files from Part 1 along with the files listed above for this part. Do not resubmit your Part 1 files unless the database itself has changed.

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