1 / 42

Intelligent Fril/SQL Interrogator

Intelligent Fril/SQL Interrogator. Dong (Walter) Xie Supervisor : J. F. Baldwin. 2003 - 2004. Why We need Computers in Business. To Store, Retrieve, Analyse and Integrate Information Database system, Information system Data mining, Machine learning, Fuzzy logic system

nijole
Download Presentation

Intelligent Fril/SQL Interrogator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intelligent Fril/SQL Interrogator Dong (Walter) Xie Supervisor : J. F. Baldwin 2003 - 2004

  2. Why We need Computers in Business • To Store, Retrieve, Analyse and Integrate Information • Database system, Information system • Data mining, Machine learning, Fuzzy logic system • Procedural programming language, Logic programming language • Never Tired, Never Complain, Less Mistake, Less Paid • To Communicate in The World • Email, Internet phone, Internet fax • Some of Popular Systems in Business • BI (Business Intelligence) • ERP (Enterprise Resource Planning) • CRM (Customer Relationship Management) SAP, Oracle, Sagent, Informatica, SAS, SPSS, Business Objects, Cognos, IBM … …

  3. Intelligent Fril/SQL Interrogator • Powerful Query System • Standard SQL query • Fril query — querying the facts and rules to give the answer with support pair • Natural language query • Fril  Support Logic Programming+ Knowledge Bases + Fuzzy Logic System (Graphic Fuzzy Sets Interface) • Fuzzy Logic Data Mining & Machine Learning Toolkit • Object Oriented • Reusable, structural, adaptable, information hiding, reliable

  4. Fril (Fuzzy Relation Inference Language) & its Resources • Fril is a support logic programming language which includes Prolog as a subset of the language, but which also allows probabilistic uncertainties and fuzzy sets to be included. • Fril and its applications are described in detail in “Fril – Fuzzy and Evidential Reasoning in Artificial Intelligence”, J. F. Baldwin, T. P. Martin and B. W. Pilsworth. • Trevor Martin’s homepagehttp://www.enm.bris.ac.uk/ai/martin/downloads/FrilResources.html • http://www.enm.bris.ac.uk/ai/fril.html

  5. Intelligent Fril/SQL Interrogator Flow Graph Fuzzy Logic Data Mining Toolkit Database tables & Output tables Database drivers User JDBC Intelligent Fril/SQL Interrogator … …. ODBC Fril JFril CFril Natural Language interface Knowledge Base Fril interface

  6. Structure of Objects Inputs List Output List Fuzzy Partition List Output of one object can link to other objects as input 1 Database tables 2 Output tables from other objects 3 Knowledge bases from other objects either 1 Output table or 2 Knowledge base FP1 FP2 Processed a temporary database TD Slot 1 SQL query using tables in the inputs list Provided Fril method or Fril knowledge base for Fril queries Slot 2 Machine learning toolkit to generate Fril knowledge base, or Fril method written by user Fril wh query to provide smart answers Slot 3 Fril wh query given by rule with result as head • Slot 2 or 3 can be empty. • Temporary database TD will be passed down from slot 1 to the last slot in sequence, and training set of slot 2 automatically formed from TD • Output of the object comes out from the last slot.

  7. 2 Knowledge Base Slot Machine Learning Toolkit Customized by User Object Slots Object Inputs 1 SQL Slot 2 & 3 empty 2 empty 3 Fril Query Slot Knowledge Base Output Table If 3 empty, the object outputs knowledge base; if 3 not empty, it outputs output table.

  8. Fril Knowledge Base in Slot 2 • Generated automatically by machine learning toolkit • IF… THEN… fuzzy logic rules, fuzzy ID3 decision tree, Bayesian network, fuzzy association rules or prototypes • The training set is automatically provided by the result set of SQL query in slot 2 • The knowledge base can be edited by user • Customized by user • Fril method written by user • Some theorem in Fril format

  9. Fuzzy Logic Machine Learning Toolkit Original Database Bayesian Net Fuzzy clusters Effective Reduced Database Fuzzy ID3 Decision Tree Fril general rule Fril general rule Fuzzy ID3 Decision Tree Fuzzy Logic rules Fuzzy Logic rules Fril general rule Fuzzy clusters prototypes Fuzzy Association rules Evidential logic rules

  10. Temporary Fril Knowledge Base Before using Fril query, we first extract data from temporary database from SQL query to form a temporary Fril knowledge base of clauses. For example: temp = We obtain Fril knowledge base ((temp John 144 20)) ((temp Jill 156 22)) ((temp Pat 120 18)) ((temp Bill 153 21)) The Fril query then operates using this temporary knowledge base Result of query put into output table

  11. Fuzzy Partitions in the Object Fuzzy partitions listed in the object are used to explain fuzzy sets in Fril method or answer Fril query Simple Fuzzy Partition : A simple fuzzy partition {fi} is a set of triangular or trapezoidal fuzzy sets such that for any data point x  X where X is the universal set.

  12. T SUPPORT a b 0.7 0.85 Fril Query in Slot 3 & its Result Object’s Output Table Fuzzy Set (short [0:1, 2:0.7, 3:0]) Temporary Knowledge Base ((case 1 a 2)) ((case 2 b 1)) ((case 3 c 3)) Fril Query in Slot 3 (X (findall (T SUPPORT) ((supp_query ((case R T short)) (SUPPORT P)) (less 0 SUPPORT)) X)) Solution in Fril format ((a 0.7) (b 0.85)) Transferred to output table as Note: Fril query in slot 3 is equivalent to a wh query, but word wh has been omitted

  13. Linking Objects Object 4 Object 3 Knowledge base 3 Output table 4 Database 2 SQL 4 SQL 3 Machine learning toolkit Fril Query 4 Object 1 Object 2 Output table 1 Database 1 Output table 2 SQL 1 SQL 2 Fril Query 1 Fril Query 2

  14. Applications in Business and Commerce • It can be either integrated with other system • AI part of these systems, such as BI, ERP, CRM, or E-Commerce … … • Or can be used alone • Supermarket Basket Analysis • Risk Analysis (Foreign Trade) • Fraud Detection (APACS London reported £373.7 million losses through credit card fraud in the 12 months ending August 2001) • Advertisement Analysis • Commodities, Currencies, Stocks & Futures Market Analysis

  15. Other Applications • Engineering • Intelligent CAD and CAM • Engineering design … … • Science • Intelligent personal identification library (face, fingerprint, DNA) • Flood or earthquake prediction • Virtual chemical combination • Medicine, Biology and Genetics • Intelligent gene library • Diagnostic expert system

  16. Supermarket Basket Example Database Customer table contains the personal details of customers Transaction table contains supermarket basket scanner panel data Product table contains the commercial and nutritional information published in web

  17. Fuzzy Sets of Sugar Content low medium high 1 29.2g (per 100g) 0 Product Table & Fuzzy Partition of Sugar Content Product Table (nutrition) Product Name UPC (universal product code) … Price Vegetarian Energy Protein Sugar Crispy Chilli Beef 2014730 £ 2.96 (unit) No (Yes / No) 318 (kcal per 100g) 11.9 (g per 100g) 23.8 (g per 100g) Fuzzy set defined by manager or user Sainsbury's online shop : http://www.sainsburystoyou.com/arriving/login.jsp

  18. Object 2 Transaction Output table 2 Object 1 SQL 2 Object 4 Object 7 Customer Transaction Output table 4 Output table 7 Object 4 Object 1 SQL 4 SQL 7 Product FP1 Output table 1 Customized rules Fril query 7 SQL 1 Fril query 1 Object 5 Object 6 Customer Customer Knowledge base 5 Output table 6 Object 4 Object 3 Object 4 Object 5 Transaction SQL 5 SQL 6 Output table 3 Object 1 Customized knowledge Machine learning toolkit SQL 3 Fril query 6 Example of Linking Objects

  19. Object1 — Support of Low Sugar Content List the items whose membership of that sugar content being low UPC, ProductName, Price, Sugar, Support Product object1_sugar … c UPC gives SELECT UPC, ProductName, Price, Sugar FROM Product ORDER BY UPC in Output Table (X (findall (UPC PRODUCTNAME PRICE SUGAR SUPPORT) ((supp_query ((case R UPC PRODUCTNAME PRICE object1_sugar_low)(case R UPC PRODUCTNAME PRICE SUGAR)) (SUPPORT P)) (less 0 SUPPORT)) X))

  20. Object2 — Total Price of Low Sugar Content Food Object1 Object2 Items, SaleTotalPrice, LabelTotalPrice, Support Transaction, Object1 UPC, ProductName, Price, Sugar, Support Product SELECT Sum(Transaction.ItemQuantity) AS NumOfItems, Sum(Transaction.TotalPriceOfTheItem) AS SaleTotalPrice, Sum(Transaction.ItemQuantity*FocusOutputTable_1.Price) AS LabelTotalPrice, FocusOutputTable_1.Support FROM Transaction INNER JOIN FocusOutputTable_1 ON Transaction.UPC=FocusOutputTable_1.UPC WHERE Transaction.Date<#10/10/2004# And Transaction.Date>#1/10/2004# GROUP BY FocusOutputTable_1.Support ORDER BY FocusOutputTable_1.Support SELECT UPC, ProductName, Price, Sugar FROM Product ORDER BY UPC (X (findall (UPC PRODUCTNAME PRICE SUGAR SUPPORT) ((supp_query ((case R UPC PRODUCTNAME PRICE object1_sugar_low)(case R UPC PRODUCTNAME PRICE SUGAR)) (SUPPORT P)) (less 0 SUPPORT)) X)) Object2 sums up the number of items sold from 01/10/2004 to 10/10/2004, their total price and the total price originally labelled according to the distribution of support of Object1, where the support is the membership of that sugar content being low

  21. Chart of Object2 Output Table (1) Object2 Support – Total Price Bar Chart & Fuzzy Set Tendency Line Based on Low Sugar Content Fuzzy Set

  22. Chart of Object2 Output Table (2) Object2 Total Price – Support Bar Chart & their Tendency Lines Based on Low Sugar Content Fuzzy Set

  23. Object3 — Marketing Segmentation or Customer Personalization Object1 Object3 Customer Informaton, Items, SaleTotalPrice, LabelTotalPrice, Support Transaction, Object1 UPC, ProductName, Price, Sugar, Support Product SELECT Customer.CardNumber, Customer.FirstName, Customer.Surname, Sum(Transaction.ItemQuantity) AS NumOfItems, Sum(Transaction.TotalPriceOfTheItem) AS SaleTotalPrice, Sum(Transaction.ItemQuantity*FocusOutputTable_1.Price) AS LabelTotalPrice, FocusOutputTable_1.Support FROM Customer INNER JOIN (Transaction INNER JOIN FocusOutputTable_1 ON Transaction.UPC = FocusOutputTable_1.UPC) ON Customer.CardNumber = Transaction.CardNumber GROUP BY FocusOutputTable_1.Support, Customer.CardNumber, Customer.FirstName, Customer.Surname ORDER BY Customer.FirstName, Customer.Surname SELECT UPC, ProductName, Price, Sugar FROM Product ORDER BY UPC (X (findall (UPC PRODUCTNAME PRICE SUGAR SUPPORT) ((supp_query ((case R UPC PRODUCTNAME PRICE object1_sugar_low)(case R UPC PRODUCTNAME PRICE SUGAR)) (SUPPORT P)) (less 0 SUPPORT)) X)) If we constrain SQL in Object3 to search an individual or a group of customers with personal information, such as name, age, post code, etc., we are able to find customers’ favourites by individual or groups.

  24. Chart of Object3 Output Table (1) Support – Total Price Bar Chart & Fuzzy Set Tendency Line Customer name = “Dong Xie” Object3 Based on Low Sugar Content Fuzzy Set

  25. Chart of Object3 Output Table (2) Total Price – Support Bar Chart & their Tendency Lines Customer name = “Dong Xie” Object3 Based on Low Sugar Content Fuzzy Set

  26. Marketing Segmentation or Customer Personalization Report To analyse customer behaviour and favourite, we plot ordering support graphs in number of items, total price, …, with respect to the energy, sugar content, fat content, …, and price, discount, …, etc. … … Energy Protein Fat Sugar Price Fuzzy Set low … … … … medium high Report an individual customer OR a certain group of customers

  27. Object5 — Customers’ Shopping Frequency & Average Spending Transaction Table (basket scanner data) = Price  Item Transaction ID Date Card Number UPC Item Quantity Total Price Object4 SELECT DISTINCT DateOfTrans, CardNumber, SUM(TotalPriceOfTheItem) AS TotalPrice FROM Transaction GROUP BY DateOfTrans, CardNumber ORDER BY DateOfTrans Object8 (= Object5 SQL) SELECT Count(FocusOutputTable_4.CardNumber) AS ShoppingFreq, Avg(FocusOutputTable_4.TotalPrice) AS AvgSpending, Customer.FirstName, Customer.Surname, Customer.Rank FROM Customer INNER JOIN FocusOutputTable_4 ON Customer.CardNumber=FocusOutputTable_4.CardNumber WHERE Customer.Rank > 0 GROUP BY Customer.FirstName, Customer.Surname, Customer.Rank Name Shopping Frequency Average Spending Customer Rank Customer rank can be adjusted by manager or analyser

  28. Object5 — Knowledge Base Learned by Data Mining Toolkit Training Set (SQL Result) Fuzzy Logic Data Mining Toolkit Fuzzy Decision Tree Fuzzy Sets Simple Fuzzy Logic Rule General Fril Rule The output of Object5 is the knowledge base, which can be linked into the input list of other objects IF … THEN … rule

  29. The Best Customers Definition from Knowledge Base Object5’s knowledge base (learned by fuzzy logic data mining toolkit) 1 X is Rarely (rank_fuzzy_set_1) Best Customer IF his/her Shopping Frequency is Rare (shoppingfreq_fuzzy_set_1) 2 X is Highly (rank_fuzzy_set_3) Best Customer IF his/her Shopping Frequency is High (shoppingfreq_fuzzy_set_3) Object7’s knowledge base (customized by user) 3 X is Fairly (rank_fuzzy_set_2) Best Customer IF his/her Shopping Frequency is Fair (shoppingfreq_fuzzy_set_2)

  30. Object6 & Object7 — To Infer the Rank of Each New Customer Object6 Load knowledge base in Fril, before to process slots [1] Customer Table [2] Object4 [3] Object5 Knowledge Base FirstName, Surname, New_Rank Form temporary knowledge base which is passed to Fril SELECT Count(FocusOutputTable_4.CardNumber) AS ShoppingFreq, Avg(FocusOutputTable_4.TotalPrice) AS AvgSpending, Customer.FirstName, Customer.Surname, Customer.Rank FROM Customer INNER JOIN FocusOutputTable_4 ON Customer.CardNumber=FocusOutputTable_4.CardNumber WHERE Customer.Rank is null GROUP BY Customer.FirstName, Customer.Surname, Customer.Rank Fril program to infer the rank of each new customer ((term R SHF ASP)(case R SHF ASP FIRSTNAME SURNAME RANK)) (X (findall (FIRSTNAME SURNAME NEW_RANK) ((supp_query ((rank is rank_fuzzy_set_2 R)(case R SHF ASP FIRSTNAME SURNAME RANK)) (NEW_RANK P)) (less 0 NEW_RANK)) X)) Fril query to infer the rank of each new customer Who are the best customers?

  31. Object7 — Knowledge Base Customized by User Object7 FirstName, Surname, New_Rank [1] Customer Table [2] Object4 Fuzzy logic rules defined by experienced expert SELECT Count(FocusOutputTable_4.CardNumber) AS ShoppingFreq, Avg(FocusOutputTable_4.TotalPrice) AS AvgSpending, Customer.FirstName, Customer.Surname, Customer.Rank FROM Customer INNER JOIN FocusOutputTable_4 ON Customer.CardNumber=FocusOutputTable_4.CardNumber WHERE Customer.Rank is null GROUP BY Customer.FirstName, Customer.Surname, Customer.Rank (avgspending_fuzzy_set_2 [ 47.108 : 0 99.497 : 1 ] ) ((rank is rank_fuzzy_set_2 R)(term R X1 avgspending_fuzzy_set_2)) ((term R SHF ASP)(case R SHF ASP FIRSTNAME SURNAME RANK)) Fril program written by user (X (findall (FIRSTNAME SURNAME NEW_RANK) ((supp_query ((rank is rank_fuzzy_set_2 R)(case R SHF ASP FIRSTNAME SURNAME RANK)) (NEW_RANK P)) (less 0 NEW_RANK)) X)) Knowledge base customized by user provides flexibility of system, which can be rules in Fril format or theorem (Fril program).

  32. A Simple Flight Routes Example In the example of fight routes query system, we design a logic object shown in the next slide to list all possible routes from Edinburgh to Beijing, their total distances and total number of stops associated.

  33. Route_List, Total_Distance, Total_Stop [1] Flight SELECT DISTINCT DepartureAirport, Destination, Distance FROM Flight ((travel DEPAR DEST S1 TOTAL_DIS TOTAL_STOP TOTAL_STOP (DEPAR | (DEST | ( )))) (case INDEX DEPAR DEST DIST)(sum S1 DIST TOTAL_DIS)) ((travel DEPAR DEST S1 TOTAL_DIS S2 TOTAL_STOP (DEPAR | ROUTE_LIST)) (case INDEX DEPAR Z DIST)(sum S1 DIST SD)(sum S2 1 ST)(travel Z DEST SD TOTAL_DIS ST TOTAL_STOP ROUTE_LIST)) (X (findall (ROUTE_LIST TOTAL_DISTANCE TOTAL_STOP) ((travel Edinburgh Beijing 0 TOTAL_DISTANCE 0 TOTAL_STOP ROUTE_LIST)) X)) Object 1 — Flight Routes Query System Object1 travelis a recursive definition in Fril containing 7 parameters. The first travel is the terminating condition of the recursion.

  34. Object 1 Output Table It is obviously that using conventional programming languages, such as C++, Java, Fortran, etc, has to cost more code than the two-line simple code in last slide. Nor SQL can implement this example just using two-line simple code. This is one of the big advantages to use Fril compared with other programming languages.

  35. Product Recommendation System Example Energy Production Vitamin Fat Salt Sugar Fibre Cost … … Baskets Find overlapping clusters and name each cluster e.g. healthy eater, junk eater, etc. A point will have membership in each cluster Each cluster can be represented by a fuzzy prototype.

  36. Filter Select useful pattern Delete incompatible pattern . . . List of recommendations with rating The Hybrid Fuzzy Expert System New Basket Vegetarian Association rules 0.8 Semantic Distance Healthy Eater associated with 0.5 Greedy Buyer each fuzzy cluster 0.2 Classify new customer … … … …  The products already in the basket will not appear in the recommendation list Fuzzy Prototypes

  37. The Fuzzy Prototype Model A fuzzy prototype is a fuzzy model of a cluster providing a description of the relevant properties of the data in the cluster. The fuzzy model can be a fuzzy decision tree, simple fuzzy logic rules, etc. Therefore, the fuzzy prototype model is the collection of fuzzy prototypes formed from the data set.

  38. Semantic Coordinate and Semantic Distance If the semantic distance between two semantic coordinates of objects is smaller than a threshold SD < ε, then we consider that these two objects have the semantic identity (SD = 0).

  39. Linking Objects For The Fuzzy Prototype Model

  40. Linking Objects For Semantic Distance

  41. Papers Related with Machine Learning Toolkit • J. F. Baldwin, Dong (Walter) Xie, “Simple Fuzzy Logic Rules based on Fuzzy Decision Tree for Classification and Prediction Problem”, Intelligent Information Processing II, Published by Springer, ISBN 0-387-23151-X (HC), Page 175, October, 2004. • J. F. Baldwin, Dong (Walter) Xie, “Fuzzy Association Rules discovered on Effective Reduced Database Algorithm”, Fuzz-IEEE 2005, Reno. • D. Xie and J. F. Baldwin, “Fuzzy prototype model and semantic distance”, Intelligent Systems, Special Issue, 2006. • D. Xie and J. F. Baldwin, “Intelligent fril/sql interrogator”, International Journal of Intelligent Systems, in review.

  42. Contact Detail Professor Jim F. Baldwin http://www.enm.bris.ac.uk/ai/enjfb.html Dong Xie (Walter) 谢冬 http://eis.bris.ac.uk/~enxdxor google search “Dong Xie” D.Xie@bristol.ac.uk (English) Xiedong75@hotmail.com (中文)

More Related