Temple University

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(tcustomer-name
  scustomer-name) ? ?u ? depositor(tcustomer
  -name ucustomer-name)
• Find the names of all customers who have a loan
  and an account at the bank
• t ?s ? borrower(tcustomer-name
  scustomer-name) ? ?u ? depositor(tcustome
  r-name ucustomer-name)

10
Example Queries

• Find the names of all customers having a loan at
  the Perryridge branch
• t ?s ? borrower(tcustomer-name
  scustomer-name ? ?u ? loan(ubranch-name
  Perryridge ?
  uloan-number sloan-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(tcustomer-name
  scustomer-name ? ?u ? loan(ubranch-name
  Perryridge ?
  uloan-number sloan-number)) ? not ?v
  ? depositor (vcustomer-name
  
  tcustomer-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(sbranch-name Perryridge
  ? ?u ? borrower (uloan-number
  sloan-number ? t customer-name
  ucustomer-name) ? ? v ? customer
  (ucustomer-name vcustomer-name
  ? tcustomer-city vcustomer-city)))
  

12
Example Queries

• Find the names of all customers who have an
  account at all branches located in Brooklyn
• t ? c ? customer (tcustomer.name
  ccustomer-name) ?
• ? s ? branch(sbranch-city Brooklyn ?
  ? u ? account ( sbranch-name
  ubranch-name ? ? s ? depositor (
  tcustomer-name scustomer-name
  ? saccount-number uaccount-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 ssn123

16
Rel. Dom. Calculus

• find STUDENT record with ssn123

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 gt 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 gt 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
Reminder our Mini-U db
23
Examples

• find all student records

RTC
24
Examples

• (selection) find student record with ssn123

25
Examples

• (selection) find student record with ssn123

or
RTC
26
Examples

• (projection) find name of student with ssn123

27
Examples

• (projection) find name of student with ssn123

need to restrict a
RTC
28
Examples

• (projection) find name of student with ssn123 -
  RTC

29
Examples contd

• (union) get records of both PT and FT students

RTC
30
Examples contd

• (union) get records of both PT and FT students

31
Examples

• difference find students that are not staff

RTC
32
Examples

• difference find students that are not staff

33
Cartesian product

• eg., dog-breeding MALE x FEMALE
• gives all possible couples

x
34
Cartesian product

• find all the pairs of (male, female) - RTC

35
Cartesian product

• find all the pairs of (male, female) - RDC

36
Proof of equivalence

• rel. algebra lt-gt rel. domain calculus
• lt-gt rel. tuple calculus

37
Overview - detailed

• rel. domain calculus
• why?
• details
• examples
• equivalence with rel. algebra
• more examples safety of expressions

38
More examples

• join find names of students taking cis351

39
Reminder our Mini-U db
40
More examples

• join find names of students taking cis351 - in
  RTC

41
More examples

• join find names of students taking cis351 - in
  RDC

42
Sneak preview of QBE
43
Sneak preview of QBE

• very user friendly
• heavily based on RDC
• very similar to MS Access interface

44
More examples

• 3-way join find names of students taking a
  2-unit course - in RTC

join
projection
selection
45
Reminder our Mini-U db
_x
.P
_y
2
grade
_x
_y
46
More examples

• 3-way join find names of students taking a
  2-unit course

47
More examples

• 3-way join find names of students taking a
  2-unit course

48
Even more examples

• self -joins find Toms grandparent(s)

49
Even more examples

• self -joins find Toms grandparent(s)

50
Even more examples

• self -joins find Toms grandparent(s)

51
Even more examples

• self -joins find Toms grandparent(s)

52
Hard examples DIVISION

• find suppliers that shipped all the ABOMB parts

53
Hard examples DIVISION

• find suppliers that shipped all the ABOMB parts

54
Hard examples DIVISION

• find suppliers that shipped all the ABOMB parts

55
More on division

• find students that take all the courses that
  ssn123 does (and maybe even more)

56
More on division

• find students that take all the courses that
  ssn123 does (and maybe even more)

57
Safety of expressions

• similar to RTC
• FORBIDDEN

58
Safety of Expressions

• ? x1, x2, , xn ? P(x1, x2, , xn)
• is safe if all of the following hold
• 1. All values that appear in tuples of the
  expression are values from dom a(P) (that is,
  the values appear either in P or in a tuple of
  a relation mentioned in P )
• 2. For every there exists subformula of the
  form ? x (P1(x)), the subformula is true if and
  only if there is a value x in dom (P1) such
  that P1(x) is true.
• 3. For every for all subformula of the form
  ?x (P1 (x)), the subformula is true if and
  only if P1(x) is true for all values x from dom
  (P1).

59
Overview - detailed

• rel. domain calculus QBE
• dfn
• details
• equivalence to rel. algebra

60
(No Transcript)
61
table