Library Management System using SQL Project --P2

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 creating and managing tables, performing CRUD operations, and executing advanced SQL queries. The goal is to showcase skills in database design, manipulation, and querying.

Objectives

Set up the Library Management System Database: Create and populate the database with tables for branches, employees, members, books, issued status, and return status.
CRUD Operations: Perform Create, Read, Update, and Delete operations on the data.
CTAS (Create Table As Select): Utilize CTAS to create new tables based on query results.
Advanced SQL Queries: Develop complex queries to analyze and retrieve specific data.

Project Structure

1. Database Setup

Database Creation: Created a database named library_db.
Table Creation: Created tables for branches, employees, members, books, issued status, and return status. Each table includes relevant columns and relationships.

-- Creates a database named library_db.
CREATE DATABASE library_db;


-- Creating branch table
DROP TABLE IF EXISTS branch;
CREATE TABLE branch (
	branch_id	VARCHAR(10) PRIMARY KEY,
	manager_id	VARCHAR(10),
	branch_address	VARCHAR(55),
	contact_no	VARCHAR(15)
);


-- Creating employees table
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
	emp_id	VARCHAR(10) PRIMARY KEY,
	emp_name	VARCHAR(30),
	position	VARCHAR(30),
	salary	NUMERIC(10,2),
	branch_id	VARCHAR(10)		-- FK
);


-- Creating books table
DROP TABLE IF EXISTS books;
CREATE TABLE books (
	isbn	VARCHAR(25) PRIMARY KEY,
	book_title	VARCHAR(75),
	category	VARCHAR(30),
	rental_price	NUMERIC(10,2),
	status	VARCHAR(10),
	author	VARCHAR(35),
	publisher	VARCHAR(55)
);


-- Creating members table
DROP TABLE IF EXISTS members;
CREATE TABLE members (
	member_id	VARCHAR(10) PRIMARY KEY,
	member_name	VARCHAR(30),
	member_address	VARCHAR(75),
	reg_date	DATE
);


-- Creating issued_status
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
);


-- Creating return_status
DROP TABLE IF EXISTS return_status;
CREATE TABLE return_status (
	return_id	VARCHAR(10) PRIMARY KEY,
	issued_id	VARCHAR(10),
	return_book_name	VARCHAR(75),
	return_date	 DATE,
	return_book_isbn	VARCHAR(25)
);

2. 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.

Task 1. Create a New Book Record -- "978-1-60129-456-2', 'To Kill a Mockingbird', 'Classic', 6.00, 'yes', 'Harper Lee', 'J.B. Lippincott & Co.')"

INSERT INTO books (isbn, book_title, category, rental_price, status, author, publisher) 
VALUES ('978-1-60129-456-2', 'To Kill a Mockingbird', 'Classic', 6.00, 'yes', 'Harper Lee', 'J.B. Lippincott & Co.');

Task 2: Update an Existing Member's Address

UPDATE members 
SET member_address = '123 Main St'
WHERE member_id = 'C101';

Task 3: Delete a Record from the Issued Status Table -- Objective: Delete the record with issued_id = 'IS121' from the issued_status table.

DELETE FROM issued_status
WHERE issued_id = 'IS121';

Task 4: Retrieve All Books Issued by a Specific Employee -- Objective: Select all books issued by the employee with emp_id = 'E101'.

SELECT * FROM issued_status
WHERE issued_emp_id = 'E101';

Task 5: List Members Who Have Issued More Than One Book -- Objective: Use GROUP BY to find members who have issued more than one book.

SELECT issued_emp_id, 
	COUNT(issued_id)  AS total_book_issued 
FROM issued_status
GROUP BY issued_emp_id
HAVING COUNT(issued_id) > 1;

3. CTAS (Create Table As Select)

Task 6: Create Summary Tables: Used CTAS to generate new tables based on query results - each book and total book_issued_cnt**

CREATE TABLE book_issued_cnt AS
SELECT b.isbn, b.book_title, COUNT(issued_id) AS issue_count
FROM issued_status AS ist
JOIN books AS b
ON ist.issued_book_isbn = b.isbn
GROUP BY b.isbn, b.book_title;

SELECT * FROM book_issued_cnt;

4. Data Analysis & Findings

The following SQL queries were used to address specific questions:

Task 7. Retrieve All Books in a Specific Category:

SELECT * FROM books
WHERE category = 'Classic';

Task 8: Find Total Rental Income by Category:

SELECT b.category,
	SUM(rental_price),
	COUNT(*)
FROM books AS b
JOIN issued_status AS ist
ON b.isbn = issued_book_isbn
GROUP BY 1;

List Members Who Registered in the Last 180 Days:

SELECT * FROM members
WHERE reg_date >= CURRENT_DATE - INTERVAL '180 days';

List Employees with Their Branch Manager's Name and their branch details:

SELECT 
	e1.* ,
	b1.manager_id,
	e2.emp_name AS manager
FROM employees AS e1
JOIN branch AS b1
ON b1.branch_id = e1.branch_id
JOIN employees AS e2
ON b1.manager_id = e2.emp_id;

Task 11. Create a Table of Books with Rental Price Above a Certain Threshold:

CREATE TABLE expensive_books AS
SELECT * FROM books
WHERE rental_price > 7.00;

SELECT * FROM expensive_books;

Task 12: Retrieve the List of Books Not Yet Returned

SELECT * 
FROM issued_status AS ist
LEFT JOIN return_status AS rs
ON ist.issued_id = rs.issued_id
WHERE rs.return_id IS null;

-- List of Books Name Not Yet Returned

SELECT DISTINCT(ist.issued_book_name) 
FROM issued_status AS ist
LEFT JOIN return_status AS rs
ON ist.issued_id = rs.issued_id
WHERE rs.return_id IS null;

Advanced SQL Operations

Task 13: Identify Members with Overdue Books
Write a query to identify members who have overdue books (assume a 30-day return period). Display the member's_id, member's name, book title, issue date, and days overdue.

SELECT 
    ist.issued_member_id,
    m.member_name,
    bk.book_title,
    ist.issued_date,
    -- rs.return_date,
    CURRENT_DATE - ist.issued_date as over_dues_days
FROM issued_status as ist
JOIN 
members as m
    ON m.member_id = ist.issued_member_id
JOIN 
books as bk
ON bk.isbn = ist.issued_book_isbn
LEFT JOIN 
return_status as rs
ON rs.issued_id = ist.issued_id
WHERE 
    rs.return_date IS NULL
    AND
    (CURRENT_DATE - ist.issued_date) > 30
ORDER BY 1

Task 14: Update Book Status on Return
Write a query to update the status of books in the books table to "Yes" when they are returned (based on entries in the return_status table).

SELECT * FROM books
WHERE status = 'no';		-- books not available  "978-0-307-58837-1" , "978-0-375-41398-8" , "978-0-7432-7357-1"

SELECT * FROM issued_status
WHERE issued_book_isbn = '978-0-307-58837-1';		-- book issued

SELECT * FROM return_status
WHERE return_book_isbn = '978-0-307-58837-1';		-- not yet return


-- Stored Procedures
CREATE OR REPLACE PROCEDURE add_return_records(p_return_id VARCHAR(10), p_issued_id VARCHAR(10), p_book_quality VARCHAR(15))
LANGUAGE plpgsql
AS $$

DECLARE 
	  v_isbn VARCHAR(25);
   	  v_book_name VARCHAR(75);
BEGIN
		-- all your logic and code
		-- inserting into returns based on users' input		
		INSERT INTO return_status(return_id, issued_id, return_date, book_quality)
	    VALUES
	    (p_return_id, p_issued_id, CURRENT_DATE, p_book_quality);

		SELECT 
	        issued_book_isbn,
	        issued_book_name
        INTO
	        v_isbn,
	        v_book_name
	    FROM issued_status
	    WHERE issued_id = p_issued_id;

	    UPDATE books
	    SET status = 'yes'
	    WHERE isbn = v_isbn;

		RAISE NOTICE 'Thank you for returning the book: %', v_book_name;
END;
$$


-- Testing FUNCTION add_return_records

SELECT * FROM books
WHERE status = 'no';		-- books not available  "978-0-307-58837-1" , "978-0-375-41398-8" , "978-0-7432-7357-1"
							
SELECT * FROM issued_status
WHERE issued_book_isbn = '978-0-307-58837-1';		-- book issued_id "IS135" , "IS134" , "IS136"

SELECT * FROM return_status
WHERE return_book_isbn = '978-0-307-58837-1';		

SELECT * FROM return_status
WHERE issued_id = 'IS134';

SELECT * FROM books
WHERE isbn = '978-0-375-41398-8';


-- Calling function 
CALL add_return_records('RS138', 'IS135', 'Good');


-- Calling function 
CALL add_return_records('RS148', 'IS134', 'Good');

Task 15: Branch Performance Report
Create a query that generates a performance report for each branch, showing the number of books issued, the number of books returned, and the total revenue generated from book rentals.

CREATE TABLE branch_reports
AS
SELECT 
	b.branch_id, 
	b.manager_id, 
	COUNT(ist.issued_id) AS number_book_issued,
	COUNT(rs.return_id) AS number_of_book_return,
	SUM(bk.rental_price) AS toral_revenue
FROM issued_status AS ist
JOIN employees AS e
	ON e.emp_id = ist.issued_emp_id
JOIN branch AS b
	ON e.branch_id = b.branch_id
LEFT JOIN return_status AS rs
	ON rs.issued_id = ist.issued_id
JOIN books AS bk
	ON ist.issued_book_isbn = bk.isbn
GROUP BY 1, 2;

SELECT * FROM branch_reports;

Task 16: CTAS: Create a Table of Active Members
Use the CREATE TABLE AS (CTAS) statement to create a new table active_members containing members who have issued at least one book in the 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 >= CURRENT_DATE - INTERVAL '2 month' 
					)
;			

SELECT * FROM active_members;

Task 17: Find Employees with the Most Book Issues Processed
Write a query to find the top 3 employees who have processed the most book issues. Display the employee name, number of books processed, and their branch.

SELECT 
	e.emp_name, 
	b.*,
	COUNT(ist.issued_id) AS no_book_issued
	FROM issued_status AS ist
JOIN employees AS e
	ON e.emp_id = ist.issued_emp_id
JOIN branch as b
	ON e.branch_id = b.branch_id
GROUP BY 1, 2
ORDER BY no_book_issued DESC
LIMIT 3;


SELECT 
	e.emp_name, 
	COUNT(ist.issued_id) AS no_book_issued, 
	b.*
	--b.branch_address
FROM issued_status AS ist
	JOIN employees AS e
ON e.emp_id = ist.issued_emp_id
	JOIN branch as b
ON e.branch_id = b.branch_id
GROUP BY e.emp_name, 3
ORDER BY no_book_issued
DESC LIMIT 3;

Task 18: Identify Members Issuing High-Risk Books
Write a query to identify members who have issued books more than twice with the status "damaged" in the books table. Display the member name, book title, and the number of times they've issued damaged books.

SELECT 
    m.member_name,
    b.book_title,
    COUNT(*) AS damaged_issue_count
FROM 
    issued_status iss
JOIN 
    members m ON iss.issued_member_id = m.member_id
JOIN 
    books b ON iss.issued_book_isbn = b.isbn
WHERE 
    b.status = 'damaged'
GROUP BY 
    m.member_name, b.book_title
HAVING 
    COUNT(*) > 2;

Task 19: Stored Procedure Objective: Create a stored procedure to manage the status of books in a library system. Description: Write a stored procedure that updates the status of a book in the library based on its issuance. The procedure should function as follows: The stored procedure should take the book_id as an input parameter. The procedure should first check if the book is available (status = 'yes'). If the book is available, it should be issued, and the status in the books table should be updated to 'no'. If the book is not available (status = 'no'), the procedure should return an error message indicating that the book is currently not available.

CREATE OR REPLACE PROCEDURE issue_book(p_issued_id VARCHAR(10), p_issued_member_id VARCHAR(30), p_issued_book_isbn VARCHAR(30), p_issued_emp_id VARCHAR(10))
LANGUAGE plpgsql
AS $$

DECLARE
-- all the variabable
    v_status VARCHAR(10);

BEGIN
-- all the code
    -- checking if book is available 'yes'
    SELECT 
        status 
        INTO
        v_status
    FROM books
    WHERE isbn = p_issued_book_isbn;

    IF v_status = 'yes' THEN

        INSERT INTO issued_status(issued_id, issued_member_id, issued_date, issued_book_isbn, issued_emp_id)
        VALUES
        (p_issued_id, p_issued_member_id, CURRENT_DATE, p_issued_book_isbn, p_issued_emp_id);

        UPDATE books
            SET status = 'no'
        WHERE isbn = p_issued_book_isbn;

        RAISE NOTICE 'Book records added successfully for book isbn : %', p_issued_book_isbn;


    ELSE
        RAISE NOTICE 'Sorry to inform you the book you have requested is unavailable book_isbn: %', p_issued_book_isbn;
    END IF;
END;
$$

-- Testing The function
SELECT * FROM books;
-- "978-0-553-29698-2" -- yes
-- "978-0-375-41398-8" -- no
SELECT * FROM issued_status;

CALL issue_book('IS155', 'C108', '978-0-553-29698-2', 'E104');
CALL issue_book('IS156', 'C108', '978-0-375-41398-8', 'E104');

SELECT * FROM books
WHERE isbn = '978-0-375-41398-8'

Task 20: Create Table As Select (CTAS) Objective: Create a CTAS (Create Table As Select) query to identify overdue books and calculate fines.

Description: Write a CTAS query to create a new table that lists each member and the books they have issued but not returned within 30 days. The table should include: The number of overdue books. The total fines, with each day's fine calculated at $0.50. The number of books issued by each member. The resulting table should show: Member ID Number of overdue books Total fines

CREATE TABLE overdue_books_summary AS
SELECT 
    m.member_id,
    COUNT(CASE WHEN CURRENT_DATE - iss.issued_date > 30 THEN 1 END) AS overdue_books_count,
    SUM(
        CASE 
            WHEN CURRENT_DATE - iss.issued_date > 30 THEN 
                (CURRENT_DATE - iss.issued_date - 30) * 0.50 
            ELSE 0 
        END
    ) AS total_fines,
    COUNT(iss.issued_id) AS total_books_issued
FROM 
    issued_status iss
JOIN 
    members m ON iss.issued_member_id = m.member_id
LEFT JOIN 
    return_status rs ON iss.issued_id = rs.issued_id
WHERE 
    rs.return_id IS NULL -- Book not returned
GROUP BY 
    m.member_id;


SELECT * FROM overdue_books_summary;

Reports

Database Schema: Detailed table structures and relationships.
Data Analysis: Insights into book categories, employee salaries, member registration trends, and issued books.
Summary Reports: Aggregated data on high-demand books and employee performance.

Conclusion

This project demonstrates the application of SQL skills in creating and managing a library management system. It includes database setup, data manipulation, and advanced querying, providing a solid foundation for data management and analysis.

How to Use

Clone the Repository: Clone this repository to your local machine.

git clone https://github.com/WilsonLakra/SQL-Project---Library-Management-System---P2.git

Set Up the Database: Execute the SQL scripts in the database_setup.sql file to create and populate the database.
Run the Queries: Use the SQL queries in the analysis_queries.sql file to perform the analysis.
Explore and Modify: Customize the queries as needed to explore different aspects of the data or answer additional questions.

Author - Wilson Lakra

This project is part of my portfolio, showcasing the SQL skills essential for data analyst roles. If you have any questions, feedback, or would like to connect, feel free to get in touch!

Contact

LinkedIn: Connect with me professionally

Thank you for your interest in this project!

Name		Name	Last commit message	Last commit date
Latest commit History 26 Commits
Library_DB_ERD.png		Library_DB_ERD.png
README.md		README.md
SQL Project - LMS Advance Solution - P2.sql		SQL Project - LMS Advance Solution - P2.sql
SQL Project - LMS Solution - P2.sql		SQL Project - LMS Solution - P2.sql
SQL Project - Library_DB_Schemas - P2.sql		SQL Project - Library_DB_Schemas - P2.sql
books.csv		books.csv
branch.csv		branch.csv
employees.csv		employees.csv
insert_queries.sql		insert_queries.sql
insert_queries2.sql		insert_queries2.sql
issued_status.csv		issued_status.csv
library.jpg		library.jpg
members.csv		members.csv
return_status.csv		return_status.csv

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

Library Management System using SQL Project --P2

Project Overview

Objectives

Project Structure

1. Database Setup

2. CRUD Operations

3. CTAS (Create Table As Select)

4. Data Analysis & Findings

Advanced SQL Operations

Reports

Conclusion

How to Use

Author - Wilson Lakra

Contact

About

Uh oh!

Releases

Packages

Languages

WilsonLakra/SQL-Project---Library-Management-System---P2

Folders and files

Latest commit

History

Repository files navigation

Library Management System using SQL Project --P2

Project Overview

Objectives

Project Structure

1. Database Setup

2. CRUD Operations

3. CTAS (Create Table As Select)

4. Data Analysis & Findings

Advanced SQL Operations

Reports

Conclusion

How to Use

Author - Wilson Lakra

Contact

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages