FDTool: a Python application to mine for functional dependencies and candidate keys in tabular data [version 2; peer review: 2 approved]

• Published in: F1000 research Vol. 7; p. 1667
• Main Authors: Buranosky, Matt, Stellnberger, Elmar, Pfaff, Emily, Diaz-Sanchez, David, Ward-Caviness, Cavin
• Format: Journal Article
• Language: English
• Published: England F1000 Research Limited 2018; F1000 Research Ltd
• Subjects: Algorithms; Databases, Factual; Software; Software Tool; Rule discovery; Data mining; Electronic health records; Functional dependencies; Relational database; FDTool
• ISSN: 2046-1402; 2046-1402
• DOI: 10.12688/f1000research.16483.2

Functional dependencies (FDs) and candidate keys are essential for table decomposition, database normalization, and data cleansing. In this paper, we present FDTool, a command line Python application to discover minimal FDs in tabular datasets and infer equivalent attribute sets and candidate keys from them. The runtime and memory costs associated with seven published FD discovery algorithms are given with an overview of their theoretical foundations. Previous research establishes that FD_Mine is the most efficient FD discovery algorithm when applied to datasets with many rows (> 100,000 rows) and few columns (< 14 columns). This puts it in a special position to rule mine clinical and demographic datasets, which often consist of long and narrow sets of participant records. The structure of FD_Mine is described and supplemented with a formal proof of the equivalence pruning method used. FDTool is a re-implementation of FD_Mine with additional features added to improve performance and automate typical processes in database architecture. The experimental results of applying FDTool to 13 datasets of different dimensions are summarized in terms of the number of FDs checked, the number of FDs found, and the time it takes for the code to terminate. We find that the number of attributes in a dataset has a much greater effect on the runtime and memory costs of FDTool than does row count. The last section explains in detail how the FDTool application can be accessed, executed, and further developed.