RELATIONAL ALGEBRA

1. CS157A Lecture 8 RELATIONAL ALGEBRA Prof. Sin-Min LEE Department of Computer Science

2. Relation schema • Named relation defined by a set of attribute and domain name pairs. • Relational database schema • Set of relation schemas, each with a distinct name. • Each tuple is distinct; there are no duplicate tuples. • Order of attributes has no significance. • Order of tuples has no significance, theoretically. • Relation name is distinct from all other relation names in relational schema. • Each cell of relation contains exactly one atomic (single) value. • Each attribute has a distinct name. • Values of an attribute are all from the same domain. • Each tuple is distinct; there are no duplicate tuples. • Order of attributes has no significance. • Order of tuples has no significance, theoretically.

3. Database Scheme A relational database scheme, or schema, corresponds to a set of table definitions. Eg: product(p_id, name, category, description) supply(p_id, s_id, qnty_per_month) supplier(s_id, name, address, ph#) * remember the difference between a DB instance and a DB scheme.

4. SAMPLE SCHEMAS AND INSTANCES The Schemas: Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) The Instances:

6. What is Relational Algebra? • Relational algebra is a procedural query language. • It consists of the select, project, union, set difference, Cartesian product, and rename operations. • Set intersection, division, natural join, and assignment combine the fundamental operations. • SQL is based on relational algebra

7. Relational algebra and relational calculus are formal languages associated with the relational model. • Both are equivalent to one another

8. What are the query languages ? • It is an abstract language. We use it to express the set of operations that any relational query language must perform. • Two types of operations: • 1.set-theoretic operations: tables are essentially sets of rows • 2.native relational operations: focus on the structure of the rows Query languages are specialized languages for asking questions,or queries,that involve the data in database.

9. Query languages • procedural vs. non-procedural • commercial languages have some of both • we will study: • relational algebra (which is procedural, i.e. tells you how to process a query) • relational calculus (which is non-procedural i.e. tells what you want)

10. SEMANTICS OF THE SAMPLE RELATIONS • Sailors: Entity set; lists the relevant properties of sailors. • Boats: Entity set; lists the relevant properties of boats. • Reserves: Relationship set: links sailors and boats by describing the boat number and date for which a sailor made a reservation. Example of the declarative sentences for which rows stand: Row 1: “Sailor ’22’ reserved boat number ‘101’ on 10/10/98”.

11. Selection and Projection condition • Selection Operator: σrating>8 (S2) Retrieves from the current instance of relation named S2 those rows where the value of the attribute ‘rating’ is greater than 8. Applying the above selection operator to the sample instance of S2 shown in figure 4.2 yields the relational instance on figure 4.4 as shown below: • π

12. Projection Operatorπsname,rating(S2) Retrieves from the current instance of the relation named S2 those columns whose names are ‘sname’ and ‘rating’. Applying the above operator to the sample instance of S2 shown in figure 4.2 yields the relational instance on figure 4.5 as shown below: N. B.:Note that the projection operator can produce duplicate rows in the resulting instance.

13. - Projection Operator (cont’d) Similarly πage(S2) yields the following relational instance Note here the elimination of duplicates SQL would yield For πage (S2):

14. 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 (⋈)

15. 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.

16. 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.

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

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

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

21. 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.

22. 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. • Duplicate rows are not permitted in relational algebra. Duplication is removed from the result. • Duplicate rows can occur in SQL, though they may be controlled by explicit keywords.

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

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

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

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

27. 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).

28. 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?

30. UNION, SET DIFFERENCE & SET INTERSECT • Union puts all tuples of two relations in one relation. To use this operator, two conditions must hold: • The two relations must be of the same arity. • The domain of ith attribute of the two participating relation must be the same. • Set difference operator computes tuples that are in one relation, but not in another. • Set intersect operator computes tuples that are common in two relations: • The five fundamental operations of the relational algebra are: select, project, cartesian product, Union, and set difference • All other operators can be constructed using these operators

31. EXAMPLE • Assume a database with the following three relations: Sailors (sid, sname, rating) Boats (bid, bname, color) Reserve (sid, bid, date) • Query 1: Find the bid of red colored boats:

32. EXAMPLE • Assume a database with the following three relations: Sailors (sid, sname, rating) Boats (bid, bname, color) Reserve (sid, bid, date) • Query 1: Find the bid of red colored boats: • ∏bid(бcolor=red(Boats))

33. EXAMPLE • Assume a database with the following three relations: Sailors (sid, sname, rating) Boats (bid, bname, color) Reserve (sid, bid, date) • Query 1: Find the name of sailors who have reserved Boat number 2.

34. EXAMPLE • Assume a database with the following three relations: Sailors (sid, sname, rating) Boats (bid, bname, color) Reserve (sid, bid, date) • Query 1: Find the name of sailors who have reserved Boat number 2. • ∏sname(бbid=2(Sailors (sid)Reserve))

35. EXAMPLE • Assume a database with the following three relations: Sailors (sid, sname, rating) Boats (bid, bname, color) Reserve (sid, bid, date) • Query 1: Find the name of sailors who have reserved both a red and a green boat.

36. 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

37. Set-Intersection • Denoted by the symbol . • Results in a relation that contains only the tuples that appear in both relations. • R  S = R – (R – S) • Since set-intersection can be written in terms of set-difference, it is not a fundamental operation.

38. Examples R S

39. Examples R  S R S R - S S - R

40. RENAME OPERATOR • Rename operator changes the name of its input table to its subscript, • ρe2(Emp) • Changes the name of Emp table to e2

41. RELATIONAL ALGEBRA INTRODUCTION • Assume the following two relations: Emp (SS#, name, age, salary, dno) Dept (dno, dname, floor, mgrSS#) • Relational algebra is a procedural query language, i.e., user must define both “how” and “what” to retrieve. • Relational algebra consists of a set of operators that consume either one or two relations as input. An operator produces one relation as its output. • Unary operators include: select, project, and rename • Binary operators include: cartesian product, equality join, natural join, join, semi-join, division, union, and set difference.

42. SELECT OPERATOR • Select (б): selects tuples that satisfy a predicate; e.g., retrieve the employees whose salary is 30,000 бSalary=30,000(Employee) • Conjunctive ( ) and disjunctive ( ) selection predicates are allowed; e.g., retrieve employees whose salary is higher than 30,000 and are younger than 25 years old: бSalary>30,000 age<25(Employee) • Note that only selection predicates are allowed. A selection predicate is either (1) a comparison (=, ≠, ≤, ≥, <, >) between an attribute and a constant (e.g., salary = 30,000) or (2) a comparison between two different attributes of the same relation (e.g., salary = age × 100). • Note: This operator is different than the SELECT command of SQL. < < <

43. EXAMPLE • Emp table:

44. EXAMPLE • Emp table: • бSalary=30,000(Employee)

45. EXAMPLE • Emp table: • бSalary=30,000(Employee)

46. EXAMPLE • Emp table: • бAge>22(Employee)

47. EXAMPLE • Emp table: • бAge>22(Employee)

48. PROJECT OPERATOR • Project (∏) retrieves a column. It is a unary operator that eliminate duplicates. e.g., name of employees: ∏name(Employee) e.g., name of employees earning more than 30,000: ∏name(бSalary>30,000(Employee))

49. EXAMPLE • Emp table:

50. EXAMPLE • Emp table: • ∏age(Emp)