Oracle

Objective:

In this assignment, students will design a Library Management System using Oracle SQL, implementing complex database operations such as stored procedures, views, functions, and triggers. Students will implement SQL objects such as stored procedures, views, triggers, and functions aligned with UI mockup displayed at the bottom of this webpage. Students are required to utilize the tables created during the Chapter 5 DDL assignment.

Requirements:

• Complete previous DDL, DML, and DQL assignments for table structure usage.
• Each SQL object (view, procedure, function, trigger) must be submitted as a separate .sql file, with the filename matching the object’s name.
• Provide a report explaining each SQL object’s design and how it connects to the proposed UI elements.

Assignment Tasks:

Stored Procedure Tasks:

1. Stored Procedure for Book Details

   • Write a stored procedure to retrieve all book details, including title, author, and isbn, for the Book Management screen’s data grid.

2. Stored Procedure for Book Inventory

   • Write a stored procedure to insert new book records into the Book table and update the BOOK_INVENTORY table accordingly.

3. Stored Procedure for Book Returns

   • Write a stored procedure to update the book return information.

4. Stored Procedure for Membership Payments

   • Write a stored procedure to handle inserts and updates for membership payment management. The procedure should insert a record into the MEMBERSHIP_PAYMENT table and update the membership_expiry_date in the MEMBERSHIP table.

5. Stored Procedure for Fine Collections

   • Write a stored procedure to handle fine collections. Compute the fine using the daily fine rate from the Fine Policy table, insert the fine into the FINES table, mark the book as returned in the Loan table, and record the return branch ID.

6. Stored Procedure to retrieve Membership History

   • Write a stored procedure to retrieve membership history, including the member's name, current membership status (if any), number of books borrowed, late return count, on-time return count, total fine paid, number of times the membership was renewed, total amount paid to date, and the current membership status (expired or valid for X days)..

7. Stored Procedure for Book Returns

   • Write a stored procedure to retrieve a popular book analysis, including branch, book title, author, number of times loaned, female borrower count, male borrower count, inventory count, and the number of idle days the book spent in the library.

8. Stored Procedure for Daily Operations

   • Write a stored procedure to retrieve daily operations information by listing all calendar dates for a given month. For each date, include the total number of books borrowed, total books returned, total membership amount collected, total fines paid, new member signups, and the count of new books purchased. Ensure all calendar dates are included, even if no data exists for a given date, by using the Dim Date table with a LEFT JOIN. You will have to use temporary table concept to complete this store procedure.

View Tasks:

1. View for Member Overview

   • Create a view to display ALL valid calendar dates for membership type of a given member.

2. View for Publisher Inventory

   • Create a view to retrieve publisher inventory, including Publisher, Book Category, Title, and Book Count.

3. View for Book Inventory

   • Create a view to fetch book inventory details, including Branch, Title, Category, Available Count, and Loaned Count.

4. View for Barrowed Books Analysis

   • Create a view to retrieve information about loaned books, including Customer Name, Book Title, Pickup Date, Due Date, Return Date, Fine, and Status.

Function Tasks:

1. Function to Calculate Total Fines per Member

   • Write a function that calculates the total fines owed by a member.

2. Function to Calculate Available Books in Branch

   • Write a function to calculate the total number of available copies of a specific book across all branches.

Trigger Task:

1. Trigger on Loan Return Date Changes
   • Create a trigger on the LOAN table that logs changes to the return_date field into the AUDIT_LOG table. Include the loan_id, old_return_date, new_return_date, and employee_id.

Submission Guidelines:

• Each SQL object (procedure, view, function, trigger) should be submitted as a separate .sql file named after the object.
• Provide a report detailing the design and implementation of each SQL object and how it relates to the UI functionality.

This assignment will help students create and manage a Library Management System using Oracle SQL, focusing on building complex database objects that support the system’s functionality through advanced queries and operations.

UX Screens for Library Management System Mockup