## QUESTIONS ON BOYCE CODD NORMAL FORMTo solve the question on BCNF, we must understand its definitions of BCNF:
All BCNF is 3NF but vice versa may or may not be true.
From the above arrow diagram on R, we can see that an attribute X is not determined by any of the given FD, hence X will be the integral part of the Candidate key, i.e. no matter what will be the candidate key, and how many will be the candidate key, but all will have X compulsory attribute.
X + = X(from the closure method we studied earlier) Since the closure of X contains only X, hence it is not a candidate key.
a) XY + = XYZ ( from the closure method we studied earlier) Since the closure of XY contains all the attributes of R, hence b) XZ + = XZY (from the closure method we studied earlier) Since the closure of XZ contains all the attributes of R, hence Hence there are two Since R has 3 attributes: - X, Y, Z, and Candidate Key is XY and XZ, Therefore, prime attribute(part of candidate key) are X, Y, and Z while a non-prime attribute is none.
a) Either X is Super Key b) Or Y is a prime attribute. Given FD are XY → Z, and Z → Y and Super Key / Candidate Key are XZ and XY a) FD: b) FD: Since both FD of R, XY → Z and Z → Y satisfy the definition of 3NF hence R is in 3 NF
a) X is Super Key Given FD are XY → Z, and Z → Y and Super Key / Candidate Key is XZ and XY b) FD: c) FD:
Since due to FD: Z → Y, our table was not in BCNF, let's decompose the table FD: Z→ Y was creating an issue, hence one table R1( Z, Y ) Create Table for key XY R2(X, Y) as XY was candidate key Create Table for key XZ R2(X, Z) as XZ was candidate key ## Note: When we have more than one key( eg: XY and XY) then while decomposing keep in mind that you compare both R2 and R3 with R1 such that among R1 and R2 or R1 and R3 there should be at least one common attribute and, that common attribute must be key in any of the table.Considering R1( Z, Y) and R2(X, Y) both tables have one common attribute Y, but Y is not key in any of the table R1 and R2, hence we discard R2(X, Y) i.e. discarding candidate key XY. Considering R1( Z, Y) and R3(X, Z) both tables have one common attribute Z, and Z is key of the table R1, hence we include R3(X, Z) i.e. including candidate key XZ. Hence decomposed tables which are in BCNF:
From the above arrow diagram on R, we can see that an attribute X is not determined by any of the given FD, hence X will be the integral part of the Candidate key, i.e. no matter what will be the candidate key, and how many will be the candidate key, but all will have X compulsory attribute.
X + = XYZ (from the closure method we studied earlier) Since the closure of X contains all the attributes of R, hence From the definition of Candidate Key (
a) X is Super Key First, we check that table is in 3NF?
a) Either X is Super Key b) Or Y is a prime attribute. a) FD: X → Y is in 3NF (as X is a super Key) b) FD: Y → Z is not in 3NF (as neither Y is Key nor Z is a prime attribute) Hence because of Y → Z using definition 2 of 3NF, we can say that above table R is not in 3NF.
Since due to FD: Y → Z our table was not in 3NF, let's decompose the table FD: Y → Z was creating issue, hence one table R1(Y, Z) Create one Table for key X, R2(X, Y), since X → Y Hence decomposed tables which are in 3NF:
Next TopicQUESTIONS ON NORMALIZATION |