Mastering Functional Dependencies in Database Design

Understanding Functional Dependencies in Database Design

• A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table.
• For any relation R, attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of Y.
• The left side of the FD diagram is called the determinant, and the right side is the dependent.
• Armstrong’s axioms are a set of inference rules used to infer all the functional dependencies on a relational database.
• The axiom of reflexivity says, if Y is a subset of X, then X determines Y.
• The axiom of augmentation says if X determines Y, then XZ determines YZ for any Z.
• The axiom of transitivity says if X determines Y, and Y determines Z, then X must also determine Z.
• The union rule suggests that if two tables are separate, and the PK is the same, you may want to consider putting them together.
• Decomposition is the reverse of the union rule.
• A dependency diagram illustrates the various dependencies that might exist in a non-normalized table.
• Actual data can help clarify which attributes are dependent and which are determinants.
• Inference rules can be used to identify and fix problems with partial dependencies, transitive dependencies, and other issues that arise in database design.