Relational Algebra Relational Calculus

1. Chapter Five Relational AlgebraRelational Calculus Objectives Fundamental operations in RA Union Set difference Select Project Cartesian Product Relational Calculus

2. Query Languages • Procedural • Relational Algebra (RA) • Non-Procedural • Tuple Relational Calculus • Domain Relational Calculus

3. Fundamental Operations in RA • Binary Operations • UNION • MINUS • CARTESIAN PRODUCT • Unary Operations • SELECT • PROJECT

4. Union of R S • Union of R  S • All tuples in R or S • Union Compatible • Some degree • Attributes of R&S must be the same

5. Union of R  S

6. Union of R  S name (faculty) name (staff)

7. Set Difference (MINUS) R - S • Set of tuples in R but not in S • Union Compatible

8. Faculty  Staff Name ID Salary Smith 1 70,000 R  S = R – (R – S) Intersection: R  S • Set of of tuples belong to both R & S • Union Compatible Find the list of faculty members who are also staff

9. Cartesian Product R x S • Set of (K1 + K2) tuples: The first K1 tuples are from R. The last K2 tuples are from S

10. Cartesian Product R x S List of courses offered in year 99?

11. Selection (Unary Relation) • Select tuples that satisfy a given predicate major = ‘COSC’ (Student) • Result is another relation • Conditions are relational operator (,, ,  , ) • Logical operators AND (), OR(), NOT()

12. Selection (Unary Relation) • Find all Faculty members which make less than $45,000 salary < 45,000(Faculty) • Find all staff who make less than $40,000 and ID > 100 salary < 40,000 AND ID > 100 (Staff) • List of number of courses offered in year 99 S_Num = S_Num AND year = 99 (Semester x Semester_Course)

13. Projection (Unary) • Select attributes • (Pi) • Find the number of faculty that teach COSC courses • name( course=‘COSC’(Faculty))

14. Theta Join • R S • Theta join allows us to combine the selection & the cartesian product into an operation If  is = It is called Equijoin If the attributes have the same name, the join is called Natural Join

15. Part Two Relational Calculus (RC) • Non-procedural • Most commercial query language • Types: • Tuple RC: Variables represent tuples • Domain RC: Variables represent values of domain

16. Tuple Relational Calculus: { t | P(t)} Examples • List of students with GPA > 3 { t | t Є student ^ t[GPA] > 3 } • List of students name with GPA > 3 { t | s Є student (t[NAME] = s[NAME] ^ t[GPA] > 3)}