edited by
28,395 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

1 votes
1 votes
No foreign key :

No tuples matching, Minimum tuples = 0

Maximum tuples = 8, as rollno in student is a primary key, it contains distinct values.

Foreign key :

All tuples have Null values in roll no field of Enroll, Minimum - 0

Maximum - 8

So, the best option that matches the answer is a) 8,8
0 votes
0 votes

Ans: 8 records

Justification:

  • There are 120 distinct Rollno in the students table.
  • There are 8 unique <Rollno, courseno> pairs in the enroll table.

Thinking about worst and best cases:

  • Min there can be 1 Rollno, 8 distinct courses.
  • Max there can be 8 distinct Rollno, and same/diff courses, 1 for each Rollno.
  • Notice that irrespective of distribution of Rollno.s; after join, we still end up with 8 records only

This observation can be extended to conclude that whenever we join 2 tables where table $T_1$’s PK is FK in $T_2$, and is a part of $T_2$’s PK; then the number of records after natural join stays the same as number of records of $T_2$.

–3 votes
–3 votes

If refertial Integrity constraints are not taken into cinsideration

a) Natural Join produce Cartesian product when there is no match, right ? '
Will natural join ever produce 0 tuples ? if so how

B) Minimum is when Single studnet is enrolled for 8 different courses = 8

C) Maximum is when no tuples matches anhd returns cartesian product  =8*120=960
 

MIN and MAX ( 8,960)
 

correct me if wrong...

edited by
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?