Unifying functional and multivalued dependencies for relational database design

Abstract

We consider the problem of unifying functional dependencies (FDs) and multivalued dependencies (MVDs) in designing relational database schemes. Given a set D of dependencies (MVDs and FDs) over a universal scheme U, we define a different set of MVDs over U, called the envelope set for D, so that a database scheme with respect to D can be designed by considering only the MVDs in the envelope set for D, instead of treating MVDs and FDs in D separately. We show that a database scheme is in 4NF with respect to D (BCNF when D has only FDs) if it is 4NF with respect to the envelope set for D. By utilizing the envelope set of dependencies we extend the conflict free property of sets of MVDs to apply to sets of FDs and MVDs. We show that if a set D of dependencies is extended conflict-free, then there exists an acyclic, joint lossless 4NF decomposition (BCNF) with respect to D which is also dependency preserving. Except for the case where D is a set of MVDs only, this was an open problem in the literature. We also show that, for a set M of MVDs, an acyclic join lossless 4NF decomposition exists if M does not split its keys. Given a set of dependencies D, obtaining the envelope set for D, determining whether D is extended conflict free, and if D is extended conflict free, then obtaining a dependency preserving, acyclic, join lossless, 4NF decomposition can be done in time polynomial in the size of D.