Library Management System (Using Database)

Hello Everyone,

I am back with another database project which was finalized 2 days back and this time I worked on Library Management using database.

Here’s a brief video on it:

So without much talking, let’s dive into the project.

To begin with, following are the tables which have been used in this project:

  1. BOOKS (Details about books in library):
Column Datatype
Book_ID VARCHAR2(10 CHAR)
Book_Title VARCHAR2(300 CHAR)
Book_Author VARCHAR2(50 CHAR)

2. USERS (Users includes both staff and students):

Column Datatype
USER_ID VARCHAR2(10 CHAR)
NAME VARCHAR2(50 CHAR)
DESIGNATION VARCHAR2(10 CHAR)

3. ISSUES (Records for books issued):

Column Datatype
Issue_ID Number(10,0)
Book_ID VARCHAR2(10 CHAR)
Book_Title Varchar2(100 CHAR)
Borrower_ID VARCHAR2(10 CHAR)
Borrower_Name Varchar2(100 CHAR)
Borrower_Designation Varchar2(50 CHAR)
Issue_Date DATE
Date_Expiry DATE

4. RETURNS (Records for books returned):

Column DataType
Return_ID Number(10,0)
Book_ID VARCHAR2(10 CHAR)
Borrower_ID VARCHAR2(10 CHAR)
Borrower_Name Varchar2(100 CHAR)
Issue_Date DATE
Deadline_Date DATE
Return_Date DATE
Delay Number(10,0)

5. FINE (Records for fine against books):

Column DataType
Fine_ID Number(10,0)
Book_ID VARCHAR2(10 CHAR)
Book_Title Varchar2(100 CHAR)
Borrower_ID VARCHAR2(10 CHAR)
Borrower_Name Varchar2(100 CHAR)
Delay NUMBER(19,9)
Fine_Amount NUMBER(39,19)

 

Please find below the lucid chart for better explaination:

 

 

 

 

 

 

 

 

 

 

 

 

 

Following are two points which were set as standard while using the delay logic:

For staff members, the maximum delay in return can be of 5 days.

For students,  the maximum delay in return can be of 4 days.

You can change this value by changing the value in below trigger (ISSUE_EXPIRY):

 

 

 

I have provided the SQL files for table creations – BOOKS, USERS, ISSUES, RETURNS and FINE which includes the sample data insertion queries as well.

Apart from this, I have also included the triggers file which will automate the value insertion of columns (marked with green arrow).

Here’s the link for all files: CLICK HERE (Github repository)

If you liked the stuff, please leave positive comment and in case you have any feedback, feel free to share that in comments!

Leave a Reply

Your email address will not be published. Required fields are marked *