Karpuram Dhanalakshmi Srivani — Published On July 31, 2022 and Last Modified On July 31st, 2022
Beginner Data Engineering SQL

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

Introduction

In this article, we will build Library Management System using MYSQL. We will build the database, which includes tables. 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 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.

library management system
Source: Lovelycoding.org

Purpose of Library Management System

Library Management System is a system that shows all the available books and their count and also books taken by people, the date on which they took that particular book, expected date of return, late due fees, membership details, and so on. Everything will be crystal clear. There will be no ambiguity. It will be beneficial for both students and librarians.

This library management is very efficient and also cost-effective. It saves a lot of time for both librarians and also students. With this, manual work is reduced, requiring less staff and maintenance. This system is user-friendly and also 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.
library management system

Source: Rovan Software Solutions

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

 

MYSQL
Source: Amazon AWS

 

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', '[email protected]$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

Structure of 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$#[email protected]', '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')

Conclusion

The library management system is essential for colleges, schools, and many more places these days. A lot of manual work can be reduced with this library management system. And also, a lot of glitches like wrong borrow date and miscalculation of fine amount are avoided. As it is a computer-managed system and so these are all avoided. It is also efficient and cost-effective. The Library management system stores the details of books and also details of persons. So overall, we have seen-

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

I hope you guys found it useful.

Connect with me on LinkedIn.

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

Leave a Reply Your email address will not be published. Required fields are marked *