ITC556 Assignment 3: Database Normalization and ERD

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

ITC556
ASSIGNMENT - 3

Table of Contents
a..................................................................................................................................................3
b..................................................................................................................................................4
c..................................................................................................................................................7
2

a.
Dependency Diagram;
Figure 1 Dependency Diagram
Composite Primary key: Pateint_id, Clinician_location
Partial Dependency:
Patient_id -> Patient_name, Pateint_address
Clinician_location -> Clinician_name
Transitive Dependency:
Triage_category -> Presenting_problem, Departing_status
3

b.
Normalization:
1NF
Figure 2 1NF of Patient Report
In this table, Patient_name and Pateint_address depend on Patient_id which is a part of the
composite primary key which results in Partial Dependency. Clinician_name also depend on
Clinician_location which is a part of Primary key and also result in Partial dependency.
To attributes, Presenting_problem and Departing_status depend on Triage_category a non-
prime attribute which results in Transitive Dependency.
2NF
To convert the 1NF into 2NF we have to remove the Partial dependency. In this case, there
are two partial dependencies which have to be removed. Following tables are introduced to
remove the partial dependency.
Figure 3 Patients Table
Figure 4 Clinicians Table
4

Figure 5 Patient Report Table
3NF
To convert 2NF in 3NF we have to remove the transitive dependency. In the Patient Report
table, transitive dependency exists. Following tables are introduced to convert the table in the
3NF.
Figure 6 Patients
Figure 7Clinicians Table
5

Figure 8 Triage Categories
Figure 9 Patient Report
6

c.
Entity Relationship Diagram
Figure 10 Entity Relationship Diagram
Patients: (Patient_id, Pateint_address, Pateint_name)
Primary key – Patient_id
Foreign key – No
Clinicians: (Clinician_location, Clinician_name)
Primary key – Clinician_location
Foreign key – No
Triage Categories: (Triage_category, Presenting_problem, Departing_status)
Primary key – Triage_category
Foreign key – No
Pateint report: (Patient_id, Cinician_location, Triage_category, First_seen_by)
Composite Primary key – Patient_id, Cinician_location
Foreign key – Patient_id, Cinician_location,Triage_category
7

References
Harrington, J. L. (2016). Relational database design and implementation. Morgan Kaufmann.
Konik, R. P., Mittelstadt, R. A., Muras, B. R., & Theuer, M. W. (2016). U.S. Patent No. 9,298,829.
Washington, DC: U.S. Patent and Trademark Office.
8