 Download Presentation RELATIONAL ALGEBRA (Chapter 2) RELATIONAL ALGEBRA (Chapter 2) - PowerPoint PPT Presentation

Download Presentation RELATIONAL ALGEBRA (Chapter 2)
An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

1. RELATIONAL ALGEBRA (Chapter 2)

2. Overall Organization Query Parser Query Optimizer Query Interpretor Relational Algebra operators: , , , , , , , ,  Index structures Abstraction of records Buffer Pool Manager File System

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

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

5. EXAMPLE • Emp table:

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

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

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

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

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

11. EXAMPLE • Emp table:

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

13. EXAMPLE • Emp table: • ∏name,age(бSalary=4000 (Emp) )

14. EXAMPLE • Emp table: • ∏name,age(бSalary=4000 (Emp) )

15. EXAMPLE • Emp table: • ∏name,age(бSalary=4000 (Emp) )

16. CARTESIAN PRODUCT • Cartesian Product (R1 ×R2) combines two relations by concatenating their tuples together, evaluating all possible combinations. If the name of a column is identical for two relations, this ambiguity is resolved by attaching the name of each relation to a column. e.g., Emp × Dept • (SS#, name, age, salary, Emp.dno, Dept.dno, dname, floor, mgrSS#) • If t(Emp) and t(Dept) is the cardinality of the Employee and Dept relations respectively, then the cardinality of Emp × Dept is: t(Emp) × t(Dept)

17. CARTESIAN PRODUCT (Cont…) • Example: Emp table: Dept table: SS# Name age salary dno dname mgrSS# floor dno

18. CARTESIAN PRODUCT (Cont…) • Cartesian product of Emp and Dept: Emp × Dept: mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno

19. CARTESIAN PRODUCT • Example: retrieve the name of employees that work in the toy department:

20. CARTESIAN PRODUCT • Example: retrieve the name of employees that work in the toy department: • ∏name(бEmp.dno=Dept.dno(Emp × бdname=‘toy’(Dept)))

21. CARTESIAN PRODUCT (Cont…) • ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno

22. CARTESIAN PRODUCT (Cont…) • ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno

23. CARTESIAN PRODUCT (Cont…) • ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno

24. CARTESIAN PRODUCT (Cont…) • ∏name(бdname=‘toy’(б Emp.dno=Dept.dno(Emp × Dept))) Name

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

26. EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN • Equality join connects tuples from two relations that match on certain attributes. The specified joining columns are kept in the resulting relation. • ∏name(бdname=‘toy’(Emp Dept))) • Natural join connects tuples from two relations that match on the specified common attributes • ∏name(бdname=‘toy’(Emp Dept))) • How is an equality join between Emp and Dept using dno different than a natural join between Emp and Dept using dno? • Equality join: SS#, name, age, salary, Emp.dno, Dept.dno, … • Natural join: SS#, name, age, salary, dno, dname, … • Join is similar to equality join using different comparison operators • A S op = {=, ≠, ≤, ≥, <, >} att op att (dno) (dno)

27. EXAMPLE JOIN • Equality Join, (Emp Dept))) Dept EMP (dno)

28. EXAMPLE JOIN • Natural Join, (Emp Dept))) Dept EMP (dno)

29. EXAMPLE JOIN • Join, (Emp ρx(Emp)))) Dept EMP Salary > 5 * salary

30. EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN (Cont…) • Example: retrieve the name of employees who earn more than Joe: • ∏name(Emp (sal>x.sal)бname=‘Joe’(ρ x(Emp))) • Semi-Join selects the columns of one relation that joins with another. It is equivalent to a join followed by a projection: • Emp (dno)Dept ≡∏SS#, name, age, salary, dno(Emp Dept)

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

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:

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 bid of red colored boats: • ∏bid(бcolor=red(Boats))

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.

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 Boat number 2. • ∏sname(бbid=2(Sailors (sid)Reserve))

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

37. 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. ∏sid( бcolor=red(Boats) (Sailors Reserve) )  ∏sid( бcolor=green(Boats) (Sailors Reserve) )

38. EXAMPLE • Assume a database with the following three relations: Emp(SS#, name, salary, age, mgrss#) • Query 3: Find employees whose salary is higher than their manager’s salary.

39. EXAMPLE • Assume a database with the following relations: Emp(SS#, name, salary, age, mgrss#) • Query 3: Find name of those employees whose salary is higher than their manager’s salary. ∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#

40. EXAMPLE ∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#

41. EXAMPLE ∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#

42. EXAMPLE ∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#

43. OTHER ALGEBRAIC OPERATORS • Assignment: One may use the assignment operator to assign the relation produced by an algebraic expression to a new relation, e.g., • ToyEmp  Emp бname=‘toy’ (Dept) • Deletion is performed using the assignment operator: • R  R – E • Example: Fire all employees whose salary is higher than 100,000 Emp  Emp – бsalary>100,000 (Emp) • Insertion is also performed using the assignment operator: • R  R  E • Example: Hire Joe Emp  Emp  (5, Joe, 15, 30000, 1)

44. OTHER ALGEBRAIC OPERATORS • Update operator changes the value of records based on its input expression AE(R) • Example, Give all employees a 10% raise: salary1.1*salary(Emp)

45. A PROGRAM • Recall Emp(SS#,name,age,salary, dno) Dept(dno,dname,floor,mgrss#) • Give the toy employees a 10% raise: