SQL-based Library Management System that manages books, members, employees, and transactions, enabling issue/return tracking, reporting, overdue detection, and data-driven insights using queries and relational database concepts.
It includes:
- Database creation
- Table creation
- Primary keys and foreign keys
- Data insertion
- Queries for analysis and reporting
- CTAS (Create Table As Select) operations
- Book issue and return tracking
- Overdue fine calculation
- Branch performance reporting
The objective of this project is to design and implement a SQL-based Library Management System that efficiently manages books, members, employees, and transactions. The system aims to enable accurate tracking of book issuance and returns, identify overdue records, generate analytical reports, and support data-driven decision-making using structured queries.
The Library Management System is a relational database project developed using SQL to simulate real-world library operations. It includes multiple interconnected tables such as books, members, employees, branches, issued records, and return records.
The project demonstrates the use of core SQL concepts including table creation, data manipulation, joins, aggregations, and advanced queries like CTAS (Create Table As Select). It supports key functionalities such as book tracking, member activity analysis, overdue detection, fine calculation, and performance reporting.
This project reflects practical data analysis scenarios where structured data is transformed into meaningful insights for operational efficiency.
The dataset used in this project is synthetically created to represent a real-world library environment. It consists of the following tables:
- Books: Contains details such as ISBN, title, category, rental price, availability status, author, and publisher.
- Members: Stores member information including ID, name, address, and registration date.
- Employees: Includes employee details such as ID, name, role, salary, and associated branch.
- Branch: Represents library branches with branch ID, manager ID, address, and contact details.
- Issued_Status: Tracks book issuance records, including member ID, book details, issue date, and employee responsible.
- Return_Status: Records returned books along with return dates and issued references.
The dataset enables simulation of real business operations such as transaction tracking, customer behavior analysis, and performance monitoring.
- Branch
- Employees
- Books
- Members
- Issued_Status
- Return_Status
- Add new book records
- Update member details
- Delete issue records
- Track books issued by employees
- Identify members issuing multiple books
- Generate book issue summary tables
- Filter books by category
- Calculate rental income by category
- Show employees with branch manager details
- Find books not yet returned
- Identify overdue books
- Generate branch performance reports
- Create active members table
- Find top employees by processed book issues
- Calculate overdue fines
- DDL commands
- DML commands
- JOINs
- GROUP BY
- HAVING
- CTAS
- Aggregate functions
- LEFT JOIN
- Subqueries
- Foreign key constraints
- Date functions
sql CREATE DATABASE Library_Project_2;
DROP TABLE IF EXISTS brach;
CREATE TABLE Branch
(
branch_id varchar(20) PRIMARY KEY,
manager_id varchar(20),
branch_address varchar(50),
contact_no varchar(20)
);DROP TABLE IF EXISTS EMPLOYEES;
CREATE TABLE EMPLOYEES
(
emp_id VARCHAR(20) PRIMARY KEY,
emp_name VARCHAR(30),
position VARCHAR(20),
salary INT,
branch_id VARCHAR(20) -- FK
);DROP TABLE IF EXISTS BOOKS;
CREATE TABLE BOOKS
(
isbn VARCHAR(20) PRIMARY KEY,
book_title VARCHAR(75),
category VARCHAR(20),
rental_price FLOAT,
status VARCHAR(20),
author VARCHAR(40),
publisher VARCHAR(40)
);DROP TABLE IF EXISTS MEMBERS;
CREATE TABLE MEMBERS
(
member_id VARCHAR(20) PRIMARY KEY,
member_name VARCHAR(35),
member_address VARCHAR(75),
reg_date date);DROP TABLE IF EXISTS ISSUED_STATUS;
CREATE TABLE issued_status
(
issued_id VARCHAR(10) PRIMARY KEY,
issued_member_id VARCHAR(10), -- FK
issued_book_name VARCHAR(75),
issued_date DATE,
issued_book_isbn VARCHAR(25),-- FK
issued_emp_id VARCHAR(10) -- FK
);This project is a SQL-based Library Management System designed to manage books, members, employees, and transactions. It supports issuing, returning, tracking overdue books, and generating reports.
-- Create Database
CREATE DATABASE Library_Project_2;
USE Library_Project_2;-- Create Branch Table
CREATE TABLE Branch (
branch_id VARCHAR(20) PRIMARY KEY,
manager_id VARCHAR(20),
branch_address VARCHAR(50),
contact_no VARCHAR(20)
);
-- Create Employees Table
CREATE TABLE Employees (
emp_id VARCHAR(20) PRIMARY KEY,
emp_name VARCHAR(30),
position VARCHAR(20),
salary INT,
branch_id VARCHAR(20)
);
-- Create Books Table
CREATE TABLE Books (
isbn VARCHAR(20) PRIMARY KEY,
book_title VARCHAR(75),
category VARCHAR(20),
rental_price FLOAT,
status VARCHAR(20),
author VARCHAR(40),
publisher VARCHAR(40)
);
-- Create Members Table
CREATE TABLE Members (
member_id VARCHAR(20) PRIMARY KEY,
member_name VARCHAR(35),
member_address VARCHAR(75),
reg_date DATE
);
-- Create Issued Status Table
CREATE TABLE issued_status (
issued_id VARCHAR(10) PRIMARY KEY,
issued_member_id VARCHAR(10),
issued_book_name VARCHAR(75),
issued_date DATE,
issued_book_isbn VARCHAR(25),
issued_emp_id VARCHAR(10)
);
-- Create Return Status Table
CREATE TABLE return_status (
return_id VARCHAR(10) PRIMARY KEY,
issued_id VARCHAR(10),
return_date DATE
);-- Add Foreign Keys
ALTER TABLE issued_status
ADD FOREIGN KEY (issued_member_id) REFERENCES Members(member_id);
ALTER TABLE issued_status
ADD FOREIGN KEY (issued_book_isbn) REFERENCES Books(isbn);
ALTER TABLE issued_status
ADD FOREIGN KEY (issued_emp_id) REFERENCES Employees(emp_id);
ALTER TABLE return_status
ADD FOREIGN KEY (issued_id) REFERENCES issued_status(issued_id);
ALTER TABLE Employees
ADD FOREIGN KEY (branch_id) REFERENCES Branch(branch_id);-- Insert Sample Members
INSERT INTO Members VALUES
('C101','Alice Johnson','123 Main St','2021-05-15'),
('C102','Bob Smith','456 Elm St','2021-06-20');
-- Insert Branch Data
INSERT INTO Branch VALUES
('B001','E109','123 Main St','+919099988676');
-- Insert Employees
INSERT INTO Employees VALUES
('E101','John Doe','Clerk',60000,'B001');
-- Insert Books
INSERT INTO Books VALUES
('978-0-553-29698-2','The Catcher in the Rye','Classic',7.00,'yes','J.D. Salinger','Little, Brown');-- Insert new book record
INSERT INTO Books VALUES
('978-1-60129-456-2','To Kill a Mockingbird','Classic',6.00,'yes','Harper Lee','J.B. Lippincott');-- Update member address
UPDATE Members
SET member_address = '125 Oak St'
WHERE member_id = 'C103';-- Delete incorrect issued record
DELETE FROM issued_status
WHERE issued_id = 'IS121';-- Get books issued by employee E101
SELECT *
FROM issued_status
WHERE issued_emp_id = 'E101';-- Find members who issued more than one book
SELECT issued_member_id, COUNT(*) AS total_books
FROM issued_status
GROUP BY issued_member_id
HAVING COUNT(*) > 1;-- Create summary table for issued books
CREATE TABLE book_counts AS
SELECT
b.isbn,
b.book_title,
COUNT(ist.issued_id) AS issue_count
FROM Books b
JOIN issued_status ist
ON b.isbn = ist.issued_book_isbn
GROUP BY b.isbn, b.book_title;-- Get all classic books
SELECT *
FROM Books
WHERE category = 'Classic';-- Calculate total rental income
SELECT b.category,
SUM(b.rental_price) AS total_income
FROM Books b
JOIN issued_status ist
ON b.isbn = ist.issued_book_isbn
GROUP BY b.category;-- Members registered in last 180 days
SELECT *
FROM Members
WHERE reg_date >= CURDATE() - INTERVAL 180 DAY;-- Employee with branch and manager details
SELECT
e.emp_name,
b.branch_id,
m.emp_name AS manager_name
FROM Employees e
JOIN Branch b ON e.branch_id = b.branch_id
JOIN Employees m ON b.manager_id = m.emp_id;-- Create table for expensive books
CREATE TABLE expensive_books AS
SELECT *
FROM Books
WHERE rental_price > 7;-- Find books not yet returned
SELECT *
FROM issued_status ist
LEFT JOIN return_status rs
ON ist.issued_id = rs.issued_id
WHERE rs.issued_id IS NULL;-- Identify overdue books (>30 days)
SELECT
m.member_name,
bk.book_title,
ist.issued_date,
DATEDIFF(CURDATE(), ist.issued_date) AS overdue_days
FROM issued_status ist
JOIN Members m ON m.member_id = ist.issued_member_id
JOIN Books bk ON bk.isbn = ist.issued_book_isbn
LEFT JOIN return_status rs ON rs.issued_id = ist.issued_id
WHERE rs.return_date IS NULL
AND DATEDIFF(CURDATE(), ist.issued_date) > 30;-- Generate branch performance report
CREATE TABLE branch_report AS
SELECT
b.branch_id,
COUNT(ist.issued_id) AS total_issued,
COUNT(rs.return_id) AS total_returned,
SUM(bk.rental_price) AS revenue
FROM issued_status ist
JOIN Employees e ON ist.issued_emp_id = e.emp_id
JOIN Branch b ON e.branch_id = b.branch_id
LEFT JOIN return_status rs ON rs.issued_id = ist.issued_id
JOIN Books bk ON bk.isbn = ist.issued_book_isbn
GROUP BY b.branch_id;-- Members active in last 2 months
CREATE TABLE active_members AS
SELECT *
FROM Members
WHERE member_id IN (
SELECT DISTINCT issued_member_id
FROM issued_status
WHERE issued_date >= CURDATE() - INTERVAL 2 MONTH
);-- Top 3 employees with most book issues
SELECT
e.emp_name,
COUNT(ist.issued_id) AS total_books
FROM issued_status ist
JOIN Employees e ON ist.issued_emp_id = e.emp_id
GROUP BY e.emp_name
ORDER BY total_books DESC
LIMIT 3;-- Create overdue fines table
CREATE TABLE overdue_fines AS
SELECT
ist.issued_id,
m.member_name,
bk.book_title,
DATEDIFF(CURDATE(), ist.issued_date) AS total_days,
GREATEST(DATEDIFF(CURDATE(), ist.issued_date) - 30, 0) AS overdue_days,
GREATEST(DATEDIFF(CURDATE(), ist.issued_date) - 30, 0) * 1 AS fine_amount
FROM issued_status ist
JOIN Members m ON m.member_id = ist.issued_member_id
JOIN Books bk ON bk.isbn = ist.issued_book_isbn
LEFT JOIN return_status rs ON rs.issued_id = ist.issued_id
WHERE rs.return_date IS NULL;