Search code examples
sqldatabaserelational-databaseaggregate-functionstuple-relational-calculus

Aggregate function in Tuple Relational Calculus

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.

Solution

  • 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:

    Friends 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:

    Querry