Maximum and Minimum Number of Tuples in Natural Join

maximum and minimum number of tuples in natural join

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.

what is maximum number of tuples in natural join

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.

Hope this will help.

Maximum number that can be returned by 2 Natural Joins

A natural join on tables that have no rows in common is in fact a cross join as you so rightly suppose. You'll get A * B * C = 800 * 200 * 500 = 80,000,000 rows.

Once the tables have columns in common a filter takes place. Depending on whether there are matches and how many, you get anything from 0 to 80,000,000 rows. Examples:

• If all tables have one column in common and its value is the same in every row in every table, you end up with all combinations again.
• If all tables have one column in common and its value is 'A' in all rows in table A, 'B' in all rows in table 'B' and 'C' in all rows in table C, you end up with no matches, i.e. zero rows.

After all, this all is dull theory, because nobody in their right mind would ever use a natural join :-)

Find the Cardinality of Natural Join

Presuming the bold A in each schema means it is a key; and presuming the Foreign Key constraint holds -- that is, the A value for every row in R does correspond to an A value in S:

• Every row in R naturally joins to a row in S on A.
• There might be rows in S that don't join to R (because there's no Foreign Key constraint to enforce that).
• So the cardinality of the joined relations is the cardinality of R, answer 1.

Is there are real-life use for a schema like this? Consider S is Customer Name in C, keyed by Customer number in A. R holds date of birth in B, also keyed by Customer number in A. Every Customer must have a name; it's true every Customer (person) must have a d.o.b., but we don't need to record that unless/until they purchase age-restricted items.

Why can't I compare 2 tables with Different number of Tuples in Relational?

For you to be able to use the normal set operators (- ∪ ∩) on relations, they need to be the same "kind" of relation. That is, they need to have the same columns.

To achieve that, you need to use projection and rename.

So if you have a relation A with fields id, name and you want to do a union with a relation B which has id, first_name, last_name you need to do: A ∪ πid, name (ρ first_name ➡name (B))

---

Related Topics