Cardinality constraints and functional dependencies in SQL: Taming data redundancy in logical database design

Abstract

We investigate the combined class of cardinality constraints and functional dependencies over SQL tables. As our first contribution, we establish a finite ground axiomatization and quadratic-time algorithm for deciding their implication problem. As our second contribution, we characterize when finite Armstrong tables exist for this class, and show how to compute finite representations of Armstrong tables for every given input. While there are extreme cases where the size of an Armstrong representation is logarithmic or necessarily exponential, our extensive experiments suggest that the size is low-degree polynomial on average. As our third contribution, we propose a new family of syntactic normal forms for the logical design of SQL tables, of which the well-known Boyce–Codd Normal Form is a special case. Our normal form characterizes SQL schemata that have an a priori upper bound on the number of records in which any redundant data value may occur in any database instance over the schema. Such bounds tame the number of records requiring updates to preserve data consistency.