Algebra Relational sql SORTING GROUP BY ORDER BY

Question

I wanted to know what is equivalent in GROUP BY, SORT BY and ORDER BY in relational algebra?

Answer 1

There is no possibility in relational algebra, but people create some "extensions" for these operations (Note: in the source text, part of the text is written as an index).

GROUP BY, According to the book "Fundamentals of Database Systems" (Elmasri, Navathe 2011, 6th ed.):

Another type of query that cannot be expressed in basic relational algebra is to specify mathematical aggregate functions for collections of values ​​from a database.
...
We can define an AGGREGATE FUNCTION operation using the character (pronounced script F) 7 to indicate these types of queries as follows:

<grouping attributes> ℑ <list of functions> (R)

where <grouping attributes> is a list of attributes of the relation specified in R, and <list of functions> is a list of pairs (<function> <attribute>). In each such pair, <function> is one of the allowed functions, such as SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT and <attribute> - the attribute of the relationship specified by R. The resulting relationship has grouping attributes plus one attribute for each element in the function list .

ORDER BY (SORT BY), another source:

Since a relation is a collection (or bag), there is no order for the relation. That is, two relations are the same if they contain the same tuples, regardless of order. However, the user often wants the output of the request to be specified in a specific order. We can define an additional operator τ, which sorts the relation, if we want to resolve an operator whose output is not a relation, but an ordered list of tuples.

For example, the expression

τLastName, FirstName (Student)

generates a list of all student sets ordered by LastName (as the primary sort key), and then FirstName (as the secondary sort key). (The secondary sort key is used only if the two keys match the primary sort key. The sort operation can display any number of sort keys from the most significant to the least significant.)

Answer 2

You can use π projection for columns that you want to group a table with them without aggregation (the PROJECT operation removes any duplicate tuples) as follows:

π c1, c2,3 (R)

where c1, c2, c3 are columns (attributes), and R is a table (relation)