IMAT5103 Database Systems and Design: Hospital Management System

This assignment presents a comprehensive database design and implementation for a hospital management system. The project begins with enterprise rules that define the system's functionality and processes, followed by conceptual and logical enhanced entity-relationship (EER) diagrams to visualize the database structure. The solution includes detailed table creation using SQL, covering entities like doctors, employees, nurses, patients, payments, rooms, and test records. Data population examples are provided to populate the tables. Indexes are created to optimize query performance, and several SQL queries are demonstrated to retrieve and manipulate data, including selections, joins, conditions, sorting, and aggregate functions. The assignment adheres to the requirements of the IMAT5103 Database Systems and Design module, showcasing a strong understanding of database modeling techniques and SQL implementation. The solution concludes with a bibliography of relevant sources.

Running head: HOSPITAL MANAGEMENT SYTEM
HOSPITAL MANAGEMENT SYTEM
Name of the Student
Name of the University
Author Note

1HOSPITAL MANAGEMENT SYTEM
Table of Contents
Enterprise Rules - Hospital management system............................................................................2
Conceptual Enhanced Entity Relationship Diagram.......................................................................3
Logical Enhanced Entity Relationship Diagram.............................................................................4
Tables...............................................................................................................................................4
Data Population.............................................................................................................................12
Indexes...........................................................................................................................................15
Query.............................................................................................................................................16
Bibliography..................................................................................................................................21

2HOSPITAL MANAGEMENT SYTEM
Enterprise Rules - Hospital management system
Enterprise rules or Business rules defines the functionality and the process of the system.
It manly helps in identifying the objects participating in the process, their relationship and the
attributes related to it.
 Each employees are whether nurse, doctor or other staff and identified by their employee
id.
 A patient (identified by patient id) is assigned to a room in the hospital by other staff
(Receptionist, warden etc.) identified by the Room no and their floor and features.
 Each patient is assigned to a doctor for checkup and appointments.
 Each doctor can be scheduled for one or more patient.
 Each nurse can govern one or more than one room.
 Each room has one or more nurses.
 Each Patient pays their bills.
 Each patient can make one or more payment identified by payment id.
 Each patient have their test and test details recorded in the system.
 Test record is identified by the patient id, hence it is the weak entity here.

3HOSPITAL MANAGEMENT SYTEM
Conceptual Enhanced Entity Relationship Diagram
Figure 1: Conceptual Enhance Entity Relationship Diagram
Source: Created by author

4HOSPITAL MANAGEMENT SYTEM
Logical Enhanced Entity Relationship Diagram
Figure 2: Logical Entity Relationship Diagram
Source: Created by author
Tables
P2512513Doctor
create table P2512513Doctor (DoctorID int primary key, Speciality varchar(50), LicenseNo int);

5HOSPITAL MANAGEMENT SYTEM
Alter table P2512513Doctor
add constraint fk_emp_doc
foreign key (DoctorID)
references P2512513Employee(EmployeeID);
P2512513Employee
create table P2512513Employee (EmployeeID int primary key, FName varchar(25), LName
Varchar(25), Street varchar(10), city varchar(25), country varchar(25), Contact int, Email
varchar(25), Salary decimal(6,2));

6HOSPITAL MANAGEMENT SYTEM
P2512513Nurse
create table P2512513Nurse (NurseID int primary key, Shift varchar(50));
Alter table P2512513Nurse
add constraint fk_emp_Nur
foreign key (NurseID)
references P2512513Employee(EmployeeID);
P2512513OtherStaff

7HOSPITAL MANAGEMENT SYTEM
create table P2512513OtherStaff (StaffID int primary key, Role varchar(50));
Alter table P2512513OtherStaff
add constraint fk_emp_Sta
foreign key (StaffID)
references P2512513Employee(EmployeeID);
P2512513NurseGovernsRoom
create table P2512513NurseGovernsRoom (NurseID int, RoomNo int, primary key(NurseID,
RoomNo));
alter table P2512513NurseGovernsRoom
add constraint fk_nur_ngr
foreign key(NurseID)

8HOSPITAL MANAGEMENT SYTEM
references P2512513Nurse (NurseID);
alter table P2512513NurseGovernsRoom
add constraint fk_Roo_ngr
foreign key(RoomNo)
references P2512513Room (RoomNo);
P2512513Patient
create table P2512513Patient (PatientID int primary key, FName varchar(25),
LName Varchar(25), Street varchar(10),
city varchar(25), country varchar(25), Contact int, DoctorID int not null,
StaffID int Not null);

9HOSPITAL MANAGEMENT SYTEM
Alter table P2512513Patient
add constraint fk_doc_pat
foreign key (DoctorID)
references P2512513Doctor(DoctorID);
Alter table P2512513Patient
add constraint fk_OS_pat
foreign key (StaffID)
references P2512513OtherStaff(StaffID);

10HOSPITAL MANAGEMENT SYTEM
P2512513Payment
create table P2512513Payment (PaymentID int primary key, PatientID int, Amount decimal(6,2),
PaymentMode varchar(25), PaymentDate Date);
alter table P2512513Payment
add constraint fk_Rat_Pay
foreign key(PatientID)
references P2512513Patient (PatientID);

11HOSPITAL MANAGEMENT SYTEM
P2512513Room
create table P2512513Room (RoomNo int primary key, Floor varchar(10), Rate decimal(6,2),
Features varchar(100), PatientID int);
Alter table P2512513Room
add constraint fk_pat_Room
foreign key (PatientID)
references P2512513Patient(PatientID);

12HOSPITAL MANAGEMENT SYTEM
P2512513TestRecords
create table P2512513TestRecords (PatientID int,
TestName varchar(50), TestDate Date, Description Varchar(150));
alter table P2512513TestRecords
add constraint fk_Pat_Test
foreign key(PatientID)
references P2512513Patient (PatientID);
Data Population
Employee

13HOSPITAL MANAGEMENT SYTEM
Doctor
Nurse
Other Staff
Nurse Governs Room

14HOSPITAL MANAGEMENT SYTEM
Patient
Payment
Test records

15HOSPITAL MANAGEMENT SYTEM
Indexes
Contact on Employee Table
Following code is able to create emp_contact index. This index is helpful for retrieve the
contact details from P2512513Employee table. emp_contact can quickly retrieve data from
contact column.
create unique index emp_contact
on P2512513Employee (Contact);
Email on Employee Table
Creating emp_email index on employee table. This index is helpful to retrieve the email
from employee table. Hospital authority can retrieve the email address of an employee.
create unique index emp_email
on P2512513Employee (Email);
License no on Doctor Table
Creating Doc_Lic index on doctor table by using create unique index statement. This
index is able to retrieve licenseno of a doctor. By using this index hospital authority can quickly
retrieve all licenseno.
create unique index Doc_Lic
on P2512513Doctor (LicenseNo);
Contact no on Patient Table

16HOSPITAL MANAGEMENT SYTEM
Following codes can create pat_contact index. This index can quickly fetch the contact
number of a patient. Hospital authority can find contact details of patient. This index is very
helpful for hospital authority.
create unique index
on P2512513Patient(Contact);
Query
Q1 Selection of Particular columns from a table
select Fname, Lname, Contact from P2512513Patient;
The query retrieves the patient details including their First name, Last name and contact
details from patient table. This query is useful because by using this query hospital authority can
get the first name, last name and contact details from patient table.
Q2 Inner Join of at least 2 tables
select (e.Fname||' '||e.Lname) as "Doctor Name" from P2512513Doctor d

17HOSPITAL MANAGEMENT SYTEM
inner join P2512513Employee e on e.EmployeeID=d.DoctorID;
The following query returns the doctor name in the current system by joining the employee and
doctors table. This is very helpful query for hospital management to know the details of doctor
and employee.
Q3 Condition using “<”, “>”, LIKE etc.
select RoomNo, Floor, rate, features from P2512513Room where features like 'AC%';
The query returns the room details having room no, floor, rate and features, where the
rooms are Air Conditioned. Hospital authority can find the ac rooms by using this query.
Q4 Use of count and/or another similar mathematical expression

18HOSPITAL MANAGEMENT SYTEM
select E.EmployeeID, (e.Fname||' '||e.Lname) as "Nurse",count(ngr.NurseID) as count from
P2512513Employee e
inner join P2512513Nurse n on e.EmployeeID=n.NurseID
inner join p2512513nursegovernsroom ngr on
ngr.nurseid=n.nurseid group by e.employeeid, (e.Fname||' '||e.Lname);
The Query returns the no of rooms governed by each of the nurses. Inner join has been
used for joining the tables. Above queries is able to retrieve the nurse details with employee id
number. This is very helpful query that can hospital authority use to get nurse details.
Q5 Use of a sorting/ordering facility
select (Fname||' '||Lname) as "Employee Name", salary from P2512513Employee order by salary
desc;

19HOSPITAL MANAGEMENT SYTEM
The query shows the Employee’s name and their salary in decreasing order. This is
helpful query for the hospital management to get the details.
Q6 A condition using IN, NOT NULL, or similar.
select (p.Fname||' '||p.Lname) as "Patient Name", t.testname, t.description from P2512513Patient
p
inner join P2512513TestRecords t on p.PatientID=t.PatientID and t.description in ('Normal');
The Query returns the patient names and their test details where the test results are
normal. Hospital authority can get the details of a patient who has normal report.

20HOSPITAL MANAGEMENT SYTEM

21HOSPITAL MANAGEMENT SYTEM
Bibliography
Amaechi James, C., Agbasonu Valerian, C. and Nwawudu Sixtus, E., 2018. Design and
Implementation of a Hospital Database Management System (HDMS) for Medical
Doctors. International Journal of Computer Theory and Engineering, 10(1).
Kluza, K. and Nalepa, G.J., 2017. A method for generation and design of business processes with
business rules. Information and Software Technology, 91, pp.123-141.