Library Management System using SQL Project --P2

Project Overview

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

This project demonstrates the development of a library management system utilizing SQL. It encompasses the creation and management of tables, execution of CRUD operations, and the implementation of advanced SQL queries. The primary aim is to demonstrate proficiency in database design, manipulation, and querying.

Objectives

Database Setup: Establish and populate the database with tables for branches, employees, members, books, issued status, and return status.
CRUD Operations: Execute Create, Read, Update, and Delete operations on the data.
CTAS (Create Table As Select): Apply CTAS to generate new tables based on query results.
Advanced SQL Queries: Formulate complex queries to analyze and extract specific data.

Project Structure

1. Database Setup

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

-- 1. Database Setup
CREATE DATABASE library_project;

--Create Library managerment system
--Create books tables
DROP TABLE IF EXISTS books;
CREATE TABLE books
(
	isbn VARCHAR(50) PRIMARY KEY,
	book_title VARCHAR(80),
	category VARCHAR(30),
	rental_price DECIMAL(10, 2),
	status VARCHAR(10),
	author VARCHAR(25),
	publisher VARCHAR(30)
);

--Create branch tables
DROP TABLE IF EXISTS branch;
CREATE TABLE branch
(
	branch_id VARCHAR(10) PRIMARY KEY, --FK
	manager_id VARCHAR(10),
	branch_address VARCHAR(30),
	contact_no VARCHAR(15)
);

--Create employees tables
DROP TABLE IF EXISTS employees;
CREATE TABLE employees
(
	emp_id	  VARCHAR(10) PRIMARY KEY,
	emp_name  VARCHAR(30),
	position  VARCHAR(30),
	salary    DECIMAL(10,2),
	branch_id VARCHAR(10)	--FK
);

--Create members tables
DROP TABLE IF EXISTS members;
CREATE TABLE members
(
	member_id VARCHAR(10) PRIMARY KEY,
	member_name VARCHAR(20),
	member_address VARCHAR(20),
	reg_date DATE
);

--Create issued_status tables
DROP TABLE IF EXISTS issued_status;
CREATE TABLE issued_status
(
	issued_id	  VARCHAR(10) PRIMARY KEY,
	issued_member_id  VARCHAR(30), --FK
	issued_book_name  VARCHAR(80),
	issued_date    DATE,
	issued_book_isbn VARCHAR(50), --FK
	issued_emp_id VARCHAR(10) --FK
);

--Create return_status tables
DROP TABLE IF EXISTS return_status;
CREATE TABLE return_status
(	
	return_id VARCHAR(10) PRIMARY KEY,
	issued_id VARCHAR(10), --FK
	return_book_name VARCHAR(10),
	return_date DATE,
	return_book_isbn VARCHAR(10) --FK
);

--ADDING THE FOREIGN KEYS
ALTER TABLE issued_status
ADD CONSTRAINT fk_members
FOREIGN KEY (issued_member_id)
REFERENCES members(member_id);

ALTER TABLE issued_status
ADD CONSTRAINT fk_books
FOREIGN KEY (issued_book_isbn)
REFERENCES books(isbn);

ALTER TABLE issued_status
ADD CONSTRAINT fk_employee
FOREIGN KEY (issued_emp_id)
REFERENCES employees(emp_id);

ALTER TABLE employees
ADD CONSTRAINT fk_branch
FOREIGN KEY (branch_id)
REFERENCES branch(branch_id);

ALTER TABLE return_status
ADD CONSTRAINT fk_return_status
FOREIGN KEY (return_book_isbn)
REFERENCES books(isbn);

ALTER TABLE return_status
ADD CONSTRAINT fk_return_status
FOREIGN KEY (issued_id)
REFERENCES issued_status(issued_id);

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.');
SELECT * FROM books;

Task 2: Update an Existing Member's Address

UPDATE members
SET member_address = '125 Oak St'
WHERE member_id = 'C103';

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(*)
FROM issued_status
GROUP BY 1
HAVING COUNT(*) > 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(ist.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;

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(b.rental_price),
    COUNT(*)
FROM 
issued_status as ist
JOIN
books as b
ON b.isbn = ist.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.emp_id,
    e1.emp_name,
    e1.position,
    e1.salary,
    b.*,
    e2.emp_name as manager
FROM employees as e1
JOIN 
branch as b
ON e1.branch_id = b.branch_id    
JOIN
employees as e2
ON e2.emp_id = b.manager_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;

Task 12: Retrieve the List of Books Not Yet Returned

SELECT * FROM issued_status as ist
LEFT JOIN
return_status as rs
ON rs.issued_id = ist.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 
		mb.member_id,
		mb.member_name,
		bk.book_title,
		ist.issued_date,
		(CURRENT_DATE - ist.issued_date) AS over_dues
FROM issued_status as ist
JOIN books as bk
ON ist.issued_book_isbn = bk.isbn
JOIN members as mb
ON ist.issued_member_id = mb.member_id
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).

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

DECLARE
    v_isbn VARCHAR(50);
    v_book_name VARCHAR(80);
    
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
issued_id = IS135
ISBN = WHERE isbn = '978-0-307-58837-1'

SELECT * FROM books
WHERE isbn = '978-0-307-58837-1';

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

SELECT * FROM return_status
WHERE issued_id = 'IS135';

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

-- calling function 
CALL add_return_records('RS148', 'IS140', '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 total_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

Task 18: 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

Objective

Create a stored procedure to manage the status of books in a library system.

Description: Develop a stored procedure that updates the status of a book based on its issuance. The procedure should operate as follows:

Accept book_id as an input parameter.
Check if the book is available (status = 'yes').
If available, issue the book and update its status in the books table to 'no'.
If not available (status = 'no'), return an error message indicating that the book is currently unavailable.

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'

Reports

Database Schema: Comprehensive overview of table structures and their interrelations.
Data Analysis: Examination of insights related to book categories, employee salaries, member registration patterns, and issued books.
Summary Reports: Consolidated data on popular books and employee performance metrics.

Conclusion

This project showcases the utilization of SQL skills for developing and managing a library management system. It encompasses database setup, data manipulation, and advanced querying, establishing a robust foundation for effective data management and analysis.

How to Use

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

git clone https://github.com/theravendrasahu/Library-System-Management-P2

Set Up the Database: Run the SQL scripts found in the database_setup.sql file to create and populate the database.
Execute the Queries: Utilize the SQL queries in the analysis_queries.sql file for data analysis.
Explore and Customize: Modify the queries as necessary to investigate various aspects of the data or to address additional inquiries.

Credit: Thank you so much Najirr to help me to built this project

Author - Zero Analyst

This project showcases SQL skills essential for database management and analysis. For more content on SQL and data analysis, connect with najir through the following channels:

YouTube: Subscribe to najirr channel for tutorials and insights
Instagram: Follow najirr for daily tips and updates
LinkedIn: Connect with najirr professionally
Discord: Join our community for learning and collaboration

Thank you for your interest in this project!

Name		Name	Last commit message	Last commit date
Latest commit History 46 Commits
.gitattributes		.gitattributes
Business_Advanced_problems_day2.sql		Business_Advanced_problems_day2.sql
Database_creation.sql		Database_creation.sql
ERD Library managment system.png		ERD Library managment system.png
Library management system.png		Library management system.png
README.md		README.md
books.csv		books.csv
branch.csv		branch.csv
business_problems_day1.sql		business_problems_day1.sql
employees.csv		employees.csv
insert_data_queries.sql		insert_data_queries.sql
issued_status.csv		issued_status.csv
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

Objective

Reports

Conclusion

How to Use

Credit: Thank you so much Najirr to help me to built this project

Author - Zero Analyst

About

Uh oh!

Releases

Packages

Languages

theravendrasahu/Library-System-Management-SQL

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

Objective

Reports

Conclusion

How to Use

Credit: Thank you so much Najirr to help me to built this project

Author - Zero Analyst

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages