Composite Key Detection in Database Schema
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing automatic-relationship-finding techniques in database systems are inefficient in detecting undeclared primary key/foreign key relationships involving composite keys, as they require testing each combination of columns, leading to exponential growth in work required.
Innovation Solution
The techniques described involve generating a filtered match pair collection by shrinking the set of candidate columns using fingerprints and matching pairs, thereby reducing the number of combinations to check and efficiently identifying potential undeclared PK-FK relationships based on composite keys.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If existing automatic-relationship-finding techniques are used to detect undeclared PK-FK relationships involving composite keys, then complete detection may be achieved, but the computational work required grows exponentially
Solution Approach 1:
The patent segments the detection process into multiple stages: first identifying candidate foreign key columns using statistical analysis and value matching, then forming candidate composite key combinations, and finally validating relationships. This segmentation avoids the need to test all possible column combinations simultaneously, reducing computational complexity from exponential to manageable levels while maintaining detection completeness.
Solution Approach 2:
The patent performs preliminary filtering of candidate columns before forming composite key combinations. By pre-identifying columns that are likely to be foreign keys based on value matching and statistical criteria, the system reduces the search space beforehand, preventing exponential growth in computational work while ensuring no valid relationships are missed.
2Measurement precision
If all combinations of columns are tested to ensure complete detection of composite key relationships, then detection accuracy is improved, but the time and resources required increase exponentially
Solution Approach 1:
The patent applies different detection strategies to different types of columns and relationships. Single-column foreign keys are detected using direct value matching, while composite keys use a combination of statistical analysis and targeted testing. This localized approach ensures high detection accuracy for each relationship type without applying overly complex methods universally, thereby reducing overall detection time.
Solution Approach 2:
The patent changes the detection parameters dynamically based on the characteristics of the data being analyzed. Statistical thresholds, value matching criteria, and combination testing parameters are adjusted based on column properties and data distributions, allowing accurate detection while optimizing computational resources for each specific detection scenario.
Data Source
AI summary
Techniques are described herein for automatically discovering undeclared PK-FK relationships between dimension tables and a fact table in situations where: the primary keys of the dimension tables (a) are not known, and (b) are composite keys, and the foreign keys of the fact table (a) are not known, and (b) are composite keys. The techniques involve removing from consideration all fact table columns that satisfy first removal criteria, and all dimension table columns that satisfy second removal criteria. Fingerprints are generated for the fact and dimension table columns that remain. Matched pairs (e.g. two fact table columns and two dimension table columns) are generated based on the columns that have not been filtered. The matched pairs are further filtered based on third removal criteria. The matched pairs that remain are then graded, and a PK-FK relationship is established based on the matched pair with the highest grade.