Project Overview

This project focuses on designing and analyzing a Library Management Database using MySQL.
The goal is to efficiently manage data related to books, borrowers, authors, and branches, and perform analytical queries to generate valuable business insights.

---

Objectives

• Simplify tracking of books and their locations.
• Identify which books are most borrowed and from which branches.
• Monitor library usage and borrowing patterns.
• Support better decision-making for library management and resource allocation.

---

Database Design

The project includes an ER Diagram and a corresponding schema representing:

• Book
• Library_Branch
• Borrower
• Book_Loans
• Book_Copies
• Author

---

Key Analytical Use Cases

1️ How many copies of the book titled "The Lost Tribe" are owned by the library branch "Sharpstown"?
2️ How many copies of "The Lost Tribe" are owned by each branch?
3️ Retrieve the names of all borrowers who do not have any books checked out.
4️ For each book loaned out from "Sharpstown" branch due on 2/3/18, get the book title, borrower’s name, and address.
5️ Retrieve the branch name and total number of books loaned out from each branch.
6️ Retrieve names, addresses, and number of books checked out for borrowers with more than five books checked out.
7️ For each book authored by "Stephen King", retrieve the title and number of copies owned by the "Central" branch.

---

SQL Skills Demonstrated

• DDL: Creating tables and defining constraints.
• DML: Inserting and updating records.
• Joins: INNER, LEFT, RIGHT joins to link multiple tables.
• GROUP BY, HAVING, ORDER BY for summarizing data.
• Subqueries and Nested Queries for complex data retrieval.

---

Business Insights

• Identified stock distribution of books across branches for better inventory control.
• Determined branch activity based on loan frequency.
• Highlighted inactive members for re-engagement.
• Detected frequent borrowers for loyalty or membership programs.
• Analyzed popular authors and borrow trends to support data-driven purchase decisions.

---

Conclusion

This SQL project provided hands-on experience in:

• Database design and management.
• Writing optimized SQL queries.
• Extracting insights for real-world library operations.

Key Takeaways:

• Improved understanding of data modeling.
• Strengthened SQL query-writing and debugging skills.
• Gained exposure to business-oriented analysis using structured data.