GATE CSE 2007 | Question: 59

Question

Information about a collection of students is given by the relation $\text{studInfo(}\underline{\text{studId}},\text{ name, sex)}$. The relation $\text{enroll(}{\text{studId}},{\text{ courseId}})$ gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?

$\pi _{courceId}\left(\left(\pi_{\text{studId}}\left(\sigma_{sex=“female"}\left(\text{studInfo}\right)\right) \times \pi_{courseId}\left(\text{enroll}\right)\right) -\text{enroll}\right)$

• A. Courses in which all the female students are enrolled.
• B. Courses in which a proper subset of female students are enrolled.
• C. Courses in which only male students are enrolled.
• D. None of the above

Comments

You can follow the naming GATEYYYY_NN for the question title (YYYY- year and NN question number) and it will show if the question is already there. Now all CS GATE papers from 1997-2014 are there. This question was already there, but I removed that.

Answer 1

The best approach is ..draw a table with 4-5 tuples and keep on doing what the query says ..at the end match the resultant table with the options i got option b

Comments

there is a high chance that this method will just waste tiime in exam.

Answer 2

What the cartesian product actually doing is, it taking the studentId of "female" student from studInfo relation, and performing the cartesian product with all courses that are available... so it will return the all possible combination of "female" student can enroll in various courses.. Now finally we are subtracting "enroll" from it.. "enroll" relation contain what actually enrollment has been done.. so the substraction will return the "courseId" a "female student has not been enrolled..!!

because, ( all possible enrollment in various course of female) - ( actual enrollment) = courses in which female enrollment has not happened!!...

Option (b) is saying "female student which are enrolled (proper Subset)".. No but it is returning  courses in which female student are NOT enrolled!!.. so option (D)

Answer 3

The expression given in question does following steps in sequence.
a) Select studids of all female students and selects all courseids of all courses.
b) Then the query does a Cartesian Product of the above select two columns from different tables.
c) Finally it subtracts enroll table from the result of above step (b). This will remove all the (studid, courseid) pairs which are present in enroll table. If all female students have registered in a courses, then this course will not be there in the subtracted result.
So the complete expression returns courses in which a proper subset of female students are enrolled.

studinfo table
studid   name    sex
------------------------
 1        a      Male
 2        c      Female 
 3        d      Female 

enroll table
studid  courseid
------------------
 1         1
 2         1
 3         1
 2         2 
 3         3
 3         2    


Result of step b
studid     courseid
---------------------
 2             1
 2             2
 2             3
 3             1
 3             2
 3             3  


Result of step c
studid    courseid
-------------------
 2           3
http://www.geeksforgeeks.org/database-management-systems-set-11/

Comments

There is a line under course ID  in enroll table ,it is correct or what it actually means

Answer 4

Ans should be B 

Comments

Let's consider the following example:

StudInfo table:

StudId    name    sex

11            A           M

12            B           M

13            C           F

14            D           F

 

enroll table:

studId   courseId

11         CSE

12         ME

13         CSE

14         ME

So this example gives the set of all courses taken by females. I understand there are many examples where a proper subset is generated out of the given algebra. So cannot option D be the correct answer?