Report on Database Normalization for Patient Records

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

Contents
Part Number A...........................................................................................................................2
Part Number B............................................................................................................................3
Crow’s Foot ERD.......................................................................................................................6
References..................................................................................................................................7
Table of Figures
Figure 1: Dependency Diagram.................................................................................................2
Figure 2: 1st NF...........................................................................................................................3
Figure 3: DetailsOfPatient Table...............................................................................................3
Figure 4: DetailsOfClinician table.............................................................................................4
Figure 5: ReportOfPatient table.................................................................................................4
Figure 6: DetailsOfPatient table.................................................................................................4
Figure 7: DetailsOfClinician table.............................................................................................5
Figure 8: TriAge table................................................................................................................5
Figure 9: ReportOfPatient table.................................................................................................5
Figure 10: Crow’s Foot ERD.....................................................................................................6

Part Number A
Dependency Diagram
Figure 1: Dependency Diagram
In the provided table, given the name as the ReportOfPatient, the composite key is found that
actually occurs when a single table contains two primary keys. The primary keys present in
this table Patient_ID and Clinician_location. Normalization is the process that has to be
applied to the given table in order to remove the inconsistency as well as the anomalies from
the data. Partial Dependency is removed in the Second NF whereas Transitive Dependency is
removed in the Third NF.
Partial Dependency:
(Patient_ID: Patient_name, Patient_Address)
(Clinician_name: Clinician_Location)
Transitive Dependency:
(Triage_category: Departing_Status)

Part Number B
1st Normal Form
Talking about the given table named ReportOfPatient, the composite key present in the data
given creates the cause for the Partial as well as the Transitive dependencies Link, (S., &
Prade, H. 2019). In 1st Normal Form, only the repeated values are removed in order to keep
the consistency in the data. For moving further and the removal of the Partial and Transitive
dependencies takes place in the next two processes.
Figure 2: 1st NF
2nd Normal Form
The main process that occurs in removing the Partial dependency present in the data is the 2nd
Normal Form (Chen et al., 2015). Partial dependency occurred in this table is due to the
dependency of the prime attribute on one of the primary keys among the two present in the
table. In order to overcome this issue, two different tables are introduced named as
DetailsOfPatient and DetailsOfClinician.
Figure 3: DetailsOfPatient Table
DetailsOfPatient (Patient_ID, Patient_Name, Patient_Address)

Figure 4: DetailsOfClinician table
DetailsOfClinician (Clinician_Name, Clinician_Location)
Figure 5: ReportOfPatient table
ReportOfPatient (Patient_ID, Clinician_Name, Triage_category, First_seen_by,
Presenting_Problem, Departing_Status)
3rd Normal Form
The last process that is occurred in the process of Normalisation is the 3rd Normal Form. The
data in this process get free of the Transitive dependency (Hamouda, S., & Zainol, Z. 2017,
August). The attribute which is not the part of the Primary key depends on the non-prime
attribute creates the Transitive dependency in the data. Here, TriAge is the new table
introduced and TriAge_Category is made as to the Primary key.
Figure 6: DetailsOfPatient table
DetailsOfPatient (Patient_ID, Patient_Name, Patient_Address)

Figure 7: DetailsOfClinician table
DetailsOfClinician (Clinician_Name, Clinician_Location)
Figure 8: TriAge table
ParametersOfTriAge (Triage_category, Departing_Status)
Figure 9: ReportOfPatient table
ReportOfPatient (Patient_ID, Clinician_Name, Triage_category, First_seen_by,
Presenting_Problem)

Crow’s Foot ERD
Figure 10: Crow’s Foot ERD

References
 Link, S., & Prade, H. (2019). Relational database schema design for uncertain
data. Information Systems.
 Chen, J., Stanley-Brown, J., & Edmunds, P. (2015). RELATIONAL DATABASE
LOGICAL DESIGN-A CASE STUDY IN TOURISM DEVELOPMENT AND
CUSTOMER RELATIONSHIP MANAGEMENT. Journal of International Management
Studies, 15(3).
 Hamouda, S., & Zainol, Z. (2017, August). Document-Oriented Data Schema for
Relational Database Migration to NoSQL. In 2017 International Conference on Big Data
Innovations and Applications (Innovate-Data) (pp. 43-50). IEEE.