1 / 16

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)

garin
Download Presentation

Multivalued Dependency

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Multivalued Dependency Prepared by Tomasz Kaciak CS157A

  2. 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)

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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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.

  9. 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}

  10. 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)

  11. 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

  12. 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

  13. 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}

  14. 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

  15. 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}

  16. 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

More Related