1 / 36

ICOM 5016 – Database Systems

Explore the fundamentals of relational algebra, including basic and advanced operators, extended features, and set-based operations. Learn about selection, projection, union, difference, intersection, joins, division, and more.

vernak
Download Presentation

ICOM 5016 – Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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)

More Related