The implication problem of data dependencies over SQL table definitions

Abstract

We investigate the implication problem for classes of data dependencies over SQL table definitions. Under Zaniolo's "no information" interpretation of null markers we establish an axiomatization and algorithms to decide the implication problem for the combined class of functional and multivalued dependencies in the presence of NOT NULL constraints. The resulting theory subsumes three previously orthogonal frameworks. We further show that the implication problem of this class is equivalent to that in a propositional fragment of Schaerf and Cadoli's [1995] family of para-consistent S-3 logics. In particular, S is the set of variables that correspond to attributes declared NOT NULL. We also show how our equivalences for multivalued dependencies can be extended to Delobel's class of full first-order hierarchical decompositions, and the equivalences for functional dependencies can be extended to arbitrary Boolean dependencies. These dualities allow us to transfer several findings from the propositional fragments to the corresponding classes of data dependencies, and vice versa. We show that our results also apply to Codd's null interpretation "value unknown at present", but not to Imielinski's [1989] or-relations utilizing Levene and Loizou's weak possible world semantics [Levene and Loizou 1998]. Our findings establish NOT NULL constraints as an effective mechanism to balance not only the certainty in database relations but also the expressiveness with the efficiency of entailment relations. They also control the degree by which the implication of data dependencies over total relations is soundly approximated in SQL table definitions. ACM Reference Format:Hartmann, S., and Link, S. 2012. The implication problem of data dependencies over SQL table definitions: Axiomatic, algorithmic and logical characterizations.

Frontiers for propositional reasoning about fragments of probabilistic conditional independence and hierarchical database decompositions

“…The equivalence between two-tuple relations for FOHDs and special truth assignments extend the existing equivalence between two-tuple relations for multivalued dependencies and special truth assignments [11,22,45].…”

“…Here, a new technique, only recently developed for multivalued dependencies [22], is generalized to define special probability models with two events of probability one half. Our technique therefore extends the existence of special probability models from the case of marginal SCI statements I(Y , Z | ∅) [17] to general SCI statements.…”

“…In this context, the implication problem for the class of saturated conditional independence statements was again characterized axiomatically, algorithmically, and logically. In particular, the algorithmic characterization is derived from an equivalence to the class of multivalued data dependencies over SQL databases with NOT NULL constraints [22]. Here, the de-facto industry standard SQL for data management permits the specification of arbitrary attributes as NOT NULL in order to disallow occurrences of a distinguished null marker in the column that the attribute denotes.…”

Possible and certain SQL keys

“…In particular, entity integrity is enforced by primary keys [34]. Core problems investigate reasoning [21], Armstrong databases [18], and discovery [22,24,32,33,40]. Applications include anomaly detection [43], consistency management [4], consistent query answers [3,28], data cleaning [17], exchange [16], fusion [36], integration [9], profiling [35], quality [38], repairs [6], and security [7], schema design [14], query optimization [23], transaction processing [2], and view maintenance [37].…”

Quantifying Integrity Impacts in Security Risk Scoring Models

“…Since then the Structures Query Language (SQL) is considered the industry standard for data management [6]. However, there exist several functionalities that distinguish SQL from the relational model [7] for instance dealing with null values. The implications of those differences are not well understood yet.…”