Let’s try understanding normalization
How many normal forms are there?
There are seven normal forms.
They are
- First Normal Form
- Second Normal Form
- Third Normal Form
- Boyce-Codd Normal Form
- Fourth Normal Form
- Fifth Normal Form
- Sixth or Domain-key Normal form
Why do we need to do normalization?
To eliminate redundancy of data i.e. having same information stored at multiple places, which eventually be difficult to maintain and will also increase the size of our database.
With normalization we will have tables with fewer columns which will make data retrieval and insert, update and delete operations more efficient.
What do we mean when we say a table is not in normalized form?
Let’s take an example to understand this,
Say I want to create a database which stores my friends name and their top three favorite artists.
This database would be quite a simple so initially I’ll be having only one table in it say friends table. Here FID is the primary key.
| FID | FNAME | FavoriteArtist |
| 1 | Srihari | Akon, The Corrs, Robbie Williams. |
| 2 | Arvind | Enigma, Chicane, Shania Twain |
This table is not in normal form why?
FavoriteArtist column is not atomic or doesn’t have scalar value i.e. it has having more that one value.
Let’s modify this table
| FID | FNAME | FavoriteArtist1 | FavoriteArtist2 | FavoriteArtist3 |
| 1 | Srihari | Akon. | The Corrs | Robbie Williams. |
| 2 | Arvind | Enigma | Chicane | Shania Twain |
This table is also not in normal form why?
We have now changed our table and now each column has only one value!! (So what’s left?)
Because here we are having multiple columns with same kind of value.
I.e. repeating group of data or repeating columns.
So what we need to do to make it normal or at least bring it in First Normal Form?
- We’ll first break our single table into two.
- Each table should have information about only one entity so it would be nice if we store our friend’s information in one table and his favorite artists’ information in another
(For simplicity we are working with few columns but in real world scenario there could be column like friend’s phone no, email , address and favorites artists albums, awards received by them, country etc. So in that case having two different tables would make complete sense)
| FID | FNAME |
| 1 | Srihari |
| 2 | Arvind |
| FID | Favorite Artist |
| 1 | Akon. |
| 1 | The Corrs |
| 1 | Robbie Williams |
| 2 | Enigma |
| 2 | Chicane |
| 2 | Shania Twain |
FID foreign key in FavoriteArtist table which refers to FID in our Friends Table.
Now we can say that our table is in first normal form.
Remember For First Normal Form
Column values should be atomic, scalar or should be holding single value
No repetition of information or values in multiple columns.
So what does Second Normal Form means?
For second normal form our database should already be in first normal form and every non-key column must depend on entire primary key.
Here we can say that our Friend database was already in second normal form l.
Why?
Because we don’t have composite primary key in our friends and favorite artists table.
Composite primary keys are- primary keys made up of more than one column. But there is no such thing in our database.
But still let’s try to understand second normal form with another example
This is our new table
| Gadgets | Supplier | Cost | Supplier Address |
| Headphone | Abaci | 123$ | New York |
| Mp3 Player | Sagas | 250$ | California |
| Headphone | Mayas | 100$ | London |
In about table ITEM+SUPPLIER together form a composite primary key.
Let’s check for dependency
If I know gadget can I know the cost?
No same gadget is provided my different supplier at different rate.
If I know supplier can I know about the cost?
No because same supplier can provide me with different gadgets.
If I know both gadget and supplier can I know cost?
Yes than we can.
So cost is fully dependent (functionally dependent) on our composite primary key (Gadgets+Supplier)
Let’s start with another non-key column Supplier Address.
If I know gadget will I come to know about supplier address?
Obviously no.
If I know who the supplier is can I have it address?
Yes.
So here supplier is not completely dependent on (partial dependent) on our composite primary key (Gadgets+Supplier).
This table is surely not in Second Normal Form.
So what do we need to do to bring it in second normal form?
Here again we’ll break the table in two.
| Gadgets | Supplier | Cost |
| Headphone | Abaci | 123$ |
| Mp3 Player | Sagas | 250$ |
| Headphone | Mayas | 100$ |
| Supplier | Supplier Address |
| Abaci | New York |
| Sagas | California |
| Mayas | London |
We now how to normalize till second normal form.
But let’s take a break over here and learn some definitions and terms.
Composite Key: -Composite key is a primary key composed of multiple columns.
Functional Dependency – When value of one column is dependent on another column.
So that if value of one column changes the value of other column changes as well.
e.g. Supplier Address is functionally dependent on supplier name. If supplier’s name is changed in a record we need to change the supplier address as well.
S.Supplier–àS.SupplierAddress
“In our s table supplier address column is functionally dependent on the supplier column”
Partial Functional Dependency – A non-key column is dependent on some, but not all the columns in a composite primary key.
In our above example Supplier Address was partially dependent on our composite key columns (Gadgets+Supplier).
Transitive Dependency– A transitive dependency is a type of functional dependency in which the value in a non-key column is determined by the value in another non-key column.
With these definitions in mind let’s move to Third Normal Form.
For a table in third normal form
- It should already be in Second Normal Form.
- There should be no transitive dependency, i.e. we shouldn’t have any non-key column depending on any other non-key column.
Again we need to make sure that the non-key columns depend upon the primary key and not on any other non-key column.
| Album | Artist | No. of tracks | Country |
| Come on over | Shania Twain | 11 | Canada |
| History | Michael Jackson | 15 | USA |
| Up | Shania Twain | 11 | Canada |
| MCMXC A.D. | Enigma | 8 | Spain |
| The cross of changes | Enigma | 10 | Spain |
Although the above table looks fine but still there is something in it because of which we will normalize it further.
Album is the primary key of the above table.
Artist and No. of tracks are functionally dependent on the Album(primary key).
But can we say the same of Country as well?
In the above table Country value is getting repeated because of artist.
So in our above table Country column is depended on Artist column which is a non-key column.
So we will move that information in another table and could save table from redundancy i.e. repeating values of Country column.
| Album | Artist | No. of tracks |
| Come on over | Shania Twain | 11 |
| History | Michael Jackson | 15 |
| Up | Shania Twain | 11 |
| MCMXC A.D. | Enigma | 8 |
| The cross of changes | Enigma | 10 |
| Artist | Country |
| Shania Twain | Canada |
| Michael Jackson | USA |
| Enigma | Spain |
Normally this is considered enough and we don’t really go on applying the other normal forms.
Most of real-world application has databases which are in third normal forms.
Bye
References:-
Head First SQL,
Murach Sql for Sql server,
http://www.stuart.edu/courses/im510/database/2NF.htm
Discover more from Nishant Rana's Weblog
Subscribe to get the latest posts sent to your email.
Share It -
Nishant Rana's Weblog 
excellent
LikeLike
kool dude,it’s very good
LikeLike
Great way of understanding….with nice examples
LikeLike
great.
LikeLike
thanks
LikeLike
gud post…
please refer the following link for more info in spoon feed.
http://www.phlonx.com/resources/nf3/
LikeLike
its really good!!
thanks…
LikeLike
awesome explanation, lt is very clear and understandable.
Thanks you so much
LikeLike
yeh! it was really helpfull to understand certain topic.
thanks
LikeLike
great effort
really very nice
LikeLike
Great it is useful for even a novice learner.i was so confused with normalization & u hav solved all the problems.Thank u so much .God bless u.
LikeLike
Thanks for appreciation !
LikeLike
It is very good to understand normalization, very clear.
LikeLike
there is confusion between 2NFand 3rd NF
LikeLike
thank you very much this had helped me a lot. Very well explained!
LikeLike
Found this helpful 4 my training at Wipro
LikeLike
Hi Rohit,
I am glad to know that!
Regards,
Nishant Rana
LikeLike
Very easy to understanding,thank u for giving information.
LikeLike
this is the best data i get about normalization ,thanks 4 that and continue doing it
LikeLike
Excellent explanation Nishant.the way u explained is very much usefull for the beginners in database.It will be very much helpfull if u could explain the rest of the normalization forms in the same manner.
LikeLike
Finally someone who I can understand about the normalization… thank you! Now I can search about the other normalizations.
LikeLike
one of the best materials available for the normalization. I am gonna bookmark your blog for more articles. Great work !
LikeLike
very clear to understand…
good examples are added.. and maked it more clear
LikeLike
hi… yeah the information is really great n u made me to recall the stuff….
the information is clear n easy for self preparation…..
need to be include more examples in complex concepts……
LikeLike
Really very nice … easy way to understand Normal form
LikeLike
Great work man..simple and powerful 🙂 🙂
thanks 🙂 🙂
LikeLike
I t is really nice ……..this is the easy way to understand NF’s
Thanks.
LikeLike
thanx i got it bcoz of u .So great works .thank you for sharing such a good thingd
LikeLike
thank u very much… its very useful for me…. simple and best
LikeLike
thanks alot …this is quite very useful for me
LikeLike
It is very important to me and is very useful also………
So thank you very much
LikeLike
It’s too good!very well explained.
LikeLike
Hi Nishant,
Just wondering if have any advise of the best approach to normalizing in Dynamics CRM? e.g. display attributes from different entities on a form as opposed to maintaining the same data in multiple entities.
Thanks in advance!
LikeLike
It’s very useful material to understand concepts. thank you
LikeLike
It is really a very excellent post………with such an clear illustration of examples and should be really appreciated………….Thanks! I’m clear with normalization now…But if it was added with 4NF ,it would be a complete chapter of normalization……………………………..
LikeLike
in simple language ,very nice
LikeLike
hey i have got one doubt. if 3rd normal form is standard one why they came up with 4th and 5th normal form. and what is the current status of normal form.
LikeLike
I found difficulty to understand this Normal forms in Text books. Your example makes me to get clear idea on those normal forms. Thanks
LikeLike
awesome examples very easy to understand, what about BCNF i desperately need that…….
LikeLike
Really i dont know what is normalisation.But after reading this i’m perfect about 1NF,2NF,3NF.ThankYou.
LikeLike
upto 3rd nf it was ok…………
but why didn’t u other nf’s
LikeLike
i liked it very much….very good explanation….thank you Rana
LikeLike
I found difficult to understand this Normal forms in many text books . Because of its confusing language . Your example is quite enough to makes me to get clear idea on those normal forms. Thanks a lot
LikeLike
its nice and well structured V GOOD
LikeLike
Really EXCELLENT job very gud approach
this approach was really like sppon feeding
Thanq very much gud luck
bye
LikeLike
really really its very informative and easily understandable…. very nice…
LikeLike
Great example and simplest way of learning the normalisation..Thanks Nishant
LikeLike
its very useful info to us and very understandable.
explain remaining NF too……..
LikeLike
Great example and simplest way of learning the normalisation..Thanks Nishant.
Can you please remaining normal forms with this type of exapmples.
LikeLike
Information provide by you is very useful ,but can u tell me in last example 3 normal form how can i get the value of country???????????
LikeLike