Reference no: EM132620190

CP5503 - Enterprise Database Systems - Oracle Assignment - James Cook University, JCU, Australia

Assignment Part - Trigger

Firstly, you have to setup the user lib_jcxxxxxx account and create the required tables. To do so, you must use the 3 given files in ass2_sql_files.zip. You should perform the following steps:

1. Run SQL Developer

2. Connect Oracle DB by the user system

3. Open the script create_user.sql and change jc000000 to your jc username, for example, jc123456

4. Run the script create_user.sql

5. You can now disconnect the system connection

6. Connect Oracle DB by the user lib_jcxxxxxx

7. Open the script lib_tables.sql and run it

Note: lib_tables.sql creates tables with basic constraints, no additional constraints

8. Open the script insert_records.sql and run it

If there is anything wrong, you can always re-run the 3 files again.

You have to login as a lib_jcxxxxxx user to write SQL and PL/SQL to define triggers, stored procedures/functions as specified in the following subsections. You should also include SQL/PLSQL statements to test the triggers and procedures/functions that you must implement. Make sure that you test single row as well as multiple row actions. Include comments for each testing statement to indicate the trigger or the procedure/function in which it applies and the expected results of the trigger firing or procedure/function executing.

Task 1 - TR_date_returned_chk trigger

You should create a trigger called TR_date_returned_chk (Loan table) to compare date_returned to the current system date before updating a row in Loan table. If date_returned is greater than the current system date then raise an application error.

Task 2 - Create FUNC_is_available_copy function

Create a function called FUNC_is_available_copy to check if a copy is available or not. This function should contain the following input parameter: copy_id_p. It should return 1 if available and 0 if not available.

Task 3 - Create TR_add_loan trigger

You should create a trigger called TR_ add_loan. This trigger fires before inserting a row in the Loan table. If the copy_id is not available then raise an application error. Hint: Use the function FUNC_is_available_copy.

Task 4 - Create FUNC_get_book function

Create a function called FUNC_get_book to retrieve book_id, title, shelf_letter, call_number and no_of_copies with a given isbn. This function should contain the following input parameter: isbn_p; the following output parameters: title_p, shelf_letter_p, call_number_p and no_of_copies_p. It should return the book_id if found and -1 if not found.

Task 5 - Create TR_insert_or_update_book trigger

Create a trigger called TR_insert_or_update_book. This trigger fires after inserting or updating a row in the Book table. For inserting, it will add its corresponding copy records into Copies table. If a book has 5 copies then 5 copy records will be added into the Copies table. The copy_number in the Copies table must be between 1 and no_of_copies. For updating, if the new value of no_of_copies is less than the old value then raise an application error. If the new value of no_of_copies is greater than the old one then write a log record into the Log table and add additional copy records into the Copies table as well. Hint: copy_id should be obtained by the SELECT sequence_name.NEXTVAL/CURRVAL INTO sequence_value FROM dual.

Attachment:- Enterprise Database Systems – Oracle Assignment Files.rar