Relational Algebra Relational algebra is a query language

Relational Algebra

Relational algebra is a query language Ask queries by applying operations • Selection ( ) • Projection ( ) • Union ( ), Intersection ( ), Difference ( ) • Product ( ) • Join (⨝), Natural Join (*) • Quotient ( ) • Renaming ( )

Example database schema • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) Employee Sales Merchandise name salary dept item color price Jones 25000 hardware furniture chair book red Smith 50000 furniture games puzzle car Brown 30000 hardware stationery pencil puzzle green 15. 00 Smith 40000 games furniture chair sofa 10. 00 blue 20000. 00 red 275. 00

Selection • F(R) rows of relation R that satisfy logical formula F • List tuples for employees named Jones: name='Jones'(Employee) • List tuples for employees who earn less than 50000: salary<50000(Employee) • List tuples for red puzzles: color='red' and item='puzzle'(Merchandise) • List tuples for items that are red or that are puzzles: color='red' or item='puzzle'(Merchandise) • List tuples for items that are not red and that are not puzzles: color 'red' and item 'puzzle'(Merchandise)

Projection • L(R) columns of relation R that correspond to attribute list L • List employee names: name(Employee) • List employee names and their salaries: name, salary(Employee) • List names of employees who earn more than 80000: name( salary>80000(Employee)) • List colors in which any item is available: color(Merchandise) • List colors and prices in which pencils are available: color, price( item='pencil'(Merchandise))

Practice queries 1 (use , ) • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List departments and salaries for employees named Smith: • List items and prices for merchandise available in blue:

Solution to practice queries 1 • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List departments and salaries for employees named Smith: dept, salary( name='Smith'(Employee)) • List items and prices for merchandise available in blue: item, price( color='blue'(Merchandise))

Union • R S rows that appear in relation R or relation S or both • List all items in database: item(Sales) item(Merchandise) • List tuples for items that are red or that are puzzles: color='red'(Merchandise) item='puzzle'(Merchandise) • List items that are red or that are sold by hardware department: item( color='red'(Merchandise)) item( dept='hardware'(Sales)) • List tuples for employees who earn < 10000 or > 50000: salary<10000(Employee) salary>50000(Employee) • List departments that sell cars or have employee named Smith: dept( item='car'(Sales)) dept( name='Smith'(Employee))

Intersection • R S rows that appear in both relations R and S • List tuples for red items with price > 50. 00: color='red'(Merchandise) price>50. 00(Merchandise) • List items that are available in both red and blue: item( color='red'(Merchandise)) item( color='blue'(Merchandise)) • List items that are red and that are sold by hardware department: item( color='red'(Merchandise)) item( dept='hardware'(Sales)) • List departments with employees named Jones and Smith: dept( name='Jones'(Employee)) dept( name='Smith'(Employee)) • List departments that sell cars and have employee named Smith: dept( item='car'(Sales)) dept( name='Smith'(Employee))

Difference • R S rows that appear in relation R but not in relation S • List departments that do not sell any items: dept(Employee) dept(Sales) • List departments with some employee who makes > 30000 and < 50000: dept( salary>30000(Employee) salary 50000(Employee)) • List departments that have some employee who makes > 30000 but do not have any employee who makes > 50000: dept( salary>30000(Employee)) dept( salary 50000(Employee)) • List items that are available in red but not in blue: item( color='red'(Merchandise)) item( color='blue'(Merchandise)) • List items that are red but that are not sold by hardware department: item( color='red'(Merchandise)) item( dept='hardware'(Sales))

Practice queries 2 (use , , ) • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List departments that have no employees named Jones or Smith: • List items that are available in either red or blue, but not both:

Solution to practice queries 2 • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List departments that have no employees named Jones or Smith: dept(Employee) dept( name='Jones' or name='Smith'(Employee)) • List items that are available in either red or blue, but not both: ( item( color='red'(Merchandise)) item( color='blue'(Merchandise)))

Product, also called Cartesian Product • R S each row in R paired with each row in S • List names of employees in departments that sell TVs: name( Employee. dept=Sales. dept and item='tv'(Employee Sales)) or name( Employee. dept=Sales. dept(Employee item='tv'(Sales))) • List items that are sold by departments with employee named Jones: item( name='Jones' and Employee. dept=Sales. dept(Employee Sales)) or item( Employee. dept=Sales. dept( name='Jones'(Employee) Sales))

General Join, also called Equi Join or Theta Join • R ⨝F S or R *F S same meaning as F(R S) • List names of employees in departments that sell TVs: name(Employee ⨝Employee. dept=Sales. dept item='tv'(Sales)) • List items that are sold by departments with employee named Jones: item( name='Jones'(Employee) ⨝Employee. dept=Sales. dept Sales) • List departments that sell items with price > 500: dept(Sales ⨝Sales. item=Merchandise. item price>500(Merchandise)) • List prices of items sold in the furniture department: price( dept='furniture' (Sales) ⨝Sales. item=Merchandise. item Merchandise)

Natural Join • R ⨝ S or R * S same meaning as L( F(R S)) where L = attributes(R) attributes(S), and F enforces that R. A=S. A for each A in attributes(R) attributes(S) • List names of employees in departments that sell TVs: name(Employee * item='tv'(Sales)) • List items that are sold by departments with employee named Jones: item( name='Jones'(Employee) * Sales) • List departments that sell items with price > 500: dept(Sales * price>500(Merchandise)) • List prices of items sold by the furniture department: price( dept='furniture' (Sales) * Merchandise)

Difference between R ⨝F S and R * S R A e j n S B f f p C g k k B f f p R ⨝R. B=S. B S C g k k D h m q A R. B R. C S. B e f g f j f k f n p k p R ⨝R. B=S. B and R. C=S. C S R*S A R. B R. C S. B e f g f j f k f n p k p A e j n S. C g k k D h m q B f f p C g k k D h m q R ⨝R. C=S. C S S. C g k k D h m q A R. B R. C S. B e f g f j f k p n p k f n p k p S. C g k k D h m q

Practice queries 3 (use , , , ⨝, *) • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List employee names and the items sold by their departments: • List yellow items that are sold by the furniture department:

Solution to practice queries 3 • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List employee names and the items sold by their departments: name, item(Employee * Sales) • List yellow items that are sold by the furniture department: item( dept='furniture'(Sales) * color='yellow'(Merchandise))

Quotient • R S tuples t such that for every tuple s in S the tuple r=st is in R • R S same meaning as L(R) L(( L(R) S) R) where attributes(S) attributes(R) and L = attributes(R) attributes(S) • List departments that sell every item: Sales item(Merchandise) • List departments that sell every green item: Sales item( color='green'(Merchandise)) • List departments that pay every employee > 50000: name, dept( salary>50000(Employee)) name(Employee)

Renaming • b/c (R) identical to relation R except attribute c is renamed to attribute b Employee name salary Jones 25000 Smith 50000 Brown 30000 Smith 40000 dept hardware furniture hardware games s/salary (Employee) name Jones Smith s 250000 40000 dept hardware furniture games • List the largest salary paid to any employee by any department: ( salary(Employee) ⨝salary s s( s/salary(Employee))) s( s/salary(Employee)) or Temp s( s/salary(Employee)); ( salary(Employee) ⨝salary s Temp) Temp

Review: more example queries • List departments that pay each of their employees > 50000: dept(Employee) dept( salary 50000(Employee)) • List departments that sell only green items: dept(Sales) dept(Sales * color 'green'(Merchandise)) • List items that are not sold by every department: item(Merchandise) (Sales dept(Employee)) or item( dept(Employee) item(Merchandise) Sales) • List names of employees whose salary is less than the price of some red item sold by their department: name( salary<price and color='red' (Employee * Sales * Merchandise))

Practice queries 4 (use , , , ⨝, *, , ) • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List items sold by every department with an employee named Smith: • List pairs of distinct items that both have the same price:

Solution to practice queries 4 • Employee (name, salary, dept) • Sales (dept, item) • Merchandise (item, color, price) • List items sold by every department with an employee named Smith: Sales dept( name='Smith'(Employee)) • List pairs of distinct items that both have the same price: item, i(Merchandise ⨝item i and price=p ( i/item( p/price(Merchandise))))