ICOM 5016 – Database Systems

1. Relational Algebra ICOM 5016 – Database Systems Dr. Amir H. Chinaei Department of Electrical and Computer Engineering University of Puerto Rico, Mayagüez Slides are adapted from:

2. Relational Algebra • Fundamental Property • Operands: relations • Operators: the most required functions in relational model • Unary or Binary • Result is also a relation Introduction. one of the two formal query languages associated with the relational model.

3. Roadmap • Core R.A. • includes basic operators • Advanced R.A. • includes additional operators (that mostly can be defined in terms of Core R.A.) • Extended R.A. • Extends R.A. to support more features of SQL

4. R.A. Operators • Basic • Selection • Projection • Union • Difference • (Cross-)product, • Additional • Intersection • Rename • Joins • Division

5. Selection • Selection extracts some rows from a relation • R2 ::= C(R1) • R2 includes only those rows of R1 that satisfy C • C is a Boolean condition

6. Example: Selection Provides R2 ::= price<6.50(Provides) R2

7. Projection • Projection extracts some columns from a relation • R2 := A(R1) • A consists of some attributes of R1 • R2 includes only those columns of R1 that are in A. • Eliminate duplicate tuples, if any.

8. Example: Projection Provides R2 R2 ::= Foodservice, Food(Provides)

9. Set-Based Operators • R.A. borrows the following standard operators on sets: • Union. R3 ::= R1 U R2 • Intersection. R3 ::= R1∩ R2 • Difference. R3 ::= R1 - R2 • Cross-Product (Cartesian product). R3 ::= R1  R2

10. Union • Union puts together all tuples that are in R1 or R2 or both • R3 ::= R1 U R2 • R1 and R2 must be union-compatible: • They have the same number of attributes • Corresponding attributes have the same domains

11. Intersection • Intersection puts together all tuples that are in both relations R1 and R2 • R3 ::= R1 ∩ R2 • R1 and R2 must be union-compatible

12. Difference • Differenceconsists ofthose tuples of first relation which do not appear in the other • R3 ::= R1 - R2 • R1 and R2 must be union-compatible

13. Product • Product pairs each tuple t1 of R1 with each tuple t2 of R2 • R3 ::= R1  R2 • Schema of R3 is the attributes of R1 and R2, in order • If an attribute name A is repeated in both R1 and R2, it is represented by R1.A and R2.A

14. Example: Product R1 R3 ::= R1  R2 R3 R2

15. F F F (F) Renaming • Renaming is useful to resolve possible name conflicts • R2 ::=  (R2( ), R1) • is a list of terms of the form AO AN • Returns R1 with columns renamed according to • Simplified notation: R2 ::=  R1

16. Example: Renaming Student Students Students ::=  (Name  Fullname, Major  Plan) Student

17. Joins One of the most useful operators in R.A. • The most commonly used way to combine several relations • Condition Joins (Theta-join) • Equijoin • Natural Join • Outer join

18. Theta-Join • Theta-join joins two relations and extracts some rows from the result • R3 ::= R1 C R2 = C(R1  R2) • Common special case: C consists solely of equalities (Equijoin) • The schema of the result consists of attributes of R1 followed by those attributes of R2 that do not appear in C.

19. R1 ::= Food Price<5.50 Name=Food CheapFood Example: Theta-Join Food CheapFood R1

20. Natural Join • A further special case • R3 ::= R1 R2 = A’( C’(R1  R2) ) • An Equijoin in which equalities are specified on all common attributes of R1 and R2 • The result is guaranteed not to have attributes with the same name

21. R1 ::= Food1 CheapFood Example: Natural Join Food1 CheapFood R1

22. Division • Division. • R3 ::= R1 / R2 • It is not needed as often • But sometimes simplifies a query significantly • “Find Foodservices that provide all foods” • Properties: • R2’s attributes  R1’s attributes • R3’s attributes = R1’s attributes – R2’s attributes • R3 contains all tuples (T3) s.t. for every tuple T2  R2, T3.T2  T1 (set of tuples of R1) • In other words, R3  R2  R1

23. Examples: Division R5 R1 R2 R6 R4 R3 * * * * * * * * * * R3 ::= R1 / R2 * * R4 ::= R1 / R3 R6 ::= R1 / R5

24. R2 ::= Foodservice(price<6.0 (Provides Food1)) R2 ::= Foodserviceprice<6.0 (Provides Food1) R2 ::= Foodserviceprice<6.0 Provides Food1 Complex Expressions • Expressions with several operators • Example: • Precedence of operators: • Select, Project, Renaming • Products, Division, Joins • Intersection • Union, Difference • Therefore, we could write: • How about this?

25. Expression Trees Useful when writing complex queries • Example: “Find all Foodservice names that provide meatball for less that 6 bucks and their rate is over 6. Name Rate>6 Price<6.0 Food=“Meatball” FoodService Provides Food1

26. Hands-on Example 1 • “Find all students who like Mofongo and frequent Pollo Tropical”. (Contest 1)

27. Hands-on Example 2 • “Find names of all ICOM students who do not frequent a foodservice that its rate is greater than 8” (Contest 2)

28. Hands-on Example 3 • “Find all Foodservice names that provide at least two foods with the same price” (Contest 3)

29. Relational Algebra on Bags Recall: SQL tables are multisets (bags) • Bags are a more general data structure • Some operations are more efficient on bags • Following R.A. operators produce a different result on a bag than on a set • Projection, Division, Union, Intersection, Difference • Bag projection and bag division do not eliminate duplicates from the result.

30. UB, ∩B, –B • Bag Union. R3::= R1 UB R2 • R3: all tuples of R1 and R2 • Example: {1,1,2} UB {1,1,1,3}= {1,1,1,1,1,2,3} • Bag Intersection. R3 ::= R1 ∩B R2 • R3: common tuples of R1 and R2 with the minimum show up • Example: {1,1,2} ∩B {1,1,1,3} = {1,1} • Bag Difference. R3 ::= R1 –B R3 • R3: all tuples appear in R1 – # of times appear in R2 • Example: {1,1,2} –B {1,1,1,3} = {2} • Do sets laws hold for bags?

31. Extended R.A. • Distinct eliminates duplicates from R1 • R2 ::=  (R1) • Order sorts R1 based on list of attributes in A • R2 ::= A (R1) • Extended Projection • R2 ::= A(R1) • A consists of some attributes of R1, as well as (arithmetic) expressions and attribute renaming

32. Examples: , ,  R1 R2 = (R1) R3 R4=A2,A1(R3) R5 =  A1 NewA1, A1, A1+A2,A2(R1)

33. Extended R.A. cont’d • Aggaggregates values of an attribute to compute a function • Sum(A), Avg(A), Count(A), Min(A), Max(A) • Not applicable on a relation, rather on an attribute • Group groups tuples based on attributes • R2 ::= A (R1) • Where, A consists of some attributes of R1 • may include some aggregate functions

34. Examples: Agg,  R1 Sum (A1) = 7 Count (A1) = 5 Avg (A3) = 4 R2 =A1,A2, Avg(A3), Sum(A2) (R1)

35. R1 O R2 Extended R.A. cont’d • Outer join • R3 ::= R1 O R2 • An extended natural join in which no tuple from R1 or R2 is omitted. • Example: R1 R2

36. Relational Completeness Definition. A query language is at least as expressive as core relational algebra is said to be relationally complete. • Examples • Safe Relational Calculus • R.A. • SQL (has additional expressive power) Recommended Exercise: from Ramakrishnan’s 4.1—4.7 (ignore Relational Calculus)