Database Library System Project with SQL Code

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 library such as books, videos, journals etc. This also helps the librarians  by providing information such as total copies available per each book,list of books that belong to a particular category(Short Loan,Long Loan,Reference items etc).

Data Base Schema:

The Schema of  Database Library System includes several tables which include detailed tables and master tables as well as Store Procedures. Master tables are developed so as to maintain the lookup details for all the items.

Tables:

Book_Mst:

This is the master table for all the books that are available in the Library. This table contains the complete list of book 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,the category to which a book belongs i.e short loan,long loan etc.This table also contains the stock details such as the no.of copies that are there actually and the no of copies available at any given point of time. When ever a book was assigned to a borrower the the available count will be decremented by 1.

Columns :

ISBN:  This is unique ID given to every book .Since there may be many 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 an Unique number given to each category.

Binding _Type :   This column contains the Binding ID whose details can be fetched form the Binding_Mst table.The Binding ID is an Unique number given to each type Binding.

Description :  This column contains a short description for each book  so that the librarian can understand about this book.

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_Mst:

This table is the Master table for the binding types.This includes the binding ID and Binding Name.The Binding ID servers 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_Master:

This  table is the Master table for the different categories for lending a book such as short Loan,Long Loan etc.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 activate for a particular person will be  captured based of 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_mst:

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

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 thar are eligible for availing Library Facilities.Ecah student will be provided with a unique  Student ID and Borrower ID.The student ID will be Primary Key ,where as Borrower_ID , Phone_no will be Unique.

Columns:

Student_id :Unique ID given to Each Student

Student_Name : The Name of the Student

Fathers_Name : Father’s Name of the Student

Sex : Gender of the Student either Male or Female

DOB:The Date of Birth of the student

Borrower_ID :The borrower ID assigned to each student

Registration_School: The school to which this student belongs

Course : Student course of study

Address :Address of the student

Phone_No :Contact number of the student 

Videos_Mst:

This table serves as the master table for all the videos which includes details like the   author      and    co-author.Each Video will be given a unique ID which pays the role of Primary Key for this table.

Columns:

Video_Id:Unique ID given to each Video file

Title:The name of the video

Author:The Author of a video

Co_Author:Co_Author for  a video

Genes:Genus for a video

Duration_ Minutes:The time period for a video

Production_Date:The date on which a particular video was produced

Publisher_Name: the Name of the company which released that video. 

/*Description :This table contains the complete list of book that are available in the library.

Each Book id provided with a Unique ISBN which serves as a primary key.*/