The objective of this project is to design and analyze a Library Management System using SQL, focusing on structured data modeling, transactional operations, and business-driven analysis.
The project simulates real-world library operations such as book issuance, returns, member management, employee performance, and branch-level reporting to derive meaningful business insights and operational metrics.
This system ensures data integrity through relational constraints and enables analytical reporting using SQL queries, CTAS operations, and stored procedures.
- The dataset is created and managed within the database environment. It includes structured records related to:
- Library branches
- Employees
- Members
- Books
- Issued books
- Returned books
- Data is inserted manually using SQL DML statements to simulate real-world library transactions.
- Dataset Link: Dataset
- Create a new book record in the books table.
- Update an existing member’s address.
- Delete a record from the issued_status table.
- Retrieve all books issued by a specific employee.
- Identify members who have issued more than one book.
- Create summary tables using CTAS to store total books issued per book.
- Retrieve all books belonging to a specific category.
- Calculate total rental income by book category.
- List members who registered within the last 180 days.
- Display employees along with their branch manager’s name and branch details.
- Create a table containing books with rental prices above a defined threshold.
- Retrieve a list of books that have not yet been returned.
- Identify members with overdue books (assuming a 30-day return period).
- Update book availability status upon return using stored procedures.
- Generate a branch performance report showing:
- Total books issued
- Total books returned
- Total rental revenue
- Create an active_members table using CTAS for members who issued at least one book in the last 2 months.
- Identify the top 3 employees who processed the highest number of book issues.
- Develop a stored procedure to manage book issuance based on availability status.
- Designed a relational database schema using SQL DDL statements.
- Implemented foreign key constraints to maintain referential integrity.
- Inserted realistic sample data using SQL DML operations.
- Executed analytical queries using:
- Joins
- Group By & Aggregations
- Date-based filtering
- Used CTAS (Create Table As Select) to generate derived analytical tables.
- Built stored procedures to:
- Automate book issuance logic
- Update book availability status
- Prevent invalid transactions
- Generated business-level reports for operational and performance analysis.
| Component | Technology |
|---|---|
| Database | MySQL / PostgreSQL |
| Query Language | SQL |
| Data Modeling | Relational Schema Design |
| Data Manipulation | DML |
| Analysis Techniques | Joins, Aggregations, CTAS |
| Business Logic | Stored Procedures |
| Reporting | SQL-based Analytical Queries |
- Proper relational design improves data consistency and accuracy.
- CTAS is highly effective for building analytical summary tables.
- Stored procedures help enforce business rules at the database level.
- Branch-level reporting enables performance comparison and optimization.
- Employee activity analysis highlights operational efficiency.
- SQL can effectively handle both transactional and analytical workloads.
This project demonstrates how SQL can be used to build a complete end-to-end Library Management System that supports both operational workflows and analytical reporting.
By combining structured schema design, business-driven queries, CTAS operations, and stored procedures, the system delivers accurate insights and reliable data management.
Such an approach closely aligns with real-world applications in libraries, educational institutions, and content rental platforms where structured data, performance tracking, and operational efficiency are critical.