Relational Algebra Relational Algebra Procedural language Six - PDF document

Relational Algebra

Relational Algebra • Procedural language • Six basic operators – select: σ – project: ∏ – union: ∪ – set difference: – – Cartesian product: x – rename: ρ • The operators take one or two relations as inputs and produce a new relation as the result CMPT 354: Database I -- Relational Algebra 2

Composition of Operations • Building expressions using multiple operations • Example: σ A=C (r x s) A B C D E C D E A B A B C D E α α 1 10 a α 10 a α α α α β 1 10 a 1 10 a 1 β 10 a β β α β 10 a 1 20 b 2 β β 2 20 b β β α γ 20 b 1 10 b 2 γ r 10 b β α 2 10 a β β s 2 10 a β β 2 20 b β γ 2 10 b CMPT 354: Database I -- Relational Algebra 3

Rename Operation • Name, and therefore to refer to, the results of relational-algebra expressions – 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 returns the result of ( E ) ( , ,..., ) x A A A 1 2 n expression E under the name X, and with the attributes renamed to A 1 , A 2 , …., A n CMPT 354: Database I -- Relational Algebra 4

Banking Example • branch (branch_name, branch_city, assets) • customer (customer_name, customer_street, customer_city) • account (account_number, branch_name, balance) • loan (loan_number, branch_name, amount) • depositor (customer_name, account_number) • borrower (customer_name, loan_number) CMPT 354: Database I -- Relational Algebra 5

Example Queries • Find all loans of over $1200 σ amount > 1200 ( loan ) • Find the loan number for each loan of an amount greater than $1200 ∏ loan_number ( σ amount > 1200 ( loan )) loan (loan_number, branch_name, amount) CMPT 354: Database I -- Relational Algebra 6

Example Queries • Find the names of all customers who have a loan, an account, or both, from the bank ∏ customer_name ( borrower ) ∪ ∏ customer_name ( depositor ) • Find the names of all customers who have a loan and an account at the bank ∏ customer_name ( borrower ) ∩ ∏ customer_name ( depositor ) depositor (customer_name, account_number) borrower (customer_name, loan_number) CMPT 354: Database I -- Relational Algebra 7

Example Queries • Find the names of all customers who have a loan at the Perryridge branch ∏ customer_name ( σ branch_name=“Perryridge” ( σ borrower.loan_number =loan.loan_number ( borrower x loan ))) • Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank ∏ customer_name ( σ branch_name = “Perryridge” ( σ borrower.loan_number = loan.loan_number (borrower x loan))) – ∏ customer_name (depositor) CMPT 354: Database I -- Relational Algebra 8

Example Queries • Find the names of all customers who have a loan at the Perryridge branch – Answer 1 ∏ customer_name ( σ branch_name = “Perryridge” ( σ borrower.loan_number = loan.loan_number (borrower x loan))) – Answer 2 ∏ customer_name ( σ loan.loan_number = borrower.loan_number ( ( σ branch_name = “Perryridge” (loan)) x borrower)) CMPT 354: Database I -- Relational Algebra 9

Example Queries • Find the largest account balance – Aggregate max is not directly supported in relational algebra – Find those balances that are not the largest • Rename account relation as d so that we can compare each account balance with all the others – Use set difference to find the max balance accounts ∏ balance (account) - ∏ account.balance ( σ account.balance < d.balance ( account x ρ d (account ))) account (account_number, branch_name, balance) CMPT 354: Database I -- Relational Algebra 10

Formal Definition • A basic expression in the relational algebra consists of either one of the following: – A relation in the database – A constant relation • Let E 1 and E 2 be relational-algebra expressions; the following are all relational-algebra expressions: – E 1 ∪ E 2 – E 1 – E 2 – E 1 x E 2 – σ p (E 1 ), P is a predicate on attributes in E 1 – ∏ s (E 1 ), S is a list consisting of some of the attributes in E 1 – ρ x (E 1 ), x is the new name for the result of E 1 CMPT 354: Database I -- Relational Algebra 11

Additional Operations • The additional operations do not add any power to the relational algebra, but can simplify writing common queries – Set intersection – Natural join – Division – Assignment CMPT 354: Database I -- Relational Algebra 12

Set-Intersection Operation – Example A B A B A B α α 1 α 2 2 α β 2 3 r ∩ s β 1 s r CMPT 354: Database I -- Relational Algebra 13

Set-Intersection Operation • r ∩ s = { t | t ∈ r and t ∈ s } – In basic operators, we only have set difference but no intersection • Assume: – r, s have the same arity – attributes of r and s are compatible • r ∩ s = r – (r – s) CMPT 354: Database I -- Relational Algebra 14

Natural Join Operation – Example A B C D E A B C D B D E α α α α α a 1 α a 1 a 1 α α γ β γ 1 a β a 2 a 3 α γ α γ β 1 a γ 4 b 1 a α γ γ α γ a 1 δ a 1 b 2 δ β δ δ β 2 b ∈ 2 b 3 b r s r s CMPT 354: Database I -- Relational Algebra 15

Natural-Join Operation • Let r and s be relations on schemas R and S respectively. r s is a relation on schema R ∪ S obtained as follows: – Consider each pair of tuples t r from r and t s from s – If t r and t s have the same value on each of the attributes in R ∩ S, add a tuple t to the result, where • t has the same value as t r on r • t has the same value as t s on s CMPT 354: Database I -- Relational Algebra 16

Example • R = (A, B, C, D) • S = (E, B, D) • Result schema = (A, B, C, D, E) • r s is defined as ∏ r.A, r.B, r.C, r.D, s.E ( σ r.B = s.B ∧ r.D = s.D (r x s)) CMPT 354: Database I -- Relational Algebra 17

Division Operation – Example A B α 1 α 2 B A α 3 α β 1 1 γ 1 β 2 δ 1 r ÷ s δ s 3 δ 4 ∈ 6 ∈ 1 β 2 r CMPT 354: Database I -- Relational Algebra 18

Division Operation • Let r and s be relations on schemas R and S respectively where R = (A 1 , …, A m , B 1 , …, B n ) and S = (B 1 , …, B n ) – The result of r ÷ s is a relation on schema R – S = (A1, …, Am) – r ÷ s = { t | t ∈ ∏ R-S (r) ∧ ∀ u ∈ s ( tu ∈ r ) }, where tu means the concatenation of tuples t and u to produce a single tuple • Suited to queries that include the phrase “for all” CMPT 354: Database I -- Relational Algebra 19

Another Division Example A B C D E α α A B C a a 1 D E α γ a a 1 α γ a α γ a b 1 a 1 γ γ a β γ a a 1 b 1 r ÷ s β γ a b 3 s γ γ a a 1 γ γ a b 1 γ β a b 1 r CMPT 354: Database I -- Relational Algebra 20

Properties of Division Operation • Let q = r ÷ s, q is the largest relation satisfying q x s ⊆ r • Let r(R) and s(S) be relations, and let S ⊆ R, r ÷ s = ∏ R-S (r) – ∏ R-S (( ∏ R-S (r) x s ) – ∏ R- S,S (r )) – ∏ R-S,S (r) simply reorders attributes of r – ∏ R-S ( ∏ R-S (r) x s) – ∏ R-S,S (r)) gives those tuples t in ∏ R-S (r) such that for some tuple u ∈ s, tu ∉ r CMPT 354: Database I -- Relational Algebra 21

Assignment Operation • The assignment operation ( ← ) provides a convenient way to express complex queries – Write query as a sequential program consisting of a series of assignments followed by an expression whose value is displayed as a result of the query – Assignment must always be made to a temporary relation variable • Example: compute r ÷ s – temp 1 ← ∏ R-S (r ), temp 2 ← ∏ R-S ((temp 1 x s) – ∏ R-S,S (r )) result = temp 1 – temp 2 • The result to the right of the ← is assigned to the relation variable on the left of the ← – May use variable in subsequent expressions CMPT 354: Database I -- Relational Algebra 22

Bank Example Queries • Find the names of all customers who have a loan and an account at bank ∏ customer_name ( borrower ) ∩ ∏ customer_name ( depositor ) • Find the name of all customers who have a loan at the bank and the loan amount ∏ ( ) borrower loan customer-n ame, loan- number, am ount CMPT 354: Database I -- Relational Algebra 23

Bank Example Queries • Find all customers who have an account from at least the “Downtown” and the “Uptown” branches – Answer 1 ∏ customer_name ( σ branch_name = “Downtown ” ( depositor account )) ∩ ∏ customer_name ( σ branch_name = “Uptown ” ( depositor account )) – Answer 2: using a constant relation ∏ customer_name, branch_name ( depositor account ) ÷ ρ temp(branch_name ) ({( “Downtown” ) , ( “Uptown” )}) CMPT 354: Database I -- Relational Algebra 24

Example Queries • Find all customers who have an account at all branches located in Brooklyn city ∏ customer_name, branch_name ( depositor account ) ÷ ∏ branch_name ( σ branch_city = “Brooklyn” ( branch )) CMPT 354: Database I -- Relational Algebra 25