Relational DBMSs Research Articles

Minimizing the MOLAP/ROLAP Divide: You Can Have Your Performance and Scale It Too

Over the past generation, data warehousing and online analytical processing (OLAP) applications have become the cornerstone of contemporary decision support environments. Typically, OLAP servers are implemented on top of either proprietary array-based storage engines (MOLAP) or as extensions to conventional relational DBMSs (ROLAP). While MOLAP systems do indeed provide impressive performance on common analytics queries, they tend to have limited scalability. Conversely, ROLAP's table oriented model scales quite nicely, but offers mediocre performance at best relative to the MOLAP systems. In this paper, we describe a storage and indexing framework that aims to provide both MOLAP like performance and ROLAP like scalability by essentially combining some of the best features from both. Based upon a combination of R-trees and bitmap indexes, the storage engine has been integrated with a robust OLAP query engine prototype that is able to fully exploit the efficiency of the proposed storage model. Specifically, it utilizes an OLAP algebra coupled with a domain specific query optimizer, to map user queries directly to the storage and indexing framework. Experimental results demonstrate that not only does the design improve upon more naive approaches, but that it does indeed offer the potential to optimize both query performance and scalability.

Integrating trust management and access control in data-intensive Web applications

The widespread diffusion of Web-based services provided by public and private organizations emphasizes the need for a flexible solution for protecting the information accessible through Web applications. A promising approach is represented by credential-based access control and trust management. However, although much research has been done and several proposals exist, a clear obstacle to the realization of their benefits in data-intensive Web applications is represented by the lack of adequate support in the DBMSs. As a matter of fact, DBMSs are often responsible for the management of most of the information that is accessed using a Web browser or a Web service invocation. In this article, we aim at eliminating this gap, and present an approach integrating trust management with the access control of the DBMS. We propose a trust model with a SQL syntax and illustrate an algorithm for the efficient verification of a delegation path for certificates. Our solution nicely complements current trust management proposals allowing the efficient realization of the services of an advanced trust management model within current relational DBMSs. An important benefit of our approach lies in its potential for a robust end-to-end design of security for personal data in Web scenario, where vulnerabilities of Web applications cannot be used to violate the protection of the data residing on the database server. We also illustrate the implementation of our approach within an open-source DBMS discussing design choices and performance impact.

Data-Intensive Science and Engineering:Requirements and Challenges

Scientific exploration after experimental science,theoretical science and computational science phases,into data-intensive science phase,are accompanied by the arrival of the big data era.Generally,big data refers to a data set with a size of hundreds of TB,or several PB or even above,and it is often distributed,heterogeneous and in low-quality.It is critical to devise novel methods to manage big data since traditional database management techniques are unfeasible to manage big data efficiently and effectively,though such techniques,especially the commercial relational DBMSs,have achieved great success in the past decades.This paper discusses concrete requirements and realistic challenges of Data-Intensive Science and Engineering(DISE),ranging from data storage and organization,computational method,data analysis,to user interfaces.Meanwhile,data quality,data security and data curation should be paid more attentions.In this paper,we attempt to describe the architecture of DISE,review the recent progress,and discuss the challenges and future work briefly.

AS B-트리: SSD를 사용한 B-트리에서 삽입 성능 향상에 관한 연구

최근 플래시 메모리 및 SSD가 노트북이나 PC의 저장장치로 사용되는 것뿐 아니라, 기업용 서버의 차세대 저장장치로 주목 받고 있다. 대용량의 데이터를 처리하는 데이터베이스에서는 삽입, 삭제, 검색을 빠르게 하기 위해 다양한 색인 기법을 사용하는데 그 중B-트리 구조가 대표적인 기법이다. 하지만 플래시 메모리 상에서는 하드디스크와 달리 덮어쓰기(overwrite) 연산을 수행하기 위해서는 먼저 해당 블록(block)에 대하여 플래시 메모리의 연산 중 가장 비용이 많이 요구되는 삭제(erase) 연산을 수행 해야만 한다. 이러한 문제점을 극복하기 위해 플래시 메모리 사이에 위치하는 플래시 변환 계층(Flash memory Translation Layer)을 사용한다. 이 플래시 변환 계층은 수정한 데이터를 동일한 논리 주소에 덮어쓰기를 하더라도 실제로 임의의 다른 물리 주소에 저장하도록 하여 이 문제를 해결할 수 있다. NAND 플래시 메모리를 배열 형태로 포함하고 있는 SSD는 한 개 이상의 플래시 메모리 패키지를 병렬로 접근할 수 있다. 이러한 병렬 접근 방식을 사용하여 쓰기 연산 성능을 향상하기 위해서는 연속한 논리 주소에 쓰기 연산을 요청하는 것이 유리하다. 하지만 B-트리는 구성 노드에 대한 삽입 삭제 연산 시에 대부분 연속되지 않은 논리 주소 공간에 대한 갱신 연산이 일어나게 된다. 따라서 SSD의 병렬 접근 방식을 최대한 활용할 수 없게 된다. 본 논문에서는 수정한 노드를 연속한 논리 주소에 쓰도록 하는 AS B-트리 구조를 제안하여 SSD의 병렬 접근 방식을 최대한 활용할 수 있도록 하였다. 구현 및 실험한 결과 AS B-트리에서의 삽입 시간이 B-트리보다 21% 개선된 것을 확인하였다. Recently flash memory has been being utilized as a main storage device in mobile devices, and flashSSDs are getting popularity as a major storage device in laptop and desktop computers, and even in enterprise-level server machines. Unlike HDDs, on flash memory, the overwrite operation is not able to be performed unless it is preceded by the erase operation to the same block. To address this, FTL(Flash memory Translation Layer) is employed on flash memory. Even though the modified data block is overwritten to the same logical address, FTL writes the updated data block to the different physical address from the previous one, mapping the logical address to the new physical address. This enables flash memory to avoid the high block-erase cost. A flashSSD has an array of NAND flash memory packages so it can access one or more flash memory packages in parallel at once. To take advantage of the internal parallelism of flashSSDs, it is beneficial for DBMSs to request I/O operations on sequential logical addresses. However, the B-tree structure, which is a representative index scheme of current relational DBMSs, produces excessive I/O operations in random order when its node structures are updated. Therefore, the original b-tree is not favorable to SSD. In this paper, we propose AS(Always Sequential) B-tree that writes the updated node contiguously to the previously written node in the logical address for every update operation. In the experiments, AS B-tree enhanced 21% of B-tree's insertion performance.

A service-oriented system for distributed data querying and integration on Grids

Data Grids rely on the coordinated sharing of and interaction across multiple autonomous database management systems. They provide transparent access to heterogeneous and autonomous data resources stored on Grid nodes. Data sharing tools for Grids must include both distributed query processing and data integration functionality. This paper presents the implementation of a data sharing system that (i) is tailored to data Grids, (ii) supports well established and widely spread relational DBMSs, and (iii) adopts a hybrid architecture by relying on a peer model for query reformulation to retrieve semantically equivalent expressions, and on a wrapper-mediator integration model for accessing and querying distributed data sources. The system builds upon the infrastructure provided by the OGSA-DQP distributed query processor and the XMAP query reformulation algorithm. The paper discusses the implementation methodology, and presents empirical evaluation results.

Efficient Storage and Querying of Horizontal Tables Using a PIVOT Operation in Commercial Relational DBMSs

In recent years, a horizontal table with a large number of attributes is widely used in OLAP or e-business applications to analyze multidimensional data efficiently. For efficient storing and querying of horizontal tables, recent works have tried to transform a horizontal table to a traditional vertical table. Existing works, however, have the drawback of not considering an optimized PIVOT operation provided (or to be provided) in recent commercial RDBMSs. In this paper we propose a formal approach that exploits the optimized PIVOT operation of commercial RDBMSs for storing and querying of horizontal tables. To achieve this goal, we first provide an overall framework that stores and queries a horizontal table using an equivalent vertical table. Under the proposed framework, we then formally define 1) a method that stores a horizontal table in an equivalent vertical table and 2) a PIVOT operation that converts a stored vertical table to an equivalent horizontal view. Next, we propose a novel method that transforms a user-specified query on horizontal tables to an equivalent PIVOT-included query on vertical tables. In particular, by providing transformation rules for all five elementary operations in relational algebra as theorems, we prove our method is theoretically applicable to commercial RDBMSs. Experimental results show that, compared with the earlier work, our method reduces storage space significantly and also improves average performance by several orders of magnitude. These results indicate that our method provides an excellent framework to maximize performance in handling horizontal tables by exploiting the optimized PIVOT operation in commercial RDBMSs.

Combining relational algebra, SQL, constraint modelling, and local search

AbstractThe goal of this paper is to provide a strong integration between constraint modelling and relational DBMSs. To this end we propose extensions of standard query languages such as relational algebra and SQL, by adding constraint modelling capabilities to them. In particular, we propose non-deterministic extensions of both languages, which are specially suited for combinatorial problems. Non-determinism is introduced by means of a guessing operator, which declares a set of relations to have an arbitrary extension. This new operator results in languages with higher expressive power, able to express all problems in the complexity class NP. Some syntactical restrictions which make data complexity polynomial are shown. The effectiveness of both extensions is demonstrated by means of several examples. The current implementation, written in Java using local search techniques, is described.

The 8 requirements of real-time stream processing

Applications that require real-time processing of high-volume data steams are pushing the limits of traditional data processing infrastructures. These stream-based applications include market feed processing and electronic trading on Wall Street, network and infrastructure monitoring, fraud detection, and command and control in military environments. Furthermore, as the "sea change" caused by cheap micro-sensor technology takes hold, we expect to see everything of material significance on the planet get "sensor-tagged" and report its state or location in real time. This sensorization of the real world will lead to a "green field" of novel monitoring and control applications with high-volume and low-latency processing requirements.Recently, several technologies have emerged---including off-the-shelf stream processing engines---specifically to address the challenges of processing high-volume, real-time data without requiring the use of custom code. At the same time, some existing software technologies, such as main memory DBMSs and rule engines, are also being "repurposed" by marketing departments to address these applications.In this paper, we outline eight requirements that a system software should meet to excel at a variety of real-time stream processing applications. Our goal is to provide high-level guidance to information technologists so that they will know what to look for when evaluation alternative stream processing solutions. As such, this paper serves a purpose comparable to the requirements papers in relational DBMSs and on-line analytical processing. We also briefly review alternative system software technologies in the context of our requirements.The paper attempts to be vendor neutral, so no specific commercial products are mentioned.

MySQL: lessons learned on a digital library

In recent years, the open source products have achieved enterprise-level quality. In response, enterprises have become more interested in migrating from proprietary, commercial products to open source. Businesses around the world commonly use the two leading open source relational DBMSs, MySQL and PostgreSQL. The Los Alamos National Laboratory's Research Library has used MySQL databases for years. However, a recent project to develop a comprehensive database of scientific journal articles and citation information revealed its unique strengths and features.

An implementation for the ATLAS conditions data management based on relational DBMSs

Developing the trigger and the data acquisition (TDAQ) for A Toroidal LHC ApparatuS (ATLAS) detector at LHC poses new challenges including the requirement to monitor, calibrate and align the detector while the on line selection is running simultaneously over thousands of processors. These include slowly evolving data such as detector alignment, calibration, and robustness, as well as data from the detector control system that cannot be stored with the event data, but must be directly accessible from each event. Motivated by the ATLAS data challenges and the recent evolution of Open Source database systems such as MySQL and Postgres, we have developed an implementation of the ATLAS Conditions database system based on the MySQL Relational DBMS. Together with Postgres, MySQL is well known for performance and simplicity and an increasing number of supported features. Several issues were investigated: how to develop a programmable API that could be used everywhere through the ATLAS online and offline computing systems; what are the optimal ways to map the conditions data model into a relational model; what is the data clustering model that is best suited to address the ATLAS data volume scalability problems. Some general issues of conditions databases are also studied, including: the schema; facilities for tagging and versioning; problems involved in managing the tiny detector control data as well as the complex; interrelated detector configuration objects. The extensive tests that were performed to monitor the development of the system are also described.

What database management system(s) should be employed in bioinformatics applications?

1. No DBMS: Although most computer scientists would dismiss this alternative as ridiculous, I would wager that a significant number of the hundreds of molecular biology DBs that now exist are not constructed using any DBMS. This should make us all stop and think. Either the biologists who are constructing these DBs know something that computer scientists don’t know, or they don’t know something that computer scientists do know. I believe the latter is the case—I believe that the vast majority of biologists have not received even the most elementary education in DBs, and have little idea of how DBMSs can help them, nor of how to go about using a DBMS. Note that the situation today is far improved over that of ten years ago. Today most large DB projects do employ a DBMS, whereas 10 years ago virtually none did so. Bioinformatics is still suffering from those mistakes. 2. Open-source DBMSs: Many of the molecular-biology DB projects that are employing DBMSs are employing open-source DBMSs to save money and because they believe that access to the source code will be useful in fixing any DBMS bugs that are discovered, or in extending the DBMS. My limited understanding of these DBMSs is that they have significant technical limitations compared to commercial DBMS product, yet little hard information about this issue is circulating in the bioinformatics community. We should obtain and circulate such information, whether from the literature or by empirical study. I also believe that the DBMSs are sufficiently complex that access to their source code is essentially useless to any but the most experienced DBMS hacker (and perhaps not even to them), and that this point must be articulated to the community. 3. Commercial DBMSs: Many projects use commercial relational DBMSs, specifically, Oracle. Virtually no projects use commercial object-oriented DBMSs (OO DBMSs). Why? OO DBMSs were developed to overcome limitations of relational DBMSs, and have now existed for about a decade. Little hard information about this issue is circulating in the bioinformatics community. We should obtain and circu-

Data management for large-scale scientific computations in high performance distributed systems

With the increasing number of scientific applications manipulating huge amounts of data, effective high-level data management is an increasingly important problem. Unfortunately, so far the solutions to the high-level data management problem either require deep understanding of specific storage architectures and file layouts (as in high-performance file storage systems) or produce unsatisfactory I/O performance in exchange for ease-of-use and portability (as in relational DBMSs). In this paper we present a novel application development environment which is built around an active meta-data management system (MDMS) to handle high-level data in an effective manner. The key components of our three-tiered architecture are user application, the MDMS, and a hierarchical storage system (HSS). Our environment overcomes the performance problems of pure database-oriented solutions, while maintaining their advantages in terms of ease-of-use and portability. The high levels of performance are achieved by the MDMS, with the aid of user-specified, performance-oriented directives. Our environment supports a simple, easy-to-use yet powerful user interface, leaving the task of choosing appropriate I/O techniques for the application at hand to the MDMS. We discuss the importance of an active MDMS and show how the three components of our environment, namely the application, the MDMS, and the HSS, fit together. We also report performance numbers from our ongoing implementation and illustrate that significant improvements are made possible without undue programming effort.

Object-Oriented Databases with Relational DBMSs

The expediency of the use of object-oriented models of data as datalogical models in designing databases is shown, and methods of integration of relational and object-oriented models are considered. A relational scheme for storage of attributes and relations of object-oriented models is proposed.

A Methodology for Schema Integration Using Concept Hierarchies

Since relational DBMSs are wide-spread, providing a relational global view for users who are only familiar with the relational model to access data in a multidatabase system is significant. However, the prior work on schema integration can not effici

On the linkage of dynamic tables in relational DBMSs

Tables and operations over tables are at the center of the relational model and have been at the core of the Structured Query Language (SQL) since its development in the 1970s. As database applications have grown rapidly, the conceptof tables has been generalized in database languages. The new generalized table conceptin the SQL standard and in some commercial databases includes explicitly defined derived tables, such as user-defined temporary tables, transition tables, user-defined table functions, and table locators, that can be manipulated by users. We call them dynamic tables, because their entities exist only at run time. The challenges that these dynamic tables pose to existing relational engines lie in the linkage between the creation of the derived table and its references. In this paper, we describe a uniform framework for compile-time and run-time processing of dynamic tables. We also give a thorough explanation of how such a generic framework can be realized in existing relational database management systems, such as IBM DATABASE 2™ Common Server. Our experience with our prototype has shown the simplicity, generality, and efficiency of our approach.

Composite-Object views in relational DBMS: An implementation perspective

We present a novel approach for supporting Composite Objects (CO) as an abstraction over the relational data. This approach brings the advanced CO model to existing relational databases and applications, without requiring an expensive migration to other DBMSs which support CO. The concept of views in relational DBMSs (RDBMS) gives the basis for providing the CO abstraction. This model is strictly an extension to the relational model, and it is fully upward compatible with it. We present an overview of the data model. We put emphasis in this paper on showing how we have made the extensions to the architecture and implementation of an RDBMS (Starburst) to support this model. We show that such a major extension to the data model is in fact quite attractive both in terms of implementation cost and query performance. We introduce a CO cache for navigation through components of a CO. With this technique, the performance of navigation through COs, which has been of a concern in RDBMSs in the past, is in fact quite satisfactory. We present our practical experience in using this facility. We show that our work on CO enables existing RDBMSs to incorporate efficient CO facilities at a low cost and at a high degree of application reusability and database sharability.

An entity-based approach to query processing in relational databases: Part I: Entity type representation

An entity-based query interface to relational databases is developed. The interface combines some object-oriented features with the strengths of universal relation DBMSs (UR-DBMSs) by a value-oriented high-level representation of complex entity types. It enhances logical data independence by hiding their implementation in a relational database. The abstraction level of the entity representation lies between the relations of the relational model and the universal relation. This allows higher level expressions than the relational model but avoids the weaknesses of the UR-DBMSs. The interface supports a declarative entity-based query language of the level of Quel (described in Part II). The key features of the interface are: (1) the entity type representation and associated operations which allow the derivation of the entity types of the results of entity-based queries, and (2) and unambiguous and optimizing solution to the query interpretation problem of UR-DBMSs. The interface constructs equivalent RA expressions for entity queries to be evaluated by relational DBMSs (RDBMSs). The interface is specified in a formal and general way. It has essential benefits, which support further processing of query results, improve query efficiency, clarify query semantics, enhance logical data independence, and allow use of existing RDBMS software by interfacing them to the entity level processing in a systematic way. It separates the entity type and relational levels into different domains and integrates them together through a well-defined interface. The interface exploits fully and easily the optimization capabilities of universal relation-based systems. The entity types provide the users with a high-level and natural view of the database. The users express their queries in terms of the entity types. This simplifies the expression of complex queries considerably w.r.t. conventional query systems. The interface exploits this knowledge in an effective way.

Refinement of enquiries in retrieval systems based on the universal relation model

Relational Database Management Systems are currently suggested to be used as a base for document retrieval systems. This is because commercially available relational DBMSs provide effective tools for managing databases and, especially, the underlying relational model supports ad hoc queries which are characteristic of document retrieval systems. When SQL is directly used for ad hoc queries navigation through the underlying database scheme has to be provided by the enquirer, which could be very difficult. This article presents the achievable simplifications when document retrieval systems will be based on the universal relation model. When the search condition of a query is too fuzzy, the cardinality of the answer set may become very large. Some retrieval systems thus provide the capability of “refining” answer sets; this allows referring to an existing answer set when issuing an additional query to reduce the number of qualifying tuples. There are two different methods to refine a given answer set: one method produces a new query based on the predicates of both queries, the other method creates an intermediate answer set based on the new query and provides the refined answer set via suitable set manipulations. The results are in general different. Focusing on queries on universal relation systems, we describe the above problem in more detail and provide criteria under which the above methods produce the same result.

Framework for query optimization in distributed statistical databases

Recently, there has been a growing interest in statistical database (SDB) research. When SDBs are dispersed among computing facilities at various sites (e.g., in health-care networks) an additional dimension is added to the already difficult problems faced by the SDB designer. A distributed statistical database management system (DS-DBMS) consists of micro data (i.e., raw data) and macro data (i.e., aggregated objects called summary tables), which can be considered essentially as aggregated views of the raw data in a special format. The first part of the paper gives an overview of a model for the representation of both raw data (micro data) and summary tables (macro data). The model is an extension of the relational model (so that existing distributed database systems can be exploited). Most of the first part is devoted to defining operations on macro data sets. Based on these operations, a set of equivalent relational operations is described, as one of the main objectives in defining the micro and macro data sets, and the operations on them, has been to use as much as possible the capabilities that are already offered by most relational DBMSs. The second part of the paper deals with one of the important aspects of performance in a DS-DBMS, namely, the efficient processing of queries. This is heavily influenced by the performance of query optimizers. However, to provide query optimization in a DS-DBMS, special issues are raised that manifest themselves in different scenarios. Some of the important issues and problems raised are discussed and solutions proposed. In addition, a set of transformations on macro operations (similar to those in relational algebra) are introduced, which can be used for optimizing queries in DS-DBMSs.

Algorithms for creating indexes for very large tables without quiescing updates

As relational DBMSs become more and more popular and as organizations grow, the sizes of individual tables are increasing dramatically. Unfortunately, current DBMSs do not allow updates to be performed on a table while an index (e.g., a B + -tree) is being built for that table, thereby decreasing the systems' availability. This paper describes two algorithms in order to relax this restriction. Our emphasis has been to maximize concurrency, minimize overheads and cover all aspects of the problem. Builds of both unique and nonunique indexes are handled correctly. We also describe techniques for making the index-build operations restartable, without loss of all work, in case a system failure were to interrupt the completion of the creation of the index. In this connection, we also present algorithms for making a long sort of operation restartable. These include algorithms for the sort and merge phases of sorting.