-1

I know this question is already asked here. But it's not answered properly there.

Q) Consider the following relational database schemes:

COURSES(Cno.name)
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.

Answer given here:

S ← π Cno (σ student_no=2310 (COMPLETED))
RESULT ← ((ρ (Course,Cno) (PRE−REQ))÷S)

same expression

But I found a flaw in it. Suppose

PRE-REQ                    COMPLETED
Cno Pre-Cno                Student_no Cno
C1    C3                   2310       C3
C2    C4                   2310       C4

The desired result should be C1, C2 but my query will return an empty relation as C1 doesn't have C4 as its pre-requisite course and similarly, C2 doesn't have C3 as its pre-requisite course.

S         RESULT
Cno       Course Cno
C3
C4

Another solution which is given in one of the answers here but by using SQL is-

SELECT Pre-Req.Cno 
FROM Completed, Pre-Req  
WHERE student_no = '2310'  
GROUP BY Pre-req.Cno  
HAVING pre-Cno IN (
    SELECT C.cno 
    FROM Completed AS C
    WHERE C.student_no = '2310';
    )

Is there any other possible way to write is as a relational algebra expression ?

ankit
  • 277
  • 1
  • 4
  • 25
user9014873
  • 53
  • 1
  • 8
  • Please do not ask duplicates. Edit the original per the many comments re its problems. Ask a new question if you work part way and ask a question about the rest of the way. But explain why you coded what you did, what you think is correct & relevant, what is not, etc. (Where is the image of "your" attempt from?) You already ignored comments there re explaining your work & giving your algebra. Please read & act on hits googling 'stackexchange homework'. (Also 'run relational algebra online'.) Also (since RA is code) [mcve]--justify "minimal" by giving a maximal part that works. – philipxy Sep 02 '18 at 07:29
  • Your last sentence is not clear. Use enough sentences to clearly say what you mean. Also please explain why your "because" is so--it is not obvious. What do you mean "null table"--do you mean, an empty table? – philipxy Sep 02 '18 at 08:14
  • I already read all the comments below that question but unable to find what I am looking for.. – user9014873 Sep 02 '18 at 08:35
  • Anyway thanks. I will take care next time :) – user9014873 Sep 02 '18 at 08:35
  • Read my comments. You did not put things in your question that the questions at the other question said should be there. Also, there's just no evidence that you tried to find a solution to this question or understand the solution you gave. Re the link/image, since you gave text it is ok, someone will put the link inline. But for the future: Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. – philipxy Sep 02 '18 at 08:36
  • You still don't tell us what version of relational algebra you are using. So how does ρ work? (It takes a list of new column names?) – philipxy Sep 02 '18 at 19:01

1 Answers1

0

Here is a possible solution (I will use a simpler notation):

COURSES_OF_2310 = π c←Cno (σ student_no=2310 (COMPLETED))
PARTIALLY_SATISFIED = PRE_REQ ⨝ PRE_REQ.preCno=c COURSES_OF_2310
NOT_SATISFIED = PRE_REQ - π Cno, preCno←c PARTIALLY_SATISFIED
FULLY_SATISFIED = π Cno PRE_REQ - π Cno (NOT_SATISFIED)

This is quite complex and probably could be simplified. However it should work now. Here is an example tested with RelaX:

COURSES(Cno)
C1
C2
C3
C4
C5
C6

PRE-REQ(Cno, pre-Cno)
C1 C3
C1 C4
C2 C3
C2 C4
C5 C3
C5 C6
C6 C4

COMPLETED(student_no, Cno)
2310 C3
2310 C4

PARTIALLY_SATISFIED(Cno, preCno c)
C1  C3  C3
C1  C4  C4
C2  C3  C3
C2  C4  C4
C5  C3  C3
C6  C4  C4

NOT_SATISFIED(Cno, preCno)
C5  C6

FULLY_SATISFIED(Cno)
C1
C2
C6
Renzo
  • 26,848
  • 5
  • 49
  • 61
  • But all the prerequisites of C3 & C4 (of which there are none) have also been completed. We see the usual specification ambiguity for queries with "all", that might or might not be solved by Codd's division or by universal quantification. – philipxy Sep 02 '18 at 08:26
  • @philipxy, I think that would require a recursive solution which is impossible in the “regular” Relational Algebra. – Renzo Sep 02 '18 at 08:32
  • Sir prequisite for course C6 is also completed..But your query will not return it..I wrote the same expression above but I stuck for cases like this only. – user9014873 Sep 02 '18 at 08:40
  • This is why unclear questions should not be answered. – philipxy Sep 02 '18 at 08:59
  • 2
    @user9014873, you are right, I've changed the answer. – Renzo Sep 02 '18 at 09:53