Algebra数据库关系代数教案.ppt

7 9 10 10 * Relational Algebra Overview Relational algebra offers a concise way to express queries. Form the basis for “real” query languages (SQL). Much more concise than SQL. It is widely used by database professionals. Basic operations. Additional operations. Example instances R1 S1 S2 “Sailors” and “Reserves” relations for our examples. Basic operations Selection Projection Union Difference Cartesian product Rename Selection (1/3) S2 Selection (2/3) S2 Selection (3/3) S2 Projection S2 Duplicate rows removed This is true for all the relational algebra operations. Operator composition S2 Union Two input relations must be union-compatible Same number of attributes. `Corresponding’ attributes have the same type. S1 S2 Intersection and Difference S1 S2 Cartesian Product Each row of S1 is paired with each row of R1. S1 ? R1 Operation Composition Renaming ?My-table(id, name, level, age) (S1) S1 My-table Example 1 ACC(acc-id, cust-id, balance) Find all accounts with balances 1200. ?balance 1200 (ACC) Find the acc-id of all accounts with balances 1200. ?acc-id (?balance 1200 (ACC)) Find the acc-id of all accounts with balances 1200 and cust-id = 100. ?acc-id (?balance 1200 ?? cust-id = 100 (ACC)) Find the acc-id of all accounts with balances 1200 or cust-id = 100. ?acc-id (?balance 1200 ?? cust-id = 100 (ACC)) Example 2 ACC(acc-id, cust-id, balance) LOAN(loan-id, cust-id, amount) Find the cust-id of customers who have both loans or accounts. ?cust-id (ACC) ? ?cust-id (LOAN) LOAN(loan-id, cust-id, amount) CUST(cust-id, name, address) Find the names of customers who have loans ?name(σLOAN.cust-id = CUST.cust-id (LOAN ? CUST)) Overview Relational algebra offers a concise way to express queries. Form the basis for “real” query languages (SQL). Much more concise than SQL. It is widely used by database professionals. Basic operations. Additional operations. Additional operations Natural join Division These operations can be transformed to basic oper