Device for data analysis and organization

[0002] The present invention relates to systems for processing and storing information of the database type.

[0003] Such systems can be rendered accessible to novice users, with the aid of appropriate development tools. These tools are very often used by professionals (persons skilled in the art), since they considerably cut the costs of developing applications geared to a database.

[0004] Additionally, it is known that the quality of an application is greatly constrained by the quality of the initial study of the requirements to be satisfied, and consequently by the budget allotted thereto. Now, owing to their “mechanical” formulation, the modules obtained with the aid of the aforesaid tools are generally inflexible. In fact, their flexibility is aimed more toward the tailoring of the user interface than to any variations in the more fundamental requirements.

[0005] For these reasons, and also as a function of natural changes in the requirements, it is frequently necessary to modify such an application subsequently. Such a later modification is a very unwieldy task, the magnitude of which grows much faster than the complexity of the relevant application. At present, it is practically inaccessible to non-specialists, whereas, done by specialists, it entails costs which grow rapidly, to the point that it is often less expensive to redo everything each time. This situation, which will be illustrated hereinafter in a very simple example, is obviously unsatisfactory.

[0006] The present invention has in particular the aim of affording a solution to this problem.

[0007] The data processing device proposed for this purpose is of the type comprising at least one computer, furnished with a central unit with a processor, at least one user peripheral, and a memory, which are run by an operating system, as well as a database management system stored in this computer, and able to cooperate with the operating system so as to allow the user the creation/input and/or the use of a database comprising, for example, at least one data table which can be broken down into rows and columns.

[0008] The expression “database management system” is understood here to cover any system of computer files making it possible to manage tables, irrespective of their mode of physical storage.

[0009] The invention includes within this device means which may be referred to as a development and assistance tool.

[0010] The assistance function is achieved through the fact that, in a means forming an autonomous meta-dictionary, chosen information relating to the structure of the database, typically the tables and the relationships between tables, or equivalent information, is stored dynamically.

[0011] Added thereto is an analysis function, which determines and stores (at least temporarily) a representation of groups of columns whose contents are related (related columns are interdependent columns, or, more generally, columns which are dependent on one another).

[0012] In practice, the analysis function can involve a statistical tool, suitable for determining interdependencies, and preferably also dependencies, between data sets, by enumerating distinct occurrences, and an analysis (drive) module capable of cooperating with the meta-dictionary and with this statistical tool, so as to obtain and store the said representation of groups of related columns. Advantageously, the statistical tool is based on a means of counting, which preferably operates directly on the columns of each table, in a manner which will be described hereinafter.

[0013] The analysis module can be devised so as to repeat the presentation of subgroups of at least two columns, until at least one subgroup is found whose columns are related, or until the possibilities are exhausted. Preferably, it systematically carries out the presentation of all the possible different pairs of columns for the said table, preferably also for all the tables of the database.

[0014] On the basis of the analysis, it is possible to undertake a restructuring, so as to establish for the user, at least in create/input mode, a presentation (or “view”) of the database which takes account of at least one group of related columns.

[0015] The restructuring module, advantageously associated with a user interface module, commences by selecting a starting table to be processed, columns to be processed from among at least one group of related columns, and a primary relationship key for this group of columns.

[0016] The restructuring can be performed by constructing a new table with the data of a group of related columns, as well as with a relationship key with the relevant table.

[0017] It can also be performed through the fact that in data input mode (form), modify access is restricted by default to a part only of the columns of a group of related columns, the others being simply read accessible, or even inaccessible.

[0018] The analysis means and its storage means can operate in different ways, in particular: on request and/or on satisfaction of certain criteria (which may comprise a partial analysis), or else, conversely, continuously, dynamically (optionally in respect of partial analysis only).

[0019] The invention can also be expressed in the form of a process applied to a computer, or else in the form of the novel industrial product constituted by the development tool.

[0020] Other characteristics and advantages of the invention will become apparent on examining the detailed description hereinbelow, its appendices, and the appended drawings, in which:

[0021] FIGS. 1 and 2 illustrate two exemplary architectures of information processing systems usable according to the invention

[0022] FIG. 3 illustrates the combination of means within which the invention is manifested,

[0023] FIG. 4 graphically illustrates a relationship between two tables,

[0024] FIGS. 5 and 5A illustrate the content of two tables, Components and Products,

[0025] FIG. 6 illustrates a report relating to the two tables of FIGS. 5 and 5A,

[0026] FIG. 7 illustrates a single table corresponding to the two tables of FIGS. 5 and 5A, and FIG. 7A illustrates the same single table, but with anomalies of input,

[0027] FIG. 8 represents the appearance on the screen of a form for inputting information, relating to the components and to the products, managed in one and the same table,

[0028] FIG. 9 represents the result of an interrogation query on the basis of the monotable structure of FIG. 7,

[0029] FIG. 10 is the detailed block diagram of the analysis means according to the invention,

[0030] FIG. 11 illustrates the meaning of the designs of blocks used here in the flow charts,

[0031] FIG. 12 (split into 12A and 12B) illustrates the operational flow chart of the query builder,

[0032] FIG. 13 illustrates the operational flow chart of the analysis module, and FIG. 13A illustrates an optional complementary operational flow chart of the analysis module,

[0033] FIG. 14 represents the result of a false dependency search query on the basis of the monotable structure of FIG. 7A,

[0034] FIG. 15 shows the selection screen with a view to reorganization,

[0035] FIG. 16 (split into 16A and 16B) illustrates the operational flow chart of the selection and reorganization modules,

[0036] FIG. 17 represents the joins of the structure with three tables Components, Products and Categories,

[0037] FIG. 18 represents the form for inputting the components after the reorganizing of the information relating to the products, and FIG. 18A represents a simplified form for inputting the components after the reorganizing of the information relating to the products,

[0038] FIG. 19 represents the result of the interrogation query after the reorganizing of the information relating to the products, and FIG. 19A represents the result of the interrogation query after a new reorganizing of the information relating to the product category, and

[0039] FIG. 20 illustrates the operational flow chart of the virtual reorganization variant.

[0040] At the end of the description:

[0041] Appendix I recalls certain known concepts, which are useful for understanding the description,

[0042] Appendix II details in text form certain elements of the invention, and

[0043] Appendix III consists essentially of SQL commands involved in an exemplary implementation of the invention.

[0044] The drawings and appendices to the description are, in essence, of definite character.

[0045] Accordingly, they will be able not only to serve in promoting the understanding of the description, but also to contribute to the definition of the invention, as appropriate.

[0046] The computer system of FIG. 1 comprises:

[0047] at least one computer 100, the so-called client, with a central unit 110 (CPU, random access memory etc.), a screen 120, a keyboard 130, a mass memory 140 (hard disk for example), a pointing peripheral 150 (mouse for example), a printer 160, and a peripheral 170 making it possible to access a network (local or remote) as well as the corresponding software, and

[0048] a computer 200, the so-called server, comprising a central unit 210 (CPU, random access memory etc.), a screen 220, a keyboard 230, a file storage system 240 (hard disk for example), optionally a pointing system 250, a peripheral 260 making it possible to access the network (local or remote) as well as the corresponding software.

[0049] The mass memory (or another memory) of the two computers accommodates an operating system. For the “client”, an operating system with graphics interface, such as Windows, OS/2 or MOTIF for example (registered trademarks) will for example be taken. For the server, the graphics interface is less useful in certain cases, and it is possible to take Windows NT, OS/2, Unix, or Novell for example (registered trademarks). The operating system is started up, in a known manner, in general when the computer is booted.

[0050] A database is installed on the storage system of the server. Each data item is represented materially by an ordered string of impressions (magnetic, optical, magneto-optical or the like) on the storage medium (disk or the like), which is accessible for example via a language of the SQL type, and with the aid of a database engine, installed on the storage system. On the “client” computer(s) side, it is sufficient to provide programs, recorded on the storage system, making it possible to access the database via an appropriate language, for example the SQL language.

[0051] FIG. 2 illustrates a “monostation” configuration integrating some of these elements into a single computer. There is then just one storage peripheral (140+240), and the peripheral allowing access to the network (170) is unnecessary.

[0052] Although the invention may be applied to numerous database management systems, or be based on a simple file system, it will initially be supposed in the description that the computer system is furnished with a relational database engine. In this regard, mention is made for all useful purposes of the following works, which are incorporated into the present ones by way of reference:

[0053] “Les bases de données relationnelles” [Relational databases], by André FLORY and Frédérique LAFOREST, published by ECONOMICA, 1996, especially chapter 2 thereof, with regard to the fundamentals and the objectives of the relational model in respect of databases, and chapter 4 thereof in respect of the theory of normalization of relational databases,

[0054] “Relational Databases and Knowledge Bases”, by Georges GARDARIN and Patrick VALDURIEZ, published by ADDISON WESLEY, 1989, especially chapter 4 thereof, with regard to the fundamentals and the objectives of the relational model in respect of databases, and chapter 5 thereof in respect of the theory of normalization of relational databases,

[0055] “Concevoir et développer avec Oracle et Case” [Designing and developing with Oracle and Case], by Hatem SMINE published by Eyrolles, 1994, in particular chapters 9 and 10 thereof, in respect of the techniques and tools for developing applications geared around a relational database, in the exemplary case of the Oracle database and its development tools.

[0056] “Using the Oracle toolset”, by Mike KROHN published by Addison-Wesley, 1993, in particular chapters 6, 7 and 11 thereof, in respect of the techniques and tools for developing applications geared around a relational database, in the exemplary case of the Oracle database and its development tools.

[0057] The information processing chain is illustrated in FIG. 3, with:

[0058] at 410, a processing unit, for example one at least of the units 110 and 210 (FIGS. 1 and/or 2);

[0059] at 440, the mass memory, for example one at least of the hard disks 140 and 240;

[0060] at 450, the operating system which makes it possible to access the physical information of the mass memory, in the form of a file system 460. It will be noted that the physical relationships are shown by solid lines, whilst the logical relationships are shown by dashed lines. The remainder of FIG. 3 depends essentially on the file system, consequently on the mass memory 440 and on the processing unit 410, through the operating system 450.

[0061] at 470, the database engine, which accesses the logical data 475.

[0062] at 471, an “internal dictionary” (this being the name generally given, in relational databases, to the information about the structure of the database, which is stored in a manner accessible to the database engine, but inaccessible to direct modification by the user-read only).

[0063] at 500, the development tool (general part) which accesses the database 475 (tables, forms, status reports, in particular).

[0064] As will be seen hereinafter, the development tool 500 works with a generator of tables, forms and status reports 505, a meta-dictionary 510, an analysis module 530, a selection module 570, as well as a physical reorganization module 580 and/or a virtual reorganization module 590.

[0065] The fundamental elements of a database are the “tables”. Each table is a set of rows; each row is organized as one or more columns; and each column corresponds to a data type. Each row comprises one and only one value (the “data item”) for each column of the table. Physically, the storage of the data can correspond to this table-like structure, or else be different.

[0066] Each table generally comprises at least one column which contains unique values only (a value is “unique” when it is found only once in the column of the relevant table), or else an equivalent means, row number for example, which may or may not be arranged as a column of the table. This column (or one of them) is then termed the primary key. The value of the primary key allows one-to-one retrieval of the corresponding row, hence the benefit thereof. All the columns which contain unique values only may serve as primary key.

[0067] A “details” table has very frequently to reference the information of another table (“master”). For this purpose, in the details table there may be provided a so-called “external key” column, which contains the primary key of the master table. When jointly accessing the data of two tables, these two tables are related by a “join” (master/details relation): when accessing the master table, the external key is used to simultaneously bring up all the corresponding details contained in the details table; conversely, when accessing the details table, the primary key of the master table can be used to simultaneously bring up complementary data contained in the master table. This concept of join or relationship between tables is illustrated by the diagram of FIG. 4, with a table of Clients, and a table of Invoices, in the case of invoicing management.

[0068] The present detailed description will focus on another example relating to a production line for computers (the products), which are manufactured by assembling components. It will be seen that two tables “Products” and “Components” can be formulated, having for example the contents illustrated in FIGS. 5 and 5A. Here, the primary key of the “Components” table is the C_SN column (the serial number of the component); this is also the external key of the “Products” table. Via the link between the two tables, it is possible to determine, for example, that the components whose C_SN values are “11”, “13” and “14” form part of the product whose product serial number P_SN is “US1”.

[0069] The pair of Products/Components tables is of the master/details type since:

[0070] to a product there corresponds from zero to n components (the zero case would correspond to a product whose components have not yet been defined or are not yet present in the table of components), and

[0071] to a component there corresponds one and only one product.

[0072] Such a join between two tables implies certain integrity constraints as to the values of the primary key and of the corresponding external key: to each value of the external key there must correspond one and only one value of the primary key.

[0073] The collection of joins for interrelating two tables is called a path. Certain database structures are such that several paths can exist between two of the tables which they contain. For example, two Clients and Suppliers tables can be related by a path passing through an Orders table or by a path passing through a Countries table. Conversely, there may be no path between two tables. For example, two “Production” and “Production_History” tables although having the same structure and containing data of the same flavor have no interrelationship (in the sense of the structure of the data).

[0074] The use of joins or relationships makes it possible to reduce information redundancies. In an ideally designed database, a particular pertinent information item is stored once only (except for the primary key/external key pairs required for the relationships). This offers a saving of space. Furthermore, updates are made easier. Thus (FIGS. 5 and 5A), modifying the name of the product (P_NAME) having a serial number P_SN equal to US1 involves modifying only a single row (in the Products table) although it consists of three components.

[0075] The data of a table are accessed via a form displayed on the screen. A form is composed of fields, each of which corresponds to a column of a table. It makes it possible to insert new rows into the table (create), to modify existing rows, to delete rows. A form which makes it possible to access several tables must then comply with the integrity constraints on the relevant structure.

[0076] A report (see example in FIG. 6) presents chosen columns of a table, for rows selected via a query (sometimes for the whole table). The result is displayed on the screen and/or printed. A report can interrogate and present the data from several tables, but it must then know the corresponding structure (external keys, primary keys and joins). In addition to reports, it is possible to define graphics displayed on the screen and/or printed.

[0077] The reports and the graphics are commonly referred to as status reports. The forms and the status reports are commonly referred to as programs. And the programs access the data via queries.

[0078] The status reports are accompanied by interrogation queries. Depending on the way in which the status report generating tools are designed, the interrogation query may be considered to be included within the status report and/or separate from the status report. Also known are insertion queries, update queries and deletion queries, which are in principle reserved for forms.

[0079] In this detailed description, the so-called SQL (Structured Query Language) query language will be used. The SQL language has formed the subject of several standardizations, the latest of which is the ANSI/ISO SQL-92 standard. The keywords of SQL commands are presumed to be understood by the reader. If necessary, they may be found in the aforementioned standard. Either quotation marks (“), or an apostrophe (') are used as delimiter for a character string. Appendix I of the description illustrates the other principal concepts needed to understand the present description, namely:

[0080] in A-11, calculated columns,

[0081] in A-12, restrictive conditions,

[0082] in A-13, the general principle of a query (SQL's SELECT command).

[0083] The SQL language makes it possible to access relational databases whilst complying with an independence between the way in which the data are physically stored and the way in which these data are manipulated logically. Through its power, the SQL language makes it possible to express complex processing operations simply, including in particular nested loops (via joins): see Examples A-13-1 and A-13-2. However, recourse to the SQL language is in no way limiting, and the present invention can equally well be applied with the aid of a lower level language, which would drive the file system of the storage peripheral directly, for example.

[0084] In general, an application groups together a collection of programs geared around a given structure (tables and integrity constraints). A menu makes it possible to select, within an application, the program with which one wishes to work.

[0085] The development of applications geared around database engines has been automated to a greater or lesser extent by development tools. A development tool is an item of software which offers the programmer a library of program generators. These program generators allow an appreciable reduction in development times: when using a program generator, the programmer inputs parameters rather than writing the source code; these parameters are thereafter interpreted so as to produce a program in source code which the programmer can modify or augment manually, insofar as he masters the source code.

[0086] In principle, the development of applications geared around database engines adopts the following methodological approach: the programmer performs an analysis of the requirements. To do this, he can use a design method (Merise for example) whose ordered set of operational rules guides him in systematizing the thought process and in avoiding design errors. The analysis makes it possible to define a “data model” which expresses the semantics of the data in a pertinent manner. (A data model can be represented graphically by an Entity/Relation diagram.). The requirements analysis phase demands that the programmer has a good knowledge of relational algebra and of normalization of the structure of a database.

[0087] With an elementary development tool, the physical structure of the data must be implanted in the database using the “data model”. After this, the development tool makes it possible to define forms and status reports geared around the structure (based on the internal dictionary) as well as menus. The user can then augment these forms, status reports and menus.

[0088] With a (more complex) advanced development tool, the structure of the data is input and stored in another dictionary, specific to the development tool. The data which have been input at this juncture, and those of the corresponding processing operations are referred to as “meta-data”. This other dictionary, or “meta-dictionary” (510, FIG. 3), is distinct from the internal dictionary. It is geared around a system of independent files or around tables of the database itself (modify accessible, only via the development tool).

[0089] The meta-dictionary forms the subject of possible consistency checks by the development tool and may even build in certain operational rules of an analysis method (Tools of the CASE type). Using this meta-dictionary, the generator 505 (FIG. 3) of the development tool takes on board the following tasks:

[0090] the physical structure of the database is created, with the aid of the meta-dictionary (the internal dictionary is updated accordingly);

[0091] the programs and the menus (if any) are generated by the development tool;

[0092] the reports generator composes several interrogation queries (generally one per set of tables), and constructs a standard report for each query.

[0093] The user can then augment them.

[0094] Thus, the complexity of accessing the database is masked to the user to a greater or lesser extent, as a function of the quality (complexity level) of the development tool.

[0095] All this makes it possible to operate a database. Once operations have begun, it may be necessary to modify the data model and hence the data structure, and to do so for various reasons, for example:

[0096] the applicational requirement may have changed (change of trade for example),

[0097] the initial requirement was ill-defined (inadequate specifications), or dealt with by a novice.

[0098] An ad hoc modification of the structure of the data (addition of a column to a table for example) is more or less well accepted by the development tools:

[0099] in the case of a “crude” tool, the user must modify the structure of the data manually, together with the queries in the relevant forms and status reports.

[0100] On the other hand, with a more complex, high-performance tool, it is sufficient to update the meta-dictionary of the development tool, this being automatically passed onto the physical structure of the data and onto the queries of the forms and status reports.

[0101] A more significant modification of the structure of the data (addition of tables, splitting of one table into several tables for example) is very unwieldy, if one desires to preserve the data already input. And the user will come up against several difficulties:

[0102] a) the new structure, even if it complies with the relational integrity, may perhaps no longer tally with the requirements of the application. The data may become unutilizable.

[0103] b) the syntax of the languages (procedural or otherwise) for accessing the data is complex and many modifications which are not taken into account by the development tool need to be made manually.

[0104] c) the modifications to be made to the queries of the forms and status reports are complex.

[0105] d) modification of the structure is itself tricky and complex if one desires to preserve the existing data (a very simple example: having an article number column, one wishes to alter the latter from numerical type to character type).

[0106] The generation by the development tool of a new version (based on the new structure) yields the starting versions of the forms, status reports and menus. Hence, all of the augmentation subsequently made to them is lost. In all cases, it is not easy, under the action of a development tool, to physically modify the data structure, and to modify the application so as to satisfy a requirement which was not envisaged originally.

[0107] The foregoing shows the importance of the problem posed. The invention, which proposes to solve this technical problem in an elegant and reliable manner, will now be described.

[0108] The invention makes it possible to start in all cases from a monotable structure. It thereafter allows the user himself to modify the structure of the data, once production has begun, as a function of his new requirements. Indeed, it does not necessitate any particular knowledge of the data access technique (SQL language for example), or of the principles governing relational data structures. It thus allows savings in analysis time, development time and subsequent modification time. It is suitable for all relational databases on the market, and also for other data management systems.

[0109] The invention provides an improved development tool or means which can also be regarded as a user environment interface.

[0110] This tool is based on a particular meta-dictionary 510 (FIG. 10), the minimum content of which (for the preferred embodiment) is given in A-21.

[0111] The meta-dictionary repeats certain information already contained in the internal dictionary, such as the name and the type of each column of each table. It contains much more of it. This meta-dictionary 510 is tended by a storage handler, which monitors every event relating to the conditions given in A-21. This monitoring can be limited to the moments at which the development tool is operating (insofar as it has exclusive rights with regard to the modifications of the said conditions). It can be wider. It is preferably continuous.

[0112] The invention is also based on a query builder 520 (FIG. 10). The queries built obey a well-defined general format, here dubbed the canonical form, the currently preferred version of which is indicated in A-22, whilst examples of canonical queries are given in A-23.

[0113] The manner in which the query builder works, in conjunction with the meta-dictionary, is illustrated in FIG. 12. In FIG. 11 will be found the meaning of the designs of blocks used in the flow charts.

[0114] After the begin 900, step 910 presents the user with various (available) possibilities for selecting columns, simple and calculated, as well as set functions. The expression set function is understood to mean a function pertaining to several rows, such as SUM( ), COUNT( ), AVERAGE( ), for example. At 911, the user can construct expressions using the columns and/or set functions. Thereafter, he can:

[0115] at 912, create and link restrictive conditions,

[0116] at 913, define one or more (ordered) sorting criteria.

[0117] These steps 910 to 913 constitute the initialization of the query builder. They are described as carried out by the user. However, the query builder may equally well operate automatically, receiving this initialization on startup, in the form of parameters, which respectively define the elements input at 911, 912, 913. It is important to note that this query designer can store, in the form of character strings, the definition of the queries, the writing of which it has made possible.

[0118] Step 920 determines the set of tables to which the columns and expressions defined at 911 to 913 belong. This is performed selectively for each of the expressions involved in the canonical query A-22. If a column belongs to two tables, it is the primary key of one and an external key of the other. The query generator can associate this column with either of the two tables (the result of the query will be the same). The table where the column is primary key will preferably be taken.

[0119] Steps 921 to 925 and 929 determine the path connecting these tables (if there are several of them). Optionally, step 923 adds one or more tables so that such a path exists, starting from the meta-dictionary. It will be noted however that, whenever the implementation of the invention starts from a unique table, all the tables created thereafter are necessarily connected by one and only one path.

[0120] Hence, the first two rows of the query A-22 (and the third optional row, if the succeeding rows call columns whose tables do not appear in the second row) are constructed at 930 and 931.

[0121] In step 940, the “following” of the path (if there is one) and the satisfying of the restrictive conditions are dealt with in a similar manner. This serves in deciding (941) to write the WHERE clause, with a list of joins (943) and/or a list of restrictive conditions (949).

[0122] Thereafter, the GROUP BY clause is written at 961, if the test 960 so indicates: existence of set functions in the expressions and/or restrictive conditions.

[0123] The ORDER BY clause is written at 971, if the test 970 so indicates (the sorting criteria may comprise set functions).

[0124] The HAVING clause is written at 981, if the test 980 so indicates: existence of set functions in the restrictive conditions.

[0125] It will be observed that this query builder is itself responsible for all the checks. It suffices to give it, by input, or else by parameter passing, the selective list of expressions to be processed.

[0126] Using the definition of the form, as stored in the meta-dictionary, the invention also provides for the automatic (dynamic) creation of one or more forms.

[0127] This form is therefore suited to the structure of the data. It allows the consultation, insertion, modification and deletion of rows in the relevant table or tables. It complies with the integrity of the data by imposing the following constraints:

[0128] the value of a column must always tally with its type (for example, whatever is input into a date-type column must be a data item recognized as a date).

[0129] a primary key column must include only unique values.

[0130] an external key column must have a set of values included within the set of values of the corresponding primary key.

[0131] Again using the meta-dictionary, the invention makes provision for the automatic generation of reports and graphics, by the module 505.

[0132] A report is compiled on the basis of an interrogation query. It is possible in a report to define:

[0133] formats for presentation or “augmentation” (such as: bold, italics, large characters),

[0134] group calculations performed on the data of the table (such as: totals, enumeration),

[0135] breaks which condition the rezeroing of the group calculations. These breaks are organized as levels. The highest level termed level zero is that of the entire report. Level 1 corresponds to the first break (the most general) and the last level corresponds to the information of the break of last rank.

[0136] restrictive conditions or “filters”, such as: product name (P_NAME)=“COMP1”, cost (COST)>1500.

[0137] certain sorting criteria.

[0138] According to one aspect of the invention, the breaks are proposed as a function of the structure of the application: consider a column COL₂which belongs to a set of tables {TAB2₁. . . TAB2_p}, and the preceding column COL₁, which belongs to a set of tables {TAB1₁, . . . TAB1_n}; these two columns are presented in the same break level, if, for every value of i, from 1 to n, and for every value of j, from 1 to p, the path for going from TAB1_ito TAB2_jdoes not comprise any join in the master/details sense. These breaks correspond to the breaking down of the structure into levels and therefore offer the user a default presentation which is expressive. The user can however modify them at will and define certain group calculations only for certain break levels and also insert or delete breaks.

[0139] FIG. 6 illustrates a report presenting the product name, the serial number of the component, the cost with:

[0140] as restrictive condition: the product name (P_NAME) must be different from “COMP1”,

[0141] a break with regard to the product name,

[0142] a cost total (COST) for the components,

[0143] a count of the number of components.

[0144] A graphic corresponds to an inquiry and to a graphical presentation of values of columns for certain rows. In general, a graphic is determined by parameters such as:

[0145] the type of presentation (histogram, pie chart etc.)

[0146] the format of presentation (color etc.)

[0147] one or more sorting criteria

[0148] calculations (sum etc.)

[0149] According to another aspect of the invention, a graphic is compiled from an interrogation query comprising from zero to several columns of label type and one or more columns of numerical type. It comprises:

[0150] The format of the label type column.

[0151] The format of the graphic. Certain graphic formats accept only a specific number of columns of label type and of columns of numerical type. For example:

[0152] The pie chart supports only one column of label type and one column of numerical type.

[0153] The XY graphic (commonly referred to as a curve) does not support columns of label type and supports only two columns of numerical type (optionally three-dimensional graphics).

[0154] If in the interrogation query there are more columns of label type than supported by the format of the graphic, the module 505 generates as many exploded graphics as necessary, using a mechanism similar to that of breaks.

[0155] Example: consider an interrogation query which returns the turnover (equal to the sum of the amounts of the orders) by product and by region (the path between the Products and Regions tables comprises a join in the master/details sense). If the format of the graphic chosen is a pie chart, then a product/turnover pie chart will be generated for each region, and a product/turnover pie chart for all the regions together.

[0156] With the aid of the meta-dictionary, the invention makes it possible to ensure that the relation between the query and the status report (report or graphic) remains consistent after any reorganization.

[0157] The example of FIGS. 5 and 5A gives an already-tailored version of the structure of tables. In reality, if he seeks to develop a database for this same example, a novice will generally put everything into a single table. With the invention, the user can always commence with a single table, which hereinafter will be dubbed “Components_and_Products” (FIG. 7). To do this, he simply defines the name and the type of the data which he wishes to input. The development tool has updated the meta-dictionary, created a monotable data structure in accordance with the name and the type of the columns indicated by the user, and created a default form allowing access to the table, and status reports.

[0158] Thus, the user can get going almost instantaneously, without having to think about the interactional structure between his data: he will, thereafter, input data via the form, and print status reports. FIG. 8 represents the form for inputting the information into the “Components_and_Products” table. FIG. 9 represents the result of an interrogation query based on the monotable structure (with no filtering).

[0159] Even though it makes it possible to commence with a single table, the invention can also be applied to the case where an experienced developer will make initial choices for the structure of the tables which are guided by formal analysis thereof and practical considerations, as soon as these choices need to be reconsidered during the subsequent operations (with more and more data).

[0160] Indeed, it is noted that the data of a table having as few rows as that of FIG. 7 are already redundant. The informed observer will notice immediately dependencies between columns: CATEG depends on C_SN, P_SN, P_NAME.

[0161] However, at the data level, CATEG also seems to depend on C_ID and on C_DESC, in accordance with the aforesaid criteria; this is because:

[0162] the components SCR17, P166, HD1.2 and SCR15 are involved only in the manufacturing of products from the USER category, and

[0163] the components SCR14, P200 and HD2 are involved only in the manufacturing of products from the SERVER category.

[0164] This dependency (which, because illogical, is false) is due to the fact that the data sample is too narrow to be representative. If a product from the USER category is for example manufactured with a hard disk referenced HD2, the dependency will disappear.

[0165] In general, the current structure of the database is no longer suitable when certain data are redundant within the table. Stated otherwise, the user is confronted with anomalies since the data model does not comply with or no longer complies with the various normal forms of the relational model.

[0166] In the case of FIG. 7, the user could be confronted with the following anomalies:

[0167] Deletion: the deleting of the single component of a product also deletes the information relating to the product.

[0168] Update: the updating of the information of a product (its name for example) must be carried out on all the rows of the table corresponding to its components. There are therefore as many modifications to be made as there are constituent components of the products.

[0169] Insertion: a product cannot be created without immediately assigning it at least one component.

[0170] Faced with the need to reorganize the structure of the database, the user would normally be compelled to operate by hand. For this purpose, the present invention provides him with means which may be broken down into three parts (FIG. 3):

[0171] i) the analysis of the data (530),

[0172] ii) the selection by the user (570) of the columns of a table which will be isolated; during this phase, the user is superintended by consistency checks.

[0173] iii) the execution (580) of the reorganization of the data structure.

[0174] Phases ii) and especially iii) are optional. Indeed, it will be seen that it is also possible, as a variant, or as an adjunct, to implement the invention without physically reorganizing the database (590).

[0175] The aspect of the invention which is presently regarded as essential is the analysis. Here it is based on the meta-dictionary given in A-21 (510, FIG. 10), or on an equivalent means.

[0176] It is also based on a statistical tool (520, FIG. 10), which, in one embodiment, consists of a set of SQL queries given in Appendix III, and suited to various operations for systematic enumeration, as a function of the current structure of the database, such as it features in the meta-dictionary. These enumerations pertain for the most part only to “distinct values”. The SQL queries interrogate the relevant table or tables directly.

[0177] The analysis phase comprises all or some of the operations which will now be described, with reference to Appendix III. It is driven by a module 530 (FIG. 10).

[0178] In general, the “current” columns denoted C_iand C_jin the description are denoted COL1 and COL2 in Appendix III, in accordance with the customary practice of persons skilled in the art.

[0179] The process firstly comprises an analysis (preferably exhaustive) of the repetitions of the values of the columns of the data tables. This analysis will be described with reference to FIG. 13 and to Appendix III.

[0180] Starting from the begin (1000), the tables of the database are determined, or better those used in the application (1010).

[0181] For each table, the analysis consists in determining:

[0182] at 1012, the number of rows, N.

[0183] In the SQL language, the number of rows of a table (step 1012) is obtained through query A-31-1, where TAB1 is the name of the relevant table.

[0184] at 1020, the names of the columns of the table,

[0185] This is obtained by reading the meta-dictionary (as a variant, the internal dictionary).

[0186] For each column C_i, the number of distinct values N_i, at 1024-1026.

[0187] The number of distinct values of a column of a table (step 1024) is obtained through the SQL query A-31-2, where COL1 is the name of the column.

[0188] then determining and running through the pairs of two different columns which can be formed for the table (1030)

[0189] This is obtained by reading the meta-dictionary (as a variant, the internal dictionary), in the form of a simple loop to be designed.

[0190] For each pair of columns (C_i, C_j), doing the operations (detailed below) making it possible to calculate:

[0191] their interdependency ratio.

[0192] the number of pairs of values which block the dependency.

[0193] For each group of columns of a table (C_i, C_j. . . C_n), any candidate keys (join or relationship) for the group.

[0194] We now return to the calculation of the dependency ratio of two columns of one and the same table. Let Ni and Nj be the respective number of distinct values of the two columns C_iand C_j(calculated at 1024-1026). At 1034, we obtain P, the number of distinct pairs of values of C_iand Cj, through the query A-31-3, where COL1 and COL2 are the names of the columns C_iand C_j, whilst:

[0195] “xyz” is a character string which does not exist in column COL1. It is best to take any character string which is a priori unlikely. The absence of the string “xyz” in COL1 can be verified through the zero response to the query A-31-4.

[0196] is the symbol for concatenating the values of two columns, as supported by the database engine.

[0197] Depending on the value P in relation to N_iand N_j, the possible situations are:

[0198] case 1042, with P>Max(N_i, N_j) there is no dependency between the two columns (1043). C_iand C_jare said to be independent. The number of pairs of values which block the dependency (case of near dependency) may then possibly be calculated.

[0199] cases 1046+1047, with P=N_iand P>N_jthen C_idetermines C_j. Column C_iis said to be the mother of C_j. Column C_jis said to be the daughter of C_i.

[0200] cases 1046+1049, with P=N_jand P>N_i, then C_jdetermines C_i. Column C_jis said to be mother of C_i. Column C_iis said to be the daughter of C_j.

[0201] cases 1044+1045, with P=N_iand P=N_jthe C_iand C_jare said to be equivalent from the point of view of dependencies, or interdependent. C_iand C_jare said to be sisters.

[0202] the case P<Min (N_i, N_j) is impossible.

[0203] Steps 1050 and 1052 respectively ensure looping around all the pairs of columns and all the tables until the End 1059.

[0204] The queries mentioned may be formulated with the aid of the SQL query builder already cited (A-22, and 520, FIG. 10). However, it will often be simpler to prepare them individually in advance, in the form of character strings, certain elements of which are variable. In the queries A-31, the variable elements are TAB1, COL1, “xyz” (or, what is equivalent ‘xyz’), and COL2. In conjunction with the meta-dictionary, step 1024 (for example) of FIG. 13, will recompose the query A-31-2, by replacing the names COL1 and TAB1 therein by the name of the column and that of the table, respectively.

[0205] The writing of programs capable of implementing the loops described in FIG. 13 is regarded as accessible to the person skilled in the art on the basis of the present description, and of a procedural language such as the C language and its variants (C++ for example), or else SMALLTALK.

[0206] The case of “near dependence” is now examined with reference to FIG. 13A. This involves a pair of columns COL1, COL2 from one and the same table, for which each value of COL1 makes it possible to determine in a one-to-one manner the value of COL2 “to within a few exceptions”. An example is given in A-24.

[0207] Generally, the number of pairs of values, B, which block the mother/daughter dependency between two columns C_iand C_jis obtained (step 1210, FIG. 13A) through the query A-32-1, where a and b are aliases defined in the table TAB1, and the other variables are defined as before. (a table alias makes it possible to access the table under a name other than its own; several aliases with regard to the same table make it possible to access the latter in two independent ways).

[0208] If B is small, it will be beneficial to force the dependency; the user will then have to normalize the two columns. Columns C_iand C_jare said to be the pseudo-mother and pseudo-daughter respectively. One also speaks of “pseudo-sisters” in the particular case where each of the two columns plays the role of pseudo-mother and of pseudo-daughter with respect to the other. This normalization of two columns in order to force the dependency will now be described.

[0209] It is firstly necessary to identify the pairs of values, which block the mother/daughter dependency between two columns C_iand C_j. This is done in step 1230 (FIG. 13A), to which the query A-32-2 corresponds. This makes it possible to assess, for a value of the pseudo-mother column, the frequencies of the various values of the corresponding pseudo-daughter column.

[0210] The query A-32-2 comprises a nested SELECT command, which counts the number of occurrences (greater than 1) of the pair of values within the table. A search is made firstly for the values of COL1 which block the dependency. This involves the values of COL1 such that the number of distinct values of COL1‘xyz’COL2 is greater than 1.

[0211] The query A-32-2 thereupon builds a main SELECT command which returns the value pairs found, given in FIG. 14, for the relevant example. For each element of the set of values of COL1 which block the dependency (according to the nested query), a search is now made for the corresponding values of COL2 and for their frequency. Likewise for the NULL value of COL1 (NULL is the case where nothing has been input).

[0212] It will be noted that it is possible to go directly to step 1230, without doing the preliminary count of step 1210.

[0213] The actual forcing (step 1250, FIG. 13A) is performed by the SQL command given in A-32-3, where:

[0214] TAB1 is the name of the table,

[0215] COL1 and COL2 are two columns of the table for which one seeks to force the dependency in the sense where COL1 will determine COL2,

[0216] VALUE2 is the new value allocated to COL2,

[0217] VALUE1 is the value of COL1 for the rows to be modified,

[0218] VALUE2_1, VALUES2_2, . . . VALUES2_n are the values of COL2 for the rows to be modified.

[0219] However, in the case of values not input into COL1, the forcing is performed by the SQL command given in A-32-4.

[0220] The forcing can also comprise the SQL command given in A-32-5, where:

[0221] TAB1 is the name of the table,

[0222] COL1 and COL2 are two columns of the table for which one seeks to force the dependency in the sense where COL1 will determine COL2,

[0223] VALUE1B is the new value allocated to COL1,

[0224] VALUE1 is the value of COL1 for the rows to be modified,

[0225] VALUE2_1, VALUES2_2, . . . VALUES2_n are the values of COL2 for the rows to be modified.

[0226] We now return to the monotable structure, but according to FIG. 7A (with slightly different content, for illustration). The dependency relation between the product number and the product name may be severed (error of input for example). The result of the query making it possible to determine the pairs which block the dependency could be (FIG. 14), expressed plainly:

[0227] “There are two components for the COMP1-USER COMPUTER product and one component for the COMP1-USER COMPUTER BIS product; one and the same product number has been used for two different products.”

[0228] To force the dependency, it is necessary by choice to:

[0229] Allocate another product number to one of the two (A-32-5). In this case the analysis of the frequencies indicates that it is more probably the USER COMPUTER BIS row which does not have the right product number.

[0230] Replace “USER COMPUTER BIS” by “USER COMPUTER” (A-32-3).

[0231] In addition, a product number must be input for the product with serial number SRV1 (A-32-4).

[0232] Any candidate keys for a group of columns must comply with the constraint: Each candidate key must be, with regard to all the other columns of the group, either mother, or sister. In order for a column to become candidate key of a group of columns, it is necessary to force the dependency with all the pseudo-daughters of the group (in the case of near dependency).

[0233] Preferably, the result of the analysis is expressed and stored in the form of a group of columns, comprising:

[0234] the subgroup of the candidate keys, and

[0235] the subgroups of their daughter-columns, ranked by level of kinship (daughters of daughters, and so on and so forth), in a tree-like manner.

[0236] The consistency checks are made in accordance with the principles governing relational structures; they depend above all on the result of the analysis of the data already input, kinship levels in particular. They guarantee the user against unutilizable data and spare him from the need for any knowledge and understanding of access to the data, and of the principles governing data structures (relational for example).

[0237] The result of the analysis makes it possible to:

[0238] suggest which columns will be able to be isolated from a table,

[0239] determine any candidate keys of a group of columns,

[0240] make it possible to benefit from an assisted mode of input using the dependencies (possibly a virtual one).

[0241] One of the implementations of the invention therefore consists in physically reorganizing the structure of the tables. Elementarily, this function of reorganizing the structure consists in splitting a table up into two tables, which will be in a master/details relation.

[0242] Although it is possible to proceed otherwise, it is nowadays deemed preferable to leave the user to choose from among the various possibilities which may emanate from the analysis. Such a choice will pertain on each occasion to a table (TAB1), and to a group of columns S1, S2 . . . Sn of this table (which will form the new table, whereas R1, R2 . . . Rp denote the columns which will remain in the table TAB1), while complying with the following constraints:

[0243] the selected group of columns S1, S2 . . . Sn must not include the primary key of the table (the starting one),

[0244] the dependencies between the selected columns make it possible to determine the candidate keys; the user may possibly select one of them.

[0245] This phase is performed for example with the aid of a user interface having windows, like the one of FIG. 15, with a window 91 for selecting tables (here one only), then a window 95 for selecting the columns to be isolated from the chosen table, then selection at 96 of a candidate key for the new table (as an option). The name of this new table, which will be denoted TAB2 hereinafter, is input at 92. The selection/reorganization phases are illustrated in the form of steps in FIG. 16, in the case of an interaction with the user, with the screen of FIG. 15.

[0246] Starting from the begin 2000, step 2010 uses the meta-dictionary to ascertain the list of tables used by the application, which list is displayed at 2012. At 2014 the user chooses the table TAB1 to be reorganized. Using the meta-dictionary, at 2020 the system delivers the list of columns of TAB1. At 2022, these columns are displayed, with the exception of the primary key of TAB1, and at 2030 the user chooses the columns Sj to be isolated.

[0247] These choices are made via a loop around steps 2032 to 2036. With each selection of a column, one determines whether this column can serve as candidate key (2034), by storing this information item.

[0248] Thereafter, we go to the actual reorganization, which is slightly different, depending on whether the user has, or has not, chosen a candidate key. It is supposed that the forms defined at the outset drove all the fields of the single table from which one started.

[0249] i) If, in steps 2042 to 2046, the user has selected a candidate key, which is assumed to be (S1), the actual reorganization is performed by steps 2048, 2049 and 2060, in accordance with the SQL commands A-33;

[0250] The master table is created by executing the command A-33-1. This SQL command creates a table named TAB2, with the result of a SELECT command, nested within the CREATE command.

[0251] The original table TAB1 is modified by stringing together the 3 commands given in A-33-2. A temporary table TEMPO is created with the non-transferred columns, plus the chosen primary key S1 (TEMPO is a name available at database level: there is no other table or view having this name). Thereafter, TAB1 is erased and TEMPO is renamed as TAB1.

[0252] ii) otherwise, in the second case, where the user has not selected a candidate key (and there is perhaps no candidate key for the group of selected columns), the actual reorganization is performed by steps 2056, 2058 and 2060, in accordance with commands A-34.

[0253] It is then necessary to introduce or create a column which will be the primary key in the master table.

[0254] The creation of the details table follows the stringing together of steps A-34-1, where CODE_TAB2 is the name of the primary key of table TAB2. CODE_TAB2 and TEMPO are names which must be available at database level. The first row is very similar to the query A-33-1, except that instead of creating the table TAB2 directly, a TEMPO view is involved. It is the second row which creates TAB2, by adding to the TEMPO view a calculated column CODE_TAB2, the value of which is ROWNUM, that is to say the rank of the row in the table, after which TEMPO is erased.

[0255] The tailoring of the original table TAB1 is performed as indicated in A-34-2.

[0256] Since for the moment the primary key exists only in TAB2, it is necessary to start from a join between TAB1 (old) and TAB2, which has just been created. Hence, the more elaborate first CREATE command.

[0257] By stringing together the 3 commands given in A-34-2, a temporary table TEMPO is created with the non-transferred columns, plus the chosen primary key S1 (TEMPO is a name available at database level: there is no other table or view having this name). Thereafter, TAB1 is erased and TEMPO is renamed as TAB1.

[0258] In both cases, the meta-dictionary is updated (step 2065):

[0259] a) the new master table TAB2 formed of the columns S1, S2, . . . Sn is taken into account.

[0260] b) in the case of the creation of a primary key for TAB2, the creation of the column CODE_TAB2 in TAB1 is noted; the creation of the column CODE_TAB2 in TAB2 is noted. The forms which make it possible to drive the table TAB1 are augmented by the column CODE_TAB2, and no longer make it possible to drive (input) the columns S1, S2, . . . Sn.

[0261] c) in the case where the column S_ihas been chosen as primary key of the table TAB2, the forms which make it possible to drive the table TAB1 no longer make it possible to drive the columns S1, S2, . . . Sn, except S_iwhich remains driven (but which must comply with the integrity constraints due to its new role.

[0262] d) the primary key of TAB2 is referenced as such.

[0263] e) the external key of TAB1 is referenced as such.

[0264] f) the information from the master/details relationship between the tables is inserted. Furthermore, it may be necessary to update the pre-existing joins relating to columns of the newly created table.

[0265] The process which has just been described in an elementary manner makes it possible to guarantee that the data meta-dictionary remains consistent and synchronized with the physical structure of the data; thus, dynamic generation of the forms, interrogation queries and status reports is ensured without user intervention, and these are automatically adapted to the new structure, as will be seen hereinafter.

[0266] The user can then continue his work on the basis of the new structure.

[0267] Beside this, it is known practice to endow a field of a form with properties such as: “input”, “modification permitted”, “modification prohibited”, or else “modifiable only if”, with a condition which can in particular be a restriction relating to certain constraints, or even the action of the user on a defined keyboard button, or on an icon defined with the mouse. In a development tool, this can be done at the level of the form generator part, working for example with a form definition file.

[0268] Starting from the moment at which, according to the invention, the single table has been partitioned so as to define a separate master table, the form which drove this single table will be modified, or replaced by a new form, whilst complying with the following rule: as soon as a form comprises fields belonging to a master table and fields belonging to a details table, only the fields belonging to the details table can be modified; and the values taken by the external key column must belong to the set of values taken by the primary key of the master table. A separate form is generated to drive the master table.

[0269] Thus, FIG. 18 represents the form for inputting the components used in production after the reorganizing of the information relating to the products. It is noted that the fields P_NAME, P_ID and CATEG are no longer directly accessible since they do not belong to the Components table whereas they did belong to the Components_and_Products table; they are determined by the value of the product serial number field (P_SN) which is the external key of the Components table which is related to the primary key of the Products table.

[0270] To obtain this form from the previous one, all the fields (columns) which have been transferred to the master table are altered to “non-modifiable”, except for the external key. Specifically, a handler can be used which will:

[0271] fetch the list of columns of the group which served in defining the master table (gleaned from the memory 550, or from the meta-dictionary 510, in the update which stems therefrom);

[0272] pull out the relationship key from this list,

[0273] search for each column name for this list in the definition of the form, so as to accompany the relevant field with the “non-modifiable” property, and

[0274] accompany the field relating to the relationship key with a “modifiable if and only if the new value exists in the master table” property. The list of existing values is given by the search query A-37 for searching in the master table TAB2, of which COL1 is the primary key (the DISTINCT keyword is optional).

[0275] As a variant, or as an adjunct, FIG. 18A represents a simplified form for inputting the components used. The fields P_ID and CATEG which were filled in automatically in the form of FIG. 18 have been deleted from the form. Only the Product name field (P_NAME) has been preserved so as to check whether the product serial number input is indeed the one desired. Specifically, the process is the same as above, except that instead of accompanying the fields with the “non-modifiable” property, they are quite simply deleted from the definition of the relevant form.

[0276] At A-23 will be found a few examples of queries written for the structure reorganized into two tables (Components and Products).

[0277] FIG. 19 represents the result of the interrogation query after the reorganizing of the information relating to the Products. A break (this being a way of illustrating the new structure) has been provided automatically by the report generator, in response to the presence of a new table. Just as with the forms, such a break can be simply determined by the “Break” property associated with the relevant column, in the definition of the relevant report. Illustrations other than a break can be envisaged.

[0278] In practice, the selection/reorganization cycle (or else the analysis/selection/reorganization cycle) can be repeated as many times as necessary, at the very east so long as there is still a group of related columns to be processed.

[0279] In the example considered, it is thereafter possible to proceed with a new reorganization of the information relating to the category. FIG. 17 represents the join relations of the structure with the three tables Components, Products and Categories. FIG. 19A represents the result of the interrogation query on these three tables. A second break has been performed automatically.

[0280] In the embodiment described above, the physical reorganizing of the tables has the following effect:

[0281] in data input mode (form), input access (modify, create, delete) is restricted to some only of the columns of a group of related columns, the others being simply read accessible, or inaccessible;

[0282] to write access these other columns, a different form must be used, processing the new table created.

[0283] This other form can be rendered accessible from the first, in particular under the following conditions:

[0284] should an external key which does not exist in the master table be input,

[0285] when the user wishes to modify the record of the master table which corresponds to the value of the external key, actuating for example a keyboard function button.

[0286] However, it would be possible to envisage, after physically reorganizing the tables, different solutions for tailoring the forms, in particular if it is supposed (doing away with the aforesaid rule) that, in a form which comprises fields belonging to a master table and fields belonging to a details table, it is possible to modify fields belonging to the details table and fields belonging to the master table.

[0287] According to an interesting variant, instead of physically restructuring the tables, the analysis performed may serve only to modify the manner of operation of the input forms, behaving as if the database structure had been reorganized (“virtual reorganization” of the database). Since the group or groups of related columns are not reflected by a modification of the structure of the tables in the meta-dictionary, the forms are then tailored on the basis of the memory of dependencies (and of keys) 550.

[0288] The tailoring takes account of the levels of kinship between the related columns of each group. The starting point is a relationship key which is one of the candidate keys and is selected by the user or chosen automatically.

[0289] In a development tool (form generating part, working for example with a form definition file), it is also known practice to endow a field of a form with a “value if empty” property, accompanied by an expression, or by a function, the result of which will be taken as the value of the field if it is empty (a little like a “default value”). Likewise, the “the value has been modified by the user” property is also known.

[0290] A request which searches, in the same table, for at least one pre-existing row having the same value for the relationship key will then be taken as expression. This query may be based on the query builder, or else be rebuilt each time, on a base prepared in advance piecewise, and stored.

[0291] In this virtual reorganization mode, a form can work as follows:

[0292] During the inputting into the field of the relationship key, the empty fields dependent on this key (sisters and daughters) are automatically filled in by default.

[0293] Thus, in the form of FIG. 8 (monotable structure), the inputting of “US1” as product serial number (P_SN) causes the automatic inputting of the Product reference field (P_ID) which is a sister column and of the Product name field (P_NAME) and category field (CATEG) which are daughter columns.

[0294] The field P_ID (COL2) is filled in automatically by the result of the query A-36 searching for “US1” in the column P_SN (COL1) of the already existing rows. The DISTINCT keyword is optional in the query A-36, as soon as any row found contains the sought-after result for COL2 (P_ID).

[0295] The user can at leisure overwrite the values filled in by default. He can thus sever the dependency between two columns and the operation of the virtual reorganization mode is then interrupted for this pair of columns.

[0296] For example in the monotable structure, there is a dependency between the Cost column (COST) and category column (CATEG): the inputting of a cost automatically fills in the category field. If the user changes the (automatic) value of the Category field, the dependency between Cost and Category is severed: the inputting of the Cost field will no longer automatically fill in the Category field. The memory of dependencies 550 is updated accordingly, and the operation of the form is modified.

[0297] This is as it were a dynamic coupling between the analysis and the input forms. Whereas the structure of the tables is not modified, the data relating to the operation of the input form or forms remain, on the other hand, physically modified in a manner which simulates the restructuring of the tables.

[0298] This can be performed systematically, according to the steps of FIG. 20. On validating 2200 a field, and if the latter has been input manually (2210), the value input is placed in a memory area of the list type, or container, in step 2220. So long as this container contains at least one element (2230), its first element (which disappears from the container) is extracted at 2240 so as at 2250 to determine its dependent columns (direct daughters and sisters of the field considered at 2200). The fields relating to the sister columns are filled in automatically, if they are empty, at 2260. In step 2270, the same is done for the direct daughter columns, which will moreover be placed in the container. (A is the direct daughter of B if there exists no column C, other than A and B, such that B is the mother of C and C is the mother of A). And we then return to 2230. The process terminates at 2290 when the container is empty. This makes it possible to process the sisters of the field targeted at 2200, as well as to run through the complete tree of its daughters, granddaughters (and their sisters), and so on and so forth.

[0299] In data input mode (form), it will be possible to compel (by default or firmly) the data present in related columns of a row to remain consistent with at least partially identical data already existing in other rows, with the aid of a query of the same type as above. The default compulsion is aimed at simplifying input by suggesting values dependent on values already input. Firm compulsion corresponds to the case where one wishes to lock the dependency or the interdependency between the columns of a group.

[0300] The analysis can be rendered dynamic, with updating in real time or near real time, in the following way:

[0301] in the case of a created row, the query A-32-1, performed for all the pairs of columns, can be employed to determine whether anything has changed in the groups of related columns. If so, the analysis is redone. As a variant, one can also decide directly to always redo the analysis on a create row. In practice, this analysis will be simplifiable, since there is only one new row to be compared with all the others.

[0302] in the case of a deleted row, it is sufficient to search for whether its disappearance re-establishes the dependency.

[0303] lastly, a modify is equivalent to a delete followed by a create.

[0304] In practice, the foregoing will be simplifiable, since there is only one new row to be compared with all the others.

[0305] Before the analysis phase, it may be useful to carry out a prior step of prenormalizing the data of each column, or of certain at least of the columns (in particular of character type). This is because the user may have input two different values having identical semantics (for example the cities ‘Paris’ and ‘PARIS’ are semantically identical although different from the point of view of their character string).

[0306] The present invention profoundly modifies the customary development cycle: the functional dependencies are deduced from the dependencies between the data. This minimizes the analysis phase. This also makes it possible to highlight dependencies which are not envisaged or not present originally. The present invention may therefore also be used as a tool for searching for correlations between the data and as a tool for dividing the data into domains.

[0307] The invention thus proposes a means making it possible in particular to reorganize a database, after a certain operating time. This means can be implemented on the initiative of the user alone. It can be proposed at regular intervals, over time, or else as a function of the growth of the database. Before such a proposal, or else in a general way, the analysis part can be implemented automatically, at least partially, so as to determine whether there is actually anything to do.

[0308] It would also be conceivable for this analysis part to be kept up to date in the background. More generally, the analysis means and its storage means then operate continuously, dynamically (rather than from time to time, on the user's request, or on prompting by the development tool). Thus, the station (or one of them) can be furnished with a handler (or “trigger”), which triggers the updating of the analysis as soon as an event occurs which may influence this analysis. To do this, it may be necessary to continuously track (directly or indirectly) all the variables processed during the analysis phase.

[0309] It will be noted that, even if it indicates that a physical reorganization is desirable, the analysis phase is not necessarily followed by such a reorganization. The user can in fact refuse the reorganization for various reasons, such as the time which it will take, or the fact that he is not convinced of the utility of dividing his table into two parts, for example.

[0310] The present invention applies in general to any type of data, any type of relation between the data, any type of database (not necessarily relational), any type of database access language, any type of database architecture, any type of operating system, any type of storage medium and of storage system.

[0311] The invention therefore to extend [sic] its effects to any file management system accessible via a programming language which would make it possible to write the detailed equivalent of the SQL commands mentioned in the description. The power of the SQL query builder described implies that the latter can generate (in particular) all the requisite statistical queries. With other languages, it may be necessary to create a module (procedure) for each of these statistical queries. In suchlike cases, the structure of FIG. 10 may be modified: the statistical tool 520 would receive only data to be processed, whilst the interrogation of the tables so as to access the columns would be done directly by the analysis module 530.

[0312] From another standpoint, with the statistical tool described, each statistical operation is conducted on all the rows, for one or two chosen columns. A variant of the SELECT COUNTs of Appendices A-31 (possibly A-32) would consist in running through the values of the relevant column, while performing a detect new value: if the value has already been encountered, we go to the next; otherwise, this value is referenced as being one of the values taken by the column, a counter is incremented and we go to the next value. At the end, the value of the counter gives the number of distinct occurrences taken by the column. For COL1“xyz”COL2 we do the same thing by considering the concatenation of COL1, “xyz” and COL2 as a simple column (by considering as before that “xyz” is chosen so as not to create any possible confusion). By processing this in a procedure, it would be conceivable to simultaneously process several variables in a row (for example, to consider COL1, COL2 and their concatenations such as COL1“xyz”COL2), then to subsequently sweep through all the rows, processing these variables selectively. All the variables (of the kind COL1, COL2 and COL1“xyz”COL2) which can be defined from all the columns of the table can even be processed in one go.

[0313] According to another interesting variant, it is possible to use ranked sets or “lists”, in each of which, for each value of a data item of a column, the rows (designated for example by a number) which use this value are cataloged.

[0314] This will be described with reference to two columns denoted COL1 and COL2 (Appendix A-25). For each value of each column, the rows which use this value are determined. The sets obtained for the two columns are compared:

[0315] if they are strictly identical, the two columns are sisters.

[0316] if each set for a first column is equal to the a [sic] set for the second column, or else to the union of several sets for the second column, then the first column is the daughter of the second.

[0317] if there is no combination of sets for a column such that their union gives a set identical to one of the sets for the other column, the columns are independent.

[0318] In Appendix A-25-1, it is observed that

[0319] E21=11 U E13 and that E22=E12 hence COL2 is the daughter of COL1.

[0320] In the example of Appendix A-25-2 (one row added), it will be observed that there is no combination of E11, E12 and E13 whose union is E21; COL2 is therefore not the daughter of COL1. Likewise, there is no combination of E21 and E22 whose union is E12; hence COL1 is not the daughter of COL2. COL1 and COL2 are consequently independent.

[0321] An operation of comparing the unions and/or intersections of ranked lists (of a predefined format) is accessible to the person skilled in the art. On that basis, the construction of a handler performing the above functions for carrying out the analysis is likewise accessible. This handler is more efficient than the aforesaid SELECT commands, at least for certain applications.

[0322] Likewise, storage in row form is not obligatory. It is for example possible to imagine, for each column, that a value be stored just once physically and that the list of row numbers (“indices”) where this value appears be stored in parallel (or equivalent storage). A row is then reconstructed dynamically by retrieving the various values of the columns of this row by searching for the row number in the lists assigned to each column.

[0323] Let the table consist of three columns COL1, COL2 and COL3 illustrated in A-26-1. For this table, the index sets (row numbers) can be constructed for each value, as indicated in A-26-2. The set of row numbers for rows with the value “X” will now be denoted X.

[0324] Relations A-26-3 show that COL1 determines COL2. In the same way, we deduce that COL2 determines COL3 and that COL1 determines COL3 (if only by transitivity).

[0325] But COL2 does not determine COL1 since

[0326] A∩a≠a.

[0327] It is desired to reorganize this table so as to culminate in the following structure:

[0328] TAB1 (COL1, COL2)

[0329] TAB2 (COL2, COL3)

[0330] COL2 will become an external key of TAB1 and the primary key of TAB2.

[0331] To reorganize, another series of index sets is defined for the primary key of the new table (COL2) and the index sets of the other columns of the new table are updated. The new structure is therefore that given in A-26-4, where the row numbers in the table TAB2 are distinguished by “bis”.

[0332] The join is made by establishing the correspondence between the two index sets for the pivot column (which is an external key of one table and the external key of the other table). For example, if one seeks the value of COL3 corresponding to COL1=“C”, the pathway is as follows:

[0333] value C for COL1 index 4 of TAB1 value b for COL2index 2bis of TAB2 value α for COL3.

[0334] Likewise, if one seeks to ascertain the values of COL1 for COL3=a, the pathway is as follows:

[0335] value α for COL3 index 1bis and 2bis of TAB2 values a and b for COL2 indices 1, 2, 3 and 4 of TAB1 values A, B, C for COL3.

[0336] We now return to the starting table to show the manner of operation of the tracking of a dependency during the input/modification/deletion of a row. By adding a row, the table becomes that illustrated in A-27-1. The membership index sets for each value become those given in A-27-2.

[0337] To check that the dependency relation between COL1 and COL2 has not been severed, it suffices to check that the set of indices corresponding to the newly input value of COL1 is always included (in the wide sense) in the set of indices corresponding to the newly input value of COL2:

[0338] It is therefore necessary to check whether {1,2,6} {5,6}={1,2,6}.

[0339] This is not the case, hence the dependency has been severed.

[0340] The indices of the rows which block the dependency are obtained easily by considering the empty intersections of the index sets which are neither empty nor equal to the set of indices of the near-daughter column.

[0341] Thus, Appendix A-27-3 shows that the dependency is blocked by the following set of two index sets:

[0342] {{1,2},{6 }}.

[0343] When deleting a row, the dependency is re-established only if the set of sets of indices which blocks the dependency does not contain an element.

[0344] Thus, deleting row 1 gives {{2},{6}}. There is more than one set, the dependency is therefore not re-established. However, deleting row 2 gives {{6}}. The dependency is re-established.

[0345] As already indicated, the modifying of a row can be treated for example as the stringing together of a delete and a create in any order.

[0346] Being geared around hardware means which carry out operations, the invention could also be expressed in process form. It also culminates in a markedly improved development tool.

[0347] The simplest version of this development tool comprises the means making it possible to carry out the following functions:

[0348] create an application with monotable structure.

[0349] create input forms as a function of the current structure.

[0350] generate interrogation queries and status reports adapted to the current structure.

[0351] To which is added, according to the invention:

[0352] as an option, the prenormalizing of the data (analysis).

[0353] the analysis of redundancies and interdependencies of the data (analysis), and

[0354] the physical reorganizing of the structure of the data as a function of the result of the analysis as well as the forms, status reports and reports (reorganization), or else the “virtual” reorganization described earlier.

[0355] In a more advanced version, the said tool will comprise the means making it possible to carry out the following operations, in order to fetch (import) an already existing file:

[0356] create a monotable structure making it possible to fetch the data of a file in tabular format.

[0357] determine for the file the column delimiter:

[0358] In each row, the number of occurrences of each character is counted. The delimiter belongs to the set of characters having the same, nonzero, frequency in each row of the file. If the set is empty, the file is not acceptable. If the set comprises more than one element, a choice of delimiter is requested of the user. Once the delimiter has been determined, possibly after having accepted signals from the user via the user interface, each row is divided into an identical number of columns.

[0359] determine the format of each column of the file:

[0360] For simplicity, it is supposed that the data can only be in numerical, alphanumeric or date format: in order for a column to be in numerical format, all the values of this column must be in numerical format. In order for a column to be in date format, all the values of this column must be in date format. If this is not the case, the column is in alphanumeric format. For the numerical format, the width and the precision of the column are determined: the width is equal to the sum of the maximum number of digits coming before the decimal separator and of the maximum number of digits coming after the decimal separator. The precision is equal to the maximum number of digits coming after the decimal separator. For the alphanumeric format, the width of the column is determined. The width is equal to the maximum number of characters of the column.

[0361] create a table in the previously determined formats.

[0362] The SQL command is of the form given in A-35, where TAB1 is the name of the table to be created, COL1 . . . COLn the name of the columns of the table and FORMAT1 . . . FORMATn the corresponding formats; FORMATi has one of the following forms:

[0363] VARCHAR2 (L) for the alphanumeric format where L is the width of the column. Certain databases do not support the VARCHAR2 format of the SQL92 standard, so the CHAR format is then used.

[0364] NUMBER(L, P) for the numerical format where L is the width of the column and P its precision.

[0365] DATE for the date format.

[0366] The tool can also comprise the means making it possible to carry out the following operations:

[0367] create forms making it possible to access the data contained in the tables, with the validation of the insertion, of the deletion or of the modification of a row being possible only if the following constraints are complied with:

[0368] The value of a column must always tally with its format.

[0369] A primary key column must comprise unique values only.

[0370] An external key column must have a set of values included within the set of values of the corresponding primary key.

[0371] It may also comprise the means making it possible to create interrogation queries where the user specifies simply the ordered list of simple columns or of calculated columns, the list of restrictive conditions and the list of sorting commands.

[0372] This corresponds to the query builder, which it will be noted exhibits intrinsic interest, independently of the use which is made of it here. This observation applies to other elements of the present invention.

[0373] In addition, the development tool advantageously comprises means for generating status reports, with:

[0374] a report generator for presenting the result of the interrogation queries, where the user specifies simply the interrogation query, the definition of breaks, any group calculations, the format of presentations of the columns and of any group calculations, and/or

[0375] a graphics generator for presenting the result of the interrogation queries where the user specifies simply the interrogation query comprising from zero to several columns of label type and one or more columns of numerical type, the format of the column of label type (possibly), the format of the graphics.

[0376] In the foregoing, a development tool has been defined which is intended to work with a pre-existing database management system. An interesting variant, in particular with the management lists (Appendices A-25 et seq., and their description), consists of an integrated system which comprises the management of tables and the analysis and/or reorganization tools described.

[0377] In all the embodiments described or envisaged, it is possible to replace or supplement the physical reorganization of the tables with the virtually reorganized database structure mode, for input into forms.

[0378] Instead of operating exhaustively, the analysis module could stop as a function of chosen criteria, including the fact that it has already found a group of related columns (with no false dependencies). It would also be possible to string together directly with regard to the restructuring, without selection by the user, as soon as the analysis gives a group of related columns. The relationship key could then be chosen, from among the candidate keys, as:

[0379] the shortest column of this group, or

[0380] a numerical column, or

[0381] a new key could be created systematically.

[0382] With appropriate precautions, it would also be possible to automate the forcing of dependency, at least in flagrant cases, or else to make a corresponding default proposal to the user.

[0383] Lastly, although it is currently preferred that the processing and storage of the data be close together, it is possible to envisage remote storage, for example in the manner of network computers.

[0384] A-11—Calculated Columns

[0385] A calculated column is a formula consisting:

[0386] of simple columns or of calculated columns.

[0387] of operators and of mathematical functions.

[0388] of set operators (sum, average, count for example).

[0389] Examples

[0390] PRICE_ET*1.206 where * is the multiplication symbol and PRICE_ET a column of a table corresponding to a price excluding tax.

[0391] PRICE_ET*VAT_RATE where VAT_RATE is a column of a table corresponding to a VAT rate.

[0392] Count(NO_INVOICE) where count( ) is an enumeration operator, and NO_INVOICE is the primary key column of a table comprising data relating to invoices.

[0393] Sum(UNIT_PRICE*QUANTITY_ORDERED) where Sum( ) is a summation operator, UNIT_PRICE is the unit price column of a table of articles and QUANTITY_ORDERED, the quantity ordered column of a table of orders.

[0394] A-12—Restrictive Conditions

[0395] A restrictive condition is composed:

[0396] of a simple column or of a calculated column.

[0397] of a comparison operator.

[0398] possibly, of a value or of a set of comparison values.

[0399] Examples

[0400] CITY=“Paris” The column corresponding to a city must be equal to the value “Paris”.

[0401] CITY from among(“Paris”, “London”, “Washington”) where among( ) is a membership operator

[0402] PRICE_ET*VAT_RATE<1000

[0403] Count(INVOICE)>3.

[0404] A-13—Queries—principle

[0405] For its part, an interrogation query consists of:

[0406] an ordered list of simple columns or of calculated columns.

[0407] a list of restrictive conditions.

[0408] a list of sorting commands.

[0409] The list of simple and calculated columns satisfies the following constraints: it does not contain any external key either directly (case of a simple column) or indirectly (in the definition of a calculated column).

[0410] The restrictive conditions are related by the ‘AND’ and ‘OR’ Boolean operators, as well as possibly the negation operator denoted ‘NOT’.

[0411] Examples

[0412] The two examples below are based on two tables “STUDENTS” (n rows) and “UNIVERSITIES” (p rows). They are related by a join with regard to an identifier of the relevant university concerned, UNIVERSITYCODE, which is external key in “STUDENTS” and primary key in “UNIVERSITIES”.

[0413] A-13-1

[0414] SELECT * FROM STUDENTS, UNIVERSITIES returns a Cartesian product of the two tables (n times p rows), which can be regarded as a new table reviewing all the universities for each student.

[0415] A-13-2

[0416] SELECT * FROM STUDENTS, UNIVERSITIES WHERE STUDENTS.UNIVERSITYCODE=UNIVERSITY.UNIVERSITY CODE returns n rows only (1 per student). The SQL engine has created a loop in order to arrive at this result: we loop around the STUDENTS table and for each student we search for the information of his/her university by virtue of the UNIVERSITYCODE.

[0417] A-21—Meta-dictionary

[0418] The meta-dictionary (of the development tool), which is stored in the database, contains, as a minimum:

[0419] the information relating to the data structure:

[0420] the names of the tables.

[0421] the names of the columns of each table.

[0422] the type of each column (character, numerical, date for example).

[0423] for each table, the indication of the column which is the primary key.

[0424] for each table, the columns which are external keys (possibly none).

[0425] the relationships between the tables together with their cardinality relationship (sense of the master/detail relationship).

[0426] the definitions of the forms:

[0427] the reference of the table (or of the tables) to be modified by virtue of the form.

[0428] the references of the columns belonging to other tables related to the table.

[0429] the definitions of the reports:

[0430] the reference of the columns included in the report.

[0431] any calculations to be performed on these columns.

[0432] the restrictive conditions on certain columns.

[0433] the display formats of each column.

[0434] other parameters (sort commands, breaks, title of the report, etc.).

[0435] the definitions of the graphics:

[0436] the reference of the columns included in the graphic.

[0437] any calculations to be performed on these columns.

[0438] the restrictive conditions on certain columns.

[0439] the type of graphic (histogram for example).

[0440] other parameters (title of the graphic, etc.)

[0441] A-22—Query: Canonical Form

[0442] It is:

[0443] This SQL SELECT command is decomposed over several lines (here, apart from exceptions which are mentioned or obvious, indented lines form in principle part of the same SQL command as the previous line).

[0444] The first line of the query indicates as “Col₁, Col₂, . . . Col_n” the list of columns (simple or calculated) which will be extracted by the query.

[0445] The second line determines as {TAB₁, TAB₂, . . . TAB_p} the set of tables to which the simple columns and the components of the calculated columns belong, firstly for the list of columns, but also for the rest of the SQL command, essentially the restrictive conditions.

[0446] It should be observed that we are dealing with a set: a table appears therein once only even though several columns of this table form part of the list of columns (for example)

[0447] In the third line come the restrictive conditions whose columns (simple or calculated) do not comprise, directly or indirectly, any set functions (sum, average, etc.); these conditions COND_iare of the form:

[0448] COL_iOP_iVAL_i

[0449] where COL_iis a column (simple or calculated),

[0450] OP_iis an SQL language operator (for example = equality, != difference, < less than, IN among, IS NULL is not filled in).

[0451] VAL_ia value, a set of values or nothing according to the choice of the operator OP_i.

[0452] The conditions are related by the Boolean operators.

[0453] They can be bracketed by parentheses so as to resolve any ambiguity regarding the priority of the operators: for example COND₁AND (COND₂OR—COND₃). By virtue of knowing the structure of the tables, the joins between the tables are constructed: COL_a=COL_bwhere COL_ais the primary key of one table and COL_bthe corresponding external key of another table, so that the set of joins constitutes the minimum but complete path passing through all the tables of the set {TAB₁, TAB₂, . . . TAB_p}. Other tables {TAB2₁, TAB2₂, . . . TAB2_q} than those belonging to {TAB₁, TAB₂, . . . TAB_p} may be involved in building the path. Several columns of the same table may be involved in the joins constituting the path. Such a path always exists.

[0454] The fourth line of the query relates to the restrictive conditions for the columns (simple or calculated) which do not contain, directly or indirectly, any set function; it is of the form:

[0455] COND_NOT_ENS₁AND COND_NOT_ENS₂. . . COND_NOT_ENS_t

[0456] The third and fourth lines (if one at least exists) are preceded by the keyword ‘WHERE’ and are related, if the two exist, by the keyword ‘AND’.

[0457] The fifth line of the query relates to the columns (simple or calculated) which do not contain, directly or indirectly, any set function; let COL_NOT_ENS₁, . . . COL_NOT_ENS_ube these columns.

[0458] Thereafter, in the sixth line come the restrictive conditions on the calculated columns which all contain at least one set function and are denoted COL_ENSi.

[0459] Lastly, in the seventh line come the sort commands denoted ORD_i. A sort command comprises a column (simple or calculated) and a direction ASC (ascending) or DESC (descending).

[0460] Only parts 1 and 2 are always present.

[0461] The third part is present if two tables (at least) are involved in the list of columns or of conditions.

[0462] The remainder is optional.

[0463] A-23—Examples of Canonical Queries

[0464] A-23-1

[0465] SELECT P_SN, P_NAME FROM PRODUCTS

[0466] Gives the list of columns {product serial number, product name}. The columns P_SN and P_NAME belong to the same PRODUCTS table. Hence, only a single table is involved and hence there is no third part (see A-22, above).

[0467] There is no restrictive condition on calculated columns which do not comprise any set function, nor are there any calculated columns which comprise set functions, nor is there any restrictive condition on calculated columns comprising set functions, and lastly, no sort.

[0468] A-23-2

[0469] SELECT P_SN, P_NAME FROM PRODUCTS

[0470] WHERE P_SN<>‘US1’

[0471] ORDER BY P_NAME ASC

[0472] List of columns {product serial number, product name}. Restrictive condition: the product serial number is different from ‘US1’

[0473] Sort command: product name, ascending.

[0474] A-23-3

[0475] SELECT PRODUCTS.P_SN, P_NAME, SUM(COST)

[0476] FROM PRODUCTS, COMPONENTS

[0477] WHERE PRODUCTS.P_SN=COMPONENTS.P_SN

[0478] AND PRODUCTS. P_SN<>‘US1’

[0479] GROUP BY PRODUCTS.P_SN, P_NAME

[0480] HAVING SUM(COST)>1000

[0481] ORDER BY P_NAME ASC

[0482] List of columns {product serial number, product name, Sum(Cost)}

[0483] Restrictive conditions:

[0484] The product serial number is different from ‘US1’

[0485] Sum(Cost)>1000

[0486] Sort commands: P_NAME ascending.

[0487] The Product serial number and Product name columns belong to the same PRODUCTS table. The Cost column belongs to the COMPONENTS table. Hence, two tables are involved and the third part of the query is ‘PRODUCTS.P_SN=COMPONENTS.P_SN’.

[0488] When there is an ambiguity regarding the name of a column, SQL requests that the ambiguity be resolved by specifying in front of the name of columns which could lead to confusion the name of the table to which they refer (followed by a prescribed separator, in SQL a fullstop).

[0489] There is a calculated column which comprises a set function (sum) and hence the fifth part of the query is: ‘GROUP BY PRODUCTS.P_SN, P_NAME’.

[0490] There is a restrictive condition on a calculated column comprising a set function and hence the sixth part of the query is:

[0491] ‘HAVING SUM(COST)>1000’.

[0492] A-24—“Near Dependency”

[0493] Consider a table with two columns and seven rows:

[0494] The row D3 or the rows D4 block dependency, since D cannot simultaneously determine 3 and 4, if there is dependency.

[0495] A-25—Dependency by Lists

[0496] A-25-1—Example 1

[0497] for COL1 the sets are:

[0498] for “A”, E11={row 1, row 4},

[0499] for “B”, E12={row 2},

[0500] for “C”, E13={row 3}.

[0501] for COL2 the sets are:

[0502] for “a”, E21={row 1, row 3, row 4},

[0503] for “b”, E22={row 2}

[0504] These sets may be manipulated for example as variables of an array (ARRAY) with two dimensions, the first for the relevant value, and the second for the list of rows, a character string with no space, where the rows are designated by their straightforward number, are separated by a comma, and are sorted in ascending order. If E1 and E2 are the names of these arrays for COL1 and COL2, we shall write for example:

[0505] E1[1,1]=“A” and E1[1,2]=“1,4”

[0506] E1[2,1]=“B” and E1[2,2]=“2”

[0507] E1[3,1]=“C” and E1[3,2]=“3”

[0508] E2[1,1]=“a” and E2[1,2]=“1,3,4”

[0509] E2[2,1]=“b” and E2[2,2]=“2”

[0510] An even simpler notation (not borrowed from computing) will consist here in designating each set by the value which it relates to, i.e.:

[0511] A=“1, 4”

[0512] B=“2”

[0513] C=“3”

[0514] a=“1,3,4”

[0515] b=“2”

[0516] A-25-2—Example 2

[0517] For COL1 the sets are now;

[0518] E11={row 1, row 4},

[0519] E12={row 2},

[0520] E13={row 3, row 5}.

[0521] for COL2 the sets are now:

[0522] E21={row 1, row 3, row 4},

[0523] E22={row 2, row 5}

[0524] A-26

[0525] A-26-1

[0526] A-26-2

[0527] A-26-3

[0528] A∩a=A B∩a=B C∩a=Ø D∩a=Ø

[0529] A∩b=Ø B∩b=Ø C∩b=C D∩b=Ø

[0530] A∩c=Ø B∩c=Ø C∩c=Ø D∩c=D

[0531] A-26-4

[0532] A-27

[0533] A-27-1

[0534] A-27-2

[0535] A-27-3

[0536] A-31

[0537] A-31-1

[0538] SELECT COUNT (*) FROM TAB1

[0539] A-31-2

[0540] SELECT COUNT (DISTINCT COL1) FROM TAB 1

[0541] A-31-3

[0542] SELECT COUNT (DISTINCT COL1‘xyz’COL2) FROM TAB1

[0543] A-31-4

[0544] SELECT COUNT (DISTINCT COL1) FROM TAB1 WHERE COL1 LIKE “%xyz”

[0545] A-32

[0546] A-32-1

[0547] SELECT COUNT (DISTINCT a.COL1‘xyz’a.COL2) FROM TAB1 a

[0548] WHERE a.COL1 IN

[0549] (SELECT b.COL1 FROM TAB1 b GROUP BY b.COL1

[0550] HAVING COUNT(DISTINCT b.COL1‘xyz’b.COL2)>1)

[0551] OR COL1 IS NULL′

[0552] A-32-2

[0553] SELECT a.COL1, a.COL2, COUNT(a.COL1‘xyz’a.COL2) FROM TAB1 a

[0554] WHERE a.COL1 IN

[0555] (SELECT b.COL1 FROM TAB1 b

[0556] GROUP BY b.COL1

[0557] HAVING COUNT(DISTINCT b.COL1‘xyz’b.COL2)>1)

[0558] OR a.COL1 IS NULL

[0559] GROUP BY a.COL1, a.COL2

[0560] ORDER BY a.COL1, COUNT (a.COL1‘xyz’a.COL2)DESC

[0561] A-32-3

[0562] UPDATE TAB1 SET COL2=‘VALUE2’ WHERE COL2 IN (‘VALUE2_—1’, ‘VALUE2_—2’, . . . ‘VALUE2_n’) AND COL1=‘VALUE1’

[0563] A-32-4

[0564] UPDATE TAB1 SET COL1=‘VALUE1’ WHERE COL2 IN (‘VALUE2_—1’, ‘VALUE2_—2’, . . . ‘VALUE 2_n’) AND COL1 IS NULL

[0565] A-32-5

[0566] UPDATE TAB1 SET COL1=‘VALUEIA’ WHERE COL2 IN (‘VALUE2_—1’, ‘VALUE_—2’, . . . ‘VALUE2_n’) AND COL1=‘VALUE1’

[0567] A-33

[0568] A-33-1

[0569] CREATE TABLE TAB2 AS SELECT DISTINCT S1, S2, . . . Sn FROM TAB1

[0570] A-33-2

[0571] CREATE TABLE TEMPO AS SELECT R1, R2, . . . Rp, S1 FROM TAB 1

[0572] DROP TABLE TAB1

[0573] RENAME TEMP TO TAB1

[0574] A-34

[0575] A-34-1

[0576] CREATE VIEW TEMPO AS SELECT DISTINCT S1, S2, . . . Sn FROM TAB1

[0577] CREATE TABLE TAB2

[0578] AS SELECT S1, S2, . . . Sn, ROWNUM CODE_TAB2 FROM TEMPO

[0579] DROP VIEW TEMPO

[0580] A-34-2

[0581] CREATE TABLE TEMPO R1, R2, . . . Rp, CODE_TAB2

[0582] AS SELECT R1, R2, . . . Rp, CODE_TAB2 FROM TAB1, TAB2

[0583] WHERE TAB1.S1=TAB2.S1

[0584] AND TAB1.S2=TAB2.S2

[0585] AND TAB1.Sn=TAB2.Sn

[0586] DROP TABLE TAB1

[0587] RENAME TEMPO TO TAB1

[0588] A-35

[0589] CREATE TABLE TAB1 (COL1 FORMAT1 COL2 FORMAT2 . . . COLn FORMATn)

[0590] A-36

[0591] SELECT [DISTINCT] COL2 FROM TAB1 WHERE COL1=“US1”

[0592] A-37

[0593] Select [distinct] COL1 from TAB2