60 likes | 274 Vues
Functional Dependencies. Alternative Data Modeling Approach Based on Formal Logic ER Diagrams can be mapped into FDs (sans some cardinality information) Algorithms to automatically generate 3 rd Normal form.
E N D
Functional Dependencies • Alternative Data Modeling Approach • Based on Formal Logic • ER Diagrams can be mapped into FDs • (sans some cardinality information) • Algorithms to automatically generate 3rd Normal form. • FDs (alone with MVDs and JDs) are used to formally define the various relational normal forms (e.g., 3rd normal form).
Functional Dependencies Attribute(s) B are said to be functionally dependent on attribute(s) A iff (if and only if) for all valid instance(s) of A, those values of A uniquely determine the value(s) for B. P# Color P#,S# Qty P# > PN
Employee(EmpID,Name,Dept,Salary,Course,Date Completed) • FDs: • EMPID Name,Dept,Salary • Course date completed • Note: A key is a set of non-redundant attributes that functionally determines all the attributes in the relation schema. • empid,course name,dept,salary,date completed
Functional Dependency Rules • Augmentation: if x Y, then ZX Y • Student# StudentName thenStudent # course # student name • Transitivity:if X Y & Y Z then X Z • Student # major and major advisor then student# advisor • Pseudo Transitivity: if X Y & YZ W then XZ W Thus if student # major and major, class advisor then student #,Class advisor
If X,Y,Z, and W are attributes: • X X (reflexive) • If X y then XZ Y (augmentation) • If X Y & X Z then X YZ (union) • If X Y then X Z where Z subset of Y (Decomposition) • IF X Y & Y Z then X Z (transitivity) • IF X Y & YZ W, then XZ W (pseudotransitivity)
Suppose relation (A B C D) with A BC, B D, and DB A • Are These Valid Derivations? • A B A D A BD • A A A C B A • Is this a “Minimal” equivalent Set? • B A • A CD