Principles of Database Systems: Relational Model III

1. Temple University – CIS Dept.CIS331– Principles of Database Systems V. Megalooikonomou Relational Model III (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

2. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

3. General Overview - rel. model • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

4. Overview - detailed • rel. tuple calculus • why? • details • examples • equivalence with rel. algebra • more examples; ‘safety’ of expressions • rel. domain calculus + QBE

5. Safety of expressions • FORBIDDEN: It has infinite output!! • Instead, always use

6. Safety of expressions • Possible to write tuple calculus expressions that generate infinite relations, e.g., {t |  t r } results in an infinite relation if the domain of any attribute of relation r is infinite • To guard against the problem, we restrict the set of allowable expressions to safe expressions. • An expression {t | P (t) }in the tuple relational calculus is safe if every component of t appears in one of the relations, tuples, or constants that appear in P

7. More examples: Banking example • branch (branch-name, branch-city, assets) • customer (customer-name, customer-street, customer-city) • account (account-number, branch-name, balance) • loan (loan-number, branch-name, amount) • depositor (customer-name, account-number) • borrower (customer-name, loan-number)

8. Example Queries • Find the loan-number, branch-name, and amount for loans of over $1200 {t | t loan  t [amount]  1200} • Find the loan number for each loan of an amount greater than $1200 {t |  s loan (t [loan-number] = s [loan-number]  s [amount]  1200} Notice that a relation on schema [loan-number] is implicitly defined by the query

9. Example Queries • Find the names of all customers having a loan, an account, or both at the bank {t | s  borrower(t[customer-name] = s[customer-name])  u  depositor(t[customer-name] = u[customer-name]) • Find the names of all customers who have a loan and an account at the bank {t |s  borrower(t[customer-name] = s[customer-name])  u  depositor(t[customer-name] = u[customer-name])

10. Example Queries • Find the names of all customers having a loan at the Perryridge branch {t | s  borrower(t[customer-name] = s[customer-name]  u  loan(u[branch-name] = “Perryridge”  u[loan-number] = s[loan-number]))} • Find the names of all customers who have a loan at the Perryridge branch, but no account at any branch of the bank {t |s  borrower(t[customer-name] = s[customer-name]  u  loan(u[branch-name] = “Perryridge”  u[loan-number] = s[loan-number]))  not v  depositor (v[customer-name] = t[customer-name]) }

11. Example Queries • Find the names of all customers having a loan from the Perryridge branch, and the cities they live in {t |s  loan(s[branch-name] = “Perryridge”  u  borrower (u[loan-number] = s[loan-number]  t [customer-name] = u[customer-name])   v  customer (u[customer-name] = v[customer-name]  t[customer-city] = v[customer-city])))}

12. Example Queries • Find the names of all customers who have an account at all branches located in Brooklyn: {t |  c  customer (t[customer.name] = c[customer-name])   s  branch(s[branch-city] = “Brooklyn”   u  account ( s[branch-name] = u[branch-name]   s  depositor ( t[customer-name] = s[customer-name]  s[account-number] = u[account-number] )) )}

13. General Overview • relational model • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

14. Overview - detailed • rel. tuple calculus • dfn • details • equivalence to rel. algebra • rel. domain calculus + QBE

15. Rel. domain calculus (RDC) • Q: why? • A: slightly easier than RTC, although equivalent - basis for QBE • idea: domain variables (w/ F.O.L.) – e.g.: • ‘find STUDENT record with ssn=123’

16. Rel. Dom. Calculus • find STUDENT record with ssn=123’

17. Details • Like R.T.C - symbols allowed: • quantifiers

18. Details • but: domain (= column) variables, as opposed to tuple variables, e.g.: ssn address name

19. Domain Relational Calculus • A nonprocedural query language equivalent in power to the tuple relational calculus • Each query is an expression of the form: {  x1, x2, …, xn  | P (x1, x2, …, xn)} • x1, x2, …, xn represent domain variables • P represents a formula similar to that of the predicate calculus

20. Example queries • Find the branch-name, loan-number, and amount for loans of over $1200 { l, b, a  |  l, b, a   loan  a > 1200} • Find the names of all customers who have a loan of over $1200 { c  |  l, b, a ( c, l   borrower   l, b, a   loan  a > 1200)} • Find the names of all customers who have a loan from the Perryridge branch and the loan amount: { c, a  |  l ( c, l   borrower  b( l, b, a   loan  b = “Perryridge”))} or { c, a  |  l ( c, l   borrower   l, “Perryridge”, a   loan)}

21. Example Queries • Find the names of all customers having a loan, an account, or both at the Perryridge branch: { c  |  l ({ c, l   borrower  b,a ( l, b, a   loan  b = “Perryridge”))   a ( c, a   depositor  b,n ( a, b, n   account  b = “Perryridge”))} • Find the names of all customers who have an account at all branches located in Brooklyn: { c  |  n ( c, s, n   customer)   x,y,z ( x, y, z   branch  y = “Brooklyn”)   a,b ( x, y, z   account   c,a   depositor)}

22. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B Reminder: our Mini-U db

23. Examples • find all student records RTC:

24. Examples • (selection) find student record with ssn=123

25. Examples • (selection) find student record with ssn=123 or RTC:

26. Examples • (projection) find name of student with ssn=123

27. Examples • (projection) find name of student with ssn=123 need to ‘restrict’ “a” RTC:

28. Examples cont’d • (union) get records of both PT and FT students RTC:

29. Examples cont’d • (union) get records of both PT and FT students

30. Examples • difference: find students that are not staff RTC:

31. Examples • difference: find students that are not staff

32. Cartesian product • eg., dog-breeding: MALE x FEMALE • gives all possible couples = x

33. Cartesian product • find all the pairs of (male, female) - RTC:

34. Cartesian product • find all the pairs of (male, female) - RDC:

35. ‘Proof’ of equivalence • rel. algebra <-> rel. domain calculus <-> rel. tuple calculus

36. Overview - detailed • rel. domain calculus • why? • details • examples • equivalence with rel. algebra • more examples; ‘safety’ of expressions

37. More examples • join: find names of students taking cis351

38. Reminder: our Mini-U db

39. More examples • join: find names of students taking cis351 - in RTC

40. More examples • join: find names of students taking cis351 - in RDC

41. TAKES SSN c-id grade _x cis351 Sneak preview of QBE:

42. TAKES SSN c-id grade _x cis351 Sneak preview of QBE: • very user friendly • heavily based on RDC • very similar to MS Access interface

43. More examples • 3-way join: find names of students taking a 2-unit course - in RTC: join projection selection

44. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id 123 cis331 A 234 cis331 B Reminder: our Mini-U db _x .P _y 2 grade _x _y

45. More examples • 3-way join: find names of students taking a 2-unit course

46. More examples • 3-way join: find names of students taking a 2-unit course

47. Even more examples: • self -joins: find Tom’s grandparent(s)

48. Even more examples: • self -joins: find Tom’s grandparent(s)

49. Even more examples: • self -joins: find Tom’s grandparent(s)

50. Even more examples: • self -joins: find Tom’s grandparent(s)