. The record of this excess debt is generated automatically the first time the l

Question

. The record of this excess debt is generated automatically the first time the loan for any particular book loan exceeds $100. For this assignment you will write 2 stored procedures:

1. For all inserts into the Loan table

2. For all updates of the fees in the Loan table (library fees on unreturned books are charged weekly)

You will also implement one trigger:

1. Record of excess fees (> 100) for each customer loan of a book

Your trigger Book Loan Trigger

The library loans books to consumers and charges fees when books are not returned on time. If the fees owed to the library for any particular book are in excess of $100, a record of that debt is made and the librarian either pursues the payment of that debt or chooses to freeze the consumers accountshould be monitoring the Loan table and automatically inserting a record into the excessDebt table when the fees for a book initially exceed $100.

It is important to realize the Loan table only represents outstanding books on loan. Once a book is returned the record of that book loan is archived to a different table and the record of the past loan is deleted from the Loan table. This means that each record in the Loan table represents an EXISTING loan of a book to a consumer.

Important: Your trigger should only fire once when the fee for the book initially exceeds $100. The fees for that book, charged to the consumer, may continue to increase over time, represented in the Loan table, but you will only place one record of the excess in the excessDebt table for the librarian to handle. You only need to record this event, all actions are taken separately by the librarian.

Make your trigger efficient by correctly defining the timing and granularity of the trigger and using the WHEN clause.

Create a table named Loan with the following columns:

Book_ID-- varchar identifier representing single copy of book in library

Member_ID -- integer identification of member

Fees-- number representing current total fees for the book

Create the following constraints on the Loan table:

Primary key

Create a table named excessDebt with the following columns:

Debt_ID -- number generated via a sequence

Book_ID -- varchar identifier representing single copy of book in library

Member_ID -- integer identification of member

Amt_Owed -- number representing amount owed

Record_date -- Date datatype

Create the following constraints on the excessDebt table:

Primary and foreign keys

Fees must be greater than one hundred

Create a sequence to generate the Debt_ID number

Develop a DDL script that creates and constrains the Loan and excessDebt tables, creates a sequence for entries into the excessDebt table and be sure to drop the tables at the start of the script. When you have that working, move on to creating the stored procedures. Continue to use ONE script for this assignment.

Next, create a trigger on the Loan table that inserts a record into the excessDebt table when the Fees amount for a book exceeds $100. You may use the Oracle SYSDATE when inserting a date into the excessDebt table. The Amt_Owed value comes from the fees value as part of the triggering update.

Include DML statements that will test your stored procedures and trigger. Use your stored procedures to create loans and update fees. Include select statements after each stored procedure executes to ensure the tables were updated/created correctly. Be sure include tests outlined below.

Fees updated but leaves the fee amount less than $100.

Fees updated to indicate an amount greater than $100 (example $102 total fees).

Fees updated to indicate am amount greater than fees in #2 (example $120 total fees).

Explanation / Answer

2)when cluase:DML

----------------------------------------

end;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.