Domain Relational Calculus and QuerybyExample

1
Domain Relational Calculus and Query-by-Example

• CS157a
• John Eagle

2

• Domain Relational Calculus
• A form of Relational Calculus which uses domain
  variables that take on values from an attributes
  domain, rather than values for an entire tuple.
• Serves as the theoretical basis of the QBE
  (Query-by Example) Language.
• Definition
• An expression in Domain Calculus is of the form
• lt x1, x2, , xn gt P(x1, x2, , xn)
• where
• x1, x2, , xn represents domain variables
• P represents a formula composed of atoms
• An Atom has the form
• lt x1, x2, , xn gt ? r , where r is a relation on
  n attributes and x1, x2, , xn are the domain
  variables or domain constraints.
• xTy , where x and y are domain variables and T is
  the comparison operator (lt,gt, , , , ?). It is
  required that x and y have domains that can be
  compared by T.
• xTc, where x is a domain variable, T is a
  comparison operator, and c is a constraint in the
  domain of attributes for which x is a domain
  variable.

3

• We build up formulae from atoms by using the
  following rules
• An Atom is a formula.
• If P1 is a formula, then so are P1 and (P1).
• If P1 and P2 are formulae, then so are P1 ? P2,
  P1 ? P2, and P1 ? P2.
• If P1(x) is a formula in x, where x is a free
  domain variable, then
• ? x (P1(x)) and ? x (P1(x))
• Example of queries using Domain Relational
  Calculus
• Find all loan numbers for loans with an amount
  greater than 1200
• lt l gt ? a, b ( lt l, a, b gt ? loan ? a gt
  1200)
• Equivalent Relational Algebra expression
• ?loan_number ( samount gt 1200 (loan))

4
Result of the query
5

• Query-by-Example (QBE)
• Developed at IBM in the early 1970s
• QBE is both the name of the DML and an early
  database system that included this language.
• Has two distinct features
• QBE has a two dimensional syntax. Queries look
  like tables.
• QBE queries are expressed by example. Instead
  of giving procedures for obtaining a desired
  answer, the user gives an example of what is
  desired.
• There are two flavors of QBE
• The original text based version
• Graphical version (i.e. Microsoft Access, Borland
  Paradox)
• Skeleton Tables
• Queries in QBE are expressed by using skeleton
  tables. These tables show the relational schema
  of the database.
• Users select the skeleton for the tables needed
  to form a query and then fills in the skeletons
  with example rows. An example row consists of
  constants and example elements (domain variables)
• This query tells the system to look for tuples in
  loan that have Perryridge as the value for the
  branch_name attribute and display each
  corresponding loan_number.

6

• Corresponding Domain Relational Calculus query
• lt x gt ? b, a ( lt x, a, b gt ? loan ? b gt
  Perryridge")
• QBE performs duplicate elimination automatically.
  To suppress this feature, insert ALL after the
  P. command.
• QBE assumes that the blank variable is has a
  unique value
• To display the entire loan relation insert P. in
  every field
• or place P. in the column headed by the
  relation name

7

• QBE allows queries which involve arithmetic
  comparisons
• For example Find the loan numbers of all loans
  with a loan amount of more than 700
• Comparisons can only involve only one arithmetic
  expression on the right-hand side of the
  comparison operation, as seen in the previous
  example. The space on the left-hand side of the
  comparison operation must be blank.
• The expression can include both variables and
  constants.
• QBE support the following arithmetic operations
  , , lt, gt, ,
• Example Find the names of all branches that are
  not located in Brooklyn
• The primary purpose of variables in QBE is to
  force values of certain tuples to have the same
  value on certain attributes.
• Example Find the loan numbers of all loans made
  jointly to Smith and Jones.
• In domain relational calculus the query would be
  written as
• lt l gt ? x ( ltx, lgt ? borrower ? x
  Smith) ? ? x ( ltx, lgt ? borrower ? x
  Jones)

8

• The QBE version of this query is
• Find all customers who live in the same city as
  Jones.
• QBE allows queries that span several different
  relations (analogous to Cartesian product or
  natural join in relational algebra).
• Find the names of all customers who have a loan
  from the Perryridge branch.

9

• At times it is either inconvenient or impossible
  to express all the constraints on the domain
  variables within the skeleton tables. QBE solves
  this problem by using a condition box.
• Logical expressions are used in the condition
  box.
• Example Find the loan number of all loans made
  to Smith, to Jones, or to both jointly.

10

• QBE in Microsoft Access
• Graphical version of query-by-example
• Attributes are arranged vertically instead of
  horizontally.
• Access uses a line to link attributes of two
  tables instead of a shared variable (as in the
  text version), to specify a join condition.
• Links between tables are created automatically
  based on the attribute name.
• Results of a query are displayed in the design
  grid.
• Example
• Find the customer_name, account_number, and
  balance for all accounts at the Perryridge branch.

11

• The Query
• The Result

12

• Find the names of all customers who have a loan
  from the Perryridge branch, and find the loan
  amount.
• In domain relational calculus
• lt c,a gt ? l ( ltc, lgt ? borrower
• ? ? b ( ltl, b, agt ? loan ? b
  Perryridge)
• In text based QBE
• In relational algebra
• ?customer_name, amount ( sbranch_name
  Perryridge (loan borrower))

13

• In Microsoft Access