RelationalDBDesign  Tweet

Database Analysis   «Prev  Next»
Lesson 3 Why we need Normalization
Objective Explain the purpose of normalization.

Objective of Database Normalization

Relational databases are built to store and access data efficiently and the design of a database has a huge impact on its performance.
The purpose of normalization is to maximize the efficiency of a database.

Consider a database where all the data is stored in a single table.
Columns of the Order Table
Columns of the Order Table
This table tracks information relating to
  1. a merchant’s orders, and
  2. contains all of a customer's information and
  3. all of the item's information in every record.
This type of design has several drawbacks:
  1. More than one entity is represented in the table. In this case, the table represents Customers, Orders, and OrderItems.
  2. The table contains redundant data. Every time a customer places an order, their contact information (Name, Address, and Phone) is needlessly repeated.
  3. Any query requires the RDBMS to search the entire table. To find every unique customer in the database, one would need to search every line. There is no location set aside to store data about Customers and only Customers.
  4. Updating information, such as customer names, requires the RDBMS to change multiple rows. If D. Loge moved from Washington to Seattle, the RDBMS would need to update every row containing his information.
  5. Deleting a customer’s only order also removes all contact information for that customer. If D. Loge cancelled the only two orders he placed, no record of him as a potential customer would remain in the database.

Database Normal Forms Explained

Tables that conform to the rules of normalization are said to be in one of several normal forms, depending on which rules the table adheres to. In this module and the next we will concentrate on the first, second, and third normal forms. There is a logical progression from one normal form to the next, so it is possible to resolve basic design flaws before tackling potentially more difficult problems. This means that a table in a normal form is automatically in all lower normal forms. That is, a table in second normal form is automatically in first normal form.
The next lesson describes first normal form and provides instructions for producing a table in first normal form.
normal forms: (Entered in the database under normal form.) A set of theoretical rules for the design of database tables.
Normalization Process - Quiz
Before you move on to the next lesson, click the quiz link below to reinforce your understanding of normalization.
Normalization Process - Quiz