Embedded functional dependencies and data-completeness tailored database design

Abstract

We establish a robust schema design framework for data with missing values. The framework is based on the new notion of an embedded functional dependency, which is independent of the interpretation of missing values, able to express completeness and integrity requirements on application data, and capable of capturing many redundant data value occurrences. We establish axiomatic and algorithmic foundations for reasoning about embedded functional dependencies. These foundations allow us to establish generalizations of Boyce-Codd and Third normal forms that do not permit any redundancy in any future application data, or minimize their redundancy across dependency-preserving decompositions, respectively. We show how to transform any given schema into application schemata that meet given completeness and integrity requirements and the conditions of the generalized normal forms. Data over those application schemata are therefore fit for purpose by design. Extensive experiments with benchmark schemata and data illustrate our framework, and the effectiveness and efficiency of our algorithms, but also provide quantified insight into database schema design trade-offs.

Data dependencies for query optimization: a survey

“…Apart from the optimizations for the operations described above, functional dependencies also support various further query-related tasks. Analogously to embedded unique constraints, embedded functional dependencies [124] can be used for query optimization under incomplete data. Cardinality estimation Query plan optimizers or other database components that estimate the cardinality of database operators often assume value independence for the different attributes and uniform value distributions 9 [75,76,109].…”

Strongly Possible Functional Dependencies for SQL

“…Finally, for database tables, lossless decomposition is an important application of FD's to eliminate redundancy and the possibilities of inconsistent updates. Köhler and Link [23] and Wei and Link [28] show how to use c-FD's or embedded FD's for that. Our future work will include a similar investigation for spFD's.…”

“…Finally, Wei and Link [28] aim to define a solid generalization of functional dependencies that do not rely on the interpretation of NULL's. Also, the two papers [23,28] studied the databases normalization based on the appropriate functional dependencies.…”

“…Finally, Wei and Link [28] aim to define a solid generalization of functional dependencies that do not rely on the interpretation of NULL's. Also, the two papers [23,28] studied the databases normalization based on the appropriate functional dependencies. It is a future research topic on how our spFD's could be applied for normalization, as normalization is important to eliminate redundancy that may cause inconsistency at updates.…”

Uniqueness Constraints for Object Stores

“…Noticeably, the keys of [32] and the mandatory PG-keys of [2] combine both existence and uniqueness constraints, while the uniqueness constraints of [42] and exclusive PG-keys of [2] separate them. The separation between existence and uniqueness constraints had already been made explicit in the context of SQL [46][47][48].…”