Minimum and Maximum number of Tuples in the Following Relations

Question

Given R with n tuples S with m tuples n<m  then How many minimum and maximum tuples in follwing relations . Please Justify with Reason / Examples

• R-S
• S-R
• R Left Join S
• R  Natural Join S
• R/S
• S/R

Comments

I) R-S=n(maximum).  // if no tuple in R matches with tuple in S.

      =0(minimum)  // all tuples matches.

II) S-R=m(maximum)

            =0(minimum)  // same reason as above.

III) R Natural join S=0(minimum) // no tuple matches.

                             =(maximum)we cannot tell, it depends on relations.

IV) R left join S=n(minimum)  

                       =(maximum) we cannot tell,it also depends on relation.

---

Can u plz exaplin it with an example how it depends on relation ?

What happens in R/S and S/R case also ?

---

@santhoshdevulapally,

R natural join S is m*n tuples in maximum , rt ?  (cartesican product when no tuples matches )
 minimum is n

---

@pc

when no tuples match ,natural join retruns empty relation.(it is equality condition)

---

@Dq

R

A	B
1	2
1	3
2	1

S

A	C
1	3
1	4
2	1

R NATURAL JOIN

A	B	C
1	2	3
1	2	4
1	3	3
1	3	4
2	1	1

CONSIDER ANOTHER RELATION

R

A	B
1	1
1	3
2	1

S

A	C
1	2
2	1

R NATURAL JOIN S

A	B	C
1	1	2
1	3	2
2	1	1

---

@santhoshdevulapally . Nope that is wrong !

If there are no attributes in common between two relations and you perform a natural join, it will return the cartesian product of the two relations

see

• http://stackoverflow.com/questions/14548543/natural-join-if-no-common-attrubute
• http://stackoverflow.com/questions/22673235/maximum-and-minimum-number-of-tuples-in-natural-join

---

@pc,see this one.

Natural Join (⋈)

Natural join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain.

Natural join acts on those matching attributes where the values of attributes in both the relations are same.

https://www.tutorialspoint.com/dbms/database_joins.htm

---

@santhoshdevulapally,
This is true only  when there is key constraint on both relation. Here the question is maximum . So we should go for the worst case .  
see
 https://en.wikipedia.org/wiki/Join_(SQL)#Natural_join

---

Confused ! What is correct one ? :(

---

@pc,if no tuple is common then we go for cartesian product.am i right??

---

Exactly what I have told above. Even shared few References :)

Answer 1

[Someone should verify . This is what I think]

1. R UNION S

• max : n+m
  Reason : union we add all the tuples from both relations. ie When R and S have no common tuple.

• min: n
  Reason : The minimum is n (the greatest of the two sizes, m and n). When all the tuples of R also exist in S.

2. R INTERSECTION S

• max : m ( m<n )
  Reason : both relation contains same tuples then we may get maximum m keys

• min: 0
  Reason : taking m=n=null if no common tuples in both relations

3. R - S

• max : m 
  Reason : if they are disjoint then in R-S we will get all tuples of R

• min: 0
  Reason : if all tuples in R is also present in S
   

4. S - R

• max : n 
  Reason : as explained above
   

• min: n-m
  Reason : m<n  there will be some tuples in S after deleting the common tuples

5. R natural join S

• max : n*m
  Reason :  if no matching key constraints natural join will produce Cartesian product )
   

• min:  0
  Reason : Identical with case 2 (INTERSECTION).

6. R LEFT OUTER JOIN S

• max : m*n
  Reason : if all rows in left tables matches with all rows in right table

• min: m  (could be 0 when m= 0)
  Reason : The minimum is 1 when m=1 , minimum is 2 when m=2, minimum is 0 when m=0

7. R / S

• max : m 
  Reason :   when n=0

• min: 0
  Reason :  Consider that relational division is similar to integer division. 3 / 7 gives 0 in integer division for example. Try to convert this into relational division

Comments

For 7th point, if m=0 then R / S  has zero tuples. Isn't it?

---

6.max of left natural join

How will all rows of left table match with all of right?

---

For natural join

mn is max.

Case 1: if there is a common attribute between and , and every row of R matches with the each row of S - i.e., the join attribute has the same value in all the rows of both and ,
Case 2: If there is no common attribute between R and S .

Min is 0.

If There is a common attribute between R and S and nothing matches.