Database normalization is a process in which data attributes are organized within a data model to increase cohesion. Essentially, database normalization is done to reduce data redundancy. It becomes difficult to store objects in a relational database that exhibits the same information in more than one place. Data normalization is performed in three forms namely; first normal form (1NF), second normal form (2NF) and third normal form (3NF). The forms describe how entity types are characterized in increasing level of normalization. Data normalization as previously stated aims to eliminate data redundancy and achieving sensible data dependencies. For instance data in a normalized table will not be stored in more than one table. Each table contains a set of data distinct from the other tables. Also, sensible relationships ought to exist between data stored in a particular table. The data could contain the first name characters alone or it could be product model alone (Chilton,2006).
Normalization processes has its cons. First, it is difficult and expensive to built normalized database. Second, it requires skills to provide functional views and access methods that are comprehensible and streamlined for customer applications. Third, normalized databases require skills to maintain conceptually current, well-indexed and normalized data.
Data redundancy is an issue in databases that allows unnecessary data duplication. A change or modification of one field requires that the same changes be effected in all other fields containing the information. Redundant data wasted valuable storage facility, time and creates troubled database that is difficult to manage.
The problem extends to data analysis and decision making. Redundant data will contribute to errors in analyzing data for decision purposes. For instance in flat file databases names of non-existent employees could be entered and eligible for remuneration. This wastes businesses and companies resources. In analysis and data reporting, incorrect figures mean that forecasting processes are inaccurate.
Normalizing data as shown in the figure below seeks to improve data integrity and quicken the rate of retrieving storing and updating. The customers, order items and menu items are easily worked as a result improves efficiency and reliability (Allan,2005).
In moving through 1NF, 2NF till 3NF, rules are followed to arrive at a normalized database. Before normalization attributes such as vendor, order and menu repeats a number of times. Thus it is hard for the system to process such repeated names. Introduction of the order entry allows us to have as many, or as few, order items associated with an order thus according the schema flexibility. Of importance is the fact that order has kept customerID as the primary key. Transitioning to the 2NF saw the introduction of the orderItem. OrderItem is in 1NF and each attribute that is not part of the primary key is fully dependent on the primary key. Thus the condition is satisfied.
The 3NF transition requires that the 2NF requirements have been met plus all entity types must depend on all portions of the primary key. Thus introducing vendors to the 3NF serves to fully describe the vendors of the products and their identification details such as phone numbers and names.
In order to normalize data from 1st, 2nd and 3th normal forms, there are rules followed established by database community. As Kroenke (2006) noted in the first normal form the following rules apply in order to avoid storing data in multiple fields
- Eliminate repeating groups in individual table
- A separate table for each set of related data
- Each set of related data has an identifying primary key
In the second normal form, records should only depend upon the table primary key. Thus
- A separate table is created for a set of values that apply to multiple records
- Tables created are related with a foreign key
Finally, in the third normal form, record files should include the record keys. Thus;
- Eliminate field that are not dependent on the primary key
References
Allan, R. G. (2005). The entity-relationship diagram as a query tool. Business Intelligence
Chilton, Michael A. (2006). Data modeling using entity relationship diagrams: A step-wise
method. Journal of Information Systems Education, 17(4), 385-394.
Kroenke, D. M., & Gray, C. D. (2006). Toward a next generation modeling facility: Neither the
entity-relationship model nor UML meet the need. Journal of Information Systems
Education, 17(1), 29-37.
