Advanced Database Case Study: City-145 Library Loan System

Author: Gishor Thavakumar

Field: Database Systems, Procedural SQL

Technologies: MySQL (Triggers, Stored Procedures, Cursors, Constraints, GRANT Privileges)

---

🧩 Project Overview

This advanced database project demonstrates procedural SQL techniques applied to automate the loan management system for City-145 Library. It employs MySQL to enforce complex business rules through triggers and stored procedures, ensuring data integrity, automated fines management, membership status tracking, and advanced privilege controls.

🚀 Built as part of a advanced database systems unit, this project showcases real-world database enforcement of logic typically handled in application code.

---

⚙️ Implemented Functionalities

✅ Business Rule Enforcement via Triggers

Rule	Trigger Implementation
BR01	Availability Check: Prevent book borrowing if no copies are available.
BR02	Member Status Check: Only REGULAR members can borrow books.
BR03	Daily Borrow Limit: Members can borrow only one copy of a book per day.
BR04	Borrowing Cap: Restrict borrowing to 5 books within 21 days.
BR05	Membership Expiry Validation: Borrow return dates cannot exceed membership expiry.
BR06	Auto-Suspension: Suspend membership automatically if fines reach $30.
BR07	Fine Accumulation: Calculate overdue fines at $2/day per overdue item.
BR08	Auto-Restoration: Restore suspended membership to REGULAR upon fine clearance and item return.

Each rule is implemented using BEFORE INSERT/UPDATE triggers with error messaging via SIGNAL SQLSTATE.

---

Automated Fine Fee Calculation

A dedicated trigger calculates fines for overdue book returns:

SET total_overdue_days = DATEDIFF(NEW.DateReturned, NEW.ReturnDueDate);
SET fine_fee = 2 * total_overdue_days;
SET total_fine = fine_fee + current_fine;

---

Membership Suspension and Termination Logic

A stored procedure (EndSuspendedMemberships) automates member termination:

• Uses cursors to iterate over suspended members.
• Evaluates past suspension logs over three years.
• Automatically terminates memberships if conditions are met.

---

Suspension Logging

Suspensions are recorded systematically:

CREATE TABLE PreviousSuspensionLog(
  SuspensionLogID INT AUTO_INCREMENT PRIMARY KEY,
  MemberID INT,
  SuspendedDate DATE,
  FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

---

Access Control

Secure privilege management:

GRANT SELECT, INSERT, UPDATE ON libraryloansystem.* TO 'librarian'@'localhost';

---

Testing Scenarios

Comprehensive tests validate database integrity:

• Book Availability: Tested inserting borrow records when copies are unavailable.
• Member Status: Validated borrow prevention for non-REGULAR members.
• Borrowing Limits: Checked daily borrowing constraints and multi-week borrowing limits.
• Fine Accumulation & Suspension: Ensured fines triggered suspensions accurately.
• Membership Restoration: Confirmed status reset upon fine payments and book returns.

---

🛠️ Technologies & Tools

• MySQL 8.x (Procedural SQL)
• MySQL Workbench
• SQL Constraints, Triggers, and Stored Procedures

---

💡 Lessons Learned

• Advanced database logic implementation using procedural SQL (Triggers, Stored Procedures).
• Using SQL procedural constructs like CURSOR, SIGNAL, HANDLER, and IF to replicate business rules.
• Designing robust data integrity constraints within the database layer itself.

---

🚀 Setup Instructions

1. Clone the repository:

   git clone https://github.com/tgishor/Advanced-SQL-Library-CaseStudy.git

2. Create and setup your MySQL database:

   CREATE DATABASE libraryloansystem;
   USE libraryloansystem;
   SOURCE implementation.sql;

3. Run tests using provided scripts or manually insert test data from the SQL file.

---

🖥️ Repository Structure

Library-Loan-System-DB-CaseStudy/
├── README.md
├── implementation.sql
└── Case-Study.pdf [with proven execution]

---

📄 License

This project is licensed under the MIT License. You are encouraged to reuse and adapt this logic for educational or professional projects.

---

🤝 Connect With Me

• GitHub: Gishor Thavakumar
• LinkedIn: Gishor Thavakumar