End user query facility including a query connectivity driver5749079Abstract An end user query technology is taught which is capable of automatically understanding the database model and guiding the user to scout for the desired information, thereby increasing productivity and ease of information access. The user is freed from the need to understanding the database model, with the end user query facility of this invention quickly guiding the user to acquire the information. This is made possible by the end user query facility of this invention first recapturing the application semantics from the existing database model to provide a set of derived semantics. The derived semantics are then used by the end user query facility to intelligently guide the user to scout for the desired information in the database. In addition, the derived semantics can be easily updated by the end user query facility when the database model is changed. Claims What is claimed is: Description TECHNICAL FIELD
______________________________________
File name Item name Domain
______________________________________
A-PARTS-FILE P-NO P-NO
B-PARTS-FILE PART-NO P-NO
______________________________________
In such a case, we would not be able to establish a file relationship between the two files as the item names are different. But it would still be meaningful to establish a link between the two files with such items as the items are in the same domain. In the example above, both A-PARTS-FILE and B-PARTS-FILE have items in the same domain called P-NO though the item names are different. A-PARTS-FILE and B-PARTS-FILE should therefore be linked. The linkage rule defined earlier is thus modified as follows to take into account items with not only the same name but with different names in the same domain: (i) both files have one item in the same domain, AND (ii) the item in the target (second) file is a unique or repeating key. For every linkage between two files, the item in the source file can be a unique key, repeating key or non-key while the target file must either be a unique or repeating key. From this restriction we can derive six possible valid types of file linkages. These are as follows:
______________________________________
Source file Target file
Notation
______________________________________
a. Unique key Unique key UU
b. Unique key Repeating key
UR
c. Repeating key Unique key RU
d. Repeating key Repeating key
RR
e. Non-Key Unique key NU
f. Non-Key Repeating key
NR
______________________________________
However, in one embodiment the repeating to repeating (RR) combination which is item d above is not stored because this represents a bad file design. A repeating to repeating relationship indicates a many to many relationship which preferably should not exist in a normalized data model. The analyst is informed of such a finding and attempt to rectify it. All the binary relationships found using the above rules are stored as follows: a. Source file b. Source file item to link to target file c. Target file d. Target file item to link to source file e. Relationship (e.g. UU,UR,RU,NU,NR) From the earlier example using EMPLOYEES and SKILLS files, the binary relationship are stored as:
______________________________________
Source file
Item Target file
Item Relationship
______________________________________
EMPLOYEES Emp-no SKILLS Emp-no UR
______________________________________
The set of binary relationships derived from the above step is stored in a Binary Relationship File 24. The next step, namely Build Basic Knowledge Threads 25, involves deriving knowledge of Database Model 10 of an application system from these binary relationships which is then stored in Knowledge Base 14 in the form of knowledge threads. Each thread represents a linkage of many files. The following is an example of a knowledge thread : EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES It contains an EMPLOYEES file linked to BRANCHES file which is then linked to EXPENSES file. Inference Engine 17 (FIG. 1) in Information Scout 15 later uses these threads in isolation or combination to infer the access paths in order to obtain the information requested by the user. For example, when the user wants to report the employees and their expenses in each branch, the above thread is used to generate the required path to navigate through an application database in order to acquire the required information. Knowledge Base 14 is made up of basic and acquired knowledge threads. The following describes the derivation of basic knowledge threads by joining binary relationships from Binary Relationship File 24. The derivation of acquired knowledge threads is described later. FIG. 3 is a flow chart of one method suitable for use as Build Basic Knowledge Threads 25 of FIG. 2. The following describes how a single basic knowledge thread is built using the method of FIG. 3, with reference to the following example of a procedure Find-Thread:
______________________________________
Procedure Find-Thread (file-count, pointer 2)
begin
old-file-count = file-count
while (pointer 2 <> end of file)
begin
read binary relationship file using pointer 2
until (thread-end = source file of binary relation
record)
or (end of binary relationship file)
if (binary relation record exists) and
(target-file of record not exist in current-thread)
then begin
call Procedure Find-Thread(file-count, pointer 2)
file-count = file-count + 1
if (max-count < file-count)
then max-count = file-count
current-thread = current-thread + target-file
reset pointer 2 to start of binary relationship
file endif
endwhile
if file-count <> old-file-count
then write current-thread into Basic Knowledge file
end
______________________________________
Note: Parameters filecount and pointer 2 are passed by value
Currentthread, maxcount are global variables
The formation of each thread begins with a first binary relationship record read from Binary Relationship File 24. This record forms an initial thread. However, if the first binary relationship record is of type `RU` (repeating to unique) relationship, it is ignored and no initial thread is formed from it. The next record is then read and if it still is of type `RU`, it is again ignored until the next record is found that is not of type `RU`, which is then used to form the initial thread. The reason for doing this is that it is only necessary to form threads using either `RU` or `UR` types and not both as they lead to the same access path being inferred by Inference Engine 17. In this embodiment we are using `UR` types. To keep track of the next record to read from Binary Relationship File 24, a pointer called pointer 1 is used. The initial thread can be extended by linking with other valid binary relationship records. To do so, Binary Relationship File is searched. This time another pointer, namely pointer 2, is used to keep track of the next record to read from Binary Relationship File 24 to link to the thread to be extended. A link to extend the thread is said to be found if the first file of the binary relationship record and the file at the end of the thread, called the thread-end file, are the same, or else the next binary relationship record is read using pointer 2. If a valid binary relationship record is found, we then examine if the target (second) file of this record is already in the thread. If it is not, it is added to the end of the thread, to become the thread-end. If it is, the next binary relationship record is read using pointer 2. The search ends when the end of Binary Relationship File 24 is reached using pointer 2. The formation of a single thread is then complete and is stored in Knowledge Base 14 as follows: Thread-Head: file name file item to link to next file Thread-Body: file name file item to link to the previous file relationship to the previous file file item to link to the next file Thread-End: file name file item to link to the previous file relationship to the previous file The thread-head contains the first file and the first file item. It is linked to the next file using the thread-body or thread-end. The thread-body is made up of a file, its item which has the same domain as the item of the previous file it is linked to and the relationship to that file. It also contains another item which is used to link to the next file. The thread-end is similar to the thread-body except that it does not have the item to link to the next file. The following example briefly shows how Knowledge Base 14 is built up with basic knowledge threads using the above procedure. Assume that Binary Relationship File 24 contains the following records:
______________________________________
Source file
Item Targret file
Item Relationship
______________________________________
1. EMPLOYEES
Br-no BRANCHES Br-no NU
2. BRANCHES
Br-no EXPENSES Br-no UR
3. EMPLOYEES
Emp-no BILLINGS Emp-no
UR
4. BILLINGS
Emp-no EMPLOYEES Emp-no
RU
5. PROJECTS
Proj-no BILLINGS Proj-no
UR
6. BILLINGS
Proj-no PROJECTS Proj-no
RU
______________________________________
The method of this example is then as follows:
______________________________________
Pointer 1 at
Thread Formed
lst record of
Use the first binary relationship
above file record EMPLOYEES link to BRANCHES as
the valid binary relationship record to
form the initial thread. Next, examine
whether this thread can be extended by
searching through the same Binary
Relationship File, this time using
pointer 2. It can be linked to the
second binary relationship record,
namely BRANCHES link to EXPENSES to form
the final thread:
EMPLOYEES .fwdarw. BRANCHES .fwdarw. EXPENSES
2nd record Similarly, using the next binary
relationship BRANCHES link to EXPENSES
record, form the initial thread:
BRANCHES .fwdarw. EXPENSES
This thread cannot be extended as the
EXPENSES file cannot be linked to other
binary relationship records.
3rd record
Using the 3rd
binary
relationship the
third thread
EMPLOYEES.fwdarw.
BILLINGS.fwdarw.
PROJECTS is
formed as
follows:
EMPLOYEES links
to BILLINGS via
Emp-no
BILLINGS links
to PROJECTS via
Proj-no
This thread uses
different items,
namely
Emp-no and Proj-
no to link the
two binary
relationship
records.
4th record No thread is formed as relationship is
'RU'.
5th record Thread formed is
PROJECTS .fwdarw. BILLINGS .fwdarw. EMPLOYEES as
follows:
PROJECTS links to BILLINGS via Proj-no
BILLINGS links to EMPLOYEES via Emp-no
This thread uses different items, namely
Proj-no and Emp-no, to link the two
binary relationship records.
6th record No thread is formed as relationship is
'RU'.
Each thread is stored in Knowledge Base 14 as follows,
using the third thread above as example:
Thread-Head file
= EMPLOYEES
item = Emp-no
Thread-Body file
= BILLINGS
item-1 = Emp-no
relationship
= UR
item-2 = Proj-no
Thread-End file
= PROJECTS
item = Proj-no
relationship
= RU
______________________________________
Information Scout FIG. 4 is a flow chart of one method suitable for use as Information Scout 15 of FIG. 1. Report Item Selector 16 first guides the user to select the items to be reported. Inference Engine 17 then infers the access path based on the items selected. Report Item Selector The user is prompted for a keyword or presented with a list of all the words in Keyword Library 13 to choose from. When the user chooses to provide a keyword, Report Item Selector 16 lists all the items that match the keyword provided. For example, the keyword DATE could return the following list of items containing the word DATE. DATE BIRTH DATE JOINED DELIVERY DATE LAST DATE UPDATE The user then selects the desired item from the list. In one embodiment, an explanation of each item is also displayed. This explanation is either extracted from the data dictionary of an application system by Data Dictionary Analyzer 21 or entered by a programmer analyst maintaining the application system. Using the above method, the user selects all the items to be reported. For each item, the file containing it is automatically identified and added to the file list using Generate File List Step 27. When two or more items are selected from the same file, only one entry is made in the file list. This file list is sorted such that the first file is the file which has the highest number of items selected and the other files are in descending order of items selected. When there is only one file in the list (that is, all the items selected come from the same file), no search of access paths is required. Otherwise, inference engine 17 is invoked to infer the access path. Inference Engine The first step involves searching for the optimal knowledge thread in Knowledge Base 14 to be used to generate an access path. If no optimal knowledge thread is found, the next step is to infer new knowledge threads, one of which is used to generate an access path (See FIG. 4). However, if no new threads can be inferred, then the user is informed that there is no solution. Knowledge base 14 comprises two sections: basic and acquired knowledge threads. The basic knowledge section contains the knowledge threads that are generated by Semantics Extractor 12. The acquired knowledge section also contains knowledge threads but these are knowledge threads that have been inferred by Inference Engine 17. The process of acquiring these knowledge threads is explained later. In this embodiment, the search is made on the acquired knowledge section first. If a thread is found where all the files in the file list using step 27 of FIG. 4 exists on the thread, this thread used to generate the access path. When the search is unsuccessful, i.e. no acquired knowledge thread is found which can match all the files in the file list, the search proceeds using the basic knowledge section. FIG. 5 shows a flow chart of one method of performing the search for a knowledge thread from the basic knowledge section to be used to generate an access path. The following example illustrates this search. Assume the file list from Step 27 in FIG. 4 has been build up from the items the user has selected and it contains two files as follows: File list: EMPLOYEES BRANCHES Assume that the basic knowledge section has the following three basic knowledge threads with EMPLOYEES as the thread-head: Thread 1: EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES Thread 2: EMPLOYEES.fwdarw.PAY Thread 3: EMPLOYEES.fwdarw.BILLINGS We define the number of files accessed as the number of files in the thread which exists in the file list starting from the thread head. Thread 1 has files which match all those in the file list, namely EMPLOYEES and BRANCHES. The number of files accessed in this case is two (EMPLOYEES.fwdarw.BRANCHES) and is equal to the number of files in the file list. This thread is considered an "optimal thread" as all the files in the file list exist contiguously on the thread starting from the thread-head. Thread 2 and 3 are invalid because BRANCHES is not found on the thread. When an optimal thread is found, the search ceases and the optimal thread is used to generate the access path. In the above example, the thread EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES is used to generate the access path but only up to the BRANCHES file with the EXPENSES file ignored or "trimmed out" as it is not required in this example of the user query. Otherwise, Inference Engine 17 attempts to infer new threads by joining two or more threads together in parallel (see FIG. 4). Those new threads that are found to be optimal, i.e. they have the number of files accessed equal to the number of files in the file list from step 27 of FIG. 4, are then classified as acquired knowledge threads and stored in the acquired knowledge section of Knowledge Base 14. The following section describes the process of deriving acquired knowledge threads. Before inferring new threads, Inference Engine 17 must first generate a list of knowledge threads consisting of basic knowledge threads which have at least one file matching any file in the file list from step 27 of FIG. 4. This thread list is then sorted in descending order of the number of files in each thread matching those in the file list. Within this sorted list of knowledge threads, if there are more than one thread with the same number of files matching those in the file list, the threads are then sorted in ascending order of the number of files in each thread. This results in a knowledge thread that has the most number of files matching those in the file list but has the least number of files on the thread being at the top of the list. This forms the sorted thread-list. This list is then used to form new threads by joining in parallel two or more threads. FIGS. 6a and 6b form a flow chart of how these new threads are inferred from the sorted thread-list. For ease of explanation of how the above procedure works, assume that the file list from step 27 of FIG. 4 contains the following three files: EMPLOYEES BRANCHES PAY and the basic knowledge section of the Knowledge Base 14 contains the following three threads as before: Thread 1: EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES Thread 2: EMPLOYEES.fwdarw.PAY Thread 3: EMPLOYEES.fwdarw.BILLINGS Thus there is no basic thread that contains all three files in the file list. Inference Engine 17 next employs parallel join inferencing. The basic knowledge threads which have at least one file found in the file list is extracted and sorted as follows:
______________________________________
No. of
files
in thread
matching
those in Thread-
Thread-list: file list
length
______________________________________
1. EMPLOYEES .fwdarw. PAY
2 2
2. EMPLOYEES .fwdarw. BRANCHES .fwdarw. EXPENSES
2 3
3. EMPLOYEES .fwdarw. BILLINGS
1 2
______________________________________
The flow chart of FIGS. 6a and 6b is then applied. First, the thread EMPLOYEES.fwdarw.PAY is added to the parallel list. Next, the file EMPLOYEES is read from the file list. Since EMPLOYEES exists on this thread it is removed from the file list. The next file from the file list is then read and examined whether it exists in the same knowledge thread. Since it does, it is also removed. However, the next file from the file list, namely BRANCHES, does not exist in the knowledge thread. It therefore remains in the file list. The next step is to retrieve from Knowledge Base 14 the basic knowledge threads whose thread head (first file) is EMPLOYEES. As stated earlier, the Knowledge Base contains the following basic knowledge threads: 1. EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES 2. EMPLOYEES.fwdarw.PAY 3. EMPLOYEES.fwdarw.BILLINGS The basic thread EMPLOYEES.fwdarw.BRANCHES.fwdarw.EXPENSES has the BRANCHES file and is therefore added to the parallel list. But before it is added, the EXPENSES file is removed as it is not a file in the file list. The parallel list now contains the following threads: Parallel list: EMPLOYEES.fwdarw.PAY (obtained from the sorted thread list) EMPLOYEES.fwdarw.BRANCHES (obtained from the basic knowledge threads) The above knowledge threads have a parallel relationship through the common file EMPLOYEES and form the parallel thread as follows:
______________________________________
EMPLOYEES .fwdarw. BRANCHES
.fwdarw. PAY
______________________________________
Next, the optimality test is applied. As the number of files accessed (which is earlier defined as the number of files in the thread starting from the thread-head) is equal to the number of files in the file list, an optimal solution has been found. This optimal thread is then added to Knowledge Base 14 as an acquired knowledge thread. However, if the optimality test fails, the above process to search for new parallel relationships is then repeated using the next thread on the sorted thread-list. If there are any new parallel relationships found, the optimality test is again applied. In the event the sorted thread-list has been exhausted with no optimal acquired knowledge thread formed from the parallel relationships found, the user is prompted to select one of the parallel relationships found if there are more than one, or else the single parallel relationship found is used to generate the access path. In cases where there are more than one parallel relationships, there may exist parallel relationships as follows:
______________________________________
a. EMPLOYEE
.fwdarw. PAY
.fwdarw. BRANCHES
b. EMPLOYEE
.fwdarw. BRANCHES
.fwdarw. PAY
______________________________________
As both these parallel relationships are semantically the same, one is redundant and is thus removed. There may also be a case whereby there are no parallel relationships found after the sorted thread list has been exhausted. In such a case, there is no solution to the end-user query. The inference method as illustrated in the flow chart of FIGS. 6a and 6b also takes into account inference of relationships using more than one file as common files. For example, the method is able to infer the following parallel relationships, whereby EMPLOYEES and PAY are the two common files: EMPLOYEES.fwdarw.PAY.fwdarw.file1 EMPLOYEES.fwdarw.PAY.fwdarw.file2 These then form the parallel thread:
______________________________________
EMPLOYEES .fwdarw. PAY .fwdarw. file1
.fwdarw. file2
______________________________________
Program Generator Based on the path inferred by Inference Engine 17, the corresponding QUIZ ACCESS statement is generated. In `QUIZ`, the file linkage is specified using the ACCESS statement with the following syntax:
______________________________________
ACCESS file › LINK item OF file TO item OF file !
› › { AND } item OF file TO item OF file ! . . !
{ LINK }
where ACCESS, LINK, AND, OF, TO are part of the 'QUIZ'
syntax,
file refers to the file name,
item refers to the item name in the file to be linked,
› ! means optional statement,
{ } means choose one of the options i.e. AND or LINK,
. . means repeats one or more times
______________________________________
In QUIZ, there are two ways of defining a linkage between a number of files: hierarchical and parallel. A hierarchical linkage is defined with the "LINK . . . TO" option of the ACCESS statement. A parallel linkage is defined with the "AND . . . TO" option of the access statement. When a single thread is used to generate the access path, the hierarchical link is used. When a combination of two or more threads are used a parallel link is used. From the earlier examples,
______________________________________
1. EMPLOYEES .fwdarw. BRANCHES
uses the hierarchical linkage to generate
ACCESS EMPLOYEES LINK Br-no OF EMPLOYEES
TO Br-no OF BRANCHES
2. EMPLOYEES .fwdarw. BRANCHES
.fwdarw.PAY
uses the parallel linkage to generate
ACCESS EMPLOYEES LINK Br-no OF EMPLOYEES
TO Br-no OF BRANCHES
AND Emp-no OF EMPLOYEES
TO Emp-no OF PAY
______________________________________
It should be noted that for a different language implementation, Inference Engine 17 and Knowledge Base 14 design need not change. Only the access path needs to be rewritten using the designated language. Compiler/Executor After Source Program 19 is generated, Compiler/Executor 20 is used to compile Source Program 19 into executable code. The compiled program is then executed to produce the report. In this embodiment, Compiler/Executor 20 is the QUIZ part of POWERHOUSE fourth generation language. ALTERNATIVE EMBODIMENTS Several additional embodiments are also taught, as will now be described. In one embodiment, a Model Purifier 26 is included which allows a user to add or alter the key type and binary relationships in Knowledge Base 14 (FIG. 7). The Model Purifier interfaces with Semantics Extractor 12 to change Knowledge Base 14 accordingly. In another embodiment, the functionality of Knowledge Base 14 is extended in order to store items that are derived from items in the database model. In this embodiment, the Model Purifier allows a user to input the specifications for the derived items. Derived items can also be obtained from source code of applications programs that access the database, in which case Semantics Extractor 12 serves to extract the derived item specifications from these programs. These derived items and their related database items together form a pseudo database file. Semantics Extractor 12 then uses this pseudo file to derive new binary relationships with normal database files and build new basic knowledge threads. The new binary relationships and new knowledge threads are then stored in Knowledge Base 14. In this embodiment, the functionality of Program Generator 18 is also extended so that after Inference Engine 17 has generated the access path which may contain pseudo database files as well as normal database files, Program Generator 18 uses this access path to generate source programs to obtain information from the normal database files and the pseudo database files. In another embodiment, another component called the Security Model Specifier 29 is included to allow a user to input a security model which specifies the items of the database model that the user can access. This is called item security. The security model also specifies the range of values within an item that the user can access. This is called value security. To support this security, the functionality of Knowledge Base 14 is extended to store the security model. Functionality of Report Item Selector 16 and Program Generator 18 are also extended to use the security model in the Knowledge Base so that the information obtained from the database at query time meets the security model specification. In another embodiment, the functionality of the Model Purifier is extended to allow a user to specify multiple domains for a data item and the aliases for a file containing this item. In this embodiment, the functionality of Program Generator 18 is also extended to generate the appropriate file alias statements in the source program to access the database to satisfy the user query. FIG. 7 depicts one embodiment of this invention in which Model Purifier 26 is used. Model Purifier 26 serves to allow a user to add or alter the key type of items in Knowledge Base 14, e.g. the key type of an item can be changed from unique to repeating key. Model Purifier 26 is also used to allow a user to alter the binary relationships between database files located within Knowledge Base 14. The rationale for the use of Model Purifier 26 in accordance with this embodiment of the present invention is that in some applications the database model or the application programs that access the database may not be rich enough for Semantics Extractor 12 to extract the necessary semantics including file linkages for the user to perform certain queries. Model Purifier 26 allow the user to input the additional semantics to satisfy these queries. FIG. 8 shows one embodiment of this invention in which Semantics Extractor 12 is extended to interface with Model Purifier 26. If the key types are altered, Model Purifier 26 activates Semantics Extractor 26 to re-derive the binary relationships and to rebuild the knowledge threads in Knowledge Base 14. If the binary relationships are altered, Model Purifier 26 activates Semantics Extractor 14 to rebuild the knowledge threads. A user may specify an item or items to be reported that may not be found in Database Model 10. Examples of such items are as follows, which we called derived items as they are obtained by defining using the items of the database files:
______________________________________
Derived Item Defined as
______________________________________
(i) Employee-Name Firstname of Employee file +
Lastname of Employee file
Note: Firstname & Lastname are items of database file
Employee
(ii) Sales-Commission
Sales-Amount of Invoice file .times.
Commission Rate of Commission
Table file
Note: Sales-Account and Commission Rate are items of
database file Invoice and Commission-Table,
respectively
(iii) Total-Sales sum of Sales-Amount of Invoice
file for each month of the year
(iv) Ratio-of-Jan-Sales
(Sales-Amount of Invoice file
to-Total-Sales
for January).div.(Total-Sales
derived from (iii) above)
______________________________________
These derived items can be obtained from direct user input into Knowledge Base 14 or from Source Code of Application Programs 27 that access the database. To obtain such derived items from direct user input, the functionality of Model Purifier 26 is extended to meet this requirement. To obtain such derived items from Applications Programs 27, the functionality of Semantics Extractor 12 is extended with Program Analyzer 28 (see FIG. 9) to extract the derived items from these programs. In addition to the definitions of the derived items, the data type, size, and format of these derived items are also extracted from Applications Programs 27 by Program Analyzer 28 of Semantics Extractor 12. These derived items and their related database items together form a pseudo database file which is included in File Definition List 22 (see FIG. 9). Semantics Extractor 12 then uses this pseudo file in File Definition List 22 to derive new binary relationships with normal database files in File Definition List 22 and build new basic knowledge threads. The new binary relationships and new knowledge threads are stored in Knowledge Base 14. Besides the Model Purifier 26 and Semantics Extractor 12, the Program Generator 18 is also extended so that after the Inference Engine has generated the access path which may contain pseudo database files as well as normal database files, the Program Generator 18 uses this access path to generate source programs to obtain information from the normal database files and pseudo database files. The following is a description of one embodiment of an algorithm suitable for use by Program Analyzer 28 of Semantic Extractor 12 to extract the derived items in accordance with the teachings of this invention: a. From a single pass application program A single pass application program is a program that contains only one database access statement. Step 1: Extract the database files that are accessed in the application program. For each database file accessed Extract the list of items of database file, Store this list and the database file name as a pseudo database file in the knowledge base. Next database file Step 2: Extract the list of derived items and their definitions from the application program For each derived item Scan for the data type, size and format, Store the derived item name, its definition, data type, size and format in the same pseudo database file obtained from step 1 above. Next derived item b. From a multiple pass application program A multiple pass application program consists of many single pass application program "stringed" together with the output of the previous pass being used as input by the current pass. Step 1: Create a new pseudo database file 1 for the first pass (i=1) using the steps of the single pass application program above. Step 2: For each of the remaining passes i.e. i=2 to n with n being the last pass, Create a pseudo database file i Store the location of the previous database pseudo file (i-1) in pseudo database file i. These derived items are then presented to the user together with the items from the database model for the user to select from in order to satisfy the user query. In order to generate a program from the access path inferred by Inference Engine 17 that may contain pseudo database files, in addition to the above described extension of the functionality of Model Purifier 26 and Semantics Extractor 12, the functionality of Program Generator 18 is also extended as follows: Step 1: Examine the access path generated by the Inference Engine 17. Step 2: For every file in the access path: If it is a pseudo database file, Then (i) generate source program to produce data for the pseudo database file using the derived item definitions; and (ii) generate source program to extract the information from both the database and the data of the pseudo database file produced by the program of step 2 (i) above. Next file In another embodiment, a security model specifies the items a user can access (item security) and the range of values within an item the user can access (value security). In item security, a user is assigned access rights to a subset of the list of items available in Knowledge Base 14. In value security, a user is assigned access rights to a range or ranges of values within an item in Knowledge Base 14. For example, the access rights could be DEPT-NO=101, SALARY <2000, or SALARY of PAY file if EMP-GRADE of EMPLOYEE file >8. FIG. 10 shows the Main Flowchart of one embodiment of the present invention which supports a security model. As shown in FIG. 10, Security Model Specifier 29 is provided for the user to input the security model into Knowledge Base 14. Also, in accordance with this embodiment, the functionality of Report Item Selector 16 and Generate File List 27 module (FIG. 4) of Information Scout 15, as well as Program Generator 18 are extended to support the security model as is now explained. In one embodiment, the functionality of Report Item Selector 16 is extended such that only the items defined in the security model that are accessible by the user will be presented to the user for selection at query time. In one embodiment, the functionality of Generate File List module 27 is extended to perform the following: Step 1: For each query item selected Retrieve the file(s) containing the query item. Next query item Step 2: For each file retrieved in step 1 For each item in the file Retrieve any value security defined Next item Next file Step 3: For each value security retrieved in steps 1 and 2 If the security definition involves value from another file Then add the file to the file list for inferring the access path Next value security In one embodiment of this invention, the functionality of Program Generator 18 is extended to perform the following: Step 1: For each query item selected Retrieve any value security defined on the item. Retrieve the file(s) containing the query item. Next query item Step 2: For each file retrieved in step 1 For each item in the file Retrieve any value security defined Next item Next file Step 3: For each value security retrieved in step 2 Join the value security definition using the AND condition. Next value security In yet another embodiment of this invention, the functionality of the Model Purifier is extended to specify multiple domains for a data item. An example of an item with multiple domains is Code Id of a database file called Master Code which may contain 2 items, namely Code Id and Code Description. The data for the Master Code file could, by way of example, be as follows:
______________________________________
Code Id Code Description
______________________________________
RACE01 Chinese
RACE02 Caucasian
.
.
CITY01 San Francisco
CITY02 Singapore
.
.
.
______________________________________
In this example, this file is used to store codes for races and cities. However, races and cities are two separate domains. To break the codes into the two separate domains, the Code Id must be redefine as Race Code and City Code. The Master Code file will then have the following file aliases: Race Master Code to contain Race Code Id City Master Code to contain City Code Id To support this multiple domain for an item, we need to extend the Model Purifier to allow a user to specify the multiple domains and the corresponding file aliases. FIGS. 12 and 13 depict one embodiment of a main flowchart and Semantics Extractor 12 of the present invention which are capable of supporting multiple domains. The redefined items, eg Race Code Id and City Code Id, are stored in Keyword Library 13 and the alias file(s) are stored in Knowledge Base 14. In this embodiment, the functionality of Program Generator 18 is extended to identify the alias file(s) in the access path generated by Inference Engine 17 and to generate the necessary alias file(s) statements in the source program. There are also a number of alternative embodiments of this invention related to how the Knowledge Base is physically implemented. The various forms of implementations can be considered in the following manner: a. Precreated versus Run-time Creation b. Permanent Storage versus Temporary Storage c. Outside the Database Model versus Inside the Database Model In precreated mode, the Knowledge Base is created once and used for every query over a period of time. Should the Database Model change, then the Knowledge Base is recreated to properly reflect the changed Database Model. In run-time mode, the Knowledge Base is created at query time for every query, regardless of whether the Database Model has or has not changed since a previous query. In permanent storage, the Knowledge Base is usually implemented in secondary storage devices such as a disk drives. In temporary storage the Knowledge Base is implemented in memory such as RAM (random access memory). A Precreated Knowledge Base is preferably (although not necessarily) implemented in permanent storage while a Run-Time Knowledge Base is preferably (although not necessarily) implemented in temporary storage. The reason is that as precreated knowledge is created once and reused many times, it would be beneficial to keep it permanent so that even if the electrical power to the computer system is switched off, the Knowledge Base is retained and can be used again once power is restored. In run-time mode, as the Knowledge Base is created at query time and not reused for the next query, it is not necessary to implement the Knowledge Base in permanent storage. In POWERHOUSE fourth generation language, the database model is implemented in the data dictionary. In SQL database language, the database model is implemented in the system catalog. In one embodiment of the present invention, the Knowledge Base is implemented outside the data dictionary or system catalog. In an alternative embodiment of the present invention, the Knowledge Base is implemented to reside partially or fully inside the data dictionary or system catalog, as the data dictionary or system catalog is a form of storage that can contain the Knowledge Base. GENERATION OF CLASSES AND THEIR TRANSLATION INTO ENTITY-RELATIONSHIP MODELS An additional embodiment is taught as will now be described. The purpose of this embodiment is to provide another way for a user to interface with Report Item Selector 16 to formulate this query. Instead of a long list of items for a user to pick as described earlier the user is initially prompted for a class to pick out of a number of classes that make up his application. An entity-relationship (E-R) model of the selected class is then presented to the user for selection of the desired attributes. The desired attributes then constitute a designation of the information to be extracted from the database. It is also possible for the user to formulate the query by selecting the desired attributes from two different classes. This invention introduces a way of selectively grouping the database files into what we called classes and translating each class definition into an E-R model. These classes represent the different types of high-level objects that make up the application, e.g. employees, customers, orders, invoices, etc. This embodiment, as shown in FIG. 11, involves the following: the extension of Semantics Extractor 12 to extract more semantics from Database Model 10 the addition of Class Generator 30 the addition of E-R Model Translator 31 the addition of E-R Model of Classes File 32 Extension of Semantics Extractor 12 FIG. 12 is a flowchart of one embodiment of this invention utilizing Semantics Extractor 12 which is extended to support the extraction of additional semantics from the Database Model 10. There are several additional parts to this embodiment. Classify Binary Relationships (BR) Classify Binary Relationships step 33 classifies each binary relationship in Binary Relationships File 24 into one of the following types, in this embodiment: has.sub.-- children has.sub.-- wards has.sub.-- subtype We shall now describe these three different types of binary relationships and explain how they can be identified from the key types of the items of the database files. Earlier we have described six kinds of file linkages, namely UR (unique key to repeating key) RU (repeating key to unique key) NU (non-key to unique key) UU (unique key to unique key) RR (Repeating key to repeating key) NR (non-key to repeating key) Of these six kinds, both the RR and NR combination should not exist in a normalized data model as they represent a bad file design. Let us now consider the remaining four kinds of file linkages. For the UR kind, there are two possible types of binary relationships that can exist, namely has.sub.-- children has.sub.-- wards Let us explain the meaning of these two types with examples. Suppose we have an EMPLOYEE file with Emp.sub.-- No as a unique key and another file called SKILLS to contain the skills of every employee. The SKILLS file has a repeating key item called Emp.sub.-- No and a non-key item called Skill but no unique key. The binary relationship between these two files is as follows:
______________________________________
Source File
Item Target File
Item Relationship
______________________________________
EMPLOYEE Emp.sub.-- No
SKILLS Emp.sub.-- No
UR
______________________________________
This binary relationships is a "has.sub.-- children" type because EMPLOYEE is not only related to SKILLS but the relationship is one where EMPLOYEE considers SKILLS as its "children". This is because the records of SKILLS can only be created if their corresponding EMPLOYEE record exists. This "has.sub.-- children" type of UR binary relationship can be identified by the fact that the source file of the binary relationship has a unique key but the target file has a repeating key but no unique key. Let us consider another example involving 3 files as follows:
______________________________________
File Unique Key Repeating Key
______________________________________
CHAPTER Chap.sub.-- No --
SECTION Chap.sub.-- No, Sect.sub.-- No
Chap.sub.-- No
PARAGRAPH Chap.sub.-- No, Sect.sub.-- No, Para.sub.-- no
Chap.sub.-- No, Sect.sub.-- No
______________________________________
In this case CHAPTER has a unique key called Chap.sub.-- No and no repeating key. SECTION has a composite unique key and a repeating key within the composite unique key. These files have the following binary relationships:
______________________________________
Source File
Item Target File
Item Relationship
______________________________________
CHAPTER Chap.sub.-- No
SECTION Chap.sub.-- No
UR
SECTION Chap.sub.-- No,
PARAGRAPH Chap.sub.-- No,
UR
Sect.sub.-- No Sect.sub.-- No
______________________________________
Both these binary relationships are "has.sub.-- children" type because records of SECTION can only EXIST if the corresponding record of CHAPTER exists. Similarly, records of PARAGRAPH can only exist if the corresponding record of SECTION exist. These "has.sub.-- children" type of UR binary relationships can be identified by the fact that the source file and target file have a unique key and the target item is a repeating key within the unique key of the target file as well as the binary relationship being UR. Consider another example where a CUSTOMER file has Cust.sub.-- No as its unique key and another file INVOICES has Inv.sub.-- No as its unique key and Cust.sub.-- No as its repeating key. They both have a binary relationship as follows:
______________________________________
Source File
Item Target File
Item Relationship
______________________________________
CUSTOMERS
Cust.sub.-- No
INVOICES Cust.sub.-- No
UR
______________________________________
This binary relationship is a "has.sub.-- wards" type. In this case, we cannot consider CUSTOMER as having INVOICES as its "children" since INVOICES have their own identity through their own unique key, namely Inv.sub.-- No. Instead, we could consider CUSTOMER as a "guardian" having INVOICES as its "wards" because INVOICES belong to their respective CUSTOMER. A "has.sub.-- wards" type can be identified by the fact that both the source and target files have a unique key and the target item is not part of the target file unique key as well as the binary relationship being UR. Let us next consider the NU and RU binary relationships. The RU binary relationships are the inverse of the UR Binary relationships. We therefore classify them as either the "inverse.sub.-- of.sub.-- has.sub.-- children" or the "inverse.sub.-- of.sub.-- has.sub.-- wards". As for the NU binary relationships, we create an inverse of it and assign to this inverse a "has.sub.-- wards" type. We then store it in Knowledge Base 14. For example, suppose we have an EMPLOYEE file having a NU binary relationship with a BRANCHES file using the Branch.sub.-- Code item from both files as follows:
______________________________________
Source File
Item Target File
Item Relationship
______________________________________
EMPLOYEE
Branch.sub.-- Code
BRANCHES Branch-Code
NU
______________________________________
We create an inverse as follows with the type specified as "has.sub.-- wards":
______________________________________
Source Target
File Item File Item Type
______________________________________
BRANCHES
Branch.sub.-- Code
EM- Branch.sub.-- Code
has.sub.-- wards
PLOYEE
______________________________________
This binary relationship is then stored in Knowledge Base 14. Lastly let us consider the UU binary relationship. Such a binary relationship is called a "has.sub.-- subtype" type. Consider for example, a file called EMPLOYEE with Emp.sub.-- No as its unique key and two other files MONTHLY.sub.-- RATED.sub.-- EMP and DAILY.sub.-- RATED.sub.-- EMP both of which also have Emp.sub.-- No as their unique key. As an example, an employee is either a monthly rated employee or a daily rated employee but not both. We consider this as EMPLOYEE having MONTHLY.sub.-- RATED.sub.-- EMP and DAILY.sub.-- RATED.sub.-- EMP as its subtypes. The following binary relationships between EMPLOYEE and the two other files with EMPLOYEE as the source file reflect this "has.sub.-- subtype" type of binary relationships:
______________________________________
Source
File Item Target File Item Type
______________________________________
EM- Emp.sub.-- No
MONTHLY.sub.-- RATED.sub.-- EMP
Emp.sub.-- No
UU
PLOY-
EE
EM- Emp.sub.-- No
DAILY.sub.-- RATED.sub.-- EMP
Emp.sub.-- No
UU
PLOY-
EE
______________________________________
If a file has more than one subtype it is possible to automatically identify which of the two opposite UU binary relationships is a "has.sub.-- subtype" and which the "inverse of has subtype" by comparing them with another pair of opposite UU binary relationships. The ones with the same source file are then the "has.sub.-- subtype". However, it is not possible to do so if a file has only one subtype. In a later section of this patent description, we explain that a user will have to input this knowledge using Model Purifier 26. (Note: In the SQL language using primary keys and foreign keys, it is possible to automatically identify which of the two opposite binary relationships is a "has.sub.-- subtype" even if a file has only one subtype.) We earlier describe the extension of Model Purifier 26 to allow an user to manually specify multiple domain and corresponding file aliases. In this embodiment, this process can now be automated using the binary relationships. We shall now describe how the procedure Derive Binary Relationship 23 can be further extended to identify files that requires file aliases to be defined and to define these file aliases. A file that require file aliases to be defined can be identified by the fact that there are more than one binary relationships of the NU or RU kind which has the same target file and item. For example, suppose there are two NU binary relationships as follows:
______________________________________
Source File
Item Target File Item Type
______________________________________
EMPLOYEES
Race.sub.-- Code
MASTER.sub.-- CODE
Code NU
EMPLOYEES
Citizenship.sub.-- Code
MASTER.sub.-- CODE
Code NU
______________________________________
The target file, namely MASTER.sub.-- CODE, and target item, namely Code, is the same for both binary relationships. For each such binary relationships, we create a file alias of the target file and replace the target file of the binary relationship with the file alias, e.g. for the above two binary relationships, we create the following file aliases for the MASTER.sub.-- CODE file and store them in the Knowledge Base 14: EMPLOYEE.sub.-- Race.sub.-- Code EMPLOYEE.sub.-- Citizenship.sub.-- Code and change the binary relationships to:
__________________________________________________________________________
Source File
Item Target File Item
Type
__________________________________________________________________________
EMPLOYEE
Race.sub.-- Code
EMPLOYEES.sub.-- Race.sub.-- Code
Code
NU
EMPLOYEE
Citizenship.sub.-- Code
EMPLOYEES.sub.-- Citizenship.sub.-- Code
Code
NU
__________________________________________________________________________
Identify Entity Type of Each File In Identify Entity Type of Each File step 34 a database file is classified as one of the following types: kernel entity characteristic entity associative entity subtype entity pure lookup entity Kernel entities are entities that have independent existence, they are "what the database is really all about". In other words, kernels are entities that are neither characteristic nor associative, e.g. suppliers, parts, employees, orders, etc. are all kernel entities. A characteristic entity is one whose primary purpose is to describe or "characterize" some other entity. For example, the file SKILLS which contains the SKILLS an employee has is a characteristic entity of the EMPLOYEE entity. Characteristic entities are existence-dependent on the entity they described which can be kernel, characteristic, or associative. An associative entity is an entity whose function is to represent a many-to-many (or many-to-many-to-many, etc.) relationship among two or more other entities. For example, a shipment is an association between a supplier and a part. The entities associated may each be kernel, characteristic, or associative. A subtype is a specialization of its supertype. For example, as described earlier MONTHLY.sub.-- RATED.sub.-- EMP and DAILY.sub.-- RATE.sub.-- EMP are subtypes of EMPLOYEE. Lastly, we have entities that look like kernel entities but should not be classified as one because their purpose is solely for lookup of code description. FIG. 13a describes the procedure to identify kernel and pure lookup entities. It first identifies those database files that are either kernel or pure lookup entities using the following rule: 1) Identify those database files that have a unique key. 2) Of these database files, eliminate those that are used as a target file in any "has.sub.-- children" or "has.sub.-- subtype" binary relationships. These database files are either kernel or pure lookup entities. To distinguish between the two it next uses the following rule: 1) IF such a database file has no "children" or "sub-type", i.e. it is not a source file in any "has.sub.-- children" or "has.sub.-- subtype" binary relationship; AND 2) IF it is not a "ward" i.e., it is not a target file in any "has.sub.-- wards" binary relationship; 3) THEN it is a pure lookup entity; 4) OTHERWISE it is a kernel entity. FIG. 13b describes one embodiment of a procedure to identify characteristic and associative entities. It uses the following rule: 1) IF a database file appears more than once as a target file in "has.sub.-- children" binary relationships, THEN it is an associative entity; 2) OTHERWISE, IF it appears only once, THEN it is a characteristic entity. Subtype entities are easily identified as they are the target files in "has.sub.-- subtype" binary relationships. Note that the SYSFILES as used in FIG. 13a is a part of File Definition List 22 which has been redefined as consisting of two files, namely SYSFILES SYSFILEITEMS SYSFILES is used to store the following: file name indicator whether it is an alias file or a real file having alias files if alias file, its real file name entity type SYSFILEITEMS is used to store the following: file name item name item type (e.g. character, numeric, date) keytype of item, e.g. unique key, repeating key, or non-key Reclassify Certain Entities and Binary Relationship 35 Even though we have earlier identified some files as kernel entities, some of these kernel entities should be reclassified as pure lookup entities. Consider for example a kernel entity EMPLOYEE having an associative entity LANGUAGE.sub.-- SPOKEN whose other kernel entity is LANGUAGE. LANGUAGE.sub.-- SPOKEN has two items, namely a repeating key called Emp.sub.-- No and another repeating key called Language.sub.-- Code. The LANGUAGE file has only two items, namely a unique key item called Language.sub.-- Code and a non-key item called Language.sub.-- Desc. Even though we earlier identified LANGUAGE as a kernel entity since it has LANGUAGE.sub.-- SPOKEN as its "children", LANGUAGE should be reclassified as a pure lookup entity since it is used solely by the LANGUAGE.sub.-- SPOKEN file to obtain the description of the Language.sub.-- Code. FIG. 14 is a flow chart depicting one embodiment of a procedure to identify these kernel entities and modify them to pure lookup entities. It uses the following rule to do this: 1) IF a kernel entity has only associative entities and no characteristic entities; and 2) IF it is not a target file in any "has.sub.-- ward" binary relationships; 3) THEN modify the kernel entity into a pure lookup entity. Also, modify the associative entities of this kernal entity into characteristic entities if the associative entities has only one other entity that associates it. Next we access all those "has.sub.-- children" and "has.sub.-- wards" binary relationships whose source file is one of these pure lookup entities. We then modify them into a new type called "inverse.sub.-- of.sub.-- pure lookup" type. We use the word "inverse" as the lookup direction is not from source file to target file but from target file to source file. Class Generator 30 Class Generator 30 (FIG. 11 ) generates a definition of a class for each kernel entity in the database and stores this definition as a Class Definition File (CDF) in Knowledge Base 14. A class is a cluster of files whose structure is a tree. The root of the tree is a kernel entity which defines the core attributes of the class. The tree has the following main branches: (i) a branch for each of the subtypes of the root kernel entity (ii) a branch for each of the "wards" of the root kernel entity (iii) a branch for each of the characteristic entities of the root kernel entity (iv) a branch for each of the associative entities of the root kernel entity These branches are derived using the "has.sub.-- subtype", "has.sub.-- wards", and "has.sub.-- children" binary relationships with the root kernel entity being the source file. Each of the above subtype, characteristic, and associative entities could also have their own branches which are their characteristic or associative entities. The latter characteristic or associative entities could also have their own characteristic or associative entities, and so forth. These branches are derived using the "has.sub.-- children" binary relationship with the target files of these binary relationships forming the new branches. The procedure of FIG. 15a and 15b together with the sub-procedure Include.sub.-- File (list) of FIG. 16a and 16b are used to derive the above branches which are then stored as a set of lists in Class Definition File A (CDF A). An example of such a list is: File=EMPLOYEE item=Emp.sub.-- No File=BILLINGS item=Emp.sub.-- No BR Type="has.sub.-- children" item=Proj.sub.-- No File=PROJECTS item=Proj.sub.-- No BR type="inv.sub.-- of.sub.-- has.sub.-- children" This list contains a file EMPLOYEE, linked to a file BILLINGS, which is linked to file PROJECTS. The binary relationship (BR) type from EMPLOYEE to BILLINGS is "has.sub.-- children" using the item Emp.sub.-- No from both files and the BR type from BILLINGS to PROJECTS is "inv.sub.-- of.sub.-- has.sub.-- children" using the item Proj.sub.-- No from both files. Let us now describe how a list in CDF A is produced using this procedure. It starts by initializing a list to the first kernel entity in SYSFILES. This kernel entity forms the root kernel entity of a class to be generated. Next it looks for a subtype entity of this kernel entity using a sorted SYSFILES and the Binary Relationships File 24. This file has been sorted in descending order of subtypes, kernels, associative, characteristics, and pure lookups. If one subtype entity is found, it is added to the list together with the name of the corresponding binary relationship type, which in this case is "has.sub.-- subtype" and the names of the items used. It then calls on a sub-procedure Include.sub.-- File (list), for example as depicted in FIG. 16a and 16b, to find associative and characteristic entities of the subtype entity. If a characteristic entity is found, it is added to the list together with the name of the corresponding binary relationship type which in this case is "has.sub.-- children" and the names of the items used. The sub-procedure Include.sub.-- File (list) is then called again, this time to find other associative entities or characteristic entities of the characteristic entity. If no such entity can be found the list is then written to the Class Definition File A (CDF A). Besides these branches, the tree of a class also has what we called "lookup" branches originating from each node in the above branches. These "lookup" branches are derived using the "inv.sub.-- of.sub.-- has.sub.-- wards" and "pure.sub.-- lookup" binary relationships, with the node being the source file and the target file forming new branches. Furthermore, the new branches could also have their own new "lookup" branches and so forth. These subsequent "lookup" branches are formed using not only the "inv.sub.-- of.sub.-- has.sub.-- wards" and "pure.sub.-- lookup" binary relationships but also the "inv.sub.-- of.sub.-- has.sub.-- children" binary relationships with the target file forming the new "lookup" branches. The procedure of FIG. 17 together with the sub-procedure Process.sub.-- File of FIGS. 1a and 18b are suitable for use to derive these branches, which are then stored as a set of lists in Class Definition File B (CDF B). Let us describe one example of how a list in CDF B is produced using this procedure. It starts by reading in the first record of CDF A. A list is then initialized to the first file in this CDF A record. A check is made to see if this file has been processed before. Since it is not, the sub-procedure Process.sub.-- File (list), for example as depicted in FIG. 18a and 18b, is then called to look for a "lookup" file for this file. If there is such a file, it is added to the lists together with the corresponding binary relationship type and the sub-procedure is called again. If no further "lookup" file can be found, the list is written to CDF B. Let us now apply the above procedures on the exemplary personnel system below to generate the classes for this system.
______________________________________
File Items Item Type
Key Type
______________________________________
EMPLOYEE Emp.sub.-- No
Numeric Unique key
Emp.sub.-- Name
Character
Non-key
Branch.sub.-- No
Numeric Non-key
Race.sub.-- Code
Character
Non-key
Address Character
Non-key
Salary Numeric Non-key
MANAGER Emp.sub.-- No
Numeric Unique key
Co.sub.-- Car.sub.-- No
Character
Non-key
NON.sub.-- MANAGER
Emp.sub.-- No
Numeric Unique key
Union.sub.-- M.sub.-- No
Character
Non-key
BRANCH Branch.sub.-- No
Numeric Unique key
Branch.sub.-- Name
Character
Non-key
Br.sub.-- Tot.sub.-- Expenses
Numeric Non-key
Country.sub.-- No
Numeric Non-key
RACE.sub.-- CODE
Race.sub.-- Code
Character
Unique key
Race.sub.-- Desc
Character
Non-key
SKILLS Emp.sub.-- No
Numeric Repeating key
Skill Character
Non-key
PROJECT Proj.sub.-- No
Numeric Unique key
Proj.sub.-- Name
Character
Non key
Cust.sub.-- No
Numeric Non-key
BILLINGS Emp.sub.-- No
Numeric Repeating key
Proj.sub.-- No
Numeric Repeating key
Month Date Non-key
Amount Numeric Non-key
CUSTOMER Cust.sub.-- No
Numeric Unique key
Cust.sub.-- Name
Character
Non-key
EXPENSES Branch.sub.-- No
Numeric Repeating key
Month Date Non-key
Adv.sub.-- Exp
Numeric Non-key
Pers.sub.-- Exp
Numeric Non-key
COUNTRY Country.sub.-- No
Numeric Unique key
Country.sub.-- Name
Character
Non-key
______________________________________
Using extended Semantics Extractor 12 of FIGS. 11 and 12, the following binary relationships are derived:
__________________________________________________________________________
Source Film
Item Target File
Item Type
__________________________________________________________________________
EMPLOYEE
Emp.sub.-- No
SKILLS Emp.sub.-- No
has.sub.-- children
EMPLOYEE
Emp.sub.-- No
BILLINGS Emp.sub.-- No
has.sub.-- children
EMPLOYEE
Emp.sub.-- No
MANAGER Emp.sub.-- No
has.sub.-- subtype
EMPLOYEE
Emp.sub.-- No
NON.sub.-- MANAGER
Emp.sub.-- No
has.sub.-- subtype
BRANCH Branch.sub.-- No
EMPLOYEE Branch.sub.-- No
has.sub.-- ward
BRANCH Branch.sub.-- No
EXPENSES Branch.sub.-- No
has.sub.-- children
COUNTRY
Country.sub.-- No
BRANCH Country.sub.-- No
inv. of pure.sub.-- lookup
RACE.sub.-- CODE
Race.sub.-- Code
EMPLOYEE Race.sub.-- Code
inv. of pure.sub.-- lookup
PROJECT
Proj.sub.-- No
BILLINGS Proj.sub.-- No
has.sub.-- children
CUSTOMER
Cust.sub.-- No
PROJECT Cust.sub.-- No
inv. of pure.sub.-- lookup
__________________________________________________________________________
Also, the entity type of each file in SYSFILES is as follows:
______________________________________
File Entity Type
Short Name of File
______________________________________
EMPLOYEE Kernel K1
MANAGER Subtype S1
NON-MANAGER Subtype S2
BRANCH Kernel K2
RACE.sub.-- CODE
Pure Lookup
L1
SKILLS Characteristic
C1
PROJECT Kernel K3
BILLINGS Associative
A1
CUSTOMER Pure Lookup
L2
EXPENSES Characteristic
C2
COUNTRY Pure Lookup
L3
______________________________________
Let us first apply the exemplary procedure of FIGS. 15a and 15b on the personnel system example. It first initializes a list to the first kernel entity in SYSFILES which is EMPLOYEE (K1). This means that it is going to generate Class Definition File A for the EMPLOYEE class. Next it uses a sorted SYSFILES and the Binary Relationship File 24 to find other entities to add to this list. The sorted SYSFILES contains files in descending order of subtypes, kernels, associatives, characteristics and pure lookups. The next entity added to the list is S1 which is MANAGER, with the items being Emp.sub.-- No and the BR type being "has.sub.-- subtype". This list is then written to CDF A. The list is initialized again to K1 and S2 added to it, with the items being Emp.sub.-- No and the BR type being "has.sub.-- subtype", after which it is written to CDF A. After the subtypes have been processed, the procedure initializes list K1 and search for those kernel entities that are "wards" of K1. However, K1 has no "wards" and so there are no such entities to add to the list. Next the procedure searches for associative entities of K1. K1 has one associative entity, namely BILLINGS (A1) so A1 is added to the list, with the items being Emp.sub.-- No and the BR type being "has.sub.-- children". Next the procedure includes PROJECTS (K3) in this list as it constitutes the other entity that associates A1, with the items being Proj.sub.-- No and the BR type being "inv.sub.-- of.sub.-- has.sub.-- children". This list containing K1, A1, K3 is then written to CDF A. After this, the procedure initializes the list to K1 again and search for characteristic entities of K1. K1 has one characteristic entity, namely SKILLS (C1), so C1 is added to the list with the items being Emp.sub.-- No and the BR type being "has.sub.-- children". The list is then written to CDF A. At this point CDF A contains the following lists: a list having K1, S1 a list having K1, S2 a list having K1, A1, K3 a list having K1, C1 The procedure next generates the lists for the next kernel entity, namely BRANCHES (K2). K2 has no subtype and associative entities but it has (EMPLOYEE) K1 as its "ward". This produces the list K2, K1, with the items being Branch.sub.-- No and the BR type being "has.sub.-- wards". This list is written to CDF A. It also has EXPENSES (C2) as its characteristic entity. This produces the list K2, C2, with the items being Branch.sub.-- No and the BR type being "has.sub.-- children", which is also written to CDF A. Finally, the list for the third and last kernel entity, namely PROJECTS (K3), is produced. However, there is only one list, namely a list having K3, A1, K1 since PROJECTS has an associative entity only which is BRANCH (A1), with K1 being the other entity that associates A1. In this list the items for K3 and A1 is Proj.sub.-- No with the BR type being "has.sub.-- children". The items for A1 and K1 is Emp.sub.-- No with the BR type being "inv.sub.-- of.sub.-- has.sub.-- children". Let us next apply the exemplary procedure of FIG. 17 on the personnel system example. It uses the lists of CDF A derived earlier to generate the lists for CDF B. Using the same personnel system it first initializes a list to the first entity in the first list of CDF A, namely K1. It then adds to this list entities that are lookup entities to K1. A lookup entity is a target file in a "pure.sub.-- lookup" or "inv.sub.-- of.sub.-- has.sub.-- ward" binary relationships, with K1 as the source file. K1 looks up on BRANCH (K2), so K2 is added to the list with the items being Branch.sub.-- No and the BR type being "inv.sub.-- of.sub.-- has.sub.-- wards". Next a check is made on K2 to see if it too has lookup entities. K2 in fact has one, namely COUNTRY (L3). L3 is therefore added to the list with the items being Country.sub.-- No and the BR type being "pure.sub.-- lookup". At this point the list contains K1, K2, L3. Since L3 has no lookup entities, this list is written to the CDF B. Next the procedure returns to K2 to see if K2 has other lookup entities. Since it does not, the procedure returns to K1. It finds that K1 has another lookup entity, namely RACE.sub.-- CODE (L1). The list containing K1, L3 with the items being Race.sub.-- Code and the BR type being "pure.sub.-- lookup", is then written to CDF B. The next entity in the first list of CDF A is next processed. This entity is MANAGER (S1). It, however, does not have any lookup entities and so it is ignored. As there is no more entity on the first list of CDF A, the first entity in the second list of CDF A is considered for processing. A check is made first to see if this entity has already been processed earlier. This entity is K1 which has been processed earlier and so it is ignored. The next entity is NON.sub.-- MANAGER (S2) which has not being processed. However, it does not have any lookup entities and so it is ignored. The above procedure is again applied for the next list of CDF A, which contains K1, A1, K3. As K1 has already been processed and A1 has no lookup entity, no list is produced for either of them. However, K3 (PROJECT) has a lookup entity, namely CUSTOMERS (L2). So the list K3, L2 is produced with the items being Cust.sub.-- No and the BR type being "pure.sub.-- lookup". It is written to CDF B. The next list of CDF A is K1, C1. However, since K1 has already been processed and C1 (SKILLS) has no lookup entities, both are ignored. The above procedure is applied to the remaining lists in CDF A. In this example, only one list is produced, containing K2, L3 with the items being Country.sub.-- No and the BR type being "pure.sub.-- lookup". Besides CDF A and CDF B, the Class Definition File also includes CDF C. CDF C includes a single list which contains all the pure lookup entities. The CDF C for the personnel system example contains L1, L2, L3. E-R Model Translator E-R Model Translator 31 (FIG. 11) is used to produce an Entity-Relationship (E-R) model for each class using CDF A, CDF B and Binary Relationships File 24. Many different embodiments of an E-R model can be produced. The following describes one example of a procedure to produce one embodiment of an entity-relationship (E-R) model of a class for all the classes except the class containing the pure lookup entities. This procedure begins by creating another file identical to SYSFILEITEMS. This duplicate file is called TEMPFILEITEMS. Next, for each "inverse.sub.-- of.sub.-- pure.sub.-- lookup" binary relationships in Binary Relationship File 24, it inserts all the items of the file used as source file in this binary relationship except those items of the file that are used as source items into this TEMPFILEITEMS at the point which corresponds to the target items of this binary relationship. Next, for each "has.sub.-- children", "has.sub.-- wards", or | ||||||