Designing Queries (Human-Computer Interaction)

When users ask questions of or communicate with the database, they are said to query it. Six different types of queries are among the most common. Your careful attention to query design can help reduce users’ time spent in querying the database, help them find the data they want, and result in a smoother user experience overall.

Query Types

The questions we pose concerning data from our database are referred to as queries. There are six basic query types. Each query involves three items: an entity, an attribute, and a value. In each case, two of these are given, and the intent of the query is to find the remaining item. Figure below will be used to illustrate all the query examples.

The entity and one of the entity’s attributes are given. The purpose of the query is to find the value. The query can be expressed as follows:

What is the value of a specified attribute for a particular entity?

Sometimes it is more convenient to use notation to formulate the query. This query can be written as

V ← (E, A)

where V stands for the value, E for entity, and A for attribute, and the variables in parentheses are given.

The question

What did employee number 73712 make in year 2009?

can be stated more specifically as

What is the value of the attribute YEAR-2009 for the entity EMPLOYEE NUMBER 73712?

The record containing employee number 73712 will be found, and the answer to the query will be $47,100.

The intent of the second query type is to find an entity or entities when an attribute and value are given. Query type 2 can be stated as follows:

What entity has a specified value for a particular attribute?

Because values can also be numeric, it is possible to search for a value equal to, greater than, less than, not equal to, greater than or equal to, and so on. An example of this type of query is as follows:

What employee(s) earned more than $50,000 in 2009?

The notation for query type 2 is

E ← (V, A)

In this case, three employees made more than $50,000, so the response will be a listing of the employee numbers for the three employees: 72845, 72888, and 80345.

The purpose of this query type is to determine which attributes fit the description provided when the entity and value are given. Query type 3 can be stated as follows:

What attribute(s) has a specified value for a particular entity?

This query is useful when many similar attributes have the same property. The following example has similar attributes (specific years) that contain the annual salaries for the employees of the company:

What years did employee number 72845 make over $50,000?

or, more precisely,

What attributes {YEAR-2006, YEAR-2007, YEAR-2008, YEAR-2009} have a value [gt] 50,000 for the entity EMPLOYEE-NUMBER  72845?

where the optional list in braces ({ }) is the set of eligible attributes.

The notation for query type 3 is

A ← (V, E)

In this example, Waters (employee number 72845) made over $50,000 for two years. Therefore, the response will be year 2007 and year 2009. Query type 3 is rarer than the preceding two types due to the requirement of having similar attributes exhibiting the same properties.

Query type 4 is similar to query type 1. The difference is that the values of all attributes are desired. Query 4 can be expressed as follows:

List all the values for all the attributes for a particular entity.

An example of query type 4 is:

List all the details in the earnings history file for employee number 72888.

The notation for query type 4 is

all V ← (E, all A)

The response for this query will be the entire record for the employee named Dryne (employee number 72888).

The fifth type of query is another global query, but it is similar in form to query type 2. Query type 5 can be stated as follows:

List all entities that have a specified value for all attributes.

An example of query type 5 is:

List all the employees whose earnings exceeded $50,000 in any of the years available.

The notation for query type 5 is

all E ← (V, all A)

The response to this query will be 72845, 72888, and 80345.

The sixth query type is similar to query type 3. The difference is that query type 6 requests a listing of the attributes for all entities rather than a particular entity. Query type 6 can be stated as follows:

List all the attributes that have a specified value for all entities.

The following is an example of query type 6:

List all the years for which earnings exceeded $40,000 for all employees in the company.

The notation for query type 6 is

all A ← (V, all E)

The response will be YEAR-2007, YEAR-2008, and YEAR-2009. As with query type 3, query type 6 is not used as much as other types.

The preceding six query types are only building blocks for more complex queries. Expressions, referred to as Boolean expressions, can be formed for queries. An example of a Boolean expression is:

List all the customers who have zip codes greater than or equal to 60001 and less than 70000, and who have ordered more than $500 from our catalogs or have ordered at least five times in the past year.

One difficulty with this statement is determining which operator (for example, AND) belongs with which condition; it is also difficult to determine the sequence in which the parts of the expression should be carried out. The following may help to clarify this problem:

LIST ALL CUSTOMERS HAVING (ZIP-CODE GE 60001 AND ZIP-CODE LT 70000) AND (AMOUNT-ORDERED GT 500 OR TIMES-ORDERED GE 5)

Now some of the confusion is eliminated. The first improvement is that the operators are expressed more clearly as GE, GT, and LT than as English phrases, such as “at least.” Second, the attributes are given distinct names, such as AMOUNT-ORDERED and TIMES-ORDERED. In the earlier sentence, these attributes were both referred to as “have ordered.” Finally, parentheses are used to indicate the order in which the logic is to be performed. Whatever is in parentheses is done first.

Operations are generally performed in a predetermined order of precedence. Arithmetic operations are usually performed first (exponentiation, then either multiplication or division, and then addition or subtraction). Next, comparative operations are performed. These operations are GT (greater than), LT (less than), and others. Finally, the Boolean operations are performed (first AND and then OR). Within the same level, the order generally goes from left to right. The precedence is summarized in the illustration below.

1
2
3

* *
* /
+ –

GT LT
EQ NE
GE LE

5
6

AND
OR

Query Methods

Two popular query methods are query by example and structured query language.

Query by example (QBE) is a simple but powerful method for implementing queries in database systems, such as Microsoft Access. The database fields are selected and displayed in a grid, and the requested query values are either entered in the field area or below the field. The query should be able to select both rows from the table that match conditions as well as specific columns (fields). Complex conditions may be set to select records, and the user may easily specify the columns to be sorted. Figure shown below an example of a query using Microsoft Access. The query design screen is divided into two portions. The top portion contains the tables selected for the query and their relationships, and the bottom portion contains the query selection grid. Fields from the database tables are dragged to the grid.

The first two rows contain the field and the table in which the field is located. The next row contains sorting information. In this example, the results will be sorted by CUSTOMER NAME. A check mark in the Show box (fourth row down) indicates that the field is to be displayed in the results. Notice that the CUSTOMER NUMBER, CUSTOMER NAME, and STATUS CODE MEANING are selected for the resulting display (other fields are displayed as well, but they do not show in the display). Notice that the ACCOUNT STATUS CODE and ACCOUNT TYPE CODE are not checked and therefore will not be in the final results. In the criteria rows, there is a 1 in the ACCOUNT STATUS CODE (indicating an active record) and a C and D (selecting a General Customer or a Discount Customer) in the ACCOUNT TYPE CODE columns. Two conditions in the same row indicate an AND condition, and two conditions in different rows represent an OR condition. This query specifies that the user should select both an Active Customer and either a General or Discount Customer.

The results of a query are displayed in a table, illustrated in the figure below. Notice that the ACCOUNT STATUS CODE and ACCOUNT TYPE CODE do not display. They are not checked and are included in the query for selection purposes only. Instead, the code meanings are displayed, which are more useful to the user. The customer names are sequenced alphabetically.

Structured query language (SQL) is another popular way to implement queries. It uses a series of words and commands to select the rows and columns that should be displayed in the resulting table. Figure illustrated below contains SQL code. The SELECT DISTINCTROW keyword determines which rows are to be selected. The WHERE keyword specifies the condition that the CUSTOMER NAME should be used to select the data entered in the LIKE parameter.

SELECT DISTINCTROW
  Customer.[Customer Number],
  Customer.[Customer Name],
  Customer.City,
  Customer.Telephone
FROM Customer
WHERE (((Customer.[Customer Name])
Like ([Enter a partial Customer Name] & “*”)));