Database Design For Library Management System

Index

1. Introduction
2. Tables
   
   Book_Details
   Binding_Details
   Category_Details
   Borrower_Details
   Student_Details
   Staff_Details
   Student_Details
   Shelf_Details
   
   
3. Normalization
4. ER-Diagram
5. SQL Command (select, update, insert, delete)
   
   Creating table “Book_Details”:
   Inserting Some Data in “Book_Details”
   Creating table “Binding_Details”:
   Creating Relationship Between Book and Binding Table:
   Creating Category Table:
   Building Relationship between Book & Category Table:
   Creating Borrower Table:
   Building Relation Between Book & Borrower Table
   Creating Staff Table
   Building Relationship between student and Borrower table:
   Checking Full Relationship
   Adding Shelf Table
   Adding Relationship Between Shelf and Book Table
   Combined All Relationship

Library Management System

Introduction

The Database Library System is intended to Automate the library activities such as creating a new borrower, giving books to the borrowers, maintaining the details of all the item that were available in the books . This also helps the librarians by providing information such as total copies available each book, list of books that belong to a particular category (Short, Long Loan, Reference items, etc).

Tables

Book_Details:

This is the master table for all the books that are available in the Library. This table contains the complete list of books that are available in the library. Each Book id provided with a unique ISBN which serves as a primary key. The book details include the ISBN, Book Title, the year in which that particular book was published, the type of binding either soft cover or hard cover and the category.

Columns

ISBN: This is unique ID given to every book .Since there may be a large no. of books with same TITLE, this ISBN no. will help us to distinguish between books of same title.

Book_Title: Provides the name of the book.

Publication_year: Contains the year of publication in ‘YY’ format (eg:2009à09)

Language: Contains the language in which this book was published.

Category_Type

This column contains the Category ID whose details can be fetched form the category_master table. The category ID is a Unique number given to each category.

Binding _Id

 

This column contains the Binding ID whose details can be fetched form the Binding_Detailstable.The Binding ID is a Unique number given to each type Binding.

No_Of_Copies_Actual: This column contains the total no. of copies of each book that were initially present.

No_Of_Copies_Current: This column contains the total no. of copies of each book that were currently available .

Binding_Details:

This table is the Master table for the binding types.This includes the binding ID and Binding Name. The Binding ID serves as a primary key.

Columns:

Binding_ID: This column contains the Unique number that was given to each type of binding.

Binding_Name: This column give the names of different types of binding.

Category_Details:

This includes the Category ID and Category Name. The Category ID servers as a primary key.
Columns:

Category _ID: This column contains the Unique number that was given to each type of Category.

Category _Name: This column give the names of different types of categories.

Borrower_Details:

This table contains the details of all the persons who lent a book from the library. Each Student will be given a Unique borrower ID. All the library related activity for a particular person will be captured based on the Borrower ID. This table will be used to track the borrowing records. The borrower ID will serve as a primary key here.

Columns:

Borrower_ID: Unique ID given to each Student.

Book_ID: This column contains the book ID which was give to the borrower.

Borrowed_From_Date: The date on which the book was given a particular borrower.

Borrowed_To_Date: The date on which that book was supposed to be returned back or should be renewed.

Actual_Return_date: The date on which the borrower returned the book to the library.

Issued_by: The ID of the Librarian who issued book to the borrower.

staff_Details:

This table contains the details of the staff in the Library. Each Staff member will be given a unique User ID which serves as a Primary Key.

Columns

User_ID: The unique ID given to each staff member present in the Library.

User_Name: The Name of the staff member.

Is_Admin: Just checking user is admin or not.

Designation: The role of the staff member in the library such as librarian, assistant, etc.

Student_Details:

This table contains the details of all the students they are eligible for availing Library facilities. Each student will be provided with a unique Student ID and Borrower ID. The student ID will be Primary Key, whereas Borrower_ID and Phone_no will be Unique.

Columns:

Student_id: Unique ID given to Each Student.

Student_Name: The Name of the Student.

Sex : Gender of the Student either Male or Female.

Date_Of_Birth: The Date of Birth of the student.

Borrower_ID: The borrower ID assigned to each student.

Department: This is contains student department.

Contact_Number: Contact number of the student.

Shelf_Details:

This table contain the position of the book…That means which floor and shelf the book is situated.

 

Column:

Shelf_Id: Contains the shelf number.

Floor: Which floor the shelf is situated.

Library Management System (SQL Commands)

Creating table “Book_Details”:

CREATE TABLE Book_Details
(
    ISBN_Codeint PRIMARY KEY,
    Book_Titlevarchar(100),
    Language varchar(10),
    Binding_Idint,
    No_Copies_Actualint,
    No_Copies_Currentint,
    Category_idint,
    Publication_yearint
)

Inserting Some Data in “Book_Details” :

INSERT INTO Book_details
VALUES('0006','Programming Concept','English',2,20,15,2,2006);

Creating table “Binding_Details”:

CREATE TABLE Binding_details
(
    Binding_idint PRIMARY KEY,
    Binding_Namevarchar(50)
)

Describe Binding table:

Describe binding_details

Inserting Some data in Binding Table:

INSERT INTO Binding_DetailsVALUES(1,'McGraw Hill);
INSERT INTO Binding_DetailsVALUES(2,'BPB Publication');

All Data of Binding Table:

select *from binding_Details

Creating Relationship Between Book and Binding Table:

ALTER TABLE Book_details
ADD CONSTRAINT Binding_ID_FK FOREIGN KEY(Binding_Id) REFERENCES Binding_Details(Binding_Id);

Checking Relationship:

selectb.Book_Title, e.binding_name
fromBook_Detailsb, Binding_Details e
whereb.binding_id = e.binding_id;

Creating Category Table:

CREATE TABLE Category_Details
(
    Category_Idint PRIMARY KEY,
    Category_Namevarchar(50)
)

Inserting some data in Category Table:

INSERT INTO CATEGORY_DETAILS VALUES(1,'Database');
INSERT INTO CATEGORY_DETAILS VALUES(2,'Programming Language');

Building Relationship between Book & Category Table:

ALTER TABLE Book_details
ADD CONSTRAINT Category_Id_FK FOREIGN KEY(Category_Id) REFERENCES Category_Details(Category_Id);

Checking Relationship:

selectb.Book_Title,e.Category_Name
fromBook_Detailsb,Category_Details e
whereb.binding_id = e.Category_id;

Creating Borrower Table:

CREATE TABLE Borrower_Details
(
    Borrower_Idint PRIMARY KEY,
    Book_Idint,
    Borrowed_From date,
    Borrowed_TO date,
    Actual_Return_Date date,
    Issued_byint
)

Inserting Some data in Category Table:

Insert into BORROWER_DETAILS VALUES(1,0004,'01-Aug-2014','7-Aug-2014','7-Aug-2014',1)
Insert into BORROWER_DETAILS VALUES(2,6,'02-Aug-2014','8-Aug-2014',NULL,1)

Building Relation Between Book & Borrower Table:

ALTER TABLE Borrower_details ADD CONSTRAINT Book_Id_FK FOREIGN KEY(Book_Id) REFERENCES Book_Details(ISBN_Code);

Checking Relationship:

selectBorrower_Details.Borrower_id,Book_Details.Book_title
fromBorrower_Details,Book_Details
whereBorrower_Details.book_id=Book_Details.ISBN_Code

ALTER TABLE Borrower_Details
ADD CONSTRAINT Issued_by_FK FOREIGN KEY(Issued_by) REFERENCES Staff_Details(Staff_Id);

Creating Staff Table :

CREATE TABLE Staff_Details
(
    Staff_Idint PRIMARY KEY,
    Staff_Namevarchar(50),
    Password varchar(16),
    Is_Adminbinary_float,
    Designation varchar(20)
)

Inserting some data in Staff Table:

Insert into STAFF_DETAILS values (1,'Tarek Hossain','1234asd',0,'Lib_mgr');
Insert into STAFF_DETAILS values (2,'Md.Kishor Morol','iloveyou',0,'Lib_clr');

All Data of Staff table:

select * from staff_details

 

Creating Student Table:

Create TABLE Student_Details
(
    Student_Idvarchar(10) PRIMARY KEY,
    Student_Namevarchar(50),
    Sex Varchar(20),
    Date_Of_Birth date,
    Borrower_Idint,
    Department varchar(10),
    contact_Numbervarchar(11)
)

Inserting Some Data in Student Table:

Insert into STUDENT_DETAILS values ('13-23059-1','Ahmed,Ali','Male','05-Oct-1995',1,'CSSE','01681849871');
Insert into STUDENT_DETAILS values ('13-23301-1','MOrol MD.Kishor','Male','03-Jan-1994',2,'CSE','01723476554');

All Data of Student Table:

select *from student_details

Building Relationship between student and Borrower table:

ALTER TABLE student_details
ADD CONSTRAINT borrower_id_FK FOREIGN KEY(Borrower_Id) REFERENCES Borrower_Details(Borrower_Id);

Checking Full Relationship:

select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To
fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_details book
wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id;

Adding Shelf Table:

Create Table Shelf_Details
(
    Shelf_idint PRIMARY KEY,
    Shelf_Noint,
    Floor_Noint
);

Inserting Some Data from Shelf Table:

Insert into Shelf_DetailsValues(1, 1, 1);
Insert into Shelf_DetailsValues(2, 2, 10001);
Insert into Shelf_DetailsValues(3, 1, 10002);

All Data In Shelf Table:

select*from Shelf_Details;

Adding Relationship Between Shelf and Book Table:

ALTER TABLE Book_Details
ADD(Shelf_Idint);

UPDATE Book_Details set Shelf_Id = 1
where ISBN_CODE = 4;

UPDATE Book_Details set Shelf_Id = 2
where ISBN_CODE = 6;

ALTER TABLE Book_Details
ADD CONSTRAINT Shelf_Id_FK FOREIGN KEY(Shelf_Id) REFERENCES Shelf_Details(Shelf_Id);

Combined all Relationship:

select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To, shelf.shelf_No
fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_detailsbook, Shelf_Details shelf
wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id and book.Shelf_Id = shelf.Shelf_Id;