-1

I am a bit confused about the difference between a Cartesian Product and Join in a Database using Relational Algebra. If I have a table Movies that has primary key attribute Movie_Name, an attribute Lead_Actor, foreign key Genre. And I have a table called Movie_Length with foreign key being Genre pointing to Genre, primary attribute Movie_Name, attribute Movie_Length.

If I want to do the following: Find the movie length in a particular genre of movies.

Could I do the following:

SELECT_{GENRE=FANTASY}(Movies) CARTESIANPRODUCT PROJECT_{ACTOR}(Movie_Length)

Please correct anything that you feel is wrong as I am learning.

For those that are wondering which Relational Algebra sets that I am using I am using the set found in this website. https://www.cs.dartmouth.edu/~cs61/Resources/Examples/RelationalAlgebra/RAsymbols/rasymbols.html

This is the textbook that I am using:

Fundamentals of Database Systems, 7th edition

Ramez Elmasri University of Texas at Arlington, Shamkant B. Navathe Georgia Institute of Technology

This is written in MySQL Syntax

Select Length From Movie_Length Inner Join Movies ON Movies.Genre WHERE Movie_Length.Genre = 'Fantasy';

Movie_Length Table:

Movie_Length Genre
Length1 Fantasy
Length2 Drama
Length3 Fantasy
Length4 Action
Length5 Fantasy

Movies Table:

Movie_name Lead_Actor Genre
Movie1 Actor1 Fantasy
Movie2 Actor3 Drama
Movie3 Actor2 Fantasy
Movie4 Actor4 Action
Movie5 Actor1 Fantasy

Result of the Query:

Movie_Length
Length1
Length3
Length5
5
  • There are many relational algebras. They differ in operators & even what a relation is. Give operator definitions & a reference for yours. Eg textbook name, edition & page. Nested algebra calls form a programming language. So give as much of a minimal reproducible example as you can. But--Google 'run relational algebra online'. Show parts you can do. Explain re how you are 1st stuck/unsure. Now you essentially ask us to (re)write a textbook with bespoke tutorial--too much. How to Ask Help center
    – philipxy
    Commented Feb 26 at 7:41
  • How could we know what queries would be OK when only your "Find ..." is given? What's your justification for yours? How could we get from "a particular genre of movies" to =FANTASY? Why Movies columns for "find actors"? What does "the difference between a Cartesian Product and Join" have to do with what you want to ask? Yes or no questions are seldom useful & seldom ask what the asker actually wants answered. "anything that you feel" is not part of an on-topic question. What 1 question do you actually want to ask? PS Why would or wouldn't this be correct? PS Why would PKs & FKs be relevant?
    – philipxy
    Commented Feb 26 at 7:58
  • Please: Put all & only what is needed to ask in your post. Including, not just at a link. "Give operator definitions" & give credit for any quotes. Again: "Find the movie length in a particular genre of movies." does not clearly say what is wanted. If you can't make yourself clearly say what is wanted you can't expect to be able to code it, let alone expect other people to know what is OK code. Also: Please act on all the comments. Where are you unsure or stuck? Why would your code give that result? Why is "confused" in the post if it's not part of the question? Why repeat in SQL? Etc.
    – philipxy
    Commented Feb 27 at 2:13
  • Please don't edit a question in a way that invalidates reasonable answer posts. Instead post a new question in a new post. Although I don't consider the answer posted to be reasonable for stopping editing because it isn't trying to actually answer a question posed by the post. Moreover while not clear a post should not be answered.
    – philipxy
    Commented Feb 27 at 2:27
  • For typical algebras for which your query would be legal, it does not return the example result from the example inputs. But if you don't say why you think it does or doesn't with justification referencing authoritative documentation then we cannot critique what you do & don't understand or misunderstand. We can't even rephrase the documentation or differently present the algebra since you give no details & even if we did it would be just another presentation for you to not understand and/or misunderstand. PS Re relational (algebra) querying.
    – philipxy
    Commented Feb 27 at 4:05

1 Answer 1

2

Forget the algebra. Your model - or how you are describing it - is fundamentally wrong.

... a table Movies that has primary key attribute Lead_Actor ...

The primary key of a Movies able MUST be something that uniquely identifies a Movie, not the actors within it.

... a table called Movie_Actor with foreign key being Actor pointing to Lead_Actor, primary key Name, and attribute Sex.

That's the wrong way around.
An Actor can exist without appearing in a Movie in the same way that a Movie (in pre-production) might exist without any Actors in it. You should have an Actors table that holds the individuals (with name and gender attributes), plus a Movie_Actors table that links those Actors to Movies (an Actor can appear in any number of Movies, including zero, and a Movie can have any number of Actors in it, also including zero, so a "weak" entity or linking table is required).

... the difference between a Cartesian Product and Join ...

In a join, rows of each table are linked based on some condition.
Very often (not not always), that's a Foreign Key in one table "pointing to" the Primary Key on another.
In a Cartesian Product, there is no join condition and so every row in one table is "joined" to every row in the other:

select * 
from tableA ; 

+----+--------+
| id | name   | 
+----+--------+
|  1 | Fred   | 
|  2 | Barney |
|  3 | Wilma  |
+----+--------+

select * 
from tableB ; 

+----+-----------+
| id | name      | 
+----+-----------+
| 77 | artichoke | 
| 88 | broccoli  |
| 99 | carrot    |
+----+-----------+

select * 
from tableA, tableB ; -- Cartesian Product 

+----+--------+----+-----------+
| id | name   | id | name      |
+----+--------+----+-----------+
|  1 | Fred   | 77 | artichoke | 
|  1 | Fred   | 88 | broccoli  | 
|  1 | Fred   | 99 | carrot    | 
|  2 | Barney | 77 | artichoke | 
|  2 | Barney | 88 | broccoli  |
|  2 | Barney | 99 | carrot    | 
|  3 | Wilma  | 77 | artichoke | 
|  3 | Wilma  | 88 | broccoli  |
|  3 | Wilma  | 99 | carrot    | 
+----+--------+----+-----------+
3
  • +1 I see the difference between the two I am going to modify my question to show what exactly I am looking for using tables.
    – Jose Serra
    Commented Feb 27 at 1:09
  • I just edited the question your answer was a great help. I never thought about thinking about using database, and Database Manipulation Language first to get closer to the relational Algebra answer I was looking for.
    – Jose Serra
    Commented Feb 27 at 1:35
  • Movie Length and Genre should be attributes on the Movies table! What possible Primary Key can you put on your Movie_Length table? As soon as you get two movies in the same Genre of the same Length, Boom! Your Model is broken. To relate Length and Genre, you have to go through the Movies table. SELECT Length FROM Movies WHERE Movies.Genre = 'Fantasy';
    – Phill W.
    Commented Feb 27 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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