Normalization
If you have been working with Databases, chances are you have
heard the term Normalization. In this article we will look at the concept of
Normalization and go thru the most common normal forms.
What is
Normalization?
Simply, Normalization Is the process of taking all the data
that will be stored in a particular DB and separating it into tables.
There are two goals of Normalization.
1. Eliminating
the redundant data
2. Ensuring
the Data integrity.
Now that we understood the goals of Normalization, we can examine
the normal forms. Each normal form is characterized by rules about how data should
be organized. We have different normal forms like First Normal Form, Second
Normal Form, Third Normal Form and so on…Each Normal Forms builds on previous
set of rules. For example data that is in third normal form is automatically in
first and second normal forms.
The easiest way to understand the normalization is to work
through an example. Let’s consider the below data.
Defining
First Normal Form
Below are the rules for First Normal Form
1 . Each field
in table must contain only a single type of data.
2 . Each piece of
data must be stored in one place.
First Normal Form commonly violated in two ways
1 . Related
data put into single field. For example in above table supplier and supplier
city put into single column.
2 . Repeating
fields.
Data in First Normal Form
There are still issues with above data, what happens if you
start buying DogSplfood from different supplier? . You need to update two
records in above table what if you miss any one of the records. It will leave
database in inconsistent state.
Choosing a
Good Primary Key:
Following factors need to considered for defining the Primary
key.
1. Stability
2. Minimality
3. Familiarity
Second Normal
Form
To achieve second normal form
1 . Tables
should be in First Normal Form
2 . Each
Contain the data about one and only on Entity..
In our case we can achieve the second normal form by
splitting it into two tables.
Animal table in second normal form
Feed table in second normal form
Foreign
Keys and Relations
By identifying a foreign key and it’s corresponding the
primary , we can tell the database server about referential integrity to be
maintained between the two tables.
The Relationship between the primary key and foreign key can
take one of forms
1 . One-to-one
2 . One-to-many
3 . Many-to-Many.
Third
Normal Form
To achieve Third Normal Form
1 . Tables
should be in the second normal form
2 . Non Key
values must directly depend on Primary Key
Animal Table in third Normal Form
Feed Table in third Normal Form
Supplier City in third Normal Form
We will stop here as most of the DB’s
are normalized up to third normal form.
