Database Key Identification Using Probabilistic Cardinality Sketches
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for identifying primary and foreign keys in database systems are not computationally efficient, especially for big data applications, and lack a unified approach for both types of keys.
Innovation Solution
A computer-implemented method using SetSketch probabilistic data structures to determine primary and foreign keys by generating, updating, and comparing cardinality estimates and inclusion coefficients against thresholds, allowing efficient computation and memory usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If complete data checking is performed to identify primary and foreign keys, then identification accuracy is improved, but computational efficiency deteriorates
Solution Approach 1:
The patent applies partial action by using probabilistic data structures (HyperLogLog sketches, Bottom-k sketches) to estimate cardinality and inclusion coefficients without examining complete data. Instead of checking all data for duplicates or comparing all foreign key candidates against primary key candidates, the system processes a probabilistic sample that provides sufficiently accurate estimates with much lower computational cost, thus resolving the contradiction between identification accuracy and computational efficiency
Solution Approach 2:
The patent creates probabilistic copies (sketches) of the actual data in primary and foreign key columns. These sketches are compact representations that capture essential statistical properties (cardinality, inclusion relationships) without containing the complete original data. By operating on these copied probabilistic structures rather than the full data, the system achieves accurate key identification with reduced computational overhead
2Productivity
If probabilistic data structures are used to improve computational efficiency, then processing speed is improved, but measurement precision deteriorates
Solution Approach 1:
The patent adjusts parameters of probabilistic data structures (number of registers in HyperLogLog, sketch size in Bottom-k) to optimize the balance between computational efficiency and measurement precision. By carefully selecting these parameters, the system achieves sufficiently accurate cardinality and inclusion coefficient estimates while maintaining high processing speed, thus resolving the contradiction between computational efficiency and measurement precision
3Adaptability or versatility
If a unified approach for identifying both primary and foreign keys is implemented, then method versatility is improved, but device complexity increases
Solution Approach 1:
The patent implements a unified algorithm that serves multiple functions: it identifies both primary keys and foreign keys using the same probabilistic data structures and computational steps. The same HyperLogLog or Bottom-k sketches are used to estimate cardinality for primary key detection and inclusion coefficients for foreign key detection, eliminating the need for separate specialized algorithms and simplifying the overall system while maintaining versatility
Data Source
AI summary
A computer-implemented method is presented for determining primary keys in a table of a database system. The method includes: determining a number of rows in the table; for a given column of the table, generating a probabilistic data structure for the given column, where the probabilistic data structure is partitioned into a plurality of registers and configuration parameters for the probabilistic data structure includes a first recording parameter, base, that controls recording of data into the probabilistic data structure; computing a cardinality estimate for the given column using the probabilistic data structure; computing a ratio between the cardinality estimate for the given column and the number of rows in the table; comparing the ratio to a threshold; and designating the given column as a primary key for the table in response to the ratio being greater than the threshold.