Database table relationship exploration method for LLM-text2sql

Abstract

The application belongs to the technical field of databases, and particularly relates to a database table relationship exploration method for LLM-Text2SQL. The method adopts a multi-stage collaborative processing strategy, integrates database metadata and data content features, and performs semantic enhancement and structured completion on original information through a large language model. On this basis, a potential association candidate set is screened by combining an algorithm based on feature similarity, and multi-dimensional verification and judgment of the association relationship are performed by fusing a predefined rule and a large language model inference mechanism, so as to finally generate an entity relationship graph supporting interactive editing and iterative optimization. The method realizes end-to-end automatic processing from a heterogeneous database with missing constraints and damaged data integrity to a normalized ER graph, and lays a solid foundation for significantly improving the success rate of natural language SQL generation based on a large language model.

Description

Technical Field

[0001] This invention belongs to the field of database technology, specifically relating to a method for exploring database table relationships for LLM-Text2SQL. Background Technology

[0002] Databases play a crucial role in enterprise IT due to the massive amounts of information they contain. Accessing data from databases requires specialized Structured Query Language (SQL), and many non-technical personnel urgently need to access and analyze data using natural language. Large Language Models (LLMs), with their powerful semantic understanding and generation capabilities, have become the mainstream solution for generating SQL based on user questions (Text2SQL or NL2SQL). The most direct method is to input the user question and database schema into the LLM (Large Language Model) and rely on its natural language understanding capabilities to generate SQL to complete data access.

[0003] The essence of relational databases is to abstract real-world entities into two-dimensional tables. Databases often contain a massive number of tables, each with several fields; table relationships define the logical connections between entities. A data query often requires multiple tables to be joined. In the theoretical model, relational databases express table relationships through primary and foreign keys; however, in practical applications, to optimize performance, table relationships are often implemented through business constraints rather than explicitly expressed in the database schema, rather than using foreign keys. In such cases, technical personnel familiar with database table structures and business constraints can write correct SQL to access the data.

[0004] Clearly understanding the relationships and constraints between data tables is fundamental for large language models to accurately perform Text2SQL tasks. Since table relationships are not expressed in the database schema in practical applications, it is necessary to manually maintain these relationships to meet the Text2SQL requirements of large models. However, due to the complexity of the domain's business, the complexity of the database, and the varying levels of understanding of the database and business by personnel, simply manually maintaining complete database table relationships is impractical. Summary of the Invention

[0005] To overcome the problems in the prior art, this invention proposes a database table relationship exploration method for LLM-Text2SQL.

[0006] The technical solution of the present invention to solve the above-mentioned technical problems is as follows:

[0007] This invention provides a database table relationship exploration method for LLM-Text2SQL, comprising the following steps:

[0008] Step 100: Obtain metadata by scanning the database system directory and analyze the characteristics of the data content;

[0009] Step 200: Candidate Association Generation: For each field, similar fields are retrieved using vector similarity, and related fields are retrieved using business documents. The similarity between fields and the degree of inclusion of values ​​are calculated. The semantics of the fields are parsed using a large language model, and the scope of candidate associations is expanded by combining the business domain knowledge base.

[0010] Step 300: Establish a dual verification mechanism that integrates rule engine and large language model reasoning to perform multi-dimensional verification and judgment of candidate association relationships;

[0011] Step 400: Convert the validated relationships into a standard ER diagram model and export the ER diagram in an interchangeable format.

[0012] Further, step 100 includes:

[0013] Initialize the property graph. The initial property graph is a 7-tuple, including the property graph nodes, edges, property names, sets of values, total functions, relations, and partial functions.

[0014] Retrieve information about all tables from the system directory of the relational database, treat all tables and their information as table attributes, and merge them into the initial attribute graph;

[0015] Extract fields and their basic information from each table in the relational database, and analyze all values ​​under each field to generate field content features, resulting in an attribute graph containing basic field information and content features; connect fields with their respective tables using edges to obtain an attribute graph describing the field-table connection relationship; merge the attribute graph containing basic field information and content features and the attribute graph describing the field-table connection relationship into the first attribute graph to obtain the second attribute graph.

[0016] For each field in each table, generate a description for each field to obtain a field description attribute graph, which is then merged into the second attribute graph to obtain the third attribute graph;

[0017] For each description of each field, a semantic vector is generated to form a semantic description attribute graph, which is then added to the third attribute graph to obtain the fourth attribute graph.

[0018] Furthermore, step 200 specifically includes:

[0019] For each field that meets the preset conditions, similar fields are retrieved using vector similarity, and highly relevant fields are retrieved from related business documents. Similar fields, fields with the same name in different tables, and highly relevant fields retrieved from the knowledge graph are then incorporated into the total set of relevant fields.

[0020] Calculate the degree of inclusion of values ​​based on each similar field in the total set of related fields;

[0021] If the inclusion rate reaches the preset threshold, the confidence level of the relationship between fields is determined by the large language model.

[0022] Based on inclusion and reliability, an inclusion attribute map and a reliability attribute map are generated. The inclusion attribute map and the reliability attribute map are then merged into the fourth attribute map to obtain the fifth attribute map.

[0023] The database table is explored by probing for possible composite unique keys through a large model. After data verification and functional dependency removal, the data is stored in the fifth attribute graph. Then, repeated operations are performed on the fields involved in the composite key to generate and merge them into the final attribute graph.

[0024] Furthermore, each field that meets the preset conditions includes:

[0025] Determine whether the value in each field is semantically unique. For fields that do not have explicit uniqueness constraints added to the database, use a large model to determine whether their values ​​are semantically unique and give a uniqueness score. If the uniqueness score is greater than a preset threshold, then each field meets the preset conditions.

[0026] Further, step 300 includes: performing rule-based filtering through predefined database design specifications, judging the business rationality and relationship type of candidate associations based on the prompting engineering guided large language model and outputting confidence scores, and determining the associations between fields and the associations between field combinations.

[0027] Furthermore, determining the relationships between fields includes: performing rule-based filtering through predefined database design specifications, filtering attributed edges from the final attribute graph to obtain single-field to single-field reference relationships, and outputting all single-field to single-field reference relationships confirmed by the rules, i.e., the relationships between fields.

[0028] Furthermore, the filtering rules include:

[0029] The endpoint field of an edge should not have duplicate values;

[0030] All values ​​stored in the edge start field in the database should appear in the end field;

[0031] The reliability of the large model's judgment has reached the set threshold;

[0032] Two fields with the same name, or whose semantic similarity aggregation reaches a threshold;

[0033] It is not a reference from a primary key to another key within the same table;

[0034] Both the start and end fields must contain at least one value; they are not columns that are all null.

[0035] The maximum length of the values ​​stored in the start and end fields in the database does not exceed a preset threshold;

[0036] The data types of the start and end fields are not included in the list of types that should be excluded;

[0037] The names of the start and end fields did not appear in the list of field names that should be excluded.

[0038] Furthermore, determining the relationships between field combinations includes: judging the validity of the composite key local graph structure, and filtering out field combinations that are associated with a composite key to a composite unique key in another table, i.e., the relationships between field combinations.

[0039] Furthermore, to determine the legality of a local graph structure of a composite bond, the following requirements must be met:

[0040] A combination of relationships between two or more fields;

[0041] All related starting tables are the same, all related ending tables are the same, and the ending fields can be combined to form a unique key; removing any pair of fields in the combination will cause the ending field combination to fail to become a unique key.

[0042] Further, step 400 includes:

[0043] After obtaining the relationships between fields and the relationships between field combinations, all tables and fields are found based on the final attribute graph. The relationships between fields are determined based on the relationships between fields and the relationships between field combinations, and an interactive entity relationship graph is generated through graphical rendering.

[0044] Compared with the prior art, the present invention has the following technical effects:

[0045] (1) The database table relationship exploration method proposed in this invention for LLM-Text2SQL achieves significant technological breakthroughs and practical application value by integrating multi-source information processing and intelligent reasoning mechanisms. This method systematically integrates database metadata and data content features, uses a large language model for semantic enhancement and structured completion, and combines feature similarity-based algorithms and rule engines to construct an end-to-end automated processing flow from heterogeneous databases with missing constraints to a normalized ER graph.

[0046] (2) This invention automatically realizes the exploration of database table relationships in real-world applications, greatly improving the accuracy and completeness of relationship identification. Through a multi-dimensional verification mechanism (rule engine and large language model reasoning collaboration), it effectively avoids omissions and errors in manual annotation, and is especially suitable for industrial-grade large-scale database scenarios. The automated exploration of table relationships provides a solid foundation for Text2SQL based on large language models, ensuring that there are accurate association constraints available when performing table associations in natural language data access.

[0047] (3) This invention significantly reduces labor costs and time consumption, shortens the traditional manual ER diagram construction process that takes weeks or even months to automated processing, and improves maintenance efficiency; enhances the traceability of data models and the ability to analyze business logic, and presents implicit relationships through structured visualization, providing a reliable basis for database migration, architecture reconstruction and business process analysis; the method has cross-domain universality, and integrates business domain knowledge through a large language model, which can adapt to the complex semantic environment of databases in different industries, and ultimately achieves a comprehensive improvement in data consistency assurance, governance cost optimization and data model manageability. Attached Figure Description

[0048] To more clearly illustrate the technical solutions and advantages in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0049] Figure 1 This is a schematic diagram of the process of the present invention;

[0050] Figure 2 This is a diagram illustrating the extracted table metadata and extracted field metadata.

[0051] Figure 3 A diagram illustrating the field descriptions generated for the information;

[0052] Figure 4 This is a schematic diagram illustrating an example of the relationships between fields detected after processing steps 210 to 250.

[0053] Figure 5 A schematic diagram illustrating an example of the composite unique key detected in step 260;

[0054] Figure 6 This is a diagram illustrating the relationships between fields obtained through rule filtering in step 300.

[0055] Figure 7The ER diagram is the final result drawn in step 400 based on the detected correlations. Detailed Implementation

[0056] To further illustrate the technical means and effects adopted by the present invention to achieve its intended purpose, the specific implementation methods, structures, features, and effects of the technical solutions proposed according to the present invention are described in detail below with reference to the accompanying drawings and preferred embodiments. Specific features, structures, or characteristics in one or more embodiments may be combined in any suitable form. Unless otherwise defined, all technical and scientific terms used in this invention have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains.

[0057] This invention proposes a database table relationship exploration method for LLM-Text2SQL. This method employs a multi-stage collaborative processing strategy, integrating database metadata (such as schema definitions and constraint information) with data content features (such as field value distribution and statistical attributes). It then uses a large language model to semantically enhance and structurally complete the original information. Based on this, it combines a feature similarity-based algorithm to screen potential association candidate sets and establishes a dual verification mechanism involving rule engine and large language model reasoning to perform multi-dimensional verification and determination of association relationships. Finally, it generates an entity relationship graph that supports interactive editing and iterative optimization. This method achieves end-to-end automated processing from heterogeneous databases with missing constraints and compromised data integrity to a normalized ER graph, laying a solid foundation for significantly improving the success rate of natural language SQL generation based on a large language model. This processing specifically includes the following steps:

[0058] Step 100: Obtain metadata by scanning the database system directory and analyze the characteristics of the data content;

[0059] Step 200: Retrieve similar fields for each field using vector similarity, retrieve related fields using business documents, calculate the similarity between fields and the degree of inclusion of values; and parse the semantics of the fields using a large language model, and expand the range of candidate associations by combining the business domain knowledge base.

[0060] Step 300: Establish a dual verification mechanism that integrates rule engine and large language model reasoning to verify and determine candidate association relationships;

[0061] Step 400: Convert the validated relationships into a standard ER diagram model and export the ER diagram in an interchangeable format.

[0062] The following is a detailed explanation of each of the above steps:

[0063] Step 100: Obtain metadata by scanning the database system directory, analyze the characteristics of the data content, generate machine-parseable feature vectors and semantic descriptions of the large language model, and achieve cross-dimensional information complementarity.

[0064] By scanning the relational database system directory, the metadata (structured information) of the relational database is obtained. The metadata includes field names, data types, primary key / foreign key constraints, index definitions and comment text. At the same time, the data content characteristics are analyzed, the uniqueness ratio of values ​​of each field, the null value rate and statistical characteristics (such as mean and variance) are calculated, and machine descriptions, large language model descriptions, structured feature vectors, etc. are generated.

[0065] This step aims to comprehensively and automatically collect metadata and data content characteristics from relational databases, providing a comprehensive and multi-layered foundation for subsequent relationship mining. By scanning the system directory of the database and collecting data from each table, it processes and outputs complete metadata for all tables and their fields, as well as machine descriptions, large language model descriptions, and structured feature vectors for each field, ultimately storing the data as structured graph data.

[0066] As an example, step 100 specifically includes the following sub-steps:

[0067] Step 110: Initialize the property graph.

[0068] Prepare an empty property. Figure 7 Tuples:

[0069] ;

[0070] In the above formula, These represent the sets of attribute graph nodes, edges, attribute names, and values, respectively. It is The full function on; mean They are the edges The starting point and the ending point; the relationship The attributes possessed by a node (or edge) are determined; mean It is a node One of the properties; while partial functions Determine the value of an attribute of a node (or edge); represent Attributes Values Due to the initial property graph It's an empty image. They are all empty sets.

[0071] Step 120: Retrieve information about all tables from the system directory of the relational database, and merge all tables and their information as table attributes into the initial attribute graph.

[0072] Retrieve all tables from the system directory of the relational database. Information, This refers to the total number of tables in the database. Table information includes the name. Notes All fields in the table Total number of rows of data Primary Key All unique constraint information .

[0073] By treating all tables and their information as table attributes and merging them into the initial attribute graph, we obtain the first attribute graph. :

[0074] ;

[0075] in, The operation represents the attribute Figure 7 Take the union of each component of the tuple; This represents a graph that integrates all tables and their information as attributes; This represents the first attribute graph.

[0076] The map that was merged middle, It is a set consisting of all tables. It is less than or equal to All positive integers;

[0077] Each table has a set of attributes:

[0078] ;

[0079] Table Relationships The values ​​of all the table attributes involved constitute a set. , The table is obtained from the database. property The specific value, mapping .

[0080] here It is a label. Represents the table Have tag attributes This means It is ,thus exist No further definition is needed. The extracted... It should be A subset of the primary key, because the primary key may be a composite key formed by combining multiple keys, or it may not exist at all; Includes tables All unique constraints are defined within the column, and each unique constraint is in turn bound to a combination of one or more columns. .

[0081] Step 130: Extract fields and their basic information from each table in the relational database, and analyze all values ​​under the field to generate field content features, obtaining an attribute graph containing basic field information and content features; connect fields with their respective tables using edges to obtain an attribute graph describing the field-table connection relationship; merge the attribute graph containing basic field information and content features and the attribute graph describing the field-table connection relationship into the first attribute graph to obtain the second attribute graph.

[0082] From each table in the relational database Extract all fields from each table. , Indicates the first The total number of fields in the table.

[0083] For each field Extract its basic information, including field names. Comments, data types The data includes whether it is a primary key, whether it has a unique constraint, etc.; and analyzes all values ​​under this field to generate its content characteristics, including the count of non-null values. Counting of duplicate values The most common occurrence of the front Value The largest value minimum value The maximum and minimum value lengths, the frequency of each value, and the minhash digest are all considered. This information is then organized into a graph structure.

[0084] ;

[0085] in, An attribute graph representing basic field information and content characteristics;

[0086] It is a collection of all fields, and the collection of their possible attributes; the collection of attributes that each table possesses:

[0087] ;

[0088] Field Relationships a collection of field values Field mapping .

[0089] Then, edges are used to connect the fields to their respective tables:

[0090] ;

[0091] in, An attribute diagram representing the field-table join relationship;

[0092] All the newly added edges in this step form a set. , Representing the The table and its first Edges between fields; each edge should have a label indicating its meaning, thus the newly added attributes form a set. ,here This represents a membership relationship; each newly added edge has... Attributes, therefore relation sets Mapping the start and end points of edges .

[0093] Finally, the attribute graph containing basic field information and content characteristics, and the attribute graph describing the field-table join relationship, are merged into the first attribute graph, i.e.:

[0094] ;

[0095] In the above formula, This represents the second attribute graph.

[0096] Because the number of records in a table can be very large, the computational cost of precisely calculating certain content feature attributes of a field would be excessive, such as the count of duplicate values ​​or the frequency of each value. In such cases, a minhash digest can be used to estimate the count of duplicate values, or the extraction of certain content features can be ignored. Furthermore, some field data types do not support the extraction of certain features; for example, geographic location does not support calculating maximum or minimum values, so the corresponding attributes of these fields must be left blank. Additionally, some fields store data that is unlikely to participate in table relationships, such as fields storing images or large files. These fields can be excluded using rules, eliminating the need for data feature extraction. Therefore, Degenerates into a partial function, no longer requiring Cartesian product All pairs in the set are defined.

[0097] Step 140: For each field in each table, generate a description for each field to obtain a field description attribute graph, and merge it into the second attribute graph.

[0098] For each field in each table Generate natural language descriptions from its basic information and content features. To enhance the information under the field node, namely:

[0099] ;

[0100] in, It is an index of different description generators, including machine-generated, large language model-generated, etc. Representative field The table to which it belongs; It represents all other fields that belong to the same table as field c; and Map Restricted to only nodes Node set The relevant parts, Indicates operators.

[0101] After generating a description for each field, the generated information is merged into the second attribute graph:

[0102]

[0103] In the above formula, Represents the third attribute graph; This represents the attribute graph describing the field.

[0104] Merged segment description attribute graph All newly added descriptive attributes Form a set ,relation All values ​​form a set. The corresponding mapping .

[0105] Step 150: Generate a semantic vector for each description of each field to form a semantic description attribute graph, add it to the third attribute graph to obtain the fourth attribute graph.

[0106] Using an embedding model, a semantic vector is generated for each description of each field. :

[0107] ;

[0108] Then add it to the overall attribute chart:

[0109] ;

[0110] In the above formula, This represents the fourth attribute graph; Represents a semantic description attribute graph.

[0111] Among them, semantic description attribute graph Added attributes New Relationship All values ​​constitute a set , mapping .

[0112] After the above steps, each table and each field in the database is stored as a node in an attribute graph, and their basic information, feature information, and semantic vectors are stored in the various attributes of the node. By selecting different description generators and different embedding models, field information can be customized, comprehensively utilizing the multi-dimensional features of the fields.

[0113] Step 200: Retrieve similar fields for each field using vector similarity, retrieve related fields using business documents, calculate the similarity between fields and the degree of inclusion of values; and parse the semantics of the fields using a large language model, and expand the range of candidate associations by combining the business domain knowledge base.

[0114] This step aims to comprehensively explore the correlations between fields, generate basic information about these correlations, and lay the foundation for subsequent verification and determination of these relationships. It retrieves similar fields for each field using vector similarity and related fields from business documents, calculates the similarity and value inclusion between fields, and uses a large language model to determine the confidence level of their correlation. Finally, it stores the relevant information between two fields in the attributes of the edges connecting them, thus solidifying it into the attribute graph.

[0115] As an example, this step 200 specifically includes the following sub-steps:

[0116] Step 210: Determine whether the value in each field is semantically unique. For fields that do not have explicit uniqueness constraints added to the database, use the large model to determine whether their values ​​are semantically unique and give a uniqueness score.

[0117] This step aims to determine whether the value in each field should be semantically unique. It plays a central role in relational database table relationships, fundamentally ensuring entity integrity constraints by enforcing the uniqueness of field or field combination values, and providing semantically clear anchors for logical relationships between tables.

[0118] For fields in the database that do not have explicit uniqueness constraints, a large model is used to determine whether their values ​​are semantically unique and to provide a uniqueness score:

[0119] ;

[0120] In the above formula, This represents the algorithmic process of scoring and judging uniqueness using a large model, and its output is... It is a real number between zero and one.

[0121] Step 220: Use vector similarity to retrieve similar fields for each field that meets the preset conditions, and retrieve highly relevant fields from relevant business documents; combine similar fields, fields with the same name in different tables, and highly relevant fields retrieved from the knowledge graph into the total set of relevant fields.

[0122] By using vector similarity, for each uniquely constrained or fields Retrieve the most similar There are several fields. Since each field may have more than one semantic vector, representing its different features, it is necessary to retrieve each vector separately and then combine the retrieval results, i.e.:

[0123] ;

[0124] in, Indicates targeting a field c The set of most similar fields retrieved; For field semantic vectors Most similar Each field.

[0125] Since fields with the same name belonging to different tables often form related relationships, in addition to semantic similarity retrieval, all fields with the same name should be considered... It is also included in the total set of related fields.

[0126] Furthermore, knowledge graphs are extracted from relevant business documents, entities are aligned to tables and fields in the database, and highly relevant field sets are obtained through multi-hop retrieval within the knowledge graph. Incorporate them into the overall set of related fields:

[0127] ;

[0128] In the above formula, This represents the total set of relevant fields.

[0129] Step 230: Calculate the degree of inclusion of the value based on each similar field in the total set of related fields.

[0130] By using the list of values ​​stored under the field attribute or the minhash digest, for For each similar field in the database, calculate the similarity of the fields in the database. In the table where it is located What percentage of the values ​​stored in the middle also appear in the field? middle:

[0131] ;

[0132] in, Indicates the degree of inclusion; The relation (i.e., the table) to which field c is located; To restrict projection operations onto field c, generate a set of deduplicated values ​​for field c.

[0133] For smaller tables, containment The inclusion degree is calculated by exhaustively enumerating all data; for larger tables, the inclusion degree is approximated by using the minhash digest of two fields.

[0134] Step 240: If the coverage reaches the preset threshold, the confidence level of the association between fields is determined by the large language model.

[0135] For all similar field pairs obtained in the previous step If the inclusion degree Reaching the threshold Then, a large model is used to determine the confidence level that there is a correlation between the two fields.

[0136] First, extract all relevant information from the two fields. Then, prompt words are generated to query the large model to obtain the reliability:

[0137] ;

[0138] In the above formula, Indicates reliability; This describes the algorithmic process of scoring reliability based on all relevant information from two fields using a large language model.

[0139] Step 250: Generate a coverage attribute graph and a reliability attribute graph based on coverage and reliability. Merge the coverage attribute graph and the reliability attribute graph into the fourth attribute graph to obtain the fifth attribute graph.

[0140] Construct directed edges connecting field nodes in the attribute graph, and store the inclusion degree and confidence generated in the above steps into the attributes of the edges:

[0141] ;

[0142] in, This represents the fifth attribute diagram;

[0143] Newly added reliability attribute graph Added attributes The new attribute set represents the score given by the large model for the uniqueness of values ​​in the field. All the newly added values ​​form a set.

[0144] , representing a set of relationships that have attributes

[0145] , mapping .

[0146] Another newly added containment attribute graph inside, It is the set of directed edges connecting all pairs of similar fields, with their start and end points mapped. Each newly added edge represents a field reference relationship. , representing the degree of inclusion between field values and the reliability given by the large language model There are three attributes in total, forming a set. ,relation Attribute value mapping:

[0147] .

[0148] After these steps, the attribute graph contains basic relationship information between all fields and their similar fields. This involves... , and Three threshold parameters are used to control the number of candidate associations generated during the exploration. This algorithm stores these three thresholds in the attributes of each field node, marking which similar fields for each field have been processed and which edges have had their association confidence evaluated by the large model, allowing for pausing and resuming processing midway. On the other hand, when... , and When these three thresholds are adjusted, only the incremental part of the retrieved similar fields and the incremental part of the edges whose inclusion degree reaches the threshold need to be processed to avoid redundant calculations.

[0149] Step 260: Probe possible composite unique keys in the database table through the large model, and after data verification and functional dependency removal, store them in the fifth attribute graph. Then, generate and merge the final attribute graph by performing repeated operations on the fields involved in the composite key.

[0150] This step aims to explore composite unique keys in each table. In practical applications, de facto composite unique keys often do not have explicit uniqueness constraints added for performance reasons. Therefore, it is necessary to use algorithms to find possible composite unique keys to provide a basis for determining the subsequent table relationships.

[0151] For all tables in the database, probe for possible composite unique keys using a large model:

[0152] ;

[0153] here It is a table The large language model is based on the information in the table, which consists of all the fields it possesses. and information for all its fields. And the information of the table in the business document knowledge graph Based on semantics, identify possible composite unique keys in the table and construct a set of composite unique keys. .

[0154] A unique key is given for each candidate combination in the large model. Retrieve data from a database table and calculate the uniqueness of its values. :

[0155] ;

[0156] Because the large model finds composite unique keys The dataset may contain too many fields, leading to functional dependencies within the composite data. Therefore, fields that depend on combinations of other fields need to be removed.

[0157] ;

[0158] in, It is the minimum combination of fields to remove all dependencies, and the minimum unique condition. .

[0159] The composite keys that have been de-dependent are stored as nodes in the fifth attribute graph. Their uniqueness is stored in the node's attribute, and edges connecting the composite key to its components and to its table are added.

[0160] ;

[0161] in, Represents the sixth attribute diagram;

[0162] Includes composite unique keys with known constraints in the table. and all newly added composite key nodes and their properties:

[0163] Attribute ownership relationship and the value of the attribute Attribute value mapping ;

[0164] Graph about edges Includes two types of edges These are used to connect fields to their respective composite keys, and composite keys to their respective tables.

[0165] For all the constituent fields involved in the composite key Repeat steps 220 to 250 to obtain the association information between relevant fields and generate a graph. The final attribute graph is formed by merging these elements. : ;

[0166] In the above formula, This represents the final attribute graph.

[0167] Step 300: Establish a dual verification mechanism that integrates rule engine and large language model reasoning to verify and determine candidate association relationships.

[0168] This step aims to comprehensively utilize the information obtained in the preceding steps to select the qualified parts from the candidate reference relationships obtained in the previous steps, and finally determine the reference relationships between fields and the association relationships between tables. A dual verification mechanism is established through the collaboration of the rule engine and the large language model inference. Rule-based filtering is performed through predefined database design specifications (such as foreign key naming patterns), while the large language model is guided by prompt engineering to judge the business rationality and relationship type of candidate associations, thereby achieving multi-dimensional and accurate verification.

[0169] As an example, step 300 specifically includes the following sub-steps:

[0170] Step 310: Filter the edges with attributes from the final attribute graph to obtain single-field to single-field reference relationships, that is, obtain the associations between fields.

[0171] Rule-based filtering is performed using predefined database design specifications (such as foreign key naming patterns). The final attribute graph is then filtered for data representing field reference relationships. The edge of the attribute outputs all reference relationships from one field to another, confirmed by the rule. :

[0172] ;

[0173] in, The predicates are customizable rules, which are taken in a form similar to disjunctive normal form in this algorithm:

[0174]

[0175] For the ideal foreign key constraint field association, conjunction To determine this, at least all of the following requirements must be met. :

[0176] ①side Endpoint field There should be no duplicate values, that is... Pick:

[0177] ;

[0178] ②side Starting point field All values ​​stored in the database should appear in the endpoint field. In, that is Pick:

[0179]

[0180] However, the combination of these two conditions is only a necessary condition, not a sufficient one, and more artificial rules must be added.

[0181] In this invention, the following preset conditions are supported by default:

[0182] ③ The reliability of the large model's judgment reaches a customizable threshold. By default, when two fields have the same name... When the two fields have different names ;

[0183] ④ Two fields with the same name, or whose semantic similarity aggregation reaches a threshold. aggregate functions You can choose from maximum, minimum, weighted average, etc.

[0184] ⑤ Not a reference from a primary key to another key within the same table, i.e. ;

[0185] ⑥ Both the start and end fields must contain at least one value; they are not all null columns.

[0186] ⑦ The maximum length of the values ​​stored in the start and end fields in the database does not exceed the threshold. ;

[0187] ⑧ The data types of the start and end fields are not included in the list of types that should be excluded. This list can be customized and by default includes decimal numbers with decimal parts, large file types, etc.

[0188] ⑨ Neither the start nor end field names appear in the list of field names that should be excluded. This list can be customized and includes creation time, modification time, etc. by default.

[0189] Considering the possibility of sporadic erroneous data due to lack of database maintenance, the requirements for foreign key constraint field associations ① and ② can be appropriately relaxed, and changed to threshold-based determination, i.e.

[0190] and This situation is considered as conjunction Requirements ③ to ⑨ are the same as above.

[0191] Step 320: Match and find composite key associations;

[0192] By matching combined references using rules, and then using rules to filter out associations that link composite keys to composite unique keys in another table, i.e., associations between combinations of fields. :

[0193] ;

[0194] in, The validity of a composite key local graph structure is determined by the conjunction of all the following requirements: it consists of associations composed of two or more fields. , All related starting tables are the same. All associated endpoint tables are the same. Furthermore, the endpoint fields can be combined to form a unique key. This field combination satisfies the minimum property, meaning that removing any pair of fields in the combination will cause the endpoint field combination to fail to become a unique key; yes power set One of the elements represents a combination of fields; Used to determine whether each field association pair meets the requirements, it is the same as in step 410, except that it does not require ① to be true.

[0195] Each composite bond found in this way It actually contains all the information about which field each field is associated with, including more detailed information about these associations. It can be queried at any time from the attribute graph.

[0196] Step 400: Interactive ER Diagram Generation and Optimization: The validated relationships are transformed into a standard ER diagram model, and entities, attributes and relationships are rendered using a graphical engine; users can manually modify rules, add, delete or adjust relationships or add business annotations through the interactive interface, and finally export the ER diagram in an exchangeable format (such as XML, JSON).

[0197] Obtain the association between fields Association between field combinations Then, based on the final attribute diagram Find all tables and fields, based on and Determine the relationships between fields, generate an interactive entity relationship diagram using graphical rendering, and allow users to add, delete, modify, and query the generated table relationships.

[0198] To record correct associations confirmed by the user and incorrect associations rejected by the user, add attributes to the corresponding edges in the attribute graph; for associations added by the user, add corresponding edges to the attribute graph to connect them; merge and persist the final attribute graph.

[0199] Method and effect demonstration:

[0200] This invention uses a sample MySQL database—a fictional DVD rental store business database called sakila—as an example to demonstrate the effectiveness of the invention. Figure 2 This refers to the table metadata extracted in step 120 and the field metadata extracted in step 130. In step 140, the field descriptions generated based on this information are as follows: Figure 3 As shown, the descriptions include naive descriptions generated from annotations, machine-generated field feature descriptions, and comprehensive descriptions generated by a large language model. It can be seen that due to the significant lack of field annotations in the database, naive descriptions often consist only of field names.

[0201] Figure 4 The following is an example of the detected field associations after processing steps 210 to 250. It can be seen that the association includes the inclusion degree and the reliability score given by the large model. Figure 5 This is an example of a composite unique key detected in step 260.

[0202] Figure 6 This represents the relationships between fields obtained after rule filtering in step 300. The large purple circles represent data tables, the smaller gray circles connected by gray lines represent fields belonging to the corresponding tables, and the blue arrows between fields indicate the relationships between them.

[0203] Figure 7 The ER diagram is the final result drawn in step 400 based on the detected correlations.

[0204] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A database table relationship exploration method for LLM-Text2SQL, characterized in that, Includes the following steps: Step 100: Obtain metadata by scanning the database system directory and analyze the characteristics of the data content; Step 200: Candidate Association Generation: For each field, similar fields are retrieved using vector similarity, and related fields are retrieved using business documents. The similarity between fields and the degree of inclusion of values ​​are calculated. The semantics of the fields are parsed using a large language model, and the scope of candidate associations is expanded by combining the business domain knowledge base. Step 300: Establish a dual verification mechanism that integrates rule engine and large language model reasoning to perform multi-dimensional verification and judgment of candidate association relationships; Step 400: Convert the validated relationships into a standard ER diagram model and export the ER diagram in an interchangeable format; Step 100 includes: Initialize the property graph. The initial property graph is a 7-tuple, including the property graph nodes, edges, property names, sets of values, total functions, relations, and partial functions. Retrieve information about all tables from the system directory of the relational database, treat all tables and their information as table attributes, and merge them into the initial attribute graph to obtain the first attribute graph; Extract fields and their basic information from each table in the relational database, and analyze all values ​​under each field to generate field content features, resulting in an attribute graph containing basic field information and content features; connect fields with their respective tables using edges to obtain an attribute graph describing the field-table connection relationship; merge the attribute graph containing basic field information and content features and the attribute graph describing the field-table connection relationship into the first attribute graph to obtain the second attribute graph. For each field in each table, generate a description for each field to obtain a field description attribute graph, which is then merged into the second attribute graph to obtain the third attribute graph; For each description of each field, a semantic vector is generated to form a semantic description attribute graph, which is then added to the third attribute graph to obtain the fourth attribute graph; Step 200 specifically includes: For each field that meets the preset conditions, similar fields are retrieved using vector similarity, and highly relevant fields are retrieved from related business documents. Similar fields, fields with the same name in different tables, and highly relevant fields retrieved from the knowledge graph are then incorporated into the total set of relevant fields. Calculate the degree of inclusion of values ​​based on each similar field in the total set of related fields; If the inclusion level reaches a preset threshold, the confidence level of the association between fields is determined by the large language model; Based on the inclusion degree and reliability, an inclusion degree attribute map and a reliability attribute map are generated. The inclusion degree attribute map and the reliability attribute map are then merged into the fourth attribute map to obtain the fifth attribute map. The database table is explored by probing for possible composite unique keys through a large model. After data verification and functional dependency removal, the data is stored in the fifth attribute graph. Then, repeated operations are performed on the fields involved in the composite key to generate and merge them into the final attribute graph.

2. The database table relationship exploration method for LLM-Text2SQL according to claim 1, characterized in that, Each field that meets the preset conditions includes: Determine whether the value in each field is semantically unique. For fields that do not have explicit uniqueness constraints added to the database, use a large model to determine whether their values ​​are semantically unique and give a uniqueness score. If the uniqueness score is greater than a preset threshold, then each field meets the preset conditions.

3. The database table relationship exploration method for LLM-Text2SQL according to claim 1, characterized in that, Step 300 includes: performing rule-based filtering through predefined database design specifications, judging the business rationality and relationship type of candidate associations based on the prompting engineering guided large language model and outputting confidence scores, and determining the associations between fields and the associations between field combinations.

4. The database table relationship exploration method for LLM-Text2SQL according to claim 3, characterized in that, Determining the relationships between fields involves: performing rule-based filtering using predefined database design specifications, filtering attribute edges from the final attribute graph to obtain single-field to single-field reference relationships, and outputting all single-field to single-field reference relationships confirmed by the rules, i.e., the relationships between fields.

5. A database table relationship exploration method for LLM-Text2SQL according to claim 4, characterized in that, The filtering rules include: The endpoint field of an edge should not have duplicate values; All values ​​stored in the edge start field in the database should appear in the end field; The reliability of the large model's judgment has reached the set threshold; Two fields with the same name, or whose semantic similarity aggregation reaches a threshold; It is not a reference from a primary key to another key within the same table; Both the start and end fields must contain at least one value; they are not columns that are all null. The maximum length of the values ​​stored in the start and end fields in the database does not exceed a preset threshold; The data types of the start and end fields are not included in the list of types that should be excluded; The names of the start and end fields did not appear in the list of field names that should be excluded.

6. The database table relationship exploration method for LLM-Text2SQL according to claim 3, characterized in that, Determining the relationships between field combinations includes: judging the validity of the composite key local graph structure, and filtering out field combinations that are associated with a composite key to a composite unique key in another table, i.e., the relationships between field combinations.

7. A database table relationship exploration method for LLM-Text2SQL according to claim 6, characterized in that, To determine the validity of a local graph structure for a composite key, the following requirements must be met: A combination of relationships between two or more fields; All related starting tables are the same, all related ending tables are the same, and the ending fields can be combined to form a unique key; removing any pair of fields in the combination will cause the ending field combination to fail to become a unique key.

8. A database table relationship exploration method for LLM-Text2SQL according to claim 3, characterized in that, Step 400 includes: After obtaining the relationships between fields and the relationships between field combinations, all tables and fields are found based on the final attribute graph. The relationships between fields are determined based on the relationships between fields and the relationships between field combinations, and an interactive entity relationship graph is generated through graphical rendering.

Images