| Lesson 8 | What does it mean when columns are functionally dependent? |
| Objective | Define functional dependencies. |
Define Functional Dependencies
Question: What role do functional dependencies play in the normalization process?
Functional dependencies play a critical role in the normalization process, as they help to identify the relationships between the columns in a table. Functional dependencies describe the relationship between two columns, where the value of one column determines the value of another column.
By analyzing the functional dependencies in a table, we can identify columns that are dependent on only a part of the primary key, as well as columns that are dependent on other non-key columns. These dependencies can result in data redundancy, inconsistency, and poor performance, which can be addressed by normalizing the table.
The normalization process involves dividing the data into smaller, more manageable tables, based on their functional dependencies. This ensures that each table contains only related data and avoids data redundancy, inconsistency, and other issues that can occur when data is stored in a non-normalized way.
Functional dependencies play a critical role in determining the appropriate structure for a database, as they help to ensure data integrity and efficiency. By normalizing a database based on its functional dependencies, we can ensure that it is well-structured, easy to use, and efficient.
In order for a relation to be in 2NF, the technical definition states that every non-key attribute must be functionally dependent on every column in the primary key. Let us begin by defining functional dependencies.
Consider the following relation:
Customer (CustID, CustFirst, CustLast, Address, Phone)
In this relation, CustID is the primary key, which means that every record in the table will contain a unique CustID value.
It also means that a Customer relation's CustID field
determines the values of the other fields in the relation.
In other words, the CustFirst, CustLast, Address, and Phone attributes are
functionally dependent on the contents of the CustID field. The field(s) that determine the values of other fields in a relation is known as the relation's
determinant[1] .
A determinant is always a
candidate key[2] for the relation.
Functional Dependency Direction
Since more than one customer can have the same name, live at the same address, and have the same phone number,
the functional dependency does not work in the opposite direction. In other words, the CustID field is not functionally dependent on the CustFirst, CustLast, Address, and Phone fields.
Functional Dependency
An attribute
A is functionally dependent on a "set of attributes X" if and only if
a "Value of X" uniquely determines a value of
A.
A functional dependency says:
If you give me the value of the primary key, that will give me the rest of the fields in the relationship.
All the fields are dependent on the
primary key[3].
Using a biological example, the mother is functionally dependent on the child.
Functional dependency is a 1 to 1 relationship. For every child, there is only one mother. That is why the relationship goes from child to mother.
It is denoting a 1 to 1 relationship where the fields ("set of attributes X") are dependent on the primary key.
The primary key is the determinant.
X --> A
child --> mother
mother --> child
- The value of child implies the value of mother
- Value of mother does NOT imply value of child
- Child is the determinant (For every child, there is only one mother)
Dependency and Determinants
- Functional dependency: Z is functionally dependent on X if the value of Y is determined by X.
In other words, if Y = X +1, the value of X will determine the resultant value of Y. Thus, Y is dependent on X as a function of the value of X.
- Transitive dependence: Z is transitively dependent on X when X determines Y and Y determines Z.
Transitive dependence thus describes that Z is indirectly dependent on X through its relationship with Y.
The functional dependency constraint establishes a functional relationship among two sets of attributes X and Y.
This constraint specifies that the value of X determines a unique value of Y in all states of a relation;
it is denoted as a functional dependency
X --> Y.
The next lesson describes the process of identifying functional dependencies.
[1]Determinant: The determinant in the description of
functional dependency in the previous point is X because X determines the value Y, at least partially because 1 is added to X as well. A determinant is the inversion or opposite of functional dependency.
[2]Candidate key: A candidate key (potential or permissible key) is a field or combination of fields that can act as a primary key field for a table. Thus uniquely identifying each record in the table.
[3]primary key:A field (or combination of fields) that uniquely identifies a record in a table.
