JPH09245048A - Argument retrieval RDB system - Google Patents

Abstract

(57) [Abstract] [PROBLEMS] To provide a relational database system that can be easily modified or newly constructed by general users and can be modified with a small amount of time and effort when the conditions for collecting data or information retrieval are changed. . SOLUTION: A data dictionary 11 which defines a non-coded table name / item name and a coded table name / item name in association with each other, and a non-coded table name / included in a processing instruction input by a user. A database management system 2 that retrieves a coded table name / item name corresponding to an item name from the data dictionary 11 and executes the processing command, a relational data model 3 that stores data, and information processing that inputs / outputs to / from the database The device 5 is provided.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention is an argument that enables a general user to utilize a relational database system (hereinafter simply referred to as a database system or RDB system) for collecting data items and search conditions at a management system level. Retrieval RDB system.

[0002]

2. Description of the Related Art Conventionally, various kinds of information have been databased in each technical field and industrial field, and up to now, a database system for storing various kinds of information has been formed. For example, a customer information database system, a company information database system, an economic information database system, a science and technology information database system, etc. are examples.

Further, with the spread of computers today, each company also owns the RDB system in the company. For example, there is an RDB system that stores information such as a customer list, transaction history of each customer, information about each customer, and business performance of employees / stores.

These RDB systems are designed by a system engineer and a database management system using a relational language is constructed before being offered to general users. This allows general users to operate the search menu prepared by the system engineer to retrieve data without being aware of the existence of the RDB program (the database management system) when using the relational database (RDB). You can search.

Here, in order to clarify the purpose, structure and effect of the present invention, the construction of the database management system in the relational language will be described.

Generally, the RDB system is composed of three components: a relational language, a database management system, and a relational data model. It should be noted that, depending on the case, it is composed of four components including an application program for utilizing various functions of the database.

FIG. 4 shows the configuration of the RDB system.

In FIG. 4, reference numeral 1 indicates a relational language, reference numeral 2 indicates a database management system configured by using the relational language 1, and reference numeral 3 indicates a relational data model searched by the database management system 2. There is. The relational language 1, the database management system 2, and the relational data model 3 constitute a relational database 4 in a narrow sense.

Reference numeral 5 indicates an information processing apparatus for constructing the database management system 2 and searching for data, and reference numeral 6 indicates an application program utilizing the relational database 4 in a narrow sense.

Here, as relational language 1, SQ
L (Structured Query Langua)
ge) is mentioned as a typical example. SQL is I
It was developed by Mr. Chamberlain's group of BM Co., and since it is almost a standard relational language nowadays, it will be described below using SQL.

SQL has an independent grammar system, and SELE
It has data operation commands such as CT, INSERT, and DELETE, data definition commands such as CREATE, ALTER, and DROP, and control commands such as GRANT and REVOKE.

To configure the database management system 2 by SQL, data definition, data operation, and data control are performed by SQL.

The definition of data means to define the tables that make up the relational data model. The tables are stored in the relational data model 3 as indicated by reference numerals 7a, 7b, 7c, ... In FIG. 4, and each table stores a fixed set of data. Each column 8 of the table is called an item and stores data of a certain attribute. Each row 9 in the table is called a record, and is a collection of data having a plurality of attributes.

In the definition of data, the above individual tables are defined. Specifically, the table structure is defined, and the table name, the attribute name of each item, data type allocation, index creation, etc. are specified (CREATE statement).

Data manipulation means addition, modification, and
Refers to operations such as search and deletion. Add, change, search, and delete data are SELECT, INSERTD, and
It is performed by each command of UPDATE and DELETE.

For data control, a GRANT statement or the like defines the access right to the table.

The definition of data, the operation of data, and the control of data by the SQL described above are performed by designating tables and items. For example, a predetermined item in the table “AA100101” is extracted to create a virtual table (called a view) “AA
The CREATE statement defining 100101, VIE "is
It is expressed as follows.

AA100101. VIE create view basic attributes (department code, account number, customer name kanji, customer name kana, customer opening date, all account closing date, customer sale code, customer midi code, general account sales code, general account midi code 、 Asset sales code 、 Assignment midi code 、 Corporate classification 、 Professional corporation classification 、 Non-resident classification 、 Gender classification 、 Relationship classification 、 Birth date 、 Key clown classification） as select BTN_CD, KOZA_NO, KYAKUKYMEIKN ALL_KZ_HEISA_YMD, KYAKU_SLS_CD, KYAKU_MDY_CD, IPN_SLS_CD, IPN_MDY_CD, RUITO_SLS_CD, RUITO_MDY_CD, H N_KBN, KEY_CLIENT_KBN from AA100101; the CREATE statement, from a table called "AA100101", item "BTN_CD, KOZA_NO, ..., KE
"Y_CLIENT_KBN" is extracted and "AA100
101. View item "VIE""department code,
Account number, ..., Key client classification ”is defined.

Further, for example, the table "AA100101"
The SELECT statement for extracting the Kanji customer name of the department store code 001 in order from the customer opening date is expressed as follows. select KYAKU_MEI_KJ from AA100101 where BTN_CD = 001 order by KYAKU_KAISETU_YMD Also, the GRANT statement that grants access to a specific table to a given user specifies the coded user name and the name of the table.

As is clear from the above SQL statement, the conventional SQL statement uses the names of the coded tables and items, and all commands are performed by the names of the coded tables and items. It was

A large number of these SQL statements constitute the database management system 2, and by executing the SQL statements of this database management system 2, various data are retrieved, and the function as a relational database is obtained. It works.

[0022]

However, in the above-mentioned conventional RDB system, it is difficult for a general user to modify the search information and the data to be collected according to his / her needs.

That is, as is apparent from the above-mentioned example, in order to modify the search information and the collected data, the database management system that defines the operation of the RDB system must be modified. Is coded, and it was difficult for general users to understand the code system.

Moreover, in order to modify the database management system, it is necessary to correctly describe it according to the grammar of the SQL sentence, so it is practically impossible for a general user to modify the database management system in response to his / her request. It was possible.

In this case, a general user could not retrieve the information he / she wanted in the form he / she wanted, and the database could not be truly utilized.

In order to organize a large number of tables and items and manage them as a fixed system, it is absolutely necessary to code the tables and items as described above. The use of non-coded table names and item names that allow general users to understand the meaning had to complicate the system of tables and items.

In addition, the conventional database system using the SQL statement is used to change the search information and the collected data.
I had to spend a lot of time and effort modifying the database management system program.

This is because the conventional relational database database management system uses SQ for table names and item names.
Because it is described in the program of L sentence, it was necessary to review the entire database management system so that there was no contradiction in order to change the conditions for collecting data and information retrieval.

This work is repeated every time maintenance of a so-called database system is performed, and there has conventionally been a request to reduce the labor and time required for this.

Therefore, the problem to be solved by the present invention can be easily modified by a general user or newly constructed, and can be modified with a small amount of time and effort when the conditions for collecting data or information retrieval are changed. To provide an RDB system.

Further, in the conventional RDB system, when the same RDB system is opened to a wide range of general users and a plurality of persons use the same RDB system, which user has what authority (for example, authority to modify the database), In addition, there is no effective means for the problem of which user is allowed to access which data.

Conventionally, a security system different from the database system was constructed to restrict access to the RDB system.

However, such a system of security protection must be complicated, and if the security protection is improved, the use of the RDB system is always contradictory.

Therefore, another problem to be solved by the present invention is that the RDB system itself has a function of giving predetermined authority and resources to the user, and the user can truly utilize the database with a simple system configuration. , And R
It is to provide an RDB system that can maintain the soundness of the DB system.

[0035]

The argument retrieval RDB system according to claim 1 of the present application defines a data dictionary in which a non-coded table name and an item name are associated with an encoded table name and an item name. And a database management system that executes data processing instructions in a relational language, enter the uncoded table name and item name included in the processing instruction entered by the user, and search the data dictionary. Database management that acquires the coded table name and item name corresponding to the non-coded table name and item name, and substitutes the coded table name and item name into the argument of the processing instruction for execution A system, a relational data model storing a table having a data structure of records and items, and a table for said data dictionary And an item name definition, a processing command for the database management system, a data input for the relational data model, and an information processing device for outputting a processing result by the database management system. Is.

According to the argument retrieval RDB system of claim 2 of the present application, in the argument retrieval RDB system of claim 1, the data dictionary includes a new table name and item name for a ready-made table name / item name code system. The database management system is configured such that a code can be added and defined, and the database management system searches the table name / item name in the processing instruction input by the user from the data dictionary, and prepares the table name / item name and the additional table. It is characterized in that the names and item names are handled and processed in the same manner.

The argument retrieval RDB system according to claim 3 of the present application includes a data dictionary defining a user name, an authority name permitted to the user, and a resource name accessible to the user, and a relational language. A database management system for executing a data processing command by a user, first prompting a user for a user name, searching the data dictionary for the user name entered by the user, and checking the authorization name for the user. , A database management system that acquires a resource name accessible to the user and executes the processing instruction under the restriction of the authority name and the resource name, and relational data storing a table having a data structure of records and items A model, processing instructions for the database management system, and An input user name, the data input to the relational data model, is characterized in that it has a, and an information processing apparatus that performs the output of the processing result by the database management system.

[0038]

BEST MODE FOR CARRYING OUT THE INVENTION Next, an argument retrieval RD according to the present invention
One embodiment of the B system will be described below.

FIG. 1 shows the configuration of the argument retrieval RDB system according to the present invention. For ease of understanding, the same parts as those in FIG. 2 are described with the same reference numerals.

Argument retrieval RDB system 10 according to the present invention
Consists of a relational language 1, a database management system 2, a relational data model 3 and a data dictionary 11.

That is, a data dictionary 11 is added to the conventional relational database 4, and the database management system 2 is further provided with an argument function (argument search function) for fetching the result of searching the data dictionary 11.

FIG. 2 shows the flow of processing by the argument retrieval RDB system 10 of the present invention. Argument retrieval RDB of the present invention
According to the system 10, the names of tables and items that the user can freely read in the data dictionary 11 (referred to as non-coded table names and non-coded item names) and coded table names. , Item names can be defined in association with each other (step 100).

On the other hand, the database management system 2 of the present invention searches the data dictionary 11 for the non-coded table name and the item name for which the processing instruction is given at the place where the table name and item name of each SQL statement are described. It is an argument that substitutes the coded table name and item name corresponding to them.

That is, the database management system 2 of the present invention uses the table name / item name of the user's processing command in SQL.
It has an argument function of inputting to a statement argument, retrieving the coded table name / item name corresponding to the table name / item name input to this argument, and substituting it into the SQL statement.

When the user commands a predetermined process in the argument retrieval RDB system 10, the user uses the uncoded table name and item name readable by the user to perform SQ.
The execution of the L sentence is instructed via the information processing device 5 (step 110).

The database management system 2 which has input the processing instruction of the user inputs the non-coded table name and item name, retrieves the non-coded table name and item name from the data dictionary 11, The corresponding coded table name and item name are acquired (step 120).

The database management system 2, which has acquired the non-coded table name and the item name, substitutes the non-coded table name and the item name in the SQL statement and executes the SQL statement (step 130).

For example, by the argument retrieval RDB system 10 of the present invention, from the table "customer", the items "department code", "account number", "customer name (kanji)", "customer name kana", "customer establishment". When "Date" is extracted and a virtual table (view) "Customer abstract" is created (CREATE statement)
Is as follows.

First, the user defines in the data dictionary 11 the following non-coded table name / item name and coded table name / item name in association with each other.

Non-coded table name / item name Coded table name / item name Customer AA100101 Customer abstract AA100101, VIE Department store code BTN_CD Account number KOZA_NO Customer name Kanji KYAKU_MEI_KJ Customer name Kana KYAKUMEI_KY Customer non-IS Date K The name / item name can be freely defined by a user-readable character string.

On the other hand, the CR of the database management system 2
The ATE sentence is described as follows.

% HC2% create view% H2% (% K1%,% K2%,% K3%,% K4%,% K5%,) as select% KC1%,% KC2%,% KC3%,% KC4% ,% KC5%, from% H1%; where% _% represents an argument. In the argument retrieval RDB system 10 of the present invention, when a process of creating a virtual table (view) “customer abstract” by extracting necessary items from the table “customer”, the user must select “customer” (table), "Customer abstract" (view), "department code" (item), "account number" (item), "customer name (kanji)" (item), "customer name kana" (item), "customer opening date" Execution of the CREATE statement can be instructed using an uncoded table name / item name such as "(item)".

When a processing command using this non-coded table name / item name is input, the argument "% H1%" is "customer".
The non-coded table name is input, the corresponding non-coded table name is searched from the data dictionary 11, and the corresponding coded table name “AA100101” is CR.
Substitute in the ATE statement. Thereafter, similarly, the database management system 2 uses the arguments "% KC1%", "% KC2".
For "%", ..., "% KC5%", enter the entered "department code", "account number", ..., "customer opening date", search the data dictionary 11 and respond to them. Substitute the coded item name, and the table name of the view and the item name of the view are the arguments "% K1%", "% K2".
For “%”, ..., “% K5%”, the item name input by the user is substituted.

In this way, after the table name / item name is assigned to all the arguments, the database management system 2 executes the CREATE statement. This CREATE statement operates in exactly the same way as the conventional CREATE statement, and extracts the necessary items from the corresponding table of the relational data model 3 to create a view.

As described above, according to the argument retrieval RDB system 10 of the present invention, once the table name / item name is defined in the data dictionary 11, the user is completely coded in the subsequent database operation. There is no need to be aware of table names and item names.

It should be noted that the database management system 2 of the argument retrieval RDB system 10 of the present invention is simplified in the data operation / control function, and the input table or item To perform only certain processing. This enables data dictionary 1
By changing the definition of 1, different items can be extracted from completely different tables without changing the CREATE statement.

For example, the user selects "DD0" for the non-coded table name "commodity" in the data dictionary 11.
The coded table name "00001" is defined, and the table "DD00
If the data "0001" is prepared, a view "commodity excerpt" can be constructed by extracting predetermined items from the items of the table "commodity" using the same CREATE statement as in the above example.

Further, according to the argument retrieval RDB system 10 of the present invention, the data dictionary 11 and the database management system 2 are configured as described above, so that even when the retrieval condition of the collected data or the information is changed, it is simply It is only necessary to change the definition of the data dictionary 11, and it is not necessary to modify the program itself of the database management system 2. In other words, the maintenance-free database management system 2 can be provided.

Incidentally, the data dictionary 11 of the present invention.
Can systematically define a new table name / item name, or freely add a new table name / item name to the definition of a ready-made table name / item name.

When the user systematically registers the new table name and item name in the data dictionary 11, the subsequent database operation does not pay attention to the coded table name / item name and does not code them. Data can be processed / searched using the created table name / item name.

On the other hand, a new table name / item name is added to the ready-made table name / item name.
When the item name is added, the user's own data processing function can be added to the ready-made RDB system. In this case, when a processing instruction is input for a specific table, the database management system 2 searches the table name of the table from the beginning to the end of the data dictionary 11. As a result, for example, if the user has added his or her own table name / item name to a ready-made table name / item name, the table name / item name is exactly the same as the ready-made table name / item name. Can be used to process and process data.

The argument retrieval RDB system according to the present invention that can be utilized by general users has been described above. Next, different embodiments of the argument retrieval RDB system of the present invention will be described below.

In this different embodiment, the data dictionary 11 can be used to secure the database.

FIG. 3 shows the flow of processing by the argument retrieval RDB system of this embodiment.

As shown in FIG. 3, in the argument retrieval RDB system of this embodiment, the user name, the access authority of the user, and the access resource are defined in the data dictionary (step 150).

On the other hand, the database management system is configured so that the program always requires the input of the user name before using the database. Further, the database management system is prepared with a GRANT statement including a user and arguments such as authority and resources accessible to the user.

The GRANT statement is described as follows. GRANT “Authority name argument” ON “Resource name argument” TO “User name argument” Here, the authority name is an inquiry command (SELECT), an insert command (INSERT), a delete command (DELETE), an update command ( UPDATE) etc. A resource name is an accessible table or item.

Argument search R of this embodiment by a predetermined user
When using the DB system, the database management system of the argument retrieval RDB system first requests the input of the user name (step 160).

When the user name is input, the database management system searches the data dictionary and acquires the authority name and resource name permitted to the user,
The GRANT instruction is executed by substituting it into the argument of the GRANT statement (step 170).

By executing the GRANT statement, the user can perform only certain processing. Or,
You can choose to display only a limited range of tables and data to that user.

As a result, in a relational database system used by a large number of users, it is possible to allocate usable processing instructions and accessible data to each user, and to ensure the safety of the entire system.

In the above description, the RDB, relational language, database management system, and relational data model have been described as having physical configurations. In practice, each of these components is
B, an information processing device controlled by software so as to operate a relational language and a database management system, and a storage device storing a relational data model. In the above description, the information processing device and the RDB are described as separate devices, but the information processing device and the relational database may be the same information processing device.

[0073]

As is apparent from the above description, according to the argument retrieval RDB system of the present invention, the table name / item name which is not coded and the table name / item name which is coded are included in the data dictionary. Can be defined in correspondence with each other, and the database management system executes the processing instruction by searching the data dictionary for the code name corresponding to the table name / item name in the processing instruction input by the user. If the code name of the table / item is registered in the data dictionary, the user can operate / process the data without being aware of the coded table name / item name.

As a result, when a general user attempts to acquire unique data by modifying the information to be searched or the conditions for information search, he / she does not have to be aware of the difficult coded table name / item name. You can use non-coded table / item names that are generally understandable.

When the user wants to process unique data, a table storing the data is prepared, and the uncoded name of the table and the coded name are associated with each other in the data dictionary. By defining, the data of the table can be freely searched and processed using the function of the database management system.

Further, according to the argument retrieval RDB system of the present invention, the database management system is specialized for performing the predetermined processing on the input data and outputting the processing result by the argument function. Even if the data to be collected and the conditions for information retrieval change, it is not necessary to modify the program of the database management system, and it is sufficient to change the definition of the data dictionary. This allows
It becomes unnecessary to spend a lot of labor and time to review the program of the database management system every time the content of the collected data changes. Further, even when the information retrieval command is modified, the purpose can be achieved by only revising a part of the program.

Further, according to the argument retrieval RDB system of claim 3 of the present application, the user name, the authority name and the resource name permitted to the user are registered in the data dictionary, and the database management system stores the database. You can prompt for a user name before using it, and give that user only the privileges and resources that are registered in the data dictionary.

As a result, even when a large number of users use the same relational database system, it is possible to give individual users individual privileges and accessible resources.

That is, even when the relational database system is opened to a wide range of general users by the structure described in claim 1 or 2, the soundness of the relational database system is ensured and the confidentiality of information is protected. It can be achieved enough.

[Brief description of drawings]

FIG. 1 is a block diagram showing the configuration of an argument retrieval RDB system according to the present invention.

FIG. 2 is a flowchart showing the flow of processing in the argument retrieval RDB system according to the present invention, which allows a user to freely define tables / items and process data.

FIG. 3 is a flow chart showing the flow of processing in the argument retrieval RDB system according to the present invention which gives usable authority and accessible resource according to each user.

FIG. 4 is a block diagram showing a configuration of a conventional relational database system.

[Explanation of symbols]

 1 Relational Language 2 Database Management System 3 Relational Data Model 4 Relational Database 5 Information Processing Device 6 Application Program 7 Table 8 Items 9 Lines 10 Argument Retrieval RDB System 11 Data Dictionary

Claims (3)

[Claims] 1. A data dictionary that defines non-coded table names and item names, coded table names and item names that correspond to each other, and a database management system that executes data processing commands in a relational language. Enter the non-coded table name and item name included in the processing instruction entered by the user, search the data dictionary, and code the corresponding non-coded table name and item name. Stores a database management system that acquires the specified table name and item name, substitutes the coded table name and item name in the arguments of the processing command, and executes the table, and a table that has a data structure of records and items Relational data model, definition of table and item names for the data dictionary, and for the database management system And processing instructions, the argument retrieval RDB system characterized by having a data input for the relational data model, and a processing unit that performs the output of processing results by the database management system. 2. The data dictionary is configured so that a new table name and item name code can be added to a ready-made table name / item name code system to be defined, and the database management system is a user system. The table name / item name in the processing command input by the user is retrieved from the data dictionary, and the table name / item name of the ready-made table and the additional table name / item name are handled in the same manner and processed. The argument retrieval RDB system according to claim 1, which is characterized in that. 3. A data dictionary for defining a user name, an authority name permitted to the user, and a resource name accessible to the user, and a database management for executing a data processing instruction in a relational language. In the system, the user is first requested to enter a user name, the data dictionary is searched for the user name entered by the user, the authority name permitted to the user and the resource name accessible to the user. And a relational data model for storing a table having a data structure of records and items, and a process for the database management system. Enter the command, user name, and the relational Arguments search RDB system characterized by having a data input for Tamoderu, and a processing unit that performs the output of processing results by the database management system.