Foreign Key Constraints Research Articles

DP-starJ: A Differential Private Scheme towards Analytical Star-Join Queries

Star-join query is the fundamental task in data warehouse and has wide applications in On-line Analytical Processing (olap) scenarios. Due to the large number of foreign key constraints and the asymmetric effect in the neighboring instance between the fact and dimension tables, even those latest dp efforts specifically designed for join, if directly applied to star-join query, will suffer from extremely large estimation errors and expensive computational cost. In this paper, we are thus motivated to propose DP-starJ, a novel Differentially Private framework for star-Join queries. DP-starJ consists of a series of strategies tailored to specific features of star-join, including 1) we unveil the different effects of fact and dimension tables on the neighboring database instances, and accordingly revisit the definitions tailored to different cases of star-join; 2) we propose Predicate Mechanism (PM), which utilizes predicate perturbation to inject noise into the join procedure instead of the results; 3) to further boost the robust performance, we propose a dp-compliant star-join algorithm for various types of star-join tasks based on PM. We provide both theoretical analysis and empirical study, which demonstrate the superiority of the proposed methods over the state-of-the-art solutions in terms of accuracy, efficiency, and scalability.

R2T: Instance-optimal Truncation for Differentially Private Query Evaluation with Foreign Keys

Answering SPJA queries under differential privacy (DP), including graph pattern counting under node-DP as an important special case, has received considerable attention in recent years. The dual challenge of foreign-key constraints and self-joins is particularly tricky to deal with, and no existing DP mechanisms can correctly handle both. For the special case of graph pattern counting under node-DP, the existing mechanisms are correct (i.e., satisfy DP), but they do not offer nontrivial utility guarantees or are very complicated and costly. In this paper, we propose the first DP mechanism for answering arbitrary SPJA queries in a database with foreign-key constraints. Meanwhile, it achieves a fairly strong notion of optimality, which can be considered as a small and natural relaxation of instance optimality. Finally, our mechanism is simple enough that it can be easily implemented on top of any RDBMS and an LP solver. Experimental results show that it offers order-of-magnitude improvements in terms of utility over existing techniques, even those specifically designed for graph pattern counting.

Foreign Keys Open the Door for Faster Incremental View Maintenance

Serverless cloud-based warehousing systems enable users to create materialized views in order to speed up predictable and repeated query workloads. Incremental view maintenance (IVM) minimizes the time needed to bring a materialized view up-to-date. It allows the refresh of a materialized view solely based on the base table changes since the last refresh. In serverless cloud-based warehouses, IVM uses computations defined as SQL scripts that update the materialized view based on updates to its base tables. However, the scripts set up for materialized views with inner joins are not optimal in the presence of foreign key constraints. For instance, for a join of two tables, the state of the art IVM computations use a UNION ALL operator of two joins - one computing the contributions to the join from updates to the first table and the other one computing the remaining contributions from the second table. Knowing that one of the join keys is a foreign-key would allow us to prune all but one of the UNION ALL branches and obtain a more efficient IVM script. In this work, we explore ways of incorporating knowledge about foreign key into IVM in order to speed up its performance. Experiments in Redshift showed that the proposed technique improved the execution times of the whole refresh process up to 2 times, and up to 2.7 times the process of calculating the necessary changes that will be applied into the materialized view.

Cquirrel

We will demonstrate Cquirrel, a continuous query processing engine built on top of Flink. Cquirrel assumes a relational schema where the foreign-key constraints form a directed acyclic graph, and supports any selection-projection-join-aggregation query where all join conditions are between a primary key and a foreign key. It allows arbitrary updates to any of the relations, and outputs the deltas in the query answers in real-time. It provides much better support for multi-way joins than the native join operator in Flink. Meanwhile, it offers better performance, scalability, and fault tolerance than other continuous query processing engines.

Put relational databases in your data curation toolbox

ABSTRACTThis poster paper makes a case for the use of relational databases as a data curation tool, especially for datasets that are published separately but can be used together due to a common identifier scheme and shared attributes. The Correlates of War datasets are used as an illustrative example to show how the normalization process results in a design with greater data reusability, while check constraints and foreign key constraints can improve data quality.

An extended access control mechanism exploiting data dependencies

In general, access control mechanisms in DBMSs ensure that users access only those portions of data for which they have authorizations, according to a predefined set of access control policies. However, it has been shown that access control mechanisms might be not enough. A clear example is the inference problem due to functional dependencies, which might allow a user to discover unauthorized data by exploiting authorized data. In this paper, we wish to investigate data dependencies (e.g., functional dependencies, foreign key constraints, and knowledge-based implications) from a different perspective. In particular, the aim was to investigate data dependencies as a mean for increasing the DBMS utility, that is, the number of queries that can be safely answered, rather than as channels for releasing sensitive data. We believe that, under given circumstances, this unauthorized release may give more benefits than issues. As such, we present a query rewriting technique capable of extending defined access control policies by exploiting data dependencies, in order to authorize unauthorized but inferable data.

A framework for OWL DL based Ontology Construction from Relational Database using Mapping and Semantic Rules

Wide Web (WWW) is capable of retrieving the results only through location finding of search terms, where as Semantic web retrieves by analysing the keywords because of the machine accessible nature of semantic data. One such Semantic data format is Ontology. Ontologies are the web documents generated by Web Ontology Language to provide more precise web content, thus by improving the performance of information retrieval. Semantic web requires data either in terms of manual creation or through conversion from existing data. Ontologies lack semantically rich data because the manual construction of these documents are time consuming and also the domain experts need to understand the syntax and semantics of Ontology development languages. One alternate to compensate for the rich set of data is to take the contents of Relational Data Base (RDB) for domain related applications in the Semantic Web. This is possible by mapping the RDB constructs into Ontology constructs. This paper proposes an RDB to Ontology mapping system framework which can generate an Ontology based on the proposed Mapping Rules for a Banking domain. The Mapping rules are generated both for, 1) direct mapping 2) integrity constraints mapping. Direct mapping is, RDB components like table, attribute, data are mapped to the corresponding Ontology components. Integrity constraints mapping covers primary key, foreign key and column constraints mapped to the relevant Ontology components. This is the direct translation of RDB structure. This paper also proposes additional Semantic Rules for the generated Ontology to provide richer semantics. This in turn provides more expressiveness in Ontology representation and also efficient reasoning power.

Test data generation for web application using a UML class diagram with OCL constraints

In this paper, we report on our current work toward efficient and effective verification of web application's basic design. We use a UML class diagram with Object Constraint Language (OCL) to describe the application behaviors and data constraints. Then we generate test data from the formally represented specifications. We make the observation that key web application behaviors can be captured through table size constraints as well as data constraints like foreign key constraints. Based on the observation, we translate the OCL specification into the equivalent constraints using table size expressions. We present a scheme to generate test data from the translated constraints using a Satisfiability Modulo Theories solver. We employ two techniques to reduce constraints. The first is string handling and the other is decomposition of table structures. We also report on an experimental result of test data generation. The result indicates a potential that our scheme works well for real applications in reasonable times.

On multi-column foreign key discovery

A foreign/primary key relationship between relational tables is one of the most important constraints in a database. From a data analysis perspective, discovering foreign keys is a crucial step in understanding and working with the data. Nevertheless, more often than not, foreign key constraints are not specified in the data, for various reasons; e.g., some associations are not known to designers but are inherent in the data, while others become invalid due to data inconsistencies. This work proposes a robust algorithm for discovering single-column and multi-column foreign keys. Previous work concentrated mostly on discovering single-column foreign keys using a variety of rules, like inclusion dependencies, column names, and minimum/maximum values. We first propose a general rule, termed Randomness , that subsumes a variety of other rules. We then develop efficient approximation algorithms for evaluating randomness, using only two passes over the data. Finally, we validate our approach via extensive experiments using real and synthetic datasets.

Polynomial time queries over inconsistent databases with functional dependencies and foreign keys

This paper addresses the problem of efficiently computing consistent answers to queries over relational databases which may be inconsistent with respect to functional dependencies and foreign key constraints. Since consistent query answers over inconsistent databases are obtained from repaired databases, we first present a repair strategy. More specifically, in this paper we consider particular sets of functional dependencies, called canonical, and a repair strategy whereby only tuple updates and insertions are allowed in order to restore consistency: if foreign key constraints are violated, new tuples (possibly containing null values) are inserted into the database, whereas if functional dependency violations occur, tuple updates (possibly introducing unknown values, i.e. special symbols which can take values from a limited set of constants of the source database) are performed. Therefore, we propose a semantics of constraint satisfaction for incomplete databases containing null and unknown values since the repair process can lead to such databases. The proposed approach allows us to obtain a unique (incomplete) repaired database which may be computed in polynomial time. Drawing on the results on the complexity of querying incomplete databases containing OR-objects, we identify classes of constraints for which the consistent answers to particular classes of conjunctive queries can be computed in polynomial time.

VQT: value cardinality and query pattern based R-schema to XML schema translation with implicit referential integrity

In this paper, we propose a new relational schema (R-schema) to XML schema translation algorithm, VQT, which analyzes the value cardinality and user query patterns and extracts the implicit referential integrities by using the cardinality property of foreign key constraints between columns and the equi-join characteristic in user queries. The VQT algorithm can apply the extracted implied referential integrity relation information to the R-schema and create an XML schema as the final result. Therefore, the VQT algorithm prevents the R-schema from being incorrectly converted into the XML schema, and it richly and powerfully represents all the information in the R-schema by creating an XML schema as the translation result on behalf of the XML DTD.

On the Complexity of Verifying Consistency of XML Specifications

XML specifications often consist of a type definition (typically, a document type definition (DTD)) and a set of integrity constraints. It has been shown previously that such specifications can be inconsistent, and thus it is often desirable to check consistency at compile time. It is known [W. Fan and L. Libkin, J. ACM, 49 (2002), pp. 368-406] that for general keys, foreign keys, and DTDs the consistency problem is undecidable; however, it becomes NP-complete when all keys are one-attribute (unary) and tractable, if no foreign keys are used. In this paper, we consider a variety of previously studied constraints for XML data and investigate the complexity of the consistency problem. Our main conclusion is that, in the presence of foreign key constraints, compile-time verification of consistency is infeasible. We look at absolute constraints that hold in the entire document and relative constraints that hold only in a part of the document. For absolute constraints, we prove decidability and establish complexity bounds for primary multiattribute keys and unary foreign keys and study unary constraints that involve regular expressions. For relative constraints, we prove that even for unary constraints the consistency problem is undecidable. We also show that results continue to hold for extended DTDs, a more expressive typing mechanism for XML.

Efficient Constraint Validation for Updated XML Database

XML constraints are either schema constraints representing rules about document structure (e.g. a DTD, an XML Schema definition or a specification in Relax-NG), or integrity constraints, which are rules about the values contained in documents (e.g. primary keys, foreign keys, etc.). We address the problem of incrementally verifying these constraints when documents are modified by updates. The structure of an XML document is a tree, whose nodes are element (or attribute) names and whose leaves are associated to values contained in the document. Considered updates are insertion, deletion or replacement of any subtree in the XML tree. Schema constraints are represented by tree automata and tree grammars. Key and foreign key constraints are represented by attribute grammars, adding semantic rules to schema grammars, to carry key and foreign key values (to verify their properties). Our incremental validation tests both schema and integrity constraints while treating the sequence of updates, in only one pass over the document. Only nodes involved in updates trigger validation tests. An analysis of complexity shows that worst cases are determined by the shape of the XML tree being processed (asymptotic upper bounds are presented). Experimental results show that our algorithms behave efficiently in practice.

View matching for outer-join views

Prior work on computing queries from materialized views has focused on views defined by expressions consisting of selection, projection, and inner joins, with an optional aggregation on top (SPJG views). This paper provides a view matching algorithm for views that may also contain outer joins (SPOJG views). The algorithm relies on a normal form for outer-join expressions and is not based on bottom-up syntactic matching of expressions. It handles any combination of inner and outer joins, deals correctly with SQL bag semantics, and exploits not-null constraints, uniqueness constraints and foreign key constraints.

Minimal-change integrity maintenance using tuple deletions

We address the problem of minimal-change integrity maintenance in the context of integrity constraints in relational databases. We assume that integrity-restoration actions are limited to tuple deletions. We focus on two basic computational issues: repair checking (is a database instance a repair of a given database?) and consistent query answers [in: ACM Symposium on Principles of Database Systems (PODS), 1999, 68] (is a tuple an answer to a given query in every repair of a given database?). We study the computational complexity of both problems, delineating the boundary between the tractable and the intractable cases. We consider denial constraints, general functional and inclusion dependencies, as well as key and foreign key constraints. Our results shed light on the computational feasibility of minimal-change integrity maintenance. The tractable cases should lead to practical implementations. The intractability results highlight the inherent limitations of any integrity enforcement mechanism, e.g., triggers or referential constraint actions, as a way of performing minimal-change integrity maintenance.

Data integration under integrity constraints

Data integration systems provide access to a set of heterogeneous, autonomous data sources through a so-called global schema. There are basically two approaches for designing a data integration system. In the global-as-view approach, one defines the elements of the global schema as views over the sources, whereas in the local-as-view approach, one characterizes the sources as views over the global schema. It is well known that processing queries in the latter approach is similar to query answering with incomplete information, and, therefore, is a complex task. On the other hand, it is a common opinion that query processing is much easier in the former approach. In this paper we show the surprising result that, when the global schema is expressed in the relational model with integrity constraints, even of simple types, the problem of incomplete information implicitly arises, making query processing difficult in the global-as-view approach as well. We then focus on global schemas with key and foreign key constraints, which represents a situation which is very common in practice, and we illustrate techniques for effectively answering queries posed to the data integration system in this case.

Exploiting constraint-like data characterizations in query optimization

Query optimizers nowadays draw upon many sources of information about the database to optimize queries. They employ runtime statistics in cost-based estimation of query plans. They employ integrity constraints in the query rewrite process. Primary and foreign key constraints have long played a role in the optimizer, both for rewrite opportunities and for providing more accurate cost predictions. More recently, other types of integrity constraints are being exploited by optimizers in commercial systems, for which certain semantic query optimization techniques have now been implemented. These new optimization strategies that exploit constraints hold the promise for good improvement. Their weakness, however, is that often the “constraints” that would be useful for optimization for a given database and workload are not explicitly available for the optimizer. Data mining tools can find such “constraints” that are true of the database, but then there is the question of how this information can be kept by the database system, and how to make this information available to, and effectively usable by, the optimizer. We present our work on soft constraints in DB2. A soft constraint is a syntactic statement equivalent to an integrity constraint declaration. A soft constraint is not really a constraint, per se, since future updates may undermine it. While a soft constraint is valid, however, it can be used by the optimizer in the same way integrity constraints are. We present two forms of soft constraint: absolute and statistical . An absolute soft constraint is consistent with respect to the current state of the database, just in the same way an integrity constraint must be. They can be used in rewrite, as well as in cost estimation. A statistical soft constraint differs in that it may have some degree of violation with respect to the state of the database. Thus, statistical soft constraints cannot be used in rewrite, but they can still be used in cost estimation. We are working long-term on absolute soft constraints. We discuss the issues involved in implementing a facility for absolute soft constraints in a database system (and in DB2), and the strategies that we are researching. The current DB2 optimizer is more amenable to adding facilities for statistical soft constraints. In the short-term, we have been implementing pathways in the optimizer for statistical soft constraints. We discuss this implementation.