1 / 53

Evaluation of Conditional Preference Queries

Evaluation of Conditional Preference Queries. Fabíola S. Fernandes, Sandra de Amo. UNIVERSIDADE FEDERAL DE UBERLÂNDIA. PÓS-GRADUAÇÃO EM CIÊNCIA DA COMPUTAÇÃO. MOTIVATION. Movies ( T itle , G enre , Y ears , D irector , A ctor ). My preferences :

wyatt-berry
Download Presentation

Evaluation of Conditional Preference Queries

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. Evaluation of Conditional Preference Queries Fabíola S. Fernandes, Sandra de Amo UNIVERSIDADE FEDERAL DE UBERLÂNDIA PÓS-GRADUAÇÃO EM CIÊNCIA DA COMPUTAÇÃO

  2. MOTIVATION Movies( Title, Genre, Years, Director, Actor ) Mypreferences: I preferthosemoviesproduced in the 90’s ratherthanfrom 80’s, ifbothbelongs to thesamecategory (genre); For the movies produced in the 80’s I prefer dramas to comedies; For Woody Allen’sfilmsofthesamegenreanddecade, I preferthosestaringtheactress Charlotte Ramplingthanthosestaring Mia Farrow. UFU

  3. MOTIVATION Movies( Title, Genre, Years, Director, Actor ) Queries: • Give the titles of the films which most fulfill my wishes among those stored in the database, provided they are not romance films. • Give the 4 films, among those stored in the database, which most fulfill my wishes. UFU

  4. OUTLINE • TheCPref-SQLLanguage • Related Work andContributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  5. THE CPref-SQL LANGUAGE • Anextensionof SQL able to expressconditionalpreferencequeries • Thequeriesincorporatethe usual hardconstraints (WHERE) as well as softconstraints (preferencerules) • Goals: • Express preferences over a database • Filtertheanswer to queriesaccording to userpreferences UFU

  6. THE CPref-SQL LANGUAGE Express preferences over a database as follows: CREATEPREFERENCESMyPrefs FROM MoviesAS Y=90 > Y=80 [T,D,A] AND IF D=Woody Allen THEN A=Charlotte Rampling > A=Mia Farrow [T] AND IF G=comedyand Y=80 THEN D=Joel Coen> D=Woody Allen [T,A] AND IF Y=80 THEN G=drama > G=comedy [T] UFU

  7. THE CPref-SQL LANGUAGE Filtertheanswers to queriesaccording to userpreferences SELECTtitle FROMmovies WHEREgenre <> ‘romance’ ACCORDING TO PREFERENCESMyPrefs, 4 UFU

  8. OUTLINE • TheCPref-SQLLanguage • Related Work andContributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  9. RELATED WORK AND CONTRIBUTIONS UFU

  10. RELATED WORK AND CONTRIBUTIONS UFU

  11. CONTRIBUTIONS • Top-K cp-queries • Algorithms BNL** and R-BNL** for evaluatingtheSelect-BestandSelectK-Bestoperators • Implementation in the core ofthe RDBMS PostgreSQL • Experimentscomparingthebuilt-in approach withthetranslationinto standard SQL UFU

  12. OUTLINE • TheCPref-SQLLanguage • Related Work and Contributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  13. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 t3 t4 UFU

  14. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 R2 t3 t4 UFU

  15. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 R2 t3 R4 t4 UFU

  16. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 R2 t3 R4 t4 UFU

  17. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 R2 t3 R4 t4 UFU

  18. THE PREFERENCE MODEL t1 t6 R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}]. t2 t5 R2 t3 R4 t4 UFU

  19. CONSISTENCY TEST [Wilson 2004] When a cp-theory is consistent? R1: (Y = 90) > (Y = 80) [{T,D,A}], R2: (D = wa)  (A = cr) > (A = mf) [{T}], R3: (G = c) ^ (Y = 80)  (D = jc) > (D = wa) [{T,A}], R4: (Y = 80)  (G = d) > (G = c) [{T}], R5: (G = c)  (Y=80) > (Y = 90) [{T}] Dependency Graph Local Consistency Y Years D G 90 80 A (x, c, 90, y, z) ? (x, c, 80, y, z) T UFU

  20. CPref-SQL ALGEBRA OPERATORS Tuples that do not have any other tuple over them in the preference hierarchy Select-Best R K K tuples with the less number of tuples above them in the preference hierarchy SelectK-Best R UFU

  21. CPref-SQL ALGEBRA OPERATORS π SELECT < attribute-list > FROM < tables > WHERE < where-conditions (hard conditions) > ACCORDING TO PREFERENCES < preference (soft conditions) > SelectBest / SelectK-Best  |X| ... R1 Rn UFU

  22. ALGORITHMS BNL** E R-BNL** SelectBest SelectK-Best BNL** R-BNL** • FollowsthelinesoftheBlockedNested Loop (BNL) algorithm(BORZSONYI et al. ICDE 2001) • Uses thestructureof a Datalogprogram to compare tuples UFU

  23. ALGORITHMS BNL** E R-BNL** DominanceTest Input:tuples t1, t2 Output:t1 > t2 ort2 > t1 ort1 ~ t2 (incomparable) • Method • Projection • 2. CP-Theory = Datalogprogram • Test t1 > t2 : dataloggoal UFU

  24. ALGORITHMS BNL** E R-BNL** DominanceTest: t1 > t2? t2 > t1? t1 ~ t2? 1. Projection CP-theory A = a1 -> C = c1 > C = c2 B = b1 -> C = c2 > C = c3 R (A, B, C, D) dom(A) = {a1, a2, a3} dom(B) = {b1, b2} dom(C) = {c1, c2, c3} dom(D) = {d1, d2} Projections UFU

  25. ALGORITHMS BNL** E R-BNL** DominanceTest: t1 > t2? t2 > t1? t1 ~ t2? 2. CP-theory = DatalogProgram • Convertsthepreferencerules in a Datalogprogram P • Eachdominancetestof 2 comparabletuples is a goal for P • Findsthe solution (goal) using SLD resolutionmethod UFU

  26. ALGORITHMS BNL** E R-BNL** DominanceTest: t1 > t2? t2 > t1? t1 ~ t2? CP-Theory A = a1 -> C = c1 > C = c2 B = b1 -> C = c2 > C = c3 DatalogProgram pref(x1, y1, z1, x2, y2, z2 ) <- x1 = a1, x2 = a1, y1 = y2, z1 = c1 , z2 = c2 pref(x1, y1, z1, x2, y2, z2 ) <- x1 = x2, y1 = b1, y2, = b1, z1 = c2 , z2 = c3 dom( x1, y1, z1, x2, y2, z2 ) <- pref( x1, y1, z1, x2, y2, z2 ) dom( x1, y1, z1, x2, y2, z2 ) <- pref( x1, y1, z1, x3, y3, z3 ), dom( x3, y3, z3, x2, y2, z2 ) UFU

  27. ALGORITHMS BNL** E R-BNL** DominanceTest: t1 > t2? t2 > t1? t1 ~ t2? 2. CP-theory = DatalogProgram Test (a1, b1, c1) > (a1, b1, c2) Goal dom( a1, b1, c1, a1, b1, c2 ) UFU

  28. ALGORITHM BNL** ProcedureMostPref(r) clearthe in-memory pageWandthetemporarytableF makerthe input repeatthefollowinguntilthe input is empty foreverytuplet in the input iftis dominatedby a tuple in W then ignore t iftdominates some tuples in W theneliminatethedominatedtuplesandinsert t intoW iftis incomparablewithalltuples in W theninserttintoWifthere is room, otherwiseaddt to F insert in SthetuplesofWwhichwereaddedtherewhenFwasempty makeFthe input, clearthetemporarytable return S UFU

  29. ALGORITHM R-BNL** 0   t’  r| t’ > t max { l(t’) | t’ > t } + 1 otherwise l(t) = t1[] t2[t1] t3[t1,t2] t4[t1,t2,t3,t5] t1 t6 0 0 t5[] t5 0 t6[] t2 1 Output S t3 2 t4 3 SelectK-Best ( 4, R ) = { t1, t5, t6, t2} UFU

  30. ALGORITHM R-BNL** ProceduretopK(r) clearthe in-memory pageWandthetemporarytableF makerthe input repeatthefollowinguntilthe input is empty foreverytuplet in the input for every tuplet’ in W iftis dominatedbyt’ thenadd t’ intoMorePref(t) iftdominatest’ thenadd t intoMorePref(t’) inserttintoWifthere is room, otherwiseaddt to F insert in SthetuplesofWwhichwereaddedtherewhenFwasempty makeFthe input, clearthetemporarytable Return S UFU

  31. OUTLINE • TheCPref-SQLLanguage • Related Work and Contributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  32. TOP-K CP-QUERIES IN THE RDBMS • Extension for PostgreSQL 8.4 • Linux Operational System • C Language • Directlyimplemented in thePostgresback-end: built-in approach UFU

  33. TOP-K CP-QUERIES IN THE RDBMS CREATE PREFERENCES Postgres back-end UFU

  34. TOP-K CP-QUERIES IN THE RDBMS ACCORDING TO PREFERENCES Postgres back-end UFU

  35. TOP-K CP-QUERIES IN THE RDBMS UFU

  36. OUTLINE • TheCPref-SQLLanguage • Related Work and Contributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  37. EXPERIMENTAL RESULTS • Benchmark TPC-H (http://www.tpc.org/tpch/) • Synthetic database • Suitewith 22 SQL queries • Queriesadaptation: • insertionofthepreferenceclause • removal of aggregate functions (group by, having, …) • changes on the terms of the WHERE clause • Performance andscalabilityevaluationsofCPref-SQLqueriesandtheirtranslations to SQL • AllCPref-SQLquerycanbetranslatedinto SQL querieswithrecursion UFU

  38. EXPERIMENTAL RESULTS ConversionCPref-SQL <-> SQL CREATE OR REPLACE VIEW Rules (title,genre,years,director,actor,tit,gen,yea,dir,act) AS (SELECT * FROM movies M, movies M1 WHEREM.genre = ‘drama' AND M1.genre = ‘musical' AND M.director = M1.director AND M.years = M1.years) UNION (SELECT * FROM movies M, movies M1 WHERE M.years = 90 AND M1.years = 80 and M.genre = M1.genre) UNION (SELECT * FROM movies M, movies M1 WHERE M.years = 80 and M1.years = 80 and M.genre = ‘drama’ and M1.genre = ‘comedy’ and M.director = M1.director and M.actor = M1.actor); CREATE PREFERENCESmypref FROMmoviesAS genre = ‘drama > genre = ‘musical’ [1,5] AND years = 90 > years = 80 [1,4,5] AND IF years = 80 THEN genre = ‘drama’ > genre = ‘comedy’ [1] UFU

  39. EXPERIMENTAL RESULTS ConversionCPref-SQL <-> SQL WITH RECURSIVERecursion ( tit, gen, yea, dir, act, title, genre, years, director, actor ) AS ( ( SELECT * FROM Rules ) UNION ( SELECTM.title, M.genre, M.years, M.director, M.actor, R.title, R.genre, R.years, R.director, R.actor FROM Rules M, Recursion R WHERE M.tit = R.tit AND M.gen = R.gen AND M.yea = R.yea M.dir = R.dir AND M.act = R.act ) ) SELECT * FROMmovies WHERE genre <> ‘romance’ ACCORDING TO PREFERENCESmypref SELECT * FROM movies WHERE genre <> ‘romance’ EXCEPT SELECT R.title, R.genre, R.years, R.director, R.actor FROM Recursion R; UFU

  40. EXPERIMENTAL RESULTS Performance UFU

  41. EXPERIMENTAL RESULTS Scalability UFU

  42. OUTLINE • TheCPref-SQLLanguage • Related Work and Contributions • EvaluationofPreferenceQueries (cp-queries) • Top-K cp-queries in the RDBMS • Experimental Results • Conclusion and Further Work UFU

  43. CONCLUSION AND FURTHER WORK Top-K cp-queries Algorithms BNL** e R-BNL** Implementation in the core of the PostgreSQL Ongoingresearch: • Development of algorithms under the approach on-top • Supporting to other built-in predicates (>, <, >=,…) Future research: • Incorporating aggregate operations in the CPref-SQL block • Optimization of the execution plan – rules rewrite UFU

  44. fabfernandes@comp.ufu.br, deamo@ufu.br !! FIM !! Evaluation of Conditional Preference Queries Fabíola S. Fernandes, Sandra de Amo UNIVERSIDADE FEDERAL DE UBERLÂNDIA PÓS-GRADUAÇÃO EM CIÊNCIA DA COMPUTAÇÃO

  45. ALGORITHM BNL** Temporary table F Input Table Output page ThePreferred tuples = queryanswer Block of Pages Window W Buffer UFU

  46. ALGORITHM BNL** t1 t6 t2 t5 t3 t4 t1 t6 t1 t2 t5 t3 Buffer W t6 t4 t5 t6 Output S INPUT Temporary table F UFU

  47. ALGORITHM R-BNL** Temporary table F Input Table Output page AlltupleswiththeirrespectiveMorePreflists Block of Pages Window W Buffer UFU

  48. ALGORITHM R-BNL** t1 t6 t2 t5 t3 t4 t1[] t2[] t2[t1] t3[] t3[t1,t2] Buffer W t4[] t4[t1,t2] t5[] t6[] Output S INPUT Temporary table F UFU

  49. ALGORITHM R-BNL** t1 t6 t2 t5 t3 t1[] t4 t2[t1] Buffer W t3[t1,t2] t4[t1,t2] Output S t5[] INPUT t6[] Temporary table F UFU

  50. ALGORITHM R-BNL** 0   t’  r| t’ > t max { l(t’) | t’ > t } + 1 otherwise l(t) = t1[] t2[t1] t3[t1,t2] t4[t1,t2,t3,t5] t1 t6 0 0 t5[] t5 0 t6[] t2 1 Output S t3 2 t4 3 SelectK-Best ( 4, R ) = { t1, t5, t6, t2} UFU

More Related