Identify Functional Dependencies during Normalization

Introduction to Functional Dependencies

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). For example, in a table storing customer information, a customer ID uniquely determines the customer’s first name, last name, address, and phone number. This is written as:

CustID -> CustFirst, CustLast, Address, Phone
  

Here, the arrow (->) means "determines." This notation indicates that for each unique CustID, there is exactly one value for CustFirst, CustLast, Address, and Phone. Functional dependencies are critical in database normalization because they help eliminate redundancy and ensure data integrity.

In some cases, a functional dependency involves a multi-field determinant, where multiple attributes together determine another attribute. For example, in an automobile insurance database, a policy number and car ID together might determine the deductible and coverage plan:

PolicyNo, CarID -> Deductible, CoveragePlan
  

Understanding functional dependencies is essential for ensuring a table meets normalization standards, such as the Second Normal Form (2NF), which requires that all non-key attributes are fully dependent on the entire primary key.

Why Identifying Functional Dependencies Matters

Identifying functional dependencies during normalization ensures that:

• Data redundancy is minimized.
• Anomalies during data insertion, update, or deletion are prevented.
• The database structure accurately reflects the business rules and relationships.

However, identifying these dependencies is not always straightforward. It requires a combination of analytical techniques and domain knowledge, often gathered through requirements analysis (the process of understanding the data a client needs and the conditions for its use).

Here are practical techniques to identify functional dependencies during database normalization:

1. Analyze Business Rules and Requirements
   • Study the business processes to understand how data attributes relate. For example, a rule like "Each employee has a unique employee ID" suggests EmployeeID -> EmployeeName.
   • Collaborate with stakeholders or domain experts to clarify relationships.
   • Document explicit constraints, such as unique identifiers or mandatory fields.
2. Examine Sample Data
   • Review a dataset to identify patterns where one attribute’s values consistently determine another’s.
   • Check for attributes (or combinations) that uniquely identify others, such as a StudentID always linked to a single StudentName.
3. Create Dependency Diagrams
   • Draw visual diagrams to map relationships between attributes, highlighting which attributes determine others.
   • Use these diagrams to confirm dependencies with stakeholders.
4. Identify Candidate Keys
   • Determine attributes (or combinations) that uniquely identify each row in a table, such as StudentID or CourseNr.
   • Verify that these keys can act as determinants for other attributes.
5. Evaluate Attribute Semantics
   • Analyze the meaning of each attribute to understand its role. For instance, does CourseNr always determine CourseName?
   • Check if an attribute depends on another in a specific context.
6. Test Functional Dependencies
   • For an attribute A, test if its value is consistently determined by another attribute or set of attributes B.
   • Use logical rules (e.g., Armstrong’s axioms) to derive additional dependencies systematically.
7. Look for Repeated Data Patterns
   • Identify columns with redundant or repeating values, which may indicate missing functional dependencies.
   • Correlate these patterns with potential determinants to resolve redundancy.
8. Apply Normalization Steps
   • Start with First Normal Form (1NF) by ensuring atomicity and eliminating repeating groups.
   • Check for Second Normal Form (2NF) by ensuring non-key attributes depend on the entire primary key, not just part of it.
   • Verify Third Normal Form (3NF) by eliminating transitive dependencies (where non-key attributes depend on other non-key attributes).
9. Conduct Interviews or Workshops
   • Discuss data relationships with users or developers familiar with the domain.
   • Use their feedback to validate or refine identified dependencies.
10. Use Database Design Tools
    • Employ Entity-Relationship Diagram (ERD) tools to visualize dependencies.
    • Leverage software that analyzes schemas or data to suggest potential dependencies.
11. Leverage Historical or Domain Knowledge
    • Refer to industry standards or existing documentation to infer dependencies.
    • Review legacy systems or schemas for insights into data relationships.
12. Verify Logical Consistency
    • Test dependencies by querying the data to ensure they hold true.
    • Simulate updates to check how changes in one attribute affect dependent attributes.

By combining these techniques, you can build a robust understanding of functional dependencies tailored to the specific database context.

To determine if a field is functionally dependent on the entire primary key (a requirement for 2NF), ask:

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

If neither is true, the field is likely not functionally dependent on the primary key, and the table may not be in 2NF. For example, in a table with a composite key PolicyNo, CarID, if CoveragePlan depends only on PolicyNo, the table violates 2NF.

To systematically identify functional dependencies, follow these steps:

Step 1: Identify Entity Types and Their Attributes
Define the entities (e.g., Student, Course, Professor) and their keys and attributes. For example:

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

From this, derive functional dependencies using these rules:

• Rule 1: If an entity has multiple keys (e.g., StudName and StudNr), each key determines the other (StudName -> StudNr, StudNr -> StudName).
• Rule 2: A key determines its single-valued attributes (StudNr -> StudAddr).

This yields:

1. StudName -> StudNr
2. StudNr -> StudName
3. StudNr -> StudAddr
4. CourseNr -> CourseName
5. CourseName -> CourseNr

Step 2: Identify Relationships and Their Attributes
Identify relationships between entities and their attributes. For example:

• Student-followed-Course-edition: Between Student and Course Edition, with attribute Grade.
• Prof-teaches-Course-edition: Between Professor and Course Edition, assuming one professor per course edition.

Apply these rules:

• Rule 3: For one-to-many or one-to-one relationships, 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 participating entities.

This yields:

1. StudNr, CourseNr, Semester, Year -> Grade (from Rule 4)
2. CourseNr, Semester, Year -> ProfName (from Rule 3, assuming one professor per course edition)

For complex relationships (e.g., involving three entities like Supplier, Part, and Project), check if one entity is determined by the others. For example, in a Supplies(Supplier, Part, Project) relation, if each Project and Part combination has at most one Supplier, derive:

Part, Project -> Supplier
  

Practical Considerations

When designing a database for an unfamiliar domain, rely heavily on requirements analysis. Conduct interviews, review documentation, and study business rules to uncover dependencies. There is no one-size-fits-all method, so combining the techniques above with domain knowledge is essential.

Functional Dependencies Exercise

To reinforce your understanding, complete the following exercise:
Functional Dependencies Exercise

Next Steps

The next lesson will cover the procedure for putting a relation into Second Normal Form (2NF), building on the functional dependencies identified here.