1 / 26

Relational Algebra

Relational Algebra. Overview. Relational algebra offers a concise way to express queries. F orm the basis for “real” query languages (SQL) . Much more concise than SQL. It is widely used by database professionals. Basic operations. Additional operations. Example i nstances. R1.

jodie
Download Presentation

Relational Algebra

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

  2. Overview • Relational algebra offers a concise way to express queries. • Form the basis for “real” query languages (SQL). • Much more concise than SQL. • It is widely used by database professionals. • Basic operations. • Additional operations.

  3. Example instances R1 • “Sailors” and “Reserves” relations for our examples. S1 S2

  4. Basic operations • Selection • Projection • Union • Difference • Cartesian product • Rename

  5. Selection (1/3) S2

  6. Selection (2/3) S2

  7. Selection (3/3) S2

  8. Projection S2 Duplicate rows removed This is true for all the relational algebra operations.

  9. Operator composition S2

  10. Union S1 S2 • Two input relations must be union-compatible • Same number of attributes. • `Corresponding’ attributes have the same type.

  11. Intersection and Difference S1 S2

  12. Cartesian Product • Each row of S1 is paired with each row of R1. S1  R1

  13. Operation Composition

  14. Renaming S1 My-table(id, name, level, age) (S1) My-table

  15. Example 1 • ACC(acc-id, cust-id, balance) • Find all accounts with balances > 1200. • balance > 1200 (ACC) • Find the acc-id of all accounts with balances > 1200. • acc-id (balance> 1200 (ACC)) • Find the acc-id of all accounts with balances > 1200 and cust-id = 100. • acc-id (balance> 1200  cust-id = 100 (ACC)) • Find the acc-id of all accounts with balances > 1200 or cust-id = 100. • acc-id (balance> 1200  cust-id = 100 (ACC))

  16. Example 2 • ACC(acc-id, cust-id, balance) • LOAN(loan-id, cust-id, amount) • Find the cust-id of customers who have both loans or accounts. • cust-id (ACC)  cust-id (LOAN) • LOAN(loan-id, cust-id, amount) • CUST(cust-id, name, address) • Find the names of customers who have loans • name(σLOAN.cust-id = CUST.cust-id(LOAN  CUST))

  17. Overview • Relational algebra offers a concise way to express queries. • Form the basis for “real” query languages (SQL). • Much more concise than SQL. • It is widely used by database professionals. • Basic operations. • Additional operations.

  18. Additional operations • Natural join • Division • These operations can be transformed to basic operations. • They do not add any power to relational algebra, but can simplify queries.

  19. Natural join r s • R = (A, B, C, D), S = (E, B, D) • Equal on all common attributes • r s = r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s)) • Result schema = (A, B, C, D, E) B D E A B C D 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s A B C D E      1 1 1 1 2      a a a a b     

  20. Example 2 revisited • LOAN(loan-id, cust-id, amount) • CUST(cust-id, name, address) • Find the names of customers who have loans. • name(σLOAN.cust-id = CUST.cust-id(LOAN  CUST)) • = • name (LOAN CUST)

  21. Division (1/3) A B B r s A 1 2              1 2 3 1 1 1 3 4 6 1 2 s r

  22. Division (2/3) • r = (A1, …, Am, B1, …, Bn) • s = (B1, …, Bn) • r s has a schema (A1, …, Am). • If (a1, …, am) is in r s, then • for every tuple (b1, …, bn) in s, (a1, …, am, b1, …, bn) exists in r.

  23. Division (3/3) A B C D E D E         a a a a a a a a         a a b a b a b b 1 1 1 1 3 1 1 1 a b 1 1 s r A B C r s   a a  

  24. Example 3 • BRANCH(brh-name, city) • ACC(acc-id, cust-name, brh-name) • Assume that no two customers have the same name. • Find all customers who have accounts at all branches in HK. • A wrong solution • cust-name ((ACC  brh-name(city = ‘HK’(BRANCH))) • A correct solution • cust-name, brh-name(ACC)  brh-name(city = ‘HK’(BRANCH))

  25. Example Database ACC acc-id cust-name brh-name BRANCH brh-name city A1 A2 A3 A4 A5 A6 A7 A8 Joey Joey Jay Jay Eason Eason Twins Twins B1 B2 B1 B3 B1 B4 B1 B2 B1 B2 B3 B4 HK HK BJ BJ brh-name(city = ‘HK’(BRANCH)) cust-name brh-name cust-name, brh-name(ACC) brh-name Joey Joey Jay Jay Eason Eason Twins Twins B1 B2 B1 B3 B1 B4 B1 B2 B1 B2

  26. Example 3 • BRANCH(brh-name, city) • ACC(acc-id, cust-name, brh-name) • Find all customers who have accounts at all branches in HK. • SQL solution (more complex; to be explained later on): • SELECT DISTINCTcust-nameFROM ACC A1WHERE NOT EXISTS ((SELECT brh-name FROM BRANCH WHERE city = ‘HK’) EXCEPT (SELECT brh-name FROM ACC A2 WHERE A1.cust-name = A2.cust-name))

More Related