PRESERVATION OF HIERARCHY STRUCTURES IN RELATIVE DATABASES

Abstract

Relational database management systems and the SQL language itself do not have any built-in mechanisms for storing and managing hierarchical structures. There are several different ways to represent trees in relational databases. This paper considers the method of modeling hierarchical data structures in the form of Adjacency Lists and Closure Tables. For each method, there are examples of writing queries to solve typical problems encountered when working with tree structures: finding all descendant leaves, all descendants and ancestors of a given leaf, moving a leaf to another ancestor leaf, and deleting leaves with all its descendants. The possibility of using recursive queries when displaying the entire tree in the Adjacency List model is considered. If the depth of the tree is not known, or it is not known at what level the specified element is, the query can not be built by standard means of the SELECT statement, then you need to create a recursive procedure, or write a recursive query. In order to avoid recursion when outputting the whole tree, all nodes of the subtree, and finding the path from a certain place to the root, the modeling of hierarchical data structures is performed in the form of a connection table (Closure Table). This complicates the process of adding a new leaf and moving the leaf to another ancestor leaf. In this case, to simplify the writing of queries, it is suggested to create triggers that will build or rebuild the links. Given the fact that sometimes there is a need to preserve dependent, in particular hierarchical structures in a relational database, you need to be able to plow the model of preservation of such data. The choice of method for solving a specific problem is influenced by the speed of basic operations with trees. Exploring different options for organizing SQL tree structures will allow you to understand and choose the best way to build such a structure in a relational database for a specific task. All SQL queries in this paper were created and tested for Oracle relational databases.