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

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