Database Design Project: Normalization and ERD for Patient Records

Desklib provides solved assignments and past papers to help students succeed.

ITC556
ASSIGNMENT - 3

Table of Contents
a..................................................................................................................................................3
b..................................................................................................................................................3
c..................................................................................................................................................8
References..................................................................................................................................9

a.
Dependency Diagram
Figure 1 Dependency Diagram
In this dependency Diagram, there is a composite primary key includes Patient_id and
Clinician_location in order to uniquely identify each patient’s record. There is transitive and
partial dependencies also occur in this dependency diagram which is discussed below:
Partial dependency:
Patient_id: Patient_name, Patient_address
Clinician_location: Clinician_name
Transitive dependency:
Triage_category: First_seen_by
b.
1NF
Figure 2 1NF
In the 1NF we have to remove the partial dependency from the system. Partial dependency
occurs when a non-prime attribute depends on a part of primary key or composite primary
key and not on the whole primary key. In this, there are two partial dependencies occur.
Patient_name and Patient_address depend on Patient_id and not Clinician_location.
Likewise, Clinician_name depends only on Clinician_location, not on Patient_id.

There is one transitive dependency also which occurs when a non-prime attribute depends on
another non-prime attribute rather than on primary key. In this First_seen_by which is a non-
prime attribute depends on another non-prime attribute Triage_category.

2NF
Converting 1NF to 2NF
We have removed the partial dependency from the table by introducing some new tables
which are shown below:
Figure 3 Patients tables
Patients (Pateint_id, Patient_address, Patient_name)
Figure 4ClinicianDetails table
ClinicianDetails (Clinician_location, Clinician_name)
Figure 5 PatientReport table
PatientReport (Patient_id, Clinician_location, Triage_category, First_seen_by,
Presenting_problem, Departing_status)

3NF
Converting 2NF to 3NF
We have removed transitive dependency by creating a new table from PatientReport table
named as Triage table.
Figure 6 Patients Table
Patients (Pateint_id, Patient_address, Patient_name)
Figure 7 ClinicianDetails table
ClinicianDetails (Clinician_location, Clinician_name)
Figure 8 Triage table

Triage (Triage_category, First_seen_by)
Figure 9 PatientReport Table
PatientReport (Patient_id, Clinician_location, Triage_category, Presenting_problem,
Departing_status)

c.
Entity Relationship Diagram
Figure 10 Entity Relationship Diagram

References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Kruse, S. L. K., & Wells, M. G. (2016). Optionality of ERD Relationships: Project for the
Introduction to Database Course. In Proceedings of the EDSIG Conference ISSN (Vol. 2473,
p. 3857).
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.