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