Create the required tables

Assignment Help Other Subject

Reference no: EM132765909

CP5503 Enterprise Database Systems - James Cook University

Oracle Assignment

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

Reference no: EM132765909

Questions Cloud

Create an appropriate greeting and closing for each email : Create an appropriate greeting and closing for each email and Provide the following heading on page one of your email and then begin your email

Which the income tax expense for the second quarter should : Which the income tax expense for the second quarter should be computed by using the? Effective tax rate expected to be applicable for the second quarter

Make the general journal entry to record the sale of bonds : On January 1, 2020, Flynn Corporation sold $600,000 of 8% bonds at $624,000. The bonds are due on January 1, 2025 and pay interest semiannually every July 1.

Explain opposed to a centralised nervous system : An animal possessing a nerve net (as opposed to a centralised nervous system) would also be expected to possess which of the following features?

Create the required tables : Create the required tables. Oracle Assignment in ass2_sql_files.zip. Create a function called FUNC_is_available_copy to check if a copy is available or not.

Which should a provision be recognized : Which should a provision be recognized? Reductions in fair value of financial instruments. / Obligations for plant decommissioning costs

Compute the impairment loss : Company A acquired Company B for $50,000,000 on January 1. The fair value of Company B's accounts are the following: Cash $2,500,000.

Which statement is true in relation to bearer plant : Which statement is true in relation to bearer plant? The agricultural produce is usually presented as current asset unless takes more than one year to mature.

What is the relative position : What is the relative position between the heart and the head when you are lying down?

User Account

All Pages