how to get rows with more than 1 appearance of a specific column in relational algebra

Question

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

zack · Answer

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.

how to get rows with more than 1 appearance of a specific column in relational algebra

One Answer

Add your own answers!

Ask a Question