Introduction

So you have learned Active Server Pages (ASPs) and want to build a dynamic Web site or application. You have gone beyond that to master Microsoft® ActiveX® Data Objects (ADO) and to drive your Web content from Microsoft SQL Server™ or another database server. After you have made your Web site database-driven, you will probably want to add a way for users to search and report on your database content.

It is easy enough to build a query form that allows users to search based on one or two fields. It is much harder to build form-based Web pages to allow searches across multiple tables and multiple fields. Not only is a more flexible search 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), but even the best interface will be difficult for the casual Web visitor to use and understand.

There is an inevitable learning curve for any complex search of structured data that you want to make available. This obstacle is unacceptable for the spontaneous usage of your Web site or application. Fortunately, there is a powerful, flexible search-specification mechanism that most of your users will understand: the English language.

Since the introduction of Microsoft English Query version 1.0 with Microsoft SQL Server version 6.5, Enterprise Edition, such a capability is no longer science fiction. English Query allows users to ask questions about your data in plain English in your existing database-driven Web sites and applications. Developing your initial natural-language search is easy, a small fraction of the effort of building your overall application. The development process is more conceptual than traditional programming, and it can be mastered by nonprogrammers who have some database background (such as a database administrator or Web-content developer who works 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 question to a SQL statement. Sample ASPs are provided that you can embed in your overall Web site and that drive the engine, prompting a user for questions, executing the engine’s returned SQL queries, and displaying the database’s results to the user.

This document will demonstrate how to author your English Query domain and how to embed the English Query engine and authored domain into 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 into a Web site built with ASP scripts.

The following illustration shows a Web page using the sample ASP scripts that come with English Query. In this sample, the user enters a question or clicks Sample questions… for some predefined questions that show the kind of information available. Pressing ENTER submits the question to the English Query engine, which generates a 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 need to 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

· On the Database tab, on the File menu, 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.

The following illustration shows the database structure of the sample SQL Server pubs database.

If any tables in the database are missing primary keys, then you will want to edit them and supply one or more fields as the primary key. It is not necessary that the underlying database has 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 will need to add the joins manually inside the authoring tool.

Creating Entities

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

Þ 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.

For the entity “author” in the pubs database, this might look like the following illustration.

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 create an entity that represents the name of the author entity, associated with the first and last name fields. You will want to create such name entities for major entities that are represented by entire tables so that the user has some way of identifying 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 will create entities for all fields in the database.

Creating Relationships

By establishing traits for your major entities, you start your model with quite a few 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 France?” But to ask the really interesting questions, you will need to 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 types of questions. The entities associated with the relationship “authors write books” might appear as shown in the following illustration.

Phrasing

Next, you will 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”) will have been created by Autotrait and Autoname.

The following 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 answer some types of 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 that you want the user to be able to ask. For example, if you want the model to support questions about authors being 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, at this point, with only the “authors write books” relationship entered, English Query should be able to 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. Click Submit.

The generated SQL query will be displayed. If you select Execute SQL, English Query will submit the query to SQL Server and display the answer.

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

Another Tools menu item called Regression Test will execute all queries in the question file and generate an output file. If you copy the output file to a reference file, then Regression Test will allow you to compare the results of the most recent output against the reference file. This is useful to ensure 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 to the user on a Web page to give them an idea of what information is available.

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, Internet Information Services (IIS) version 4.0 and installing from your IIS computer, deploy a Web page that allows users to ask questions to your English Query domain by clicking the Setupasp.vbs file. This is a Windows® Scripting 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 tell the ASP scripts where your database is.

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

Embedding into Your Own ASP Applications

You can go further and use the sample application to point the way toward 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. A little bit of explanation of the structure of this sample application should make this easier for you.

The following 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 would embed code such as this into 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

Case nlUserClarifyResponse
DoClarification objEQResponse, strQuestion
Case nlErrorResponse
Response.Write objEQResponse.Description & "<BR>"
End Select

First, 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 will be 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 will usually 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 just 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 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.

Conclusion

Microsoft English Query provides a powerful searching capability for your SQL Server-based Web site or application. It is easy to implement, especially in an ASP-based application. Adding English Query to your Web site allows you to move away from writing custom reports or complex searching forms.