Understanding Relational Algebra: A Foundation for Query Languages

1. Relational Algebra – Basis for Relational Query Languages Based on presentation by Juliana Freire

2. Formal relational query languages

3. Is this the Algebra you know? Algebra -> operators and atomic operands Expressions -> applying operators to atomic operands and/or other expressions Algebra of arithmetic: operands are variables and constants, and operators are the usual arithmetic operators E.g., (x+y)*2 or ((x+7)/(y-3)) + x Relational algebra: operands are variables that stand for relations and relations (sets of tuples), and operations include union, intersection, selection, projection, Cartesian product, etc – E.g., (π c-ownerChecking-account) ∩ (π s-ownerSavings-account)

4. What is a query? A query is applied to relation instances, and the result of a query is also a relation instance. (view, query) – Schemas of input and output fixed, but instances not. • Operators refer to relation attributes by position or name: – E.g., Account(number, owner, balance, type) – Positional notation easier for formal definitions, named-field notation more readable. – Both used in SQL

5. Relational Algebra Operations • The usual set operations: union, intersection, difference • Operations that remove parts of relations: • selection, projection • Operations that combine tuples from two relations: • Cartesian product, join • Since each operation returns a relation, • operations can be composed!

6. Removing Parts of Relations • Selection – rows • Projection - columns

7. Selection: Example

8. Another selection

9. Example of Projection

10. Projection removes duplicates

11. Set Operations • Union • Intersection • Difference

12. What happens when sets unite?

13. Union Operation – Example A B A B • Relations r, s:    1 2 1   2 3 s r A B     1 2 1 3 • r  s:

14. Union Example

15. Union Compatibility

16. Intersection

17. Set Difference Operation – Example • Relations r, s: A B A B    1 2 1   2 3 s r • r – s: A B   1 1

18. Difference

19. Another way to show intersection?

20. Summary so far: • E1 U E2 : union • E1 - E2 : difference • E1 x E2 : cartesian product • c(E1) : select rows, c = condition (book has p for predicate) • IIs(E1) : project columns : s =selected columns •  x(c1,c2) (E1) : rename, x is new name of E1, c1 is new name of column

21. Combining Tuples of Two Relations • Cross product (Cartesian product) • Joins

22. Cartesian-Product Operation – Example • Relations r, s: A B C D E   1 2     10 10 20 10 a a b b r s • r xs: A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b

23. Cross Product Example

24. Cross Product • How to resolve???? Renaming operator:  Rename whole relation: Teacher X secondteacher(Teacher)  Teacher.t-num, Teacher.t-name, secondteacher.t-num, secondteacher.t-name OR rename attribute before combining: Teacher X secondteacher(t-num2, t-name2)(Teacher) t-num, t-name, t-num2, t-name2 OR rename after combining c(t-num1, t-name1, t-num2, t-name2)(Teacher X Teacher) t-num1, t-name1, t-num2, t-name2

25. Join: Example

26. Join : Example

27. Condition Join

28. Equi and Natural Join

29. Divide operator

30. Divide Operation

31. Divide Definition

32. When to divide?

33. Division Example

34. Dividing without division sign

35. Working out an example

36. Assignment operation

37. Why would we use Relational Algebra?????

38. Equivalencies help

39. ER vs RA • Both ER and the Relational Model can be used to model the structure of a database. • Why is it the case that there are only Relational Databases and no ER databases?

40. RA vs Full Programming Language • Relational Algebra is not Turing complete. There are operations that cannot be expressed in relational algebra. • What is the advantage of using this language to query a database?

41. Summary of Operators updated • Summary so far: • E1 U E2 : union • E1 - E2 : difference • E1 x E2 : cartesian product • c(E1) : select rows, c = condition (book has p for predicate) • IIs(E1) : project columns : s =selected columns •  x(c1,c2) (E1) : rename, x is new name of E1, c1 is new name of column • E1 E2 : division • E1 E2 : join, c = match condition

43. Practice • Find names of stars who’ve appeared in a 1994 movie • Information about movie year available in Movies; so need an extra join: σyear=1994(πname(Stars ⋈ AppearIn ⋈ Movies)) • A more efficient solution: πname(Stars ⋈ AppearIn ⋈ (σyear=1994( Movies)) • An even more efficient solution: πname(Stars ⋈ πname(AppearIn ⋈ (πmovieIdσyear=1994(Movies))) A query optimizer can find this, given the first solution!

44. Extended Relational Algebra Operations • Generalized projection • Outer join • Aggregate functions

45. Generalized projection – calculate fields

46. Aggregate Operation – Example A B C • Relation r:         7 7 3 10 • gsum(c) (r) sum(c ) 27

47. Aggregate • Functions on more than one tuple • Samples: • Sum • Count-distinct • Max • Min • Count • Avg • Use “as” to rename branchnameg sum(balance) as totalbalance (account)

48. Aggregate Operation – Example • Relation account grouped by branch-name: branch_name account_number balance Perryridge Perryridge Brighton Brighton Redwood A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 branch_nameg sum(balance) (account) branch_name sum(balance) Perryridge Brighton Redwood 1300 1500 700

49. Outer Join • Keep the outer side even if no join • Fill in missing fields with nulls

50. branch_name loan_number amount Downtown Redwood Perryridge L-170 L-230 L-260 3000 4000 1700 customer_name loan_number Jones Smith Hayes L-170 L-230 L-155 Outer Join – Example • Relation loan • Relation borrower