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

Abstract

The application 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. The database relationship intelligent discovery and ER graph construction method comprises the following steps: S1. metadata acquisition and preprocessing; S2. field perception metadata vectorization; S3. multi-strategy candidate relationship recall; S4. LLM structured reasoning and verification; S5. data sampling and cross-validation; S6. hierarchical negative sample self-evolution learning; and S7. ER graph generation and visualization. The hierarchical negative sample self-evolution learning mechanism is organically combined with the structured reasoning capability of the LLM, and is supplemented by LoRA rapid fine-tuning and a multi-stage verification process, so that the deficiencies of the prior art in the aspects of accuracy, generalization capability, cold start efficiency, reasoning transparency and automatic integration degree in the discovery of the primary-foreign key relationship of the relational database are effectively solved, and a more advanced and practical solution is provided.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of ER graph construction, and in particular to a database relationship intelligent discovery and ER graph construction method and a self-evolution learning method. BACKGROUND

[0002] In modern large-scale database systems, the relationships between tables are complex and numerous, and the design documents are often missing 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 prone to errors, especially in production environments that lack explicit foreign key constraints. Relationship discovery becomes a major challenge. Existing technologies (such as WO2018013318A1, US20150254255A1, CN106227892B, ERGenius, etc.) achieve relationship discovery through machine learning, symbolic analysis, or statistical analysis, but have the following limitations:

[0003] 1. Limited accuracy and generalization ability: For example, WO2018013318A1 relies on statistical features of data content and basic metadata patterns, lacks understanding of deep semantic associations, and does not introduce adaptive negative sample learning mechanisms for different data environments (such as cross-domain and cross-tenant), resulting in insufficient accuracy and generalization ability in complex naming rules, implicit relationships, or new database patterns, and long cold start time.

[0004] 2. Inference is not transparent and integration is difficult: The inference process of existing methods (such as the implementation of some of the cited patents) is often a "black box", making it difficult to provide clear and interpretable decision-making basis, and lacking the ability to use large language models (LLM) for structured reasoning and output structured results (such as JSON) that can be directly consumed by automated pipelines, limiting their integration in automated operations and DevOps processes.

[0005] 3. Lack of scalability and personalization: Traditional methods may face computational bottlenecks when dealing with large-scale databases. Once the model is trained, it is difficult to quickly and cost-effectively adjust to specific small-scale datasets or personalized needs, lacking the lightweight personalized fine-tuning ability based on LoRA proposed in the present application. Although LoRA technology has developed rapidly since its introduction, how to efficiently and accurately collect appropriate positive and negative samples for specific tasks has always been a key bottleneck in its full potential in engineering practice.

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

[0007] 5. Heterogeneous database environment adaptability is poor: Enterprise-level data environments often contain multiple different types and versions of databases. Existing technologies often face deployment complexity 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.

[0008] Database relationship discovery, especially in complex data environments lacking explicit foreign key constraints, is an important challenge in the field of data management. With the explosive growth of data size and the diversification of data sources, automatically and accurately identifying inter-table relationships and constructing entity-relationship diagrams (ER diagrams) is crucial for data understanding, data integration, data governance, and automated operations.

[0009] In existing technologies, there are various methods attempting to solve this problem. For example, some researches use natural language processing techniques and embedding models for schema extraction. Belefqih et al. in "Semantic Schema Extraction in NoSQL Databases using BERT Embeddings" (2024) proposed a structured schema extraction method for NoSQL databases (especially JSON documents), which uses BERT embeddings and semantic similarity analysis to extract entity-attribute-type (triplet) triples and perform schema compression representation. However, this method mainly focuses on the induction of field structures in NoSQL databases and does not delve into the semantic relationship reasoning of primary-foreign key (PK-FK) in relational databases, the direct generation of ER diagrams, or the use of large language models (LLM) for structured reasoning or adaptive learning through hierarchical negative sample mechanisms.

[0010] On the other hand, some researches have begun to explore the use of large language models (LLM) to enhance data discovery capabilities. For example, An et al. in "LEDD: Large Language Model-Empowered Data Discovery in Data Lakes" (arXiv:2502.15182, 2025) demonstrated a system called LEDD, which uses LLM to generate a hierarchical global catalog in data lakes and supports semantic search for tables.

[0011] LEDD realizes hierarchical clustering and catalog generation by LLM summarization and semantic embedding of multi-dimensional information such as table metadata, and analyzes the joinable / unionable relationship between new columns and existing columns in real time when the user expands the node. Although the LEDD system has made progress in data catalog construction and semantic retrieval using LLM, its main focus is on the global catalog organization of the data lake and the analysis of general relationships such as joinable / unionable, and it does not specifically optimize the accurate determination of primary-foreign key relationships in relational databases. It also does not disclose the use of a hierarchical negative sample self-evolution learning mechanism combined with LoRA fast fine-tuning technology to improve the adaptation speed and accuracy of specific scenarios such as cross-tenant and cross-domain, and to force the output of structured JSON containing confidence and explanation reasons to support automated pipelines. SUMMARY

[0012] The present application provides a database relationship intelligent discovery and ER graph construction method and a self-evolution learning method. By organically combining a hierarchical negative sample self-evolution learning mechanism with the structured reasoning capability of LLM, supplemented by LoRA fast fine-tuning and a multi-stage verification process, the present application effectively solves the deficiencies of existing technologies in the discovery of primary-foreign key relationships in relational databases, such as accuracy, generalization ability, cold start efficiency, reasoning transparency, and automation integration, and provides a more advanced and practical solution.

[0013] To achieve the above object, the present application adopts the following technical solutions:

[0014] The database relationship intelligent discovery and ER graph construction method comprises:

[0015] S1. Metadata acquisition and preprocessing; extracting metadata from at least one heterogeneous database, and normalizing the metadata to obtain a structured metadata description list;

[0016] S2. Domain-aware metadata vectorization; using an embedding model fine-tuned for the domain, converting column description texts in the structured metadata description list into high-dimensional semantic vectors, and constructing an approximate nearest neighbor index based on the semantic vectors;

[0017] S3. Multi-strategy candidate relationship recall; performing vector similarity retrieval based on column name rules through the approximate nearest neighbor index, combining heuristic filtering rules, and generating a primary-foreign key candidate pair set;

[0018] S4. LLM structured reasoning and verification; calling an LLM fine-tuned for instructions, based on the primary-foreign key candidate pair set, outputting structured data results containing relationship judgment, confidence score, and explanation reasons for each primary-foreign key candidate pair, and dynamically injecting positive and negative sample examples in the reasoning prompt;

[0019] S5. Data sampling and cross-validation; based on the structured data results of LLM output, randomly sample the foreign key column sample value and calculate its inclusion rate in the primary key column, if the inclusion rate is not lower than the preset threshold, confirm the primary-foreign key relationship;

[0020] S6. Hierarchical negative sample self-evolution learning; maintain three types of negative sample pools: global layer, domain layer, and tenant layer, archive new difficult negative samples generated by data sampling or manual feedback according to the level, use LoRA for rapid fine-tuning based on negative samples, update model parameters, and generate negative sample pool update records;

[0021] S7. ER graph generation and visualization; convert the primary-foreign key relationship confirmed by data sampling into Mermaid, PlantUML or equivalent description language (i.e. chart description language), combine the difficult negative sample processing information in the negative sample pool update records generated in S6, add relationship confidence annotations and misjudgment avoidance explanations in the ER graph, render the visualized ER graph with explanatory metadata, and output the JSON results together with the negative sample pool update records.

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

[0023] In this specification, during LLM inference, difficult negative samples from the negative sample pool are injected into the Prompt in the In-Context Learning mode for immediate correction of potential misjudgments.

[0024] In this specification, data sampling is passed by sampling 1000 non-empty values in the foreign key column and calculating that at least 95% of the values exist in the primary key column.

[0025] In this specification, an approximate nearest neighbor index is constructed based on semantic vectors using an approximate nearest neighbor index construction tool.

[0026] In this specification, difficult negative samples that are successfully corrected or still misjudged through In-Context Learning are automatically archived in the hierarchical negative sample pool for subsequent LoRA rapid fine-tuning.

[0027] In this specification, in the ER graph generation step, the confidence score and explanation reason in the structured data results are rendered on the corresponding relationship line of the visualized ER graph as interactive annotation information.

[0028] In this specification, the embedding model and the LLM are incrementally fine-tuned using negative sample pool data, which is achieved by updating a set of low-rank adaptation parameters, wherein the number of low-rank adaptation parameters is less than 1% of the total parameter quantity of the LLM.

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

[0030] A self-evolution learning method for optimizing a machine learning model, comprising:

[0031] a. Constructing a hierarchical negative sample pool, which contains at least two or more of global layer, domain layer and tenant layer;

[0032] b. Extracting negative samples from each hierarchical pool according to the preset or dynamically adjusted sampling weight to form a training batch;

[0033] c. Using the negative samples to perform parameter-efficient incremental fine-tuning of the machine learning model;

[0034] d. Archiving the newly identified difficult negative samples in the model application process to the corresponding hierarchical layer of the hierarchical negative sample pool according to their applicable scope, thereby forming a learning closed loop.

[0035] In this specification, the normalization process includes uniform case, removal of meaningless prefixes and suffixes, and generation of structured description text.

[0036] In summary, the present application has at least the following beneficial effects:

[0037] 1. Excellent generalization ability and extremely low cold start cost (benefiting from "hierarchical negative sample management" and "LoRA fast fine-tuning"): By constructing a three-layer negative sample pool of global, domain and tenant levels, various difficult samples from general knowledge to highly personalized scenarios are captured. The dynamic negative sample sampling strategy adjusts the sampling weight according to the task requirements (such as new tenant access, domain optimization) and model performance, accelerating personalized adaptation. Combined with LoRA low-rank adaptation technology, only a small number of parameters (about several MB) need to be updated, and the model fine-tuning for new tenants or new domains can be completed within 10 minutes. In addition, the present application also introduces a negative sample In-Context Learning (ICL) mechanism as an instant patch for zero training cost during inference, forming a "short-term memory + long-term memory" complementary system with LoRA: ICL provides instant misjudgment correction ability, LoRA realizes persistent model parameter optimization, and the two work together rather than repeatedly, ensuring that the system has both agile response ability and deep learning ability.

[0038] 2. High degree of automation integration and explainability (from "LLM structured reasoning"): Use fine-tuned LLM for relationship reasoning, and force output in JSON format (such as Function Calling mechanism), to ensure that the results (including relationship judgment, confidence, and explanation reasons) are presented in standardized structured data, which can be seamlessly integrated into automated data governance pipelines, DevOps processes, or CMDB systems. The "rationale" field of LLM provides transparent decision basis for manual review and problem tracing, solving the pain points of traditional "black box" decision-making.

[0039] 3. Leading relationship discovery accuracy (thanks to "multi-stage verification" and overall scheme synergy): Through the four-stage synergy of domain-aware vector retrieval, heuristic rule filtering, LLM deep semantic reasoning, and data sampling verification, combined with semantic understanding, rule constraints, and data facts, effectively filter noise and reduce false positives and false negatives. This project has significant effect on relationship database primary-foreign key relationship discovery.

[0040] 4. Efficient personalized model iteration (through "LoRA fast fine-tuning" to empower "hierarchical negative sample self-evolution learning"): The hierarchical negative sample self-evolution mechanism combined with LoRA technology can absorb new experiences by updating only a small number of adapter parameters when new difficult negative samples are identified in the verification link and assigned to the corresponding pool, without the need for time-consuming full model retraining. This agility allows the system to quickly respond to changes in specific databases or business scenarios, continuously maintaining high performance, especially suitable for frequently updated or highly customized enterprise-level applications. BRIEF DESCRIPTION OF DRAWINGS

[0041] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings needed in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0042] Figure 1 The flowchart of the database relationship intelligent discovery and ER diagram construction method involved in the present application.

[0043] Figure 2 The flowchart of the multi-strategy hierarchical screening involved in the present application.

[0044] Figure 3 The schematic diagram of the core concept of the three-layer negative sample pool involved in the present application.

[0045] Figure 4 The schematic diagram of the ER effect diagram involved in the present application.

[0046] Figure 5A comparison diagram of the present application and the traditional exhaustive matching. DETAILED DESCRIPTION

[0047] In the following, only certain exemplary embodiments are simply described. As those skilled in the art can recognize, the described embodiments can be modified in various different ways without departing from the spirit or scope of the embodiments of the present application.

[0048] Therefore, the drawings and description are considered to be essentially exemplary rather than restrictive.

[0049] The following disclosure provides many different embodiments, or examples, for implementing different structures of the embodiments of the present application. For the purpose of simplifying the disclosure of the embodiments of the present application, the components and settings of specific examples are described in the following. Of course, they are merely examples, and the purpose is not to limit the embodiments of the present application. In addition, the embodiments of the present application can repeatedly refer to numerals and / or reference letters in different examples, and such repetition is for the purpose of simplification and clarity, which itself does not indicate the relationship between the various embodiments and / or settings discussed.

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

[0051] As Figure 1 shown, the embodiments provide a database relationship intelligent discovery and ER diagram construction method, comprising:

[0052] S1. Metadata acquisition and preprocessing; extracting metadata from at least one heterogeneous database, normalizing the metadata to obtain a structured metadata description list;

[0053] S2. Domain-aware metadata vectorization; using a field-tuned embedding model, converting column description text in the structured metadata description list into a high-dimensional semantic vector, and constructing an approximate nearest neighbor index based on the semantic vector;

[0054] S3. Multi-strategy candidate relationship recall; according to column name rules, performing vector similarity retrieval through the approximate nearest neighbor index, combining heuristic filtering rules, and generating a set of primary-foreign key candidate pairs;

[0055] S4. LLM structured reasoning and verification; calling an instruction-tuned LLM, based on the set of primary-foreign key candidate pairs, outputting structured data results containing relationship judgment, confidence score and explanation reason for each primary-foreign key candidate pair, and dynamically injecting positive and negative sample examples in the reasoning prompt;

[0056] S5. Data sampling and cross-validation; based on the structured data results output by the LLM, randomly sampling foreign key column sample values and calculating their inclusion rate in the primary key column, and if the inclusion rate is not lower than a preset threshold, confirming the primary-foreign key relationship;

[0057] S6. Hierarchical negative sample self-evolution learning; maintaining global layer, domain layer, and tenant layer three types of negative sample pools, archiving new difficult negative samples generated by data sampling or manual feedback according to the level, using LoRA for rapid fine-tuning based on negative samples, updating model parameters, and generating negative sample pool update records;

[0058] S7. ER graph generation and visualization; converting the primary-foreign key relationship confirmed by data sampling into Mermaid, PlantUML, or equivalent description language, combining the difficult negative sample processing information in the negative sample pool update records generated in S6, adding relationship confidence annotations and misjudgment avoidance explanations in the ER graph, rendering a visual ER graph with explanatory metadata (as shown in Figure 4 ), and outputting the JSON result together with the negative sample pool update records.

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

[0060] In some embodiments, during LLM inference, difficult negative samples from the negative sample pool are injected into the Prompt in the In-Context Learning manner for immediate correction of potential misjudgments.

[0061] In some embodiments, data sampling is passed by sampling 1000 non-empty values from the foreign key column and calculating that at least 95% of the values exist in the primary key column.

[0062] In some embodiments, an approximate nearest neighbor (ANN) index is constructed based on semantic vectors using an ANN index construction tool.

[0063] In some embodiments, the approximate nearest neighbor (ANN) index construction tool can be FAISS or Milvus.

[0064] In some embodiments, the heuristic filtering rules include data type compatibility checks and foreign key table row count ≥ primary key table cardinality checks.

[0065] 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.

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

[0067] In some embodiments, the calculation process of the inclusion rate is as follows: 1000 rows of non-empty values of T2.C2 are randomly sampled, and the inclusion rate thereof in T1.C1 is calculated, the inclusion rate being calculated by counting how many values exist in T1.C1 from the non-empty values of T2.C2 by statistical sampling, and then dividing the number by the total number of non-empty values sampled from T2.C2, wherein T1.C1 is a primary key candidate pair, and T2.C2 is a foreign key candidate pair.

[0068] In some embodiments, the normalization process includes uniformizing the case, removing meaningless prefixes and suffixes, and generating a structured description text.

[0069] In some embodiments, a self-evolution learning method for optimizing a machine learning model comprises:

[0070] a. Constructing a hierarchical negative sample pool, the hierarchical negative sample pool comprising at least two or more of a global layer, a domain layer, and a tenant layer;

[0071] b. Extracting negative samples from each layer pool according to a preset or dynamically adjusted sampling weight to form a training batch;

[0072] c. Using the negative samples to perform parameter-efficient incremental fine-tuning of the machine learning model;

[0073] d. Archiving difficult negative samples newly identified in the model application process to the corresponding layer of the hierarchical negative sample pool according to their applicable scope, thereby forming a learning closed loop.

[0074] The self-evolution learning method for optimizing a machine learning model can be used to optimize the model in the database relationship intelligent discovery and ER graph construction method.

[0075] The technical concept of the present application is as follows:

[0076] The present application aims to solve the following technical problems:

[0077] 1. Automatically identifying primary-foreign key relationships and generating accurate ER graphs in a database without explicit foreign key constraints.

[0078] 2. Improving the generalization ability and rapid adaptation ability of the relationship discovery system in different business domains or tenants.

[0079] 3. Providing an interpretable reasoning process to support seamless integration into an automated pipeline.

[0080] 4. Optimize the consumption of computing resources in large-scale database scenarios.

[0081] The core process of the present application includes the following stages:

[0082] 1. Metadata collection and preprocessing: Extract table and column metadata from the database and perform normalization processing.

[0083] 2. Domain-aware metadata vectorization: Use fine-tuned embedding models to generate semantic vectors and build efficient retrieval indexes.

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

[0085] 4. LLM structured inference and verification: Use fine-tuned LLMs for relationship inference and output structured JSON results (structured data results).

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

[0087] 6. Hierarchical negative sample self-evolution learning: Manage global, domain, and tenant-level negative sample pools, and combine LoRA fine-tuning to achieve cross-domain adaptation and continuous optimization.

[0088] 7. ER graph generation and visualization: Integrate confirmed relationships, output ER graph descriptions in standard format, and render them.

[0089] In some embodiments, metadata collection and preprocessing:

[0090] Function: This stage aims to lay a solid foundation for the entire relationship discovery process. It not only extracts raw table and column metadata (such as names, data types, constraints, annotations, etc.) from the database, but also crucially transforms these raw information into clean, consistent, and context-rich datasets through a series of normalization processes (such as case unification, removal of meaningless prefixes and suffixes, and generation of structured description texts). This standardized output is the prerequisite for the accurate and efficient operation of subsequent advanced analysis stages (such as semantic vectorization and LLM inference), ensuring the quality and usability of input information and avoiding deviations caused by inconsistent or incomplete metadata.

[0091] Steps:

[0092] 1. Obtain table names, column names, data types, nullability, primary key information, unique constraints, column annotations, etc. through standard interfaces (such as JDBC DatabaseMetaData, information_schema).

[0093] 2. (Optional) Data statistics feature extraction: Estimate the deduplicated count of a column using HyperLogLog. Calculate the proportion of non-empty values.

[0094] 3. Metadata normalization: Unify the case style (e.g., all to lower case), remove common, non-semantic table / column prefixes or suffixes (e.g., tbl_, col_, _id, _pk, etc.) to distill the core business vocabulary for subsequent semantic analysis and similarity comparison. Generate structured descriptive text for each column, e.g., in the format:

[0095] “Table: <table_name> | Column: <column_name> | Type: <column_type> ( <length>| nullable: <is_nullable> | primary key: <is_primary_key> | unique: <is_unique> | description: <column_comment> ". This format aims to provide rich and consistent contextual information for subsequent embedding models, ensuring all key metadata attributes are included.

[0096] Output: Structured metadata description list.

[0097] In some embodiments, domain-aware metadata vectorization:

[0098] Functionality: The core goal of this stage is to elevate the pre-processed metadata from the previous stage from simple text and structure descriptions to a semantic level of understanding. By employing an (domain knowledge fine-tuned) embedding model, the structured description text of each column is converted into high-dimensional semantic vectors. This allows the system to go beyond superficial similarities and capture deeper semantic associations between columns (e.g., user_acc_id and customer_identifier might both refer to user identification). These semantic vectors are crucial for subsequent discovery of hidden or non-obvious relationships and enable fast retrieval of semantically similar columns in large-scale databases by constructing efficient Approximate Nearest Neighbor (ANN) indexes, providing strong support for candidate relationship recall.

[0099] Steps:

[0100] 1. Embedding model selection and fine-tuning: Select an appropriate pre-trained text embedding model (e.g., BAAI / bge-large-zh for Chinese optimization, or the general Sentence Transformer series models, etc.). The key is to fine-tune the selected embedding model using a domain dataset containing rich table structures, column definitions, and known primary-foreign key (PK-FK) pairs, such as the large relational database schema dataset SchemaPile-Perm. This fine-tuning aims to enhance the model's semantic understanding of database metadata (particularly the combination of column names, types, annotations, etc.), so that the generated vectors can more accurately reflect the potential associations between columns, thereby optimizing the representation of semantic relevance.

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

[0102] 3. Use FAISS or Milvus to build ANN indexes, supporting fast Top-K similarity queries.

[0103] Hardware resources:

[0104] Training: Fine-tuning the embedding model requires 1-2 NVIDIA A100 80GB GPUs, and takes about 2-4 hours.

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

[0106] In some embodiments, multi-strategy candidate relationship recall:

[0107] Function: This stage plays the role of an intelligent "preliminary screening", whose main purpose is to efficiently identify a batch of high-quality, high-probability candidate primary-foreign key pairs from the vast sea of potential column combinations (avoiding the exhaustive O(N²) matching). It comprehensively employs multiple strategies: traditional "naming pattern matching" can quickly capture simple relationships that conform to common norms; "vector similarity retrieval" based on the generated vectors in the previous stage can uncover potential relationships with different names but related semantics; and "heuristic rules" (such as data type compatibility checks, primary key uniqueness requirements, and the common database design knowledge that the number of potential foreign key table rows is usually greater than that of the primary key table) further filter out obviously unreasonable combinations. Through this multi-pronged approach, this stage aims to maximize the recall rate of true relationships while significantly reducing the number of candidates submitted to the subsequent computationally intensive LLM inference stage, thereby improving the efficiency and accuracy of the overall process. The comparison chart of the present invention and traditional exhaustive matching is shown in Figure 5 .

[0108] As shown in Figure 2 , the steps are:

[0109] 1. Primary key side screening:

[0110] Heuristic rule: column name contains "id", "key", or is explicitly defined as a primary key, or unique value ratio > 0.99.

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

[0112] 3. Filtering rules: data type compatibility (e.g. INT and BIGINT); (optional) cardinality check: the number of foreign key table rows is usually greater than that of the primary key table; exclude self-reference (unless explicitly specified).

[0113] 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 (e.g. 10,000 columns), about C(N, 2) = 49,995,000 comparisons are needed. This method significantly reduces the complexity through the following optimizations:

[0114] Identify M potential primary key columns (typically M << N, about 5-10% of N);

[0115] Perform ANN search on each primary key column, returning on average K≈30 candidate foreign key columns, total candidate combinations: M×K≈M×30, much smaller than N²;

[0116] FAISS search complexity: O(M log N);

[0117] Actual effect: In a database of 10,000 columns, reduce 50M comparisons to about 15,000 candidate pairs, FAISS search completed in about 2-3 minutes on a single GPU.

[0118] Output: List of candidate primary-foreign key pairs.

[0119] In some embodiments, LLM structured inference and verification:

[0120] Functionality: This is the core "intelligent decision-making" link in the entire relationship discovery process. Based on the candidate relationship pairs screened out in the previous stage, this stage introduces a large language model (LLM) fine-tuned by special instructions for deep analysis and judgment. LLM, with its powerful natural language understanding, code understanding, and pattern reasoning ability learned from a large amount of data, comprehensively evaluates the metadata and context information of each candidate PK-FK pair to determine its likelihood of forming a real foreign key relationship. The key is that LLM not only gives a simple "yes / no" judgment, but also outputs a structured JSON object according to the pre-set Prompt, which contains the relationship confidence score (confidence_score) and the detailed rationale for making the judgment. This design greatly enhances the transparency and explainability of the relationship discovery process, facilitating manual review and debugging, and the standardized output also enables seamless integration into downstream applications such as automated data governance, CMDB updates, etc.

[0121] Steps:

[0122] 1. Prompt design:

[0123] You are a database design expert. Given the following candidate primary-foreign key pairs:

[0124] Primary key: <PK_table>.<PK_column>(<PK_metadata>);

[0125] Foreign key: <FK_table>.<FK_column>(<FK_metadata>);

[0126] Please judge.<FK_table> .<FK_column> Whether to quote<PK_table> .<PK_column> As a foreign key, and output in JSON format:

[0127] {

[0128] "source_table":<FK_table> ",

[0129] "source_column":<FK_column> ",

[0130] "target_table":<PK_table> ",

[0131] "target_column":<PK_column> ",

[0132] "is_foreign_key": boolean,

[0133] "confidence_score": float,

[0134] "rationale": <explanation>"}.

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

[0136] 2. Instruction LLM selection and fine-tuning:

[0137] Large language models such as Starcoder or GPT / DEEPSEEK / Qwen series models are used to generate code or text, and instruction fine-tuning is performed based on the SchemaPile dataset.

[0138] Fine-tuning objectives: improve PK-FK judgment accuracy and JSON output stability.

[0139] 3. Inference:

[0140] Each candidate pair takes about 0.1-0.2 seconds to infer (single NVIDIA A100 80GB GPU).

[0141] The output is forced to be in JSON format, supporting automated pipeline integration.

[0142] LLM inference accuracy:

[0143] During inference, especially when facing complex business logic or non-standard naming, LLM may produce "factual errors", leading to incorrect identification or missed relationships. To address this challenge and ensure the accuracy of the results, the following mitigation measures are particularly focused on:

[0144] Combined multi-stage verification: Although LLM is the core inference engine, its judgment is combined with the preliminary vector similarity screening and subsequent data sampling verification to form a robust three-stage verification process.

[0145] Optimize Prompt engineering: carefully designed Prompt provides sufficient context for LLM to guide it to make more accurate judgments.

[0146] Improve model accuracy and confidence explainability: through the core "layered negative sample self-evolution learning" mechanism and ICL of the invention, to improve model accuracy and confidence explainability, and gradually improve domain adaptability.

[0147] Manual review of low-confidence results: the system can be configured to introduce human review for candidate relationships with low confidence given by LLM, especially in scenarios where accuracy requirements are extremely high, as a key supplement to quality assurance.

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

[0149] Hardware resources:

[0150] LoRA fine-tuning: Time and resource consumption details of fine-tuning are as follows:

[0151] Reasoning: about 2-3 minutes per 1000 candidate pairs (single NVIDIA A100 80GB GPU).

[0152] In some embodiments, data sampling and cross-validation:

[0153] Function: This stage is a key step for "fact-checking" the LLM reasoning results, aiming to further confirm or refute the authenticity of candidate relationships by directly examining actual data in the database. Although LLM performs well in understanding metadata and semantics, there may still be "factual errors" or incorrect judgments in specific complex scenarios. Therefore, for candidate relationships given by LLM with high confidence or uncertainty, this stage will randomly sample a portion of data samples from the potential foreign key column and check the existence of these values in the corresponding primary key column (i.e., 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 improving the accuracy and reliability of the final identified database relationships, and is a necessary supplement and important guarantee for pure model-driven methods. The core concept of the three-layer negative sample pool is as shown in Figure 3 .

[0154] Steps:

[0155] 1. According to the confidence score generated by the LLM reasoning in the previous stage, preferentially select candidate pairs with higher confidence (e.g., greater than 0.85, this threshold can be adjusted according to actual needs to balance accuracy and recall rate) or confidence in the uncertain interval for sampling.

[0156] 2. For candidate pair (T1.C1, T2.C2):

[0157] Randomly sample 1000 non-empty values of T2.C2, calculate its inclusion rate in T1.C1. The inclusion rate is calculated by counting how many values in the sampled non-empty values of T2.C2 also exist in T1.C1 (candidate primary key column), and then dividing this number by the total number of non-empty values sampled from T2.C2. A high inclusion rate (e.g., usually set to 0.95 or higher, this threshold can be adjusted) indicates that many values of T2.C2 are found in T1.C1, supporting the possibility of a real foreign key relationship between them.

[0158] Threshold: inclusion rate >= 0.95 is considered as passing the verification.

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

[0160] Purpose: To ensure that the candidate primary key column (T1.C1) truly possesses the basic properties of a primary key. The core requirement of a primary key is that its values must be unique in the table to uniquely identify each record. If there are duplicate values in T1.C1, it cannot serve as an effective primary key, and the logic of T2.C2 referencing it as a foreign key is also invalid. This step is a direct data verification of the candidate primary key's qualifications, enhancing the reliability of the relationship judgment.

[0161] Hardware resources:

[0162] Sampling every 1000 rows for about 0.1 seconds (single-core CPU, SQLite environment).

[0163] In some embodiments, hierarchical negative sample self-evolution learning - achieving continuous optimization and cross-scenario adaptation:

[0164] 6.1 Overview

[0165] Function: This stage is the core mechanism for the invention to achieve high adaptability, continuous learning ability and cross-scenario generalization ability of the model. Its main purpose is to establish a refined, hierarchical (global, domain, tenant) negative sample knowledge base, combined with dynamic, targeted sampling strategies and lightweight LoRA (Low-Rank Adaptation) rapid fine-tuning technology, to give the system the following key capabilities:

[0166] 1. Rapid adaptation to new environments and personalized needs: When the system faces new database instances, specific business domains or tenants, it can extract "counterexamples" (i.e. known, easily confused non-relationships) from the corresponding level of negative sample pool, and use LoRA to fine-tune the pre-trained model in a very short time (such as 10 minutes level). This avoids the expensive cost of starting from scratch or large-scale full-tuning, significantly improving the cold start efficiency and the speed of customized adaptation to specific scenarios.

[0167] 2. Continuous optimization and self-evolution: As the system accumulates experience in actual application (for example, errors found by stage 3.5 data sampling, or false positives / misses of manual feedback), these new "difficult negative samples" will be intelligently classified into the corresponding negative sample pool. The system can then trigger LoRA fine-tuning periodically or on demand, "absorbing" these new knowledge into the model, forming a data-driven, closed-loop self-evolution learning loop. This allows the model's accuracy and robustness to improve continuously over time and with the accumulation of data.

[0168] 3. Improve generalization and reduce false positives: Through hierarchical management, the system can learn from highly universal rules (global layer, such as avoiding all columns ending with _temp being associated) to industry-specific terminology (domain layer, such as account_id and customer_id in the finance domain are not always foreign keys) and even unique naming habits of specific tenants (tenant layer, such as order_ref in a certain tenant refers to a specific type of order). This multi-level knowledge accumulation enables the model to more accurately identify true and false relationships when faced with complex and variable real-world data, effectively reducing false positives caused by similar naming, data coincidence, etc.

[0169] Necessity and innovation:

[0170] In the field of machine learning, if negative samples are used, it usually relies on a single global negative sample pool, which is difficult to address the following challenges:

[0171] Context heterogeneity: Different business domains (such as finance, e-commerce, and medical) have different naming conventions and "pseudo-relationships" (such as user_id vs creator_id and non-foreign keys), and a single pool cannot capture domain-specific features.

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

[0173] Cold start and generalization dilemma: A single pool requires a large amount of data and time to adapt to new database schemas, with poor cold start performance.

[0174] The hierarchical negative sample self-evolution learning of the present invention uses a global, domain, and tenant three-level pool, combined with dynamic sampling and LoRA fine-tuning, to build a self-evolution closed loop, giving the system the general knowledge and rapid adaptation ability of a database expert.

[0175] Here are a few examples:

[0176] Scenario Database profile Typical "fake foreign key" misjudgment Which layer's negative samples are most useful Why 1. SaaS e-commerce platform's "multi-store" instance Same schema, but each online store is a tenant; table names are fixed, column names are standardized shop_id, creator_id, order_id) creator_id and user_id overlap numerically, but have a primary-foreign key relationship; models often recognize them as PK-FK Tenant layer Each store's "employee account" is `creator_id`, but only in the tenant's table; add these to the tenant's negative sample pool, and switching to another store won't misjudge 2. Financial group's multi-system bank Logical integration of core accounting, risk control, and CRM systems; column names are diverse (loan_no, ln_no, credit_id) credit_id refers to customer credit rating in CRM, but "credit limit number" in risk control database; same name, contains id, not foreign key Domain layer (finance) This "industry-specific naming convention" does not exist in other industries; make it a financial domain negative sample, and other bank subsidiaries within the group can also benefit directly 3. Game company acquires small studio Merge Unity log database into big data warehouse; many columns end with guid (string) player_guid is often misjudged as pointing to the user_guid in the main table; but it's actually anonymous event logs, not user backtracking Global layer "Guids in all names are not necessarily associated with each other" is a general experience, put it in the global layer to solve once and for all 4. Government multi-department shared data warehouse or data lake Multiple independent business departments share a lake; columns up to 200k+ Data lake maps different departments' id_number (citizen's personal information identifier) to the same field name; models tend to think they are all foreign keys Domain layer (public affairs) + tenant layer (department) Citizen number can indeed cross databases, but each department also has "demo fake data" mixed together; domain layer teaches the model "first look at data quality, then recognize foreign keys", and the tenant layer marks demo tables as false samples

[0177] 1. Cross-tenant but same Schema (Scenario 1)

[0178] Problem: Fixed field name = insufficient naming information, leading the model to "force match" based on string similarity.

[0179] Tenant negative samples directly tell the model: In this store, creator_id is only a local foreign key in two or three tables, and similar columns should not be touched.

[0180] Benefits: When a new store is online, only a few hundred tenant-level false positive samples are needed for LoRA fine-tuning, immediately reducing false positives.

[0181] 2. Multi-industry differences (Scenario 2 & 3)

[0182] Finance and gaming are far apart in naming conventions.

[0183] Domain negative samples serve as a "industry-wide exclusion list":

[0184] Finance: credit_id, loan_no, acct_no do not refer to each other;

[0185] When migrating to other customers in the same industry, almost zero changes are needed.

[0186] 3. Large-scale data lake (Scenario 4)

[0187] Running a full-bank ER algorithm once a week is acceptable, but it may need to handle more than 200,000 columns at a time.

[0188] The biggest benefit of layering here is training cost:

[0189] Global layers are fixed and have captured common misjudgment patterns;

[0190] Department tenant layers only incrementally train their own samples, rather than re-running 20,000 hard-negative combinations.

[0191] 6.2 Acquisition of initial negative samples and cold start

[0192] In order to effectively start the layered negative sample learning mechanism, the accumulation of initial negative samples is crucial. This method intelligently acquires high-quality seed negative samples from the pre-phase:

[0193] 1. Rule-based filtered negative samples: In "Multi-strategy candidate relationship recall", candidate relationship pairs that are explicitly excluded by heuristic rules (such as data type incompatibility, base number check failure, meaningless self-reference, etc.) will be used as initial negative samples. This type of sample is usually classified as a global negative sample pool due to its clear non-relationship characteristics, which is used to build the basic discrimination ability of the model. For example, if column T1.C1 (INT) and T2.C2 (STRING) are filtered due to type mismatch, then (T1.C1, T2.C2) constitutes an initial global negative sample.

[0194] 2. Negative samples based on high-confidence rejection by LLM: In "LLM Structured Inference and Validation", if a candidate pair is judged as non-foreign key (is_foreign_key: false) by LLM with a high confidence score (e.g., confidence_score > 0.9), this candidate pair rejected by LLM's deep semantic analysis will also be selected as an initial negative sample. Its attribution level (global, domain) will refer to the rationale field output by LLM to determine the universality of its rejection logic.

[0195] 3. Strong negative samples based on data sampling failure: In "Data Sampling and Cross-Validation", for those candidate pairs that may have some ambiguity at the metadata level, but the data sampling results (such as inclusion rate far below the preset threshold) clearly show that they do not constitute a relationship, they will be strong initial negative samples. This type of sample is strictly falsified at the data level, and is extremely valuable for the model to learn "seemingly reasonable but not actually" patterns.

[0196] The initial negative samples obtained in the above manner 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 to specific database environments.

[0197] 6.3 Dynamic management and optimization of negative sample pool

[0198] To ensure the continuous effectiveness of the hierarchical negative sample pool, avoid resource consumption caused by unlimited expansion, and maintain the timeliness and high quality of samples, this method uses one or more of the following dynamic management and optimization strategies:

[0199] 1. Time-based decay mechanism: Introduce a timestamp for negative samples and dynamically adjust their sampling probability or importance based on their "age". Older negative samples (e.g., more than 12 months) that have not been used effectively for ICL for a long time will have their weights significantly reduced or removed to reflect potential evolution of business logic and data patterns.

[0200] 2. Pool capacity management and representative sampling: Set a reasonable capacity guidance upper limit for each level of the negative sample pool. When new high-quality negative samples (e.g., newly discovered through data sampling or typical misjudgments from manual feedback) need to be added, if the pool is close to saturation, replace lower-value old samples based on factors such as "difficulty" (model misjudgment confidence), "novelty" (difference from existing negative sample patterns), and "age", to ensure the overall quality and representativeness of samples in the pool.

[0201] 3. Negative sample redundancy analysis: By analyzing the similarity of negative samples in metadata features or embedding space, identify and merge or remove highly redundant negative sample entries to improve the refinement of the negative sample pool and training efficiency.

[0202] Through these dynamic management mechanisms, the method ensures that the hierarchical negative sample pool not only continuously absorbs new learning experience, but also maintains its vitality and high relevance to the current model optimization goal through "metabolism", thereby supporting long-term self-evolution and performance improvement of the system.

[0203] 6.4 Implementation steps

[0204] 1. Hierarchical construction of negative sample pool:

[0205] Global pool: Store industry-independent difficult negative samples (such as naming similar non-PK-FK pairs).

[0206] Domain pool: Misjudgment samples specific to finance, retail and other fields.

[0207] Tenant pool: Local misjudgment samples of specific database instances.

[0208] Negative sample data structure example:

[0209] {

[0210] "negative_pair" : {

[0211] "source_table" : "string",

[0212] "source_column" : "string",

[0213] "target_table" : "string",

[0214] "target_column" : "string"

[0215] },

[0216] "layer" : "global|domain|tenant",

[0217] "domain" : "string|null",

[0218] "tenant_id" : "string|null",

[0219] "reason" : "string",

[0220] "timestamp" : "ISO8601 string"

[0221] }。

[0222] 2. Dynamic sampling and weight adjustment:

[0223] Initial sampling weights: Global: Domain: Tenant = 3:2:1.

[0224] Dynamic adjustment based on sliding window precision P: if layer X precision < threshold T, then w <- w + Dw. For example, increase tenant pool weight if tenant accuracy < 0.9.

[0225] 3. Difficulty negative sample mining:

[0226] Identify samples with ambiguous confidence (0.4-0.6) and verification errors, and add them to the corresponding pool.

[0227] 4. LoRA fine-tuning:

[0228] Incremental fine-tuning of embedding model and LLM using negative sample pool data.

[0229] Hardware resource configuration:

[0230] GPU requirements: single NVIDIA A100 80GB GPU;

[0231] Memory usage: 15-20GB (including model loading, gradient calculation, optimizer state);

[0232] Fine-tuning time: 8-11 minutes (depending on the number of negative samples);

[0233] Adapter size: 5-10MB (about 8MB when r=32).

[0234] 5. Cross-warehouse negative sample sharing judgment method:

[0235] Objective: Determine to what extent the negative samples learned from one data warehouse (source) are applicable to another data warehouse (target), especially at the global and domain levels.

[0236] Steps:

[0237] a. Compute the naming similarity: Use Jaccard similarity to compare the naming conventions of table names and column names between source warehouse and target warehouse. Jaccard similarity is used to measure the degree of overlap between two sets of names (one set from the source warehouse and the other set from the target warehouse). Its calculation method is: first find the number of table names and column names that the two warehouses have in common, then find the total number of all unique table names and column names of the two warehouses (i.e. the union set). Finally, divide the number of common names by the total number of unique names. This result (Jaccard index) is a value between 0 and 1, the closer to 1 indicates that the naming conventions of the two warehouses are more similar, the closer to 0 indicates the greater the difference.

[0238] b. Determine the applicable rate: According to the Jaccard similarity index calculated in the previous step, determine the "applicable rate" of a negative sample. This rate guides how many percentage of negative samples should be recommended or migrated from the global and domain negative sample pool of the source warehouse to the target warehouse. For example, the Jaccard index can be divided by a preset sensitivity threshold (such as 0.1), and the result is limited to a maximum of 1. If the calculated Jaccard index is 0.05, the applicable rate may be set to 0.5, which means that about 50% of the global / domain level negative samples in the source warehouse can be considered for application to the target warehouse. If the Jaccard index reaches or exceeds the set threshold (such as 0.1), the applicable rate can be set to 1, indicating that the negative samples of the source warehouse are highly relevant to the target warehouse and can be shared in large quantities.

[0239] Application: R_applicable determines how many percentage of negative samples should be recommended or migrated from the global and domain negative sample pool of the source warehouse to the target warehouse. A high R_applicable value supports more extensive sharing of negative samples, which helps to quickly apply learned experience on new warehouses and reduce false positives.

[0240] In addition to LoRA fast fine-tuning, the three-layer negative sample pool can also be used as In-Context examples or retrieve context injection LLM during inference to instantly eliminate false positives (see the next section for negative sample ICL enhancement).

[0241] 6.5 Negative Sample In-Context Learning (ICL) Enhancement

[0242] Purpose: The "Negative Sample In-Context Learning (ICL) augmentation" mechanism proposed in this section aims to provide a agile, low-cost and highly interpretable way to instantly improve the accuracy of large language models (LLM) on relationship judgment tasks, especially when dealing with difficult or ambiguous candidate relationship pairs. As a complement and enhancement to LoRA fine-tuning (a "long-term memory" solidification method for model parameter updates), it has the following key values:

[0243] 1. Instant performance improvement and error correction: Unlike LoRA, which requires retraining or fine-tuning model parameters, ICL directly guides the LLM to make more accurate judgments in the current inference step by dynamically injecting relevant positive and negative sample examples (especially "difficult" negative samples retrieved from a hierarchical negative sample pool) into the input prompt of the LLM. This achieves "plug-and-play" performance improvement without waiting for model update cycles, allowing rapid response to newly discovered misjudgment patterns.

[0244] 2. Enhance inference transparency and interpretability: By showing the LLM specific counterexamples (negative samples) similar to the current case to be judged, ICL not only helps the model avoid making the same mistake, but also encourages the model to more clearly explain in its output "rationale" why a certain candidate pair is not a foreign key relationship. This makes the model's decision-making process more transparent to users, easy to understand and trust, especially when diagnosing and correcting errors.

[0245] 3. Agile with zero training cost: The use of ICL does not involve any training or updating of model parameters, so its computational cost is mainly concentrated on sample retrieval and slightly increased prompt length, which can be considered as "zero training cost". This makes it a very flexible and economical means, especially suitable for scenarios that require rapid iteration, verification of new knowledge or limited resources. It gives the system a "short-term memory" capability to instantly utilize the latest negative sample insights, which can be filtered and used for more durable LoRA fine-tuning later.

[0246] Example Prompt Structure:

[0247] / / Positive Example START (from existing knowledge or previous verification)

[0248] Candidate Pair:

[0249] Primary Key: TableA.ID (Metadata: INT, PK, NOTNULL, Note: Unique identifier of TableA)

[0250] Foreign Key: TableB.A_ID (Metadata: INT, NULL, Comment: references TableA);

[0251] Contextual Information: TableB is typically linked to TableA to fetch transaction data.

[0252] Analysis:

[0253] - TableB.A_ID and TableA.ID have compatible data types.

[0254] - Column names A_ID and ID imply an association.

[0255] - Contextual information supports this link.

[0256] Decision:

[0257] {"source_table": "TableB",

[0258] "source_column": "A_ID",

[0259] "target_table": "TableA",

[0260] "target_column": "ID",

[0261] "is_foreign_key": true,

[0262] "confidence_score": 0.95,

[0263] "rationale": "Based on naming convention, data type compatibility, and contextual information, TableB.A_ID is likely to reference TableA.ID as a foreign key."};

[0264] [Positive Example END].

[0265] / / Negative Sample Example (from Tenant Layer Negative Sample Pool) [Negative Example START];

[0266] Candidate Pair:

[0267] Primary Key: shop.employee.creator_id (Metadata: INT, PK, NOT NULL, Comment: Unique ID of the employee who created this record in this shop);

[0268] Foreign key: another_shop_orders.creator_id (Metadata: INT, NULL, Comment: The 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 each shop.

[0269] analyze:

[0270] - Although the column names I creator_id are the same and the data types match, they operate in different tenant contexts.

[0271] - No explicit cross-tenant relationship is defined for creator_id.

[0272] - Numerical overlaps may exist, but this does not represent a true foreign key relationship across tenants.

[0273] decision making:

[0274] {"source_table":"another_shop_orders",

[0275] "source_column":"creator_id",

[0276] "target_table":"shop.employee",

[0277] "target_column":"creator_id",

[0278] "is_foreign_key": false

[0279] "confidence_score": 0.80, / / The model is confident that this is not a foreign key.

[0280] "rationale": "Although the column names 'creator_id' and 'other_shop_orders' are the same, 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."

[0281] [Negative Example END].

[0282] / / Current candidate relationship to be evaluated;

[0283] [Current Candidate START] Candidate Pairs:

[0284] Primary key: <PK_table>.<PK_column>(<PK_metadata>);

[0285] Foreign key: <FK_table>.<FK_column>(<FK_metadata>);

[0286] Context information: Analysis of relevant context information for the current candidate pair:

[0287] / / LLM makes a decision on chain analysis here:

[0288] { JSON output of LLM};

[0289] [Current Candidate END],

[0290] ICL and LoRA fine-tuning collaborative strategy:

[0291] The three-layer negative sample pool can follow the existing hierarchical weight logic, "retrieve first (for ICL), then fine-tune (LoRA), and then solidify (update model parameters)", forming a closed loop.

[0292] Stage 0 (Baseline): Model relying only on LoRA fine-tuning, establishing performance baseline.

[0293] Stage 1 (ICL Augmentation): On the basis of fine-tuning the model, introduce the ICL layer. Especially for candidate relationships with low output confidence or known difficult patterns, relevant negative samples are injected into the prompt for immediate remediation and explanation enhancement.

[0294] Stage 2 (Feedback Loop to LoRA): Regularly analyze the performance of ICL, identify those "new" difficult negative samples that have been successfully corrected by ICL and are representative, or samples that still perform poorly with the assistance of ICL, and add them 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" in ICL.

[0295] Engineering practice considerations:

[0296] In actual engineering, in order to ensure the effectiveness of ICL (In-Context Learning), a variety of strategies and considerations are used. Especially when dealing with a large number of samples, the strategy of dynamically selecting ICL examples from the hierarchical negative sample pool (global, domain, tenant) is particularly critical.

[0297] 1. Prioritize difficult samples: Prioritize "difficult" negative samples that are historically misclassified by the model or have high similarity to the current candidate relationship feature being evaluated as ICL examples. This can strengthen the model's ability to recognize complex scenarios.

[0298] 2. Control context length: Strictly control the number of injected ICL examples (e.g., a total of 5-8 positive and negative examples) to the optimal recognition area of the LLM's context window. If more information is needed, consider using more advanced techniques such as Retrieval Augmented Generation (RAG) or Soft-Prompt.

[0299] 3. Ensure example diversity: Avoid excessive single or repetitive table name and column name patterns in ICL examples within a single prompt. This helps reduce the model's memory bias on surface location information and encourages it to learn more generalized and robust patterns.

[0300] 4. Mix positive and negative samples: Cross or strategically arrange positive and negative samples in the prompt instead of simply stacking them separately. This approach helps the model better learn and distinguish decision boundaries.

[0301] 5. Establish automatic feedback and persistence mechanism: The system automatically records "new" difficult negative samples and their context that are successfully identified or still failed in each round of reasoning. These records can be reviewed by humans, and the approved samples will be added to the corresponding negative sample pool for subsequent ICL retrieval or longer-term LoRA fine-tuning.

[0302] 6. Gray-scale verification and traffic allocation strategy: Allocate online reasoning traffic to "fine-tuning only" and "fine-tuning + ICL" strategies in a certain proportion (e.g., 80 / 20).

[0303] 7. Cost and efficiency balancing strategy: When GPU resources are scarce or new patterns need to be responded to quickly, prefer to enable ICL, as it has relatively low computational cost and does not require retraining. In periods of relatively abundant computing resources (e.g., night batch processing), perform LoRA fine-tuning, which will solidify the knowledge of valid and universal negative samples verified by ICL into model parameters, to improve the performance of the base model.

[0304] In some embodiments, ER graph generation and visualization:

[0305] Function: This stage is the result presentation part of the whole relationship discovery process, and its core purpose is to systematically integrate the effective primary-foreign key relationships identified in all previous stages (especially LLM inference and data sampling) and transform them into entity-relationship (ER) diagrams that are user-friendly, easy to understand and use. This not only includes clearly expressing tables, key columns and their reference relationships in the database in a standardized textual description language (such as Mermaid code), but also includes using corresponding tools to render these textual 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 facilitating the understanding of complex data models, assisting in system design and maintenance, improving missing documentation, and supporting more efficient data governance and decision-making.

[0306] Steps:

[0307] 1. Integrate LLM confidence and sampling results to confirm primary-foreign key relationships;

[0308] 2. Output Mermaid format;

[0309] 3. Use Mermaid Live Editor or PlantUML to render visual ER diagrams.

[0310] Example 1: Database relationship discovery of a large commercial bank's core system

[0311] 1.1 Application background

[0312] A certain database system of a large commercial bank has evolved over 20 years, containing more than 3,000 tables and 45,000 columns, covering account management, loan management, risk control system, customer relationship management and other subsystems. Due to management performance requirements, explicit foreign key constraints are not allowed, and the original design document has become severely outdated. The bank urgently needs to sort out a complete data relationship diagram to support data governance and system modernization.

[0313] 1.2 Implementation process

[0314] Stage One: Metadata Collection and Preprocessing

[0315] The system extracts metadata from the Oracle19c database, and a typical table structure example is as follows:

[0316] -- Customer Information Table

[0317] TB_CUST_INFO(CUST_NO VARCHAR2(20),-- Customer Number (actual primary key, but no PK constraint)

[0318] CUST_NAME VARCHAR2(100), -- Customer Name

[0319] CERT_TYPE VARCHAR2(2), --Document type

[0320] CERT_NO VARCHAR2(30), -- ID number

[0321] REG_DATE (DATE -- Registration Date).

[0322] --Account Information Table

[0323] TB_ACCT_INFO(ACCT_NO VARCHAR2(30),--Account (Primary Key)

[0324] CUST_ID VARCHAR2(20), -- Customer ID (implicit foreign key)

[0325] ACCT_TYPE VARCHAR2(10), -- Account type

[0326] OPEN_DATE DATE, -- Account opening date

[0327] BALANCE NUMBER(15,2) -- Balance).

[0328] --Loan Information Sheet

[0329] TB_LOAN_MSTR(LOAN_NO VARCHAR2(30),--Loan number (primary key)

[0330] BORROWER_ID VARCHAR2(20), -- Borrower ID (implicit foreign key)

[0331] LOAN_AMT NUMBER(15,2), -- Loan amount

[0332] LOAN_TERM NUMBER(3), -- Loan Term

[0333] RATE NUMBER(5,4) -- Interest Rate.

[0334] --Transaction Log

[0335] TB_TRANS_FLOW(TRANS_ID VARCHAR2(40), --Transaction ID (primary key)

[0336] ACCT_NO VARCHAR2(30), -- Account (implicit foreign key)

[0337] TRANS AMT NUMBER(15,2),--transaction amount

[0338] TRANS TIME TIMESTAMP--transaction time).

[0339] Example of structured description generated after preprocessing:

[0340] "Table: TB_CUST_INFO | Column: CUST_NO | Type: VARCHAR2(20) | Nullable: NO | Primary Key: NO | Unique: YES | Description: Customer Number";

[0341] "Table: TB_ACCT_INFO | Column: CUST_ID | Type: VARCHAR2(20) | Nullable: NO | Primary Key: NO | Unique: NO | Description: Customer Identifier".

[0342] Phase Two to Three: Vectorization and Candidate Recall

[0343] Generate vectors using BAAI / bge-large-zh model fine-tuned in the financial field;

[0344] Identify 312 potential primary key columns (about 0.7%);

[0345] Generate 9,360 candidate relationship pairs through vector retrieval.

[0346] Phase Four: LLM Inference

[0347] Example output of LLM inference:

[0348] {"source_table":"TB_ACCT_INFO",

[0349] "source_column":"CUST_ID",

[0350] "target_table":"TB_CUST_INFO",

[0351] "target_column":"CUST_NO",

[0352] "is_foreign_key":true,

[0353] "confidence_score":0.92,

[0354] rationale: "CUST_ID and CUST_NO, although slightly different in naming, both represent customer identification. Considering that accounts in the banking system must be associated with customers and the data types are completely matched, it is likely a foreign key relationship."

[0355] Phase Five: Data Sampling Verification

[0356] Extract 1,000 non-empty values of TB_ACCT_INFO.CUST_ID;

[0357] Inclusion rate check: 998 / 1000 = 99.8% (pass);

[0358] Confirm the uniqueness of TB_CUST_INFO.CUST_NO: 100% (pass).

[0359] Phase Six: Hierarchical Negative Sample Learning

[0360] Examples of negative samples specific to the finance field:

[0361] {"negative_pair":{

[0362] "source_table":"TB_LOAN_MSTR",

[0363] "source_column":"LOAN_NO",

[0364] "target_table":"TB_ACCT_INFO",

[0365] "target_column":"ACCT_NO"},

[0366] "layer":"domain",

[0367] "domain":"finance",

[0368] "reason":"Although the format of loan number and account number is similar, they are independent numbering systems in the banking system. Loans may be associated with multiple accounts or not associated with accounts." "timestamp":"2024-11-15T10:30:00Z"}.

[0369] 1.3 Implementation Effect

[0370] 487 previously undocumented key relationships were discovered; 23 incorrect historical documentation records were identified; complete basis for data bloodline analysis was provided; supported the bank's data middle platform construction project.

[0371] The final generated mermaid is as follows:

[0372] Each table has a primary key that uniquely identifies each record in the table:

[0373] USERS table: user_id (PK) - User unique identifier;

[0374] ORDERS table: order_id (PK) - Order unique identifier;

[0375] ORDER_ITEMS table: item_id (PK) - Order detail unique identifier;

[0376] PRODUCTS table: product_id (PK) - Product unique identifier;

[0377] CATEGORIES table: category_id (PK) - Category unique identifier;

[0378] Foreign Key (FK) relationship explanation:

[0379] 1. ORDERS.customer_id (FK) → USERS.user_id (PK);

[0380] Meaning: The customer_id field in the orders table references the user_id in the users table;

[0381] Business relationship: Indicates "which user placed this order";

[0382] Mermaid relationship line: USERS || --o {ORDERS: "places";

[0383] 2. ORDER_ITEMS.order_id (FK) → ORDERS.order_id (PK);

[0384] Meaning: The order_id field in the order_items table references the order_id in the orders table;

[0385] Business relationship: Indicates "which order does this detail belong to";

[0386] Mermaid relationship line: ORDERS || --o {ORDER_ITEMS: "contains";

[0387] 3. ORDER_ITEMS.product_id (FK) → PRODUCTS.product_id (PK);

[0388] Meaning: The product_id field in the order_items table references the product_id in the products table;

[0389] Business relationship: Indicates "which product is bought in the order item";

[0390] Mermaid line: PRODUCTS || --o{ORDER_ITEMS: "ordered_as";

[0391] 4. PRODUCTS.category_id (FK) → CATEGORIES.category_id (PK);

[0392] Meaning: The category_id field in the products table references the category_id in the categories table;

[0393] Business relationship: Indicates "which category a product belongs to";

[0394] Mermaid line: CATEGORIES || --o{PRODUCTS: "categorizes";

[0395] 5. CATEGORIES.parent_id (FK) → CATEGORIES.category_id (PK);

[0396] Meaning: The parent_id field in the categories table references the category_id of the same table (self-reference);

[0397] Business relationship: Indicates "the hierarchy of categories, such as 'Electronics' is the parent category of 'Mobile Phones'";

[0398] Mermaid line: CATEGORIES || --o{CATEGORIES: "parent_of";

[0399] Explanation of relationship symbols:

[0400] || indicates the "one" end (primary key end);

[0401] o{ indicates the "many" end (foreign key end);

[0402] || -- o{ indicates a one-to-many relationship.

[0403] Such ER graph structure is the target of the database relationship intelligent discovery system of the application to automatically identify: in the database lacking explicit foreign key constraints, through the analysis of table name, column name, data type and other metadata, combined with LLM reasoning and data verification, the implicit primary-foreign key relationship is automatically discovered.

[0404] Technical principle of LoRA fast fine-tuning:

[0405] LoRA (Low-Rank Adaptation) realizes efficient fine-tuning of parameters by inserting a low-rank matrix in the linear layer of the pre-trained model:

[0406] Parameter comparison: traditional full-fine-tuning needs to update all model parameters (e.g. 7B model about 28GB), while LoRA only updates adapter parameters (about 5-10MB, less than 0.1% of the total). Computational complexity: the training time complexity is reduced from O(N) to O(r²), where r is the rank of the low-rank matrix (usually r=16-64).

[0407] Hardware requirements: using LoRA to fine-tune StarCoder and other large models, a single high-performance GPU (such as NVIDIA A100 80GB) can usually meet the requirements.

[0408] Specific measurement of 10-minute fine-tuning:

[0409] Due to the large differences between actual projects, experimental tests are used for estimation on NVIDIA A100 80GB GPU:

[0410] Negative sample data loading: about 30 seconds;

[0411] LoRA adapter initialization: about 10 seconds;

[0412] Fine-tuning training (500-1000 negative samples, 5 epochs): about 8 minutes Model saving and verification: about 1 minute;

[0413] Total: about 10 minutes.

[0414] The present application has significant differences and beneficial effects:

[0415] 1. Targeted and accurate: Unlike Belefqih et al. who mainly focus on schema structure extraction of NoSQL, the present application specifically targets relational databases (especially in production environments lacking explicit foreign key constraints) and focuses on the accurate identification of primary-foreign key (PK-FK) semantic relationships and the automatic construction of ER graphs.

[0416] 2. Innovation of core technology path:

[0417] Hierarchical negative sample self-evolution learning: The invention introduces a three-layer negative sample pool management mechanism of global, domain and tenant, dynamically samples difficult negative samples, and combines LoRA (Low-Rank Adaptation) lightweight and fast fine-tuning technology, which can complete the personalized model adaptation of new database environment or specific tenant in a very short time (for example, 10 minutes level), significantly improving the generalization ability and cold start efficiency of the model. This is different from the path of LEDD mainly relying on LLM for summary and general relationship analysis, and also not involved in the method of Belefqih et al.

[0418] LLM structured inference and interpretable output: The invention uses fine-tuned LLM for relationship inference, and forces it to output structured JSON results containing relationship judgment, confidence score (confidence_score) and explanation rationale. This design not only guarantees the high interpretability of the results, but also enables seamless integration into downstream pipelines such as automated data governance, CMDB update or DevOps. This is not explicitly emphasized or implemented by existing technologies such as Belefqih et al. and LEDD (at least in their published documents).

[0419] 3. Multi-stage verification and accuracy guarantee: The invention adopts a multi-stage process including domain-aware metadata vectorization, multi-strategy candidate relationship recall, LLM structured inference verification and data sampling cross-validation, which cooperatively improves the accuracy of relationship discovery, effectively filters noise, and reduces false positives and false negatives.

[0420] 4. Cross-scene adaptability and efficiency: Through hierarchical negative sample management and LoRA fast fine-tuning, the invention can efficiently adapt to the needs of different business domains, multi-tenants and heterogeneous database environments, ensuring high relationship discovery performance in diverse scenarios.< / explanation> < / length>

Claims

1. A method for intelligent discovery of database relationships and ER diagram construction, characterized in that, Comprise: S1. Extract metadata from at least one heterogeneous database, normalize the metadata, and obtain a structured metadata description list; S2. Convert the column description text in the structured metadata description list into a high-dimensional semantic vector using a field-tuned embedding model, and construct an approximate nearest neighbor index based on the semantic vector; S3. According to the column name rule, vector similarity retrieval is performed through the approximate nearest neighbor index, and heuristic filtering rules are combined to generate a set of primary-foreign key candidate pairs; S4. Call the LLM tuned by the instruction, and based on the primary-foreign key candidate pair set, output structured data results for each primary-foreign key candidate pair; S5. Based on the structured data results, randomly extract foreign key column sample values and calculate their inclusion rate in the primary key column. If the inclusion rate is not lower than the preset threshold, the primary-foreign key relationship is confirmed; S6. Maintain three types of negative sample pools at the global, domain, and tenant levels. New difficult negative samples generated by data sampling or manual feedback are archived according to the level. Based on the negative samples, LoRA is used for rapid fine-tuning to update the model parameters and generate negative sample pool update records; S7. Convert the primary-foreign key relationship confirmed by data sampling into a graph description language, combine the difficult negative sample processing information in the negative sample pool update records, add relationship confidence annotations and misjudgment avoidance explanations in the ER graph, and render a visual ER graph. 2.The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, The initial sampling weight of the three types of negative sample pools is set to global layer: domain layer: tenant layer = 3:2:1, and the sampling weight is automatically adjusted according to the accuracy of each layer model. 3.The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, During LLM inference, difficult negative samples from the negative sample pool are injected into the Prompt in the In-Context Learning mode for immediate correction of potential misjudgments.

4. The method of claim 1, wherein, Through sampling 1000 non-empty values of the foreign key column and calculating that at least 95% of the values exist in the primary key column, data sampling is determined to pass.

5. The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, Use the approximate nearest neighbor index construction tool to construct the approximate nearest neighbor index based on the semantic vector.

6. The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, Difficult negative samples that are successfully corrected or still misjudged through context learning are automatically archived in the negative sample pool for subsequent LoRA rapid fine-tuning.

7. The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, In the ER graph generation step, the confidence score and explanation reason in the structured data results are rendered on the corresponding relationship line of the visual ER graph as interactive annotation information.

8. The database relationship intelligent discovery and ER diagram construction method of claim 1, wherein, Incremental fine-tuning of the embedding model and LLM using negative sample pool data is performed, and the incremental fine-tuning is achieved by updating a set of low-rank adaptation parameters, wherein the number of low-rank adaptation parameters is less than 1% of the total parameter quantity of LLM.

9. The database relationship intelligent discovery and ER diagram construction method of claim 2, wherein, Periodically evaluate the performance of the model on negative samples at each level, and dynamically adjust the sampling weight according to the evaluation results.

Images