Chapter 24 - Developing with English Query

Many database administrators have developed database-driven Web sites using Active Server Pages (ASP), Microsoft ActiveX Data Objects (ADO), and SQL Server or another database server only to find it difficult for their users to search the database and report on it.

It is easy enough to build a query form that allows users to search based on one or two fields, but it is much more difficult to build form-based Web pages that allow searches across multiple tables and multiple fields. A more flexible search is difficult to implement (there are many problems beyond the user-interface and Web-coding aspects, such as defining how the various tables and fields are related to each other) and even the best interface can be difficult for the casual Web visitor to use and understand.

Microsoft English Query, which was introduced in Microsoft SQL Server version 6.5, Enterprise Edition, and has been improved for SQL Server version 7.0, addresses this issue by providing users with the ability to use natural language when querying existing database-driven Web sites and applications.

Using English Query to develop an initial natural-language search is easy—it is only a small part of the effort of building your overall application—and the development process is more conceptual than traditional programming. In fact, the process can be mastered by nonprogrammers who have some database background, for example, database administrators and Web-content developers who work often with databases.

English Query ships with an engine, a component object model (COM) server, that is used at run time to convert a user's English language question to an SQL statement. English Query also provides sample ASPs that you can embed in a Web site to drive the engine, prompting a user for questions, executing the engine's returned SQL queries, and displaying query results to the user.

This chapter explains how to author an English Query domain and how to embed the domain and the English Query engine in your Web site.

Building with English Query

Microsoft English Query can be embedded into any application that supports COM. One common scenario is to embed it in a Web site built with ASP scripts.

A typical Web page can use the sample ASP scripts that come with English Query. For example, the user enters a question or clicks Sample questions… for predefined questions that show the kind of information available. The user clicks ENTER to submit the question to the English Query engine, which generates an SQL statement that is then submitted to SQL Server using ADO. The returned recordset is then displayed in the lower frame.

Authoring an English Query Domain

The first step to building a Microsoft English Query application is to model the semantics of your problem domain, that is, you must specify how English-language entities (nouns) and relationships (verbs, adjectives, traits, and subsets) map to tables, fields, and joins in your database. To do this, use the English Query authoring tool, which appears in the Microsoft English Query program group after installation.

To initialize your database structure

• From the File menu on the Database tab, click New, and then select Structure loaded from database. 

  This initializes the database structure from your SQL Server schema, filling the Database tab with tables and fields. 

This illustration shows the database structure of the sample SQL Server pubs database.

If any tables in the database are missing primary keys, edit them and supply one or more fields as the primary key. It is not necessary that the underlying database have a primary key, but all tables must have primary keys identified in the authoring tool for your application to build.

If tables will be related to each other in queries, then there should be joins indicated between those tables. The joins usually are retrieved from the foreign keys defined in your database. However, if the necessary foreign keys are not present (usually they are there to force referential integrity), then you should add the joins manually inside the authoring tool.

Creating Entities

Now you are ready to start adding semantic entities. This consists of defining the entities in your database and the tables or fields with which they are associated.

To create an entity

1. On the Semantics tab, right-click Entities, and then click Insert Entity…. 

2. In the New Entity dialog box, under Semantic Properties, in the Words/phrases identifying entity box, enter a description of the entity, for example, author or writer. In the Entity type box, enter a type. 

3. Under Database Properties, in the Table or Fields boxes, identify which part of the database represents the entity. 

   Major entities usually correspond to entire tables. If it is a major entity, then enter the fields that should be used to display the entity, such as name or address. 

This illustration shows this process for the entity "author" in the pubs database.

Major entities have two kinds of minor entities associated with them: names and traits. Names indicate how the entity is identified in questions and statements. By clicking Autoname for the author entity, you can create an entity that represents the name of the author entity, associated with the first and last name fields. You can create such name entities for major entities that are represented by entire tables so the user can identify the specific entity in question.

By clicking Autotrait, you can create traits for the entity: minor entities that the major entity has. Click Accept All to create minor entities for all of the semantically meaningful fields, as well as for trait relationships between the major entity and the newly created minor entities.

In general, you should create entities for all tables in the database. By using Autotrait, you* *can create entities for all fields in the database.

Creating Relationships

By establishing traits for the major entities, your model begins with several relationships. At this point, you can ask questions about things having traits, for example, "What authors have city Seattle?", "Show authors and their cities," "What book has the title The Busy Executive's Database Guide?", or "What publishers have country/region France?" But to ask the really interesting questions, you must create relationships between major entities, for example, "authors write books" and "publishers publish books."

To create a relationship between major entities

1. On the Semantics tab, right-click Relationships, and then click Insert Relationship…. 

2. In the New Relationship dialog box on the Entities tab, click Add Entity…. 

3. In the Select Entities list, click all entities that participate in the relationship. 

If the relationship occurs at a time or place, including the time or place entity helps English Query answer some questions. The entities associated with the relationship "authors write books" might appear as shown in this illustration.

Phrasing

Next, you can create phrasings for the relationship. Phrasing types include verb phrasings ("authors write books"), preposition phrasings ("publishers are in cities"), adjective phrasings ("books are popular"), and subset phrasings ("some books are bestsellers"). Most trait phrasings ("books have royalties") and name phrasings ("author names are the names of authors") are created by Autotrait and Autoname.

This illustration shows the Verb Phrasing dialog box defined for "authors write books."

To create phrasings for a relationship

1. On the Semantics tab, right-click Relationships, and then click Insert Relationships…. 

2. In the New Relationships dialog box, on the Phrasings tab, click Add…. 

3. In the Select Phrasing dialog box, select a phrasing type. 

Time and Location

If a relationship occurs at a specific time or place, then it is helpful to anticipate some user questions and to supply the date or location entity on the Time/Location tab of the New Relationship dialog box.

You can create relationships for every kind of question you want the user to ask. For example, if you want the model to support questions about authors in cities, return to the "authors have cities" relationship and supply a new preposition phrasing that says "authors are in cities."

Testing Your Model

At any time during the development process, you can test what you have modeled by invoking Test Application from the Tools menu. For example, with only the "authors write books" relationship entered, English Query can answer questions such as "Who wrote the most books?", "Who wrote Net Etiquette?", and "What books did Anne Ringer write?"

To test your application

1. On the Tools menu, click Test Application…. 

2. In the Test Application dialog box, in the Question box, enter your query as an English sentence, and then click Submit. 

3. If you select Execute SQL, English Query submits the query to SQL Server and displays the answer. 

4. If you think the question is useful as a sample, then click Add to Question File. 

Another Tools menu item called Regression Test executes all queries in the question file and generates an output file. If you copy the output file to a reference file, then Regression Test allows you to compare the results of the most recent output against the reference file. This ensures that English Query can handle all questions as you continue to enhance your English Query domain. The question file questions also can be used as a set of sample questions that can be displayed on a Web page to give users an idea of the available information.

Building and Deploying

After you have developed and tested the model to your satisfaction inside the English Query authoring tool, you are ready to build the application. Build Application on the Tools menu creates the English Query domain (.eqd) file. The .eqd file and the English Query engine (the COM object called Mseq.Session) can be deployed inside any COM-supporting application.

Adding English Query to Your Web Site

An example framework for adding English Query to your Web site is provided in the Samples/Asp2 subdirectory of the Microsoft English Query directory. If you are running Microsoft Windows NT Server's built-in Web server, Microsoft Internet Information Services (IIS) version 4.0, and installing from your IIS computer, you can deploy a Web page that allows users to query your English Query domain by choosing the Setupasp.vbs file. This is a Windows Script Host (WSH) script that copies the ASP files and your created .eqd file to a directory on the Web server, creates an IIS virtual directory for the English Query pages, and sets options in a Params.inc file to point ASP scripts to your database.

If you are running IIS 3.0, perform the previous steps manually (the Readme.htm file also documents the steps).

Using English Query in ASP Applications

You can also use the sample application to begin integrating English Query into other ASP applications. For example, you might choose to have an English Query text box available on your search page or to supplement existing reporting mechanisms.

This code fragment (a simplified version of the ASP sample code) shows the essence of how to convert the questions supplied by users into SQL. You embed code such as this in the ASP that processed the user's query.

' create the English Query object
Set objEQSession = Server.CreateObject("Mseq.Session")
' load the domain 
objEQSession.InitDomain ("c:\pubs\pubs.eqd")
' convert user's question to English Query response object
Set objEQResponse = objEQSession.ParseRequest(Request ("User Question"))
' determine what kind of response object it is
Select Case objEQResponse.Type
Case nlCommandResponse
Set objCommands = objEQResponse.Commands
For intCommand = 0 To objCommands.Count - 1
Set objCommand = objCommands(intCommand)
Select Case objCommand.CmdID
Case nlQueryCmd
' execute the returned SQL and display to the user 
DoSQLCommand objCommand
Case nlAnswerCmd
' just display the answer
Response.Write objCommand.Answer
End Select
Next
Case nlUserClarifyResponse
DoClarification objEQResponse, strQuestion
Case nlErrorResponse
Response.Write objEQResponse.Description & "<BR>"
End Select

Create an English Query object with Server.CreateObject ("Mseq.Session"). To load your domain, call the InitDomain method with the name of the .eqd file. Call ParseRequest() with the user's question and a Response object is returned. The Response object can be a CommandResponse, which is a collection of commands that are either SQL commands or direct answers. Each SQL command should be executed against your SQL Server database, and you can display the result as a table on the Web page. The process of executing the SQL command using ADO and displaying the result in a table is embedded in the DoSQLCommand function (available in Samples/Asp/Common.inc).

If the command is an answer, it is displayed directly to the user. The response might also be a request for clarification. For example, the question might be "What are all the compact cars in Washington?" and the clarification question might ask whether Washington is a city or a state. The DoClarification call (also available in Common.inc) encapsulates the code necessary to prompt the user to choose from possible values in the UserInputs * *collection on the Response object.