I don't actually feel very comfortable with relational algebra, so, I'll do it first using standard SQL and then use a tool called RelaX - relational algebra calculator 0.18.2 to do the translation.
First, the table you wrote, I'll call it students, and define it and fill it with:
CREATE TABLE students
(
id INTEGER PRIMARY KEY,
grade INTEGER,
state TEXT
) ;
INSERT INTO students
(id, grade, state)
VALUES
(1, 83, 'CA'),
(2, 94, 'TX'),
(3, 92, 'WA'),
(4, 78, 'CA') ;
RelaX will translate this into a dataset, represented by the following tuples:
group: Joan (imported from SQL)
students = {
id:number, grade:number, state:string
1 , 83 , 'CA'
2 , 94 , 'TX'
3 , 92 , 'WA'
4 , 78 , 'CA'
}
In order to find what you're looking for, we first need a table with tuples in the form (state, grade)
, having the maximum grade of each state. This query is done, in SQL with a MAX(grade)
per state
using a GROUPs BY state
. You would write it like:
SELECT
state, max(grade) AS grade
FROM
students AS s2
GROUP BY
state ;
Next, you need to JOIN
this table (that is named max_grades
) to the students
one, and you do it ON
equal states, and equal grade (i.e.: the max grade per state)...
SELECT
s1.id
FROM
students AS s1
JOIN
(
SELECT
state, max(grade) AS grade
FROM
students
GROUP BY
state
) AS max_grades
ON s1.state = max_grades.state AND s1.grade = max_grades.grade
... and this gets translated by RelaX to the following relational algebra expression and reponse:
π s1.id ρ s1 students ⨝ s1.state = max_grades.state and s1.grade = max_grades.grade ρ max_grades ( π state, grade γ state; MAX(grade)→grade ρ s2 students)
s1.id
1
2
3
NOTE1:
- If several students of one state would have the max grade, this expression would return ALL of them, not just an arbitrary one of that state.
Alternative:
If you cannot GROUP BY
, you can use another construct:
SELECT DISTINCT
id
FROM
students
EXCEPT
SELECT
s1.id
FROM
students AS s1
JOIN students AS s2 ON s1.state = s2.state AND s1.grade < s2.grade
This goes a bit more in-line with your original thinking, although I personally find it less clear...
The translation to relational algebra is:
π id students - π s1.id ρ s1 students ⨝ s1.state = s2.state and s1.grade < s2.grade ρ s2 students
Best Answer
Relational natural join is more or less SQL natural join. SQL natural join is defined as SQL inner join using all common columns. (And it is only defined when each argument has exactly one column with each of those names.) If you find what the course actually says about relational vs SQL natural join & inner join with using then it will be that. If the course says that there is an "equivalence" for that expression then it will be because the set of common columns for those tables will be those columns in a particular example.
(To say that relational algebra natural join "is the equivalent of" something in SQL is vague & needs defining. There are many versions of relational algebra that differ in operators available, in how operators with a given name work & even in what a relation is. Meanwhile SQL tables can have NULLs, multiple columns with the same names & multiple rows with the same contents--all typically non-relational.)