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

Computer Science Asked by Mohammadreza on August 14, 2020

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

One 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.

Answered by zack on August 14, 2020

Add your own answers!

Related Questions

Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir