Category: Database Projects

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!

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).

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!