1. Relational Algebra Eugene Sheng Computer Science Department NIU

2. Introduction • one of the two formal query languages of the relational model • collection of operators for manipulating relations • Operators: two types of operators • Set Operators: Union(),Intersection(), Difference(-), Cartesian Product (x) • New Operators: Select (), Project (), Join (⋈)

3. Introduction – cont’d • A Relational Algebra Expression: a sequence of relational algebra operators and operands (relations), formed according to a set of rules. • The result of evaluating a relational algebra expression is a relation.

4. Selection • Denoted by c(R) • Selects the tuples (rows) from a relation R that satisfy a certain selection condition c. • It is a unary operator • The resulting relation has the same attributes as those in R.

5. Example 1: S: • state=‘IL’(S)

6. Example 2: • CREDIT  3(C) C:

7. Example 3 SNO=‘S1’and CNO=‘C1’(E) E:

8. Selection - Properties • Selection Operator is commutative C1(C2 (R)) = C2(C1 (R)) • The Selection is an unary operator, it cannot be used to select tuples from more than one relations.

9. Projection • Denoted by L(R), where L is list of attribute names and R is a relation name or some other relational algebra expression. • The resulting relation has only those attributes of R specified in L. • The projection is also an unary operation. •  Duplication is removed from the result.

10. Projection - Example • Example 1: STATE (S)

11. Projection - Example Example 2: CNAME, DEPT(C)

12. Projection - Example Example 3: S#(STATE=‘NY'(S))

13. SET Operations • UNION: R1 R2 • INTERSECTION: R1 R2 • DIFFERENCE: R1 - R2 • CARTESIAN PRODUCT: R1 R2

14. Union Compatibility • For operators , , -, the operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes, and the domains of the corresponding attributes must be compatible; that is, dom(Ai)=dom(Bi) for i=1,2,...,n. • The resulting relation for , , or - has the same attribute names as the first operand relation R1 (by convention).

15. Union Compatibility - Examples • Are S(SNO, SNAME, AGE, STATE) and C(CNO, CNAME, CREDIT, DEPT) union compatible? • Are S(S#, SNAME, AGE, STATE) and C(CNO, CNAME, CREDIT_HOURS, DEPT_NAME) union compatible?

16. Union, Intersection, Difference • T= R U S : A tuple t is in relation T if and only if t is in relation R or t is in relation S • T = R  S: A tuple t is in relation T if and only if t is in both relations R and S • T= R - S :A tuple t is in relation T if and only if t is in R but not in S

17. Examples R S

18. Examples R  S R S R - S S - R

19. Cartesian Product • R(A1, A2, ..., Am) and S(B1, B2, ... , Bn) • T(A1, A2, ... , Am, B1, B2, ..., Bn) = R(A1, A2, ..., Am) X S(B1, B2, ..., Bn) • A tuple t is in T if and only if t[A1,A2, ... ,Am] is in R and t[B1, B2, ..., Bn] is in S. - If R has N1 tuples and S has N2 tuples, then T will have N1*N2 tuples.

20. Cartesian Product R Rx S S

21. Question • Compute S x C • Compute S x E

22. A Sample DB: student-course-enrollment S: E: C: