Project Guidelines, Part 4
When completing this deliverable,
you will focus on constraints and functions in PL/pgSQL.
- (2pt) Include everything from the previous project submissions.
- (4pt) For each of the relation schemas of your project, declare
You probably have already identified keys,
and some of you had previously specified keys in your schema.
But in your last deliverable,
you were instructed to remove them if they had been defined.
For this deliverable,
you should either restore them or add
the declaration of keys for all relations
in your database schema (i.e., createschema.sql).
you should declare at least one foreign-key constraint for some relation.
Even if you decided that no such constraints should logically hold in your domain,
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.
Note: you may also need to update your populatesmall.sql and your data-generation program
to account for your key declarations
and your foreign-key constraints.
If so, re-generate the data files for your populatelarge.sql script
and include them in your submission accordingly.
- A suitable key for the relation.
- Any foreign key (referential integrity) constraints that
should be enforced for the relation.
- (4pt) 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.
Also note: you may need to update your populatesmall.sql and your data-generation program
to obey the checks that you are adding here.
In all cases, note your changes in your README under createschema.sql
and data generation as appropriate.
- (5pt) 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.
Be imaginative. Be creative.
However, here are some sorts of things you might try
if you can't think of something more interesting on your own:
Also provide a query that exercises your function.
- Compute some aggregate value from a relation
and use that value to modify values in that or another relation.
- Enforce a constraint by searching your database for violations and fixing them in some way.
- (5pt) 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 from the previous requirement,
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.
Between your trigger function and the function you wrote for the previous item,
at least one of these should utilize a cursor.
Also include a query in this file that exercises your trigger.
As with your last project component,
please create a gzipped tar (.tgz) 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 tgz/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.tgz’ and it should expand to
create a new directory called ‘henryp4’.
This directory should contain the files from previous projects:
your proposal with conversion to relations and FD's, E/R diagram,
the modified createschema.sql reflecting the changes described above,
the dropschema.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 and modifications from prior versions.
Do not forget to update your dropschema.sql file
with CASCADE options as appropriate.