The relevance of the problem of optimizing the database structure of a node in corporate information systems (CIS) is due to the widespread use of information technologies of multilevel, geographically dispersed computer systems, including those with distributed databases. One of the research aims is to determine and build a mathematical model of the optimality criteria for the structure of a remote node of the distributed corporate information system database. The statistics of user SQL-queries activity is taken into account and presented in the form of a multidimensional database. Criteria of the model effectiveness are formulated, which are independence from the central node of the database, the size of the local database, and an indicator of the level of need for data synchronization. The problem of multicriteria optimization is solved by using of hierarchy analysis method. Among the using method's features can be mentioned: different sets of optimality criteria for the evolving individuals; quantifying of the data representation marker value into 5 alternatives and automatically presetting the matrices of pairwise comparisons on the last level of the hierarchy. Solving the problem of multicriteria analysis and choosing the best alternative makes possible to determine the optimal level of the data representation marker. It makes possible to classify the attributes and tuples of DB relations according to their representation on the node of distributed CIS.
In information systems development, there is a trend of transition from local to distributed
databases (DDB). There are many database management systems (DBMS) that allow you to host,
maintain and process data on various nodes of computer information systems (CIS). The main task of
distributed database management systems is to provide access control to the data of many users and
ensure the integrity and consistency of data [1]. Within one company there is a need to automate
different types of accounting [
The database structure optimizing is considered in [
A combined data distribution strategy is the best in terms of combining the benefits of strategies with and without duplication. But when using it, in addition to the task of synchronizing duplicate information, the task of designing the structure of the database is actual, depending which node data belonging to. In addition, the performance of the system will directly depend on the decision on the need for partial or complete duplication of data. Some tables of a relational database can be duplicated completely, and some – after projection and selection. That is, for optimized data representation on a remote node, it is necessary to use vertical and horizontal data fragmentation procedures.
choosing the best alternative to the marker of data representation of the remote node of distributed
The developed model should take into account the statistics of user requests to local and remot e
data. Using filtering by selected dimensions, the appropriate subsets of data can be obtained [
The obtained multicriteria problem must be solved to determine the optimal level of data
representation marker. It should be noted that the optimality criteria, the models of which were
defined, are independent, monotonic and are represented on the set of real numbers in the interval [0;
1]. The classical methods of Pareto and Slater [
Among the well-known relational algebra operations [
R'' = {tup[P] | tup[P] ∈ R[P]data ˄ F(tup, S) tr=ue} (1)
Q = <Workplace, User, Application, Rset'', Qsetinner>, where workstation = <Type, Location>; User = <Role, Name>; Rset'' = { R'' | {tup[P] | tup[P] ∈ R[P]data ˄ F(tup, S) = true} } – the set of resulting relations obtained from the basic relations (tables) of the database by the corresponding queries; – is a set of nested queries of the main query Q.
will be involved - availability and speed of data obtaining, independence from the central DB node, the DB size, the level of data reliability, the need for further synchronization.
be defined as the union of subsets R' of all queries received by the database from a remote node R''union = ⋃ =1
′′ , or R''union = {tup[Punion] | tup[Punion] ∈ R[Punion]data ˄ F(tup, Sunion) = true},
where tup[Punion] = ⋃ =1
[ ] , and Sunion = ⋁
=1
To avoid the need for further replication some data that required on the DDB node can only be presented on the central node of the database and participate the query through the use of distributed queries. So the resulting relation Rremote will only be a subset of R''union. Due to the fact that to represent the data on the remote node it is necessary to use elements of both vertical and horizontal data fragmentation (both projection and selecting), a subset of the base relation R that will describe the relation of the remote node can be represented as follows: ℎ = {A | A ∈ Rshema , Rprimary ⊂ ℎ , A ∈ Rprimary ˅ Fa(Node, A) = true}
= { tup | tup ∈ Rdata , tupprimary ∈ Rremote-depdata ˅ Ftup(Node, tup) = true}
these relations, depending on the current. Otherwise, the need for data is solved using the evaluation function Ftup(Node, tup).
The model of presenting user queries should support the possibility of their further classification according to belonging to a particular workplace, location, user role and other criteria that can be added to the model. That is, the user query is defined as (2) (3) (4) the database has a maximum level. The speed of data obtaining compared to the central node is usually lower due to less powerful computing resources, but can be increased by performing selecting and projection operations and decreasing the number of data locks. The local database is large, therefore this criteria is not optimal. Also, all data requires synchronization with the central node, which is quite a resource-intensive operation.
the basis of a relational model of user SQL-queries (4) was created a multidimensional database [
the level of data representation necessity at the node of distributed CIS. For each element value of marker is taken from the following set: {"necessary", neutral", "not required"}. To dimension the "Location", the marking is performed automatically with the value "necessary" for the corresponding remote node and "not required" for all others.
When determining the value of the representation marker for the row of the fact table [
So, we have a model where each dimension attribute has a value, a marker and a weight Adim ={Val, Mrk, vol}, where Mrk = {"obligatorily", "necessary", " neutral", "not required" , "forbidden"}, and vol – weight (ignored for the values of the marker "obligatorily" and "forbidden"). By converting a non-numeric linguistic variable of markers into a numeric value ("obligatorily" – "2", "necessary" – "1", "neutral" – "0", "not required" – "-1", "forbidden" – "-2"), the aggregation function was defined: =1 = {−2, 2, ∃
= 2 ∃
∑ =1( = − 2 ˄ ∄
= 2 ∗
Repr (Node,R,A,tup)=( ( , , ) =1 > ), – the threshold coefficient of data representation in a certain node Node, that is where defined at the range of [-1, 1].
node (or, in some cases, in other nodes) of distributed CIS. In this case, we have the maximization of optimality for criteria of the need for data synchronization. That is because there is no duplication of data. The level of reliability is also maximum, and the size of the local database has a minimum value (no local database). But, at the same time, the availability of data and the access speed are minimized, and the work of CIS is highly dependent on the central node availability.
the others got worse. It is logical to assume that the optimal values of all DCIS DB structure criteria acquire between the 2nd and 3rd steps. To be able to perform the analysis and find the optimal distribution of data between the remote and central nodes, it is necessary to formalize the database structure quality criteria.
access speed directly depend on the representation of user SQL-query data on the node of distributed
Favailab(Node, Q) = ∀ ∃ 1, ∀ ’’ ∃ ℎ
, ’’ ∈ ℎ 0, ∃ ’’ ∄ ℎ , ’’ ∈ ℎ ( ( element the function of belonging to a remote node is equal to one. as the average value where { → → Favailab(Node, Q) = → availab(Node,
) = 1) 1, ( ∃ ′′ ∈
′′ ( ′′) ( ∃ 0, ( ∀ ′′ ∈
′′ ( ′′) ( ∀ =1 ∈ =1 ∈ > −1) ˅ ≤ −1) ˄ { → availab(Node, ) = 0) KoefsizeR = 0 ( )
SizeR’’ = KoefsizeR × p’ × ∑ ′ =1 ( ) (7) (8) (9) (10) (11)
Next, we consider the criterion of the local database size. This criterion affects both the
performance of queries to the local database and the power of computing resources required to
perform database and CIS administration operations. The database under the relational DBMS control
(including distributed) is presented on disk space as a file or group of files [
the result of a sequence of selecting and projection operations, and is part of the set Rremote. On the
other hand, each DBMS provides information about the amount of disk space required to store the
value of the attribute defined on a particular domain [
However, the values obtained by (9) cannot be used in calculations, because SizeR almost never equals to SizeRdbms. This may be due to the presence of additional data structures (indexes) related to the table, as well as other properties of data representation on the disk. Therefore, for each relation we determine the correction factor where p is the power of R'', n' – is the number of elements of the set Rremote shema (number of attributes), and each attribute Ai ∈ Di ∈Typei.
Fsize = ∑ =1 ′′,
The last of the above criteria is the need for data synchronization. First, we define a subset of the
remote node data for which the data change operations are performed. To do this, define the model of
the SQL-query that modify data Qmodif = <Виміри, R''modif, type>, where R''modif – is a subset on the
relation R, which changes due to data modification operations, type = {insert, update, delete} –
operation type. R''modif is defined as
data require the use of more resource-intensive synchronization algorithms [
will determine the subset of the basic relation on which data conflitct can take place. This where S – is a logical condition, defined in SQL query, F(tup, S) – is a function that reflects its fulfillment for the corresponding tuple, and P modif – is a set of attributes that are modified.
Considering the set of queries to the database, the resulting subset ′′ of the base relation R can be defined as the union of subsets R'' modif of all queries (13) received by the database from the remote node ′′ = ⋃ =1 ′′
.
, which will be modified on the central node or other nodes with future synchronization with the central node. The intersection of the sets ′′ R''modif = {tup[Pmodif] | tup[Pmodif] ∈ R[Pmodif]data ˄ F(tup, S) = true} (13) – the cardinality of all queries, attributes and tuples of which are (12)
та (14) (15)
Based on (14), we add to the multidimensional DB (5) the dimension SyncroFlg = {true, false}, which will be determined on the tuple <R, A, tup>. Next, based on the aggregate value of the representation marker =1 and the representation coefficient perform filtering of the multidimensional DB according to the decision on representation (6) and SyncroFlg = true. Aggregate the results by <R, A, tup> and count the number of queries. The ratio of the obtained value to the total number of queries according to (6) will be an indicator of the level of data synchronization need
Fsynchro =
, – relation power, including queries of the remote node (according to the decision on representation), which includes the values of the tuples attributes (cells), which are also included in where the set R'' modif
node, and represented in the remote node.
A multicriteria problem, that was obtained, must be solved to determine the optimal level of data
representation marker. Classical Pareto and Slater methods [
composition. The main stages of the method are building a hierarchy, estimating the importance and priorities, checking the consistency of priorities and synthesis of the solution.
- stakeholders - criteria - alternatives. The value of the data representation marker (alternative) is a real number in the interval [-1, 1]. It leads to potential large number of alternatives at the 4th level of the hierarchy and therefore the matrices of pairwise comparisons by criteria can become very big.
reducing the number of alternatives to 5: "low" (L) – "-1", "lower them medium" (LM) – "-0.5", "medium" (M) – "0", "higher then medium" (HM) – "0.5", and "high" (H) – "1". The level of "decision makers" is represented by the elements "Owner", "Database Administrator", "Database
Note that the list of criteria differs for the decision makers. Thus, all three criteria are important for the owner (the database size, the need for synchronization and independence from the central database), because they have influence on both the quality of CIS and the cost of equipment. For the database administrator, the criteria of database size and the need to organize data synchronization are important. In turn, for the database developer and CIS operator, the criterion of database size is not critical. It is clear that the relative weight of each of the criteria for different decision makers will also differ. Using the scale of relative importance of the criteria [23] and with the involvement of the decision maker (which at this stage is the owner) we build a matrix of pairwise comparisons for decision makers (Table 1). At the third level of the hierarchy, the corresponding matrices of pairwise comparisons are formed according to the criteria of optimality for each decision maker. Thus, for the decision maker "owner" we have the following matrix of pairwise comparisons of optimality criteria (Table 2).
For the data in Table 2 CI = 3.2%, which indicates the allowable level of consistency (in case the value is higher 10% there is a need to adjust the values of the matrix).
comparisons of alternatives separately for each criterion of optimality, similar to Table 1 and Table 2.
formulated in (8, 12, 15) allows to perform the initial calculation of matrix data based on numerical values of the data representation marker for each alternative. Next, the matrix is submitted to the decision maker for approval. For example, the size of the local node database depending on one of the five alternatives can change as follows (Table 3).
Based on the above data, the size of the database at low (min) and high (max) level of the data representation marker differs by 0.75 / 0.02 = 37.5 times. According to principles of pairwise comparisons and the axiom of homogeneity, we perform normalization of the values given in Table 3, using a slightly modified formula of natural normalization: = ( ( − − ) ) ∗ ( − 1) + 1, where is the value of the optimality criterion for the i-th alternative, and k = 9.
comparisons of alternatives for the criterion of the local database size (Table 5).
= 0,570 0,190 0,095 , 0,081 [0,063] = 0,036 0,082 0,164 , 0,328 [0,328] ℎ = (17) (18) (20) Low
Owner 0,563 0,09 0,09
formalized. Using the definition of selecting and projection operations, as well as taking into account the hierarchical structure of user queries, the model that describes their structure was built. This model includes analytical characteristics and allows to define for each base relation a subset (node relation), which will consist of elements that are part of the resulting sets of SQL-queries sequence.
the offered aggregation function the level of representation marker for each attribute and tuple of relation is calculated. To determine the optimal value of the representation marker, several optimality criteria are introduced and mathematical models are built for each of them. This allow to calculate their values depending on the limit level of the data representation marker at the node of distributed CIS. Solving a multi-criteria problem and finding the optimal level of data representation at a remote node can increase the level of data availability and efficiency of distributed CIS. Efficiency is defined as the ratio of result and resources, so taking into account the vector of relative weight of the optimality criteria of the model (16), we calculate the efficiency as =
х 0
Thus, the results of the research allow to increase the efficiency of using the distributed CIS node of the subject area by 25% compared to the presentation of only critical data, and by 11% compared to the presentation of all necessary data of the central database, respectively. The research can be followed by presenting the obtained vector of global priorities in the form of fuzzy sets of one variable. Dephasing the obtained results can make numerical value of the optimal level of data representation at the RKIS node more accurate.
and communication decision support technologies for strategic decision-making with multiple criteria and uncertainty for military-civilian use” (research project no. 0117U007144, financed by the