ITC556 Assignment 3: Database Systems Normalization and ERD

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

ITC556
Assignment 3

ITC-556 ASSIGNMENT-3 Database Systems
Table of Contents
a..................................................................................................................................................3
b..................................................................................................................................................3
c..................................................................................................................................................7
References..................................................................................................................................9
2

ITC-556 ASSIGNMENT-3 Database Systems
a.
Figure 1 Patient_report table
There is no partial dependency.
Transitive Dependency: When one or more non prime attributes depends on a non prime
attribute then transitive dependency occurs.
(Clinician_location :- Clinician_name)
(Triger_Category :- Prsenting_problem)
b.
1NF
Figure 2 Patient_report table
Transitive Dependency:
(Clinician_location :- Clinician_name)
(Triger_Category :- Prsenting_problem)
2NF
1NF to 2NF
In order to convert the table from 1NF to 2NF, we have to remove the partial dependency
from the table but there is no partial dependency in this database only have transitive
dependencies.
3

ITC-556 ASSIGNMENT-3 Database Systems
Figure 3 Patient_report table
Transitive Dependency:
(Clinician_location :- Clinician_name)
(Triger_Category :- Prsenting_problem)
2NF to 3NF
In order to convert the table from 2NF to 3NF, we have to remove the transitive dependency
from the table. In order to remove the transitive dependencies we have introduced new
attribute Report_id and three tables or entities as follows:
Figure 4 Patient Table
Patient (Patient_id, Pateint_name, Pateint_address)
Figure 5 Triage table
Triage (Triage_category, Presenting_problem)
4

ITC-556 ASSIGNMENT-3 Database Systems
Figure 6 Clinician table
Clinician (Clinician_location, Clinician_name)
Figure 7 Patient_report table
Patient_report (Report_id, Patient_id, Triage_category, Clinician_location, First_seen_by,
Departing_status)
5

ITC-556 ASSIGNMENT-3 Database Systems
ERD
Figure 8 3NF ERD
6

ITC-556 ASSIGNMENT-3 Database Systems
c.
Figure 9 Modified ERD
Patient (Patient_id, Patient_name, Date_of_birth, Gender, Contact_no, Patient_address)
Primary key: Patient_id
Foreign key: No
Clinician (Clinician_location, Clinician _name, Number_of_staff,)
Primary key: Clinician_location
Foreign key: No
Triage (Triage_category, Presenting_problem, Body_condition , Body_temperature,
Eye_colour)
Primary key: Triage_category
Foreign key: No
7

ITC-556 ASSIGNMENT-3 Database Systems
Patient_Report (Report_id, Patient_id, Clinician_location, Triage_category, First_seen_by,
Departing_status )
Primary key: Report_id
Foreign key: Patient_id, , Clinician_location, Triage_category
8

ITC-556 ASSIGNMENT-3 Database Systems
References
Roy-Hubara, N., Rokach, L., Shapira, B. and Shoval, P., 2017. Modeling graph database
schema. IT Professional, 19(6), pp.34-43.
Connolly, T. and Begg, C., 2015. Database Systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
9