edited by
20,317 views
97 votes
97 votes

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
edited by

11 Answers

Best answer
87 votes
87 votes

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

edited by
28 votes
28 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

1 flag:
✌ Edit necessary (akashkumarsen4 “It says "Remaining are the courseids which are NOT done by at least one female student" but it is already given in the question that "Assume that every course is taken by at least one male and at least one female student."”)
12 votes
12 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...

edited by
12 votes
12 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.

Answer:

Related questions