Identification Of Primary And Foreign Keys

A computer-implemented method for determining foreign keys in a second table relative to a given column designates as a primary key in a first table of a database system, comprising:

• generating, by the computer processor, a first probabilistic data structure for the given column, where the first probabilistic data structure is partitioned into a plurality of registers and configuration parameters for the first probabilistic data structure includes a first recording parameter, base, that controls recording of data into the first probabilistic data structure;
• for a first column of the second table, generating, by the computer processor, another probabilistic data structure, where the another probabilistic data structure is partitioned into a plurality of registers and configuration parameters for the another probabilistic data structure includes another recording parameter, base, that controls recording of data into the another probabilistic data structure;
• computing, by the computer processor, a cardinality estimate for the first column using the another probabilistic data structure;
• computing, by the computer processor, an inclusion coefficient for the first column of the second table in relation to the given column of the first table based in part on the cardinality estimate for the first column;
• comparing, by the computer processor, the inclusion coefficient to a threshold;
• and
• designating the first column of the second table as a foreign key in response to the inclusion coefficient being greater than the threshold.

A computer-implemented method for formulating a join operation in a database system, comprising:

• receiving, by a computer processor, an indicator for a first table to be joined;
• determining, by the computer processor, a given column of the first table as a primary key by
• determining, by a computer processor, a number of rows in the table;
• for a given column of the table, generating, by the computer processor, a probabilistic data structure for the given column;
• computing, by the computer processor, a cardinality estimate for the given column using the probabilistic data structure;
• computing, by the computer processor, a ratio between the cardinality estimate for the given column and the number of rows in the table;
• comparing, by the computer processor, the ratio to a threshold;
• designating the given column as a primary key for the table in response to the ratio being greater than the threshold;
• determining, by the computer processor, foreign keys in a second table relative to the given column designated in the first table, where the determination is based in part on the probabilistic data structure;
• receiving, by the computer processor, a join operation for joining the first table with the second table in the database system using the foreign keys;
• and
• joining, by the computer processor, the first table with the second table according to the join operation.