Systems and methods for managing foreign key constraints

• US 20100241637A1
• Filed: 03/23/2009
• Published: 09/23/2010
• Est. Priority Date: 03/23/2009
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Kissner, Jurgen G,, Fischbach, Andreas
• Granted Patent: US 8,010,521 B2
• Time in Patent Office: Days
• US Class Current: 707/752
• CPC Class Codes: G06F 16/24565 Triggers; Constraints

Abstract

Systems and methods consistent with the invention may include generating, via a processor, an entity relationship model to indicate a plurality of types of relationships between the entities, determining relationships between the database tables corresponding to the entities by using the entity relationship model, wherein the database tables are stored in a computer memory device, generating a graph based on the entity relationship model, generating sets of database elements representing the entities and corresponding data objects stored in the database tables, wherein the element sets are generated based on the generated graph, assigning ordering numbers to the database element sets and database elements included in the database element sets and executing the query transaction by using the data objects in an order based on the ordering numbers.

Claims

1. A computer-implemented method of executing a query transaction by using data objects stored in database tables corresponding to entities of an organization, comprising:

generating, via a processor, an entity relationship model to indicate a plurality of types of relationships between the entities;

determining relationships between the database tables corresponding to the entities by using the entity relationship model, wherein the database tables are stored in a computer memory device;

generating a graph based on the entity relationship model;

generating sets of database elements representing the entities and corresponding data objects stored in the database tables, wherein the element sets are generated based on the generated graph;

assigning ordering numbers to the database element sets and database elements included in the database element sets; and

executing the query transaction by using the data objects in an order based on the ordering numbers.

2. The method of claim 1, wherein the entity relationship model is represented by a directed graph having vertices representing the entities and arrows representing relationships between the entities.

3. The method of claim 2, wherein the relationships between the entities are of at least one of types one-to-one, one-to-many, many-to-one, and many-to-many.

4. The method of claim 3, wherein the generated graph is a reduced graph that is generated based on the directed graph.

5. The method of claim 1, wherein the entity relationship model defines foreign key constraints that are used to enforce the relationships between the database tables.

6. The method of claim 1, wherein the sets are cyclic sets, such that each database element in one of the sets is in a cyclic relationships with all the remaining database elements of the one set, and the database elements of the one set are not in a cyclic relationship with database elements of a second one of the sets.

7. The method of claim 1, wherein the query transaction comprises one or more of insertion, deletion and update operations.

8. The method of claim 7, wherein the order of execution of the query transaction is based on a type of the operation, such that when the query transaction is an insertion operation, the transaction is executed in the same order as indicated by the ordering numbers.

9. The method of claim 7, wherein the execution order of the query transaction is based on a type of the operation, such that when the query transaction is a deletion operation, the transaction is executed in a sequence reverse to the one indicated by the ordering numbers.

10. The method of claim 1, wherein the entity relationship model is generated by determining the entities and the plurality of types of relationships based on metadata stored in databases of the organization.

11. The method of claim 1, wherein the execution order of the query transaction is based on a type of the operation, such that when the query transaction is an update operation, the transaction is executed in an arbitrary sequence provided by a user.

19. The system of claim 1, wherein the query transaction comprises one or more of insertion, deletion and update operations, and the order of execution of the transaction is based on the type of the operation being performed.

12. A computer-implemented system for executing a query transaction by using data objects stored in database tables corresponding to entities of an organization, comprising:

a memory device including a plurality of modules; and

a data processor for executing the plurality of modules to;

generate an entity relationship model to indicate a plurality of types of relationships between the entities;

determine relationships between the database tables corresponding to the entities by using the entity relationship model, wherein the database tables are stored in a computer memory device;

generate a graph based on the entity relationship model;

generate sets of database elements representing the entities and corresponding data objects stored in the database tables, wherein the element sets are generated based on the generated graph;

assign ordering numbers to the database element sets and database elements included in the database element sets; and

execute the query by using the data objects in an order based on the ordering numbers.

13. The system of claim 12, wherein the plurality of modules include an entity manager factory, a constraint resolution engine, and a query processing module.

14. The system of claim 12, wherein the entity relationship model is a directed graph having vertices representing the entities and arrows representing relationships between the entities.

15. The system of claim 14, wherein the relationships between the entities are of at least one of types one-to-one, one-to-many, many-to-one, and many-to-many.

16. The system of claim 15, wherein the generated graph is a reduced graph that is generated based on the directed graph.

17. The system of claim 13, wherein the entity relationship model defines foreign key constraints that are used to enforce the relationships between the database tables.

18. The system of claim 12, wherein the sets are cyclic sets, such that each database element in one of the sets is in a cyclic relationships with all the remaining database elements of the one set, and the database elements of the one set are not in a cyclic relationship with database elements of a second one of the sets.

20. A computer-readable storage medium comprising instructions, which when executed on a processor, cause the processor to perform a method of executing a query transaction by using data objects stored in database tables corresponding to entities of an organization, the method comprising:

generating an entity relationship model to indicate a plurality of types of relationships between the entities;

determining relationships between the database tables corresponding to the entities by using the entity relationship model;

generating a graph based on the entity relationship model;

generating sets of database elements representing the entities and corresponding data objects stored in the database tables, wherein the element sets are generated based on the generated graph;

assigning ordering numbers to the database element sets and database elements included in the database element sets; and

executing the query transaction by using the data objects in an order based on the ordering numbers.