Database Column Inclusion Dependency Search for PK-FK Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Determining primary key-foreign key relationships in large databases is computationally intensive and inefficient due to the need for extensive disk input and output operations and brute force methods, especially when identifying inclusion dependencies between columns.
Innovation Solution
An inclusion dependency determination system (IDDS) that prunes, sorts, and arranges database columns based on dependency characteristics, dynamically selects search techniques, and minimizes disk operations by retaining or relinquishing data in memory, using dynamic pointers and relationship mapping to establish primary key-foreign key relationships.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If brute force method is used to determine inclusion dependency between all column pairs, then completeness of relationship detection is improved, but computational time and disk operations increase significantly
Solution Approach 1:
The patent applies preliminary action by first determining dependency characteristics (minimum value, maximum value, average step size) of all columns before proceeding to pair-wise comparison. This preliminary analysis filters out impossible column pairs early, reducing the search space for inclusion dependency determination without compromising completeness.
Solution Approach 2:
The patent applies local quality by dynamically selecting search techniques based on the specific characteristics of each column pair being compared. Different search algorithms (sequential search, binary search, hash-based search) are chosen based on the dependency characteristics of the specific column pair, optimizing the comparison process for each local case rather than using a uniform approach.
2Measurement precision
If all column pairs are explored for inclusion dependency, then accuracy of primary key-foreign key identification is improved, but disk input and output operations increase
Solution Approach 1:
The patent extracts and utilizes dependency characteristics (minimum value, maximum value, average step size) of columns to filter out column pairs that cannot possibly satisfy inclusion dependency. This extraction of key characteristics allows the system to focus disk operations only on promising column pairs, reducing unnecessary disk I/O while maintaining identification accuracy.
Solution Approach 2:
The patent applies dynamics by making the search technique selection adaptive based on the specific characteristics of each column pair. The system dynamically chooses the most efficient search algorithm (sequential, binary, or hash-based) depending on the dependency characteristics, optimizing the balance between accuracy and disk operations for each comparison task.
3Ease of manufacture
If sequential search is used for all column pairs, then implementation simplicity is improved, but search efficiency deteriorates
Solution Approach 1:
The patent applies dynamics by making the search technique selection adaptive based on the specific characteristics of each column pair. The system dynamically chooses the most efficient search algorithm (sequential, binary, or hash-based) depending on the dependency characteristics, optimizing the balance between accuracy and disk operations for each comparison task.
Solution Approach 2:
The patent applies parameter changes by modifying the search approach based on column characteristics such as data distribution, value ranges, and step sizes. The system changes the search parameters and algorithms dynamically to match the specific properties of each column pair, achieving optimal search efficiency without sacrificing implementation clarity.
Data Source
Figure 1
Figure 2A~2B
Figure 2C~2D
AI summary
A method and an inclusion dependency determination system (IDDS) for determining inclusion dependency between columns of tables in a target database to establish primary key (PK)-foreign key (FK) relationships among data in the columns with minimized disk input and output operations are provided. The IDDS determines dependency characteristic data (DCD) of each column and arranges the columns by applying one or more predefined rules to the columns based on a minimum value of the data of each column. The IDDS determines pairs of arranged columns that demonstrate a possibility of inclusion dependency based on the DCD and identifies a first column and a second column of each determined pair as a candidate PK and a candidate FK respectively. The IDDS determines inclusion dependency between the candidate PK and the candidate FK on comparing data of the candidate PK with the data of the candidate FK using dynamically determined search techniques.