Understanding Functional Dependencies in Database Design
This article explores the concept of functional dependencies (FDs) in the context of database relations. It defines FDs and provides examples related to real-world applications, such as individuals and companies. The importance of keys and superkeys in determining attributes of relations is highlighted, alongside rules for deriving keys in various relationship types (binary, multiway). The article further delves into FD properties like entailment and equivalence, and explains the closure of attribute sets with a practical algorithm. Understanding these concepts is vital for effective database design and normalization.
Understanding Functional Dependencies in Database Design
E N D
Presentation Transcript
Functional Dependencies Definition: If two tuples agree on the attributes A , A , … A 1 2 n then they must also agree on the attributes B , B , … B 1 2 m Formally: A , A , … A B , B , … B 1 2 m 1 2 n Motivating example for the study of functional dependencies: Name Social Security Number Phone Number
Examples Product: name price, manufacturer Person: ssn name, age Company: name stock price, president Key of a relation is a set of attributes that: - functionally determines all the attributes of the relation - none of its subsets determines all the attributes. Superkey: a set of attributes that contains a key.
Finding the Attributes of a Relation Given a relation constructed from an E/R diagram, what is its key? Rules: 1. If the relation comes from an entity set, the key of the relation is the set of attributes which is the key of the entity set. Person name ssn address
Rules for Binary Relationships name buys Person Product price name ssn Several cases are possible for a binary relationship E1 - E2: 1. Many-many: the key includes the of E1 together with the key of E2. What happens for: 2. Many-one: 3. One-one:
Rules for Multiway Relationships None, really. Except: if there is an arrow from the relationship to E, then we don’t need the key of E as part of the relation key. Product Purchase Store Payment Method Person
Some Properties of FD’s A , A , … A B , B , … B Is equivalent to 1 2 m 1 2 n B A , A , … A 1 1 2 n Splitting rule and Combing rule B A , A , … A 2 1 2 n … B A , A , … A m 1 2 n A , A , … A A Always holds. 1 2 n i
Comparing Functional Dependencies Entailment: a set of functional dependencies S1 entails a set S2 if: any database that satisfies S1 much also satisfy S2. Example: A B, B C entails A C Equivalence: two sets of FD’s are equivalent if each entails the other. {A B, B C } is equivalent to {A B, A C, B C}
Closure of a set of Attributes Given a set of attributes A and a set of dependencies C, we want to find all the other attributes that are functionally determined by A. In other words, we want to find the maximal set of attributes B, such that for every B in B, C entails A B.
Closure Algorithm Start with Closure=A. Until closure doesn’t change do: if is in C, and B is not in Closure then add B to closure. B A , A , … A 1 2 n Are all in the closure, and A , A , … A 1 2 n
Example A B C A D E B D A F B Closure of {A,B}: Closure of {A, F}:
