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?"
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.
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 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:
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 .
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:
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.
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:
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.
To add a synonym, follow these steps:
It now is possible to ask this question:
List all the chick flicks and their running times.
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:
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:
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
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