Implementation Of The Library Management System Technology Essay

This report focuses on the design and implementation of the library management system. It deals with the complete processes on building and implementing it. It focuses in the technical aspects of the system starting with identifying the necessary components and building the relevant relationship between or among them as needed for the smooth and efficient operation of the system.

The process starts with the entities involved in the system, with proceeding towards the ER Diagram in order to identify the meaningful relationship between the entities. Next is the table design which fulfils the normalization principle of relational database system and finally the physical tables are created with the necessary and relevant data in them.

The system can keep track of the members joining and leaving the library, borrowing and returning of the books. Checking the availability of the books using different attributes as parameters. Finally checking of the overdue books and fines payable is another feature of the system.

Abbreviation

SQL Structured Query Language

ER Entity Relationship

ERD Entity Relationship Diagram

DDL Data Definition Language

DML Data Manipulation Language

1. Introduction:

Library management system is a system that facilitates the easiness in using and tracking the library assets. It provides an instant real picture and process of all the activities that happens in a library commencing from the member joining the library and the same leaving the library with all the utilization and rendering of the library facilities in between.

1.1 Background:

A library is a collection resources especially books that a wide range of individuals can access and share them. Library has been in use since 15th century as has been gone through several stages of improvisation and it’s been in the form as today. Library can be categorized into different types according to the organization that runs it as Academic library, public library, School library etc. Further more it can be classified according to the subject matter of the documents it contains as Medical library, Law library, Arts Library etc. Libraries are organized in a way to access the materials in an easy an effective way. There are several systems in practice which makes the library organized. Library is basically concerned about acquisition, preservation and administration of its resources. In order to carry out these tasks a system is implemented. All these process has been carried out manually before the advancement of new technologies. Now the computer automated system is in practice to carry out these systems which makes all these processes effective and efficient. These computer automated systems makes easy for the members to access the available resources where as it makes easy for the librarians to keep track and maintain the library resources.

1.2 Objective:

The objective of the entire project

Design and implement a library database.

Construct the expandable search alternatives for the best handling of member queries, searching the books by book title, book author, ISBN.

Construct the search option for the librarian to find the over due books and fine details.

1.3 Scope:

The goal of this project is to design a database for a college library which will be implemented in the form of an online library which provides members a digitized catalog in order to search the books and browse information about the book. In the same time this system also enables the librarians to find the information of the book in a precise way especially the fine and overdue books. The summarized activities are as follows:

Members registration

Book issue and collection

Book cataloging

Collecting overdue book and fines

1.4 Technology Used:

Technology used in this project is MYSQL.

2. Current system and its fallacy

The library is managed, organized and run by the librarian. All the functions like managing books, issuing books, and finding the books for the members is carried out by librarian. This is where the time and effort is consumed. Whereas the members also find difficulty in finding availability the books and need the assistance of librarian in every step. The librarian also finds it tedious if s/he has to deal with several members at a same time. There is a difficult system

2.1 New system and its features

The new system is also based on the daily routines of the library but the function carried out within it will be automated. Once all the data are stored in a database with a proper system both the librarian and members will be benefited over the traditional system as it is designed with the following features in mind:

Effective

The library system will streamline the library process which accelerates the effectiveness of the library.

Efficacious

The users are served in a reasonable time and are also able to search and select their required books.

Efficient

Members and Librarians are able to use the system quickly without any long procedures.

Elegant

Since the system is intuitive and comprehensive and is designed to be as effortless as possible

3. Literature Review

This report focuses on the conceptual design of the system using ER i.e. entity relation ship diagram which is a diagrammatic representation of the entities and their relation with one another which is explained in the forth coming section. The ER is constructed using the UML. UML stands for unified modeling language. Software engineering uses UML as the standardized modeling language. It was created by Object management group. UML is a language used to present the blueprint of the system to be designed. It has got its own notations to represent the processes and objects involved in the software to be designed.

ER Diagram is a graphical representation of the entities and relation between them in order to execute a system. This Diagram is constructed in some available ER Diagramming packages which is Visual paradigm community edition in this case.

MYSQL has been chosen as the database system for this library management system. Since it is relational database available under the terms of GNU public license. Besides the ease of using through a variety of freely available tools as PHPMYADMIN makes a choice of most of database designers today. Besides it is the database of choice because of its high performance, high reliability and ease of use. Moreover it runs on maximum operating systems available in the IT fraternity like windows, Mac OS, Linux, Solaris, IBM .

3.1 Requirement Analysis

The library management system should be able to fulfill the user requirements to all the extent and when analyzed are found as follows

Member should be able to join and leave the library

Member of a library should be able to borrow and return books

Member of the library should be able to check book availability.

Librarian should be able to check the overdue book and the fines payable.

4. New system Design

The library management system with the goal to cover the requirements noted above needs to be developed in a system which has the proven track of implementing the same or same type of systems. So, a relational database system is chosen to carry out the task. The relational database system is basically a system where data is stored in a container called tables and the relational among them is also stored in the same form. The new system follows the waterfall model of the software development models. The processes in execution order in a water fall model are:

Requirement analysis

Software Design

Integration

Testing

Deployment

Maintenance

4.1 Conceptual Data Model

The design of this data model is part of software design phase. After going through the requirement analysis the next step is the Software design and in it conceptual data model is the first among them. The conceptual data model identifies the necessary entities and establishes a relevant relation among them. In this design ER Diagram (Entity relationship diagram) is created to illustrate the relations between the entities. The diagrammatic conceptual representation of structured data is known as ER Diagram. Relational schema is used in this method for database modeling. The dominant method of database designing in the Software industry is the ER Diagram. Peter Chen invented the ER diagram in the early days to model the design of the databases from a more abstract perspective particularly for the academic research. Based on that, ER diagram was further elaborated and now is used worldwide in different forms. ER diagram deals with three major components of a database namely, Entity, Attributes and Relation.

Entity is nothing but a set of particular thing which shares common properties. Whereas attributes are the set of common properties which the entity share. The last but not the least component of an ER Diagram is a relation which is the model of association between one or more entities.

Each relationship in ER Diagram has a cardinality which defines the degree of relationship between the two entities. In other words how the related entities has presence over one another. Cardinality is of three types: one to one, one to many and many to many. In an ER diagram the one occurrence of the entity is shown by a vertical line and many occurrence of the entity is shown by the crow foot.

The relationship is established on the basis of several keys called primary keys and foreign keys. Besides there are other keys called candidate key and composite key.

Primary key:

The attribute which uniquely identifies a record in a table is called primary key.

Foreign Key:

As the name specifies this is the key which points the primary key of another table to which it is related.

Candidate Key:

The attribute that is identified to be unique for a given record is the candidate key.

Composite Key:

Primary key consisting of more than one attributes to identify the record uniquely is composite key.

For the library system the constructed ER Diagram is shown below.

4.2 Logical Design

The logical representation of the entities is drawn out in the logical design. It is a table prototype of the entities.

Based on the conceptual Design above we do have following table and entities mapping.

Member

member_id (integer),

member_fname (varchar), member_lname (varchar), member_address(integer), member_phone (varchar), member_email (varchar),

member_type(integer)

member_id is primary key and member_address and member_type are foreign keys to build the one to many relationship between address table and member table and member table and member_type table respectively.

Member_Type

member_type_id(integer), member_type_name (varchar), member_type_allowed_day (integer), member_type_fine (integer)

member_type_id is primary key.

Address

address_id(integer),

address_description (varchar),

postcode (varchar)

address_id is primary key.

Postcode

postcode_id (integer),

postcode_description (varchar),

postcode id is primary key.

Books

ISBN (integer),

book_name (varchar),

book_status (varchar),

book_type_id (integer),

book_publisher(integer)

ISBNis primary key, book_type_id, book_publisher are the foreign keys which relates the the book table with book type with one to many, and with publisher table in one to many relation ship as well.

Book_Type

book_type_id(integer),

book_type_name (varchar)

book_type_idis primary key.

Publisher

publisher_id (integer),

publisher_name (varchar)

publisher_idis primary key.

Author

author_id(integer),

author_fname (varchar),

author_lname (varchar),

author_email (varchar)

author_idis primary key.

Librarian

librarian_id(integer),

librarian _fname (varchar),

librarian _lname (varchar),

librarian _address(integer),

librarian_phone (varchar),

librarian_email (varchar)

librarian idis primary key, librarian_address is the foreign key which establishes the one to many relationship with address table.

Book_Author

ISBN(integer),

author_id(integer)

ISBN is the foreign key which maintains one to many relation with books table and author_id is the foreign key which establishes the one to many relationship with author table. Thus in this way Many to Many relationship is established between Book and Author table.

Borrow

borrow_id(integer),

member_id(integer),

ISBN(integer),

librarian_id(integer),

borrow_date (date),

return_date (date),

expected_return_date (date)

borrow_id is the primary key and memer_id, ISBN, librarian_id are the foreign keys. Member table and borrow table shares one to many relationship, Books table and borrow table shares one to many relationship and librarian table also shares one to many relation with borrow table involving the mentioned foreign keys. In other words many to many relation ship occurs between these entities i.e. many to many between librarian and books, member and books.

Fine

fine_id(integer),

borrow_id(integer),

librarian_id(integer),

fine_amount (float),

fine_id is the primary key and borrow_id, librarian_id are the foreign keys. Fine is associated with borrow in one to one relation whereas librarian is associated in one to many relation.

4.3 Normalization

After the conceptual model the logical representation of entities are created and then before converting them into the tables with physical existence normalization is carried out. Normalization is process by which the data redundancy is nullified. This is achieved by disintegrating the single relationship with ambiguity into multiple smaller and precise relations. The tables holding such data are split into several atomic tables so that they become isolated and the data manipulations are carried out in a propagative way i.e. a change in an entry point data in a relation makes it triggered and reflected throughout the relations which frees tables from modification anomalies, i.e. insertion, updation and deletion anomalies.

Several levels of normalization exist in database design and are called Normal Forms. They are First Normal Form (1NF), Second Normal Form (2NF) and Third Normal form (3NF).

First Normal Form (1NF)

First Normal form is concerned with multiple valued attributes. For the table to be in a first normal form it cannot contain multiple values for any attributes.

Second Normal Form (2NF)

For the table to be in second normal form the non key attributes of the table shouldn’t be partial dependent on any single element of composite primary key.

Third Normal Form (3NF)

For the table to be in third normal form the non key attributes shouldn’t have the transitive dependency on the primary key.

The tables for the different entities are normalized in following manner:

Books Table (without normalization):

Fields (ISBN, book_name, book_status, book_edition, book_type, book_publisher, book_author) where ISBN is the primary key.

books table before normalization

In the above table the author value is repeated in the book_author table, for the table to be in 1NF, the table cell must contain a single value. The next is the value of book publisher and book type is repeated in multiple rows so it results in the update, insert and delete anomalies.

To overcome this problem the author name and publisher entity are separated into multiple tables as shown below. The author table is created with author_id as primary key.

Author Table:

Fields (author_id, author_fname, author_lname, author_email) where author_id is the primary key.

author_id

author_fname

author_lname

author_email

1001

Max

Godwell

[email protected]

1002

Robin

Hood

[email protected]

The relationship between book and author table is maintained in book_author table which is many to many as many a book has many authors and an author can write many books. This relation is established by creating a third table book_author which maps book to author and vice versa. This table contains two fields both as foreign keys which refer to book and author table respectively to establish one to many relationship on both sides.

Book_Author Table :

ISBN

author_id

91238745654

Publisher Table:

Fields (publisher_id, publisher_name) publisher_id is the primary key. Similarly the repetition of publisher values in a book table is rectified by creating a publisher table as publisher_id as primary key which is then mapped as foreign key in books table to create one to many relationship, which rectifies the insert, update and delete anomaly thus creates 1NF in all cases.

publisher_id

publisher_name

Apress

Princeton

Book_Type Table:

Fields (book_type_id, book_type_name) book_type_id is the primary key. Similarly the repetition of book type values in a book table is rectified by creating a book_type table with book_type_id as primary key which is then mapped as foreign key in books table to create one to many relationship, which rectifies the insert, update and delete anomaly thus creates 1NF in all cases.

Book_type_id

Book_type_name

Computing

Economics

The second normal form deals with the dependency of the non key columns with the primary key, i.e. no non key columns can depend upon another non key columns or no non key columns can depends on any one column in case of composite primary key. The conclusion is all the non key columns must be dependent on primary key strictly. Since this is also satisfied in all the tables above all the tables satifies 2NF as well.

The third Normal form deals with transitive dependency of non key columns, since this is abolished in all the tables above all the tables are in 3NF as well.

Books Table (Normalized):

Fields (ISBN, book_name, book_status, book_edition, book_type, book_publisher)

ISBN is the primary key, book_type is the foreign key referring to book_type table and book_publisher is the foreign key referring to the publisher table.

Books table after normalization

Member Table:

Fields (member_id, member_fname, member_lname, member_phone, member_email, member_address, member_postcode, member_type, member_allowed_day, member_type_fine) member_id is the primary key.

Member table before normalization

The table above contains repeated address, repeated postcode, repeated member type, repeated member_type_allowed day and member_type_fine so this should be resolved to convert the tables in 1NF.

Since there is no composite primary key there is no partial dependency as such so once the above table is resolved to 1NF it becomes 2NF as well.

Since member_type_allowed_day, member_type_fine is dependent on member_type alone which is dependent in member_id it creates transitive dependency. So the table to be in 3NF this anomaly should be removed.

Again to overcome these anomalies the tables are separated and the relation between them are established accordingly.

The member, address, postcode and member_type tables are separated as below to convert into 3NF.

To avoid the repetition of member type in member table the member type is separated in member_type table with member_type_id as primary key and member_type as foreign key in member table which creates one to many relation between these two tables. This also rectifies the transitive dependency existing on the member table formerly and thus resolves the table to 3NF.

Member_Type:

Fields (member_type_id, member_type_name, member_type_allowed_day, member_type_fine) member_type_id is the primary key.

member_type_id

member_type_name

member_type_allowed_day

member_type_fine

Student

Lecturer

The dependency of address in postcode also creates the violation of 2NF through the dependency of non key attribute in another non key attribute other than primary key so this is further resolved by splitting address and postcode table separately.

The address table has address_id as primary key which is referred in member table as foreign key which establishes one to many relation between them. At the same moment the postcode table is created with postcode_id as primary key and is referred in address table as foreign key named postcode also creates one to many relation between postcode and address. Thus the 2NF violation in member table is abolished and further more all the resulting table satisfies all the Normal form conditions.

Address Table:

Fields (address_id, address_description, postcode) address_id is the primary key and postcode is the foreign key referring postcode_id in the postcode table which creates one to many relation between address and postcode table.

Address_id

address_description

postcode

121 East Road

45 Parr Road

36 Jhon Road

23 Plashet Road

Postcode Table:

Fields (postcode_id, postcode_description) postcode_id is the primary key.

postcode_id

postcode_description

E15 3QS

NW9 1NF

SE1 2NX

Member Table (Normalized):

Fields (member_id, member_fname, member_lname, member_phone, member_email, member_type, member_address) member_id is the primary key; member_type is the foreign key referring to member_type_id in member table to create one to many relations between these two table. member_address is the foreign key referring to address_id of Address table to establish another one to many relation.

As in case of librarian the repeating fields in a table address and which caused the insert, update and delete anomalies so violated 1NF and this is resolved by creating the Librarian table with the foreign key mapped to address table thus maintaining one to many relationship and thus abolishes any Normalization violations and thus results in satisfaction to all the Normal forms.

Library Table (without normalization) :

Fields ( librarian_id, librarian_fname, librarian_lname, librarian_phone, librarian_email, librarian_address, librarian_postcode) librarian_id is the primary key.

Library Table (normalized):

Fields (librarian_id, librarian_fname, librarian_lname, librarian_email) librarian_id is the primary key and librarian_address is the foreign key which refers to address_id in the Address table to create one to many relationship.

Borrow Table (without normalization) :

Fields (borrow_id, member_id, ISBN, issuing_librarian, borrow_date, return_date, expected_return_date, returning_librarian, fine_amount) borrow_id is the primary key.

Borrow table before normalization

The above table violates the 1NF by repeating the librarian ids at the same once it is resolved all other normal forms conditions are satisfied. The borrow table is divided into one more table as Fine and the librarian is associated to Borrow and Fine table separately through one to many relationships in both cases. This eradicates the violation of normal forms and thus makes the form normalized up to 3NF.

Fine table:

Fields (fine_id, borrow_id, librarian_id,fine_amount) fine_id is the primary key and borrow_id is the indexed foreign key which is mapped to the borrow_id of the borrow table, so as to create one to one relationship between them. The relation ship is created one to one as one borrow can only have one fine and not more than once.

fine_id

borrow_id

librarian_id

fine_amount

1001

1002

Borrow table (Normalized):

Fields (borrow_id, member_id, ISBN, librarian_id, borrow_date, return_date, expected_return_date) borrow_id is the primary key. member_id is the foreign key which referrs to the member_id of the member table thus creating one to many relationship in between. Similarly ISBN is the foreign key that refers to the ISBN in the book table thus creating one to many relation between them. Similarly librarian_id is the foreign key that is mapped with librarian_id of the librarian table to create one to many relationship again.

Borrow table after normalization

4.4 Physical Design

The physical design refers to the creation of the physical tables in the preferred database management system. In database management system there are the specific SQL called Data definition languages (DDL) to create the physical table and establish the relationship between the tables. They are CREATE, ALTER, And DROP. CREATE statement is used to create the physical structure of the table. ALTER statement is used to modify the existing structure of a table as adding and removing columns, constraints etc. DROP statement is used to delete a table.

For instance we have to create the tables as per the requirement. Here are the DDL statements to create the tables as illustrated in the ER diagram

CREATE TABLE Books (

ISBN INT NOT NULL ,

book_name VARCHAR( 100 ) NOT NULL ,

book_status CHAR( 1 ) NOT NULL ,

book_type INT NOT NULL,

book_edition INT NOT NULL ,

book_publisher INT NOT NULL ,

PRIMARY KEY ( ISBN )

);

CREATE TABLE Book_Type (

book_type_id INT NOT NULL ,

book_type_name VARCHAR( 50 ) NOT NULL

PRIMARY KEY ( book_type_id )

);

CREATE TABLE Author (

author_id INT NOT NULL,

author_fname VARCHAR ( 50) NOT NULL ,

author_email VARCHAR ( 100) NOT NULL

);

CREATE TABLE Publisher (

publisher_id INT NOT NULL,

publisher_name VARCHAR (100) NOT NULL,

PRIMARY KEY ( publisher_id )

);

CREATE TABLE Book_Author (

book_id INT NOT NULL ,

author_id INT NOT NULL

);

CREATE TABLE Member (

member_id INT NOT NULL ,

member_fname VARCHAR( 50 ) NOT NULL ,

member_lname VARCHAR( 50 ) NOT NULL,

member_phone VARCHAR( 25 ) NOT NULL ,

member_email VARCHAR( 100 ) NOT NULL ,

member_type INT NOT NULL ,

member_address INT NOT NULL ,

PRIMARY KEY (member_id )

);

CREATE TABLE Member_Type (

member_type_id INT NOT NULL ,

member_type_name VARCHAR( 50 ) NOT NULL ,

member_type_allowed_day INT NOT NULL ,

member_type_fine INT NOT NULL ,

PRIMARY KEY (member_type_id)

);

CREATE TABLE Address (

address_id INT NOT NULL ,

address_desc TEXT NOT NULL ,

postcode INT NOT NULL ,

PRIMARY KEY (address_id )

);

CREATE TABLE Postcode (

postcode_id INT NOT NULL,

postcode_description VARCHAR( 10 ) NOT NULL ,

PRIMARY KEY (postcode_id )

);

CREATE TABLE Librarian (

librarian_id INT NOT NULL ,

librarian_fname VARCHAR( 50 ) NOT NULL ,

librarian_lname VARCHAR( 50 ) NOT NULL ,

librarian_address INT NOT NULL ,

librarian_phone VARCHAR( 25 ) ,

librarian_email VARCHAR( 100 ) ,

PRIMARY KEY ( `librarian_id` )

);

CREATE TABLE Borrow (

borrow_id INT NOT NULL ,

member_id INT NOT NULL ,

ISBN INT NOT NULL ,

librarian_id INT NOT NULL ,

borrow_date DATE NOT NULL ,

return_date DATE ,

expected_return_date DATE NOT NULL ,

PRIMARY KEY (borrow_id )

);

CREATE TABLE Fine (

fine_id INT NOT NULL ,

borrow_id INT NOT NULL ,

librarian_id INT NOT NULL ,

fine_amount FLOAT NOT NULL

fine_paid CHAR( 1 ) NULL ,

PRIMARY KEY ( `fine_id` ),

INDEX (borrow_id)

);

Once the table physical structures are created then the relation among them are established as per the ER diagram and normalization.

Below are the DDL statements used to create the foreign keys so as to create the relationship between the tables as illustrated in ER diagram.

ALTER TABLE Books ADD FOREIGN KEY (book_type) REFERENCES Book_Type (book_type_id) ;

ALTER TABLE Books ADD FOREIGN KEY ( `book_publisher` ) REFERENCES Publisher( publisher_id ) ;

ALTER TABLE Book_Author ADD FOREIGN KEY ( ISBN ) REFERENCES books( ISBN ) ;

ALTER TABLE Book _ Author ADD FOREIGN KEY ( author_id ) REFERENCES Author( author_id ) ;

ALTER TABLE Member ADD FOREIGN KEY ( member_address ) REFERENCES Address( address_id ) ;

ALTER TABLE Member ADD FOREIGN KEY ( member_type) REFERENCES Member_Type( member_type_id ) ;

ALTER TABLE Librarian ADD FOREIGN KEY ( librarian_address ) REFERENCES Address( address_id ) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( member_id ) REFERENCES Member( member_id ) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( ISBN ) REFERENCES books(ISBN) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( librarian_id) REFERENCES Librarian( librarian_id );

ALTER TABLE fine ADD FOREIGN KEY ( borrow_id) REFERENCES Borrow( borrow_id );

ALTER TABLE fine ADD FOREIGN KEY ( librarian_id ) REFERENCES Librarian( librarian_id );

ALTER TABLE address ADD FOREIGN KEY ( postcode ) REFERENCES Postcode( postcode_id)

5 Data Manipulation language

Data manipulation language DML is SQL is the statement which used to insert, update, delete and retrieve the data from the table or multiple tables as per requirement. The INSERT statement is used to insert a record in a table. UPDATE statement is used to modify the value of the attribute in a record and DELETE statement is used to delete a record from the table.

5.1 DML to insert dummy records

INSERT INTO Postcode (postcode_id ,postcode_description)

VALUES (‘2’, ‘NW9 E5J’);

INSERT INTO Address (address_id ,address_desc ,postcode)

VALUES (‘2′, ’45 Parr Road’, ‘2’);

INSERT INTO Member_Type (member_type_id ,member_type_name ,member_type_allowed_day ,member_type_fine)

VALUES (‘1’, ‘Student’, ’10’, ’50’), (‘2’, ‘Lecturer’, ’30’, ’25’);

INSERT INTO Member (member_id ,member_fname ,member_lname ,member_address,member_type ,member_phone ,member_email)

VALUES (‘2002’, ‘Sam’, ‘Harison’, ‘1’, ‘2’, ‘0763456734’, ‘[email protected]‘);

INSERT INTO Librarian (librarian_id ,librarian_fname ,librarian_lname ,librarian_address ,librarian_phone ,librarian_email)

VALUES (‘3001’, ‘Hue’, ‘Sim’, ‘2’, ‘09876456235’, ‘[email protected]‘);

INSERT INTO Author (author_id ,author_name ,author_email)

VALUES (‘5001’, ‘Herbert Schildt’, ‘[email protected]‘);

INSERT INTO Book_Type (book_type_id ,book_type_name)

VALUES (‘3’, ‘Computing’);

INSERT INTO Publisher (publisher_id ,publisher_name)

VALUES (‘3’, ‘Dreamtech’);

INSERT INTO Books (ISBN ,book_title ,book_status ,book_type ,book_edition ,book_publisher)

VALUES (‘3456789’, ‘Oracle 11g’, ‘A’, ‘3’, ‘3’, ‘3’);

INSERT INTO Book_Author (book_id ,author_id)

VALUES (‘3456789’, ‘5001’);

INSERT INTO Borrow (borrow_id ,member_id ,ISBN ,librarian_id ,borrow_date ,return_date,expected_return_date)

VALUES (‘2’, ‘2002’, ‘3456789’, ‘3001’, ‘2011-02-16′, NULL,’2011-03-16’);

INSERT INTO Fine (fine_id ,borrow_id ,librarian_id ,fine_amount)

VALUES (‘2’, ‘2’, ‘3001’, ’15’,’Y’);

5.2 Functionality of SQL statements

As per our requirements, in order to make a member i.e. either a student or lecturer join and leave the library, the DML SQL statement will be as follows. The values given in the SQL statements are for illustration purpose only.

To join the library

INSERT INTO Member ( `member_id` ,`member_fname` ,`member_lname` ,

`member_address` ,`member_phone` ,`member_email`)VALUES

(‘1001’, ‘Robin’, ‘Carson’, ‘1’, ‘07424187656’, ‘[email protected]‘);

The above SQL statement inserts a new record into a table member with the given values such that the address value is reflected from the table address as there exists one to many relation between these two.

To leave the library

DELETE FROM Member where member_id=’1001′;

The above SQL statement deletes a record from the table member whose member id is 1001

To borrow the books

INSERT INTO Borrow (`borrow_id` ,`member_id` ,`ISBN` ,`librarian_id` ,`borrow_date` ,`return_date`)VALUES (‘1’, ‘1001’, ‘2147483647’, ‘1001’, ‘2011-02-13’, ”);

UPDATE Books SET `book_status` = ‘U’ WHERE ISBN =2147483647

The insert statement in the first step inserts a record in a borrow table with the given set of values and the second statement sets the status of the book ‘UNAVAILABLE’ abbreviated as ‘U’ for further borrowing.

To return the books

UPDATE Borrow SET `return_date` = ‘2011-02-16’ WHERE borrow_id =1

UPDATE Borrow SET `return_date` = ‘2011-02-16’ WHERE ISBN =2147483647

AND member_id=’1001′ and borrow_date=’2011-02-13′

UPDATE Books SET `book_status` = ‘A’ WHERE ISBN =2147483647

The update statements above sets the return date for the particular borrow in the borrow table, the updation can be done by searching unique borrow id as in case of first statement or by searching ISBN, member_id and borrow date as a combined search term as in later statement. Once the updation in the return date is performed the status of the book is again set to ‘AVAILABLE’ abbreviated as ‘A’ for further borrowing.

To check whether the book is available

SELECT book_status FROM Books WHERE ISBN=isbn-number

SELECT book_status FROM Books WHERE book_title like ‘%book_title%’

SELECT author.author_id, book_author.book_id,books.book_title,books.book_status

FROM Author

LEFT JOIN Book_Author

ON Author.author_id=Book_Author.author_id

LEFT JOIN Books

ON Book_Author.book_id=Books.isbn

where Author.author_name like’%author_name%’ and Books.book_status=’A’

ORDER BY Books.book_title

SELECT Author.author_id, Book_Author.book_id,Books.book_title,Books.book_status

FROM Author

LEFT JOIN Book_Author

ON Author.author_id=Book_Author.author_id

LEFT JOIN Books

ON Book_Author.book_id=Books.ISBN

WHERE Author.author_name like’%author_name%’

ORDER BY Books.book_title

SELECT Books.ISBN,Books.book_title, Book_Type.book_type_name from books

LEFT JOIN Book_Type

ON Books.book_type=Book_Type.book_type_id

Book_Type.book_type_name like =’type_name’

ORDER BY Books.book_title ASC

SELECT ISBN, book_title, book_publisher

FROM Books

LEFT JOIN Publisher ON Books.book_publisher = Publisher.publisher_id

Publisher.publisher_name LIKE ‘%publisher_name%’

ORDER BY Books.book_title ASC

The first statement checks the availability of the book according to the ISBN supplied and returns the corresponding value of book_status abbreviated as ‘U’ for unavailable and ‘A’ for available.

The second statement checks the availability of the books as per the book title supplied and returns the corresponding value of book of book_status abbreviated as ‘U’ for unavailable and ‘A’ for available.

The third statement searches the table according to author name and it returns all the books with the status as ‘A’ which means all the available books in alphabetical order according to the book name in ascending order.

The fourth statement searches the table according to author name and it returns all the books with the status as ‘A’ and ‘U’ as available and unavailable associated to the given author. The listing is again in alphabetical order according to the book name in ascending order.

The fifth statement searches the table according to type of book and it returns all the books with the status as ‘A’ and ‘U’ as available and unavailable associated to the given type. The listing is again in alphabetical order according to the book name in ascending order.

The sixth statement searches the table according to publisher of book and it returns all the books with the status as ‘A’ and ‘U’ as available and unavailable associated to the given type. The listing is again in alphabetical order according to the book name in ascending order.

To check the overdue books

SELECT * FROM BORROW WHERE return_date IS NULL

AND CURDATE( ) > expected_return_date ORDER BY member_id

The above query checks the expected return date greater than the current date and displays the records satisfying the condition.

To check the fines payable

SELECT SUM (FINE_AMOUNT) AS Fine_Payable FROM FINE WHERE FINE_PAID <>’Y’ OR FINE_PAID IS NULL.

The above query calculates the total payable fine from Fine table and displays the final value as Fine_Payable.

SELECT Fine.fine_id, Member.member_id, Member.member_fname, Member.member_lname, Fine.fine_amount

FROM Fine

LEFT JOIN Borrow ON Fine.borrow_id = Borrow.borrow_id

LEFT JOIN Member ON Borrow.member_id = Member.member_id

WHERE Fine.fine_paid <> ‘Y’ OR Fine.fine_paid IS NULL ORDER BY Member.member_id

The above query list out the unpaid fines from fine table with the respective members details on it.

6. Conclusion

After the thorough analysis, design and implementation the replacement over the traditional library system helps to process the library functions in effective way. This design has been able to achieve the goals that were described earlier in the requirement analysis and the project goal. This system synchronizes with the emerging technology to process and proceed the library requirements. Since this system uses the modern approach of software development it is always flexible to be scalable as per requirement without the changes in the design concept and techniques.

Order Now