GATE CSE 2002 | Question: 2.24

Question

Relation $R$ is decomposed using a set of functional dependencies, $F$, and relation $S$ is decomposed using another set of functional dependencies, $G$. One decomposition is definitely $\text{BCNF}$, the other is definitely $3NF$, but it is not known which is which. To make a guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of $F$ and $G$ are available).

• A. Dependency-preservation
• B. Lossless-join
• C. $\text{BCNF}$ definition
• D. $3NF$ definition

Comments

Don't think that 3NF decomposition is always Dependency preserving !!  it is not the case always....it is also possible that 3NF decomposition not dependency preserving

the correct concept is : For every relation R ,there  always exists a  decomposition into 3NF that is lossless and dependency preserving

 

also consider the following two statements:

S1: 3NF decomposition is  always lossless join and dependency preserving

S2 : There is always a 3NF decomposition possible which is lossless and dependency preserving

 

S1 is FALSE 

S2 is TRUE

---

Option C is correct

Using BCNF we can deside Which is IN BCNF and Which is 3NF like which is which.

 

---

\[
\begin{array}{c|c|c}
\textbf{Normal Form} & \textbf{Lossless Join} & \textbf{Dependency Preserving} \\ \hline
2NF  & \checkmark & \checkmark \\
3NF  & \checkmark & \checkmark \\
BCNF & \checkmark & \text{May or may not be}
\end{array}
\]

since $\text{BCNF}$ is stricter, the decomposition which is $\text{3NF}$ will fail $\text{BCNF definition}$

Answer 1

• A. $\text{False}$.  $\text{BCNF}$ may or may not satisfy Dependency preservation, $3NF$ always does. But we can't make any guaranteed decision, regarding $\text{BCNF}$ if it satisfies Dependency preservation
• B. $\text{False}$. Both are lossless.
• C. $\text{True}$. Using this we can always decide between $\text{BCNF}$ & $3NF$.
• D. $\text{False}$. Every $\text{BCNF}$ relation is also $3NF$ trivially.

Answer -> $C$ ( & Only $C$).

Comments

Good comment @ybairwa786

---

@jacknroll 
 i think its perfectly explained as you can see examples of BCNF decompositions which are not dependency preserving
Try with this example : 

AB -> CD, D -> A

Also for the second option both are loseless as when you decompose any relation into BCNF you will only eliminate the RHS from the dependencies so The left key will always be in one sub relation (Table) so it will never become lossy in BCNF.

---

@jacknroll

yes you are right that the statement "3NF decomposition is always dependency preserving" is actually FALSE there is a slight modification in this statement that "it is always possible to decompose any relation into 3NF with Dependency preserving" which is actually true.

Answer 2

A. dependency preservation.
in 3NF Dependency always preserved but in BCNF it  may or may not be preserved.
For a particular set of FDs it may not differentiate BCNF and 3NF.

B.Lossless  join always possible in both BCNF as well as 3NF.

D. 3NF definition also unable to differentiate BCNF & 3NF bcoz every BCNF is trivially 3NF.

C. every 3NF which is not BCNF fails BCNF Definition so it may used to differentiate which is BCNF & which is 3NF ..

Comments

In Question, it is given that other is definitely 3NF.Then it may or may not be BCNF.

In case if other decomposition is 3NF but not BCNF, BCNF Definition ables to identify which decomposition is 3NF and which one is BCNF.

But in case if other Decomposition is both 3NF and BCNF then BCNF definition unable to identify which decomposition is BCNF and which one is 3NF.

So how C is the Answer ? If it is the second case then C option also fails.

---

I thought about this too, but it is given in the question that one relation is definitely BCNF and one is definitely 3NF. Therefore, both cannot be BCNF. So the BCNF test will be sufficient.

Answer 3

Option (C) . Since it is given that out of F and G, one is 3NF and other is BCNF then clearly we can apply BCNF defintion to differentiate between F and G.

Option (D) is useless as 3NF defintion will be satisfied by both the Functional Dependencies sets.

Option (A) and Option (B) are not the criteria to decide about BCNF and 3NF. They are criteria for Relation decomposition.

Eventhough Decompostion has been used here and some give Option (A) Dependency Preservation as answer but I will go with BCNF defintion as the distinguishing factor.

Answer 4

BCNF may or may not preserve dependency preservation. 3NF satisfy dependency preservation. Both BCNF and 3NF are Lossless join decomposition. So we BCNF definition is required.

Answer is C. BCNF definition

Answer 5

Explain please

3NF Dependency always preserved but in BCNF it  may or may not be preserved. 

Answer 6

Note 

If Relation is in 2NF then it is always in 1NF

If Relation is in 3NF then it is always in 2NF and 1NF

If Relation is in BCNF then it is always in 3NF, 2NF and 1NF 

Eliminate each option 

a) dependency preserving  not the criteria to decide about BCNF and 3NF. It is for Relation decomposition.

b) Lossless  not the criteria to decide about BCNF and 3NF. It is for Relation decomposition.

c)Since it is given that out of F and G, one is 3NF and other is BCNF then  we can apply BCNF defintion to differentiate between F and G.

if Relation is in BCNF Then it is alwayS is in 3NF, 2NF, 1NF

if Relation is in 3NF Then it is always is in 2NF, 1NF but may or maynot be in BCNF 

d) 3NF defintion will be satisfied by both the Functional Dependencies sets So False