CS125 Project, Part 6

Due Monday, November 22, 2004

[note non-standard due-date]

Submission Reminders

As with your last project component, please create a tar or zip file that expands to create a directory identified by your Postgres user name followed by "p6".  Please name the tar/zip file in the same way (followed by the appropriate file extension).  For example, if your postgreSQL user-name is "henry", your tar file should be called "henryp6.tar" and it should expand to create a new directory called "henryp6".  This directory should contain the relevant files, appropriately named, required for your deliverable.  For each task, you should have an input script file (.sql extension) and an output file.

As before, include the table creation and initialization scripts with your submissions.

Part 6 Deliverables
  1. For each of the relation schemas of your project, 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 project 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.

  3. Add two attribute-based and two tuple-based CHECK constraints to relations of your database schema.  Show the revised schema, its successful declaration, and the response of PostgreSQL to inserts that violate the constraints. You may combine this part with the previous part if you like, to avoid repeating the schema.
  4. Write two PL/pgSQL functions (See the PL/pgSQL Documentation) to perform operations on your 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.

    Submit 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 project that was changed by the program. The queries demonstrating the effects may be included in the file that holds your PL/pgSQL programs for convenience, but the results or output should be in an appropriate output file.

  5. Write two PostgreSQL Triggers.  As always, use the documentation as necessary for the special idiosyncrasies of triggers in PostgreSQL.  For your two triggers, you will need to write two PL/pgSQL stored functions.  At least one of these functions should involve more than one SQL statement.  Both should use one or more parameters in a significant way.

    Submit your code and a script showing the functions and 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. In your script, include queries that demonstrate the trigger's effect in the first case and non-effect in the second.

Acknowledgements:  Most of the material for the project descriptions and assignments has been taken from Professor Ullman's website.