Building a Simple English Query Application

An easy way to get started with the English Query designer is to point it at a copy of your database and start a new EQ project with the Project Wizard. Because you are most familiar with the data in your database, exploring this data with the designer shows you the benefits and limitations of English Query better than with the Tutorial databases.

Requirements for an English Query Application

English Query works best on a normalized database. When you use the Project Wizard, it makes assumptions about the relationships between your tables based on the referential integrity constraints between them. For example, a database containing customers and rentals with a one-to-many relationship will assume that "customers have rentals." Likewise, movies that reference a Genres lookup table will create the relationship "movies have genres."

If your database is not well normalized, English Query will return inaccurate results. Suppose that you had customers with more than one address. One way to solve this problem would be to add an "addresses" table with the CustomerID in it. However, your predecessor (lazy administrator that he was) decided to take the easy way out and violate first normal form. He coded the application to make a duplicate entry with a new CustomerID , identical customer name , and the second address.

English Query would then count this customer twice when asked the question "How many customers are there?"

TIP

You can use views to solve most of the problems that English Query encounters in databases that lack good normalization rules. After creating the views, you need to manually add the entities to your solution and then add the relationships between the entities based on those views.

Entities

You are probably used to hearing of an Entity as a table in a relational database. An entity in an English Query application is any noun-object that can be referenced in the database. Entities can be tables or specific columns in a table. For example, a Customer table would comprise an entity. Suppose there was another table, VisitLog, that tracked dates when a customer visited a store. Instead of adding the table, you could instead add just the VisitDate column as an entity. You would then describe this relationship as "Customers make a visit."

When you use the Project Wizard, it creates entities based on each table you select. You can choose to include or not include each table in your database.

TIP

English Query doesn't allow you to take entities based on tables in other databases. Remember that it does allow you to reference views, however, so you can get around this limitation by building views that reference other databases, and use those in your English Query project.

Relationships

An English Query relationship defines how the English Query entities in your project relate to each other. In a relational database, there are only a few types of relationships. A typical parent/child relationship could be one-to-one (which could be either one-to-zero-or-one or one-to- exactly-one ), or one-to-many. In an English Query relationship, you must define how the words your users will use map to the relationships between your tables.

"Customers have rentals" means that there is a relationship between the customers and rentals entities. Each entity could have many subentities (a table to a column, for example). A customer could have an address, a phone number, and a ZIP code. Each of these relationships will be represented in the application.

The Project Wizard infers most of these relationships for you. However, you must go through and define relationships needed by your users that the Project Wizard didn't guess. The relationships that will be guessed include the relationship between a table and its columns (customers have addresses and phone numbers ; movies have directors, run times, and genres) and relationships that are defined by primary and foreign keys (a customer rents a movie; a customer relates to rentals).

To define these relationships, you must select a phrasing type that describes the relationship. Here are the different types of phrases that can be defined in an English Query application.

Prepositional Phrasing

Prepositional phrases include a preposition, such as from . Here is an example of a prepositional phrase in an English Query:

 Which customers are from NY? 

To create a prepositional phrase, follow these steps:

1. Select the entity that will be described by the phrase. In this case, select the Customers entity.

2. Click the Add button for phrasings.

3. Select Prepositional Phrasing from the Phrasing Type dialog box.

4. Enter the prepositions that will be used to describe the relationship. In this case, from will be used.

5. Select the entity that contains the appropriate measurement. In this case, select Customer_state, which is a column in the Customers table.

Verb Phrasing

By default, English Query assumes that the basic verb have will describe the relationships between entities. In many cases, this is sufficient. However, users will employ additional verb phrases to ask questions about the data. All of the following questions are verb phrases:

 Who bought oranges last month?  How many movies were rented yesterday? How many customers visited stores in NY? 

The verbs in these questions are bought , rented , and visited .

Adjective Phrasing

The English language contains adjectives that describe, in inexact ways, traits of the nouns in sentences. For example, tall person or large store are adjective phrases that a user could ask your application to quantify.

To make this work, you need to specify both the adjectives that describe the measurements and what discrete measurement constitutes these adjectives. Suppose you had a movie rental database with renters' ages in it. This database would allow you to ask questions like in this example:

 Which movies were rented by young customers? 

To answer this question, the application needs to know what young means:

1. Edit the rentals relationship.

2. Add a phrase and select Adjective Phrase.

3. Of the three types of adjective phrases, select Measurement.

4. Four pieces of data are required to quantify what young means. Enter young in the Low Value box. Add synonyms such as teenager , kid , and children . Enter the numerical cutoff for young in the Numerical Threshold box. (Use 18 here.)

5. Finish by entering the same information for the High Threshold, using old , mature , and senior . Select an appropriate age for the high age threshold.

Trait Phrasing

Trait phrasing most often describes the relationships between a table and its columns. A customer has an address, a phone number, and an age.

 List customer address and age 

The Project Wizard normally imports this for you.

Name and ID Phrasing

Use these phrases to indicate the unique IDs of an entity and the descriptive names of an entityfor example:

 Who are the customers?  What are the customer's IDs? 

The Project Wizard normally imports this for you.

Subset Phrasing

A subset is another way of describing a type. For example, the genre of a movie is a subset of movies. You would need to define a subset phrase to answer the questions:

 Which movies are science fiction?  Who rented romances or dramas? 

To create a subset phrasing, follow these steps:

1. Add a relationship to the entity referencing the subset. In this case, use movies.

2. Add genres to the list of entities participating in the reference. Add a phrasing.

3. Select subset phrasing.

4. The subject is movies. The entity containing category values is genres.

Entity Synonyms

Every business has special words to describe things that are not in public parlance. If you were developing an English Query application for a movie rental store, for example, a movie type (genre) could have four or five different names: horror , thriller, and gore, for example.

You can make synonym entries to map any word to any other word in your application, as long as the entity type represents a field. By default, the English Query processor creates links between common English words.

NOTE

You can add synonyms to your application in two ways. Entity synonyms are in effect only for the data values in the entity for which you enter the synonym. Application synonyms are active throughout the application. If conflicts arise, entity synonyms override the priority of application synonyms.

To add a synonym, follow these steps:

1. Edit the entity. In this example, edit the genre_descriptions entity because it contains the actual words for which you want to provide synonyms.

2. Click Advanced Properties.

3. Select the Name Synonyms tab.

4. The left column is a list of synonyms for words in the right column. The words in the right column are the actual database values.

5. For this example, enter Chick Flicks and Drama , Thriller and Horror , and Gore and Horror . You can enter as many synonyms for a word as you like.

It now is possible to ask this question:

 List all the chick flicks and their running times. 

Dictionary Entries

Dictionary entries enable you to add to the existing English Query dictionary, define application-wide synonyms, adjust how the application interprets specific words, and adjust what the application returns in its results.

Three kinds of dictionary entries exist:

• Words (synonyms)

• Read synonyms (remap what the query processor sees)

• Write synonyms (remap what the user sees)

Words

Because the dictionary contains only the root forms of words and uses generalized grammatical rules to interpret tenses and plural nouns, any irregular words must be defined with a dictionary entry. The plural of the word person is people . Persons is also valid, but this is handled properly by the English Query language processor.

In the movie database example, People means "Customer." Here is how to add the word to the dictionary:

1. Select the menu option "Add Dictionary Entry."

2. In the Root form drop-down, select Person.

3. In the Part of speech drop-down, select Common Noun.

4. For this example, check the Irregular Plural check box and enter people .

Read Synonym

This enables you to invisibly map a word to another word for the entire application. For example, anytime you see the word person , you could interpret that to mean customer . Any queries that contain person would be fed into the processor as customer , and the returned results would reference the word customer . Assuming that you defined people as in the previous example, this question:

 Which people rented science fiction? 

gives this answer:

 The customers that rented science fiction are:  David Wordsmith        2001: A Space Odyssey Rachel Welch           2001: A Space Odyssey John Black             A Clockwork Orange 

Write Synonym

If you want to return different words from a question but you still want the processor to interpret them in their original form, you can create a write synonym. Creating a write synonym that maps Customer to Guest gives this slightly different result,

 Who rented science fiction? 

that gives this answer:

 The guests that rented science fiction are:  David Wordsmith         2001: A Space Odyssey Rachel Welch            2001: A Space Odyssey John Black              A Clockwork Orange