Mining functional dependency from relational databases using equivalent classes and minimal cover

Data Mining (DM) represents the process of extracting interesting and previously unknown knowledge from data. This study proposes a new algorithm called FD_Discover for discovering Functional Dependencies (FDs) from databases. This algorithm employs some concepts from relational databases design theory specifically the concepts of equivalences and the minimal cover. It has resulted in large improvement in performance in comparison with a recent and similar algorithm called FD_MINE.


INRODUCTION
FDs are relationships (constraints) between the attributes of a database relation; a FD states that the value of some attributes are uniquely determined by the values of some other attributes. Discovering FDs from databases is useful for reverse engineering of legacy systems for which the design information has been lost. Furthermore, discovering FDs can also help a database designer to decompose a relational schema into several relations through the normalization process to get rid or eliminate some of the problems of unsatisfactory database design. The identification of these dependencies that are satisfied by a database instance is an important topic in data mining literature [5] . The problem of generating or discovering FDs from relational databases had been studied in [3,4,7,8,11,12] . A straight forward solution algorithm is shown to require exponential time of all inputs (number of attributes and number of tuples in a relation).
In this study, we are proposing a new algorithm for discovering FDs from static databases. This algorithm will use and employ some concepts from relational database theory, such as the theory of equivalencies and minimal cover of FDs. The proposed algorithm aims at minimizing the time requirements of algorithms that discover FDs from databases. We will compare the result of our proposed algorithm with a previous well known algorithm called FD_MINE [12] .
Some of the previous studies in discovering FDs from databases presented in [2,4,8] have focused on discovering embedded parallel query execution, optimizing queries, providing some kind of summaries over large data sets or discovering association rules in stream data.
Other studies presented in [3,7,9,11,12] have presented various methods for discovering FDs from large databases, these studies have focused on discovering FDs from very large databases and had faced a general problem which is represented by the exponential time requirements that depend on database size (the dimensionality problem in number of tuples and attributes).
Functional dependencies: An FD, denoted by X→Y, is constraint between two sets of attributes X and Y that are subsets of some relation schema R. It specifies a constraint on all possible tuples t 1 and t 2 in R such that if t 1 [X] = t 2 [X], then they must also have t 1 This means that the value of the Y component in any tuple in R depend on or are determined by the value of the X component or alternatively the value of the X component of any tuple uniquely (or functionally) determines the value of the Y component.

Definition 1:
If A is an attribute or set of attributes in relation R, all the attributes in R that are functionally dependent on A in relation R with respect to F (where F is a set of FDs that holds on R), form the closure of A and it is denoted by A + or Closure(A).

Definition 2:
The nontrivial closure of attribute A with respect to F that is denoted by Closure→(A), is defined as Closure→(A) = Closure (A)-A.

Definition 3:
The closure of a set F of FDs is the set of all FDs that are logically implied by F and it is denoted by F + .
Minimal cover: Minimal FDs or minimal cover is a useful concept in which all unnecessary FDs are eliminated from F. The concept of minimal cover of F is sometimes called Irreducible Set of FDs. The minimal cover of FDs of F is denoted by F c .
To find the minimal cover of a set of FDs of F, we transform F such that for each one of its FDs that has more than one attribute in the right hand side is reduced to a set of FDs that have only one attribute on the right hand side. The minimal cover F c is then a set of FDs such that:

Functional dependencies and equivalent classes:
To discover a set of FDs that are satisfied by a relation instance, we use the partition method that divides the tuples of this instance into groups based on the different values of each column (attribute). For each attribute, the number of groups is equal to the number of different values for that attribute. Each group is called an equivalent class [10] . For instance, given the following relation instance as shown in Table 1  Next, we test each pair of equivalent classes of each pair of attributes if they are same or not. For each pair of equivalent classes that are the same, we conclude that their corresponding attributes are equivalent and each attribute is functionally determined Table1: Binary relation instance  Tuple ID  A  B  C  D  E  t1  0  1  0  1  0  t2  0  1  0  1  0  t3 1 Table 1 by the other. For instance, since the equivalent classes for attribute A = the equivalent classes for attribute D, we can conclude that attribute A is equivalent to attribute D and consequently: (A↔D, A→D, D→A). Furthermore, A can be added to the closure of D and D can be added to the closure of A. This process is repeated for all attributes and for all of their combinations (candidate set). For instance, given a relation with five attributes (A, B, C, D, E) the candidate set is {φ, A, B, C, D, E, AB, AC, AD, AE, BC, BD, DE, CD, CE, DE, ABC, ABD, ABE, ACD, ACE, ADE, BCD, BCE, BDE, CDE, ABCD, ABCE, ABDE, ACDE, BCDE, ABCDE} for a total of 32 (i.e., 2 5 ) combinations. These candidate attributes of this relation are represented as a lattice as shown in Fig. 1.

Fig. 1: Lattice for the attributes of the relation in
Each node in Fig. 1 represents a candidate attributes. An edge between any two nodes such as E and DE indicates that the FD: DE→D, needs to be checked. Hence, discovering FDs from very large databases with large number of attributes may require an exponential time [6,8] .
FD_MINE algorithm: FD-Mine algorithm uses the theory of FDs to reduce both the size of the dataset and the number of FDs to be checked through the discovered equivalences. Figure 2 shows the FD_MINE algorithm. For more detail of this algorithm refer to [12] .

FD_M INE algorithm
To discover all functional dependencies in a dataset. Input: Dataset D and its attributes X 1 , X 2 , ... , X m Output: FD_SET, EQ_SET and KEY_SET The main body of the FD_MINE algorithm has a loop that iterates n times, where n is the cardinality of the candidate set in the given database. Therefore, this main body has a time complexity of n. Within each iteration of the above loop, there is a call for each of the following: • A loop that iterates n times over all attributes in the Candidate_Set within each iteration of this loop there is call for each of the following procedures: • ComputeNonTrivialClosure(), this procedure iterates n times • ObtainFDandKey(), this procedure iterates n times. For a total time of n (n+n)= 2 n 2 • ObtainEQSet(), this procedure performs two nested loops each with n iterations for a total time of n 2 . • PruneCandidates(CANDIDATE_SET. this procedure performs one loop with n iteration. • GenerateCandidates(CANDIDATE_SET) this procedure performs two nested loops each with n iterations for a total time of n 2 .
Therefore, the total time required by the FD_MINE algorithm is: T (n) = n (2 n 2 +n 2 +n+n 2 ) = 4n 3 +n 2 Suggested work: In this study, we suggest an algorithm that discovers all FDs from databases that  The suggested algorithm called FD_Discover and it will incorporate the following concepts.
• An incremental minimal (Canonical) cover computation during each phase of discovering FDs to minimize the number of FDs to be checked • During each phase of the algorithm and for each attribute, we compute its nontrivial closure attributes. For each equivalent pair of attribute closures, we remove one of them from the candidate set of attributes. Also, we add the fact that these two attributes are equivalent (↔). This will reduce the number of attributes to be checked during each phase of the algorithm

FD_Discover algorithm:
To introduce our FD_Discover algorithm the following terms should be defined: EQ_Set = Set of discovered equivalent set in form: X ↔ Y.
Candidate_Se t= attributes over a dataset to be checked (Relation Attributes).
X i : One of the attributes in candidate_set. Figure 3 shwos the main procedure of the FD_Discover algorithm.
The main procedure of the FD_Discover algorithm calls the ComputeMinimalNontrivialFD(X i ) that is shown in Fig. 4. This procedure computes for each  attribute its nontrivial closure set. For each attribute X i if Y in its closure then add X i →Y to the FD_Set and add X i Y to TmpList. Furthermore, this procedure checks each pair of attributes whether their closures are equivalent or not. For each equivalent pair remove one of the attributes from the candidate set of attributes. Figure 5 shows the GenerateNextLevelCandidates procedure. Table 1, the following steps show how our suggested algorithm can be applied on this relation only for the first level. (C) has one attribute in its closure set {B}, for each attribute in this set, this procedure first adds the fact that attribute C functionally determines each attribute in its closure set to FD_Set (i.e add C→B to FD_Set. Therefore FD_Set = {A→B, A→D, C→B}). Next this procedure checks whether attribute C is equivalent to attribute B or not. This procedure finds out that attribute B is not equivalent to attribute C Time coplexity of FD_Discover algorithm: The main body of the FD_Discover algorithm has a loop that iterates n times, where n is the cardinality of the candidate set, in the given database. Therefore, this main body has a time complexity of n. Within each iteration of the above loop, there is a call for each of the following procedures:

Example: Given the database shown in
• ComputeMinimalNontrivialFD(), this procedure is called n times each call of this procedure takes n iterations, for a total time of n 2 • GeneratNextLevelCandidates(Candidate_Set) this procedure performs two nested loops, each with n iteration for a total time of n 2 Therefore, the total time required by the FD_Discover algorithm is: T(n) = n(n 2 +n 2 )) = 2n 3   Table 2 shows the results of the actual times (on a PC with speed of 2.3 GHz) required for FD_MINE algorithm and FD_Discover algorithm for different UCI datasets [1] with varying number of attributes for discovering all FDs at all levels.

RESULTS AND DISCUSSION
In Table 2 Bridge and Echocardiogram datasets have the same number of attributes and a similar number of tuples but the time required for Echocardiogram database is much less than the time required for Bridge database. This had happened because they have different data and Echocardiogram has more equivalent attributes than Bridge so the number of checks is less and the time required is also less. From these results we can notice that our FD_Discover algorithm needs less time than FD_MINE by a factor of almost 5. Table 3 shows the time complexity comparison based on T(n) that are computed earlier for FD_Discover algorithm and for FD_MINE algorithm.
The FD_Discover algorithm reduces the number of FDs to be checked and produces fewer FDs than FD_MINE algorithm (the FDs that are discovered by the FD-Discover algorithm are equivalent to those FDs that are discovered by the FD_MINE algorithm).  Therefore, the Semi-lattice for FD-Discover algorithm will be smaller than the semi-lattice of FD-MINE algorithm. For instance, during the discovering process of the FDs from the database given in Table 1, Fig. 6a shows the semi-lattice of checked FDs using FD_Discover algorithm and Fig. 6b shows the semilattice of checked FDs using FD_MINE algorithm. It is noticed that the semi-lattice for FD_Discover algorithm has fewer edges than the semi-lattice for FD_MINE algorithm.

CONCLUSION
We have suggested a new algorithm (FD_Discover) to discover FDs which utilizes the concepts of equivalent properties and minimal (Canonical) cover of FDs.
The aim of this algorithm is to optimize the time requirements when compared with a previous algorithm called FD_MINE. The analyses of the FD_Discover algorithm had a better performance of a factor of 5 over the FD_MINE algorithm.
Furthermore, simulation results for both algorithms have shown similar results. In FD_Discover algorithm there is no need to check all attributes to discover functional dependency as a result of applying the equivalent properties. However, in FD_MINE algorithm all attributes must be checked. In FD_Discover algorithm only one procedure (Procedure ComputeMinimalNontrivialFD) is needed to discover immediately FD_Set, EQ_Set and pruning Candidate set for next level, whereas FD_MINE algorithm needs three procedures one to discover FD_Set (ObtainFDandKey), another to discover EQ_Set (ObtainEQSet) and PruneCandidates and GenerateNextLevelCandidate to prune Candidate set for next level. This leads to higher complexity in number of nested loops and time required.