Foreign Keys Research Articles

Efficiently repairing and measuring replica consistency in distributed databases

In a distributed database, maintaining large table replicas with frequent asynchronous insertions is a challenging problem that requires carefully managing a tradeoff between consistency and availability. With that motivation in mind, we propose efficient algorithms to repair and measure replica consistency. Specifically, we adapt, extend and optimize distributed set reconciliation algorithms to efficiently compute the symmetric difference between replicated tables in a distributed relational database. Our novel algorithms enable fast synchronization of replicas being updated with small sets of new records, measuring obsolence of replicas having many insertions and deciding when to update a replica, as each table replica is being continuously updated in an asynchronous manner. We first present an algorithm to repair and measure distributed consistency on a large table continuously updated with new records at several sites when the number of insertions is small. We then present a complementary algorithm that enables fast synchronization of a summarization table based on foreign keys when the number of insertions is large, but happening on a few foreign key values. From a distributed systems perspective, in the first algorithm the large table with data is reconciled, whereas in the second case, its summarization table is reconciled. Both distributed database algorithms have linear communication complexity and cubic time complexity in the size of the symmetric difference between the respective table replicas they work on. That is, they are effective when the network speed is smaller than CPU speed at each site. A performance experimental evaluation with synthetic and real databases shows our algorithms are faster than a previous state-of-the art algorithm as well as more efficient than transferring complete tables, assuming large replicated tables and sporadic asynchronous insertions.

Integrated Management and Monitoring System of Police Station 1 Divisoria, Cagayan de Oro City

The study focused on the improvement of a manual to computerized system for Police Station No.1 in Divisoria, Cagayan de Oro City. It consists of functionally related GUI (application program) and a database. The system consists of a Land-based system for information management and monitoring. Qualitative studies, involving interviews and observations, were used to collect data. The system is designed to help its users organize records and maintain their confidentiality. Also, the system is much easier to apply since the design is relatively simple. Keywords Management, Monitoring, Primary Key, Foreign Key, Technology, Develop, Design.

Mining frequent conjunctive queries using functional and inclusion dependencies

We address the issue of mining frequent conjunctive queries in a relational database, a problem known to be intractable even for conjunctive queries over a single table. In this article, we show that mining frequent projection-selection-join queries becomes tractable if joins are performed along keys and foreign keys, in a database satisfying functional and inclusion dependencies, under certain restrictions. We note that these restrictions cover most practical cases, including databases operating over star schemas, snow-flake schemas and constellation schemas. In our approach, we define an equivalence relation over queries using a pre-ordering with respect to which the support is shown to be anti-monotonic. We propose a level-wise algorithm for computing all frequent queries by exploiting the fact that equivalent queries have the same support. We report on experiments showing that, in our context, mining frequent projection-selection-join queries is indeed tractable, even for large data sets.

AN EXTENDED DATABASE REVERSE ENGINEERING V A KEY FOR DATABASE FORENSIC INVESTIGATION

The database forensic investigation plays an important role in the field of computer. The data stored in the database is generally stored in the form of tables. However, it is difficult to extract meaningful data without blueprints of database because the table inside the database has exceedingly complicated relation and the role of the table and field in the table are ambiguous. Proving a computer crime require very complicated processes which are based on digital evidence collection, forensic analysis and investigation process. Current database reverse engineering researches presume that the information regarding semantics of attributes, primary keys, and foreign keys in database tables is complete. However, this may not be the case. Because in a recent database reverse engineering effort to derive a data model from a table-based database system, we find the data content of many attributes are not related to their names at all. Hence database reverse engineering researches is used to extracts the information regarding semantics of attributes, primary keys, and foreign keys, different consistency constraints in database tables. In this paper, different database reverse engineering (DBRE) process such as table relationship analysis and entity relationship analysis are described .We can extracts an extended entity-relationship diagram from a table-based database with little descriptions for the fields in its tables and no description for keys. Also the analysis of the table relationship using database system catalogue, joins of tables, and design of the process extraction for examination of data is described. Data extraction methods will be used for the digital forensics, which more easily acquires digital evidences from databases using table relationship, entity relationship, different joins among the tables etc. By acquiring these techniques it will be possible for the database user to detect database tampering and dishonest manipulation of database.

NORMALIZATION IN TYPE-2 FUZZY RELATIONAL DATA MODEL BASED ON FUZZY FUNCTIONAL DEPENDENCY USING FUZZY FUNCTIONS

In fuzzy relational databases, the data dependencies, especially the fuzzy functional dependency(ffd) plays an important role in maintaining the consistency of the database and in avoiding the redundant storage of the data. In the past, it has been shown that the type-2 fuzzy relational databases captures impreciseness and incompleteness in data in a better way. The aim of this paper is to provide the concepts for database normalization in a type-2 fuzzy relational database, so that the normalized schemas can be obtained. Here, we deal with the fuzzy functional dependency(ffd) based normalization of type-2 fuzzy relational databases. We use the concepts of fuzzy functions to derive the fuzzy equality and using this fuzzy equality, we define a new definition of fuzzy functional dependency. First we discuss various approaches proposed by the researchers in this context and show why our fuzzy functional dependency is better, as compared to the earlier ffds proposed by the researchers. We call our ffd as non-0 LHS ffd. We identify an anomaly called "spurious ffd" and show that some of the significant contributions proposed by the earlier researchers are suffering from this anomaly, but the non-0 LHS ffd does not suffer from it. Then, we prove that the set of inference rules for the non-0 LHS ffd are sound and complete. We use the definition of non-0 LHS ffd in obtaining the first three normal forms upto BCNF for type-1 and type-2 fuzzy relational schemas. The result of the decomposition and the procedure to obtain the membership value of the decomposed relations is proposed. The associated concepts like the fuzzy key, fuzzy superkey, fuzzy foreign key are defined in terms of non-0 LHS ffd. On the basis of these concepts, we define full ffd, partial ffd etc. In the last, we show that in our case, the relationship of total-ordering between the three normal forms in classical relational databases is also observed.

SMARTINT: using mined attribute dependencies to integrate fragmented web databases

Many web databases can be seen as providing partial and overlapping information about entities in the world. To answer queries effectively, we need to integrate the information about the individual entities that are fragmented over multiple sources. At first blush this is just the inverse of traditional database normalization problem--rather than go from a universal relation to normalized tables, we want to reconstruct the universal relation given the tables (sources). The standard way of reconstructing the entities will involve joining the tables. Unfortunately, because of the autonomous and decentralized way in which the sources are populated, they often do not have Primary Key---Foreign Key relations. While tables may share attributes, naive joins over these shared attributes can result in reconstruction of many spurious entities thus seriously compromising precision. Our system, SmartInt is aimed at addressing the problem of data integration in such scenarios. Given a query, our system uses the Approximate Functional Dependencies (AFDs) to piece together a tree of relevant tables to answer it. The result tuples produced by our system are able to strike a favorable balance between precision and recall.

The ProteoRed MIAPE web toolkit: A User-friendly Framework to Connect and Share Proteomics Standards

The development of the HUPO-PSI's (Proteomics Standards Initiative) standard data formats and MIAPE (Minimum Information About a Proteomics Experiment) guidelines should improve proteomics data sharing within the scientific community. Proteomics journals have encouraged the use of these standards and guidelines to improve the quality of experimental reporting and ease the evaluation and publication of manuscripts. However, there is an evident lack of bioinformatics tools specifically designed to create and edit standard file formats and reports, or embed them within proteomics workflows. In this article, we describe a new web-based software suite (The ProteoRed MIAPE web toolkit) that performs several complementary roles related to proteomic data standards. First, it can verify that the reports fulfill the minimum information requirements of the corresponding MIAPE modules, highlighting inconsistencies or missing information. Second, the toolkit can convert several XML-based data standards directly into human readable MIAPE reports stored within the ProteoRed MIAPE repository. Finally, it can also perform the reverse operation, allowing users to export from MIAPE reports into XML files for computational processing, data sharing, or public database submission. The toolkit is thus the first application capable of automatically linking the PSI's MIAPE modules with the corresponding XML data exchange standards, enabling bidirectional conversions. This toolkit is freely available at http://www.proteored.org/MIAPE/.

의미적 제약조건을 고려한 XML 스키마의 변환

XML 데이터를 효율적으로 저장하고 질의하기 위하여 많은 기법들이 제안되었다. 이러한 목표를 위한 한 가지 방법은 XML 데이터를 관계형 형식으로 변환하여 관계형 데이터베이스를 사용하는 것이다. XML 문서의 내용, 구조, 의미 정보인 제약조건 보존은 스키마를 변환하는 과정에서 매우 중요하다. 특히 키 제약조건은 데이터베이스 이론의 중요한 부분을 차지한다. 따라서 제안된 기법은 주키와 외래키를 표현함으로써 XML의 의미를 반영하며, 변환하는 데에 XML 데이터의 키 제약조건뿐만 아니라 데이터의 내용과 구조와 의미도 보존한다. 변환 정보는 문서의 내용, 문서의 구조(부모-자식 관계), 함수적 종속성, XML key와 keyref 제약조건에 의해 포착한 문서의 의미이다. 제안된 기법은 XML 스키마를 변환할 때에 의미적 제약조건들의 보존을 보장함으로써 관계형 데이터베이스에서 데이터 무결성을 보장하기 위한 저장 프로시저나 트리거를 사용할 필요가 없는 이점이 있다. 이러한 변환은 산업체에서 필요한 데이터 관리의 한 부분으로, 이미 웹에 저장되어있는 데이터를 데이터베이스에 저장하여 다른 업무에 활용할 수가 있을 것이다. 본 논문에서는 DTD에서 지원하는 ID/IDREF 키, 상속 관계, 묵시적 참조 무결성은 반영하지 못하였다. Many techniques have been proposed to store and query XML data efficiently. One way achieving this goal is using relational database by transforming XML data into relational format. It is important to transform schema to preserve the content, the structure and the constraints of the semantics information of the XML document. Especially, key constraints are an important part of database theory. Therefore, the proposal technique has considered the semantics of XML as expressed by primary keys and foreign keys. And, the proposal technique can preserve not only XML data constraints but also the content and the structure and the semantics of XML data thru transformation process. Transforming information is the content and the structure of the document(the parent-child relationship), the functional dependencies, semantics of the document as captured by XML key and keyref constraints. Because of XML schema transformation ensures that preserving semantic constraints, the advantages of these transformation techniques do not need to use the stored procedure or trigger which these data ensures data integrity in the relational database. In this paper, there is not chosen the ID/IDREF key which supported in DTD, the inheritance relationship, the implicit referential integrity.

Evaluating Join Performance on Relational Database Systems

The join operator is fundamental in relational database systems. Evaluating join queries on large tables is challenging because records need to be efficiently matched based on a given key. In this work, we analyze join queries in SQL with large tables in which a foreign key may be null, invalid or valid, given a referential integrity constraint. We conduct an extensive join performance evaluation on three DBMSs. Specifically, we study join queries varying table sizes, row size and key probabilistic distribution, inserting null, invalid or valid foreign key values. We also benchmark three well-known query optimizations: view materialization, secondary index and join reordering. Our experiments show certain optimizations perform well across DBMSs, whereas other optimizations depend on the DBMS architecture.

Inference Document Type (Dtd) From Xml Document: Web Structure Mining

XML is becoming a prevalent format and defacto standard for data exchange in many applications. While traditionally, lots of data are stored and managed in relational databases. There is an urgent need to research some efficient methods to convert these data stored in relational databases to XML format when integrating and exchanging these data in XML format. The semantics of XML schemas are crucial to design, query, and store XML documents and functional dependencies are very important representations of semantic information of XML schemas. As DTDs are one of the most frequently used schemas for XML documents in these days, we will use DTDs as schemas of XML documents here. This paper studies the problem of schema conversion from relational schemas to XML DTDs. As functional dependencies play an important role in the schema conversion process, the concept of functional dependency for XML DTDs is used to preserve the semantics implied by functional dependencies and keys of relational schemas. A conversion method is proposed to convert relational schemas to XML DTDs in the presence of functional dependencies, keys and foreign keys. The methods presented here can preserve the semantics implied by functional dependencies, keys and foreign keys of relational schemas and can convert multiple relational tables to XML DTDs at the same time.

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.

Zagrada—The New Zagreb Radiocarbon Database

After introducing the liquid scintillation counting (LSC) method and graphite target preparation for accelerator mass spectrometry (AMS) measurements in the Zagreb Radiocarbon Laboratory, the existing database software designed only for gas proportional counting (GPC) (using a DOS operating system) could not satisfy the requirements for parallel conduction of several techniques. This has been enabled recently by the development of the new relational database ZAGRADA, which—using SQL scripts and constraints defined by primary and foreign keys—enforces high data integrity and provides better performances in data filtering and sorting. The structural scheme of this database conceptually comprises 4 basic modules with data on the samples, chemical pretreatment and preparation, measurements and data on the final results. A user-friendly graphical user interface has been designed to perform various actions and data manipulation to the database. The implementation of a new database for 14C samples has significant contribution to scientific research performed in the Radiocarbon and Tritium Laboratory and to quality assurance and quality control, and will enable better and easier communication with customers.

Extended aggregations for databases with referential integrity issues

Querying inconsistent databases remains a broad and difficult problem. In this work, we study how to improve aggregations computed on databases with referential errors in the context of database integration, where each source database has different tables, columns with similar content across multiple databases, but different referential integrity constraints. Thus, a query in an integrated database may involve tables and columns with referential integrity errors. In a data warehouse, even though the ETL processes fix referential integrity errors, this is generally done by inserting “dummy” records into the dimension tables corresponding to such invalid foreign keys, thereby artificially enforcing referential integrity. When two tables are joined and aggregations are computed, rows with an invalid or null foreign key value are skipped, effectively eliminating potentially valuable information. With that motivation in mind, we extend SQL aggregate functions computed over tables with referential integrity issues to return complete answer sets in the sense that no row is excluded. We associate to each referenced key in the dimension table, a probability that invalid or null foreign keys refer to it. Our main idea is to compute aggregations over joined tables including rows with invalid or null references by distributing their contribution to aggregation totals, based on probabilities computed over correct foreign keys. Experiments with real and synthetic databases evaluate the usefulness, accuracy and performance of our extended aggregations.

METHODOLOGY FOR DATA CONVERSION FROM XML DOCUMENTS TO RELATIONS USING EXTENSIBLE STYLESHEET LANGUAGE TRANSFORMATION

Extensible Markup Language (XML) has been used for data-transport and data-transformation while the business sector continues to store critical business data in relational databases. Extracting relational data and formatting it into XML documents, and then converting XML documents back to relational structures, becomes a major daily activity. It is important to have an efficient methodology to handle this conversion between XML documents and relational data. This paper aims to perform data conversion from XML documents into relational databases. It proposes a prototype and algorithms for this conversion process. The pre-process is schema translation using an XML schema definition. The proposed approach is based on the needs of an Order Information System to suggest a methodology to gain the benefits provided by XML technology and relational database management systems. The methodology is a stepwise procedure using XML schema definition and Extensible Stylesheet Language Transformations (XSLT) to ensure that the data constraints are not scarified after data conversion. The implementation of the data conversion is performed by decomposing the XML document of a hierarchical tree model into normalized relations interrelated with their artifact primary keys and foreign keys. The transformation process is performed by XSLT. This paper will also demonstrate the entire conversion process through a detailed case study.

AVOIDANCE OF CYCLICAL REFERENCE OF FOREIGN KEYS IN DATA MODELING USING THE ENTITY-RELATIONSHIP MODEL

The entity-relationship (ER) model is clearly established as a conceptual model of choice when building relational database systems. Once the ER model is built, it can be mapped algorithmically to the relational model and consequently to the data definition language (DDL) of SQL. Algorithmic mapping of the ER model to the relational model can produce foreign keys referencing each other’s tables cyclically. This cyclical reference of foreign keys generates irresolvable deadlocks when enforcing the referential integrity rule using the cascading option. However, it is not obvious for database designers while building an ER model. In this article, we provide a set of rules of ER modeling for avoiding cyclical references of foreign keys.

Learning to create data-integrating queries

The number of potentially-related data resources available for querying --- databases, data warehouses, virtual integrated schemas --- continues to grow rapidly. Perhaps no area has seen this problem as acutely as the life sciences, where hundreds of large, complex, interlinked data resources are available on fields like proteomics, genomics, disease studies, and pharmacology. The schemas of individual databases are often large on their own, but users also need to pose queries across multiple sources, exploiting foreign keys and schema mappings. Since the users are not experts, they typically rely on the existence of pre-defined Web forms and associated query templates, developed by programmers to meet the particular scientists' needs. Unfortunately, such forms are scarce commodities, often limited to a single database, and mismatched with biologists' information needs that are often context-sensitive and span multiple databases. We present a system with which a non-expert user can author new query templates and Web forms, to be reused by anyone with related information needs. The user poses keyword queries that are matched against source relations and their attributes; the system uses sequences of associations (e.g., foreign keys, links, schema mappings, synonyms, and taxonomies) to create multiple ranked queries linking the matches to keywords; the set of queries is attached to a Web query form. Now the user and his or her associates may pose specific queries by filling in parameters in the form. Importantly, the answers to this query are ranked and annotated with data provenance, and the user provides feedback on the utility of the answers, from which the system ultimately learns to assign costs to sources and associations according to the user's specific information need, as a result changing the ranking of the queries used to generate results. We evaluate the effectiveness of our method against "gold standard" costs from domain experts and demonstrate the method's scalability.

A relational approach to probabilistic classification in a transductive setting

Transduction is an inference mechanism adopted from several classification algorithms capable of exploiting both labeled and unlabeled data and making the prediction for the given set of unlabeled data only. Several transductive learning methods have been proposed in the literature to learn transductive classifiers from examples represented as rows of a classical double-entry table (or relational table). In this work we consider the case of examples represented as a set of multiple tables of a relational database and we propose a new relational classification algorithm, named TRANSC, that works in a transductive setting and employs a probabilistic approach to classification. Knowledge on the data model, i.e., foreign keys, is used to guide the search process. The transductive learning strategy iterates on a k-NN based re-classification of labeled and unlabeled examples, in order to identify borderline examples, and uses the relational probabilistic classifier Mr-SBC to bootstrap the transductive algorithm. Experimental results confirm that TRANSC outperforms its inductive counterpart (Mr-SBC).

Unary and n-ary inclusion dependency discovery in relational databases

Foreign keys form one of the most fundamental constraints for relational databases. Since they are not always defined in existing databases, the discovery of foreign keys turns out to be an important and challenging task. The underlying problem is known to be the inclusion dependency (IND) inference problem. In this paper, data-mining algorithms are devised for IND inference in a given database. We propose a two-step approach. In the first step, unary INDs are discovered thanks to a new preprocessing stage which leads to a new algorithm and to an efficient implementation. In the second step, n-ary IND inference is achieved. This step fits in the framework of levelwise algorithms used in many data-mining algorithms. Since real-world databases can suffer from some data inconsistencies, approximate INDs, i.e. INDs which almost hold, are considered. We show how they can be safely integrated into our unary and n-ary discovery algorithms. An implementation of these algorithms has been achieved and tested against both synthetic and real-life databases. Up to our knowledge, no other algorithm does exist to solve this data-mining problem.

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.