I need help with database 1-Book Loan Trigger DBMS Oracle sql and all the requir
ID: 3606358 • Letter: I
Question
I need help with database 1-Book Loan Trigger
DBMS Oracle sql and all the requirements are needed and 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 account. The record of this excess debt is generated automatically. You will implement the recording of this excess debt with a trigger that monitors the Loan table and automatically inserts a record into a new Debt 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.
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, but you will only place one record of the excess in the Debt 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-- character book 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 Debt with the following columns:
Debt_ID -- number generated via a sequence
Book_ID -- character book 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 Debt 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 Debt tables and be sure to drop the tables at the start of the script.
Create a trigger on the Loan table that inserts a record into the Debt table when the Fees amount for a book exceeds $100. You may use the Oracle SYSDATE when inserting a date into the Debt table. The Amt_Owed value comes from the Fees value as part of the triggering update.
Include DML statements that will test your trigger. You will need to first insert a record into the Loan table with an initial fee less than $100. After the newloan has been inserted include statements that update the Loan table to execute each of the three test cases below.
Fees updated but leaves the fee amount less than $100.
Fees updated to indicate an amount greater than $100 (example $102).
Fees updated to indicate am amount greater than fees in #2 (example $120).
Explanation / Answer
--Dropping table Loan if exist.
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE Loan’;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
--Dropping table Debt if exist.
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE Debt’;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
--Creating table Loan with Book_ID and Member_ID as primary key.
CREATE TABLE Loan
(
Book_ID VARCHAR(10),
Member_ID NUMBER,
Fees NUMBER,
PRIMARY KEY (Book_ID, Member_ID)
);
--Creating table Debt with Debt_ID as Primary key in which value is inserted using a sequence. --Book_ID is Foreign key referring to Book_ID of Loan table. Check constraint is applied on --Amt_Owed.
CREATE TABLE Debt
(
Debt_ID NUMBER,
Book_ID VARCHAR(10),
Member_ID NUMBER,
Amt_Owed NUMBER,
Record_Date DATE,
CONSTRAINT Debt_Pri PRIMARY KEY (Debt_ID),
CONSTRAINT Book_ID_F FOREIGN KEY (Book_ID) REFERENCES Loan (Book_ID),
CONSTRAINT Fee_Check CHECK (Amt_Owed > 100)
);
--Creating Sequence Debt_Seq with Minimum value 1 and increment of 1. This sequence is --used to insert value of Debt_ID in Debt table.
CREATE SEQUENCE Debt_Seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.