-1
Enroll (table):

cid        sid    grade    gradepoint
CS425      001    A        4.0
CS595      001    B        3.0
CS595      002    A        4.0
EE401      001    A        4.0
EE401      002    B        3.0
EE401      004    A        4.0
PHYS571    002    C        2.0
PHYS571    004    A        4.0

Using relational algebra, the operation is (excuse the lack of greek letters, I don't know how to add them on SO):

select_(Enroll.gradepoint<x.gradepoint)^Enroll.cid=x.cid (Enroll x rho_x(Enroll))

Basicaly, it is saying to do a cartesian product of the Enroll table with itself (renaming the second instance of Enroll to "x"), then select only the rows where the cid matches (of course) and where the gradepoint is lower than itself. But this makes no sense. Wouldn't this just result in an empty set?

JohnSmithy1266
  • 407
  • 1
  • 6
  • 15
  • Could it be that in relational algebra the *order* of the terms in the selection predicate actually matters?? – JohnSmithy1266 Oct 03 '17 at 19:25
  • No, @JohnSmithy1266, the order in the σ selection doesn't matter, nor in the π projection. There is no 'column order' in RA and access is by attribute name only. Neither is there dot-prefix attribute naming usually (are you actually using SQL?) -- what version of RA are you using? I just can't follow your notation. By ρ (rho) renaming relation `Enroll` to `x`, I guess you get two copies of each attribute arising from the cartesian product. You're matching on `cid` only, so you'll get different `sid`s, `grade`s, `gradepoint`s. – AntC Oct 03 '17 at 21:37
  • @AntC I dind't know there were different variations of RA. I'm using the standard taught in a grad school course, I would assume. – JohnSmithy1266 Oct 03 '17 at 22:10
  • There is no one "RA" (relational algebra). They differ even in what a relation is. *Give a reference to your RA*. (Rho seems to rename *a relation* which doesn't make sense even if relations contain their own name, but that's the kind of idiotic so-called RAs SQL apologists define.) A select/restrict condition evaluates to true/false, so why would order matter? (It *could*, per contained operators.) [Every query has a meaning](https://stackoverflow.com/a/24425914/3404097), your paraphrase is wrong. *Where and how are you stuck*?? Please google 'stackexchange homework'. See [ask]. – philipxy Oct 03 '17 at 22:12
  • Here is a link to the type of relational algebra that I am using: https://www.tutorialspoint.com/dbms/relational_algebra.htm – JohnSmithy1266 Oct 03 '17 at 22:43
  • 1
    Thanks. Please edit clarifications into your question, not comments. Please address my other request: show your work & where & how you are stuck. Note that *your expression is not using the same algebra as your link*. Re your 1st comment, what is the "it" you mean when you say "could it be"? "It" seems to be some kind of cause of some kind of *problem*? PS 1. You can google 'unicode' with 'join', 'rho', etc then cut & paste. 2. https://dbis-uibk.github.io/relax/calc.htm (Did you try to find an online algebra evaluator?) 3. tutorialspoint is a poor information source; that page is an example. – philipxy Oct 03 '17 at 23:29
  • 1
    What exactly are you trying to ask? The result *value*? The result *meaning*? Please edit to be clear. – philipxy Oct 04 '17 at 00:38

1 Answers1

1

Let's say operator rename n (r) returns a table value like r but with new attribute names like the old ones prefixed with n.. Then we have:

/* rows where
    (cid, sid, grade, gradepoint) in Enroll
and (x.cid, x.sid, x.grade, x.gradepoint) in Enroll
and gradepoint < x.gradepoint and cid = x.cid
*/
restrict gradepoint < x.gradepoint and cid = x.cid (
    Enroll times rename x (Enroll)
    )

(Which are rows of the form (cid, sid, grade, gradepoint, x.cid, x.sid, x.grade, x.gradepoint).)

Unfortunately that comment doesn't say anything about the business situation. So neither does the table. But suppose in our case (cid, sid, grade, gradepoint) in Enroll when in coursecidstudentsidgot gradegrade& grade point averagegradepoint. (This is the table's (characteristic) predicate in business terms.) Then by replacing the in we get that that query is also:

/* rows where
    in course cid student sid got grade grade & grade point average gradepoint
and in course x.cid student x.sid got grade x.grade & grade point average x.gradepoint
and gradepoint < x.gradepoint and cid = x.cid
*/

Each row in a table value (constant/variable or query result) makes a statement from plugging it into the table's predicate. (And each absent row states the negation/not of the statement from plugging it into the predicate.) The designer gives the constant/variable predicates and the query result predicate comes from those & the relation operators. (restrict & joins (including times & intersect) introduce and, project introduces there exists, union introduces or, minus introduces and not, etc.)

Here, the statement involves one course and two gradepoints, each belonging to a different student. So there's no "where the gradepoint is lower than itself".

Forming a relational algebra query from an English description
Is there any rule of thumb to construct SQL query from a human-readable description?

(A so-called "algebra operator" like you mention to "rename" a value is really a programming language non-terminal for assignment to a variable. That is orthogonal to algebra values & operators. Such muddling arises from unclear thinking, typical of SQL apologetics.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • I'm trying to understand your answer but I'm a little confused. I think you might have some erroneous code designation, for example around the word "got" or "in course". – JohnSmithy1266 Oct 04 '17 at 13:53
  • 1
    Your comment is not clear. But "in course..." is not code, it is *English*, it is a template what a row says about the business situation. Rows that make a true statement go in the table. We say, it is what the table means; it is the *(characteristic) predicate* of the table in business terms. Did you read the links? I'll edit when I can. *Please* read your title & question and see that it is not clear what you are asking. And address all my comments. – philipxy Oct 04 '17 at 20:29