sql - maximum and minimum number of tuples in natural join

Question

I came across a question that states

Consider the following relation schema pertaining to a students

• database: Student (rollno, name, address)
• Enroll (rollno, courseno, coursename)

where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?

I have seen several solutions on Internet like this or this

As per my understanding. maximum tuples should be 8 and minimum should be 8 as well, since for each (rollnum,course) there should be a roll num in Students. Anyone who can help in this regard

Answer 1

I hope, you understood what Natural Join exactly is. You can review here.

If the tables R and S contains common attributes and value of that attribute in each tuple in both tables are same, then the natural join will result n*m tuples as it will return all combinations of tuples.

Consider following two tables

Table R (With attributes A and C)

 A  |  C
----+----
 1  |  2
 3  |  2

Table S (With attributes B and C)

 B  |  C
----+----
 4  |  2
 5  |  2
 6  |  2

Result of natural join R * S (If domain of attribute C in the two tables are same )

 A | B |  C
---+---+----
 1 | 4 |  2
 1 | 5 |  2
 1 | 6 |  2
 3 | 4 |  2
 3 | 5 |  2
 3 | 6 |  2  

You can see both R and S contain the attribute C whose value is 2 in each and every tuple. Table R contains 2 tuples, Table S contains 3 tuples, where Result table contains 2*3=6 tuples.

Moreover, while performing a natural join, if there were no common attributes between the two relations, Natural join will behave as Cartesian Product. In that case, you'll obviously have m x n as maximum number of tuples.

Consider following two tables

Table R (With attributes A and B)

 A  |  B
----+----
 1  |  2
 3  |  2

Table S (With attributes C and D)

 C  |  D
----+----
 4  |  2
 5  |  2

Result of natural join R * S

 A | B |  C |  D
---+---+----+----
 1 | 2 |  4 |  2
 1 | 2 |  5 |  2
 3 | 2 |  4 |  2
 3 | 2 |  5 |  2

Hope this helps.