4

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=DirectorProducer(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
5
+150

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:

join formula

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 \:

enter image description here

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:

enter image description here

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

enter image description here

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

enter image description here

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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