1 / 69

Carnegie Mellon Univ. School of Computer Science 15-415 - Database Applications

This lecture from Carnegie Mellon University's School of Computer Science dives into relational algebra, an essential aspect of database applications. It covers key historical developments introduced by E.F. Codd in the 1970s, illustrating the concepts of relations, tuples, attributes, and the significance of formal query languages. The lecture discusses fundamental relational operators, including selection, projection, union, difference, and joins, demonstrating their applications and importance in database querying. This foundational understanding is crucial for anyone studying databases.

reia
Télécharger la présentation

Carnegie Mellon Univ. School of Computer Science 15-415 - Database 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 - Database Applications Lecture #5: Relational Algebra

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

  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

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

  5. Example Database: CMU SCS 15-415

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

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

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

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

  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

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

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

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

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

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

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

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

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

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

  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

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

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

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

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

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

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

  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

  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

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

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

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

  32. Cartesian product CMU SCS 15-415

  33. CMU SCS 15-415

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

  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

  36. Joins • Equijoin: CMU SCS 15-415

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

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

  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

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

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

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

  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

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

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

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

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

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

  49. Division CMU SCS 15-415

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

More Related