234 Views

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

**Overall Organization**Query Parser Query Optimizer Query Interpretor Relational Algebra operators: , , , , , , , , Index structures Abstraction of records Buffer Pool Manager File System**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.**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. < < <**EXAMPLE**• Emp table:**EXAMPLE**• Emp table: • бSalary=30,000(Employee)**EXAMPLE**• Emp table: • бSalary=30,000(Employee)**EXAMPLE**• Emp table: • бAge>22(Employee)**EXAMPLE**• Emp table: • бAge>22(Employee)**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))**EXAMPLE**• Emp table:**EXAMPLE**• Emp table: • ∏age(Emp)**EXAMPLE**• Emp table: • ∏name,age(бSalary=4000 (Emp) )**EXAMPLE**• Emp table: • ∏name,age(бSalary=4000 (Emp) )**EXAMPLE**• Emp table: • ∏name,age(бSalary=4000 (Emp) )**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)**CARTESIAN PRODUCT (Cont…)**• Example: Emp table: Dept table: SS# Name age salary dno dname mgrSS# floor dno**CARTESIAN PRODUCT (Cont…)**• Cartesian product of Emp and Dept: Emp × Dept: mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno**CARTESIAN PRODUCT**• Example: retrieve the name of employees that work in the toy department:**CARTESIAN PRODUCT**• Example: retrieve the name of employees that work in the toy department: • ∏name(бEmp.dno=Dept.dno(Emp × бdname=‘toy’(Dept)))**CARTESIAN PRODUCT (Cont…)**• ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno**CARTESIAN PRODUCT (Cont…)**• ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno**CARTESIAN PRODUCT (Cont…)**• ∏name(бdname=‘toy’ (б Emp.dno=Dept.dno(Emp × Dept))) mgrSS# SS# Name age salary Emp.dno dname floor Dept.dno**CARTESIAN PRODUCT (Cont…)**• ∏name(бdname=‘toy’(б Emp.dno=Dept.dno(Emp × Dept))) Name**RENAME OPERATOR**• Rename operator changes the name of its input table to its subscript, • ρe2(Emp) • Changes the name of Emp table to e2**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)**EXAMPLE JOIN**• Equality Join, (Emp Dept))) Dept EMP (dno)**EXAMPLE JOIN**• Natural Join, (Emp Dept))) Dept EMP (dno)**EXAMPLE JOIN**• Join, (Emp ρx(Emp)))) Dept EMP Salary > 5 * salary**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)**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**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:**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))**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.**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))**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.**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) )**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.**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#**EXAMPLE**∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#**EXAMPLE**∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#**EXAMPLE**∏Emp.name( бemp.salary>mgr.salary(Emp ρmgr(Emp)) ) mgrSS#=SS#**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)**OTHER ALGEBRAIC OPERATORS**• Update operator changes the value of records based on its input expression AE(R) • Example, Give all employees a 10% raise: salary1.1*salary(Emp)**A PROGRAM**• Recall Emp(SS#,name,age,salary, dno) Dept(dno,dname,floor,mgrss#) • Give the toy employees a 10% raise: