Normalization - solved exercises - Normal forms 1

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database - Examples / Normalization to 1NF, 2NF, 3NF




Let us assume a table User_Personal as given below;

UserID
U_email
Fname
Lname
City
State
Zip
MA12
Mani@ymail.com
MANISH
JAIN
BILASPUR
CHATISGARH
458991
PO45
Pooja.g@gmail.co
POOJA
MAGG
KACCH
GUJRAT
832212
LA33
Lavle98@jj.com
LAVLEEN
DHALLA
RAIPUR
CHATISGARH
853578
CH99
Cheki9j@ih.com
CHIMAL
BEDI
TRICHY
TAMIL NADU
632011
DA74
Danu58@g.com
DANY
JAMES
TRICHY
TAMIL NADU
645018
  • Is this table in First Normal Form?
Yes. All the attributes contain only atomic values.

  • Is this table in Second Normal Form?
To verify this property, we need to find all the functional dependencies which are holding in User_Personal table, and have to identify a Primary key.
Let us do that by using the sample data. This leads to the following set of FDs;
F = { UserID U_email Fname Lname City State Zip, 
Zip City State, 
City Zip State }
As UserID attribute can uniquely determine all the other attributes, we can have UserID as the Primary key for User_Personal table.
The next step is to check for the 2NF properties;
Property 1 – The table should be in 1NF.
Property 2 – There should not be any partial key dependencies.

Our table is in 1NF, hence property 1 is holding.
Primary key of our table is UserID and UserID is single simple attribute. As the key is not composite, there is no chance for partial key dependency to hold. Hence property 2 is also holding.
User_Personal table is in 2NF.

  • Is User_Personal in 3NF?
To verify this we need to check the 3NF properties;
Property 1 – Table should be in 2NF.
Property 2 – There should not be any Transitive Dependencies in the table.

Table User_Personal is in 2NF, hence property 1 is satisfied.

User_Personal table holds the following Transitive dependency;

UserID Zip, Zip City State

Hence, property 2 is not satisfied and the table is not in 3NF.

Solution:
Decompose User_Personal. For this, we can use the functional dependencies Zip City State and UserID U_email Fname Lname City State Zip.

As a result, we can have the following tables (primary keys are underlined);

User_Personal (UserID, U_email, Fname, Lname, Zip)
City (Zip, City, State)

UserID
U_email
Fname
Lname
Zip
MA12
Mani@ymail.com
MANISH
JAIN
458991
PO45
Pooja.g@gmail.co
POOJA
MAGG
832212
LA33
Lavle98@jj.com
LAVLEEN
DHALLA
853578
CH99
Cheki9j@ih.com
CHIMAL
BEDI
632011
DA74
Danu58@g.com
DANY
JAMES
645018
Table - User_Personal

Zip
City
State
458991
BILASPUR
CHATISGARH
832212
KACCH
GUJRAT
853578
RAIPUR
CHATISGARH
632011
TRICHY
TAMIL NADU
645018
TRICHY
TAMIL NADU
Table – City

Both tables are in 3NF.
Hence, tables are normalized to Third Normal Form.


7 comments:

  1. This is the best and most effective tutorial I have ever read so far in computer science field. I read a lot of tutorials and watched a lot of videos but got more and more confused. But now it is plain and crystal clear for me. Thanks a lot.

    ReplyDelete
  2. its really helpful. god bless you. now finally i understood all the things in deep. loved your work

    ReplyDelete
  3. Can u please tell why u haven't considered U _ email as a primary key..It can also determine all the other attributes uniquely..

    ReplyDelete
    Replies
    1. Thanks. That's right. We could also consider the mail as the key according to the sample data. But when you consider in real time, if you have a constraint such that mail should be unique, then it would work otherwise not.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Why not split Tabel city into a 3th table City - State? Since you have 1 city with the same state? You didn't remove redundancy this way?

    ReplyDelete
    Replies
    1. For fitting into the screen, i left few information like the area name in the city. instead i used zip code to refer to that area. Your question is correct. If we consider the given table alone, then City table is not in 3NF because of transitive dependencies

      Delete

Popular Posts