JP6840062B2 - Table-related analysis support device and table-related analysis support method - Google Patents

Description

The present invention relates to a table relation analysis support device and a table relation analysis support method, and more particularly to a table relation analysis support device suitable for analyzing a relationship between tables in a relational database, and a table relation analysis support method. ..

When performing system maintenance or replacement development, it is important to understand the relationship between the data used in the target system. A database system has been conventionally used for searching and analyzing data by a computer. In particular, relational databases (relational databases), which are designed and developed based on a relational model, are the mainstream databases. In a relational database, various data are included in a table, and understanding the relationship between the tables leads to understanding the relationship between the data. Generally, in a system with a large number of tables, the relationships between the tables become complicated and difficult to understand. Therefore, if a plurality of tables can be presented together as a set, the relationships that should be understood at one time can be reduced, so that the relationships between the tables can be easily understood, and as a result, the data relationships can be easily grasped. For example, Patent Document 1 discloses a table classification method that classifies tables by hierarchically grouping them and facilitates database integration.

Japanese Unexamined Patent Publication No. 2010-39593

The technique described in Patent Document 1 of the above-mentioned prior art aims at grouping similar tables existing in each system when integrating a plurality of systems using a relational database. The technique described in Patent Document 1 focuses on the order of appearance of columns in the table, the position of appearance, and the number of appearances of similar columns. However, in general, in a relational database, there are no restrictions on the position and order of columns, so this technique may not be used in a directed manner. Further, when a single system is applied, it is rare that many similar columns appear due to normalization or the like, and it is difficult to organize the table by paying attention to the number of columns appearing.

An object of the present invention is to provide a table relationship analysis support device that makes it easy to intuitively grasp the relationship between tables in a relational database by focusing on the relationship of appearance of data.

The configuration of the table relationship analysis support device of the present invention is preferably a table relationship analysis support device that analyzes the relationship of data in a specific column between tables of a relational database, and the relationship of data in the tables is one-to-one. One-to-one output of table-to-table relationship analysis information that defines tables with a many-to-one relationship as one table group and table-to-table relationship analysis information that summarizes tables with a one-to-one relationship as a table group from the table-to-table relationship information. Analysis result that displays the contents of the multi-to-one relational convolution part that updates the table-to-table relationship analysis information and the contents of the table-to-table relationship analysis information by collecting the relational convolution part and the tables that have a many-to-one relationship as a table group. It has a display unit, and the analysis result display unit displays one or more tables of a relational database as a table group in a reduced format.

According to the present invention, it is possible to provide a table relationship analysis support device that makes it easy to intuitively grasp the relationship between tables in a relational database by focusing on the relationship of appearance of data.

It is a figure which shows the functional structure of the table relation analysis support apparatus of Embodiment 1. FIG. It is a figure which shows the hardware software structure of the table relation analysis support apparatus of Embodiment 1. FIG. It is a flowchart which shows the process of the convolution part 201 of a one-to-one relationship. It is a flowchart which shows the process of the convolution part 203 of a many-to-one relationship. It is a diagram showing the relationship between the tables of the relational database of the first embodiment (No. 1). It is a figure which shows an example of the table group relation table of Embodiment 1 (the 1). It is a figure which shows an example of the allocation table (the 1). It is a figure which shows an example of the table group relation table of Embodiment 1 (the 2). It is a diagram showing the relationship between the tables of the relational database of the first embodiment (No. 2). It is a figure which shows an example of the table group relation table of Embodiment 1 (the 3). It is a figure which shows an example of the allocation table of Embodiment 1 (the 2). It is a figure which shows an example of the table group relation table of Embodiment 1 (the fourth). It is a figure which shows an example of the table group relation table of Embodiment 1 (the 5). It is a figure which shows an example of the allocation table of Embodiment 1 (the third). It is a diagram showing the relationship between the tables of the relational database of the first embodiment (No. 3). It is a diagram showing the relationship between the tables of the relational database of the first embodiment (No. 4). It is a figure which shows an example of the allocation table of Embodiment 1 (the fourth). It is a figure which shows an example of the display screen of the analysis result of the table relation analysis support apparatus of Embodiment 1. FIG. It is a figure which shows the functional structure of the table relation analysis support apparatus of Embodiment 2. It is a figure which shows the hardware software structure of the table relation analysis support apparatus of Embodiment 2. It is a flowchart which shows the process of the inter-table relation extraction unit 1901. It is a flowchart which shows the process of the cutting part 1903 of a many-to-many relationship. It is a flowchart which shows the process of the closed circuit division part 1907. It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 1). It is a figure which shows an example of the inter-table relation information of Embodiment 2. It is a figure which shows an example of the relation-to-table relation information which has been disconnected many-to-many relations. It is a figure which shows an example of a many-to-many relation table list. It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 2). It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 3). It is a figure which shows an example of the table group relation table of Embodiment 2 (the 1). It is a figure which shows an example of the allocation table of Embodiment 2 (the 1). It is a figure which shows an example of the allocation table of Embodiment 2 (the 2). It is a figure which shows an example of the table group relation table of Embodiment 2 (the 2). It is a figure which shows an example of the table group relation table of Embodiment 2 (the third). It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 4). It is a figure which shows an example of the allocation table of Embodiment 2 (the third). It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 5). It is a figure which shows an example of the allocation table of Embodiment 2 (the fourth). It is a diagram showing the relationship between the tables of the relational database of the second embodiment (No. 6). It is a figure which shows an example of the display screen of the analysis result of the table relation analysis support apparatus of Embodiment 2 (the 1). It is a figure which shows an example of the display screen of the analysis result of the table relation analysis support apparatus of Embodiment 2 (the second). It is a figure which shows the employee ID table. It is a figure which shows the employee profile table. It is a figure which shows the employee salary table.

Hereinafter, each embodiment of the present invention will be described with reference to FIGS. 1 to 44.

[About table relationships]
First, with reference to FIGS. 42 to 44, the correspondence between the tables, which is the focus of the table relation analysis in the present embodiment, will be described.
42 to 44 are tables illustrated for that purpose.

The basis of a relational database is a table consisting of named columns (vertical columns) and records (horizontal) that fill the values. A plurality of tables are logically connected by a foreign key (FK) that guarantees referential integrity.

The employee ID table 20 shown in FIG. 42 is a table for storing employee IDs. The employee profile table 21 shown in FIG. 43 is a table for storing each employee profile such as a name and an address. The employee profile table 21 shown in FIG. 43 is a table for storing each employee profile such as a name and an address. The employee salary table 22 shown in FIG. 44 is a table for storing the monthly salary of each employee. These three tables are logically linked by an employee ID as a foreign key. For example, it can be seen that the name of the employee with the employee ID "e001" is "Yama OO man" and the salary of the payment month "January 2017" is "253,500 yen".

By the way, paying attention to the appearance of the employee ID data of the column which is the foreign key, the employee ID table 20 and the employee profile table 21 have a one-to-one relationship. In the present embodiment, it is assumed that each table has a "one-to-one relationship".

Further, regarding the appearance of the employee ID data, the employee ID table 20 and the employee salary table 22 have a one-to-many relationship when the employee ID table 20 is regarded as the original table and the employee salary table 22 is regarded as the destination table. There is. In the present embodiment, it is assumed that the employee ID table 20 of the original table and the employee salary table 22 of the destination table have a "one-to-many relationship".

Conversely, if the employee salary table 22 is regarded as the original table and the employee ID table 20 is regarded as the destination table, there is a many-to-one relationship. In this case, the employee salary table 22 of the original table and the destination table It is assumed that the employee ID table 20 of the table has a "many-to-one relationship".

Furthermore, although not specifically shown, when the data in the columns of the foreign keys of the two tables is many-to-many, the two tables are said to have a "many-to-many relationship".

[Embodiment 1]
Hereinafter, the first embodiment of the present invention will be described with reference to FIGS. 1 to 18.
First, the configuration of the table-related analysis support device of the first embodiment will be described with reference to FIGS. 1 and 2.

The table-related analysis support device 100 includes a one-to-one relationship convolution unit 201, a many-to-one relationship convolution unit 203, and each functional unit of the analysis result display unit 204.

The one-to-one relationship convolution unit 201 inputs the table-to-table relationship information 200 and outputs the table-to-table relationship analysis information 202. The table-to-table relationship information 200 is information on the correspondence between tables in a relational database. The table-to-table relationship analysis information 202 is information that summarizes the tables having a corresponding relationship as a table group based on the table-to-table relationship information. Specific examples of the table-to-table relationship information 200 and the table-to-table relationship analysis information 202 will be described later.

The convolution unit 203 of the many-to-one relationship receives the table-to-table relationship analysis information 202 as input, and updates the table-to-table relationship analysis information 202 by collecting the tables having the many-to-one relationship as a table group.

The analysis result display unit 204 displays the table-to-table relationship information 200 and the table-to-table relationship analysis information 202 on a display device 120 such as a display in a format that can be visually recognized by the user.

The table-related analysis support device 100 is composed of a general information processing device, and as shown in FIG. 1, a CPU (Central Processing Unit) 110, a display device 120, a main memory 130, an input device 140, and the like. It has an external storage device 150 and a network communication device 160.

The CPU 110, the display device 120, the main memory 130, the input device 140, the external storage device 150, and the network communication device 160 are connected via the bus 170, and data can be transmitted and received.
The CPU 110 executes programs and data loaded from the external storage device 150 into the main memory 130, and controls each part of the table-related analysis support device 100.

The external storage device 150 is a large-capacity storage device such as an HDD (Hard Disk Drive) or an SDD (Solid State Drive). The table relationship analysis support program 151 is installed in the external storage device 150, and the table-to-table relationship information 200 and the table-to-table relationship analysis information 202 are stored.

The table-related analysis support program 151 includes a one-to-one relationship convolution module 151a, a many-to-one relationship convolution module 151b, and an analysis result display module 151c as subprograms.

The one-to-one convolution module 151a, the many-to-one convolution module 151b, and the analysis result display module 151c are each of the one-to-one convolution unit 201, the many-to-one convolution unit 203, and the analysis result display unit 204, respectively. It is a module that executes the functions of the functional part. The CPU 110 executes these modules loaded from the external storage device 150 into the main memory 130.

In the first embodiment, as shown in FIG. 1, the table-related analysis support device 100 has a one-to-one relationship convolution unit 201, a many-to-one relationship convolution unit 203, and an analysis result display unit 204 in this order. To execute the process.

The one-to-one relationship convolution unit 201 inputs the table-to-table relationship information 200 and outputs the table-to-table relationship analysis information 202. The table-to-table relationship information 200 includes at least cardinality information between tables in a relational database. As illustrated at the beginning, the cardinality information is information that shows the connection of the data of the columns of interest in each table, and at least the source table that is the base point, the destination table that is the target, and the one-to-one relationship. Includes relationships represented by one-to-many relationships, many-to-one relationships, and many-to-many relationships. The inter-table relationship analysis information 202 includes an inter-table relationship table 320 and an allocation table 321. The table-group relationship table 320 is a table showing the table relationships, and is a table including at least a source table group 601 as a base point, a target destination table group 602, and a relationship 603 of the table group (specific example). Will be described later). A table group is a collection of one or more tables or a group of tables, and one of them is a representative table group.

In this embodiment, various naming conventions are used to identify the table groups, but if each table group can be identified, it is not necessary to be particular about this naming convention. The allocation table 321 is a calculation table for organizing the tables as a table group, and is a table including at least an ID 700 for identifying each information, a table group 701, and an allocation number 702 (specific examples will be described later).

The convolution unit 203 of the many-to-one relationship updates the table-to-table relationship analysis information 202 with the tables of the many-to-one relationship as a table group.

The analysis result display unit 204 edits the table-to-table relationship information 200 and the analyzed table-to-table relationship analysis information 202 and graphically outputs them to the display device 120 (specific examples of the display will be described later).

Next, each process in the table-related analysis support device will be described with reference to FIGS. 3 and 4.
In the first embodiment of the table-related analysis support device 100, each functional unit is called in the order of the one-to-one relationship convolution unit 201, the many-to-one relationship convolution unit 203, and the analysis result display unit 204.

First, the process of the one-to-one convolution section 201 will be described with reference to FIG.
First, the one-to-one relationship convolution unit 201 acquires the table-to-table relationship information 200 as input, regards each table as a table group consisting of one table, and creates a table-group relationship table 320 (S301).

Next, all the table groups included in the table group relationship table 320 are added to the empty allocation table 321 (S302). At this time, the allocation number 702 is made so that the unallocated state can be known (in this embodiment, it is left blank. A specific example will be described later).

Next, using the table group relationship table 320 as an input, a set of table groups having a one-to-one relationship between the table groups is acquired (S303).
Hereinafter, ☆: S304 to S306 are repeated for all the table groups included in the set of table groups acquired in S303.

For each table group Ti having a one-to-one relationship included in the acquired set of table groups (S304), all the table groups reachable only by the one-to-one relationship are acquired from Ti, and a new table group is created (S305). A known algorithm can be adopted as an algorithm for acquiring a table group that can be reached only by a one-to-one relationship from the table group of interest.

Next, the overlapping table group is removed from the plurality of new table groups created in S305 (S307).

Next, the table group relationship table 320 and the new table group remaining after S307 are used as inputs, and the table group relationship table 320 is updated (S308). In S308, among the original table group 601 and the destination table group 602 of the inter-table relationship table 320, the table group including the table included in the new table group remaining after S307 is replaced with the new table group remaining after S307 including the table. Replace with a group of tables.

Next, in the table group relationship table 320, the original table group 601 and the destination table group 602 delete the same row, and the table group relationship table 320 is updated (S309).
Hereinafter, ★: S310 to S313 are repeated for the new table group remaining after S307.

First, for each created table group G (S310), the created table group G is added to the allocation table 321 after numbering the ID 700 (S311). Then, with respect to the table group included in the table group of interest in S310, the ID 700 numbered in S311 is described in the allocation number 702 of the allocation table 321 to update the allocation table 321 (S312).

Next, the processing of the convolutionary portion 203 having a many-to-one relationship will be described with reference to FIG.
Hereinafter, while there is one-to-many in the relationship 603 of the table-group relationship table 320, ◆: S401 to S412 are repeated.

Relationship between table groups The table group included in the original table group 601 is acquired from the table 320 (S402).
Hereinafter, ☆: S403 to S411 are repeated for the table group Ts acquired in S402.

From the table group relationship table 320, a row in which the table group acquired in S403 is the original table group 601 is acquired, and it is determined whether all the relationships 603 are one-to-one or one-to-many (S404).

If all relationships 603 are one-to-one or one-to-many (S404: YES), then S405 is executed, otherwise (S404: NO), S411 is executed.
Hereinafter, ★: S405 to S410 are repeated for the destination table group 602Td of the row in which the original table group 601 of the table group relationship table 320 is the table group Ts acquired in S403.

First, the table-group relationship table 320 is updated (S406). In this S406, the table group Td acquired in S405 is used as the representative table group, a new table group including the table group Ts acquired in S403 is created, and the original table group 601 and the destination table group 602 of the inter-table relationship table 320 are created. Among the table groups included in, the table group focused on in S405 is replaced with a new table group G.

Next, from the table group relationship table 320, the row in which the original table group 601 is the table group Ts acquired in S403 and the row in which the original table group 601 and the destination table group 602 have the same table group are deleted, and the table group is deleted. The relationship table 320 is updated (S407).

Next, the table group G newly created in S406 is added to the allocation table 321 after numbering the ID 700, and the allocation table 321 is updated (S408).

Next, for the table group included in the table group G newly created in S406, the ID 700 numbered in S408 is added to the allocation number 702 in the allocation table 321 to update the allocation table 321 (S409).

Next, the operation of the table relation analysis support device 100 based on a specific example will be described with reference to FIGS. 5 to 18.

Hereinafter, the operation until the table relationship analysis support device 100 analyzes the relationship between the tables having the corresponding relationship as shown in the diagram of FIG. 5 and displays it in a format that is easy for the user to see will be described.

The table will be represented by a square figure with lines emphasized on the left and top, such as tables A500 to G506. The line segment indicates that the relationship between the tables and the table group is one-to-one, and the arrow indicates that the relationship between the tables and the table group is many-to-one. Here, the base of the arrow is "many" and the base of the arrow is "one". That is, the many-to-one relationship is a relationship in which, for a specific column, "a plurality of related data exist in the table on the original side of the arrow for a certain data included in the table on the front side of the arrow". It shows that.

First, the operation of the one-to-one convolution unit 201 in this example will be described with reference to FIGS. 6 to 10.

The table relationship shown in FIG. 5 is expressed as in the table group relationship table 320 of FIG. The table group relationship table 320 includes columns ID 600, a source table group 601 and a destination table group 602, and a relationship 603. The table-group relationship table 320 is referred to in S301 of FIG.

Relationship between table groups ID 600 in Table 320 is a number that identifies each row. The original table group 601 represents a table group that serves as a base point of the relationship 603. Further, the destination table group 602 represents a table group that is the target of the relationship 603. Here, the name of the column is a table group, but the table can also be regarded as one table group and described in this column. The relationship 603 represents a relationship with the destination table group 602 as viewed from the source table group 601, that is, any one-to-one, one-to-many, many-to-one, or many-to-many relationship.

The allocation table 321 is a work table used to organize the table relations as a table group, and as shown in FIG. 7, consists of columns ID 700, table group 701, and allocation number 702.

ID700 is a line number that identifies each line. The table group 701 represents a table group (or a table) for grouping. The allocation number 702 indicates whether or not the table group of the row is included in the other table group 701 (allocated), and if it is included in the other table group 701, the allocation number 702 ID 700 of the table group 701 including the table group 701 is described in the above. In addition, one table group (or table) may be included in a plurality of table groups 701. At the time of the first S302, the value stored in the table group 701 is the table related to the table shown in FIG. 5, and the allocation number 702 is blank.

In S303, rows of ID 600 having a one-to-one relationship 603 and ID 600 of "1", "2", "7", and "8" are acquired from the table group relationship table 320. After that, S304 to S306 are repeated for the table A, the table B, the table D, and the table F. In S305, for example, when table A is targeted, table B corresponds to a table group (table) that can be reached only by a one-to-one relationship, so a new table group AB is created. Here, the table groups T1-T2 represent a table group including tables T1 and T2 having a one-to-one relationship with each other.

In the present embodiment, the representative table group of the new table groups AB includes both the table A and the table B, but the table group including only one of them may be used as the representative table. For example, when the table A and the table B are compared, since it is the table group B that has the relationship 603 with other than the table groups AB, the table group including only the table B may be used as the representative table group. By repeating S304 to S306, two table groups AB and two table groups DF are obtained as new table groups. Then, in S307, duplication is eliminated, and table groups AB and table groups DF are obtained one by one. In S308, among the table groups included in the original table group 601 and the destination table group 602 of the inter-table group relationship table 320, the table groups included in the newly created table groups AB and the table groups DF. The table group A and the table group B are replaced with the table group AB, and the table group D and the table group F are replaced with the table group DF.

The table-group relationship table 320 after the implementation of S308 is shown in FIG. In S309, the rows with strikethroughs in the table-group relationship table 320 of FIG. 8 are deleted.

S310 to S313 are repeated for the table groups AB and the table groups DF newly created in S305. In S311 the table group focused on in S310 is added to the allocation table 321 after numbering the ID 700. In S312, the allocation number 702 in the allocation table 321 is updated. The table group 701 to be updated is the table A, the table B, the table D, and the table F included in the table group AB and the table group DF newly created in S305. ID700 of the table group AB and the table group DF.

The relationship between the tables when the one-to-one convolution unit 201 is completed in this example is shown in FIG. The square missing square newly introduced in FIG. 9 is a symbol representing a table group. The table group A-B900 and the table group D-F901 are the table groups newly created in S305.

Further, the table group relationship table 320 at the time of completion of the one-to-one relationship convolution unit 201 in this example is shown in FIG. In this table group relationship table 320, the column of the value of the table A and the table B and the column of the value of the table D and the table F disappear, and the table group AB and the table group DF newly appear. There is. The bold underline in FIG. 10 indicates that there is a one-to-many relationship and that there is a reflexive many-to-one relationship entry. For example, an entry with ID 6 and an entry with ID 5 and an entry with ID 9 and an entry with ID 10 have a reflexive relationship.

Further, the allocation table 321 at the time of completion of the one-to-one convolution unit 201 in this example is shown in FIG. Compared with FIG. 7, the table group AB (ID700 is “8”) and the table group DF (ID700 is “9”) are increased, and the table A (ID700 is “1”) and the table B (ID700 is “1”). "8" is described in the allocation number 702 of "2"), and "9" is described in the allocation number 702 of the table D (ID700 is "4") and the table F (ID700 is "6"). ing.

Next, the operation of the multi-to-one convolution unit 203 in this example will be described with reference to FIGS. 12 to 17.
The table group relationship table 320 at the start of the many-to-one convolution unit 203 is FIG. 10 when the one-to-one convolution unit 201 is completed, and the allocation table 321 is FIG. 11.

Relationship between table groups S401 to S412 are repeated until there is no one-to-many relationship in the relationship 603 of Table 320. The table-group relationship table of FIG. 10 includes a one-to-many relationship (bold underlined in FIG. 10).

In the process of S402, the table group relationship table 320 is input, and the table group included in the original table group 601 is acquired. In this example, the table groups AB, the table C, the table groups DF, the table E, and the table G are acquired. Then, S403 to S411 are repeated for the table group acquired in S402. In the process of S404, all the relationships in which the original table group 601 of the table group relationship table 320 is the table group acquired in S403 are acquired, and it is evaluated whether all of them are one-to-one or one-to-many relationships. In this example, the table groups DF and the table G both have only a one-to-many relationship. Relationship between table groups S405 to S410 are repeated for the destination table group 602 for the row in which the original table group 601 of the table 320 is the table group DF. In this example, table group C and table group E are applicable. When the table group C is targeted, in the process of S406, the table C is set as the representative table group, and the table group C: DF including the table group DF is created. Here, TA: TB is assumed to represent a table group in which a table group (or table) TA and a table group (or table) TB having a many-to-one relationship are put together.

Further, the table group C of the original table group 601 and the destination table group 602 in the table group relationship table 320 is replaced with the table group C: DF. When the table group E is targeted, in S406, the table group E is set as the representative table group, and the table groups E: DF and G including the table groups DF and the table group G are created. Here, with respect to the table group (or table) TA, the table group (or table) TB, and the table group (or table) TC, the notation of TA: TB and TC is a notation that summarizes TA: TB and TA: TC. Is.
Further, the table group E of the original table group 601 and the destination table group 602 in the table group relationship table 320 is replaced with the table groups E: DF and G.

The table group relationship table 320 to be deleted in the process of S407 is shown in FIG. In S407, from the table group relationship table 320, the original table group 601 is the row of the table group DF or the table group G (cancellation line in FIG. 12), and the original table group 601 and the destination table group 602 are the same table group. Delete the line with (bold underline in FIG. 12). In the process of S408, the table group (table group C: DF and table group E: DF, G) created in S406 is added to the allocation table 321 after numbering the ID 700. In the case of table group C: DF, in the processing of S409, "10" is added to the allocation number 702 of the table group DF (ID700 is "9"), and the table group C (ID700 is "3") is assigned. Add "10" to number 702. Further, in the case of the table group E: DF, G, in S409, "11" is added to the allocation number 702 of the table group DF (ID700 is "9"), and the table group E (ID700 is "5"). Add "11" to the allocation number 702 of.

The table group relationship table 320 after completing S401 to S412 once is shown in FIG.

Next, the allocation table 321 after completing S401 to S412 once is shown in FIG.

Further, the relationship between the tables after completing S401 to S412 once is shown in FIG. In the illustration, the table group in which the allocation number 702 of the allocation table 321 is blank is displayed, and the relationship 603 of the table 320 is used to draw a line representing the table and the relationship between the table groups. The dotted square 1500 in FIG. 15 represents the same table or table group sharing the same table group. Here, the table group C: DF (1501) and the table groups E: DF, G (1502) share the table group DF.

In the process shown in FIG. 4, when S401 to S412 are completed once, the process returns to S401 and enters the second loop.

The relationship between the tables after completing S401 to S412 twice is shown in FIG.

Further, the allocation table 321 at this time is shown in FIG. When S401 to S412 are completed twice, the table group relationship table 320 is empty with all rows deleted.

After that, the process returns to S401, but since the table group relationship table 320 is empty and there is no one-to-one or one-to-many relationship, the process moves to S413 and the processing of the convolution unit 203 of the many-to-one relationship is completed.

Next, a display screen of the analysis result of the table-related analysis support device will be described with reference to FIG.
The screen 1800 when not selected is shown in FIG. 18 (a).
The unselected screen 1800 shown in FIG. 18A displays the table-to-table relationship information 200 and the table-to-table relationship analysis information 202 as initial states after the analysis is completed in the above example.

On the analysis result display screen, one or more displayed tables can be selected. For example, when the table groups AB: C and DF (1601) are selected, they are displayed as shown in the screen 1801 when AB: C and DF are selected as shown in FIG. 18 (b). When the table group E: DF, G (1602) is selected, the screen 1802 when E: DF, G is selected as shown in FIG. 18C is displayed.

AB: The display contents will be described with an example in which C and DF are selected. The selected table groups AB: C and DF include tables A500 to D503 and table F505. These tables are displayed, and a line indicating the relationship between the tables is drawn based on the table-to-table relationship information 200. It also displays table groups other than the selected table group. Furthermore, if each displayed table is included in other table groups, a dotted line indicating the table sharing relationship is drawn between the table and the table group. In addition, the tables included in the table groups AB, which are the representative table groups of the selected table groups AB: C and DF, may be displayed so that they can be seen (bold underlined in the figure). This is an effective operation when, for example, the structure of the table A is investigated by paying attention to it.

In addition, the analysis result display unit 204 may be able to create a new table group, divide an existing table group, or the like by inputting user operations or information between tables, or the user's. It may have a function of inputting operation and table information and displaying the table and the table group including the table so as to be distinguished. As an example of the former, in order to divide an existing table group, there is a method of inputting information on the time-series update order of tables and excluding tables having different update timings from the table group. As an example of the latter, by inputting the information of the master table / transaction table of the table and displaying the master table in a different color or cutting it out of the group group and displaying it, there is little change in the data and it is understood. There is a method to understand the relationship with the transaction table after first understanding the information of the master table, which is easy to use.

Further, the analysis result display unit 204 may have a function of displaying the execution progress of each function, such as a one-to-one relationship convolution unit 201 and a many-to-one relationship convolution unit 203. For example, on the screen 1801 when AB: C, DF is selected shown in FIG. 18B, as a result after executing the folding unit 201 having a one-to-one relationship, the table A and the table B, and the table D and the table F are displayed. In summary, they may be displayed as table groups AB and table groups DF, which makes it easier to understand because the tables and table groups to be understood can be narrowed down. In addition, the analysis result display unit 204 may have a function of inputting a relationship between tables other than cardinality information and displaying the relationship so that the relationship can be understood. For example, by inputting the overlap ratio of data corresponding to FK between tables and changing the thickness of the line representing the relationship 603 according to the ratio, it is easy to understand the equivalent / subordinate relationship between the two tables. become.

The method described in this embodiment does not operate correctly when there is a many-to-many relationship between the tables or when there is a cycle configured by the many-to-one relationship. However, in relational databases, it is common to normalize so that there is no many-to-many relationship. Similarly, a cycle formed by a many-to-one relationship can be said to be sufficiently practical because it is often eliminated by normalization.

As described above, in the present embodiment, the table relationship of the relational database is analyzed from the viewpoint of whether there is a one-to-one or one-to-many relationship, and the screen as shown in FIG. 18A is displayed. Then, the user pays attention and clicks the icon of the table group including the table whose structure is to be examined, so that the structure is expanded in detail as shown in FIGS. 18 (b) and 18 (c). You can see the screen. This is effective because the structure of a database having a complicated table relationship can be quickly analyzed, especially in a large-scale database, and it is easy to grasp intuitively.

As described above, in the present embodiment, paying attention to the fact that it is difficult to understand the relationship if there are a plurality of data related to the data focused on for understanding the relationship of the data, the pair of data included in the table. By creating a set (table group) of tables that have a one-to-one / many-to-one relationship among one-to-many, many-to-one, and many-to-many relationships, the relationship of related data is displayed in an easy-to-understand manner. This makes it easier to understand the relationships within the table group.

In addition, by creating a table group, the relationships that should be focused on at one time can be reduced, so that the structure of the database, that is, the table and the table group, and the relationship between the table groups can be easily grasped.

[Embodiment 2]
Hereinafter, Embodiment 2 of the present invention will be described with reference to FIGS. 20 to 41.
The table relationship analysis support method described in the first embodiment is a method applied when there is a one-to-one, one-to-many, or many-to-one relationship between tables, and when there is a many-to-many relationship or many. I did not mention the case where there is a cycle composed of one-to-one relationships.

This embodiment is applied to the case where the relationship between the tables has a many-to-many relationship in addition to the pattern of the first embodiment or a closed circuit configured by the many-to-one relationship.

Further, in the first embodiment, the information regarding the relationship between the tables shown in FIG. 5 is described as being given from the outside, but in the present embodiment, the function of obtaining the relationship between the tables from the table of the database to be analyzed. Will also be described.

In this embodiment, the points different from those in the first embodiment will be mainly described.

First, the configuration of the table-related analysis support device of the second embodiment will be described with reference to FIGS. 19 and 20.

In addition to the functional configuration of the first embodiment shown in FIG. 1, the table-relationship analysis support device 100 of the present embodiment has a table-to-table relationship extraction unit 1901, a many-to-many relationship cutting unit 1903, and a closed circuit division unit. There is 1907.

The table-to-table relationship extraction unit 1901 takes the FK information 1902 as an input, analyzes the analysis target database 1900, and outputs the table-to-table relationship information 200. The FK information 1902 includes at least the information of the foreign key between the tables included in the analysis target database 1900. The analysis target database 1900 is assumed to be a relational database, but if it is a set of data that can define a constraint similar to a foreign key, it can be handled by the table relation analysis support device of the present embodiment. The many-to-many relationship cutting unit 1903 inputs the table-to-table relationship information 200, and outputs the many-to-many relationship table list 1904 and the many-to-many relationship disconnected table-to-table relationship information 1905. The many-to-many relationship table list 1904 records at least a set of tables having a many-to-many relationship out of the table-to-table relationship information 200. The many-to-many relationship disconnected table-to-table relationship information 1905 is obtained by removing the rows of the many-to-many relationship 603 from the table-to-table relationship information 200. The inter-table relationship analysis information 1906 is obtained by adding a closed circuit sharing number (described later) to the allocation table 321 of the inter-table relationship analysis information 202. The closed circuit dividing unit 1907 removes the closed circuit composed of the many-to-one relationship included in the table-to-table relationship analysis information 1906, and updates the table-to-table relationship analysis information 1906. The analysis result display unit 1908 has, in addition to the analysis result display unit 204 of the first embodiment, a function of displaying so that the many-to-many relationship can be understood.

In addition to the table-relationship analysis support device 100 of the first embodiment, the table-relationship analysis support device 100 of the present embodiment includes an external storage device 150, an inter-table relationship extraction module 151d, and a many-to-many relationship cutting module 151e. The closed circuit division module 151f is installed. The inter-table relationship extraction module 151d, the many-to-many relationship cutting module 151e, and the closed circuit dividing module 151f are modules that execute the functions of the inter-table relationship extracting unit 1901, the many-to-many relationship cutting unit 1903, and the closed circuit dividing unit 1907, respectively. is there. Similar to the first embodiment, the table-related analysis support device 100 executes these modules loaded from the external storage device 150 into the main memory 130 by the CPU 110.

Further, the table-relationship analysis support device 100 of the present embodiment, in addition to the table-relationship analysis support device 100 of the first embodiment, stores the analysis target database 1900, FK information 1902, and many-to-many relationship as data in the external storage device 150. The table list 1904 and the many-to-many relationship disconnected table-to-table relationship information 1905 are stored.

Next, the process of the inter-table relationship extraction unit 1901 will be described with reference to FIG.
The inter-table relationship extraction unit 1901 repeats ☆ ★: S2001 to S2015 in a round-robin manner for all pairs of all tables included in the analysis target database 1900. Hereinafter, the table focused on in S2001 in the loop of ☆ will be referred to as table X, and the table focused in S2002 in the loop of ★ will be referred to as table Y.

First, the foreign key defined between the table X and the table Y is acquired from the FK information 1902 (S2003). Here, the foreign key on the table X side is referred to as FKx, and the foreign key on the table Y side is referred to as FKy.

Next, all the FKx data is acquired from the analysis target database 1900, duplication is eliminated, and a unique set of data DX is obtained (S2004). Then, 〇: S2005 to S2007 are repeated for all the unique data DX acquired in S2004.

First, the number of FKy data corresponding to the data of interest in S2005 is acquired, the number of the data is determined (S2006), and if the number is less than 2 (S2006: less than 2), go to S2007 and 2 or more. In the case of (S2006: 2 or more), the process proceeds to S2011.

Next, it is determined whether or not there is duplication in the FXx data (S2008), and if there is no duplication (S2008: YES), the process proceeds to S2009, and if there is a duplication (S2008: NO), the process proceeds to S2010.

Next, it is determined whether or not there is duplication in the FXx data (S2011), and if there is no duplication (S2011: YES), the process proceeds to S2012, and if there is a duplication (S2011: NO), the process proceeds to S2013.

When S2008 is YES, a row having an original table of X, a destination table of Y, and a relationship of one-to-one is added to the output table-to-table relationship information 200 (S2009).

When S2008 is NO, a row in which the source table is X, the destination table is Y, and the relationship is many-to-one is added to the output table-to-table relationship information 200 (S2010).

When S2011 is YES, a row having an original table of X, a destination table of Y, and a relationship of one-to-many is added to the output table-to-table relationship information 200 (S2012).

When S2011 is NO, a row with an original table of X, a destination table of Y, and a many-to-many relationship is added to the output table-to-table relationship information 200 (S2013).

Then, when the loop of ☆ ★: S2001 to S2015 is exited, the table-to-table relationship extraction unit 1901 outputs the table-to-table relationship information 200 (S2016).

Next, the processing of the many-to-many relationship cutting portion 1903 will be described with reference to FIG.
First, the many-to-many relationship cutting unit 1903 extracts and deletes a row having a many-to-many relationship from the table-to-table relationship information 200 (S2101).

Next, the source table and the destination table of the rows extracted in S2101 are added to the many-to-many relationship table list 1904 (S2102).

Next, the process of the closed circuit dividing portion 1907 will be described with reference to FIG. 23.
First, from the table group relationship table 320, a cycle that is composed of a many-to-one relationship and does not include two or more identical table groups is extracted (S2201). Since there is a known algorithm for extracting a cycle, it can be used.

Next, all the table groups constituting the closed cycle acquired in S2201 are acquired (S2202). With respect to the table group Tc acquired in S2202, ☆: S2203 to S2208 are repeated.

First, from the cycles acquired in S2201, the cycles including the table group Tc focused in S2203 are extracted, and each cycle is used as a new table group (S2204).

Next, the table group Tc acquired in S2202 included in the original table group 601 and the destination table group 602 in the table group relationship table 320 is replaced with a new table group created in S2204 (S2205).

Next, when the new table group created in S2204 is not included in the allocation table 2212, the ID 700 is numbered and added (S2206).

Next, with respect to the table group included in the cycle, the ID 700 numbered in S2206 of the corresponding table group is described in the allocation number 702 of the allocation table 2212 if there is no duplication (S2207).

Next, for all the table groups consisting of closed circuits, the ID 700 of each other's table group 701 is described in the closed circuit sharing number 3000 of the table group 701 including the same table (S2209).

Next, from the table group relationship table 320, a row in which one or more table groups 701 representing the same cycle are included in the original table group 601 and the destination table group 602 is deleted (S2210). In this embodiment, it is not described to support the analysis of the structure in the cycle, but by cutting any relationship 603 in the cycle, the convolution part in the many-to-one relationship also in the cycle. With 203 and the like, it becomes possible to analyze the structure. At this time, as the cutting position, for example, for a table having only a one-to-many relationship, by cutting the one-to-many relationship constituting the closed cycle, it becomes easy to grasp the relationship between the table inside the cycle and the table outside the cycle.

Next, the operation of the table relation analysis support device 100 based on a specific example will be described with reference to FIGS. 24 to 40.
In this embodiment, the relationship between the tables of the relational database represented by the diagram shown in FIG. 24 will be taken as an example.

Similar to the first embodiment, the table A2300 to the table M2312 represent a table, the line indicates that the relationship between the tables and the table groups is one-to-one, and the one-sided arrow indicates that the relationship between the tables and the table groups is many-to-one. It represents that. In the present invention, as a novel notation, double-headed arrows indicate that there are many-to-many relationships between tables and table groups.

In the example taken up in the second embodiment, the table A, the table C, the table D, the table B, the table A, the table G, the table H, the table I, and the table B form a closed path, and the table K Note that the table L has a many-to-many relationship.

Hereinafter, the operation of the many-to-many relationship cutting portion 1903 will be described with reference to FIGS. 25 to 28.
The relationship between the tables shown in the diagram of FIG. 24 is represented by the table-to-table relationship information 200 of FIG. In S2101 of FIG. 22, rows having a many-to-many relationship (ID2400 is “25” and “26”) are deleted from the table-to-table relationship information 200. In S2102, the original table 2401 and the destination table 2402 of the row deleted in S2101, the table K and the table L, and the table L and the table K are added to the many-to-many relationship table list 1904.

The many-to-many relationship disconnected table-to-table relationship information 1905 after the completion of the many-to-many relationship cutting section 1903 is shown in FIG.

The many-to-many relationship table list 1904 after the completion of the many-to-many relationship cutting section 1903 is shown in FIG. 27.

A diagram of the relationship between the tables after the many-to-many cutting section 1903 is completed is as shown in FIG.

Next, the operation of the closed circuit dividing portion 1907 will be described with reference to FIGS. 29 to 35.
After the completion of the many-to-many cutting section 1903, the one-to-one convolution section 201 is further applied, and the table and the relationship between the table groups that are the inputs of the closed circuit dividing section 1907 are illustrated, as shown in FIG. Become. Here, the table L and the table M are grouped together as a table group LM.

Next, the table group relationship table 320, which is the input of the closed circuit dividing unit 1907, is shown in FIG. This is a table having information equivalent to the table-related information in FIG. 26.

Next, when the allocation table 2212, which is the input of the closed circuit dividing portion 1907, is shown, FIG. 30 is shown in FIG. 31.

In S2201 of FIG. 23, the closed cycles A * C * D * B and the closed paths A * G * H * I * B, which are closed paths composed of a many-to-one relationship, included in the table group relationship table 320 are extracted. Here, with respect to the table T1, the table T2, ..., The table Tn, T1 * T2 * ... * Tn means that the table T1, the table T2, ..., The table Tn form a cycle on the diagram.

Next, in S2202, the table A, the table B, the table C, the table D, the table H, the table I, and the table G, which constitute the closed circuit acquired in S2201, are acquired. S2203 to S2208 are repeated for the table acquired in S2202. When focusing on the table group A in S2203, in S2204, for the closed circuit A * C * D * B and the closed circuit A * G * H * I * B, the table group A * C * D * B and the table group A * G * Create H * I * B.

In S2205, according to the table group A * C * D * B, the table A, the table C, the table D, and the table B included in the original table group 601 and the destination table group 602 of the table group relationship table 320 are all table group A. Replaced with * C * D * B, and by table group A * G * H * I * B, table A, table G, table H, which are included in the original table group 601 and the destination table group 602 of the table group relationship table 320. Table I and table B are replaced with the table group A * G * H * I * B. Since the table group A and the table group B are included in both cycles, the table group A * C * D * B and the table group A * G * H * I * B are described together.

Next, in S2206, after numbering the ID700, the table group A * C * D * B and the table group A * G * H * I * B are added to the allocation table 2212.

Next, in S2207, the table group A * C * D * B describes "15", which is the ID 700 numbered in S2205, in the allocation number 702 of the table A, the table C, the table D, and the table B, and the table. According to the group A * G * H * I * B, "16", which is the ID 700 numbered in S2205, is described in the allocation number 702 of the table A, the table G, the table H, the table I, and the table B. Since the table group A and the table group B are included in both cycles, the ID 700s of both the table group A * C * D * B and the table group A * G * H * I * B are described together.

Next, in S2209, since there are a table group A * C * D * B representing a closed circuit and a table group shared by the table group A * G * H * I * B, a table group A and a table group B, the table group “16”, which is the ID 700 of the table group A * G * H * I * B, is described in the closed circuit sharing number 3000 of the A * C * D * B, and similarly, the table group A * G * H * I * B of the table group A * G * H * I * B. The ID 700 of the table group A * C * D * B, "15", is described in the closed circuit sharing number 3000.

Next, in S2210, in the table group relationship table 320, a row containing one or more same table groups in the original table group 601 and the destination table group 602 is deleted.

The table 320 between the table groups before the implementation of S2210 is shown in FIG. 33, and the row with the strikethrough is the target of deletion.

The table group relationship table 320 after the implementation of the closed circuit dividing portion 1907 is shown in FIG. 34.
Then, the relationship between the tables after the closed circuit dividing portion 1907 is implemented is shown in FIG. 35.

After the closed circuit dividing portion 1907 is implemented, the convolution portion 203 having a many-to-one relationship is implemented.
FIG. 36 shows the allocation table 2212 at the time when S401 to S412 are completed once in the convolution unit 203 having a many-to-one relationship. Further, in the convolution unit 203 having a many-to-one relationship, the relationship between the table and the table group at the time when S401 to S412 are completed once is shown in the diagram of FIG. 37.

Next, FIG. 38 shows the allocation table 2212 at the time when S401 to S412 are completed twice in the convolution unit 203 having a many-to-one relationship. Further, in the convolution unit 203 having a many-to-one relationship, the relationship between the table and the table group at the time when S401 to S412 are completed twice is shown in the diagram of FIG. 39. Here, for the table group (or table) TA, the table group (or table) TB, and the table group (or table) TC, the symbols TA: TB: TC are TA: TB and TB: TC. means.

At this point, the table 320 has no one-to-one or one-to-many rows of relations 603, and the processing of the convolution unit 203 having a many-to-one relation is completed.

Next, the display screen of the analysis result of the table-related analysis support device of the second embodiment will be described with reference to FIG. 40.
An example of a display screen of the analysis result of the table-related analysis support device of the second embodiment is shown in FIG. 40.

In table group E: A * C * D * B (3800), table group E and table group A * C * D * B have a many-to-one relationship, and table A, table C, table D, and table B are closed. It shows that it does. Similarly, the table group K: A * G * H * I * B: J (3801) is the table group K :, the table group A * G * H * I * B, and the table group A * G * H * I *. It is shown that B and table J have a many-to-one relationship, and that table A, table G, table H, table I, and table B form a closed path.

Further, it shows that the table group K: A * G * H * I * B: J (3801) and the table group LM (2800) have a many-to-many relationship.

Further, the table group E: A * C * D * B (3800) and the table group K: A * G * H * I * B: J (3801) share the table A and the table B.

Here, when the table group E: A * C * D * B (3800) is selected, the structure of the table group is expanded and displayed as shown in FIG. 41.

In the present embodiment, in addition to the first embodiment, when the table relationship is closed on the diagram, even if the table has a many-to-many relationship, it can be analyzed and displayed in a form that is easy for the user to see. Therefore, it is effective for analyzing a database having a complicated table structure.

100 ... Table-related analysis support device 110 ... CPU
120 ... Display device 130 ... Main memory 140 ... Input device 150 ... External storage device 160 ... Network communication device 170 ... Bus 201 ... One-to-one relationship folding unit 202 ... Table-to-table relationship analysis information 203 ... Many-to-one relationship folding unit 204 ... Analysis result display unit 1900 ... Analysis target database 1901 ... Table-to-table relationship extraction unit 1902 ... FK information 1903 ... Many-to-many relationship disconnection unit 1904 ... Many-to-many relationship table list 1905 ... Many-to-many relationship disconnected table-to-table relationship information 1906 ... Table-to-table relationship analysis information 1907 ... Closed circuit division

Claims (9)

A table relationship analysis support device that analyzes the relationship of data in a specific column between tables in a relational database.
Table-to-table relationship analysis information that defines tables with one-to-one and many-to-one relationships as one table group,
From the table-to-table relationship information, a one-to-one relationship convolution unit that outputs the table-to-table relationship analysis information that summarizes the tables that have a one-to-one relationship as a table group.
A convolution unit of a many-to-one relationship that updates the inter-table relationship analysis information by grouping tables having a many-to-one relationship as a table group.
It has an analysis result display unit that displays the contents of the table-to-table relationship analysis information.
The analysis result display unit is a table-related analysis support device characterized by displaying one or more tables of a relational database as a table group in a reduced format. The analysis result display unit is characterized in that the display of the table group is displayed in a format in which it is possible to visually recognize whether the relationship of data between the included tables is a one-to-one relationship or a many-to-one relationship. 1 The table-related analysis support device according to 1. The table-related analysis support device according to claim 2, wherein the display format of the table group is displayed in a graph format connecting the tables. Further, it has a many-to-many relationship cutting unit that collects tables having a many-to-many relationship from the table-to-table relationship information as a table group, extracts the tables from other table groups, and updates the table-to-table relationship analysis information.
The table-relationship analysis support device according to claim 1, wherein the analysis result display unit displays a table group of a table having a many-to-many relationship in a format separated from other table groups. When the data relationship in a specific column between the tables of the relational database is a many-to-one relationship, and when the many-to-one relationship of the table is expressed by a directed line segment, when a closed circuit with the table as a node is constructed. In addition, it has a closed circuit dividing unit that separates the closed table from other table groups as a table group and updates the inter-table relationship analysis information.
The table-related analysis support device according to claim 1, wherein the analysis result display unit displays a table group of a closed table in a format separated from other table groups. The table relationship analysis support device according to claim 1, wherein the analysis result display unit displays the shared relationship of the tables when there is a table shared between the table groups. The table relationship analysis support device according to claim 1, wherein the specific column for analyzing the data relationship is a foreign key of a table of a relational database. Furthermore, it analyzes whether the data relationship in a specific column between tables in a relational database is one-to-one relationship, many-to-one relationship, or many-to-many relationship, and outputs table-to-table relationship information. The table relation analysis support device according to claim 1, further comprising a relation extraction unit. It is a table relationship analysis support method that analyzes the data relationship in a specific column between tables in a relational database.
About table-to-table relationship analysis information that defines tables with one-to-one relationship and many-to-one relationship of table data as one table group
From the table-to-table relationship information, a step of collecting the tables having a one-to-one relationship as a table group and updating the table-to-table relationship analysis information, and
A step of updating the inter-table relationship analysis information by grouping tables having a many-to-one relationship as a table group, and
It has a step of displaying the contents of the inter-table relationship analysis information.
The step of displaying the contents of the table-to-table relationship analysis information is a table relationship analysis support method characterized in that one or more tables of a relational database are displayed as a table group in a reduced format.

Images