Tutorial

This tutorial guides you through the major tasks of creating a Microsoft English Query application, using the sample database Pubs. To print the tutorial in its entirety, click Print or, from the File menu, click Print Topic.

Before you start   Make sure you have access to the Pubs database, and start English Query. (If you need help connecting to Pubs, ask your system administrator.) This tutorial takes about an hour to complete. If you want to stop the tutorial and come back to it later, you can save the project. For information about saving the project, see Saving the Pubs Project section later in this tutorial.

The Sample Database

The sample Pubs database is installed with Microsoft® SQL Server™. This database might be used for a large publishing concern that needs to track authors, titles, stores, and its smaller publishing groups. An English Query application based on this database could provide people with the ability to ask publishing-related questions, such as which employees work in which publishing groups, or which author has written a specific book.

An overview of the tables, fields, and joins in this database follows:

The Sample Database

The Semantic Objects

The following illustration shows some of the many entities and relationships (semantic objects) possible with the Pubs database. In this tutorial, you’ll be creating a few of these.

Entities and Relationships (The highlighted entities are defined in this tutorial.)

Considering the Questions

Consider the questions that users of a Pubs English Query application might want to ask. In particular, look at questions that concern the entities author and book. Looking at a few potential questions will help you create the entities and relationships (expressed as phrasings) in the following steps.

As you may have guessed, there are many ways in which questions can be phrased by the user. A corresponding relationship phrase is needed for each type of question.

Extracting the Database Structure

First, you will retrieve the structure of the Pubs database and open it in the English Query domain editor. The structure of the database is part of the domain knowledge needed to build an English Query application, and it allows you to associate entities with tables and fields in the database.

    To extract the structure of the Pubs database

1. On the File menu, click New.
2. In the New Project dialog box, click Structure loaded from database, and then click OK.
3. In the Select Data Source dialog box, select the Pubs database (the correct data source may be listed on either of the tabs), and then click OK.

(You may need to create a new ODBC data source first. Click Help for information about creating a new data source.)

4. Log in with your name and password.

For example, both Login ID and Password might accept the entry guest. (Contact your system administrator if you need help logging on.) Click OK.

5. On the Database Objects tab, click Tables, and then click the specific tables, fields, and joins to see the structure of the Pubs database.



Notice that table names are preceded by a designator for the owner of the table (in this case, dbo for database owner). In this tutorial, table names are referred to without the owner designation, for example, authors.

Preliminary Tasks

To make sure the project works properly, you must:

• Assign keys to two tables in the Pubs database that are missing them.

Keys are fields used to identify records.

    To assign keys

1. On the Database tab, double-click the table roysched to display the Table Properties dialog box.
2. In the Keys box, enter these fields (separated by commas): title_id, lorange, hirange.

You can also select these fields from a list by clicking Specify Keys.

3. Click Apply.
4. Repeat steps 1-3, substituting discounts as the table and stor_id, lowqty, highqty as the keys.

Saving the Pubs Project

You can save your version of the Pubs project as you work, especially if you need to delay completion of the tutorial. Because Pubs.eqp is shipped as a sample file, you’ll want to give your version a unique name: for example, Mypubs.eqp.

    To save the project

1. On the File menu, click Save Project As.
2. In File name, type a unique name for this project, such as Mypubs.eqp.
3. Click Save.

The Methodology

In this tutorial, you will define two entities and then create the relationship between them. Then you'll move on to defining subsequent entities and their relationships. In the future, you may want to define all the entities of your own application and then go back to define all the relationships. However, the first method is recommended for learning how to use English Query.

Defining the Book and Title Entities and a Relationship

First you’re going to define the major entity book, followed by the creation of the entity title, and then a relationship between book and title.

Creating the Book Entity

You define the book entity to answer general questions about the titles in the Pubs database, such as, “How many books are there?” or, “List the books.” The book entity is represented by the entire Titles table.

    To create the book entity

1. On the Insert menu, click Entity.



2. In the Words/Phrases identifying entity box, type the name of the entity, book, followed by a comma and synonyms for book, such as publication, tome, work, and so forth. (Consider adding your own synonyms for book. These synonyms should be those commonly used by you or potential users of this application.)
3. In the Entity type box, select None.
4. In the Table box, select titles.
5. Click Entire table is associated with this entity.

This option indicates that each row in the Titles table uniquely identifies a book. For example, you want to allow a question such as, “Count the books.” Associating the book entity with the entire Titles table, rather than just the title field, ensures that the count is correct. Because all fields are used in the count to uniquely identify the book, two books that happen to have the same title are counted individually.

6. In Display fields box, enter title and pubdate. You enter these so that the titles and publication dates display whenever general questions about books, such as  “List the books,” are asked. Click OK.
7. Click Apply.

Creating the Title Entity and Relationship with Book

The title entity is a minor entity. You define a title entity to answer specific questions about the books in the Pubs database, such as, “What book starts with the word Net?” The title entity is represented by the Title field in the Titles table.

    To create the title entity

1. On the Insert menu, click Entity.
2. In the Words/phrases identifying entity box, enter the name of the entity, title, followed by a comma and synonyms for title, such as book name.
3. In the Entity type box, select None.

Because the title entity is represented by a field in the Titles table, you now must specify some database properties as well.

4. In the Table box, select titles.
5. In the Fields box, select  title.
6. In the Name type box, select Proper name.
7. To ensure that the English Query dictionary can answer questions about particular book titles, click Add values of this entity to the domain, and then click Apply.

    To create the relationship titles are names of books

1. On the Insert menu, click Relationship.
2. In the Relationship dialog box, click Add Entity.
3. In the Select Entities box, select book, and click OK.
4. Repeat steps 2-3 for the title entity.
5. Click the Phrasings tab, and then click Add.
6. Double-click Name/ID Phrasing.
7. In the Entity that is Name/ID box, select titles, and in the Entity being named box, select books. Click OK.
8. On the Database tab, click Calculate Default Join Table, and then click Apply. (If you don’t do this step, English Query will calculate it for you. Steps that follow assume English Query will detect the correct default join table.)

Creating Author Entities and Relationships

Next you’ll create the major entity author, followed by its trait entities, which include author_name, author_address, and author_phone. You’ll define the relationships between these entities and author as you create each of them.

Each relationship, separately and used together with others, tells English Query how to answer the following types of questions:

Creating the Author Entity

The author entity is a major entity. You define an author entity to answer general questions about the authors in the Pubs database, such as, “How many authors are there?” or “List the authors.” The author entity is represented by the entire Authors table.

    To create the author entity

1. On the Insert menu, click Entity.
2. In the Words/phrases identifying entity box, type the main name of the entity, author, followed by a comma and synonyms for author, such as writer. (Consider adding your own synonyms for author, such as novelist or reporter. These synonyms should be those commonly used by you or potential users of this application.)
3. In the Entity type box, select Person.
4. In the Table box, select authors.
5. Click Entire table is associated with this entity.

This option indicates that each row in the Authors table uniquely identifies an author. For example, you want to allow a request such as “Count the authors.” Associating the author entity with the entire Authors table, rather than just the author name fields, ensures that the count is correct. Because all fields are used in the count to uniquely identify the author, two authors that happen to have the same name are counted individually.

6. In the Display Fields box, type or select au_fname, au_lname, and phone, and click OK.

You enter these fields so that the authors’ names and phone numbers will display whenever general questions about authors are asked. For the questions, “What authors live in Ann Arbor?” and “List all of the authors,” you see answers that include the authors’ names and phone numbers.

7. Click Apply.

Creating the Author_Name Entity and Relationship

You’re going to create an author_name entity so that users of this application can ask questions about specific authors. For example, “What do you know about Charlene Locksley?” Creating an author_name entity and its relationship with author tells English Query that proper nouns (which are initial-capped) correspond to values stored in the Authors table.

    To create the author_name entity and relationship using Autoname

1. On the Semantics tab, double-click the entity author to display the Entity properties dialog box.
2. Click the Autoname button to display the Autoname dialog box, notice the default values provided for Name Type and Name Fields, and click Accept.

To see the objects created using Autoname, look on the Semantic Objects tab. The author name minor entity appears under the author entity. The author_names_are_the_names_of_authors relationship appears under Relationships.

Now create the author_address entity and the relationship between it and author: authors have addresses. This phrasing makes possible questions about author addresses, such as, “List all the authors and their addresses.”

Creating the Author_Address Entity and Relationship

You’re going to create the author_address entity so that users can ask questions such as, “What is Albert Ringer’s address?”

    To create the author_address entity

1. On the Insert menu, click Entity.
2. In the Words/phrases identifying entity box, type the main word to identify the entity, author address, followed by comma and synonyms for author address, such as address.
3. In the Entity type box, select Geographical Location.
4. In the Table box, select authors.
5. In the Fields box, select address, city, state, and zip. Click OK.
6. Click Apply.

    To create the relationship authors have addresses

1. On the Insert menu, click Relationship.
2. Click Add Entity.
3. In the Select Entities box, select author, and then click OK.
4. Repeat steps 2-3 for the author_address entity.
5. Click the Phrasings tab, and click Add.
6. Double-click Trait Phrasing.
7. In the Subject box, select authors; in the Object box, select author_addresses. Click OK.
8. Click the Time/Location tab to see the option that was selected.
9. Click Apply.

Creating the Author_Phone Entity

When used with the author entity in a relationship, the author_phone entity allows English Query to answer questions about an author’s phone number, such as, “List the authors and their phone numbers.” Along with the relationship between author_name and author, the author_phone entity and its relationship with author also answers questions about specific authors, such as, “What is Anne Ringer’s phone number?”

    To create the author_phone entity

1. On the Insert menu, click Entity.
2. In the Words/phrases identifying entity box, type the name of the entity, author phone, followed by synonyms for author phone, such as phone number and telephone number (separated by commas).
3. In the Entity type box, select None.
4. In the Table box, select authors.
5. In the Fields box, select phone.

The data from these fields will now be used to answer questions about authors’ phone numbers.

6. Click Apply.

    To create the relationship authors have author_phones

1. On the Insert menu, click Relationship.
2. Click Add Entity.
3. In the Select Entities box, select author, and then click OK.
4. Repeat steps 2-3 for the author_phone entity.
5. Click the Phrasings tab, and click Add.
6. Double-click Trait Phrasing.
7. In the Subject box, enter authors; in the Object box, enter author_phones. Click OK and then Apply.

Creating the Store Entity and Relationship with Book

You can define a store entity to answer specific questions about what books the book stores buy. The store entity is represented by the Stores table.

    To create the store entity

1. On the Insert menu, click Entity.
2. In the Words/phrases identifying entity box, enter the name of the entity, store, followed by a comma and synonyms for store, such as outlet, and receiver.
3. In the Entity type box, select None.
4. Click Entire table is associated with this entity.
5. In the Table box, select stores.
6. In the Display Fields box, select stor_name.
7. Click Apply.

    To create the relationship stores buy books

1. On the Insert menu, click Relationship.
2. In the Relationship dialog box, click Add Entity.
3. In the Select Entities box, select store,and click OK.
4. Repeat steps 2-3 for the book entity.
5. Click the Phrasings tab, and then click Add.
6. Double-click Verb Phrasing.
7. In the Sentence type box, select Subject Verb Object.
8. In the Subject box, select stores, in the Verb box, type buy, and in the Object box, select books.
9. Click OK.

Note   It’s a good idea to save your project now. For information, see Saving the Pubs Project, earlier in this topic.

Testing the Author Questions

It’s important to test the preceding questions to make sure the new relationships between entities work as expected. You may have to log into the Pubs database again.

    To test the Author Questions

1. On the Tools menu, click Test Application.



2. In the Question box, type one of the previous questions, such as “What are the names of all the authors?” and then click Submit.

The question is restated in the Restatement box.

To see the returned answers, click Execute SQL.

To see the phrasings used to answer this question, click Analysis.

Using this process, try out these other test questions:

“What is Bennet’s first name?”

“List all the phone numbers.”

“What is Sheryl Hunter’s phone number?”

“Where do the authors live?”

“What is Michael O'Leary address?”

3. Try asking related questions of your own, such as, “How many authors are there?” or, “Which authors live in Ann Arbor?”

Defining a Relationship Between the Author and Book Entities

Defining a relationship between the major entity author and the major entity book tells English Query how to answer the following types of questions:

When creating relationships with verb phrases, phrase them in active voice, such as, authors write books, instead of the passive voice, such as, books are written by authors. When you specify relationships in active voice, users can ask questions in either active or passive voice (in other words, you get passive voice automatically).

The following steps help you define the phrasings needed to answer these questions, such as authors write books (which also covers the passive, books are written by authors) and a simpler prepositional phrasing, books are by authors.

    To create the relationship authors write books

1. On the Insert menu, click Relationship.
2. Click Add Entity.
3. In the Select Entities box, enter author, and then click OK.
4. Repeat steps 2-3 for the book entity.
5. Click the Phrasings tab, and click Add.
6. Double-click Verb Phrasing.
7. In the Sentence type box, select Subject Verb Object.
8. In the Subject box, select authors.
9. In the Verb box, type write.
10. In the Direct object box, select books. Click OK.

Note   When creating relationship phrasings, entities are shown in the plural: authors and books. This so you will naturally type the base form, or root, of the verb—write, in this case (authors write books), rather than the writes as in the incorrect authors writes books.

Now create another phrasing to express this relationship: books are by authors.

1.  On the Phrasings tab, click Add.
2. Double-click Preposition Phrasing.
3. In the Subject box, select books.
4. In the Prepositions box, type by.
5. In the Object box, select authors. Click OK and then Apply.

Testing Questions

Now you're going to test questions to make sure the new entities and relationships return the answers you expect.

    To test author writes books questions

1. On the Tools menu, click Test Application, and click Execute SQL.
2. In the Question box, type one of the previous questions, such as, “What authors wrote what books?”; then click Submit.

The question is restated in the Restatement box.

To see the returned answers, click Execute SQL.

To see the phrasings used to answer this question, click Analysis.

Using this process, try out the other questions such as:

“List the books by Anne Ringer.”

“What books are written by Anne Ringer?”

“How many books did Marjorie Green write?”

“Did Michael O’Leary write a book?”

Try asking related questions such as, “List the authors and books.” Notice that there are two answers: a list of authors and a list of books, but no association between them. To get the right association, you’d have to ask a slightly different question, one that uses a relationship between authors and books, such as, “List the authors and their books” (authors write books phrasing), or “List the books written by authors” (books are written by authors phrasing).

Testing Stores Buy Books Questions

Here you use the Suggestion feature to help you test questions.

    To test a question about stores

1. Type the question, "What books did Barnum's sell?"
2. Look at the Analysis tab. English Query couldn't answer the question because it can't decipher to what entity Barnum's refers).
3. Click Suggestion.
4. In the Entity Clarification dialog box, click store. Click OK and then Apply.
5. Submit the question again: "What books did Barnum's sell?"

The answer is now provided.

Building the MyPubs English Query Application

When you build the Pubs English Query application, the file created for this application uses the name of the project: in this case, Mypubs.eqd.

    To build the Mypubs application

1. On the Tools menu, click Build Application.

A message warns you that build time depends on the number of data values loaded into the English Query dictionary. In this case, the build time will be brief.

2. Click OK.

When the build has completed, a confirmation box shows the path for the application. Its name is Mypubs.eqd.

Deploying Mypubs.eqd on an IIS Server

You can deploy English Query applications in several ways: by deploying on the Web and by embedding English Query functionality in a Microsoft Visual Basic® or Microsoft Visual C++® application.

If you’re running Internet Information Server 4.0 and IIS with the Windows Scripting Host installed, the simple way to deploy a Web page that allows users to pose questions to your English Query domain is to run Setupasp.vbs. It is in the \Program Files\Microsoft English Query\Samples\Asp2 directory.

Setupasp.vbs is a Windows Scripting Host (WSH) script  that copies the ASP files and the domain file (.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, which indicates to the ASP script where to find your database. Then you can test your application and link to the page from other Web pages on your site, including other ASP applications.

For more information on embedding an English Query applications in your own ASP applications, see Overview of Deploying on the Web.