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.
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.
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.
Table 4: Lecturers’ Data.
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.
Table 6: Lecturers’ Data.
Table 7: Class Data.
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.
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.