Programmatic implementation of the Dijkstra algorithm in the Transact-SQL language using relational algebra

Mikhail Urubkin,Denis Fathi,Vasiliy Galushka,Vladimir Fathi,Sofya Petrenkova

doi:10.1051/e3sconf/202016410016

Mikhail Urubkin, Denis Fathi + Show 3 more

Open Access

https://doi.org/10.1051/e3sconf/202016410016

Copy DOI

Abstract

The article is devoted to the topical issue of data processing in the database management systems. It presents a solution to the problem of finding paths in a graph using Dijkstra’s algorithm, set as a sequence of relational operations and functions of the Transact-SQL language. The efficiency of the known information system architectures and the impact of various ways of distributing functions between system components are reviewed. The article describes features of the relational algebra and the Transact-SQL operations, and provides a brief description of Dijkstra’s algorithm. For its programmatic implementation, several stages are defined, for each of which a formal description of the relational operations performed on it is given. The outputs of these operations are shown using the example of the database tables, and the algorithm to find the final path is given. The issues of the proposed method’s productivity and security of programmatic implementation of the path search in a graph are discussed separately.

Highlights

Contemporary large information systems are built, as a rule, using one of the options of a distributed architecture, the most common of which are client-server and three-tier architectures. Both of them assume the presence of a database (DB) server in the information system structure, which functions are strictly limited to storing data, searching for it, and issuing it on request, as well as an application server in the three-tier architecture or a thick client in the client-server architecture
In any of these cases, there is a need to transfer data over the network between the components of the information system, and along with this, there are increasing requirements for the bandwidth of communication channels and the level of transmission security, which in turn increases the computational cost of performing cryptographic operations
This goal can be achieved by combining the functions of the application server and the database server (Fig. 1), which will allow eliminating the need to transfer data between them, perform conversions from the storage format in the database to the presentation format in the program, and will generally simplify the architecture and logic of the information system

Summary

Introduction

Contemporary large information systems are built, as a rule, using one of the options of a distributed architecture, the most common of which are client-server and three-tier architectures. It happens when you need to find the route on the map, where, for example, streets are indicated with graph edges and crossroads with graph vertices, but in other cases: building a route to a page on a site, searching for a node in a computer network, etc The solution of this task using a classic three-tier architecture will require storing data in the database in form of relational tables, passing them to the application, converting them to adjacency matrices, performing path calculation, and sending the result to the client (Fig. 1). This goal can be achieved by combining the functions of the application server and the database server (Fig. 1), which will allow eliminating the need to transfer data between them, perform conversions from the storage format in the database to the presentation format in the program, and will generally simplify the architecture and logic of the information system

Result presentation

Conclusions