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:
- 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).
Zip file containing all the project files: Click here to download
Lucid (ERD): Click here to download
If you liked the stuff, please leave positive comment and in case you have any feedback, feel free to share that in comments!