- what is normalization and types of normalization 1NF 2NF 3NF with examples SQL simple definition of normalization
Normalization is the techinque of designing the database with the least redundancy and duplicacy of data.
- Types of Normalization:
- First Normal Form
- Second Normal Form
- Third Normal Form
- BCNF - Boyce Code Normal Form
- Fifth Normal Form
- Sixth Normal Form : Impossible to achieve this level of normalization
- First Normal Form
- Eliminate repeating groups in individual tables.
- Create a separate table for each set of related data.
- Identify each set of related data with a primary key.
- Example :
Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.
Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
- Second Normal Form
- Create separate tables for sets of values that apply to multiple records.
- Relate these tables with a foreign key.
- Example :
Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.
The following two tables demonstrate second normal form:
Students :
Registration:
- Third Normal Form
- Eliminate fields that do not depend on the key.
- Example :
In the last example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:
Students :
Faculty :
