Search

International audience; Driven by the dominance of the relational model, we investigate how the requirements of applications on the certainty of functional dependencies can improve the outcomes of relational database schema design. For that purpose, we assume that tuples are assigned a degree of possibility with which they occur in a relation, and that functional dependencies are assigned a dual degree of certainty which says to which tuples they apply. A design theory is developed for functional dependencies with degrees of certainty, including efficient axiomatic and algorithmic characterizations of their implication problem. Naturally, the possibility degrees of tuples bring forward different degrees of data redundancy, caused by functional dependencies with the dual degree of certainty. Variants of the classical syntactic Boyce–Codd and Third Normal Forms are established. They are justified semantically in terms of eliminating data redundancy and update anomalies of given degrees, and minimizing data redundancy of given degrees across all dependency-preserving decompositions, respectively. As a practical outcome of our results, designers can simply fix the degree of certainty they target, and then apply classical decomposition and synthesis to the set of functional dependencies whose associated degree of certainty meets the target. Hence, by fixing the certainty degree a designer controls which integrity requirements will be enforced for the application and which data will be processed by the application. The choice of the certainty degree also balances the classical trade-off between query and update efficiency on future database instances. Our experiments confirm the effectiveness of our control parameter, and provide original insight into classical normalization strategies and their implementations.

A new approach, to measure normalization completeness for conceptual model, is introduced using quantitative fuzzy functionality in this paper. We measure the normalization completeness of the conceptual model in two steps. In the first step, different normalization techniques are analyzed up to Boyce Codd Normal Form (BCNF) to find the current normal form of the relation. In the second step, fuzzy membership values are used to scale the normal form between 0 and 1. Case studies to explain schema transformation rules and measurements. Normalization completeness is measured by considering completeness attributes, preventing attributes of the functional dependencies and total number of attributes such as if the functional dependency is non-preventing then the attributes of that functional dependency are completeness attributes. The attributes of functional dependency which prevent to go to the next normal form are called preventing attributes., Comment: 8 Pages

Introduction Designing database is an art process similar to building a house. Database designers always face the problems of designing a relational database that will be free of database anomalies. These anomalies bring repetition of tuples that delay processing time and occupy memory spaces. Suppose that the value of the attribute BUILDER determines values of the attribute MODEL and PRICE, (BUILDER [right arrow] MODEL, PRICE) and that the value for the attribute MODEL determines the value for PRICE, (MODEL [right arrow] PRICE). Grouping these attributes in relation HOUSE(BUILDER, MODEL, PRICE) has several undesirable properties. First the relationship between MODEL and PRICE is repeated in the relation for each BUILDER who builds a particular MODEL of home. This repetition creates difficulties if a BUILDER who happens to be the last BUILDER of a certain MODEL home is deleted from the relation, then the relationship between the MODEL and its PRICE also disappears from the relation. This is called a deletion anomaly. Similarly, if a new builder who happens to be the first BUILDER of a certain MODEL home is added then the relationship between MODEL of a home and its PRICE will also be added. This is called an insertion anomaly. Suppose that the relationship between a MODEL and its PRICE is changed e.g. the price is increased; then the MODEL and PRICE relationship should be affected for every BUILDER of the MODEL. This is called update anomaly. These anomalies are undesirable since the user is not likely to realize the consequence of the insertion, deletion or updating. The user may inadvertently affect a relationship that was not intended to be modified. Consistency, insertion, deletion and updating are not probe effecting all groupings of attributes. If the relation HOUSE(BUILDER, MODEL, PRICE) is normalized then the consistency and anomaly problems disappear. Normalization is a step by step reversible process of replacing a given collection of relations by successive collection in which the relations have a progressively simpler and more regular structure (Date & Darwen, 2000). The reversibility guarantees that the original collection of relations can be recovered and therefore no information has been lost. Codd proposed three normal forms which he called first normal form (1NF), second normal form (2NF) and third normal form (3NF). A stronger definition of 3NF was proposed by Boyce and Codd and is known as Boyce-Codd Normal Form (BCNF). All these normal forms except 1NF are based on the functional dependencies among the attributes of a relation (Elmasri & Navathe, 1994). First normal form relates to the structure of the relation. It requires that every attribute of a relation be based on a simple domain. The database designers have no problem to know if a relation violates first normal form. They can put the relation into first normal form algorithmically by replacing a non-simple domain by its constituent simple domains. In the second (2NF), third (3NF) and Boyce Codd normal form (BCNF), there is a need for the database designers to know the real meaning and application of database keys such as candidate key, primary key, super key, etc,. Problem Statement Database designers always find it difficult to determine these keys from relational database schemas. It has been difficult to motivate students and database designers to derive primary, candidate, alternative and super keys because they think this area is dry and theoretical. There are many algorithms to determine the database keys but they look abstract for students. Many database researchers indicated that relational database model to derive database keys tends to be complex for the average designers. Failure to determine the database keys at times leads to poor design that can generate database anomalies. The database key algorithms often require extensive relational algebraic backgrounds that database designers lack. …

This paper discusses normalization of relations when the candidate keys of a relation have missing information represented by nulls. The paper shows that when the missing information is of the type “not applicable” or “does not exist,” problems and confusion can arise in normalizing relations. Candidate keys with missing information commonly are found in relations that represent information on two entities with a one-to-one relationship between them. The current definition of Boyce-Codd Normal Form (BCNF) is ineffective in identifying poor designs in such relations that may have insertion/deletion anomalies. It is shown that the above problem can be corrected by incorporating the concept of entity integrity rule into the definition of BCNF. This paper also shows that incorporating the entity integrity rule into the definition of either a relation or a candidate key does not provide a satisfactory solution to the problem.

The issue of providing a formal justification for the use of fourth normal form (4NF) in relational database design is investigated. The motivation and formal definitions for three goals of database design are presented. These goals are the elimination of: redundancy, key-based update anomalies and fact-based replacement anomalies. It is then shown that, depending on the type of constraints permitted, either Boyce-Codd normal form (BCNF) or 4NF are the exact conditions needed to ensure most of the design goals. However, it is also shown that the conditions required to ensure the absence of a particular class of key-based update anomaly are new normal forms which have not previously been identified. In particular, for the case where the only constraints are functional dependencies (FDs), it is shown that the required normal form is a new normal form that is stronger than third normal form (3NF) yet weaker than BCNF. Similarly, in the more general case where both FD and multivalued dependencies (MVDs) are present, the required normal form is a new normal form that is weaker than 4NF.

In this paper, the adequacy of fifth normal form (5NF) in relational database design is investigated. It is shown that 5NF is inadequate because it does not generalise fourth normal form (4NF) and because it is equivalent to the very stringent requirement that every attribute is a key, a requirement that is effectively impossible to satisfy in practical database design. By restricting the join dependencies (JDs) in the set of constraints to those that do not have superfluous components, the definition of 5NF is then changed to a new normal form, called reduced fifth normal form (5NFR), and it is shown that 5NFR generalises 4NF. It is also shown that 5NFR is a strictly weaker condition than projection-join normal form (PJ/NF), the other normal form that has been proposed for JDs.

In this paper we present some characterizations of relation schemes in second normal form (2NF), third normal form (3NF) and Boyce-Codd normal form (BCNF). It is known [6]that the set of minimal keys of a relation scheme is a Sperner system (an antichain) and for an arbitrary Sperner system there exists a relation scheme the set of minimal keys of which is exactly the given Sperner system. We investigate families of 2NF, 3NF and BCNF relation schemes where the sets of minimal keys are given Sperner systems. We give characterizations of these families. The minimal Armstrong relation has been investigated in the literature [3, 7, 11, 15, 18]. This paper gives new bounds on the size of minimal Armstrong relations for relation schemes. We show that given a relation scheme s such that the set of minimal keys is the Sperner system K, the number of antikeys (maximal nonkeys) of K is polynomial in the number of attributes iff so is the size of minimal Armstrong relation of s. We give a new characterization of relations and relation schemes that are uniquely determined by their minimal keys. From this characterization we give a polynomial-time algorithm deciding whether an arbitrary relation is uniquely determined by its set of all minimal keys. We present a new polynomial-time algorithm testing BCNF property of a given relation scheme.

Boyce-Codd Normal Form (BCNF) is an important normal form in the theory of relational databases. Unfortunately, it is not always possible to obtain a lossless functional-dependency-preserving BCNF decomposition. (This decomposition is called a perfect BCNF decomposition for short.) The necessary condition of the existence of a perfect BCNF decomposition is discussed. Three propositions about a perfect BCNF decomposition are obtained. The concrete determining algorithms are given.

A functional dependency (fd) family was recently defined [20] as the set of all instances satisfying some set of functional dependencies. A Boyce-Codd normal form, abbreviated BCNF, family is defined here as an fd-family specified by some BCNF set of functional dependencies. The purpose of this paper is to present set-theoretic/algebraic characterizations relating to both types of families. Two characterizations of F ( I ), the smallest fd-family containing the family I of instances, are established. The first involves the notion of agreement, a concept related to that of a closed set of attributes. The second describes F ( I ) as the smallest family of instances containing I and closed under four specific operations on instances. Companion results are also given for BCNF- families. The remaining results concern characterizations involving the well-known operations of projection, join and union. Two characterizations for when the projection of an fd-family is again an fd-family are given. Several corollaries are obtained, including the effective decidability of whether a projection of an fd-family is an fd-family. The problem for BCNF-families disappears since it is shown that the projection of a BCNF-family is always a BCNF-family. Analogous to results for fd-families presented in [20], characterizations of when the join and union of BCNF-families are BCNF-families are given. Finally, the collections of all fd-families and all BCNF-families are characterized in terms of inverse projection operations and intersection.

An algorithm is given for the lossless decomposition of a relation into Boyce-Codd Normal Form.