Relational Algebra Instructor Mohamed Eltabakh meltabakhcs wpi edu
Relational Algebra Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1
More Relational Operators 2
Joins l We mentioned Cartesian Product multiplies two relations RXS R l S What if I want to join R and S based on a certain condition? l Natural and Theta Joins 3
Natural Join: R ⋈ S (Join on the common attributes) l Consider relations l R with attributes AR, and l S with attributes AS. l Let A = AR ∩ AS = {A 1, A 2, …, An} The common attributes l In English l Natural join R ⋈ S is a Cartesian Product R X S with equality predicates on the common attributes (Set A) 4
Natural Join: R ⋈ S l R ⋈ S can be defined as : Project the union of all πAR – A, A, AS - A attributes (σR. A 1 = S. A 1 AND R. A 2 = S. A 2 AND … R. An = S. An (R X S)) Equality on common attributes Cartesian Product Common attributes appear once in the result 5
Natural Join: R ⋈ S: Example R S Implicit condition (R. B = S. B and R. D = S. D) R⋈S 6
Theta Join: R ⋈C S l l Theta Join is cross product, with condition C It is defined as : R ⋈C S = (σC (R X S)) R S Theta join can express both Cartesian Product & Natural Join Recommendation: Always use Theta join (more explicit and more clear) A B D C 1 2 2 3 3 2 4 5 A B D C 4 5 3 2 2 3 R ⋈ R. A>=S. CS 7
Example Queries Find customer names having account balance below 100 or above 10, 000 πcustomer_name (depositor ⋈ πaccount_number(σbalance <100 OR balance > 10, 000 (account))) This projection is optional 8
Assignment Operator: l The assignment operation (←) provides a convenient way to express complex queries on multiple line l Write query as a sequence of line consisting of: l l Series of assignments Result expression containing the final answer l Assignment must always be made to a temporary relation variable l May use a variable multiple times in subsequent expressions l Example: l R 1 (σ ((A=B) ^ (D>5)) (R – S)) ∩ W R 2 R 1 ⋈(R. A = T. C) T l Result R 1 U R 2 l 9
Example Queries For branches that gave loans > 100, 000 or hold accounts with balances >50, 000, report the branch name along whether it is reported because of a loan or an account R 1 πbranch_name, ‘Loan’ As Type (σamount >100, 000 (loan)) R 2 πbranch_name, ‘Account’ As Type(σbalance > 50, 000 (account))) Result R 1 U R 2 10
Example Queries Find customers having account balance below 100 and loans above 10, 000 R 1 πcustomer_name (depositor ⋈ πaccount_number(σbalance <100 (account))) R 2 πcustomer_name (borrower ⋈ πloan_number(σamount >10, 000 (loan))) Result R 1 ∩ R 2 11
More Relational Operators 12
Outer Join l An extension of the join operation that avoids loss of information l Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result l Uses null values to fill in empty attributes with no matching l Types of outer join between R and S l l l Left outer (R o⋈ S): preserve all tuples from the left relation R Right outer (R ⋈o S): preserve all tuples from the right relation S o Full outer (R ⋈ S): preserve all tuples from both relations 13
Left Outer Join (R o⋈ S): Example R S R⋈S (R o⋈ S) 14
Right Outer Join (R ⋈o S): Example R S R⋈S (R ⋈o S) 15
Full Outer Join (R ⋈ S): Example o R S R⋈S o (R ⋈ S) 16
Outer Join l Outer Join also applies to theta join R ⋈c S o o (R ⋈c. S) R o⋈c. S Any arbitrary condition for the join is allowed 17
Duplicate Elimination: (R) l l Delete all duplicate records Convert a bag to a set R (R) A B 1 2 A B 3 4 1 2 18
Grouping & Aggregation operator: l Aggregation function takes a collection of values and returns a single value as a result l l l avg: average value min: minimum value max: maximum value sum: sum of values count: number of values Grouing & Aggregate operation in relational algebra l l g 1, g 2, …gm, F 1(A 1), F 2(A 2), …Fn(An) (R) R is a relation or any relational-algebra expression g 1, g 2, …gm is a list of attributes on which to group (can be empty) Each Fi is an aggregate function applied on attribute Ai within each group 19
Grouping & Aggregation Operator: Example R sum(c)(R) S branch_name, sum(balance)(S) 20
Example Queries Find customer names having loans with sum > 20, 000 πcustomer_name (σsum > 20, 000 ( customer_name, sum(amount)(loan ⋈ borrower))) 21
Example Queries Find the branch name with the largest number of accounts R 1 branch_name, count. Accounts count(account_number)(account) R 2 Max max(count. Accounts)(R 1) Result πbranch_name(R 1 ⋈count. Accounts = Max R 2) 22
Example Queries Find account numbers and balances for customers having loans > 10, 000 πaccount_number, balance ( (depositor ⋈ account) ⋈ (πcustomer_name (borrower ⋈ (σamount >10, 000 (loan)))) ) 23
Reversed Queries (what does it do)? πcustomer_name(customer) - πcustomer_name(borrower) Find customers who did not take loans 24
Reversed Queries (what does it do)? R 1 ( Max. Loan max(amount)(σbranch_name= “ABC” (loan))) Result πcustomer_name(borrower ⋈ (R 1 ⋈Max. Loan=amount^branch_name= “ABC” loan)) Find customer name with the largest loan from a branch “ABC” 25
Summary of Relational-Algebra Operators l Set operators l Union, Intersection, Difference l Selection & Projection & Extended Projection l Joins l Natural, Theta, Outer join l Rename & Assignment l Duplicate elimination l Grouping & Aggregation 27
- Slides: 26