Relational algebra aggregate function

Question

I have following employee schema:

employee (id, name, address, age)

To find out the name of oldest employee, I know the SQL which looks like:

select name from employee,
(select max(age) max_age from employee) e2
where age=max_age

but I am not sure how to do that in relational algebra. Once again, text books have not mentioned how to do such things.

My probable solution looks like follows:

π_name (σ_{age=max_age}( employee × ρ_{max_age/age}(ℱ_max(age)(employee)) ))

Does this answer your question? [Aggregate Relational Algebra (Maximum)](https://stackoverflow.com/questions/4952451/aggregate-relational-algebra-maximum) — philipxy, May 04 '22 at 19:09
Does this answer your question? [Query using aggregation and/or groups in relational algebra - count, max, min, etc](https://stackoverflow.com/q/15660545/3404097) — philipxy, Sep 02 '22 at 06:21
There are many RAs (relational algebras). They differ in operators & even what a relation is. Give operator definitions & your reference for yours. Eg textbook name, edition & page. Nested algebra calls form a programming language. So give as much of a [mre] as you can. But--Google 'run relational algebra online'. Re "is this right": [ask] [Help] PS [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) [More.](https://stackoverflow.com/a/55671890/3404097) PS Changing SQL subquery use to joins is a faq. PS What exactly is your 1 (specific researched non-duplicate) question? — philipxy, Sep 02 '22 at 06:30

score 1 · Answer 1 · edited May 23 '17 at 10:26

Your "probable solution" is not wrong, but is too complex.

Note that the filter (σ_{age = max_age}) is a test for equality, and that max_age is a rename of age. You can avoid the rename and the sigma by putting a nat join instead of the cross-product:

πname ( employee ⋈ (ℱmax(age)(employee)) )

I'm not surprised you can't find this in textbooks:

1) they don't teach the full power of natural join.

2) they don't cover aggregate functions very thoroughly.

And that's because of weaknesses in SQL compared with RA (IMO):

1) natural join is a relatively recent innovation in SQL (not well supported).

2) SQL's implementation of aggregates is ghastly.

BTW, you can avoid the max aggregate altogether, with a bit more work, see finding max value among two table without using max function in relational algebra

Relational algebra aggregate function

1 Answers1