A SQL

We are presenting a coherent framework for XQuery processing that incorporates IR-style approximate matching and allows the ordering of results by their relevance score. Our relevance ranking algorithm is based on both stem matching and term proximity. Our XQuery processor is stream-based, consisting of iterators connected into pipelines. In our framework, all values produced by XQuery expressions are assigned scores,and these scores propagate and are combined when piped through the iterators. The most important feature of our evaluation engine is the use of structural and content-based inverse indexes that deliver data in document order and facilitate the use of efficient merge joins to evaluate path expressions and search predicates. We present the rules for the translation from a large part of XQuery to iterator pipeline. Our modular approach of building pipelines to evaluate XQuery scales up to any query complexity because the pipes can be connected in the same way complex queries are formed from simpler ones.

Schema mappings establish a correspondence between data stored in two databases, called source and target respectively. Query processing under schema mappings has been investigated extensively in the two cases where each target atom is mapped to a query over the source (called GAV, global-as-view), and where each source atom is mapped to a query over the target (called LAV, local-as-view). The general case, called GLAV, in which queries over the source are mapped to queries over the target, has attracted a lot of attention recently, especially for data exchange. However, query processing for GLAV mappings has been considered only for the basic service of query answering, and mainly in the context of conjunctive queries (CQs) in relational databases. In this paper we study query processing for GLAV mappings in a wider sense, considering not only query answering, but also query rewriting, perfectness (the property of a rewriting to compute exactly the certain answers), and query containment relative to a mapping. We deal both with the relational case, and with graph databases, where the basic querying mechanism is that of regular path queries. Query answering in GLAV can be smoothly reduced to a combination of the LAV and GAV cases, and for CQs this reduction can be exploited also for the remaining query processing tasks. In contrast, as we show, GLAV query processing for graph databases is non-trivial and requires new insights and techniques. We obtain upper bounds for answering, rewriting, and perfectness, and show decidability of relative containment.

Aufgrund fallender Preise zur Speicherung von Daten kann man derzeit eine explosionsartige Zunahme in der Menge der verfügbaren Daten beobachten. Diese Entwicklung gibt Unternehmen und wissenschaftliche Institutionen die Möglichkeit empirische Daten in ungekannter Größenordnung zu analysieren. Für viele Firmen ist die Analyse der gesammelten Daten aus ihrem operationalen Geschäft längst zu einem zentralen strategischen Aspekt geworden. Im Gegensatz zu der seit längerem schon betriebenen Business Intelligence, bestehen diese Analysen nicht mehr nur aus traditionellen relationalen Anfragen. In zunehmendem Anteil kommen komplexe Algorithmen aus den Bereichen Data Mining und Maschinelles Lernen hinzu, um versteckte Muster in den Daten zu erkennen, oder Vorhersagemodelle zu trainieren. Mit zunehmender Datenmenge und Komplexität der Analysen wird jedoch eine neue Generation von Systemen benötigt, die diese Kombination aus Anfragekomplexität und Datenvolumen gewachsen sind. Relationale Datenbanken waren lange Zeit das Zugpferd der Datenanalyse im großen Stil. Grund dafür war zum großen Teil ihre deklarativen Anfragesprache, welche es ermöglichte die logischen und physischen Aspekte der Datenspeicherung und Verarbeitung zu trennen, und Anfragen automatisch zu optimieren. Das starres Datenmodell und ihre beschränkte Menge von möglichen Operationen schränken jedoch die Anwendbarkeit von relationalen Datenbanken für viele der neueren analytischen Probleme stark ein. Diese Erkenntnis hat die Entwicklung einer neuen Generation von Systemen und Architekturen eingeläutet, die sich durch sehr generische Abstraktionen für parallelisierbare analytische Programme auszeichnen; MapReduce kann hier beispielhaft genannt werden, als der zweifelsohne prominenteste Vertreter dieser Systeme. Zwar vereinfachte und erschloss diese neue Generation von Systemen die Datenanalyse in diversen neuen Anwendungsfeldern, sie ist jedoch nicht in der Lage komplexe Anwendungen aus den Bereichen Data Mining und Maschinelles Lernen effizient abzubilden, ohne sich dabei extrem auf spezifische Anwendungen zu spezialisieren. Verglichen mit den relationalen Datenbanken haben MapReduce und vergleichbare Systeme außerdem die deklarative Abstraktion aufgegeben und zwingen den Anwender dazu systemnahe Programme zu schreiben und diese manuell zu optimieren. In dieser Dissertation werden verschiedene Techniken vorgestellt, die es ermöglichen etliche der zentralen Eigenschaften von relationalen Datenbanken im Kontext dieser neuen Generation von daten-parallelen Analysesystemen zu realisieren. Mithilfe dieser Techniken ist es möglich ein Analysesystem zu beschreiben, dessen Programme gleichzeitig sowohl generische und ausdrucksstark, als auch prägnant und deklarativ sind. Im einzelnen stellen wir folgende Techniken vor: Erstens, eine Programmierabstraktion die generisch ist und mit komplexen Datenmodellen umgehen kann, aber gleichzeitig viele der deklarativen Eigenschaften der relationalen Algebra erhält. Programme, die gegen dies Abstraktion entwickelt werden können ähnlich optimiert werden wie relationale Anfragen. Zweitens stellen wir eine Abstraktion für iterative daten-parallele Algorithmen vor. Die Abstraktion unterstützt inkrementelle (delta-basierte) Berechnungen und geht mit zustandsbehafteteten Berechnungen transparent um. Wir beschreiben wie man einen relationalen Anfrageoptimierer erweitern kann so dass dieser iterative Anfragen effektiv optimiert. Wir zeigen dabei dass der Optimierer dadurch in die Lage versetzt wird automatisch Ausführungspläne zu erzeugen, die wohlbekannten, manuell erstellten Programmen entsprechen. Die Abstraktion subsumiert dadurch spezialisierte Systeme (wie Pregel) und bietet vergleichbare Performanz. Drittens stellen wir Methoden vor, um die Programmierabstraktion in eine funktionale Sprachen einzubetten. Diese Integration ermögliche es prägnante Programme zu schreiben und einfach wiederzuverwendenden Komponenten und Bibliotheken, sowie Domänenspezifische Sprachen, zu erstellen. Wir legen dar wie man die Übersetzung und Optimierung des daten-parallelen Programms mit dem Sprachübersetzer der funktionalen Sprache so integriert, dass maximales Optimierungspotenzial besteht.

An improved division operator for relational algebra

Preface: Volume 44, Issue 3

A purely relational account of the true XQuery semantics can turn any relational database system into an XQuery processor. Compiling nested expressions of the fully compositional XQuery language, however, yields odd algebraic plan shapes featuring scattered distributions of join operators that currently overwhelm commercial SQL query optimizers.This work rewrites such plans before submission to the relational database back-end. Once cast into the shape of join graphs, we have found off-the-shelf relational query optimizers---the B-tree indexing subsystem and join tree planner, in particular---to cope and even be autonomously capable of reinventing advanced processing strategies that have originally been devised specifically for the XQuery domain, e.g., XPath step reordering, axis reversal, and path stitching. Performance assessments provide evidence that relational query engines are among the most versatile and efficient XQuery processors readily available today.

Preparing a data set for analysis is generally the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group. In general, a significant manual effort is required to build data sets, where a horizontal layout is required. We propose simple, yet powerful, methods to generate SQL code to return aggregated columns in a horizontal tabular layout, returning a set of numbers instead of one number per row. This new class of functions is called horizontal aggregations. Horizontal aggregations build data sets with a horizontal denormalized layout (e.g. point-dimension, observation-variable, instance-feature), which is the standard layout required by most data mining algorithms. We propose three fundamental methods to evaluate horizontal aggregations: CASE: Exploiting the programming CASE construct; SPJ: Based on standard relational algebra operators (SPJ queries); PIVOT: Using the PIVOT operator, which is offered by some DBMSs. Experiments with large tables compare the proposed query evaluation methods. Our CASE method has similar speed to the PIVOT operator and it is much faster than the SPJ method. In general, the CASE and PIVOT methods exhibit linear scalability, where as the SPJ method does not. In a relational database, especially with normalized tables, a significant effort is required to prepare a summary data set that can be used as input for a data mining or statistical algorithm. Most algorithms require as input a data set with a horizontal layout, with several Records and one variable or dimension per column. That is the case with models like clustering, classification, regression and PCA; consult. Each research discipline uses different terminology to describe the data set. In data mining the common terms are point-dimension. Statistics literature generally uses observation-variable. Machine learning research uses instance-feature. This article introduces a new class of aggregate functions that can be used to build data sets in a horizontal layout (denormalized with aggregations), automating SQL query writing and extending SQL capabilities. We show evaluating horizontal aggregations is a challenging and interesting problem and we introduced alternative methods and optimizations for their efficient evaluation. II. MOTIVATION As mentioned above, building a suitable data set for data mining purposes is a time- consuming task. This task generally requires writing long SQL statements or customizing SQL Code if it is automatically generated by some tool. There are two main ingredients in such SQL code: joins and aggregations; we focus on the second one. The most widely- known aggregation is the sum of a column over groups of rows. Some other aggregations return the average, maximum, minimum or row count over groups of rows. There exist many aggregations functions and operators in SQL. Unfortunately, all these aggregations have limitations to build data sets for data mining purposes. The main reason is that, in general, data sets that are stored in a relational database (or a data warehouse) come

The subject of the study is the methods of intellectual analysis, namely the construction of a decision tree, associative analysis, the identification of patterns between related events based on data presented by a relational model. The purpose of the study is to analyze the features of information units and data structures, using the example of relational systems that affect the technology of knowledge extraction. Tasks: the article solves the following tasks: to consider the relational data model as the most popular and effective data structure used in intelligent information systems for data processing and storage; to analyze the operations of relational algebra, the operational component of the relational data model regarding the application of aggregate functions; to develop a general formal statement of the problem of knowledge extraction from a relational database; to consider the concept of functional associative rules; the ID3 decision tree generation algorithm focused on data processing in relational systems is analyzed. The following methods are implemented: modern view and trends in the field of data mining; features of building information systems based on relational databases, relational algebra, theory of normalization of relations; analysis of literature on the topic of research; comparative analysis. Results achieved: the relational data model is considered as the most effective data structure used in intelligent information systems for data processing and storage. A group of aggregate functions of relational databases is identified and analyzed with respect to key attributes of the relation, which makes it possible to build logical dependencies between information units of the subject area being analyzed. The task of extracting knowledge from the database is formally formulated. The concept of functional associative rules is introduced. The ID3 decision tree generation algorithm focused on data processing in relational systems is carefully analyzed. The semantic network (SN), built on the basis of the proposed approach, allows to increase the efficiency of decision support systems. Conclusions: the universal approach proposed in the article to build a relational data model of an information system for searching for associative patterns in data allows to solve a whole class of typical tasks in which objects are connected by a "many-to-many" relationship or M →N. The relational database model is proposed as a universal information structure for solving associative analysis tasks and presenting knowledge in the form of a semantic network. The examples given in the article confirm the effectiveness of the developed and considered approaches to solving the problem of data mining in the environment of relational systems. Solving the problem of identifying knowledge in data will allow to improve the quality of management decisions made.

We investigate the problem of incremental maintenance of materialized XML views. We are considering the case where the underlying database is a relational database and the view exposed to querying is a materialized XML view. Then, updates to the underlying database should be reflected to the stored XML view, to keep it consistent with the source data, without recreating the entire view from the database after each source update. Unlike related work that uses algebraic methods, we use source-to-source compositional transformations, guided by the database and view schemata. We first translate SQL updates to pure (update-free) XQuery expressions that reconstruct the entire database state, reflecting the updated values in the new state. Then, we synthesize the right-inverse of the XQuery view function, guided by the view schema. This inverse function is applied to the old view to derive the old database state, which in turn is mapped to the new database state through the update function, and then is mapped to the new view through the view function. The resulting view-to-view function is normalized and translated to XQuery updates that destructively modify the materialized view efficiently to reflect the new view values.

The COCOON project was intended to extend the concepts and the architecture of relational database management systems (DBMSs) beyond nested relational to object-oriented ones. Based upon the nested relational DBMS kernel DASDBS, we have built a prototype implementation of the COCOON model. Key characteristics of COCOON are: generic, set-oriented query and update operators similar to relational algebra and SQL updates, respectively; object-preserving semantics of query operators, which allows for the definition of updatable views; a separation of the two aspects of programming language “classes”: type vs. collection; predicative description of collections, similar to “defined concepts” in KL-One-like knowledge representation languages; automatic classification of objects and views (positioning in the class hierarchy); physical clustering of subobjects via the use of nested relations as the internal storage structures; support for the optimization of both, the physical DB design and query transformation, by corresponding optimizers.

1. Background SAS is a statistical analysis system written by SAS Institute of Cary, North Carolina, United States of America. It is a statistical analysis language that allows users to analyze data without need of extensive EDP training. SAS has the power of a 4th Generation Programming Language. There are interfaces from SAS to various data bases, including DB2 and Oracle. Many relational databases allow the definition of views on the tables managed by the database. SAS/ ACCESS software can be used to process these views and retrieve the DBMS data for use in the SAS system. [9J SAS runs on a wide range of operating systems (UNIX, MVS, CMS, VMS, PRIMOS, AOS/VS, OS/2, MS-DOS, PC DOS, VSE, and Macintosh-1MP interactive statistical analysis only). The most obvious applications of statistical analysis packages to data editing are for statistical and macro editing and for outlier detection. SAS is well suited for these purposes. SAS is easy to use for simple within-record [IJ editing, yet powerful enough to use as a programming language for building generalized data-editing systems.

We introduce a controlled form of recursion in XQuery, an inflationary fixed point operator, familiar from the context of relational databases. This operator imposes restrictions on the expressible types of recursion, but we show that it is sufficiently versatile to capture a wide range of interesting use cases, including Regular XPath and its core transitive closure operator. While the optimization of general user-defined recursive functions in XQuery appears elusive, we describe how inflationary fixed points can be efficiently evaluated, provided that the recursive XQuery expressions are distributive. We test distributivity syntactically and algebraically, and provide experimental evidence that XQuery processors can benefit substantially from this mode of evaluation.

SQL/PGQ is the emerging ISO standard for querying property graphs defined as views over relational data. We formalize its expressive power across three fragments: the read-only core, the read-write extension, and an extended variant with richer view definitions. Our results show that graph creation plays a central role in determining the expressiveness. The read-only fragment is strictly weaker than the read-write fragment, and the latter is still below the complexity class NL. Extending view definitions with arbitrary arity identifiers closes this gap: the extended fragment captures exactly NL. This yields a strict hierarchy of SQL/PGQ fragments, whose union covers all NL queries. On ordered structures the hierarchy collapses: once arity-2 identifiers are allowed, higher arities add no power, mirroring the classical transitive-closure collapse and underscoring the central role of view construction in property graph querying.

Typed query languages for databases containing queries

The FCG code generator produces portable code that supports efficient twospace copying garbage collection. The code generator transforms the output of the FAST compiler front end into an abstract machine code. This code explicitly uses a call stack, which is accessible to the garbage collector. In contrast to other functional language compilers that generate assembly directly, FCG uses the C compiler for code generation, providing high-quality code optimisations and portability. To make full use of the C compiler's capabilities, FCG includes an optimisation scheme that transforms the naively generated stack-based code into a register-based equivalent form. The results for a benchmark of functional programs show that code generated by FCG performs well in comparison with the LML compiler.