Domain Relational Calculus and Query-by-Example

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

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: { < l > | ∃ a, b ( < l, a, b > ∈ loan ⋀ a > 1200) } Equivalent Relational Algebra expression Πloan_number ( σamount > 1200 (loan))

4. Result of the query

5. Query-by-Example (QBE) • 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.

6. Corresponding Domain Relational Calculus query { < 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

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

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

13. In Microsoft Access: