| Lesson 9 | Functional dependencies involved in normalization |
| Objective | Identify functional dependencies |
Unfortunately, there is no cut-and-dried way to identify functional dependencies.
One will have to rely on the knowledge you gain during requirements analysis to make those determinations. You may design a database for an unfamiliar company, or need to account for data on subjects about which you have little prior knowledge. The interviews you conduct and the outside reading you do are absolutely invaluable in identifying the business objects to be represented in the database and the functional dependencies in the resulting relations.
Requirements analysis: The stage in the database design cycle when designers find out everything they can about the
data the client needs to store in the database and the conditions under which that data needs to be accessed.One will have to rely on the knowledge you gain during requirements analysis to make those determinations. You may design a database for an unfamiliar company, or need to account for data on subjects about which you have little prior knowledge. The interviews you conduct and the outside reading you do are absolutely invaluable in identifying the business objects to be represented in the database and the functional dependencies in the resulting relations.
field is functionally dependent
One strategy you can use to determine whether a field is functionally dependent on the entire primary key is to ask if the field is an
attribute of the business object represented by the table or if it is a foreign key field used to establish a link with another table. If
neither is true, there is a good possibility the field is not functionally dependent and, therefore, the table is not in 2NF.
Representing Functional Dependencies
In relational notation, a functional dependency is represented with the -> symbol. For example:
CustID -> CustFirst, CustLast, Address, Phone
The -> (arrow) character is read as "determines, so the functional dependency above is read as "CustID determines CustFirst, CustLast, Address, and Phone."
If a functional dependency has a multi-field determinant, separate the fields to the left of the arrow by a comma. An automobile insurance policy, where each policy could cover more than one car under different terms, is an example of a multi-field determinant:
PolicyNo, CarID -> Deductible, CoveragePlan
multi-field determinant: A determinant consisting of more than one field.CustID -> CustFirst, CustLast, Address, Phone
The -> (arrow) character is read as "determines, so the functional dependency above is read as "CustID determines CustFirst, CustLast, Address, and Phone."
If a functional dependency has a multi-field determinant, separate the fields to the left of the arrow by a comma. An automobile insurance policy, where each policy could cover more than one car under different terms, is an example of a multi-field determinant:
PolicyNo, CarID -> Deductible, CoveragePlan
The next lesson reviews the procedure for putting a relation in 2NF.
Functional Dependencies - Exercise
Before you move on to the next lesson, complete the following exercise to reinforce your understanding of functional dependencies.
Functional Dependencies - Exercise
Functional Dependencies - Exercise