SQL Projects For Beginners: Library Management System

Build a Library Management System

Building a Library Management System involves designing a database to manage books, members, and borrowing transactions.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

Code:

CREATE DATABASE LibraryDB;
USE LibraryDB;

Create the Tables:

Books Table:

This table stores information about the books in the library.

Structure:

Code:

-- Create the Books table with the following columns:
-- book_id: unique identifier for each book, auto-incremented, primary key
-- title: the title of the book, cannot be null
-- author: the author of the book, cannot be null
-- genre: optional genre of the book
-- published_year: year the book was published
-- is_available: status if the book is available, defaults to TRUE (available)
CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    published_year YEAR,
    is_available BOOLEAN DEFAULT TRUE
);

Members Table:

This table stores information about library members.

Structure:

Code:

-- Create the Members table with the following columns:
-- member_id: unique identifier for each member, auto-incremented, primary key
-- name: member's full name, cannot be null
-- email: optional email address
-- phone_number: optional contact number
-- join_date: the date the member joined, defaults to the current date
CREATE TABLE Members (
    member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone_number VARCHAR(15),
    join_date DATE DEFAULT (CURRENT_DATE)
);

Librarians Table:

This table stores the details of the librarians.

Structure:

Code:

-- Create the Librarians table with the following columns:
-- librarian_id: unique identifier for each librarian, auto-incremented, primary key
-- name: librarian's full name, cannot be null
-- email: optional email address
-- phone_number: optional contact number
-- hire_date: the date the librarian was hired, defaults to the current date
CREATE TABLE Librarians (
    librarian_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone_number VARCHAR(15),
    hire_date DATE DEFAULT (CURRENT_DATE)
);

Borrowing (Loans) Table:

This table manages the borrowing records of members.

Structure:

Code:

-- Create the Borrowing table to manage borrowing transactions:
-- loan_id: unique identifier for each loan, auto-incremented, primary key
-- book_id: foreign key referencing the Books table
-- member_id: foreign key referencing the Members table
-- borrow_date: the date the book was borrowed, defaults to current date
-- return_date: the date the book was returned (can be NULL if not returned yet)
-- librarian_id: foreign key referencing the Librarians table
CREATE TABLE Borrowing (
    loan_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    member_id INT,
    borrow_date DATE DEFAULT (CURRENT_DATE),
    return_date DATE,
    librarian_id INT,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id),
    FOREIGN KEY (librarian_id) REFERENCES Librarians(librarian_id)
);

Inserting Data

Add some sample data to the tables.

Books Table:

-- Insert book records into the Books table
INSERT INTO Books (title, author, genre, published_year) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925),
('1984', 'George Orwell', 'Dystopian', 1949),
('To Kill a Mockingbird', 'Harper Lee', 'Classic', 1960);

Members Table:

-- Insert member records into the Members table
INSERT INTO Members (name, email, phone_number) VALUES
('Alen King', '[email protected]', '1234567890'),
('Alece Hofman', '[email protected]', '9876543210');

Librarians Table:

-- Insert librarian records into the Librarians table
INSERT INTO Librarians (name, email, phone_number) VALUES 
('Nail Horn', '[email protected]', '4567891230'), 
('Garden McGraw', '[email protected]', '7894561230');

Basic functionalities:

• Add books, members, and borrowing transactions.
• Track books that are available or borrowed.
• Track loan history for members.

Writing Queries for Functionality:

Query-1: Borrow a Book (Insert into Borrowing Table and Update Book Availability)

-- Insert a new record into the Borrowing table
INSERT INTO Borrowing (book_id, member_id, librarian_id, borrow_date)
-- Specify the values for book_id, member_id, librarian_id, and the current borrow date
VALUES (1, 1, 1, CURRENT_DATE);

Explanation:

This SQL statement inserts a new borrowing record into the Borrowing table. It indicates that member 1 is borrowing book 1 with the assistance of librarian 1, and the borrowing date is set to the current date using the CURRENT_DATE function. This function automatically captures the date when the record is inserted.

Output:

loan_id	book_id	member_id	borrow_date	return_date	librarian_id
1	1	1	2024-10-16	NULL	1

-- Update the Books table to mark book 1 as unavailable
UPDATE Books
-- Set the is_available field to FALSE (unavailable)
SET is_available = FALSE
-- Specify the book with book_id 1 to update
WHERE book_id = 1;

Explanation:

This SQL statement updates the Books table to mark book 1 as unavailable, indicating that it has been borrowed. The is_available column is set to FALSE for the book with book_id 1, showing that the book is no longer available for borrowing until it is returned.

Output:

book_id	title	author	genre	published_year	is_available
1	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	0
2	1984	George Orwell	Dystopian	1949	1
3	To Kill a Mockingbird	Harper Lee	Classic	1960	1

Query-2: Return a Book (Update Return Date and Book Availability)

-- Update the Borrowing table to record the return date for loan 1
UPDATE Borrowing
-- Set the return_date field to the current date (indicating the book has been returned)
SET return_date = CURRENT_DATE
-- Specify the loan with loan_id 1 to update
WHERE loan_id = 1;

Explanation:

This SQL statement updates the Borrowing table to set the return date for loan 1 to the current date, indicating that the book associated with loan 1 has been returned. The CURRENT_DATE function captures the date when the update is made, marking the return of the borrowed item.

Output:

loan_id	book_id	member_id	borrow_date	return_date	librarian_id
1	1	1	2024-10-16	2024-10-16	1

-- Update the Books table to mark book 1 as available
UPDATE Books
-- Set the is_available field to TRUE (indicating the book is available again)
SET is_available = TRUE
-- Specify the book with book_id 1 to update
WHERE book_id = 1;

Explanation:

This SQL statement updates the Books table to mark book 1 as available again, indicating that it has been returned and is ready for borrowing. The is_available column is set to TRUE for the book with book_id 1, signaling that it is now available

Output:

book_id	title	author	genre	published_year	is_available
1	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	1
2	1984	George Orwell	Dystopian	1949	1
3	To Kill a Mockingbird	Harper Lee	Classic	1960	1

Query-3: Check Available Books

-- Select all columns from the Books table for books that are available
SELECT * FROM Books
-- Filter the results to only include books where is_available is TRUE
WHERE is_available = TRUE;

Explanation:

This SQL statement retrieves all books from the Books table that are currently available for borrowing. It selects all columns (*) from the table where the is_available column is set to TRUE, indicating that the books are available for checkout.

Output:

book_id	title	author	genre	published_year	is_available
1	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	1
2	1984	George Orwell	Dystopian	1949	1
3	To Kill a Mockingbird	Harper Lee	Classic	1960	1

Query-4: View Member Loan History

-- Select member name, book title, borrow date, and return date for member 1
SELECT m.name, b.title, br.borrow_date, br.return_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get member information
JOIN Members m ON br.member_id = m.member_id
-- Join the Books table to get book titles
JOIN Books b ON br.book_id = b.book_id
-- Filter the results to show borrowing history for member 1
WHERE m.member_id = 1;

Explanation:

This SQL statement retrieves the borrowing history of member 1, displaying the member's name, book titles, borrowing dates, and return dates. The query joins the Borrowing, Members, and Books tables to fetch related information. It filters the results to show only records where the member_id is 1.

Output:

name	title	borrow_date	return_date
Alen King	The Great Gatsby	2024-10-16	2024-10-16

Query-5: List Overdue Books (Books Not Returned in 14 Days)

-- Select member name, book title, and borrow date for overdue books
SELECT m.name, b.title, br.borrow_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get member information
JOIN Members m ON br.member_id = m.member_id
-- Join the Books table to get book titles
JOIN Books b ON br.book_id = b.book_id
-- Filter for books that have not been returned (return_date is NULL)
WHERE br.return_date IS NULL
-- Further filter for books borrowed more than 14 days ago (overdue)
AND br.borrow_date < CURRENT_DATE - INTERVAL 14 DAY;

Explanation:

This SQL statement lists books that are overdue and have not been returned within the last 14 days. It retrieves the member's name, book title, and borrowing date for all unreturned books. The query joins the Borrowing, Members, and Books tables and filters for records where the return_date is NULL and the borrow_date is older than 14 days from the current date.

Output:

Output not generated for insufficient data

Query-6: List All Books by a Specific Author

-- Select the title, genre, and published year of books
SELECT title, genre, published_year
-- From the Books table
FROM Books
-- Filter the results to include only books written by 'George Orwell'
WHERE author = 'George Orwell';

Explanation:

This SQL statement retrieves information about all books written by George Orwell from the Books table. It selects the title, genre, and published_year of books where the author column matches 'George Orwell'. This allows users to view all relevant details for books by the specified author.

Output:

title	genre	published_year
1984	Dystopian	1949

Query-7: Find Books Published After a Certain Year

-- Select the title, author, and published year of books
SELECT title, author, published_year
-- From the Books table
FROM Books
-- Filter the results to include only books published after the year 2000
WHERE published_year > 2000;

Explanation:

This SQL statement retrieves information about books that were published after the year 2000 from the Books table. It selects the title, author, and published_year for books where the published_year is greater than 2000, helping users filter and view newer publications.

Output:

Output not available for insufficient data

Query-8: Count Total Books in the Library

-- Count the total number of books in the Books table
SELECT COUNT(*) AS total_books
-- From the Books table
FROM Books;

Explanation:

This SQL statement calculates the total number of books available in the Books table by counting all rows. The result is displayed with the alias total_books, representing the total number of books in the library's collection.

Output:

total_books
3

Query-9: View All Members Who Borrowed a Specific Book

-- Select member name, borrow date, and return date for members who borrowed the book '1984'
SELECT m.name, br.borrow_date, br.return_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get member information
JOIN Members m ON br.member_id = m.member_id
-- Join the Books table to get book information, including the title
JOIN Books b ON br.book_id = b.book_id
-- Filter the results to show records for the book titled '1984'
WHERE b.title = '1984';

Explanation:

This SQL statement lists all members who have borrowed the book titled '1984'. It retrieves the member's name, the borrowing date, and the return date. The query joins the Borrowing, Members, and Books tables to fetch the relevant information, filtering results where the title of the book matches '1984'.

Output:

Output not genetated for insufficient data

Query-10: Find Borrowing History of a Specific Member

-- Select the book title, borrow date, and return date for the borrowing history of member 1
SELECT b.title, br.borrow_date, br.return_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Books table to get the book titles
JOIN Books b ON br.book_id = b.book_id
-- Filter the results to show the borrowing history for member 1
WHERE br.member_id = 1;

Explanation:

This SQL statement retrieves the borrowing history of a specific member (with member_id = 1). It shows the titles of the books borrowed, along with the borrowing and return dates. The query joins the Borrowing and Books tables to display the relevant details for the books borrowed by the member.

Output:

title	borrow_date	return_date
The Great Gatsby	2024-10-16	2024-10-16

Query-11: List All Available Books of a Specific Genre

-- Select the title, author, and published year of available Fiction books
SELECT title, author, published_year
-- From the Books table
FROM Books
-- Filter the results to include only books in the Fiction genre
WHERE genre = 'Fiction'
-- Further filter the results to include only books that are available
AND is_available = TRUE;

Explanation:

This SQL statement retrieves all books from the Books table that are currently available and belong to the Fiction genre. It selects the title, author, and published_year for books where the genre is 'Fiction' and the is_available column is set to TRUE, indicating that these books are available for borrowing.

Output:

title	author	published_year
The Great Gatsby	F. Scott Fitzgerald	1925

Query-12: Calculate the Total Number of Books Borrowed by Each Member

-- Select the member name and count of books borrowed by each member
SELECT m.name, COUNT(br.loan_id) AS total_books_borrowed
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get member information
JOIN Members m ON br.member_id = m.member_id
-- Group the results by member name to get the count of borrowed books per member
GROUP BY m.name;

Explanation:

This SQL statement counts the total number of books borrowed by each member in the library. It retrieves each member's name and the count of their borrowed books using the COUNT function on the loan_id from the Borrowing table. The results are grouped by the member's name to provide a total for each individual member.

Output:

name	total_books_borrowed
Alen King	1

Query-13: List All Overdue Books Not Yet Returned

-- Select the member name, book title, and borrow date for overdue books that have not been returned
SELECT m.name, b.title, br.borrow_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get member information
JOIN Members m ON br.member_id = m.member_id
-- Join the Books table to get book titles
JOIN Books b ON br.book_id = b.book_id
-- Filter for records where the book has not been returned (return_date is NULL)
WHERE br.return_date IS NULL
-- Further filter for books borrowed more than 30 days ago (overdue)
AND br.borrow_date < CURRENT_DATE - INTERVAL 30 DAY;

Explanation:

This SQL statement identifies books that have not been returned and are considered overdue by more than 30 days. It retrieves the member's name, the book title, and the borrowing date from the Borrowing, Members, and Books tables. The query filters for records where the return_date is NULL (indicating the book has not been returned) and where the borrow_date is older than 30 days from the current date.

Output:

Output not generated for insufficient data

Query-14: List the Librarians Who Processed the Most Borrowings

-- Select the librarian name and count of borrowings they processed
SELECT l.name, COUNT(br.loan_id) AS total_borrowings
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Librarians table to get librarian information
JOIN Librarians l ON br.librarian_id = l.librarian_id
-- Group the results by librarian name to count their total borrowings
GROUP BY l.name
-- Order the results by total borrowings in descending order
ORDER BY total_borrowings DESC;

Explanation:

This SQL statement lists each librarian along with the total number of borrowings they have processed. It retrieves the librarian's name and counts the number of borrowings by using the COUNT function on the loan_id from the Borrowing table. The results are grouped by the librarian's name, sorted in descending order based on the total borrowings, allowing for easy identification of the most active librarians.

Output:

name	total_borrowings
Nail Horn	1

Query-15: Find All Books Borrowed But Not Yet Returned

-- Select the member name, book title, and borrow date for books that have not been returned
SELECT m.name, b.title, br.borrow_date
-- From the Borrowing table with alias br
FROM Borrowing br
-- Join the Members table to get information about the member who borrowed the book
JOIN Members m ON br.member_id = m.member_id
-- Join the Books table to get the titles of the borrowed books
JOIN Books b ON br.book_id = b.book_id
-- Filter the results to include only records where the book has not been returned (return_date is NULL)
WHERE br.return_date IS NULL;

Explanation:

This SQL statement retrieves information about all books that have been borrowed but have not yet been returned. It selects the member's name, the title of the book, and the borrowing date from the Borrowing, Members, and Books tables. The query filters for records where the return_date is NULL, indicating that the books are still checked out and have not been returned by the members.

Output:

Output not generated for insufficient data

This step-by-step guide provides the foundation for a Library Management System with detailed SQL database design and functionality implementation.

SQL Code Editor: