ML-Based Relational Key Discovery

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing techniques for identifying primary and foreign keys in relational datasets are inefficient, prone to errors, and unable to handle large datasets or data quality issues, particularly in identifying multi-field primary keys and overcoming data quality challenges.

Innovation Solution

The use of machine learning models to analyze data profiles of relational datasets, identifying primary and foreign key candidates by scoring proposals based on feature values, and generating graphical user interfaces for user evaluation and selection.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If traditional techniques are used to identify primary and foreign keys in relational datasets, then the process is simple and straightforward, but the techniques are inefficient, prone to errors, and unable to handle large datasets or data quality issues

Engineering Contradiction:

Improveaccuracy of key identificationVSAvoidefficiency of key discovery process

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent replaces traditional mechanical/manual key identification techniques with machine learning models. The system profiles relational datasets to extract features, then uses trained machine learning models to automatically score and identify primary and foreign key candidates, eliminating manual intervention and improving both accuracy and efficiency.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Solution Approach 2:

The patent introduces data profiling as an intermediary step between the raw relational dataset and the machine learning model. The profiler extracts relevant features (nullity, cardinality, distinctness, etc.) that serve as inputs to the machine learning models, enabling them to make accurate key identification decisions without directly processing the entire dataset.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Productivity

If machine learning models are used to analyze data profiles and score key proposals, then accuracy and scalability improve, but computational resources and system complexity increase

Engineering Contradiction:

Improvescalability to large datasetsVSAvoidcomplexity of machine learning system

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent segments the key identification process into distinct modular components: a data profiling module that extracts features from relational datasets, separate machine learning models for primary key and foreign key identification, and a candidate ranking system. This segmentation makes the complex system manageable and allows each component to be optimized independently.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent performs data profiling as a preliminary action before applying machine learning models. By pre-processing the relational dataset to extract relevant features (nullity, cardinality, distinctness, etc.), the system reduces the computational burden on the machine learning models and enables faster, more scalable key identification on large datasets.

Inventive Principle:

Principle #10Preliminary action

3Adaptability or versatility

If existing techniques are used, then the implementation is straightforward, but they are unable to handle multi-field primary keys or overcome data quality challenges

Engineering Contradiction:

Improveability to handle multi-field keysVSAvoidcomplexity of handling data quality issues

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent applies local quality by evaluating different field combinations with different criteria. The machine learning models assess individual fields and their combinations based on local characteristics (uniqueness, nullity, cardinality) to identify multi-field primary keys, allowing each field combination to be evaluated on its own merits rather than applying a uniform approach.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent uses composite approaches by combining multiple machine learning models (for primary key and foreign key identification) and integrating their outputs. The system also combines multiple features from data profiling (nullity, cardinality, distinctness) to create a comprehensive evaluation framework that handles data quality issues through the collective judgment of multiple models and features.

Inventive Principle:

Principle #40Composite materials

Data Source

PatentUS20250036602A1Machine learning techniques for discovering keys in relational datasets

Publication Date: 2025.01.30 AB INITIO TECHNOLOGY LLC

AI summary

Techniques for discovering primary, unique, and/or foreign keys for relational datasets are described. The techniques include profiling the relational datasets to obtain respective data profiles; identifying one or more primary key candidates for a first relational dataset using a first data profile of the first relational dataset and a first trained machine learning model; identifying one or more foreign key proposals for a second relational dataset using the one or more primary key candidates by performing a subset analysis of the second relational dataset with respect to the first relational dataset; identifying one or more foreign key candidates for the second relational dataset using the first data profile, a second data profile of the second relational dataset, and a second trained machine learning model different from the first trained machine learning model; and outputting the at primary key candidate(s) and the foreign key candidate(s).