CA2626048A1 - Method for controlling a relational database system - Google Patents
Method for controlling a relational database system Download PDFInfo
- Publication number
- CA2626048A1 CA2626048A1 CA002626048A CA2626048A CA2626048A1 CA 2626048 A1 CA2626048 A1 CA 2626048A1 CA 002626048 A CA002626048 A CA 002626048A CA 2626048 A CA2626048 A CA 2626048A CA 2626048 A1 CA2626048 A1 CA 2626048A1
- Authority
- CA
- Canada
- Prior art keywords
- database
- relations
- data tables
- data
- access path
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Abandoned
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2448—Query languages for particular applications; for extensibility, e.g. user defined types
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2445—Data retrieval commands; View definitions
Abstract
The invention relates to a method for controlling a relational database system by executing, using a database language, a database inquiry in a relational database which comprises as the assigned data structure a plurality of data tables interlinked by relations, a table of said relations being available. A processable database instruction without relation-specific operations is established by indicating those data tables to which the processing functions should be applied and also indicating the order of the selected data tables which should be used for processing. An access path is determined which concretely specifies an access order to the database. For this purpose, a partial access path is calculated using the table of relations between two successive, selected data tables based on the relations existing between the two successive data tables. The access path is then put together from all the calculated partial access paths.
Description
Method for controlling a relational database system The invention concerns a method for controlling a relational database system by executing a database query in a relational database which contains, as an associated data structure, a plurality of data tables interconnected through relations, by using a database language, with a table of the relations being provided, and with a processabie database statement being created, which does not contain any relation-related operations, and an access path being determined, which concretely states the order of access to the database.
The concept of a relational database is characterized by increased user-friendliness, the separation between program and data, the table structure, and the integrity of data through the reduction of redundancies when compared to other databases.
Several attempts have been made to develop languages for database queries based on the model of a relational database, and of these, SQL has proven to be the most useable so far. All other approaches, such as OLAP, OQL and TSQL have meanwhile been integrated into the SQL standard.
US 2005/0076045 Al relates to a method for structuring data in a relational database system to improve the handiing possibilities of a multidimensional database by offering the possibility to unite several items (VALUE HOLDING ITEMS and STRUCTURE ITEMS) of one dimension into a new item (STRUCTURE ITEM) of this dimension and at the same time using a method for generating optimized SQL
statements by intermediate storage of data in order to make up for the high loss of speed caused by other SQL statements for the same queries. It describes a way to hierarchically unite various items of one dimension for which combinations there are no aggregated values stored in the data table used. Other than the items existing with aggregated values (VALUE HOLDING ITEMS), the items existing without aggregated values (STRUCTURE ITEMS) may be selected freely according to the method described in this publication and may be changed or extended any time.
US 6 732 091 BI describes a method of processing database queries, for example for SQL, wherein the best possible access plan for an actual data structure is selected from a plurality of possible access plans for a given statement leading to the same result. The basis for this is a limited form of the table of relations allowing only NATURAL-JOIN links.
In practice, it has proven disadvantageous that queries only refer to a concrete data structure of the actually used database in its current state. Changing the data structure results in immediate changes in the query. The same query for one data structure may therefore look completely different for another data structure.
In many cases, the main element of an SQL query is formed by relation-oriented operations, such as projection, join or selection, or by set-oriented operations, such as union, intersection, and difference of sets. This virtually always implies the use of the formation of at least one Cartesian product of data tables and constraints, employing the available relation information.
The following Example 1 illustrates the influence of the data structure on the formulation of the query.
Example 1:
Output of all companies based in Vienna, Austria, sorted according to company name, company departments and contact persons Data structure 1:
Relations: companies <-> departments <-> contact_persons SELECT companies.*, departments.*, contact_persons.* ....................
processing function FROM companies, departments, contact_persons .............................
Cart. product; relation information WHERE (companies.company_ID = departments.company_#)............ relation information AND (departments.department_4D = contact_persons.department_#).... relation information AND (companies.city = Vienna") ................................................... processing function ORDER BY companies.name ............................................................... processing function Data structure 2:
Relations: companies <-> area_of business <-> departments <-> contact_persons SELECT companies.*, departments.*, contact_persons.*
......................processing function FROM companies, area_of business, departments, contact_persons... Cart.
product; relation information WHERE (companies.c_ID = area_of_business.c_#) ...........................
relation information AND (area of business.area_ID = departments.area_#) .....................
relation information AND (departments.d_ID = contact_persons.d_#) .. ............................
processing function ORDER BY companies.name .....................................................
.......... processing function As can be seen, relations depend on the given data structure and must be re-formulated for each database statement, resulting in the dependence on the data structure.
The aim of the invention is to create a method which eliminates the dependence of the database statement on the data structure and achieves simplifications for the user.
Another aim is to allow the reduction of the length of notation which considerably increases the clarity of queries.
It is a further aim of the invention to provide a method as mentioned above which allows the simultaneous access to different existing data structures by means of statements which have been written for only one specific data structure.
According to the invention, this is achieved by - the database statement created containing only processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, - indicating, in the table of relations, the relations as links between two data tables each via at least one key field in a manner known per se, - determining the access path by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, - introducing relation-oriented operations into the database statement by means of the access path, which has been determined using the table of relations, producing an SQL statement which can be processed by any database system supporting the SQL standard.
By leaving out all relation-related operations, the database statement, on the one hand, is shortened and therefore more clearly arranged for the user, and, on the other hand, it is present in a form which is independent of the underlying data structure, which provides for advantages in terms of speed of creation and processing. By making use of the table of relations known for each data structure, the access path, which determines the order of access to the database, is computed and can either be used for the generation of an SQL statement or directly be used for processing.
The table of relations contains all relations corresponding to the data structure associated with the database queried and may furthermore contain freely generated relations.
The concept of a relational database is characterized by increased user-friendliness, the separation between program and data, the table structure, and the integrity of data through the reduction of redundancies when compared to other databases.
Several attempts have been made to develop languages for database queries based on the model of a relational database, and of these, SQL has proven to be the most useable so far. All other approaches, such as OLAP, OQL and TSQL have meanwhile been integrated into the SQL standard.
US 2005/0076045 Al relates to a method for structuring data in a relational database system to improve the handiing possibilities of a multidimensional database by offering the possibility to unite several items (VALUE HOLDING ITEMS and STRUCTURE ITEMS) of one dimension into a new item (STRUCTURE ITEM) of this dimension and at the same time using a method for generating optimized SQL
statements by intermediate storage of data in order to make up for the high loss of speed caused by other SQL statements for the same queries. It describes a way to hierarchically unite various items of one dimension for which combinations there are no aggregated values stored in the data table used. Other than the items existing with aggregated values (VALUE HOLDING ITEMS), the items existing without aggregated values (STRUCTURE ITEMS) may be selected freely according to the method described in this publication and may be changed or extended any time.
US 6 732 091 BI describes a method of processing database queries, for example for SQL, wherein the best possible access plan for an actual data structure is selected from a plurality of possible access plans for a given statement leading to the same result. The basis for this is a limited form of the table of relations allowing only NATURAL-JOIN links.
In practice, it has proven disadvantageous that queries only refer to a concrete data structure of the actually used database in its current state. Changing the data structure results in immediate changes in the query. The same query for one data structure may therefore look completely different for another data structure.
In many cases, the main element of an SQL query is formed by relation-oriented operations, such as projection, join or selection, or by set-oriented operations, such as union, intersection, and difference of sets. This virtually always implies the use of the formation of at least one Cartesian product of data tables and constraints, employing the available relation information.
The following Example 1 illustrates the influence of the data structure on the formulation of the query.
Example 1:
Output of all companies based in Vienna, Austria, sorted according to company name, company departments and contact persons Data structure 1:
Relations: companies <-> departments <-> contact_persons SELECT companies.*, departments.*, contact_persons.* ....................
processing function FROM companies, departments, contact_persons .............................
Cart. product; relation information WHERE (companies.company_ID = departments.company_#)............ relation information AND (departments.department_4D = contact_persons.department_#).... relation information AND (companies.city = Vienna") ................................................... processing function ORDER BY companies.name ............................................................... processing function Data structure 2:
Relations: companies <-> area_of business <-> departments <-> contact_persons SELECT companies.*, departments.*, contact_persons.*
......................processing function FROM companies, area_of business, departments, contact_persons... Cart.
product; relation information WHERE (companies.c_ID = area_of_business.c_#) ...........................
relation information AND (area of business.area_ID = departments.area_#) .....................
relation information AND (departments.d_ID = contact_persons.d_#) .. ............................
processing function ORDER BY companies.name .....................................................
.......... processing function As can be seen, relations depend on the given data structure and must be re-formulated for each database statement, resulting in the dependence on the data structure.
The aim of the invention is to create a method which eliminates the dependence of the database statement on the data structure and achieves simplifications for the user.
Another aim is to allow the reduction of the length of notation which considerably increases the clarity of queries.
It is a further aim of the invention to provide a method as mentioned above which allows the simultaneous access to different existing data structures by means of statements which have been written for only one specific data structure.
According to the invention, this is achieved by - the database statement created containing only processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, - indicating, in the table of relations, the relations as links between two data tables each via at least one key field in a manner known per se, - determining the access path by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, - introducing relation-oriented operations into the database statement by means of the access path, which has been determined using the table of relations, producing an SQL statement which can be processed by any database system supporting the SQL standard.
By leaving out all relation-related operations, the database statement, on the one hand, is shortened and therefore more clearly arranged for the user, and, on the other hand, it is present in a form which is independent of the underlying data structure, which provides for advantages in terms of speed of creation and processing. By making use of the table of relations known for each data structure, the access path, which determines the order of access to the database, is computed and can either be used for the generation of an SQL statement or directly be used for processing.
The table of relations contains all relations corresponding to the data structure associated with the database queried and may furthermore contain freely generated relations.
One possible embodiment of the method according to the invention may consist in that, for the purpose of determining partial access paths, the table of relations is read into a graph based on the theory of graphs, and the partial access paths are computed using the thus formed graph. This means that the determination of the access path according to the invention can be carried out in a simple way in a manner known per se.
A further mode of carrying out the invention may be to introduce relation-oriented operations in the database statement by means of the access path. The relation-oriented operations introduced may be such as to allow the conversion of the database statement, developed according to invention, into a valid SQL
statement by means of a program, thereby sparing the user the effort of creating and entering the entire SQL database statement by him or herself, in particular when it comes to complex queries.
According to an alternative embodiment of the inventive method, the determined access path, which provides references to data tables step by step, may be separately indicated in the database statement, and for accessing the database, these references to the data tables are followed step by step. Here, the processable database statement and the determined access path are both used directly for the query.
In the case of different data structures, according to another embodiment of the invention, the conversion of a database statement which is valid for one data structure into one that is valid for another data structure may be achieved by - eliminating, from a database statement for a first database having a first data structure associated therewith, the associated first relation-oriented operations, - introducing second relation-oriented operations into the database statement which has been cleared of the first relations, said second relation-oriented operations corresponding to a second data structure which is associated with a second database, - executing the query on the basis of the database statement which includes the second relation-oriented operations by determining the partial access paths, using the associated table of the second relations, and assembling them to obtain an access path.
For large-scale users in particular, this enables the cost-efficient merging of existing query statements for a particular data structure with statements for a different data structure.
All above-mentioned embodiments of the inventive method are similarly suited for implementing a relational database system comprising a computer system with a relational database, a data processing unit and a memory, said data processing unit working according to the inventive method.
Such a computer program may exist in any form, but in particular as a computer program product available on a computer-readable medium, such as floppy disk, CD
or DVD, having computer program encoding means, wherein a computer is caused by the program to execute the method in order to create a data carrier or electronic carrier signal according to invention after the computer program has been loaded.
However, it is also possible to have it as a computer program product which includes a computer program on an electronic carrier signal, wherein a computer is caused by the program to execute the method according to the invention after the computer program has been loaded.
The aim of the invention is therefore also achieved by providing a data carrier or, equivalently thereto, an electronic carrier signal to be read into a relational database system, wherein the database statement present on the data carrier is created without any relation-related operations by only indicating processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, indicating, in the table of relations, the relations as links between two data tables each via at least one key field in a manner known per se, and the data carrier containing an access path which is determined by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, wherein relation-oriented operations are introduced into the database statement by means of the access path, which has been determined using the table of relations, producing an SQL
statement which can be processed by any database system supporting the SQL standard.
A further embodiment of the inventive data carrier including database statements may consist in that an access path, determined according to the method of the invention, has already been introduced into the database statements and is used to control the database system through the database statement for accessing the relational database.
Alternatively, the data carrier intended to be read into a relational database system according to the invention may be such as to have the access path, which is used to control the database system through the database statement for accessing the relational database, indicated separately on the data carrier, with the access path providing references to data tables step by step and these references to the data tables being followed step by step in order to access the database.
Furthermore, the invention concerns a computer program having instructions which are adapted to execute the method according to the invention.
-6a-In addition, the invention relates to a computer program product having a computer-readable medium with computer program encoding means, wherein a computer is caused by the program to execute the method according to invention after the computer program has been loaded.
Lastly, the invention also relates to a computer program product having a computer program on an electronic carrier signal, wherein a computer is caused by the program to execute the method according to the invention after the computer program has been loaded.
The invention will now be explained in detail making reference to the exemplary applications which are illustrated in the drawings, in which:
(continued on page 7 of the original description) -6b-Fig. 1 is a schematic diagram of an ordered tree for the application of the inventive method to an example of a query (Example 1);
Fig. 2 is a schematic diagram of an ordered tree for the determination of the access path by means of the inventive method in another example of a query (Example 2);
Fig. 3 is a schematic diagram of an exemplary table of relations for a first data structure;
Fig. 4 is a schematic diagram of an exemplary table of relations for a second data structure; and Fig. 5 is an extended table of relations for another example for the application of the inventive method.
The main tasks of a database system are to store and to manage data and to provide data through database queries.
The invention relates to relational database systems which enable access to data stored in a database by using a database language. In the exemplary embodiments shown, the widely employed database language SQL is used, however, the invention is not limited to use thereof.
Every relational database includes an associated data structure in the form of a plurality of data tables which are interconnected through relations. Data tables are to be understood as data organized in rows and columns as is illustrated hereafter by the exemplary Tables 1 to 10.
In this specification, the term "row relation" designates each row in a table, i.e. for Table 1 "companies" (company_ID, name, street, ZIP, city, no._of staff) and for Table 2 "departments" (department_ID, company_#, department_name), for example. The links between two data tables via at least one key field, for example 1:n; companies (company_ID) <-> departments (company #) are referred to as "relations". The column "company_ID" in the "companies" table (Table 1) is a primary key for which there can be any number of values in the "departments" table (Table 2) in the "company_#" column.
table_1: companies company_ID name street ZIP city no._of staff 1 Bauer GmbH Street 1 1010 Wien 59 2 Muller GmbH Street 2 1020 Wien 50 3 Schneider Street 3 2000 Stockerau 98 GmbH
4 Hannes OEG Street 4 8750 Fohnsdorf 5 table_2: departments department_ID company_# department_name 1 1 marketing 2 1 human resources 3 1 research 4 2 marketing 2 research 6 3 human resources -~-table_3:contact_persons contact_person_ID department_# last name t"irst_name I 1 Geing Josef 2 1 Umdau Peter 3 1 Graf Elli 4 2 Pleit Hannes 2 Schon Gerda 6 3 Toll Maria 7 4 Stang Peter table 4: contacts contact_ID contact_person_# contact_date conversation_content established_by 1 1 2003-03-01 callback requested 2 2 2002-05-02 documents are important 3 3 2004-07-04 not available 4 6 2003-02-13 will get back to us 5 6 2002-09-24 showed great interest 6 2 2004-12-08 will come tomorrow at 2 p.m.
7 1 2002-10-16 currently no budget available 8 3 2004-10-17 has lost the documents 9 7 2003-03-19 always takes time if necessary table 5: events event_ID contact_person_# event_date no._of persons table 6: events status status_1D event # status_date status_type_# option_date table_7: additional_status_types status_type_ID title 1 booked 2 option granted 3 paid 4 reminder sent no show 6 postponed table 8: invoices invoice ID event # invoice date invoice #
l 1 16.07.2004 4 2 2 18.09.2004 55 3 3 12.03.2003 6 4 4 18.04.2004 7 5 5 19.11.2003 8 6 6 21.03.2004 9 7 7 02.12.2004 10 table 9: invoice line items invoice line item ID invoice # line item # line item total 1 1 1 Ã 100.00 2 1 2 Ã 12.00 3 1 3 Ã 354.00 4 2 1 Ã 6.00 2 3 Ã 9.00 6 2 5 Ã 98.00 7 3 6 Ã 7.00 8 3 2 Ã 6.00 9 4 1 Ã 54.00 4 4 Ã 456.00 table 10: additional invoice line items titles line item ID line item title 1 set meals 2 food 3 beverages 4 music 5 decoration 6 venue rental For each data structure, a table of relations which contains all relations corresponding to the data structure associated with the database queried and optionally also freely generated relations is provided.
table 11: Table of Relations for Fig. 1 From Table From Column To Table To Column Type of Relation companies company_ID departments company_# 1: n departments department_ID contact_persons department # 1:n contact_persons contact_person_ID contacts contact_person_# 1:n contact_persons contact_person_ID events contact_person_# 1:n events eventID events status event # 1:n additional_status_types stafus_type_ID events_status status_type_# 1:n events event ID invoices event # 1:n invoices invoice ID invoice line items invoice # 1:n additional_invoice_line_ line_item_ID invoice_line_items line_item_# 1:n items titles For the above-mentioned data structure of the data tables 1 to 10 (Table 1 to 10), it is possible to formulate, for example, the following request which demonstrates how complex the problems may be that the method according to the invention is able to solve. The following example refers to the above data structure.
Example 2:
List all companies, including their this year's total event turnover, with which at least ten contacts have been established this year and whose marketing departments have booked at least three events in this year with more than 50 Euros turnover for food and beverages.
A further mode of carrying out the invention may be to introduce relation-oriented operations in the database statement by means of the access path. The relation-oriented operations introduced may be such as to allow the conversion of the database statement, developed according to invention, into a valid SQL
statement by means of a program, thereby sparing the user the effort of creating and entering the entire SQL database statement by him or herself, in particular when it comes to complex queries.
According to an alternative embodiment of the inventive method, the determined access path, which provides references to data tables step by step, may be separately indicated in the database statement, and for accessing the database, these references to the data tables are followed step by step. Here, the processable database statement and the determined access path are both used directly for the query.
In the case of different data structures, according to another embodiment of the invention, the conversion of a database statement which is valid for one data structure into one that is valid for another data structure may be achieved by - eliminating, from a database statement for a first database having a first data structure associated therewith, the associated first relation-oriented operations, - introducing second relation-oriented operations into the database statement which has been cleared of the first relations, said second relation-oriented operations corresponding to a second data structure which is associated with a second database, - executing the query on the basis of the database statement which includes the second relation-oriented operations by determining the partial access paths, using the associated table of the second relations, and assembling them to obtain an access path.
For large-scale users in particular, this enables the cost-efficient merging of existing query statements for a particular data structure with statements for a different data structure.
All above-mentioned embodiments of the inventive method are similarly suited for implementing a relational database system comprising a computer system with a relational database, a data processing unit and a memory, said data processing unit working according to the inventive method.
Such a computer program may exist in any form, but in particular as a computer program product available on a computer-readable medium, such as floppy disk, CD
or DVD, having computer program encoding means, wherein a computer is caused by the program to execute the method in order to create a data carrier or electronic carrier signal according to invention after the computer program has been loaded.
However, it is also possible to have it as a computer program product which includes a computer program on an electronic carrier signal, wherein a computer is caused by the program to execute the method according to the invention after the computer program has been loaded.
The aim of the invention is therefore also achieved by providing a data carrier or, equivalently thereto, an electronic carrier signal to be read into a relational database system, wherein the database statement present on the data carrier is created without any relation-related operations by only indicating processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, indicating, in the table of relations, the relations as links between two data tables each via at least one key field in a manner known per se, and the data carrier containing an access path which is determined by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, wherein relation-oriented operations are introduced into the database statement by means of the access path, which has been determined using the table of relations, producing an SQL
statement which can be processed by any database system supporting the SQL standard.
A further embodiment of the inventive data carrier including database statements may consist in that an access path, determined according to the method of the invention, has already been introduced into the database statements and is used to control the database system through the database statement for accessing the relational database.
Alternatively, the data carrier intended to be read into a relational database system according to the invention may be such as to have the access path, which is used to control the database system through the database statement for accessing the relational database, indicated separately on the data carrier, with the access path providing references to data tables step by step and these references to the data tables being followed step by step in order to access the database.
Furthermore, the invention concerns a computer program having instructions which are adapted to execute the method according to the invention.
-6a-In addition, the invention relates to a computer program product having a computer-readable medium with computer program encoding means, wherein a computer is caused by the program to execute the method according to invention after the computer program has been loaded.
Lastly, the invention also relates to a computer program product having a computer program on an electronic carrier signal, wherein a computer is caused by the program to execute the method according to the invention after the computer program has been loaded.
The invention will now be explained in detail making reference to the exemplary applications which are illustrated in the drawings, in which:
(continued on page 7 of the original description) -6b-Fig. 1 is a schematic diagram of an ordered tree for the application of the inventive method to an example of a query (Example 1);
Fig. 2 is a schematic diagram of an ordered tree for the determination of the access path by means of the inventive method in another example of a query (Example 2);
Fig. 3 is a schematic diagram of an exemplary table of relations for a first data structure;
Fig. 4 is a schematic diagram of an exemplary table of relations for a second data structure; and Fig. 5 is an extended table of relations for another example for the application of the inventive method.
The main tasks of a database system are to store and to manage data and to provide data through database queries.
The invention relates to relational database systems which enable access to data stored in a database by using a database language. In the exemplary embodiments shown, the widely employed database language SQL is used, however, the invention is not limited to use thereof.
Every relational database includes an associated data structure in the form of a plurality of data tables which are interconnected through relations. Data tables are to be understood as data organized in rows and columns as is illustrated hereafter by the exemplary Tables 1 to 10.
In this specification, the term "row relation" designates each row in a table, i.e. for Table 1 "companies" (company_ID, name, street, ZIP, city, no._of staff) and for Table 2 "departments" (department_ID, company_#, department_name), for example. The links between two data tables via at least one key field, for example 1:n; companies (company_ID) <-> departments (company #) are referred to as "relations". The column "company_ID" in the "companies" table (Table 1) is a primary key for which there can be any number of values in the "departments" table (Table 2) in the "company_#" column.
table_1: companies company_ID name street ZIP city no._of staff 1 Bauer GmbH Street 1 1010 Wien 59 2 Muller GmbH Street 2 1020 Wien 50 3 Schneider Street 3 2000 Stockerau 98 GmbH
4 Hannes OEG Street 4 8750 Fohnsdorf 5 table_2: departments department_ID company_# department_name 1 1 marketing 2 1 human resources 3 1 research 4 2 marketing 2 research 6 3 human resources -~-table_3:contact_persons contact_person_ID department_# last name t"irst_name I 1 Geing Josef 2 1 Umdau Peter 3 1 Graf Elli 4 2 Pleit Hannes 2 Schon Gerda 6 3 Toll Maria 7 4 Stang Peter table 4: contacts contact_ID contact_person_# contact_date conversation_content established_by 1 1 2003-03-01 callback requested 2 2 2002-05-02 documents are important 3 3 2004-07-04 not available 4 6 2003-02-13 will get back to us 5 6 2002-09-24 showed great interest 6 2 2004-12-08 will come tomorrow at 2 p.m.
7 1 2002-10-16 currently no budget available 8 3 2004-10-17 has lost the documents 9 7 2003-03-19 always takes time if necessary table 5: events event_ID contact_person_# event_date no._of persons table 6: events status status_1D event # status_date status_type_# option_date table_7: additional_status_types status_type_ID title 1 booked 2 option granted 3 paid 4 reminder sent no show 6 postponed table 8: invoices invoice ID event # invoice date invoice #
l 1 16.07.2004 4 2 2 18.09.2004 55 3 3 12.03.2003 6 4 4 18.04.2004 7 5 5 19.11.2003 8 6 6 21.03.2004 9 7 7 02.12.2004 10 table 9: invoice line items invoice line item ID invoice # line item # line item total 1 1 1 Ã 100.00 2 1 2 Ã 12.00 3 1 3 Ã 354.00 4 2 1 Ã 6.00 2 3 Ã 9.00 6 2 5 Ã 98.00 7 3 6 Ã 7.00 8 3 2 Ã 6.00 9 4 1 Ã 54.00 4 4 Ã 456.00 table 10: additional invoice line items titles line item ID line item title 1 set meals 2 food 3 beverages 4 music 5 decoration 6 venue rental For each data structure, a table of relations which contains all relations corresponding to the data structure associated with the database queried and optionally also freely generated relations is provided.
table 11: Table of Relations for Fig. 1 From Table From Column To Table To Column Type of Relation companies company_ID departments company_# 1: n departments department_ID contact_persons department # 1:n contact_persons contact_person_ID contacts contact_person_# 1:n contact_persons contact_person_ID events contact_person_# 1:n events eventID events status event # 1:n additional_status_types stafus_type_ID events_status status_type_# 1:n events event ID invoices event # 1:n invoices invoice ID invoice line items invoice # 1:n additional_invoice_line_ line_item_ID invoice_line_items line_item_# 1:n items titles For the above-mentioned data structure of the data tables 1 to 10 (Table 1 to 10), it is possible to formulate, for example, the following request which demonstrates how complex the problems may be that the method according to the invention is able to solve. The following example refers to the above data structure.
Example 2:
List all companies, including their this year's total event turnover, with which at least ten contacts have been established this year and whose marketing departments have booked at least three events in this year with more than 50 Euros turnover for food and beverages.
In the method according to the invention, it is substantially the following steps that are being executed:
1) Formulating a processable database statement without any relation-related operations 2) Determining an access path In detail, this is carried out as follows:
ad 1) Indicating processing functions and data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place.
SELECT ... ... ... ... ... processing function companies .......... data table to which the processing function is to be applied all columns (.*)...... columns to which the processing function is to be applied Indicating all columns of the "company" table Determining the sum of all line item totals from the associated column of the "invoice_line_item" table, restricting this to the event year (i.e. this year) Therefore, the creation of the database statement according to the invention resuits in:
SELECT companies.*, (SELECT SUM (invoice_line_items.line_item_total) WHERE (events.event date=date.year) ) WHERE (SELECT COUNT(contacts.") WHERE (contacts.contact_date=date.year) )> = 10 AND (SELECT COUNT(events.*) WHERE (departments.department_name= õmarketing") AND (SELECT SUM (invoice_line_items.line_item_total) WHERE (additional_invoice_Iine_items_titles.Iine_item titie IN ("food", "beverages")) ) >50 AND (events.event_date = date.year) ) >=3 [here, õdate.year" replaces BETWEEN 01-01 current year AND 12-31 current year]
This query contains only the tables to which the processing functions are to be applied, without any relations. Thereby, independence of any data structure is obtained, and at the same time, the length of notation is reduced, leading to increased clarity. For example, the usually formed Cartesian products which are restricted by relations are eliminated because the statement already contains all pieces of information which can be used in combination with the table of relations to determine an access path for the individual data structure, so that on the one hand, independence of the concrete data structure is given, and on the other hand, the entry of the query made by the user is shortened and limited to the essential, which in turn increases its clarity.
ad 2) Based on to the formulation of the problem to be solved, there appears a natural order of the data tables used as well as hierarchy levels in the query (statement), which are conditional on the sub-queries (SUBSELECT) to be processed and from which partial access paths are determined with the aid of the tabie of relations (Table 11) in order to generate an SQL statement for the respective data structure.
1) Formulating a processable database statement without any relation-related operations 2) Determining an access path In detail, this is carried out as follows:
ad 1) Indicating processing functions and data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place.
SELECT ... ... ... ... ... processing function companies .......... data table to which the processing function is to be applied all columns (.*)...... columns to which the processing function is to be applied Indicating all columns of the "company" table Determining the sum of all line item totals from the associated column of the "invoice_line_item" table, restricting this to the event year (i.e. this year) Therefore, the creation of the database statement according to the invention resuits in:
SELECT companies.*, (SELECT SUM (invoice_line_items.line_item_total) WHERE (events.event date=date.year) ) WHERE (SELECT COUNT(contacts.") WHERE (contacts.contact_date=date.year) )> = 10 AND (SELECT COUNT(events.*) WHERE (departments.department_name= õmarketing") AND (SELECT SUM (invoice_line_items.line_item_total) WHERE (additional_invoice_Iine_items_titles.Iine_item titie IN ("food", "beverages")) ) >50 AND (events.event_date = date.year) ) >=3 [here, õdate.year" replaces BETWEEN 01-01 current year AND 12-31 current year]
This query contains only the tables to which the processing functions are to be applied, without any relations. Thereby, independence of any data structure is obtained, and at the same time, the length of notation is reduced, leading to increased clarity. For example, the usually formed Cartesian products which are restricted by relations are eliminated because the statement already contains all pieces of information which can be used in combination with the table of relations to determine an access path for the individual data structure, so that on the one hand, independence of the concrete data structure is given, and on the other hand, the entry of the query made by the user is shortened and limited to the essential, which in turn increases its clarity.
ad 2) Based on to the formulation of the problem to be solved, there appears a natural order of the data tables used as well as hierarchy levels in the query (statement), which are conditional on the sub-queries (SUBSELECT) to be processed and from which partial access paths are determined with the aid of the tabie of relations (Table 11) in order to generate an SQL statement for the respective data structure.
The above-mentioned order and levels of hierarchy of the data tables used are preferably represented in the form of an ordered tree 7 (Fig. 1) which includes a root and nodes 11, 12, 13 and 14, with node 14 being a sub-node of node 13 .
As its information field 21, the root 10 of the ordered tree 7 contains all names of the data tables of the superordinate query only, for example in the form of a list or an array which is conveniently in the same order as indicated in the statement and optionally without duplicate data table names. For the root 10, the information field 21 contains the data table COMPANIES (Table 1). Sub-queries (SUBSELECT) und their subordinate sub-queries (SUBSELECT in SUBSELECT) are subordinate to the root 10 and listed in the tree-structure as nodes 11, 12, 13, 14. The information fields 22, 23, 24, 25 of the nodes 11, 12, 13, 14 contain the names of the data tables associated with the sub-queries which have been formulated in the respective SUBSELECT clause, also preferably in the form of a list or array. For node 13, these are the data tables EVENTS (Table 5), DEPARTMENTS (Table 2) and EVENTS
(Table 5) in its information field 24. According to the query formulation, the SUBSELECT clause of node 13 contains a further SUBSELECT, namely node 14, which, in turn, has the data tables INVOICE_LINE_ITEMS (Table 9) and ADDITIONAL_INVOICE_LINE_ITEMS_TITLES (Table 10) listed in its information field.
This tree 7 is run through in a pre-order run: root 10, node 11, node 12, node 13 and node 14.
As levels of hierarchy, the following can be represented:
Level 1: companies root 10, superordinate query Level 1.1: invoice_line items, events node 11, sub-query in the SELECT part of the main query Level 1.2: contacts, contacts node 12, sub-query in the WHERE part of the main query Level 1.3: events, departments, events node 13, sub-query in the AND part of the main query Level 1.3.1: invoice line items, additional invoice line items titles node 14, sub-query in the AND sub-query Next, the access path, which concretely prescribes the order of access of the database, is determined by computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and assembling the access path from all computed partial access paths.
Note that due to the omission of all product descriptions in the statement (FROM
part), the order of the data tables that has been formulated with the processing functions may lead to the double or multiple run-through of partial access paths, so that in the formation of a Cartesian product, the same data tables may erroneously be considered more than once, leading to a wrong result of the query.
By means of the access path, which has been determined using the table of relations shown above (Table 11), relation-oriented operations can now be introduced into the database statement formulated according to the invention (Example 2), producing the following SQL statement (the introduced operations are shown in italics) which can be processed by any database system supporting the SQL standard. One possible way of computing will be explained afterwards.
SELECT companies.*, (SELECT SUM (invoice_line_items.line_item_total) FROM departments, contact persons, events, invoices, invoice line items WHERE (companies. company_lD=departments. company_#) AND (departments. department ID=contact persons. department #) AND (contact_persons.contacf_person ID=events.confact_person_#) AND (events. e ventID=invoices.event #) AND (invoices.invoice invoice-ID line items.invoice #) AND (events.event_date=date.year) FROM companies WHERE (SELECT COUNT(contacts.*) FROM departments, contact persons, contacts WHERE (companies. company_ID=departments. company_#) AND (departments. department lD=contact persons.department #) AND (contact_persons.contact_person_ID=contacts.contact_person #) AND (contacts.contact_date = date.year) >= 10 AND (SELECT COUNT(events.*) FROM departments, contact_persons, events WHERE (companies. company_ID=departments. company_#) AND (departments. department 1D=contact_persons.department #) AND (contact_persons.contact_person ID=evenfs.contact_person #) AND (departments.department_name="marketing") AND (SELECT SUM(invoice_line_items.line_item_total) FROM invoices, invoice_line items, additional invoice line items titles WHERE (events.event ID=in voices. event #) AND (invoices. invoiceID=invoice line items.invoice #) AND(invoice line items.line item #=additional invoice_line items titles.line item ID) AND (additiona{_line_items tities.Iine_item titie IN (õfood","beverages") ) > 50) AND (events. eventdate=date.year) >=3 The length of the statement obtained this way compared to the length of the one that has been formulated in step 1) clearly demonstrates the advantages that can be obtained in terms of shortness and clarity and particularly in terms of independence of the data structure.
If the statement is stored as a query, the inventive notation and the generated notation can be used as such, re-computation no longer being necessary. A re-computation will only be carried out if the data structure and/or the statement itself is changed or modified. Thereby, the short period of time for the conversion into an SQL statement is eliminated. Further optimizations will be appreciated by those skilled in the art.
Another possibility is to separately indicate the determined access path, which provides references to data tables step by step, in the database statement, and to follow these references to the data tables step by step in order to access the database. This yields, for example:
WAY companies -> departments WHERE (companies.company_ID=departments.company_#) -> contact persons WHERE (departments.department ID=contact_persons.department #) -> events WHERE (contact persons.contact_person ID=events.contact_person #) -> invoices WHERE (events.event ID=invoices. event #) -> invoice line items WHERE (invoices. invoice/D=invoice_line items.invoice #) -> additional invoice line items titles WHERE (invoice tine items.line item #= additional invoice line items titles.line item ID) contact_persons -> contacts WHERE (contact_persons.contact_person ID=contacts.contact_person_#) SELECT companies.*, (SELECT SUM (invoice_Iine_items.line_item_total) WHERE (events.event date=date.year) ) WHERE (SELECT COUNT(contacts.*) WHERE (contacts.contact_date=date.year) )> = 10 AND (SELECT COUNT(events.*) WHERE (departments.department_name= õmarketing") AND (SELECT SUM (invoice_line_items.line_item_total) WHERE (additional_invoice_Iine_items_tities.iine_item_title I N ("food", "beverages")) ) >50 AND (events.event date = date.year) >=3 By this, the introduction into the statement is eliminated, and the access path provides step-by-step reference to the data tables. When accessing the database, these references are followed step by step.
One possible algorithm for determining the access path by means of an ordered tree as depicted in Fig. 1 will now be explained, making reference to a simple data structure in order to provide for the better understanding without the distraction of table names and the like. Note, however, that the algorithm mentioned hereunder is only one amongst many that may be used for the calculation.
In general, when determining the overall access path, doubly listed partial paths are deleted.
For example, the data tables provided are A, B, C, D, E, F, G, H.
The information field of a root of the ordered tree contains, for example: A, C, F, B.
This results in the following partial paths:
partial path AC: A B C
partial path CF: C D E F
partial path FB: F E D C B
The unadjusted overall access path is therefore: A B C D E F E D C B
As its information field 21, the root 10 of the ordered tree 7 contains all names of the data tables of the superordinate query only, for example in the form of a list or an array which is conveniently in the same order as indicated in the statement and optionally without duplicate data table names. For the root 10, the information field 21 contains the data table COMPANIES (Table 1). Sub-queries (SUBSELECT) und their subordinate sub-queries (SUBSELECT in SUBSELECT) are subordinate to the root 10 and listed in the tree-structure as nodes 11, 12, 13, 14. The information fields 22, 23, 24, 25 of the nodes 11, 12, 13, 14 contain the names of the data tables associated with the sub-queries which have been formulated in the respective SUBSELECT clause, also preferably in the form of a list or array. For node 13, these are the data tables EVENTS (Table 5), DEPARTMENTS (Table 2) and EVENTS
(Table 5) in its information field 24. According to the query formulation, the SUBSELECT clause of node 13 contains a further SUBSELECT, namely node 14, which, in turn, has the data tables INVOICE_LINE_ITEMS (Table 9) and ADDITIONAL_INVOICE_LINE_ITEMS_TITLES (Table 10) listed in its information field.
This tree 7 is run through in a pre-order run: root 10, node 11, node 12, node 13 and node 14.
As levels of hierarchy, the following can be represented:
Level 1: companies root 10, superordinate query Level 1.1: invoice_line items, events node 11, sub-query in the SELECT part of the main query Level 1.2: contacts, contacts node 12, sub-query in the WHERE part of the main query Level 1.3: events, departments, events node 13, sub-query in the AND part of the main query Level 1.3.1: invoice line items, additional invoice line items titles node 14, sub-query in the AND sub-query Next, the access path, which concretely prescribes the order of access of the database, is determined by computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and assembling the access path from all computed partial access paths.
Note that due to the omission of all product descriptions in the statement (FROM
part), the order of the data tables that has been formulated with the processing functions may lead to the double or multiple run-through of partial access paths, so that in the formation of a Cartesian product, the same data tables may erroneously be considered more than once, leading to a wrong result of the query.
By means of the access path, which has been determined using the table of relations shown above (Table 11), relation-oriented operations can now be introduced into the database statement formulated according to the invention (Example 2), producing the following SQL statement (the introduced operations are shown in italics) which can be processed by any database system supporting the SQL standard. One possible way of computing will be explained afterwards.
SELECT companies.*, (SELECT SUM (invoice_line_items.line_item_total) FROM departments, contact persons, events, invoices, invoice line items WHERE (companies. company_lD=departments. company_#) AND (departments. department ID=contact persons. department #) AND (contact_persons.contacf_person ID=events.confact_person_#) AND (events. e ventID=invoices.event #) AND (invoices.invoice invoice-ID line items.invoice #) AND (events.event_date=date.year) FROM companies WHERE (SELECT COUNT(contacts.*) FROM departments, contact persons, contacts WHERE (companies. company_ID=departments. company_#) AND (departments. department lD=contact persons.department #) AND (contact_persons.contact_person_ID=contacts.contact_person #) AND (contacts.contact_date = date.year) >= 10 AND (SELECT COUNT(events.*) FROM departments, contact_persons, events WHERE (companies. company_ID=departments. company_#) AND (departments. department 1D=contact_persons.department #) AND (contact_persons.contact_person ID=evenfs.contact_person #) AND (departments.department_name="marketing") AND (SELECT SUM(invoice_line_items.line_item_total) FROM invoices, invoice_line items, additional invoice line items titles WHERE (events.event ID=in voices. event #) AND (invoices. invoiceID=invoice line items.invoice #) AND(invoice line items.line item #=additional invoice_line items titles.line item ID) AND (additiona{_line_items tities.Iine_item titie IN (õfood","beverages") ) > 50) AND (events. eventdate=date.year) >=3 The length of the statement obtained this way compared to the length of the one that has been formulated in step 1) clearly demonstrates the advantages that can be obtained in terms of shortness and clarity and particularly in terms of independence of the data structure.
If the statement is stored as a query, the inventive notation and the generated notation can be used as such, re-computation no longer being necessary. A re-computation will only be carried out if the data structure and/or the statement itself is changed or modified. Thereby, the short period of time for the conversion into an SQL statement is eliminated. Further optimizations will be appreciated by those skilled in the art.
Another possibility is to separately indicate the determined access path, which provides references to data tables step by step, in the database statement, and to follow these references to the data tables step by step in order to access the database. This yields, for example:
WAY companies -> departments WHERE (companies.company_ID=departments.company_#) -> contact persons WHERE (departments.department ID=contact_persons.department #) -> events WHERE (contact persons.contact_person ID=events.contact_person #) -> invoices WHERE (events.event ID=invoices. event #) -> invoice line items WHERE (invoices. invoice/D=invoice_line items.invoice #) -> additional invoice line items titles WHERE (invoice tine items.line item #= additional invoice line items titles.line item ID) contact_persons -> contacts WHERE (contact_persons.contact_person ID=contacts.contact_person_#) SELECT companies.*, (SELECT SUM (invoice_Iine_items.line_item_total) WHERE (events.event date=date.year) ) WHERE (SELECT COUNT(contacts.*) WHERE (contacts.contact_date=date.year) )> = 10 AND (SELECT COUNT(events.*) WHERE (departments.department_name= õmarketing") AND (SELECT SUM (invoice_line_items.line_item_total) WHERE (additional_invoice_Iine_items_tities.iine_item_title I N ("food", "beverages")) ) >50 AND (events.event date = date.year) >=3 By this, the introduction into the statement is eliminated, and the access path provides step-by-step reference to the data tables. When accessing the database, these references are followed step by step.
One possible algorithm for determining the access path by means of an ordered tree as depicted in Fig. 1 will now be explained, making reference to a simple data structure in order to provide for the better understanding without the distraction of table names and the like. Note, however, that the algorithm mentioned hereunder is only one amongst many that may be used for the calculation.
In general, when determining the overall access path, doubly listed partial paths are deleted.
For example, the data tables provided are A, B, C, D, E, F, G, H.
The information field of a root of the ordered tree contains, for example: A, C, F, B.
This results in the following partial paths:
partial path AC: A B C
partial path CF: C D E F
partial path FB: F E D C B
The unadjusted overall access path is therefore: A B C D E F E D C B
As can be seen, E D C B are present twice and will accordingly be deleted.
The correct overall access path is therefore: A B C D E F
Assuming the existence of a tree representation as shown in Fig. 2, one of many possibilities of how to calculate the access path according to the inventive method and translate it into a SQL statement by carrying out the following steps will be described.
A data structure contains data tables A, B, C, D, E,........
According to the query on which the representation of Fig. 2 is based and which, for reasons of clarity, remains unspecified, the following tree structure is obtained.
Root (node) 101 with information field 201 containing C, A, D
Node 102 with information field 202 containing G, E
Node 103 with information field 203 containing F, I, G
Node 104 with information field 204 containing K, H, K, B
Node 105 with information field 205 containing G, D
The table of relations which belongs to the data structure chosen links, for a less complex representation here in a simplified manner, all alphabetically subsequent data tables via a respective common relation field, i.e. A<->B, B<->C, C<->D,..... As can be seen from Table 12 (table of relations), the following relation fields are being assumed:
table A columns a, z1 table B columns b, al table C columns c, b1 table D columns d, c1 table 12: Table of Relations for Fig.2 From Table From Column To Table To Column Type of Relation A a B al 1: n B b C b1 1:n C c D c1 1:n D d E dl 1:n ........ 1:n The ordered tree that has been created for a given query is run through according to a so-called pre-order by going through all nodes, starting with the first node, i.e. root 101, of the ordered tree 107, followed by all other nodes 102, 103, 104, 105 according to their hierarchy level.
Level 1: C, A, D
root (node) 10 Level 1.1: E, G
node 102 Level 1.2: F, l, G
node 103 Level 1.2.1: K, H, K, B
node 104 Level 1.3: G, D
node 105 As mentioned before, Fig. 2 shows the representation of a tree of a query that is not specified in detail. The root 101 (top node) contains in its information field 201 a{ist or array or the like of all names of the data tables that are present in the hierarchy level 1 of the superordinate query; as described above, only the data tables to which the processing functions are to be applied have been indicated in this non-specified query. The order of the indicated data tables is C, A, D.
In the next hierarchy level 1.1, one node 102, 103, 104 has been created for each sub-query of the superordinate query, each containing the data table names used in the respective sub-query in the form of a list, array or the like as its information field 202, 203, 205, respectively. The order for 102 is, for example, G, E, and for 103, for example, F, I, G, etc.
Hierarchy level 1.2.1 represents the sub-query of the sub-query 1.2 (node 103) and contains, as its information field 204, the data table names K, H, K, B used for it.
Within each node, starting with the root 101, the list of data table names, for instance the data tables C, A, D in the information field 201, are run through, and partial access paths are computed for each two subsequent data tables in the list, avoiding multiple run-throughs, as has already been described above, in order prevent unnecessary product formations which would lead to a wrong product.
Note that a node which lies directly under another node is termed the (direct) successor of this node. Accordingly, the other node is called direct predecessor.
After the entire list of the information field of a node has been run through, the next node in the pre-order is identified. The partial access path between the first data table of the list of the information field of this next node and the last data table of the list of the information field of its direct predecessor is determined, then the list of said next node is run through to the last data table, as has been described above.
For example, node 104;
run-through of the list of data tables in the information field 204:
K, H, K, B.
According to the pre-order, the next node is node 105, but as the direct predecessor of this node 105 is node 101, the link between G (node 105) and D (node 101) is identified and the partial access path computed. This is followed by the run-through of the data tables G,D in the information field 205 of node 105 of the tree 107.
As mentioned before, duplicate partial access paths are eliminated after or during the run-through of each node, so all doubly or multiply appearing data table names and relation information for the respective node or one of its predecessors, including the root, are discarded.
The calculation of the each partial access path is carried out, for example, as follows.
First, the table of relations (Table 12) is, for example, read into a graph, with the edges of the graph each containing also the relation conditions in order to determine, through the relations, links between two data tables, using the theory of graphs.
Therefore, for level 1, the path between A and C obtained in the first step C -> A as the result of the calculation of, for example, the shortest way, is the following list:
Tables Relation Conditions C B b1 b B A al a Next is A -> D, the path being A, B, C, D
The correct overall access path is therefore: A B C D E F
Assuming the existence of a tree representation as shown in Fig. 2, one of many possibilities of how to calculate the access path according to the inventive method and translate it into a SQL statement by carrying out the following steps will be described.
A data structure contains data tables A, B, C, D, E,........
According to the query on which the representation of Fig. 2 is based and which, for reasons of clarity, remains unspecified, the following tree structure is obtained.
Root (node) 101 with information field 201 containing C, A, D
Node 102 with information field 202 containing G, E
Node 103 with information field 203 containing F, I, G
Node 104 with information field 204 containing K, H, K, B
Node 105 with information field 205 containing G, D
The table of relations which belongs to the data structure chosen links, for a less complex representation here in a simplified manner, all alphabetically subsequent data tables via a respective common relation field, i.e. A<->B, B<->C, C<->D,..... As can be seen from Table 12 (table of relations), the following relation fields are being assumed:
table A columns a, z1 table B columns b, al table C columns c, b1 table D columns d, c1 table 12: Table of Relations for Fig.2 From Table From Column To Table To Column Type of Relation A a B al 1: n B b C b1 1:n C c D c1 1:n D d E dl 1:n ........ 1:n The ordered tree that has been created for a given query is run through according to a so-called pre-order by going through all nodes, starting with the first node, i.e. root 101, of the ordered tree 107, followed by all other nodes 102, 103, 104, 105 according to their hierarchy level.
Level 1: C, A, D
root (node) 10 Level 1.1: E, G
node 102 Level 1.2: F, l, G
node 103 Level 1.2.1: K, H, K, B
node 104 Level 1.3: G, D
node 105 As mentioned before, Fig. 2 shows the representation of a tree of a query that is not specified in detail. The root 101 (top node) contains in its information field 201 a{ist or array or the like of all names of the data tables that are present in the hierarchy level 1 of the superordinate query; as described above, only the data tables to which the processing functions are to be applied have been indicated in this non-specified query. The order of the indicated data tables is C, A, D.
In the next hierarchy level 1.1, one node 102, 103, 104 has been created for each sub-query of the superordinate query, each containing the data table names used in the respective sub-query in the form of a list, array or the like as its information field 202, 203, 205, respectively. The order for 102 is, for example, G, E, and for 103, for example, F, I, G, etc.
Hierarchy level 1.2.1 represents the sub-query of the sub-query 1.2 (node 103) and contains, as its information field 204, the data table names K, H, K, B used for it.
Within each node, starting with the root 101, the list of data table names, for instance the data tables C, A, D in the information field 201, are run through, and partial access paths are computed for each two subsequent data tables in the list, avoiding multiple run-throughs, as has already been described above, in order prevent unnecessary product formations which would lead to a wrong product.
Note that a node which lies directly under another node is termed the (direct) successor of this node. Accordingly, the other node is called direct predecessor.
After the entire list of the information field of a node has been run through, the next node in the pre-order is identified. The partial access path between the first data table of the list of the information field of this next node and the last data table of the list of the information field of its direct predecessor is determined, then the list of said next node is run through to the last data table, as has been described above.
For example, node 104;
run-through of the list of data tables in the information field 204:
K, H, K, B.
According to the pre-order, the next node is node 105, but as the direct predecessor of this node 105 is node 101, the link between G (node 105) and D (node 101) is identified and the partial access path computed. This is followed by the run-through of the data tables G,D in the information field 205 of node 105 of the tree 107.
As mentioned before, duplicate partial access paths are eliminated after or during the run-through of each node, so all doubly or multiply appearing data table names and relation information for the respective node or one of its predecessors, including the root, are discarded.
The calculation of the each partial access path is carried out, for example, as follows.
First, the table of relations (Table 12) is, for example, read into a graph, with the edges of the graph each containing also the relation conditions in order to determine, through the relations, links between two data tables, using the theory of graphs.
Therefore, for level 1, the path between A and C obtained in the first step C -> A as the result of the calculation of, for example, the shortest way, is the following list:
Tables Relation Conditions C B b1 b B A al a Next is A -> D, the path being A, B, C, D
Tables Relation Conditions A B a al B C b bl C D c c1 With this, the run-through of the list in the information field 201 of the first node 101 is completed, what follows now is the adjustment, i.e. the elimination of rows of identical values, then the result is converted into a string and introduced into the SQL
statement at the proper location (AFTER SELECT and BEFORE WHERE).
For level 1, the following string is obtained:
FROM A, B, C, D
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.c = D.c1) With this, the end of the list of current node, in this case of the root, has been reached. The next step is to determine whether there are any predecessor nodes or not. For the root, this is, by definition, not the case.
Summing up, the following steps are executed:
Information field 201 of node 101 C->A
A->D
results in: FROM A, B, C, D
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.c = D.cl) Next node: 102, predecessor node: 101 first data table of current node and last data table of predecessor node D ->G
run-through of list G -> E
results in: FROM E, F, G
WHERE (D.d = E.dl) AND (E.e = F.el) AND (F.f = G.fl) Next node: 103, predecessor node: 101 first data table of current node and last data table of predecessor node D -> F
run-through of list F -> I
I->G
results in: FROM E, F, G, H, I
WHERE (D.d=E.d1) AND (E.e=F.el) AND (F.f=G.fl) AND (G.g=H.g1) AND
(H.h=I.h1) Next node: 104, predecessor node: 103 first data table of current node and last data table of predecessor node G -> K
run-through of list K->H
H -> K
K->B
results in: FROM J, K
WHERE (I.i = J.il) AND (J.j = K.jl) Next node: 105, predecessor node: 101 first data table of current node and last data table of predecessor node D -> G
run-through of list G -> D
results in: FROM E, F, G
WHERE (D.d = E.d1) AND (E.e = F.el) AND (F.f = G.fl) An application of the method according to the invention could consist in that for an existing data structure, several different statements exist which are not applicable for another data structure, because of different relations that exist between the data tables. In practice, this problem is often encountered in company mergers, or when converting data in the course of the change form one program provider to another, or when changing data structures because of decreased speeds.
As the invention enables the formulation of a data statement without reference to a particular data structure, the following procedure becomes possible.
From a database statement for a first database having a first data structure associated therewith, the associated first relation-oriented operations are eliminated.
Next, second relation-oriented operations, corresponding to a second data structure which is associated with a second database, are introduced into the database statement which has been cleared of the first relation-oriented operations.
Now, the query can be executed on the basis of the database statement which includes the second relation-oriented operations by determining the partial access paths, using the associated table of the second relations, and assembling them to obtain an access path.
statement at the proper location (AFTER SELECT and BEFORE WHERE).
For level 1, the following string is obtained:
FROM A, B, C, D
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.c = D.c1) With this, the end of the list of current node, in this case of the root, has been reached. The next step is to determine whether there are any predecessor nodes or not. For the root, this is, by definition, not the case.
Summing up, the following steps are executed:
Information field 201 of node 101 C->A
A->D
results in: FROM A, B, C, D
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.c = D.cl) Next node: 102, predecessor node: 101 first data table of current node and last data table of predecessor node D ->G
run-through of list G -> E
results in: FROM E, F, G
WHERE (D.d = E.dl) AND (E.e = F.el) AND (F.f = G.fl) Next node: 103, predecessor node: 101 first data table of current node and last data table of predecessor node D -> F
run-through of list F -> I
I->G
results in: FROM E, F, G, H, I
WHERE (D.d=E.d1) AND (E.e=F.el) AND (F.f=G.fl) AND (G.g=H.g1) AND
(H.h=I.h1) Next node: 104, predecessor node: 103 first data table of current node and last data table of predecessor node G -> K
run-through of list K->H
H -> K
K->B
results in: FROM J, K
WHERE (I.i = J.il) AND (J.j = K.jl) Next node: 105, predecessor node: 101 first data table of current node and last data table of predecessor node D -> G
run-through of list G -> D
results in: FROM E, F, G
WHERE (D.d = E.d1) AND (E.e = F.el) AND (F.f = G.fl) An application of the method according to the invention could consist in that for an existing data structure, several different statements exist which are not applicable for another data structure, because of different relations that exist between the data tables. In practice, this problem is often encountered in company mergers, or when converting data in the course of the change form one program provider to another, or when changing data structures because of decreased speeds.
As the invention enables the formulation of a data statement without reference to a particular data structure, the following procedure becomes possible.
From a database statement for a first database having a first data structure associated therewith, the associated first relation-oriented operations are eliminated.
Next, second relation-oriented operations, corresponding to a second data structure which is associated with a second database, are introduced into the database statement which has been cleared of the first relation-oriented operations.
Now, the query can be executed on the basis of the database statement which includes the second relation-oriented operations by determining the partial access paths, using the associated table of the second relations, and assembling them to obtain an access path.
SELECT A.*
FROM A
WHERE ( (SELECT COUNT(C.*) FROM B, C
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.fieldl = 100) > 20) AND (A.fieldl = 40) Eliminate: FROM A
Eliminate: FROM B, C
and thus all relations A - B and B - C and A - C found in the table of relations, therefore A.a = B.bl and B.b = C.b1 As the inventive statement, there remains:
SELECT A.*
WHERE ( (SELECT COUNT(C.*) WHERE (C.fieldl = 100) ) > 20) AND (A.fieldl = 40) The inventive method can be applied to this statement using a new table of relations.
In order to illustrate the influence of the data structure on the data statement, Figs. 3 and 4 show schematic diagrams of other two different tables of relations for a data structure 3 and a data structure 4, respectively, on which the same query is to be executed.
In Fig. 5, the table of relations for the data structure 4 of Fig. 4 is shown in extended form. In addition to the usual relations between data tables, the column at the right-hand side contains freely allocatable names or macro-codes which, upon call, can be set or executed instead of the data tables in left column. In the context of the principle of the invention, this enables even further independence of the data structure, as it is possible to calculate table names and table fields or give them other names.
Extended table of relations for data structure 4 in Fig 5.:
Here, alias names for data tables and alias names for data fields as well as information regarding the partitioning of tables, such as companies.city, are indicated.
Example 3:
All companies with at least 3 contact persons in the marketing department First, for an exemplary application according to the invention, the data tables and processing functions that are necessary according to the query are indicated.
SELECT companies.*
WHERE ( (SELECT COUNT(contact_persons.*) WHERE (department.name = õmarketing") ) >= 3) The execution of the inventive methods yields different SQL statements for data structure 3 and data structure 4, respectively.
For data structure 3:
SELECT companies.*
FROM companies WHERE ( (SELECT COUNT(*) FROM departments, contact_persons WHERE (companies.company_ID=departments.company_#) AN D(departments. department_I D=contact_persons. department_#) AND (departments. name=" marketing") ) >= 3) For data structure 4:
SELECT companies.*
FROM companies WHERE ( (SELECT COUNT(*) FROM contact_persons, department_names WHERE (companies. company_ID=contact_persons.company_#) AND (contact_persons.department_#= department_names.department ID) AND (department_names.title="marketing") ) >= 3)
FROM A
WHERE ( (SELECT COUNT(C.*) FROM B, C
WHERE (A.a = B.al) AND (B.b = C.bl) AND (C.fieldl = 100) > 20) AND (A.fieldl = 40) Eliminate: FROM A
Eliminate: FROM B, C
and thus all relations A - B and B - C and A - C found in the table of relations, therefore A.a = B.bl and B.b = C.b1 As the inventive statement, there remains:
SELECT A.*
WHERE ( (SELECT COUNT(C.*) WHERE (C.fieldl = 100) ) > 20) AND (A.fieldl = 40) The inventive method can be applied to this statement using a new table of relations.
In order to illustrate the influence of the data structure on the data statement, Figs. 3 and 4 show schematic diagrams of other two different tables of relations for a data structure 3 and a data structure 4, respectively, on which the same query is to be executed.
In Fig. 5, the table of relations for the data structure 4 of Fig. 4 is shown in extended form. In addition to the usual relations between data tables, the column at the right-hand side contains freely allocatable names or macro-codes which, upon call, can be set or executed instead of the data tables in left column. In the context of the principle of the invention, this enables even further independence of the data structure, as it is possible to calculate table names and table fields or give them other names.
Extended table of relations for data structure 4 in Fig 5.:
Here, alias names for data tables and alias names for data fields as well as information regarding the partitioning of tables, such as companies.city, are indicated.
Example 3:
All companies with at least 3 contact persons in the marketing department First, for an exemplary application according to the invention, the data tables and processing functions that are necessary according to the query are indicated.
SELECT companies.*
WHERE ( (SELECT COUNT(contact_persons.*) WHERE (department.name = õmarketing") ) >= 3) The execution of the inventive methods yields different SQL statements for data structure 3 and data structure 4, respectively.
For data structure 3:
SELECT companies.*
FROM companies WHERE ( (SELECT COUNT(*) FROM departments, contact_persons WHERE (companies.company_ID=departments.company_#) AN D(departments. department_I D=contact_persons. department_#) AND (departments. name=" marketing") ) >= 3) For data structure 4:
SELECT companies.*
FROM companies WHERE ( (SELECT COUNT(*) FROM contact_persons, department_names WHERE (companies. company_ID=contact_persons.company_#) AND (contact_persons.department_#= department_names.department ID) AND (department_names.title="marketing") ) >= 3)
Claims (13)
1. A method for controlling a relational database system by executing a database query in a relational database which contains, as an associated data structure, a plurality of data tables interconnected through relations, by using a database language, with a table of the relations being provided, and with a processable database statement being created, which does not contain any relation-related operations, and an access path being determined, which concretely states the order of access to the database, characterized in that - the database statement created contains only processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, - in the table of relations, the relations are indicated as links between two data tables each via at least one key field in a manner known per se, - the access path is determined by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, - relation-oriented operations are introduced into the database statement by means of the access path, which has been determined using the table of relations, producing a SQL statement which can be processed by any database system supporting the SQL standard.
2. The method according to claim 1, characterized in that said table of relations contains all relations corresponding to the data structure associated with the database queried and may furthermore contain freely generated relations.
3. The method according to claim 1 or claim 2, characterized in that, for the purpose of determining said partial access paths, said table of relations is read into a graph based on the theory of graphs and said partial access paths are computed using the thus formed graph.
4. The method according to claim 1, 2 or 3, characterized in that relation-oriented operations are introduced in the database statement by means of said access path.
5. The method according to claim 1, 2 or 3, characterized in that said determined access path, which provides step by step references to data tables, may be separately indicated in the database statement, and for accessing the database, said references to the data tables are followed step by step.
6. The method according to any one of claims 1 to 5, characterized in that - from a database statement for a first database having a first data structure associated therewith, the associated first relation-oriented operations are eliminated, - second relation-oriented operations are introduced into said database statement which has been cleared of said first relations, said second relation-oriented operations corresponding to a second data structure which is associated with a second database, - the query is executed on the basis of said database statement which includes said second relation-oriented operations by determining said partial access paths, using the associated table of the second relation-oriented operations, and assembling them to obtain an access path.
7. A relational database system comprising a computer system with a relational database, a data processing unit and a memory, said data processing unit working according to the method of any one of claims 1 to 6.
8. A data carrier having a database statement formulated in a database language for controlling and reading into a relational database system according to claim 7, characterized in that said database statement present on the data carrier is created by only indicating processing functions, data tables and their columns to which the processing functions are to be applied as well as the order and levels of hierarchy according to which the processing is to take place, wherein the order and levels of hierarchy of the data tables used are represented in the form of an ordered tree, and the root of the ordered tree contains all names of the data tables of the superordinate query only, and subordinate sub-queries are subordinate to the root and listed in the tree-structure as nodes, which contain names of the data tables associated with the sub-queries, indicating, in the table of relations, the relations as links between two data tables each via at least one key field in a manner known per se, and the data carrier containing an access path which is determined by first computing a partial access path, via the table of relations, between each two subsequent, selected data tables on the basis of the relations which exist between the subsequent data tables, and then assembling the access path from all computed partial access paths, wherein relation-oriented operations are introduced into the database statement by means of the access path, which has been determined using the table of relations, producing a SQL statement which can be processed by any database system supporting the SQL standard.
9. The data carrier according to claim 8 for reading into a relational database system according to claim 7, characterized in that said data carrier includes database statements in which an access path, determined according to the method of any one of claims 1 to 6, has been introduced which is used to control said database system through said database statement for accessing said relational database.
10. The data carrier according to claim 8 for reading into a relational database system according to claim 7, characterized in that said access path, which is used to control said database system through said database statement for accessing said relational database, is indicated separately on said data carrier, with said access path providing step by step references to data tables and said references to the data tables being followed step by step in order to access the database.
11. A computer program having instructions which are adapted to execute the method according to any one of claims 1 to 6.
12. A computer program product having a computer-readable medium with computer program encoding means, wherein a computer is caused by the program to execute the method according to any one of claims 1 to 6 after the computer program has been loaded.
13. A computer program product having a computer program on an electronic carrier signal, wherein a computer is caused by the program to execute the method according to any one of claims 1 to 6 after the computer program has been loaded.
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| PCT/AT2005/000430 WO2007048148A1 (en) | 2005-10-28 | 2005-10-28 | Method for controlling a relational database system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CA2626048A1 true CA2626048A1 (en) | 2007-05-03 |
Family
ID=35427226
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002626048A Abandoned CA2626048A1 (en) | 2005-10-28 | 2005-10-28 | Method for controlling a relational database system |
Country Status (9)
| Country | Link |
|---|---|
| EP (1) | EP1941395B1 (en) |
| JP (1) | JP5087547B2 (en) |
| CN (1) | CN101297290B (en) |
| AT (1) | ATE476712T1 (en) |
| CA (1) | CA2626048A1 (en) |
| DE (1) | DE502005010057D1 (en) |
| ES (1) | ES2350243T3 (en) |
| HK (1) | HK1118118A1 (en) |
| WO (1) | WO2007048148A1 (en) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102646111A (en) * | 2012-02-16 | 2012-08-22 | 中国测绘科学研究院 | Knowledge base-based fast construction method of common correlation information query tree |
| CN105306987B (en) * | 2015-10-23 | 2018-06-22 | 深圳国微技术有限公司 | A kind of device for controlling TS stream interface bit rate outputs |
| CN106933929B (en) * | 2015-12-31 | 2020-02-07 | 北京国双科技有限公司 | Method and device for adjusting data table connection |
| CN108205593A (en) * | 2017-12-29 | 2018-06-26 | 华为技术有限公司 | A kind of method and device of inquiry |
| CN111125155B (en) * | 2018-10-31 | 2023-10-27 | 北京国双科技有限公司 | Access path-based data query method, device, storage medium and processor |
Family Cites Families (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JP3779431B2 (en) * | 1997-06-13 | 2006-05-31 | 富士通株式会社 | Relational database management device, intermediate link table automatic creation processing method, and program storage medium |
| JP3856978B2 (en) * | 1999-03-30 | 2006-12-13 | 三菱電機株式会社 | Hierarchical distribution diagram display method and computer-readable recording medium recording program |
| DK173451B1 (en) * | 1999-04-16 | 2000-11-20 | Targit As | Method, apparatus and data carrier for processing queries to a database |
| US6834287B1 (en) * | 2001-03-14 | 2004-12-21 | Trilogy Development Group, Inc. | Classification engine for managing attribute-based data |
| US20050076045A1 (en) * | 2001-03-19 | 2005-04-07 | Pal Stenslet | Method and system for handling multiple dimensions in relational databases |
| US7617179B2 (en) * | 2002-06-29 | 2009-11-10 | Ianywhere Solutions, Inc. | System and methodology for cost-based subquery optimization using a left-deep tree join enumeration algorithm |
| CN1560763B (en) * | 2004-02-19 | 2010-05-05 | 北京大学 | Method for translating expandable mark language path inquiry into structure inquiry |
-
2005
- 2005-10-28 EP EP05799132A patent/EP1941395B1/en not_active Not-in-force
- 2005-10-28 CA CA002626048A patent/CA2626048A1/en not_active Abandoned
- 2005-10-28 ES ES05799132T patent/ES2350243T3/en active Active
- 2005-10-28 AT AT05799132T patent/ATE476712T1/en active
- 2005-10-28 JP JP2008536874A patent/JP5087547B2/en not_active Expired - Fee Related
- 2005-10-28 CN CN2005800519436A patent/CN101297290B/en not_active Expired - Fee Related
- 2005-10-28 WO PCT/AT2005/000430 patent/WO2007048148A1/en active Application Filing
- 2005-10-28 DE DE502005010057T patent/DE502005010057D1/en active Active
-
2008
- 2008-08-19 HK HK08109268.8A patent/HK1118118A1/en not_active IP Right Cessation
Also Published As
| Publication number | Publication date |
|---|---|
| ATE476712T1 (en) | 2010-08-15 |
| DE502005010057D1 (en) | 2010-09-16 |
| CN101297290A (en) | 2008-10-29 |
| HK1118118A1 (en) | 2009-01-30 |
| JP5087547B2 (en) | 2012-12-05 |
| EP1941395B1 (en) | 2010-08-04 |
| EP1941395A1 (en) | 2008-07-09 |
| JP2009514071A (en) | 2009-04-02 |
| CN101297290B (en) | 2013-05-08 |
| WO2007048148A1 (en) | 2007-05-03 |
| ES2350243T3 (en) | 2011-01-20 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8117187B2 (en) | Method for controlling a relational database system | |
| JP6542154B2 (en) | Data search device, program, and recording medium | |
| US7788305B2 (en) | Hierarchy nodes derived based on parent/child foreign key and/or range values on parent node | |
| AU2005315254B2 (en) | Reporting model generation within a multidimentional enterprise software system | |
| Bloesch et al. | ConQuer: a conceptual query language | |
| Phipps et al. | Automating data warehouse conceptual schema design and evaluation. | |
| Willard et al. | Adding range restriction capability to dynamic data structures | |
| Malinowski et al. | Hierarchies in a multidimensional model: From conceptual modeling to logical representation | |
| US5974407A (en) | Method and apparatus for implementing a hierarchical database management system (HDBMS) using a relational database management system (RDBMS) as the implementing apparatus | |
| AU2005310977B2 (en) | Aggregatable dimension information FN multidimensional enterprise software system | |
| US7418438B2 (en) | Automated default dimension selection within a multidimensional enterprise software system | |
| Jayapandian et al. | Automating the design and construction of query forms | |
| US20050010550A1 (en) | System and method of modelling of a multi-dimensional data source in an entity-relationship model | |
| Shin et al. | Denormalization strategies for data retrieval from data warehouses | |
| CA2588341A1 (en) | Automated relational schema generation within a multidimensional enterprise software system | |
| CA2626048A1 (en) | Method for controlling a relational database system | |
| US5752016A (en) | Method and apparatus for database interrogation using a user-defined table | |
| Abdel Azez et al. | Optimizing join in HIVE star schema using key/facts indexing | |
| US20100287223A1 (en) | Functionally-dependent analysis objects | |
| JP5835084B2 (en) | Query integration method, query integration program, and integrated component generation device | |
| Maturana et al. | Design and implementation of an optimization-based decision support system generator | |
| KR20080068035A (en) | Method for controlling a relational database system | |
| US9400814B2 (en) | Hierarchy nodes derived based on parent/child foreign key and/or range values on parent node | |
| Seto et al. | Fuzzy query model for XML documents | |
| PHIPPS | Migrating an operational database schema to data warehouse schemas |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| FZDE | Discontinued |
Effective date: 20141028 |