Current Relational Database Research Articles

TransJoin: An Algorithm to Implement Division Operator of Relational Algebra in Structured Query Language

Division operator is one of operators in Relational Algebra which is not implemented directly in SQL (Structured Query Language) standard. Therefore it is not be able to retrieve division query results with an SQL statement in the current relational database management systems (RDBMS). Database programmers have to create a complicated query to perform the task. It is the only relational algebra operators which is not implemented in ANSI SQL standard. This research aimed to study and design an algorithm named TransJoin (transformation and join) to implement it in SQL. TransJoin works to relation P(x, y) divided by Q(y) resulted R(x) through grouping and transforming each y attribute becomes P’(x, y’) and Q’(y’) and each x value is a single tuple with y’ is a composite value of y. Then, result R(x) is resulted of joining P’(x, y’) and Q’(y’) relations by matching y’ attribute. TransJoin was implemented in an open source RDBMS SQLLite. TransJoin tested and delivered valid results by comparing of traditional SQL queries and our proposed SQL queries with various data. The research showed that our algorithm is much more efficient by consuming 0.078 milliseconds compared contrasted the traditional query in 1,974.08 milliseconds for 9,991 tuples.

Enhanced Data Lake Clustering Design based on K-means Algorithm

In recent years, Big Data requirements have evolved. Organizations are trying more than ever to accent their efforts on industrial development of all data at their disposal and move further away from underpinning technologies. After investing around Data Lake concept, organizations must now overhaul their data architecture to face IoT (Internet of Things) and AI (Artificial Intelligence) expansion. Efficient and effective data mapping treatments could serve in understanding the importance of data being transformed and used for decision-making process endorsement. As current relational databases are not able to manage large amounts of data, organizations headed towards NoSQL (Not only Structured Query Language) databases. One such known NoSQL database is MongoDB, which has a high scalability. This article mainly put forward a new data model able to extract, classify, and then map data for the purpose of generating new more structured data that meet organizational needs. This can be carried out by calculating various metadata attributes weights, which are considered as important information. It also processed on data clustering stored into MongoDB. This categorization based on data mining clustering algorithm named K-Means.

Supporting Uncertain Predicates in DBMS Using Approximate String Matching and Probabilistic Databases

Current relational database systems are deterministic in nature and lack the support for approximate matching. The result of approximate matching would be the tuples annotated with the percentage of similarity but the existing relational database system can not process these similarity scores further. In this paper, we propose a system to support approximate matching in the DBMS field. We introduce a `≈' (uncertain predicate operator) for approximate matching and devise a novel formula to calculate the similarity scores. Instead of returning an empty answer set in case of no match, our system gives ranked results thereby providing a glance at existing tuples closely matching with the queried literals. Two variants of the `≈' operator are also introduced for numeric data: `≈+' for higher-the-better and `≈-' for lower-the-better cases. Efficient approximate string matching methods are proposed for matching string-type data whereas numeric closeness is used for other types of data (date, time, and number). We also provide results of our system taken over several sample queries that illustrate the significance of our system. All experiments are performed using the MySQL database, whereas the IMDb movie database and European Football database are used as sample datasets.

A scalable robust and automatic propositionalization approach for Bayesian classification of large mixed numerical and categorical data

Companies want to extract value from their relational databases. This is the aim of relational data mining. Propositionalization is one possible approach to relational data mining. Propositionalization adds new attributes, called features, to the main table, leading to an attribute-value representation, a single table, on which a propositional learner can be applied. However, current relational databases are large and composed of mixed, numerical and categorical, data. Moreover, the specificity of relational data is to involve one-to-many relationships. As an example of such data, consider customers purchasing products: each customer can purchase several products. Therefore, there is a need for techniques able to learn complex aggregates. Learning such features means to explore a combinatorial, possibly infinite, space and such an approach is prone to overfitting. We introduce a propositionalization approach dedicated to a robust Bayesian classifier. It efficiently samples a given number of features in the language bias, following a distribution over the complex aggregates. This distribution is also used to penalize complex aggregates in the regularization of the robust Bayesian classifier. Experiments show that it performs better than state-of-the-art methods on most investigated benchmarks and can deal with large datasets more easily. A new real, large, mixed relational dataset is introduced which confirms the ability of our approach to learn complex aggregates.

Analysis and Evaluation of a Framework for Sampling Database in Recommenders

In this paper the authors proposed a database sampling framework that aims to minimize the time necessary to produce a sample database. They argue that the performance of current relational database sampling techniques that maintain the data integrity of the sample database is low and a faster strategy needs to be devised. The sampling method targets the production environment of a system under development that generally consists of large amounts of data computationally costly to analyze. The results have been improved due to the fact that the authors have selected the users that they had more information about them and they have made the data table denser. Therefore, by increasing the data and making the rating more comprehensive for all the users they can help to produce the more and better association rules. The obtained results were not that much suitable for Jester dataset but with their proposed methods the authors have tried to improve the quantity and quality of the rules. These results indicate that the effectiveness of the system greatly depends on the input data and the applied dataset. In addition, if the user rates more number of the items the system efficiency will be more increased.

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

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.

NoSQL real-time database performance comparison

The amount of data being produced is increasing constantly, as the number and variety of connected devices are growing and the advances in data storage and mining are supporting this evolution. However, storing and handling high quantities of data is challenging the current Relational Database Management Systems. Big Data and its related products came to help in this matter, and the NoSQL databases arise with the purpose to offer better solutions and features to handle massive amounts of data with higher performance, sometimes near real-time. The present study presents the NoSQL databases scenario and background, and elaborates a detailed study with the characteristics, a features comparison and a performance evaluation of three different NoSQL databases extensively used in the market nowadays: Couchbase, MongoDB and RethinkDB. Tests were performed in two different scenarios: single thread and multiple threads. The results reveal that Couchbase had a better performance at most of the operations, except for retrieving multiple documents and inserting documents with multiple threads, operations in which MongoDB scored better.

Nearly Periodic Facts in Temporal Relational Databases

Despite the huge amount of work devoted to the treatment of time within the relational context, few relevant temporal phenomena still remain to be addressed. One of them is the treatment of “ nearly periodic events ”, i.e., events/facts that occur in intervals of time which repeat periodically (e.g., a meeting occurring twice each Monday , possibly not at regular times ). Nearly periodic events are quite frequent in everyday life, and thus in many applicative contexts. Their treatment within the relational model is quite challenging, since it involves the integrated treatment of three aspects: (i) the number of repetitions, (ii) their periodicity , and (iii) temporal indeterminacy . Coping with this problem requires an in-depth extension of current temporal relational database techniques. In this paper, we introduce a new data model, and new definitions of relational algebraic operators coping with the above issues. We ascertain the properties of the new model and algebra, with emphasis on the expressiveness of our representation model, on the reducibility property, and on the correctness of the algebraic operators.

Efficient Set-Correlation Operator Inside Databases

Large scale of short text records are now prevalent, such as news highlights, scientific paper citations, and posted messages in a discussion forum, and are often stored as set records in hidden-Web databases. Many interesting information retrieval tasks are correspondingly raised on the correlation query over these short text records, such as finding hot topics over news highlights and searching related scientific papers on a certain topic. However, current relational database management systems (RDBMS) do not directly provide support on set correlation query. Thus, in this paper, we address both the effectiveness and the efficiency issues of set correlation query over set records in databases. First, we present a framework of set correlation query inside databases. To the best of our knowledge, only the Pearson’s correlation can be implemented to construct token correlations by using RDBMS facilities. Thereby, we propose a novel correlation coefficient to extend Pearson’s correlation, and provide a pure-SQL implementation inside databases. We further propose optimal strategies to set up correlation filtering threshold, which can greatly reduce the query time. Our theoretical analysis proves that with a proper setting of filtering threshold, we can improve the query efficiency with a little effectiveness loss. Finally, we conduct extensive experiments to show the effectiveness and the efficiency of proposed correlation query and optimization strategies.

Improving the Deductive System DES with Persistence by Using SQL DBMS's

This work presents how persistent predicates have been included in the in-memory deductive system DES by relying on external SQL database management systems. We introduce how persistence is supported from a user-point of view and the possible applications the system opens up, as the deductive expressive power is projected to relational databases. Also, we describe how it is possible to intermix computations of the deductive engine and the external database, explaining its implementation and some optimizations. Finally, a performance analysis is undertaken, comparing the system with current relational database systems.

Ophidia: Toward Big Data Analytics for eScience

This work introduces Ophidia, a big data analytics research effort aiming at supporting the access, analysis and mining of scientific (n-dimensional array based) data. The Ophidia platform extends, in terms of both primitives and data types, current relational database system implementations (in particular MySQL) to enable efficient data analysis tasks on scientific array-based data. To enable big data analytics it exploits well-known scientific numerical libraries, a distributed and hierarchical storage model and a parallel software framework based on the Message Passing Interface to run from single tasks to more complex dataflows. The current version of the Ophidia platform is being tested on NetCDF data produced by CMCC climate scientists in the context of the international Coupled Model Intercomparison Project Phase 5 (CMIP5).

Indexing method for multidimensional vector data

Efficient management of multidimensional data is a challenge when building modern database applications that involve many fold data such as temporal, spatial, data warehousing, bio-informatics, etc. This problem stems from the fact that multidimensional data has no order that preserves proximity. The majority of existing solutions to this problem cannot be easily integrated into the current relational database systems since they require modifications to the kernel. A prominent class of methods that can use existing access structures are ‘space filling curves’. In this work we describe a method that is also based on the space filling curve approach, but in contrast to earlier methods, it connects regions of various sizes rather than points in multidimensional space. Our approach allows efficient transformation of interval queries into regions of data which results in significant improvements when accessing the data. In detailed empirical study, we have demonstrated that the proposed method, which can be integrated within the commercial RDBMS, outperforms the best available off-the-shelf methods for accessing multidimensional point data.

Purpose-based access control exploits by HDB

As organizations increase their reliance on, as a result preserving privacy has become a crucial requirement for operating a business that manages personal data. Private Schools, district schools, and state education agencies maintain a large volume of personnel files. Due to voluminous stores of personal data being held by Education Industry today, preserving privacy has become a crucial requirement for operating a business. Hippocratic databases have been proposed to answer this requirement through a database design that includes responsibility for the privacy of data as a founding tenet. We identify, study, and implement privacypreserving feature for education industry in Hippocratic databases. This paper includes the support of how current relational database management systems can be transformed into their privacy preserving equivalents.

Developing a labelled object-relational constraint database architecture for the projection operator

Current relational databases have been developed in order to improve the handling of stored data, however, there are some types of information that have to be analysed for which no suitable tools are available. These new types of data can be represented and treated as constraints, allowing a set of data to be represented through equations, inequations and Boolean combinations of both. To this end, constraint databases were defined and some prototypes were developed. Since there are aspects that can be improved, we propose a new architecture called labelled object-relational constraint database (LORCDB). This provides more expressiveness, since the database is adapted in order to support more types of data, instead of the data having to be adapted to the database. In this paper, the projection operator of SQL is extended so that it works with linear and polynomial constraints and variables of constraints. In order to optimize query evaluation efficiency, some strategies and algorithms have been used to obtain an efficient query plan. Most work on constraint databases uses spatiotemporal data as case studies. However, this paper proposes model-based diagnosis since it is a highly potential research area, and model-based diagnosis permits more complicated queries than spatiotemporal examples. Our architecture permits the queries over constraints to be defined over different sets of variables by using symbolic substitution and elimination of variables.

PIST: An Efficient and Practical Indexing Technique for Historical Spatio-Temporal Point Data

Despite pressing need, current relational database management systems (RDBMS) support for spatio-temporal data is limited and inadequate, and most existing spatio-temporal indices cannot be readily integrated into existing RDBMSs. This paper proposes a practical index for spatio-temporal (PIST) data, an indexing technique, rather than a new indexing structure, for historical spatio-temporal data points that can be fully integrated within existing RDBMSs. PIST separates the spatial and temporal components of the data. For the spatial component, we develop a formal cost model and a partitioning strategy that leads to an optimal space partitioning for uniformly distributed data and an efficient heuristic partitioning for arbitrary data distributions. For the temporal component of the data a B?+?-tree is used. We show that this layer's performance can be maximized if an optimal maximal temporal range is enforced, and we present a procedure to determine such an optimal value. Being fully mapped onto a RDBMS, desirable and important properties, such as concurrency control, are immediately inherited by PIST. Using ORACLE as our implementation platform we perform extensive experiments with both real and synthetic datasets comparing its performance against other RDBMS-based options, as well as the MV3R-tree. PIST outperforms the former by at least one order of magnitude, and is competitive or better with respect to the latter, with the unarguable advantage that it can readily used on top of virtually any existing RDBMS.

TRANSLATING RELATIONAL SCHEMA WITH CONSTRAINTS INTO XML SCHEMA

With XML adopted as the technology trend on the Internet, and with investment in the current relational database systems, companies must convert their relational data into XML documents for data transmission on the Internet. In the process, to preserve the users' relational data requirements of data constraints into the converted XML documents, we must define a meaningful root element for each XML document. The construction of an XML document is based on the root element and its relevant elements. The root element can be selected from a relational entity table in the existing relational database, which depends on the requirements to present the business behind. The relevant elements are mapped from the related entities, based on the navigability of the chosen entity. The derived root and relevant elements can form a Data Type Definition Graph (DTD-graph) of an XML conceptual schema diagram which can be mapped into a Data Type Definition (DTD) of an XML schema. The result is a translated XML schema with semantic constraints transferred from a relational conceptual schema of an Extended Entity Relationship (EER) model. The data conversion from relational data to the XML documents can be done after the schema translation. The relational data are loaded into XML documents according to the translated DTD.

Parallel ROLAP Data Cube Construction on Shared-Nothing Multiprocessors

The pre-computation of data cubes is critical to improving the response time of on-line analytical processing (OLAP) systems and can be instrumental in accelerating data mining tasks in large data warehouses. In order to meet the need for improved performance created by growing data sizes, parallel solutions for generating the data cube are becoming increasingly important. The paper presents a parallel method for generating data cubes on a shared-nothing multiprocessor. Since no (expensive) shared disk is required, our method can be used on low cost Beowulf style clusters consisting of standard PCs with local disks connected via a data switch. Our approach uses a ROLAP representation of the data cube where views are stored as relational tables. This allows for tight integration with current relational database technology. We have implemented our parallel shared-nothing data cube generation method and evaluated it on a PC cluster, exploring relative speedup, local vs. global schedule trees, data skew, cardinality of dimensions, data dimensionality, and balance tradeoffs. For an input data set of 2000000 rows (72 Megabytes), our parallel data cube generation method achieves close to optimal speedup; generating a full data cube of /spl ap/227 million rows (5.6 Gigabytes) on a 16 processors cluster in under 6 minutes. For an input data set of 10,000,000 rows (360 Megabytes), our parallel method, running on a 16 processor PC cluster, created a data cube consisting of /spl ap/846 million rows (21.7 Gigabytes) in under 47 minutes.

Converting relational database into XML documents with DOM

The revolution of XML is recognized as the trend of technology on the Internet to researchers as well as practitioners. Companies need to adopt XML technology. With investment in the current relational database systems, they want to develop new XML documents while running existing relational databases on production. They need to reengineer the relational databases into XML documents with constraints preservation. In the process, schema translation must be done before data conversion. Since the existing relational databases are usually normalized, they have to be reconstructed into XML document tree structures. This can be accomplished through denormalization by joining the normalized relations into tables according to their data dependencies constraints. The joined tables are mapped into DOMs, which are then integrated into XML document trees. The user specifies an XML document root with its relevant nodes to form a partitioned XML document tree to meet their requirements. The selected XML document tree is mapped into an XML schema in the form of DTD. We then load joined tables into DOMs, integrate them into a DOM, and transform it into an XML document.

SEAM: A state-entity-activity-model for a well-defined workflow development methodology

Current conceptual workflow models use either informally defined conceptual models or several formally defined conceptual models that capture different aspects of the workflow, e.g., the data, process, and organizational aspects of the workflow. To the best of our knowledge, there are no algorithms that can amalgamate these models to yield a single view of reality. A fragmented conceptual view is useful for systems analysis and documentation. However, it fails to realize the potential of conceptual models to provide a convenient interface to automate the design and management of workflows. First, as a step toward accomplishing this objective, we propose SEAM (State-Entity-Activity-Model), a conceptual workflow model defined in terms of set theory. Second, no attempt has been made, to the best of our knowledge, to incorporate time into a conceptual workflow model. SEAM incorporates the temporal aspect of workflows. Third, we apply SEAM to a real-life organizational unit's workflows. In this work, we show a subset of the workflows modeled for this organization using SEAM. We also demonstrate, via a prototype application, how the SEAM schema can be implemented on a relational database management system. We present the lessons we learned about the advantages obtained for the organization and, for developers who choose to use SEAM, we also present potential pitfalls in using the SEAM methodology to build workflow systems on relational platforms. The information contained in this work is sufficient enough to allow application developers to utilize SEAM as a methodology to analyze, design, and construct workflow applications on current relational database management systems. The definition of SEAM as a context-free grammar, definition of its semantics, and its mapping to relational platforms should be sufficient also, to allow the construction of an automated workflow design and construction tool with SEAM as the user interface.

Implementation of a deductive database system using SQLBase

Deductive databases intellectualize relational databases by providing complex inference ability and they are competitive with current commercial relational databases. The deductive system presented in this paper is a unified logic/database system where programs may be expressed declaratively, in a form close to first-order logic, and at the same time efficiently access very large data bases. The implementation is based on a logic-based language compilation. A Prolog-like program is translated into Warren Abstract Machine (WAM) instructions by a compiler, and the resulting WAM code is executed by an emulator. The inference engine and the relational database are tightly coupled, i.e. external records are retrieved from the underlying database and unified with Prolog-like terms as and when required. The tight coupling of a logic-based language and a relational DBMS achieved a satisfactory performance.