Normalization is a Systematic Approach, which is used to design a Relational Database table up to higher normal form. The basic goal of normalization is to keep each attribute in only one place to eliminate redundancy and the inconsistencies that can result from redundancy. The process requires a deep understanding of each attribute and each attribute’s relationship to its primary key.
Deliverable includes No-Data-Redundancy and No-Inconsistency.
1NF – 1st Normal Form
- Add Primary Key and ensure that every attribute and depends on that Primary Key. Each row as a whole should be Unique. Identify each set of related data with a Primary Key.
- Eliminate Repeating Groups in Individual Tables.
- Create Separate Table for each set of Related Data.
- Make sure that each attribute is Atomic (not Multi-Valued). Values Stored in a Column should be of the Same Domain. Multi-Valued can be divided like instead of Full_Name, it can be First_Name, Middle_Name and Last_Name.
2NF – 2nd Normal Form
- Be in 1NF
- Create Foreign Key.
- Create separate tables for sets of values that apply to multiple records.
- Relate these tables with a foreign key.
- Single Column Primary Key that does not functionally dependent on any subset of candidate key relation
- It should not have Partial Dependency.
3NF – 3rd Normal Form
- Be in 2NF
- To move our 2NF table into 3NF, we again need to divide our table.
- Eliminate Data not dependent on Primary Key
- It doesn’t have Transitive Dependency.
Example: We have again divided our tables and created a new table which stores Salutations. There are no Transitive Functional Dependencies, and hence our table is in 3NF.
Transitive Dependency Example: Changing the non-key column Full Name may change Salutation.
Name: William Salutation: Mr.
Name: Lina Salutation: Ms.
Exception: Microsoft Says: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible inter field dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.
