Reference no: EM132211484

Assignment

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 tologin 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_chktrigger

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

You should produce your answer in Microsoft Word with a heading Task2containing:
- the required trigger (PL/SQLstatements)
- testing: write 2 SQL statements to insert data into the table for testing trigger. One is for a valid record and another one is for an invalid record.
- showing the testing results

Hint:
create or replacetrigger TR_date_returned
before insert or update of date_returned on Loan
for each row
begin
if (:new.date_returned> sysdate) then
RAISE_APPLICATION_ERROR(-20000, date_returned cannot be greater than current date');
end if;
end;

-- test valid date_returned
...
-- results
...
-- test invalid date_returned
...
-- results
...

Task 2: CreateFUNC_is_available_copyfunction

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. It should return 1 if available and 0 if not available.

You should produce your answer in Microsoft Word with a heading Task2containing:
- the required function (PL/SQLstatements)
- testing:write an anonymous block (PL/SQLstatements) to call the function to test.
- showing the testing results. Both cases: available and not available.

Hint: A copy is unavailable if and only if the copy is found in the Loan table with date_returned being null.

Hint:

create or replaceFUNCTION FUNC_GET_AVAILABLE_COPY(...)
RETURN NUMBER AS
-- define a variable to return a number and initialise it to 1 (available)
-- define another variable to get a Loan's column; it's will be used in "select ... into ..." statement
BEGIN
-- write a "select ... into" statement to get a record from the Loan table having the given copy_id and
-- date_returned being null
-- if found then set the variable to 0 (unavailable)
RETURN the variable;
EXCEPTION
WHEN no_data_found THEN
RETURN the variable; -- it is 1 as defined
END FUNC_GET_AVAILABLE_COPY;

-- testing function: you can write an anonymous block of code to test the function
declare
-- a variable to pass to the function
begin
-- call the function and output the result
end;

-- testing results
...

Task 3: Create TR_add_loantrigger

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.

You should produce your answer in Microsoft Word with a heading Task3containing:
- the required trigger (PL/SQLstatements)
- testing: write 2 SQL statements to insert data into the table for testing trigger. One is for a valid record and another one is for an invalid record.
- showing the testing results

Task 4: CreateFUNC_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 andno_of_copies_p. It should return the book_id if found and -1 if not found.

You should produce your answer in Microsoft Word with a heading Task4containing:
- the required function (PL/SQLstatements)
- testing:write an anonymous block (PL/SQLstatements) to call the function to test.
- showing the testing results. Both cases: Found and not found. For having a record, should output title_p, shelf_letter_p, call_number_p andno_of_copies_p.

Task 5: CreateTR_insert_or_update_booktrigger

Create a trigger called TR_insert_or_update_book. This trigger fires after inserting or updating a row in the Booktable. 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_numberin 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;

You should produce your answer in Microsoft Word with a heading Task5containing:
- the required trigger (PL/SQLstatements)
- inserting data into the Booktable for testing trigger
- updating data on the Booktable for testing trigger
- showing the testing results

Attachment:- Assignment-Part.rar