Database Normalization Report: A Case Study of Patient Data

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

Contents
Part a...........................................................................................................................................2
Part b..........................................................................................................................................3
Part c...........................................................................................................................................6
References..................................................................................................................................7
List of Figures
Figure 1: Dependency Design....................................................................................................2
Figure 2: 1NF.............................................................................................................................3
Figure 3: Ptnt Table....................................................................................................................3
Figure 4: Clncn table..................................................................................................................3
Figure 5: PatientReport table.....................................................................................................4
Figure 6: Ptnt table.....................................................................................................................4
Figure 7: Clncn table..................................................................................................................4
Figure 8: TAge table..................................................................................................................4
Figure 9: PatientReport table.....................................................................................................5
Figure 10: ER Diagram..............................................................................................................6

Part a
Dependency Design:
Figure 1: Dependency Design
The table PatientReport, Patient_ID and Clinician_Name are considered as the primary keys
and both combines together and called as the Composite Primary Keys. The primary key is
basically assigned with the attribute in order to keep the unique data in the table.
Partial Dependency:
Partial Dependency in a table is encountered when the prime attributes depend on one of the
primary keys present in a table. This dependency is removed from the table by converting the
table into Second Normal Form.
(Patient_ID: Patient_name, Patient_Address)
(Clinician_name: Clinician_Location)
Transitive Dependency:
Transitive Dependency is encountered when Non-prime attributes are dependent on the non-
prime attributes and such dependency is removed from the table by converting the table into
Third Normal Form.
(Triage_category: Presenting_Problem)

Part b
First Normal Form
In the table PatientReport, Patient_ID, as well as the Clinician_Name, are considered as the
primary keys and both together known as the Composite Primary Keys (Carter, P. A. 2016).
The provided table is in First Normal Form and still partial as well as the Transitive
dependencies are present here.
Figure 2: 1NF
Second Normal Form
Second Normal Form is used in order to remove the Partial Dependency from the table.
Partial Dependency is defined as when the attributes are dependent on one of the two
mentioned Primary Keys (Bernard, M., & Bachu, E. 2015). The presence of the two partial
dependencies are seen here-
For removing both of the Partial dependencies presents, the step taken is the creation of the
two tables named as Ptnt and Clncn.
Figure 3: Ptnt Table

Ptnt (Patient_ID, Patient_Name, Patient_Address)
Figure 4: Clncn table
Clncn (Clinician_Name, Clinician_Location)
Figure 5: PatientReport table
PatientReport (Patient_ID, Clinician_Name, Triage_category, First_seen_by,
Presenting_Problem, Departing_Status)
As mentioned above, table PatientReport contains transitive dependency in which
Presenting_Problem purely depends on the Triage_category attribute that is not actually a
part of Primary Key.
Third Normal Form
Third Normal Form is used in order to remove the Transitive Dependency from the table. As
seen above, table PatientReport contains transitive dependency because the attribute
Presenting_Problem purely depends on the Triage_category attribute (Kumar, K., & Azad, S.
K. 2017, October). For removing this, a new table is to be created named as the TAge and

Triage_category would be considered as the foreign key in the table named as the
PatientReport.
Figure 6: Ptnt table
Ptnt (Patient_ID, Patient_Name, Patient_Address)
Figure 7: Clncn table
Clncn (Clinician_Name, Clinician_Location)
Figure 8: TAge table
TAge (Triage_category, Presenting_Problem)

Figure 9: PatientReport table
PatientReport (Patient_ID, Clinician_Name, Triage_category, First_seen_by,
Departing_Status)

Part c
Figure 10: ER Diagram

References
 Carter, P. A. (2016). Building an Inventory Database. In Expert Scripting and Automation
for SQL Server DBAs (pp. 157-175). Apress, Berkeley, CA.
 Bernard, M., & Bachu, E. (2015). Database Systems with Case Studies. PHI Learning
Pvt. Ltd..
 Kumar, K., & Azad, S. K. (2017, October). Database normalization design pattern.
In 2017 4th IEEE Uttar Pradesh Section International Conference on Electrical,
Computer and Electronics (UPCON) (pp. 318-322). IEEE.