The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+19 votes

Consider the following relational database schemes:

  • PRE-REQ(Cno, pre-Cno)
  • COMPLETED (student_no, Cno)

COURSES gives the number and name of all the available courses.

PRE-REQ gives the information about which courses are pre-requisites for a given course.

COMPLETED indicates what courses have been completed by students

Express the following using relational algebra:

List all the courses for which a student with student_no 2310 has completed all the pre-requisites.

asked in Databases by Veteran (59.6k points)
edited by | 1.4k views

In relation COURSES  ,(comma) missing

 COURSES ( -> COURSES (Cno. , name)

13 Answers

+6 votes
Best answer
$S \leftarrow \pi_{Cno}(\sigma_{student\_no=2310}(COMPLETED))\\RESULT \leftarrow ((\rho_{(Course,Cno)}(PRE-REQ)) \div S )$
answered by Boss (16.4k points)
selected by

we will select CNO for stud_no=2310 and rename CNO as pre-cno in COMPLETED relation and then perform divison of PreReq table with the table created above. Right??
No, it won't work. Say if student completed two courses C1 and C2 that are prerequisite for C3 and C4 respectively. Then what will ur query return?. It will return Null table.
Bcoz neither C3 has both prerequisite as C1 and C2 nor C4 has both prerequisite.
so can u write the correct query in relational algebra as sql both??


The division operation p(P) ÷ q(Q) only makes sense when Q ⊆ P.

In the above assignment, there are no common attributes between the two relations as the only common attribute 'Cno' has been renamed to 'Course', so the division makes no sense.



Can I do this

Project courseno (Select studentno=2310 (completed join pre requisite))
+6 votes

SQL query will be 

FROM Completed, Pre-Req  
WHERE student_no = '2310'  
GROUP BY cno  
HAVING pre-Cno IN (
    SELECT C.cno 
    FROM Completed AS C
    WHERE C.student_no = '2310';
answered by Boss (30.7k points)
But question is for relational algebra query.
arjun sir can u plz write RELATIONAL ALGEBRA for it?? i am not able to figure out. :-(

The one given by @mint below looks right to me. Verify
ΠCno.(PRE-REQ ⋈pre Cno.=Cno.   ΠCno.Sno.=2310(COMPLETED)))

Is it right sql query for above scenario???

select COURSES.cno
+4 votes

RA query: ΠCno.(PRE-REQ ⋈pre Cno.=Cno.   ΠCno.Sno.=2310(COMPLETED)))   

make it in two parts:

X= ΠCno.Sno.=2310(COMPLETED))      {X gives all the tuples in which 2310 is present}

YCno.(PRE-REQ pre Cno.=Cno.   X)   

I think Y is the required answer... correct me if i wrong.

answered by Active (1.1k points)
I got the same answer. I am wondering my no one commented here before. Veterans ?

PRE-REQ gives the information about which courses are pre-requisites for a given course, hence a course may have many prerequisite courses. In that case suppose a course C1 has two prerequisite courses- CP1 and CP2, now even if Sno-2310 completes only one of those prerequisite courses then also above query will print C1, which it should not, if and only if Sno-2310 completes both CP1 and CP2 then only it should print C1

There maybe more than one prereq for a single course.
+1 vote

T1 will have all the available course numbers

T2 will have all the course numbers completed by student2310

T3  will have the combination of all the courses and the courses completed by student2310

PRE_REQ - T3 (set minus operation) will return us all the entries of PRE_REQ which are not there at T3,

Suppose <C1,C5> is a particular tuple of (PRE-REQ - T3),

Now what does it implies? -->It implies that C5 is one of the prerequisite course for C1 which has not been completed by C5. Proof: If student2310 would have completed C5 then definitely <C1,C5> should have been there at  T3(remember T3 is the combination of all the courses and the courses completed by student2310) and in that case (PRE_REQ - T3) can't have  <C1,C5> as a tuple.

So, for any such <C1,C5> tuple, (<C1, any  course id>) of PRE_REQ - T3, C1  should not be printed as output(Since there is some prerequisite course for C1 which student2310 has not completed).

Now, suppose we have not got any tuple as a result of (PRE_REQ - T3) where C2 is there under cno attribute(<C2, any course id>), what does it implies?--> It implies that student2310 has completed all the prerequisite courses C2.

Hence, in order to get the final result we need to project cno from (PRE_REQ - T3) and subtract it from T1.

answered by Active (1.8k points)
0 votes

FROM Courses C1


                                      FROM PRE-REQ P1

                                      WHERE C1.Cno=P1.Cno


                                      SELECT Cno

                                      FROM COMPLETED C2

                                      WHERE C2.student_no='2310' ) ) ;

Is this query correct ?
answered by (47 points)
0 votes

This should be the answer.

ΠCno(PRE-REQ) - ΠCno(PRE-REQ - ρPRE-REQ.Cno/CnoPRE-REQ.Cno,pre-Cno((σstudent_no=2310 (COMPLETED))⋈Cno=pre-Cno PRE-REQ)))

answered by Active (1.5k points)
edited by
0 votes
My Attempt

$\large \pi_{\text{NAME}}(\text{COURSES}\ * \ \text{PREREQ}\ \div \rho_{\text{CNO=PRECNO}}(\text{COMPLETED}) )$
answered by Junior (741 points)
0 votes

MY attempt please let me know where i m wrong

answered by (69 points)
edited by
0 votes

X= ρ(pre-Cno)Cno.Sno.=2310(COMPLETED)))  

YCno.(PRE-REQ   X)  

Y is  the correct answer.
correct me if I am wrong

answered by (25 points)
0 votes

I came up with two approach for this Question.

Note- Questions in which "ALL" keyword is used, most probably would be solved with division operation.  

answered by (405 points)

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

41,078 questions
47,675 answers
62,393 users