CS145 - Fall 2013
Introduction to Databases
Assignment #5
Due Monday November 11
This assignment consists of three automated quizzes, a set of automated triggers exercises, and the second part of your programming project. Please leave time to carefully follow the Submission Instructions below, which are particularly significant for the programming project.

Automated Quizzes for Basic Material

Log into the CS145 Class2Go Site. Click on the Quizzes tab. Complete three quizzes:
As always, there are several versions of each quiz, and you are permitted to take each version up to three times. All of the versions contain the same set of questions, however the correct and incorrect multiple-choice options are different in each version. For each quiz, we will use the highest score achieved on any version of the quiz as of the due date. And remember: Automated quizzes receive no credit if submitted late.

Automated SQL Triggers Exercises

Log into the CS145 Class2Go Site. Click on the Interactive Exercises tab. Under Constraints and Triggers, complete one exercise set:
Before beginning the problems, you may want to glance through the SQLite documentation for the CREATE TRIGGER statement.

As with all of the automated exercises, the website is designed so you can interactively develop your solutions in place: each problem allows you to run your proposed solution and see a comparison between your result and the expected one. Once you're happy with your solutions, you must press Submit Answers for your score to be recorded.

If you prefer to develop your solutions offline:
The automated system tests your triggers using SQLite. Trigger capabilities and syntax differ considerably across different database systems, so if you try the exercises on a system other than SQLite, you will most likely need to make significant changes to port them to SQLite. Also note that the automated system tests your solutions by: (1) creating your trigger(s); (2) executing specific modification commands against the original database state; (3) running a query after the modifications to ensure that your trigger(s) reacted correctly; (4) restoring the original state.

Whichever way you work, don't forget to enter your solutions and press "Submit Answers" to obtain your score. You may do the exercises as many times as you like, with no time lapse required between tries. Although all scores are recorded, we will only use the highest score you achieve as of the due date. As usual, automated exercises receive no credit if submitted late.

AuctionBase Project Part 2 -- Data Integrity

Important: Activate CGI Service for Part 3

Before you will be able to begin work on Project Part 3, you will need to activate a personal CGI account on the Stanford Unix machines. Activation can take up to 24 hours -- we strongly urge you to do it now, to avoid any delays when you begin Project Part 3. Visit Stanford's Personal CGI Service page and follow the instructions. Once your CGI account is activated, you will have a new directory ~/cgi-bin/, which you will use for Part 3.


Overview

This part of the project will make use of SQLite constraints and triggers to monitor and maintain the integrity of your AuctionBase data. You will also add a "current time" feature to your AuctionBase database. Because different database systems support different capabilities for constraints and triggers, this part of your project must be implemented in SQLite, regardless of whether you are using other systems for other parts of your project.

Part A: Current Time

The original auction data that we provided for you in XML, which you translated into relations and loaded into your AuctionBase database in Project Part 1, represents a single point in time, specifically one second after midnight on December 20th, 2001 ("2001-12-20 00:00:01").

To fully test your functionality, and to simulate the true operation of an online auction system in which auctions close as time passes, you should maintain a fictitious "current time" in your database. Add a new one-attribute table to your AuctionBase schema. (Warning: Don't try to call the attribute in your table "current_time" -- it turns out that's a reserved word in SQLite.) 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: 2001-12-20 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(...);
    insert into Time values (...);
    select ... from Time;

Part B: Constraints and Triggers

Before getting started on this part, please read the help document on Referential Integrity in SQLite. You may also want to refer to the SQLite documentation for the CREATE TRIGGER and DROP TRIGGER statements, although if you've already done the automated triggers exercises then you should be in good shape. Finally you might refer to the documentation on PRIMARY KEY, UNIQUE, and REFERENCES declarations in the SQLite CREATE TABLE statement documentation. Be aware that SQLite constraints and triggers do not conform exactly to the SQL-99 (SQL2) standard, as covered in the non-demo portion of the lecture videos and in other course materials.

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:

Here is what you need to do:

Having trouble?

If you run into difficulties, before posting on the Q&A Forum or contacting the course staff, please consult the Project Part 2 FAQ Page. We will keep it up-to-date with answers to common questions.

Submission Instructions for Project Part 2

Create a submission directory containing all and only the following files:

time.sql
Command file for creating and initializing table Time as specified in Part A
constraints.txt
Constraints file as specified in Part B
constraints_create.sql
Command file for creating your tables with your key, referential integrity, and row-level CHECK constraints
constraints_verify.sql
Command file for verifying that your referential integrity constraints hold
constraints_good.sql
Command file with valid database modifications
constraints_bad.sql
Command file with invalid database modifications
trigger1_add.sql
Command file for creating first constraint-checking triggers
trigger1_drop.sql
Command file for dropping first constraint-checking triggers
trigger2_add.sql
Command file for creating second constraint-checking triggers
trigger2_drop.sql
Command file for dropping second constraint-checking triggers

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
You may resubmit as many times as you like, however only the latest submission and timestamp are saved, and those are what we will use for grading your work and determining late penalties. Submissions via email will not be accepted.

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 any changed 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.

Reminders: Late Policy and Honor Code