Relational Design Theory Functional Dependencies Jennifer Widom Relational

Relational Design Theory Functional Dependencies Jennifer Widom

Relational design by decomposition Functional Dependencies § “Mega” relations + properties of the data § System decomposes based on properties § Final set of relations satisfies normal form – No anomalies, no lost information § Functional dependencies Boyce-Codd Normal Form § Multivalued dependences Fourth Normal Form Functional dependencies are generally useful concept § Data storage – compression § Reasoning about queries – optimization Jennifer Widom

Example: College application info. Functional Dependencies Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Apply(SSN, c. Name, state, date, major) Jennifer Widom

Functional Dependencies Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Suppose priority is determined by GPA Two tuples with same priority have same GPA Jennifer Widom

Functional Dependencies Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Two tuples with same priority have same GPA Jennifer Widom

Functional Dependency Functional Dependencies § Based on knowledge of real world § All instances of relation must adhere Jennifer Widom

Functional Dependencies Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Jennifer Widom

Functional Dependencies Apply(SSN, c. Name, state, date, major) Jennifer Widom

Functional Dependencies and Keys Functional Dependencies § Relation with no duplicates § Suppose Ᾱ all attributes Jennifer Widom

Trivial Functional Dependency Functional Dependencies Nontrivial FD Completely nontrivial FD Jennifer Widom

Rules for Functional Dependencies Splitting rule Functional Dependencies Can we also split left-hand-side? Jennifer Widom

Rules for Functional Dependencies Combining rule Functional Dependencies Jennifer Widom

Rules for Functional Dependencies Trivial-dependency rules Functional Dependencies Jennifer Widom

Rules for Functional Dependencies Transitive rule Functional Dependencies Jennifer Widom

Closure of Attributes Functional Dependencies § Given relation, FDs, set of attributes Ᾱ § Find all B such that Ᾱ B Jennifer Widom

Closure Example Functional Dependencies Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) SSN s. Name, address, GPA priority HScode HSname, HScity Jennifer Widom

Closure and Keys Is Ᾱ a key for R ? Functional Dependencies How can we find all keys given a set of FDs? Jennifer Widom

Specifying FDs for a relation Functional Dependencies § S 1 and S 2 sets of FDs § S 2 “follows from” S 1 if every relation instance satisfying S 1 also satisfies S 2 How to test? Does A B follow from S ? Jennifer Widom

Specifying FDs for a relation Functional Dependencies Want: Minimal set of completely nontrivial FDs such that all FDs that hold on the relation follow from the dependencies in this set Jennifer Widom

Functional Dependencies Functional dependencies are generally useful concept § Relational design by decomposition Functional dependencies Boyce-Codd Normal Form § Data storage – compression § Reasoning about queries – optimization Jennifer Widom