1 / 13

# Domain Relational Calculus and Query-by-Example - PowerPoint PPT Presentation

Domain Relational Calculus and Query-by-Example. CS157a John Eagle. 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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Domain Relational Calculus and Query-by-Example' - brook

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Domain Relational Calculus and Query-by-Example

CS157a

John Eagle

• 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

{< 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 that x and y 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.

• 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:

{ < l > | ∃ a, b ( < l, a, b > ∈ loan ⋀ a > 1200) }

Equivalent Relational Algebra expression

Πloan_number ( σamount > 1200 (loan))

Query-by-Example (QBE) rules:

• Developed at IBM in the early 1970’s

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

{ < x > | ∃ b, a ( < x, a, b > ∈ loan ⋀ b > “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

• QBE allows queries which involve arithmetic comparisons rules:

• 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: ¬, =, <, >, ≤, ≥

• 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:

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

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

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

QBE in Microsoft Access all the constraints on the domain variables within the skeleton tables. QBE solves this problem by using a

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

The Query: all the constraints on the domain variables within the skeleton tables. QBE solves this problem by using a

The Result:

• Find the names of all 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:

In relational algebra:

Πcustomer_name, amount ( σbranch_name = “Perryridge” (loan borrower))

In Microsoft Access: Perryridge branch, and find the loan amount.