JMathNorm: A Database Normalization Tool Using Mathematica

Boyce–Codd normal form and object normal forms

6 - Normalization

The design of the logical structure of databases using the entity-relationship (E-R) model is considered. The goal is to focus on E-R conditions for desirable logical design, using an extended translation of the full E-R model into the more easily implementable relational database model; all entity sets in a regular E-R diagram will map to relation schemes that are in the very desirable Boyce-Codd normal form (BCNF). Conditions under which extended E-R relationship sets translate to BCNF schemes are found. These results extend to both types of weak entity sets, subset and dependent entity sets, and contribute to the logical design of robust database systems. An E-R-diagram-based design system currently under development enforces the conditions that ensure BCNF by ensuring extended regularity and other measures of well-behaved E-R models. >

Some results about normal forms for functional dependency in the relational datamodel

Several anonymization techniques, such as generalization and bucketization, have been designed for privacy preserving microdata publishing. Recently it has become major importance in research areas and numerous approaches have been proposed to preserve privacy in literature. In order to solve this issue, recent work study the problem of privacy preservation hazard for mined Conditional Functional Dependency (CFD) against (d,l) inference model using Compact frequent pattern growth branch sort algorithm (CFPGBS) methods. Boyce–Codd normal Form (BCNF) method is presented for publishing multiple subtables and it is anonymized through (d, l)- inference model. But the major problem of the work is that initial partitioning is done without considering the importance of the feature, so it contains all quasi identifier features. It becomes more complex to perform initial partitioning if the data samples becomes large,thus may also reduce the privacy results. To overcome this issue in this paper presents a hybrid feature selection method which removes unimportant quasi identifier in the final Boyce–Codd normal Form (BCNF) table. The proposed hybrid feature selection method follows the procedure of hybrid firefly algorithm with harmony search (HFAHS) algorithm to select optimal feature in the table for initial partition of top down approach. For selected features expectation Maximization (EM) based Gaussian mixture distribution (EM-GMM) based top down initial partition is done to group the partition data from the result d,l -inference model along with changed publishing data from BCNF. Experimental results show that the proposed BCNF- d,l -inference model can adeptly anonymize the microdata with less information loss when compared to existing methods against CFD. The effectiveness and privacy results of proposed methods are also compared with existing conventional methods. Our workload experiments confirm that proposed preserves better utility, privacy than d,l -inference model against CFD attack and is more effective in workloads with selection of optimal quasi identifiers.

9 - Normalization

The relational database design approach requires the process of normalization in order to minimize data redundancy and update anomalies in the relational schema. Algorithms defined in normalization theory depend upon various dependencies namely functional, multivalued, join and inclusion dependencies that should be carefully defined for a database application. Identification of these dependencies and a minimal cover is a complex and time consuming task for almost all practical problems. This work discusses how the normalization process can be eliminated from the required steps of database design. It explores various constructs of entity relationship diagram (ERD) and their transformation to relational schema. This work elaborates how un-normalized relations are created during the entity relationship (ER) model to relational schema transformation. A set of rules is presented which if followed at the stage of conceptual modeling would always generate a relational schema that satisfies normal forms up to Boyce-Codd normal form (BCNF), thus eliminating the need for normalization. The motivation behind this paper is to save the database designer's valuable time and effort otherwise required in defining dependencies, in finding a minimal cover and in normalizing a given relational schema.

The analysis of a relational database to reduce its component schemes into fourth normal form is not trivial. Decompositions into Boyce–Codd normal form is not difficult, but the concepts and analysis needed to further decompose into fourth normal form are usually difficult to comprehend and use. A diagrammatic representation of dependencies is described which provides an informal aid to the construction of relational schemes in fourth normal form.

A key is simple if it consists of a single attribute. It is shown that if a relation schema is in third normal form and every key is simple, then it is in projection-join normal form (sometimes called fifth normal form), the ultimate normal form with respect to projections and joins. Furthermore, it is shown that if a relation schema is in Boyce-Codd normal form and some key is simple, then it is in fourth normal form (but not necessarily projection-join normal form). These results give the database designer simple sufficient conditions, defined in terms of functional dependencies alone, that guarantee that the schema being designed is automatically in higher normal forms.

It is well known that faithful (i.e. dependency preserving) decompositions of relational database schemas into Boyce-Codd Normal Form (BCNF) do not always exist, depending on the set of functional dependencies given, and that the corresponding decision problem is NP-hard. The only algorithm to guarantee both faithfulness and BCNF (if possible) proposed so far in [Os79] is a brute-force approach which always requires exponential time. To be useful in practice, e.g. in automated design tools, we require more efficient means.In this paper we present an algorithm which always finds a faithful BCNF decomposition if one exists, and which is usually efficient, and exponential only in notorious cases.

In the conceptual design of relational databases one of the main goals is to create a conceptual scheme, which minimize redundancies and eliminate deletion and addition anomalies, i.e. to create relation schemes in some good normal form. The study of relational databases has produced a host of normal forms: 2NF, 3NF, BCNF, Elementary-Key Normal Form, 4NF, Weak 4NF, PJ/NF, DK/NF, LTKNF, (3,3)NF, etc. There are two features which characterize these normal forms. First, they consider each relation separately. We believe that a normal form (which reflects the goodness of the conceptual design) should be related to the whole conceptual scheme. Second, the usefullness of all normal forms in relational database design have been based on the assumption that a data definition language (DDL) of a database management system (DBMS) is able to enforce key dependencies. However, different DDLs have different capabilities in defining constraints. In this paper we will discuss the design of conceptual relational schemes in general. We will also define a good normal form (GNF) which requires a minimally rich DDL; this normal form is based only on a primitive concept of constraints. We will not, however, discuss the normalization process itself – how one might, if possible, convert a relation scheme that is not in some normal form into a collection of relation schemes each of which is in that normal form.

A new normal form, namely, object-normal form (ONF), is introduced. It is shown that the existing definitions of the fifth normal form (5NF) are unsatisfactory. The correct definition is given for the first time. The importance of the 5NF is demonstrated. For improving data representation in a database with a 5NF schema, the notion of negating relation is introduced. It serves as a basis for the new normal form, namely, the sixth normal form. Combining requirements of the ONF and other normal forms, new normal forms are defined: the fourth object-normal form, the fifth object-normal form, and the sixth object-normal form. It is shown that the standard order of steps of the normalization procedure should be changed: first, the specific requirements of the fourth normal form should be satisfied, and only then the requirements of the second normal form, and so forth.

In this paper we present a theory of relational database systems based on the partition lattice, which represents a new mathematical approach to the structure of relational database systems. A partition lattice can be defined for any given relation. This partition lattice is shown to be a meet-morphic image of the Boolean algebra of subsets of the attribute set. The partial ordering in the lattice is proved to be equivalent to the concept of functional dependency, and thus Armstrong's axioms for functional dependencies are proved. We solve the problem of finding the list of all keys by seeking the prime implicants of the Boolean function associated with the principal ideals generated by the attributes. We demonstrate the properties of the Boyce-Codd Normal Form (BCNF), and give a modified algorithm for synthesizing an information-lossless BCNF based on the principal filter. The necessary and sufficient conditions for multivalued dependency (MVD) are given in terms of a lattice equation, and the inference rules of MVD are proved. The necessary and sufficient conditions for join dependency (JD) are given; consequently, we can prove the known result that acyclic join dependency (AJD) is equivalent to a set of MVDs. The concept of data independence is introduced, and is extended to conditional independence and mutual independence. We established this algebraic theory of relational databases in the same spirit that the theory of probability was constructed. We present a comparison that demonstrates the similarities.

Database normalization is a process which eliminates redundancy, organizes data efficiently and improves data consistency. Functional, multivalued, and join dependencies (FDs, MVDs, and JDs) play fundamental roles in relational databases where they provide semantics for the data and at the same time are the foundations for database design. In this study we investigate the issue of defining functional, multivalued and join dependencies and their normal forms in XML database model. We show that, like relational databases, XML documents may contain redundant information, and this redundancy may cause update anomalies. Furthermore, such problems are caused by certain dependencies among paths in the document. Our goal is to find a way for converting an arbitrary XML Schema to a well-designed one that avoids these problems. We extend the notion of tuple for relational databases to the XML model. We show that an XML tree can be represented as a set of tree tuples. We introduce the definitions of FD, MVD, and JD and new Normal Forms of XML Schema that based on these dependencies (X-1NF, X-2NF, X-3NF, X-BCNF, X-4NF, and X-5NF). We show that our proposed normal forms are necessary and sufficient to ensure all conforming XML documents have no redundancies.

The traditional relational database model may be extended into a fuzzy database model based on the mathematical framework of fuzzy set theory to process imprecise or uncertain information. While designing such a fuzzy relational database model that does not suffer from data redundancy and anomalies, the present authors have defined several fuzzy normal forms in ref [1]. However, as one decomposes an unnormalized relation into a desired normal form, it should also satisfy the essential properties of dependency preservation and lossless join of relation schemes which take a significant role in the design theory of a relational database. We have concentrated on these two important issues in this paper and have designed algorithms that confirm dependency preservation and lossless join decomposition of an unnormalized relation into the fuzzy third normal form or fuzzy Boyce Codd normal form. The algorithms have been tested and validated with examples.