JMathNorm-Database Normalization Tool Using Mathematica

This paper is about designing a complete interactive tool, named JMathNorm, for relational database (RDB) normalization using Mathematica. It is an extension of the prototype developed by the same authors [1] with the inclusion of Second Normal Form (2NF), and Boyce-Codd Normal Form (BCNF) in addition to the existing Third normal Form (3NF) module. The tool developed in this study is complete and can be used for real-time database design as well as an aid in teaching fundamental concepts of DB normalization to students with limited mathematical background. JMathNorm also supports interactive use of modules for experimenting the fundamental set operations such as closure, and full closure together with modules to obtain the minimal cover of the functional dependency set and testing an attribute for a candidate key. JMathNorm’s GUI interface is written in Java and utilizes Mathematica’s JLink facility to drive the Mathematica kernel.

Design of a RDB system consists of four main phases, namely, (i) determination of user requirements, (ii) conceptual design, (ii) logical design, and finally, (iv) physical design [2]. During the conceptual design phase, set of business rules is transformed into a set of entities with a set attributes and relationships among them. Extended Entity Relationship (EER) modeling tool can be utilized for the graphical representation of this transformation. The entity set in the EER model is then mapped into a set of relation schemas {R1, R2, R3, …, Rn} where each Ri represents one of the relations of the DB schema. A temporary primary key is designated, and a set of functional dependencies (FD’s) among the attributes of each schema are established as an outcome of this phase. As a side product of the logical design phase, each Ri is transformed into well-formed groupings such that one fact in one group is connected to other facts in other groups through relationships [3]. The ultimate aim of this article is to perform this rather mechanical transformation process, called normalization, efficiently in an automatic fashion.

c Springer-Verlag Berlin Heidelberg 2007JMathNorm: A Database Normalization Tool Using Mathematica 187 Commercial DB design tools do not provide a complete solution for automatic normalization and existing normalization tools for the purpose require high level programming skills and complex data structures. Two such implementations in Prolog language are discussed in [4,5]. Another study on automatic transformation is given in [6] in which UML is used to access Object Constraint Language (OCL) to construct expressions that encode FD’s using classes at a meta-level. An alternative approach to normalization is given in [3] that focus on addressing FD’s to normalize DB schema in place of relying on the formal definitions of normal forms. The impact of this method on IS/IT students perceptions is also measured in the same study. It appears that this approach is only useful for small sets of FD’s in a classroom environment and in particular not suited for automatic normalization. A web-based tool for automatic normalization is given in [7] which can normalize a DB schema up to 3NF for a maximum of 10 FD’s only.
This article is an extension of the work [1] and discusses a complete normalization tool called JMathNorm which implements 2NF, 3NF, and BCNF using the
abstract algorithms found in the literature . JMathNorm’s normalization
modules are written in Mathematica [8] using the basic list/set operations, the
user interface is designed using Java language, and finally, execution of Mathematica modules is accomplished by employing Mathematica’s Java Link (JLink)
utility. The design approach in this study is similar to the Micro tool given in
[9]. However, JMathNorm provides additional aspects for educational purposes
and is implemented efficiently without using any complex data structures such
as pointers.
The remainder of this article is organized as follows. Section 2 briefly reviews
the DB normalization and some of the basic functions used in normalization
algorithms. In Section 3 Mathematica implementation of BCNF algorithm is
given. JMathNorm tool is demonstrated in Section 4. Remarks about the tool
and discussion for future work are provided in the final section.
2 A Discussion on Normalization Algorithms
A functional dependency (FD) is a constraint about sets of attributes of a relation Ri
in the DB schema. A FD between two sets of attributes X and Y,
denoted by, X → Y specifies that there exists at most one value of Y for every
value of X (determinant)[2,10,11]. In this case, one asserts that X determines Y
or Y is functionally dependent on X.
For example, f or a DB schema PURCHASE-ITEM = {orderNo, partNo,
partDescription, quantity, price}, with P K = {orderNo, partNo}, using a set of
business rule one can specifiy the following FD’s:

Free download research paper