Method and apparatus for creating a well-formed database system using a computer

• US 7,461,076 B1
• Filed: 07/25/2000
• Issued: 12/02/2008
• Est. Priority Date: 07/25/2000
• Status: Expired due to Term

• Current Assignee: E Piphany Incorporated
• Original Assignee: EPiphany Incorporated (Infor Incorporated)
• Inventors: Wegbreit, Eliot Leonard, Walsh, Gregory Vincent, Weissman, Craig David, Jain, Ankur S.
• Primary Examiner(s): Corrielus; Jean M
• Assistant Examiner(s): COLAN, GIOVANNA B
• Application Number:
  US09/625,518
• Time in Patent Office: 3,052 Days
• Field of Search: 707/103.R, 707/104.1, 707/102, 707/509, 707/101, 707/3, 717/137
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A method of defining a well-formed database system by defining the organization of the data in the database, and by defining the operations for that data, is described. The definition can be used to automatically create and populate the well-formed database system. The well-formed database system conforms to rules of correctness and produces results that conform to the rules. The organization is defined by a data organization definition that specifies tables, their columns, and the relationships between tables. The operations define procedures that operate on the tables and the table columns. Importantly, the operations are defined along with the tables, columns, and relationships, so that the resulting system is well-formed.

Claims

1. A method of creating a system for creating a well-formed database system using a computer, the method comprising:

the computer accessing a definition of the system, the definition defining a schema for use by the system, the schema defining a set of tables, a set of columns that correspond to the set of tables, and a set of relationships between the tables of the set of tables, the definition further defining a set of operations for manipulating the data, the set of operations defining programs that operate on the set of tables and the set of table columns; and

the computer using the definition to generate the set of tables.

2. The method of claim 1 wherein the set of tables includes a first table and a second table, wherein the first table includes a first column, wherein the second table includes a second column, and wherein the first column and the second column are related by a join and are therefore guaranteed to be from the same domain.

3. The method of claim 1 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to one relationship, and wherein the generating the set of tables includes automatically generating a foreign key column in the first table, wherein the foreign key column is for holding a foreign key to the second table.

4. The method of claim 1 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to many relationship, and wherein the generating the set of tables includes automatically generating an associative table corresponding to the first table and the second table, and wherein the associative table has a unique value created for each unique many-to-many relationship between the first table and the second table.

5. The method of claim 1 wherein the set of tables includes a first table and a second table, and wherein the first table includes one or more columns from the second table, and wherein said one or more columns are automatically populated from the one or more columns.

6. The method of claim 1 wherein the computer using the definition to generate the set of tables also includes the computer performing at least some of the set of operations on at least some of the set of tables.

7. The method of claim 1 wherein a transaction type column is automatically included in some tables of the set of tables.

8. The method of claim 1 wherein a date column is automatically included in some tables of the set of tables.

9. The method of claim 1 wherein a source system key column is automatically included in some tables of the set of tables.

10. The method of claim 1 wherein the definition defines a set of source system extraction operations, wherein the set of source system extraction operations are for extracting data from a source system and for manipulating the data for populating the database, and wherein the set of source system extraction operations correspond to the schema definition.

11. The method of claim 10 wherein the source system extraction operations correspond to the schema definition by populating source system data into the database system according the schema definition.

12. The method of claim 1 wherein the definition defines a set of aggregates for the database system, the set of aggregates corresponding to the schema definition, the method further comprising:

the computer using the definition to create a set of aggregate tables corresponding to the set of aggregates; and

populating the set of aggregate tables.

13. The method of claim 12 wherein the set of aggregates corresponds to the schema definition by defining which aggregates should be made from which tables in the database system.

14. The method of claim 12 wherein the definition defines an aggregate operation for an aggregate of the set of aggregates.

15. The method of claim 14 wherein the aggregate operation includes a SUM operation.

16. The method of claim 14 wherein the aggregate operation includes an AVERAGE operation.

17. The method of claim 1 wherein the definition includes a user interface definition for querying the database and for presenting results, the user interface definition corresponding to the schema definition.

18. The method of claim 17 wherein the user interface definition specifies which columns from which tables can be used in a query.

19. The method of claim 1 wherein the definition defines a set of source system extraction operations, a set of aggregates, and a user interface definition, that correspond to the schema definition.

20. The method of claim 1 wherein the database system includes a datamart, wherein the schema definition includes a star schema definition, wherein the set of tables includes a set of fact tables and a set of dimension tables.

21. A system comprising:

a database system;

a first program for accessing a definition of the schema for the database system, the schema defining a set of tables, a set of columns corresponding to the set of tables, and a set of relationships between the tables of the set of tables, the definition further defining a set of operations for manipulating the data, the set of operations defining programs that operate on the set of tables and the set of table columns, the first program further for using the definition to generate the set of tables.

22. The system of claim 21 wherein the set of tables includes a first table and a second table, wherein the first table includes a first column, wherein the second table includes a second column, and wherein the first column and the second column are related by a join and are therefore guaranteed to be from the same domain.

23. The system of claim 21 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to one relationship, and wherein the generating the set of tables includes automatically generating a foreign key column in the first table, wherein the foreign key column is for holding a foreign key to the second table.

24. The system of claim 21 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to many relationship, and wherein the generating the set of tables includes automatically generating an associative table corresponding to the first table and the second table, and wherein the associative table has a unique value created for each unique many-to-many relationship between the first table and the second table.

25. The system of claim 21 wherein the set of tables includes a first table and a second table, and wherein the first table includes one or more columns from the second table, and wherein said one or more columns are automatically populated from the one or more columns.

26. The system of claim 21 wherein the first program includes an enterprise manager for accessing the definition, causing the generation of the set of tables, and causing the population of the tables.

27. The system of claim 21 further comprising a database, the database for storing the set of tables.

28. The system of claim 21 further comprising an aggregate building program for accessing a definition of a set of aggregates and the definition of the schema and for generating the set of aggregates from the definition of the set of aggregates and the definition of the schema.

29. The system of claim 21 further comprising a query and reporting program for generating a user interface from a definition of the user interface and the definition of the schema.

30. A system comprising:

means for accessing a definition of the system, the definition defining a schema for use by the system, the schema defining a set of tables, a set of columns corresponding to the set of tables, and a set of relationships between the tables of the set of tables, the definition further defining a set of operations for manipulating the data, the set of operations defining programs that operate on the set of tables and the set of table columns; and

means for using the definition to generate the set of tables.

31. The system of claim 30 wherein the set of tables includes a first table and a second table, wherein the first table includes a first column, wherein the second table includes a second column, and wherein the first column and the second column are related by a join and are therefore guaranteed to be from the same domain.

32. The system of claim 30 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to one relationship, and wherein the generating the set of tables includes automatically generating a foreign key column in the first table, wherein the foreign key column is for holding a foreign key to the second table.

33. The system of claim 30 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to many relationship, and wherein the generating the set of tables includes automatically generating an associative table corresponding to the first table and the second table, and wherein the associative table has a unique value created for each unique many-to-many relationship between the first table and the second table.

34. The system of claim 30 wherein the set of tables includes a first table and a second table, and wherein the first table includes one or more columns from the second table, and wherein said one or more columns are automatically populated from the one or more columns.

35. The system of claim 34 wherein the definition of the system further includes a definition of the aggregates for the system, the system further comprising:

means for generating a set of aggregates from the definition of the aggregates and the definition of the schema.

36. The system of claim 33 wherein the definition of the system further includes a definition of a user interface for the system, the system further comprising:

means for generating the user interface from the definition of the user interface and the definition of the schema.

37. The system of claim 33 wherein the definition of the system includes a definition of aggregates for use in the system and a definition of a query and reporting mechanism interface for the system, the set of tables includes a set of fact tables and a set of dimension tables, and wherein the system further comprises:

means for generating the set of fact tables;

means for generating the set of dimension tables;

means for generating a set of aggregate tables; and

means for generating a query and reporting mechanism interface.

38. A computer program product comprising:

a memory medium; and

a computer program stored on the memory medium, the computer program comprising instructions for accessing a definition of a system, the definition defining a schema for use by the system, the schema defining a set of tables, a set of columns corresponding to the set of tables, and a set of relationships between the tables of the set of tables, the definition further defining a set of operations for manipulating the data, the set of operations defining programs that operate on the set of tables and the set of table columns, and instructions for using the definition to generate the set of tables.

39. The computer program product of claim 38 wherein the set of tables includes a first table and a second table, wherein the first table includes a first column, wherein the second table includes a second column, and wherein the first column and the second column are related by a join and are therefore guaranteed to be from the same domain.

40. The computer program product of claim 38 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to one relationship, and wherein the generating the set of tables includes automatically generating a foreign key column in the first table, wherein the foreign key column is for holding a foreign key to the second table.

41. The computer program product of claim 38 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to many relationship, and wherein the generating the set of tables includes automatically generating an associative table corresponding to the first table and the second table, and wherein the associative table has a unique value created for each unique many-to-many relationship between the first table and the second table.

42. The computer program product of claim 38 wherein the set of tables includes a first table and a second table, and wherein the first table includes one or more columns from the second table, and wherein said one or more columns are automatically populated from the one or more columns.

43. A computer data signal embodied in a carrier wave comprising:

a computer program, the computer program comprising instructions for accessing a definition of a system, the definition defining a schema for use by the system, the schema defining a set of tables, a set of columns corresponding to the set of tables, and a set of relationships between the tables of the set of tables, the definition further defining a set of operations for manipulating the data, the set of operations defining programs that operate on the set of tables and the set of table columns, and instructions for using the definition to generate the set of tables.

44. The computer data signal embodied in the carrier wave of claim 43 wherein the set of tables includes a first table and a second table, wherein the first table includes a first column, wherein the second table includes a second column, and wherein the first column and the second column are related by a join and are therefore guaranteed to be from the same domain.

45. The computer data signal embodied in the carrier wave of claim 43 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to one relationship, and wherein the generating the set of tables includes automatically generating a foreign key column in the first table, wherein the foreign key column is for holding a foreign key to the second table.

46. The computer data signal embodied in the carrier wave of claim 43 wherein the set of tables includes a first table and a second table, and wherein the definition defines that the first table relates to the second table by a many to many relationship, and wherein the generating the set of tables includes automatically generating an associative table corresponding to the first table and the second table, and wherein the associative table has a unique value created for each unique many-to-many relationship between the first table and the second table.

47. The computer data signal embodied in the carrier wave of claim 43 wherein the set of tables includes a first table and a second table, and wherein the first table includes one or more columns from the second table, and wherein said one or more columns are automatically populated from the one or more columns.