A Framework for Assessing Achievability of Data-Quality Constraints

TL;DR

This paper introduces a new framework for evaluating whether a set of data-processing tools can be combined to achieve desired data quality, considering tools as black boxes with certain known properties.

Contribution

The paper develops a novel framework for assessing the achievability of data quality constraints by modeling data-processing tools as black-box procedures with limited exposed properties.

Findings

Framework encapsulates data cleaning and migration tasks.

Analyzes properties of procedures under relational constraints.

Identifies special cases with feasible reasoning.

Abstract

Assessing and improving the quality of data are fundamental challenges for data-intensive systems that have given rise to applications targeting transformation and cleaning of data. However, while schema design, data cleaning, and data migration are now reasonably well understood in isolation, not much attention has been given to the interplay between the tools addressing issues in these areas. We focus on the problem of determining whether the available data-processing procedures can be used together to bring about the desired quality of the given data. For instance, consider an organization introducing new data-analysis tasks. Depending on the tasks, it may be a priority to determine whether the data can be processed and transformed using the available data-processing tools to satisfy certain properties or quality assurances needed for the success of the task. Here, while the organization may control some of its tools, some other tools may be external or proprietary, with only basic information available on how they process data. The problem is then, how to decide which tools to apply, and in which order, to make the data ready for the new tasks? Toward addressing this problem, we develop a new framework that abstracts data-processing tools as black-box procedures with only some of the properties exposed, such as the applicability requirements, the parts of the data that the procedure modifies, and the conditions that the data satisfy once the procedure has been applied. We show how common tasks such as data cleaning and data migration are encapsulated into our framework and, as a proof of concept, we study basic properties of the framework for the case of procedures described by standard relational constraints. While reasoning in this framework may be computationally infeasible in general, we show that there exist well-behaved special cases with potential practical applications.

Equations

$$\text{EVisits}(\text{facility}:x,\text{patInsur}:y,\text{timestp}:z)\to \text{LocVisits}(\text{facility}:x,\text{patInsur}:y,\text{timestp}:z).$$

$${\text{outcome}}_P(I)=\{I^{\prime }\mid I^{\prime }\text{ is a possible outcome of applying }P\text{ to }I\}.$$

$${\text{outcome}}_P(\mathcal{I})=\bigcup _{I\in \mathcal{I}}{\text{outcome}}_P(I),$$

$${\text{outcome}}_{P_1,\dots ,P_n}(I)={\text{outcome}}_{P_n}({\text{outcome}}_{P_{n-1}}(\cdots ({\text{outcome}}_{P_1}(I))\cdots )).$$

$$G(x,u,v)$$

$$G(u,x,v)$$

$$G(u,v,x)$$

$$D(x)\wedge D(y)$$

$$G(x,y,u)\wedge G(u,z,v)\wedge G(y,z,w)$$

$$D(x)$$

$$E(x,y)$$

$$E(x,y)\wedge E(y,z)$$

$$G(x,y,z)\wedge E(x,x^{\prime })\wedge E(y,y^{\prime })\wedge E(z,z^{\prime })$$

$$G(x,y,z)\wedge G(x^{\prime },y^{\prime },z^{\prime })\wedge E(x,x^{\prime })\wedge E(y,y^{\prime })$$

$$E(x,y)\wedge C(u,x)\wedge C(v,y)\wedge N(u,v)\wedge R(w)$$

$$G(x,u,v)$$

$$G(u,x,v)$$

$$G(u,v,x)$$

$$D(x)$$

$$E(x,y)\wedge C(u,x)\wedge C(v,y)\wedge N(u,v)\wedge R(w)$$

$$E(x,y)$$

$$E(x,y)\wedge E(y,z)$$

$$D(x)\wedge D(y)$$

$$G(x,y,u)\wedge G(u,z,v)\wedge G(y,z,w)$$

$$G(x,y,z)\wedge E(x,x^{\prime })\wedge E(y,y^{\prime })\wedge E(z,z^{\prime })$$

$$G(x,y,z)\wedge G(x^{\prime },y^{\prime },z^{\prime })\wedge E(x,x^{\prime })\wedge E(y,y^{\prime })$$

$$E^d(x,y)$$

$$G^d(x,y,z)$$

$$G^{\text{binary}}(x,y,z)$$

$$F(x)$$

Full text

Toward addressing this problem, we develop a new framework that abstracts data-processing tools as black-box procedures with only some of the properties exposed, such as the applicability requirements, the parts of the data that the procedure modifies, and the conditions that the data satisfy once the procedure has been applied. We show how common database tasks such as data cleaning and data migration are encapsulated into our framework and, as a proof of concept, we study basic properties of the framework for the case of procedures described by standard relational constraints. We show that, while reasoning in this framework may be computationally infeasible in general, there exist well-behaved special cases with potential practical applications.

1 Introduction

A common approach to ascertaining and improving the quality of data is to develop procedures and workflows for repairing or improving data sets with respect to quality constraints. The community has identified a wide range of data-management problems that are vital in this respect, leading to the creation of several lines of studies, which have normally been followed by the development of toolboxes of applications that practitioners can use to solve their problems. This has been the case, for example, for the Extract-Transform-Load (ETL) [13, 18] process in business applications, or for the development of automatic tools to reason about the completeness or cleanliness of the data [15].

As a result, organizations facing data-improvement problems now have access to a variety of data-management tools to choose from; the tools can be assembled to construct so-called workflows of data operations. However, in contrast with the considerable body of research on particular data operations, or even entire business workflows (see, e.g., [12, 8, 11, 3]), previous research appears to have not focused explicitly either on the assembly process itself or on providing guarantees that the desired data-quality constraints will be satisfied once the assembled workflow of procedures has been applied to the available data.

We investigate the problem of constructing workflows from already available procedures. That is, we consider a scenario in which an organization needs to meet a certain data-quality criterion or goal using available data-improvement procedures. In this case, the problem is to understand whether these procedures can be assembled into a data-improvement workflow in a way that would guarantee that the data set produced by the workflow will effectively meet the desired quality goal.

Motivating example: Suppose that data stored in a medical-data aggregator (such as, e.g., Premier [27]) are accessed to perform a health-outcomes analysis in population health management [19, 25, 32], focusing on repeat emergency-room visits in the Washington, DC area. The goal of the analysis is to see whether there is a relationship between such repeat visits and ages and zip codes of patients.

We assume that the aggregator imports information about emergency-room visits from a number of facilities, and stores the information using a relation Visits with attributes facility and facilityLoc for the ID and location of the medical facility, patInsur for the patient insurance number, and timestp for the date and time of the visit. We also assume that medical-record information imported from each facility is stored at the aggregator in a relation Patients, with attributes facility, patInsur, name, age, zipCode, and so on.

The analyst plans to isolate information about emergency-room visits for the Washington area in a relation LocVisits, which would have all the attributes of Visits except facilityLoc, as the values of the latter are understood to be fixed. Further, to obtain the age and zip code of patients, the analyst also needs to integrate the data in Visits with those of Patients.

To process the data, the analyst has access to some procedures that are part of the aggregator’s everyday business. For example, the aggregator periodically runs a StandardizePatientInfo procedure, which first performs entity resolution on insurance IDs in Patients, using both the values of all the patient-related attributes in that relation and a separate “master” relation InsurerInfo that stores authoritative patient information from insurance companies, and then merges the results into Visits. Further, the aggregator offers a procedure MigrateIntoLocVisits that will directly populate LocVisits with the relevant information about emergency rooms (but not the age and zip code of patients).

The analyst is now facing a number of choices, some of which we list here:

• (i)

Use the StandardizePatientInfo procedure on Patients, then manually import the correct(ed) information into LocVisits, and finally join this relation with Patients.

• (ii)

Run MigrateIntoLocVisits to get the relevant patient information, and then join with Patients without running the procedure StandardizePatientInfo.

• (iii)

Add age and zipCode attributes to LocVisits, get the information into LocVisits as in (ii), and then try to modify StandardizePatientInfo into operating directly on LocVisits.

Which of these options is the best for the planned analysis? Option (i) seems to be the cleanest, but if the analyst suspects that StandardizePatientInfo may produce some loss of data, then going with (ii) or (iii) might be a better option. Further, suppose the analyst also has access to a separate relation HealthcareInfo from a health NGO, with information about emergency-room visits gathered from other independent sources. Then the analyst could pose the following quality criterion on the assembled workflow: The result of the workflow should provide at least the information that can be obtained from the relation HealthcareInfo. How could one guarantee that such a criterion will be met?

Contributions: Our goal is to develop a general framework that can be used to determine whether the available data-processing tools can be put together into a workflow capable of producing data that meet the desired quality properties. To address this problem, we abstract data-processing tools as black-box procedures that expose only certain properties. The properties of interest include (i) preconditions, which indicate the state of the data required for the procedure to be applicable; (ii) the parts of the data that the procedure modifies; and (iii) postconditions, which the data satisfy once the procedure has been applied.

In this paper we introduce the basic building blocks and basic results for the proposed framework for assessing achievability of data-quality constraints. The contributions include formalizing the notion of (sequences of) data-transforming procedures, and characterizing instances that are outcomes of applying (sequences of) procedures over other instances. We also illustrate our design choices by discussing ways to encode important database tasks in the proposed framework, including data migration, data cleaning, and schema updates.

One of the advantages of our framework is its generality, as it can be used to encode multiple operations not only on relational data, but on semistructured or even unstructured text data. This very generality implies that to be able to reason about the properties of our framework, one needs to first instantiate some of its most abstract components. As a proof of concept, we provide an in-depth analysis of applications of (sequences of) procedures over relational data, where the procedures are stated using standard relational-data formalisms. We show that properties concerning outcomes of procedures are in general (not surprisingly) undecidable. At the same time, we achieve decidability and tractability for broad classes of realistic procedures that we illustrate with examples. While the formalism and results presented in this paper have practical implications on their own, we see them mainly as prerequisites that need to be understood before one can formalize the notion of assembling procedures in the context of and in response to a user task. We conclude this paper by showing how the proposed framework can be used to formally define the following problem: Given a set of procedures and data-quality criteria, is it possible to assemble a sequence of procedures such that the data outcome is assured to satisfy this criteria?

Related Work: Researchers have been working on eliciting and defining specific dimensions of quality of the data — [31] provides a widely acknowledged standard; please also see [17, 24]. At the general level, high-quality data can be regarded as being fit for their intended use [22, 30, 10] — that is, both context and use (i.e., tasks to be performed) need to be taken into account when evaluating and improving the quality of data. Recent efforts have put an emphasis on information-quality policies and strategies; please see [22] for a groundbreaking set of generic information-quality policies that structure decisions on information. An information-quality improvement cycle, consisting of the define-measure-analyze-improve steps for data quality, has been proposed in [29]. Work has also been done [23] in the direction of integrating process measures with information-quality measures. Our work is different from these lines of research in that in our framework we assume that task-oriented data-quality requirements are already given in the form of constraints that need to be satisfied on the data, and that procedures for improving data quality are also specified and available. Under these assumptions, our goal is to determine whether the procedures can be used to achieve satisfaction of the quality requirements on the data.

The work [15] introduces a unified framework covering formalizations and approaches for a range of problems in data extraction, cleaning, repair, and integration, and also supplies an excellent survey of related work in these areas. More recent work on data cleaning includes [7, 6, 21, 28, 26]. The research area of business processes [11] studies the environment in which data are generated and transformed, including processes, users of data, and goals of using the data. In this context, researchers have studied automating composition of services into business processes, see, e.g., [3, 4, 5], under the assumption that the assembly needs to follow a predefined workflow of executions of actions (services). In contrast, in our work, the predefined part is the specified constraints that the data should satisfy after the assembled workflow of available procedures has been applied to it. Another line of work [12, 8] is closer to reasoning about static properties of business process workflows. That work is different from ours in that it does not pursue the goal of constructing new workflows.

Outline of the paper: Section 2 contains basic definitions used in the paper. Section 3 introduces the proposed framework, and Section 4 discusses encoding tasks such as data exchange, data cleaning, and alter-table statements. The formal results are presented in Section 5. Section 6 concludes with a discussion of future challenges and opportunities.

2 Preliminaries

Schemas and instances: Assume a countably infinite set of attribute names $\mathcal{A}=\{A_{1}$, $A_{2}$, $\ldots\}$ and a countably infinite set (disjoint from $\cal A$) of relation names $\mathcal{R}=\{R_{1}$, $R_{2}$, $\ldots\}$. A relational schema is a partial function ${\cal S}:\mathcal{R}\to 2^{\mathcal{A}}$ with finite domain, which associates a finite set of attributes to a finite set of relation symbols. If ${\cal S}(R)$ is defined, we say that $R$ is in ${\cal S}$. A schema ${\cal S}^{\prime}$ extends a schema ${\cal S}$ if for each relation $R$ such that ${\cal S}(R)$ is defined, we have that ${\cal S}(R)\subseteq{\cal S}^{\prime}(R)$. That is, ${\cal S}^{\prime}$ extends ${\cal S}$ if ${\cal S}^{\prime}$ assigns at least the same attributes to all relations in ${\cal S}$. We also assume a total order $\leq_{\mathcal{A}}$ over all attribute names in order to be able to switch between the named and unnamed perspectives for instances and queries.

We define instances so that it is possible to switch between the named and unnamed perspectives. Assume a countably infinite set of domain values $D$ (disjoint from both $\mathcal{A}$ and $\mathcal{R}$). Following [1], an instance $I$ of schema ${\cal S}$ assigns to each relation $R$ in ${\cal S}$, where ${\cal S}(R)=\{A_{1},\dots,A_{n}\}$, a set $R^{I}$ of named tuples, each of which is a function of the form $t:\{A_{1},\dots,A_{n}\}\rightarrow D$, representing the tuples in $R$. (We use $t(A_{i})$ to denote the element of a tuple $t$ corresponding to the attribute $A_{i}$.) By using the order $<_{\mathcal{A}}$ over attributes, we can alternatively view $t$ as an unnamed tuple, corresponding to the sequence $\bar{t}=t(A_{1}),\dots,t(A_{n})$, with $A_{1}<_{\mathcal{A}}\cdots<_{\mathcal{A}}A_{n}$. Thus, we can also view an instance $I$ as an assignment $R^{I}$ of sets of unnamed tuples (or just tuples) $\bar{t}\in D^{n}$. In general, when we know all attribute names for a relation, we use the unnamed perspective, but when the set of attributes is not clear, we resort to the named perspective. For the sake of readability, we abuse notation and use $\text{Schema}(I)$ to denote the schema of an instance $I$.

For instances $I$ and $J$ over a schema ${\cal S}$, we write $I\subseteq J$ if for each relation symbol $R$ in ${\cal S}$ we have that $R^{I}\subseteq R^{J}$. Furthermore, if $I_{1}$ and $I_{2}$ are instances over respective schemas ${\cal S}_{1}$ and ${\cal S}_{2}$, we denote by $I_{1}\cup I_{2}$ the instance over schema ${\cal S}_{1}\cup{\cal S}_{2}$ such that $R^{I_{1}\cup I_{2}}=R^{I_{1}}\cup R^{I_{2}}$ if $R$ is in both ${\cal S}_{1}$ and ${\cal S}_{2}$, $R^{I_{1}\cup I_{2}}=R^{I_{1}}$ if $R$ is only in ${\cal S}_{1}$, and $R^{I_{1}\cup I_{2}}=R^{I_{2}}$ if $R$ is only in ${\cal S}_{2}$. Finally, an instance $I^{\prime}$ extends an instance $I$ if (1) $\text{Schema}(I^{\prime})$ extends $\text{Schema}(I)$, and (2) for each relation $R$ in $\text{Schema}(I)$ with assigned attributes $\{A_{1},\dots,A_{n}\}$ and for each tuple $t$ in $R^{I}$, there is a tuple $t^{\prime}$ in $R^{I^{\prime}}$ such that $t(A_{i})=t^{\prime}(A_{i})$ for each $1\leq i\leq n$. Intuitively, $I$ extends $I^{\prime}$ if the projection of $I^{\prime}$ over the schema of $I$ is contained in $I$.

Conjunctive queries: Since our goal is for queries to be applicable to different schemas, we adopt a named perspective on queries. A named atom is an expression of the form $R(A_{1}:x_{1},\dots,A_{k}:x_{k})$, where $R$ is a relation name, each $A_{i}$ is an attribute name, and each $x_{i}$ is a variable. We say that the variables mentioned by such an atom are $x_{1},\dots,x_{k}$, and the attributes mentioned by it are $A_{1},\dots,A_{k}$. A conjunctive query (CQ) is an expression of the form $\exists\bar{z}\phi(\bar{z},\bar{y})$, where $\bar{z}$ and $\bar{y}$ are tuples of variables and $\phi(\bar{z},\bar{y})$ is a conjunction of named atoms that use the variables in $\bar{z}$ and $\bar{y}$.

A named atom $R(A_{1}:x_{1},\dots,A_{k}:x_{k})$ is compatible with schema ${\cal S}$ if $\{A_{1},\dots,A_{k}\}\subseteq{\cal S}(R)$. A CQ is compatible with ${\cal S}$ if all its named atoms are compatible. Given a named atom $R(A_{1}:x_{1},\dots,A_{k}:x_{k})$, an instance $I$ of a schema ${\cal S}$ that is compatible with the atom, and an assignment $\tau:\{x_{1},\dots,x_{k}\}\rightarrow D$ of values to variables, we say that $(I,\tau)$ satisfy $R(A_{1}:x_{1},\dots,A_{k}:x_{k})$ if there is a tuple $a:\mathcal{A}\rightarrow D$ matching values with $\tau$ on attributes in $R$ in the sense that $a(A_{i})=\tau(x_{i})$ for each $1\leq i\leq k$. (Under the unnamed perspective we would require a tuple $a$ in $R^{I}$ such that its projection $\pi_{A_{1},\dots,A_{k}}\bar{a}$ over attributes $A_{1},\dots,A_{k}$ is precisely the tuple $\tau(x_{1}),\dots,\tau(x_{k})$.) The usual semantics of conjunctive queries now follows, extending the notion of assignments in the usual way. Finally, given a conjunctive query $Q$ that is compatible with ${\cal S}$, the evaluation $Q(I)$ of $Q$ over $I$ is the set of all the tuples $\tau(x_{1}),\dots,\tau(x_{k})$ such that $(I,\tau)$ satisfy $Q$.

We also need to specify queries that extract all tuples stored in a given relation, regardless of the schema, as is done in SQL with the query SELECT * FROM R. To be able to do this, we also use what we call total queries, which, as we do not need to know the arity of $R$, are simply constructs of the form $R$, for a relation name $R$. A total query of this form is compatible with a schema ${\cal S}$ if ${\cal S}(R)$ is defined, and the evaluation of this query over an instance $I$ over a compatible schema ${\cal S}$ is simply the set of tuples $R^{I}$.

Data Constraints: Most of our data constraints can be captured by tuple-generating dependencies (tgds), which are expressions of the form \forall\bar{x}\big{(}\exists\bar{y}\phi(\bar{x},\bar{y})\rightarrow\exists\bar{z}\psi(\bar{x},\bar{z})\big{)}, for conjunctive queries $\exists\bar{y}\phi(\bar{x},\bar{y})$ and $\exists\bar{y}\psi(\bar{x},\bar{z})$, and by equality-generating dependencies (egds), which are expressions of the form \forall\bar{x}\big{(}\exists\bar{y}\phi(\bar{x},\bar{y})\rightarrow x=x^{\prime}\big{)}, for a conjunctive query $\exists\bar{y}\phi(\bar{x},\bar{y})$ and variables $x,x^{\prime}$ in $\bar{x}$. As usual, for readability we sometimes omit the universal quantifiers of tgds and egds. An instance $I$ satisfies a set $\Sigma$ of tgds and egds, written $I\models\Sigma$, if (1) the schema of $I$ is compatible with each conjunctive query in each dependency in $\Sigma$, and (2) every assignment $\tau:\bar{x}\cup\bar{y}\rightarrow D$ such that $(I,\tau)\models\phi(\bar{x},\bar{y})\rightarrow D$ can be extended into an assignment $\tau^{\prime}:\bar{x}\cup\bar{y}\cup\bar{z}\to D$ such that $(I,\tau^{\prime})\models\psi(\bar{x},\bar{z})$.

A tgd is full if it does not use existentially quantified variables on the right-hand side. A set $\Sigma$ of tgds is full if each tgd in $\Sigma$ is full. $\Sigma$ is acyclic if an acyclic graph is formed by representing each relation mentioned in a tgd in $\Sigma$ as a node and by adding an edge from node $R$ to $S$ if a tgd in $\Sigma$ mentions $R$ on the left-hand side and $S$ on the right-hand side.

Structure Constraints: Structure constraints are used to specify that schemas need to contain a certain relation or certain attributes. A structure constraint is a formula of the form $R[\bar{s}]$ or $R[*]$, where $R$ is a relation symbol, $\bar{s}$ is a tuple of attributes, and $*$ is a symbol not in $\mathcal{A}$ or $\mathcal{R}$ intended to function as a wildcard. A schema ${\cal S}$ satisfies a structure constraint $R[\bar{s}]$, denoted by ${\cal S}\models R[\bar{s}]$, if ${\cal S}(R)$ is defined, and each attribute in $\bar{s}$ belongs to ${\cal S}(R)$ The schema satisfies the constraint $R[*]$ if ${\cal S}(R)$ is defined. For an instance $I$ over a schema ${\cal S}$ and a set $\Sigma$ of tgds, egds, and structure constraints, we write $(I,{\cal S})\models\Sigma$ if $I$ satisfies each data constraint in $\Sigma$ and ${\cal S}$ satisfies each structure constraint in $\Sigma$.

3 Procedures

In this section we formalize the notion of procedures that transform data. We view procedures as black boxes, and assume no knowledge of or control over their inner workings. Our reasoning about procedures is based on two properties: an input condition, or precondition on the state of the data that must hold for a procedure to be applicable, and an output condition, or postcondition on the state of the data that must hold after the application.

Example 1

Consider again the medical example discussed in the introduction, with a schema having two relations: LocVisits, holding information about emergency-room visits in a geographical area, and EVisits, holding visit information for an individual emergency room in a particular location. Suppose we know that a procedure is available that migrates the data from EVisits to LocVisits. We do not know how the procedure works, but we do know that once it has been applied, all tuples in EVisits also appear in LocVisits. In other words, this procedure can be described by the following information:

Precondition: The schema has relations LocVisits and EVisits, each with attributes facility, patInsur and timestp (standing for facility ID, patient insurance ID, and timestamp).

Postcondition: Every tuple from EVisits is in LocVisits.

Scope and safety guarantees: To rule out procedures that, for example, delete all the tuples from the database, we must be assured that our procedure only modifies the relation LocVisits, and that it preserves all the tuples present in LocVisits before the application of the procedure. We shall soon see how to encode these guarantees into our framework.

Suppose that after a while, the requirements of one of the partner agencies of the organization impose an additional requirement: Relation LocVisits should also contain information about the age of the patients. Suppose the organization also has a relation Patients, where the patient age is recorded in attribute age, together with facility and patientId. To migrate the patient ages into LocVisits, one needs the following steps: First add the attribute age to LocVisists, and then update this table so that the patient ages are as recorded in Patients. We observe that all the procedures involved in this operation can be captured using the same framework of preconditions, postconditions, and scope/safety guarantees that we used to capture the data-migration procedure.

3.1 Formal Definition

We define procedures with respect to a class $\mathbb{C}$ of constraints and a class $\mathbb{Q}$ of queries.

Definition 1

A procedure $P$ over $\mathbb{C}$ and $\mathbb{Q}$ is a tuple $(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$, where

• •

Scope is a set of structure constraints that defines the scope (i.e., relations and attributes) in which the procedure acts;

• •

$\mathcal{C}_{\text{in}}$ and $\mathcal{C}_{\text{out}}$ are constraints in $\mathbb{C}$ that describe the pre- and postconditions of the procedure, respectively; and

• •

$\mathcal{Q}_{\text{safe}}$ is a set of queries in $\mathbb{Q}$ that serve as a safety guarantee for the procedure.

Example 2

Let us return to the procedure outlined in Example 1, where the intention was to define migration of data from relation EVisits into LocVisits. In our formalism, we describe this procedure as follows.

Scope: Since the procedure migrates tuples into LocVisits, the scope of the procedure is just this relation. This is described using the structure constraint $\textit{LocVisits}[*]$.

$\mathcal{C}_{\text{in}}$: We use the structure constraints $\textit{EVisits}[\textit{facility},\textit{patInsur},\textit{timestp}]$ and LocVisits $[\textit{facility},$ $\textit{patInsur},$ $\textit{timestp}]$, to ensure that the database has the correct attributes.

$\mathcal{C}_{\text{out}}$: The postcondition comprises the tgd

[TABLE]

That is to say, after the procedure has been applied, the projection of EVisits over facility, patInsur and timestp is a subset of the respective projection of LocVisits.

$\mathcal{Q}_{\text{safe}}$: We can add safety guarantees in terms of queries that need to be preserved when the procedure is applied. In this case, since we do not want the procedure to delete anything that was stored in LocVisits before the migration, we add the safety constraint $\textit{LocVisits}(\textit{facility}:x,\textit{patInsur}:y,\textit{timestp}:z)$, whose intent is to to state that all answers to this query on LocVisits that are present in the database before the application of the procedure must be preserved. We formalize this intuition when giving the semantics below.

3.2 Semantics

Formalizing the semantics of procedures requires additional notation. Given a set $\mathcal{C}$ of structure constraints and a schema ${\cal S}$, we denote by $Q_{{\cal S}\setminus\mathcal{C}}$ the conjunctive query that, intuitively, is meant to retrieve the projection of the entire database over all relations and attributes not mentioned in $\mathcal{C}$. Formally, $Q_{{\cal S}\setminus\mathcal{C}}$ includes a conjunct $R(A_{1}:z_{1},\dots,A_{m}:z_{m})$ for each relation $R$ in ${\cal S}$ but not mentioned in $\mathcal{C}$, where ${\cal S}(R)=\{A_{1},\dots,A_{m}\}$ and $z_{1},\dots,z_{m}$ are fresh variables. In addition, if some constraint in $\mathcal{C}$ mentions a relation $T$ in ${\cal S}$, but no constraint in $\mathcal{C}$ is of the form $T[*]$, then $Q_{{\cal S}\setminus\mathcal{C}}$ also includes a conjunct $T(B_{1}:z_{1},\dots,B_{k}:z_{k})$, where $\{B_{1},\dots,B_{k}\}$ is the set of all the attributes in ${\cal S}(T)$ that are not mentioned in any constraint in $\mathcal{C}$, and $z_{1},\dots,z_{k}$ are again fresh variables. For example, consider a schema ${\cal S}$ with relations $R$, $S$, and $T$, where $R$ has attributes $A_{1}$ and $A_{2}$, $T$ has attributes $B_{1}$, $B_{2}$ and $B_{3}$, and $S$ has $A_{1}$ and $B_{1}$. Further, consider the set $\mathcal{C}$ with a single constraint $R[*]\wedge S[B_{1}]$. Then $Q_{{\cal S}\setminus C}$ is the query $T(B_{1}:z_{1},B_{2}:z_{2},B_{3}:z_{3})\wedge S(A_{1}:w_{1})$. Note that $Q_{{\cal S}\setminus\mathcal{C}}$ is unique up to the renaming of variables and order of conjuncts.

A procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ is applicable on an instance $I$ over schema ${\cal S}$ if (1) The query $Q_{{\cal S}\setminus\textit{Scope}}$ and each query in $\mathcal{Q}_{\text{safe}}$ are compatible with both ${\cal S}$ and ${\cal S}^{\prime}$, and (2) $(I,{\cal S})$ satisfy the preconditions $\mathcal{C}_{\text{in}}$. We can now proceed with the semantics of procedures.

Definition 2

Let $I$ be an instance over a schema ${\cal S}$. An instance $I^{\prime}$ over schema ${\cal S}^{\prime}$ is a possible outcome of applying $P$ over the instance and schema $(I,{\cal S})$ if the following holds:

$P$ is applicable on $I$. 2. 2.

$(I^{\prime},{\cal S}^{\prime})\models\mathcal{C}_{\text{out}}$. 3. 3.

The answers of the query $Q_{{\cal S}\setminus\textit{Scope}}$ do not change: $Q_{{\cal S}\setminus\textit{Scope}}(I)=Q_{{\cal S}\setminus\textit{Scope}}(I^{\prime})$. 4. 4.

The answers of each query $Q$ in $\mathcal{Q}_{\text{safe}}$ over $I$ are preserved: $Q(I)\subseteq Q(I^{\prime})$.

In the definition, we state the schemas of instances $I$ and $I^{\prime}$ explicitly, to reinforce the fact that schemas may change during the application of procedures. However, most of the time the schema can be understood from the instance, so we normally just say that an instance $I^{\prime}$ is a possible outcome of $I$ (even if the schemas of $I$ and $I^{\prime}$ are different). Let us also recall that we use $\text{Schema}(I)$ to denote the schema of an instance $I$.

Example 3 (Example 2 continued)

Recall the procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ defined in Example 2. Consider the instance $I$ over the schema ${\cal S}$ with relations EVisits and LocVisits, each with attributes facility, patInsur, and timestp, as shown in Figure 1 (a). Note first that $P$ is indeed applicable on $I$. When applying the procedure $P$ over $I$, we know from Scope that the only relation whose content can change is LocVisits, while EVisits (or more precisely, the projection of EVisits over patInsur, facility and timestp) is the same across all possible outcomes. Furthermore, we know from $\mathcal{C}_{\text{out}}$ that in all possible outcomes the projection of EVisits over attributes facility, patInsur, and timestp must be the same as the projection of LocVisits over the same attributes. Finally, from $\mathcal{Q}_{\text{safe}}$ we know that the projection of LocVisits over these three attributes must be preserved.

Perhaps the most obvious possible outcome of applying $P$ over $I$ is that of the instance $J_{1}$ in Figure 1 (b), corresponding to the outcome where the tuple in EVisits that is not yet in LocVisits is migrated into this last relation. However, since we assume no control over the actions performed by the procedure $P$, it may well be that it is also migrating data from a different relation that we are not aware of, producing an outcome whose relation EVisits remains the same as in $I$ and $J_{1}$, but where LocVisits has additional tuples, as depicted in Figure 1 (c). Moreover, it may also be the case that the procedure alters the schema of LocVisits, adding an extra attribute age, importing the information from an unknown source, as shown in Figure 1 (d).

As we have seen in this example, in general the number of possible outcomes (and even the number of possible schemas) that result after a procedure is executed is infinite. For this reason, we are generally more interested in properties shared by all possible outcomes, which motivates the following definitions.

Definition 3

The outcome set of applying a procedure $P$ to $I$ is defined as the set.

[TABLE]

4 Defining Common Database Tasks as Procedures

We now show additional examples of defining common database tasks as procedures within our framework. We show that data exchange, alter-table statements, and data cleaning can all be accommodated by the framework, and provide additional examples in Appendix 0.A. It is worth noticing that in our first three examples we use only structure constraints, tgds, and egds as pre- and postconditions, and that our safe queries are all conjunctive queries. The last example calls for extending the language used to define procedures.

4.1 Data Exchange

We have already seen an example of specifying data-migration tasks as black-box procedures. However, a more detailed discussion will allow us to illustrate some of the basic properties of our framework. Following the notation introduced by Fagin et al. in [14], the most basic instance of the data-exchange problem considers a source schema ${\cal S}_{\text{s}}$, a target schema ${\cal S}_{\text{t}}$, and a set $\Sigma$ of dependencies that define how data from the source schema are to be mapped to the target schema. The dependencies in $\Sigma$ are usually tgds whose left-hand side is compatible with ${\cal S}_{\text{s}}$, and the right-hand side is compatible with ${\cal S}_{\textit{t}}$. The data-exchange problem is as follows: Given a source instance $I$, compute a target instance $J$ so that $(I,J)$ satisfies all the dependencies in $\Sigma$. Instances $J$ with this property are called solutions for $I$ under $\Sigma$.

In encapsulating this task as a black box within our framework, we assume that the target and source schemas are part of the same schema. (Alternatively, one can define procedures working over different databases.) Let $({\cal S}_{\text{s}},{\cal S}_{\text{t}},\Sigma)$ be as above. We construct the procedure $P^{\textit{st}}=(\textit{Scope}^{\textit{st}},\mathcal{C}_{\text{in}}^{\textit{st}},\mathcal{C}_{\text{out}}^{\textit{st}},\mathcal{Q}_{\text{safe}}^{\textit{st}})$, where

• •

$\textit{Scope}^{\textit{st}}$ contains an atom $R[*]$ for each relation $R$ on the right-hand side of a tgd in $\Sigma$;

• •

$\mathcal{C}_{\text{in}}^{\textit{st}}$ contains a structure constraint $Q[A_{1},\dots,A_{n}]$ for each query of the form$Q(A_{1}:x_{1},\dots,A_{n}:x_{n})$ on the left-hand side of a tuple-generating dependency in $\Sigma$;

• •

$\mathcal{C}_{\text{out}}^{\textit{st}}$ is the set of all the tgds in $\Sigma$; and

• •

$\mathcal{Q}_{\text{safe}}^{\textit{st}}$ is the conjunction of all the atoms $R$ appearing in any tgd in $\Sigma$.

By the semantics of procedures, it is not difficult to conclude that, for every pair of instances $I$ and $J$ over ${\cal S}_{\text{s}}$ and ${\cal S}_{\text{t}}$, respectively, we have that $J$ is a solution for $I$ if and only if the instance $I\cup J$ over the schema ${\cal S}_{\text{s}}\cup{\cal S}_{\text{t}}$ is a possible outcome of applying $P^{\textit{st}}$ over $(I,{\cal S}_{\text{s}}\cup{\cal S}_{\text{t}})$. We can make this statement much more general, as the set of all possible outcomes essentially corresponds to the set of solutions of the data-exchange setting.

Proposition 1

An instance $J$ over schema ${\cal S}_{\text{s}}\cup{\cal S}_{\text{t}}$ is a possible outcome of applying $P^{\textit{st}}$ over $(I,{\cal S}_{\text{s}}\cup{\cal S}_{\text{t}})$ if and only if $J$ is a solution for $I$ under $\Sigma$.

4.2 Alter Table Statements

In our framework, procedures can be defined to work over more than one schema, as long as the schemas satisfy the necessary input and compatibility conditions. This is inspired by SQL, where statements such as INSERT INTO R (SELECT * FROM S) would be executable over any schema, as long as the relations $R$ and $S$ have the same types of attributes in the same order. Thus, it seems logical to allow procedures that alter the schema of the existing database. To do so, we use structure constraints, as shown in the following example.

Example 4

Recall from Example 1 that, due to a change in the requirements, we now need to add the attribute age to the schema of LocVisits. In general, we capture alter table statements by procedures without scope, used only to alter the schema of the outcomes, so that it would satisfy the structural postconditions of procedures. In this case, we model a procedure that adds age to the schema of LocVisits with the procedure $P^{\prime}=(\textit{Scope}^{\prime},\mathcal{C}_{\text{in}}^{\prime},\mathcal{C}_{\text{out}}^{\prime},\mathcal{Q}_{\text{safe}}^{\prime})$, where $\textit{Scope}^{\prime}$ and $\mathcal{Q}_{\text{safe}}^{\prime}$ are empty (if there is no scope, then the database does not change modulo adding attributes, so we do not include any safety guarantees), $\mathcal{C}_{\text{in}}^{\prime}$ is the stucture constraint $\textit{LocVisits}[*]$, stating that the relation exists in the schema, and $\mathcal{C}_{\text{out}}^{\prime}$ is the structure constraint $\textit{LocVisits}[\textit{age}]$, stating that LocVisits now has an age attribute. Note that the instance $J_{3}$ in Figure 1(d) with EVisits as in $J_{1}$ in Figure 1(b), is actually a possible outcome of applying $P^{\prime}$ over instance $J_{1}$; the part of the instance given by the schema of $J_{1}$ does not change, but we do add an extra attribute age to LocVisits, and we cannot really control the values of the newly added attribute.

We remark that the empty scope in $P^{\prime}$ guarantees that no relations or attributes are deleted when applying this procedure. This happens because $Q_{{\cal S}\setminus\textit{Scope}}$ must be compatible with the schema of all outcomes. However, nothing prevents us from adding extra attributes on top of age. This decision to use the open-world assumption on schemas reflects the understanding of procedures as black boxes, which we can execute but not control in other ways.

4.3 Data Cleaning

Data cleaning is a frequent and important task within database systems (see e.g., [15]). The most simple cleaning scenario one could envision is when we have a relation $R$ whose attribute values are deemed incorrect or incomplete, and it is desirable to provide the correct values. There are, in general, multiple ways to do this; here we consider just a few of them.

The first possibility is to assume that we have the correct values in another relation, and to use this other relation to provide the correct values for $R$. Consider an example.

Example 5

Consider again the schema from Example 1. Recall that in Example 4 we added the attribute age to the schema of LocVisits. The problem is that we have no control over the newly added values of age. (If the procedure was a SQL alter-table statement, then the age column would be filled with nulls.) However, another relation, Patients, associates an age value with each pair of (facility, patInsur) values; all we need to do now is to copy the appropriate age value into each tuple in LocVisits. To this end, we specify the procedure $P^{*}=(\textit{Scope}^{*},\mathcal{C}_{\text{in}}^{*},\mathcal{C}_{\text{out}}^{*},\mathcal{Q}_{\text{safe}}^{*})$, which copies the values of age from Patients into LocVisits, using the values of facility and patInsur as a reference.

$\textit{Scope}^{*}$: We use the constraint $\textit{LocVisits}[\textit{age}]$, so that the only piece of the database the procedure can alter is age in the relation LocVisits.

$\mathcal{C}_{\text{in}}^{*}$: The preconditions are the structure constraints $\textit{LocVisits}[\textit{facility},\textit{patInsur},\textit{age}]$ and $\textit{Patients}[\textit{facility},\textit{patInsur},\textit{age}]$, plus the fact that the values of facility and patInsur need to determine the values of age in the Patients relation, specified with the dependency $\textit{Patients}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:z)\wedge\textit{Patients}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:w)\rightarrow z=w$. Note that in this case we do not actually need the structure constraints in Patients, because they are implicit in the dependencies (they need to be compatible with the schema), but we keep them for clarity.

$\mathcal{C}_{\text{out}}^{*}$: The postcondition is the constraint $\textit{Patients}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:z)\wedge\textit{LocVisits}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:w)\rightarrow z=w.$ Alternatively, if we know that all the $(\textit{facility},\textit{patInsur})$ pairs from Patients are in LocVisits (which can be required with a precondition), we can specify the same postcondition via $\textit{LocVisits}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:w)\rightarrow\textit{Patients}(\textit{facility}:x,\textit{patInsur}:y,\textit{age}:w)$.

$\mathcal{Q}_{\text{safe}}^{*}$: Same as before, no guarantees are needed.

As desired, in all the outcomes of $P^{*}$ the value of the age attribute in LocVisits is the same as in the corresponding tuple (if it exists) in Patients with the same facility and patInsur values. But then again, the procedure might modify the schema of some relations, or might even create auxiliary relations in the database in the process. What we gain is that this procedure will work regardless of the shape of relations LocVisits and Patients, as long as the schemas satisfy the compatibility and structure constraints.

In the above example we used a known auxiliary relation to clean the values of age in LocVisits. Alternatively, we could define a more general procedure that would, for instance, only remove nulls from LocVisits, without controlling which values end up replacing these nulls. In order to state this procedure, let us augment the language of tgds with an auxiliary predicate $C$ (for constant) with a single attribute val, which is to take the role of the NOT NULL constraint in SQL: It is true only for the non-null values in $D$.

Example 6

Let us now define a procedure $\hat{P}=(\hat{\textit{Scope}},\hat{\mathcal{C}}_{\text{in}},\hat{\mathcal{C}}_{\text{out}},\hat{\mathcal{Q}}_{\text{safe}})$ that simply replaces all null values of the attribute age in relation LocVisits with non-null values.

$\hat{\textit{Scope}}$: The scope is again $\textit{LocVisits}[\textit{age}]$, just as in the previous example.

$\hat{\mathcal{C}}_{\text{in}}$: In contrast with the procedure $P^{*}$ of the previous example, this procedure is light on preconditions: We only need relation LocVisits to be present and have the age attribute.

$\hat{\mathcal{C}}_{\text{out}}$: The postcondition states that the attribute age of LocVisits no longer has null values. To express this, we use the auxiliary predicate $C$, and define the constraint $\textit{LocVisits}(\textit{age}:x)\rightarrow C(\textit{val}:x)$, which states that no value in the attribute age in LocVisits is null.

$\hat{\mathcal{Q}}_{\text{safe}}$: Since we only want to eliminate null values, we also include the safety query $\textit{LocVisits}(age:x,\textit{facility}:y,\textit{patInsur}:z)\wedge C(\textit{val}:x)$, so that we preserve all the non-null values of age (with the correct facility and patInsur attached to these ages).

5 Basic Computational Tasks for Relational Procedures

In this section we study some formal properties of our procedure-centric framework, with the intent of showing how the proposed framework can be used as a toolbox for reasoning about sequences of database procedures. We focus on what we call relational procedures, where the sets of pre- and postconditions are given by tgds, egds, or structure constraints, and safety queries can be conjunctive or total queries. While there clearly are interesting classes of procedures that do not fit into this special case in the proposed framework, we remark that relational procedures are general enough to account for a wide range of relational operations on data, including the examples in the previous section.

5.1 Applicability

In the proposed framework we focus on transformations of data sets given by sequences of procedures. Because we treat procedures as black boxes, the only description we have of the results of these transformations is that they ought to satisfy the output constraints of the procedures. In this situation, how can one guarantee that all the procedures will be applicable? Suppose that, for instance, we wish to apply procedures $P_{1}$ and $P_{2}$ to an instance $I$ in sequential order: First $P_{1}$, then $P_{2}$. The problem is that, since output constraints do not fully determine the outcome of $I$ after applying $P_{1}$, we cannot immediately guarantee that this outcome is an instance that satisfies the preconditions of $P_{2}$.

Given that the set of possible outcomes is in general infinite, our focus is on guaranteeing that any possible outcome of applying $P_{1}$ over $I$ will satisfy the preconditions of $P_{2}$. To formalize this intuition, we need to extend the notion of outcome to a set of instances. We define the outcome of applying a procedure $P$ to a set of instances ${\cal I}$ as

[TABLE]

the union of the outcomes of all the instances in ${\cal I}$. Furthermore, for a sequence $P_{1},\dots,P_{n}$ of procedures we define the outcome of applying $P_{1},\dots,P_{n}$ to an instance $I$ as the set

[TABLE]

We can now define the first problem of interest:

Applicability:

Input: A sequence $P_{1},\dots,P_{n}$ of procedures, a schema ${\cal S}$;

Question: Is it true that, for any arbitrary instance $I$ over ${\cal S}$, procedure $P_{n}$ can

be applied to each instance in the set $\textit{outcome}_{P_{1},\dots,P_{n-1}}(I)$?

It is not difficult to show that the Applicability problem is intimately related to the problem of implication of dependencies, defined as follows: Given a set $\Sigma$ of dependencies and an additional dependency $\lambda$, is it true that all the instances that satisfy $\Sigma$ also satisfy $\lambda$ — that is, does $\Sigma$ imply $\lambda$? Indeed, consider a class $\mathcal{L}$ of constraints for which the implication problem is known to be undecidable. Then one can easily show that the applicability problem is also undecidable for those procedures whose pre- and postconditions are in $\mathcal{L}$: Intuitively, if we let $P_{1}$ be a procedure with a set $\Sigma$ of postconditions, and $P_{2}$ a procedure with a dependency $\lambda$ as a precondition, then it is not difficult to come up with proper scopes and safety queries so that $\textit{outcome}_{P_{1}}(I)$ satisfies $\lambda$ for every instance $I$ over schema ${\cal S}$ if and only if $\lambda$ is true in all instances that satisfy $\Sigma$. However, as the following proposition shows, the applicability problem is undecidable already for very simple procedures, and even when we consider the data-complexity view of the problem, that is when we fix the procedure and take a particular input instance.

Proposition 2

There are fixed procedures $P_{1}$ and $P_{2}$ that only use tgds for their constraints, and such that the following problem is undecidable. Given an instance $I$ over schema ${\cal S}$, is it true that all the instances in $\textit{outcome}_{P_{1}}(I)$ satisfy the preconditions of $P_{2}$?

The proof of Proposition 2 is by reduction from the embedding problem for finite semigroups, shown to be undecidable in [20].

There are several lines of work aiming to identify practical classes of constraints for which the implication problem is decidable, and all that work can be applied in our framework. However, we opt for a stronger restriction: Since all of our examples so far use only structure constraints as preconditions, for the remainder of the paper we focus on procedures whose preconditions comprise structure constraints. In this setting, we have the following result.

Proposition 3

Applicability* is in polynomial time for sequences of relational procedures whose preconditions contain only structure constraints.*

5.2 Representing the Outcome Set

We have seen that deciding properties about the outcome set of a sequence of procedures (or even of a single procedure) can be a complicated task. One of the reasons is that procedures do not completely define their outcomes: We do not really know what will be the outcome of applying a sequence $P_{1},\dots,P_{n}$ of procedures to an instance $I$, we just know it will be an instance from the collection $\textit{outcome}_{P_{1},\dots,P_{n}}(I)$. This collection may well be of infinite size, but can it still be represented finitely? The database-theory community has developed multiple formalisms for representing sets of database instances, from notions of tables with incomplete information [16] to knowledge bases (see, e.g., [9]). In this section we study the possibility of representing outcomes of (sequences of) procedures by means of incomplete tables, along the lines of [16]. We also discuss some negative results about representing outcomes of general procedures in systems such as knowledge bases, but leave a more detailed study in this respect for future work.

The first observation we make is that allowing arbitrary tgds in procedures introduces problems with management of sequences of procedures. Essentially, any means of representing the outcome of a sequence of procedures needs to be so powerful that even deciding whether it is nonempty is going to be undecidable.

Proposition 4

There is a fixed procedure $P$ that does not use preconditions and only use tgds in their postconditions, such that the following problem is undecidable: Given an instance $I$, is the set $\textit{outcome}_{P_{1}}(I)$ nonempty?

The reason we view Proposition 4 as a negative result is because it rules out the possibility of using any “reasonable” representation system. Indeed, one would expect that deciding non-emptiness should be decidable in any reasonable way of representing infinite sets of instances. Proposition 4 is probably not surprising, since reasoning about tgds in general is known to be a hard problem. Perhaps more interestingly, in our case one can show that the above fact remains true even if one allows only acyclic tgds, which are arguably one of the most well-behaved classes of dependencies in the literature. The idea behind the proof is that one can simulate cyclic tgds via procedures with only acyclic tgds and no scope.

Example 7

Consider two procedures $P_{1}$ and $P_{2}$, where $P_{1}=(\textit{Scope}^{1},\mathcal{C}_{\text{in}}^{1},\mathcal{C}_{\text{out}}^{1},\mathcal{Q}_{\text{safe}}^{1})$, with $\textit{Scope}^{1}=\{R[*],T[*]\}$, $\mathcal{C}_{\text{in}}^{1}=\emptyset$, $\mathcal{C}_{\text{out}}^{1}=\{R(A:x)\rightarrow T(A:x)\}$ and $\mathcal{Q}_{\text{safe}}^{1}=R(A:x)\wedge T(A:x)$; $P_{2}$ has empty scope, preconditions, and safety queries, and has postconditions $\{T(A:x)\rightarrow R(A:x)\}$. Let $I$ be an instance over the schema with relations $R$ and $T$, both with attribute $A$. By definition, the set of possible outcomes of $P_{1}$ over $I$ are all instances $J$ that extend $I$ and satisfy the dependency $R(A:x)\rightarrow T(A:x)$. However, the set $\textit{outcome}_{P_{1},P_{2}}(I)$ corresponds to all instances $I^{\prime}$ that extend $I$ and satisfy both dependencies $R(A:x)\rightarrow T(A:x)$ and $T(A:x)\rightarrow R(A:x)$ (In other words, we can use $P_{2}$ to filter out all those instances $J$ where $T^{J}\not\subseteq R^{J}$). Intuitively, this happens because the outcome set of applying $P_{2}$ over any instance not satisfying $T(A:x)\rightarrow R(A:x)$ is empty, and we define $\textit{outcome}_{P_{1},P_{2}}(I)$ as the union of each set $\textit{outcome}_{P_{2}}(K)$, for each instance $K\in\textit{outcome}_{P_{1}}(I)$.

By applying the idea of this example to the proof of Proposition 4, we show:

Proposition 5

Proposition 4 holds for procedures $P_{1}$ and $P_{2}$ that only use acyclic tgds.

Since acyclic tgds do not help, we may consider restrictions to full tgds. Still, even this is not enough for making the non-emptiness problem decidable, once one adds the possibility of having schema constraints in procedures.

Proposition 6

There exists a sequence $P_{1},P_{2},P_{3}$ of procedures such that the following problem is undecidable: Given an instance $I$, is the set $\textit{outcome}_{P_{1},P_{2},P_{3}}(I)$ nonempty? Here, all the procedures have no preconditions, and have postconditions built using acyclic sets of full tgds and schema constraints (and nothing else).

Propositions 4 and 6 tell us that restricting the classes of dependencies allowed in procedures may not be enough to guarantee outcomes that can be represented by reasonable systems. Thus, we now adapt a different strategy: We restrict interplay between the postconditions of procedures, their scope, and their safety queries. Let us define two important classes of procedures that will be used thoroughout this section.

We say that procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ is safe scope if the following holds:

• •

$\mathcal{C}_{\text{out}}$ is a set of tgds where no relation in the right-hand side of a tgd appears also in the left-hand side of a tgd;

• •

The set Scope contains exactly one constraint $R[*]$ for each relation $R$ that appears on the right-hand side of a tgd in $\mathcal{C}_{\text{out}}$; and

• •

The query $Q_{\text{safe}}$ corresponds to $\bigwedge_{R[*]\in\textit{Scope}}R$, that is it binds precisely all the relations in the scope of $P$.

(For instance, procedure $P$ in Example 2 is essentially a procedure with safe scope, as it can easily be transformed into one by slightly altering the safety query.)

We also define a class of procedures that ensure that certain attributes or relations be present in the schema. Formally, we say that a procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ is an alter-schema procedure if the following holds:

• •

Both Scope and $\mathcal{Q}_{\text{safe}}$ are empty; and

• •

$\mathcal{C}_{\text{out}}$ is a set of structure constraints.

Let $\mathbb{P}^{\textit{safe},\textit{alter}}$ be the class of all the procedures that are either safe scope or alter-schema procedures. The class $\mathbb{P}^{\textit{safe},\textit{alter}}$ allows for practically-oriented interplay between migration and schema-alteration tasks and, as we will see in this section, is more manageable from the point of view of reasoning tasks, in terms of complexity. To begin with, deciding the non-emptiness of a sequence of procedures is essentially tractable for $\mathbb{P}^{\textit{safe},\textit{alter}}$:

Theorem 5.1

The problem of deciding, given an instance $I$ and a sequence $P_{1},\dots,P_{n}$ of procedures in $\mathbb{P}^{\textit{safe},\textit{alter}}$, whether $\textit{outcome}_{P_{1},\dots,P_{n}}(I)\neq\emptyset$, is in exponential time, and is polynomial if the number $n$ of procedures is fixed.

The proof of Theorem 5.1 is based on the idea of chasing instances with the dependencies in the procedures, and of adding attributes to schemas as dictated by the alter-schema procedures. As usual, to enable the chase we need to introduce labeled nulls in instances (see, e.g., [16, 14]), and composing procedures calls for extending the techniques of [2] to enable chase instances that already have null values. Using the enhanced approach, one can show that the result of the chase is a good over-approximation of the outcome of a sequence of procedures. To state this result, we introduce conditional tables [16].

Let $\mathcal{N}$ be an infinite set of null values that is disjoint from the set of domain values $D$. A naive instance $T$ over schema ${\cal S}$ assigns a finite relation $R^{T}\subseteq(D\cup\mathcal{N})^{n}$ to each relation symbol $R$ in ${\cal S}$ of arity $n$. Conditional instances extend naive instances by attaching conditions over the tuples. Formally, an element-condition is a positive boolean combination of formulas of the form $x=y$ and $x\neq y$, where $x\in\mathcal{N}$ and $y\in(D\cup\mathcal{N})$. Then, a conditional instance $T$ over schema ${\cal S}$ assigns to each $n$-ary relation symbol $R$ in ${\cal S}$ a pair $(R^{T},\rho^{T}_{R})$, where $R^{T}\subseteq(D\cup\mathcal{N})^{n}$ and $\rho^{T}_{R}$ assigns an element-condition to each tuple $t\in R^{T}$. A conditional instance $T$ is positive if none of the element-conditions in its tuples uses inequalities (of the form $x\neq y$).

To define the semantics, let $\textit{Nulls}(T)$ be the set of all nulls in any tuple in $T$ or in an element-condition used in $T$. Given a substitution $\nu:\textit{Nulls}(T)\rightarrow D$, let $\nu^{*}$ be the extension of $\nu$ to a substitution $D\cup\textit{Nulls}(T)\rightarrow D$ that is the identity on $D$. We say that $\nu$ satisfies an element-condition $\psi$, and write $\nu\models\psi$, if for every equality $x=y$ in $\psi$ it is the case that $\nu^{*}(x)=\nu^{*}(y)$ and for every inequality $x\neq y$ we have that $\nu^{*}(x)\neq\nu^{*}(y)$. Furthermore, we define the set $\nu(R^{T})$ as $\{\nu^{*}(t)\mid t\in R^{T}$ and $\nu\models\rho^{T}_{R}(t))\}$. Finally, for a conditional instance $T$, $\nu(T)$ is the instance that assigns $\nu(R^{T})$ to each relation $R$ in the schema.

The set of instances represented by $T$, denoted by $\textit{rep}(T)$, is defined as $\textit{rep}(T)=\{I\mid$ there is a substitution $\nu$ such that $I$ extends $\nu(T)\}$. Note that the instances $I$ in this definition could have potentially bigger schemas than $\nu(T)$, or, in other words, we consider the set $\textit{rep}(T)$ to contain instances over any schema extending the schema of $T$.

The next result states that conditional instances are good over-approximations for the outcomes of sequences of procedures. More interestingly, these approximations preserve the minimal instances of outcomes. To put this formally, we say that an instance $J$ in a set ${\cal I}$ of instances is minimal if there is no instance $J^{\prime}\in{\cal I},J^{\prime}\neq J$, and such that $J$ extends $J^{\prime}$.

Proposition 7

Let $I$ be an instance and $P_{1},\dots,P_{n}$ be a sequence of procedures in $\mathbb{P}^{\textit{safe},\textit{alter}}$. Then either $\textit{outcome}_{P_{1},\dots,P_{n}}=\emptyset$ or one can construct, in exponential time (or polynomial if $n$ is fixed), a conditional instance $T$ such that

• •

$\textit{outcome}_{P_{1},\dots,P_{n}}(I)\subseteq\textit{rep}(T)$; and

• •

If $J$ is a minimal instance in $\textit{rep}(T)$, then $J$ is also minimal in $\textit{outcome}_{P_{1},\dots,P_{n}}(I)$.

We remark that this proposition can be extended to include procedures defined only with egds, at the cost of a much more technical presentation. While having an approximation with these properties is useful for reasoning tasks related to CQ answering, or in general checking any criterion that is closed under extensions of instances, there is still the question of whether one can find any reasonable class of properties whose entire outcomes can be represented by these tables. However, as the following example shows, this does not appears to be possible, unless one is restricted to sequences of procedures almost without interaction with each other (see an example in appendix 0.A.2).

Example 8

Consider a procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ with safe scope, where $\textit{Scope}=S[*]$, $\mathcal{C}_{\text{in}}$ is empty, $\mathcal{C}_{\text{out}}=\{R(A:x)\rightarrow S(A:x)\}$ and $\mathcal{Q}_{\text{safe}}=S$. Consider now the conditional instance $T$ over the schema with relations $R$ and $S$, both with attribute $A$, given by $R^{T}=\{1,2\}$ and $S^{T}=\{1,2\}$. One could be tempted to say that $T$ is itself a representation of the set $\textit{outcome}_{P}(\textit{rep}(T))$, and indeed $\textit{rep}(T)$ and $\textit{outcome}_{P}(\textit{rep}(T))$ share their only minimal instance (essentially, the instance given by $T$). However, the open-world assumption behind $\textit{rep}(T)$ allows for instances that do not satisfy $\mathcal{C}_{\text{out}}$, whereas all outcomes in $\textit{outcome}_{P}(\textit{rep}(T))$ must satisfy $\mathcal{C}_{\text{out}}$. One can in fact generalize this argument to show that conditional instances are not enough to fully represent outcome sets.

Example 8 suggest that one could perhaps combine conditional instances with a knowledge base, to allow for a complete representation of the outcome set of sequences of safe procedures. However, this would require studying the interplay of these two different types of representation systems, a line of work which is interesting in its own right.

6 Future Work and Opportunities

In this paper, we introduced basic building blocks for a proposed framework for assessing achievability of data-quality constraints. We demonstrated that the framework is general enough to represent nontrivial database tasks, and exhibited realistic classes of procedures for which reasoning tasks can be tractable. Our next step is to address the problem of assessing achievability of constraints, which can be formalized as follows. Let $Q$ be a boolean query, $\Pi$ a set of procedures, and $I$ an instance over a schema ${\cal S}$. Then we say that $I$ can be readied for $Q$ using $\Pi$ if there is a sequence $P_{1},\dots,P_{n}$ of procedures (possibly empty and possibly with repetitions) from $\Pi$ such that $Q$ is compatible with and true in each instance $I^{\prime}$ in the set $\textit{outcome}_{P_{1},\dots,P_{n}}(I)$. (If the latter conditions involving $Q$ are true on $I$, then we say that $I$ is ready for $Q$.) We are confident that this problem is decidable for sets of procedures in $\mathbb{P}^{\textit{safe},\textit{alter}}$, and we plan on looking into more expressive fragments.

The proposed framework presents opportunities for several directions of further research. One line of work would involve understanding how to represent outcomes of sequences of procedures, or how to obtain good approximations of outcomes of more expressive classes of procedures. To solve this problem, we would need a better understanding of the interplay between conditional tables and knowledge bases, which would be interesting in its own right.

We also believe that our framework is general enough to allow reasoning on other data paradigms, or even across various different data paradigms. Our black-box abstraction could, for example, offer an effective way to reason about procedures involving unstructured text data, or even data transformations using machine-learning tools, as long as one can obtain some guarantees on the data outcomes of these tools.

Appendix 0.A Additional Examples

0.A.1 SQL data-modification statements

We show how to encode arbitrary SQL INSERT and DELETE statements as procedures. Due to dealing with arbitrary SQL, we relax the constraints and queries that we use.

INSERT statements: Consider a SQL statement of the form INSERT INTO S Q, where $Q$ is a relational-algebra query.

Scope: Not surprisingly, the scope of the procedure is the relation S.

$\mathcal{C}_{\text{in}}$: The precondition for the procedure is that all the relation names and attributes mentioned in $Q$ must be present in the database.

$\mathcal{C}_{\text{out}}$: The postcondition is stated using the constraint $\texttt{Q}\subseteq\texttt{S}$. (Note that the SQL statement only works when Q and S have the same arity.)

$\mathcal{Q}_{\text{safe}}$: Since we are inserting tuples, we need the query S to be preserved.

Alternatively, we can specify an INSERT statement of the form INSERT INTO S VALUES $\bar{a}$, with $\bar{a}$ a tuple of values. In order to formalize this, we just need to change the postcondition of the procedure to $\bar{a}\subseteq\texttt{S}$.

DELETE statements: Consider a SQL statement of the form DELETE FROM S WHERE $C$, in which $C$ is a boolean combination of conditions.

Scope: The scope is the relation S, as expected.

$\mathcal{C}_{\text{in}}$: The precondition for the procedure is that all the relations and attributes mentioned in $C$ must be present in the database.

$\mathcal{C}_{\text{out}}$: There are no postconditions in this query.

$\mathcal{Q}_{\text{safe}}$: Let $Q_{C}$ be the query SELECT * FROM S WHERE C. Then the safety query is $\texttt{S}-Q_{C}$, which preserves only those tuples that are not to be deleted.

0.A.2 Representing sequences of procedures

As we mentioned, one possibility to obtain a full representation of sequences of procedures is to further restrict the scope of sequences of safe procedures. To be more precise, let us say that a sequence $P_{1},\dots,P_{n}$ of procedures is a safe sequence if (1) each $P_{i}$ is either an alter-schema procedure or a safe-scope procedure that only uses tgds, and (2) for every $1\leq j\leq n$, none of the atoms on the right-hand side of a tgd in $P_{j}$ is part of the scope of any $P_{i}$, with $i\leq j$. Intuitively, safe sequences of procedures restrict the possibility of sequencing data-migration tasks when the result of one migration is used as an input for the next one.

A conditional instance with scope is a pair $\mathbb{T}=(T,\textit{Rel})$, where $T$ is a conditional instance and Rel is a set of relation names. The set of instances represented by $\mathbb{T}$, denoted again by $\textit{rep}(\mathbb{T})$, now contains all the instances $J$ in $\textit{rep}(T)$ where, for each relation $R\in\text{Schema}(\nu(T))$ that is not in Rel, the projection of $R^{J}$ over the attributes of $R$ in $T$ is the same as $R^{\nu(T)}$. (In other words, we allow extra tuples only in the relations whose symbols are in the set Rel.) It is now not difficult to show the following result.

Proposition 8

For each instance $I$ and each safe sequence $P_{1},\dots,P_{n}$ of procedures one can construct a conditional instance $\mathbb{T}$ with scope such that $\textit{rep}(\mathbb{T})=\textit{outcome}_{P_{1},\dots,P_{n}}(I)$.

Appendix 0.B Proofs and Intermediate Results

0.B.1 Proof of Proposition 2

The reduction is from the complement of the embedding problem for finite semigroups, shown to be undecidable in [20], and it is itself an adaptation of the proof of Theorem 7.2 in [2]. Note that, since we do not intend to add attributes nor relations in the procedures of this proof, we can drop the named definition of queries, treating CQs now as normal conjunctions of relational atoms.

The embedding problem for finite semigroups problem can be stated as follows. Consider a pair $\textbf{A}=(A,g)$, where $A$ is a finite set and $g:A\times A\rightarrow A$ is a partial associative function. We say that A is embeddable in a finite semigroup is there exists $\textbf{B}=(B,f)$ such that $A\subseteq B$ and $f:B\times B\rightarrow B$ is a total associative function. The embedding problem for finite semigroups is to decide whether an arbitrary $\textbf{A}=(A,g)$ is embeddable in a finite semigroup.

Consider the schema ${\cal S}=\{C(\cdot,\cdot),E(\cdot,\cdot),N(\cdot,\cdot),G(\cdot,\cdot,\cdot),F(\cdot),D(\cdot)\}$. The idea of the proof is as follows. We use relation $G$ to encode binary functions, so that a tuple $(a,b,c)$ in $G$ intuitively corresponds to saying that $g(a,b)=c$, for a function $g$. Using our procedure we shall mandate that the binary function encoded in $G$ is total and associative. We then encode $\textbf{A}=(A,g)$ into our input instance $I$: the procedure will then try to embed $A$ into a semigroup whose function is total.

In order to construct the procedures, we first specify the following set $\Sigma$ of tgds. First we add to $\Sigma$ a set of dependencies ensuring that all elements in the relation $G$ are collected into $D$:

[TABLE]

The next set verifies that $G$ is total and associative:

[TABLE]

Next we include dependencies that are intended to force relation $E$ to be an equivalence relation over all elements in the domain of $G$.

[TABLE]

The next set of dependencies we add $\Sigma$ ensure that $G$ represents a function that is consistent with the equivalence relation $E$.

[TABLE]

The final tgd in $\Sigma$ serves us to collect possible errors when trying to embed $\textbf{A}=(A,g)$. The intuition for this tgd will be made clear once we outline the reduction, but the idea is to state that the relation $F$ now contains everything that is in $R$, as long as a certain property holds on relations $E$, $C$ and $N$.

[TABLE]

Let then $\Sigma$ consists of tgds (1)-(11). We construct fixed procedures $P_{1}=(\textit{Scope}^{1},\mathcal{C}_{\text{in}}^{1},\mathcal{C}_{\text{out}}^{1},\mathcal{Q}_{\text{safe}}^{1})$ and $P_{2}=(\textit{Scope}^{2},\mathcal{C}_{\text{in}}^{2},\mathcal{C}_{\text{out}}^{2},\mathcal{Q}_{\text{safe}}^{2})$ as follows.

procedure $P_{1}$:

$\textit{Scope}^{1}$: The scope of $P_{1}$ consists of relations $G$, $E$, $D$ and $F$, which corresponds to the constraints $\{G[*],E[*],D[*],F[*]\}$.

$\mathcal{C}_{\text{in}}^{1}$: There are no preconditions for this procedure.

$\mathcal{C}_{\text{out}}^{1}$: The postconditions are the tgds in $\Sigma$.

$\mathcal{Q}_{\text{safe}}^{1}$: This query ensures that no information is deleted from all of $G$, $E$ and $F$ (and thus that no attributes are added to them): $G(x,y,z)\wedge E(u,v)\wedge D(w)\wedge F(p)$.

procedure $P_{2}$:

$\textit{Scope}^{2}$: The scope of $P_{2}$ is empty.

$\mathcal{C}_{\text{in}}^{2}$: The precondition for this constraint is $R(x)\rightarrow F(x)$.

$\mathcal{C}_{\text{out}}^{2}$: The are no postconditions.

$\mathcal{Q}_{\text{safe}}^{2}$: There is no safety query.

Note that $P_{2}$ does not really do anything, it is only there to check that $R$ is contained in $F$. We can now state the reduction. On input $\textbf{A}=(A,g)$, where $A=\{a_{1},\dots,a_{n}\}$, we construct an instance $I_{\textbf{A}}$ given by the following interpretations:

• •

$E^{I_{\textbf{A}}}$ contains the pair $(a_{i},a_{i})$ for each $1\leq i\leq n$ (that is, for each element of $A$);

• •

$G^{I_{\textbf{A}}}$ contains the triple $(a_{i},a_{j},a_{k})$ for each $a_{i},a_{j},a_{k}\in A$ such that $g(a_{i},a_{j})=a_{k}$;

• •

$D^{I_{\textbf{A}}}$ and $F^{I_{\textbf{A}}}$ are empty, while $R^{I_{\textbf{A}}}$ contains a single element $d$ not in $A$;

• •

$C^{I_{\textbf{A}}}$ contains the pair $(i,a_{i})$ for each $1\leq i\leq n$; and

• •

$N^{I_{\textbf{A}}}$ contains the pair $(i,j)$ for each $i\neq j$, $1\leq i\leq n$ and $1\leq j\leq n$.

Let us now show $\textbf{A}=(A,g)$ is embeddable in a finite semigroup if and only if $\textit{outcome}_{P_{1}}(I)$ contains an instance $I$ such that $I^{\prime}$ does not satisfy the precondition $R(x)\rightarrow F(x)$ of procedure $P_{2}$.

($\Longrightarrow$) Assume that $\textbf{A}=(A,g)$ is embeddable in a finite semigroup, say the semigroup $\textbf{B}=(B,f)$, where $f$ is total. Let $J$ be the instance such that $E^{J}$ is the identity over $B$, $D^{J}=B$ and $G^{J}$ contains a pair $(b_{1},b_{2},b_{3})$ if and only if $f(b_{1},b_{2})=b_{3}$; $F^{J}$ is empty and relations $N$, $C$ and $R$ are interpreted as in $I_{\textbf{A}}$. It is easy to see that $J\models\Sigma$, $Q_{{\cal S}\setminus\textit{Scope}}$ is preserved and that $\mathcal{Q}_{\text{safe}}(I_{\textbf{A}})\subseteq\mathcal{Q}_{\text{safe}}(J)$, this last because A was said to be embeddable in B. We have that $J$ then does belong to $\textit{outcome}_{P_{1}}(I)$, but $J$ does not satisfy the constraint $R(x)\rightarrow F(x)$.

($\Longleftarrow$) Assume now that there is an instance $J\in\textit{outcome}_{P_{1}}(I)$ that does not satisfy $R(x)\rightarrow F(x)$. Note that, because of the scope of $P_{1}$, the interpretation of $C$, $N$ and $R$ of $J$ must be just as in $I$. Thus it must be that the element $d$ is not in $F^{J}$, because it is the only element in $R^{J}$. Construct a finite semigroup $\textbf{B}=(B,f)$ as follows. Let $B$ consists of one representative of each equivalence class in $E^{J}$, with the additional restriction that each $a_{i}$ in $A$ must be picked as its representative. Further, define $f(b_{1},b_{2})=b_{3}$ if and only if $G(b_{1},b_{2},b_{3})$ is in $G$. Note that $J$ satisfies the tgds in $\Sigma$, in particular $G$ is associative and $E$ acts as en equivalence relation over $G$, which means that $f$ is indeed associative, total, and well defined. It remains to show that A can be embedded in B, but since $G^{J}$ and $E^{J}$ are supersets of $G^{I_{\textbf{A}}}$ and $E^{I_{\textbf{A}}}$ (because of the safety query of $P_{1}$), all we need to show is that each $a_{i}$ is in a separate equivalence relation. But this hold because of tgd (11) in $\Sigma$: if two elements from $A$ are in the same equivalence relation then the left hand side of (11) would hold in $I_{\textbf{A}}$, which contradicts the fact that $F^{J}$ does not contain $d$.

0.B.2 Proof of Proposition 3

Let $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$. We first show how to construct, for each instance $I$ over a schema ${\cal S}$, the minimal schema ${\cal S}_{\text{min}}$ such that all pairs $(J,{\cal S}^{\prime})$ that are possible outcomes of applying $P$ over $(I,{\cal S})$ are such that ${\cal S}^{\prime}$ extend ${\cal S}_{\text{min}}$.

The algorithm receives a procedure $P$ and a schema ${\cal S}$ and outputs either ${\cal S}_{\text{min}}$, if the procedure is applicable, or a failure signal in case there is no schema satisfying the output constraints of the procedure. Along the algorithm we will be assigning numbers to some of the relations in ${\cal S}_{\text{min}}$. This is important to be able to decide failure.

**Algorithm $A(P,{\cal S})$ for constructing ${\cal S}_{\text{min}}$

Input**: procedure $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}}$ and schema ${\cal S}$.

Output: either failiure or a schema ${\cal S}_{\text{min}}$.

If ${\cal S}$ does not satisfy the structural constraints in $\mathcal{C}_{\text{in}}$ or is not compatible with either $\mathcal{Q}_{\text{safe}}$ or $Q_{{\cal S}\setminus\textit{Scope}}$, output failure. Otherwise, continue. 2. 2.

Start with ${\cal S}_{\text{min}}=\emptyset$. 3. 3.

For each total query $R$ in $\mathcal{Q}_{\text{safe}}$, assume that $|{\cal S}(R)|=k$. Set ${\cal S}_{\text{min}}(R)={\cal S}(R)$, and label $R$ with $k$. 4. 4.

Add to ${\cal S}_{\text{min}}$ all relations $R$ mentioned in an atom $R[*]$ in $\mathcal{C}_{\text{out}}$ (if they are not already part of ${\cal S}_{\text{min}}$), without associating any attributes to them 5. 5.

In the following instructions we construct a set $\Gamma(P,{\cal S})$ of pairs of relations and attributes. Intuitively, a pair $(R,\{a_{1},\dots,a_{n}\})$ in $\Gamma(P,{\cal S})$ states that each schema in the output of $P$ must contain a relation $R$ with attributes $a_{1},\dots,a_{n}$.

• •

For each relation $R$ in ${\cal S}$ that is not mentioned in Scope, add to $\Gamma(P,{\cal S})$ the pair $(R,{\cal S}(R))$.

• •

For each constraint $R[a_{1},\dots,a_{n}]$ in Scope, add the pair $(R,{\cal S}(R)\setminus\{a_{1},\dots,a_{n}\})$ to $\Gamma(P,{\cal S})$.

• •

For each atom $R(a_{1}:x_{1},\dots,a_{n}:x_{n})$ in $\mathcal{Q}_{\text{safe}}$ add to $\Gamma(P,{\cal S})$ the pair $(R,\{a_{1},\dots,a_{n}\})$.

• •

For each atom $R(a_{1}:x_{1},\dots,a_{n}:x_{n})$ in a tgd or egd in $\mathcal{C}_{\text{out}}$ add to $\Gamma(P,{\cal S})$ the pair $(R,\{a_{1},\dots,a_{n}\})$.

• •

For each constraint $R[a_{1},\dots,a_{n}]$ in $\mathcal{C}_{\text{out}}$, add to $\Gamma(P,{\cal S})$ the pair $(R,\{a_{1},\dots,a_{n}\})$. 6. 6.

For each pair $(R,A)$ in $\Gamma(P,{\cal S})$, do the following.

• •

If $R$ is not yet in ${\cal S}_{\text{min}}$, add $R$ to ${\cal S}_{\text{min}}$ and set ${\cal S}_{\text{min}}(R)=A$;

• •

If $R$ is in ${\cal S}_{\text{min}}$, update ${\cal S}_{\text{min}}(R)={\cal S}_{\text{min}}(R)\cup A$. 7. 7.

If ${\cal S}_{\text{min}}$ contains a relation $R$ labelled with a number $n$ where, ${\cal S}_{\text{min}}(R)>n$, output failure. Otherwise output ${\cal S}_{\text{min}}$.

By direct inspection of the algorithm, we can state the following.

Proposition 9

Let $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ be a relational procedure and ${\cal S}$ a relational schema. Then for each relation $R$ in ${\cal S}_{\text{min}}$ with attributes $\{a_{1},\dots,a_{n}\}$, every instance $I$ over ${\cal S}$ and every pair $(J,{\cal S}^{\prime})$ in the outcome of applying $P$ to $(I)$, we have that ${\cal S}(R)$ is defined, with $\{a_{1},\dots,a_{n}\}\subseteq{\cal S}(R)$.

Furthermore, the following lemma specifies, in a sense, the correctness of the algorithm.

Lemma 1

Let $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ be a relational procedure and ${\cal S}$ a relational schema. Then:

• i)

If $A(P,{\cal S})$ outputs failure, either $P$ cannot be applied over any instance $I$ over ${\cal S}$, or for each instance $I$ over ${\cal S}$ the set $\textit{outcome}_{P}(I)$ is empty.

• ii)

If $A(P,{\cal S})$ outputs ${\cal S}_{\text{min}}$, then the schema of any instance in $\textit{outcome}_{P}(I)$ extends ${\cal S}_{\text{min}}$.

Proof

For i), not that if some of the components of $P$ are not compatible with ${\cal S}$, or ${\cal S}$ does not satisfy the constraints in $\mathcal{C}_{\text{in}}$, then clearly $P$ cannot be applied over any instance $I$ over ${\cal S}$. Assume then that ${\cal S}$ satisfies all compatibilities and preconditions in $P$, but $A(P,{\cal S})$ outputs failure. Then ${\cal S}_{\text{min}}$ contains a relation $R$ such that $|{\cal S}_{\text{min}}(R)|=m$, but $R$ is labelled with number $k$, for $k<\ell$. From the algorithm, we this implies that $|{\cal S}_{\text{min}}(R)|>|{\cal S}(R)$, but that there is a query $R$ in $\mathcal{Q}_{\text{safe}}$. Clearly, $\mathcal{Q}_{\text{safe}}$ cannot be preserved under any outcome, since by Observation 9 we require the schemas of outcomes to assign more attributes to $R$ than those assigned by ${\cal S}_{\text{min}}$, and thus the cardinality of tuples in the answer of $R$ differs between $I$ and its possible outcomes. Finally, item ii) is a direct consequence of Observation 9.

Note that the algorithm $(A,P)$ runs in polynomial time, and that the total size of ${\cal S}_{\text{min}}$ (measured as the number of relations and attributes) is at most the size of ${\cal S}$ and $P$ combined. Thus, to decide the applicability problem for a sequence $P_{1},\dots,P_{n}$ of procedures, all we need to do is to perform subsequent calls to the algorithm, setting ${\cal S}_{0}={\cal S}$ and then using ${\cal S}_{i}=A(P_{i},{\cal S}_{i-1})$ as the input for the next procedures. If $A(P_{n},{\cal S}_{n-1})$ outputs a schema, then the answer to the applicability problem is affirmative, otherwise if some call to $A(P_{i},{\cal S}{i-1})$ outputs failure, the answer is negative.

0.B.3 Proof of Proposition 4

This proof is a simple adaptation of the reduction we used in the proof of Proposition 2. Indeed, consider again the schema ${\cal S}$ from this proof, and the procedure $P$ given by:

Scope: The scope of $P$ consists of relations $G$, $E$, $D$ and $F$, which corresponds to the constraints $G[*],E[*],D[*]$ and $F[*]$.

$\mathcal{C}_{\text{in}}$: There are no preconditions for this procedure.

$\mathcal{C}_{\text{out}}$: The postconditions are the tgds in $\Sigma$ plus the tgd $F(x)\rightarrow R(x)$.

$\mathcal{Q}_{\text{safe}}$: This query ensures that no information is deleted from all of $G$, $E$ and $F$: $G(x,y,z)\wedge E(u,v)\wedge D(w)\wedge F(p)$.

Given a finite semigroup A, we construct now the following instance $I$:

• •

$E^{I_{\textbf{A}}}$ contains the pair $(a_{i},a_{i})$ for each $1\leq i\leq n$ (that is, for each element of $A$);

• •

$G^{I_{\textbf{A}}}$ contains the triple $(a_{i},a_{j},a_{k})$ for each $a_{i},a_{j},a_{k}\in A$ such that $g(a_{i},a_{j})=a_{k}$;

• •

All of $D^{I_{\textbf{A}}}$, $F^{I_{\textbf{A}}}$ and $R^{I_{\textbf{A}}}$ are empty;

• •

$C^{I_{\textbf{A}}}$ contains the pair $(i,a_{i})$ for each $1\leq i\leq n$; and

• •

$N^{I_{\textbf{A}}}$ contains the pair $(i,j)$ for each $i\neq j$, $1\leq i\leq n$ and $1\leq j\leq n$.

By a similar argument as the one used in the proof of Proposition 2, one can show that $\textit{outcome}_{P}(I)$ has an instance if and only if A is embeddable in a finite semigroup. The intuition is that now we are adding the constraint $F(x)\rightarrow R(x)$ as a postcondition, and since $R$ is not part of the scope of the procedure the only way to satisfy this restriction is if we do not fire the tgd (11) of the set $\Sigma$ constructed in the aforementioned proof. This, in turn, can only happen if A is embeddable.

0.B.4 Proof of Proposition 6

The reduction, just as that of Proposition 2, is by reduction from the embedding problem for finite semigroups, and builds up from this proposition. Let us start by defining the procedures $P_{1}$, $P_{2}$ and $P_{3}$.

For procedure $P_{1}$ we first build a set $\Gamma_{1}$ of tgs. This set is similar to the set $\Sigma$ used in Proposition 2, but using three additional dummy relations $G^{d}$, $E^{d}$ and $G^{\text{binary}}$.

First we add to $\Gamma_{1}$ dependencies that collect elements of $G$ into $D$, and that initialize $E$ as a reflexive relation.

[TABLE]

Next the dependency that states that $F$ contains everything in $R$ if some conditions about $E$ occur.

[TABLE]

The dependencies that assured that $E$ was an equivalence relation where acyclic, so we replace the right hand side with a dummy relation.

[TABLE]

Next come the dependencies assuring $G$ is a total and associative function, using also dummy relations.

[TABLE]

Finally, the dependencies that were supposed to ensure that $E$ worked as the equality over function $G$, using again the dummy relations.

[TABLE]

We can now define procedure $P_{1}$:

Scope: The scope of $P_{1}$ consists of relations $G$, $E$, $D$, $F$, $G^{d}$, $E^{d}$ and $G^{\text{binary}}$ which corresponds to the constraints $G[*],E[*],D[*],F[*],E^{d}[*],G^{d}[*]$ and $G^{\text{binary}}[*]$.

$\mathcal{C}_{\text{in}}$: There are no preconditions for this procedure.

$\mathcal{C}_{\text{out}}$: The postconditions are the tgds in $\Gamma_{1}$.

$\mathcal{Q}_{\text{safe}}$: This query ensures that no information is deleted from all of $G$, $E$, $F$, $G^{d}$, $E^{d}$ and $G^{\text{binary}}$: $G(x,y,z)\wedge E(u,v)\wedge D(w)\wedge F(p)\wedge G^{d}(x^{\prime},y^{\prime},z^{\prime})\wedge E^{d}(u^{\prime},v^{\prime})\wedge G^{\text{binary}}(a,b)$.

Note that, even though relations $G$ and $E$ are not mentioned in the right hand side of any tgd in $\Gamma_{1}$, they are part of the scope and thus they could be modified by the procedures $P_{1}$.

The procedure $P_{2}$ has no scope, no safety queries, no precondition, and the only postcondition is the presence of a third attribute, say $C$, in $G^{\text{binary}}$, by using a structural constraint $G^{\text{binary}}[A,B,C]$ (to maintain consistency with our unnamed perspective, we assume that these three attributes are ordered $A<_{\mathcal{A}}B<_{\mathcal{A}}C$).

To define the final procedure, consider the following set of tgds $\Gamma_{3}$.

[TABLE]

Then we define procedure $P_{3}$ is as follows.

Scope: The scope of $P_{3}$ is again empty.

$\mathcal{C}_{\text{in}}$: There are no preconditions for this procedure.

$\mathcal{C}_{\text{out}}$: The postconditions are the tgds in $\Gamma_{3}$.

$\mathcal{Q}_{\text{safe}}$: There are also no safety queries for this procedure.

Let ${\cal S}$ be the schema containing relations $G$, $E$, $D$, $F$, $F^{\text{check}}$, $G^{d}$, $E^{d}$ and $G^{\text{binary}}$ and $R$. The attribute names are of no importance for this proof, except for $G^{\text{binary}}$, which associates attributes $A$ and $B$.

Given a finite semigroup A, we construct now the following instance $I_{\textbf{A}}$:

• •

$E^{I_{\textbf{A}}}$ contains the pair $(a_{i},a_{i})$ for each $1\leq i\leq n$ (that is, for each element of $A$);

• •

$G^{I_{\textbf{A}}}$ contains the triple $(a_{i},a_{j},a_{k})$ for each $a_{i},a_{j},a_{k}\in A$ such that $g(a_{i},a_{j})=a_{k}$;

• •

All of $D^{I_{\textbf{A}}}$, $F^{I_{\textbf{A}}}$ and ${F^{\text{check}}}^{I_{\textbf{A}}}$ are empty;

• •

$R^{I_{\textbf{A}}}$ has a single element $d$ not used elsewhere in $I_{\textbf{A}}$

• •

$C^{I_{\textbf{A}}}$ contains the pair $(i,a_{i})$ for each $1\leq i\leq n$; and

• •

$N^{I_{\textbf{A}}}$ contains the pair $(i,j)$ for each $i\neq j$, $1\leq i\leq n$ and $1\leq j\leq n$.

Let us now show $\textbf{A}=(A,g)$ is embeddable in a finite semigroup if and only if $\textit{outcome}_{P_{1},P_{2},P_{3}}(I)$ is nonempty.

($\Longrightarrow$) Assume that $\textbf{A}=(A,g)$ is embeddable in a finite semigroup, say the semigroup $\textbf{B}=(B,f)$, where $f$ is total. Let $J$ be the instance over ${\cal S}$ such that both ${E^{d}}^{J}$ and $E^{J}$ are the identity over $B$, $D^{J}=B$, both ${G^{d}}^{J}$ and $G^{J}$ contains a pair $(b_{1},b_{2},b_{3})$ if and only if $f(b_{1},b_{2})=b_{3}$; ${G^{\text{binary}}}^{J}$ is the projection of $G^{J}$ over its two first attirbutes, $F^{J}$ and ${F^{\text{check}}}^{J}$ are empty and relations $N$, $C$ and $R$ are interpreted as in $I_{\textbf{A}}$.

It is easy to see that $J$ is in the outcome of applying $P_{1}$ over $I$. Now, let ${\cal S}^{\prime}$ be the extension of ${\cal S}$ where $G^{\text{binary}}$ has an extra attribute, $C$, and $K$ is an instance over ${\cal S}^{\prime}$ that is just like $J$ except that ${G^{\text{binary}}}^{K}$ is now the same as $G^{J}$ (and therefore $G^{K}$). By definition we obtain that $K$ is a possible outcome of applying $P_{2}$ over $J$, and therefore $K$ is in $\textit{outcome}_{P_{1},P_{2}}(I)$. Furthermore, one can see that the same instance $K$ is again an outcome of applying $P_{3}$ over $K$, therefore obtaining that $\textit{outcome}_{P_{1},P_{2},P_{3}}(I)$ is nonempty.

($\Longleftarrow$) Assume now that there is an instance $L\in\textit{outcome}_{P_{1},P_{2},P_{3}}(I)$. Then by definition there are instances $J$ and $K$ such that $J$ is in $\textit{outcome}_{P_{1}}(I)$, $K$ is in $\textit{outcome}_{P_{2}}(J)$ and $L$ is in $\textit{outcome}_{P_{3}}(K)$.

Let $J^{*}$ be the restriction of $J$ over the schema ${\cal S}$. From a simple inspection of $P_{1}$ we have that $J^{*}$ satisfies as well the dependencies in $P_{1}$, so that $J^{*}$ is in $\textit{outcome}_{P_{1}}(I)$.

Let now ${\cal S}^{\prime}$ be the extension of ${\cal S}$ that assigns also attribute $C$ to $G^{\text{binary}}$. Now, since $K$ is an outcome of $P_{2}$ over $J$ and $P_{2}$ has no scope, if we define $K^{*}$ as the restriction of $K$ over ${\cal S}^{\prime}$, then clearly $K^{*}$ must be in the outcome of applying $P_{2}$ over $J^{*}$. Note that, by definition of $P_{3}$ (since its scope is empty), the restriction of $L$ up to the schema of $K$ must be the same instance as $K$, and therefore the restriction $L^{*}$ of $L$ to ${\cal S}^{\prime}$ must be the same instance than $K^{*}$. Furthermore, since $L$ (and thus $L^{*}$) satisfies the constraints in $P_{3}$, and the constraints only mention relations and atoms in ${\cal S}^{\prime}$, we have that $K^{*}$ must be an outcome of applying $P_{3}$ over $(K^{*},{\cal S}^{\prime})$.

We now claim that $K^{*}$ satisfy all tgds (1)-(11) in the proof of Propositon 2. Tgds (1-3) and (6) are immediate from the scopes of procedures, and the satisfaction for all the remaining ones is shown in the same way. For example, to see that $K^{*}$ satisfies $E(x,y)\rightarrow E(y,x)$, note that $J^{*}$ already satisfies $E(x,y)\rightarrow E^{d}(y,x)$. From the fact that the interpretations of $E^{d}$ and $E$ are the same over $J^{*}$ and $K^{*}$ and that $K^{*}$ satisfies $E^{d}(x,y)\rightarrow E(x,y)$ we obtain the desired result.

Finally, since $K^{*}$ satisfies $F(x)\ \rightarrow\ F^{\text{check}}(x)$, and the interpretation of $F^{\text{check}}$ over all of $I$, $J^{*}$ and $K^{*}$ must be empty, we have that the interpretation of $F$ over $K^{*}$ is empty as well. Given that $K^{*}$ satisfies all dependencies in $\Sigma$, it must be the case that the left hand side of the tgd (11) is not true $K^{*}$, for any possible assignment. By using the same argument as in the proof of Proposition 2 we obtain that $\textbf{A}=(A,g)$ is embeddable in a finite semigroup.

0.B.5 Proof of Theorem 5.1

This theorem is an immediate corollary of Proposition 7, together with an inspection on the complexity of computing the over-approximation. We provide all details in the proof of the next proposition (Proposition 7).

0.B.6 Proof of Proposition 7

For the proof we assume that all procedures does not use preconditions. We can treat them by first doing an initial check on compatibility that only complicates the proof.

We also specify an alternative set of representatives for conditional instances (which is actually the usual one). The set $\hat{\textit{rep}}(G)$ of representatives of a conditional instance $G$ is simply $\hat{\textit{rep}}(G)=\{I\mid$ there is a substitution $\nu$ such that $\nu(T)\subseteq I\}$. That is, $\hat{\textit{rep}}(G)$ only specifies instances over the same schema as $G$. The following lemma allows us to work with this representation instead; it is immediate from the definition of safe scope procedures.

Lemma 2

If $G$ is a conditional instance, then (1) $\hat{\textit{rep}}(G)\subseteq\textit{rep}(G)$, and (2) an instance $J$ is minimal for $\textit{rep}(G)$ if and only if it is minimal for $\hat{\textit{rep}}(G)$.

Moreover, from the fact that procedures with safe scope are acyclic, we can state Theorem 5.1 in [2] in the following terms:

Lemma 3 ([2])

Given a set $\Sigma$ of tgds and a positive conditional instance $G$, one can construct, in polynomial time, a positive conditional instance $G^{\prime}$ such that (1) $\hat{\textit{rep}}(G^{\prime})\subseteq\hat{\textit{rep}}(G)$ and (2) all minimal models of $\hat{\textit{rep}}(G^{\prime})$ satisfy $\Sigma$.

Moreover, by slightly adapting the proof of Proposition 4.6 in [2], we can see that the conditional instance constructed above has even better properties. In order to prove this theorem all that one needs to do is to adapt the notion of solutions for data exchange into a scenario where the target instance may already have some tuples (which will not fire any dependencies because of the safeness of procedures).

Lemma 4 ([2])

Let $P=\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}}$ be a procedure with safe scope, and let $G$ be a positive conditional instance. Then one can construct a conditional instance $G^{\prime}$ such that, for every minimal instance $I$ of $\hat{\textit{rep}}(G)$, the set $\hat{\textit{rep}}(G^{\prime})$ contains all minimal instances in $\textit{outcome}_{P}(I)$, and for every minmal instance $J$ in $\hat{\textit{rep}}(G^{\prime})$ there is a minimal instance $I$ of $\textit{rep}(G)$ such that $J$ is minimal for $\textit{outcome}_{P}(I)$.

Finally, we can show the key result for this proof.

Lemma 5

Let ${\cal I}$ be a set of instances, and $G$ a conditional table that is minimal for ${\cal I}$, and $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ a procedure with safe scope. Then either $\textit{outcome}_{P}({\cal I})=\emptyset$ or one can construct, in polynomial time, a conditional instance $G^{\prime}$ such that

• i)

$\textit{outcome}_{P}({\cal I})\subseteq\textit{rep}(G^{\prime})$; and

• ii)

If $J$ is a minimal instance in $\textit{rep}(G^{\prime})$, then $J$ is also minimal in $\textit{outcome}_{P}({\cal I})$.

Proof

Using the chase procedure mentioned in Lemma 4, we see that the conditional table $G^{\prime}$ produced in this lemma satisfies the conditions of this Lemma, for $\hat{\textit{rep}}(G)$.

For i), let $J$ be an instance in $\textit{outcome}_{P}({\cal I})$. Then there is an instance $I$ in ${\cal I}$ such that $J\in\textit{outcome}{P}(I)$. Let $I^{*}$ be a minimal instance in ${\cal I}$ such that $I$ extends $I^{*}$. By our assumption we know that $I^{*}$ belongs to $\textit{rep}(G)$, and since $I^{*}$ is minimal it must be the case that $I^{*}$ belongs (and is minimal) for $\hat{\textit{rep}}(G)$. Therefore, by Lemma 4 we have that $\hat{\textit{rep}}(G^{\prime})$ contains all minimal instances for $\textit{outcome}_{P}(I^{*})$. But now notice that for every assignment $\tau$ and tgd $\lambda$ such that $(I^{*},\tau)$ satisfies $\lambda$, we have that $(I,\tau)$ satisfy $\lambda$ as well. This means that every instance in the set $\textit{outcome}_{P}(I)$ must extend a minimal instance in $\textit{outcome}_{P}(I^{*})$ (if not, then a tgd would not be satisfied due to some assignment that would not be possible to extend). Since every minimal instance in $\textit{outcome}_{P}(I^{*})$ is in $\hat{\textit{rep}}(G^{\prime})$, then by the semantics of conditional tables it must be the case that $J$ belongs to $\hat{\textit{rep}}(G^{\prime})$ as well, and therefore to $\textit{rep}(G^{\prime})$.

Item [ii)] follows from the fact that any minimal instance in $\textit{rep}(G^{\prime})$ must also be minimal for $\hat{\textit{rep}}(G^{\prime})$ and a direct application of Lemma 4.

The next Lemma constructs the desired outcomes for alter schema procedures.

Lemma 6

Let ${\cal I}$ be a set of instances, and $G$ a conditional table that is minimal for ${\cal I}$, and $P=(\textit{Scope},\mathcal{C}_{\text{in}},\mathcal{C}_{\text{out}},\mathcal{Q}_{\text{safe}})$ an alter schema procedure. Then either $\textit{outcome}_{P}({\cal I})=\emptyset$ or one can construct, in polynomial time, a conditional instance $G^{\prime}$ such that

• i)

$\textit{outcome}_{P}({\cal I})\subseteq\textit{rep}(G^{\prime})$; and

• ii)

If $J$ is a minimal instance in $\textit{rep}(G^{\prime})$, then $J$ is also minimal in $\textit{outcome}_{P}({\cal I})$.

Proof

Assume that $\textit{outcome}_{P}({\cal I})\neq\emptyset$ (this can be easily checked in polynomial time). Then one can compute the schema ${\cal S}_{\text{min}}$ from the proof of Proposition 3. This schema will add some attributes to some relations in the schema of $G$, and possibly some other relations with other sets of attributes. Let $\text{Schema}(G)={\cal S}$.

We extend $G$ to a conditional table $G^{\prime}$ over ${\cal S}_{\text{min}}$ as follows:

For every relation $R$ such that ${\cal S}_{\text{min}}(R)\setminus{\cal S}(R)=\{A_{1},\dots,A_{n}\}$, with $n\geq 1$, for tuples from $G^{\prime}$ by adding to each tuple in $G$ a fresh null value in each of the attributes $A_{1},\dots,A_{n}$. 2. 2.

For every relation $R$ such that ${\cal S}(R)$ is not defined, but ${\cal S}_{\text{min}}(R)$ is defined, set $R^{G^{\prime}}=\emptyset$

The properties of the lemma now follow from a straightforward check.

The proof of Proposition 7 now follows from successive applications of Lemmas 6 and 5: one just need to compute the appropriate conditional table for each procedure in the sequence $P_{1},\dots,P_{n}$. That each construction is in polynomial size if the number $n$ of procedures is fixed, or exponential in other case, follows also from these Lemmas, as the size of the conditional table $G^{\prime}$, for a procedure $P$ and a conditional table $G$, is at most polynomial in $G$ and $P$ (and thus we are composing a polynomial number of polynomials, or a fixed number if $n$ is fixed).

Proof of Theorem 5.1: While in general checking that the set represented by an arbitrary conditional instance may be np-complete, we note that in [2] it was shown that, for Lemma 5, all that is needed is a positive conditional instance, and clearly deciding whether a positive conditional instance represents at least one solution is in polynomial time. Thus, for the proof of the Theorem we just compute the (positive) conditional instance exhibited for Proposition 7 and then do the polynomial check on the size of the final conditional instance.