The Gateway to Computer Science Excellence

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

- Courses in which all the female students are enrolled.
- Courses in which a proper subset of female students are enrolled.
- Courses in which only male students are enrolled.
- None of the above

+56 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 .

+24

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

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)

+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???

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

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

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?

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????

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

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

**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

@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

@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

**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

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.

0

Statement 1: course id in which not all girl students enrolled.

Statement 2: i.e. proper subset of girls student appeared..

how are both statements equivalent?

Statement 2: i.e. proper subset of girls student appeared..

how are both statements equivalent?

0

What will be the answer in the following case?

StudentInfo

1 | A | M |

2 | B | F |

3 | C | F |

CourseInfo

1 | C1 |

1 | C2 |

2 | C1 |

3 | C2 |

The given two tables satisfy the condition of at least one boy and at least one girl has been enrolled in a course.

Now if we apply the query provided int he question we'll get {C1, C2} as the output, which is not a proper subset of {C1,C2}.

SO, answer should be option D.

+25 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

+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??

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.

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

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

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

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?

+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.**

+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

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

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)

+3 votes

The expression given in question does following steps in sequence.

a) Select *studids* of all female students and selects all *courseid*s 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/

- All categories
- General Aptitude 1.9k
- Engineering Mathematics 7.6k
- Digital Logic 2.9k
- Programming and DS 4.9k
- Algorithms 4.4k
- Theory of Computation 6.2k
- Compiler Design 2.1k
- Databases 4.1k
- CO and Architecture 3.4k
- Computer Networks 4.2k
- Non GATE 1.4k
- Others 1.5k
- Admissions 595
- Exam Queries 573
- Tier 1 Placement Questions 23
- Job Queries 72
- Projects 18

50,832 questions

57,686 answers

199,271 comments

107,196 users