GATE | GATE-CS-2004 | Question 90

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 ?
(A) 8, 8
(B) 120, 8
(C) 960, 8
(D) 960, 120


Answer: (A)

Explanation: The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.

What is the maximum possible number of tuples?
The result of natural join becomes equal to the Cartesian product when there are no common attributes. The given tables have a common attribute, so the result of natural join cannot have more than the number of tuples in larger table.

        Student
-------------------------------------
Rollno          name         Address
-------------------------------------
1                a             abc
2                b             bcd
3                c             cde



                   Enroll
----------------------------------------------
Rollno           Courseno          Coursename
----------------------------------------------
1                 12                 pqr
1                 14                 qrs
1                 15                 rst 
2                 13                 uvw 
2                 14                 qrs 

Result of natural join
----------------------------------------------
Rollno  Name   Address   Courseno  Coursename
----------------------------------------------
1        a      abc         12      pqr
1        a      abc         14      qrs
1        a      abc         15      rst 
2        b      bcd         13      uvw
2        b      bcd         14      qrs 

What is the minimum possible number of tuples?
It might be possible that there is no rollno common. In that case, the number of tuples would be 0. But in the question rollno is a primary key in Student table and it is a prime attribute in the Enroll table.

So there is no chance of having null values in the rollno column in enroll table and Student table and every tuple in Enroll will have a parent. So minimum number of tuples possible are 8 (only 8 tuples in the Enroll table).

Option (A) is correct.

Quiz of this Question



My Personal Notes arrow_drop_up

Improved By : krishnaswamy



Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.