Implementation Of The Library Management System Technology Essay

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 its 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 practi ce 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, A ttributes 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 shouldnt 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 shouldnt 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 [emailprotected] 1002 Robin Hood [emailprotected] 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 1 91238745654 2 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 1 Apress 2 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 1 Computing 2 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 1 Student 14 10 2 Lecturer 30 5 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 1 121 East Road 1 2 45 Parr Road 2 3 36 Jhon Road 3 4 23 Plashet Road 1 Postcode Table: Fields (postcode_id, postcode_description) postcode_id is the primary key. postcode_id postcode_description 1 E15 3QS 2 NW9 1NF 3 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 1 1 1001 10 2 2 1002 15 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_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_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 ,po