Problem solve Get help with specific problems with your technologies, process and projects.

Relating Cartesian product to relational algebra and calculus

What is a Cartesian product and what relation does it have to relational algebra and relational calculus?
Named after the famous french philosopher Renee Descartes, a Cartesian product is a selection mechanism of listing all combination of elements belonging to two or more sets. Usually programmers create a Cartesian product by omitting a join condition accidentally or erroneously.

For example,

Say, I have three numbers in set (table) A --> 1, 2, 3

I have three letters in a different set (table) B --> E, F, G

The Cartesian product of the two sets (A X B) will be the following rows

    1 E
    1 F
    1 G
    2 E
    2 G
    2 G
    3 E
    3 F
    3 G

Relational algebra is used to express queries by applying specialized operators to relations. Relationships (resulting query) are determined and established by attributes (column value) in entities (table) through some operators. Cartesian product is one such operator.

Other operators for relational algebra are:

i) UNION (A U B) 
Set of tuples (result set)  that are in  A or B or both
ii) Difference (A - B) 
Set of tuples (result set) that are in A but not in B.
ii) Intersection (A ^ B) 
Set of tuples that are both in A and in B.
iii) Cartesian Product (A X B) 
All possible combination of entries in A and in B

iv) Project
Selecting a subset of the attributes (columns) of an entity (table).
v) Select --> Set of tuples that are in a A through some condition.
vi) Join  
vii) Division (Quotient)

On the other hand relational calculus is an alternative method to relational algebra. However, both are equivalent to one another -- i.e., for each expression of the relational algebra there is an equivalent in the relational calculus. The converse is also true. Relational calculus is closer to the natural language and based on predicate logic. Where as relational algebra is more like a programming language. Relational calculus simply states what the problem is and relational algebra provides a mechanism (procedure) for a solution. SQL is a prime example of relational algebra.

Dig Deeper on Using Oracle PL-SQL