Reference no: EM132077797

For this laboratory exercise, you will use the same database schema of the Pine Valley Furniture Company. The database schema is provided for you.

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.

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

Attachment:- Exercise.rar