- Describe the steps that you would use in order to convert database tables to the First Normal Form, the Second Normal Form, and the Third Normal Form
Normalization is an approach of decomposing tables with an aim of eliminating redundant data and ensuring that data dependencies make sense through logical arrangement and storage. Normalization of a database allows handling and update of the database. Data storage should happen once to avoid storage of data that can undergo calculation from existing data in the databases. It prevents data loss. During the normalization process, removal of redundancies is important. Following of data integrity rules is important for the satisfaction of all integrity constraints. There are rules followed in database normalization. The rules include the first normal form, second normal form, third normal form and BCNF (Sumathi & Esakkirajan, 2007). The normal forms of data indicate how much redundancy exists in the data. The normalization process involves the following steps:
- Specifying the key that brings the relationships
- Specifying the functional dependencies of the relationship
- Application of definition for each of the normal functions
- If the relations do not meet the definition of a normal form, the relationship is changed to form new relations until they meet the definition function.
- Testing of the new relations to ensure that they meet the definitions required by each of the normal forms.
- Provide one (1) example that is relevant to a college environment that illustrates reasons for converting database tables to the First, Second, and Third Normal Forms
First Normal Form
A table without normalization is difficult to handle and update it in the database without encountering insertion, data loss and deletion. A good example is a student table.
It is evident that the student name Gad appears twice in the table and the subject Mathematics appears twice. This is contrary to the First Normal Form. To make the above table to the First Normal Form breaking of the table in two forms is necessary.
New Student Table
Both the student table and the subject table possess the attributes of first normal form.
A table for normalization to the second Normal Form must meet all the requirements of the First Normal Form. Partial dependency of column on primary key should not exist which means that each of the column in the table that is not the primary key must show dependence upon the entire key for its existence.
- Explain typical situations when denormalizing a table is acceptable. Provide one (1) example of denormalizing a database table to justify your response.
Denomalization is important for enhancing performance. Denormalization is use in removal of joints and avoiding of queries. However, before denormalization, one must look for indicators that help in identification of tables that require denormalization (Ricardo, 2012). The indicators include large primary keys that are clumsy to query and those that consume large volume of space when carried as key columns. Other indicators include existence of repeated groups. There are critical issues involved in deciding to denormalize. They include planning how to keep the data in a synchronized form and refactoring the queries using the denormalized fields.
Key
Key
- Explain the significant manner in which business rules influence both database normalization and the decision to denormalize database tables.
Design and implementation of a database should satisfy the needs of the organization. Organizations require logical data modeling that is consistent and accurate. The database normalization should not allow for data losses because organizations know the importance of having accurate information (Prat & Adamski, 2001). All businesses have rules and all people that deal with the business rely on information given to make decisions. Databases systems should provide with decision makers with the correct type of information. It should also supply information to suppliers, customers and other stakeholders of the business
ReferencesTop of Form
Pratt, P., & Adamski, J. (2000). Concepts of database management (1st ed.). Cambridge, Mass.: Course Technology.
Ricardo, C. M. (2012). Databases illuminated. Sudbury, MA: Jones & Bartlett Learning.
Sumathi, S., & Esakkirajan, S. (2007). Fundamentals of relational database management systems. Berlin: Springer.
