The Gateway to Computer Science Excellence
+52 votes
5.3k views

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

  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
in Databases by Active (3.3k points)
edited by | 5.3k views
+1
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.

11 Answers

+50 votes
Best answer

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 .

by Veteran (60.4k points)
edited by
+18
$\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
+2
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

0

@Digvijay Pandey How can you say that, always you get a proper subset? You might get the same Set of all female students as well by taking an example, where no course is allowed to have all female students but have atleast one female student (acc. to question). This will output 

  • All the CourseId's, and
  • All the Female Students

And, by definition a Set can be a Subset of itself but not a Proper Subset. 

0

@ayushsomani

Can you explain how you got by taking an example?

0

@Lakshman Patel RJIT @Digvijay Pandey

Consider a situation, where 

  • we have atleast one female student in each course (acc. to question)
  • And, not all female students are enrolled in any course

You can take the Selected Answer example (by Digvijay Pandey). Remove the tuple (2 C2). Now, solve the problem. 

0

Given that: Assume that every course is taken by at least one male and at least one female student.

StudInfo

studId name sex
1 A M
2 B F

enroll

studId courseId
1 C1
2 C1

Final output is$:\phi$

No, one is enrolled in this instance.

   (OR)

StudInfo

studId name sex
1 A M
2 B M
3 C F
4 D F

enroll

studId courseId
1 C1
1 C2
1 C3
3 C1
4 C2
4 C3

Final output is$:C1,C2,C3$

Courses in which all the female students are enrolled $({\color{Red} {Wrong}})$.

Because $3$ does not enroll $C2,C3$ Courses and $C4$ does not enroll in $C1$ Course.

So, option $(A)$ is wrong and option $(C)$ is also wrong.

Now check for option $(B)$

StudInfo

studId name sex
1 A M
2 B F
3 C F
4 D F

enroll

studId courseId
1 C1
1 C2
2 C1
3 C1
4 C2

These are the girls, who do not enroll the course.

$(2, C2),(3, C2),(4, C1)$

Final output is$: C1, C2$

So, the proper subset of girls has enrolled the course $C1$ and $C2$.

Option $(B)$ is the correct answer.

But taking only one or two examples does not make any statement correct, so we should think in a logical way also.

+1

@Lakshman Patel RJIT Exactly, you will get these tuples (3 C2, 3 C3, 4 C1) before projecting CourseId's. Female Set (3 , 4) and in Output, we get (3 and 4). 

0
Yes here courseId is $C1,C2,C3$
0

@Lakshman Patel RJIT Courses in which proper subset of female students are enrolled

Set of female students who are enrolled - {3,4}

Proper subsets of this Set are - { ϕ, {3} , {4}}

What we got - {3,4}. Then, how we can say this holds true.

 

0

@Lakshman Patel RJIT

We could have selected Option (D). 

0

@srestha Ma'am    @ayushsomani

My above example is counterexample for option (B).

0

@Lakshman Patel RJIT Oh sorry, My bad.

So, you think Option (D) should be the correct option. 

0

@srestha Ma'am

I got it now.

@ayushsomani

Please check my above comment.

0

What is proper subset of {2,3,4}?

  • Set of girls enrolled in the Course - {2,3,4} (C1 and C2 are Courses in which they were enrolled).
  • You, yourself getting Output as (2 C2), (3 C2) and (4 C1).

@Lakshman Patel RJIT No way, this option is correct.

0

@ayushsomani

There are two courses $C1, C2$  and there are three girls $\{2,3,4\}$

$'2'$ is enroll in $C1$ not in $C2$

$'3'$ is enroll in $C1$ not in $C2$

$'4'$ is enroll in $C2$ not in $C1$

Final result is $:(2,C2),(3,C2),(4,C1)$

Project the CourseId and get $C1,C2$.

Now we can say that $C1$ is the course which takes only $\{2,3\}$.

So, it is $\{2,3\}\subset \{2,3,4\}.$

and the $C2$ is the couse which take only $\{4\}$. 

$\{4\}\subset \{2,3,4\}$

So, the correct answer is $(B)$.

I hope it is clear now, if not then rethink again, you will get it.

+24 votes

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

by Loyal (8.2k points)
+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
0
I am convinced that the result is "list of courses in which a female student has not enrolled". How is this equivalent to option B.
+9 votes

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

by Boss (17k points)
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
0

Q1. What if in the question it is not given that each course is taken by at least one female student but it is given that there is at least one female student in StudInfo relation?

Q2. What if in the question it is not given that each course is taken by at least one female student?

Q3: What if Studid is not a key in the Studinfo table?

@minal

+9 votes

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.

by Veteran (62.6k points)
0

@Bikram look at here. 

+1
yes, your answer is correct .
+5 votes
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
by Boss (14.4k points)
0
there is a high chance that this method will just waste tiime in exam.
+4 votes
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)
by Active (5k points)
+3 votes

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/
by Active (5k points)
edited by
0

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

0 votes

Ans should be B 

by (445 points)
Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,654 questions
56,166 answers
193,872 comments
94,262 users