6

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

  • Your first link of solutions requires authentication to view the answer. – crthompson Mar 26 '14 at 21:21
  • @paqogomez it states "A natural join over two sets, returns only those tuples in which the common attribute between the two tuples match. Here the common attribute is rollno. Since there are only 8 tuples in the Enroll table, the maximum number of tuples in the natural join of Student and Enroll cannot be greater than 8. That will be the case where each roll no in the Enroll table is also present in the Student table. And the minimum number of tuples in their natural join is 0, where there is not a single common roll no between the two tables." –  Mar 26 '14 at 21:24
  • Well you are right! `Enroll` may either contain data about min 1 student or about max 8 students. A natural(inner) join will always result in 8 rows as `roll no` being referenced in `Enroll`. – KrazzyNefarious Mar 26 '14 at 21:30
  • min =8 and max=8 is the right answer i guess @BhupeshC, just wondering how so many people are ginving different answers on internet –  Mar 26 '14 at 21:33
  • 2
    @user1765876, being a *composite key*, you cannot insert `null` in `roll no` and cannot insert any other data apart from what you have in `Student`. So min = 8 and max 8. – KrazzyNefarious Mar 26 '14 at 21:35

4 Answers4

7

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.

Subash
  • 301
  • 3
  • 9
5

If there was a referential constraint in place ensuring that every rollno in Enroll must also appear in Student then your answer of 8 for both minimum and maximum would be correct. The question doesn't actually mention any such constraint however. There's no need to assume that the RI constraint exists just because the rollno attribute appears in both tables. So the best answer is 0 minimum and 8 maximum. If it's a multiple-choice question and 0,8 isn't one of the given answers then answer 8,8 instead - and tell your teacher that the question is unclear.

nvogel
  • 24,981
  • 1
  • 44
  • 82
-1

If you are asking about the maximum number of tuple that could appear in the natural join of R and S the its the Cartesian product of both the tuples

Pravin Janjal
  • 15
  • 4
  • This is not clear. Do you mean, *the number of rows in* the Cartesian product of both *tables*? If so, you are still not clear about what you mean by "Cartesian product". Whatever you mean it is almost certainly wrong because since there are common columns the natural join can have fewer tuples. – philipxy Dec 22 '18 at 03:31
-1

Yes answer should be 8,8 . Because Rollno is key in Student table and rollno,courseno are compound key . Relationships between Student and enrol table is 1:M . So maximum number of tuples is same as many side ie. 8 And minimum number of tuples is 8 if Foreign key exist other wise 0.

So answer is 8,8 .

A K Singh
  • 1
  • As the accepted answer points out, the question does not say that "Relationships between Student and enrol table is 1:M" [sic] so you are assuming it without justification. Moreover, that is *the same thing* as "[a] Foreign key exist[s]" but you write as if it isn't. Also, how does this answer add that is not in the other answers to make it "useful" (per the voting arrow mouseover texts)? – philipxy Dec 22 '18 at 03:31