edited by
28,396 views
64 votes
64 votes

Consider the following relation schema pertaining to a students database:

  • Students (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?

  1. $8, 8$
  2. $120, 8$
  3. $960, 8$
  4. $960, 120$
edited by

7 Answers

Best answer
56 votes
56 votes

Rollno in students is key, ans students table has $120$ tuples, In Enroll table rollno is FK referencing to Students table. In natural join it'll return the records where the rollno value of enroll matches with the rollno of students so, in both conditions min and max records will be resulted $(8,8).$
hence A is the answer.

Hint: table which has non-key, no of records of that will be resulted.

edited by
23 votes
23 votes

for minimum

since , here, roll no is primary key is given for both the table not any clue about foriegn key is given so, we cant assume ROLL as foreign key.

suppose a condition:-

STUDENT                                                                

rollno(PK) name add
25 jan a
32 man c
42 pan b

 ENROLL

rollno(PK) name add
45 moh

a

32 soh b
64 poh c

then , if we apply natural join(*) hen no any tuples will be selected

so,min=0

now for aximum,

suppose a condition where all the 8 tuples in ENROLL is present then STUDENT(naturaljoin)ENROLL gives 8 tuplesbecause out of 120  tuples only 8 tuples will be matched

and, since , roll no. os student table is also PK then they must be differnt so maxm * tuples 

so, final answer must be 8,0

but, in option it is not given so, i think GATE question setter have missed out to mention refrencial integrity . if, RI would be there then it's answer will be 8,8

8 votes
8 votes

Some interesting variations can be asked in such question. I have discussed some of them below.

To ensure entity integrity, it is required that every table have a primary key(PK). Neither the PK nor any part of it can contain null values. 

Note that “rollno” in Student relation is primary key, So, it cannot contain Null values. Similarly, “rollno” and “courseno” in Enroll table cannot contain Null values. 

The number of tuples in the student and Enroll tables are 120 and 8 respectively.

Some Variations we can try in this question :

Case 1 :

If there was a referential constraint in place ensuring that every rollno in Enroll must also appear in Student (i.e. “rollno” of relation Enroll is assumed to be a foreign key referencing to the relation “Students”) :

1. 

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural join?

Answer : Maximum = 8 ; Minimum = 8

​​​​​​​2.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes Cartessian Product (aka Cross product or cross join) ?

Answer : Maximum = $120 \times 8$ ; Minimum = $120 \times 8$

3.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural left outer join join?

Answer : Maximum = 127 ; Minimum = 120

4.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural right outer join join?

Answer : Maximum = 8 ; Minimum = 8

5.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural full outer join join?

Answer : Maximum = 127 ; Minimum = 120

Case 2 :

The question doesn't actually mention any referential constraint constraint So there's no need to assume that the referential integrity constraint exists just because the rollno attribute appears in both tables. So,

If there is NO referential constraint in place ensuring that every rollno in Enroll must also appear in Student (i.e. “rollno” of relation Enroll is NOT a foreign key referencing to the relation “Students”) :

​​​​​​​1. 

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural join?

Answer : Maximum = 8 ; Minimum = 0

​​​​​​​2.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes Cartessian Product (aka Cross product or cross join) ?

Answer : Maximum = $120 \times 8$ ; Minimum = $120 \times 8$

3.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural left outer join join?

Answer : Maximum = 127 ; Minimum = 120

4.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural right outer join join?

Answer : Maximum = 8 ; Minimum = 8

5.

What are the maximum and minimum number of tuples that can be present in (Student $*$ Enroll), where $’*’$ denotes natural full outer join join?

Answer : Maximum = 128 ; Minimum = 120

4 votes
4 votes
nothing mentioned about foriegn key
we have two cases :
case 1)  Student(rollno) is foreign key but it is not possible becoz if it is foreign key ( it have 120 tupples) then parent relation (Enroll) should have atleast 120 tupples but Enroll have only 8 tupples
case 2)  Enroll(rollno) is foreign key and it is possible becoz parent table of it(Student) have atleast 8 tupples)

now come to question
Maximum and Minimum no of tuples are (8,8) becoz in Child relation (Enroll) we have 8 tuples and only those 8 tuples are join with parent relation (Students)

correct me if wrong :)
Answer:

Related questions

42 votes
42 votes
4 answers
1
0 votes
0 votes
0 answers
4
aditi19 asked Nov 2, 2018
297 views
in case of outer join for combining two tables, if there is no common attribute does it returns cartesian product just like natural join does?