I have a table like this:

PostId   |    Body    |    AuthorId 
  2             b               F
  2             b               E
  2             b               C
  4             d               A
  4             d               E
  8             h               F 

So what I want is to get all the rows that have more that 1 appearance of PostId. Here the result would be 2 and 4 because they appear more than 1 time. I want this in relational algebra.
I have already a relation that works just fine but in this relation I use aggregation methods and I don't really what to use count in my relation. I am wondering if there is a way to this with subtraction or division operators?
What I do for now is π PostId (σ c ≥ 2 ( γ Body; COUNT(PostId)->c R2)) to get the row with more than 1 appearance of PostId.
Thanks in advance for help


2 Answers 2


What about $\pi_{PostId}(\sigma_{\text{PostId} = \text{PostId} ~AND~ (\text{Body} <> \text{Body} ~OR~ \text{AuthorId} <> \text{AuthorId}}(R_2 \times R_2))$? It does not use relational division neither substraction, but I think that fits your needs (relational algebra without counting).

The idea is to make a cartesian product of $R_2$ with itself. Hence, you have pairs of tuples of $R_2$. Then, the idea is to look for two pairs that have the same PostId but are not the exact same tuple (i.e., whose Body or AuthorId are different). If you have at least one pair, then, the PostId of such pair appears at least twice (possibly more). Once you have this, you can project the PostId column.


Here is a relational algebra expression given $R_2$ described in the question that uses set division operator

$$ \Pi_{\text{PostId}, \text{AuthorId}}(R_2) \div \sigma_{\text{AuthorId} = E}(\Pi_{\text{AuthorId}}(R_2)) $$

$\sigma_{\text{AuthorId} = E}(\Pi_{\text{AuthorId}}(R_2))$ will return a set $\{E\}$. Then by the definition of set division operator, we can see that only $(2,E)$ and $(4,E)$ belong to the $R_2$. Thus, the above relational algebra expression will return $\{2,4\}$. Also, we observe that both 2 and 4 are the only PostIDs that have more than one appearance. Thus, the above relational algebra expression gives the right answer.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.