EER Modelling and SQL

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