#dbms RELATIONAL ALGEBRA QUESTION

Question

Information about a collection of students is given by the relation studInfo (studId, name, sex). The relation enroll (studId, courseId) gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by EXACTLY one male and EXACTLY one female student. What does the following relational algebra expression represent?

πcourceId((πstudId(σsex="female"(studInfo))×πcourseId(enroll))−enroll)

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

Answer 1

STUDINFO

StudID	Name	Sex
S1	A	Male
S2	B	Female
S3	C	Female

Enroll

StudID	CourseID
S1	C1
S2	C1
S1	C2
S3	C2

ALGEBRA : =  π  _courseID ( ( π _studID (σ _sex="female" ( STUDINFO )) × π _courseID ( enroll )) − enroll )

From  the highlighted part  : We are taking Cartesian product of all FEMALE STUDENTS from STUDINFO table with all COURSES in Enroll Table.

The table will look like:

Cross Product

StudID	CourseID
S2	C1
S2	C2
S3	C1
S3	C2

Then We are subtracting Enroll from above table. 

Note: Subtraction of Table P with Table Q contains all the tuples which are in P but not in Q. Example: P = {1,2,3} and Q = {2,3,6}  then P - Q = {1}

The resultant Table from Cross Product  MINUS  Enroll will look like:

Result

StudID	CourseID
S2	C2
S3	C1

Now we are selecting CourseID from above Table

Selected Courses : C1 and C2 

Option A) Courses in which all the female students are enrolled =  False because only S2,S1 joined C1 and only S3,S1 joined C2 where S1 is Male.

Option B) Courses in which a proper subset of female students are enrolled.   = TRUE

Option C) Courses in which only male students are enrolled.= False because S2 along with S1 also joined C1 and S3 along with S1 also joined C2

Option B is correct.

Comments

@Kamal Pratap

How is c1 and c2 makes proper subset?

also how is option a false because they are not saying that courses in which "only" all females are enrolled.

and in c1 and c2 both females are covered and we don't care about any males as long as all females are included.

---

Option B says Courses in which a proper subset of female students are enrolled.

C1 course is enrolled by S1 and S2 but not S3, this means from the set of female students = {S2,S3} only S2 enrolled C1. That is proper subset of female students has enrolled in a course.

C2 course is enrolled by S1 and S3 but not S2, this means from the set of all female students = {S2,S3} only S3 enrolled C2 and S2 is proper subset of female students which has enrolled in a course.

Option A says: Courses in which all the female students are enrolled.

They are asking the particular course which is enrolled by all females (Male may or may not be). but that is not true in actual only 1 female and 1 male student has selected a course. Then obviously how all females can enroll in 1 course.

The context is different. You are thinking S2 and S3 has joined in some course so all females have enrolled in some course but they are asking that particular course where S2 and S3 both have enrolled but according to the condition given in question it cant be possible.

---

@Kamal Pratap Yes, thank you for awesome explanation! 

What would be the case if the word EXACTLY is replaced by at least in Question?

---

Then 1 course can be enrolled by all female students as well. for example:

StudInfo:

StudID	Name	gender
S1	A	M
S2	B	F
S3	C	F

Enroll:

StudID	CourseID
S1	C1
S2	C1
S3	C1
S3	C2

Cartesian product of Female students with course id table look like:

StudID	CourseID
S2	C1
S2	C2
S3	C1
S3	C2

Now if ENROLL is subtracted from above table we get

StudID	CourseID
S2	C2

Select CourseID from above table  = C2

which shows that only C2 course is the only course where a proper subset of {S2,S3} --> S2 has joined.