Multivalued Dependency

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, t1X t2X
  implies t1Y t2Y
• 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 Ysid ! sname

6
Armstrongs 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 then X ? Z
• crno ? cid and cid ? subj
• so crno ? subj

7
Armstrongs 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
Armstrongs axioms

• Armstrongs axioms are sound and complete
  inference 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 FDs. Its closure, F
  (cover F),
• is the set of all FDs
• X ? Y X ? Y is derivable from F by
  Armstrongs 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-gtC (given) SZ-gtSC (Augmentation)
• SZ-gtZ (Transitivity) SZ-gtC
  (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 Frepeat for each functional
  dependency f in F apply reflexivity and
  augmentation rules on f add the resulting
  functional dependencies to F for each pair of
  functional dependencies f1and f2 in F if
  f1 and f2 can be combined using transitivity
  then add the resulting functional dependency to
  Funtil F does not change any further

13
Example of computing X

• FA ?B, AC ?D, AB ?C
• 1. resultA
• A ?B
• resultAB
• AB-gtC
• resultABC
• ABC-gtBD
• 3. resultABCD
• X
  ABCD

14
Finding primary key Ex.

• R ( A, B, C, D, E, F )
• FA ? B, BC ? A, D ? EF, B ? D, A ? C
• Task Find the candidate key(s) and identify a
  primary key
• A-gtB B-gtD D-gtEF A-gtBDEF
• A-gtC
• A-gtBCDEF

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 Theory
  mason.gmu.edu/brodsky/infs614/fall03/lecture12.pp
  t
• FDs and Normal Forms
  www.cs.wisc.edu/cs784-1/lecs/784_lec_6.ppt
• Schema Normalization, Concluded
  Zachary G. Ives