IMAT5103: Database Design and Implementation for Hospital Management

This assignment presents a comprehensive hospital database design and implementation project. It begins with a case study analysis of a hospital's need for a computerized database system to replace a manual system. The project then proceeds to conceptual and logical database design, including Entity-Relationship diagrams. The core of the assignment involves creating tables using Oracle DBMS, implementing SQL queries for data retrieval, and creating useful indexes to optimize database performance. The SQL queries demonstrate the ability to select, join, sort, count, and use subqueries to extract relevant information from the database. The project concludes with a bibliography of relevant academic sources.

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

1
HOSPITAL MANAGEMENT
Table of Contents
Stage 1: Scenario and Conceptual Database Design..................................................................2
Task 1.1: A Case study of the hospital management system:................................................2
Task 1.2: Conceptual database design:..................................................................................3
Stage 2: Logical Database Design and Oracle SQL Implementation/querying.........................4
Task 2.1: Logical Database Design:.......................................................................................4
Task 2.2: Creating tables using Oracle DBMS......................................................................5
Task 2.3: Creating the four most useful indexes..................................................................13
Task 2.4: SQL Query writing...............................................................................................15
Bibliography:............................................................................................................................19

2
HOSPITAL MANAGEMENT
Stage 1: Scenario and Conceptual Database Design
Task 1.1: A Case study of the hospital management system:
As XYZ hospital has many advanced technologies, the patient rate is increasing day
by day. The old database is unable to handle the workflow and store every information. The
old system is maintained manually. Maintaining a manual database is hard and time-
consuming. So they are appointing a software analyst to create a reliable database for XYZ
hospital. They need an advanced computerized database system that can store every detail
and any hospital admin can maintain this database system.
In the XYZ hospital database, doctors, patients, nurses and hospital employees are the
entities in this database. It is very hard to store and maintain this information manually. This
database can store all the necessary information for the organization. Many patients can visit
the hospital but they need to fix an appointment before admit. A doctor, nurse or any official
staff of the hospital is an employee. A patient needs to pay the full amount of money before
they discharged from the XYZ hospital. Every department has several rooms and each doctor
is assigned to a particular patient. This database is beneficial for the XYZ hospital.

3
HOSPITAL MANAGEMENT
Task 1.2: Conceptual database design:
Figure 1: Conceptual data model of Hospital management
(Source: created by author)

4
HOSPITAL MANAGEMENT
Stage 2: Logical Database Design and Oracle SQL
Implementation/querying
Task 2.1: Logical Database Design:
Figure 1: Logical data model of Hospital management
(Source: created by author)
Task 2.2: Creating tables using Oracle DBMS
Creating P2519719Department table
CREATE TABLE P2519719Department (department_id int NOT NULL PRIMARY KEY,
department_name VARCHAR2(20));

5
HOSPITAL MANAGEMENT
The creation of the P2519719Department table is successful.
Creating P2519719Doctor table.
CREATE TABLE P2519719Doctor (doctor_id int NOT NULL PRIMARY KEY,
First_name VARCHAR2(20),Last_name VARCHAR2(20),Department_id int references
P2519719Department (department_id));
P2519719Doctor is created.
Creating P2519719Employee table.
CREATE TABLE P2519719Employee (Employee_ID int NOT NULL PRIMARY KEY,
Employee_name VARCHAR2(20),salary int,Department_id int references
P2519719Department (department_id));

6
HOSPITAL MANAGEMENT
P2519719Employee is created.
Creating P2519719Nurse table.
CREATE TABLE P2519719Nurse(nurse_ID int NOT NULL PRIMARY KEY, nurse_Name
varchar2(30),Department_id int references P2519719Department (department_id));
Creation of P2519719Nurse table is successful.
Creating P2519719patient table.
CREATE TABLE P2519719patient(patient_ID int NOT NULL PRIMARY
KEY,patient_name varchar(20), age int,doctor_id int references P2519719doctor
(doctor_id));

7
HOSPITAL MANAGEMENT
Creation of P2519719patient is successful.
Creating P2519719appointment table.
CREATE table P2519719appointment(appointment_ID int NOT NULL PRIMARY KEY,
appointment_date varchar2 (10),doctor_id int references P2519719doctor
(doctor_id),patient_id int references P2519719patient (patient_id));
Creation of P2519719appointment table is successful.
Creating the P2519719payment table.
CREATE table P2519719payment (payment_ID int NOT NULL PRIMARY KEY,
payment_date varchar2(20), amount int,patient_id int references P2519719patient
(patient_id));

8
HOSPITAL MANAGEMENT
P2519719payment table is created.
Creating P2519719room table.
create table P2519719room (room_id int not null primary key,patient_id int references
P2519719patient (patient_id));
P2519719room is created.
Task 2.3: Creating the four most useful indexes
Every record in the database is a key field that can help to recognize the particular
field. In the data structure, the indexing process is able to retrieve the record from a database
inefficient way. By using this method, a database can be optimized. An index can reduce the
disk access when a query is used. The index will help to response SQL query very fast. By
using create index command, an index can be created. Two types of organization mechanism
are followed on indexing to store in the database.
EMPLOYEE_INDEX

9
HOSPITAL MANAGEMENT
create unique index EMPLOYEE_INDEX on P2519719Employee
( Employee_ID,employee_name, salary);
EMPLOYEE_INDEX is created by using the above code. This index will help the
hospitality management to retrieve the employee name, id and their salary. By using this
index name of an employee, id and salary information can be retrieved quickly.
INDEX_PAYMENT
CREATE UNIQUE INDEX INDEX_PAYMENT ON P2519719payment
(patient_id,payment_id,amount);
INDEX_PAYMENT helps to retrieve payment details. Authority is able to retrieve
the payment information when necessary. By using this index payment, data can be retrieved
in seconds.
INDEX_PATIENT
CREATE UNIQUE INDEX INDEX_PATIENT ON P2519719PATIENT
(PATIENT_ID,doctor_ID,patient_name);
INDEX_PATIENT is created by using the above code. Hospital management can use
this index to retrieve patient details. Id of a patient, doctor id and name of a patient can be
retrieved quickly by using this index.

10
HOSPITAL MANAGEMENT
INDEX_APPOINTMENT
CREATE UNIQUE INDEX INDEX_APPOINTMENT ON
P2519719APPOINTMENT(payment_id,patient_id,amount);
INDEX_APPOINTMENT index is generated by using the above code. Hospital
management can retrieve all information on appointment details. This code is beneficial for
retrieving appointment id, doctor id, and patient id.
Task 2.4: SQL Query writing
Query 1:
Select employee_name,salary,department_id from P2519719Employee where
employee_id=2;
This is a simple example of select a particular column. By using this statement,
hospital management can able to see a particular employee by pressing their employee id.
Query 2:
select * from P2519719Employee
inner join P2519719department on
P2519719Employee .employee_id=P2519719department .department_id;

11
HOSPITAL MANAGEMENT
Above code is an example of an inner join. By using this code, management can see
all details of employee with their department details. This is a very useful statement that can
use any hospital organization.
Query 3:
select * from P2519719doctor
full outer join P2519719patient on P2519719doctor.doctor_id=P2519719patient .patient_id;
Above query is able to join two tables. Doctor table and patient table. This is an
example of an outer join. By using this query, hospital management is able to see which
doctor is assigned to a patient.
Query 4:
SELECT * FROM P2519719Employee
ORDER BY salary DESC;

12
HOSPITAL MANAGEMENT
Above query is an example of sorting/ordering facility. By using this query, hospital
management authority is able to see all employee details by their salary descending order.
Query 5:
select count(*) from
P2519719patient;
Above query is an example of count function. By using this query, hospital
management is able to see a total number of patient are admitted in their hospital.
Query 6:
select employee_name,department_id, salary from P2519719Employee where
salary= (select max(salary) from P2519719Employee);

13
HOSPITAL MANAGEMENT
Above code is an example of a sub query. Hospital authority can see the maximum
salary of an employee with all details.

14
HOSPITAL MANAGEMENT
Bibliography:
Böhm, A., Dittrich, J., Mukherjee, N., Pandis, I. and Sen, R., 2016. Operational analytics data
management systems. Proceedings of the VLDB Endowment, 9(13), pp.1601-1604.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Elsaadany, A.M., Alaeddin, F.H., Badran, M.F. and Alsuhaibani, H.S., 2017. How i do it: A
practical database management system to assist clinical research teams with data collection,
organization, and reporting. The Arab Journal of Interventional Radiology, 1(1), p.10.
Gueye, M., Badiane, A.S., Diop, S.B., Ly, M., Gueye, M.D.N., Diop, A.D., Diop, A.N. and
Fall, M., 2017. Assessment of a Database Management System in a Mammogram Unit of a
Radiologic Department in Fann Teaching Hospital (Senegal). International Journal of
Medical Physics, Clinical Engineering and Radiation Oncology, 6(04), p.401.
Han, L.I., 2018. Complex query in hospital information system based on database
intermediate table. Chinese Medical Equipment Journal, 39(1), pp.37-40.
Han, L.I., 2018. Complex query in hospital information system based on database
intermediate table. Chinese Medical Equipment Journal, 39(1), pp.37-40.
Hasan, M., 2018. Oracle database administration (Doctoral dissertation, Daffodil
International University).
Lahiri, T., Chavan, S., Colgan, M., Das, D., Ganesh, A., Gleeson, M., Hase, S., Holloway, A.,
Kamp, J., Lee, T.H. and Loaiza, J., 2015, April. Oracle database in-memory: A dual format
in-memory database. In 2015 IEEE 31st International Conference on Data Engineering (pp.
1253-1258). IEEE.

15
HOSPITAL MANAGEMENT
Mukherjee, N., Chavan, S., Colgan, M., Das, D., Gleeson, M., Hase, S., Holloway, A., Jin,
H., Kamp, J., Kulkarni, K. and Lahiri, T., 2015. Distributed architecture of Oracle database
in-memory. Proceedings of the VLDB Endowment, 8(12), pp.1630-1641.
Na, W.A.N.G. and WANG, J.G., 2017. The Significance of the Establishment of the
Database of Doctor-patient Disputes. DEStech Transactions on Economics, Business and
Management, (icmed).
Van Aken, D., Pavlo, A., Gordon, G.J. and Zhang, B., 2017, May. Automatic database
management system tuning through large-scale machine learning. In Proceedings of the 2017
ACM International Conference on Management of Data (pp. 1009-1024). ACM.
Xie, C.Y., Lin, G.L., Lai, Y.A., Yeh, J., Huang, Y.M., Chung, Y.F. and Cher, T.S., 2017,
November. An Efficient Hierarchical Key Management Scheme for Access Medical
Database. In 2017 International Conference on Information, Communication and
Engineering (ICICE) (pp. 461-463). IEEE.