ISI 2017 PCB C3 (A)

Question

Let R(A, B, C) be a relation with primary key (A) and S(A, D, E) a relation with primary key (A, D). Each of the relations has n tuples. If the number of tuples in R natural join S is m, then determine the number of tuples in R natural left outer join S.

Answer 1

A is a primary key in R and A is a foreign key in S referring to A in R. Therefore, the number of tuples in natural join should be the number of tuples in S, i.e, n. Since the number of tuples in R and number of tuples in natural join of R and S are same=> n, therefore the number of tuples in R natural right outer join S should also be n.

In R natural left outer join S we cannot say the exact number of tuples, but we can say max and min no of tuples.

1.min should be n

2.max should be 2n-1 => ex-:

R(A,B,C)= (1,-,-) ,(2,-,-), (3,-,-)

S(A,D,E)= (1,2,-), (1,3,-),(1,4,-)

Please correct me if I am wrong.

Comments

thanks.. I have edited my answer :)

---

Here S has the primary key (A, D). So I think a natural join will be cartesian product

---

But its not mentioned in question that S.A is foreign key referring to R.A.

R(A,B,C) = (1,_,_), (2,_,_),(3,_,_),(4,_,_)

S(A,D,E) = (1,1,_),(1,2,_) ,(3,1,_), (5,3,_)

Since R.A is primary key,there will be n distinct elements, but S.A need not all be distinct.

Number of R.A which are not in S.A  =  #{1,2,3,4} -  #{1,3,5} = 1 = x

now x elements in R do not match with S, they will have NULL values from S table.

So elements in LOJ  will be m + x.


Not sure if this approach is right.