พีชคณิตแบบสัมพันธ์ (Relational Algebra)

1. บทที่ 3 พีชคณิตแบบสัมพันธ์ (Relational Algebra) อ. ดร. ชุรี เตชะวุฒิ CS (204)321 ระบบฐานข้อมูล 1 (Database System I)

2. Outlines 1) Unary relational operations: SELECT and PROJECT 2) Relational algebra operations from set theory 3) Binary relational operations: JOIN and DIVISION 4) Additional relational operations 5) Examples of queries in relational algebra

3. The SELECT operation is used to select a subset of the tuples from a relation that satisfy a selection condition. Unary relational operations: SELECT • Sigma σ is used to denote the SELECT operator, and the selection condition is a Boolean expression specified on the attributes of relation R. • The result of relational operation is a relation. • The degree of the relation resulting from a SELECT operation is the same as that of R. • The number of tuples in the resulting relation is always less than or equal to the number of tuples in R.

4. <comparison op> is one of the operators Unary relational operations: SELECT • Clauses of selection condition: <attribute name><comparison op><constant value> <attribute name><comparison op><attribute name> • Boolean operators AND, OR, NOT can also be used to form a selection condition

5. (The result is shown in Figure 7.8(a) ) • EX1: select the EMPLOYEE tuples whose department is 4. Unary relational operations: SELECT • EX2: select the EMPLOYEE tuples whose salary is greater than $30,000. • EX3: select the tuples for all employees who either work in department 4 and make over $25,000 per year, or work in department 5 and make over $30,000 .

6. The PROJECT operation is used to project the relation over certain attributes of a relation. Unary relational operations: PROJECT • Pi π is used to present the PROJECT operator. • The result of the PROJECT operation has only the attributes specified in <attribute list> and in the same order as they appear in the list. • The degree of the relation that is equal to the number of attributes in <attribute list>. • Duplicate elimination if the attribute list includes only nonkey attributes of R.

7. (The result is shown in Figure 7.8(b) ) (The result is shown in Figure 7.8(c) ) • EX4: list each employee’s last and first name and salary. Unary relational operations: PROJECT • EX5: list each employee’s sex and salary.

8. -------- (1) (The result is shown in Figure 7.9(a) ) -------- (2) • Two ways to write the relational algebra: (1) Write single relational algebra expression by nesting the operations, or Sequences of operations and RENAME operation (2) Apply one operation at a time and create intermediate result relations. • EX6: to retrieve the first name, last name, and salary of all employees who work in department number 5.

9. (The result is shown in Figure 7.9(b) ) • RENAME operation can be used to rename the attributes in the intermediate and result relations. Sequences of operations and RENAME operation • EX7: from EX1 with RENAME operation

10. Rho ρis used to present the RENAME operator. Sequences of operations and RENAME operation -------- (1) -------- (2) -------- (3) (1) Rename both resulting relation name and attributenames (2) Rename only resulting relation name (3) Rename only attributenames

11. Use standard mathematical on sets and on two union-compatible relationsR and S Relational algebra operations from set theory • UNION Combine tuples in R and S and then eliminate duplicate tuples. • INTERSECTION Include all tuples that are in both R and S. • MINUS or DIFFERENCE Include all tuples that are in R but not in S.

12. Rules for using these operators: • Commutative operations Relational algebra operations from set theory • Associative operations • Note: DIFFERENCE operation is not commutative

13. Another set theoretic operation is CARTESIAN PRODUCT or CROSS PRODUCT. Relational algebra operations from set theory • Relations on which it is applied do not have to be union compatible. • If R has nR tuples and S has nS tuples, then RxS will havenR*nS tuples.

14. (The result is shown in Figure 7.12 ) • The operation is useful when followed by a selection that matches values of attributes coming from the component relations. Relational algebra operations from set theory • EX8: to retrieve for each female employee a list of the names of her dependents

15. The JOIN operation, denoted by ,is used to combine related tuples from two relations into single tuples. Binary relational operations: JOIN • The general form of a JOIN operation on two relations is • A general join condition is in the form

16. JOIN: Only combinations of tuples satisfying the join condition appear in the result. CARTESIAN PRODUCT: All combinations of tuples are included in the result. • The main difference between CARTESIAN PRODUCT and JOIN: Binary relational operations: JOIN

17. THETA JOIN Use such a general join condition with one of the comparison operators. • EQUIJOIN The result always have one ore more pair of attributes that have identical values in every tuple. Involve with equality comparisons only . • NATURAL JOIN(denoted by *) Basically an EQUIJOIN followed by removal of the superfluous attributes (only one join attribute is kept). The removal requires two join attributes have the same name in both relations. If this is not the case, a renaming operation is applied first. • Three kinds of JOIN operations: Binary relational operations: JOIN

18. The DIVISION operation is denoted as following Binary relational operations: DIVISION

19. EX9:Find the answer T B: b1 b4 (As shown in Figure 7.15(b) ) Binary relational operations: DIVISION A: a1 a2 a3 a4 a1 a3 a2 a3 a4 a1 a2 a3 R B: b1 b1 b1 b1 b2 b2 b3 b3 b3 b4 b4 b4 S A: a1 a2 a3

20. (1) Use AGGREGATE FUNCTIONS in the case of, for example, retrieving the average or total salary of all employees or the number of employee tuples Must use some common functions including SUM, AVERAGE, MAXIMUM, MINIMUM,COUNT. • Cannot be perform with the basic relational algebra operations previously described. Additional relational operations:AGGREGATE FUNCTIONand GROUPING • Two ways of applications: (2) Use GROUPING to group tuples in a relation by the value of some of their attributes and then applying an AGGREGATE FUNCTIONS independently to each group.

21. “Script F”, , is used to present AGGREGATE FUNCTION operation. Additional relational operations:AGGREGATE FUNCTIONand GROUPING is a list of attributes of the relation specified in R. is a list of pairs. are such asSUM, AVERAGE, MAXIMUM, MINIMUM,COUNT. is an attribute of the relation specified in R. • The resulting relation has the grouping attributes plus one attribute for each element in the function list.

22. (The result is shown in Figure 7.16(b) ) • EX10: group employee tuples by DNO, and list the number of employees in the department, and their average salary. Additional relational operations:AGGREGATE FUNCTIONand GROUPING

23. Query1: Retrieve the name and address of all employees who work for the ‘Research’ department. Note: The operation order is SELECT – JOIN - PROJECT Examples of queries in relational algebra • Query2: Find the names of employees who work on all the projects controlled by department number 5. • Query3: List the names of employees who have at least one dependent.