When do I use operators Cross Join (Cross Product) and Natural Join in a relational algebra statement?
Some versions of the relational algebra have relation headings that are sets of (unordered, uniquely named) attributes. Then (relational "Cartesian") PRODUCT aka CROSS JOIN (aka, wrongly, CROSS PRODUCT) is defined only when the input relations share no attribute names but otherwise acts like NATURAL JOIN. So its role is to confirm that you expect that there are no shared attribute names.
(Some versions of the relational algebra have relation headings that are not sets; attributes can be ordered and/or multiple attributes can have the same name. Usually PRODUCT outputs an attribute for every input attribute. If there's a NATURAL JOIN then its result will be like first doing PRODUCT, then RESTRICTing on equality of pairs of same-named attributes, then PROJECTing out one attribute of each pair. So PRODUCT works for any two inputs, and NATURAL JOIN might be undefined when an input has duplicate attribute names, but they will give the same result when there are no shared attribute names.)
As to why you would compose any particular relational algebra query:
Every table/relation has a statement parameterized by columns/attributes. (Its "characteristic predicate".) The rows/tuples that make the statement true go in the table/relation. First find the statements for the given tables/relations:
// customer [Cust-Name] has £[Balance] in account [Acc-No] at branch [Branch] Deposit (Branch, Acc-No, Cust-Name, Balance) // customer [Cust-Name] loan [Loan-No] balance is £[Balance] at branch [Branch] Loan(Branch, Loan-No, Cust-Name, Balance)
Now put these given statements together to get a statement that only the rows we want satisfy. Use AND, OR, AND NOT, AND condition. Keep or drop names. Use a new name if you need one.
customer [Cust-Name] loan [Loan-No] balance is £[Loan-Balance] at branch [Branch] AND customer [Cust-Name] has £[Balance] in account [Acc-No] at branch [Branch]
Now to get the algebra replace:
- every statement by its table/relation
- every AND of table/relation statements by ⋈ (natural join)
- every OR of table/relation statements (which must have the same columns/attributes) by ∪ (union)
- every AND NOT of statements (which must have the same columns/attributes) by \ (difference)
- every AND condition by σ condition
- every Keeping names to keep by π names to keep (projection) (and Dropping by π names to keep)
- every column/attribute renaming in a given statement by ρ (rename).
∩ (intersection) and x (product) are special cases of ⋈ (∩ for both sides the same columns/attributes and x for no shared columns/attributes).
(ρ Loan-Balance/Balance Loan) ⋈ Deposit