Other Relational Languages.

Other Relational Languages

In Chapter 4, we described SQL — the most influential commercial relational-database language. In this chapter, we study two more languages: QBE and Data log. Unlike SQL, QBE is a graphical language, where queries look like tables. QBE and its variants are widely used in database systems on personal computers. Data log has a syntax modeled after the Prolog language. Although not used commercially at present, Data log has been used in several research database systems.

Here, we present fundamental constructs and concepts rather than a complete users’ guide for these languages. Keep in mind that individual implementations of a language may differ in details, or may support only a subset of the full language.

In this chapter, we also study forms interfaces and tools for generating reports and analyzing data. While these are not strictly speaking languages, they form the main interface to a database for many users. In fact, most users do not perform explicit querying with a query language at all, and access data only via forms, reports, and other data analysis tools.

Query-by-Example

Query-by-Example (QBE) is the name of both a data-manipulation language and an early database system that included this language. The QBE database system was developed at IBM’s T. J. Watson Research Center in the early 1970s. The QBE data- manipulation language was later used in IBM’s Query Management Facility (QMF). Today, many database systems for personal computers support variants of QBE language. In this section, we consider only the data-manipulation language. It has two distinctive features:

1. Unlike most query languages and programming languages, QBE has a two- dimensional syntax: Queries look like tables. A query in a one-dimensional language (for example, SQL) can be written in one (possibly long) line. A two- dimensional language requires two dimensions for its expression. (There is a one-dimensional version of QBE, but we shall not consider it in our discussion).

2. QBE queries are expressed “by example.” Instead of giving a procedure for obtaining the desired answer, the user gives an example of what is desired. The system generalizes this example to compute the answer to the query.

Despite these unusual features, there is a close correspondence between QBE and the domain relational calculus.

We express queries in QBE by skeleton tables. These tables show the relation schema, as in Figure 5.1. Rather than clutter the display with all skeletons, the user selects those skeletons needed for a given query and fills in the skeletons with example rows. An example row consists of constants and example elements, which are domain variables. To avoid confusion between the two, QBE uses an underscore character ( ) before domain variables, as in x, and lets constants appear without any qualification.

This convention is in contrast to those in most other languages, in which constants are quoted and variables appear without any qualification.

Queries on One Relation

Returning to our ongoing bank example, to find all loan numbers at the Perryridge branch, we bring up the skeleton for the loan relation, and fill it in as follows:

This query tells the system to look for tuples in loan that have “Perryridge” as the value for the branch-name attribute. For each such tuple, the system assigns the value of the loan-number attribute to the variable x. It “prints” (actually, displays) the value of the variable x, because the command P. appears in the loan-number column next to the variable x. Observe that this result is similar to what would be done to answer the domain-relational-calculus query

{(x)| ∃ b, a((x, b, a) ∈ loan ∧ b = “Perryridge”)}

QBE assumes that a blank position in a row contains a unique variable. As a result, if a variable does not appear more than once in a query, it may be omitted. Our previous query could thus be rewritten as

Comparisons can involve only one arithmetic expression on the right-hand side of the comparison operation (for example, > ( x + y − 20)). The expression can include

both variables and constants. The space on the left-hand side of the comparison operation must be blank. The arithmetic operations that QBE supports are =, <, ≤, >, ≥, and ¬.

Note that requiring the left-hand side to be blank implies that we cannot compare two distinct named variables. We shall deal with this difficulty shortly.

As yet another example, consider the query “Find the names of all branches that are not located in Brooklyn.” This query can be written as follows:

To execute this query, the system finds all pairs of tuples in borrower that agree on the loan-number attribute, where the value for the customer-name attribute is “Smith” for one tuple and “Jones” for the other. The system then displays the value of the loan-number attribute.

In the domain relational calculus, the query would be written as

Queries on Several Relations

QBE allows queries that span several different relations (analogous to Cartesian product or natural join in the relational algebra). The connections among the various relations are achieved through variables that force certain tuples to have the same value on certain attributes. As an illustration, suppose that we want to find the names of all customers who have a loan from the Perryridge branch. This query can be written as

To evaluate the preceding query, the system finds tuples in loan with “Perryridge” as the value for the branch-name attribute. For each such tuple, the system finds tuples in borrower with the same value for the loan-number attribute as the loan tuple. It displays the values for the customer-name attribute.

We can use a technique similar to the preceding one to write the query “Find the names of all customers who have both an account and a loan at the bank”:

Now consider the query “Find the names of all customers who have an account at the bank, but who do not have a loan from the bank.” We express queries that involve negation in QBE by placing a not sign (¬) under the relation name and next to an example row:

Compare the preceding query with our earlier query “Find the names of all cus- tomers who have both an account and a loan at the bank.” The only difference is the ¬ appearing next to the example row in the borrower skeleton. This difference, however, has a major effect on the processing of the query. QBE finds all x values for which

1. There is a tuple in the depositor relation whose customer-name is the domain variable x.

2. There is no tuple in the borrower relation whose customer-name is the same as in the domain variable x.

The ¬ can be read as “there does not exist.”

The fact that we placed the ¬ under the relation name, rather than under an attribute name, is important. A ¬ under an attribute name is shorthand for /=. Thus, to find all customers who have at least two accounts, we write

In English, the preceding query reads “Display all customer-name values that appear in at least two tuples, with the second tuple having an account-number different from the first.”

The Condition Box

At times, it is either inconvenient or impossible to express all the constraints on the domain variables within the skeleton tables. To overcome this difficulty, QBE includes a condition box feature that allows the expression of general constraints over any of the domain variables. QBE allows logical expressions to appear in a condition box.

The logical operators are the words and and or, or the symbols “&” and “|”.

For example, the query “Find the loan numbers of all loans made to Smith, to Jones  (or to both jointly)” can be written as

It is possible to express the above query without using a condition box, by using P. in multiple rows. However, queries with P. in multiple rows are sometimes hard to understand, and are best avoided.

As yet another example, suppose that we modify the final query in Section 5.1.2 to be “Find all customers who are not named ‘Jones’ and who have at least two ac- counts.” We want to include an “x /= Jones” constraint in this query. We do that by bringing up the condition box and entering the constraint “x ¬ = Jones”:

As another example, consider the query “Find all branches that have assets greater than those of at least one branch located in Brooklyn.” This query can be written as

QBE allows complex arithmetic expressions to appear in a condition box. We can write the query “Find all branches that have assets that are at least twice as large as the assets of one of the branches located in Brooklyn” much as we did in the preceding query, by modifying the condition box to

The Result Relation

The queries that we have written thus far have one characteristic in common: The results to be displayed appear in a single relation schema. If the result of a query includes attributes from several relation schemas, we need a mechanism to display the desired result in a single table. For this purpose, we can declare a temporary result relation that includes all the attributes of the result of the query. We print the desired result by including the command P. in only the result skeleton table.

As an illustration, consider the query “Find the customer-name, account-number, and balance for all accounts at the Perryridge branch.” In relational algebra, we would construct this query as follows:

1. Join depositor and account.

2. Project customer-name, account-number, and balance. To construct the same query in QBE, we proceed as follows:

1. Create a skeleton table, called result, with attributes customer-name, account- number, and balance. The name of the newly created skeleton table (that is, result) must be different from any of the previously existing database relation names.

2. Write the query.

The resulting query is

Ordering of the Display of Tuples

QBE offers the user control over the order in which tuples in a relation are displayed. We gain this control by inserting either the command AO. (ascending order) or the command DO. (descending order) in the appropriate column. Thus, to list in ascending alphabetic order all customers who have an account at the bank, we write

QBE provides a mechanism for sorting and displaying data in multiple columns. We specify the order in which the sorting should be carried out by including, with each sort operator (AO or DO), an integer surrounded by parentheses. Thus, to list all account numbers at the Perryridge branch in ascending alphabetic order with their respective account balances in descending order, we write

The command P.AO(1). specifies that the account number should be sorted first; the command P.DO(2). specifies that the balances for each account should then be sorted.

Aggregate Operations

QBE includes the aggregate operators AVG, MAX, MIN, SUM, and CNT. We must post- fix these operators with ALL. to create a multiset on which the aggregate operation is evaluated. The ALL. operator ensures that duplicates are not eliminated. Thus, to find the total balance of all the accounts maintained at the Perryridge branch, we write

The average balance is computed on a branch-by-branch basis. The keyword ALL. in the P.AVG.ALL. entry in the balance column ensures that all the balances are considered. If we wish to display the branch names in ascending order, we replace P.G. by P.AO.G.

To find the average account balance at only those branches where the average account balance is more than $1200, we add the following condition box:

As another example, consider the query “Find all customers who have accounts at each of the branches located in Brooklyn”:

The domain variable w can hold the value of names of branches located in Brooklyn. Thus, CNT.UNQ. w is the number of distinct branches in Brooklyn. The domain variable z can hold the value of branches in such a way that both of the following hold:

• The branch is located in Brooklyn.

• The customer whose name is x has an account at the branch.

Thus, CNT.UNQ. z is the number of distinct branches in Brooklyn at which customer x has an account. If CNT.UNQ. z = CNT.UNQ. w, then customer x must have an account at all of the branches located in Brooklyn. In such a case, the displayed result includes x (because of the P.).

Modification of the Database

In this section, we show how to add, remove, or change information in QBE.

Deletion

Deletion of tuples from a relation is expressed in much the same way as a query. The major difference is the use of D. in place of P. QBE (unlike SQL), lets us delete whole tuples, as well as values in selected columns. When we delete information in only some of the columns, null values, specified by −, are inserted.

We note that a D. command operates on only one relation. If we want to delete tuples from several relations, we must use one D. operator for each relation.

Here are some examples of QBE delete requests:

• Delete customer Smith.

• Delete the branch-city value of the branch whose name is “Perryridge.”

Thus, if before the delete operation the branch relation contains the tuple (Perryridge, Brooklyn, 50000), the delete results in the replacement of the pre- ceding tuple with the tuple (Perryridge, −, 50000).

• Delete all loans with a loan amount between $1300 and $1500.

Note that, in expressing a deletion, we can reference relations other than those from which we are deleting information.

Insertion

To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted. We do the insertion by placing the I. operator in the query expression. Obviously, the attribute values for inserted tuples must be members of the attribute’s domain.

The simplest insert is a request to insert one tuple. Suppose that we wish to insert the fact that account A-9732 at the Perryridge branch has a balance of $700. We write

More generally, we might want to insert tuples on the basis of the result of a query. Consider again the situation where we want to provide as a gift, for all loan customers of the Perryridge branch, a new $200 savings account for every loan account that they have, with the loan number serving as the account number for the savings account. We write

To execute the preceding insertion request, the system must get the appropriate information from the borrower relation, then must use that information to insert the appropriate new tuple in the depositor and account relations.

Updates

There are situations in which we wish to change one value in a tuple without changing all values in the tuple. For this purpose, we use the U. operator. As we could for insert and delete, we can choose the tuples to be updated by using a query. QBE, however, does not allow users to update the primary key fields.

Suppose that we want to update the asset value of the of the Perryridge branch to $10,000,000. This update is expressed as

The blank field of attribute branch-city implies that no updating of that value is required.

The preceding query updates the assets of the Perryridge branch to $10,000,000, regardless of the old value. There are circumstances, however, where we need to update a value by using the previous value. Suppose that interest payments are being made, and all balances are to be increased by 5 percent. We write

This query specifies that we retrieve one tuple at a time from the account relation, determine the balance x, and update that balance to x * 1.05.

QBE in Microsoft Access

In this section, we survey the QBE version supported by Microsoft Access. While the original QBE was designed for a text-based display environment, Access QBE is designed for a graphical display environment, and accordingly is called graphical query-by-example (GQBE).

Figure 5.2 An example query in Microsoft Access QBE.

Figure 5.2 shows a sample GQBE query. The query can be described in English as “Find the customer-name, account-number, and balance for all accounts at the Perryridge branch.” Section 5.1.4 showed how it is expressed in QBE.

A minor difference in the GQBE version is that the attributes of a table are writ- ten one below the other, instead of horizontally. A more significant difference is that the graphical version of QBE uses a line linking attributes of two tables, instead of a shared variable, to specify a join condition.

An interesting feature of QBE in Access is that links between tables are created automatically, on the basis of the attribute name. In the example in Figure 5.2, the two tables account and depositor were added to the query. The attribute account-number is shared between the two selected tables, and the system automatically inserts a link between the two tables. In other words, a natural join condition is imposed by default between the tables; the link can be deleted if it is not desired. The link can also be specified to denote a natural outer-join, instead of a natural join.

Another minor difference in Access QBE is that it specifies attributes to be printed in a separate box, called the design grid, instead of using a P. in the table. It also specifies selections on attribute values in the design grid.

Queries involving group by and aggregation can be created in Access as shown in Figure 5.3. The query in the figure finds the name, street, and city of all customers who have more than one account at the bank; we saw the QBE version of the query earlier in Section 5.1.6. The group by attributes as well as the aggregate functions

Figure 5.3 An aggregation query in Microsoft Access QBE.

are noted in the design grid. If an attribute is to be printed, it must appear in the design grid, and must be specified in the “Total” row to be either a group by, or have an aggregate function applied to it. SQL has a similar requirement. Attributes that participate in selection conditions but are not to be printed can alternatively be marked as “Where” in the row “Total”, indicating that the attribute is neither a group by attribute, nor one to be aggregated on.

Queries are created through a graphical user interface, by first selecting tables. Attributes can then be added to the design grid by dragging and dropping them from the tables. Selection conditions, grouping and aggregation can then be specified on the attributes in the design grid. Access QBE supports a number of other features too, including queries to modify the database through insertion, deletion, or update.