Database Systems

1. Database Systems Lecture 11 Relational Algebra Database Management Systems

2. Today’s agenda • Example Database Application (COMPANY) • Relational Algebra • Unary Relational Operations • Relational Algebra Operations From Set Theory Database Management Systems

3. Relational Algebra Overview • £ Unary Relational Operations • • SELECT (symbol: s (sigma)) • • PROJECT (symbol: p (pi)) • • RENAME (symbol: r (rho)) • £ Relational Algebra Operations from Set Theory • • UNION ( È ), INTERSECTION ( Ç ), DIFFERENCE (or • MINUS, – ) • • CARTESIAN PRODUCT ( x ) • £ Binary Relational Operations • • JOIN (several variations of JOIN exist) • • DIVISION • £ Additional Relational Operations • • OUTER JOINS, OUTER UNION • • AGGREGATE FUNCTIONS (SUM, COUNT, AVG, MIN, MAX)

4. Relational Algebra • The basic set of operations for the relational model is known as the relational algebra. These operations enable a user to specify basic retrieval requests. • The result of a retrieval is a new relation, which may have been formed from one or more relations. The algebra operations thus produce new relations, which can be further manipulated using operations of the same algebra. • A sequence of relational algebra operations forms a relational algebra expression, whose result will also be a relation that represents the result of a database query (or retrieval request). Database Management Systems

5. Relational Algebra: 5 Basic Operations • Selection ( ) Selects a subset of rows from relation (horizontal). • Projection ( ) Retains only wanted columns from relation (vertical). • Cross-product(x) Allows us to combine two relations. • Set-difference (–) Tuples in r1, but not in r2. • Union( ) Tuples in r1 and/or in r2. Since each operation returns a relation, operationscan be composed! (Algebra is “closed”.)

6. Unary Relational Operations • SELECT Operation SELECT operation is used to select a subset of the tuples from a relation that satisfy a selection condition. It is a filter that keeps only those tuples that satisfy a qualifying condition – those satisfying the condition are selected while others are discarded. Example: To select the EMPLOYEE tuples whose department number is four or those whose salary is greater than $30,000 the following notation is used: DNO = 4(EMPLOYEE) SALARY > 30,000(EMPLOYEE) In general, the select operation is denoted by <selection condition>(R) where the symbol  (sigma) is used to denote the select operator, and the selection condition is a Boolean expression specified on the attributes of relation R Database Management Systems

7. Unary Relational Operations SELECT Operation Properties • The SELECT operation <selection condition>(R) produces a relation S that has the same schema as R • The SELECT operation  is commutative; i.e., • <condition1>(< condition2> (R)) = <condition2> (< condition1> ( R)) • A cascaded SELECT operation may be applied in any order; i.e., • <condition1>(< condition2> (<condition3> (R)) • = <condition2> (< condition3> (< condition1> ( R))) • A cascaded SELECT operation may be replaced by a single selection with a conjunction of all the conditions; i.e., • <condition1>(< condition2> (<condition3> (R)) • = <condition1> AND < condition2> AND < condition3> ( R))) Database Management Systems

8. FIGURE 6.1Results of SELECT and PROJECT operations. (a) s(DNO=4 AND SALARY>25000) OR (DNO=5 AND SLARY>30000)(EMPLOYEE). (b) pSEX, SALARY(EMPLOYEE). Database Management Systems

9. Select rating where rating is greater than “8”

10. Answer

11. Unary Relational Operations (cont.) • PROJECT Operation This operation selects certain columns from the table and discards the other columns. The PROJECT creates a vertical partitioning – one with the needed columns (attributes) containing results of the operation and other containing the discarded Columns. Example: To list each employee’s first and last name and salary, the following is used: LNAME, FNAME,SALARY(EMPLOYEE) The general form of the project operation is <attribute list>(R) where  (pi) is the symbol used to represent the project operation and <attribute list> is the desired list of attributes from the attributes of relation R. The project operation removes any duplicate tuples, so the result of the project operation is a set of tuples and hence a valid relation. Database Management Systems

12. Projection S2

13. Unary Relational Operations (cont.) PROJECT Operation Properties • The number of tuples in the result of projection  <list> (R)is always less or equal to the number of tuples in R. • If the list of attributes includes a key of R, then the number of tuples is equal to the number of tuples in R. •  <list1> ( <list2> (R) ) =  <list1> (R) as long as<list2>contains theattributes in<list2> Database Management Systems

14. FIGURE 6.1Results of SELECT and PROJECT operations. (a) s(DNO=4 AND SALARY>25000) OR (DNO=5 AND SLARY>30000)(EMPLOYEE). (b) pSEX, SALARY(EMPLOYEE). Database Management Systems

15. (a) Project sname,rating(b) Project age

17. Unary Relational Operations (cont.) • Rename Operation We may want to apply several relational algebra operations one after the other. Either we can write the operations as a single relational algebra expression by nesting the operations, or we can apply one operation at a time and create intermediate result relations. In the latter case, we must give names to the relations that hold the intermediate results. Example: To retrieve the first name, last name, and salary of all employees who work in department number 5, we must apply a select and a project operation. We can write a single relational algebra expression as follows: FNAME, LNAME, SALARY(DNO=5(EMPLOYEE)) OR We can explicitly show the sequence of operations, giving a name to each intermediate relation: DEP5_EMPS  DNO=5(EMPLOYEE) RESULT  FNAME, LNAME, SALARY (DEP5_EMPS) Database Management Systems

18. Unary Relational Operations (cont.) • The RENAME operator is used to give a name to results or output of queries, returns of selection statements, and views of queries that we would like to view at some other point in time: [3] [4] [5] [6] • The RENAME operator is symbolized by ρ (rho). • The general syntax for RENAME operator is: ρ s(B1, B2, B3,….Bn)(R ) • ρ is the RENAME operation. • S is the new relation name. • B1, B2, B3, …Bn are the new renamed attributes (columns). Database Management Systems

19. RENAME OPERATOR • The RENAME Operator • R is the relation or table from which the attributes are chosen. • To implement the RENAME statement in SQL, we take a look at an example in which we would like to choose the Date of Birth and Employee Number attributes and RENAME them as ‘Birth_Date’ and ‘Employee_Number’ from the EMPLOYEE relation… • ρ (Birth_Date, Employee_Number)(EMPLOYEE ) ← dob, empno(EMPLOYEE ) • The arrow symbol ← means that we first get the PROJECT operation results on the right side of the arrow then apply the RENAME operation on the results on the left side of the arrow.

20. FIGURE 6.2Results of a sequence of operations. (a) pFNAME, LNAME, SALARY(sDNO=5(EMPLOYEE)). (b) Using intermediate relations and renaming of attributes. Database Management Systems

21. Union and Set-Difference • All of these operations take two input relations, which must be union-compatible: • Same number of fields. • `Corresponding’ fields have the same type. • For which, if any, is duplicate elimination required?

22. Union S1 S2

23. Relational Algebra Operations FromSet Theory • UNION Operation The result of this operation, denoted by R S, is a relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated. Example: To retrieve the social security numbers of all employees who either work in department 5 or directly supervise an employee who works in department 5, we can use the union operation as follows: DEP5_EMPS DNO=5(EMPLOYEE) RESULT1 SSN(DEP5_EMPS) RESULT2(SSN) SUPERSSN(DEP5_EMPS) RESULT  RESULT1RESULT2 The union operation produces the tuples that are in either RESULT1 or RESULT2 or both. The two operands must be “type compatible”. Database Management Systems

24. Relational Algebra Operations FromSet Theory • Type Compatibility • The operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes, and the domains of corresponding attributes must be compatible; that is, dom(Ai)=dom(Bi) for i=1, 2, ..., n. • The resulting relation for R1R2,R1 R2, or R1-R2 has the same attribute names as the first operand relation R1 (by convention). Database Management Systems

25. Relational Algebra Operations FromSet Theory • UNION Example STUDENTINSTRUCTOR Database Management Systems

26. Relational Algebra Operations From Set Theory (cont.) • INTERSECTION OPERATION The result of this operation, denoted by R S, is a relation that includes all tuples that are in both R and S. The two operands must be "type compatible" Example: The result of the intersection operation (figure below) includes only those who are both students and instructors. STUDENT INSTRUCTOR Database Management Systems

27. Intersection S1 S2

28. Relational Algebra Operations From Set Theory (cont.) • Set Difference (or MINUS) Operation The result of this operation, denoted by R - S, is a relation that includes all tuples that are in R but not in S. The two operands must be "type compatible”. Example: The figure shows the names of students who are not instructors, and the names of instructors who are not students. STUDENT-INSTRUCTOR INSTRUCTOR-STUDENT Database Management Systems

29. Set Difference S1 S2 – S1 S2

30. Relational Algebra Operations From Set Theory (cont.) • Notice that both union and intersection are commutative operations; that is R  S = S  R, and R  S = S  R • Both union and intersection can be treated as n-ary operations applicable to any number of relations as both are associative operations; that is R  (S  T) = (R  S)  T, and (R  S)  T = R  (S  T) • The minus operation is not commutative; that is, in general R - S ≠ S – R Database Management Systems

31. Relational Algebra Operations From Set Theory (cont.) • CARTESIAN (or cross product) Operation • This operation is used to combine tuples from two relations in a combinatorial fashion. In general, the result of R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a relation Q with degree n + m attributes Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order. The resulting relation Q has one tuple for each combination of tuples—one from R and one from S. • Hence, if R has nRtuples (denoted as |R| = nR ), and S has nStuples, then | R x S | will have nR*nStuples. • The two operands do NOT have to be "type compatible” Example: FEMALE_EMPS  GENDER=’F’(EMPLOYEE) EMPNAMES  FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS  EMPNAMES x DEPENDENT Database Management Systems

32. FIGURE≈6.5aThe CARTESIAN PRODUCT (CROSS PRODUCT) operation. Database Management Systems

33. FIGURE≈6.5bThe CARTESIAN PRODUCT (CROSS PRODUCT) operation. Database Management Systems

34. Cross Product Example R1 S1 R1 X S1 =

35. Compound Operator: Join • Joins are compound operators involving cross product, selection, and (sometimes) projection. • Most common type of join is a “natural join” (often just called “join”). R S conceptually is: • Compute R X S • Select rows where attributes that appear in both relations have equal values • Project all unique atttributes and one copy of each of the common ones. • Note: Usually done much more efficiently than this. • Useful for putting “normalized” relations back together.

36. Natural Join Example R1 S1 R1 S1 =

37. Other Types of Joins • Condition Join (or “theta-join”): • Result schema same as that of cross-product. • May have fewer tuples than cross-product. • Equi-Join: Special case: condition c contains only conjunction of equalities.

38. “Theta” Join Example R1 S1 =