Understanding Normalizations in Database(RDBMS)


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?

  1. We’ll first break our single table into two.
  2. 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.

Unknown's avatar

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

152 thoughts on “Understanding Normalizations in Database(RDBMS)”

    2. Yes I got this. This flow of information is very best to understand normalization.
      Information is so nice and wel published. Thank a lot for giving such information in systmatic maner.

      Like

      Reply

  3. hi,
    Information provided by u is very useful and also easy to understand.Also the examples are very much illustrative.

    Thnak you,
    mahi

    Like

    Reply

  7. Thank you for this explaination . Very much clear. very usefull and helpfull. can you provide me more info about the database like this ?

    Like

    Reply

  11. It is very very good. Excellent. I was very much confused about the normalization concepts. After reading it , i learnt about normalization. It is very helpful for my exam. Thanks for the authors.

    Like

    Reply

  12. Dear Sir,
    In the same line , please explain the Boyce-Codd normal form, fourth and fifth normal form please.

    Thanking you in advance
    Sudhir

    Like

    Reply

  13. 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.

    Thanks & regards,
    Vasavi.

    Like

    Reply

  35. Hi

    I had gone through various other sites for normalizations .But the information i found here is really useful and easy to understand.
    Thanks a lot!!!
    The terms like partial ,functional dependency involved have been nicely explained .

    I was so happy after reading this :).

    Thanks Supriya

    Like

    Reply

  36. Hi

    Thanks a lot for the effort you put in for explaining these concepts.

    It was very helpful.
    Thanks a lot and keep up your work which benefits lots of people.

    Thanks
    D.Prasanna.

    Like

    Reply

  37. hey man………

    really till date could not understand these terms better anywhere….

    great job….
    loved it…..

    i think if i would have gone thru this in my engg. i would have not got a back in DBMS….

    thx buddy once again..

    Like

    Reply

  45. Dear Nishant Ji,
    I was searching for a better concept on Normal forms and I came across your site.
    Its really very nice and you have explained the concept in such a lucid manner. its highly commendable.
    Thanks a ton.
    Regards,
    Alok Srivastava

    Like

    Reply

  48. it was really very very good.
    the egs given are really what v need and expect nowadays.totaly i would rate 10/10.thank u for the information.

    Like

    Reply

Please share your thoughts

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Nishant Rana's Weblog

Subscribe now to keep reading and get access to the full archive.

Continue reading

Power Platform Puzzles

D365 CRM, Power Platform Tips &Tricks

Power Spark

Power Spark By Shrangarika

Van Carl Nguyen

Exploration of Power Platform

My Trial

It is my experience timeline.

Power⚡Thomas

Sharing my knowledge and experience about the Microsoft Power Platform.

Arpit Power Guide

a guide to powering up community

Welcome to the Blog of Paul Andrew

Sponsored by Cloud Formations Ltd

Deriving Dynamics 365

Deriving Solutions and features on Power Platform/Dynamics 365

The CRM Ninja

Thoughts & musings from a Microsoft Business Applications Ninja!

buycrm.shop

D CRM Explorer

Learn about Microsoft Dynamics CRM Power Platform customization and implementation and other cool stuffs

Stroke // Jonas Rapp

I know pre-stroke. I will improve who I was.

Microsoft Dynamics CRM / 365

Balu Gajjala

Power Melange

Power Melange By Shalinee

Clavin's Blog - PPUG.ORG

AI - Power Automate - Power Apps - SharePoint Online - Azure - Nintex - K2 - Artificial Intelligence

Sat Sangha Salon

An Inquiry in Being

The Indoencers

The Influencers & Influences of Indian Music

Lets think Innovative

Kokulan's Blog

Monika Halan's blog

Hand's-free money management

D365 Demystified

A closer look at Microsoft Dynamics 365.

Microsoft Mate (msftmate) - Andrew Rogers

Experienced consultant primarily focused on Microsoft Dynamics 365 and the Power Platform

Knowhere365

KKIT365

Manmit Rahevar's Blog

One Stop Destination for Microsoft Technology Solutions

MG

Naturally Curious

Microsoft Technologies and D365 Blogs by Prasanna Vadlamudi

Prasanna Vadlamudi

Brian Illand

Power Platform and Dynamics 365

Steve Mordue

The Professional Paraphraser

Subwoofer 101

Bass defines your home theater

justanotherbuzzword.wordpress.com/

Neel Bhatt

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Codemender

Everything D365

Discovering Azure DevOps and D365 Business Applications

Tech Wizard

Lets do IT Spells

Jan Kodet

Minh Trung 's Weblog

stoovo

Two Bite Tips

Regina Properties For Sale | Russ Parry REALTOR® | RE/MAX Crown Real Estate

Russ Parry REALTOR®

mobiupdates.com

XRM Tricks (Power Platform & Dynamics CRM )

Power Platform & Dynamics CRM

CRM TIPS BY PRM

Mail to crmtipsbyprm@gmail.com for queries and suggestions

Microsoft Dynamics CRM Blog

nijos.dev

Giving back to the community what I have learned

Power Platform Learning

Your Go-To Resource for Power Apps, Power Automate & More

xrm CRM Dynamics

Dynamics CRM Technical & Functional Info

SharePoint and other geeky stuff

Dynamics 365 Blogs - Explained in unique way

Sometimes you need to look at things from different perspective.