The Impact of Business Rules on the Normalization and the Denormalization Process

Database normalization is a database design technique that involves organization of data with the aim of reducing redundancy as well as the undesirable traits such as deletion, insertion and update anomalies (Fong, 2015). In fact, it comprises of a series of steps that put the data in a tabular form while removing any instance of duplicated data (Fong, 2015). Consequently, it comprises of three steps namely First, second and third normal forms.

Normalization Process

1st normal form

Given unnormalized form, the first normal form is reached by removing repeating groups and form a new entity (Coronel & Morris, 2016). For example, an unnormalized form may comprise of CourseCode, CourseName, TutorID, TutorName, StudentNO, StudentName, DateOfBirth and Gender. Since a student is likely to register more than one course, the table is likely to have repeated data. To turn the table into the first normal form, repeating groups are removed and placed into two different tables. In which case, the CourseCode, CourseName, TutorID, TutorName are separated from StudentNo, StudentName, DateOfBirth, and Gender. Also, it is important to ensure that the original key (CourseCode) is added to each of the groups as shown in the tables below. Similarly, when separating repeated fields, it is important to assign the same primary key as the original table

Course Table

CourseCode

CourseName

TutorID

TutorName

Student Table

CourseCode

StudentNo

StudentName

DateOfBirth

Gender

2nd normal form

The second normal form is arrived at by removing partial keys and the available dependencies to form a new table (Coronel & Morris, 2016). Hence, all the non-key attributes should be fully functionally dependent on the primary key. In doing so, students data will only be stored in the database once, irrespective of the number of course registered. In the registration example, the CourseCode in table Student table is fully dependent on StudentNo but not StudentName, DateOfBirth, and Gender. Thus, the second normal form will involve separating the student table in order to remove partial keys. The following tables show the tables in 2nd

normal form;

Course Table

CourseCode

CourseName

TutorID

TutorName

Class Table

CourseCode

StudentNo

Student Table

StudentNo

StudentName

DateOfBirth

Gender

3rd normal form

            The 3rd normal form is achieved by removing inter-key dependencies (Fong, 2015). Accordingly, it involves the removing attributes that are not dependent on the key in a given table (Coronel & Morris, 2016). For instance, in the Course Table, the attributes TutorID and TutorName because they are not dependent on the primary key. Hence the tables in third normal form will comprise of;

Course Table

CourseCode

CourseName

TutorID

TutorName

Tutor Table

TutorID

TutorName

Class Table

CourseCode

StudentNo

Student Table

StudentNo

StudentName

DateOfBirth

Gender

Denormalization

Denormalization is the process of enhancing the read performance of a database by either grouping data of adding redundancy (Vajk et al., 2013). Comparatively, it is a process of addressing scalability and accessibility in relational database software (Vajk et al., 2013). The following two tables satisfy second normal form;

BookNo

LibraryNo

Quantity

Library

LibraryAddress

In the above tables, it is evident that information about books, libraries and library address is found in two separate tables. Therefore, Denormalization can be achieved by combining the two tables as follows;

BookNo

LibraryNo

LibraryAddress

Quantity

Impact of Business Rules to Normalization and Denormalization

Before undertaking database design, it is always crucial to undertake requirement analysis. The data collected during this process helps in defining the rules and procedures to incorporate in the design process (Coronel & Morris, 2016). Thus, business rules impact the normalization steps since it involves definition and use of data items (Coronel & Morris, 2016). Consequently, business rules may define a lot of data items that make access and retrieval of normalized data difficult. Hence, the definition of business rules is also likely to influence the decision made in cases when denormalization is needed.  Comparatively, business rules will determine the need for normalization and later bring out the need for denormalizing.

References

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning.

Fong, J. S. P. (2015). Data Normalization. In Information Systems Reengineering, Integration and Normalization (pp. 343-376). Springer, Cham.

Vajk, T., Feher, P., Fekete, K., & Charaf, H. (2013, December). Denormalizing data into schema-free databases. In Cognitive Infocommunications (CogInfoCom), 2013 IEEE 4th International Conference on (pp. 747-752). IEEE.