Introduction to Relational Algebra: Properties, Operations, and Examples

1. Chapter on Relational Algebra Ritu CHaturvedi Some figures are adapted from T. COnnolly

2. INTRODUCTION • Relational algebra and relational calculus are formal languages associated with the relational model. • Informally, relational algebra is a (high-level) procedural language and relational calculus a non-procedural language. • However, formally both are equivalent to one another. • A language that produces a relation that can be derived using relational calculus is relationally complete. Ritu CHaturvedi Some figures are adapted from T. COnnolly

3. Properties of Relational Algebra • Procedural language • Operators operate on one or more relations and the result is always a relation without changing the original relations. • This property is called closure. (If we perform one or more of the RA operations on a relational DB, we get back a result set that belongs to the same DB => relational DB is closed with respect to the operations of RA ). • Both operands and results are relations, so output from one operation can become input to another operation. • Allows expressions to be nested, just as in arithmetic. Ritu CHaturvedi Some figures are adapted from T. COnnolly

4. Properties of Relational Algebra • 5 basic operations in relational algebra: Selection, Projection, Cartesian product, Union, and Set Difference. (These perform most of the data retrieval operations needed). • Also have Join, Intersection, and Division operations, which can be expressed in terms of 5 basic operations. Ritu CHaturvedi Some figures are adapted from T. COnnolly

5. Unary Operators • Select () (select in Class Notes ) • Project (π) (projectin Class Notes ) Ritu CHaturvedi Some figures are adapted from T. COnnolly

6. Binary Operators • Set Operations • Union • Intersection • Difference (minus) • Cartesian Product (join) • Join Operations • Theta Join • Equijoin • Natural Join • Outer Join • Division Ritu CHaturvedi Some figures are adapted from T. COnnolly

7. Figure Ritu CHaturvedi Some figures are adapted from T. COnnolly

8. Figure Ritu CHaturvedi Some figures are adapted from T. COnnolly

9. Example : Ritu CHaturvedi Some figures are adapted from T. COnnolly

10. UNARY OPERATORS Select () Selects a subset of tuples from a relation R that satisfy a certain predicate (or a Boolean condition). R1 = <Predicate>(R) Degree of R1 is the same as degree of R Cardinality of R1 <= Cardinality of R Ritu CHaturvedi Some figures are adapted from T. COnnolly

11. Examples • Select employees who work in department number 4. • Select employees who work in department number 4 and whose salary is greater than $70,000. Ritu CHaturvedi Some figures are adapted from T. COnnolly

12. Project () • Selects vertical subsets of a relation R extracting the values of specified attributes of R eliminating duplicates. • R1 = <attribute-list>(R) • Degree of R1  Degree of R • Cardinality of R1  Cardinality of R (!!) Ritu CHaturvedi Some figures are adapted from T. COnnolly

13. Example: • Retrieve the First Name, Last Name and Salary of all employees. • Retrieve the First Name, Last Name and Salary of employees who work in department 4. Ritu CHaturvedi Some figures are adapted from T. COnnolly

14. Example: Ritu CHaturvedi Some figures are adapted from T. COnnolly

15. Example: Ritu CHaturvedi Some figures are adapted from T. COnnolly

16. Set operations • Two relations R(A1,….An) and S(B1,…Bn) are said to be Union Compatible if they have the same degree n and Dom(AI) = Dom(BI) where 1 I  n Ritu CHaturvedi Some figures are adapted from T. COnnolly

17. Union R  S: is a relation that includes all tuples that are either in R or in S or in both R and S, duplicate tuples being eliminated. • If R and S have I and J tuples, respectively, union is obtained by concatenating them into one relation with a maximum of (I + J) tuples • Example: List the names of all students and instructors. Ritu CHaturvedi Some figures are adapted from T. COnnolly

18. Intersection • R  S: is a relation that includes all tuples that are in both R and in S. • Expressed using basic operations: R  S = R – (R – S) • Example: List the names of those who are both students and instructors. Ritu CHaturvedi Some figures are adapted from T. COnnolly

19. Difference R  S : is a relation that includes all tuples that are in R but not in S. Examples: • List the names of students who are not instructors. • List the names of instructors who are not students. Ritu CHaturvedi Some figures are adapted from T. COnnolly

20. Example of set operators Ritu CHaturvedi Some figures are adapted from T. COnnolly

21. Cartesian Product • R  S is a concatenation of every tuple of R with every tuple of S. • If Degree(R ) = n and Degree(S) = m , then Degree(R  S) = n + m • If Cardinality(R ) = t1 and Cardinality(S) = t2, then Cardinality(R  S) = t1 * t2 Ritu CHaturvedi Some figures are adapted from T. COnnolly

22. Al A B No 1 2 3 Al No A 1 A 2 A 3 B 1 B 2 B 3 Example : R X S R1 R2 R1 X R2 Ritu CHaturvedi Some figures are adapted from T. COnnolly

23. Example : Cartesian Product Ritu CHaturvedi Some figures are adapted from T. COnnolly

24. Example (Decomposing complex relations) List for all female employees, the names of their dependents. Ritu CHaturvedi Some figures are adapted from T. COnnolly

25. Solution to Slide 23 Ritu CHaturvedi Some figures are adapted from T. COnnolly

26. JOIN OPERATIONS • Join is a derivative of Cartesian Product equivalent to performing a Selection operation using the join predicate as the selection, over the Cartesian product of the two operand relations. • Join is the most difficult operation to implement in an RDBMS and is one of the reasons why relational systems have intrinsic performance problems. • See the figure in Slide 8 Ritu CHaturvedi Some figures are adapted from T. COnnolly

27. Natural Join : The Process • Links tables by selecting rows with common values in common attribute(s) • Three-stage process • Cartesian Product creates one table • Select yields appropriate rows • Project yields single copy of each attribute to eliminate duplicate columns Ritu CHaturvedi Some figures are adapted from T. COnnolly

28. Natural Join Information from two or more tables is combined Ritu CHaturvedi Some figures are adapted from T. COnnolly

29. Natural Join: R S • The Natural Join is an Equijoin( next slide) of the two relations R and S over all common attributes x. One occurrence of each common attribute is eliminated from the result. • Degree of a natural join = (Sum of degrees of R and S)  (number of attributes in x) Example: Retrieve the names of the managers of each department Ritu CHaturvedi Some figures are adapted from T. COnnolly

30. Other Joins • EquiJOIN • Links tables based on equality condition that compares specified columns of tables • Does not eliminate duplicate columns • Join criteria must be explicitly defined • Theta JOIN • EquiJOIN that compares specified columns of each table using operator other than equality one Ritu CHaturvedi Some figures are adapted from T. COnnolly

31. Other Joins • Outer JOIN • Matched pairs are retained • Unmatched values in other tables left null • Right and left Ritu CHaturvedi Some figures are adapted from T. COnnolly

32. Theta Join : R  S • The Theta Join operation defines a relation that contains tuples satisfying the predicate  from the Cartesian Product of R and S • R  S = (RS) • Degree of Theta Join is the sum of degrees of R and S Ritu CHaturvedi Some figures are adapted from T. COnnolly

33. Example : Theta Join • Retrieve the names of the managers of each department. Ritu CHaturvedi Some figures are adapted from T. COnnolly

34. Equijoin When the predicate  contains only equality(=), theta join is called Equijoin. Ritu CHaturvedi Some figures are adapted from T. COnnolly

35. Outer Join R S • The (left) outer join is a join in which tuples from R that do not have matching values in the common attributes of S are also included in the result relation. Missing values in the second relation are set to null. • ( • Example : Retrieve all employee names and the department names they manage(if they manage a department) Ritu CHaturvedi Some figures are adapted from T. COnnolly

36. Left Outer Join Ritu CHaturvedi Some figures are adapted from T. COnnolly

37. DIVISION OPERATOR R  S • Let relation R be defined over attribute set A and relation S be defined over attribute set B such that B  A. Let C = A  B i.e. C is the set of attributes of R That are not attributes of S. • The Division operation defines a relation over the attributes C that consists of the set of tuples from R that match the combination of every tuple in S. Ritu CHaturvedi Some figures are adapted from T. COnnolly

38. Example : Division - Retrieve the names of employees who work on all the projects that John Smith works on. • Slide 8 Ritu CHaturvedi Some figures are adapted from T. COnnolly

39. Example : Division Ritu CHaturvedi Some figures are adapted from T. COnnolly

40. More examples • Queries (RA) (Questions 1 to 7 : Solutions to be discussed in class) Ritu CHaturvedi Some figures are adapted from T. COnnolly