0

How can I find the maximum without using aggregate operation in relational algebra?

The schema of Database is as follows

  • Item(IName, Brand)
  • Shop(SName, City, Address)
  • Sells(IName, SName, Price)

How can I find the item name and snop name which is sold in maximum price without using aggregate function in relational algebra? I know to solve this with using aggregate functions but not sure without using it.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
mobiusT
  • 47
  • 4
  • This is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Feb 15 '22 at 19:23
  • Does this answer your question? [Aggregate Relational Algebra (Maximum)](https://stackoverflow.com/questions/4952451/aggregate-relational-algebra-maximum) – philipxy Feb 15 '22 at 19:25

1 Answers1

0

In SQL, but not in relational algebra

You can sort by Price descendingly and limit the results to 1.

Example in MySQL

SELECT IName, SName, Price
FROM Sells
ORDER BY Price DESC
LIMIT 0, 1;

The highest Priced item will be the first record in the result and due the to limit you ignore anything else.

Alternatively, you can find the r1 record which has no r2 record with higher price:

SELECT r1.IName, r1.SName, r1.Price
FROM Sells r1
LEFT JOIN Sells r2
ON r1.Price < r2.Price
WHERE r2.Price IS NULL
LIMIT 0, 1;

The query above selects the items for which we do not find a higher priced pair. LEFT JOIN allows the right-hand-side to be nonexistent, so any r1 record will have at least a pair, but those r1 records that have the greatest price will have all nulls in r2. Equating for a column with null ensures that we filter out all r1 records that had a higher priced pair. We still have a LIMIT clause, because in the case of a tie, i.e. more records share the same maximum Price then we break the tie by selecting the first item that matched the criteria.

By default, the first approach I have shown should be preferred, but, if it is not an option for some reason, then you can go by the second approach.

EDIT

Relational algebra

@philipxy pointed out in the comment section that relational algebra has no null or sorting or limit. As a result, in relational algebra one would need to write an expression that searches among the records that which has a price for which does not exist another record whose price is higher.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • 1
    Relational algebra <> SQL. SQL is not actually relational, it has some relational aspects, despite marketing & misconceptions. Tables are not relations. "One can infer SQL from relational algebra notation and vice-versa." False. One can for some expressions with some allowances. PS There is no sorting in relational algebras or LIMIT. And in "basic" algebras no aggregation. And no NULL. Etc. Anyway the question asks for relational algebra, and this answer doesn't give it. – philipxy Feb 15 '22 at 19:39