Finding Extraneous Attributes in DBMS / How to find extraneous attributes in the process of Normalization? / Finding Extraneous Attributes Examples / Extraneous attribute finding as part of finding Canonical cover
Extraneous Attribute
If we are able to remove an
attribute from a functional dependency without changing the closure of the set
of functional dependencies, that attribute is called as Extraneous Attribute.
[Dictionary
meaning of ‘Extraneous’ is ‘irrelevant’, ‘inappropriate’, or ‘unconnected’]
Assume a set of functional
dependencies F, and the closure of set of functional dependencies F+.
Also, assume that we remove an attribute from any of the FDs under F and find
the closure of new set of functional dependencies. Let us mention the new
closure of set of functional dependencies as F1+. If F+ equals the newly constituted
closure F1+, then the attribute which has been removed is called as
Extraneous Attribute. In other words, that attribute does not violate any of
the functional dependencies.
Example 1:
Let us consider a relation R with
schema R(A, B, C) and set of functional dependencies F = { AB → C,
A →
C
}. The closure for F is F+ = { AB → C,
A →
C
}.
In AB → C,
B is extraneous attribute. The reason is, there is another FD A → C,
which means when A alone can determine C, the use of B
is unnecessary (redundant).
Now, we can find the closure for
the new set of functional dependencies, which is same as F+. Hence,
we can declare that B is extraneous.
Example 2:
Let us consider a relation R with
schema R(A, B, C, D) and a set of functional dependencies F = { A → BC, B → C,
AB →
D
}. What extraneous attributes are present in FDs of F?
C
is extraneous in the RHS (Right Hand Side) of A
→ BC. Because, A can determine B (A → BC), B can determine C (B → C). Hence, A can determine C also
(Transitivity rule). Hence, it is inappropriate to repeat or check an attribute
many times.
B
is extraneous in the LHS of AB → D. The reason is, from A
→ BC, it is clear that A determines B. it would
indirectly mean that if you know A and B then you know D also.
Formal definition of Extraneous Attribute
In a
set of functional dependencies F, consider a functional dependency α → β.
Attribute A is
extraneous in α, if A ∈ α, and F logically implies (F − {α → β}) ∪ {(α − A) → β}.
Attribute A is
extraneous in β, if A ∈ β, and the set of functional dependencies
(F − {α →β}) ∪ {α → (β − A)} logically implies F.
For example, suppose F contains
AB → CD, A → E, and E → C. To check if
C is extraneous in AB → CD, we compute the attribute closure of AB under F’
= {AB → D, A → E, and E → C}. The closure is ABCDE, which includes
CD, so
we infer that C is extraneous.
[Source:
Database System Concepts – Korth]
Go to How to find closure of set of functional dependencies? page
Go to Normalization - Solved Exercises page