Lesson 2: Creating an English Query Application | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

When creating an English Query application, you will perform the following steps:

Create or import database schema

Define entities

Create relationships between entities

Test the application

Build the application

After this lesson, you will be able to:

Create an English Query application

Test an English Query application

Build an English Query application

Estimated lesson time: 90 minutes

Creating or Importing Database Schema

When creating an English Query application, the first step is to identify the database schema. You can either create each table individually or import the information from an Open Database Connectivity (ODBC) data source.

Using the Domain Editor

You use the Domain Editor to create a new English Query project. The Domain Editor provides two options for starting an application. You may select either of the following options:

Create an empty project

Import the schema information from an existing data source

It is simpler to import schema information from a database. After you import the schema, you can fine-tune it to fit your needs.

Identifying Tables and Fields

Whether you create an empty project or import schema information, you must provide a table name and at least one field. If you import the database schema, expand the Tables folder and inspect the tables and fields that were created.

Declaring Keys

Each table in the Domain Editor must have a declared key. If the data source does not have keys declared, you must specify them in the Domain Editor or your application will not compile.

NOTE
Changing schema information in the Domain Editor does not affect the underlying data source.

Creating Joins

Joins relate information in each table to the other tables. If your data source has declared foreign keys set up, the joins will be created for you when you import the data. If a join is not defined between tables, you will not be able to set up relationships based on those tables.

Defining Entities

After you set up the schema information, you must identify the entities. Entities are real items (nouns) such as Authors, Books, and Cities.

Identifying Major and Minor Entities

Major entities are broad categories of entities. Each major entity is represented by a table in the database. For example, the authors table is a collection of Author entities. Minor entities are subsets or traits of major entities. One or more minor entities may be collected in a single table.

Defining the Entity

When defining the entities, you should do the following:

Provide words or phrases that identify the entity that you are creating.

List the primary word or phrase first. For example, the word customer is the primary word for identifying the customer entity.

Include several alternate words (synonyms) that a user may use in order to prevent English Query from having to clarify a word. For example, add the words client, patron, and shopper as alternate words for the customer entity. Consider using a thesaurus to look up alternate words.

NOTE
When defining entities, always use lower case. If users will ever refer to an entity using proper case, you must add the proper case option as a synonym. For example, users can refer to an entity called author using authors, AUTHORS, or even AuThOrS, but if they will ever use Authors, you must add Author as a synonym for the author entity because it is treated as a different and unrelated word.

Provide an entity type to help English Query determine the entities involved in a phrase. For example, a customer entity is a Person entity and a product entity is a Physical Object.

Select whether this entity is associated with a database object. For example, major entities will usually be associated with an entire table.

Specifying Names and Autoname

Major entities are usually represented by whole tables but can be represented by a few columns. This is more common when working with databases that have been denormalized.

Display fields refer to information that is returned to a user about the entity in a typical query. For example, a user asking for authors who wrote certain books will typically get the last name and first name of the author as a response.

Names or IDs allow you to specify how the data should be treated, such as which field contains a first name and which field contains a last name and how a first name and last name are combined to make a proper name. Names are minor entities that are related to major entities by name relationships. English Query provides the Autoname option, which automatically creates name relationships for you. (We work with this option in the next exercise.)

Relating Traits and Autotraits

Minor entities are generally subsets or extended information about major entities. These attributes are known as traits. When you relate traits to major entities, you enable users to query for extended information, such as Authors who live in a particular city.

Similar to the Autoname option, the Autotrait option automatically creates minor entities for each of the remaining fields in the major entity table and relates the traits to their respective major entity.

Creating Relationships between Entities

After you define each entity, you must define the relationships between the entities.

Phrasing Relationships

Several types of phrases in the English language can show relationships. Some examples are

Name and ID phrasings (au_id is an Author s ID)

Trait phrasings (Authors have addresses)

Pre-position phrasings (Cities are in States)

Adjective phrasing (Some books are good)

Subset phrasing (Some books are about cooking)

Verb phrasings (Authors write books)

Each of these types can be configured for your entities. The Autoname and Autotrait tools create several Name/ID phrasings and Trait phrasings for you.

Specifying Database Options

You can specify joins on the Database tab in the Domain Editor. Doing so will help English Query determine the correct Transact-SQL statement to use when relating tables, such as in complex join questions.

If the relationship between the entities applies only when certain conditions are met, specify a statement that signifies the condition. Phrase the statement as though it were in the WHERE clause of a Transact-SQL statement.

Specifying Time and Location Options

If a relationship involves specific times for the relationship to be true (for example, authors receive royalties after the publish date) or if the condition requires certain location criteria (for example, people buy products in stores), select the fields that identify the criteria on the Time/Location tab when editing the relationship.

Testing the Application

After you have provided schema, entity, and relationship information, you can test the application.

Testing Sample Statements

When testing the application, you can submit sample statements to determine how English Query interprets the statement. You can test the following:

An English sentence

English Query will show a rephrasing of the sentence in English and the associated Transact-SQL statement that it will generate.

The Transact-SQL statement

In order to test the Transact-SQL statement, you must provide an ODBC data source name (DSN).

Saving Question Files

Once you have tested a few sentences, you can save them in a question file. This is an ASCII file that allows you to review questions that have been submitted. The file can be used again in the regression test.

Performing Regression Tests

The Regression Test tool allows you to provide a list of questions to be submitted in rapid format to English Query for processing:

The input file is a simple ASCII text file with a single question on each line.

The output is saved to a text file for review.

You can run regression tests after each change and review the outputs to verify that existing functionality does not get broken while improvements are being made.

Building the Application

Once you are satisfied with the results of your testing, it is time to build the application.

Create the Domain File

Building an application starts by defining entities and relationships. This can take a significant amount of time, depending on the number of entities and relationships that you have set up. We step through a simple example of the process in detail during the next exercise.

Distribute the Domain File

Once the entities and relationships are defined, they are compiled into an .EQD file that can be used with the COM-server component to add natural-language ability to one or more applications.

Exercise 1: Setting Up a DSN

In this exercise, you will create an open database connectivity (ODBC) data source name (DSN) for the Northwind_Mart database.

To set up an ODBC DSN for the Northwind_Mart database

On the Start menu, point to Settings, and then click Control Panel. In the Control Panel, double-click ODBC Data Sources.

Select the System DSN tab, and then click the Add button.

Select the SQL Server driver, and then click the Finish button.

Type in Northwind_Mart for the Name and select (local) in the Server drop-down list.

Click the Next button.

Select SQL Server authentication using a login ID and password entered by the user for how SQL Server should verify the authenticity of the login.

Type in sa for the Login ID. If you have assigned a password to sa on your SQL Server then type it in Password, otherwise leave Password blank.

Click the Next button.

Change the default database to Northwind_Mart.

Click the Next button, and then click the Finish button.

Click the Test Data Source button to verify your setup. If the test fails, go back and verify that you have entered the information in steps 4 9 correctly.

Click the OK button twice to close the ODBC dialog boxes and save your new ODBC DSN.

Exercise 2: Creating an English Query Project

In this exercise, you will import the data structure of an existing database into the Domain Editor. You will then add the entities and relationships that define your domain and compile your project into an .EQD file.

To import the data structure

On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, point to English Query, and then choose Microsoft English Query.

Select Structure Loaded from Database, and then click the OK button.

Select the Machine Data Source tab, select the Northwind_Mart data source, and then click OK.

Click OK again to log on to SQL Server.

English Query connects to the data source and enumerates each of the tables from the target database.

Expand the Tables folder, and view the tables that have been imported.

To create entities in your domain

Select the Semantics tab.

Right-click the Entities folder, and select Insert Entity. The New Entity dialog box is displayed.

In the Words/phrases identifying entity text box, type customer.

Change the Entity type to Person.

Check the check box that is labeled Entire table is associated with this entity. This creates a major entity.

Select the dbo.Customer_Dim table in the Table drop down list.

Next to the Display fields text box, click the ellipsis ( ) button. Select the CompanyName and ContactName fields, click the > button. CompanyName and ContactName must both appear in the Selected list. Click OK.

Click the Autotrait button. You will be prompted to save your changes. Click OK.

The next dialog box allows you to create minor entities associated with the major entity that you are currently creating. Click the Accept All button.

Click the Autoname button, and then click the Accept button to accept the naming relationship for customers.

Click the Apply button.

Expand the Relationships folder and note the relationships that Autotrait and Autoname have created for you.

Create another entity for products. Type product for the Word/phrases identifying entity.

Change the Entity type to Physical Object.

Check the check box that is labeled Entire table is associated with this entity. This creates a major entity.

Select the dbo.Product_Dim table in the Table drop down list.

Next to the Display fields text box, click the ellipsis ( ) button. Select the ProductName field, click the > button and click OK.

Click the Autotrait button, save your changes, and then click the Accept All button.

Click the Autoname button, and then click the Accept button to accept the naming relationship for products.

Click the Apply button.

To create relationships

Right-click the Relationships folder, and select Insert Relationship.

Click the Add Entity button. Select both the customer and the product entities (hold down the CTRL key on the keyboard when you click the entities). Click OK.

Select the Phrasings tab, and then click the Add button.

Select Verb Phrasing, and then click the OK button.

Select Subject Verb Object in the Sentence type drop-down list.

Select customers in the Subject list, type buy in the Verb text box, and then select products in the Direct object list. Click the OK button.

Click the Apply button.

To save your project

On the File menu, select Save Project.

Select the C:\Northwind folder. Create it if it does not exist. Type in Northwind_Mart for the file name of your project.

Click the Save button to save your project as C:\Northwind\Northwind_Mart.EQP.

To test and build your application

On the Tools menu, select Test Application.

If you are warned that this process could take a significant amount of time, click OK.

Check the Execute SQL check box. This allows you to see the results of the query as well as the Transact-SQL statement generated.

To test a query, type How many customers are there? in the Question, and then click the Submit button. Notice the Transact-SQL statement that is generated by English Query for your natural-language query.

Type How many products are there? in the Question, and then click the Submit button.

To test the relationship that you set up, type How many customers have bought products? in the Question, and then click the Submit button.

To build the application, close the test window. On the Tools menu, select Build Application.

If you are warned that building the application may take a long time, click OK.

Your application is built and the domain file is saved to C:\Northwind\Northwind_Mart.EQD.

Lesson Summary

In this lesson, you learned the five main steps in creating an English Query domain knowledge. They were the following:

Importing or defining the database structure

Defining the entities

Defining the relationships

Testing the application

Building the application