Relational Calculus

1
Relational Calculus

• By Ying Chen
• CS 157A

2

• Tuple Relational Calculus
• Domain Relational Calculus

3
Tuple Relational Calculus

• It is a nonprocedural query language.
• A query is expressed as t P(t)
• It is the set of all tuples t such that predicate
  P is true for t
• t is a tuple variable, tA denotes the value of
  tuple t on attribute A

4
Predicate Calculus Formula

• Set of comparison operatores
• lt, , gt, , , ?
• Set of connectives
• and ( ), or (v), not ()
• Implication (gt)
• x gt y means if x is true, then y is true.

5
Predicate Calculus Formula(contd)

• ? t ? r ( Q(t) ) There exists a tuple t in
  relation r such that predicate Q (t) is true
• ? t ? r ( Q(t) ) Q is true for all tuples t in
  relation r

6
Banking Example

• Branch ( branch-name, branch-city )
• 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)

7
Example Queries

• Find the loan-number, branch-name, and amount for
  loans of over 1200
• t t ? loan tamount gt 1200

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

9
Example Queries

• 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(
  tcustomer-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 Perryridg
• ? uloan-number sloan-number))

11
Safety of Expressions

• It is possible to write tuple calculus
  expressions that generate infinite relations.
• For example, expression
• t t ? r results in an infinite relation
  if the domain of any attribute of relation r is
  infinite.

12
To solve this problem

• We restrict the set of allowable expressions to
  safe expressions.
• What is safe expression?

13
Definition of Safe Expression

• 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 tuple relational formula
  P.

14
Example

• t ( t ?loan) is NOT safe.
• It is possible to have a tuple t not in loan
  that contains values that do not appear in loan.
• All examples in the previous expressions are safe.

15
Domain Relational Calculus

• A nonprocedural query language equivalent in
  power to the tuple relational calculus.

16
Formal Definition

• Each query is an expression of the form
• ltx1, x2, , xngt P(x1, x2, , xn)
• 1) x1,x2,xn represent domain variables
• 2) P represents a formula similar to the
  predicate calculus

17
Example Queries

• Find the loan number, branch name, and amount for
  loans of over 1200
• lt l, b, a gt lt l, b, a gt ? loan a gt 1200

18
Example Queries

• Find all loan numbers for loans with an amount
  greater than 1200
• lt l gt ?b, a ( lt l, b, a gt ? loan a gt 1200 )

19
Example Queries

• Find the names of all customers who have a loan
  from the Perryridge branch and find the loan
  amount
• lt c, a gt ?l ( ltc, lgt ? borrower
• ? b ( lt l, b, a gt ?loan
• b Perryridge ) )

20
Reference

• Silberschatz, Korth, Sudarshan, Database System
  Conceptes (2002), McGraw-Hill, New York

21
The End