Docstoc

assignment 4

Document Sample
 assignment 4 Powered By Docstoc
					                                          COSC 344
                                         Assignment 4

Overview

In this assignment, you will write a PL/SQL procedure and a trigger.

Due date for assignment 4

Monday, 27 September 2010 at 4 PM

Description

A bit of creativity is needed for this assignment. It is particularly suited to:

   - maintaining derived data in existing tables or a new table
   - enforcing "business" rules
   - enforcing integrity constraints not conveniently done with the normal table constraints
       provided by SQL.

These are only suggestions; there are many other possibilities.

PL/SQL procedure – Write a PL/SQL procedure of your choosing. It should include at least
one loop or if-then-else statement or a cursor. Put the procedure into a script, called proc.sql,
which will allow the procedure to be loaded into the database.

Trigger – Write a trigger of your choosing. It should maintain at least one derived attribute.
Put the trigger into a script, called trig.sql, which will allow the procedure to be loaded into
the database. Think carefully about the various actions that will cause the derived attribute to
change.

NOTE – Either the procedure or the trigger must have an exception clause.

Write a short description of the purpose of your PL/SQL procedure. Include simple operating
instructions. If the procedure takes any arguments, include a sample input that causes it to
execute successfully. If your procedure raises an exception, include instructions on how to
get it to raise the exception.

Write a short description of the purpose of your trigger. Include 2 SQL commands that cause
it to fire. If your trigger includes an exception, include instructions on how to get it to raise
the exception.

When you are done, create a subdirectory called cosc344_asgn4 in your home directory.
Put your proc.sql and trig.sql files into that directory. Also include your
load.sql file that drops tables, creates tables and loads data into your tables.
What to turn in

1. A short description of your PL/SQL procedure and trigger with operating instructions.
2. Print out of proc.sql and trig.sql.
3. If you have altered or augmented your tables from assignment 2 or 3, give a brief
   description of the changes made.
4. The directory, cosc344_asgn4, with the required files. Submit your files using the
   COSC344 submit script. Type

     submit344       cosc344_asgn4

   from your home directory. The script displays its progress so you can see that it has
   worked. You can resubmit before the due date if you wish.


If you are late

If you do not complete on time, email Raymond after you have made your late submission so
he knows to collect it for marking. The turn-in time will be the latter of the email time, the
file dates, or the time when the printed material is turned in.

Marking

This assignment is worth 7% of your final mark. It will be marked out of a total of 31 marks
as follows:

       Marks          Item
         4            Overall creativity
        10            PL/SQL procedure
        10            Trigger
         5            Write up
         2            Following instructions

				
DOCUMENT INFO