Library Management System using MYSQL

Introduction

In this article, we will build a Library Database Management System using MySQL. The database for library management system will include tables that store and organize information about books, users, and transactions. Imagine that you go to the library, take a book, and just enter that book into the computer rather than entering your details and book details in a register. Isn’t it easy and convenient? Here comes the library database management system. This system is handy for people going to the library and searching for their desired book and for the librarian to guide them and take care of these things. It ensures that everything works in systematic order, given that each person taking or returning books needs to enter the record in the system. In this article, you will get insights about the library database management system project and also understand about the library management system SQL project with these projects you will able to clear your doubts on library management system.

Learning Objectives:

• Understanding the purpose and importance of a library management system, including its objectives, features, and benefits for librarians and patrons.
• Learning how to design and implement a library management system database using MySQL, including creating tables, inserting data, defining relationships, and writing triggers and procedures.
• Gaining practical experience in building a functional library management system database by following the step-by-step guide provided in the content, which covers table structures, data insertion, testing, and other crucial aspects.

This article was published as a part of the Data Science Blogathon.

Purpose of Library Management System

The Library Database Management System is a comprehensive system that shows all available books and their count, as well as books taken by people, the date they borrowed a particular book, the expected return date, late fees, membership details, and more. Using a database for library management system ensures that everything is crystal clear, with no ambiguity. This clarity is beneficial for both students and librarians.

This system is very efficient and cost-effective. Implementing a database for library management system saves a lot of time for both librarians and students. With this system, manual work is significantly reduced, requiring less staff and maintenance. Additionally, the system is user-friendly and very easy to use.

Analysis of Features

Firstly we need to take every user perspective; user means not only customers but also staff in the library. What are all the features each user requires?

We will start with features required by library staff

• Record while issuing books- When anyone takes a book, staff should be able to scan the barcode on the book and should be able to enter the record.
• Profile editing- Staff should be able to edit the profile and the profiles of the people with membership in that library.
• They should be able to keep track of books issued by them.
• Should be able to ask, request, or demand the books from the people who took that if they crossed the due date.
• They should be able to track books, their place, and so on.
• If there occurs any change in the system, or if anyone else entered details or tried to access the system, staff should get the notification.

Next going to features required by a student

• They should be able to create their account themselves
• If their desired book is not available in the library, they should be able to request that particular book.
• They should be able to receive notification if the due date for any particular book is exceeded.
• By giving details of a particular book like the name of the book, author, and published by, they should be able to find the book’s place in the library.
• A payment option should be there so that students can pay their membership fees, late fees, and so on.

In the following code, we will be using MYSQL and the names of some books so that they would be easy to understand.

Creating Tables

So our first step is to create tables. Let us create a database as db_LibraryManagement and then create all the required tables inside it.

CREATE PROC dbo.LibraryManagementSystemProcedure
AS CREATE DATABASE db_LibraryManagement
GO
CREATE TABLE table_publisher (
PublisherName VARCHAR(50) PRIMARY KEY NOT NULL,
PublisherAddress VARCHAR(100) NOT NULL,
PublisherPhone VARCHAR(20) NOT NULL,
);

Now let’s create a table for a book.

CREATE TABLE table_book (
BookID INT PRIMARY KEY NOT NULL IDENTITY (1,1),
Book_Title VARCHAR(100) NOT NULL,
PublisherName VARCHAR(100) NOT NULL
);

Similarly, create a table for the library branch.

CREATE TABLE table_library_branch (
library_branch_BranchID INT PRIMARY KEY NOT NULL IDENTITY (
library branch BranchName VARCHAR(100) NOT NULL, library_branch_BranchAddress VARCHAR(200) NOT NULL,
);

View the library branch table.

SELECT FROM table_library_branch
CREATE TABLE table_borrower (
CardNo INT PRIMARY KEY NOT NULL IDENTITY (100,1),
BorrowerName VARCHAR(100) NOT NULL,
BorrowerAddress VARCHAR(200) NOT NULL,
BorrowerPhone VARCHAR(50) NOT MILL,
);

Create a table to store the book copies.

CREATE TABLE table_book_copies (
book_copies CopiesID INT PRIMARY KEY NOT NULL 
book_copies BookID INT NOT NULL 
book_copies BranchID INT NOT NULL 
book_copies No Of Copies INT NOT NULL,
);

Create one more table for storing book authors

SELECT FROM table_book_copies CREATE TABLE table_book_authors (
book_authors AuthorID INT PRIMARY KEY NOT NULL IDENTITY (1,1),
book_authors BookID INT NOT NULL CONSTRAINT fk_book_id3 FOREIGN KEY REFERENCES table_book(book_BookID) ON UPDATE CASCADE ON DELETE CASCADE, table_book(book_BookID) ON UPDATE CASCADE,
book authors AuthorName VARCHAR(50) NOT NULL,
);

SELECT FROM table_book_authors

Inserting Data

Next is having some data inserted into the tables. Let’s do it now.

-- Table structure for table `book`

CREATE TABLE IF NOT EXISTS `book` (

  `isbn` char(13) NOT NULL,

  `title` varchar(80) NOT NULL,

  `author` varchar(80) NOT NULL,

  `category` varchar(80) NOT NULL,

  `price` int(4) unsigned NOT NULL,

  `copies` int(10) unsigned NOT NULL

);

Adding data into the table book.

INSERT INTO `book` (`isbn`, `title`, `author`, `category`, `price`, `copies`) VALUES

('9788654552277', 'X-Men: God Loves, Man Kills', 'Chris', 'Comics', 98, 39),

('0964161484100', 'Mike Tyson : Undisputed Truth', 'Larry Sloman, Mike Tyson', 'Sports', 654, 79),

('6901142585540', 'V for Vendetta', 'Alan Moore', 'Comics', 600, 23),

('9094996245442', 'When Breath Becomes Air', 'Paul Kalanithi', 'Medical', 500, 94),

('8653491200700', 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 432, 120);

Structure of the table for book issue.

CREATE TABLE IF NOT EXISTS `book_issue` (

`issue_id` int(11) NOT NULL,

  `member` varchar(20) NOT NULL,

  `book_isbn` varchar(13) NOT NULL,

  `due_date` date NOT NULL,

  `last_reminded` date DEFAULT NULL

) ;

CREATE TRIGGER `issue_book` BEFORE INSERT ON `book_issue`

 FOR EACH ROW BEGIN

  SET NEW.due_date = DATE_ADD(CURRENT_DATE, INTERVAL 20 DAY);

    UPDATE member SET balance = balance - (SELECT price FROM book WHERE 

isbn = NEW.book_isbn) WHERE username = NEW.member;

    UPDATE book SET copies = copies - 1 WHERE isbn = NEW.book_isbn;

    DELETE FROM pending_book_requests WHERE member = NEW.member AND book_isbn = NEW.book_isbn;

END

CREATE TRIGGER `return_book` BEFORE DELETE ON `book_issue`

 FOR EACH ROW BEGIN

    UPDATE member SET balance = balance + (SELECT price FROM book WHERE isbn = OLD.book_isbn) WHERE username = OLD.member;

    UPDATE book SET copies = copies + 1 WHERE isbn = OLD.book_isbn;

END

Structure of librarian table.

CREATE TABLE IF NOT EXISTS `librarian` (

`id` int(11) NOT NULL,

  `username` varchar(20) NOT NULL,

  `password` char(40) NOT NULL

) ;

Adding details of the librarian.

INSERT INTO `librarian` (`id`, `username`, `password`) VALUES

(1, 'Vani', 'xthds97@3h$yfc*jrk0%dfg

Structure of table for the member.

CREATE TABLE IF NOT EXISTS `member` (

`id` int(11) NOT NULL,

  `username` varchar(20) NOT NULL,

  `password` char(40) NOT NULL,

  `name` varchar(80) NOT NULL,

  `email` varchar(80) NOT NULL,

  `balance` int(4) NOT NULL

);

CREATE TRIGGER `add_member` AFTER INSERT ON `member`

 FOR EACH ROW DELETE FROM pending_registrations WHERE username = NEW.username

CREATE TRIGGER `remove_member` AFTER DELETE ON `member`

 FOR EACH ROW DELETE FROM pending_book_requests WHERE member = OLD.username

Structured table for pending book requests

CREATE TABLE IF NOT EXISTS `pending_book_requests` (

`request_id` int(11) NOT NULL,

  `member` varchar(20) NOT NULL,

  `book_isbn` varchar(13) NOT NULL,

  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

);

Structure of table for pending registrations.

CREATE TABLE IF NOT EXISTS `pending_registrations` (

  `username` varchar(30) NOT NULL,

  `password` char(20) NOT NULL,

  `name` varchar(40) NOT NULL,

  `email` varchar(20) NOT NULL,

  `balance` int(10),

  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

);

Add data for pending registrations table.

INSERT INTO `pending_registrations`

(`username`, `password`, `name`, `email`, `balance`, `time`)

VALUES

('Robin200', '7t6hg$56y^', 'Robin', '[email protected]', 200, '2021-03-21 08:59:00'),

('Aadhya100', 'Ujgf(76G5$#f@df', 'Aadhya', '[email protected]', 1500, '2021-03-21 2:14:53');

Now let’s add primary keys to these tables.

ALTER TABLE `book`

ADD PRIMARY KEY (`isbn`);

ALTER TABLE `book_issue`

ADD PRIMARY KEY (`issue_id`);

ALTER TABLE `librarian`

ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`);

ALTER TABLE `member`

ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `email` (`email`);

ALTER TABLE `pending_book_requests`

ADD PRIMARY KEY (`request_id`);

ALTER TABLE `pending_registrations`

ADD PRIMARY KEY (`username`);

Testing

Now it’s time to test our database. The below code is to test whether we are getting the correct output. We are getting to know the number of books named The Lost Tribe present in the library Sharpstown.

CREATE PROC dbo.bookCopiesAtAllSharpstown 
AS
SELECT copies.book_copies_BranchID AS [Branch ID], branch.library_branch_BranchName AS [Branch Name],
	   copies.book_copies_No_Of_Copies AS [Number of Copies],
	   book.book_Title AS [Book Title]
	   FROM table_book_copies AS copies
			INNER JOIN table_book AS book ON copies.book_copies_BookID = book.book_BookID
			INNER JOIN table_library_branch AS branch ON book_copies_BranchID = branch.library_branch_BranchID
	   WHERE book.book_Title = @bookTitle AND branch.library_branch_BranchName = @branchName
GO
EXEC dbo.bookCopiesAtAllSharpstown

(@bookTitle varchar(70) = 'The Lost Tribe', @branchName varchar(70) = 'Sharpstown')
);

How to create a Library Management System Project?

By following these streamlined steps, you can effectively develop and launch a Library Management System.

Conclusion

The library management system is essential for colleges, schools, and many other institutions these days. A lot of manual work can be reduced with this system. Implementing a library management system project in SQL minimizes errors such as wrong borrow dates and miscalculations of fine amounts. Since it is a computer-managed system, these glitches are avoided, ensuring accuracy and reliability.

Hope you like the article, and now you clear your understanding about the library management system projects. And How to create library management system project.

Moreover, the library management system project in SQL is efficient and cost-effective. It stores comprehensive details of books and user information, making it easier to manage and access records. This system is a significant upgrade from traditional methods, enhancing the management and operational aspects of libraries. Hope you like the article and get understanding about the library database management system project. You will get to know about all the concept and installation of library database management.

Key Takeaways

• To build a database to maintain all the related information
• We built tables separately to store data.
• Learned the purpose of the library management system.
• What features are required for students and librarians to use LMS?
• We have seen all the implementations using MYSQL
• and how the software allows storing all the details related to the library.
• Finally, we tested the final database.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Karpuram

Hello Everyone,
This is Srivani. I had completed my B.Tech in the computer science department. I am interested in Data Science and programming. Thanks for reading my articles and hope you get knowledge from them.