Natural join subset decomposition

Question

For this question , I found the answer is (c). but I can give an example to show that (c) is not correct. which is the answer?

Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ‘select A,B,C from r’ and r2 = ‘select A, D from r’. Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is true?

(a) s is subset of r
(b) r U s = r
(c) r is a subset of s
(d) r * s = s

If the Answer is (c) , consider the following example with lossy decomposition of r into r1 and r2.

Table r

A B C D

1 10 100 1000
2 20 200 1000
3 20 200 1001

Table r1

A B C

1 10 100 2 20 200

Table r2

A D

2 1000
3 1001

Table s (natural join of r1 and r2)

A B C D

2 20 200 1000

The answer is not (c) . but I can also give you an example that (c) can be an answer. What should be the answer?

Answer 1

Table r is:

A B  C   D
1 10 100 1000
2 20 200 1000
3 20 200 1001

r1 is:

r1 = ‘select A,B,C from r’ 

A B  C   
1 10 100 
2 20 200 
3 20 200 

r2 is:

r2 = ‘select A, D from r'

A D
1 1000
2 1000
3 1001

s is:

s = r1 * r2

1 10 100 1000
2 20 200 1000
3 20 200 1001

So effectively r is as subset of s. If the definicion of r1 is ‘select A,B,C from r’ you just can't remove a row (as you did in your example) from the result and said that r1 still complies with the definition, the same applies to r2 where you remove the first row.

Answer 2

Table r
A B C D

1 10 100 1000
1 20 200 2000

Table r1
A B C

1 10 100
1 20 200

Table r2
A D

1 1000
1 2000

Table s (natural join of r1 and r2)
A B C D

1 10 100 1000
1 20 200 1000
1 10 100 2000
1 20 200 2000

The decomposition is called "lossy" because we have lost the ability to recompose the original relation value using natural join. This lost ability manifests itself by extra rows appearing when we try the natural join. The underlying cause why this happens is that the decomposition did not retain any key ( { {B} {C} {D} } ) fully in both tables of the decomposition. If any key of the original is fully retained in all components of a decomposition, then the decomposition isn't lossy.