- 45 Views
- Uploaded on
- Presentation posted in: General

Evaluation of Conditional Preference Queries

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

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