Questions on Normal Forms -Part 1
Identify the Normal Forms of the relation R :
Question 1 :
R(ABCD)
FD : {A → B, B → C}
Solution :
(A)+ = {ABC}
As no dependency defined for D, hence R is in 1NF.
Question 2:
R(ABCD)
FD1 : A → B,
FD2 : B → C,
FD3 : AD → BC
Solution :
| Step 1 : |
Find Candidate Key : (AD)+ = {ADBC} |
| Step 2 : |
Functional
Dependency |
Find Highest
Normal Form |
Reason |
| FD1 : A → B, |
1NF |
Partial Dependency as A is part of
Key |
| FD2 : B → C, |
2NF |
Nonkey Nonkey allowed |
| FD3 : AD → BC |
BCNF |
LHS is the SuperKey |
|
Question 3:
R(ABCDE)
FD1 : AB → C,
FD2 : C → D,
FD3 : D → E
FD4 : E → A
Solution :
| Step 1 : |
Find Candidate Key : (AB)+ = {ABCDE}
Other Candidate Keys derived from AB
{AB,EB,DB,CB} |
| Step 2 : |
Functional
Dependency |
Find Highest
Normal Form |
Reason |
| FD1 : AB → C, |
BCNF |
LHS is the SuperKey |
| FD2 : C → D, |
3NF |
Part of Key → Part of Key is allowed. |
| FD3 : D → E |
3NF |
Part of Key → Part of Key is allowed. |
| FD3 : E → A |
3NF |
Part of Key → Part of Key is allowed. |
| R is in 3NF. |
|
Question 4:
R(ABCDEF)
FD1 : AB → C,
FD2 : C → D,
FD3 : B → E,
FD4 : B → F
Solution :
| Step 1 : |
Find Candidate Key : (AB)+ = {ABCDEF} |
| Step 2 : |
Functional
Dependency |
Find Highest
Normal Form |
Reason |
| FD1 : AB → C, |
BCNF |
LHS is the SuperKey |
| FD2 : C → D, |
2NF |
NonKey → NonKey is allowed. |
| FD3 : B → E |
1NF |
Partial Dependency as B is part of
Key |
| FD3 : B → F |
1NF |
Partial Dependency as B is part of
Key |
| R is in 1NF. |
|
Question 5 :
a) If relation R consists of only simple candidate keys then R
should be in .......?
b) If relation R consists of only prime attributes, then R should
be in .......?
c) If relation R is in 3NF and every CK is simple CK, then
relation is in ....?
d) If relation R with no non trivial FD, then R is in ......?
Solution :
a) If relation R consists of only simple candidate keys then R
should be in 2NF but may or may not be in BCNF,3NF
b) If relation R consists of only prime attributes, then R should be
in 3NF but may or may not be in BCNF
X → Y {X: Candidate Key , Y : Prime Attribute }
Example of this type of relation is defined in Question 3, i.e.
R(ABCDE)
FD : {AB → C, C → D, D → E, E → A}
CK : {AB,EB,DB,CB}
c) If relation R is in 3NF and every CK is simple CK, then relation
is in BCNF.
Example of this type of relation is :
R(ABCD)
FD : {A → B, B → C, C → D, D → A}
d) If relation R with no non trivial FD, then R is in always BCNF.
Example of this type of relation is :
R(ABC)
FD : {A → A, B → B, C → C, AB → AB, AC → AC, BC → BC, ABC → ABC}
]]>