4.5k views

Information about a collection of students is given by the relation $\text{studInfo(}\underline{\text{studId}},\text{ name, sex)}$. The relation $\text{enroll(}\underline{\text{studId}}, \underline{\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)$

1. Courses in which all the female students are enrolled.
2. Courses in which a proper subset of female students are enrolled.
3. Courses in which only male students are enrolled.
4. None of the above
edited | 4.5k views
0
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.

STUDENTINFO
$1$     $A$       $M$
$2$     $A$       $F$
$3$     $A$       $F$

ENROLL

$1$       $C1$
$1$       $C2$
$2$       $C1$
$2$       $C2$
$3$       $C2$

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

$2$                   $C1$
$*$
$3$                   $C2$

$=$

$2$        $C1$
$2$        $C2$
$3$        $C1$
$3$        $C2$

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

$3$      $C1$

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

$C1$..
$C1$ is course id in which not all girl students enrolled.
i.e. proper subset of girls student appeared..

Hence (B) is the correct answer .

edited by
+13
$\left(\pi_{\text{studId}}\left(\sigma_{sex="female"}\left(\text{studInfo}\right)\right) \times \pi_{courseId}\left(\text{enroll}\right)\right)$
here we are cross multipling each female student with each course.

By subtracting it from actual enroll table we r getting the female student who are not actually enrolled for that course.

So some other female student must enroll for that course.

i.e. proper subset of female student

(Answer must be atleast 1 female enrolled for each course as declared in question itself , no female student in any course is not possible here)
+2
What if
2         C2
Is not in enroll table?
+1
But in question It is clearly mentioned that "every course is taken by at least one male and one female  student".

So In ur ENROLL table  , ( 3    C1)  should be already present. Isn't it???
0

"every course is taken by at least one male and one female  student"

Yes. C1 is already opted by 1(male) and 2(female). So, 3 C1 combination is not mandatory.

0
nice explanation
0
OPTION B) may be wrong in some cases:

if   '2 c2'  is not in the enroll table then final result will contain both 'C1' and 'C2' then it is not a proper subset of courses female student enrolled then option B) will be incorrect .please explain me if i go wrong
+1
What if STUDENT_INFO table has a entry corresponding to a female student who haven't enrolled in any course.

As per the question its not mandatory that every female student should have enrolled in atleast one course.

Then the result would have every course id in enroll table. Am i right?
0
Very Nice explanation,I understand this clearly but in enroll table both attribute are prime attribute then can we put duplicate values in that?????

yes,To met the given condition of the question we've nothing to do & also the example is appropriate, but this doubt comes in my mind after seeing this. Am I right or wrong????
0
it is in enroll table thats why it is removed
0

If  ϕ is proper subset

How answer is C which is "Courses for which only proper subsets of female enrolled

So it can be also in output "Courses for which no girl enrolled"

But in question it is given that

every course is taken by at least one male and at least one female student

Ans is b,

First it does a cross join between female students id and all course ids,  then subtract the entries which are already present in enroll table.

Remaining are the courseids which are NOT done by at least one female student

+2
Result of minus will be a relation containing id of female student with courses they have not enrolled .(i.e course id's are of courses in which  female students are not enrolled)

Can you please explain where i am wrong??
+4
Consider a course id c1

Which is enrolled by all female students, when you apply minus this will not appear in the result,  only those courses which are not done  by all of the female students will appear.
0

I totally agree with your logic on what the query is doing but how does that make the ans B ? Can you please explain "Courses in which a proper subset of female students are enrolled." in simple terms ?

0
But here, there could be a particular course say C1 which is opted by only male students. And the output of the query would be all female students with C1.

Thus the answer is not proper subset of female students enrolled for a particular course.

It would be all the courses, for which a particular female student has not enrolled

Ans is B,

1. (Female students id) Natural JOIN (All course ids in which at-least one student enroll i.e. taken from enroll),

2. Subtract the entries which are already present in enroll table. ( which are nothing but in which female enrolls).

Remaining are the tupple in which female student are not enroll for every course (i.e. female students with atleast one course in which she not enroll actually) which is always proper subset of all enrolled course.

0

@Bikram look at here.

+1

@saket   you missed out something ..lets take an example...

 studid name sex 1 a f 2 b f 3 c m 4 d m

here if apply inner most query which gives studid whichhave sex =female so we get id no {1,2).,now cross product between {1,2}and course id {c1,c2} which gives four tuple

 stuid course id 1 c1 1 c2 2 c1 2 c2

enroll table

 stdid course id 1 c1 1 c2 3 c1 4 c2 2 c1

now we have to perform intermediate table which gives crosse product of female id and course id - enroll which gives {2,c2} so c2 is course id which is not enrolled by id no 2 female so we can se option b is correct...

now c is wrong becoz each course should be enrolled by atleast one m and f. and option a will be correct if we get c1 course id which is enrolled by all female but we get course id c2 from above query so only option b is correct...

edited by
0
Thnks for such an awesome explanation 　 :)
0
maje le raha hai :(
0
he he...kisi ne downvote bhi kiya coment ...tumne hi kiya hoga shayad :P
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
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)

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/

edited by
0

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

Ans should be B

As simple as possible, Thanks :)

proper subset means subset not equal to set itself.......so above relational algebra is going to give all courses that "all girls have not taken"

1
2