3

Not sure it is a correct SQL and would like to work backward converting SQL back to tuple relational calculus to check the correctness. Let's say we come up this query to display the classes where all its requirement classes have been complete by all the participants who finished the introduction. So we have two tables, progress and requirement. where progress: mId (string), cId (string) and requirement: : cId (string), rId (string) all the enrollment are kept in progress and all the requirements are specified requirement table.

mId:memeberId
cId:cId
rId:requirmentClassId



Select cId 
From requirement r 
Where r.rId in (Select distinct(cId) 
                From progress 
                Where mId in (Select mId 
                              From  progress 
                              Where cId='intro'
                               )
                );

We first select all the members from the progress table where they has done the intro session. Select mId From progress Where cId='intro'

Then we have all the members who finished the intro, so we can select all the other distinct classes has done by the members.

Select distinct(cId) 
                From progress 
                Where mId in (Select mId 
                              From  progress 
                              Where cId='intro'
                               )
                )

Now we have all the classes completed by all the members who finished the intro, now we can find out all the next possible class(es) that should be offered to all the members who finish the intro?

Ok, after I finish writing the question, I don't think this query is correct since now we have all the classes completed by all the members who took the intro, but not necessary all the classes is completed by individual student, correct ? Please check my work. any help is highly appreciated.

philipxy
  • 14,867
  • 6
  • 39
  • 83
mmmm
  • 81
  • 7
  • 1
    (1) I don't see how converting to relational calculus helps validate a query. (2) I don't understand what your question is. (3) If you have a question about specific data and a specific question, I would suggest that you ask a *new* question, with sample data, desired results, and leave out the discussion of relational algebra. – Gordon Linoff Jan 28 '21 at 12:07
  • @GordonLinoff, My questions is :Please check my work , if this query returns the class(es) where all its requirement classes have been completed by every single participants who finished the introduction class. – mmmm Jan 28 '21 at 12:20
  • . . As I say, that is not the question you have asked here. Ask a new question with sample data, desired results, and your question. Leave out all the stuff about relational algebra. – Gordon Linoff Jan 28 '21 at 13:27
  • It's possible to talk about the same rows being returned by corresponding expressions in SQL & a TRC, for instance if you feel you understand one but are shaky about the other. But you aren't clearly doing that here. One could also reasonably talk about rows returned by SQL expressions, and one could use assumed knowledge of TRC or predicate calculus to simplify what one says. But it's not clear what you are trying to accomplish or ask. Please clarify via edits, not comments. If you are using a TRC, define it & give a reference to where you got it, there are many. [ask] [help] – philipxy Jan 28 '21 at 19:32

0 Answers0