Aggregate function in Tuple Relational Calculus

Question

How do you translate a COUNT or a GROUP BY or any other aggregate function you find in SQL into Tuple Relational Calculus?

User

| pk | email    |
|    | password |
|    | ...      |

Friendship

| pk | user1_email |
| pk | user2_email |
|    | date        |
|    | accepted    |

SELECT *
FROM user u
LEFT OUTER JOIN friendship f
ON (f.user1_email = u.email OR f.user2_email = u.email)
GROUP BY u.email
HAVING COUNT(u.email) < 3

I would like to transform this query into Tuple Relational Calculus. The JOIN and the SELECT are straightforward.

Answer 1

As Lennart says, it's not possible to express those functions so, I decided to transform the count in another way.

First let's assert the following predicate:

Then we can say that having 2 or less friends, is having 0 friends, 1, or 2. To have 1 friend is like saying that there exists a friend (friend1) for wich Friends(me, friend1) is true.

To have 2 friends, you must have 1 friend and another, different. And finally you must not have any more friend.

All this can be express like this:

Answer 2

I don't think you can express aggregate functions in neither TRC nor RA. However, there have been proposals to extend them, see for example:

http://cis.csuohio.edu/~matos/notes/cis-612/NestedRelations/Extending%20Relational%20Algebra%20and%20Relational%20Calculus%20with%20Se.pdf