Deductive AI and SQL Server



Watson, IBM's poster child for artificial intelligence, could not have been built using a single type of AI technology. This is hardly surprising. The intelligence needed to understand a question can differ quite dramatically from the intelligence needed to answer it. Indeed, upon understanding a question it takes intelligence to know what techniques are most likely to bear fruit in the form of an answer (and, in Watson's case, perhaps frame the answer as a question!)

Today, when folks talk about "artificial intelligence," they are usually talking about the great successes that have been achieved in recent years applying statistical learning theory. Neural networks are the most prominent, but hardly the only, examples of statistical techniques. The big advantage is that developers do not need to concern themselves with the details of how to, say, recognize a face; they need only create a system that will learn and generalize when exposed to many, many, many, exemplars.

There are, of course, other types of artificial intelligence, including software that applies deductive logic. Starting in the 1970s, there was a lot of interest in the logic programming language Prolog, but at that time successes were few, since machines did not have either the computing power or the available memory that we take for granted today. Today, few people are aware of Prolog code executing within applications, but the language continues to find new uses. IBM's Watson, for example, includes Prolog code in its complex AI implementation.

Data, Rules, and Logic

We have seen that SQL Server 2017 supports graph data, so it would be easy to create tables that include a unidirectional edge called "parent" that would connect people. What if we wanted to learn about grandparents, or descendants, or aunts and uncles, or ancestors? Are grandparents enough or should we also include grandmothers and grandfathers? It would be impractical to keep adding columns to describe the entire list of relationships folks might have with each other. It would, however, be easy to create definitions for each of these relationships and then let the system automatically deduce whether a given relationship exists between individuals.

A curious thing about Prolog is that, unlike most programming languages, there is virtually no distinction between program code and data. Everything (well, almost everything) is just a "fact." For example, we could read parent data from an SQL Server graph table and create the following Prolog facts. (Many Prolog textbooks start with genealogical examples. We'll continue that tradition and build on an example from Ivan Bratko.)

parent( pam, bob). % Pam is a parent of Bob

parent( tom, bob).

parent( tom, liz).

parent( bob, ann).

parent( bob, pat).

parent( pat, jim).

If we want to distinguish fathers and mothers and grandfathers and grandmothers, we will need a few more facts.

male( tom).

male( bob).

male( jim).

female( pam).

female( liz).

female( ann).

female( pat).

That's about all we can get from SQL Server. But a few more facts can be added directly to our Prolog program. For example

mother( X, Y) :-

parent( X, Y),

female( X).

In Prolog, "variables" are indicated by capital letters. The code above essentially means that X is the mother of Y if X is a parent of Y and X is female.


In Prolog, values are not assigned to variables as they are in imperative languages like C#. The Prolog process is, in fact, similar to what logicians refer to as "unification". In other words, if we start with mother(X, bob) the system will attempt to determine if there exists a value for X for which mother(X,bob) is true.

In a slightly more complicated example, we could add definitions like the following:

grandparent(X,Z) :-

parent( X, Y),

parent( Y, Z).

This fact permits Prolog to deduce that X is the grandparent of Z if X is a parent of Y and Y is a parent of Z. Not surprisingly, the rules can also be recursive:

ancecestor( X, Z) :-

parent( X, Y),

ancestor( Y, Z).

Getting Started

If you are curious to explore this further, there are top-quality open source implementations you can download and start using today. SWI-Prolog can support large professional programs and is in use worldwide. XSB implements some features which can help avoid some programming pitfalls, but XSB does not enjoy the wide support that SWI does; there are more available tutorials and examples on the web using SWI. Both implementations can easily convert SQL Server table data into Prolog facts using ODBC connections.

The full example code including the above snippets, tested using SWI-Prolog, can be downloaded here.


Without a doubt, the statistical techniques of neural networks and artificial intelligence will continue to make great strides. However, it should not be forgotten that deductive logic can be added to applications application programs to make them even more intelligent.

AUTHOR: Dan Buskirk

Related Training:
SQL Server Training
Dan Buskirk

Written by Dan Buskirk

The pleasures of the table belong to all ages.” Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.

Chat With Us