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!

Hospital Management System (Using Database)

Hello Everyone,

Today, I am gonna publish my first database project which is based on hospital management.

Here’s a brief video on it:

It involves the below list of tables (with mentioned structure):

  1. PATIENT:
Column Datatype
PAT_ID VARCHAR2(10 CHAR)
PAT_NAME VARCHAR2(60 CHAR)
PAT_GENDER VARCHAR2(2 CHAR)
PAT_ADDRESS VARCHAR2(100 CHAR)
PAT_NUMBER NUMBER(11,0)
PAT_DOC_CODE VARCHAR2(10 CHAR)

2. PATIENT_DIAGNOSIS:

Column Datatype
DIAG_ID VARCHAR2(10 CHAR)
DIAG_DETAILS VARCHAR2(200 CHAR)
DIAG_REMARKS VARCHAR2(200 CHAR)
DIAG_DATE DATETIME
PAT_ID VARCHAR2(10 CHAR)

3. DOCTOR:

Column Datatype
DOC_CODE VARCHAR2(10 CHAR)
DOC_NAME VARCHAR2(60 CHAR)
DOC_GENDER VARCHAR2(2 CHAR)
DOC_ADDRESS VARCHAR2(200 CHAR)
DOC_DESIGNATION VARCHAR2(30 CHAR)
DOC_NUMBER NUMBER(11,0)

4. BILL:

COLUMN DATATYPE
BILL_NO INTEGER
PAT_ID VARCHAR(10 CHAR)
PAT_NAME VARCHAR2(60 CHAR)
PAT_GENDER VARCHAR2(2 CHAR)
PAT_ADDRESS VARCHAR2(100 CHAR)
DOC_NAME VARCHAR2(160 CHAR)
AMOUNT NUMBER(19,9)

 

Please find below the lucid chart for better explaination:

 

 

 

 

 

 

 

 

 

 

 

 

I have provided sql files for table creations which includes the sample data to be inserted for PATIENT, PATIENT_DIAGNOSIS and DOCTOR.

Apart from this, I have also included the trigger/sequence file which includes the trigger/sequence creation statements along with the sample insertions for fine table in which we only need to insert value for columns BILL_NO and AMOUNT and the remaining details are fetched automatically using triggers from the status tables ( PATIENT, PATIENT_DIAGNOSIS and DOCTOR).

 

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!