UGC NET CSE | January 2017 | Part 2 | Question: 19

Question

Consider a schema $R(MNPQ)$ and functional dependencies $M\rightarrow N, P\rightarrow Q$. Then the decomposition of  $R$ into $R_{1} \left (MN \right )$ and  $R_{2} \left (PQ \right )$ is __________.

• A. Dependency preserving but not lossless join
• B. Dependency preserving and lossless join
• C. Lossless join but not dependency preserving
• D. Neither dependency preserving nor lossless join.

Answer 1

R(MNPQ) and functional dependencies M→N, P→Q. Then the decomposition of R into R1(MN) and R2(PQ) 

here clearly the dependencies are preserved but they are not lossless as both R1 and R2 cannot be formed back again as they donot have a common attribute which is a key so answer is 1

Comments

lossless??

---

I think option 1 It will be not lossless join.

---

i answered it..it will not be loseless

---

@udipito lossy does not mean that some records are lost....It means that you will not be able to generate the exact relation as before ...In the above if R1 and R2 are joined to form the previous relation you will not get exact relations as many will come out of from the Cartesian product to remove unnecessary things we need a common attribute which is a key(unique) it is clearly lossy

Answer 2

Here as only M->N and P->Q is there, and decomposition is also in MN and PQ

so it is Not Loseless

and all dependency are also preserved

so 1 is correct answer here

Comments

i know the method to find lossless which is so long.. by drawing tables..

is there any simpler way?

---

i think not..i also ude the same method..but here you can do just by visualising

---

For better understanding of the concept go to the article 

http://www.geeksforgeeks.org/lossless-join-and-dependency-preserving-decomposition/

---

@Debasmita Bhoumik

For two relations : Just see which are common attributes of the relations i.e. by looking only. Now, take closure of that common attribute if u found any one of the relations back that means it is lossless.

For more than two relations u have to follow the Chase Algorithm which is very easy to implement.

Answer 3

OPTION 1 Since there is no common attribute in the decomposed table so when we join it there will be some spurious(Extra) tuple so itwill be lossy.But the dependencies are preserved.

Answer 4

The condition for the decomposition to be lossless is that the common attribute(s) between the decomposed relation(s) should be the "key" in at least one of the decomposed relations. Since this is not true for the given decomposition, the decomposition is not lossless.

The dependencies are well preserved as  $M\rightarrow N$  is applicable for relation $R_{1}$  and  $P\rightarrow Q$  is applicable for relation  $R_{2}$ and their union gives original functional dependencies set.

$\therefore$  Option A is correct.

Answer 5

Lossless Join Property:
A decomposition is considered lossless if we can reconstruct the original relation by natural joining the decomposed relations without any loss of information(i.e. without any addition of spurious tuples).

The criterion for a decomposition \( R \) into \( R_1 \) and \( R_2 \) to be lossless is:
\( R_1 \cap R_2 \) must be a superkey for either \( R_1 \) or \( R_2 \).

In this case, \( R_1(M, N) \) and \( R_2(P, Q) \) have no common attributes (i.e., \( R_1 \cap R_2 = \emptyset \)). Therefore, the decomposition cannot be lossless, as there is no common attribute to act as a key in either \( R_1 \) or \( R_2 \).

Dependency Preserving Property:
A decomposition is considered dependency preserving if all functional dependencies of the original relation can be enforced in the decomposed relations without requiring a join of the decomposed relations.

The given functional dependencies are:
1. \( M \to N \)
2. \( P \to Q \)

In \( R_1(M, N) \), the dependency \( M \to N \) can be enforced.

In \( R_2(P, Q) \), the dependency \( P \to Q \) can be enforced.

Since each dependency can be enforced in one of the decomposed relations, the decomposition preserves dependencies.

In short,
The decomposition of \( R \) into \( R_1(M, N) \) and \( R_2(P, Q) \) is:
Dependency preserving but not lossless join.

Option A is Correct
Dependency preserving but not lossless join.

Suggestion Note:

The criterion that for a decomposition \( R \) into \( R_1 \) and \( R_2 \) to be lossless, \( R_1 \cap R_2 \) must be a superkey for either \( R_1 \) or \( R_2 \), should only be used to check binary decompositions. For non-binary decompositions, the Chase Test is a more suitable method.