A mini project on designing a DATABASE for Library management system using mySQL

K L UNIVERSITY Green Fields, Vaddeswaram Department of Computer science and Engineering 2016 DATABASE SYSTEMS Library Management System Submitted by Faculty K.Chakitha-150030458 Bhupesh Deka S.V.Rohith-150031000 section-10, batch-24

CERTIFICATE This is to certify that the Project entitled “Library Management System”, being submitted by K.Chakitha-150030458, S.V.Rohith-150031000 in partial fulfillment for the award of degree of Bachelor of Technology in Computer science Engineering is a record of detailed work carried out under the guidance, during the academic year 2017 and it has been found worthy of acceptance according to the requirements of the university. Faculty In charge Bhupesh Deka

ACKNOWLEDGEMENTS We are very much thankful to our loving Parents and Faculty for their care and responsibility in helping us to achieve this work done. We are also greatly indebted to our KLUniversity that has provided a healthy environment to drive us to achieve our ambitions and goals. Place: KLU Date: 11/04/2017

TABLE OF CONTENTS Content Pg. No 1 Introduction 1 2 Data types 2 3 Data Requirements  Entities  Attributes  Relationships - cardinality 3 4 Entity Relationship Diagram 5 5 Schema Diagram 6 6 Creating database using MySQL 7 7 Test-case Queries 8 Conclusion 9 References

1. INTRODUCTION 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. Library’s 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 conveying 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. Library Management System allows the user to store the book details and the customer details. The system is strong enough to withstand regressive yearly operations under conditions where the database is maintained and cleared over a certain time of span. The implementation of the system in the organization will considerably reduce data entry, time and also provide readily calculated reports. OBJECTIVE: - It keeps track of all the information about the books in the library, their cost, status and total number of books available in the Library. The user will find it easy in this automated system rather than using the manual writing system. The system contains a database where all the information will be stored safely.

2. Data types and its description 1. Integer: one optional sign character (+ or -) followed by at least one digit (0-9). Leading and trailing blanks are ignored. No other character is allowed. 2. Varchar: It is used to store alpha numeric characters. In this data type we can set the maximum number of characters up to 8000 ranges by defaults SQL server will set the size to 50 characters range. 3. Date: The DATE data type accepts date values. No parameters are required when declaring a DATE data type. Date values should be specified in the form: YYYY-MM-DD. However, Point Base will also accept single digits entries for month and day values. 4. Time: The TIME data type accepts time values. No parameters are required when declaring a TIME data type. Date values should be specified in the form: HH:MM: SS. An optional fractional value can be used to represent nanoseconds.

3. Data Requirements Entities  BRANCH  EMPLOYEE  CUSTOMER  ISSUE STATUS  RETURN STATUS  BOOKS Attributes  BRANCH  Manager_id  Branch_id  Address  Contact_no  Branch_h_no  Street  City  State  Zipcode  CUSTOMER  Customer_id  Books_issued  Name  Address  Registration_date  ISSUE STATUS  Issue_book_name  Issue_id  Issue_date  ISBN  Customer_id  RETURN STATUS  Return_id  Return_date

 Customer_id  Return_book_name  ISBN  BOOKS  ISBN  Title  Category  Rental_price  Author  Publisher  Status RELATIONSHIPS - CARDINALITY  MANAGER manages the BRANCH (1 - N)  CUSTOMER registers in the respective BRANCH (N – 1)  CUSTOMER issues BOOKS (1 – N)  CUSTOMER returns BOOKS (N – 1)  EMPLOYEE updates BOOKS (N – N)

4. Entity-Relationship-DIAGRAM Entity Relationship Diagram is used in modern database software engineering to illustrate logical structure of database. It is a relational schema database modelling method used to Model a system and approach. This approach commonly used in database design. The diagram created using this method is called ER-diagram. The ER-diagram depicts the various relationships among entities, considering each object as entity. Entity is represented as rectangle shape and relationship represented as diamond shape. It depicts the relationship between data object. The ER-diagram is the notation that is used to conduct the data modelling activity.

5. SCHEMA DIAGRAM A schema is the structure behind data organization. It is a visual representation of how different table relationships enable the schema’s underlying mission business rules for which the database is created. Database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It's the database designers who design the schema to help programmers understand the database and make it useful. Schema diagrams have an important function because they force database developers to transpose ideas to paper. This provides an overview of the entire database, while facilitating future database administrator work.

6. CREATING DATABASE USING MYSQL mysql> create database libproject; mysql> use libproject; Database changed mysql> CREATE TABLE BOOKS(ISBN int(100) not null,book_title varchar(50) not null,category varchar(50) not null,rental_price int(10) not null,status varchar(50),author varchar(50) not null,publisher varchar(50) not null,primary key(ISBN)) ; Query OK, 0 rows affected (1.44 sec) mysql> CREATE TABLE EMPLOYEE(employ_id int(10) not null,employ_name varchar(50) not null,position varchar(30) not null,salary int(10) not null,primary key(employ_id)); Query OK, 0 rows affected (0.35 sec) mysql> create table customer(customer_id int(10) not null,customer_name varchar(50),customer_address varchar(100) not null,registration_date date not null,primary key(customer_id)); Query OK, 0 rows affected (0.38 sec) mysql> create table brach(branch_no int(10) not null,manager_id int(10) not null,branch_address varchar(100) not null,contact_no int(10) not null,primary key(branch_no)); Query OK, 0 rows affected (0.49 sec) mysql> create table issue_status(issue_id int(10) not null,issued_cust int(10) not null,issued_book_name varchar(50) not null,issue_date date not null,isbn_book int(10) not null,primary key(issue_id),constraint foreign key(isbn_book) references BOOKS(ISBN),constraint foreign key(issued_cust) references customer(customer_id)); Query OK, 0 rows affected (0.66 sec) mysql> alter table brach rename branch;

Query OK, 0 rows affected (0.50 sec) mysql> create table return_status(return_id int(10) not null,return_cust int(10) not null,returned_book_name varchar(50) not null,return_date date not null,isbn_book2 int(10) not null,primary key(return_id),constraint foreign key(isbn_book2) references BOOKS(ISBN),constraint foreign key(return_cust) references issue_status(issued_cust)); Query OK, 0 rows affected (0.39 sec)

mysql> insert into books values(1000,'book1','comedy',5,'available','author1','pub1'); Query OK, 1 row affected (0.18 sec) mysql> insert into books values(1001,'book2','scifi',3,'available','author2','pub2'); Query OK, 1 row affected (0.08 sec) mysql> insert into books values(1003,'book3','romance',1,'un- available','author3','pub3'); Query OK, 1 row affected (0.06 sec) mysql> insert into books values(1004,'book4','thriller',7,'available','author4','pub4'); Query OK, 1 row affected (0.07 sec)

mysql> alter table branch add constraint foreign key(manager_id) references employee(employ_id); Query OK, 0 rows affected (1.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into employee values(991,'emp1','manager',30000); Query OK, 1 row affected (0.06 sec) mysql> insert into employee values(992,'emp2','worker',10000); Query OK, 1 row affected (0.07 sec) mysql> insert into employee values(993,'emp3','worker',10000); Query OK, 1 row affected (0.05 sec) mysql> insert into employee values(994,'emp4','reader',20000); Query OK, 1 row affected (0.07 sec) mysql> insert into employee values(995,'emp5','assist',20000); Query OK, 1 row affected (0.07 sec)

mysql> insert into branch values(1,991,'branch_addr1',987654321); Query OK, 1 row affected (0.07 sec) mysql> insert into branch values(3,993,'branch_addr3',987654323); Query OK, 1 row affected (0.09 sec) mysql> insert into customer values(11,'cus1','hom1','2008:10:10'); Query OK, 1 row affected (0.07 sec) mysql> insert into customer values(12,'cus2','hom2','2008:03:03'); Query OK, 1 row affected (0.09 sec) mysql> insert into customer values(13,'cus3','hom3','2009:03:03'); Query OK, 1 row affected (0.07 sec) mysql> insert into customer values(14,'cus4','hom4','2009:04:04'); Query OK, 1 row affected (0.06 sec) mysql> insert into issue_status values(51,12,'book1','2010:01:01',1001);

Query OK, 1 row affected (0.08 sec) mysql> insert into issue_status values(52,14,'book4','2010:01:01',1004); Query OK, 1 row affected (0.06 sec) mysql> insert into return_status values(61,12,'book1','2010:10:01',1001); Query OK, 1 row affected (0.08 sec) mysql> insert into return_status values(62,13,'book1','2010:10:01',1001); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`libproject`.`return_status`, CONSTRAINT `return_status_ibfk_2` FOREIGN KEY (`return_cust`) REFERENCES `issue_status` (`issued_cust`)) Since customer id-13 isn’t taken any book, we cannot blindly give the input!!

7. TEST QUERIES 1. Display the customer name who took the book of type comedy. Mysql>select customer_name from customer where book_type=’comedy’; 2. Display issue id ,issued customer name whose ibsn book number is 1004. Mysql>select issue_id , issued_cust from issue_status where isbn_book=1004; 3. Display all contents in issue status table where isbn book of issue table and return table are equal. Mysql>select p.* from issue_status p, return_status r where p.isbn_book=r.isbn_book2;

4. Add a column called book type in customer table. Mysql>alter table customer add book_type varchar(10) not null; Query ok,4 rows affected(0.37sec) Records:4 Duplicates:0 Warnings:0 Mysql>describe customer;

8. CONCLUSION • SQL database management application which is very well used in the modern world in organising and manipulating a database. • Though SQL doesn’t have the GUI interface like Microsoft access is having and they all manage the database comfortable. • Depending on the user or users, if an organisation has multiple users then they should go for SQL server based application. • This project shows how to create tables in SQL and 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. 9. REFERENCES  http://people.cs.pitt.edu/~chang/156/03ERmodel.html  http://www.academia.edu/13780884/Database_system_for_library_man agement_system  https://lbsitbytes2010.wordpress.com/2013/09/21/er-diagram-of- library-management-rno15s5cs2/  https://www.slideshare.net/fiu025/library-management- 32343393?next_slideshow=1  http://www.c-sharpcorner.com/UploadFile/ea3ed6/database-design-for- library-management-system/  http://stackoverflow.com/questions/17641134/what-is-different- between-er-diagram-and-database-schema

A mini project on designing a DATABASE for Library management system using mySQL

A mini project on designing a DATABASE for Library management system using mySQL

In this document

More Related Content

What's hot

Viewers also liked

Similar to A mini project on designing a DATABASE for Library management system using mySQL

More from svrohith 9

Recently uploaded

A mini project on designing a DATABASE for Library management system using mySQL