Referential Integrity Constraints Research Articles

Minimal coverage of generalized typed inclusion dependencies in databases

The paper discusses the theory and algorithms necessary to construct a minimal covering of generalized typed inclusion dependencies. Traditionally, the construction of minimal covering is used for all types of dependencies in order to obtain a non-redundant and consistent database design. Generalized inclusion dependencies correspond to referential integrity constraints, when several main and several external relations are involved in one constraint, which corresponds to an ultragraph edge. In previous work, based on a study of the properties of dependencies, a system of axioms was presented with proof of consistency and completeness. In this work, the closures were studied for generalized typed inclusion dependencies. An algorithm for constructing closures has been developed and its correctness has been proven. The results obtained are further used to develop an algorithm for constructing a minimal covering. At the end of the article, examples are presented that demonstrate the operation of the algorithms.

Query rewriting for semantic query optimization in spatial databases

Query processing is an important challenge for spatial databases due to the use of complex data types that represent spatial attributes. In particular, due to the cost of spatial joins, several optimization algorithms based on indexing structures exist. The work in this paper proposes a strategy for semantic query optimization of spatial join queries. The strategy detects queries with empty results and rewrites queries to eliminate unnecessary spatial joins or to replace spatial by thematic joins. This is done automatically by analyzing the semantics imposed by the database schema through topological dependencies and topological referential integrity constraints. In this way, the strategy comes to complement current state-of-art algorithms for processing spatial join queries. The experimental evaluation with real data sets shows that the optimization strategy can achieve a decrease in the time cost of a join query using indexing structures in a spatial database management system (SDBMS).

Consolidation of database check constraints

Independent modeling of various modules of an information system (IS), and consequently database subschemas, may result in formal or semantic conflicts between the modules being modeled. Such conflicts may cause collisions between the integrated database schema of a whole IS and the modeled subschemas. In our previous work, we have proposed criteria and algorithms for identifying and resolving such conflicts so as to provide a consolidation of database subschemas with the integrated database schema with respect to various database concepts, such as domains, relation schemes, primary key constraints and referential integrity constraints. In this paper, we propose a new approach and algorithms for identifying conflicts and testing consolidation of subschemas with the integrated database schema against check constraints. The proposed approach is based on satisfiability modulo theory (SMT) solvers. Hereby, we propose the integration of SMT solvers into our MDSD tool, aimed at supporting a database schema integration process.

ОСОБЛИВОСТІ РОЗРОБЛЕННЯ БАЗ ДАНИХ ДЛЯ МЕДИЧНИХ УСТАНОВ

Nowadays in medical departments, the main problem is to keep big amount of information and create logical connections between them to increase efficiency of medical personal. You need to store different amount of information in different columns and sometimes create new one, but without developer, it is a problem, because you need to edit structure of database.To prevent this problem developers using software package that helps them to modify database schema. Moreover, the same tool medical personal could use as well.First of all we need to understand what exactly you working with, what type of information will be worked on, also ensure on simple examples (the way it could be easy) what needs to be done. Next step consist of algorithm structure and base structure, it is the main part of development.Packages that you need could be an assembly of several languages for example: C#, SQL, Python. Any type of connection that focused on this, called «NoSQL»Main difference: NoSQL gives you more abilities than simple SQL, by using combination of languages that focused on result and efficiency.Instead of most NoSQL databases it offers a concept of «eventual consistency» in which database changes are propagated to all nodes «eventually» (typically within milliseconds) so queries for data might not return updated data immediately or might result in reading data that is not accurate, a problem known as stale reads. Additionally, some NoSQL systems may exhibit lost writes and other forms of data loss. Fortunately, some NoSQL systems provide concepts such as write-ahead logging to avoid data loss. For distributed transaction processing across multiple databases, data consistency is an even bigger challenge that is difficult for both NoSQL and relational databases. Even current relational databases do not allow referential integrity constraints to span databases. There are few systems that maintain both ACID transactions and XI Open XA standards for distributed transaction processing.

Enhancing Relational Database Security by Metadata Segregation

Although many prominent Relational Database Management Systems provides inbuilt security controls and mechanisms, the information resided in the data-store are at great risk. This research aims to reduce the risk of unauthorized data access by providing an extra layer of security. This research proposes a novel method for incorporating information security while designing the relational database by segregating the information on the basis of its sensitivity level and creating referential integrity constraints dynamically at run time. Different techniques to identify and quantify sensitive attributes and restructuring database architecture have been discussed for the proposed approach. The primary keys of the restructured tables and most critical information attributes were secured using Transparent Data Encryption utility provided by Oracle 11g to prohibit illegitimate use of information. The performance of the proposed architecture was evaluated with 1,000,000 records which shows that by increasing the number of records, the response time of Select statement increased dramatically whereas it increased gradually for Insert, Update and Delete operations.

A design specification and a server implementation of the inverse referential integrity constraints

The inverse referential integrity constraints (IRICs) are specialization of non-key-based inclusion dependencies (INDs). Keybased INDs (referential integrity constraints) may be fully enforced by most current relational database management systems (RDBMSs). On the contrary, non-key-based INDs are completely disregarded by actual RDBMSs, obliging the users to manage them via custom procedures and/or triggers. In this paper we present an approach to the automated implementation of IRICs integrated in the SQL Generator tool that we developed as a part of the IIS

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.

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.

Integrity Constraint Checking in Distributed Nested Transactions over a Database Cluster


 
 
 This paper presents a solution to check referential integrity constraints and conjunctive global constraints in a relational multi database system. It also presents the experimental results obtained by implementing this solution over a PC cluster with Oracle9i DBMS.
 The goal of those experimentations is to measure the time spent to check global constraints in a distributed systems. The results show that the overhead induced by our distributed constraint checking is reduced by 50% compared to a centralized checking of constraints.
 
 

Data warehouse clustering on the web

In collaborative e-commerce environments, interoperation is a prerequisite for data warehouses that are physically scattered along the value chain. Adopting system and information quality as success variables, we argue that what is required for data warehouse refreshment in this context is inherently more complex than the materialized view maintenance problem and we offer an approach that addresses refreshment in a federation of data warehouses. Defining a special kind of materialized views, we propose an open multi-agent architecture for their incremental maintenance while considering referential integrity constraints on source data.

Deductive databases for computing certain and consistent answers from mediated data integration systems

We address the problem of retrieving certain and consistent answers to queries posed to a mediated data integration system under the local-as-view paradigm with open sources and conjunctive and disjunctive view definitions. For obtaining certain answers a query program is run under the cautious stable model semantics on top of a normal deductive database with choice operator that specifies the class of minimal legal instances of the integration system. This methodology works for all monotone Datalog queries. To compute answers to queries that are consistent with respect to given global integrity constraints, the specification of minimal legal instances is combined with another disjunctive deductive database that specifies the repairs of those legal instances. This allows to retrieve the answers to any Datalog ¬ query that are consistent with respect to global universal and referential integrity constraints.

Formal specification synthesis for relational database model

This article proposes an automatic scheme for synthesizing formal specification from the definitions of the relational data model—entity relationship diagram (ERD), and their data dictionaries. The formal specification of both structural and behavioral properties of the relational database model is generated in Z schemas. In our approach, the mandatory structural constraints—the uniqueness of primary key, foreign keys, and referential integrity constraints among the relations in the method—are preserved. We propose a set of transformation rules to exhaustively produce Z schemas of the states and primitive operations—cascade insertion, deletion, and updating. Moreover, in this article we focus on a composition technique of constructing the composite operations using the primitive composition tree. The revision of the formal specification can be conducted easily with the mathematical proofs of the properties of the data model using the Z prover tool. © 2004 Wiley Periodicals, Inc.

Formal specification scheme for database applications using requirements particle networks

This paper proposes a systematic scheme for synthesizing formal specification from the definitions of relational data model – entity relationship diagram and their data dictionaries. The formal specification of both structural and behavioral properties of relational data model is generated in Z schemas. In our approach, the mandatory structural constraints – the uniqueness of primary key, foreign keys, and referential integrity constraints among the relations in the model, are preserved. We propose a set of transformation rules to produce Z schemas of the states and primitive operations – cascade insertion, deletion, and updating. Moreover, a composition technique of constructing the composite operations is presented by using requirements particle networks. The revision of the formal specification can be easily conducted with the mathematical proofs of the properties of the data model using Z prover tool.

Managing periodically updated data in relational databases

Recent trends in information management involve the periodic transcription of data onto secondary devices in a networked environment, and the proper scheduling of these transcriptions is critical for efficient data management. To assist in the scheduling process, we are interested in modeling data obsolescence , that is, the reduction of consistency over time between a relation and its replica. The modeling is based on techniques from the field of stochastic processes, and provides several stochastic models for content evolution in the base relations of a database, taking referential integrity constraints into account. These models are general enough to accommodate most of the common scenarios in databases, including batch insertions and lifespans both with and without memory. As an initial "proof of concept" of the applicability of our approach, we validate the insertion portion of our model framework via experiments with real data feeds. We also discuss a set of transcription protocols that make use of the proposed stochastic model.

Safe referential integrity and null constraint structures in relational databases

Referential integrity and null constraints are extensively used in relational databases for expressing existence dependencies between tuples and restrictions on the way nulls appear in relations, respectively. Referential integrity and null constraints can cause the following data manipulation anomalies: 1. (i) referential integrity and null constraints may block the execution of certain data manipulations, although these manipulations would result in consistent database states, and 2. (ii) depending on the order of enforcing the referential integrity constraints, certain data manipulations may result in different database states, thus being unpredictable. In this paper we examine these anomalies and formally develop safeness conditions for avoiding them.

Expert systems designed for maintenance

Abstract Constraints for knowledge are proposed as a tool for constructing maintainable expert systems. A simple taxonomy of knowledge constraints is given. It is shown that a special class of knowledge constraints, coupled with the referential integrity constraint, together provide the necessary foundation for a mechanism to substantially automate the expert systems maintenance process provided that the knowledge has been normalised. The normalisation of knowledge is described and its inherent value to the maintenance process is illustrated. An experimental expert systems design and maintenance tool based on this approach has been built.

Identifying extended entity-relationship object structures in relational schemas

Relational schemas consisting of relation-schemes, key dependencies and key-based inclusion dependencies (referential integrity constraints) are considered. Schemas of this form are said to be entity-relationship (EER)-convertible if they can be associated with an EER schema. A procedure that determines whether a relational schema is EER-convertible is developed. A normal form is proposed for relational schemas representing EER object structures. For EER-convertible relational schemas, the corresponding normalization procedure is presented. The procedures can be used for analyzing the semantics of existing relational databases and for converting relational database schemas into object-oriented database schemas.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>