Functional Dependencies are fundamental to the process of normalization in database design. They play a key role in distinguishing good database designs from bad ones. A functional dependency is a type of constraint that generalizes the notion of a key.
A functional dependency describes the relationship between attributes (columns) in a table. It is represented by an arrow sign (→).
In other words, a dependency FD: X→Y means that the values of Y are determined by the values of X. Two tuples sharing the same values of X will necessarily have the same values of Y. An attribute on the left-hand side is known as the "Determinant". Here, X is a Determinant.
The term Armstrong Axioms refers to the sound and complete set of inference rules or axioms introduced by William W. Armstrong, used to test the logical implication of functional dependencies. Armstrong Axioms define the set of rules for reasoning about functional dependencies and inferring all the functional dependencies in a relational database.
The closure of a set of attributes can be defined as the set of attributes that can be functionally determined from it. The set of functional dependencies that is logically implied by a set F is called the closure of F and is written as F*. It is defined as: "If F is a set of functional dependencies on a relation R, then F+ is the closure of F by using the inference axioms."
Example: R (A, B, C, D) and set of Functional Dependencies are A→B, B→D, C→B then what is the Closure of A, B, C, D?
Solution: A* is
A* → {A, B, D} i.e., A→B, B→D is exists and C is not FD on A. So it is eliminated.
B* → {B, D} i.e., B→D is exists and A, C is not FD on A. So it is eliminated.
C* → {C, B, D} i.e., C→B, B→D is exists and A is not FD on C. So it is eliminated.
A functional dependency is said to be a full dependency if and only if the determinant of the functional dependency is either a candidate key or a super key, and the dependent can be either a prime or non-prime attribute.
Explanation: Let’s take the functional dependency X →Y (i.e., X determines Y). Here, Y is said to be fully dependent if it cannot determine any subset of X.
Example: Consider the following determinant ABC→D. Here, ABC determines D, but D is not determined by any subset of A, B, C, or AB, BC, AC. So, D is fully functionally dependent on ABC.
If a non-prime attribute of the relation is derived by only a part of the candidate key, then such a dependency is known as a partial dependency.
Explanation: In a relation having more than one key field, a subset of non-key fields may depend on all key fields, but another subset or a particular non-key field may depend on only one of the key fields. Such a dependency is defined as a partial dependency.
Example: Consider the determinants AC→P, A→D , D→P , D→P. From these determinants, P is not fully functionally dependent on AC. If we find A+ (i.e., A’s closure), A→D, D→P, i.e., A→P. But we don’t have any requirement of C. The C attribute is removed completely. So, P is partially dependent on AC.
Conditions Where a Table Cannot Have Partial Functional Dependency:
1. If the primary key consists of a single attribute.
2. If the table consists of only two attributes.
3. If all the attributes in the table are part of the primary key.
If a non-prime attribute of a relation is derived by either another non-prime attribute or a combination of part of the candidate key along with a non-prime attribute, then such a dependency is defined as a transitive dependency. In a relation, there may be a dependency among non-key fields. Such a dependency is called a transitive functional dependency.
Example: If X→Y and Y→Z , then we can determine that X→Z holds.
Conditions Where a Table Cannot Have Transitive Functional Dependency:
1. If the table consists of only two attributes.
2. If all the attributes in the table are part of the primary key.
It is basically related to the reflexive rule. If X is a set of attributes, and Y is a subset of X, then X→Y holds.
Example: ABC→BC is a trivial dependency.
Consider three fields X, Y, and Z in a relation. If for each value of X, there is a well-defined set of values for Y and a well-defined set of values for Z, and the set of values for Y is independent of the set of values for Z, this dependency is a multi-valued dependency, i.e., X→Y and X→Z.
These types of functional dependencies help in identifying and organizing relationships between attributes in a database, ensuring a more structured and efficient database design.
Prime Attributes: Attributes that are part of any candidate key of a relation are called prime attributes.
Non-Prime Attributes: Attributes that are not part of any candidate key are called non-prime attributes.
A candidate key is a minimal set of attributes of a relation that can be used to identify a tuple uniquely.
Example: Consider the student table: student(s_no, s_name, s_phone, age).
Here, s_no can be a candidate key. We can have more than one candidate key in a table.
Types of Candidate Keys:1. Simple Candidate Key: Consists of only one attribute.
2. Composite Candidate Key: Consists of multiple attributes.
A super key is a set of attributes of a relation that can be used to identify a tuple uniquely.
⇒ Adding zero or more attributes to a candidate key generates a super key.
⇒ A candidate key is a super key, but the reverse is not true.
Example: Consider the student table: student(s_no, s_name, s_phone, age). Here, s_no and (s_no, s_name) can be super keys.
These concepts help in understanding the uniqueness constraints within a database, ensuring that each record can be uniquely identified, which is crucial for maintaining data integrity and avoiding redundancy
To check if any functional dependency like A → B can be determined from F1, compute A+ from F1. If A+ includes B, then A → B can be derived as a functional dependency in F1.
A key attribute is an attribute capable of identifying all other attributes in a given table.
⇒ Primary Key: It is a unique value attribute in a table used to enforce entity integrity and to identify rows uniquely.
⇒ Composite Primary Key: Sometimes, a single attribute is not sufficient to identify rows uniquely, so we combine two or more attributes to identify the rows uniquely.
⇒ Candidate Keys: Sometimes, two or more independent attributes can be used to identify the rows uniquely. For example, (vehicle number, engine number, purchase date). Either vehicle number or engine number can be used as a key attribute, then they are called candidate keys. One of the candidate keys can be elected as the primary key.
Different database designers may define different sets of functional dependencies from the same requirements. To evaluate whether they are equivalent, we must be able to derive all functional dependencies in G from F and vice versa.
