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.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.*/
3 Replies to “Database Library System Project with SQL Code”
Here why we use bind anyone can tell me please
please can you send the code