Chapter 2 manipulating query expressions
Download
1 / 94

CHAPTER 2: MANIPULATING QUERY EXPRESSIONS - PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on

CHAPTER 2: MANIPULATING QUERY EXPRESSIONS. PRINCIPLES OF DATA INTEGRATION. ANHAI DOAN ALON HALEVY ZACHARY IVES. Introduction. How does a data integration system decide which sources are relevant to a query? Which are redundant? How to combine multiple sources to answer a query?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CHAPTER 2: MANIPULATING QUERY EXPRESSIONS' - justin


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
Chapter 2 manipulating query expressions

CHAPTER 2: MANIPULATING QUERY EXPRESSIONS

PRINCIPLES OF

DATA INTEGRATION

ANHAI DOAN ALON HALEVY ZACHARY IVES


Introduction
Introduction

  • How does a data integration system decide which sources are relevant to a query? Which are redundant? How to combine multiple sources to answer a query?

  • Answer: by reasoning about the contents of data sources.

    • Data sources are often described by queries / views.

  • This chapter describes the fundamental tools for manipulating query expressions and reasoning about them.


Outline
Outline

  • Review of basic database concepts

  • Query unfolding

  • Query containment

  • Answering queries using views


Basic database concepts
Basic Database Concepts

  • Relational data model

  • Integrity constraints

  • Queries and answers

  • Conjunctive queries

  • Datalog


Relational terminology
Relational Terminology

Relational schemas

  • Tables, attributes

    Relation instances

  • Sets (or multi-sets) of tuples

    Integrity constraints

  • Keys, foreign keys, inclusion dependencies


Chapter 2 manipulating query expressions

Attribute names

Table/relation name

Product

Tuples or rows


Sql very basic
SQL (very basic)

Interview:

candidate, date, recruiter, hireDecision, grade

EmployeePerf:

empID, name, reviewQuarter, grade, reviewer

select recruiter, candidate

fromInterview, EmployeePerf

where recruiter=name AND

grade < 2.5


Query answers
Query Answers

  • Q(D): the set (or multi-set) of rows resulting from applying the query Q on the database D.

  • Unless otherwise stated, we will consider sets rather than multi-sets.


Sql w aggregation
SQL (w/aggregation)

EmployeePerf:

empID, name, reviewQuarter, grade, reviewer

select reviewer, Avg(grade)

fromEmployeePerf

where reviewQuarter=“1/2007”


Integrity constraints keys
Integrity Constraints (Keys)

  • A key is a set of columns that uniquely determine a row in the database:

    • There do not exist two tuples, t1 and t2 such that t1≠ t2 and t1 and t2have the same values for the key columns.

    • (EmpID,reviewQuarter) is a key for EmployeePerf


Integrity constraints functional dependencies
Integrity Constraints (Functional Dependencies)

  • A set of attribute A functionally determines a set of attributes B if: whenever , t1 and t2 agree on the values of A, they must also agree on the values of B.

  • For example, (EmpID, reviewQuarter) functionally determine (grade).

  • Note: a key dependency is a functional dependency where the key determines all the other columns.


Integrity constraints foreign keys
Integrity Constraints (Foreign Keys)

  • Given table T with key B and table S with key A: A is a foreign key of B in T if whenever a S has a row where the value of A is v, then T must have a row where the value of B is v.

  • Example: the empID attribute of EmployeePerf is a foreign key for attribute emp of Employee.


General integrity constraints
General Integrity Constraints

Tuple generating dependencies (TGD’s)

Equality generating dependencies (EGD’s): right hand side contains only equalities.

Exercise: express the previous constraints using general integrity constraints.


Conjunctive queries
Conjunctive Queries

Q(X,T) :-

Interview(X,D,Y,H,F), EmployeePerf(E,Y,T,W,Z),

W < 2.5.

Joins are expressed with multiple occurrences of the same variable

select recruiter, candidate

fromInterview, EmployeePerf

where recruiter=name AND

grade < 2.5


Conjunctive queries interpreted predicates
Conjunctive Queries (interpreted predicates)

Q(X,T) :-

Interview(X,D,Y,H,F), EmployeePerf(E,Y,T,W,Z),

W < 2.5.

Interpreted (or comparison) predicates. Variables must also appear in regular atoms.

select recruiter, candidate

fromInterview, EmployeePerf

where recruiter=name AND

grade < 2.5


Conjunctive queries negated subgoals
Conjunctive Queries (negated subgoals)

Q(X,T) :-

Interview(X,D,Y,H,F), EmployeePerf(E,Y,T,W,Z), OfferMade(X, date).

Safety: every head variable must appear in a positive subgoal.


Unions of conjunctive queries
Unions of Conjunctive Queries

Multiple rules with the same head predicate

express a union

Q(R,C) :-

Interview(X,D,Y,H,F), EmployeePerf(E,Y,T,W,Z),

W < 2.5.

Q(R,C) :-

Interview(X,D,Y,H,F), EmployeePerf(E,Y,T,W,Z),

Manager(y), W > 3.9.


Datalog recursion
Datalog (recursion)

Database: edge(X,Y) – describing edges in a graph.

Recursive query finds all paths in the graph.

Path(X,Y) :- edge(X,Y)

Path(X,Y) :- edge(X,Z), path(Z,Y)


Outline1
Outline

  • Review of basic database concepts

  • Query unfolding

  • Query containment

  • Answering queries using views


Query unfolding
Query Unfolding

  • Query composition is an important mechanism for writing complex queries.

    • Build query from views in a bottom up fashion.

  • Query unfolding “unwinds” query composition.

  • Important for:

    • Comparing between queries expressed with views

    • Query optimization (to examine all possible join orders)

    • Unfolding may even discover that the composition of two satisfiable queries is unsatisfiable! (exercise: find such an example).


Query unfolding example
Query Unfolding Example

The unfolding of Q3 is:


Query unfolding algorithm
Query Unfolding Algorithm

  • Find a subgoal p(X1 ,…,Xn) such that p is defined by a rule r.

  • Unify p(X1 ,…,Xn) with the head of r.

  • Replace p(X1 ,…,Xn) with the result of applying the unifier to the subgoals of r (use fresh variables for the existential variables of r).

  • Iterate until no unifications can be found.

  • If p is defined by a union of r1, …, rn, create n rules, for each of the r’s.


Query unfolding summary
Query Unfolding Summary

  • Unfolding does not necessarily create a more efficient query!

    • Just lets the optimizer explore more evaluation strategies.

    • Unfolding is the opposite of rewriting queries using views (see later).

  • The size of the resulting query can grow exponentially (exercise: show how).


Outline2
Outline

  • Review of basic database concepts

  • Query unfolding

  • Query containment

  • Answering queries using views


Query containment motivation 1
Query Containment: Motivation (1)

Intuitively, the unfolding of Q3 is equivalent to Q4:

How can we justify this intuition formally?


Query containment motivation 2
Query Containment: Motivation (2)

Furthermore, the query Q5 that requires going through two hubs is contained in Q’3

We need algorithms to detect these relationships.


Query containment and equivalence definitions
Query Containment and Equivalence: Definitions

Query Q1contained in query Q2 if

for every database D

Q1(D)  Q2(D)

Query Q1 is equivalent to query Q2 if

Q1(D)  Q2(D) and Q2(D)  Q1(D)

Note: containment and equivalence are properties of the queries, not of the database!


Notation apology
Notation Apology

  • Powerpoint does not have square

  • The book uses the square notation for containment, but the slides use the rounded version.




Why do we need it
Why Do We Need It?

  • When sources are described as views, we use containment to compare among them.

  • If we can remove sub-goals from a query, we can evaluate it more efficiently.

  • Actually, containment arises everywhere…


Reconsidering the example
Reconsidering the Example

Relations:Flight(source, destination)

Hub(city)

Views:

Q1(X,Y) :- Flight(X,Z), Hub(Z), Flight(Z,Y)

Q2(X,Y) :- Hub(Z), Flight(Z,X), Flight(X,Y)

Query:

Q3(X,Z) :- Q1(X,Y), Q2(Y,Z)

Unfolding:

Q’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Hub(W), Flight(W,Y), Flight(Y,Z)


Remove redundant subgoals
Remove Redundant Subgoals

Redundant subgoals?

Q’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Hub(W), Flight(W,Y), Flight(Y,Z)

Q’’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Flight(Y,Z)

Is Q’’3 equivalent to Q’3?

Q’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y)

Hub(W), Flight(W,Y), Flight(Y,Z)


Containment conjunctive queries
Containment: Conjunctive Queries

  • No interpreted predicates (,)

  • or negation for now.

  • Recall semantics:

    • if  maps the body subgoals to tuples in D

    • then, is an answer.


Containment mappings
Containment Mappings

  • : Vars(Q1) Vars(Q2)

  • is a containment mapping if:

and


Example containment mapping
Example Containment Mapping

Q’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Hub(W), Flight(W,Y), Flight(Y,Z)

Q’’3(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Flight(Y,Z)

Identity mapping on all variables, except:


Theorem chandra and merlin 1977
Theorem[Chandra and Merlin, 1977]

Q1 contains Q2 if and only if there is a

containment mapping from Q1 to Q2.

Deciding whether Q1 contains Q2

is NP-complete.


Proof sketch
Proof (sketch)

(if): assume  exists:

Let t be an answer to Q2 over database D

(i.e., there is a mapping  from Vars(Q2) to D)

Consider  .


Proof only if direction
Proof (only-if direction)

Assume containment holds.

Consider the frozen database D’ of Q2.

(variables of Q2 are constants in D’).

The mapping from Q1 to D’: containment map.


Frozen database
Frozen Database

Q(X,Z) :- Flight(X,U), Hub(U), Flight(U,Y),

Flight(Y,Z)

Frozen database for Q(X,Z) is:

Flight: {(X,U), (U,Y), (Y,Z)}

Hub: {(U)}


Two views of this result
Two Views of this Result

  • Variable mapping:

    • a condition on variable mappings that guarantees containment

  • Representative (canonical) databases:

    • We found a (single) database that would offer a counter example if there was one

  • Containment results typically fall into one of these two classes.


Union of conjunctive queries
Union of Conjunctive Queries

Theorem: a CQ is contained in a union of

CQ’s if and only if it is contained in one of

the conjunctive queries.

Corollary: containment is still NP-complete.


Cq s with comparison predicates
CQ’s with Comparison Predicates

A tweak on containment mappings provides a sufficient condition:

  • : Vars(Q1)  Vars(Q2):

and



Containment mappings are not sufficient
Containment Mappings are not Sufficient

No containment mapping, but


Query refinements
Query Refinements

We consider the refinements of Q2

The red containment mapping applies for the first refinement and the blue to the second.


Constructing query refinements
Constructing Query Refinements

  • Consider all complete orderings of the variables and constants in the query.

  • For each complete ordering, create a conjunctive query.

  • The result is a union of conjunctive queries.


Complete orderings
Complete Orderings

Given

a conjunction C of interpreted atoms over

a set of variables X1,…,Xn

a set of constants a1,…,am

CT is a complete ordering if: CT |= C, and


Query refinements1
Query Refinements

Let CT be a complete ordering of C1

Then:

is a refinement of Q1


Theorem queries with interpreted predicates klug 88 van der meyden 92
Theorem: [Queries with Interpreted Predicates][Klug, 88, van der Meyden, 92]

Q1 contains Q2 if and only if there is a

containment mapping from Q1 to every

refinement of Q2.

Deciding whether Q1 contains Q2

is

In practice, you can do much better (see CQIPContainment Algorithm in Sec. 2.3.4


Queries with negation
Queries with Negation

Queries assumed safe:

every head variable appears in

a positive sub-goal in the body.

Revised containment mappings:

map negative subgoals in Q1

to negative subgoals in Q2.

 Sufficient condition, but not necessary.



Theorem queries with negation
Theorem [Queries with Negation]

B: total number of variables and

constants in Q2.

Q1 contains Q2 if and only if

Q1(D)Q2(D) for all databases D with

at most B constants.

Deciding whether Q1 contains Q2

is


Bag semantics
Bag Semantics

Set semantics: {(SF, Anchorage)}

Bags: {(SF, Anchorage), (SF, Anchorage)}


Theorem conjunctive queries bag semantics
Theorem [Conjunctive Queries, Bag Semantics]

Q1 is equivalent to Q2 if and only if

there is a 1-1 containment mapping.

Trivial example on non-equivalence:

Query containment?


Grouping and aggregation
Grouping and Aggregation

  • Count queries are sensitive to multiplicity

  • Max queries are not sensitive to multiplicity

  • and many more results (see Section 2.3.6).


Outline3
Outline

  • Review of basic database concepts

  • Query unfolding

  • Query containment

  • Answering queries using views


Motivating example part 1
Motivating Example (Part 1)

Movie(ID,title,year,genre)

Director(ID,director)

Actor(ID, actor)

Containment is enough to show that V1 can be used to answer Q.


Motivating example part 2
Motivating Example (Part 2)

Containment does not hold, but intuitively, V2 and V3 are useful for answering Q.

How do we express that intuition?

Answering queries using views!


Problem definition
Problem Definition

Input: Query Q

View definitions: V1,…,Vn

A rewriting: a query Q’ that refers only

to the views and interpreted predicates

An equivalent rewriting of Q using V1,…,Vn:

a rewriting Q’, such that Q’  Q.


Motivating example part 3
Motivating Example (Part 3)

Movie(ID,title,year,genre)

Director(ID,director)

Actor(ID, actor)

maximally-contained rewriting


Maximally contained rewritings
Maximally-Contained Rewritings

Input: Query Q

Rewriting query language L

View definitions: V1,…,Vn

Q’ is a maximally-contained rewriting of

Q given V1,…,Vn and L if:

1. Q’  L,

2. Q’  Q, and

3. there is no Q’’ in L such that

Q’’  Q and Q’ Q’’


Motivation in words
Motivation (in words)

  • LAV-style data integration

    • Need maximally-contained rewritings

  • Query optimization

    • Need equivalent rewritings

    • Implemented in most commercial DBMS

  • Physical database design

    • Describe storage structures as views



Algorithms for answering queries using views
Algorithms for answering queries using views

  • Step 1: we’ll bound the space of possible query rewritings we need to consider (no interpreted predicates)

  • Step 2: we’ll find efficient methods for searching the space of rewritings

    • Bucket Algorithm, MiniCon Algorithm

  • Step 2b: we consider “logical approaches” to the problem:

    • The Inverse-Rules Algorithm

  • We’ll consider interpreted predicates, …


Bounding the rewriting length

Query:

Rewriting:

Expansion:

Boundingthe Rewriting Length

Theorem: if there is an equivalent erwriting, there is one with at most n subgoals.

Proof: Only n subgoals in Q can contribute to the image of the containment mapping 


Complexity result lmss 1995
Complexity Result[LMSS, 1995]

  • Applies to queries with no interpreted predicates.

  • Finding an equivalent rewriting of a query using views is NP-complete

    • Need only consider rewritings of query length or less.

  • Maximally-contained rewriting:

    • Union of all conjunctive rewritings of length n or less.


The bucket algorithm
The Bucket Algorithm

Key idea:

  • Create a bucket for each subgoal g in the query.

  • The bucket contains view atoms that contribute to g.

  • Create rewritings from the Cartesian product of the buckets.


Bucket algorithm in action
Bucket Algorithm in Action

View atoms that can contribute to Movie:

V1(ID,year), V2(ID,A’), V4(ID,D’,year)


The buckets and c artesian product
The Buckets and Cartesian product

Consider first candidate rewriting: first V1 subgoal is redundant, and V1 and V4 are mutually exclusive.



The bucket algorithm summary
The Bucket Algorithm: Summary

  • Cuts down the number of rewriting that need to be considered, especially if views apply many interpreted predicates.

  • The search space can still be large because the algorithm does not consider the interactions between different subgoals.

    • See next example.


The minicon algorithm
The MiniConAlgorithm

Intuition: The variable I is not in the head of V5,

hence V5 cannot be used in a rewriting.

MiniCon discards this option early on, while the Bucket algorithm does not notice the interaction.


Mincon algorithm steps
MinCon Algorithm Steps

  • Create MiniCon descriptions (MCDs):

    • Homomorphism on view heads

    • Each MCD covers a set of subgoals in the query with a set of subgoals in a view

  • Combination step:

    • Any set of MCDs that covers the query subgoals (without overlap) is a rewriting

    • No need for an additional containment check!


Minicon descriptions mcds an atomic fragment of the ultimate containment mapping
MiniCon Descriptions (MCDs)An atomic fragment of the ultimate containment mapping

MCD:

mapping:

covered subgoals of Q: {2,3}


Mcds detail 1
MCDs: Detail 1

Need to specialize the view first:

MCD:

mapping:

covered subgoals of Q: {2,3}


Mcds detail 2
MCDs: Detail 2

Note: the third subgoal of the view is not included in the MCD.

MCD:

mapping:

covered subgoals of Q still: {2,3}


Inverse rules algorithm
Inverse-Rules Algorithm

  • A “logical” approach to AQUV

  • Produces maximally-contained rewriting in polynomial time

    • To check whether the rewriting is equivalent to the query, you still need a containment check.

  • Conceptually simple and elegant

    • Depending on your comfort with Skolem functions…


Inverse rules by example
Inverse Rules by Example

Given the following view:

And the following tuple in V7:

V7(79,Manhattan,1979,Comedy)

Then we can infer the tuple:

Movie(79,Manhattan,1979,Comedy)

Hence, the following ‘rule’ is sound:

IN1: Movie(I,T,Y,G) :- V7(I,T,Y,G)


Skolem functions
Skolem Functions

Now suppose we have the tuple

V7(79,Manhattan,1979,Comedy)

Then we can infer that there exists somedirector. Hence, the following rules hold (note that they both use the same Skolem function):

IN2: Director(I,f1(I,T,Y,G)):- V7(I,T,Y,G)

IN3: Actor(I,f1(I,T,Y,G)):- V7(I,T,Y,G)


Inverse rules in general rewriting inverse rules query
Inverse Rules in GeneralRewriting = Inverse Rules + Query

Given Q2, the rewriting would include:

IN1, IN2, IN3, Q2.

Given input: V7(79,Manhattan,1979,Comedy)

Inverse rules produce:

Movie(79,Manhattan,1979,Comedy)

Director(79,f1(79,Manhattan,1979,Comedy))

Actor(79,f1(79,Manhattan,1979,Comedy))

Movie(Manhattan,1979,Comedy)

(the last tuple is produced by applying Q2).


Comparing algorithms
Comparing Algorithms

  • Bucket algorithm:

    • Good if there are many interpreted predicates

    • Requires containment check. Cartesian product can be big

  • MiniCon:

    • Good at detecting interactions between subgoals


Algorithm comparison continued
Algorithm Comparison (Continued)

  • Inverse-rules algorithm:

    • Conceptually clean

    • Can be used in other contexts (see later)

    • But may produce inefficient rewritings because it “undoes” the joins in the views (see next slide)

  • Experiments show MiniCon is most efficient.

  • Recently: MiniCon improved by using constraint satisfaction techniques.


Inverse rules inefficiency example
Inverse Rules Inefficiency Example

Now we need to re-compute the join…


View based query answering
View-Based Query Answering

  • Maximally-contained rewritings are parameterized by query language.

  • More general question:

    • Given a set of view definitions, view instances and a query, what are all the answers we can find?

  • We introduce certain answers as a mechanism for providing a formal answer.


View instances possible db s
View Instances = Possible DB’s

Consider the two views:

And suppose the extensions of the views are:

V8: {Allen, Copolla}

V9: {Keaton, Pacino}


Possible databases
Possible Databases

There are multiple databases that satisfy the above view definitions: (we ignore the first argument of Movie below)

DB1. {(Allen, Keaton), (Coppola, Pacino)}

DB2. {(Allen, Pacino), (Coppola, Keaton)}

If we ask whether Allen directed a movie in which Keaton acted, we can’t be sure.

Certain answers are those true in all databases that are consistent with the views and their extensions.


Certain answers formal definition open world assumption
Certain Answers: Formal Definition [Open-world Assumption]

  • Given:

    • Views: V1,…,Vn

    • View extensions v1,…vn

    • A query Q

  • A tuple t is a certain answer to Q under the open-world assumption if t  Q(D) for all databases D such that:

    • Vi(D)  vi for all i.


Certain answers closed world assumption
Certain Answers[Closed-world Assumption]

  • Given:

    • Views: V1,…,Vn

    • View extensions v1,…vn

    • A query Q

  • A tuple t is a certain answer to Q under the open-world assumption if t  Q(D) for all databases D such that:

    • Vi(D) = vi for all i.


Certain answers example
Certain Answers: Example

V8: {Allen}

V9: {Keaton}

Under closed-world assumption:

single DB possible  (Allen, Keaton)

Under open-world assumption:

no certain answers.


The good news
The Good News

  • The MiniCon and Inverse-rules algorithms produce all certain answers

    • Assuming no interpreted predicates in the query (ok to have them in the views)

    • Under open-world assumption

    • Corollary: they produce a maximally-contained rewriting


In other news
In Other News…

  • Under closed-world assumption finding all certain answers is co-NP hard!

Proof: encode a graph -- G = (V,E)

q has a certain tuple iff G is not 3-colorable


Interpreted predicates
Interpreted Predicates

  • In the views: no problem (all results hold)

  • In the query Q:

    • If the query contains interpreted predicates, finding all certain answers is co-NP-hard even under open-world assumption

    • Proof: reduction to CNF.


Summary of chapter 2
Summary of Chapter 2

  • Query containment and answering queries using views are fundamental tools in our arsenal.

  • In general, they are NP-complete (or worse), but in practice they are not the bottleneck.

  • Certain answers are the formalism we use to model answers in data integration systems:

    • They capture our knowledge about what tuples must be in the instance of the mediated schema.