Database Design Project: Normalization and ER Diagrams

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

ITC556
ASSIGNMENT - 3

Table of Contents
A.................................................................................................................................................3
B.................................................................................................................................................3
C.................................................................................................................................................6
List of Figures
Figure 1 Patient Report Dependency Diagram..........................................................................3
Figure 2 Pateint_Report table 1NF............................................................................................3
Figure 3 Patient_details table.....................................................................................................4
Figure 4 Clinician_information table.........................................................................................4
Figure 5 Patient_Report table.....................................................................................................4
Figure 6 Patient_details table.....................................................................................................5
Figure 7 Clinician_information..................................................................................................5
Figure 8 Triage_classification table...........................................................................................5
Figure 9 Patient_Report table.....................................................................................................5
Figure 10 Entity Relationship Diagram.....................................................................................6
2

A.
Figure 1 Patient Report Dependency Diagram
Assumption:
Pateint_id and Clinician_name are introduced as a Composite Primary key. This composite
key can help to store the non-duplicate data in the database.
Partial Dependency:
Pateint_id -> Pateint_name, Pateint_address
Clinician_name -> Clinician_location
Transitive Dependency:
Triage_category -> Presenting_problem
B.
Figure 2 Pateint_Report table 1NF
In the Patient_Report, Pateint_id and Clinician_name are introduced as Composite Primary
key. There are two partial and one transitive dependency exist in the table.
2NF
In 2nd normalization form, we identify the partial dependency and remove it by creating the
new tables. Partial dependency is dependency when a non-prime attribute depends on a part
of the composite primary key, not on whole Composite primary key. In this scenario, there
3

are two partial dependencies as discussed in the Dependency Diagram. To remove the partial
dependency we have introduced some new tables shown below.
Figure 3 Patient_details table
Figure 4 Clinician_information table
Figure 5 Patient_Report table
3NF
In the 3rd normalization form, we identify the transitive dependency. Transitive dependency is
the dependency when a non-prime attribute depends on another attribute which is also a non-
prime attribute. In the Patient_Report transitive dependency exists where Presenting_problem
4

a non-prime attribute depends on Triage_category which is also a non-prime attribute. To
remove the Transitive_dependency we have created a new table which is shown below.
Figure 6 Patient_details table
Figure 7 Clinician_information
Figure 8 Triage_classification table
Figure 9 Patient_Report table
5

C.
Entity Relationship Diagram
Figure 10 Entity Relationship Diagram
Pateint_details (Patient_id, Patient_name, Patient_address)
Triage_classifivcation (Triage_category, Presenting_problem)
Clinician_information (Clinician_name, Clinician_location)
Patient_Report (Patient_id, Clinician_name, Triage_category, Departing_status,
First_seen_by))
6

References
Shen, M., Kshemkalyani, A. D., & Hsu, T. Y. (2015, May). Causal consistency for geo-
replicated cloud storage under partial replication. In 2015 IEEE International Parallel and
Distributed Processing Symposium Workshop (pp. 509-518). IEEE.
El-Halees, A. M., & Kehail, E. O. (2017). Integrate Database Design Techniques with Agile
Applications. Integrate Database Design Techniques with Agile Applications.
Connolly, T., & Begg, C. (2015). Database Systems. Pearson Education UK.
7