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 }
Zip → City 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.
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.
ReplyDeleteits really helpful. god bless you. now finally i understood all the things in deep. loved your work
ReplyDeleteCan u please tell why u haven't considered U _ email as a primary key..It can also determine all the other attributes uniquely..
ReplyDeleteThanks. 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.
DeleteThis comment has been removed by the author.
ReplyDeleteWhy 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?
ReplyDeleteFor 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
DeleteSir how can we get to the following set of fd's ?
ReplyDeleteJust user_id-> {all other attributes} would suffice and would uniquely identify here.
so why do we need the (zip->city,state) and (city->zip,state).
Please Help.
Thank you. And Great tutorial.
user_id alone can be the key. The identification of all functional dependencies will help in normalizing to 2nf, 3nf, etc. Zip->city state is a transitive dependency. If there is transitive dependencies then the table is not in 3nf. Note: city->zip state does not hold on this relation. hence i removed it.
DeleteI understood the basic concept. Thank you. :)
DeleteVery informative..thanks a lot..I have understood it clearly
ReplyDeletei guess there is transitive dependency in city table
ReplyDeletezip -> city,state
city -> state
once check it
Thanks Jayaprakash. I have not considered city -> state. The reason is that there may be a city with the same name as that of the other in same state or different state. If you consider city as unique attribute, your point is correct
Deleteif i want to make diagram between the two tables what is the primary key and foreign key
ReplyDeleteWhich diagram you are mentioning about? I would take it as ER diagram. The first property about connecting two relations is type of relationship. If the relationship type is many-to-many, then you have to create third table. In all the other cases, you can directly connect.
DeleteIf you want to connect two tables, then in most of the cases, one of those tables will be ONE side table, and the other one will be MANY side table. The primary key of ONE side table will be included as FOREIGN key of MANY side table. If you can't understand, please refer here 'How to reduce an ER diagram to relation schemas - http://www.exploredatabase.com/2018/01/reduce-erd-with-many-relationships-to-schema.html' or else please contact me.
Thank a lot
ReplyDeleteConvert it to one level higher normal form than the existing one
ReplyDeleteTo convert it to BCNF? City table is already in BCNF because the LHS of all FDs are candidate keys. Likewise, User_Personal table is also in BCNF. If we consider email as unique and still the property "the LHS should be candidate key" is true for user_personal. Hence, it is also in BCNF.
DeleteNice
ReplyDelete