Synchronization of Data between Special-Type Tables and the Database

Correct automation of access to information can be achieved by creating tools based on the theory of inter-model mappings and ensuring the commutativity of data transformations. This paper is devoted to the technology of data transfer between a relational database and a special type of tabular representation of data. The table structure is a convenient tool for the user, since it allows not only to edit data synchronized with the database, but also to perform various types of analysis using spreadsheets. In general case, the table size can be huge. This paper proposes a technique for reducing table size by applying logical constraints when loading data. This introduces two problems: fictitious empty values and the loss of empty values needed to edit the data. The work proposes a solution to these problems by using an intermediate representation of data in the form of a database query that contains logical constraints. A special form of these constraints, consistent with the SQL standard, is necessary to deal with the null value problem. For this purpose, subsets of relations are formed from a partial order that corresponds to referential integrity in the database. The resulting hierarchies are used to form the table dimensions. The paper concludes with an analysis of the correctness of the transformations.