Scribd
Upload
234 views

Module002 LaboratoryExercise002

This document describes a laboratory exercise for an Advanced Database Systems course involving the Pine Valley Furniture Company database. Students are instructed to create PL/SQL programs, procedures, functions, triggers to perform various validation checks and logging of actions on the database tables. This includes validating dates, checking product quantities, and logging user events to an EVENTLOGS table. Students are to test the various triggers and programs by performing data manipulation operations on the database tables.

Uploaded by

Joaquin Smith
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
234 views

Module002 LaboratoryExercise002

This document describes a laboratory exercise for an Advanced Database Systems course involving the Pine Valley Furniture Company database. Students are instructed to create PL/SQL programs, procedures, functions, triggers to perform various validation checks and logging of actions on the database tables. This includes validating dates, checking product quantities, and logging user events to an EVENTLOGS table. Students are to test the various triggers and programs by performing data manipulation operations on the database tables.

Uploaded by

Joaquin Smith
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Course Code MIT412

Description Advanced Database Systems


College / Department:
School of Graduate Studies Lab Exer No. 2
Online Education

LABORATORY EXERCISE Page 1 of 2

For this laboratory exercise, you will use the same database schema of the Pine Valley Furniture Company.
The database schema is provided for you, which can be downloaded from this module.

Construct the following PL/SQL programs and be able to provide a screenshot of the output.

1. Create a procedure that displays the name of the customer with the highest amount of orders among
all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function
called GET_HIGHORDER_FUNC that retrieves the name and total amount ordered of customer with the
highest amount of order. Invoke the procedure and view the results.

2. Create a trigger called VALIDATE_DATEHIRED_TRIG to validate the hired date of an employee when a
new row is added to the EMPLOYEE table. If the employee hired date is more than current date,
display an appropriate message that prompts the user regarding the error and include an exception.
The new row will be inserted to the table only if the hired date is valid. Test the trigger by inserting a
new row to the EMPLOYEE table with an invalid hired date.

3. Do the same in Item #1 to validate employee’s date of birth. This time you create a procedure called
VAL_BDAY_PROC that will validate the employee’s date of birth. The procedure accepts the date of
birth as a parameter. Then create a trigger called VALIDATE_BDAY_TRIG that invokes the
VAL_BDAY_PROC. Test the trigger by inserting a new row to the EMPLOYEE table with an invalid
birthday.

4. Modify the PRODUCT table by adding a new column named PRODUCT_ONHAND with data type
NUMBER(3,0). Add the value 10 in each row of the PRODUCT_ONHAND column. Create a function
called GET_QUANTITY_FUNC that retrieves the on hand value of a specific product. Create a procedure
called CHECK_ONHAND_PROC that ensures that the quantity ordered by customer is less than or equal
to the on hand value of the product. Invoke the GET_QUANTITY_FUNC and CHECK_ONHAND_PROC
inside a trigger and name it as CHECK_ONHAND_TRIG. If the quantity ordered by the customer is not
greater than the quantity on hand, update the column PRODUCT_ONHAND in the PRODUCT table by
subtracting quantity ordered from product on hand. Include exception handling and test the trigger by
inserting new rows to the ORDERLINE table with ordered quantity 11 and 5.
Course Code MIT412

Description Advanced Database Systems


College / Department:
School of Graduate Studies Lab Exer No. 2
Online Education

LABORATORY EXERCISE Page 2 of 2

5. Create a table called EVENTLOGS with the following attributes:

Column Data type Constraint Description


Eventlog_id Number(3,0) Primary key A sequence of number that identifies the user
event logs
User_name Varchar2(20) The name of the user logged in to the database
Date_done Date Date when the user logged in to the database
Time_done Timestamp Time when the user perform an operation to the
database
Action_done Varchar2(50) The action performed by the user to the
database
Object_name Varchar2(30) The table from which the action was done

Create a sequence called EVENTLOG_ID_SEQ to generate an automatic id number for the


EVENTLOG_ID column whenever the trigger is fired. START WITH 1 INCREMENT BY 1 NO CYCLE.

Create an AFTER trigger that records all the information about the action performed by the user who
logged in to the database. Perform at least 10 different DML operations on the PVFC database to test
the trigger. Then display the content of the EVENTLOGS table. Sample output:

Eventlog_id User_name Date_done Time_done Action_done Object_name


1 System 6/22/2016 7:00pm Insert ID 101 EMPLOYEE
2 System 6/22/2016 7:22pm Update salary EMPLOYEE
3 HR 6/22/2016 7:30pm Delete ID 10 PRODUCT

You might also like