The Library Management System is a relational database project designed to efficiently manage and analyze library operations such as book tracking, borrower activity, author insights, and loan management.
This project demonstrates how SQL can be used to handle real-world data relationships, ensuring data consistency and enabling actionable insights for better decision-making.
- Create a normalized database for books, branches, borrowers, and loans.
- Manage book availability and borrower activities effectively.
- Retrieve complex insights using SQL queries and JOIN operations.
- Simulate real-world scenarios of a library network across multiple branches.
- Book – Book details (Title, Book ID, Publisher, etc.)
- Author – Information about book authors
- Publisher – Publisher details
- Library Branch – Different branches in the library network
- Borrower – Library users with membership info
- Book Copies – Number of copies of each book per branch
- Book Loans – Tracks issued books, due dates, and borrowers
CREATE TABLE Library_Branch (...); CREATE TABLE Borrower (...); CREATE TABLE Books (...); CREATE TABLE Book_Authors (...); CREATE TABLE Book_Copies (...); CREATE TABLE Book_Loans (...); CREATE TABLE Publisher (...);
Book 1 ——— N Book_Copies N ——— 1 Library_Branch Book 1 ——— N Book_Loans N ——— 1 Borrower Book 1 ——— N Book_Authors N ——— 1 Author
| # | Query Objective | Description | Example Output |
|---|---|---|---|
| 1 | Book Count per Branch | Retrieve how many copies of "The Lost Tribe" each branch owns | Sharpstown – 5, Central – 5, Saline – 5, Ann Arbor – 5 |
| 2 | Borrowers with No Checkouts | Identify members who haven’t borrowed any books | Jane Smith |
| 3 | Due Date Tracking | List books due on specific dates per branch | Sharpstown – Due 2/3/18 |
| 4 | Active Borrowers | Retrieve borrowers with more than 5 checkouts | Joe Smith (7), Tom Li (13), Tom Haverford (6) |
| 5 | Popular Authors | Retrieve books by Stephen King owned by “Central” branch | It – 5 copies, The Green Mile – 5 copies |
| 6 | Total Loans per Branch | Find number of books loaned from each branch | Central – 11, Sharpstown – 10, Saline – 10, Ann Arbor – 10 |
- 📘 Book Availability: Clear visibility into branch-level stock distribution
- 👥 Borrower Behavior: Identification of inactive and heavy borrowers
- ⏳ Loan Management: Efficient due-date tracking and overdue prevention
- ✍️ Author Trends: Understanding of popular authors and their circulation
- 🏢 Branch Performance: Analysis of book loans across different branches
- Database: MySQL / PostgreSQL
- Query Language: SQL
- Design: ER Model & Normalization (3NF)
- Visualization: PowerPoint (for presentation)
- Managing multiple relationships using JOINs
- Handling data consistency with foreign keys
- Aggregating and filtering data efficiently
- Parsing date-based insights (especially for overdue detection)
This project provided hands-on experience in database design, query optimization, and analytical reporting using SQL. It shows how structured databases empower libraries to make data-driven decisions for inventory and user management.