Geek Answers Handbook

Maximum and minimum number of tuples in a natural join

I came across a question that reads

Consider the following student relationship pattern

  • : Student ( rollno , name, address)
  • Sign up ( rollo, courseno , coursename)

where the main keys are shown underlined. The number of tuples in the Tables of students and enrollments is 120 and 8, respectively. What are the maximum and minimum number of tuples that may be present in (Student * Enroll), where '*' denotes a natural join?

I have seen several solutions on the web like this or this

In accordance with my understanding. maximum tuples must be equal to 8, and the minimum must be equal to 8, since for each (rollnum, course) there must be a number of student rolls. Anyone who can help in this regard

+7
sql join relational-database natural-join tuples
source share
4 answers

If there was a reference constraint to ensure that every role in Enroll should also appear in Student, then your answer of 8 for minimum and maximum would be correct. However, in fact, the issue is not mentioned in any such restriction. It is not necessary to assume that the RI constraint exists only because the rollno attribute appears in both tables. Therefore, the best answer is 0 minimum and 8 maximum. If a multiple choice question and 0.8 is not one of the answers given, then answer instead of 8.8 and tell your teacher that the question is unclear.

+3
source share

I hope you understand what Natural Join is. You can view it here .

If tables R and S contain common attributes and the value of this attribute is the same in each tuple in both tables, then the natural join will result in n * m tuples, since it will return all combinations of tuples.

Consider the 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

The result of the natural join R * S (if the attribute region C in the two tables is the 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 that both R and S contain an attribute C whose value is 2 in each and every tuple. Table R contains 2 tuples, Table S contains 3 tuples, where the result table contains 2 * 3 = 6 tuples.

In addition, when performing a natural union, if there were no common attributes between the two relations, the Natural union will behave as a Cartesian product . In this case, you will obviously have mxn as the maximum number of tuples.

Consider the 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

The result of the natural compound 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.

+4
source share

If you ask about the maximum number of tuples that can appear in a natural union of R and S, its Cartesian product of both tuples

0
source share

Yes, the answer should be 8.8. Because Rollno is the key in the Student and rollno tables, courseno are composite keys. The relationship between the student and the enrolled table is 1: M. Thus, the maximum number of tuples is the same as that of many parties, i.e. 8 And the minimum number of tuples is 8 if a foreign key exists except 0.

So the answer is 8.8.

0
source share

More articles:


All Articles