As technology advances, information in different organizations can no more be maintained manually. There is a growing need for the information to become computerized so that it can be suitably stored. This is where databases are formulated. Databases are convenient storage systems which can store large amounts of data and together with application programs such as interfaces they can aid in faster retrieval of data. To design a complete database system for a hospital management so that its information can be stored, maintained, updated and retrieved conveniently and efficiently. Hospital Management system deals with the collection of patient’s information, diagnosis details, etc. The main function of the system is to register and store patient details and doctor details and retrieve these details as and when required, and also to manipulate these details meaningfully.
The need for developing Hospital Management Database System is,
< 1. Efficiently maintains the details about the patient
< 2. Simultaneously updates changes made to any data, item in the entire data base.
< 3. It is faster than manual system
It is very difficult to retrieve data from case files in hospital directory. It is difficult to handle the whole system manually and it is less accurate and to keep the data in case files for future reference because it may get destroyed. Redundancy of data may occur and this may lead to the inconsistency. The manual system is so time-consuming. Database system is very easy to operate. Speed and accuracy are the main advantages of database system. There is no redundancy of data. The data are stored in the computer’s secondary memories like hard disk, etc.it can be easily receive and use at any time.
DATABASE DESIGN
Entity Relationship Model (ERM)
< 1) ER Diagram
In the ER diagram, we can view the entities- Patient, Doctor, Receptionist, Department, Medicine, Test, OT(Operation Theatre), Room, Nurse, Ward_ boy, Driver, Ambulance, Carriers, Accountant and Bill. Among these entities, relationships exist which connect all the entities in the diagram. For example, Patient, Doctor and Receptionist are connected via the relationship appointment. In other words, a receptionist will set up a doctor’s appointment for a patient. Similarly, Doctor, Patient and Medicine are connected via the relationship prescription. Here, a doctor may prescribe one or more medicine to a patient. In a similar way, other entities are connected via relationships in a meaningful way.
< 2) Cardinalities
One doctor can be from one or many departments. One department may have one or many doctors. So it is a many to many relationship named doctor from department (in the diamond).
Ternary Relationship
One receptionist can admit one patient in one room in a certain date and time. One receptionist can admit in one room one patient in a certain date and time. In one room, one patient is admitted by one receptionist in a certain date and time. So the relationship is a ternary relationship named admission (in the diamond) with the cardinality ratio from patient to receptionist to room being one to one to one.
B. Relational Model
The entire ER diagram can be converted to a Relational Model. The attribute(s) of a relation which serve as a primary key of the table are underlined. Those attribute(s) which represent foreign keys are indicated as fk underneath .
i)Patient(Pat_id, Pat_name, age, sex, Address, DOB, MOB)
Here DOB refers to patient’s date of birth and MOB refers to mobile number.
ii)Room(Room_id, Room_No, Room_type, Room_cost)
iii)Admission(admsn_id, Pat_id, Room_id, Rcp_id, date, time)
(fk) (fk) (fk)
This is a junction table among Patient, Room and Receptionist tables. Primary key of the Patient table goes to Admission table as foreign key. Primary key of the Room table goes to Admission table as foreign key. Primary key of the Receptionist table goes to Admission table as foreign key. Admsn_id is a primary key in the Admission table. Since the cardinality ratio from Patient to Receptionist to Room is 1 to 1 to 1, Pat_Id from Patient table, Room_Id from Room table and Rcp_Id from Receptionist table become foreign keys in the table
Admission junction table
iv)Receptionist(rcp_id, rcp_name, Age, Address, MOB, Shifting)
Here Shifting refers to morning, afternoon or night shifts.
v)Doctor Table(Doc_id, Doc_name, Age, Address, Salary, MOB, Designation, Passed_from)
Here Passed_from indicates passed from which institution.
vi)Appointment(Ap_id, Pat_id, Doc_id, Rcp_id, Ap_date, Ap_time)
(fk) (fk) (fk)
This is a junction table between Patient, Receptionist &
Doctor tables.
vii)Bill(Bill_id, Bill_purpose, Bill_total)
Here Bill_purpose refers to the cause e.g blood test for which the bill is paid.
viii)Accountant(Acct_id, Acct_name, Age, Address, MOB, Working_time, Acct_salary)
ix)Payment Table (Pay_id, Bill_id, Pat_id, Acct_id, Pay_type, Pay_date)
(fk) (fk)
This is a junction table between Patient, Bill & Accountant tables. Primary key of the Patient Table goes to Payment table as foreign key. Primary key of the Accountant Table goes to Payment table as foreign key. Primary Key of the Bill Table becomes a part of the primary key in Payment table. (Pay_id, Bill_id) is the primary key in the Payment table. Since the cardinality ratio from Patient to Accountant to Bill is 1 to 1 to
C (which stands for many), Pat_Id from Patient table and Acct_id from Accountant table become foreign Keys in the Payment table while Bill_id from Bill table becomes a part of the primary key in the Payment table. In this table, pay_type refers to the type of payment such as cash, pay order, check or credit card.
x)Medicine(Mdcn_id, Mdcn_name, company, m_date, e_date, price)
m_date refers to manufacture date. e_date refers to expiry date.
xi)Prescription( Prs_id, Doc_id, Mdcn_id, Pat_id, date, fee)
(fk) (fk)
This is a junction table between Patient, Doctor & Medicine tables.
xii)Test(Test_id, Test_name, date, rep_date, fee)
Here rep-date refers to the date the report of the test will be given.
xiii)Assist(Srl_no, Pat_id, Doc_id, Test_id, time, date)
(fk) (fk)
This is a junction table between Patient, Doctor & Test tables. Srl_no corresponds to serial number of conducted test.
xiv)OT (Ot_id, Ot-room_no)
Here OT refers to Operation Theater.
xv) Operation(Op_id, Doc_id, Pat_id, Ot_id, Op_date)
Here Nrs_wo_shift refers to the working shift of the nurse such as morning, afternoon or night shifts.
xix) Nursing_Service(Ns_id, Pat_id_, Nrs_id, Room_id)
(fk)
This is a junction table between Patient, Room & Nurse tables.
xx) Ward_Boy(Wb_id, Wb_name, MoB, w_shift, Salary)
Here w_shift refers to working shift of a ward boy.
xxi)Cleaning _Service(Cls_id, Pat_id, Wb_id, Room_id)
(fk)
This is a junction table between Patient , Room & Ward_Boy tables.
xxii)Driver(Dr_id, Dr_name, MoB, Address, Shift, Salary)
xxiii)Ambulance(Amb_id, Amb_num, Capacity)
xxiv)Ambulance_Service(As_id, Pat_id, Dr_id, Amb_id)
(fk) (fk) (fk)
This is a junction table between Patient, Driver & Ambulance tables.
xxv)Carriers(Cr_id, Cr_name, MOB, Address, Salary)
Carriers are those persons who carry patients from the ambulance to the hospital premises.
xxvi)Carrying_Service Table(CS_id, Cr_id, Amb_id, Pat_id)
(fk) (fk)
This is a junction table between Patient, Ambulance & Carriers tables.
C. Functional Dependencies and Normalization
1) Fulfillment of all normal forms
Room Table:-
(1)Room(Room_id, Room_No, Room_type, Room_cost)
{Room_id} => {Room_No}
Here Room-id corresponds to serial numbers like 1, 2, 3, 4 etc. Room_No corresponds to designated numbers of rooms like 206, 307, 508 etc. A Functional Dependency exists from Room_No to Room_id because two different Room_Nos cannot correspond to the same Room_id. Similarly, the following functional dependencies exist:
{Room_id} => {Room_type}
{Room_id} => {Room_cost}
Considering the above full functional dependencies, we now verify whether the relation fulfills Codd’s stringent rules of normalization. [1,2,5]
This is a junction table between Patient, Doctor & OT tables.
xvi) Department(Dept_id, Dept_name, treatment)
xvii) Doctor_from_Department(Dfd_id, Doc_id, Dept_id)
This is a junction table between Doctor & Department tables.
xviii)Nurse(Nrs_id, Nrs_name, Age, Address, MoB, Nrs_wo_shift, experience, salary)
The relation is in 1NF because the attributes of the relation do not have sub attributes. The relation is in 2NF because non-primary keys are fully functionally dependent on primary key. The relation is in 3NF because no transitive dependency exists from non-primary key to primary key. The relation is in BCNF because there is no part of primary key that is fully functionally dependent on non primary key. Similarly, all other relations of the system follow Codd’s rules of normalization except the Payment table which is explained below.
2) Violation of Normal forms
Payment Table :-
This is a junction table between Patient, Bill & Accountant tables.
(1)Payment(Pay_id, Pat_id, Bill_id, Acct_id, Pay_type,Pay_date)
For the above relation, the following functional dependencies exist:
{Pay_id}=> {Pay_type, Pay_date, Pat_id}
Two different patient ids, payment dates and payment types cannot correspond to the same payment id. So Pay_type, Pay_date and Pat_id are fully functionally dependent on Pay_id.
{Bill_id}=> {Acct_id , Pat_id}
Similarly two different accountant ids and patient ids cannot correspond to the same bill id. So Acct_Id and Pat_id are fully functionally dependent on Bill_id. Based on the above functional dependencies: The relation is in 1NF. The relation is not in 2NF because all non-primary keys are not fully functionally dependent on the primary key (Pay_id, Bill_id). So we split the relation to make it 2NF.
(2)Payment1(Pay_id, Pay_Type, Pay_date, Pat_id)
(3)Payment2 (BP_id, Bill_id, Acct_id, Pat_id)
{BP_id stands for Bill Pay id and corresponds to serial numbers of all bills such as 1, 2, 3 etc in ascending order. Bill_id on the other hand corresponds to the bill code numbers}.The relations are now in 2NF.The relations are in 3NF.The relations are in BCNF.
D. Implementation in SQL Server
SQL server is a modern software where we can store huge amount of information via a database. In fact we have implemented our database system in SQL server. In this server we can store data easily, retrieve data speedily and execute the queries conveniently by SQL query language. We can create relations easily by code and also manually.
Output
Patientdetails2 table
CREATE TABLE Patientdetails2(Regno varchar(10),Name varchar(20),Gender varchar(10),Age varchar(10),City varchar(20),mobno varchar(10));
INSERT INTO Patientdetails2 VALUES(1001,’Silpa’,’Female’,23,’Trivandrum’,’986753’);
INSERT INTO Patientdetails2 VALUES(1002,’Krishna’,’Female’,22,’Kollam’,’956471’);
INSERT INTO Patientdetails2 VALUES(1003,’Lejin’,’Male’,27,’Adoor’,’875643’);
INSERT INTO Patientdetails2 VALUES(1004,’Anand’,’Male’,35,’Kottayam’,’865943’);
Alter table Patientdetails2 ADD Religion varchar(10);
Patientmedicine2
CREATE TABLE Patientmedicine2(Diagnosisno varchar(10),Medicineno varchar(10),Medicinename varchar(20),Noofdoses varchar(10));
INSERT INTO Patientmedicine2 VALUES(78,’435’,’Dolo-650’,’3’);
INSERT INTO Patientmedicine2 VALUES(54,’876’,’Ablavar’,’2’);
INSERT INTO Patientmedicine2 VALUES(63,’243’,’Baclofen’,’3’);
INSERT INTO Patientmedicine2 VALUES(25,’645’,’Palifermin’,’4’);
Doctordetails2
CREATE TABLE Doctorsdetails2(Doctid varchar(10), Doctname varchar(10),DOB varchar(10),Eduqualif varchar(20),deptment varchar(20),Salary varchar(20),phneno varchar(10),emailid varchar(20));
INSERT INTO Doctorsdetails2 VALUES(43,’Dr.james ’,’24-08-1983’,’MBBS’,’Cardiology’,’70000’,’914323’,’jame@gmail.com’);
INSERT INTO Doctorsdetails2 VALUES(7658,’Dr.Sanjay ’,’19-11-1985’,’MBBS’,’Medicine’,’30000’,’897645’,’sanjay@gmail.com’);
INSERT INTO Doctorsdetails2 VALUES(2354,’Dr.Ann’,’22-03-1980’,’MBBS’,’opthalmology’,’600000’,’856790’,’Ann@gmail.com’);
INSERT INTO Doctorsdetails2 VALUES(2354,’Dr.vimal’,’22-03-1980’,’MBBS’,’opthalmology’,’600000’,’856790’,’vimal@gmail.com’);
Alter table Doctorsdetails2 ADD docmob varchar(10);
Update Doctorsdetails2 set docmob=345675 where doctid=2354;
Conclusion
Our database contains all the information needed to be maintained in the hospital management system. As we have computerized the entire system via a database, the maintenance is very convenient and efficient and also retrieval of data according to demand is speedy. Therefore, our designed system is a good and useful implementation. We can further improvise it by enhancing its security. This system is used to interface with the SQL Server and hence data accessed, retrieved and searched for far better in a more efficient and convenient form.
.jpg)
