1 / 45

RELATIONAL ALGEBRA (Chapter 2)

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

Pat_Xavi
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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:

More Related