What is Database Normalization?

An article by Jorge Reis    Comments Off on What is Database Normalization?

In terms of relational database design, database normalization is the process in which a database structure is free from any uncertainties like UPDATE, INSERTION and DELETION incidences.  When this happens, the integrity of the data is at stake.  That’s why, normalization was conceptualized.

To be able to normalize a certain database, it should be designed in the third normal form.  In the third normal form, all data will be secured, and only certain areas of the table are subjected to any change.  Generally, a standard database design should be fully normalized, and alterations must only be done if it is necessary.

Main Objectives of Database Normalization

The first normal form, which is developed by E.F. Codd, it has a primary objective of allowing users to perform queries and manipulation of significant information.  The first normal form must be under the universal sub-language, which is the SQL statement.  For example, users working on certain banking/credit transactions should practically analyze the data input for databases.  In cases where data are not under the first normal form, problems may arise.

Other objectives of database normalization include easy access to undesirable data that undergone unauthorized insertion, update or deletion.  Database normalization also allows less time for restructuring since new data are introduced to specific tables.  As a result, the lifespan of programs or applications are increased.  With the use of normalization, the development of relational model is in conformity with the users.  Moreover, normalization can also be effective for the collection of query statistics for further manipulation and storage.

Problems Encountered in Database Normalization

When a user modifies or normalizes its database incorrectly, the user may experience incoherence as well as inconsistency of data.  Because of this, anomalies may arise.  Here are the three major problems encountered in database normalization.

The first problem is the update anomaly.  A certain table requires ample information for this table to become effective.  Information within the table is expressed in multiple records; therefore, information may require updates.  Because of these updates, inconsistencies may occur.  For instance, you are maintaining a directory of your employees.  Your employee table will definitely include the employee’s name, identification number and address.  In cases when an employee changes his address, you may need to update your database.  If your update is not successfully done, then the table will have an inconsistent state.  Thus, the table will share an incorrect data when you ask the employee’s address.

The second problem is the insertion anomaly.  There are situations where data cannot be recorded.  In these circumstances, users should add significant data to prevent inconsistency.  For example, you will be developing an “Employee and Their Position” table.  You will need the employee’s name, ID, address, hiring data as well as position in the company.  The table will only present the files of the current employees, and thus, it cannot hold new data input.

The third and last problem is the deletion anomaly.  There are situations where you need to delete irrelevant facts from your table.  Because of normalization, you cannot delete these facts completely.