JPH0934899A - Retrieval path output method and device - Google Patents

Abstract

PROBLEM TO BE SOLVED: To decide a retrieval path automatically by referencing a column management table representing storage information of each column so as to decide a coupling state between columns thereby deciding a retrieval path from a key column to a retrieval object column name. SOLUTION: The device is provided with a column name input means 11, a column management table 16, a path decision means 10 and a decision result output means 13, and the retrieval path decision means 10, and the retrieval path decision means 10 is made up of a column management table input means 17, an inter-column coupling decision means 15 and a retrieval path decision means 12. When a retrieval path to retrieval where a column is stored in a database is found out, a key column name and a retrieval object column name being a key to retrieve a database are entered and a column management table representing storage information of each column is referenced, the coupling state between columns is decided and the retrieval path from the key column to the retrieval object column name is decided based on the decision result.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a search path output method and apparatus, and more particularly to a search path for searching a column from another column in a database system in which columns are divided into a plurality of tables. The present invention relates to a search route output method and device for determining and outputting.

[0002]

2. Description of the Related Art Hitherto, as a route search device for searching one column on a database for another column, Japanese Patent Laid-Open No. HEI-2 is disclosed.
There is -144671. This method proposes an apparatus for determining a search path when searching one column for another column in a database in which columns are divided into a plurality of tables.

This device determines a search path from one column to another column by the following procedure. From the input device, enter the name of the column (hereinafter, key column) that is the key of the search and the name of the column that is the target of the search (hereinafter, the target column). The apparatus is provided with data of a table in which each column exists (referred to as "field name table" in Japanese Patent Laid-Open No. 2-144671), and the table name in which the key column and the target column exist is searched from the data. The apparatus is provided with data (referred to as "join table" in Japanese Patent Laid-Open No. 2-144671) indicating the join relationship between the tables, and is searched from the data indicating the join relationship between the tables based on the table name obtained in Determine the route.

[0004]

However, the above-mentioned conventional method aims to automatically determine the search path only by inputting the key column and the target column, but the data indicating the join relation between the tables is used. The part that creates the (join table) is not automated, and the join table must be researched by a person and registered in advance.

The present invention has been made in view of the above points, and it is an object of the present invention to provide a search route output method and apparatus capable of automatically determining a search route without the need for a human to check the search route. To aim.

[0006]

FIG. 1 is a diagram for explaining the principle of the present invention. The present invention, in a database system in which columns, which are various types of information registered on a table, are distributed over a plurality of tables, provides a search path for searching where the columns are stored in the database. In the search path output method to find, enter the key column name that is the key for searching the database and the column name of the search target (step 1), and refer to the column management table that shows the storage information for each column The combined state is determined (step 2), and the search path from the key column to the column name of the search target is determined based on the determination result (step 3).

Further, according to the present invention, when determining connection information between columns (step 2), a column X and a column Y exist in an n-th order connection relationship (same table), and the columns Y and Z are If there is a 0th-order connection relationship, the logic that the column X and the column Z are connected at any order from 0 to n + 1 is used, and the higher order is determined based on the lower order connection determination result. Determine the connection relationship of.

FIG. 2 is a diagram showing the principle of the present invention. The present invention relates to a search path output device for determining a search path when a column is searched from the database in a database system in which columns that are various types of information registered on the table are divided into a plurality of tables. In the column name input means 11 for inputting the name of a column (hereinafter, referred to as a key column) that is a key for searching the database and the name of a column that is a target of the search (hereinafter, a target column)
A column management table 16 for managing which table each column exists, a route determination means 10 for determining a search route from a key column to a target column, and a route determination means 10
The search path determination unit 10 refers to the column management table input unit 17 for inputting the column management table, and the column management table 16 to determine whether each column is different from the others. Inter-column join determination means 15 for determining the join state between the column and the column through which the table is joined, and converting the column into a predetermined data format for storage.
And the search path determination means 12 for determining the search path from the key column to the target column based on the result of the inter-column connection determination means 15.

Further, the inter-column coupling determination means 15 described above is used.
If a column X and a column Y exist in an nth-order connection relationship (same table) and the column Y and a column Z exist in a 0th order connection relationship, the columns X and Z are 0 to n + 1. It includes means for determining a high-order connection relation based on a low-order connection determination result by using the logic of connection with any of the following orders.

As described above, according to the present invention, the column management table input unit 17 inputs the column management table 9, the inter-column connection determination unit 15 determines the connection state between columns from the column management table 16, and the predetermined format is determined. It is converted to the data of and stored in the device. In the combined state determination process, "Column X
And column Y have an nth-order connection relationship, and column Y and column Z
Is in a 0th-order connection relationship (existing in the same database), column X and column Z are connected by any order of 0 to n + 1 ”. It is a mechanism to judge the connection relation of high degree.

Further, at the time of the search, the key column name and the target column name are input from the outside, and the search path determination unit refers to the data for which the coupling information between the columns is determined and refers to the search path from the key column to the target column. Is determined and the result is output from the search path output unit. As a result, in a database system where columns exist in multiple tables, 1
The search path from one column to another can be determined and output.

[0012]

BEST MODE FOR CARRYING OUT THE INVENTION Prior to the following description, terms and notations used in the description will be defined. -Column: Various information registered in the database table-Key column: Column that is the key of the search-Object column: Column that is the object of the search-Nth-order coupling between columns: Column groups X, Y, C1 to Cn Therefore, X and C1, C1 and C2, ... Cn-1 and Cn, Cn
And Y are in the same table, X and Y are said to be connected to the nth order. -Relay column: When X and Y are coupled to the nth order through C1 to Cn, C1 to Cn are called a relay column. -Relay table: X and C1 are in table T1 and C1
And C2 are in table T2, ... Cn and Y are in table T
When it is n + 1, T1 to Tn + 1 are called a relay table between X and Y. Immediately preceding column: X and Y are bonded to the nth order through C1 to Cn, and X and C1, C1 and C2, ..., Cn-1 and Cn,
If Cn and Y are in the same table, Cn is called the previous column from X to Y. -Previous table: The table connecting the previous column from X to Y and Y is called the previous table from X to Y.

FIG. 3 shows the configuration of the search path output device of the present invention. The search route output device shown in FIG. 1 includes a column name input unit 11 that inputs a key column and a target column, a route determination unit 10 that determines a search route, and a column management table input unit that inputs a column management table to the route determination unit 10. 17, a search path output unit 13 and an output file 18.

The route determination unit 10 includes a search route determination unit 12,
Inter-column join data 14, which is data relating to the inter-column join state for all combinations of columns that exist in the database, inter-column join determination unit 15, and columns that are data that records in which table each column exists It is composed of a management table 16.

The search path determining unit 12 determines the search path from the key column to the target column by referring to the inter-column connection data 14 based on the key column name and the target column name. The inter-column connection determination unit 15 determines inter-column connection information based on the column management table 16 and generates inter-column connection data 14.

FIG. 4 shows the structure of the column management table of the present invention. The column management table 16 is in the form of tabular data, and if the table Ti described in the column heading includes the column Cj in the row heading, a symbol (Cj, Ti) indicating that ( In the figure, ○) is described. In the example of the figure, (C1, T1), (C2, T2), (C2, Tn
), (Cj, Ti), (Cn, T1), (Cn, T3)
) Is attached. Therefore, table T1 contains columns C1 and Cn and table T2 contains C2.
However, table T3 contains Cn, and table Ti contains Cj.
However, the table Tn contains C2.

FIG. 5 shows the structure of inter-column connection data of the present invention. The inter-column connection data 14 is data generated by the inter-column connection determination unit 15. In the table of the figure, the key column name is described in the row heading column, the target column name is described in the column heading column, and the combined data between the key column X and the target column Y is described in the (X, Y) column. become.

In each column, the immediately preceding table from the row heading description table to the column heading description table, the immediately preceding column, and the join order are described in the form of "join order- (previous table name) -previous column name". As an example, in the row X of the table of FIG. 5, X- (T1) -C1- (T2) -C2 --... Cn-
An example in which a column connection such as (Tn + 1) -Y is stored is shown. In the figure, the process of reading the immediately preceding column Cn and the immediately preceding table Tn + 1 of Y from the (X, Y) column and reading the immediately preceding column Cn-1 and the immediately preceding table Tn of Cn from the (X, Cn) column are repeated. The relay table and column can be detected in sequence. In addition, in the same figure, the inside of () shows a relay table, and between "-" and "-" shows a relay column.

FIG. 6 is a flow chart showing the operation of the inter-column coupling determination unit of the present invention. First, prior to processing, a table of inter-column combined data 14 is prepared on the buffer for recording combined data. Step 101) The column management table input unit 17 inputs the column management table 16 to the route determination unit 10.

Step 102) The inter-column join decision unit 15 of the route decision unit 10 decides the 0th order join relation between columns based on the input column management table 16, and the result is stored in the join data storage table. It is described in a predetermined column of the inter-column connection data 14. Step 103) Let X be the column corresponding to the first row of the table for storing the inter-column combined data 14.

Step 104) The initial value of the order count value k is set to 0. Step 105) A column having a k + 1th-order coupling relationship with X is determined based on a column having a kth-order coupling relationship with X, and the determination result is written in the table of inter-column coupling data 14.

Step 106) It is judged whether or not a column having a k + 1th order connection relationship with X is found. If found, the process goes to Step 107, and if not found, the process goes to Step 108. Step 107) It is determined whether or not there is a blank in the X row, and if there is a blank, the value of k is incremented and Step 105
Move to If there is no margin, the process proceeds to step 108.

Step 108) Inter-column combined data 1
It is determined whether or not there is a lower row in the table of No. 4, and if there is a lower row, the process proceeds to step 109, and if there is no lower row, the process ends. Step 109) Let X be the relevant row in the table of inter-column combined data 14.

Note that step 1 of the above flow chart
In 05, if the join relation is determined for all columns that are joined to X in some way, it is not possible to find a new column that has a join relation with X. If no table having a k + 1 primary join relation is found, it is determined that the join relations have been exhausted for all columns, and the process of step 105 is repeated to end the process.

Next, the processing of the above step 105 will be described in detail. FIG. 7 is a diagram for explaining the connection relationship determination processing of the inter-column connection determination unit of the present invention. It is assumed that the numbers in circles in the table of the inter-column combined data 14 shown in the same figure and the numbers in the following description match, and "*****" in the figure.
Indicates that the connection relationship between the two columns has not been determined.

Focusing on the column C having a k-joint relationship with X, the name of C is stored in the variable C. Access row C. Attention is paid to columns W1 to W3 that have a 0th-order coupling relationship with C. Check the connection between X and W1-W3 (in the figure, W
1) k-1 order connection relationship has been determined). With regard to W2 and W3, the connection relationship has not been determined.

In Tables (C, W2), (C, W) for the tables W2, W3 whose connection relationship with X has not been determined.
The table name in "-()-" is read from each column of 3) and stored in the array T. Tables W2 and W3 whose connection relationship with X is undetermined
For (), the bonding relationships are described in the (X, W2) and (X, W3) columns. For example, in the case of W2, the connection relation is described in the form of "k + 1 (content of element corresponding to W2 of array T) -content of C".

Next, a procedure for the search path determination unit 12 to read the search path from the inter-column connection data 14 will be described. FIG. 8 shows the operation of the search route determination unit of the present invention. It is assumed that the numbers in the circles in the figure match the numbers in the following description.
In the figure, (a) shows inter-column combined data,
(B) shows the contents of the search path output file 18. The search path determination unit 12 receives the key column X and the target column Y from the column name input unit 11 and reads the search path by referring to the inter-column combined data (a). The read result is written in the search path output file 18 (b).

First, the target column name Y is described in the output file 18 (b). Next, the (X, Y) column α of the inter-column join data (a)
To access the coupling order n, the immediately preceding column Cn, and the immediately preceding table Tn + 1 into the buffer. Then, the immediately preceding column and the immediately preceding table are described in the output file 18 (b) in the format shown by β.

Next, if the bond order is not 0, the process proceeds. Access the (X, Cn) column and perform the same procedure. This is a column where the bond order is 0 ((X,
Repeat until you hit column C1). As a result, the output file 18 outputs a relay table / column sequence from the target column Y to the input column X in a predetermined format.

[0031]

Embodiments of the present invention will be described below with reference to the drawings. FIG. 9 shows the tables in the database of the embodiment of the present invention. The table in the database shown in the figure is
There are five tables: "employee table", "post table", "affiliation table", "location table", and "prefecture special product table".

FIG. 10 shows an example of a column management table corresponding to the table shown in FIG. "Employee table" shown in the figure
Column contains "name", "age", "post", "affiliation", "affiliation table", "affiliation",
"Job description" and "Location" are included, "Title table" includes "Title" and "Salary", "Location table" includes "Location" and "Prefecture", and "Prefecture" The “special product table” includes “prefecture” and “special product”.

FIG. 11 shows an example of inter-column combined data corresponding to the column management table of FIG. In FIG.
"Affiliation" is "affiliation", "special product" is "special product",
“Location” shall be abbreviated as “location”. FIG. 12 shows an output example of search results when the key column is “name” and the target column is “special product”.

[1] First, the column management table input unit 17 inputs the column management table of FIG. Column management table 16
The first row of indicates the table in which the "name" column exists. Since the “name” column exists only in the “employee table”, only the column “employee table” is marked with a circle. The same applies to other rows, and the column of the table in which the column of that row exists is marked with a circle.

[2] Next, the inter-column connection determination unit 15 determines the connection relationship between columns. Below, the connection relationship between the “name” and other columns is determined, and the connection data shown in FIG.
The processing procedure until the data shown in FIG. (1) First, a column having a zero-order connection relationship with "name" is determined, and "0 (" name "and the table name that connects that column)" is displayed in the column where the row of "name" and the column of the column intersect.
Is recorded (step 102).

It should be noted that having a 0th order connection relationship means that they are in the same table. For example, in FIG. 11, since “name”, “age”, “post”, and “belonging organization” are in the same “employee” table, “0-
The symbol "(employee)" is written.

(2) Next, the column in the first row of the table is X
It is said. Here, the "name" is X (step 1
03). (3) X, that is, a column having a primary binding relationship with "name" is determined (steps 104 and 105). Judgment shall follow the procedure of FIG. That is, attention is paid to one of the columns having a zero-order relationship with the “name”. Here, "affiliation" is taken up.

The character string “belonging organization” is put in the variable C, and the row of “belonging organization” is accessed. Attention is paid to the column having a zero-order connection relationship with "affiliation" in the row of "affiliation". “Name”, “age”, “post”, “business content”, and “location” are applicable.

The intersections of the “Name”, “Age”, “Title”, “Job Description”, “Location” column columns and the “Name” row are checked to see if a connection relationship has been determined. Here, the "business content" and "location" portions are undetermined. Access the columns of (C (that is, "belonging organization"), "business content") and (C, "location"), and
The immediately preceding table name described in "-" is read into the array T. In the example of FIG. 11, the array element T (1) corresponding to “business content” and the array element T corresponding to “location”
In "(2)", the "affiliation" is entered.

The connection relations are described in the (“name”, “business content”) and (“name”, “location”) columns, respectively. The contents corresponding to the respective elements of the coupling order 1 and T and the contents of the variable C are described. As a result, in each column, "1- (belonging organization) -belonging organization" and "1- (belonging organization) -belonging organization" will be described.

The same applies to the other columns “age” and “post” that have a zero-order relationship with “name”. (4) In steps 106 and 107 of FIG. 6, as the end condition, there is a column that has a primary binding relationship with “name”, and there is a blank space in the line of “name”. Is not applicable. Therefore, k is incremented by 1 to 1 and the process proceeds to step 105.

(5) When the above procedure is repeated, at the time when the tertiary bond relationship between the "special product" and the "name" is determined,
This means that the ending condition of step 107 is satisfied, and the binding relationship between “name” and other columns is exhausted. [3] It is assumed that the key column name “name” and the target column name “special product” are input from the column name input unit 11. The search route determination unit 12 determines the search route according to FIG.
The result is described in the output file 18 as shown in FIG.

(1) First, the search path output unit 13 writes the target column name “special product” in the output file 18. (2) Access the ("Name", "Special product") column. (3) The immediately preceding table name “prefecture special product” and the immediately preceding column name “prefecture” are read, and the search path output unit 13 writes the contents in the output file 18.

(4) Since the bond order is not 0, the process proceeds to the next step. (5) Access the column of (column just before “name” and “special product” (ie “prefecture”)). (6) The same processes as (1) to (4) above are performed. This is the column of the column where the bond order is 0, that is, ("name",
Repeat until you hit the "Affiliation" column. As a result, the search route is described by the search route output unit 13 in the format shown in FIG.

That is, the search route to the target column is: special product ↓ (prefecture special product) ↓ prefecture ↓ (affiliation organization) ↓ affiliation organization ↓ (employee) ↓ name.

With the above processing, the search path from the key column to the target column can be output using the data accumulated in the output file 18. It should be noted that the present invention is not limited to the above-described embodiment, but can be variously modified and applied within the scope of the claims.

[0047]

As described above, the search path output method and apparatus of the present invention have the following effects. (1) It is not necessary to give the search route itself as knowledge to the apparatus, and it suffices to give data indicating in which table each column exists.

(2) Therefore, it is possible to save the labor of the human being searching the search route.

[Brief description of drawings]

FIG. 1 is a diagram for explaining the principle of the present invention.

FIG. 2 is a principle configuration diagram of the present invention.

FIG. 3 is a configuration diagram of a search route output device of the present invention.

FIG. 4 is a configuration diagram of a column management table of the present invention.

FIG. 5 is a diagram showing a structure of inter-column combined data of the present invention.

FIG. 6 is a flowchart showing the operation of the inter-column coupling determination unit of the present invention.

FIG. 7 is a diagram for explaining the connection relation determination processing of the inter-column connection determination unit of the present invention.

FIG. 8 is a diagram showing a procedure for reading a search path from inter-column combined data according to the present invention.

FIG. 9 is a diagram showing a table in a database according to an embodiment of the present invention.

FIG. 10 is a diagram showing an example of a column management table corresponding to the table shown in FIG. 9 according to an embodiment of the present invention.

11 is a diagram showing an example of inter-column combined data corresponding to the column management table of FIG. 10 according to an embodiment of the present invention.

FIG. 12 is a diagram showing an output example of search results according to an embodiment of the present invention.

[Explanation of symbols]

 9 Column management table 10 Route determination unit 11 Column name input unit 12 Search route determination unit 13 Search route output unit 14 Inter-column join data 15 Inter-column join determination unit 16 Column management table 17 Column management table input unit 18 Output file

Claims (4)

[Claims] 1. In a database system in which columns, which are various kinds of information registered on a table, are distributed over a plurality of tables, a search path for searching where in the database the columns are stored. In the search path output method to find the, enter the key column name that is the key to search the database and the column name of the search target, and refer to the column management table that shows the storage information of each column And a search route output method that determines the search route from the key column to the column name of the search target based on the determination result. 2. A column X and a column Y exist in an nth-order connection relationship (same table) and a column Y and a column Z exist in a 0th order connection relationship when determining connection information between the columns. If there is a relationship, column X and column Z are connected to each other at any degree from 0 to n + 1, and a high-order connection relationship is determined based on a low-order connection determination result. The search route output method according to item 1. 3. A search path output for determining a search path when searching for the column from the database in a database system in which columns of various types of information registered on the table are present in a plurality of tables. In the device, a column name input means for inputting a column (hereinafter, key column) name that is a key for searching the database, a column name (hereinafter, target column) that is a search target, and which table each column exists in A column management table for managing whether to perform a search route determination unit that determines a search route from the key column to the target column, and a determination result output unit that outputs a determination result of the search route determination unit, The search route determination means refers to the column management table input means for inputting the column management table and the column management table. The inter-column join determination means for determining the join state between the columns in which each column is joined to the other column, converting the data to a predetermined data format, and storing the inter-column join determination means, A search route output device comprising search route determining means for determining a search route from a key column to a target column based on a result. 4. The inter-column coupling determining means determines that a certain column X and a column Y are in an nth-order coupling relationship (same table), and the columns Y and Z are in a zero-order coupling relationship. , Column X and column Z include means for determining a high-order connection relation based on a low-order connection determination result by using the logic that they are connected at any order from 0 to n + 1. 3. The search route output device described in 3.