Evaluation of conditional preference queries
Sponsored Links
This presentation is the property of its rightful owner.
1 / 53

Evaluation of Conditional Preference Queries PowerPoint PPT Presentation


  • 54 Views
  • Uploaded on
  • Presentation posted in: General

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 :

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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( 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


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


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work andContributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


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


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


THE CPref-SQL LANGUAGE

Filtertheanswers to queriesaccording to userpreferences

SELECTtitle

FROMmovies

WHEREgenre <> ‘romance’

ACCORDING TO PREFERENCESMyPrefs, 4

UFU


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work andContributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


RELATED WORK AND CONTRIBUTIONS

UFU


RELATED WORK AND CONTRIBUTIONS

UFU


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


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


ALGORITHM R-BNL**

0  t’  r| t’ > t

max { l(t’) | t’ > t } + 1otherwise

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


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


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


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


TOP-K CP-QUERIES IN THE RDBMS

CREATE PREFERENCES

Postgres

back-end

UFU


TOP-K CP-QUERIES IN THE RDBMS

ACCORDING

TO

PREFERENCES

Postgres

back-end

UFU


TOP-K CP-QUERIES IN THE RDBMS

UFU


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


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


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


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


EXPERIMENTAL RESULTS

Performance

UFU


EXPERIMENTAL RESULTS

Scalability

UFU


OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


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


[email protected], [email protected]

!! 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


ALGORITHM BNL**

Temporary table F

Input Table

Output page

ThePreferred

tuples = queryanswer

Block of Pages

Window W

Buffer

UFU


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


ALGORITHM R-BNL**

Temporary table F

Input Table

Output page

AlltupleswiththeirrespectiveMorePreflists

Block of Pages

Window W

Buffer

UFU


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


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


ALGORITHM R-BNL**

0  t’  r| t’ > t

max { l(t’) | t’ > t } + 1otherwise

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


TOP-K CP-QUERIES IN THE RDBMS

CreatePreferences

  • Catalogstructure:

    • Eachcolumn is anattributeoftherelationmovies

    • Eachrowrepresents a rule

UFU


TOP-K CP-QUERIES IN THE RDBMS

CreatePreferences

UFU


TOP-K CP-QUERIES IN THE RDBMS

Insertionoftheoperators in thequeryprocessor

UFU


  • Login