"At least one" clause in Relational Algebra

Problem Detail: 

I'm fairly new to the syntax of relational algebra, and I'm having a hard time understanding how I could set a "at least one" clause.

Example: I have:

• a table with books (listing the title, year published and ID),
• a table with authors (listing their name and ID),
• a table which lists what author wrote what book (through a tuple of the IDs mentioned before).

How could I, in relational algebra, get "All the authors that have published at least one book per year between 2008 and 2010"?

I have figured this so far. At step "b", the Natural join is used since both tables have PublicationID in common. Thus, the resulting table is |PublicationID|AuthorID|Year|. So I'm simply missing the step "c", where I don't understand how to gather a sub-set of the authors that published at least one book per year between 2008 and 2010.

$ a \leftarrow \pi_{PublicationID,Year} (Publication)$

$ b \leftarrow a \bowtie AuthorPublication $

$ c \leftarrow \sigma_{something} $

Asked By : Balaam

Answered By : Romuald

A first hint toward a solution is to think about "what's the result of a natural join between $Author$, $Publication$ and $AuthorPublication$?" The answer is "the universal relation" $R(BookID,AuthorID,Title,Year,Name)$ describing who wrote a book and when. Note that a book without any author or an author without any book written won't appear in $R$. So an author in $R$ as written at least one book. The following query gives the authors who wrote at least a book in year 2008: $$ \pi_{Name}(Author \Join AuthorPublication \Join \sigma_{(2008 = year)}(Publication))$$

For the final answer, compute the intersection of this query with its variants: $$ \pi_{Name}(Author \Join AuthorPublication \Join \sigma_{(2008 = year)}(Publication)) \cap \pi_{Name}(Author \Join AuthorPublication \Join \sigma_{(2009 = year)}(Publication)) \cap \pi_{Name}(Author \Join AuthorPublication \Join \sigma_{(2010 = year)}(Publication))$$

You may provide other equivalent answers with outer $Author \Join$.

Best Answer from StackOverflow

Question Source : http://cs.stackexchange.com/questions/12218