Functional Dependencies involved in Normalization

Question: What are some of the techniques used to to identify functional dependencies during normalization?
There are several techniques that can be used to identify functional dependencies during the normalization process. Some of the most common techniques include:

1. Reviewing business rules: Business rules can provide valuable insights into the relationships between different columns in a table. By reviewing the business rules, we can identify the functional dependencies between different columns.
2. Examining data: By examining the data in a table, we can identify any patterns or relationships between different columns. For example, if the values in one column always correspond to specific values in another column, there may be a functional dependency between those columns.
3. Using a data profiling tool: Data profiling tools can be used to analyze the data in a table and identify any patterns or relationships between columns.
4. Applying normalization rules: Applying the normalization rules themselves can also help to identify functional dependencies. For example, if a table is not in first normal form, it may be because of a functional dependency that needs to be addressed.
5. Creating dependency diagrams: Dependency diagrams can be used to visually represent the relationships between different columns in a table. By creating a dependency diagram, we can easily identify any functional dependencies that exist between columns.

By using these techniques, we can identify the functional dependencies that exist between different columns in a table, which can help us to normalize the table more effectively.

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^[1] 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.

One strategy you can use to determine whether a field is functionally dependent on the entire primary key is to ask:

1) Is the field an attribute of the business object represented by the table or
2) Is it a foreign key field used to establish a link with another table?

If neither of these statements is true, there is a good possibility the field is not functionally dependent. Consequently, the table is not in (2NF) second normal form.

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^[2], 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

Student: {StudName}, {StudNR}
attributes: StudAddr
Course: {CourseNr}, {CourseName}
Course Edition: {CourseNr, Semester, Year}
Professor: {ProfName}

The rule ER2FD3 also has a variant for n-ary relationships where n>2, which is somewhat more complex. Consider, for example, a relationship that holds between Entity Types A, B and C. Then it should be checked if perhaps one of the participating Entity Types, say C, is functionally determined by the two others, i.e., if for every combination of A entity and B entity there is at most one C entity in this relationship with them. If that is the case, then an FD should be derived from a combination of keys of A and keys of B to the keys of C. An example of this would be a relation Supplies(Supplier, Part, Project) where each Project has for each Part at most one Supplier.

A functional dependency is a one-way relationship between two attributes such that at any given time, for each unique value of attribute A, only one value of attribute B is associated with it through the relation. For example, assume that A is the customer number from the orders relation. Each customer number is associated with one customer first name, one last name, one street address, one city, one state, one zip code, and one phone number. Although the values for those attributes may change, at any moment, there is only one.

Before you move on to the next lesson, complete the following exercise to reinforce your understanding of functional dependencies.
Functional Dependencies - Exercise
The next lesson reviews the procedure for putting a relation in 2NF.

[1]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.

[2]multi-field determinant: A determinant consisting of more than one field.