Database Normalization and ERD: ITC556 Database Systems Assignment 3

Past papers and solved assignments for students. This assignment covers database normalization and ERD design.

ITC556
Database System
Assignment - 3

Table of Contents
a..................................................................................................................................................3
b..................................................................................................................................................3
c..................................................................................................................................................6
References..................................................................................................................................7
2

a.
Figure 1 Dependency Diagram
Dependency diagram is created to show the relationship between the primary key and non-
prime attributes. It also shows the transitive dependency and partial dependency.
In this, we have assumed Patient_id and Clinician_location as composite primary key and
others as Non-prime attributes.
b.
1NF
Figure 2 1st Normalization
It is not a normalized form it contains partial and transitive dependencies.
Partial Dependency: Partial dependency appears when a non-prime attribute depends on a
part of the primary key.
(Pateint_id, Patient_name, Patient_address)
(Clinician_location, Clinician_name)
Transitive Dependency: Transitive dependency appears when a non-prime attribute depends
on another non-prime attribute.
(Triage_category, First_seen_by)
2NF
To achieve the 2nd normalization form we have to remove all the partial dependencies from
the table. To remove the partial dependencies we have created two tables Patient and
Clinicians.
3

Figure 3 Patient
Figure 4 Clinicians
Figure 5 Report
3NF
To achieve the 2nd normalization form we have to remove the transitive dependency from the
table. There is transitive dependency existed in the Report table. To remove the transitive
dependency we have created one table named as Triages.
Figure 6 Patient
4

Figure 7 Clinicians
Figure 8 Triages
Figure 9 Report
5

c.
ERD
Figure 10 ERD
6

References
Kasbe, T. (2016). DBMS Concepts-A Practical Approach. Educreation Publishing.
Fredstam, M., & Johansson, G. (2019). Comparing database management systems with
SQLAlchemy: A quantitative study on database management systems.
CE, S. I. (2016). Data Base Management System using SQL.
7