Cartesian Product Operation in SQL and RDBMS - concept discussion with example

  Cartesian Product Operation

 

* The Cartesian product operation is denoted by a cross ( X ) which allows us to combine information from any two relations .

 

* One can write the Cartesian Product of Relations R1 and R2 as R1 x R2

 

* A relation is by definition a subset of a Cartesian product of a set of domains .

 

* From the definition , one can have an intuition about the definition of the Cartesian product operation .

 

* Since the same attribute name may appear in both R1 and R2 , one may need to devise a naming schema to distinguish between the attributes .

 

* One can do the attachment to an attribute , the name of the relation from which the attribute originally came from .

 

* For example , the relation schema for

r = borrower * loan is :

 

(

borrower.customer_name ,

borrower.loan_number,

loan.loan_number ,

loan.branch_name ,

loan.branch_name ,

loan.amount

)

 

* With the schema , one can distinguish between borrower.loan_number from loan.loan_number as both the attributes do have the same name but the main relational table for the table are different which are loan and borrower

 

* The naming convention for any of the relations or schemas requires that the relation should have distinct names

 

* In general , if we have two relations r1(R1) and r2(R2) , then r1 x r2 is a relation whose schema is the concatenation of relations R1 and R2 .

 

Relation R contains all the tuples t for which there is a tuple t1 in relation r1 ; and a tuple t2 in r2 for which t|R1| = t|R1| and t|R2| = t2|R2|

 

* Suppose we want to find the names of all the customers who have a loan at the "PerryRidge" branch , then one may need the information in both the loan relation and the borrower relation through the given selection statement .

 

<< FIG - 01 >>

 

 

 From the given statement , one can find that a selection of attribute "branch-name" can be done with a relation existing from the relational tables for borrower and the loan relational table but given that the branch_name is "PerryRidge" over here .

 

* From the above relation , if one wants to find if there is a cartesian product operation that associates every tuple of loan with every tuple of borrower , with the customer having a loan in the “PerryRidge” branch , then there is some tuple in borrower x loan that contains the name of the customers which can be obtained by

criterion / condition from one table borrower to table loan where borrower.loan_number = loan.loan_number