Relational Algebra

1. Relational Algebra Ch 7.4-7.6

2. Outline • Set Theoretic operations • union, intersection, difference, Cartesian product • Relational operations • project, select, rename, join, division

3. Set Theoretic Operations • Set Operators • union • intersection • difference • Cartesian Product • Set operators are binary and will only work on two relations or sets of data.

4. Set Theoretic Operations • Union: R ∪ S, is a relation that includes all tuples that are either in R or in S or in both (duplicates are eliminated). • Intersection: R ∩ S, is a relation that includes all tuples that are in both R and S. • Set Difference: R – S, is a relation that includes all tuples that are in R but not in S. • Union and Intersection are commutative and associative. • R ∪ S ≡ S ∪ R and R∪(S∪T) ≡ (R∪S)∪T • R ∩ S ≡ S ∩ R and R ∩(S ∩ T) ≡ (R ∩ S) ∩ T • R – S ≠ S – R (Difference is not commutative).

5. Set Theoretic Operators (cont’d) • Can only be used on union compatible sets • R (A1, A2, …, AN) and S(B1, B2, …, BN) are union compatible if: • degree (R) = degree (S) = N • domain (Ai) = domain (Bi) for all i

6. Union (⋃) • Assuming R & S are union compatible… • union: R⋃S is the set of tuples in either R or S or both. • since it is a set, there are no duplicate tuples

7. Set Operations (Example) Two union compatible relations Student ∪ Instructor

8. Example of Union Dept_phones (dcode, phone_number) R = S = R ⋃ S = ??? S ⋃ R = ???

9. Intersection (⋂) • Assuming that R & S are union compatible: • intersection: R ⋂ S is the set of tuples in both R and S • Note that R ⋂ S = S ⋂ R

10. Set Operations (Example) Student ∩ Instructor Two union compatible relations

11. Example: use R and S from before… • Dept_phones (dcode, phone_number) • R = S = • R ⋂ S = ??? • S ⋂ R = ???

12. Difference (-) • Difference: R – S is the set of tuples that appear in R but do not appear in S • Is (R – S) = (S – R) ??? • Example: R – S

13. Set Operations (Example) STUDENT – INSTRUCTOR Two union compatible relations INSTRUCTOR – STUDENT

14. Cartesian Product (X) • Sets do not have to be union compatible (usually not) • In general, the result of: R(A1, A2, …, AN) X S(B1, B2, …, BM) is Q (A1, A2, …, AN, B1, B2, …, BM) • If R has C tuples and S has D tuples, the result is C*D tuples.

15. Cartesian Product • Also called “cross product” • Note that union, intersection and cross product are commutative and associative

16. Cartesian Product Example R = Dept_phones (dcode, phone_number) S = Department (code, office) What is R X S? R = S =

17. Cartesian Product • Q has one tuple for each combination of tuples, one from R and one from S. • The operation applied by itself is generally meaningless unless it is followed by a selection. • It is common to use Select operation with Cartesian Product, so a special operation (Join) was developed to specify this sequence in a single operation.

18. Cartesian Product (cont) • Ex: Retrieve for each female employee a list of her dependents. FEMALE_EMPS ← SEX = “F” (EMPLOYEE) EMPNAMES ← PFNAME,LNAME,SSN (FEMALE_EMPS) EMP_DEPENDENTS ← EMPNAMES  DEPENDENT ACTUAL_DEPENDENTS ← SSN = ESSN (EMP_DEPENDENTS) RESULT ← PFNAME,LNAME,DEPENDENT_NAME (ACTUAL_DEPENDENTS)

19. Relational Operations • developed specifically for relational databases • used to manipulate data in ways that set operations can’t • select • project • join • division

20. Selection Operation • Used to select a subset of the tuples • Selection is based on a “select condition” • The selection condition is basically a filter • Notation: σ<condition>(<Relation>)

21. Selection (cont’d) • To process a selection, we: • look at each tuple • see if we have a match (based on the condition) • The degree of the result is the same as the degree of the relation | σ | = | r(R) | • c1 (c2 (… (cn(R)) …)) = c1 AND c2 AND … AND cn (R)

22. The SELECT Operation () • Ex: Select all employees who work on department 4. DNO = 4 (EMPLOYEE) Ex: Select all employees who work on department 4 and their salaries are more than 25000 or who work on department 5 and their salaries are more than 30000.  (DNO = 4 AND SALARY>25000) OR (DNO=5 AND SALARY>30000) (EMPLOYEE)

23. Selection Example • Faculty (fnum, name, office, salary, rank)

24. Selection Example (cont’d) • σ salary > 27000(Faculty) • σrank = associate(Faculty) • σ fnum = 34567(Faculty)

25. Selection Example (cont’d) • σ(salary>26000) and (rank=associate) (Faculty) • σ(salary<=26000) and (rank !=associate) (Faculty) • σmax(salary) (Faculty)

26. Selection (cont’d) • For the condition • any combination of expressions that can be resolved to a boolean value with the relation is okay • For the relation • any relational expression that resolves to a relation is okay

27. Back to the Dept & Phone example σcode=comp(department X dept_phone) • First, we resolve the cross product of Department and Dept_phone. • We then apply the select to the resulting relation.

28. Selection (cont’d) σ is commutative σc1 (σc2(R)) = σc2 (σc1(R))

29. Project Operation () • A select filters out rows • A project filters out columns • reduces data (columns) returned • reduces duplicate columns created by cross product (why?) • creates a new relation

30. Project (cont’d) • Notation: <attribute list>(Relation) • Thedegree of the result is the number of attributes in the <attribute list> of the project. • |  | <= | r(R) |

31. The PROJECT Operation (P) • P<list1>(P<list2> (R)) = P<list1> (R) as long as <list2> contains the attributes in <list1>. • The project operation is not commutative. • Ex: List name and salary for each employee. • PLNAME,FNAME,SALARY (EMPLOYEE) • Ex: List sex and salary for each employee. PSEX,SALARY (EMPLOYEE)

32. Project Example Faculty (fnum, name, office, salary, rank) • name, office(Faculty) • fnum, salary(Faculty)

33. Rename () • Used to give a name to the resulting relation • Notation to make relational algebra easier to write and understand • We can now use the resulting relation in another relational algebra expression • Notation: <New Name> <Relational Expression>

34. Rename Example Faculty (fnum, name, office, salary, rank) Associatesσrank = associate(Faculty) Result name(Associates)

35. Join Operation • Join is a commonly used sequence of operators • Take the Cartesian product of two relations • Select only related tuples • (Possibly) eliminate duplicate columns

36. Join Example R = S = R1  R X S R2 σ dcode = code (R1) Result code, office, number (R2)

37. Join Example (cont’d) • You could do all of that, or you could do a join • Result R⋈dcode = code S

38. Kinds of Joins • There are 3 different kinds of joins • Theta join: A join with some condition specified • Equijoin: A join where the only comparison operator used is “=“ • Most common since most joins link together related tuples using a foreign key

39. Kinds of Joins (cont’d) • Natural join • Is an equijoin followed by the removal of duplicate (superfluous) column(s) • When people talk about “joins”, this is the one we most often mean. • A Natural join is denoted by (*) • Standard definition requires that the columns used to join the tables have the same name • This is a good idea b/c it makes relationships more obvious

40. Size of a Natural Join • if R contain nR tuples and S contain nS tuples, then the size of R ⋈<>S is between 0 and nR*nS • join selectivity = Expected size of the result nR * nS • This value is used by the optimizer to estimate the cost of the join.

41. Types of Joins • Left Outer Join • keep all of the tuples from the “left” relation • join with the right relation • pad the non-matching tuples with nulls • Right Outer Join • same as the left, but keep tuples from the “right” relation • Full Outer Join • same as left, but keep all tuples from both relations

42. Left Outer Join name phone name email • If we do a left outer join on R and S, and we match on the first column, the result is: R= S= name phone email

43. Right Outer Join name phone name email • If we do a right outer join on R and S, and we match on the first column, the result is: R= S= name phone email

44. Full Outer Join name phone name email • If we do a full outer join on R and S, and we match on the first column, the result is: R= S= name phone email

45. Complete Set of Relational Algebra Operators • It has been shown that {σ, , ⋃, –, X} is a complete set of operations. • Any other relational algebra operations can be expressed as a sequence of operations from this set.

46. Complete set (cont’d) • Intersection: R ⋂S ≡ (R ⋃ S) – ((R – S) ⋃ (S – R)) • Join: R <condition>S ≡ σ<condition>(R X S) • These other operations were added to express the power of relational algebra but are not really necessary.