Database relation intelligent discovery and ER graph construction method and self-evolution learning method

Abstract

The invention provides a database relationship intelligent discovery and ER graph construction method and a self-evolution learning method, and belongs to the technical field of ER graph construction, and the database relationship intelligent discovery and ER graph construction method comprises the steps of S1, metadata collection and preprocessing; s2, vectorizing domain sensing metadata; s3, recalling a multi-strategy candidate relationship; s4, performing LLM structured reasoning and verification; s5, carrying out data sampling inspection and cross validation; s6, hierarchical negative sample self-evolution learning; and S7, generating and visualizing an ER graph. According to the invention, a layered negative sample self-evolution learning mechanism and the structured reasoning ability of LLM are organically combined, and LoRA rapid fine tuning and a multi-stage verification process are supplemented. The defects of accuracy, generalization ability, cold start efficiency, reasoning transparency, automation integration and the like in the aspect of discovery of the primary and foreign key relationship of the relational database in the prior art are effectively overcome, and a more advanced and practical solution is provided.

Description

Technical Field

[0001] The present invention relates to the technical field of ER diagram construction, and in particular to a method for intelligently discovering database relationships and constructing ER diagrams, as well as a self-evolutionary learning method. Background Art

[0002] In modern large-scale database systems, relationships between tables are complex and numerous, and design documents often become lost or outdated due to system evolution. Traditional methods rely on database experts to manually sort out primary and foreign key relationships, which is time-consuming and error-prone. Relationship discovery becomes a significant challenge, especially in production environments lacking explicit foreign key constraints. Existing technologies (such as WO2018013318A1, US20150254255A1, CN106227892B, and ERGenius) achieve relationship discovery through machine learning, symbolic analysis, or statistical analysis, but they suffer from the following limitations: 1. Limited accuracy and generalization capabilities: For example, WO2018013318A1 relies on the statistical characteristics of data content and basic metadata patterns, lacks the ability to understand deep semantic associations, and does not introduce an adaptive negative sample learning mechanism for different data environments (such as cross-domain and cross-tenant). As a result, the accuracy and generalization capabilities are insufficient in complex naming rules, implicit relationships, or completely new database models, and the cold start time is long.

[0003] 2. Opaque reasoning and difficult integration: The reasoning process of existing methods (such as the partial implementation of the cited patents) is often "black box", making it difficult to provide clear and explainable decision-making basis. They also lack the ability to use large language models (LLMs) for structured reasoning and output structured results (such as JSON) that can be directly consumed by automated pipelines, limiting their integration into automated operations and DevOps processes.

[0004] 3. Insufficient scalability and personalization: Traditional methods may face computational bottlenecks when processing large-scale databases. Once the model is trained, it is difficult to quickly and cost-effectively adjust it for specific small-scale datasets or personalized needs. They lack the lightweight personalized fine-tuning capabilities proposed in this paper based on LoRA. Although LoRA technology has developed rapidly since its launch, the key bottleneck in realizing its full potential in engineering practice has always been how to efficiently and accurately collect appropriate positive and negative samples for specific tasks.

[0005] 4. High computational complexity: O(N²) pairwise matching of large-scale databases results in high resource consumption and makes it difficult to meet efficiency requirements.

[0006] 5. Poor adaptability to heterogeneous database environments: Enterprise-level data environments often contain multiple database types and versions. When faced with this heterogeneity, existing technologies often lead to complex deployment and poor compatibility due to differences in metadata standards, SQL dialects, data type definitions, and other factors, making it difficult to provide unified and efficient relationship discovery capabilities.

[0007] Discovering database relationships, especially in complex data environments lacking explicit foreign key constraints, is a key challenge in data management. With the explosive growth of data and the diversification of data sources, automatically and accurately identifying relationships between tables and constructing entity-relationship diagrams (ER diagrams) is crucial for data understanding, data integration, data governance, and automated operations.

[0008] In the existing technology, various methods have attempted to address this problem. For example, some studies have used natural language processing technology and embedding models for pattern extraction. In "Semantic Schema Extraction in NoSQL Databases using BERT Embeddings" (2024), Belefqih et al. proposed a structured schema extraction method for NoSQL databases (specifically JSON documents). This method uses BERT embeddings and semantic similarity analysis to extract entity-attribute-type (triplet) triples and perform schema compression. However, this method mainly focuses on the field structure induction of NoSQL databases and does not delve into the semantic relationship reasoning of primary and foreign keys (PK-FK) in relational databases, the direct generation of ER diagrams, or the use of large language models (LLMs) for structured reasoning or adaptive learning through layered negative sampling mechanisms.

[0009] On the other hand, some research has begun exploring the use of large language models (LLMs) to enhance data discovery capabilities. For example, An et al. presented a system called LEDD in "LEDD: Large Language Model-Empowered Data Discovery in Data Lakes" (arXiv: 2502.15182, 2025). The system uses LLMs to generate a hierarchical global catalog in the data lake and supports semantic search across tables.

[0010] LEDD implements hierarchical clustering and catalog generation by performing LLM summarization and semantic embedding on multi-dimensional information such as table metadata. It also analyzes the joinable / unionable relationships between newly added columns and existing columns in real time when users expand nodes. Although the LEDD system has made progress in utilizing LLM for data catalog construction and semantic retrieval, its primary focus is on the global catalog organization of the data lake and the analysis of common relationships (such as joinable / unionable). It does not specifically optimize the precise determination of primary and foreign key relationships in relational databases. It also does not publicly utilize the hierarchical negative sample self-evolution learning mechanism proposed in this invention in combination with LoRA fast fine-tuning technology to improve the adaptation speed and accuracy in specific scenarios (such as cross-tenant and cross-domain), nor does it enforce the output of structured JSON containing confidence levels and explanations to support automated pipelines. Summary of the Invention

[0011] The present invention provides a method for intelligent discovery of database relationships and construction of ER diagrams, as well as a self-evolutionary learning method. By organically combining the layered negative sample self-evolutionary learning mechanism with the structured reasoning capability of LLM, and supplemented by LoRA fast fine-tuning and multi-stage verification processes, it effectively solves the shortcomings of existing technologies in the discovery of primary and foreign key relationships in relational databases, such as accuracy, generalization capability, cold start efficiency, reasoning transparency, and automation integration, and provides a more advanced and practical solution.

[0012] To achieve the above object, the present invention adopts the following technical solutions: Database relationship intelligent discovery and ER diagram construction methods, including: S1. Metadata collection and preprocessing: Extract metadata from at least one heterogeneous database, normalize the metadata, and obtain a structured metadata description list; S2. Domain-aware metadata vectorization: Using a domain-tuned embedding model, we convert column description text in the structured metadata description list into high-dimensional semantic vectors and construct an approximate nearest neighbor index based on the semantic vectors. S3. Multi-strategy candidate relationship recall: vector similarity retrieval based on column naming rules and approximate nearest neighbor indexing, combined with heuristic filtering rules, generates a set of primary and foreign key candidate pairs. S4.LLM structured reasoning and verification: Call the LLM fine-tuned by instructions, based on the set of primary and foreign key candidate pairs, and output structured data results containing relationship judgments, confidence scores, and explanations for each primary and foreign key candidate pair, and dynamically inject positive and negative examples into the reasoning prompts; S5. Data sampling and cross-validation: Based on the structured data output by the LLM, randomly select sample values ​​from the foreign key columns and calculate their inclusion ratio in the primary key columns. If the inclusion ratio is not lower than the preset threshold, the primary-foreign key relationship is confirmed. S6. Layered negative sample self-evolution learning: maintain three types of negative sample pools: global, domain, and tenant. File new difficult negative samples generated by data sampling or manual feedback by level. Use LoRA to quickly fine-tune based on negative samples, update model parameters, and generate negative sample pool update records. S7.ER diagram generation and visualization: Convert the primary and foreign key relationships confirmed by data sampling into Mermaid, PlantUML, or an equivalent description language (i.e., a diagram description language). Combined with the difficult negative sample processing information in the negative sample pool update record generated in S6, add relationship confidence annotations and misjudgment avoidance instructions to the ER diagram, render a visual ER diagram with explanatory metadata, and output the JSON result together with the negative sample pool update record.

[0013] In this specification, the initial sampling weights of the three types of negative sample pools are set to global layer: domain layer: tenant layer = 3:2:1, and the sampling weights are automatically adjusted according to the accuracy of the model at each layer.

[0014] In this specification, during LLM reasoning, difficult negative samples from the negative sample pool are injected into Prompt in an In-Context Learning manner to immediately correct potential misjudgments.

[0015] In this specification, data sampling is performed by sampling 1,000 rows of non-null values ​​in the foreign key column and calculating whether at least 95% of the values ​​exist in the primary key column to determine whether the data sampling has passed.

[0016] In this specification, an approximate nearest neighbor index construction tool is used to construct an approximate nearest neighbor index based on a semantic vector.

[0017] In this specification, difficult negative samples that are successfully corrected or still misclassified through in-context learning are automatically archived into the stratified negative sample pool for subsequent LoRA fast fine-tuning.

[0018] In this specification, in the ER diagram generation step, the confidence score and explanation reason in the structured data result are used as interactive annotation information and rendered on the corresponding relationship line of the visual ER diagram.

[0019] In this specification, the negative sample pool data is used to perform incremental fine-tuning on the embedding model and LLM, and the incremental fine-tuning is achieved by updating a set of low-rank adaptation parameters, where the number of low-rank adaptation parameters is less than 1% of the total number of LLM parameters.

[0020] In this specification, the performance of the model on negative samples of each layer is periodically evaluated, and the sampling weights are dynamically adjusted according to the evaluation results.

[0021] A self-evolutionary learning method for optimizing a machine learning model, comprising: a. Construct a stratified negative sample pool, wherein the stratified negative sample pool includes at least two or more layers of the global layer, the domain layer, and the tenant layer; b. Extract negative samples from each level pool according to preset or dynamically adjusted sampling weights to form a training batch; c. performing efficient incremental fine-tuning of parameters of the machine learning model using the negative samples; d. File the newly identified difficult negative samples during the model application process into the corresponding level of the stratified negative sample pool according to their scope of application, thereby forming a closed learning loop.

[0022] In this specification, normalization processing includes unifying upper and lower case, removing meaningless prefixes and suffixes, and generating structured description text.

[0023] In summary, the present invention has at least the following beneficial effects: 1. Excellent generalization and extremely low cold-start cost (thanks to "hierarchical negative sample management" and "LoRA fast fine-tuning"): By constructing three-tiered negative sample pools at the global, domain, and tenant levels, this system captures a wide range of difficult samples, from general knowledge to highly personalized scenarios. A dynamic negative sample sampling strategy adjusts sampling weights based on task requirements (such as new tenant onboarding and domain optimization) and model performance, accelerating personalized adaptation. Combined with LoRA's low-rank adaptation technology, model fine-tuning for new tenants or domains can be completed in just 10 minutes, requiring only a small parameter update (on the order of a few MB). Furthermore, this invention introduces the negative sample In-Context Learning (ICL) mechanism as an instantaneous patch for zero training cost during inference. This complements LoRA in a complementary "short-term memory + long-term memory" system: ICL provides immediate misjudgment correction, while LoRA enables persistent model parameter optimization. The two work together, rather than duplicating each other, to ensure the system possesses both agile responsiveness and deep learning capabilities.

[0024] 2. Highly automated integration and explainability (derived from "LLM structured reasoning"): Leveraging a fine-tuned LLM for relational reasoning, the system enforces JSON output (e.g., the Function Calling mechanism). This ensures that results (including relational judgments, confidence levels, and explanations) are presented as standardized structured data, enabling seamless integration into automated data governance pipelines, DevOps processes, or CMDB systems. The LLM "rationale" field provides transparent decision-making rationale, facilitating manual review and issue tracing, addressing the pain points of traditional "black box" decision-making.

[0025] 3. Leading relationship discovery accuracy (thanks to "multi-stage verification" and synergy with the overall solution): Through the four-stage collaboration of domain-aware vector retrieval, heuristic rule filtering, LLM deep semantic reasoning, and data spot check verification, combining semantic understanding, rule constraints, and data facts, this project effectively filters out noise and reduces false positives and missed detections. This project has a significant impact on discovering primary and foreign key relationships in relational databases.

[0026] 4. Efficient personalized model iteration (enabled by "LoRA Fast Fine-tuning" and "Stratified Negative Sample Self-Evolution Learning"): The Stratified Negative Sample Self-Evolution mechanism, combined with LoRA technology, allows the system to incorporate new difficult negative examples after they are identified and assigned to the appropriate pool during the verification phase. This new learning can be incorporated by simply updating a small number of adapter parameters, eliminating the need for time-consuming full model retraining. This agility enables the system to quickly respond to changes in specific databases or business scenarios, maintaining consistently high performance. This makes it particularly suitable for enterprise applications that require frequent updates or are highly customized. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0028] Figure 1 It is a flowchart of the method for intelligent discovery of database relationships and construction of ER diagrams involved in the present invention.

[0029] Figure 2 Schematic diagram of the multi-strategy hierarchical screening process involved in the present invention.

[0030] Figure 3 Schematic diagram of the core concept of the three-layer negative sample pool involved in the present invention.

[0031] Figure 4 It is a schematic diagram of the ER effect diagram involved in the present invention.

[0032] Figure 5 It is a comparative diagram of the exhaustive matching between the present invention and the traditional one involved in the present invention. DETAILED DESCRIPTION

[0033] In the following, only certain exemplary embodiments are briefly described. As those skilled in the art would realize, the described embodiments may be modified in various different ways without departing from the spirit or scope of the embodiments of the present invention.

[0034] Accordingly, the drawings and description are to be regarded as illustrative in nature and not restrictive.

[0035] The disclosure below provides many different embodiments or examples for implementing different structures of the embodiments of the present invention. In order to simplify the disclosure of the embodiments of the present invention, the components and configurations of specific examples are described below. Of course, these are merely examples and are not intended to limit the embodiments of the present invention. In addition, the embodiments of the present invention may repeat reference numerals and / or reference letters in different examples. Such repetition is for the purpose of simplicity and clarity and does not in itself indicate the relationship between the various embodiments and / or configurations discussed.

[0036] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0037] like Figure 1 As shown, this embodiment provides a method for intelligently discovering database relationships and constructing ER diagrams, including: S1. Metadata collection and preprocessing: Extract metadata from at least one heterogeneous database, normalize the metadata, and obtain a structured metadata description list; S2. Domain-aware metadata vectorization: Using a domain-tuned embedding model, we convert column description text in the structured metadata description list into high-dimensional semantic vectors and construct an approximate nearest neighbor index based on the semantic vectors. S3. Multi-strategy candidate relationship recall: vector similarity retrieval based on column naming rules and approximate nearest neighbor indexing, combined with heuristic filtering rules, generates a set of primary and foreign key candidate pairs. S4.LLM structured reasoning and verification: Call the LLM fine-tuned by instructions, based on the set of primary and foreign key candidate pairs, and output structured data results containing relationship judgments, confidence scores, and explanations for each primary and foreign key candidate pair, and dynamically inject positive and negative examples into the reasoning prompts; S5. Data sampling and cross-validation: Based on the structured data output by the LLM, randomly select sample values ​​from the foreign key columns and calculate their inclusion ratio in the primary key columns. If the inclusion ratio is not lower than the preset threshold, the primary-foreign key relationship is confirmed. S6. Layered negative sample self-evolution learning: maintain three types of negative sample pools: global, domain, and tenant. File new difficult negative samples generated by data sampling or manual feedback by level. Use LoRA to quickly fine-tune based on negative samples, update model parameters, and generate negative sample pool update records. S7. ER diagram generation and visualization: Convert the primary and foreign key relationships confirmed by data sampling into Mermaid, PlantUML, or equivalent description languages, combine them with the difficult negative sample processing information in the negative sample pool update record generated in S6, add relationship confidence annotations and misjudgment avoidance instructions to the ER diagram, and render a visual ER diagram with explanatory metadata (such as Figure 4), and output the JSON result together with the negative sample pool update record.

[0038] In some embodiments, the initial sampling weights of the three types of negative sample pools are set to global layer: domain layer: tenant layer = 3:2:1, and the sampling weights are automatically adjusted according to the accuracy of the model at each layer.

[0039] In some embodiments, during LLM reasoning, difficult negative samples from the negative sample pool are injected into Prompt in an In-Context Learning manner to immediately correct potential misjudgments.

[0040] In some embodiments, the data sampling pass is determined by sampling 1000 rows of non-null values ​​for the foreign key columns and calculating that at least 95% of the values ​​exist in the primary key columns.

[0041] In some embodiments, an approximate nearest neighbor (ANN) index building tool is used to build an approximate nearest neighbor index based on the semantic vector.

[0042] In some embodiments, the approximate nearest neighbor (ANN) index building tool may be FAISS or Milvus.

[0043] In some embodiments, the heuristic filtering rules include a data type compatibility check and a cardinality check of the number of rows in the foreign key table ≥ the cardinality check of the primary key table.

[0044] In some embodiments, the fields of the structured data result include source_table, source_column, target_table, target_column, is_foreign_key, confidence_score, and rationale.

[0045] In some embodiments, the embedding model and LLM are incrementally fine-tuned using negative sample pool data, and the incremental fine-tuning is achieved by updating a set of low-rank adaptation parameters, where the number of low-rank adaptation parameters is less than 1% of the total number of LLM parameters, and the fine-tuning time is 8-11 minutes.

[0046] In some embodiments, the inclusion rate calculation process is: randomly sample 1,000 rows of non-null values ​​from T2.C2, and calculate their inclusion rate in T1.C1. The inclusion rate is obtained by counting how many values ​​of the non-null values ​​sampled from T2.C2 also exist in T1.C1, and then dividing this number by the total number of non-null values ​​sampled from T2.C2, where T1.C1 is a primary key candidate pair and T2.C2 is a foreign key candidate pair.

[0047] In some embodiments, the normalization process includes unifying upper and lower case, removing meaningless prefixes and suffixes, and generating structured description text.

[0048] In some embodiments, a self-evolutionary learning method for optimizing a machine learning model includes: a. Construct a stratified negative sample pool, wherein the stratified negative sample pool includes at least two or more layers of the global layer, the domain layer, and the tenant layer; b. Extract negative samples from each level pool according to preset or dynamically adjusted sampling weights to form a training batch; c. performing efficient incremental fine-tuning of parameters of the machine learning model using the negative samples; d. File the newly identified difficult negative samples during the model application process into the corresponding level of the stratified negative sample pool according to their scope of application, thereby forming a closed learning loop.

[0049] The self-evolutionary learning method used to optimize machine learning models can be used to optimize models in database relationship intelligent discovery and ER diagram construction methods.

[0050] The technical concept of the present invention is as follows: The present invention aims to solve the following technical problems: 1. In databases without explicit foreign key constraints, automatically identify primary and foreign key relationships and generate accurate ER diagrams.

[0051] 2. Improve the generalization and rapid adaptation capabilities of the relationship discovery system across different business areas or tenants.

[0052] 3. Provides an explainable reasoning process and supports seamless integration into automated pipelines.

[0053] 4. Optimize computing resource consumption in large-scale database scenarios.

[0054] The core process of the present invention includes the following stages: 1. Metadata collection and preprocessing: Extract table and column metadata from the database and perform normalization.

[0055] 2. Domain-aware metadata vectorization: Use a fine-tuned embedding model to generate semantic vectors and build an efficient retrieval index.

[0056] 3. Multi-strategy candidate relationship recall: Combine naming patterns, vector similarity retrieval, and heuristic rules to screen candidate primary and foreign key pairs.

[0057] 4.LLM structured reasoning and verification: Use the fine-tuned LLM to perform relational reasoning and output structured JSON results (structured data results).

[0058] 5. Data sampling and cross-validation: Verify the correctness of candidate relationships through data sampling.

[0059] 6. Layered Negative Sample Self-Evolution Learning: Through global, domain, and tenant-level negative sample pool management, combined with LoRA fine-tuning, cross-domain adaptation and continuous optimization are achieved.

[0060] 7.ER diagram generation and visualization: Integrate confirmed relationships, output ER diagram description in standard format and render it.

[0061] In some embodiments, metadata collection and preprocessing: Function: This phase lays a solid foundation for the entire relationship discovery process. It not only extracts raw table and column metadata (such as names, data types, constraints, and comments) from the database, but also, crucially, transforms this raw information into a clean, consistent, and context-rich dataset through a series of normalization processes (such as capitalization, removal of meaningless prefixes and suffixes, and generation of structured descriptive text). This standardized output is a prerequisite for the accurate and efficient execution of subsequent advanced analysis phases (such as semantic vectorization and LLM reasoning). It ensures the quality and usability of the input information and avoids errors in subsequent processing caused by inconsistent or incomplete metadata.

[0062] step: 1. Obtain table name, column name, data type, nullability, primary key information, unique constraint, column comment, etc. through standard interfaces (such as JDBC DatabaseMetaData and information_schema).

[0063] 2. (Optional) Data Statistical Feature Extraction: Use HyperLogLog to estimate the deduplication count of the column and calculate the proportion of non-null values.

[0064] 3. Metadata normalization: Unify the capitalization style (for example, convert all to lowercase), remove common, non-semantic table / column name prefixes or suffixes (such as tbl_, col_, _id, _pk, etc.) to refine the core business vocabulary and facilitate subsequent semantic analysis and similarity comparison. Generate structured descriptive text for each column, for example, the unified format is: "surface:<table_name> | Column:<column_name> Type:<column_type> ( <length>)|Optional:<is_nullable> Primary Key:<is_primary_key> |Unique:<is_unique> Description:<column_comment> This format is designed to provide rich and consistent contextual information for subsequent embedding models, ensuring that all key metadata properties are included.

[0065] Output: List of structured metadata descriptions.

[0066] In some embodiments, domain-aware metadata vectorization: Functionality: The core goal of this phase is to elevate the metadata preprocessed in the previous phase from simple text and structural descriptions to a semantic level for understanding. By employing an embedding model (fine-tuned with domain knowledge), the structured descriptive text of each column is converted into a high-dimensional semantic vector. This allows the system to go beyond literal similarity and capture deeper semantic connections between columns (for example, user_acc_id and customer_identifier may both refer to user identifiers). These semantic vectors are key to subsequently discovering hidden or non-obvious relationships. Furthermore, by constructing an efficient approximate nearest neighbor (ANN) index, it enables rapid retrieval of semantically similar columns in large-scale databases, providing strong support for recalling candidate relationships.

[0067] step: 1. Embedding Model Selection and Fine-tuning: Choose an appropriate pre-trained text embedding model (for example, models optimized for Chinese, such as BAAI / bge-large-zh, or the general-purpose Sentence Transformer series). Crucially, the selected embedding model must be fine-tuned using a domain dataset with rich table structures, column definitions, and known primary and foreign key (PK-FK) pairs, such as the SchemaPile-Perm dataset of large relational database schemas. This fine-tuning aims to enhance the model's semantic understanding of database metadata (particularly information such as column names, types, and comments), allowing the generated vectors to more accurately reflect the potential correlations between columns, thereby optimizing the representation of semantic relevance.

[0068] 2. Generate a high-dimensional vector (e.g., 768 dimensions) for the descriptive text of each column.

[0069] 3. Use FAISS or Milvus to build ANN index to support fast Top-K similarity query.

[0070] Hardware resources: Training: Fine-tuning the embedding model requires 1-2 NVIDIA A100 80GB GPUs and takes about 2-4 hours to train.

[0071] Inference: Vector generation and retrieval take approximately 0.5 seconds per 1000 columns (single NVIDIA V100 32GB GPU, batch mode).

[0072] In some embodiments, multi-strategy candidate relationship recall: Function: This stage acts as an intelligent "pre-screening" role. Its main purpose is to efficiently identify a batch of high-quality and highly likely candidate primary-foreign key pairs from a vast number of potential column combinations (avoiding O(N²) exhaustive matching). It comprehensively applies multiple strategies: Traditional "naming pattern matching" can quickly capture simple relationships that conform to common specifications; "vector similarity retrieval" generated in the previous stage can discover potential relationships with different names but related semantics; and "heuristic rules" (such as data type compatibility checks, primary key uniqueness requirements, and the general database design common sense that the number of rows in the foreign key table is usually more than that in the primary key table) further filter out obviously unreasonable combinations. Through this multi-pronged approach, this stage aims to maximize the recall rate of real relationships while significantly reducing the number of candidates that need to be submitted to the subsequent computationally intensive LLM inference stage, thereby improving the efficiency and accuracy of the overall process. A comparison chart of the present invention and traditional exhaustive matching is as Figure 5 shown.

[0073] As Figure 2 shown, steps: 1. Primary key end screening: Heuristic rules: The column name contains "id", "key", or is explicitly defined as the primary key, or the proportion of unique values > 0.99.

[0074] 2. Foreign key end retrieval: For each potential primary key column, retrieve the top-K (K = 20 - 50) similar columns in the ANN index.

[0075] 3. Filtering rules: Data type compatibility (such as INT and BIGINT); (optional) Cardinality check: The number of rows in the foreign key table is usually greater than that in the primary key table; Exclude self-references (unless in a specific mode).

[0076] Complexity optimization analysis: The complexity of traditional exhaustive pairwise matching is O(N²), where N is the total number of columns. In a large database (such as 10,000 columns), approximately C(N,2) = 49,995,000 comparisons need to be made. This method significantly reduces the complexity through the following optimizations: Identify M potential primary key columns (usually M << N, about 5 - 10% of N); Perform ANN retrieval for each primary key column, and on average, return K ≈ 30 candidate foreign key columns. The total number of candidate combinations: M × K ≈ M × 30, which is much less than N²; FAISS retrieval complexity: O(M log N); Practical results: In a 10,000-column database, the number of comparisons is reduced from 50M to about 15,000 candidate pairs, and FAISS retrieval is completed in about 2-3 minutes on a single GPU.

[0077] Output: A list of candidate primary-foreign key pairs.

[0078] In some embodiments, LLM structured reasoning and verification: Function: This is the core "intelligent decision-making" step in the entire relationship discovery process. Based on the candidate relationship pairs screened in the previous stage, this stage introduces a Large Language Model (LLM), fine-tuned with specialized instructions, for in-depth analysis and judgment. Leveraging its powerful natural language understanding, code comprehension, and pattern reasoning capabilities learned from extensive data, the LLM comprehensively evaluates the metadata and contextual information of each candidate PK-FK pair to determine the likelihood of it forming a true foreign key relationship. Crucially, the LLM goes beyond a simple "yes / no" decision and instead outputs a structured JSON object, following a pre-set prompt, containing a relationship confidence score (confidence_score) and detailed rationale for the decision (rationale). This design significantly enhances the transparency and explainability of the relationship discovery process, facilitating manual review and debugging. The standardized output also enables seamless integration into downstream applications such as automated data governance and CMDB updates.

[0079] step: 1. Prompt design: You are a database design expert. Given the following candidate primary and foreign key pairs: Primary Key:<PK_table> .<PK_column> (<PK_metadata> ); Foreign Key:<FK_table> .<FK_column> (<FK_metadata> ); Please judge<FK_table> .<FK_column> Whether to quote<PK_table> .<PK_column> As a foreign key, and output in JSON format: { "source_table":"<FK_table> ", "source_column": "<FK_column> ", "target_table": "<PK_table> ", "target_column": "<PK_column> ", "is_foreign_key":boolean, "confidence_score":float, "rationale":" <explanation>"}.

[0080] The confidence_score field in the above JSON structure indicates the model's confidence level in its judgment of the relationship. This score will be used in subsequent data sampling and cross-validation stages to prioritize candidate relationship pairs that require further verification.

[0081] 2. Instruction LLM selection and fine-tuning: Use code or text to generate large language models (such as Starcoder) or GPT / DEEPSEEK / Qwen series models, and perform instruction fine-tuning based on the SchemaPile dataset.

[0082] Fine-tuning goal: Improve the accuracy of PK-FK judgment and the stability of JSON output.

[0083] 3. Reasoning: The inference time for each candidate pair is approximately 0.1-0.2 seconds (single NVIDIA A100 80GB GPU).

[0084] The output is forced to JSON format, supporting automated pipeline integration.

[0085] LLM Inference Accuracy: During the reasoning process, LLM can generate "factual errors," especially when faced with complex business logic or non-standard naming, leading to misidentification or omission of relationships. To address this challenge and ensure the accuracy of the results, this phase focuses on the following mitigation measures: Combined with multi-stage verification: Although LLM is the core inference engine, its judgment is combined with the initial vector similarity screening and subsequent data sampling verification to form a robust three-stage verification process.

[0086] Optimize prompt engineering: Well-designed prompts provide LLM with sufficient context to guide it to make more accurate judgments.

[0087] Improve model accuracy and confidence interpretability: Through the core "layered negative sample self-evolutionary learning" mechanism and ICL of this invention, we can improve model accuracy and confidence interpretability, and gradually improve domain adaptability.

[0088] Manual review of low-confidence results: The system can be configured with a policy to introduce a manual review process for candidate relationships with low confidence given by LLM, especially in scenarios with extremely high accuracy requirements, as a key supplement to quality assurance.

[0089] These measures work together to maximize the LLM's powerful reasoning capabilities while effectively controlling and reducing its potential risk of "factual errors".

[0090] Hardware resources: LoRA fine-tuning: For details on fine-tuning time and resource usage, see Resource Calculation; Inference: About 2-3 minutes per 1,000 candidate pairs (single NVIDIA A100 80GB GPU).

[0091] In some embodiments, data sampling and cross-validation: Function: This stage is a key step in "fact checking" the results of LLM reasoning, which aims to further confirm or disprove the authenticity of the candidate relationship by directly checking the actual data in the database. Although LLM performs well in understanding metadata and semantics, there may still be "factual errors" or wrong judgments in certain complex scenarios. Therefore, for candidate relationships that LLM gives a high confidence or uncertainty, this stage will randomly extract a part of the data samples from the potential foreign key columns and check the existence of these values ​​in the corresponding primary key columns (ie, the inclusion rate) and the uniqueness of the primary key column itself. This cross-validation based on actual data provides strong empirical evidence for the hypotheses generated by the model, effectively improves the accuracy and reliability of the database relationships finally identified, and is a necessary supplement and important guarantee for pure model-driven methods. The core concepts of the three-layer negative sample pool are as follows: Figure 3 shown.

[0092] step: 1. Based on the confidence score (confidence_score) generated by the previous LLM inference stage, candidate pairs with high confidence scores (for example, greater than 0.85; this threshold can be adjusted to balance precision and recall based on actual needs) or confidence scores in the uncertainty range are prioritized for spot checks.

[0093] 2. For candidate pairs (T1.C1, T2.C2): Randomly sample 1000 non-null values ​​from T2.C2 and calculate their inclusion ratio in T1.C1. This inclusion ratio is calculated by counting how many of the non-null values ​​sampled from T2.C2 are also present in T1.C1 (the candidate primary key column), and then dividing this number by the total number of non-null values ​​sampled from T2.C2. A high inclusion ratio (for example, typically set to 0.95 or higher, although this threshold is adjustable) indicates that many values ​​from T2.C2 are also found in T1.C1, supporting the possibility of a true foreign key relationship between them.

[0094] Threshold: An inclusion rate ≥ 0.95 is considered to have passed verification.

[0095] 3. (Optional) Verify the uniqueness of T1.C1.

[0096] Purpose: Ensure that the candidate primary key column (T1.C1) truly possesses the essential properties of a primary key. The core requirement for a primary key is that its value must be unique within the table, uniquely identifying each record. If T1.C1 contains duplicate values, it cannot serve as a valid primary key, and the logic for T2.C2 to reference it as a foreign key will also fail. This step directly verifies the candidate primary key's eligibility, enhancing the reliability of relationship judgment.

[0097] Hardware resources: The sampling time for checking every 1000 rows is about 0.1 seconds (single-core CPU, SQLite environment).

[0098] In some embodiments, layered negative sample self-evolutionary learning enables continuous optimization and cross-scenario adaptation: 6.1 Overview Functionality: This phase is the core mechanism for achieving the model's high adaptability, continuous learning, and cross-scenario generalization capabilities. Its primary purpose is to establish a refined, hierarchical (global, domain, tenant) negative sample knowledge base, combined with a dynamic, targeted sampling strategy and lightweight LoRA (Low-Rank Adaptation) fast fine-tuning technology, to empower the system with the following key capabilities: 1. Rapidly adapt to new environments and personalized needs: When the system encounters a new database instance, specific business domain, or tenant, it can extract "counterexamples" (known, easily confused, non-relational pairs) from the corresponding layer's negative sample pool and fine-tune the pre-trained model using LoRA in a very short time (e.g., 10 minutes). This avoids the expensive cost of training from scratch or large-scale full-scale fine-tuning, significantly improving cold start efficiency and the speed of customized adaptation to specific scenarios.

[0099] 2. Continuous Optimization and Self-Evolution: As the system accumulates experience in real-world applications (for example, through misjudgments discovered through data spot checks in Phase 3.5, or false positives / negatives from manual feedback), these new "hard negative examples" are intelligently assigned to the corresponding negative example pool. The system can then periodically or on-demand trigger LoRA fine-tuning to "absorb" this new knowledge into the model, forming a data-driven, closed-loop self-evolutionary learning loop. This allows the model's accuracy and robustness to continuously improve over time and with the accumulation of data.

[0100] 3. Improving Generalization and Reducing False Positives: Through layered management, the system can learn everything from highly universal general rules (global level, such as avoiding cross-correlations between columns ending in _temp) to specific industry terminology (domain level, such as in the financial sector, where account_id and customer_id are not always foreign keys) and even unique naming conventions for specific tenants (tenant level, such as within a tenant, where order_ref refers specifically to a specific type of order). This multi-layered knowledge accumulation enables the model to more accurately identify true and false relationships when faced with complex and volatile real-world data, effectively reducing false positives caused by naming similarities, data coincidences, and other factors.

[0101] Necessity and innovation: In the field of machine learning, negative sample learning usually relies on a single global negative sample pool, which makes it difficult to address the following challenges: Contextual heterogeneity: Naming conventions and "pseudo-relationships" (such as user_id vs. creator_id, which are not foreign keys) vary greatly across different business domains (such as finance, e-commerce, and healthcare), making it difficult for a single pool to capture domain specificity.

[0102] Tenant personalized needs: Multi-tenant SaaS platforms or internal enterprise databases have unique naming conventions, and a single pool cannot reflect fine-grained features.

[0103] Cold start and generalization dilemma: A single pool in a new database mode requires a large amount of data and time to adapt, resulting in poor cold start performance.

[0104] The layered negative sample self-evolution learning of the present invention builds a self-evolution closed loop through three-level pools: global, domain, and tenant, combined with dynamic sampling and LoRA fine-tuning, giving the system general knowledge and rapid adaptation capabilities like a database expert.

[0105] Here are a few examples: Scenario Database Overview Typical "false foreign key" misjudgment Which layer of negative samples is most useful? Why 1. "Multi-store" example of SaaS e-commerce platform Same schema, but each online store is a tenant; table names are fixed, column names are standardized (shop_id, creator_id, order_id) Although creator_id and user_id have overlapping values, they are primary-foreign key relationships; models often consider them as PK-FK. Tenant layer The "employee account" of each store is `creator_id`, but it is only consistent in the tenant table; add these pairs to the tenant negative sample pool to avoid misjudgment when switching to other stores 2. Multi-system integration of financial groups The core accounting, risk control, and CRM systems have been logically integrated; column names are varied (loan_no, ln_no, credit_id) credit_id refers to the customer's credit rating in CRM, and refers to the "credit limit order number" in the risk control database; the name also contains id, not a foreign key Domain layer (finance) This type of "industry-specific naming convention" does not exist in other industries; by making it a negative example in the financial sector, other banking subsidiaries within the group can also directly benefit 3. Game companies acquire small studios Merge Unity log library into the big data warehouse; many columns end with GUID (string) player_guid is often mistakenly identified as pointing to the main table user_guid; however, it is actually an anonymous event log and cannot be traced back to the user. Global Layer "All names with `guid` are not necessarily related to each other" is a general experience, which can be solved globally at once 4. Government departments share data warehouses or data lakes Multiple independent business units share a single lake; up to 200k columns The data lake maps the id_number (personal information identifier) ​​of different departments to the same field name; the model tends to think that they are all foreign keys. Domain layer (public affairs) + tenant layer (department) Citizen accounts can indeed be cross-database, but each department also has "demo fake data" mixed in; the domain layer teaches the model "first check the data quality and then recognize the foreign key", and the tenant layer marks the demonstration table as a fake sample 1. Cross-tenant but same schema (Scenario 1) Problem: Fixed field names = insufficient naming information, causing the model to "force matching" based solely on string similarity.

[0106] The tenant negative sample directly tells the model: In this store, creator_id is a local foreign key only in two or three tables, and other similar columns should not be touched.

[0107] Benefit: When launching a new store, you only need to feed a few hundred tenant-level misjudgment samples to fine-tune LoRA, which immediately reduces false positives.

[0108] 2. Differences across multiple industries (Scenarios 2 & 3) The naming standards of finance and games are very different.

[0109] Domain negative samples serve as an "industry-wide exclusion list": Finance: credit_id, loan_no, and acct_no do not reference each other; When migrating to other customers in the same industry, there is almost no change.

[0110] 3. Large-Scale Data Lake (Scenario 4) Running the ER algorithm on the entire database can be left until the weekend, but it may require processing more than 200,000 columns at a time.

[0111] The biggest benefit of stratification here is training cost: The global layer is fixed, which has captured common misjudgment patterns; The department tenant layer only incrementally trains its own samples instead of re-running the hard-negative combination of 200,000 columns.

[0112] 6.2 Acquisition of Initial Negative Samples and Cold Start In order to effectively start the layered negative sample learning mechanism, the accumulation of initial negative samples is crucial. This method intelligently obtains high-quality seed negative samples from the previous stage: 1. Rule-Based Negative Samples: In "Multi-Strategy Candidate Relationship Recall," candidate relationship pairs explicitly excluded by heuristic rules (such as data type incompatibility, cardinality check failure, and meaningless self-references) serve as initial negative samples. Due to their clear non-relational characteristics, these samples are typically included in the global negative sample pool to build the model's basic discriminative capabilities. For example, if columns T1.C1 (INT) and T2.C2 (STRING) are filtered due to type mismatch, then (T1.C1, T2.C2) constitutes an initial global negative sample.

[0113] 2. Negative samples based on high-confidence rejection by LLM: In "LLM structured reasoning and verification," if LLM determines a candidate pair is not a foreign key (is_foreign_key: false) and assigns a high confidence score (for example, confidence_score > 0.9), these candidate pairs rejected by LLM's deep semantic analysis will also be selected as initial negative samples. Their classification level (global, domain) will be determined based on the rationale field output by LLM to determine the universality of the rejection logic.

[0114] 3. Strong negative examples based on failed data spot checks: In "data spot checks and cross-validation," candidate pairs that may appear misleading at the metadata level but whose data spot checks (e.g., inclusion ratios well below a preset threshold) clearly indicate an unrelated relationship are considered strong initial negative examples. These examples, rigorously falsified by the data, are extremely valuable for model learning to identify seemingly plausible but untrue patterns.

[0115] The initial negative samples obtained in the above way provide a solid foundation for subsequent dynamic sampling, LoRA fine-tuning and ICL enhancement, effectively solving the cold start problem and accelerating the model's adaptation process to a specific database environment.

[0116] 6.3 Dynamic Management and Optimization of Negative Sample Pool To ensure the continued effectiveness of the stratified negative sample pool, avoid resource consumption caused by unlimited expansion, and maintain the timeliness and high quality of samples, this method adopts one or more of the following dynamic management and optimization strategies: 1. Time-based decay mechanism: A timestamp is introduced for negative samples, and their probability or importance of being sampled is dynamically adjusted based on their "age". Older negative samples (for example, those older than a preset time threshold, such as 12 months) that have not been used for effective ICL for a long time will have their weight significantly reduced or even removed to reflect the potential evolution of business logic and data patterns.

[0117] 2. Pool Capacity Management and Representative Sampling: Reasonable capacity limits are set for negative sample pools at all levels. When new, high-quality negative samples (e.g., newly discovered through data spot checks or typical false positives from manual feedback) are added, and the pool is nearing saturation, older, less valuable samples are replaced based on factors such as the sample's "difficulty" (confidence in the model's false positives), "novelty" (difference from existing negative sample patterns), and "age" to ensure the overall quality and representativeness of the pool.

[0118] 3. Negative sample redundancy analysis: By analyzing the similarity of negative samples in metadata features or embedding space, highly redundant negative sample entries are identified and merged or removed to improve the refinement of the negative sample pool and training efficiency.

[0119] Through these dynamic management mechanisms, this method ensures that the stratified negative sample pool can not only continuously absorb new learning experiences, but also maintain its vitality and high relevance to the current model optimization objectives through "metabolism", thereby supporting the long-term self-evolution and performance improvement of the system.

[0120] 6.4 Implementation steps 1. Layered construction of negative sample pool: Global pool: stores industry-irrelevant difficult negative samples (such as non-PK-FK pairs with similar names).

[0121] Domain pool: Targets misjudgment samples specific to finance, retail, and other fields.

[0122] Tenant pool: local false positive samples for a specific database instance.

[0123] Example of negative sample data structure: { "negative_pair": { "source_table": "string", "source_column": "string", "target_table": "string", "target_column": "string" }, "layer":"global|domain|tenant", "domain": "string|null", "tenant_id": "string|null", "reason": "string", "timestamp":"ISO8601 string" }.

[0124] 2. Dynamic sampling and weight adjustment: Initial sampling weight: global: domain: tenant = 3:2:1.

[0125] Dynamic adjustment based on the sliding window precision P: If the precision of layer X is less than the threshold T, then w←w+Δw. For example, if the tenant precision is less than 0.9, increase the tenant pool weight.

[0126] 3. Difficult Negative Sample Mining: Samples with ambiguous recognition confidence (0.4-0.6) and verification errors are added to the corresponding pool.

[0127] 4. LoRA fine-tuning: Incrementally fine-tune the embedding model and LLM using the negative sample pool data.

[0128] Hardware resource configuration: GPU requirement: Single NVIDIA A100 80GB GPU; Memory usage: 15-20GB (including model loading, gradient calculation, and optimizer state); Fine-tuning time: 8-11 minutes (depending on the number of negative samples); Adapter size: 5-10MB (about 8MB when r=32).

[0129] 5. Cross-warehouse negative sample sharing judgment method: Goal: Determine to what extent negative examples (especially global and domain-level ones) learned from one data repository (source) are applicable to another data repository (target).

[0130] step: a. Calculate naming similarity: Use Jaccard similarity to compare naming conventions, such as table and column names, between the source and target warehouses. Jaccard similarity measures the degree of overlap between two sets of names (one from the source warehouse and the other from the target warehouse). It is calculated by first finding the number of table and column names shared by the two warehouses, then finding the total number of unique table and column names in the two warehouses (i.e., the union). Finally, divide the number of shared names by the total number of unique names. The result (Jaccard index) is a value between 0 and 1, with values ​​closer to 1 indicating more similar naming conventions between the two warehouses and values ​​closer to 0 indicating greater differences.

[0131] b. Determine the applicable ratio: Based on the Jaccard similarity index calculated in the previous step, determine the "applicable ratio" of a negative sample. This ratio guides the proportion of negative samples that should be recommended or migrated from the global and domain negative sample pools of the source repository to the target repository. For example, the Jaccard index can be divided by a preset sensitivity threshold (such as 0.1), and the result can be limited to a maximum of 1. If the calculated Jaccard index is 0.05, then the applicable ratio may be set to 0.5, which means that approximately 50% of the global / domain-level negative samples in the source repository can be considered for application to the target repository. If the Jaccard index reaches or exceeds the set threshold (such as 0.1), the applicable ratio can be set to 1, indicating that the negative samples in the source repository are highly correlated with the target repository and can be shared in large quantities.

[0132] Application: R_applicable determines the proportion of negative samples recommended or migrated from the global and domain negative sample pools of the source repository to the target repository. A high R_applicable value supports wider sharing of negative samples, helping to quickly apply learned experience to new repositories and reduce misjudgments.

[0133] In addition to being used for fast fine-tuning of LoRA, the three-layer negative sample pool can also be directly injected into LLM as in-context examples or retrieval context during inference to instantly eliminate misjudgments (see the negative sample ICL enhancement in the next section for details).

[0134] 6.5 Negative Sample In-Context Learning (ICL) Enhancement Purpose: The core purpose of the "Negative Sample In-Context Learning (ICL) Enhancement" mechanism proposed in this section is to provide an agile, low-cost, and highly interpretable method for instantly improving the accuracy of large language models (LLMs) on relation judgment tasks during inference, especially when dealing with difficult or ambiguous candidate relation pairs. As a complement and enhancement to LoRA fine-tuning (a "long-term memory" curation method for model parameter updates), it has the following key benefits: 1. Instant Performance Improvement and Error Correction: Unlike LoRA, which requires retraining or fine-tuning model parameters, ICL dynamically injects relevant positive and negative examples (particularly "difficult" negative examples retrieved from the stratified negative pool) into the LLM's input prompts, directly guiding the LLM to make more accurate judgments in the current inference step. This enables "plug-and-play" performance improvements, eliminating the need to wait for model update cycles and enabling rapid response to newly discovered misclassification patterns.

[0135] 2. Enhanced reasoning transparency and explainability: By presenting the LLM with specific counterexamples (negative samples) similar to the case being judged, ICL not only helps the model avoid making the same mistakes but also encourages the model to more clearly explain in its output "rationale" why a candidate pair is not a foreign key relationship. This makes the model's decision-making process more transparent, easier to understand, and trust, especially when diagnosing and correcting errors.

[0136] 3. Agility with zero training cost: ICL does not involve any training or updating of model parameters. Therefore, its computational cost is primarily focused on sample retrieval and a slightly increased prompt length, making it virtually "zero training cost." This makes it a highly flexible and economical approach, particularly suitable for scenarios requiring rapid iteration, validating new knowledge, or resource constraints. It gives the system a "short-term memory" capability, enabling it to instantly leverage the latest negative sample insights, which can then be filtered and used for more sustained LoRA fine-tuning.

[0137] Example Prompt structure: / / Positive example (from existing knowledge or previous verification) [Positive Example START]; Candidate pairs: Primary key: TableA.ID (metadata: INT, PK, NOTNULL, comment: unique identifier of TableA); Foreign key: TableB.A_ID (metadata: INT, NULL, comment: references TableA); Context information: TableB is usually linked to TableA to get transaction data.

[0138] analyze: -TableB.A_ID and TableA.ID have compatible data types.

[0139] -The column names A_ID and ID imply that there is a relationship.

[0140] -Contextual information supports this link.

[0141] decision making: {"source_table":"TableB", "source_column": "A_ID", "target_table": "TableA", "target_column": "ID", "is_foreign_key": true, "confidence_score": 0.95, "rationale": "Based on naming conventions, data type compatibility, and contextual information, TableB.A_ID is likely to reference TableA.ID as a foreign key."}; [Positive Example END].

[0142] / / Negative example (from tenant-level negative sample pool) [Negative Example START]; Candidate pairs: Primary key: shop.employee.creator_id (metadata: INT, PK, NOT NULL, comment: the unique ID of the employee who created this record in this shop); Foreign key: another_shop_orders.creator_id (metadata: INT, NULL, annotation: ID of the user who created the order in another shop instance) Context information: These tables belong to different, isolated shop instances (tenants). creator_id is a local identifier within the scope of each shop.

[0143] analyze: - Although the column names I creator_id I the same and the data types match, they are running in different tenant contexts.

[0144] - There is no explicit cross-tenant relationship defined for creator_id.

[0145] - There may be overlap in values, but this does not represent a true foreign key relationship across tenants.

[0146] decision making: {"source_table":"another_shop_orders", "source_column":"creator_id", "target_table":"shop.employee", "target_column":"creator_id", "is_foreign_key": false, "confidence_score": 0.80, / / The model is confident that this is not a foreign key "rationale": "Although the creator_id columns have the same name, they belong to different tenant schemas (shop.employee vs another_shop_orders) and are not designed to reference each other. This is a common "false positive" pattern in multi-tenant systems."}; [Negative Example END].

[0147] / / Current candidate relationship to be evaluated; [Current Candidate START] Candidate pair: Primary Key:<PK_table> .<PK_column> (<PK_metadata> ); Foreign Key:<FK_table> .<FK_column> (<FK_metadata> ); Context information: <Context information related to the current candidate pair> Analysis: / / LLM conducts thought chain analysis and decision making here: { / / JSON output of LLM}; [Current Candidate END], Synergistic strategy of ICL and LoRA fine-tuning: The three-layer negative sample pool can follow the existing hierarchical weight logic, "first search (for ICL), then fine-tune (LoRA), and then solidify (update model parameters)", forming a closed loop.

[0148] Stage 0 (Baseline): Relying only on the LoRA fine-tuned model to establish a performance baseline.

[0149] Stage 1 (ICL Augmentation): Based on the fine-tuned model, the ICL layer is introduced. In particular, for candidate relations with low confidence in the model output or known difficult patterns, relevant negative samples are retrieved and injected into prompts for immediate remediation and explanation enhancement.

[0150] Stage 2 (Feedback Loop to LoRA): Regularly analyze ICL performance and identify representative "new" difficult negative examples that were successfully corrected by ICL (or examples where the model still performed poorly despite ICL assistance). These examples are added to the corresponding negative sample pool for the next round of LoRA fine-tuning. This allows the model to gradually internalize the "short-term experience" from ICL.

[0151] Engineering practice considerations: In practical projects, to ensure the effectiveness of ICL (In-Context Learning), a variety of strategies and considerations are employed. In particular, the strategy of dynamically selecting ICL examples from a stratified negative sample pool (global, domain, and tenant) is particularly critical when processing a large number of samples.

[0152] 1. Prioritize difficult examples: We prioritize "difficult" negative examples that the model has historically misjudged or that have highly similar features to the candidate relationships being evaluated as ICL examples. This strengthens the model's ability to discern complex scenarios.

[0153] 2. Controlling context length: Strictly control the number of injected ICL examples (e.g., 5-8 positive and negative examples in total) to achieve optimal recognition within the context window of the LLM. If more information is needed, more advanced techniques such as retrieval-augmented generation (RAG) or soft-prompt will be considered.

[0154] 3. Ensure sample diversity: In a single prompt, we try to avoid overly simple or repetitive table and column name patterns in ICL examples. This helps reduce the model's memory bias towards superficial location information and encourages it to learn more generalized and robust patterns.

[0155] 4. Mixed positive and negative samples: Interleave or strategically arrange positive and negative samples in Prompt instead of simply stacking them separately. This practice helps the model better learn and distinguish decision boundaries.

[0156] 5. Establish an automatic feedback and persistence mechanism: The system automatically records the "new" difficult negative examples that ICL successfully identified or failed in each round of reasoning, along with their context. These records are available for manual review, and those that pass the review are added to the corresponding negative sample pool for subsequent ICL retrieval or longer-term LoRA fine-tuning.

[0157] 6. Grayscale verification and traffic allocation strategy: Allocate online inference traffic to the "fine-tuning only" and "fine-tuning + ICL" strategies in a certain ratio (for example, 80 / 20).

[0158] 7. Cost-Efficiency Balancing Strategy: When GPU resources are limited or when rapid response to new patterns is required, ICL is prioritized, as it is relatively computationally inexpensive and does not require retraining. During periods of relatively abundant computing resources (such as nighttime batch processing), LoRA fine-tuning is performed to incorporate the generalizable negative sample knowledge validated by ICL into the model parameters, thereby improving the performance of the base model.

[0159] In some embodiments, ER diagram generation and visualization: Function: This stage presents the results of the entire relationship discovery process. Its core purpose is to systematically integrate the valid primary and foreign key relationships confirmed in all previous stages (especially LLM reasoning and data sampling) and convert them into user-friendly, easy-to-understand, and easy-to-use entity-relationship (ER) diagrams. This includes not only clearly expressing the tables, key columns, and the reference relationships between them in a standardized text description language (such as Mermaid code), but also using appropriate tools to render these text descriptions into intuitive graphical ER diagrams. The ultimate goal is to provide database administrators, developers, data analysts, and other stakeholders with an accurate and visual overview of the database structure, thereby greatly promoting the understanding of complex data models, assisting in system design and maintenance, completing missing documentation, and supporting more efficient data governance and decision-making.

[0160] step: 1. Based on the LLM confidence and sampling results, confirm the primary and foreign key relationships; 2. Output Mermaid format; 3. Use Mermaid Live Editor or PlantUML to render the visual ER diagram.

[0161] Example 1: Database relationship discovery in a core system of a large commercial bank 1.1 Application Background A large commercial bank's database system, after 20 years of evolution, now includes over 3,000 tables and 45,000 columns, encompassing multiple subsystems, including account management, loan management, risk control, and customer relationship management. Due to performance requirements, explicit foreign key constraints were not permitted, and the original design documentation was significantly outdated. The bank urgently needed a complete data relationship diagram to support data governance and system modernization.

[0162] 1.2 Implementation Process Phase 1: Metadata Collection and Preprocessing The system extracts metadata from the Oracle19c database. Here is an example of a typical table structure: --Customer Information Form TB_CUST_INFO(CUST_NO VARCHAR2(20),--Customer number (actual primary key, but no PK constraint) CUST_NAME VARCHAR2(100),--Customer name CERT_TYPE VARCHAR2(2),--certificate type CERT_NO VARCHAR2(30),--certificate number REG_DATE DATE--Registration date).

[0163] --Account information table TB_ACCT_INFO(ACCT_NO VARCHAR2(30),--Account number (primary key) CUST_ID VARCHAR2(20),--Customer ID (implicit foreign key) ACCT_TYPE VARCHAR2(10),--Account type OPEN_DATE DATE,--Account opening date BALANCE NUMBER(15,2)--balance).

[0164] --Loan Information Form TB_LOAN_MSTR(LOAN_NO VARCHAR2(30),--Loan number (primary key) BORROWER_ID VARCHAR2(20),--Borrower ID (implicit foreign key) LOAN_AMT NUMBER(15,2),--Loan amount LOAN_TERM NUMBER(3),--Loan term RATE NUMBER(5,4)--interest rate).

[0165] --Transaction flow table TB_TRANS_FLOW(TRANS_ID VARCHAR2(40),--Transaction ID (primary key) ACCT_NO VARCHAR2(30),--Account number (implicit foreign key) TRANS_AMT NUMBER(15,2),--transaction amount TRANS_TIME TIMESTAMP--transaction time).

[0166] Example of structured description generated after preprocessing: "Table: TB_CUST_INFO|Column: CUST_NO|Type: VARCHAR2(20)|Nullable: NO|Primary Key: NO|Unique: YES|Description: Customer Number"; "Table: TB_ACCT_INFO | Column: CUST_ID | Type: VARCHAR2(20) | Nullable: NO | Primary Key: NO | Unique: NO | Description: Customer ID".

[0167] Phases 2-3: Vectorization and Candidate Recall Generate vectors using the BAAI / bge-large-zh model fine-tuned in the financial field; Identified 312 potential primary key columns (about 0.7%); Vector retrieval generates 9,360 candidate relation pairs.

[0168] Stage 4: LLM Reasoning Example output of LLM inference: {"source_table":"TB_ACCT_INFO", "source_column": "CUST_ID", "target_table":"TB_CUST_INFO", "target_column": "CUST_NO", "is_foreign_key": true, "confidence_score": 0.92, "rationale": "Although CUST_ID and CUST_NO have slightly different names, they both represent customer identifiers. Considering that accounts must be associated with customers in the banking system and the data types match exactly, this is most likely a foreign key relationship."}

[0169] Phase 5: Data sampling and verification Extract 1,000 non-null values ​​of TB_ACCT_INFO.CUST_ID; Inclusion rate check: 998 / 1000=99.8% (passed); Confirm the uniqueness of TB_CUST_INFO.CUST_NO: 100% (passed).

[0170] Stage 6: Stratified Negative Sample Learning Examples of negative samples specific to the financial field: {"negative_pair":{ "source_table":"TB_LOAN_MSTR", "source_column": "LOAN_NO", "target_table":"TB_ACCT_INFO", "target_column": "ACCT_NO"}, "layer": "domain", "domain": "finance", "reason": "Although the loan number and account number have similar formats, they are independent numbering systems in the banking system. Loans may be associated with multiple accounts or no accounts.","timestamp": "2024-11-15T10:30:00Z"}

[0171] 1.3 Implementation Effect 487 previously undocumented key relationships were discovered; 23 erroneous historical document records were identified; a complete basis was provided for data lineage analysis; and the bank's data middle-office construction project was supported.

[0172] The resulting mermaid is as follows: Every table has a primary key that uniquely identifies each record in that table: USERS table: user_id (PK) - user unique identifier; ORDERS table: order_id (PK) - unique identifier of the order; ORDER_ITEMS table: item_id (PK) - unique identifier of order details; PRODUCTS table: product_id (PK) - unique identifier of the product; CATEGORIES table: category_id (PK) - category unique identifier; Foreign Key (FK) relationship description: 1.ORDERS.customer_id(FK)→USERS.user_id(PK); Meaning: The customer_id field in the order table references the user_id field in the user table. Business relationship: indicates "which user placed this order"; Mermaid relationship line: USERS||--o{ORDERS: "places"; 2.ORDER_ITEMS.order_id(FK)→ORDERS.order_id(PK); Meaning: The order_id field in the order details table references the order_id in the order table; Business relationship: indicates "which order this detail belongs to"; Mermaid relationship line: ORDERS||--o{ORDER_ITEMS: "contains"; 3.ORDER_ITEMS.product_id(FK)→PRODUCTS.product_id(PK); Meaning: The product_id field in the order details table references the product_id in the product table; Business relationship: indicates "which product is purchased in the order details"; Mermaid relationship line: PRODUCTS||--o{ORDER_ITEMS: "ordered_as"; 4.PRODUCTS.category_id(FK)→CATEGORIES.category_id(PK); Meaning: The category_id field in the product table references the category_id in the category table; Business relationship: indicates "which category the product belongs to"; Mermaid relationship line: CATEGORIES||--o{PRODUCTS: "categorizes"; 5.CATEGORIES.parent_id(FK)→CATEGORIES.category_id(PK); Meaning: The parent_id field in the category table references the category_id of the same table (self-reference); Business relationship: represents the hierarchical structure of categories, such as 'Electronic Products' is the parent category of 'Mobile Phones'; Mermaid relationship line: CATEGORIES||--o{CATEGORIES: "parent_of"; Relation symbol description: || represents one end of "one" (primary key end); o{indicates the "many" end (foreign key end); ||--o{ indicates a one-to-many relationship.

[0173] This ER diagram structure is exactly what the database relationship intelligent discovery system of this application aims to automatically identify: in a database lacking explicit foreign key constraints, by analyzing metadata such as table names, column names, and data types, combined with LLM reasoning and data validation, these implicit primary and foreign key relationships are automatically discovered.

[0174] Technical principle of LoRA fast fine-tuning: LoRA (Low-Rank Adaptation) achieves efficient parameter fine-tuning by inserting low-rank matrices into the linear layers of the pre-trained model: Parameter size comparison: Traditional full fine-tuning requires updating all model parameters (e.g., a 7B model is approximately 28GB), while LoRA only updates the adapter parameters (approximately 5-10MB, accounting for <0.1%). Computational complexity: Training time complexity is reduced from O(N) to O(r²), where r is the rank of the low-rank matrix (typically r=16-64).

[0175] Hardware requirements: When using LoRA to fine-tune large models such as StarCoder, a single high-performance GPU (such as the NVIDIA A100 80GB) is usually sufficient.

[0176] Specific calculation of 10-minute fine-tuning: Since actual projects vary greatly, we use experimental tests to estimate the results on an NVIDIA A100 80GB GPU: Negative sample data loading: about 30 seconds; LoRA adapter initialization: about 10 seconds; Fine-tuning training (500-1000 negative samples, 5 epochs): about 8 minutes Model saving and verification: about 1 minute; Total: approximately 10 minutes.

[0177] The present invention has significant differences and beneficial effects: 1. Targeted and accurate: Unlike Belefqih et al., who mainly focus on NoSQL schema structure extraction, this invention specifically targets relational databases (especially in production environments lacking explicit foreign key constraints), focusing on the precise identification of primary-foreign key (PK-FK) semantic relationships and the automated construction of ER diagrams.

[0178] 2. Innovation of core technology paths: Layered Negative Sample Self-Evolution Learning: This paper introduces a three-tiered negative sample pool management mechanism: global, domain, and tenant. By dynamically sampling difficult negative samples and combining it with the lightweight and fast fine-tuning technology LoRA (Low-Rank Adaptation), it can complete personalized model adaptation to new database environments or specific tenants in a very short time (e.g., 10 minutes), significantly improving the model's generalization and cold start efficiency. This differs from LEDD, which primarily relies on LLM for summarization and general relationship analysis, and is also not related to the method of Belefqih et al.

[0179] LLM Structured Reasoning and Interpretable Output: This paper utilizes a fine-tuned LLM for relational reasoning and enforces its output as structured JSON containing relational judgments, confidence scores, and rationales. This design not only ensures the high interpretability of the results but also enables seamless integration into downstream pipelines such as automated data governance, CMDB updates, or DevOps. This is a feature not explicitly emphasized or implemented in existing techniques such as Belefqih et al. and LEDD (at least in their published literature).

[0180] 3. Multi-stage verification and accuracy assurance: This invention adopts a multi-stage process including domain-aware metadata vectorization, multi-strategy candidate relationship recall, LLM structured reasoning verification, and data sampling cross-validation to collaboratively improve the accuracy of relationship discovery, effectively filter noise, and reduce misjudgments and missed judgments.

[0181] 4. Cross-scenario adaptability and efficiency: Through layered negative sample management and LoRA fast fine-tuning, the present invention can efficiently adapt to the needs of different business fields, multi-tenants and heterogeneous database environments, ensuring that high relationship discovery performance can be maintained in a variety of scenarios.< / explanation> < / length>

Claims

1. Database relationship intelligent discovery and ER diagram construction method, characterized by: include: S1. Extract metadata from at least one heterogeneous database, normalize the metadata, and obtain a structured metadata description list; S2. Using a domain-tuned embedding model, we convert the column description text in the structured metadata description list into high-dimensional semantic vectors and construct an approximate nearest neighbor index based on the semantic vectors. S3. Perform vector similarity retrieval based on column name rules and approximate nearest neighbor indexing, combined with heuristic filtering rules, to generate a set of primary and foreign key candidate pairs. S4. Call the LLM that has been fine-tuned by the instructions, based on the set of primary and foreign key candidate pairs, and output structured data results for each primary and foreign key candidate pair; S5. Based on the structured data results, randomly select sample values ​​from the foreign key columns and calculate their inclusion ratio in the primary key columns. If the inclusion ratio is not lower than a preset threshold, the primary-foreign key relationship is confirmed. S6. Maintain three types of negative sample pools: global, domain, and tenant. Archive new difficult negative samples generated by data sampling or manual feedback by level. Use LoRA to quickly fine-tune based on negative samples, update model parameters, and generate negative sample pool update records. S7. Convert the primary and foreign key relationships confirmed by data sampling into a diagram description language. Combined with the difficult negative sample processing information in the negative sample pool update record, add relationship confidence annotations and misjudgment avoidance instructions to the ER diagram, and render it to obtain a visual ER diagram.

2. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: The initial sampling weights of the three types of negative sample pools are set to global layer: domain layer: tenant layer = 3:2:1, and the sampling weights are automatically adjusted according to the accuracy of the model at each layer.

3. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: During LLM reasoning, difficult negative samples from the negative sample pool are injected into Prompt in an in-context learning manner to immediately correct potential misjudgments.

4. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: The data sampling is determined to have passed by sampling 1,000 rows of non-null values ​​for the foreign key column and calculating that at least 95% of the values ​​exist in the primary key column.

5. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: An approximate nearest neighbor index is constructed based on the semantic vector using the approximate nearest neighbor index construction tool.

6. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: Difficult negative samples that are successfully corrected through context learning or still misclassified are automatically archived into the negative sample pool for subsequent LoRA fast fine-tuning.

7. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: In the ER diagram generation step, the confidence score and explanation reasons in the structured data results are used as interactive annotation information and rendered on the corresponding relationship lines of the visual ER diagram.

8. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 1, characterized in that: The embedding model and LLM are incrementally fine-tuned using the negative sample pool data. The incremental fine-tuning is achieved by updating a set of low-rank adaptation parameters, where the number of low-rank adaptation parameters is less than 1% of the total parameters of the LLM.

9. The method for intelligent discovery of database relationships and construction of ER diagrams according to claim 2, characterized in that: Periodically evaluate the performance of the model on negative samples in each layer, and dynamically adjust the sampling weights based on the evaluation results.

10. A self-evolutionary learning method for optimizing a machine learning model, characterized in that: include: a. Construct a stratified negative sample pool, wherein the stratified negative sample pool includes at least two or more layers of the global layer, the domain layer, and the tenant layer; b. Extract negative samples from each level pool according to preset or dynamically adjusted sampling weights to form a training batch; c. using the negative samples to perform efficient incremental fine-tuning of parameters of the machine learning model; d. File the newly identified difficult negative samples during the model application process into the corresponding level of the stratified negative sample pool according to their scope of application, thereby forming a learning closed loop.

Images