1 / 58

Principles of Database Systems: Relational Model III

Explore the history, concepts, and formal query languages of the relational model. Dive into detailed topics such as relational tuple calculus, safety of expressions, and the use of rel. domain calculus. Examples and queries provided.

igable
Download Presentation

Principles of Database Systems: Relational Model III

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. 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)

More Related