Logical Data Modeling through Physical Design

Introduction
Storage of hospital’s records is very key. Many are times when patients’ medical
reports have to have to be retrieved for treatment and future reference. Hospitals
further keep records of their employees and medical stocks. In this case scenario,
we shall be designing a database for a hospital management system using Oracle
Database management system (Kopia, 2019). Both the conceptual database design
and the logical designs have been provided to illustrate the internal structure of the
database. Structured Query Language (SQL) has been extensively used to
manipulate database objects. Furthermore, sample data and SQL queries have also
been provided to test the database (Langabeer, 2018).

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Selection of the case upon which the database design and
implementation is to be based
(a) Relevant background information on the organization
In this context, a Hospital management system has been selected as the case
scenario. Better Health Care is fictious hospital that offers medical services and
treatment to its patients. The hospital is organized into different sections namely
Management section, Finance section, Pharmacy section, Health records sections,
Labs section, Wards section and Sanitary section. The management section
comprises of all the doctors, nurses and the hospital board of management. The
finance section controls the financial resources of the hospital including ordering
and paying for drugs and the payments of the employees. The Pharmacy section
keeps and disseminates drugs to patients. The health records section keeps medical
records of all the patients who visits the hospital. The labs section carries out
medical tests on patients. The wards section takes care of the patients and the
sanitary section maintain the cleanliness of the hospital.
(b) Operations a database support
The database is expected to store hospital details about the following:
i. Details of the hospital sections i.e. Section ID and Section Name.
ii. Details of all the employees i.e. Employee ID, First Name, Last Name,
Gender, Date of Birth, Job Description, Section, Phone Number, Salary.
iii. Details of the patients i.e. Patient ID, First Name, Last Name, Gender, Date
of Birth, Phone Number.
iv. Details of wards i.e. Ward ID, Ward description
v. Patients’ records i.e. ID, Patient ID, Date of Visit, Diagnosis Results, Ward
ID, Employee ID, Recommendation, Date of Discharge, Bill
(Helskyaho, 2015)

All the health records have to be stored in a manner that enables quick retrieval of
the patients’ data. Furthermore, treatment of all the patients is carried out from the
wards.
Task 1.2: Conceptual database design for the case scenario and the list of
enterprise rules being modelled.
Enterprise rules
The following business rules have been used to model the relationship between
entities.
i. One section in the hospital comprises of many employees and each
employee belongs to one and only one section.
ii. A ward can have optionally many patients at a time.
iii. Each patient can have one or many records.
iv. One patient can be attended to by many employees and one employee
can attend to many patients.
Entity Relationship Diagram (DFD)
The DFD below illustrated the relationship between entities in the database:

(Hernandez, 2013)
From the ERD, five entities can be identified: Section, ward, employee, patient and
health record.
A section should comprise of many employees who belong to a particular section.
Employees specifically doctors, nurses or lab attendants can serve many patients as
well as one patient can be served by many employees. In addition, a patient can
have one or many health records kept by the hospital.
Stage 2: Logical Database Design and Oracle SQL
Implementation/querying
Task 2.1: Logical Database Design
The table below illustrate the logical database design for Better Heath Care hospital.
Table Name Field Name Data Type Constraint