March 22, 2006

Coverage: [DBCB] Chapter 20, pp. 1047-1062

Introduction to Information Integration

a problem: creating an online bookshop
- using data from multiple sources: half price books Dayton, dark horse (Yellow Springs), bn.com, borders.com, amazon.com, half.com, and so on
how can we use these heterogeneous data sources (e.g., DBs, the web, software repositories, cross-indices) in a unified way?
three main approaches
- federation
  - data sources remain independent, but any source can call upon any other
- warehousing
  - maintain a copy of data from each source in a single DB (the data warehouse)
    - data sometimes preprocessed
- mediation
  - a single entity (the mediator) governs the communication between the heterogeneous parties; translates queries, filters, and synthesizes results
applications
- there are numerous
- primary application is OLAP (Online Analytic Processing)
  - discover patterns or trends of importance to the business
  - queries that analyze the business
    - sometimes called decision support queries
data cube
- a DB architecture used to organization integrated, and aggregated data
a new paradigm for information integration/exchange/automation is emerging on the web
- called the semantic web

Semantic Web

evolution of the internet
- e-mail: human-human communication
- web: human-computer communication
- semantic web: computer-computer communication
a W3C initiative
goal is automation
relies on ontologies and standards
OWL (Web Ontology Language), RDF (Resource Description Framework)
web services, web APIs
- Amazon.com exports their APIs
we will study this more toward the end of the course

Issues in Information Integration

example

D1:

Books (isbn, title, author, publisher, edition, soft, large_print)

D2:

Novel (isbn, title, author, publisher)
Textbook (isbn, title, author, publisher, edition)
Options (isbn, option)

heterogeneous data sources
- same data, different schema
different attribute names
type differences
- int vs. string
value differences
semantic differences
- D2 might classify cookbooks using Textbooks
missing values
- use NULLs
- semistructured data models
  - typically graph-oriented

Federated Databases

courtesy

requires a great deal of communication overhead
- n databases, n(n-1) communication, translation components
embedded SQL, remote queries

Data Warehouses

courtesy

main idea: global shared schema
users can query the warehouse, but cannot update it
approaches to warehouse construction
- reconstruct from scratch (every night or less frequent)
  - warehouse unavailable during reconstruction
  - easy, but expensive
- incremental update
  - more complex than starting from scratch
    - analogy to backing up a hard disk with rsync
  - updates typically will involve small amounts of data
  - helpful when warehouse is large
- in situ approach
  - requires too much communication for all but small warehouses whose data sources change slowly
little programs called wrappers (wrap a data source) help maintain the shared schema
example:
- consider data sources D1 and D2 introduced above
- BooksWarehouse(isbn, title, author, soft, dealer)
- copy query:
```
INSERT INTO BooksWarehouse(isbn, title, author, soft, dealer)
   SELECT isbn, title, author, soft, 'dealer1'
   FROM Books;
```
- no combiner necessary here

Mediation

courtesy

idea is to provide a virtual view (or a collection of virtual views) of a shared schema
- contrast with warehouse which is materialized
centralized approach; communication facilitated by a software component called a mediator
- contrast with the decentralized approach used in federation
a mediator acts as a combiner as well as an interface to the outside world
users query underlying data sources through the mediator
example:
- mediator forwards this query to each of its wrappers (e.g., W1 and W2)
  - sometimes need not forward to all wrappers
    - e.g., `Is a used copy of Catch-22 available?' once one is found the query is answered

Wrappers

primarily used in mediation
sometimes also called extractors
not always SQL; can be, e.g., Python code to fill out a form on a webpage
how to construct a wrapper?
- as a set of templates for query patterns
  - classify queries into templates (parameterized queries)
  - T => Q
    - T is template
    - Q is query
    - mediator facilitates the translation
  - can require up to 2^n templates, where n is the number of attributes
  - example:
```
SELECT *
FROM BooksMediator
WHERE author = '$a';
   =>
SELECT isbn, title, author, soft, 'dealer1'
FROM Books
WHERE author = '$a';
```
- wrapper generation
  - akin to YACC
  - set of templates (a specification) => wrapper
    - wrapper generator facilitates the translation

Filters

a way to deal with the explosion in the number of templates a wrapper might require

consider that a wrapper may not have the following template (adapted from [DBCB], p. 1060):

SELECT *
FROM BooksMediator
WHERE author = '$a' AND title = '$t';
   =>
SELECT isbn, softcover, used, 'dealer1'
FROM Books
WHERE author = '$a' AND title = '$t';

so then how can we answer the following query? (adapted from [DBCB] Example 20.6, p. 1061):
```
SELECT *
FROM BooksMediator
WHERE author = 'Orwell' AND title = 'Animal Farm';
```
store all the books by Orwell in a temporary relation at the wrapper and filter it there (again, adapted from [DBCB] Example 20.6, p. 1061):
```
SELECT *
FROM TempBooks
WHERE title = 'Animal Farm';
```

What Else Can We Do at the Wrapper-level?

projections, joins, aggregation, and so on
for example, the wrapper from the previous example could project out the attributes author and title as they are implicit in the query (adapted from [DBCB] Example 20.7, p. 1062):
```
SELECT isbn, softcover, used
FROM TempBooks
WHERE title = 'Animal Farm';
```
possible so far as the template returns a superset of the required data to the wrapper

References

Database Systems: The Complete Book