392 views
Consider the following relations: $R_1(A,B,C)$ and $R_2(A,D,E)$. $R_1$ has 1000 records and $R_2$ has 2000 records. The attribute $A$ in $R_2$ is referencing attribute $A$ in $R_1$. Let $X$ be minimum number of records in $R_1$ ⨝ $R_2$ and $Y$ be the maximum number of records in $R_1$⨝$R_2$. The sum of $(X+Y)$ is _______.
reopened | 392 views
You need to understand what is Natural Join operation do.

here R1(A,B,C) R2(B,C,D) with R2(A) is forrien key to R1(A).

R1 NATURAL JOIN R2 = number of relation with foreign key attribute relation.

So maximum =2000 , minimum =0

Sum( maximum + minimum) = 2000
Why minimum cannot be 0?Assume the foreign key in R2 is all NULL,because question does not say that foreign key CAN NOT be null and in general it may be null.So to get minmum if all the null then join will 0.

I think untill unless mentioned we need not to consider.

Arnabi  what is the answer ? I will go with 2000 for numerical type.:

Anu007 :- You are suggesting 2000 or 4000 as the answer?

Taking the minimum case into consideration answer given is 2000.

+1 vote

Let A in R2 be null for all the tuples as foreign keys can be null , hence natural join would result in zero tuples. Hence minimum = 0 tuples

Max records will only be generated when the value in R2(A) for all the tuples is same as any value in R1(A). Here, all values of A in R1 will be unique as it must be a key due to foreign key constraint. Hence in that case , output would be 2000.

Hence sum(min+max)=2000

selected by
+1 vote

Assume R1 has 3 tuples and R2 has 2 tuples

R1                               R2

A B C                       A D E

1 2 3                        1 8 9

1 4 5                        1 9 10

1 6 7

R1 NATURAL JOIN R2

A B C D E

1  2 3  8 9

1  2 3  9 10

1  4 5  8  9

1  4 5  9 10

1  6 7  8  9

1  6 7  9  10

So we have 3*2 = 6 tuples.

NOTE:- Since it is not mentioned that A is key so value of A can be repeated.

So now if R1 has 1000 tuples and R2 has 2000 tuples so R1 NATURAL JOIN R2 = 1000*2000 =2000000 tuples(max)=X

Minimum number of tuples= 0=Y(when all values of A in R2 is null). Value of A in R2 can be null as it is not mentioned that A in R2 is not null.

So X-Y= 2000000-0=2000000

Correct me if I am wrong

$X+Y$ rt?
max tuples should be 2000 and not 2000000 due to the foreign key constraint.
Foreign key constraint says that a child attribute must be present in the parent attribute. How it restricts the maximum tuples here?
Ya yaaa X+y sorry...

@arjun sir

lets say R1 and R2 are two tables where a in R2 references a of R1.

R1                          R2

a b  c                   a  d  e

1 2 1                    1 2 3

2 3 4                     2 4 5

1 4 6

R1 JOIN R2=

a b c d e

1 2 1 2 3

2 3 4 4 5

1 2 1 4 6

so the maximum tuples is 3.

Solving like this i.e taking a in r1 as  primary key and a in r2 as foreign key i got 2000 as maximum tuples in the above question.

yes, but Foreign key reference implies Primary key for the referred attribute, rt?
yeah right
yes bt in qstn it is not mentioned that A is primary key and since it is not mentioned and we need to find max number of tuples so we take 2000*1000
By default any attribute referred to by a foreign key must be unique making it a key. So, this need not be explicitly mentioned.
I think minimum can be zero becoz we have to take worst case condition if nothing is mentioned.....pls let me know if I am wrong