QBE and Domain Relational Calculus

QBE (Query-By-Example)

QBE is an abbreviation for Query-By-Example. It was developed at IBM in the early 1970’s. QBE is named as the combination of both DML and an early database system as they included this language. It has two distinct features:

• QBE has two-dimensional syntax. Queries look like that of tables.
• QBE queries are expressed in the form of “by example”. The user gives an example of what is desired instead of giving procedures for obtaining the desired answer.

There are two flavors of QBE and they are:

• The original text-based version.
• Graphical version (i.e. Microsoft Access, Borland Paradox).

Skeleton Tables

Queries in QBE are expressed using skeleton tables. The skeleton tables show the relational schema of the database. Users select the skeleton for the tables which are needed to form a query and then fills in the skeletons with example rows. An example row consists of constants and example elements that are domain variables.

This query tells the system to look for tuples inloan that has “Perryridge” as the value for the attribute named "branch_name" and displays each corresponding "loan_number".

The corresponding Domain Relational Calculus query is:

{ < x > | ∃ b, a ( < x, a, b > ∈ loan â ‹ € b > “Perryridge") }

• QBE automatically performs the elimination of duplicates. In order to suppress this feature, insert ALL after the P. command.
  
  
  
  
• QBE assumes that the blank variable is a unique value and has a unique value.
  
  
  
  
• Insert P. in every field to display the entire loan relation
  
  
  

  or place P. in the column headed by the relation name.
  
  
  
  

• QBE allows the queries which involve arithmetic comparisons. For example: Find the loan numbers of all loans with a loan amount of more than 700.
  
  
  
  
• Comparisons can involve only one arithmetic expression on the right-hand side of the comparison operation which is seen in the previous example. The space on the left-hand side of the comparison operation must be blank. The expression can include both the variables and constants. QBE support the following arithmetic operations: ¬, =,<, >, ≤, ≥.
• Example: Find the names of all branches which are not located in Brooklyn.
  
  
• The primary purpose of variables in QBE is to force the values of certain tuples to have same values on certain attributes.
  Example: Find the loan numbers of all the loans made as a joint to Smith and Jones.

  In domain relational calculus the query would be written as:

  { < l > | ∃ x ( <x, l> ∈ borrower â‹€ x = “Smith”) â ‹ € ∃ x ( <x, l> ∈ borrower â‹€ x = “Jones”)}

  The QBE version of this query is:
  
  

• Find all the customers who live in same city as Jones.
  
  

  QBE allows queries that span several different relations which are analogous to Cartesian product or natural join in relational algebra.
  
  

• Find the names of all customers who have a loan from the Perryridge branch.
  
  
  
  
  
  
  

At most times, it is either inconvenient or impossible to express all the constraints in the domain variables within the skeleton tables. QBE solves this problem 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.

QBE in Microsoft Access: Microsoft Access is the graphical version of query-by-example. Their attributes are arranged vertically instead of horizontally. Microsoft Access uses a line to link attributes of two tables instead of a shared variable like in the text version in order to specify a join condition. The links between tables are created automatically based on their attribute names. Results of a query are displayed in the design grid.

Example:

Find the names of all the customers who have a loan from the Perryridge branch and find the loan amount.

In domain relational calculus:

{ < c,a > | ∃ l ( <c, l> ∈ borrower

â‹€ ∃ b ( <l, b, a> ∈ loan â‹€ b = “Perryridge”)}

In text based QBE

Domain Relational Calculus

Domain Relational Calculus can be understood as a form of relational calculus which uses domain variables that take on values from an attributes domain rather than values for an entire tuple. It serves as the theoretical basis of QBE (Query-By -Example) language.

Definition:

An expression in domain calculus is of the form:

{< x1, x2, … , xn > | P (x1, x2, … , xn) }

where x1, x2, … , xn represents domain variables.

P represents a formula composed of atoms.

An Atom has the form:

• < x1, x2, … , xn > ∈ r where r is a relation on n attributes and x1, x2, … , xn are the domain variables or domain constraints.
• xΘy where x and y are domain variables and Θ is the comparison operator (<,>, ≤, ≥, =, ≠). It is required for x andy to have domains that can be compared by Θ.
• xΘc where x is a domain variable, Θ is a comparison operator and c is a constraint in the domain of attributes for which x is a domain variable.

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 which has an amount greater than $1200:

{ < l > | ∃ a, b ( < l, a, b > ∈ loan â ‹ € a > 1200) }.

Equivalent Relational Algebra expression:

Πloan_number ( σamount > 1200 (loan))

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.