Library Management System using SQL

Project Overview

Project Title: Library Management System Level: Intermediate Database: library_db

This project demonstrates the implementation of a Library Management System using SQL. It includes database creation, table relationships, CRUD operations, and advanced SQL queries for data analysis and reporting.

The purpose of this project is to showcase practical SQL skills in database design, data management, and query writing.

---

Objectives

• Design and create a relational database for library management.
• Create tables for branches, employees, members, books, issued books, and returned books.
• Perform CRUD operations on records.
• Use joins to connect related tables.
• Generate reports using aggregate functions.
• Use CTAS (CREATE TABLE AS SELECT) for summary tables.
• Analyze data using SQL queries.

---

Project Structure

Library-Management-System/
│── 01_create_tables.sql
│── 02_insert_data.sql
│── 03_project_tasks.sql
│── schema-diagram.png
│── README.md

---

Database Schema

---

Tables Used

• Branch
• Employee
• Member
• Book
• Issue Status
• Return Status

---

CRUD Operations

Create

• Inserted sample records into the books table.

Read

• Retrieved and displayed data from various tables.

Update

• Updated records in the employees table.

Delete

• Removed records from the members table as needed.

---

Tasks Performed

1. Create a New Book Record
2. Update an Existing Member's Address
3. Delete a Record from Issued Status Table
4. Retrieve All Books Issued by a Specific Employee
5. List Members Who Have Issued More Than One Book
6. Create Summary Tables Using CTAS
7. Retrieve All Books in a Specific Category
8. Find Total Rental Income by Category
9. List Members Who Registered in the Last 180 Days
10. List Employees with Their Branch Manager's Name and Branch Details
11. Create a Table of Books with Rental Price Above a Threshold
12. Retrieve the List of Books Not Yet Returned

---

Reports and Analysis

• Book category analysis
• Rental income reports
• Member registration trends
• Book issue and return tracking
• Summary reports for books and employees

---

SQL Concepts Used

• CREATE DATABASE
• CREATE TABLE
• ALTER TABLE
• PRIMARY KEY
• FOREIGN KEY
• INSERT
• UPDATE
• DELETE
• SELECT
• WHERE
• JOIN
• GROUP BY
• HAVING
• ORDER BY
• Aggregate Functions
• CTAS

---

Author

Ayush Jayswar

This project demonstrates practical SQL skills required for database management and data analysis.