Database Normalization Report: Organic Pastures Clinic

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

Contents
I........................................................................................................................................................2
II.......................................................................................................................................................2
III.....................................................................................................................................................5
References........................................................................................................................................7
Figure 1: Dependency Diagram.......................................................................................................2
Figure 2: First form of normalization..............................................................................................3
Figure 3: Table ReportPatient..........................................................................................................3
Figure 4: Table Patient.....................................................................................................................3
Figure 5: Clinician Table.................................................................................................................4
Figure 6: Patient Table.....................................................................................................................4
Figure 7: Clinician Table.................................................................................................................4
Figure 8: Triage Table.....................................................................................................................5
Figure 9: Report_Patient table.........................................................................................................5
Figure 10: ER diagram.....................................................................................................................6

I.
Dependency Diagram is given as:
Figure 1: Dependency Diagram
The case study in this assessment is provided which is based on an Organic Pastures Clinic
which provides beauty treatments for the different age groups so in this particular assessment
there is a need to perform and show the normalization of the tables. The table is also provided
and the name of the table is Report_Patient that have the composite primary key and it is named
as Patient_id and Clinician_location. The two dependencies named as partial and transitive are
faced while performing the normalization and these are removed by converting them in different
normalization form (Park, et al., (2015)).
Partial Dependencies:
 Patient_id: Patient_name, Patient_Address
 Clinician_location: Clinician_name
Transitive dependency:
 Triage_Category: Departing_status
II.
Normalization is shown stepwise. So the first normalization form will check for the atomicity
hence the table will still contain the partial and transitivity. So on further normalization, we’ll
move to second normalization form.
1NF:

Figure 2: First form of normalization
2NF:
The conversion of the table in the second normal form will remove the partial dependency and
the partial dependency can be noticed in the table patient and the table clinician (Foster et al.,
(2016)).
 Report_Patient
(Patient_id,Clinician_location,Triage_Category,Departing_status,First_seen_by,Present
ing_problem)
Figure 3: Table Report_Patient
 PatientDetails (Patient_id, Patient_name, Patient_address)
Figure 4: Table PatientDetails
 ClinicianDetails (Clinician_location, Clinician_name)

Figure 5: ClinicianDetails Table
 The table Report_Patient have transitive dependency which is between the
Departing_status which is depending on Triage_category (Scotti, et al., (2016)).
3NF:
As the partial dependency is removed, now it is important to remove the transitive dependency as
well, for that it is necessary to perform third normal form so in order to perform this extra table is
created as Triage_category and it is assigned as a foreign key.
 PatientDetails (Patient_id, Patient_address, Patient_Name)
Figure 6: PatientDetails Table
 ClinicianDetails (Clinician_location, Clinician_name)
Figure 7: ClinicianDetails Table
 Triage (Triage_Category, Departing_Status)

Figure 8: Triage Table
 Report_Patient (Patient_id, Clinician_location, Triage_Category, Presenting_problem,
First_seen_by)
Figure 9: Report_Patient table
III.
ER diagram:

Figure 10: ER diagram

References
 Scotti, A., Hannum, M., Ponomarenko, M., Hogea, D., Sikarwar, A., Khullar, M., ... &
Deng, L. (2016). Comdb2 bloomberg's highly available relational database
system. Proceedings of the VLDB Endowment, 9(13), 1377-1388.
 Foster, E. C., & Godbole, S. (2016). The Relational Model. In Database Systems (pp.
37-72). Apress, Berkeley, CA.
 Park, J., Nguyen, T. K., Do Dang Thach, O. C. J., & Moon, C. J. (2015). Data Scalability
Design for Multi-Satellite Management based on ER Model. Appl. Math, 9(1L), 165-170.