IT Course: Database Normalization Project - 3NF and ERD

This assignment focuses on database normalization, specifically implementing the Third Normal Form (3NF) and creating Entity-Relationship Diagrams (ERD) for patient records. The student begins by analyzing a dependency diagram and identifying transitive dependencies. The solution then details the process of transforming the original table into 3NF by creating new tables and attributes to eliminate partial dependencies and ensure data integrity. The assignment includes a revised diagram reflecting the normalized structure and a crow's foot ERD diagram illustrating the relationships between the entities. The final solution includes a discussion on how the normalized database avoids anomalies. The student references a database management systems textbook to support the methodology used.

STUDENT NAME : MANISH KHATIWODA
Student Id :11681209

Dependency diagram for patient record
patient_id patient_name patient_address triage_cat seen_by presenting_
problem
clinician_
name
clinician_
location
departing
status
transitive dependency
partial depedency
student n ame :Manish Kh atiwo da Student Id :11681209 Subject code :I TC-556
From the dependency diagram we can note that triage_cat is dependent on the presenting_problem
but none of the two is a primary key. The triage cat is said to be transitive dependent on the
presenting_problem.
If we assume that every clinician has his/her own location that will make clinician location to be unique
and the clinician location will act as a foreign key. Clinician name and first_seen_by field wil partially
depend on clinician_location.
B). Modify the diagram to meet 3NF requirements. Rename attributes to meet the naming convention
and create new attributes and entities where necessary.
First we will create new tables from our original table.
patient re cords
patient_id patient_name patient_address triage_cat clinician_no departing_statuspresenting_prob-
lem
clinician_no clinician_name clinician_location
clinician
clinician_role
student n ame :Manish Kh atiwo da Student Id :11681209 Subject code :I TC-556
The first step is to remove partial dependency attributes and create new table for them. In our new
table we have added a new attribute clinician_no to act as a foreign key in the patient record table.
Clinician_role is a new name of the first_seen_by which will be dependent on clinician_no primary key.

departing_statusclinician_no
student n ame :Manish Kh atiwo da Student Id :11681209 Subject code :I TC-556
patient_id
patient_id
patient_name patient_address
patient d etails table
problem_id
problem_id
triage_cat presenting_problem
clinician_no clinician_name clinician_location
clinician table
clinician_role
pr oblem table
patient re cord table
The diagram above show a 3NF form. New entities have been created to satisfy the 3rd Normal form.
This table removes the anomalies that may be experience on the database. Now deleting the patient
record will not affect the patient detail table.
The diagram below shows the crow’s foot ERD diagram

student n ame :Manish Kh atiwo da Student Id :11681209 Subject code :I TC-556
patient d etails
patient re cords
clinician
pr oblem table
patient_id
patient_id
problem_id
clinician_no
clinician_name
clinician_location
clinician_role
patient_name
patient_address
clinician_no
problem_id
departing_status
triage_cat
problem_name
pk
pk
pk
pk
fk
fk
at tends
con tain
has

Reference
Raghu, R & Johaness, G. (n.d). Database management system (2nd ed.). McGraw-Hill Higher
Education
Normalization of database. Retrieved from https://www.studocu.com/en/u/1400957