Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
English Query - Part 1
Last updated Mar 28, 2003.
In a previous tutorial I began a thread on using Natural Language Processing (NLP) technology in databases. Microsoft SQL Server 2000 includes a product called English Query that allows your users to type in questions as they would normally frame them in English, and receive query results from a database. In this tutorial I'll start a demonstration of that process that you can follow along with on your test system.
Before you can start, you'll need to install the product. English Query is similar to Analysis Services (in SQL Server 2000, at least) in that it is installed in a separate step from the database engine. Which brings up an important question: where should you put the installation?
Just as in the case of Analysis Services, you can place English Query on the same system that houses your production database. Just because you can, however, doesn't mean that you should. In any system of appreciable size or activity, say more than 20 users or several hundred transactions a minute, you should always separate your data store from processing activities. That means you should have separate servers for Analysis Services, Reporting Services, and English Query from the database server.
To install English Query, insert the SQL Server 2000 CD in the drive, and select SQL Server Components from the menu that appears. From there, select Install English Query. Select the defaults to get the engine, documentation and samples.
According to the previous tutorial on Natural Language Processing, we need to start by developing our questions. For this example we'll use the pubs database since it is small and easily understood. The pubs database is included with SQL Server 2000 and contains data regarding books, authors, sales and stores, and publishers. I'll focus this tutorial on some simple questions from that database regarding author information. What we're after here is an understanding of English Query, not the database itself.
Following the process I've previously outlined, we've asked our users what kind of information they would like to know from the system. They filled out the questionnaires we sent out and from that information we've selected these questions to develop:
- Who are the authors?
- How many books has author (Name) written?
- What books have they written?
- Who sold the most books?
In production you'll have far more questions, of course. As you'll see by the time we finish this series you'll often be able to answer multiple questions with a single construct.
The next step is to place the questions into categories. The first category (Who are the authors, what books have they written) has to do with general information. The next category (How many books has author (Name) written) deals with numerical operations. The final category deals with action (who sold the most books). These questions form the basic categories that you'll work with even in the most demanding application.
Once again following the outline from the previous tutorial, we now need to ensure we have the information in the database that answers the questions. In the case of this example, I developed the questions from the database, so of course it meets this requirement. In real-world environments this isn't always the case. You may find that the information you are after isn't currently captured in your source database or Analysis Services cubes. In that case you either have to gather or create the information or report back to the users that they can't get that from the system as it currently stands.
We need to understand not only the questions the users might ask of the system, but also the database model within the system. This information is called the logical model of the database, or simply the metadata. You can get this information by examining the tables and relationships manually or by looking at an Entity Relationship Diagram of the database. I have an entire series of tutorials on that here. In any case you'll need to understand what physical constructs support the questions the users want answered. Once you understand the physical tables and relationships, you can query the database to find the answers to the questions.
In our example we examine the ERD of the pubs database and run the following queries to get the answers to each question:
Who are the authors?
USE pubs GO SELECT au_fname + ’ ’ + au_lname AS ’Author’ FROM authors ORDER BY au_lname GO How many books has author (Name) written? SELECT au_fname + ’ ’ + au_lname AS ’Author’ , count(title_id) AS ’Books’ FROM authors LEFT OUTER JOIN titleauthor ON titleauthor.au_id = authors.au_id GROUP BY au_fname + ’ ’ + au_lname ORDER BY Books What books have they written? SELECT au_fname + ’ ’ + au_lname AS ’Author’ , title FROM authors INNER JOIN titleauthor ON titleauthor.au_id = authors.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id ORDER BY au_lname
Who sold the most books? This question is answered by the previous queries.
So now we know that the data exists to answer the questions. With the questions defined and categorized, we have enough information to create the first part of the structure we need for the application. This first structure is called the semantic model. We'll flesh that out further as we move along. The second set of information consisting of the queries we created a moment ago satisfies the requirements for the physical model. With these two models complete, we can begin to create a matrix of the logical to the physical arrangement. From there we'll tie out the various objects to each other, and then it's a simple matter of laying out the structure in English Query. We'll do that in the next few tutorials.
Informit Articles and Sample Chapters
If you happen to still use SQL Server 7, you can check out this free chapter on English Query for that version.