Rewritings for Answering Queries Using Views

1
Rewritings for Answering Queries Using Views

• Foto Afrati
• National Technical University of Athens

2
Motivation Information Integration

• Databases are not self-contained.

• Various heterogeneous information sources.

• Take information from various sources.

• Mediators Views

• Data warehouses Materialized Views

• Base Relations Do not exist

3
Answering queries using views

• How to answer a query using only the results of
  views? LMSS95
• Many applications
• Data warehouses
• Data integration
• Query optimization

4

• Introduction Ullman,ICDT97-TCS00

emp (E) phone (E,P) office (E,O) mgr (E,M) dept
(E,D)
Base Relations
5

• Introduction

Views
v1(E,P,M) - emp (E), phone (E,P), mgr
(E,M) v2(E,O,D) - emp (E), office (E,O), dept
(E,D) v3(E,P) - emp (E), phone (E,P), dept
(E,toy)
6

• Introduction

Query
Q(P,O) - phone (sally,P), office (sally,O)
Rewriting
answer(P,O) - v1(sally,P,M), v2(sally,O,D)
answer(P,O) - v3(sally,P), v2(sally,O,D)
answer(P,O) ? Q(P,O)
7
Expansion of a rewriting

• Replace the view subgoals by its definitions

answer(P,O) -emp (sally), phone (sally,P), mgr
(sally,M), emp (sally),
office(sally,O), dept (sally,M) answer(P,O)
-emp (sally), phone (sally,P), mgr
(sally,toy), emp (sally),
office(sally,O), dept (sally,M)
8

• Rewriting Queries Using Views

Query Q
answer () - b1(), b2(), .... , bn()

Rewriting
answer () - v1(), v2(), .... , vm()
?

. . .

answer ()-
. . .
bm1(), bm2(), ....
b11(), b12(), .... , b1k()
Expansion
9

• Maximally Contained Rewriting

Given a query Q and view definitions V , P is a
maximally contained rewriting(MCR) if, every
contained rewriting is contained in P.
10
example

• Query conjunctive query
  
• View conjunctive queries

Find MCRs
Bound the number of subgoals in the rewriting
Without constants Number of subgoals in a
minimal rewriting is no more than the number of
subgoals in the query. LMSS95
With constants Number of subgoals in a minimal
rewriting is no more than the sum of the number
of subgoals and the number of variables in the
query. RSU95
11
Query conjunctive query
View conjunctive queries
Bucket Algorithm
Q(X,Y)-a(X,Z),b(Z,Y) v1(X,Z)-a(X,Z),b(Z,Y)
v2(Z,Y)-a(X,Z),b(Z,Y)
Q(X,Y)- v1(X,Z), v2(Z,Y) rewriting
12
Bucket algorithm works only when all view
variables are distinguished Q(X,Y)-a(X,Z),b(Z,Y)
v1(X,Y)-a(X,Z),b(Z,Y) v2(X,Y)-a(X,Z),b(Z,Y)
Q(X,Y)- v1(X,Y), v2(X,Y) not a rewriting
13

• Query conjunctive query
• View conjunctive queries
• MCR is a union of conjunctive queries.
• We know efficient algorithms to derive it.
• Bucket L97
• MiniCon PL00
• SVB M01
• Inverse rules DG97

14
Q(W)-r(W), W?4
...continued

• Expansion
• Q(W)-r(X), s(W,W), X?W, W?X, W?4
• Equivalent to
• Q(W)-r(W), s(W,W), W?4
• .

15

• Query conjunctive query with Left
  Semi-Interval (LSI)
  
• View conjunctive queries with Arithmetic
  Comparisons(AC)

• LSI a?X
• aconstant Xvariable
• AC Y?X or Y?X
• X variable or constant and Yvariable
• or
• Y variable or constant and Xvariable

16
Containment of CQs with Arithmetic Comparisons

• Q1 - Q10, b1
• Q2 - Q20, b2
• Q1 ? Q2 iff b2 gt m1(b1) V V mn(b1)
• where m1, , mn are all the containment
  mappings from Q10 to Q20
• In Q10 , Q20 no variable appears twice
• and no constant appears.

17

• Q1-p(X,4), 4gtX
• Q2 -p(X,4), p(3,X), 4?X
• Q1-p(X,Y), 4gtX, Y4
• Q2 -p(X,Y), p(Z,W), 4?X , Y4, Z3, XW
• ??X? X, Y? Y
• ??X? Z, Y? W
• 4?X ? Y4 ? Z3 ? XW ?
• (4gtX ? Y4)?(4gtZ ? W4)

Q2?Q1 two mappings are needed to prove it
18
Complexity of query containment

• CQ NP CM77

P 2

• CQAC ? Kl88

• CQLSI NP Kl88
• CQSI1- CQACNP ALM02
• CQLSI- CQACNP ALM02, ALM04
• CQLSIPI- CQACNP ALM04

19
Query conjunctive query with Left
Semi-Interval (LSI)
View conjunctive queries with Arithmetic
Comparisons(AC)

• Bound the number of subgoals in the rewriting
• MCR is a union of conjunctive queries with
  arithmetic comparisons. ALM02
• Each item in the buckets is a view subgoal AND
  some comparison predicates.

20

• Query / Views MCR
• CQ / CQ union of CQs ?
• CQ? / CQ co-NP-complete
• CQLSI / CQAC union of CQLSIs ?
• CQSI1 / CQSI Datalog with SIs

? Shared Variable Bucket Algorithm
21

• Query conjunctive query with Left
  Semi-Interval and
• at most one Right
  Semi-Interval (SI1)
  
• View conjunctive queries with
  Semi-Interval (SI)
• Example
• Q(W)-r(X,Y,W,Z), 4 ? W, 9 ? X, 8 ? Y, Z ? 4
• V(W)-r(X,Y,W,Z), p(X1,X2,X3)
• 4 ? W, 9 ? X, 8 ? Y, Z ? 4, X1 ? 9, X2 ? 3,
  X3 ? 4

22

• Query conjunctive query with Left
  Semi-Interval and at most one Right Semi-Interval
  (SI1)
• View conjunctive queries with Left
  Semi-Interval and Right Semi-Interval (SI)
• No strict inequalities allowed.
• Containment of CQ-SI into CQ-SI1 reduces
• to containment of a conjunctive query into
• a Datalog program. ALM02
• MCR is a datalog program with SIs. ALM02

23

• Contained Rewriting

• Given a query Q and view definitions V ,
• P is a contained rewriting if, for every view
  instance IV ,
• P(IV ) ? Q(D), for any database D such that
• IV ?V (D) .

24
Answering Queries using Views

• Given Query Q
• View definitions
• View instance IV
• Question Find all certain answers
• Definition A tuple t is a certain answer if t
  is an element of Q(D) for any D such that IV ?V
  (D)

25

• Query conjunctive query with ?
• View conjunctive queries

• Whenever an MCR exists it derives
• all certain answers. AD98,DG98
• Deciding whether a tuple is certain answer is
  co-NP complete AD98
• Hence there is no MCR in polynomially evaluated
  languages.

26
Other interesting cases

• Views are unions of conjunctive queries
• Views have binding patterns RSU95
• In the presence of functional dependencies.
• Regular Path Queries CGLV99

27
Thank you
28
Equivalent Rewriting

• Query
• Q(S,C)-car (M,smith), loc (smith,C), part
  (S,M,C)
• Views
• v1(M,D,C)- car (M,D), loc (D,C)
• v2(S,M,C)- part (S,M,C)
• Rewriting
• Q(S,C)- v1(M,smith,C), v2(S,smith,C)

29
views
database
CWA
V(D)
D
V
D
subset of V
?V(D)
OWA
30
Equivalent Rewritings under the CWA

• P2
• Q(S,C)- v1(M, smith,C),v2(S, smith,C)
• P1
• Q(S,C)- v1(M, smith,C1),v2(S, smith,C),
• v1(M1, smith,C)

under the CWA P2 is equivalent to P1

• P2 is contained in P1

31

• Query conjunctive query
  
• View conjunctive queries

ERs under CWA
Under the CWA, all equivalent rewritings produce
the same answers.

• Find ERs with minimum number of subgoals
• Idea
• Find all candidate view subgoals.
• For each candidate,
• find what query subgoals are covered.
• Combine as in bucket.
• ALU01

32
Query conjunctive query with arithmetic
comparisons View
conjunctive queries with arithmetic comparisons

• Q(W)-r(W), W?4
• V1(Y,Z)- r(X), s(Y,Z), X?Y, Z?X
• V2(Y,Z)- r(X), s(Y,Z), X?Y, Z?X
• Rewriting
• Q(W)- V1(W,W), W ?4