Functional Dependencies

Functional Dependencies in Database Management

1. Key Concepts

• Attribute (Column): Individual pieces of data within a table.

• Tuple (Row): A single record or row containing values for each attribute.

• Relation (Table): A collection of tuples, each with the same set of attributes.

2. Functional Dependency (FD)

• Definition: A relationship between two sets of attributes in a relation, denoted as X -> Y, where X determines Y.

• Full Functional Dependency: All attributes in X are necessary to determine Y.

• Partial Functional Dependency: Proper subsets of X can determine Y.

• Transitive Dependency: Dependency where Z depends on Y, which depends on X.

• Trivial Dependency: When Y is a subset of X, i.e., X already includes Y.

• Non-Trivial Dependency: When Y is not a subset of X.

3. Multi-Valued Dependency (MVD)

• Definition: Denoted as X ↠↠ Y, indicating that X determines a set of values for Y, not just a single value.

4. Candidate Key

• Definition: A minimal set of attributes that uniquely identify each tuple in a relation.

• Prime Attribute: Attributes that form a part of a candidate key.

• Non-Prime Attribute: Attributes not part of any candidate key.

5. Normalization

• Definition: The process of reducing redundancy and dependency issues in a relational database by decomposing relations into smaller, well-structured relations.

• Normalization Forms: Various normal forms (e.g., 1NF, 2NF, 3NF, BCNF) define specific criteria for eliminating redundancy and dependency issues based on functional dependencies.

Functional dependencies are fundamental in maintaining data integrity and efficient database design, helping to prevent anomalies and inconsistencies while optimizing data storage. Trivial and non-trivial dependencies are key distinctions in understanding the significance of functional dependencies in a given database schema.

Let's illustrate these concepts with examples:

This answer is directly from my CS course and obtained from the Connolly and Begg text book.

1. Full Functional Dependencies

Identify the candidate keys here (propertyNo, iDate and pAddress). This is because any combination of those 3 can allow you to find what the other attributes are for a given tuple (I can find the staffNo that did the inspection given those three things, I can find the carReg the staffNo used given those 3 things etc.). But note, you need all of those 3 to find the other attributes, not just a subset. Full dependencies always relate to non-candidate keys depending on candidate keys, either depending on all or depending on some.

1. Partial Dependencies

Given those three candidate keys, look within the candidate keys. Is there any subset(s) of the candidate key which is dependent on the other? Yes, it is pAddress. Given a propertyNo, you can figure out what the address of the property. Then look outside of the candidate keys. Is there any of these keys that depend on only parts of the candidate key, not all components? In this case there are not. So partial dependencies are always dependencies within the candidate keys or dependencies of non-candidate keys on only parts of the candidate keys rather than all components

1. Transitive Dependencies

Now, look at the non-candidate keys (staffNo, comments, iTime (inspection time), sName, carReg). Within those, is there anything that is functionally dependent on the other? Yes, it is sName - given a staffNo, you can figure out the name of the staff member. But staffNo is functionally dependent on the 3 candidate keys. So by transitivity, propertyNo + iDate + pAddress -> staffNo -> sName, so sName is transitively dependent on staffNo. Transitive dependencies always relate to attributes outside of candidate keys.