The Gateway to Computer Science Excellence

First time here? Checkout the FAQ!

x

+26 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?

- 8, 8
- 120, 8
- 960, 8
- 960, 120

Nowhere it is mentioned that Enroll.rollno is a FK to Student. So if there are 8 distinct rollno and all are present in student table then max is 8 while for min there may not be any rollno matching thus 0. Had it been mentioned FK Enroll.rollno to Student.rollno then both would have been (max,min) = (8,8).

Since not mentioned ans should have been (8,0). But no options match. May be a typo in the question!

Isn't it?

Since not mentioned ans should have been (8,0). But no options match. May be a typo in the question!

Isn't it?

Ans is A) only. In the question, the above, what I have mentioned, is not present but it is assumed.

+28 votes

Best answer

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.

hence A is the answer.

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

Students:

100 alen delhi

101 boby chennai

Enroll:

100 A101 DBMS

100 A102 Compilers

100 A103 Algorithms

101 A101 DBMS

101 A102 Compilers

101 A103 Algorithms

Now, Students $\Join$ Enroll will give how many tuples?

100 alen delhi

101 boby chennai

Enroll:

100 A101 DBMS

100 A102 Compilers

100 A103 Algorithms

101 A101 DBMS

101 A102 Compilers

101 A103 Algorithms

Now, Students $\Join$ Enroll will give how many tuples?

Students has primary/unique Key and two records 100 and 101. Enroll has FK that is not unique so join will return(non key side records) 6 records.

So, the same should happen in the given question rt? Max number of tuples must be 120 and not 8, rt?

No, because Student(PK) table has 120 records, but Enroll(FK) has 8 records means in Enroll May be single student is enrolled for 8 different subjects or 8 different students are enrolled for 8 different subjects. PK will match with only 8 records of Enroll therefore 8 records will be returned here only. same reason as explained above.

What if the db is :

Students:

103 alen delhi

104 boby chennai

Enroll:

100 A101 DBMS

100 A102 Compilers

100 A103 Algorithms

101 A101 DBMS

101 A102 Compilers

101 A103 Algorithm

A foreign key can have null value or can refer to the primary key of parent table.What will happen if all the values in child table contain null i.e enroll contains only null?Can we consider such a case?

Who says roll_no in Enroll is a foreign key.(no where mentioned).

What if Student table's roll_no attribute has values from 1 to 120 and in Enroll Table it is from 121 to 128 ?

Then natural join gives $0$ rt?

What if Student table's roll_no attribute has values from 1 to 120 and in Enroll Table it is from 121 to 128 ?

Then natural join gives $0$ rt?

yes not told

but primary key of student refer by enroll table

So, roll no of enroll table must be foreign key

but primary key of student refer by enroll table

So, roll no of enroll table must be foreign key

primary key of student may contain 120 different values

but only maximum 8 different values key values of foreign key of enroll tuple matches with it, so maximum 8 tuple will be ans

Here filtering is done w.r.t. foreign key

but only maximum 8 different values key values of foreign key of enroll tuple matches with it, so maximum 8 tuple will be ans

Here filtering is done w.r.t. foreign key

Option (8,0) is not mentioned in the question, hence we can assume that FK is there, though it is not mentioned explicitly in the question, so answer is (8,8)

If (8,0) was given an option, (8,0) is correct answer as it is not mentioned that FK is there.

If (8,0) was given an option, (8,0) is correct answer as it is not mentioned that FK is there.

how (8,0)?

student must be enrolled?

without this what enroll table exactly referring with it's key " rollno, courseno "?

And foreign key cannot stay without it's primary key. rt?

So, (8,8) will be ans(if there is no option too)

rt?

think slowly :)

@srestha

What you are saying is functionally correct! but not technically. if someone argues based on the technical grounds then (8,0) is a possible correct answer as it's **not mentioned** in the question that rollno in enroll table is referencing the PK of the student table.

As it's students database so we are assuming that referential integrity is there, but until it is mentioned explicitly in the question someone can challenge it.

+12 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

+3 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 :)

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 :)

–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...

- All categories
- General Aptitude 1.2k
- Engineering Mathematics 4.7k
- Digital Logic 1.9k
- Programming & DS 3.5k
- Algorithms 3k
- Theory of Computation 3.7k
- Compiler Design 1.5k
- Databases 2.8k
- CO & Architecture 2.5k
- Computer Networks 2.9k
- Non GATE 837
- Others 1.2k
- Admissions 278
- Exam Queries 396
- Tier 1 Placement Questions 17
- Job Queries 50
- Projects 7

33,687 questions

40,231 answers

114,271 comments

38,801 users