VIEW 107000
Login Register
Search ...
FOR FREE YEAR SOLVED Sign Up
Normalization Examples
Before seeing the examples please follow previous chapters:
Find Super and Candidate Key from Attribute Closer, 2NF, 3NF, BCNF.
For more details about partial dependency and transitive dependency follow:
2NF, 3NF
Engineering - CET 2022
Example 1:
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
Q. R (ABCD) Today.
NMIMS
{AB → CD, D → A}
Assume that it does not contain any multi-valued attribute.
Find the highest normal form of this relation.
Solution:
So, initially, we think, it is in 1NF
R (ABCD)
{AB → CD, D → A}
To find the highest normal form of the above relation we first calculate the Candidate key, Prime attribute, and Non-
prime attribute from this given functional dependencies.
To find the candidate key we have done closer of all attributes:
For more details please follow: Find Super and Candidate Key from Attribute Closer
So, ‘AB’ is Super Key and Candidate Key also.
So, attribute ‘D’ is not a Super Key and Candidate key also.
So, attribute ‘C’ is not a Super Key and Candidate key also.
So, Candidate Key (CK) = {AB}, Prime Attribute (PA) = {A, B}, NPA (Non-prime attribute) = {C, D}
Check for 2NF:
Here AB is not a proper subset of Candidate Key. AB → AB, so, it is not Partial Dependency. It is in 2NF.
Here D → AB, so it is not Partial Dependency because D is not a subset of the candidate key and A and B is prime
attribute so, it is in 2NF.
So, R (ABCD) – is in 2NF.
Engineering - CET 2022
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
Check for 3NF: Today.
NMIMS
Here ‘AB’ is Super Key, so, it is in 3NF, it is not Transitive Dependency.
Here ‘D’ is not Super Key but ‘A’ is Prime Attribute. So, it is not Transitive Dependency. It is also in 3NF.
So, R(ABCD) is in 3NF also.
But it is not in BCNF why because D → A is a Functional Dependency but D is not Super Key.
So, redundancy can exist due to D → A.
We can try to decompose it:
But the problem is it is not dependency preserving because we can’t derive Functional Dependency AB → CD.
Important note: Every 3NF we can’t convert to BCNF. Maybe it is possible or maybe not possible. So, it is not
mandatory that every 3NF must be converting to BCNF.
Example 2:
Q. R (ABCDEF)
{A → BCDEF, BC → ADEF, B → F, D → E}
Assume it is in 1NF.
Find the highest normal form of this relation.
Solution:
To find the highest normal form of the above relation we first calculate the Candidate key, Prime attribute, and Non-
prime attribute from this given functional dependencies.
To find the candidate key we have done closer of all attributes:
Engineering - CET 2022
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
Today.
NMIMS
For more details please follow: Find Super and Candidate Key from Attribute Closer
So, Candidate Keys = {A, BC}, Prime Attributes = {A, B, C}, Non-Prime Attribute = {D, E, F}.
Check for 2NF:
A → BCDEF:
Functional Dependency but not Partial dependency. It is in 2NF because A ⊄ Candidate Key (attribute ‘A’ itself a
Candidate Key).
BC → ADEF:
It is a functional dependency, it is in 2NF because of BC ⊄ CK. So, it is not Partial Dependency (‘BC’ itself a
Candidate Key).
B → F:
It is Partial Dependency (PD), it is a partial dependency because of B ⊂ CK and ‘F’ is Non-Prime Attribute (NPA).
So, it is not in 2NF.
D → E:
It is in 2NF because of D ⊄ CK.
So, R(ABCDEF) not in 2NF because B → F is Partial Dependency.
So, we should decompose the table where it creates the problem, B → E, so, decompose BE as one table and the rest of the
attribute as another table.
Check for lossless decomposition:
Also, dependency preserving because we derive all Functional Dependencies as main Functional Dependencies.
Check for 3NF:
Engineering - CET 2022
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
B → F where ‘B’ is Super KeyToday.
so, it is in 3NF also.
NMIMS
A → BCDE: It is functional dependency because ‘A’ is Super Key, it is in 3NF.
BC → ADE - is in 3NF because BC is Super Key.
D → E: It is transitive dependency, so it is not in 3NF because ‘D’ is not Super Key and ‘E’ is Non-Prime Attribute.
So, decompose table into two parts-
It is lossless and Dependency Preserving.
Because A → BCD, BC → AD, D → E, B → F all are Super Key on the left-hand side.
When we check the highest normal form of a relation, we can check it both ways.
Example 3:
Now we check the reverse way:
R (ABCDEFGH)
Engineering
{ABC → DE, E → FG, H → G, G → H, ABC- →
CET
EF}2022
[Link]
Now we can drive candidate key, after 12th.
prime Developand
attribute, next-gen tech [Link]
non-prime Admissionsfrom
Open 2022. Register
attribute closer. Learn more
Today.
Candidate Key (CK) = ABC, Prime
NMIMS Attribute (PA) = {A, B, C}, Non-prime-attribute (NPA) = {D,E,F,G,H}
First, we check for BCNF:
ABC → DE is in BCNF because ‘ABC’ is Super Key.
E → FG is not in BCNF because ‘E’ is not Super Key.
H → G is not in BCNF, ‘H’ is not Super Key.
G → H is not in BCNF, ‘G’ is not Super Key.
ABC → EF is BCNF, ABC is Super Key.
So, the highest normal form of relation.
R (ABCDEFGH) is 2NF but not in 3NF.
Please Login to Bookmark Share
Contributed by
Debashis Roy Faculty of Computer Science Department, JCC College, University of Calcutta
Ask:
suppor t@[Link]
Contribute:
contribute@[Link]
USEFUL LINK
Home
About Us
Privacy Policy
Terms of Use
EXAMS
NTA NET Gate Bank Clerk
CONTACT
Contact Us
Help Center
Engineering - CET 2022
NEWSLETTER
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
To get updated news and information subscribe:
Today.
NMIMS
Email
a
GO
2022 © MyCareerwise - All rights reserved
Engineering - CET 2022
[Link] after 12th. Develop next-gen tech skills. Admissions Open 2022. Register Learn more
Today.
NMIMS