1 / 69

Carnegie Mellon Univ. School of Computer Science 15-415/615 - DB Applications

Carnegie Mellon Univ. School of Computer Science 15-415/615 - DB Applications. C. Faloutsos & A. Pavlo Lecture #4: Relational Algebra. Overview. history concepts Formal query languages relational algebra rel. tuple calculus rel. domain calculus. History.

zandra
Download Presentation

Carnegie Mellon Univ. School of Computer Science 15-415/615 - DB Applications

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. Carnegie Mellon Univ.School of Computer Science15-415/615 - DB Applications C. Faloutsos & A. Pavlo Lecture #4: Relational Algebra

  2. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus CMU SCS 15-415/615

  3. History • before: records, pointers, sets etc • introduced by E.F. Codd in 1970 • revolutionary! • first systems: 1977-8 (System R; Ingres) • Turing award in 1981 CMU SCS 15-415/615

  4. Concepts - reminder • Database: a set of relations (= tables) • rows: tuples • columns: attributes (or keys) • superkey, candidate key, primary key CMU SCS 15-415/615

  5. Example Database: CMU SCS 15-415/615

  6. Example: cont’d k-th attribute (Dk domain) Database: rel. schema (attr+domains) tuple CMU SCS 15-415/615

  7. Example: cont’d rel. schema (attr+domains) instance CMU SCS 15-415/615

  8. Example: cont’d • Di: the domain of the i-th attribute (eg., char(10) rel. schema (attr+domains) instance CMU SCS 15-415/615

  9. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus CMU SCS 15-415/615

  10. Formal query languages • How do we collect information? • Eg., find ssn’s of people in 415 • (recall: everything is a set!) • One solution: Rel. algebra, ie., set operators • Q1: Which ones?? • Q2: what is a minimal set of operators? CMU SCS 15-415/615

  11. Relational operators • . • . • . • set union U • set difference ‘-’ CMU SCS 15-415/615

  12. Example: • Q: find all students (part or full time) • A: PT-STUDENT union FT-STUDENT CMU SCS 15-415/615

  13. Observations: • two tables are ‘union compatible’ if they have the same attributes (‘domains’) • Q: how about intersection U CMU SCS 15-415/615

  14. Observations: • A: redundant: • STUDENT intersection STAFF = STAFF STUDENT CMU SCS 15-415/615

  15. Observations: • A: redundant: • STUDENT intersection STAFF = STAFF STUDENT CMU SCS 15-415/615

  16. Observations: • A: redundant: • STUDENT intersection STAFF = STUDENT - (STUDENT - STAFF) STAFF STUDENT CMU SCS 15-415/615

  17. Observations: • A: redundant: • STUDENT intersection STAFF = STUDENT - (STUDENT - STAFF) Double negation: We’ll see it again, later… CMU SCS 15-415/615

  18. Relational operators • . • . • . • set union • set difference ‘-’ U CMU SCS 15-415/615

  19. Other operators? • eg, find all students on ‘Main street’ • A: ‘selection’ CMU SCS 15-415/615

  20. Other operators? • Notice: selection (and rest of operators) expect tables, and produce tables (-> can be cascaded!!) • For selection, in general: CMU SCS 15-415/615

  21. Selection - examples • Find all ‘Smiths’ on ‘Forbes Ave’ ‘condition’ can be any boolean combination of ‘=‘, ‘>’, ‘>=‘, ... CMU SCS 15-415/615

  22. Relational operators • selection • . • . • set union • set difference R - S R U S CMU SCS 15-415/615

  23. Relational operators • selection picks rows - how about columns? • A: ‘projection’ - eg.: finds all the ‘ssn’ - removing duplicates CMU SCS 15-415/615

  24. Relational operators Cascading: ‘find ssn of students on ‘forbes ave’ CMU SCS 15-415/615

  25. Relational operators • selection • projection • . • set union • set difference R - S R U S CMU SCS 15-415/615

  26. Relational operators Are we done yet? Q: Give a query we can not answer yet! CMU SCS 15-415/615

  27. Relational operators A: any query across two or more tables, eg., ‘find names of students in 15-415’ Q: what extra operator do we need?? CMU SCS 15-415/615

  28. Relational operators A: any query across two or more tables, eg., ‘find names of students in 15-415’ Q: what extra operator do we need?? A: surprisingly, cartesian product is enough! CMU SCS 15-415/615

  29. Cartesian product • eg., dog-breeding: MALE x FEMALE • gives all possible couples = x CMU SCS 15-415/615

  30. so what? • Eg., how do we find names of students taking 415? CMU SCS 15-415/615

  31. Cartesian product • A: CMU SCS 15-415/615

  32. Cartesian product CMU SCS 15-415/615

  33. CMU SCS 15-415/615

  34. FUNDAMENTALRelational operators • selection • projection • cartesian product MALE x FEMALE • set union • set difference R - S R U S CMU SCS 15-415/615

  35. Relational ops • Surprisingly, they are enough, to help us answer almost any query we want!! • derived/convenience operators: • set intersection • join(theta join, equi-join, natural join) • ‘rename’ operator • division CMU SCS 15-415/615

  36. Joins • Equijoin: CMU SCS 15-415/615

  37. Cartesian product • A: CMU SCS 15-415/615

  38. Joins • Equijoin: • theta-joins: generalization of equi-join - any condition CMU SCS 15-415/615

  39. Joins • very popular: natural join: RS • like equi-join, but it drops duplicate columns: STUDENT (ssn, name, address) TAKES (ssn, cid, grade) CMU SCS 15-415/615

  40. Joins • nat. join has 5 attributes equi-join: 6 CMU SCS 15-415/615

  41. Natural Joins - nit-picking • if no attributes in common between R, S: nat. join -> cartesian product CMU SCS 15-415/615

  42. Overview - rel. algebra • fundamental operators • derived operators • joins etc • rename • division • examples CMU SCS 15-415/615

  43. Rename op. • Q: why? • A: shorthand; self-joins; … • for example, find the grand-parents of ‘Tom’, given PC (parent-id, child-id) CMU SCS 15-415/615

  44. Rename op. • PC (parent-id, child-id) CMU SCS 15-415/615

  45. Rename op. • first, WRONG attempt: • (why? how many columns?) • Second WRONG attempt: CMU SCS 15-415/615

  46. Rename op. • we clearly need two different names for the same table - hence, the ‘rename’ op. CMU SCS 15-415/615

  47. Overview - rel. algebra • fundamental operators • derived operators • joins etc • rename • division • examples CMU SCS 15-415/615

  48. Division • Rarely used, but powerful. • Example: find suspicious suppliers, ie., suppliers that supplied all the parts in A_BOMB CMU SCS 15-415/615

  49. Division CMU SCS 15-415/615

  50. Division • Observations: ~reverse of cartesian product • It can be derived from the 5 fundamental operators (!!) • How? CMU SCS 15-415/615

More Related