PREFERENCE SQL - DATABASE QUERYING FOR DATA THAT FITS BEST TO THE USER'S WISHES
By Uwe Harms
Searching in databases for e-commerce often results in an empty answer, as not all the requirements can be fulfilled. This is a strong handicap in e-commerce and e-shop applications. Preference SQL now enriches database querying with the useful concept of searching the data fitting best if no perfect answer can be found. It is a search with soft constraints where no exact answer is expected. The concept provides the base for building electronic sales interfaces that behave more like natural salespersons. Preference SQL offers the linguistic means to express that one tuple is better than another one. Adherence to SQL-style declarative programming guarantees maximum programming efficiency. Accessibility through connectivity standards makes it available on a broad variety of platforms.
When someone wants to buy a car and he or she goes to a car dealer, there are a variety of different choices. For example, imagine the buyer seeks a green BMW 5-series, built in 1995, with air conditioning, 30,000 miles, and a price tag of $15,000 (i.e. his personal criteria). The salesman looks around and tells him he does not have this specific car, but he has some different choices and can offer him a blue BMW with 20,000 miles but for $20,000, or a red one with 40,000 miles, or a BMW of the 7-series. Then it is up to the buyer to select one or none of these alternatives. A customer oriented seller offers alternatives that do not exactly fit into the buyer's wishes but lie within a certain interval.
Current SQL is based on precise logic that does not directly understand the notion of preferences and hence is incapable of directly supporting soft constraints or imprecise criteria. Thus, any preference must be forced into a KO-criterion. The user has to think like an SQL database.
This often results in answers like, "no such car or hotel or flight found that matches your criteria". Then, the user has to modify his criteria and try again. When he repeatedly receives empty query results, he is very disappointed and will leave the electronic retailer's web site.
To solve this problem, an extension of standard SQL that adds preferences as first class citizen language constructs is necessary. Then, the database and the user interface appear to think like the end-user. It becomes the burden of an intelligent and powerful query optimizer to find the right answer. If the ideal item is not available in the database, the query optimizer has to automatically locate those items that are closest in the sense of the stated preferences. Indeed, the database will look like a cooperative software agent giving good customer advice during e-shopping.
Database Preference Software GmbH, Augsburg, Germany, made a breakthrough by developing such an extension to SQL. Preference SQL Technology, with its efficient personalized search capabilities, is at the heart of e-commerce and forms one of its enabling key technologies.
E-shopping, naturally, is strongly related to preferences and personal needs of customers. Wishes are a very natural thinking paradigm in the real world. They are of a qualitative nature, a human being likes one item best and ranks all the others. Very rarely people assign numerical data to their wishes, humans don't think in numbers when ranking items or wishes for making shopping decisions. The wishes are expressed by people in a completely declarative manner. The customer does not want to be involved in technical details concerning how their wishes are satisfied, but they have to be fulfilled as sufficiently as possible.
These premises guided the definition of Preference SQL. Preference SQL isn't just another ad hoc approach, instead it is an intuitive paradigm for efficient database filtering in e-commerce applications.
Preference SQL compatibly extends standard SQL by introducing new language constructs that treat preferences as first class citizen language constructs. Preferences express that certain data items are more preferable than others when querying the database. They can form arbitrarily complex relationships, can be defined by a powerful preference definition language and persistently stored in the database.
The create preference statement of Preference SQL allows it to conveniently model a large spectrum of user wishes that occur in shopping reality, including:
For example. in the statement:
CREATE PREFERENCE my_colors (POSITIVE VALUE IN('green','blue'), TYPE
VARCHAR(10));the customer favors these colors over any other color.
Approximations are also possible, for instance, the mileage of a car:
CREATE PREFERENCE my_mileage (VALUE AROUND 30000, TYPE INTEGER);Database Preference Software GmbH developed and implemented innovative rewriting algorithms to optimize the evaluation plan of a Preference SQL query. Proprietary, highly inventive algorithms are used for intelligent filtering of large search spaces against preferences.
Actually the rewritten SQL code is handed over to a conventional SQL database system. The additional code, generated for rewriting purposes by the Preference SQL Optimizer, is fully SQL92 entry-level compliant. Thus, these extensions can run in combination with any SQL92 entry-level compliant database system. This even holds for preferences referring to approximate data values, these fuzzy or imprecise soft constraints are efficiently mapped into SQL technology without resorting to fuzzy logic. This clearly preserves any legacy investments in SQL database systems and complies with the requirements of industry standards. One is able to keep the installed SQL database system software and without changing the existing database.
The system integrates with Java application environments and acts as a JDBC (Java Database Connectivity) or with C and C++ as an ODBC (Object Database Connectivity) driver to the application.
Preference SQL 1.2 supports many relational database systems including IBM's DB2 Universal Database and Oracle - for details and the versions see the web-page. The search engine runs on Windows NT and Linux servers and has the JDBC and ODBC interface.
In a graphical user interface, the customer chooses important items, sets KO-criteria, and ranks the constraints following his or her personal wishes. The search engine then selects - as in real life car shopping - BMWs that fit in the preference wishes. Those constraints that are not exactly fulfilled, for example the mileage of the car, are highlighted in a different color. Then - like in the case of a human salesperson - the customer can directly recognize the alternatives and select the most appropriate offering. This way, the consumer does not get an empty answer, as in the case of usual SQL shopping.
Database Preference Software GmbH is a German start-up company, a spin-off of the University Augsburg. It was founded in 1997 in Augsburg by Prof. Werner Kiessling and Dr. Gerhard Koestler and is financed via start-up fonds.
More information and demos in English can be found: http://www.preference.de/.