Adam Blum
Microsoft Corporation
February 1998
So you learned Active Server Pages (ASP) to build yourself a dynamic Web site or application. You went beyond that to master Active Data Objects (ADO) and drive your Web content from Microsoft® SQL Server™ or another database server. Once you make your Web site database-driven, you'll probably want to add some way for users to search and report on your database content. Of course, it's easy enough to add a query form that lets users search based on one or two fields. It's much harder to build form-based Web pages to allow searches across multiple tables and multiple fields. Not only is this more flexible search difficult to implement (there are many problems beyond the mundane UI and Web-coding aspects, including defining how the various tables and fields are related to each other), but, even with the best interface, it will be difficult for your casual Web visitors to use and understand. There's 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 that you want to solicit and encourage. Fortunately, there is a powerful, flexible search specification mechanism that all your users are likely understand—the English language.
Since the introduction of Microsoft English Query 1.0 with SQL Server 6.5 Enterprise Edition last year, such a capability is no longer the stuff of science fiction. English Query allows users to ask questions about your data in plain English in your existing database-driven Web sites and applications. Putting up your initial natural language search is very easy—a small fraction of the effort of building your overall application.
The development process is higher level than traditional programming and can be mastered by nonprogrammers with some database background (such as a DBA or Web content developer who often works with databases). English Query ships with an engine (a COM server) used at run time to convert a user's English question to a SQL statement. We even provide sample ASP pages (which you can embed in your overall Web site) that drive the engine, prompting a user for English questions, executing the engine's returned SQL queries, and displaying the database's results to the user. In this article, we'll show you some of the details of how to author your English Query domain and how to embed the English Query engine and authored domain into your own Web site.
English Query can be embedded into any application that supports COM, but certainly a common scenario is to embed it into a Web site built with ASP scripts. Figure 1 shows a Web page put up using the sample ASP scripts that come with English Query.
Figure 1. Sample English Query Web page
In the sample application shown in Figure 1 the user enters a question or clicks the Sample Questions button for some predefined questions that show them what kinds of information are available. Clicking Enter submits the question to the English Query engine, which generates a SQL statement that is submitted to SQL Server via ADO. The returned recordset is then displayed in the lower frame. Let's look at some of the details of how you'd get such an application built.
The first step to building an English Query application is to model the semantics of your problem domain. You need to tell it how English language entities (nouns) and relationships (verbs, adjectives, traits, and subsets) map to tables, fields, and joins in your database. To do this you'll use the English Query authoring tool (which appears in the Microsoft English Query program group after installation). Choose New Applications from the File menu, and then select Structure loaded from database to initialize the database structure from your SQL Server schema, filling the database tab with tables and fields. Figure 2 shows the database structure of the SQL Server PUBS database sample.
If any of the tables in the database are missing primary keys, you'll want to edit them and supply one or more fields as the primary key. It's not necessary that the underlying database actually has a primary key, but all tables must have primary keys identified in the authoring tool for your application to build.
Figure 2. Creating the database structure
If tables will be related to each other in queries, then there should be joins indicated between the necessary tables. These are usually retrieved from the foreign keys defined in your database, but if the necessary foreign keys aren't present (usually they are there to force referential integrity) then you'll need to add the joins manually inside the authoring tool.
Now you're ready to start adding semantic entities. Right click on the Entities branch of the Semantic Objects tree to insert an entity. Supply some words to describe the entity (for example, "author," "writer")—the primary entity name should be first. Specify a type if it's a person, place, or time. Now identify what part of the database, the specific table or field, represents the entity. Major entities will generally correspond to entire tables. If it is a major entity, supply the fields that should be used to display the entity. This might be first and last name for a person, or name and address for a company. For the entity "author" in the PUBS database this might look like Figure 3.
Figure 3. Defining an entity
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. Clicking on the Autoname button for the author entity will create an entity that represents the name of the author entity, represented by the first and last name fields. You'll 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 questions.
Clicking on Autotrait allows you to create traits for the entity—minor entities that the major entity has. Clicking Accept All creates minor entities for all of the semantically meaningful fields as well as trait relationships between the major entity and the newly created minor entities.
Figure 4. Using Autotrait
You continue to create more entities (along with names and traits) for all the major "nouns" that users could ask questions about. In the PUBS examples, this would be books and publishers.
Establishing traits for your major entities starts your model out with quite a few relationships. At this point, you can ask questions about things having traits. For example, "What authors have city Seattle?", "Show the authors and their cities.", "What book has the title The Busy Executive's Database Guide?", and "What publishers have country France?" But to ask the really interesting questions that can be posed about your database, you'll need to create relationships between major entities, such as "authors write books" and "publishers publish books." Right clicking on the Relationships branch on the Semantic Objects tree and clicking Insert Relationship will present you with the relationship definition dialog box. Click Add Entity and select the entity from the drop-down box for 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 Figure 5.
Figure 5. Defining a relationship
Next you'll create phrasings for that relationship which can be verb phrasings (such as "authors write books"), preposition phrasings ("publishers are in cities"), adjective phrasings ("book are popular"), or subset phrasings ("some books are bestsellers"). Most trait phrasings ("book have royalties") and name phrasings ("author names are the names of authors") are created by Autotrait and Autoname as we've just shown. Figure 6 shows a verb phrasing defined for "authors write books."
Figure 6. Adding a phrasing
If a relationship occurs at a specific time or place, it's helpful to supply the date or location entity on the Time/Location tab to answer some types of user questions. In this example, you'd supply PUBDATE as the entity identifying when the relationship occurs.
You'll 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, you'd go back to the "authors have cities" relationship and supply a new preposition phrasing that says, "Authors are in cities."
See the "Creating an Application" section of the Microsoft English Query Developer's Guide (an icon in the Microsoft English Query program group) for further details of the entire authoring process.
At any time during your development process you can test what you've modeled so far 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 Sushi, Anyone?", and "What books did Anne Ringer write?" Figure 7 shows the Test Application dialog box. You'll enter the query as an English sentence in the question field and click Submit. The generated SQL query will be displayed below, and, if you have Execute SQL checked, it will submit the query to SQL Server and display the answer. If you think this question is useful as a sample or for continued testing, click Add to Question File.
Another Tools menu item called Regression Test will execute all queries in the Question File and compare the output against a reference file (usually generated the first time you run Regression). This is a good way to make sure you haven't "broken your model," as it becomes more and more complex over time. The Question File questions can also be used as a set of sample questions that can be displayed to the user on the Web page to give them an idea of what kinds of information are available.
Figure 7. Testing your application
Once you've developed and tested the model to your satisfaction inside the English Query authoring tool, you're ready to build the application. To build the application, select Build Application from the Tools menu to create the English Query domain (.EQD) file. The .EQD file and the English Query engine (the COM server "MSEQ.Session") can be deployed inside of any COM-supporting application.
We supply an example framework for doing so in the SAMPLES/ASP2 subdirectory under the Microsoft English Query directory. If you're running Microsoft Internet Information Server (IIS) version 4.0 and doing the install from your IIS machine, the simple way to deploy a Web page that allows users to ask questions to your English Query domain is to click on the file SETUPASP.VBS. This is a Windows Scripting Host (WSH) script that copies the ASP files and your created .EQD to a directory on the Web server, creates an IIS virtual directory for the English Query pages, and sets options in a file called PARAMS.INC to tell the ASP scripts where your database is. If you're running IIS 3.0, then just perform these steps manually (README.HTM documents the steps we just described). Then you're ready to test in your browser, by hitting the page http://localhost/pubs, for example. You can then link to this page from other Web pages on your site, including other 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 users' supplied questions into SQL. You would embed code like this into the ASP page that processed the user's query.
' Create the English Query object.
Set objEQSession = Server.CreateObject("Mseq.Session")
' Load the domain, such as, "C:\PUBS\PUBS.EQD".
objEQSession.InitDomain(Application("DomainFile"))
' Convert user's question (strQuest) to English Query response object
Set objEQResponse = objEQSession.ParseRequest(strQuest)
' 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
First you'll create an English Query object with Server.CreateObject("Mseq.Session"). To load your domain you'll call the InitDomain method and the name of the .EQD file. You'll then get a response object back by calling the ParseRequest() method with the user's question. The response can be a command response, which is a set of commands that are either SQL commands or direct answers that English Query can supply without connecting to the database. The SQL command should be executed against your SQL Server database and you'll generally display the result as a table on your Web page. The process of executing the SQL command via 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's 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 Honda Civics in Washington?" and the clarification might ask whether Washington was a city or a state. The DoClarification call (also available in COMMON.INC) encapsulates the code necessary to prompt the user to disambiguate the question, by displaying possible values from the UserInputs collection on the response object.
English Query provides a powerful searching capability for your SQL Server–based Web site or application. It's easy to implement especially if you need to do so in an ASP-based application. Adding this to your site should let you move away from writing a lot of custom reports or complex searching forms.