SQL Server Magazine

When I started to learn English Query, I often knew the question I wanted English Query to answer, but I wasn't sure how to make English Query understand. Understanding the various relationships that you can define among entities was the key to giving English Query the proper information to answer my questions. This article looks at the relationships that you can define within English Query, how to define them, and the benefits you derive from their use. English Query supports the following relationships: name/ID, trait, preposition, adjective, subset, verb, and command. "English Query from the Beginning," June 2000, uses the SQL Server Northwind database to explain the basics of how to build and test an English Query model. This article also uses the Northwind database and the sample English Query model for Northwind that we built in the earlier article.

Although your initial goal in an English Query project might be to answer the most common questions your users will ask, the ultimate goal is to identify and model all the relationships between entities in your database. You want to have a semantic model that represents the business you run. During the final keynote address at the Professional Association for SQL Server (PASS) conference in October 2000, the public got a peek at some of the future benefits that can result from having a semantic model of your business. Using an English Query semantic model, a Microsoft demo automatically generated Web pages to display all the data the model represented and exposed the data as a Microsoft Outlook Web part. These HTML pages included such relationships as the parent-child relationship between the Orders and Order Details tables in the Northwind database. Developing a semantic model of your business has powerful implications. Imagine all the things you might be able to generate automatically if you had such a model. You might be able to automatically generate the physical data model, Visual Basic (VB) forms to implement data entry, and (as the previously mentioned demo shows) Web-based forms. Right now, English Query is the way you make a semantic model, which can be the basis for more than just English Query.

To create the complete semantic model, you must identify all the relationships among all the entities in your database. You can expose these relationships by asking questions that your users might ask. Every question you ask is actually a proxy—an example that represents a class of questions that users might ask. When you model the relationship or phrasing that lets English Query answer your specific question, English Query will probably be able to use that new relationship or phrasing to answer an entire class of related questions.

After you've defined the tables and entities to English Query (as we did in "English Query from the Beginning"), you must define relationships. Relationships describe how entities relate to one another. Although you might have defined the customers and products entities, English Query has no inherent understanding of how these two entities relate to one another. For example, English Query doesn't know that customers buy products until you define the relationship stating that fact.

As I mentioned in "English Query from the Beginning," the English Query 2000 Project Wizard automatically generates many of the relationships that exist among database entities. The Project Wizard can generate an even greater number of relationships for OLAP databases because an OLAP database schema's hierarchical design gives clues to many of the relationships. You can also add new relationships that the wizard doesn't automatically generate. In addition, you can change or delete any relationship regardless of whether the wizard created it automatically or you created it manually.

An entity is almost always involved in at least one relationship. A phrasing describes a relationship between entities; because two entities can be related in more than one way, each set of entities might have several phrasings. You might prefer to model all the possible relationships with all possible phrasings, but that task might be too large. To limit the scope of the project, think about the most likely questions your intended audience might ask. Consider the ways that your users might ask these questions, then make a list of the questions. Your model should include the relationships and phrasings necessary to answer the target questions. Let's look at English Query's different phrasing types.

Name/ID Phrasing
Almost every entity has a name or ID. You use the name/ID phrasing to let English Query know which entity (column) contains the name or ID of the target entity. The Project Wizard will discover almost all of the relationships between entities and their names.

If you are going to use your own English Query model to follow along with this article, I recommend that you begin by creating a temporary directory on your hard drive. Then, copy all the files from the C:\Program Files\Microsoft English Query\Samples\Models\Northwind directory into your temporary directory. Now you can experiment with the Northwind model without affecting the original sample model.

Now, run English Query and open the Northwind model (northwind.eqp) in your temporary directory. In the Project Explorer window, open Northwind and double-click northwind.eqm. To see all entities and relationships, select the Semantics tab of the resulting window. To see the details of a relationship, double-click it to open the Relationship dialog box, select the phrasing of the same name, and click edit.

Here are some examples of name/ID relationship phrasings.

• "Employee names are the names of employees." This phrasing defines how the entity employee_name is related to the entity employee. In this case, the employee_name entity refers to the firstname and lastname columns of the Northwind database's Employees table.
• "Employee IDs are the IDs of employees." As with the employee name, this relationship tells English Query that the employee_ID entity is related to the employee entity.

These phrasings help English Query respond to the requests "List the employee names" and "Show the customer names." If you ask English Query the latter question and get the response I don't understand the word "customer" in the phrase "customer name," you're probably missing a name/ID phrasing that tells English Query that "customer names are the names of customers." To add a new relationship, you can right-click an entity involved in the relationship and select Add Relationship from the context-sensitive menu, or you can select Add Relationship from the Model menu item.

You can also ask about a specific entity—for example, "What do you know about Around the Horn?" (Around the Horn is a company name in the Northwind database's Customers table.) To enter a test question, select Start from the Debug menu to enter debug mode. Type your request into the Query window, and press Enter.

The Analysis tab in the same test dialog box, which Figure 1 shows, reveals that English Query uses the name/ID phrase to answer the question. The Analysis tab displays all the entities and relationships that English Query used to resolve your query.

Trait Phrasing
You use a trait phrasing to describe an entity's attributes. For example, you could tell English Query that:

• Employees have birthdates
• Employees have Social Security numbers
• Employees have phone numbers
• Employees have names

These trait phrasings let English Query successfully answer such requests for information as:

• "List the employees and their birthdates."
• "List the employees' phone numbers and birthdates."
• "Show Mary Smith's phone number."
• "What is the phone number of Mary Smith?"
• "What is John Jones' Social Security number?"
• "What is the Social Security number of John Jones?"
• "What Social Security number does John Jones have?"

   Prev. page   [1] 2 3 4     next page