Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 246 of 361 Next >

English Query - Part 2

Last updated Mar 28, 2003.

I'm continuing my previous tutorial on using Natural Language Processing (NLP) technology in SQL Server, specifically in the product called English Query. In the first tutorial I introduced the product and in the second I explained how to install the product, as well as what the data structure we're looking at. You'll also recall that I framed the questions we'll answer in a sample application we're developing, using the pubs database that comes with SQL Server. This database deals with the publishing industry and contains information about authors, the books they wrote, and the sales information including store data. Here are the questions we're working on:

Who are the authors?
How many books has author (Name) written?
What books have they written?
Who sold the most books?

In the last tutorial I also categorized the questions, and this week I'll explain why. Here are the categories I came up with:

Question	Category
Who are the authors, what books have they written	General information
How many books has author (Name) written	Numerical operations
Who sold the most books	Action

These categories begin to help us break down the types of queries the users will ask in the application. An important part of working with an NLP system is to think not in terms of database theory, but in the way people think and talk. As we put together the application these categories have direct impact on how we will create various parts of the application, so I'll bring these two lists up throughout the series.

We're almost ready to open the tools for English Query, but we have one more set of concepts to cover. English Query makes heavy use of the English language constructs, as you might imagine. Although you don't need to be a linguist to use the product, you do need to understand which English components you use when you design an application. These constructs are used in the Semantic Model I introduced in the previous tutorials. The Semantic Model maps the English language components to the Physical Model that a relational database contains.

There are two main parts of English that English Query uses: Entities and Relationships. Let's begin by examining the Entities.

Entities in English Query are similar to nouns in English (a person, place or thing) and to columns or groups of columns in a database. For instance, we'll define the title column in the pubs database as an English Query Entity called book. We'll also define the au_fname and au_lname columns as a single English Query Entity called author.

You may have already noticed a limitation with this concept. I might call the person who writes a book an author, but someone else might call them a writer. When they use the application to say "How many books has writer Buck Woody written?" they won't get an answer. To deal with this, English Query includes Synonyms. These are just as their English language counterparts – another word for the same thing. You should make sure you include as many as possible in your design, but no make sure you don't obscure the meaning too far by using non-exact Synonyms. For instance, dog and pet might be synonyms in one sense, but pet is too broad to mean just dogs.

The next component is the Relationship. Relationships show what the Entities have to do with each other. Authors write books – stores sell books – publishers publish books, that sort of thing. When we define these, some of the statements (like publishers publish books) might seem a bit basic. After all, isn't it obvious that publishers publish books? It may be to human minds, but remember that no matter how realistic this all is, a computer doesn't "understand" anything. The more complete you are with the relationships, the more useful the application becomes.

English Query implements relationship definitions using a construct called Phrasings. Phrasings come in various types:

Adjectives
Commands
Name/ID
Prepositions
Subset
Trait
Verbs

Let's examine how we'll use these Phrasing Types.

The first item in the list is the Adjective. An Adjective modifies or further describes an Entity. For instance, an expensive book, a prolific author, a lot of sales are all implementations of Adjective Phrasings. As we design the application, we'll include lots of Adjective Phrasings.

The next item is the Command. This type of Phrasing allows users to enter and change data in the application. I don't recommend using this type of Phrasing until you've created several applications and understand how they work.

The Name/ID Phrasing type is the one we'll use most often. Using this type of Phrasing we'll define what things are, such as Book Names are the names of books. Again, some of these instructions might seem silly or obvious, but they are required to make sure the application has the illusion of human intelligence.

Prepositions link Entities with other parts of the sentence. For instance, "Which book was written after book X?" describes one preposition. Here's a sample list:

About	Above	According to	Across	Along with
As to	Because of	Before	Below	Concerning
Down	Due to	For	From	In
In case of	In place of	Instead of	Like	Of
On	Over	Past	Regarding	Since
Through	Till	To	Toward	Under
Until	Up to	With	With regard to	Without

The Subset Phrasing lets you break down your queries into groups, such as "Some authors live in the Northeast."

The Trait Phrasing is another commonly used type. We'll use this type when we show ownership, with the words have and has. Stores have addresses; Books have authors, and so forth.

Finally we have the Verb Phrasing type. Just as in spoken English, verbs show action. Example sentences here are "Authors write books" and "Stores sell books". We need to be careful here – Microsoft reserves some of these words for itself. You can find a list of those in Books Online.

That's enough for this week. In the next installment we'll start putting some of these concepts to work.