Efficiently Detecting Inclusion Dependencies

Abstract

Data sources for data integration often come with spurious schema definitions such as undefined foreign key constraints. Such metadata are important for querying the database and for database integration.We present our algorithm SPIDER (Single Pass Inclusion DEpendency Recognition) for detecting inclusion dependencies, as these are the automatically testable part of a foreign key constraint. For IND detection all pairs of attributes must be tested. SPIDER solves this task very efficiently by testing all attribute pairs in parallel. It analyzes a 2 GB database in ∼ 20 min and a 21 GB database in ∼ 4 h. Schema Discovery for Data IntegrationIn large integration projects one is often confronted with undocumented data sources. One important schema information are foreign key constraints, which are necessary for meaningful querying and for database integration.One example is the popular life science database Protein Data Bank (PDB) that can be imported into a relational schema using the OpenMMS 1 schema and parser. The OpenMMS schema defines 175 tables with 2, 705 attributes but not a single foreign key constraint.If foreign key constraints are not defined explicitly they are given implicitly by the data. Thus, we want to identify inclusion dependencies (INDs) in a schema as they are a precondition for foreign keys. An inclusion dependency A ⊆ B means that all values of the dependent attribute A are contained in the value set of the referenced attribute B. We call a pair of attributes A and B an IND candidate. An IND is satisfied if the IND requirements are met and unsatisfied otherwise. Obviously, a satisfied IND is only the 1 openmms.sdsc.edu automatically testable part of a foreign key. Whether a special IND corresponds to a semantically correct foreign key must be decided in a second step.The challenge in detecting INDs is the potentially large number of IND candidates as each pair of attributes must be tested. With n attributes this results in (n − 1) 2 IND candidates. To the best of our knowledge all previous approaches for unary IND detection restrict IND candidates by the attribute's data type as proposed in [5], i. e., only IND candidates are created with both attributes sharing the same data type. This restriction reduces the problem complexity heavily, but in life science databases we cannot use this restriction, because one cannot rely on datatypes.In this paper we present our algorithm SPIDER to detect unary INDs. SPIDER works in two phases: (i) all attribute value sets are sorted inside a RDBMS, and (ii) all IND candidates are tested in parallel while reading each attribute's value at most once. Its strength is the data structure that synchronizes all IND candidate tests efficiently without running into deadlocks or missing an IND.There are two approaches in related work on detecting unary Afterwards all IND candidates are tested in parallel exploiting the sets of attribute names. We shall show in Sec. 3 that SPIDER outperforms both approaches up to orders of magnitude.In [2] we tested the perfor...

On multi-column foreign key discovery

“…Surprisingly, little previous work deals with the case of discovering multi-column foreign keys [14]. Even for single-column keys, existing work is limited and focuses mainly on identifying inclusion dependencies, since the only formal requirement for specifying a foreign key constraint is that the foreign key be a subset of the primary key [1,14]. However, checking only for inclusion can lead to a large number of false positives.…”

Profiling relational data: a survey

“…A frequent real-world use-case of multi-column profiling is the discovery of foreign keys [96,123] with the help of inclusion dependencies [14,100]. An inclusion dependency states that all values or value combinations from one set of columns also appear in the other set of columns-a prerequisite for a foreign key.…”

“…The SPIDER algorithm [14] is another example, which preprocesses the data by sorting the values of each column and writing them to disk. Next, each sorted stream, corresponding to the values of one particular attribute, is consumed in parallel in a cursor-like manner, and an Ind A ⊆ B can be discarded as soon as we detect a value in A that is not present in B.…”

“…If the distinct value sets of columns A and B are not available, we can estimate the Jaccard similarity using their MinHash signatures [38]. Conditional functional dependencies [24], [59], CTANE [47], CFUN [42], FACD [91], FastCFD [47] Inclusion dependencies [101], [87], SPIDER [14], ZigZag [102] Conditional inclusion dependencies [61], CINDERELLA [13], PLI [13] Foreign keys [123], [143] Denial constraints FastDC [29] Differential dependencies [128] Sequential dependencies [57] 5 Dependency detection…”

Policy Based Detection during Emergency and Sharing Secure Information

“…SPIDER helps to handle this task very efficiently by testing all the attributes parallel. It can analyze 2 GB database in 20 minutes and 21 GB in 4 hours [3] Maayan Gafny et al, proposed a detection method to identify suspicious users in the database through an application. The malicious activity is detected when the result-sets are sent to the user with respect to the request that the user sent.…”