Domain relational calculus and query by example
Download
1 / 13

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


  • 99 Views
  • Uploaded on

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.

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

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


An Image/Link below is provided (as is) to download presentation

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

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

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


Domain relational calculus and query by example

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:

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

    Equivalent Relational Algebra expression

    Πloan_number ( σamount > 1200 (loan))



Domain relational calculus and query by example

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.


Domain relational calculus and query by example

Corresponding Domain Relational Calculus query rules:

{ < 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


Domain relational calculus and query by example

  • 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”)}


Domain relational calculus and query by example

The QBE version of this query is: rules:

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


Domain relational calculus and query by example

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


Domain relational calculus and query by example

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.


Domain relational calculus and query by example

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

The Result:


Domain relational calculus and query by example

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


Domain relational calculus and query by example

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