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

(All female,courseId pairs - enroll) => when you know that every female is enrolled atleast in anyone of the course then if we subtract enroll from all female,courseId pairs then we get those pairs where female has not enrolled in that course.

now if we separate courseId from it then it gives all the courses which is taken by some female and not taken by other females, so it means these are courses where not all female enrolled or these are courses in which a proper subset of female students are enrolled.

---

The closest option is B

---

• Not taken by at least one is confirmed: Because that's exactly what survives the subtraction.
• Taken by at least one is confirmed: Because the problem explicitly tells us every course has at least one female student.
• Since it's at least one but not all, it must be a proper subset (Option B)

Answer 1

$$\overset{\underline{\text{STUDENTINFO}}}{\begin{array} {c c c } 1 & A & M \\ 2 & A & F \\ 3 & A & F \end{array}} \qquad \qquad \overset{\underline{\text{ENROLL}}} {\begin{array} {c c } 1 &  C1 \\ 1 & C2 \\ 2 & C1 \\ 2 & C2 \\ 3 & C2 \end{array}}$$

• $\pi _{courceId}(\sigma_{sex=“female"}(\text{studInfo} ))\times \pi_{courseId}\left(\text{enroll}\right)$ $$\implies \begin{array} {c c } 2 &  & C1 &  \\  & \ast  &  \\ 3 &  & C2  \end{array} = \begin{array} {c c } 2 &  C1 \\ 2 & C2 \\ 3 & C1 \\ 3 & C2  \end{array}$$
• $(\pi _{studId}(\sigma_{sex=“female"}(\text{studInfo})) \times \pi_{courseId}(\text{enroll})) -\text{enroll})$ $$\implies \begin{array} {c c } 3 &  C1 \end{array}$$
• $\pi _{courceId}((\pi _{studId}(\sigma_{sex=“female"}(\text{studInfo})) \times \pi_{courseId}(\text{enroll})) -\text{enroll})$ $$\implies C1$$

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

Hence (B) is the correct answer.

Comments

At last there is (stId,cID) which we select cID from
the sID that cID is assosiated with is not enrolled for that course
but atleast one female must enroll for that course
so it results in a proper subset of females who are enrolled for that course

---

right right got it thanks

---

this is really helpful thanks

Answer 2

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

Comments

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.

---

"Assume that every course is taken by at least one male and at least one female student" 
That case not possible

---

Courses in which a proper subset of female students are enrolled., meaning that courses in which alteast one female student isn't enrolled. 
 

Answer 3

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.

Comments

@Bikram look at here. 

---

yes, your answer is correct .

Answer 4

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

Comments

Thnks for such an awesome explanation 　 :)

---

maje le raha hai :(

---

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

Answer 5

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 6

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)