Normalization is a technique where we break data in to logical pieces for a better design. The main purpose of normalization is to reduce redundant and duplicate data.
Redundant means unnecessary and repetitive means duplicate data. Please note redundant does not mean repetitive but repetitive can be redundant.
There are three normalization techniques which are used heavily in relational world:-
First normal form says break your data in to logical pieces.
- First normal form.
- Second normal form.
- Third normal form.
For instance you can see the below table which has student names; if you ever want to query student names having “Koirala” and not “Harisingh”, you can imagine what kind of a query you will end up with.
So the better approach would be to break this field into further logical pieces so that we can write clean and optimal queries. Second normal form says “All keys should depend on the full primary key and not partially”.
In simple words data should depend fully on primary keys. For instance in the above table we can see the primary key is created on roll number and standard. Now watch the syllabus field closely. The syllabus field is associated with a standard and not with a student directly (roll number).
The syllabus is associated with the standard in which the student is studying and not directly with the student. So if tomorrow we want to update the syllabus we have to update it for each student, which is painstaking and not logical. It makes more sense to move these fields out and associate them with the Standard table.
You can see how we have moved the syllabus field and attached it to the Standards table.This rule is nothing but the 2nd normal form.
3rd normal form: “No column should depend on other non-primary key columns”.
In the above figure you can see how the average field is dependent on the marks and subject. This is also one form of redundancy. So we can eliminate such calculative fields. This is third normal form.
You can also see the 6 most asked SQL Server interview question with answers videos from here :-
These videos are created by www.questpond.com team.