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

Evaluation of Conditional Preference Queries PowerPoint PPT Presentation


  • 50 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

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work andContributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

THE CPref-SQL LANGUAGE

Filtertheanswers to queriesaccording to userpreferences

SELECTtitle

FROMmovies

WHEREgenre <> ‘romance’

ACCORDING TO PREFERENCESMyPrefs, 4

UFU


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work andContributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

RELATED WORK AND CONTRIBUTIONS

UFU


Evaluation of conditional preference queries

RELATED WORK AND CONTRIBUTIONS

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

CREATE PREFERENCES

Postgres

back-end

UFU


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

ACCORDING

TO

PREFERENCES

Postgres

back-end

UFU


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

UFU


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

EXPERIMENTAL RESULTS

Performance

UFU


Evaluation of conditional preference queries

EXPERIMENTAL RESULTS

Scalability

UFU


Evaluation of conditional preference queries

OUTLINE

  • TheCPref-SQLLanguage

  • Related Work and Contributions

  • EvaluationofPreferenceQueries (cp-queries)

  • Top-K cp-queries in the RDBMS

  • Experimental Results

  • Conclusion and Further Work

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries1

[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


Evaluation of conditional preference queries

ALGORITHM BNL**

Temporary table F

Input Table

Output page

ThePreferred

tuples = queryanswer

Block of Pages

Window W

Buffer

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

ALGORITHM R-BNL**

Temporary table F

Input Table

Output page

AlltupleswiththeirrespectiveMorePreflists

Block of Pages

Window W

Buffer

UFU


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

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


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

CreatePreferences

  • Catalogstructure:

    • Eachcolumn is anattributeoftherelationmovies

    • Eachrowrepresents a rule

UFU


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

CreatePreferences

UFU


Evaluation of conditional preference queries

TOP-K CP-QUERIES IN THE RDBMS

Insertionoftheoperators in thequeryprocessor

UFU


  • Login