As with your last project component, please create a tar or zip
file that expands to create a
identified by your Postgres user name followed by "p5". 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
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
Part 6 Deliverables
For each of the relation schemas of your project, indicate
A suitable key for the relation.
Any foreign key (referential integrity) constraints that you
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
relations and at least one foreign-key constraint for some relation
if you decided that no such constraints should logically hold -- we
almost every project will have some natural foreign-key constraints).
us the resulting database schema and the result of successfully
these relations to the database system.
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
You may combine this part with the previous part if you like, to avoid
repeating the schema. Note,
our version of PostgreSQL may prevent you from using sub-queries in
your checks. That is OK, just create sensible attribute and
tuple-based checks that do not rely on sub-queries.
Write one PL/pgSQL function (See the PL/pgSQL
to perform some operation on your database. The function should
be nontrivial, illustrating a feature or features such as
local variables, multiple SQL statements, loops, and/or branches.
In addition, at least one of your functions (between #3 and #4) 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:
Compute some aggregate value from a relation and use that
value to modify values in that or another relation.
Create a new relation and load it with values computed from
one or more existing relations.
Enforce a constraint by searching your database for
and fixing them in some way.
Submit a listing of your programs and scripts showing them
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.
Write one PostgreSQL Trigger.
As always, use the documentation
as necessary for the special idiosyncrasies of triggers in
PostgreSQL. For your trigger, you will need to write a
PL/pgSQL stored function. This function should
involve more than one SQL statement.
Submit your code and a script showing the function and trigger
Also, the script should show the effect of a database modification that
exercises the trigger.
In your script, include queries that demonstrate the trigger's
of the material for the project descriptions and assignments has been
taken from Professor Ullman's website.