Multivalued Dependency

Multivalued Dependency Prepared by Tomasz Kaciak CS157A

Not All Designs are Equally Good This is a poor schema design Is this one better? StudentInfo(sid, sname, crno, subj, cid, exp-grade) Student(sid, sname) Course(crno, cid) Subject(cid, subj) Takes(sid, crno, exp-grade)

Problems of first design • Redundancy, potential inconsistency • Insertion Anomalies • Deletion Anomalies Second design • Illustrates decompositions and normalization.

Functional Dependencies Def. Given a relation schema R and subsets X, Y of R: An instance r of R satisfies FD XY if, for any two tuples t1, t2 2 r, t1[X ] = t2[X] implies t1[Y] = t2[Y] • For an FD to hold for schema R, it must hold for every possible instance of r

Functional DependenciesDescribe “Key-Like” Relationships A key is a set of attributes where: If keys match, then the tuples match A functional dependency (FD) is a generalization: If an attribute set determines another, written X!Ythen if two tuples agree on attribute set X, they must agree on Y:sid!sname

Armstrong’s Axioms: Inferring FDs Reflexivity: If YX then X Y (trivial dependency) sname, sid  sname Augmentation: If XY then XWYW crno  subj so crno, exp-grade  subj, exp-grade Transitivity: If XY and YZ thenXZ crnocid and cid subj so crnosubj

Armstrong’s Axioms Lead to… • Union: If X Y and XZ then XYZ • Pseudotransitivity: If XY and WYZ then XWZ • Decomposition: If XY and ZY then XZ

Armstrong’s axioms • Armstrong’s axioms are sound and completeinference rules for FDs! • Sound: all the derived FDs (by using the axioms) are those logically implied by the given set • Complete: all the logically implied (by the given set) FDs can be derived by using the axioms.

Now we can check FDs closure Defn. Let F be a set of FD’s. Its closure, F+(cover F+), is the set of all FD’s: {XY | XY is derivable from F by Armstrong’s Axioms}

Consider FDs Example F= {A→C, B→C, CD→E}, let show that AD→E: 1) A→C (given) 2) AD→CD (Augmentation) 3) CD→E (given) 4) AD→E (2, 3 and Transitivity)

One more Ex. R(C S Z), CS→Z, Z →C What is the relation NF? • Z->C (given) SZ->SC (Augmentation) SZ->Z (Transitivity) SZ->C (Transitivity) • All attributes are PRIME (since CS and ZS are keys) • Therefore, in 3NF, but not in BCNF

Computing F+ • To compute the closure of a set of functional dependencies F: • F+ = Frepeatfor each functional dependency f in F+ apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F+for each pair of functional dependencies f1and f2 in F+iff1 and f2 can be combined using transitivitythen add the resulting functional dependency to F+until F+ does not change any further

Example of computing X+ F={A B, AC D, AB C} 1. result=A A B • result=AB AB->C • result=ABC ABC->BD 3. result=ABCD X+ {ABCD}

Finding primary key Ex. • R: ( A, B, C, D, E, F ) • F={A  B, BC  A, D  EF, B  D, A  C} • Task: Find the candidate key(s) and identify a primary key A->B B->D D->EF A->BDEF A->C A->BCDEF

FD Redundancy • Sets of functional dependencies may have redundant dependencies that can be inferred from the others Eg: A  C is redundant in: {A  B, B  C, A  C} Parts of a functional dependency may be redundant • E.g. on RHS: {A  B, B  C, A  CD} can be simplified to {A  B, B  C, A  D} • E.g. on LHS: {A  B, B  C, AC  D} can be simplified to {A  B, B  C, A  D}

Materials Used • Schema Refinement & Normalization Theorymason.gmu.edu/~brodsky/infs614/fall03/lecture12.ppt • FDs and Normal Formswww.cs.wisc.edu/~cs784-1/lecs/784_lec_6.ppt • Schema Normalization, ConcludedZachary G. Ives

Multivalued Dependency