JP2906638B2 - Database search system - Google Patents

Description

Description: TECHNICAL FIELD The present invention relates to a database search system for searching data in a relational database.

[Conventional technology]

2. Description of the Related Art A conventional database search system stores target information in a storage unit (for example, an auxiliary storage device) as a database expressed by a relational model, which is one of data models, that is, a relational database. Some records can be searched.

In this relational database data search, the user must request access to the database (data search, modification / deletion, etc.) using a database search language such as SQL (Structured Query Language). Can be, for example, the SQL
Using to indicate the table to be searched,
Given the record search conditions, the relational database data is searched.

[Problems to be solved by the invention]

However, in the above-mentioned conventional database search system, when a referential constraint is defined between a certain table and another table, when a certain table as a search target table and a search condition of a record are designated, a given condition is not applied. Can search only records that meet the specified conditions (search target table and search conditions), and search for information indicating defined reference constraints. Record could not be retrieved.

Therefore, there is a problem that data related to the record, that is, a record registered in another table, cannot be traced based on the record obtained by the search.

The present invention has been made in view of the above circumstances,
It is an object of the present invention to provide a database search system capable of searching for a record related to the searched original record.

[Means for solving the problem]

In order to achieve the above object, a database search system according to the present invention includes a storage unit that stores a relational database including reference constraint information indicating a reference constraint between defined tables, and a search target table that is an element of the relational database. A reference constraint search unit for searching the reference constraint information related to the storage unit, a target record registered in the search target table, and the reference constraint information searched by the reference constraint search unit. Record searching means for searching the storage means for a record related to the target record.

[Action]

According to the database search system of the present invention, based on a record searched according to a search condition and a reference constraint related to a table to be searched to which the record belongs, the database is related to the searched original record. Records belonging to other tables are searched.

Therefore, it is possible to easily trace from the searched original record to a record related to the original record.

〔Example〕

Hereinafter, embodiments of the present invention will be described with reference to FIG. 1 to FIG.

FIG. 1 is a block diagram showing an embodiment of a database search system according to the present invention.

In the figure, a database search system includes a relational database 11, a processing device 12, a storage unit 13
And an input / output device 14.

Tables in the relational database 11
Stored are relational data (relational data) composed of groups and information indicating reference constraints between defined tables (hereinafter referred to as reference constraint information).

In the processing device 12, the reference constraint search means 12A searches the relational database 11 for reference constraint information related to the table to be searched in response to the display request, and outputs the search result to the input / output device.

On the other hand, the record search means 12B, when given the data indicating the table to be searched and the search condition of the record,
A record matching these pieces of information is searched from the relational database 11. Further, a desired record in the searched record and a reference constraint (reference constraint search unit 12A) relating to the table to be searched to which the record belongs.
The relational database 11 searches the relational database 11 for a record belonging to another table related to the desired record, based on the reference constraint selected from among the (reference constraints retrieved by).

Each of the searched records is input to the input / output device 14.

Further, the table has three basic functions of table operation: selection (selection), projection (projection), and joining (joining). Note that these three basic functions are well-known technologies, and thus description thereof will be omitted in this embodiment.

The storage unit 13 stores records searched by the record search unit 12A and reference constraint information searched by the reference constraint search unit 12B.

The input / output device 14 includes an input device (not shown) composed of a keyboard and a mouse, and a display device (not shown) such as a display.
Using a SQL (Structured Query Language), a database is searched, and various commands and various data are input. On the other hand, the display device displays search results from the processing device 12, This is for displaying output information such as a window.

The processing device 12, the storage unit 13, and the input / output device 14 are connected to a bus.
The processing device 12 is connected to the
The storage unit 13 controls the input / output device 14.

Now, here, relational data stored in the relational database 11 is shown in FIGS.

Figure 2 shows the name 21 as the primary key and the employee number (Employee N
o) An example of an employment table (hereinafter referred to as an EMP table) 20 in which 22 and group names 23 are associated with each other. FIG. 3 shows an employee number (employee number) 31 as a primary key and a salary 32. FIG. 4 shows an example of a salary table 30 with an employee number (employee number) 41 and a telephone number 42 as primary keys.
An example of a telephone number table 40 in which
FIG. 5 shows a subgroup list table 50 in which a subgroup name 51 as a primary key and a subgroup list 52 are associated with each other.

In this embodiment, the table group defines the following referential constraints.

In other words, "salary (employee No) → EMP (employee No)""telephone number (employee No) → EMP (employee No)""EMP (subgroup name) → subgroup list (subgroup name)" In the reference constraint shown, the employee number 31 (see FIG. 3) of the salary table 30 is a reference source, and EM
The employee number 22 (see FIG. 2) of the P table 20 is a reference destination, and in the above-described reference constraint, the employee number 41 (see FIG. 4) of the telephone number table 40 is a reference source, and the EMP The employee number 22 (see FIG. 2) of the table 20 is a reference destination. Further, in the above-described referential constraint, the subgroup name 23 of the EMP table 20 (see FIG. 2)
Is the reference source, and the subgroup name 51 of the subgroup list table 50 (see FIG. 5) is the reference destination.

Next, a database search processing operation of the database search system in the above configuration will be described.

6 to 8 are views for explaining the processing steps of the database search processing, and FIG.
5 shows a flowchart illustrating a database search processing operation.

In FIG. 9, the user enters “EMP” as a search target table in the search target table designation area 61 and the search condition designation area 62 of the window 60 shown in FIG.
And "subgroup = VI3G" as a record search condition
Is specified (step 101), the record search means 21B
Is based on the specified search target table and search conditions.
The corresponding record is searched from the relational database 11 (step 102), and the search result is output to the input / output device 14. The input / output device 14 displays the searched record in a form that the user can understand (step 10).
3).

In this example, a record meeting the search condition is searched from a group of records registered in the EMP table 20 shown in FIG. 2, and as shown in FIG.
Records as search results are displayed in the search result display area 63 of 60
R1, R2, ..., Rn are displayed. The contents of each record displayed in the search result display area 63 are as shown in FIG.
"Name, employee number, group name".

When the search result is displayed, the referential constraint search unit 12A
It is determined whether or not a desired record has been selected by the user from the displayed records (record group) (step 104), and if so, whether or not there is a display request of the reference constraint from the user. Determine (Step 10
Five).

Here, if there is no display request, the process waits until there is a request. On the other hand, if there is a display request of referential constraint, the display is related to the table to which the desired record already selected belongs (that is, the search target table). The relational database 11 searches the relational database 11 for the reference restriction information to be output, and outputs the searched reference restriction information to the input / output device 14. I / O device
In step 14, the input reference constraint information is displayed in a form that can be understood by the user (step 106).

FIG. 7 shows the display format of the reference constraint displayed on the input / output device 14 when the record R1 displayed in the search result display area 63 is selected and a display request for the reference constraint is made. Show.

In the display mode shown in this figure, the window 70 includes, in the table 70, the table to which the selected record R1 belongs, that is, the reference constraint 71 related to the EMP table 20 as the search target table.
To 73 are displayed.

Next, the record search means 12B determines whether or not a desired reference constraint is selected from the displayed reference constraints (step 107), and if not selected, waits until it is selected. If selected, the table that is currently being searched among the tables related to the selected referential constraint is set as a new search target table, and the value of the column (column) related to the referential constraint is currently selected. Corresponding to the value of the existing record as a new search condition,
The record is searched (step 108).

Here, referential constraints displayed in window 70 above
When 71 is selected, of the pay table 30 and the EMP table 20 related to the referential constraint, the referent pay table 30 based on the referential constraint is set as a new search target table. The value of the column related to the constraint, that is, the value of employee number 31 (see Fig. 3)
With records corresponding to the employee number (17537) of the record R1 as new search conditions, records matching these conditions are searched.

FIG. 8 shows a display form of the search result based on the new search target table and the new search condition.

In the display mode shown in this figure, the search target table designation area 81 and the search condition designation area 82 of the window 80 are automatically generated with a “salary” as a search target table and a “salary” as a record search condition, respectively. Employee No = 1753
"7" is specified, and the record R84 meeting the conditions is displayed in the search result display area 83.

However, if the desired record is not selected in step 104, the record search means 12B determines whether or not the search is completed (step 109). If the result of this determination is not complete, the process returns to step 104. The steps after this step are executed. On the other hand, when the search is completed, the database search processing operation is ended.

〔The invention's effect〕

As described above, according to the present invention, based on a record searched according to a search condition and a referential constraint related to a table to be searched to which the record belongs, a relation between the record and the searched original record is established. Since a record belonging to another table to be searched is searched, it is possible to easily trace from the searched original record to a record related to the record.

Therefore, it is possible to interactively and easily find necessary data from the database.

[Brief description of the drawings]

FIG. 1 is a block diagram showing an embodiment of a database search system according to the present invention, FIGS. 2 to 5 are diagrams showing an example of relational data stored in a relational database 11, and FIGS. FIG. 9 is a diagram for explaining the processing steps of the database search processing, and FIG. 9 is a flowchart showing the database search processing operation. 11 ... relational database, 12 ... processing unit, 12A ... reference constraint search unit, 12B ... record search unit, 13 ... storage unit, 14 ... input / output unit, 20, 30, 40, 50 ... table , 60, 70, 80 ... window, 61, 81 ... search target table specification area, 62, 82 ... search condition specification area, 63, 83 ... search result display area.

Claims (1)

(57) [Claims] 1. A database retrieval system for retrieving records stored in a table on a table-by-table basis, comprising: storage means for storing a relational database including reference constraint information indicating reference constraints between defined tables; First search means for searching a record corresponding to a search condition from a search table by inputting a table and search conditions; and displaying at least reference constraint information related to the table together with the search result after the search by the first search means. Display means for selecting, reference selection information selected from the reference restriction information displayed on the display means, and first search means based on the reference restriction information selected by the selection means. Search means for searching the storage means for a record related to the record searched by the storage means Database search system characterized by comprising a.