Cartesian-Product operation Combine information, r 1 x r 2 - PowerPoint PPT Presentation

Cartesian-Product operation • Combine information, r 1 x r 2 • Remember: a relation is a subset of a Cartesian product • Naming scheme to differentiate attributes: relation.attribute • only for non-distinct attributes • What tuples appear in r 1 x r 2 ? • tuples in r 1 x r 2 : all possible combinations of tuples in r 1 and r 2 • if r 1 has n 1 , and r 2 has n 2 , then r 1 x r 2 has n 1* n 2 tuples

Cartesian-Product • For relations r 1 (R 1 ), r 2 (R 2 ) : • r 1 x r 2 concatenation of R 1 and R 2 • For tuple t � r 1 x r 2 ,, then: • there is t 1 � r 1 and t 2 � r 2 such that: • t[R 1 ] = t 1 [R 1 ] and t[R 2 ] = t 2 [R 2 ]

Rename Operation • Name and refer to results of relational-algebra operations • Allows us to refer to a relation by more than one name. • Example: � x (E) � � � � � returns the expression E under the name X • If a relational-algebra expression E has arity n , then � x(A1, A2, …, An) (E) � returns the result of expression E under the name X , and with the � attributes renamed to A 1 , A 2 , …., A n .

Example of Rename in action Query: Find the highest amount loan_number amount L-11 900 L-15 1500 loan x � temp (loan) L-17 1000 Cartesian product with itself (renamed) L-93 500 Loan table � loan.amount < temp.amount (loan x � temp (loan)) Rows without the highest amount � loan.amount ( � loan.amount < temp.amount (loan x � temp (loan))) � balance (account) — � loan.amount ( � loan.amount < temp.amount (loan x � temp (loan)))

Another rename example Customers living in the same street and city as Green Customer_name Customer_street Customer_city Adams Spring Chicago Brooks Senator Brooklyn Curry Elm Harrison Glenn New Era Stamford Green Elm Harrison Hayes Elm Harrison Find out Green’s street and city: � customer_street, customer_city ( � customer_name=”Green” ) …… A Rename it: (why?) …… B � green_addr(street, city) ((A)) Take cross-product with original relation …… C Customer X B Select needed values and project � customer.customer_name ( � customer.customer_street=green_add.street ^ customer.customer_city=green_add.city (C))

Additional (derived) operations • Set-Intersection operation Customers who have both a loan account and a borrower account � customer_name (borrower) � � customer_name (depositor) • r � s = r - (r - s) customer_name customer_name Adams Hayes Curry Johnson Hayes customer_name Jones Jackson � Hayes Lindsay Jones Jones Smith Smith Smith Turner Williams Depositor Borrower