What Does Normalization of a Database Mean?

Charles Rondeau

Database Normalization

by Linda Pogue

Unless you are a student in a computer course or a database developer/administrator, you probably don't care much about database normalization.

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

1st 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.

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.

3rd Normal Form removes non-key dependencies, eliminating data which does in some way describe the key. For most databases, 3rd 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 4th 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.

For example, this table:

• Employee
• Team
• Manager

Would become these tables:

• Employee
• Team

and

• Employee
• Manager

5th 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. This saves money by saving space on servers and mainframes, by allowing applications to run faster and more efficiently, and by preventing data loss.

Learn More About Database Design

Database design is an interesting study.

Many colleges and universities have classes on database design, and require the course as part of their degree programs for Computer Science degrees.

If the subject is interesting to you, or if you are taking a course on database design, this book will be interesting.

The author, Michael J. Hernandez, teaches you how to determine what to add to your database design to make it work the way it needs to.


Will you be taking database design as part of your major required courses?

 

Please Comment Below