Faculty of Computing: EER Modelling and SQL Database Design Project

This assignment presents a comprehensive database design for a multi-specialty hospital, XYZ Hospital, covering various departments, doctors, patients, and operations. The solution begins with the selection of the XYZ hospital as a case study, followed by the conceptual database design using an Enhanced Entity Relationship (EER) diagram. This diagram visualizes entities like doctors, departments, and patients, including bridge tables for check-ups and operations to handle many-to-many relationships. The second stage details the logical database design, table creation using SQL, index creation for performance optimization, and data population with sample records. The assignment concludes with several SQL queries demonstrating data retrieval and manipulation, such as retrieving patient and doctor names, operation details, department and doctor counts, and doctor salaries. The assignment is a practical application of database design principles and SQL querying techniques.

Running head: EER MODELLING AND SQL
EER Modelling and SQL
Name of the Student
Name of the University
Author’s Note:

1EER MODELLING AND SQL
Table of Contents
Stage 1:.......................................................................................................................................2
Task 1.1: Case Selection........................................................................................................2
Task 1.2 Conceptual Database Design:..................................................................................3
Stage 2:.......................................................................................................................................5
Task 2.1 Logical Database Design:........................................................................................5
Task 2.2 Table Creation:........................................................................................................6
Task 2.3 Index Creation:........................................................................................................9
Task 2.4 Data Population:......................................................................................................9
Task 2.5 SQL Query:...........................................................................................................13
Bibliography:............................................................................................................................15

2EER MODELLING AND SQL
Stage 1:
Task 1.1: Case Selection
The selected organization is the XYZ hospital. It is a multispecialty hospital. This
hospital has various department under which various doctors operate. The hospital is
consisted of rooms in which every patient remains admitted for their treatment. The people
who work in the hospital are doctors, compounders, nurses and other general workers. People
with different types of ailment arrive at hospital. After following proper procedure, a patient
will go through check-up by an available doctor. If the check-up result is positive, then the
patient is within the hospital. The patient is later discharged after the treatment is complete.
The aim of the report is to design and implement a database for the XYZ hospital. The
database will be designed for maintaining the records of several departments, doctors, rooms
and many other things in the hospital. The database will also hold the records of the patients,
admitted patients, patents who was checked by doctors, patients who have been operated and
many more.
The XYZ hospital has several departments. These departments are pathology,
orthopaedic, dental, aesthetic, gynaecology, emergency, bool bank, laboratory, I.C.U.,
theatre, neurology, M.R.I., cardiology, corpse, operation and many more. The hospital has an
OPD or Out Patient Department. To get a card, the patients have to come to this Out Patient
Department. These cards server the purpose of entry card for individual patients. To visit a
doctor and check-up, the card is essential. After the patient’s entry in the card is done, the
patient can go to the concerned doctor’s room to receive check-up his/her ailments. Based on
the ailments the doctor admits the patient to a specific department. However, if the case is not
serious then the doctor prescribes some medicines to the patient. As per the need, the patient
can choose general room or private room. It is mandatory to specific formalities, such as

3EER MODELLING AND SQL
room charges and many other formalities, of the hospital before the patient gets admitted to
the hospital. The doctor discharges the patient after the treatment is done. The patient again
have to fulfil specific formalities of the hospital before he gets discharged from the hospital.
The patient have to pay test cost, balance cost, operation cost, doctor’s cost and any more.
The hospital has two kind of doctors such as the call on doctors and regular doctors. The
regular doctors will be present in the hospital daily. The calls on doctors are invited by the
XYZ hospital if the concerned regular doctor is not present in the hospital.
Task 1.2 Conceptual Database Design:
The EER or Enhanced Entity Relationship diagram is a high-level data model diagram
that can include the extensions to the main entity relationship model. The EER diagram is
designed for visualizing the concepts like sub and super class, union, Specialization and
Generalization and aggregation. The EER diagrams are more accurate than the database
schema diagrams. It has entire modelling concepts of ER model included within it. The data
constraints and properties are more accurately reflected through EER model.
Only seven entities, including the super and sub class, has been considered while
creating the Enhanced Entity Relationship model. It is because the proposed database needs
to be simple and understandable. All the necessary entities like doctor, department and
patient has been selected. As one patient can go for check-up several times and each time a
separate doctor can see the patient, there is a many-to-many relationship among these two
entities. To eliminate this entity, a bridge table called check-up has been introduced in the
EER diagram. Another bridge table is operation. Only the admitted patients’ treatment
included operation, this is linked to only AdmittedPatient entity. The reason for creating this
table is same as check-up. A patient can go through several operations. Here, one thing is
different and that is the date as a part of composite primary key. The data is also considered

4EER MODELLING AND SQL
as primary key because the same doctor can do operation on same patient different times. The
date unique specifies the operation in this case.
Figure 1: Enhanced Entity Relationship Diagram
(Source: Created by Author)

5EER MODELLING AND SQL
Stage 2:
Task 2.1 Logical Database Design:
Figure 2: Logical Database Design of XYZ Hospital
(Source: Created by Author)

6EER MODELLING AND SQL
Task 2.2 Table Creation:
CREATE TABLE p17244121_department (
deptID number(10) NOT NULL,
departmentName varchar2(50) NOT NULL,
facilities varchar2(50),
location varchar2(50),
CONSTRAINT departments_pk PRIMARY KEY (deptID)
);
CREATE TABLE p17244121_doctor (
docNumber number(10) NOT NULL,
deptID number(10) NOT NULL,
name varchar2(50) NOT NULL,
qualification varchar2(50),
contactNumber varchar2(50),
location varchar2(50),
salary decimal(10,2),
CONSTRAINT doctors_pk PRIMARY KEY (docNumber),
CONSTRAINT fk_department FOREIGN KEY (deptID) REFERENCES
p17244121_department(deptID)
);

7EER MODELLING AND SQL
CREATE TABLE p17244121_RegularPatient (
patNumber number(10) NOT NULL,
name varchar2(50) NOT NULL,
address varchar2(50),
sex varchar2(50),
age number(10),
CONSTRAINT RegularPatients_pk PRIMARY KEY (patNumber)
);
CREATE TABLE p17244121_PatientAdmit (
patNumber number(10) NOT NULL,
name varchar2(50) NOT NULL,
address varchar2(50),
sex varchar2(50),
age number(10),
medicalCondition varchar2(50),
emergencyContact varchar2(50),
contactPersonName varchar2(50),
CONSTRAINT PatientAdmits_pk PRIMARY KEY (patNumber)
);
CREATE TABLE p17244121_CheckUP (

8EER MODELLING AND SQL
checkUpID number(10) NOT NULL,
docNumber number(10) NOT NULL,
regularpatient number(10),
admitpatient number(10),
checkupDate Date NOT NULL,
diagnosis varchar2(50) NOT NULL,
status varchar2(50),
treatment varchar2(50),
CONSTRAINT checkups_pk PRIMARY KEY (checkUpID ),
CONSTRAINT fk_doctor FOREIGN KEY (docNumber) REFERENCES
p17244121_doctor(docNumber),
CONSTRAINT fk_regularpatient FOREIGN KEY (regularpatient) REFERENCES
p17244121_RegularPatient(patNumber),
CONSTRAINT fk_admitpatient FOREIGN KEY (admitpatient) REFERENCES
p17244121_PatientAdmit(patNumber)
);
CREATE TABLE p17244121_Operation (
docNumber number(10) NOT NULL,
patNumber number(10) NOT NULL,
dateOfOperation Date NOT NULL,

9EER MODELLING AND SQL
roomNumber number(10),
bedNumber number(10),
admitOn Date,
dischargeOn Date,
CONSTRAINT operations_pk PRIMARY KEY (docNumber, patNumber,
dateOfOperation),
CONSTRAINT fk_operationdoctor FOREIGN KEY (docNumber) REFERENCES
p17244121_doctor(docNumber),
CONSTRAINT fk_admitpatientoperation FOREIGN KEY (patNumber)
REFERENCES p17244121_PatientAdmit(patNumber)
);
Task 2.3 Index Creation:
CREATE INDEX idx_operation on p17244121_Operation(roomNumber,
bedNumber, admitOn, dischargeOn) TABLESPACE Users;
CREATE INDEX idx_doctor on p17244121_doctor(name) TABLESPACE Users;
CREATE INDEX idx_checkup on p17244121_CheckUP(checkupDate, diagnosis)
TABLESPACE Users;
CREATE INDEX idx_patientadmit on p17244121_PatientAdmit(name,
medicalCondition) TABLESPACE Users;
Task 2.4 Data Population:
INSERT INTO p17244121_department VALUES (1, 'pathology', 'ABC', 'Building
1');

10EER MODELLING AND SQL
INSERT INTO p17244121_department VALUES (2, 'orthopaedic', 'ABC', 'Building
2');
INSERT INTO p17244121_department VALUES (3, 'dental', 'ABC', 'Building 3');
INSERT INTO p17244121_department VALUES (4, 'aesthetic', 'ABC', 'Building 1');
INSERT INTO p17244121_department VALUES (5, 'gynaecology', 'ABC', 'Building
2');
INSERT INTO p17244121_doctor VALUES (1, 1, 'Jackson Grasby', 'pathology', '(02)
4972 1963', '29 Milbrodale Road LEMINGTON NSW 2330', '2500.00');
INSERT INTO p17244121_doctor VALUES (2, 3, 'Jack Jukes', 'Dentist', '(08) 9073
4929', '87 Farrar Parade KADATHINNI WA 6519', '1800.00');
INSERT INTO p17244121_doctor VALUES (3, 3, 'Amber Ormond', 'Dentist', '(03)
5332 2045', '21 Sullivan Court WOOMELANG VIC 3485', '1650.00');
INSERT INTO p17244121_doctor VALUES (4, 5, 'Amber Ormond', 'Gynecologist',
'[phone]', '40 Ridge Road KULLOGUM QLD 4660', '3650.00');
INSERT INTO p17244121_doctor VALUES (5, 5, 'Layla Riordan', 'Gynecologist',
'[phone]', '35 Swanston Street PARADISE VIC 3381', '3200.00');
INSERT INTO p17244121_RegularPatient VALUES (1, 'Joshua Gribble', '84 High
Street STENHOUSE BAY SA 5575', 'Male', 28);
INSERT INTO p17244121_RegularPatient VALUES (3, 'Lucinda Leeson', '41 Halsey
Road BLACKFELLOWS CREEK SA 5201', 'Female', 38);

11EER MODELLING AND SQL
INSERT INTO p17244121_RegularPatient VALUES (5, 'Phoebe Strahan', '3 Daly
Terrace RIDGEWOOD WA 6030', 'Female', 31);
INSERT INTO p17244121_RegularPatient VALUES (7, 'Jordan Tighe', '71
Davenport Street CORROWONG NSW 2633', 'Male', 52);
INSERT INTO p17244121_RegularPatient VALUES (9, 'Hunter Conyers', '7 Amiens
Road GLEN AYR NSW 2850', 'Male', 79);
INSERT INTO p17244121_PatientAdmit VALUES (2, 'Natasha Mullagh', '61 Clifton
Street MOORILIM VIC 3610', 'Female', 67, 'Critical', '[phone]', 'Sienna Kellaway');
INSERT INTO p17244121_PatientAdmit VALUES (4, 'Victoria Kiernan', '85
Yangan Drive BANOON NSW 2347', 'Female', 56, 'Moderate', '[phone]', 'Noah
Moffat');
INSERT INTO p17244121_PatientAdmit VALUES (6, 'Emma Gardiner', '95 Quintin
Street WAMURAN QLD 4512', 'Female', 76, 'Critical', '[phone]', 'Henry Rapke');
INSERT INTO p17244121_PatientAdmit VALUES (8, 'Christian Dumaresq', '60
Whitehaven Crescent WARRAMI QLD 4854', 'Male', 96, 'Good', '[phone]', 'Jasper
Wentworth-Shields');
INSERT INTO p17244121_PatientAdmit VALUES (10, 'Jai Wienholt', '14 Bette
McNee Street DHURAGOON NSW 2733', 'Male', 72, 'Critical', '[phone]', 'Jasper
Wentworth-Shields');