LIBRARY MANAGEMENT SYSTEM

LIBRARY MANAGEMENT SYSTEM

Farouq Umar Idris
Database Concepts and RDBMS (CIS206)
01/10/2013
Alka Khurana

LIBRARY MANAGEMENT SYSTEM

ABSTRACT
Library Management System has tremendous importance in managing library
database most especially when dealing with large number of customers. This project shows
how important upgradation of library management system to e-library management is with
the concepts of showing relationships using primary keys and foreign keys in both SQL and
Microsoft Access. It also shows how different SQL is to Microsoft access though both are
used in maintaining and manipulating data in database but at some point have their
differences.

LIBRARY MANAGEMENT SYSTEM

Contents
ABSTRACT............................................................................................................................... 2
1.0

SQL DEFINITION ......................................................................................................... 4

1.1

DATA MANIPULATION LANGUAGE....................................................................... 5

1.2

DATA DEFINITION LANGUAGE............................................................................... 5

1.3

DATA CONTROL LANGUAGE .................................................................................. 5

2.0

DATABASE AND MANAGEMENT SYSTEMS......................................................... 6

2.1

DBMS ............................................................................................................................. 6

2.2

RDBMS........................................................................................................................... 6

2.3

DIFFERENCE BETWEEN DBMS AND RDBMS ....................................................... 6

3.0

ABOUT THE SYSTEM ................................................................................................. 8

ER DIAGRAM .......................................................................................................................... 9
FORMS .................................................................................................................................... 22
CREATING TABLES IN SQL AND INSERTING VALUES ............................................... 28
Reference ................................................................................................................................. 34

LIBRARY MANAGEMENT SYSTEM

1.0

SQL DEFINITION
Structured Query Language (more often known as SQL) is a programming language

specially designed to help user in manipulating data that are stored in relational database
management system (RDBMS).
SQL has three basic languages.
1. Data manipulation language (DML)
2. Data definition language (DDL)
3. Data control language (DCL)

LIBRARY MANAGEMENT SYSTEM

1.1

DATA MANIPULATION LANGUAGE

These include the following:

1. Select
2. Delete
3. Update
4. Insert

1.2

DATA DEFINITION LANGUAGE

These include:
1. Create
2. Drop
3. Alter

1.3
DATA CONTROL LANGUAGE
These include:
1. Grant
2. Revoke

LIBRARY MANAGEMENT SYSTEM

2.0

DATABASE AND MANAGEMENT SYSTEMS

A database is a collection of organised data. e.g. collecting information about all

employees in an organisation.

2.1

DBMS
Database management systems (DBMS) are applications specially designed for the

purpose of interacting with the user, the built database and other applications when capturing
and analysing data. There are lots of software systems that are general-purpose database
management system software which allows user to create, define, make querry, update and
administer databases. Some of this software includes Microsoft access, MySQL, SQLite,
FoxPro, dBASE, Oracle, FileMaker Pro etc.

2.2

RDBMS
Relational database management system (known as RDBMS) is an extension of

database management system which is based on relational modelling. It has become a

predominant choice of information storage in newly created databases used in personnel data,
logistics, manufacturing, finance etc. RDBMS has replaced the legacy databases and network
database used nowadays because it is easier and faster to use and understand

2.3

DIFFERENCE BETWEEN DBMS AND RDBMS

1. DBMS is used in just managing database while RDBMS is used to maintain relationship
among tables.
2. DBMS accepts flat file entry data type but RDBMS does not
3. RDBMS is used in complex applications while DBMS is used in simpler applications
4. RDBMS enforces the use and rules of foreign key but DMBS does not

LIBRARY MANAGEMENT SYSTEM

5. DBMS manage only small entry of data while RDBMS can manage both large and small
entry sets of data.
2.4

DIFFERENCE BETWEEN SQL AND ACCESS

1. SQL Server is a server based database while Microsoft Access is used as local database.
2. Access is used for small desktop size databases used by less than 5 users at the same time
and have a front end GUI system to design applications quickly while SQL Server is a
more robust system and is able to handle large amounts of users as well as data sizes and
does not have front end GUI system so it will require other development tools (Visual
Studios, .NET, VB, C++, etc.)
3. Access employs security based on the actual database settings. Each database can have
different security settings and employee lists. Logins are not tied to a users Windows
logon while in SQL, On Center Software recommends using Windows Authentication
when setting up an SQL database, that way, the users Windows security is transferred to
the database and only valid users can access the database AND the security is centralized.

LIBRARY MANAGEMENT SYSTEM

3.0

ABOUT THE SYSTEM

A library is a collection of organized information and resources which is made

accessible to a well-defined community for borrowing or reference sake. The collection of the
resources and information are provided in digital or physical format in either a building/room
or in a virtual space or even both. Librarys resources and collections may include
newspapers, books, films, prints, maps, CDs, tapes, videotapes, microform, database etc.
The main aim of this system is to develop a new programmed system that will convey
an ever lasting solution to the manual base operations and to make available a channel
through which staff can maintain the record easily and customers can access the information
about the library at whatever place they might find themselves.
The library has the following tables in its database;
1. Books (book_id, bookName, bookisbn, BOOKAUTHOR and bookedition)
2. Customer (customer_id, customername, customer_email, customer_address)
3. Staff (staff_id, staff_name, staff_address, staff_gender, staff_phone)
4. Branch (branch_id, branch_name, branch_location)
5. Issue (issue_id, issue_date, expiry_date, book_name, book_id)
6. Return (return_id, expiry_date, isuue_date, book_id)
The attributes are used to identify and keep track of each item (entity) in the library using
relationships within them which is established by the use of unique key identifiers (concept of
primary and foreign keys)

LIBRARY MANAGEMENT SYSTEM

ER DIAGRAM

HAV
E

branchnam
e
BRANCH

Branch_id

location

LIBRARY MANAGEMENT SYSTEM

10

The database store the daily activity carried out in the library, the database system
provides a supported form for the customer to make booking as well as to retrieve and modify
the existing records from the database. In order to have full data administration, this data can
be promoted, retrieved, manipulated and updated, to get a new record of the services rendered
in the library. Therefore, the library would have a new computerized system that would
record the information of the daily services carried out within the library and all its branches.
In order to overcome the current problem the library is facing, the following solutions
need to be deployed;
1. Computerized customer payment system
2. Digital ID for customers and staff
3. Use of a good RDBMS software in manipulating data in the database
4. RFID chip installed in books to ease transactions and security purpose.
5. Restrict access to database to only administrators and/or authorised personnel only

LIBRARY MANAGEMENT SYSTEM

4.0

TABLES IN MICROSOFT ACCESS

Book table

Book table showing how data types are taken I n the book table

11

LIBRARY MANAGEMENT SYSTEM

Issue table

Issue table design view

12

LIBRARY MANAGEMENT SYSTEM

Customer table

Customer table design view, showing data types.

13

LIBRARY MANAGEMENT SYSTEM

14

Branch table

Above is the design view of branch table, showing how data are taken and how the data types
are defined.

LIBRARY MANAGEMENT SYSTEM

15

Return table

The return table has RETURN_ID which was taken as the primary key and a data
type set as number (autonumber). Other fields like BOOK_ID, ISSUE_ID, CUSTOMER_ID,
STAFF_ID are the foreign keys in the table having a data type of number.

LIBRARY MANAGEMENT SYSTEM

Staff table

In the table above, the staff_id is taken as the primary key and BRANCH_ID is the
foreign key in the table which has a data type number. Other fields like STAFFNAME,
STAFFADDRESS etc. are having a data type of text.

16

LIBRARY MANAGEMENT SYSTEM

Above is a screen capture of a database titled library created in Microsoft access showing
tables and forms created.

17

LIBRARY MANAGEMENT SYSTEM

The above screen shot show the book table in Microsoft access

18

LIBRARY MANAGEMENT SYSTEM

Customer table showing customers name and few more detail of the customer like email
address and customer id

19

LIBRARY MANAGEMENT SYSTEM

Issue table shows how book are issued to customers using the customer_id and book_id

20

LIBRARY MANAGEMENT SYSTEM

Library branch table showing branches with their respective locations and IDs

21

LIBRARY MANAGEMENT SYSTEM

22

FORMS
Forms can be used to update records in a master table. For instance a new customer
comes in and makes a transaction (e.g. he was issued a book), a new form will be made under
the customers ID and update his record through the form (i.e. if he is an existing customer)
or create new record for him (if he is a new customer).

LIBRARY MANAGEMENT SYSTEM

Book form, for updating or creating new book record in the database in Microsoft access

23

LIBRARY MANAGEMENT SYSTEM

Staff form, also used to update record of staff in the database.

24

LIBRARY MANAGEMENT SYSTEM

Return of borrowed books form. Used for monitoring return of book in the database.

25

LIBRARY MANAGEMENT SYSTEM

This form has to be filled before giving any book out to any customer.

26

LIBRARY MANAGEMENT SYSTEM

Customer data form. It is made very simple and easy in order to capture the
customers information when registering with the library.

27

LIBRARY MANAGEMENT SYSTEM

28

CREATING TABLES IN SQL AND INSERTING VALUES

Above is database titled library created in SQL using MySQL application. The
database library is created and a table (book) was created in it where various values were
assigned to it. Among the values is Book_id which represents the primary key of the table.
Insert values in done by using the command insert into where it allocates the written
value into the values assigned.

LIBRARY MANAGEMENT SYSTEM

29

Above is the view of the inserted vales into the table book. Viewing the values is
done by using the command select *from book after executing it, it displays all the inserted
values as seen above.

LIBRARY MANAGEMENT SYSTEM

Table above shows how to create another table (branch) in the database library.

30

LIBRARY MANAGEMENT SYSTEM

After executing, it shows the values inserted in the table.

31

LIBRARY MANAGEMENT SYSTEM

This is the relationship generated for the entire tables generated from the database

32

LIBRARY MANAGEMENT SYSTEM

7.0

33

CONCLUSION
SQL and Microsoft access are all database management application which is very well
used in the modern world in organising and manipulating a database.

Though they have their differences like the GUI interface Microsoft access is having and
SQL doesnt have, they all manage the database comfortable.

Depending on the user or users, if an organisation have multiple users then they should go
for SQL server based application else if, they should go for Microsoft access.

This project shows how to create tables in both SQL and Microsoft access and also how
to create simple data manipulation language and data definition language with how to
execute them.

It also shows how relationships are established with the concepts of primary and foreign
key within a table.

Lastly, the project shows how queries are created in SQL server, queries like the create
command, view, update, alter etc.

LIBRARY MANAGEMENT SYSTEM

Reference
1. Henry J. Lucas, 2008, Database management for business organisation fifth edition.
2. Alfanso Sucre, 1998, Managing business database second edition.
3. www.sqlquerry.net
4. www.w3schools.com/sql/

34