Relational Theory – Are Three Equalities on a Join Operation Legal?

relational-theory

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

Best Answer

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)

Related Solutions

Relational Algebra Question – Understanding the Basics

DISCLAIMER : Never Learned Relational Algebra but it looks interesting

From the schema given and your question, this is what the SQL should be:

SELECT
    emp_mgr.person_name
FROM
    manages emp_mgr
    INNER JOIN employee emp ON emp_mgr.person_name  = emp.person_name
    INNER JOIN employee mgr ON emp_mgr.manager_name = mgr.person_name
WHERE
    emp.street = mgr.street AND
    emp.city = mgr.city
;

Here is another query that only uses JOINs, no WHERE clause:

SELECT
    emp.person_name
FROM
    (SELECT A.person_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.person_name = B.person_name) emp
    NATURAL JOIN
    (SELECT A.manager_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.manager_name = B.person_name) mgr
;

The first query gets all employees who are managed and their managers in the form of a Cartesian Product. Then, it looks for a common street and city.

The second query collects personnel records (name,street,city) of employees and their managers and performs a NATURAL JOIN between the employess and their managers using (street,city).

If you can transalate both queries back to Relational Algebra, I think you will have what you are looking for. I believe the second may be of better help.

SQL and Relational Algebra – Beginner Questions Answered

Your sql query is wrong:

- Assume there is a student s1 who has passed one exam 
  after '2000-01-01' and none before. 
  Your query results in {s1} - {} = {s1}. This will be a false positive.

- Assume there is a student s1 that passed three exams 
  after '2000-01-01' and one exam before. 
  Your query results in {s1} - {s1} = {}. This will be a false negative.

I'm having a bit of trouble reading your algebra expressions, but the first one looks ok. It should not matter that you do the selection (topic = "motorcycle") after the join instead of joining on the selection.

The second one can't be right. Assume there's a newspaper that published both an article on motorcycle and an article on something else. Your expression will pick the article on something else and therefore return that newspaper (incorrectly).