relational algebra

Question

Consider the following schema:
Student (Sid, Sname, age)
Course Info (Cid, Cname, Instructor SSN)
Enroll (Sid, Cid, grade). 
The relational algebra expression for “find the Cid’s of courses enrolled by two different students”

(no options)

Answer 1

Student (Sid, Sname, age)
Course Info (Cid, Cname, Instructor SSN)
Enroll (Sid, Cid, grade)
The relational algebra expression for “find the Cid’s of courses enrolled by two different students” :

1. “find the Cid’s of courses enrolled by at least two (different) students” :

$E_1(S,C,G) \leftarrow Enroll$ // Renaming of enroll relation into E_1 with attributes names as $S,C,G$ in that order.

$At\_least2 \leftarrow \prod_{cid} (E_1 \Join_{(C = Cid \, \wedge \,S \neq Sid)} Enroll)$ //Finding courses which have at least two students.

2. “find the Cid’s of courses enrolled by at least three (different) students” :

Similar to what we did in the previous query. 

$E_1(S1,C1,G1) \leftarrow Enroll$ // Renaming of enroll relation into E_1 with attributes names as $S1,C1,G1$ in that order.

$E_2(S2,C2,G2) \leftarrow Enroll$ // Renaming of enroll relation into E_2 with attributes names as $S2,C2,G2$ in that order.

$At\_least3 \leftarrow \prod_{cid} ( (E_1 \Join_{(C = Cid \, \wedge \,S \neq Sid)} Enroll) \,\, \Join_{(C2 = C1 \, \wedge \, S2 \neq S1 \, \wedge \, S2 \neq Sid)} E_2  )$ //Finding courses which have at least three students.

3. “find the Cid’s of courses enrolled by exactly two (different) students” :

$Exactly2 \leftarrow At\_least2 - At\_least3$

 

Comments

thank you very much bhai

Answer 2

well taking it as cids of courses enrolled by at least two students

       Project_(Cid)(Select_{(R1.Cid=R2.Cid^R1.Sid<>R2.sid)}((Rename_(R1)(Enroll))X (Rename_(R2)((Course info)Natural join(Enroll)))))

Answer 3

cid's of courses enrolled by exactly two students =[cid's of courses enrolled by at least three students]-[cid's of courses enrolled by at least  two students].

cid's of courses enrolled by at least three students$\rightarrow$ $\rho(T_1,Enroll)$,$\rho(T_2,Enroll)$,$\rho(T_3,Enroll)$

=$\pi_{sid} (\sigma_{T_1.cid=T_2.cid=T_3.cid \ AND \ T_1.sid\neq T_2.sid\  AND \  T_2.sid\neq T_3.sid\ AND \ T_3.sid\neq T_1.sid}(T_1 *T_2*T_3))$

cid's of courses enrolled by at least two students$\rightarrow$$\rho(T_1,Enroll)$,$\rho(T_2,Enroll)$

=$\pi_{sid} (\sigma_{T_1.cid=T_2.cid \ AND \ T_1.sid\neq T_2.sid}(T_1 *T_2))$,,where $*$ is cross product

Comments

Exactly two = At least two - at least three.