Normalization of Database

Normalization is a process that removes redundancy, permits efficient updates of the data in the database, and prevents the accidental loss of important data. There are different levels of normalization. Each level reduces large tables into multiple smaller, more easily managed tables.

Description of Normalization

The first level, 1^st Normal Form, has no duplicated rows, with single values for each attribute in the column. For instance, all the customers in a customer table will have one and only one Customer ID number. Because there are no duplicated rows, data can be access through a search on specific keys, which are unique to the row of data. All the non-key attributes are dependent on the key, and all the records have the same number of fields. First Normal Form is abbreviated 1NF.

In the second level, 2nd Normal Form, data which is not dependent on the key is broken out into other smaller tables. The second Normal Form removes part key dependencies from the database by creating new tables to hold the information. Much of this will be in a one to many relationship. For instance, in a newspaper database, one newspaper has many subscribers. Second Normal Form is abbreviated 2NF.

Third Normal Form removes non-key dependencies, eliminating data which does in some way describe the key. For most databases, 3^rd Normal Form is sufficient. Using a zip code as an example, street addresses in a given area would be dependent on the zip code. Many cities have a Poplar Street. Without the zip code, it would be impossible for the U.S. Postal Service to deliver a letter addressed to 141 Poplar Street, Paris. It could be Paris, France, or Paris, Texas. There would be no way of knowing. Thus the zip code would be a key dependency and would remain with an address database in Third Normal Form. Third Normal Form is abbreviated 3NF.

In 4^th Normal Form, abbreviated 4NF, the table has no multi-valued dependencies. The table is not allowed to have two or more one to many or many to many relationships that are not directly related. The table must also satisfy all requirements for the 3NF in order to be in Fourth Normal Form. In this form, a table with fields called employee, team, and manager would be broken down into two tables. Example:

This table:

• Employee
• Team
• Manager

Would become these tables:

• Employee
• Team

and

• Employee
• Manager

Fifth Normal Form, 5NF, reconstructs the original table, while eliminating redundancies. By decomposing the Normal Forms back to a table that contains all the relevant data, it is possible to increase the speed of SQL queries run by users and programs. This allows significant time saving during update activity.

Benefits to Normalization

There are numerous benefits to database normalization, including saving space on servers and mainframes, increasing speed of applications which query the databases, and preventing data loss or corruption. Server and mainframe required by databases is decreased when redundant data is eliminated. For example, if a table had a million rows, and 10% of the data was redundant, normalization would decrease the number of rows to 900,000. Applications accessing this table would have 100,000 fewer records to read during processing, positively impacting processing time.

Data corruption is greatly reduced in normalized databases, since the number of places data must be inserted, deleted or updated are reduced, and are logically organized to prevent loss. Normalized databases are easier to change when new features are added during development, or later during the maintenance phase of database design. At the bottom line, normalization saves money by saving space on servers and mainframes, by allowing applications to run faster and more efficiently, and by preventing data loss.