What are pitfalls in relational database design. with a suitable example, explain the role of functional dependency in the process of normalisation?
Answers
Answered by
3
In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection {\displaystyle \pi _{X,Y}R} is a function, i.e. Y is a function of X.[1][2] In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set. A functional dependency FD: X → Y is called trivial if Y is a subset of X.
In other words, a dependency FD: X → Ymeans 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.
The determination of functional dependencies is an important part of designing databases in the relational model, and in database normalization and denormalization. A simple application of functional dependencies is Heath's theorem; it says that a relation R over an attribute set U and satisfying a functional dependency X → Y can be safely split in two relations having the lossless-join decomposition property, namely into {\displaystyle \pi _{XY}(R)\bowtie \pi _{XZ}(R)=R} where Z = U − XYare the rest of the attributes. (Unions of attribute sets are customarily denoted by mere juxtapositions in database theory.) An important notion in this context is a candidate key, defined as a minimal set of attributes that functionally determine all of the attributes in a relation. The functional dependencies, along with the attribute domains, are selected so as to generate constraints that would exclude as much data inappropriate to the user domainfrom the system as possible.
A notion of logical implication is defined for functional dependencies in the following way: a set of functional dependencies {\displaystyle \Sigma } logically implies another set of dependencies {\displaystyle \Gamma }, if any relation R satisfying all dependencies from {\displaystyle \Sigma }also satisfies all dependencies from {\displaystyle \Gamma }; this is usually written {\displaystyle \Sigma \models \Gamma }. The notion of logical implication for functional dependencies admits a sound and complete finite axiomatization, known as Armstrong's axioms.
Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection {\displaystyle \pi _{X,Y}R} is a function, i.e. Y is a function of X.[1][2] In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set. A functional dependency FD: X → Y is called trivial if Y is a subset of X.
In other words, a dependency FD: X → Ymeans 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.
The determination of functional dependencies is an important part of designing databases in the relational model, and in database normalization and denormalization. A simple application of functional dependencies is Heath's theorem; it says that a relation R over an attribute set U and satisfying a functional dependency X → Y can be safely split in two relations having the lossless-join decomposition property, namely into {\displaystyle \pi _{XY}(R)\bowtie \pi _{XZ}(R)=R} where Z = U − XYare the rest of the attributes. (Unions of attribute sets are customarily denoted by mere juxtapositions in database theory.) An important notion in this context is a candidate key, defined as a minimal set of attributes that functionally determine all of the attributes in a relation. The functional dependencies, along with the attribute domains, are selected so as to generate constraints that would exclude as much data inappropriate to the user domainfrom the system as possible.
A notion of logical implication is defined for functional dependencies in the following way: a set of functional dependencies {\displaystyle \Sigma } logically implies another set of dependencies {\displaystyle \Gamma }, if any relation R satisfying all dependencies from {\displaystyle \Sigma }also satisfies all dependencies from {\displaystyle \Gamma }; this is usually written {\displaystyle \Sigma \models \Gamma }. The notion of logical implication for functional dependencies admits a sound and complete finite axiomatization, known as Armstrong's axioms.
Similar questions