+====================================+
          |      DATABASE SYSTEMS PROJECT      |
          |              Phase IV:             |
          |        DBMS Procedural Language    |
          |   & Stored Procedures and Triggers |
          +====================================+

     When you hand me this phase, attach updated E-R diagram and relational
schema with it..

Phase IV: Stored Subprograms, Packages and Triggers.

1. General Description

  In this phase, you are going to 
    -- study Postgres PL/pgSQL language
    -- learn and practice how to create stored procedures, functions,
       triggers and pacakges.
    -- know the purposes and benefits of stored procedures, functions,
       triggers and prackages.

  psql is the command-line application/interface  to Postgres databases.The application
  can be used to create all postgres database schema objects including stored procedures,
  functions, triggers and packages, database users, tablespaces, databases, indexes,
  constraits and et. The PL/pgSQL (similar to Oracle PL/SQL) lanugage is the language
  used to create database schema  objects mentioned above. PL in both PL.pgSQL  and PL/SQL
  means Procedural Language. PL have provide some commom programming languages' features
  that SQL language does't have such as
       - define a subprogams: procedrure, function, subroutine.
       - Selective statements: switch, case, if satements.
       - Repetive statemnts: while, for, do-while.

    Use the internet search engine to find out the syntax of statements/structures list above.
The string "PL/pgsql: control/loop/slective  statement" typed in google search engine will shorten
your search.

2. Coding Requirements

   A. Write the following stored procedure or function in PL/pgSQL:

	(1) Create three stored procedures or functions

       -- A stored procedure for inserting a new record into
	      one of your tables. The field values are passed to the
	   procedure through the input parameters.
	   -- A stored procedure for deleting a existing record
	      based on the primary key of your selected table.
	   -- A stored function which returns average of a numerical
	      fields of highest or lowest N records where N is the
	      parameter for the function.

    (2) Write three triggers

        One for deletion and update, one "instead of" trigges which
        implement at least two to three level cascaded update and deletions.
        
        Given two tables, Department and Employee, the DID in employee table
        is the foreign key indiating which departmen a employee is working
        for. 

        -- Define a "before update DID trigger on department table to 
           update DID in employee table to new DID to.
        -- Create a delete trigger for cascaded deletion also. Make sure you
           backup your data.
        -- Create a view involved two to three tables, and define a "INSTEAD OF"
           trigger so that when you update the view, the update will be perform by
           trigger, and the updates will be performed on base table.
    (3) Put all stored procedures, functions and trigger into one package.

2. Organize your report for this phase as

Phase 4: Stored Procedures, Packages, and Triggers
     General description on Pl/PgSQL and subsections.

    4.1 PL/PgSQL
       4.1.1 introduction to PL/PgSQL
       4.1.2 Advantages of PL/PgSQL
       4.1.3 control Statements( selective, looping and assignment) and their sntaxes.
       4.1.4 PL/SQL syntax of VIEW, FUNCTION, PROCEDURE, TRIGGER.

    4.2 Views and stored Subprogram for your database
       4.2.1 Views
       4.2.2 Stored Procedures and/pr Functions
       4.2.3 Testing Results of Views, Functions and/or Procedures

    4.3 Syntax of Stored Function and Procedures and Triggers of 
        Three DBMS (Microsoft SQL and MySQL and Oracle)

        4.3.1 The differences between the three languages SQL,MySQL,PLSQL
        4.3.2 The similarities between T\SQL,PL\SQL, and MY\SQL
        4.3.3 Syntax of Stored Functions and/or Procedures of the three DBMSs