Relational Algebra - Basic Operations CS263 Lecture 11

1. Relational Algebra - Basic OperationsCS263 Lecture 11

2. Relational Algebra • Relational algebra operations work on one or more relations to define another relation leaving the original intact. • Both operands and results are relations, so output from one operation can become input to another operation. • Allows expressions to be nested, just as in arithmetic. This property is called closure. • 5 basic operations in relational algebra: Selection, Projection, Cartesian product, Union, and Set Difference. • These perform most of the data retrieval operations needed. • Also have Join, Intersection, and Division operations, which can be expressed in terms of 5 basic operations.

3. Relational Algebra Operations

4. Example: List all staff with a salary greater than £10,000. • salary > 10000 (Staff)  Selection (Restriction) predicate (R) Works on a single relation R and defines a relation that contains only those tuples of R that satisfy the specified condition (predicate).

5. Example: Produce a list of salaries for all staff, showing only their staffNo, fName, lName, and salary details. • staffNo, fName, lName, salary (Staff) Projection  col1, . . . , coln(R) Works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.

6. Example: Produce a list of all staff that work in either of two departments (each department has a separate database), showing only their staffNo, and date of birth. •  staffNo, dob(Staff_DepA) staffNo, dob (Staff_DepB)  Staff_DepB Staff_DepA staffNo dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40 CC15 11-03-66 staffNo dob CC15 11-03-66 SA51 21-11-82 staffNo dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40   Union RS Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S must be union-compatible (i.e. same attributes).

7. Example: Produce a list of staff that work in both department A and department B, showing only their staffNo, and date of birth. • ( staffNo, dob(Staff_DepA)) ( staffNo, dob (Staff_DepB)) Staff_DepB Staff_DepA staffNo dob SA51 21-11-82 staffNo dob CC15 11-03-66 SA51 21-11-82 staffNo dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40   Intersect R S Defines a relation consisting of the set of all tuples that are in both R and S. R and S must be union-compatible.

8. Example: Produce a list of all staff that only work in department A (each department has a separate database), showing only their staffNo, and date of birth. •  staffNo, dob(Staff_DepA) staffNo, dob (Staff_DepB) Staff_DepB Staff_DepA staffNo dob SL10 14-02-64 DS40 01-01-40 staffNo dob CC15 11-03-66 SA51 21-11-82 staffNo dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40 Set Difference R–S Defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible.

9. Example: Combine details of staff and the departments they work in. •  staffNo, job, dept (Staff) dept, name (Dept) X Staff dept name 10 Stratford 20 Barking Dept staffNo job dept dept name SL10 Salesman 10 10 Stratford SA51 Manager 20 10 Stratford DS40 Clerk 20 10 Stratford SL10 Salesman 10 20 Barking SA51 Manager 20 20 Barking DS40 Clerk 20 20 Barking staffNo job dept SL10 Salesman 10 SA51 Manager 20 DS40 Clerk 20 X Cartesian product X R XS Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.

10. Relational Algebra Operations

11. Example: Produce a list of staff and the departments they work in. • ( staffNo, job, dept(Staff)) ( dept, name(Dept)) Staff.dept = Dept.dept Staff dept name 10 Stratford 20 Barking Dept staffNo job dept dept name SL10 Salesman 10 10 Stratford SA51 Manager 20 20 Barking DS40 Clerk 20 20 Barking staffNo job dept SL10 Salesman 10 SA51 Manager 20 DS40 Clerk 20 Because the predicate operator is an ‘=‘ this is known as an Equijoin Join <join condition> <join condition> R S Defines a relation that results from a selection operation (with a join predicate) over the Cartesian product of relation R and relation S.

12. Example: Produce a list of staff and the departments they work in. • ( staffNo, job, dept(Staff)) ( dept, name(Dept)) dept name 10 Stratford 20 Barking Staff Dept staffNo job dept name SL10 Salesman 10 Stratford SA51 Manager 20 Barking DS40 Clerk 20 Barking staffNo job dept SL10 Salesman 10 SA51 Manager 20 DS40 Clerk 20 Natural Join R S This performs an Equijoin of the two relations R and S over all common attributes. One occurrence of each common attribute is eliminated from the result.

13. Example: Produce a list of all departments and associated staff that work in them. ( dept, name(Dept)) ( staffNo, job, dept(Staff)) Staff Dept staffNo job dept SL10 Salesman 10 SA51 Manager 20 DS40 Clerk 20 dept name staffNo job 10 Stratford SL10 Salesman 20 Barking SA51 Manager 20 Barking DS40 Clerk 30 Watford dept name 10 Stratford 20 Barking 30 Watford Left Outer Join R S Left outer join is a join in which tuples from R that do not have matching values in common columns of S are also included in the resulting relation.

14. Example: Produce a list of staff that work in both department A and department B, showing only their staffNo, and date of birth. • ( staffNo, dob(Staff_DepA)) ( staffNo, dob (Staff_DepB)) Staff_DepB Staff_DepA staffNo dob SA51 21-11-82 staffNo dob CC15 11-03-66 SA51 21-11-82 staffNo dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40   Intersect R S Defines a relation consisting of the set of all tuples that are in both R and S. R and S must be union-compatible.

15. Example:Show all staff that use all the company’s programming languages. • Staff_ProgProg  Prog Staff_Prog language COBOL BASIC staffNo language SL10 COBOL SA51 BASIC SA51 COBOL SE14 BASIC SE18 BASIC  staffNo SA51  Division  R S Defines a relation over common attributes C that consists of set of tuples from R that match a combination of every tuple in S.