Project Guidelines, Part 4

In completing this deliverable, you will focus on constraints and functions in PL/pgSQL.


  1. For each of the relation schemas of your project, indicate
    1. A suitable key for the relation. (sorry Daniel, this was not added in response to the exchange at the end of class.)
    2. Any foreign key (referential integrity) constraints that you expect will hold for the relation.
    You probably have already identified keys, and some of you have already specified keys in your schema. But in your last deliverable, you may have found that you needed to remove those specifications in order to see the time savings resulting from the indexes. In any event, 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). Your create-schema script should reflect these changes; document these changes in your README file identified as this task.
  2. Add two attribute-based and at least one tuple-based CHECK constraints to relations of your database schema. These changes should be reflected in your create-schema script. Note, our version of PostgreSQL does not support sub-queries in your checks. That is OK, just create sensible attribute and tuple-based checks that do not rely on sub-queries.
  3. Write one PL/pgSQL function (See the PL/pgSQL Documentation) to perform some operation on your database. The function should be nontrivial, illustrating one or more features such as local variables, multiple SQL statements, loops, and/or branches. In addition, at least one of your functions should utilize a cursor. Be imaginative. However, here are some sorts of things you might try if you can't think of something more interesting on your own:
    1. Compute some aggregate value from a relation and use that value to modify values in that or another relation.
    2. Create a new relation and load it with values computed from one or more existing relations.
    3. Enforce a constraint by searching your database for violations and fixing them in some way.
  4. Write one PostgreSQL Trigger. For your trigger, you will need to write a second PL/pgSQL stored function before creating the actual trigger. For your trigger and function, as well as the function frem the previous item 3, place the code in the file ‘triggerfunctions.sql’. Do not forget to document your code. Refer to the documentation and/or class notes for the particular idiosyncrasies of triggers in PostgreSQL.

Submission Instructions

As with your last project component, please create a tar or zip file that expands to create a directory identified by your Westmont email user name (same as your database name) followed by ‘p4’. 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 ‘henryp4.tar’ and it should expand to create a new directory called ‘henryp4’. This directory should contain the files from previous projects: your proposal, E/R diagram, the modified createschema.sql reflecting the changes described above, the destroyschema.slq, etc., and the triggerfunctions.sql file for this part. Also, include a README file that lists all your files with brief descriptions of what they contain.