Library System Database Design

Title: Library System Database Design


The Library System is a computer-based application used to automate a library. It allows the librarian to maintain the information about books, magazines and other library materials. It also allows the librarian to maintain and organize the information about its clientele or borrowers.

This project focuses on the automation of system process of adding newly acquired books, borrowing books and borrower’s information, returning of books, searching for the location of the books and printing of the inventory of books in the library.

The automated library system provided electronic list of books and generated report related to transactions in the library such as borrowing and returning of books. In addition, automated library system has also a database feature which allowed the end-user to back-up and restore information in the database.

This was also designed based on the needs of the office and its clientele. Transactions were done instantly, securely, safely, quickly and efficiently.

Database Schema with description of table:

Book table will store the basic information of the books such as the title, authors, publisher, copyright year. The table is connected to the subject table.

tblbook (id, isbn, bookTitle, bauthors, serialNo, subjectid, description, publisher, copyrightyear, noOfPages, noOfCopies, dateReceived, dateEncoded, encodedBy)

The subject table will store information on the different subjects or topic. This will serve as the categories of the books.

tblsubject (id, subjectName, description)

the library system will be implemented in the senior high school thus the grade level table will store information of the different grade level in the school.

tblgrade (id, gradeNo)

Section table will hold the list of different class sections

tblsection (id, sectionName)

borrower table will store basic information of the borrower such as lastname, firstname and contact. The table is connected to the grade level table.

tblborrower (id, fname, mname, lname, contact, category (student, teacher,) gradeid)

borrowed book table will store information on the books borrowed which includes the borrower information, date borrowed and the date it will be returned.

tblborrowedbook (id, bookid, borrowerid, dateborrowed, duedate, numCopies, status(returned, not), processedby, datereturned, receivedby)

Fine table stores information about the amount of fine per day.

tblfine (fineamount)

user table will store information of the persons who will use the system, it includes the username, password, name, contact the user role.

tbluser (id, username, password, fullname, contact, usertype (librarian, staff))

Table Relationship:

Library System Table Relationship
Library System Table Relationship

Sample Query:

The query below will display the list of due date borrowed books

msql = " SELECT tblbook.booktitle, tblborrowedbook.dateborrowed, tblborrowedbook.duedate, Concat(tblborrower.lname, ', ', tblborrower.fname, ' ', tblborrower.mname) as bname " & _
" FROM tblbook INNER JOIN (tblborrower INNER JOIN tblborrowedbook ON tblborrower.ID = tblborrowedbook.borrowerid) ON tblbook.ID = tblborrowedbook.bookid " & _
" WHERE CURDATE() > duedate and bstatus ='Borrowed' or bstatus='Rent'" & _
" GROUP BY tblbook.booktitle, tblborrowedbook.dateborrowed, tblborrowedbook.duedate, tblborrower.fname, tblborrower.mname, tblborrower.lname;"

Function to Display the Real-time Inventory Report

Real-Time Inventory Report
Real-Time Inventory Report


  • SCRUD (search, create, update, delete)
  1. Book Info
  2. Category Info
  3. Borrower Info
  4. Course Info
  5. Update Fine Amount
  6. User Info
  • Process Book Issue for borrowers (borrower can borrow more 1 one book)
  • View/Print all borrowed book list
  • View/Print all unreturned book list
  • View/Print inventory of books
  • View/Print Borrowers Card (all the books borrowed by the borrower)
  • View/Print Book Card (list of borrowers who borrowed the book)
  • Activity Log
  • Process Fine
  • View Violation of Students
  • View/print User Log

Note: Staff can only encode information, can’t delete, update information


After conducting an investigation and determined all the results, the researchers formulated the following conclusions that can give the readers a good understanding about this study.

  1. Majority of the respondents claimed that the performance of the manual system in terms of speed, accuracy, security and usability is good. Their current system is okay, but most of the respondents want to have the better or best performance of the system.
  2. There is a need to change the manual system into an automated system for a possible improvement.
  3. Most of the respondents prefer to replace the current library system into an automated system for it would be a great help to improve the library transactions.
  4. The need to replace the current system into an automated system is highly agreed by the respondents.
  5. The use of automated library system is accepted by the respondents.


you can leave your comments, suggestions and questions on the comment box below or you may visit our facebook page.

, , , , , , , , , , , , , , , , , , , , ,

Post navigation