Home > Free Essays > Tech & Engineering > Data > Healthcare Database Normalization
3 min
Cite This

Healthcare Database Normalization Essay

StarStarStarStarStar

Procedure of Normalization

Normalization involves the rearrangement of columns, rows, and tables’ relations in a relational database to minimize redundancy of data stored in them. The process of rearrangement entails three consecutive processes, namely, first normal form, second normal form, and third normal form. In describing normalization, this essay will use the following unnormalized table.

Table 1: Unnormalized Table.

Student_ID Student_Name Class Lecturer_ID Lecturer_Name Department
S101 James Martin CL01, CL02 L51 Mike Thomas Nursing
S102 Lucas Mark CL03 L52 Alex Day Biochemistry
S103 Jane Davis CL04, CL05, CL06 L53 Campino Adams Chemistry

The First Normal Form

The role of the first normal form is to remove redundant data and eliminate composite attributes (Chavan, & Meshram, 2012). The conversion of the above table to the first normal form entails the removal of composite attributes of a class to flatten the table so that each attribute has a single value.

Table 2: First Normal Form.

Student_ID Student_Name Class Lecturer_ID Lecturer_Name Department
S101 James Martin CL01 L51 Mike Thomas Nursing
S101 James Martin CL02 L51 Mike Thomas Nursing
S102 Lucas Mark CL03 L52 Alex Day Biochemistry
S103 Jane Davis CL04 L53 Campino Adams Chemistry
S103 Jane Davis CL05 L53 Campino Adams Chemistry
S103 Jane Davis CL06 L53 Campino Adams Chemistry

The Second Normal Form

According to Bahmani, Naghibzadeh, and Bhamani (2008), the conversion of a table in the first normal form to the second normal form entails the identification of primary keys and removal of partial dependencies. Since the table in the first normal form has composite primary keys (Student_ID and Lecturer_ID), which cause partial dependencies of students’ data and lecturers’ data, their removal will create a table in the second normal form.

Table 3: Students’ Data.

Student_ID Student_Name Class
S101 James Martin CL01
S101 James Martin CL02
S102 Lucas Mark CL03
S103 Jane Davis CL04
S103 Jane Davis CL05
S103 Jane Davis CL06

Table 4: Lecturers’ Data.

Lecturer_ID Lecturer_Name Department
L51 James Martin Nursing
L51 James Martin Nursing
L52 Alex Day Biochemistry
L53 Campino Adams Chemistry
L53 Campino Adams Chemistry
L53 Campino Adams Chemistry

The Third Normal Form

The conversion of the table in the second normal form to the third normal form entails the removal of transitive variables and ensuring that primary keys and foreign keys have referential integrity (Bahmani et al., 2008). The removal of transitive dependencies eliminates redundancies resulting in the generation of tables in the third normal form as shown below.

Table 5: Students’ Data.

Student_ID Student_Name Lecturer ID
S101 James Martin L51
S102 Lucas Mark L53
S103 Jane Davis L53

Table 6: Lecturers’ Data.

Lecturer_ID Lecturer_Name Department
L51 Mike Thomas Nursing
L52 Alex Day Biochemistry
L53 Campino Adams Chemistry

Table 7: Class Data.

Student_ID Class
S101 CL01
S101 CL02
S102 CL03
S103 CL04
S103 CL05
S103 CL06

Denormalization

In typical situations, denormalization of tables is acceptable when maintenance of history is essential, improvement of performance is necessary, and quick reporting is obligatory (Pinto, 2009). To maintain historical records amidst changing data attributes, denormalization of tables by creating an additional table to store historical records is acceptable. To improve performance, the creation of tables for frequently queried data involves denormalization aimed at improving the performance of queries. When quick reporting is obligatory, the denormalization of the target tables hastens the generation of reports.

An example of denormalization to maintain historical records

New Customer ID Products Purchases
New Customer ID Products Purchases

Denormalization

New Customer ID Old Customer ID Products Purchases

Impact of Business Rules on Normalization and Denormalization

Business rules have a significant impact on the design of databases because they determine the nature and the number of tables generated. Business rules that require coordination and integration between departments require the normalization of tables while business rules that favor independence of departments require denormalization of tables. Moreover, as business rules influence the nature of activities and operations in an organization, they determine normalization and denormalization. Extensive operations and activities require normalization to hasten their management while limited operations and activities require denormalization.

References

Bahmani, A., Naghibzadeh, M., & Bhamani, B. (2008). Automatic database normalization and primary key generation. Canadian Conference on Electrical and Computer Engineering, 1(1), 1-16.

Chavan, S., & Meshram, B. (2012). Study of Various Normal forms and Functional Dependency. International Journal of Emerging Technology and Advanced Engineering, 2(7), 394-399.

Pinto, Y. (2009). A Framework for Systematic Database Denormalization. Global Journal of Computer Science and Technology, 9(4), 44-52.

This essay on Healthcare Database Normalization was written and submitted by your fellow student. You are free to use it for research and reference purposes in order to write your own paper; however, you must cite it accordingly.
Removal Request
If you are the copyright owner of this paper and no longer wish to have your work published on IvyPanda.
Request the removal

Need a custom Essay sample written from scratch by
professional specifically for you?

Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar
Writer online avatar

certified writers online

Cite This paper

Select a referencing style:

Reference

IvyPanda. (2021, April 6). Healthcare Database Normalization. Retrieved from https://ivypanda.com/essays/healthcare-database-normalization/

Work Cited

"Healthcare Database Normalization." IvyPanda, 6 Apr. 2021, ivypanda.com/essays/healthcare-database-normalization/.

1. IvyPanda. "Healthcare Database Normalization." April 6, 2021. https://ivypanda.com/essays/healthcare-database-normalization/.


Bibliography


IvyPanda. "Healthcare Database Normalization." April 6, 2021. https://ivypanda.com/essays/healthcare-database-normalization/.

References

IvyPanda. 2021. "Healthcare Database Normalization." April 6, 2021. https://ivypanda.com/essays/healthcare-database-normalization/.

References

IvyPanda. (2021) 'Healthcare Database Normalization'. 6 April.

More related papers
Psst... Stuck with your
assignment? 😱
Hellen
Online
Psst... Stuck with your assignment? 😱
Do you need an essay to be done?
What type of assignment 📝 do you need?
How many pages (words) do you need? Let's see if we can help you!