Data Normalization

Data Normalization is a process of refining a database model so that its structures conform to certain standards for the design of tables. The tables and fields you define make up the structure of the database. Good data normalization results in data tables that make sense in a fundamental way. Well-normalized tables are easy to understand when looked at. It is easy to see the kind of data stored and the types of updates needed to be performed. It should be easy to create data entry routines and simple reports directly from normalized tables. In fact, the rule of thumb is: If it's hard to work with a data table, more normalization is probably needed.

Normalization ensures that your database design is the most effective, or in other words, the least likely to change. A normalized database also reduces the chances of repetitive data occurring.

There are five rules involved in normalization. Each of these rules is referred to as a normal form. When you refine a data model to comply with one of these rules, the model is said to be normalized to that rule's degree. For instance, once the design is refined to comply with the first normal form, the design is normalized to the first degree.

Following are some of the advantages of normalization:

• Applying normal form rules to your application sometimes reveals new requirements or rules that have been overlooked.
• Normalization leads to accurate and complete reflection of business requirements and rules.
• Normalization minimizes redundancy by means of referential integrity.
• Normalization creates a logical scheme that minimizes the potential for unintentional results.

Normalizations Rules

First Normal Form (1NF) (Eliminate Repeating Groups)

• All the columns in a table must be single-valued and no repeating groups are allowed. In other words, each column must contain one and only one piece of information, and no two rows can contain the same record of information.
• Example of Single Valued: In the Customer table, a column cannot store both the First Name and Phone Number of a customer. Instead, one column stores the first name, and the other column stores the phone number of a customer.

Second Normal Form (2NF)(Eliminate Redundant Data)

• All the non-key attributes in a table must depend only on the primary key. In other words, no attribute may depend on another attribute in a table, if the latter is not a primary key.

pid	empname	deptid	deptname	deptlocation
100	John Smith	10	Accounts	Florida
101	Adam Scott	20	Security	Virginia
102	John Doe	10	Accounts	Florida
103	Mary Jones	20	Security	Virginia
104	Elizabeth London	30	Technical	Texas

Third Normal Form (3NF) (Eliminate Keys Not Dependent on Primary Key)

• Transitive dependencies are not allowed. In other words, any column which is not a key in the table should not depend on another column not a key in the table.

orderid	orderdate	customerid	productid	product_price
100	20-JUN-99	200	12	20.00
101	30-AUG-99	201	13	12.00
102	25-JAN-98	201	12	20.00
103	07-JUL-99	202	14	15.00
104	15-MAR-00	203	15	14.00
105	24-APR-00	204	16	23.00
106	12-JUN-00	205	17	34.00

Fourth Normal Form (4NF)

• Isolate Independent Multiple Relationships

Fifth Normal Form (5NF)

• Isolate Semantically Related Multiple Relationships