Are three equalities on a join operation legal in relational algebra?

Let be the following database:

Film(Title,Director,Actor)
Produce(Producer,Title)

Express in relational algebra the following questions:


Which actors produce at least a Film they directed?

Which actors produce every film they directed?

• For the first question, it leads me to an issue as far as I have three equality on the join operation and I don't know if it is legal.

If it were I would have tried the following:

ΠActor(Film)⋈(Producer=Actor=Director)ΠProducer(Produce)

But I'm not sure that's a true join operation in relational Algebra.

-
For the second one I tried something totally different:

ΠActor(Film)÷ΠDirector(Film)÷ΠProducer(Produce)

Responses to comments:

• A film may have several actors.

• For a film with several actors, there are multiple rows in the relation Film

• A Director may, or may not, also be an Actor in the same Film

The notation Producer=Actor=Director is wrong. Besides that, neither ΠActor(Film) nor ΠProducer(Produce) has an attribute Director.
The first projection has only an attribute Actor, the second projection has only an attribute Producer.

In the second answer what is meant if you divide two relations that have the same number of attributes. I think that does not make sense because the result is a relation with no attributes.

You first relation Film(Title,Director,Actor) is very strange.
It defines a relation between three attributes actor, directors and title.
But from my understanding of a film there does not exist such a relation.
There is a relation between actor an title, if an actors plays in a movie with this title.
And there is a relation between a director and a title if a person is a director of this movie.
But I don't understand the tuple (actor, director, title).
Does an actor have a director in a movie and another actor has another director in the same movie?
If that is not the case then you should better use two relations:
A relation Acts(Person, Title)and a relation Directs(Person, Title).

Here are the answers using only natural join, which makes them a little bit clumsy.

The first question


Which actors produce at least a Film they directed?

has the following answer, using the notation from wikipedia:


Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \pi_{\text{Actor}}\left(\rho_{\text{Actor}/\text{Director}}\left(\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce})\right)\right)$$

Here is an equivalent SQL code (Oracle):

select F2.Actor
from Film F2 join (
  Film F1 join Produce P 
    on (F1.Director=P.Producer and F1.Title=P.Title)
  ) on F2.Actor=F1.Director

Example

For the following data the result is Donald Duck

• Film

Title | Director | Actor
-------------------+-------------+-----------
Mickey Mouse Revue | Donald Duck | Minnie Mouse
Mickey Mouse Revue | Donald Duck | Mickey Mouse
Duck Tales | Walt Disney | Donald Duck


• Producer

Producer | Title
------------+------------------
Donald Duck | Mickey Mouse Revue
Walt Disney | Duck Tales


The second question


Which actors produce every film they directed

uses additionally the set difference operator \:


Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \rho_{\text{Actor}/\text{Director}}(\pi_{\text{Director}}((\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce}))\setminus \pi_{\text{Director}}(\pi_{\text{Director},\text{Title}}(\text{Film}) \setminus \rho_{\text{Director}/\text{Producer }}(\text{Produce}))))$$

These are all (Director,Title) pairs that were not produced by the director of the title:

and therefore these are all directors that haven't produced at least one of their titles:

Similar, these are all directors that produced at least one of their titles:

The difference are the directors that have produced all of the titles the directed.
Now we have to join it to the set of all actors to filter out the directors that are also actors (but not necessary of the films they directed).

Here is an equivalent SQL Code (Oracle):

Select F2.Actor
from Film F2 join 
(
  (Select F1.Director,F1.Title
  from Film join Produce on (
      F1.Director=P.Producer 
      and F.Title=P.Filem))
  minus
  (Select Director, Title
  from Film
  minus
  select Producer,Title
  from Produce)
) F3
on(F2.Actor=F3.Director)

StackExchange Database Administrators Q#131217, answer score: 5