Identifying Functional Dependencies during Normalization

Functional Dependencies: A Brief Recap

A functional dependency is a relationship between two attributes in a database where the value of one attribute - the determinant - uniquely determines the value of another attribute - the dependent. The notation X → Y is read as "X determines Y" or "Y is functionally dependent on X."

For example, in a table storing customer information, a customer ID uniquely determines the customer's first name, last name, address, and phone number:

CustID -> CustFirst, CustLast, Address, Phone

For each unique CustID value, there is exactly one value for each of the dependent attributes. Functional dependencies are the analytical foundation of database normalization: they identify where redundancy exists and guide the decomposition of tables into well-structured relations.

A functional dependency can also involve a multi-field determinant, where two or more attributes together determine another attribute. In an automobile insurance database, a policy number and car ID together determine the deductible and coverage plan:

PolicyNo, CarID -> Deductible, CoveragePlan

Understanding and correctly identifying functional dependencies is a prerequisite for achieving Second Normal Form, which requires that all non-key attributes depend fully on the entire primary key.

Identifying functional dependencies during normalization produces three concrete outcomes:

• Reduced redundancy. When a dependency is identified and the table is decomposed accordingly, each fact is stored in exactly one place rather than duplicated across many rows.
• Anomaly prevention. Incorrect or missing dependencies are the root cause of insertion, update, and deletion anomalies. Correctly mapping dependencies eliminates the structural conditions that make anomalies possible.
• Accurate schema design. Functional dependencies reflect business rules. A schema that correctly captures functional dependencies accurately represents the real-world constraints of the business domain.

Identifying functional dependencies is not always straightforward. It requires a combination of analytical techniques and domain knowledge, most of which is gathered through requirements analysis - the process of understanding what data a system needs to store, what constraints apply to that data, and how the data elements relate to one another.

No single method reliably surfaces all functional dependencies in a complex schema. The following twelve techniques, used in combination, build a comprehensive picture of the dependencies present in a database:

1. Analyze business rules and requirements. Business rules are the primary source of functional dependencies. A rule such as "each employee has a unique employee ID" directly implies EmployeeID → EmployeeName. Study business processes, collaborate with domain experts and stakeholders, and document all explicit constraints - unique identifiers, mandatory fields, and business-defined relationships.
2. Examine sample data. Review actual data to look for patterns where one attribute's values consistently determine another's. If StudentID always maps to exactly one StudentName across all rows, that pattern supports a functional dependency. Be cautious: sample data can suggest dependencies that the business rules do not actually guarantee. Always verify against the business rules.
3. Create dependency diagrams. Draw visual diagrams that map which attributes determine which others. Dependency diagrams make the structure of the functional dependencies visible and make it easier to identify missing or incorrect dependencies. They are also useful for reviewing the analysis with stakeholders who are not fluent in functional dependency notation.
4. Identify candidate keys. A candidate key is a minimal set of attributes that uniquely identifies each row in a table. Every candidate key is a determinant, and every determinant is a candidate key. Systematically identifying all candidate keys for a table reveals the full set of determinants from which other functional dependencies can be derived.
5. Evaluate attribute semantics. Analyze the meaning of each attribute in the context of the business domain. Does CourseNr always determine CourseName? Does DeptID always determine DeptName? Understanding what each attribute represents - and what uniqueness constraints apply to it - is the most reliable way to identify functional dependencies.
6. Test functional dependencies formally. For an attribute A, test whether its value is consistently determined by a set of attributes B by checking whether any two rows with the same B values also have the same A value. Armstrong's axioms - reflexivity, augmentation, and transitivity - can be used to derive additional dependencies systematically from a known set of dependencies.
7. Look for repeated data patterns. Columns with redundant or repeating values in a table often indicate that a functional dependency is being violated rather than properly captured. When the same combination of values appears in multiple rows, investigate whether those values belong to a separate entity that should have its own table.
8. Apply normalization steps progressively. Work through the normal forms in sequence. Achieving 1NF first clarifies the table structure. Checking for 2NF violations surfaces partial dependencies. Checking for 3NF violations surfaces transitive dependencies. Each step reveals a specific class of functional dependency problem.
9. Conduct interviews or workshops. Users and developers familiar with the business domain often have implicit knowledge of data relationships that is not captured in any document. Interviews and workshops draw out this knowledge, validate identified dependencies against real operational experience, and surface constraints that sample data or documentation did not reveal.
10. Use database design tools. ERD tools such as Oracle Data Modeler and dbdiagram.io visualize entity relationships and can highlight potential functional dependencies. Some database schema analysis tools examine existing data to suggest candidate keys and potential dependencies based on data patterns.
11. Leverage historical and domain knowledge. Industry standards, regulatory requirements, and existing legacy systems often encode functional dependencies implicitly. Reviewing prior schemas, data dictionaries, and domain-specific standards can reveal dependencies that would not be obvious from the current requirements alone.
12. Verify logical consistency. After identifying a set of functional dependencies, verify them against the data. Query the database to confirm that no two rows violate a claimed dependency. Simulate updates to check that changes in one attribute propagate correctly to dependent attributes. Dependencies that fail this verification either represent business rule violations in the data or incorrect dependency analysis.

Combining these techniques produces a robust and reliable picture of the functional dependencies in a database schema, tailored to the specific business context.

When working with a specific table, a practical two-question test can quickly identify whether a field is functionally dependent on the entire primary key - the core requirement for Second Normal Form:

1. Is the field an attribute of the business object that the table represents?
2. Is it a foreign key used to link this table to another table?

If the answer to both questions is no, the field is likely not functionally dependent on the primary key, and the table probably violates 2NF. The field belongs in a different table where it is properly dependent on that table's primary key.

Consider a table with the composite primary key (PolicyNo, CarID). If CoveragePlan is an attribute of the insurance policy rather than of the specific car on that policy, it depends only on PolicyNo and not on the full composite key. This partial dependency violates 2NF. CoveragePlan should be moved to a Policy table where PolicyNo is the primary key.

The following two-step process provides a systematic method for deriving functional dependencies from an entity-relationship model. The Student-Course-Professor domain illustrates the approach.

Step 1: Identify Entity Types and Their Attributes

Define the entities, their candidate keys, and their non-key attributes:

Student:        Keys = {StudName}, {StudNr}   Attributes = {StudAddr}
Course:         Keys = {CourseNr}, {CourseName}
Course Edition: Keys = {CourseNr, Semester, Year}
Professor:      Keys = {ProfName}

Two rules govern functional dependencies derived from entity attributes:

• Rule 1: If an entity has multiple candidate keys, each key determines the other. Because both StudName and StudNr uniquely identify a student, each determines the other: StudName → StudNr and StudNr → StudName.
• Rule 2: A candidate key determines all single-valued attributes of the entity. StudNr determines StudAddr; CourseNr determines CourseName.

Applying these rules to the four entities yields five functional dependencies:

1. StudName → StudNr
2. StudNr → StudName
3. StudNr → StudAddr
4. CourseNr → CourseName
5. CourseName → CourseNr

Step 2: Identify Relationships and Their Attributes

Two relationships exist in this domain:

• Student-followed-Course-edition - a student takes a specific course edition (identified by CourseNr, Semester, Year) and receives a Grade.
• Prof-teaches-Course-edition - a professor teaches a course edition, assuming one professor per course edition per semester and year.

Two rules govern functional dependencies derived from relationships:

• Rule 3: In a one-to-many or one-to-one relationship, a key from the "many" side determines a key on the "one" side.
• Rule 4: A relationship's single-valued attribute is determined by the combination of keys from all participating entities.

Applying these rules yields two additional functional dependencies:

6. StudNr, CourseNr, Semester, Year → Grade (Rule 4 - Grade is determined by the specific student and specific course edition)
7. CourseNr, Semester, Year → ProfName (Rule 3 - one professor per course edition)

For more complex relationships involving three entities - such as a Supplies relationship between Supplier, Part, and Project - check whether one entity is determined by the combination of the others. If each Project and Part combination has at most one Supplier:

Part, Project -> Supplier

When designing a database for an unfamiliar domain, requirements analysis is the most reliable source of functional dependency information. Conduct interviews with domain experts, review existing documentation, examine legacy systems, and study the business rules that govern how data is created, used, and modified.

There is no single method that works for every context. A database for a university scheduling system will require different analytical techniques than one for an insurance claims system or a manufacturing inventory. The twelve techniques described above should be combined and weighted according to what information is available and what the specific domain requires.

Finally, remember that functional dependencies are not static. They reflect business rules as they exist at the time of design. When business rules change - a policy that previously allowed one instructor per course is revised to allow co-instructors, for example - the functional dependencies in the schema must be re-evaluated and the normalization analysis repeated for the affected tables.

To reinforce your understanding of functional dependency identification, complete the exercise below:
Functional Dependencies Exercise