Relational Algebra

1. Relational Algebra Dashiell Fryer

2. What is Relational Algebra? • Relational algebra is a procedural query language. • It consists of the select, project, union, set difference, Cartesian product, and rename operations. • Set intersection, division, natural join, and assignment combine the fundamental operations.

3. Select Operation • Select operation returns a relation that satisfies the given predicate from the original relation. • Denoted by lowercase sigma σ. • Example: σbranch-name = “Perryridge”(loan) from page 89 returns a relation of the tuples from relation loan whose branch is Perryridge. • =,≠,≤,≥,<,>, Λ,¬,v are legal in the predicate.

4. Project • Unary operation • Denoted by uppercase pi Π. • Returns a relation with only the specified attributes. • Example: page 90 Πloan-number, amount (loan) lists all of the loan numbers and the amounts.

5. Composition of Operations • Results of relational operations are relations themselves. • Compositions of operations form a relational-algebra expression.

6. Union • Denoted, as in set theory, by . • Binary operation • Results in a relation with all of the tuples that appear in either or both of the argument relations.

7. Union (Cont’d) • Unions must be between compatible relations • Both relations must have the same number of attributes. • Domains of the ith attribute of the first and the ith attribute of the second must be the same for all i.

8. Set Difference • Denoted by - • Binary operation • R – S produces all tuples in R but not in S • Relations must be compatible under the same conditions as the union operation.

9. Cartesian-Product • Denoted by a cross (×) • Binary operation • Combines any two relations • R × S is the Cartesian-Product of R and S • Output has the attributes of both relations

10. Cartesian-Product (Cont’d) • Repeated attribute names are preceded by the relation they originated from. • Example: page 93 r= borrower × loan (borrower.customer-name, borrower.loan-number, loan.loan-number, loan.branch-name, loan.amount)

11. Rename • Unary operation • Denoted by lowercase Greek letter rho ρ • Useful for naming the unnamed relations returned from other operations.

12. Formal Definition • Relational Algebra acts on either a relation in the database or a constant relation. • Suppose E1 and E2 are relational-algebra expressions

13. Formal Definition (Cont’d) • All relational-algebra expressions are: E1 E2 E1 – E2 E1 × E2 σP(E1), P is the predicate ΠS(E1), S is a list of attributes in E1 ρx(E1), x is the new name of E1

14. Additional Operations • The fundamental operations can be composed to make new, simpler operations. • Set-Intersection • Natural-Join • Division • Assignment

15. Set-Intersection • Denoted by the symbol . • Results in a relation that contains only the tuples that appear in both relations. • R  S = R – (R – S) • Since set-intersection can be written in terms of set-difference, it is not a fundamental operation.

16. Natural-Join • Denoted by |x|. • Binary operation • Creates a Cartesian-product of the arguments then performs selection to force equality on attributes that appear in both relations

17. Division • Denoted by  • Binary Operation • Used in queries that include the phrase “for all”.

18. Division (Cont’d) • Division is an operation on schema R – S • A tuple t is in r  s if and only if: • t is in ΠR – S(r) and • For every tuple ts in s, there is a tuple tr in r satisfying both of the following: a. tr[S] = ts[R] b. tr[R – S] = t

19. Assignment • Denoted by the  symbol • Works like assignment operations in programming languages • Assignment must always be made to a temporary relation • Does not result in display to the user

20. Summary • Relational Algebra defines a set of operations that operate on relations and result in new tables. • Operations can be composed • Additional operations simplify the algebra but do not add any additional power.

21. The End