Library Management System Database Project using PHP & MySQL/MS Access

EXECUTIVE SUMMARY

Libraries are popular places where there are numerous books to keep track of. Not only books but the librarian is also required to keep track of users, books that were taken, due dates, etc. Making manual entries and keeping track of due dates is not easy when the user’s size is more. It becomes complex when there are numerous books and when the members of the library are increasing. Tracking members and books details become time-consuming and are prone to error. Thus, the objective of the project is to come up with easy to use and user-friendly database management application that helps the librarian with all the tasks related to efficient library management.

The application is expected to be a secure, user-friendly, and easy-to-use database application for library management, that is capable of performing library tasks like adding books, searching books, issuing books, returning books, and generating fines and reports. The application is expected to simplify the librarian tasks, reduce errors in bookkeeping, and make the task interactive and interesting.  

The project management and development shall be iterative and shall employ the RAD application development methodology. The rapid Application Development methodology is a fast way of completing a project prototype with more emphasis on the design phase and application development. With RAD in use, the application development shall be broken down into smaller tasks that are easily manageable and monitored.

At the end of the project, there shall be a live web-based application with an easy-to-use and rich navigational front end designed with JavaScript and PHP and a back end with My SQL. Both platforms are open-free and thus making applications cost-effective.

PROJECT PROPOSAL

1.     Statement of Work

1.1  Project Background

Database management systems have become vital for organizations to manage large databases and to perform transactions upon such large data. These database applications not only store data, but also manage them, synchronize them, and help in information retrieval without errors. They reduce manual efforts and enhance the quality of information retrieval services. Due to this reason, they are widely used in almost all sectors. Libraries are popular places where there are numerous books to keep track of. Not only books but the librarian is also required to keep track of users, books that were taken, due dates, etc. Making manual entries and keeping track of due dates is not easy when the user’s size is more. Thus, this work implements a library management system database application that helps the librarian manage all tasks in an efficient and user-friendly manner.

1.2  Vision

The project vision is to come up with a library management system database application that does the jobs of the librarian like maintaining book records, maintaining user records, due dates, fines, etc. efficiently and in a quick time without errors.

1.3  Project Objective

The objective of the project is to come up with easy to use and user-friendly database management application that helps the librarian with all the tasks related to efficient library management.

1.4  Project Scope

The project scope is:

  • To have a user-friendly and easy-to-use database application for library management.
  • To have an application that secures data records.
  • To have an application that generates reports on books, due reports, fine reports, etc. easily.
  • To have an application that can track the user activities and their records easily.
  • To have an application that reduces the errors and efforts of the librarian.
  • The application shall have a login and password for allowing only authorized users to access the application.

1.5  Value proposition

This application shall reduce the manual errors and efforts of the librarian. It shall provide the end-user with the abstraction of all the database details and thus simplify the task as a set of small activities. It shall enhance the quality of the services of the library. It shall keep better track of book availability, dues, fines, etc., and enhance the quality of response to the user by the librarian. 

1.6  Technical details

Existing system:

Currently, the library system in use is a mixture of both file-based and manual-based work. Excel files are used to keep records of books, library users, and library user transactions. With such a system the key challenges are

  • More duplicate records as there is no control on duplicity.
  • inconsistent records are more as there is no referential integrity control
  • Time-consuming tasks as a simple tasks may require extensive search and a lot of entries.
  • such a system is prone to errors due to inconsistencies in data
  • Lack of user-friendly GUI: the system lacks a GUI that facilitates the end-user by providing an abstraction of the back end. This abstraction of the backend makes the end-user efficiently do his or her job without worrying about other details.
  • Data loss risk: if the data is lost it becomes impossible to recover the data from excel files.
  • Generating reports of books or users with fines becomes difficult and time-consuming as it requires multiple worksheets to be navigated and used. Due to the cumbersome job, the accuracy of the reports is low.

Thus, it is proposed to come up with an efficient, user-friendly, library management system that makes end-user tasks easy, error-free, and fast. The system shall follow the norms of a database management system ensuring integrity, consistency, and no duplicates of data.

Thus, the system shall have a simple to use GUI as the front end. Database application shall be developed using My SQL. The front-end JavaScript & PHP. The operating system is Microsoft Windows 7 and above with either 32 or 63 bits configuration.

1.7  Challenges

The Key challenges the project development may face are as follows:

  • The existing data from excel can be imported to MY SQL database. But to ensure referential integrity of the data it is essential that the database follows norms of having the primary key, removing duplicates ensuring consistency, etc. Thus, it may require the excel data to pre-process, convert and then migrate it to MY SQL.
  • The end-user may be new to GUI and API usage. Thus, there may be a need to train the end-user with the application.
  • Issues may arise from front-end and back-end connectivity. This can be managed through proper coding.
  • The system development may face hurdles if its development process is not managed and monitored properly. It is essential to have a Gantt chart with proper milestones to monitor the activities.

1.8  Organization of the proposal

This project proposal is divided into sections. The first section was a background of the project giving details on its vision, value proposition, objectives, and scope.

Sections 2 to 6 details are as follows:

  • Section 2: This section gives in detail the project methodology and the implementation plan. In this section the database design principles and methodology employed is elaborated, the way the application is developed is defined and the key areas of the application along with key features and functionalities are explained through various diagrams. It shall also contain the test plans for the application.
  • Section 3: This section defines the key deliverables from this project. It thus gives details on what the project shall give for a specified set of inputs and how the result is used.
  • Section 4: This section defines how the application project management shall be carried out. It shall give details of the milestones to be achieved and details on project monitoring and evaluation.
  • Section 5:This section gives details on the project team and their roles
  • Section 6: This section is about the tools and the supporting platforms that make the application implementation possible.

2.     Methodology and Implementation Plan

The database design and implementation process shall be done on the MySQL database management platform. The project management and development shall be iterative and shall employ the RAD application development methodology. The rapid Application Development methodology is a fast way of completing a project prototype with more emphasis on the design phase and application development. With RAD in use, the application development shall be broken down into smaller tasks that are easily manageable and monitored. With regular communication and feedback among the team, the application development becomes faster and more efficient with RAD. The key phases of application development thus are as follows:

  1. Requirement analysis
  2. Application Design
  3. Rapid construction & Testing
  4. Going live

2.1  Requirements Analysis

This is the first phase of the application development lifecycle. The objective of this phase is to gather the end-user requirements and the expectations of the stakeholders from the library management system. These are listed in the form of functional and non-functional requirements.

Functional requirements

  1. Login/logout: The application should provide user-based login and logout mechanism. Thus authorized users can login into the system.
  2. Add book: This shall allow the librarian to add the book record into the database.
  3. Manage book: This shall allow the librarian to edit or delete any book record in the database.
  4. Search book: This shall allow the user to search for a book through book name, author name, or both.
  5. View book: This shall allow viewing all the books, new books, old books, damaged books, lost books.
  6. Issue Book: This shall make an entry of the member with the books browed and the due to return.
  7. Accept Return book: This shall enter the return date and ensure that the book is successfully returned.
  8. View issued books: This shall list the books that have been borrowed with their member name and due date.
  9. View returned books: This shall consist of lists of returned books.
  10. Add member: This shall help the librarian to add a new member and categorize them.
  11. Manage members: This shall make changes in the member record like deactivating them, deleting them, and editing their information.
  12. Search member: this shall help in searching the library member record
  13. View fine details: This shall generate a fine for those borrowed books with member details that skipped their due dates.
  14. Generate report: this shall allow the system admin to generate user reports, book reports, borrower reports, fine list reports, etc., and download them.

Non-functional requirements

  1. Security: the application should endure data security and user access security.
  2. Navigational requirement: The navigational options of the system should be user-friendly and easy to use.
  3. Database requirements: The database should be accessed by only secure and authorized users. The database should be available, consistent, have no duplicate entries, and should give error-free output.
  4. Performance requirement: the system should be responding with a proper message thus giving an idea to the user of the interactions.

Use case diagram

Following is the use case diagram of the system

Figure 1: Use case diagram

Activity diagram: Consider the process of adding a book record

The steps of adding the book record to the database are as follows:

Figure 2: Activity diagram

1.1  Conceptual Design

ER diagram for the Library Management System

Figure 4: ER Diagram

1.2  DBMS cost/benefit analysis

The technology that shall be used for the proposed system is the MySQL database management system. It is used because it is simple to use, supports a large database system, and has compatibility with various programming languages that can support the front-end design.

The front-end technology relies on JavaScript and PHP. These are selected as they offer good design options, navigational options, making the user interface interactive and more appealing. Thus, with consideration to having a good and user-friendly interface, these technologies are selected.

The other options were to use the Oracle database management system and MS Access database management system. But MS access system was ruled out due to its failure to support large databases and its difficulty in providing front-end connection to programing and scripting languages like PHP. Oracle was ruled out as it is tough to use and is costly when compared to MY SQL which is free and open source.

Product

Cost

Option 1 Oracle

Standard Edition One – $5,800 per unit (sockets)

Option 2 MS Access

$109.99 per license

Option 3 My SQL

Free

 

Front end option 1 Java and swings

Free but requires programming expertise

Front end option 2 PHP and Javascript

Free requires scripting expertise

Front end option 3 ASP.NET

Free requires scripting expertise

 

Based on the above and the available expertise, the project is built using MY SQL, PHP ad Java Scripts is selected.

1.3  Logical Design

The main tables proposed for the system are as follows:

User table:

User_id

Username

password

firstname

Lastname

Primary key int

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Book table

book_id

Book_title

Author

Book_copies

Book_pub

Publisher_name

Isbn

Copyright_year

status

Category_id

Primary key int

Varchar(100)

Varchar(100)

int

Varchar(100)

Varchar(100)

Varchar(50)

int

Varchar(30)

 

Borrow

borrow_id

Date_borrow

Due_date

Member_id

Primary key int

Date

date

Int

Member

member_id

Firstname

lastname

Gender

contact

address

type

Status

Primary key int

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Return

Return_id

Book-id

Borrow_id

Borrow_status

Date_return

Fine

Primary key int

int

Int

Varchar(100)

Date

int

1.4  Physical Design

The proposed system shall have a user interface form designed and developed using JavaScript and PHP scripting languages. The key issue in this design is the special emphasis on navigational options, proper usage of arrows and icons so that the user can operate the system without any hurdles. The user navigation should give control freedom to the user. It should not be complex and should be user-friendly.

The database backend is designed and developed using MySQL. It can be problematic to establish front and back-end connectivity while using the application. Thus, in such a case proper commands for the connection must be established.

1.5  Prototype

A sample prototype is as follows:

Front end admin login screen

Front end admin login screen

Back end book table

Adding books

Adding members

1.6  Performance Evaluation

The Project is successful only if it is capable of giving expected results. Thus, the application is to be tested to ensure that it is executing as per expected objectives. The proposed application shall be using black-box testing wherein test cases shall be built and executed. The test cases shall test the user interfaces and each use case. If the expected results are achieved the application shall be assumed to be error-free and ready for launch. Otherwise, the test result recommendations shall be implemented.

2.     Expected Result

At the end of the project completion following things shall be expected:

  • A back end designed and developed in MySQL with tables for users, members, books, return records, borrowing records, searching capabilities, and report generation.
  • A front end that is user-friendly, rich with icons, and is appealing to use. The front end shall be designed so that the user can perform the actions without any issues.
  • A fully functional application with both front end and back end connectivity capable of doing all the said tasks of use cases.
  • An application manual describing the ways to use the application
  • A cost-benefit analysis describing the feasibility of the product in economic terms.
  • A requirement analysis documents and work breakdown structure.
  • A complete coding and scripting file
  • A test project report giving details of project implementation and test results.
  • Final project document

3.     Project Management Approaches & Milestones

The objective of project management is to ensure that the project development is planned well and executed properly to ensure quality and timely delivery.  For proper management of the application development, the project development process is broken down into small tasks. Each task is grouped as milestones. For each milestone, a deadline is assigned and an expected outcome checklist is prepared.

Once a milestone is reached, the checklist is evaluated and the reviews are done with the team members. The outcome of the checklist evaluations and the review are recommended and implemented to ensure that the project is meeting its quality standards.

To ensure the tasks are executed on time a Gantt chart is used. The Gantt chart for the project is as follows:

Figure 4: Gantt Chart

Here you can download the entire project source code, User Manual Report.

4 Replies to “Library Management System Database Project using PHP & MySQL/MS Access”

Leave a Reply

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