Developing a Database for Healthcare Organization: Project Report

This project focuses on the development of a database for a healthcare organization, aiming to replace error-prone spreadsheets with a more efficient and user-friendly system. The project involves designing a relational database management system (RDBMS) using Microsoft Access to store and manage data related to diseases and patients. The design process includes creating an Entity-Relationship (ER) diagram to visualize entities like 'Diseases' and 'Patient', and defining their attributes. The project addresses the many-to-many relationship between diseases and patients by implementing a bridge table to avoid data anomalies. The assignment thoroughly explains functional dependencies, and the normalization process to ensure data integrity, covering First, Second, and Third Normal Forms. The database design allows for efficient querying of patient and disease information, including the identification of disease frequencies, based on referential integrity between the entities. The project highlights the advantages of using a database over spreadsheets for healthcare data management, providing doctors and staff with a simplified interface for easy communication and data retrieval, ultimately improving the efficiency of treatment-related information access.

Running head: DATABASE DEVELOPMENT
Database Development
Name of the Student
Name of the University
Author’s note

1DATABASE DEVELOPMENT
Table of Contents
1) Approval of Project Topic:..........................................................................................................2
2) Abstract:......................................................................................................................................2
3) Design Method:...........................................................................................................................2
4) Description of Entities and Attributes:........................................................................................3
5) Functional Dependency:..............................................................................................................4
6) Relationships:..............................................................................................................................5
7) Normalization:.............................................................................................................................5
a) First Normal Form:..................................................................................................................5
b) Second Normal Form:.............................................................................................................6
c) Third Normal Form:....................................................................................................................6
Bibliography:...................................................................................................................................8

2DATABASE DEVELOPMENT
1) Approval of Project Topic:
Yes
2) Abstract:
The database is created for supporting a healthcare organization to record the diseases
and patient database. The database is supposed to be storing the data of the diseases, patients and
which patient has diagnosed with which diseases. The spread sheets are very error prone and
time consuming. Finding results from a spread sheet is a complex work that everyone does not
understand. For this reason, the database is implemented. It will provide the doctors and other
staff of the organization a simple interface to interact and easy communication medium. The
database is developed also with the purpose of allowing the doctors to find the treatment related
information very easily. As the RDBMS is based on establishing relationships among the
entities, the developed database in Microsoft Access will be extremely useful for the
organization.
The database will run the query of identification of the patient and diseases name based
on the most frequent diseases from a certain date to year end. This query combines both the
patient and diseases entities. These entities together provide the details of the diseases frequency
information. This query is based on the referential integrity between patient and diseases. If the
referential integrity is correctly formed, then finding the result would be possible otherwise the
database cannot support the basic need of the query.

3DATABASE DEVELOPMENT
3) Design Method:
The ER diagramming has been followed for designing the database of the system. As the
name suggests, it is based on entities and relationships and these two things are only focused on
in this project. An ERD or entity relationship diagram can be referred to as the graphical
visualization of information system in terms of data used and processed in the system. Through
the ER Diagram, the entities like diseases and patient is created. The definition of the model
states that a real world system does not support any many-to-many relationship. After designing
the many-to-many relation among the diseases and patient, it was not possible to correctly
represent the patient and diseases relation properly without data anomalies. Therefore, as per the
instructions of the ER diagram development, a bridge table is created to support the relation and
convert the many-to-many into one-to-many.
4) Description of Entities and Attributes:
The first entity is the diseases itself. This entity has a primary key named as ‘ID’. This is
an auto number which means every time a new row is created it will updated automatically. No
two auto numbers are same in the same entity. This number is not implemented for give meaning
to the entity, it is here for providing structural support to the entity. The entity has no such
attribute that will have different value for each row, therefore this ID is used as primary key. The
other filed is the name. This name will be used in the real world practice like in prescription or
bills instead of ID. The diseases has symptoms, tests, primary treatments and all these
information can be easily captured using the attributes of the entity. Each row of the entity just
hold the information of each diseases.

4DATABASE DEVELOPMENT
The second entity is the patient. The patient entity is developed for the purpose of storing
the patient details and the details of their treatment. This entity has a primary key named as ‘ID’.
This is an auto number which means every time a new row is created it will updated
automatically. No two auto numbers are same in the same entity. This number is not
implemented for give meaning to the entity, it is here for providing structural support to the
entity. The entity has no such attribute that will have different value for each row, therefore this
ID is used as primary key. The name of the patient will be used in the prescription but in the
bills, the ID will also be present. This entity has a diseases foreign key to store the diseases that it
had. This makes the whole entity prone to data anomaly. It is fine until one patient is diagnose
with a different diseases second time. This time the same patient has to be created again to just
store the diseases ID. This is the issue many-to-many relation can create in real world. However,
a bridge table can easily handle this issue.
5) Functional Dependency:
It is possible to state that the diseases name and ID also the patient name and ID can
determine each other.
ID  name
name  ID , for both the entities as these two attributes are same in patient and diseases
entities. However, this condition does not apply when two names are same but ID is different.
Therefore, only the universal condition will be considered and that is ID determines the name.
As ID is primary key, it cannot same for two or more rows.
ID  name.

5DATABASE DEVELOPMENT
Like the name attribute, the other attributes also have dependency on the primary key. It
is shown in the below.
ID  name, primaryTreatment, possibleTests, mainMedicines, symptoms
ID  name, residentialAddress, contactNumber, emailAddress,
emergencyContactNumber, contactPersonName, diseases
Now the issues is with the diseases attribute in the patient entity. If it is considered that
all attributes are in one entity and there is no normalization then dependency would be like
following.
diseasesID, patientID  diseases
As the above functional dependency suggests, the diseases is dependent on both the
primary keys.
patientID  patientName, residentialAddress, contactNumber, emailAddress,
emergencyContactNumber, contactPersonName, diseases, diseasesName, primaryTreatment,
possibleTests, mainMedicines, symptoms
diseasesID  diseasesName, primaryTreatment, possibleTests, mainMedicines,
symptoms, diseases
So the transitive dependency is existed through the diseasesID.
6) Relationships:
The diseases and patient entities have many-to-many relationship among them. It is
because, the patient can be diagnosed with the different disease next time he/she comes for
checkup. On the other side, more than one patient can be diagnosed with same diseases.

6DATABASE DEVELOPMENT
7) Normalization:
a) First Normal Form:
Suppose the table is like HealthCare (patientID, patientName, residentialAddress,
contactNumber, emailAddress, emergencyContactNumber, contactPersonName, diseasesName,
primaryTreatment, possibleTests, mainMedicines, symptoms)
Now the diseases related attributes have to be multivalued. The primary condition for
being in first normal form is no multivalued data should be present in the database. Therefore the
new table must have atomic valued attributes. This means, for each diseases, the patient details
will be repeated.
b) Second Normal Form:
The table must not have any partial dependency to be in the second normal form. The
partial dependency is as shown below.
diseasesID  diseasesName, primaryTreatment, possibleTests, mainMedicines,
symptoms, diseases
The dieasesID is not a primary key but other non-key attributes is dependent on it. Two
tables are created diseases and patient. The tables are as following.
Patient (patientID, patientName, residentialAddress, contactNumber, emailAddress,
emergencyContactNumber, contactPersonName, diseases)
Diseases (dieasesID, diseasesName, primaryTreatment, possibleTests, mainMedicines,
symptoms)
The diseases act as the foreign key.

7DATABASE DEVELOPMENT
c) Third Normal Form:
The entity must not have any transitive dependency. However, it is already shown the
transitive dependency remains through dieasesID. If it is removed then following entities are
created.
Patient (ID, name, residentialAddress, contactNumber, emailAddress,
emergencyContactNumber, contactPersonName)
Diseases (ID, name, primaryTreatment, possibleTests, mainMedicines, symptoms)
PatientDiseases (patientID, diseasesID, startDate, endDate)

8DATABASE DEVELOPMENT
Bibliography:
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Greenstein, M. A., Grunin, G., Schwenger, M. N., & Swamikrishnan, P. (2018). U.S. Patent
Application No. 15/837,211.
Setiaji, B., & Wibowo, F. W. (2016). Chatbot using a knowledge in database. In 7th
International Conference on Intelligent Systems, Modelling and Simulation.