Method and system for identifying and analyzing hidden data relationships in databases

A system for identifying and analyzing hidden relationships in application databases, the system in communication with at least one application database and a data modeling system, the system comprising:

• a processor executing program instructions stored in a memory;
• a data analysis engine in communication with the processor and configured to:
• analyze one or more database query language statements, during a database session, to identify a join indicator representative of data fields from two or more tables which are joined using a value that is common to each data field, wherein the join indicator is identified in each of the one or more database query language statements retrieved from one or more log tables in an application database;
• generate a data definition language file, based on the identified join indicators of the database query language statements, wherein the data definition language includes relationship between the two or more tables;
• and
• ascertain a relationship between the two or more tables as a hidden relationship if content of the created data definition language file is not defined in a database schema of the application database which is represented in a physical data model,
• append the data definition language file to corresponding database query language statement in an event the hidden relationship is ascertained;
• wherein the data analysis engine is configured to perform the analysis and generation until all the database query language statements in the log tables are analyzed;
• and
• a logical data definition language generation engine in communication with the processor and configured to generate a logical data definition language file, based on the generated data definition language file, if the hidden relationship is ascertained so as to update a logical data model, in the data modeling system, with the content of the logical data definition language file,
• whereby the logical data model represents hidden relationships between tables in the application database.

A computer program product, comprising:

• a non-transitory computer-readable medium having computer-readable program code stored thereon, the computer-readable program code comprising instructions that, when executed by a processor, cause the processor to:
• (a) analyze one or more database query language statements, during a database session, to identify a join indicator representative of data fields from two or more tables which are joined using a value that is common to each data field, wherein the join indicator is identified in each of the one or more database query language statements retrieved from one or more log tables in an application database;
• (b) generate a data definition language file based on the identified join indicators of the database query language statements, wherein the data definition language includes relationship between the two or more tables;
• (c) repeat steps (a) and (b) until all the database query language statements in the log tables are analyzed;
• (d) ascertain a relationship between the two or more tables as a hidden relationship if content of the created data definition language file is not defined in a database schema of the application database which is represented in a physical data model;
• (e) append the data definition language file to corresponding database query language statement in an event the hidden relationship is ascertained;
• and
• (f) generate a logical data definition language file, based on the created data definition language file, if the hidden relationship is ascertained so as to update a logical data model with the content of the logical data definition language file, whereby the logical data model represents hidden relationships between the two or more tables in the application database.