GB2172130A

GB2172130A - Method and system for managing view

Info

Publication number: GB2172130A
Application number: GB08605657A
Authority: GB
Inventors: Sadayuki Hikita; Suguru Kawakami; Hiromi Haniuda; Akifumi Sakamoto; Hideki Yamamoto
Original assignee: Oki Electric Industry Co Ltd
Current assignee: Oki Electric Industry Co Ltd
Priority date: 1985-03-07
Filing date: 1986-03-07
Publication date: 1986-09-10
Also published as: JPH0554140B2; GB2172130B; GB8605657D0; JPS61204733A

Abstract

Information to define a view (134) for simplifying relational operations in a database (132) is distributed to individual local database management systems (131) in a corresponding computer (136) handling queries. Upon a query on the view (134) from the application program (138) running in the computer (136) said query request is processed in the computer (136) to comprise a request to process within the view 134 defined as the set of base tables (133-1) ,..., (133-n). Thus, each database management system (131) conducts relational operations on the requested view (134) contained within the database (132), in conformity with said processing request. <IMAGE>

Description

SPECIFICATION Method and system for managing view DESCRIPTION The present invention relates to a view management system capable of reducing load imposed on a relational database management system in a view management mechanism of the relational database management system.

In a general relational database management system, a database consists of tables, each table being an assembly of records, each record being composed of several fields. Processing for the database (referred to as database processing) is effected by a combination of relational operations associated with the table group, named an query.

Such a database management system is disclosed in for example "A HISTORY OF SYS TEM R AND SQL/DATA SYSTEM". Proceedings of Seventh International Conference On Very Large Data Bases, IEEE Computer Society Press 1981 pp459-460, wherein the database management system has a translating mechanism (referred to as a view management system) for translating, upon processing a query to a database, a query on a virtual table (referred to as a view) prepared by applying several relational operations to base tables to a query on the base tables and thereby a user can profitably employ the database with ease by effecting a simple query on the view instead of a complicated query yielded by combining relational operations for specific tables.

For example, let us suppose a database concerning university students. The data base comprises tables composed of STUDENTS, COURCES, and TAKES, as shown in Fig. 5.

STUDENT&num;, NAME, AGE, SEX, and so on in each table are respectively fields of records constituting each table. STUDENT&num; signifies the school register number of a student, NAME the name of the student, AGE age, SEX the distinction of sex, COURCE&num; a subject number, C-NAME a subject name, UNITS units for the subject, TEACHER a teacher in charge, and GRADE a student's grade. In addition, each record, for example (0001, Brown, 18, M) in the table STUDENTS means that a student having the school register 0001 is a woman named Brown, 18 years. Moreover, (0005, Computing I, 1, Ellis) for example means that a subject number 0005 has the subject number of Computing I whose unit is 1 and whose teacher in charge is Ellis. Likewise, (0001, 0005,A) for exemple in the table TAKES means that a student of a school register 0001 takes a subject 0005 and the result of its examination is A.Then a view is defined by a request to define the view as shown in Fig. 6. Namely, the request written by for example an SQL language (by IBM) defines a virtual table, i.e., a view by combining three tables STUDENTS, TAKES COURCES under the condition that the field STUDENT&num; of the table STUDENTS is equal to the field STU DENT&num; of the table TAKES, and the field COURCE&num; of the table COURCES, conducting a restrict operation under the condition that C NAME is computing I, whereby effecting a projection operation where the fields are selected as being two of NAME and GRADE.

Furthermore, in the above definition, the name of the view is C1, which view (virtual table) has two fields named NAME and GRADE (corresponding respectively to a field NAME in the table STUDENTS and a field GRADE in the table TAKES). Here, a retrieval estimates the name of a student who takes a course in the subject Computing I and the result of its examination is supposed to be effected to the table group STUDENTS, COURCES, and TAKES. First, in effecting the above retrieval without use of the view, it can be done by a query shown in Fig. 7.

There, the query on join, restrict, and project operations for the three tables is illustratively shown. Then, in effecting the above retrieval with use of the view C1 described above, only a retrieval on the C1 enables the above retrieval to be executed.

Fig. 2 is a block diagram exemplarily showing the hardware of a prior view managing system. A view 214 is defined with respect to a table group 213 stored in a database 212. For example, a view definition 221-1 defines a view with respect to tables 213-1, 213-2, 213-3, and a view definition 221-2 difines another view with respect to tables 213-2, 213-3, ---, 213-i. In such a way, a view definition 221-m difines a view with use of a virtual table prepared by applying relational operations to tables 213-j, 213-k, 213-1.

A database management system 211 and computers 216-1, 216-2 ... are interconnected via communication channel 215. A query to the database management system 211 via an application program 217 on the view 214 in the computer 216-1 is supplied to the database management system 211 via a DBMS (database management system) access managing means 218, and after its syntax is analyzed by a query analyzer 219, translated by a view manager 220 from the query on the view 214 being a virtual table to a query on the base tables 213 based on for example the view definition 221-1, and thereafter allows database processing to be effected for the tables 213 by a database manager 222.

Hereupon, for the transformation from the query on the view to that on the base tables, if for example the view has been defined by a request to define the view shown in Fig. 6 and a query shown in Fig. 9 is effected on the view C1, then the view manager 220 transforms the query to a query as shown in Fig. 10. However, provided that the database management system is allowed to have the view manager as described above, particularly many users conduct a query with use of a view with respect to a database, or provided that capability of the database management system is not satisfactory, the above processing in the view manager is a severe burden on the whole database processing. Accordingly, the prior view management system suffers from the deterioration of a processing speed over the whole database management system.

It is an object of the present invention to reduce a load imposed on view management in processing of a query from computers by a database management system by distributing the roll of the view manager to the database management system and the computers utilizing the view manager.

To achieve the above object, a view managing system includes a data base composed of tables, computers each requesting processing on the data base, and a data base managing system for delivering the processed result and the processing state to the computers upon the processing request from the computers.

The database management system has a first view manager for executing a procedure to define a view and store the view definition in the database as well as a procedure to read the view definition stored in the database based on a request to read the view definition from the computer, and a database manager for executing relational operations processing request for the database from the computer.

In addition, the computer has a second view manager for executing a procedure to read and hold the view definition from the first view manager as well as a procedure to trans- form a processing request for the view to a processing request for the tables based on the read view definition. The above and other objects, features and advantages of the present invention will become more apparent from the following description when taken in conjunction with the accompanying drawings in which a preferred embodiment of the present invention is shown by way of illustrative example.

Figure 1 is a block diagram exemplarily showing a first embodiment of a view management system according to the present invention.

Figure 2 is a block diagram illustrating a prior view management system, Figure 3 is a flowchart illustrating the oper ation of the first embodiment shown in Fig. 1, Figure 4 is a block diagram showing a second embodiment of a view management system according to the present invention, Figure 5 is an example of tables in the second embodiment, Figure 6 is an example of an request to define a view for the tables of Fig. 5, Figure 7 is an example of a query request without a view, Figure 8 is a first example of a query request for a view, Figure 9 is a second example of a query request for a view, Figure 10 is an example of transforming a query on the view C1 of Fig. 7 (Fig. 9) to that on bases group, Figure 11 is an example of a query request without a view for tables stored in multiple databases, Figure 12 is an exmaple of a request to define a view for tables stored in multiple databases, and Figure 13 is an example of query on a view defined for tables on multiple databases.

In Fig. 1, illustrating a first embodiment of a view management system according to the present invention, designated at 131, is database management system (hereinafter referred to as DBMS), 132 is a database, and 136 is a computer. The database management system 131 includes a first view manager 137b and a first query analyzer 141. The database 132 has views 134 defined on any combination of tables 133-1, 133-2, ---, 133-n, and a table group 133, and view definition 135-1, 135-2, 000, 135-m corresponding to the respective views. The computer 136 is connected with the database management system 131 via a communication channel 143 and has a second view manager 137a, an application program 138, a second guery analyzer 139, and a database management system access manager.

Then, a flow of the data processing of Fig.

1 will be described by making use of a flowchart of Fig. 3.

(A) Definition of a view.

Step 300: A request to define a view is issued from the application program (AP).

Step 301: The processing request is analyzed by the second query analyzer 139.

Step 305: The view defining request is supplied by the view manager 137a to the database management system (DBMS) 131 via a database management system access manager 140.

Step 311: The processing request is analyzed by the first query analyzer 141.

Step 312: The first view manager 137b defines the view in conformity with the view defining request, and the view definition is stored in the view definition 135 of the database 132 (effected via the database manager 142).

Step 315: The processing status is returned to the computer 136. Namely, information indicative of completion of storing of the view definition is returned to the computer 136 in the order of the database manager 142, the first view manager 137b, the first query analyzer 141.

Step 306: It is decided what a request from the application program is. Namely, the processing status returned from the above Step 315 is decided to be the view defining request by the second view manager 137a via the database management system access manager 140.

Step 307: The processing status is returned to the application program 138.

(B) Read of the view.

Step 300: A read request for the view definition is provided from the application program 138.

Step 301: The processing request is analyzed (effected by the second query analyzer 139).

Step 303: It is decided that the second view manager 137a has already held view definition for which a read request is presented. If the information has already been read, the control advances to the Step 307, processing (B)-(10) described later, and if not yet read, the control advances to Step 305 described below.

Step 305: The request to read the view definition is supplied to the database management system (DBMS) 131.

Step 311: The request is analyzed by the first query analyzer 141.

Step 314: The view definition is read by the first view manager 137b. This is effected from the view definition 135 of the database 132 via the database manager 142.

Step 315: The read view definition and the processing status are returned to the computer 136 in the order of the database manager 142 the first view manager 137b the first query analyzer 141.

Step 306: It is decided what the request from the application program is. Namely, the read view definition returned from the above Step 315 and the processing status are decided to be view definition by the view manager 137a via the database management system access manager 140.

Step 309: The read view information is held by the second view manager 137a.

Step 307: The processing status is returned to the application program 138.

(C) Processing for query.

Step 300: A query is issued from the application program 138.

Step 301: The processing request is analyzed by the second query analyzer 139.

Step 302: Whether or not the query is on the view is decided by the second view manager 137a. Unless on the view, the control advances to the Step 305 of (C)-(5) described later, or if on the view, advances to the next Step 304.

Step 304: The query on the view is transformed to that on the base table query 133 by the second view manager 137a.

Step 305: A query is issued from the second view manager 137a to the database management system (DBMS) 131 via the database management system access manager 140.

Step 311: The processing request is analyzed by the first query analyzer 141.

Step 313: A query on the base table group is sent from the first query analyzer 141 to the database manager 142 in which relational operations for the base table group are executed.

Step 315: The queried result and the processing status are returned to the computer 136 in the order of the database manager 142 the first query analyzer 141.

Step 306: The second view manager 137a decides what the request from the application program 138 is.

Step 308: The processing status and the queried result are returned to the application program 138.

As described above, the processing is completed.

In ordinary processing, a view is first defined prior to the execution of the application program. In addition, the view definition is read upon initialization of the application program and held by the view manager, and any query from the application program thereafter is transformed based on the view definition.

The Steps 300 to 310 are processed by the computer 136 while the Steps 311 to 315 processed by the database management system 131.

As shown in Fig. 4, illustrating a second embodiment of the view management system according to the present invention, a multiple database management systems 451-1, 452-2, and a computer 452 are interconnected by communication channels 453. View definition 457-1 for a view 456 defined for a table data group 455-1-1, .., 455-1-n and 455-2-1, ., 455-2-n stored in the database management systems 451-1 and 451-2 has been previously held by a view manager 458a via a view manager 458b in the database management system 451-1 in accordance with a request from a view manager 458a of the computer 452 to the database management system 451-1. A query from the application program 459 on the view 456 by the database management systems 451-1, 451-2 is analyzed by a query analyzer 460.Thereafter, a query on the view 456 being a virtual table is transformed to that on the base tables 455-11, .., 455-1-n and 455-2-1 ..., 455-2-n based on the view definition retained by the view manager 458a and the database 454-1 which has been previously read out by the view managers 458a and 458b. Thereafter, a query is issued to the database management systems 451-1 and 451-2 by a database management system access manager 461, and database processing is effected on the table group 455-1-1, ., 455-1-n, 455-2-1, . ., 455-2-n through query managing means 462-1, 462-2 and database manager 463-1, 463-2.

For example, it is supposed that, in tables shown in Fig. 5, a table STUDENTS is stored to the database 454-1, and tables COURCES and TAKES are stored in the database 454-2.

Then, a retrieval "Estimate names of students taking a subject Computing I and the results of its examination" for the tables STUDENTS, COURCES, and TAKES is investigated.

First, in Fig. 11, where a query is shown in effecting the retrieval without use of a view, 454-1: STUDENTS S, 454-2: COURCES C, 454-2: TAKES T mean that the table STU DENTS is stored in the database 454-1 while the tables COURCES and TAKES are stored in the database 454-2, and S, C, and T are respectively employed as aliases.

Then, a case in which a view C2 has been defined as shown in Fig. 12 for the above three tables will be investigated. In the figure, 454-1: STUDENTS S, etc., describe names of the tables, databases in which the table names are stored, and aliases of the tables.

Upon effectuating the above retrieval with use of the C2, the retrieval can be effected by a query shown in Fig. 13. This query does not include information concerning a data base in which tables are stored during the query request, similary with the query on the view defined on the tables stored in the single database shown in Fig. 8.

According to the present invention, as described above, the roll of the view manager is destributed to the database management system and the computer with use of the database management system, whereby a load imposed on query processing of the database management system is reduced and the processing speed of the data management system is improved. Moreover, the view management system according to the present invention can be applied to realization of location transparency in which a user of the distributed database management system can query thereto without being conscious of a fact that the databases have been distributed to multiple sites constituting the distributed database management system.

Although certain preferred embodiments have been shown and described it should be understood that many changes and modifications may be made therein without departing form the scope of the appended claims.

Claims

1. A view managing method in a view management system including database composed of a data table group, a computer for effecting a processing request on said database, and a database management system connected with said computer for managing said database and thereby delivering, upon said processing request from said computer, a processed result and a processing status to said computer, said view managing method comprising the steps of:: (a) a step of allowing said computer to issue a view defining request to said database management system each time a database processing request is issued from an application program running in the computer, (b) a step of allowing said database management system to define a view from said tables in conformity with said view defining request from the computer, and store the view definition in said database, (c) a step of allowing said computer to request said database management system to read said view definition stored in the database (132) in response to said database processing request from said application program, (d) a step of allowing said database management system to read said view definition, stored in the database based on said read request, (e) a step of allowing said computer to transform a request to process a view in said database processing to a request to process tables based on the read view definition, and (f) step of allowing said database management system to effect relational operations for the tables under the management thereof in conforming with said transformed processing request and return the processed result and the processing status to the computer.

2. A view managing method according to claim 1, wherein multiple database management systems, are employed as said database management system, said database is a distributed database, and said view is defined based on the table group extending over multiple said databases.

3. A view management system including a database composed of a table group, a computer to effect a request to process said database, and a database management system connected with said computer for managing said database and delivering, upon the processing request from said computer, the processed result and the processing status to said computer, said view management system further comprising:: (a) a first means provided in the computer (136) for analyzing the processing request from an application program running in said computer and for thereby deciding whether or not a query should be effected on the view or whether or not the view definition should be read, (b) a second means provided in the computer for transforming when said processing request from said application program is a query on the view, said query to an query on said base tables in conformity with said analyzed result, (c) a third means provided in the said database management system for analyzing the processing request from the computer and for thereby deciding whether or not said processing request is to define the view, whether or not said processing request is a query on said tables and whether or not said processing request is to read the view definition, (d) a fourth means provided in said database management system for defining, when said decided result by said third means is a view defining request, the view from each of the tables based on said decided results and requesting said defined view to be stored in said database (132), while reading, when said decided result is a request to read the view definition, the view definition stored in said database and requesting the read view definition, to be delivered to the computer, and (e) a fifth means provided in said database management system for effecting relational operations for said tables in conformity with the request from the fourth means.

4. A view management system including a database composed of tables, a computer for effecting a request to process said database, and a database management system for delivering, upon said processing request by said computer, the processed result and the processing status, said database management system comprising a first view manager for executing a procedure to define a view and store the view definition in the database as well as a procedure to read the view definition stored in the database by a request to read the view definition from said computer and a database manager for effecting relational operation processing on the tables by a request to process the database from the computer, said computer having a second view manager for executing a procedure to read and hold the view definition, from said first view manager as well as a procedure to transform the request to process the view to a request to process the tables based on said view definition.

5. A data processing system comprising: a database including a plurality of tables processing means operative to request processing on the database; a database management system for delivering the result of processing of the data base to the processing means, said management system including a first view manager for executing a procedure to define a view and store the view definition in the database and to read the stored definition in response to a processing request from the processing means, a database manager for executing relational operations processing on the database in response to a request from the processing means, and a second view manager for executing a procedure to read and hold a view definition produced by the first view manager as well as a procedure to transform a processing request for the view to a processing request for the tables based on the read view definition.

6. A view management system substantially as hereinbefore described with reference to Figs. 1 and 3 of the accompanying drawings.

7. A view management systen substantially as hereinbefore described with reference to Fig. 4 of the accompanying drawings.