GATE CSE 1999 | Question: 2.7, UGCNET-June2014-III: 25

Question

Consider the schema $R=(S,T, U, V)$ and the dependencies $S \rightarrow T, T \rightarrow U, U \rightarrow V$ and $V \rightarrow S$. Let $R = (R1\text{ and } R2)$ be a decomposition such that $R1 \cap R2 \neq \phi$. The decomposition is

• A. not in $\textsf{2NF}$
• B. in $\textsf{2NF}$ but not $\textsf{3NF}$
• C. in $\textsf{3NF}$ but not in $\textsf{2NF}$
• D. in both $\textsf{2NF}$ and $\textsf{3NF}$

Comments

similarily if we decompose it by any combinations
like  (SVT)  (TUV)  or  (STU)  (UV)  etc ... then also they are in BCNF , so in 3NF , 2NF , 1NF also

---

Option D is Correct

---

Conclusion: it's in bcns so by default 2NF,3NF,1NF also satisfied 

But when it comes to dependency preserving and lossless...

3NF decomposition will produce atleast one decomposition that is lossless and dp(no guaranty for every decomposition)

Answer 1

$R_1 \cap R_2 \neq \phi.$ This makes the decomposition lossless join, as all the attributes are keys, $R_1 \cap R_2$ will be a key of the decomposed relations (lossless condition says the common attribute must be a key in at least one of the decomposed relation).  Now, even the original relation $R$ is in $3NF$ (even $\text{BCNF}$)as all the attributes are prime attributes (in fact each attribute is a candidate key).  Hence, any decomposition will also be in $3NF$ (even $\text{BCNF}$). Option $D$.

PS: Decomposition in $3NF$ means decomposed relations are in $3NF$. But when we consider any decomposed relation, we must also include any FD which are being implied by the original relational schema. For example, in a decomposed relation $STU,$ there will be a FD $U\to S$ as well.

Comments

but it is said that BCNF Removes Prime --> Prime dependency right ?

---

NOT EXACTLY ALL......BUT IT REMOVES THOSE PRIME-TO-PRIME WHERE DETERMINENT IS NOT A SUPERKEY........(CORRECT ME IF I AM WRONG) 

---

In this que we get decomposition to be lossless , dependency preserving and in Bcnf

Answer 2

Answer : In both 2NF and 3NF 

Dependencies are 

 S --> T, T --> U, U -->V  V --> S

S+ = STUV          U+ = UVST

T+ = UVST          V+ = VSTU

There is no Partial Dependencies here So it is in 2 NF

RHS of every Dependencies  is a Key as well as all are Prime Attributes So it is in 3 NF.

Comments

Decomposition is not lossy because of given in the question that intersection of two relations is not empty.

Which means there is something in the intersection and by looking at the FD's it definitely become Lossless and Dependency preserving too. 

---

@Kuljeet Shan brother you are slightly wrong, it's a lossy decomposition. 

As a binary decomposition is lossless onlt if R1 Intersection R2 is super key in either R1 or R2. Here R1 intersection R2 is empty. So it can't be super key in R1 and nor in R2. So it will be for sure a lossy decomposition.

 

---

R1 intersection R2 is empty

It is given that R1 intersection R2 is not not empty brother, since every key is a candidate key, so aslong as there is atleast one attribute common between two relations, it is loseless decomposition.

Answer 3

R1 ∩ R2 != ∅. This makes the decomposition lossless join and as all the attributes are prime, so whatever is the decomposition, it will be 2NF and 3NF. So, Ans will be option d.

Comments

here it's R1 ∩ R2 =∅ .i.e, lossy

Answer 4

(d) both in 2nf and 3nf

since r1 and r2 does not have any common attributes , and if we decomse it to relation having 2 attributes, then they are by default in 3nf , Suppose if we decompose r1 with 1 attribute and r2 with 3 attributes, since all of them are dependent all of them could be a prime attribute in this case also they are in 3nf, since they are in 3nf, 2nf by default satisfy

Comments

Sir this is what  found in elmasri

"Normal forms, when considered in isolation from other factors, do not guarantee a good database design. It is generally not sufficient to check separately that each relation schema in the database is, say, in BCNF or 3NF. Rather, the process of normalization through decomposition must also confirm the existence of additional properties that the relational schemas, taken together, should possess. These would include two properties:

The nonadditive join or lossless join property, which guarantees that the spurious tuple generation problem discussed in Section 15.1.4 does not occur with respect to the relation schemas created after decomposition.
The dependency preservation property, which ensures that each functional dependency is represented in some individual relation resulting after decomposition.

The nonadditive join property is extremely critical and must be achieved at any cost, whereas the dependency preservation property, although desirable, is sometimes sacrificed."

---

Yes. Thats true. But whether a lossy decomposed relation do not have a normal form is not clear, at least for me :)

---

@Aravind

"since r1 and r2 does not have any common attributes" 

On your this statement please tell me how R1 and R2 doesn't have something common.

as clearly mentioned in the question that R1∩R2≠ϕ means their intersection is not empty. right ?

so they have something common. Isn't it?

Answer 5

If all the attributes are candidate keys, then any subset of attributes will be a super key

Whatever FD you can form, your LHS will have a subset of attributes

From above stataments, we can say that any FD that can be formed will have LHS as a super-key

This is good enough to say that the relation is in BCNF ...

This is also applicable even after the decomposition of relation irrespective of what common attributes we have (even if there is no common attributes, though lossy decompositions are not recommended)

Comments

If R1={S,T,U,V} and R2=$\Phi$ ,

Then R1 $\cap$ R2=$\phi$

 

then it is lossless and dependency preserving and also follow the rules of 3nf. Since it is 3nf , then it is 2nf also.

Answer 6

since no partial dependency and non key to non key

every determinant is deriving all atributes so in bcnf

 by default 1nf

so it is all 1nf,2nf,3nf, and bcnf