GB2269689A - Method for editing sub-query statements for searching a relational database - Google Patents

Abstract

The relational-database access method has an interactive primary manipulation-registration phase comprising the steps of selecting table names (s1), selecting column name(s) (s2), retrieving data with specified restrictions (s3), displaying data retrieved by execution of the steps above (s4), and storing the sequential manipulation of steps 1 to 3 together with a table name (s5). This is followed by an interactive query phase comprising the steps of selecting table name(s) (s6), selecting column name(s) (s7), retrieving relational database data under the condition that the data stored at step 5 is retrieved by accessing its table name and is used as a restriction (s8), and displaying a table retrieved from the execution of the steps 6 to 8 above (s9). Use of the stored data avoids the need for a user to enter SQL statements having complex sub-query statements. <IMAGE>

Description

SPECIFICATION Method for Editing Sub-Query Statements for Searching a Relational Database BACKGROUND OF INVENTION 1. Field of Invention The present invention relates to a method for editing sub-query statements for searching relational database by means of simple manipulation. The method allows interactive manipulation of sub-query statement instead of writing complex SQL (Structured Query Language) statements that are used for accessing relational databases, in a file.

2. Description of the Related Art A relational database stores a set of data in the form of a table. The table, in general, contains many different types of data and is identified by a table name which usually expresses the content of the table.

The table consists of a plurality of lines and a plurality of columns. The line is a horizontal division in the table and it is a collection of data elements related to a specific event and is also called record or tuple. The column is identified by a column name and is a vertical division in the table and indicates the each data element. The column is also called attribute. In the table, a plurality of lines in which data are same do not exist, in other words, having a double line in a table is prohibited. The order of lines or columns in a table can be arbitrarily selected. The features of a relational database is completely different from that of a file. The search of a relational database with more than one table can be freely performed, but a data change is permitted for only one table at a time.At this time, each data element can be updated, but data can be inserted or deleted by line. A collection of these types of tables are called a relational database.

SQL (Structured Query Language) is one of languages for accessing a relational database as described above. SQL is a command type language that can be used from a terminal under TSS (Time Sharing System) without the aid of a program, but SQL can also be used within a program. The normal host languages determine how the data should be processed, whereas SQL determines which data should be placed into a table. Normal host languages such as FORTRAN, COBOL and C can execute a process based on each record, but SQL can execute a process on a plurality of lines per command.

The basic format of a search of a relational database using SQL is described as follows: SELECT (A ] , FROM [B], WHERE [ C ] where A should describe one or more column names, B should describe one or more table names, and C should be described restrictions on the selection.

According to this SQL statement, desired data can be extracted from the relational database. In advanced applications of SQL programming, there are also the sub-query statements. The sub-query searches another table in the relational database by using data which was previously extracted from the relational database.

However, it takes time to create SQL statements when complex sub-query statements are included, therefore only experts in the art can easily edit SQL statements with sub-query statements.

SUMMARY OF THE INVENTION Accordingly, it is an object of the present invention to provide a method for editing SQL statements, including sub-query statements, by means of simple manipulation so that even non-experts in the art of editing SQL statements can easily and quickly perform database searches.

In order to solve the above described problems, a method for editing sub-query statements for searching a relational database by means of simple manipulation instead of using the complex SQL (Structured Query Language) that is normally used for searching a relational database.

Fig. 1 is a flow chart showing the basic procedure according to the present invention.

The method for editing the sub-query statements according to the present invention includes the primary manipulation registration phase and the searching phase.

The primary manipulation registration phase is the phase registering a new table name. After the registration of the table name, the registered table will be accessed in relational database at any time in the future.

The searching phase is the phase executing the subquery on the registered table.

In order to edit the SQL sub-query statements according to the present invention, a pointing device such as a touch pen, a mouse, or the like, and a keyboard are used as input devices, and a CRT monitor or the like is used as an output device to enter names into an interactive computer system of the present invention.

The names identify tables or columns in the database, and the term enter means to locate the cursor on a line on the CRT monitor and press the enter key on the keyboard or the like. Before editing the SQL sub-query statements, a plurality of databases and table names and column names thereof have to be stored in storage in the computer system.

Hereinafter, the number after s indicates step number.

The primary manipulation registration phase includes the following steps; (sl): Enter one or more table names into memory by means of input device by selecting the one or more table names from a plurality of the table names, shown on a monitor, each name corresponding to relational databases stored in storage.

(s2): Enter one or more column names into memory by means of the input device by selecting the one or more column names from a plurality of the column names, shown on the monitor, which can be seen in the each table having the table name.

(s3): Enter a restriction statement into memory by means of the input device after selecting the restriction statement from those shown on the monitor.

Data in each column are restricted by the restriction statement.

(s4): Display the data found as a result of the execution of the steps described above.

(s5): Store the steps above in the form of a file in storage with a registration name as shown on the monitor by means of the input device.

The query phase includes the following steps; (s6): Enter one or more table names into memory by means of input device after selecting the one or more table names from a plurality of the table names, stored in the storage, as shown on the monitor.

(s7): Enter one or more column names into memory by means of the input device after selecting the one or more column names from a plurality of said column names in the each table having its table name shown on the monitor.

(s8): Enter a restriction statement into memory by means of the input device, after determining the restriction statement, including the registration name registered in the primary manipulation registering phase shown on the monitor whereby data in the each column in the entered table and in the table extracted from the registration name are restricted in a condition.

(s9): Display extracted data generated as a result of execution of steps described above in the query phase.

The method has another feature in that the registration name registered in the primary manipulation registration phase may be stored in a binary form as a file thereby safely storing secret information.

-The mode of operation of the present invention is as follows.

Firstly, register a sequence of manipulations by means of an input device with a registration name for creating a SQL statement which accesses a relational database by selecting a plurality of table names and a column name with restriction statements then displays a table to be extracted in the primary manipulation registration phase.

Secondly, generate a table to be extracted by subquery statements using the registered name registered in the primary manipulation registration phase by means of input device and showing a display on the CRT monitor in the query phase thereby saving time and labor for editing the sub-query statements.

BRIEF DESCRIPTION OF THE DRAWINGS The above and related objects of the present invention will become apparent from a study of the following specification and drawings in which: Fig. 1 is a flow chart showing the basic procedure according to the present invention, Fig. 2A through Fig. 2G are explanatory views for the sub-query showing different kinds of tables, Fig. 3A, Fig. 3B and Fig. 3C are explanatory views showing samples of SQL statements, Fig. 4 is an explanatory view showing a procedure of an embodiment according to the present invention, and Fig. 5 is an explanatory view showing sample SQL statements performing the process of the present invention shown in Fig. 4.

DESCRIPTION OF PREFERRED EMBODIMENTS Before describing the preferred embodiments, an explanation will be given of a conventional art regarding a method for editing sub-query statements for searching a relational database.

To begin with, various kinds of tables generated from relationship databases will be explained.

~Fig. 2A through Fig. 2G are explanatory views for the sub-query showing different kinds of tables.

Each table shown in Fig. 2A through Fig. 2G has a corresponding table name T1, T2, T3, T4, T5, T6, T7 respectively.

Fig. 2A shows a table T1 having two columns of CUSTOMER'S NAME and CODE NO..

Fig. 2B shows a table T2 having two columns of CODE NO. and CAR NAME.

Fig. 2C shows a table T3 having two columns of ENGINE TYPE and MAKER'S NAME.

Fig. 2D shows a table T4 having two columns of CAR NAME and ENGINE TYPE.

Tables from T1 to T4 are called real tables in which desired data are previously written.

Fig. 2E shows a table T5 having two columns of CUSTOMER'S NAME and CAR NAME.

T5 is called a reference table because T5 refers to T1 and T2 and was created such that data in each column of CODE NO. is same.

Fig. 2F shows a table T6 having two columns of CAR NAME and MAKER'S NAME.

T6 is also called a reference table because T6 refers to T3 and T4 and was created such that data in each column of ENGINE TYPE is same.

Fig. 2G shows a table T7 having two columns of CUSTOMER'S NAME and MAKER'S NAME.

T7 is called an extracted table because T7 is extracted from T1, T3 and T4 to obtain two columns of CUSTOMER'S NAME and MAKER'S NAME. The extracted tables are extracted by the execution of SQL statements.

Hereinafter, SQL statements will be explained for the purpose of obtaining a desired extracted table.

Fig. 3A, Fig. 3B and Fig. 3C are explanatory views showing samples of SQL statements.

Fig. 3A shows SQL statements to obtain T5 consisting of two columns of CUSTOMER'S NAME and CAR NAME. As a result of executing the SQL statements described in Fig. 3A, T5 is extracted from T1 and T2 such that data in each column of CODE NO. is same.

Fig. 3B shows SQL statements to obtain T6 consisting of two columns of CAR NAME and MAKER'S NAME. As a result of executing the SQL statements described in Fig. 3B, T6 is extracted from T3 and T4 such that data in each column of ENGINE TYPE is same.

Fig. 3C shows SQL statements to obtain T7 consisting of two columns of CUSTOMER'S NAME and MAKER'S NAME. As a result of executing the SQL statements described in Fig. 3C, T7 was extracted from T1, T3 and T4. As can be seen, SQL statements with sub-query statements can be very complicated.

An embodiment according to the present invention will now be described hereinafter.

Fig. 4 is an explanatory view showing a procedure of an embodiment according to the present invention. The number shown on the upper left of each block indicates the step number which corresponds to the number in Fig. 1.

In order to edit the SQL sub-query statements according to the present invention, a pointing device such as a mouse or a touch pen, and a keyboard, are used as input devices, and a CRT monitor is used as an output device, to enter names into a interactive computer system of the present invention. The names identify tables or columns of databases, and the term enter means to locate the cursor on a line on the CRT monitor and press an enter key such as an enter key on a keyboard or the like.

Before editing the SQL sub-query statements, a database, a plurality of table names and column names have to be stored in storage in the computer system.

In the primary manipulation registration phase, there are five steps, from sl to s5, as below.

(sol) The first table name selecting step: Enter the table names DETAILS and CUSTOMER by means of a pointing device.

(s2): The first column name selecting step: Enter the column name PRODUCTNO. which can be found in the tables selected at step sl by means of the pointing device.

(s3): The first retrieval condition setting step: Enter CUSTOMERNAME and [=] by means of the pointing device, in order, then enter PFU by means of the keyboard and CRT monitor combination. This entry means that the customer name is PFU Limited. Next, enter DETAILS.CUSTOMERNO. and [ = ] by means of the pointing device, in order, then enter CUSTOMER.CUSTOMERNO. by means of the keyboard. This entry corresponds to; the SQL statement that SELECTs the CUSTOMERNO. column FROM tables of DETAILS and CUSTOMER WHERE [ DETAILS. CUSTOMERNO. [=] [ CUSTOMER. CUSTOMERNO. ] ; which means data in each column of CUSTOMERNO. in the tables of DETAILS and CUSTOMER are same. Thus a restriction, namely, data in each column in tables are in a desired condition, is applied to extract a table.

(s4): The first display step: SQL statements will be executed immediately after the steps from sl to s3 are finished, then a table can be extracted in accordance with the SQL statement, and the resultant table can be seen on the CRT monitor screen.

(s5): Manipulation registration step: Enter REGISTRATION by means of pointing device thereby registering the registered name of a sequential manipulation from step sl to step s3 above described. This name can be saved as a file name in storage by the normal procedure.

In the Query phase, there are four steps, from s6 to s9, as below.

(s6): The second table name selecting step: In order to create a new table, enter a desired table name PRODUCT by means of the pointing device.

(s7): The second column name selecting step: In order to get a new column, enter a desired column name PRODUCTNAME found in the table selected at step s6 by means of the pointing device.

(s8): The second retrieval condition setting step: Enter CUTOMERNO. and [ = ] by means of the pointing device, in order, then enter REGISTRATION which registered at step s5 by means of the pointing device.

(so): The second display step: SQL statements will be executed after input manipulation, in the steps s6 to s8, is finished, then a table can be extracted in accordance with the SQL sub-query statements, then the resultant table can be seen on the CRT monitor screen.

Fig. 5 is an explanatory view showing sample SQL statements performing the process of the present invention shown in Fig. 4.

As can be seen, complex SQL restriction statements are shown at the description after WHERE in Fig. 5. The statements shown are for the purpose of the sub-query.

Therefore unless otherwise programmers are trained, creating sub-query statements is very difficult to accomplish because of the complexity. On the other hand, as heretofore explained, the method for editing sub-query statements according to the present invention is very simple, so that the persons who are not familiar with SQL program can easily edit the sub-query statements.

The other feature of the present invention is that the registration data registered at the manipulation registration step s5 may be stored in the binary form and the extracted table may not be recognized by a third person. The conversion from the text form to the binary form can be easily accomplished by those who are skilled in the art. It should be noted that secret databases can be safely stored according to the present invention.

As heretofore explained, according to the present invention, the method for editing sub-query statements for searching a relational database can provide a means of simple manipulation instead of using complex SQL (Structured Query Language) statements for accessing relational databases. Thus even those who are not really familiar with SQL programming can easily edit the subquery statements by simple manipulation.

According to the other feature of the present invention, the registered data may be stored in the binary form to prevent the extracted table being recognized by a third person, so that secret information can be safely stored in the form of the table.

Claims (3)

CLAIMS:

1. A method for editing sub-query statements for searching a relational database having tables with rows and columns, the tables, rows and columns all having assigned names, comprising: a primary manipulation registration phase comprising the steps of, placing table names, at least one column name, and a primary restriction statement, into memory by manipulating an input device whereby a table is extracted from the relational databases and is shown on a monitor, and registering a registration name, for said table by manipulating said input device, into storage, and a query phase comprising the steps of, placing at least one table name, at least one column name and a secondary restriction statement into said memory, by manipulating said input device, in which said secondary restriction statement includes said primary restriction statement retrieved by said registration name whereby a table is extracted from said relational database and is shown on a monitor.

2. A method for editing sub-query statements for searching a relational database, comprising a primary manipulation registration phase and a query phase in an interactive computer system using an input device and an output device in combination; said primary manipulation registration phase comprises the steps of, entering one or more table names into a memory after selecting said one or more table names from a plurality of table names stored in storage, entering one or more column names into said memory after selecting said one or more column names from a plurality of column names in each said table, entering a restriction statement into said memory after determining said restriction statement by which data in each column are restricted, di#splaying a table extracted as a result of executing said steps described above, and storing a series of sequential manipulations of said steps into said storage in the form of a file with a registration name; and said query phase comprises steps of, entering one or more table names into said memory after selecting said one or more table names from a plurality of table names stored in said storage, entering one or more column names into said memory after selecting said one or more column names from a plurality of column names in each said table, entering a restriction statement into said memory after determining said restriction statement including said registration name registered in said primary manipulation registering phase, whereby data in each column in said entered table and in said table extracted from said registration name are restricted, and displaying a table extracted as a result of the execution of the steps described above in said query phase.

3. A method as claimed in claim 2, wherein said registration name registered in said primary manipulation registration phase may be stored as a file in binary form thereby safely storing secret information.