An annotation management system for relational databases

Abstract

We present an annotation management system for relational databases. In this system, every piece of data in a relation is assumed to have zero or more annotations associated with it and annotations are propagated along, from the source to the output, as data is being transformed through a query. Such an annotation management system is important for understanding the provenance and quality of data, especially in applications that deal with integration of scientific and biological data.We present an extension, pSQL, of a fragment of SQL that has three different types of annotation propagation schemes, each useful for different purposes. The default scheme propagates annotations according to where data is copied from. The default-all scheme propagates annotations according to where data is copied from among all equivalent formulations of a given query. The custom scheme allows a user to specify how annotations should propagate. We present a storage scheme for the annotations and describe algorithms for translating a pSQL query under each propagation scheme into one or more SQL queries that would correctly retrieve the relevant annotations according to the specified propagation scheme. For the default-all scheme, we also show how we generate finitely many queries that can simulate the annotation propagation behavior of the set of all equivalent queries, which is possibly infinite. The algorithms are implemented and the feasibility of the system is demonstrated by a set of experiments that we have conducted.

Provenance management in curated databases

“…Most curators believe that additional record keeping is needed to record where the data comes fromits provenance. There has been some examination [2,8,16,22,24] of provenance issues in data warehouses; that is, views of some underlying collection of data. But curated databases are not warehouses: they are manually constructed by highly skilled scientists; they are not computed automatically from existing data sets; they are not views.…”

“…Of course, it is also important to be able to propagate provenance and other annotations through more traditional relational database queries (or extensions to complex objects or XML), and to support provenance tracking for bulk updates. A fair amount of work has already been done on provenance and annotation for relational queries (including [2,6,8,16,22,24]); however, issues such as how to assign provenance information to the results of joins and unions which may "fuse" data from different sources remain ill-understood. One popular approach (used in [2,22]) to dealing with joins and unions that appears to be consistent with our framework is to interpret data that has been "fused" as "coming from" both source locations.…”

MONDRIAN: Annotating and Querying Databases through Colors and Blocks

“…Bhagwat et al [6] propose an annotation mechanism for relational databases where annotations are stored in extra annotation attributes. The authors extend the SelectProject-Join-Union fragment of SQL with a PROPAGATE clause which allows the user to specify how annotations should propagate.…”

“…Annotations provide a solid way of keeping track of provenance. Indeed, computing provenance by forwarding annotations along data transformations has been proposed in various forms [5,18,21,6]. The data provenance problem without the use of annotation is studied by Cui et al [14], Buneman et al [10,11], and Widom [23].…”

“…In the literature, existing mechanisms assume that each annotation is attached to a particular value of a specific attribute (e.g., see [6]). So, one can annotate, for example, the gene name NF1, in Figure 1(a), with the name of the researcher that discovered this gene.…”