GATE2004-14

Question

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?

• A. $8, 8$
• B. $120, 8$
• C. $960, 8$
• D. $960, 120$

Comments

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?

---

wht is the ans according to key?

---

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

---

If ,Rollno is not a F.K in Enroll then minimum would be 0, so from the options, A should be the answer(assuming Roll no is F.K in enroll).

---

What if they mention that no refrential integrity constraint violation

---

Here 2 cases are possible

Case 1:

Student table roll no contain 1 to 120 distinct number

but Enroll table roll number contain only value 1 in all 8 tuples

Here minimum as well as max tuples are 8

Case 2:

Student table roll no contain 1 to 120 distinct number

Enroll table roll number contain only value 1 to 8 distinct number

Here min as well as max no of tuples are 8

---

If 'roll no' is not given as primary key for student table then maximum 120 tuples would be there in natural join.

Answer 1

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.

Comments

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?

---

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.

---

Yes. That's correct.

---

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


 

---

Question assumes rollno on Enroll is a foreign key- which is clear from choices.

---

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?

---

rollNo field in enroll table can't be null because it's part of the primary key (rollNo, courseNo)

---

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?

---

FK is not mentioned in the question. Isn't it?

---

yes not told

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

---

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.

---

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.

---

@Manu Thakur If FK is not mentioned then how can you say max no of tuples 8?

max no of tuples possible is 120 right?

---

@Arjun sir,

Enroll (rollno, courseno, coursename)

in this table lets suppose ..(rollno,courseno) not forming key ..just rollno acting as foreign key to roll no in student ..then rollno in Enroll table may contain NULL in every row as it is FK..as it is not part of key..so in natural join no match hence MIN = 0..in that case ..right sir ?

Answer 2

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

Comments

In your minimum , if no match in roll number natural join will display cartesian product , rifht ?

---

in natural join if there is common attribute there and nothing is matched then no tupple will be selected but in natuaral join if there would be no common attribute then natural join act as cartesian product , but in above question common attribute is present but matching is not there

---

In Enroll table roll no is not primary key so  you are wrong and (8,8) is answer option A

Answer 3

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

Answer 4

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

Answer 5

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

Comments

for cartesian product there should be no common column but here it is given we are not matching tuples or rows...right??

---

in natural join if there is common attribute there and nothing is matched then no tupple will be selected but in natuaral join if there would be no common attribute then natural join act as cartesian product , but in above question common attribute is present but matching is not there

---

@saket_nandan i have understood what you told. and your solution seems to be correct but i would like to know what happens if Referential Integrity is not taken into consideration
PS : My answer above is NOT the solution for the GATE question