1 / 11

Today’s class

Today’s class. 3NF Decomposition Multi-valued Dependencies (MVD) 4NF. Example 4 . Let R=R(A,B,C) with FDs F={ B C, ACB} Keys: {A,C}, {A,B} BC violates BCNF condition R1={B,C} with BC ({B} is the key) R2={B,A} What about ACB BCNF decomposition is not Dependency Preserving.

fruma
Télécharger la présentation

Today’s class

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. Today’s class 3NF Decomposition Multi-valued Dependencies (MVD) 4NF

  2. Example 4 • Let R=R(A,B,C) with FDs F={ BC, ACB} • Keys: {A,C}, {A,B} • BC violates BCNF condition • R1={B,C} with BC ({B} is the key) • R2={B,A} • What about ACB • BCNF decomposition is not Dependency Preserving

  3. 3NF • Less stringent than BCNF • Dependency preserving at the cost of a little redundancy • A relation R is in 3NF iff every non-trivial FD X  Y is such that X is either a superkey or a member of key • Example 4 satisfies 3NF condition • Since ‘B’ is a part of a key ({A,B})

  4. Example 4 (Re-visited) • Let R=R(A,B,C) with FDs F={ BC,ACB} • Keys: {A,C}, {A,B} • BC, AC B satisfy 3NF condition • R is in 3NF • R may contain redundancy due to BC

  5. Note • 3NF decomposition is similar to 4NF decomposition • A relation is in 4NF => it is in 3NF

  6. Multi-valued Dependency – EX1 Consider the following relations Name  Street City

  7. Multi-valued Dependency – EX2 Consider the following relation CTB with NO FDs and the key is CTB. But with the constraint that the recommended texts for a course are independent of the instructor. CTB is in BCNF but contains redundancy

  8. MVD • The MVD X  Y is said to hold over R if, each X value is associated with a set of Y values and this set is ‘independent’ of the values in the other attributes • The MVD X  Y hold over r=r(R) implies if t1, t2 R and t1.X=t2.X, then there must be some t3r such that t3.XY=t1.XY and t3.Z=t2.Z • In previous example: C  T • FD restricts tuples where as MVD adds tuples • If (a, b1, c1), (a, b2, c2)  r=R(X,Y,Z) then (a, b1, c2) and (a, b2, c1) must be in r

  9. Theory of MVDs • Replication • X  Y => X  Y • Converse is not true • MVD complementation • If X  Y, then X  R-X-Y • In the given example • C  T => C  B • MVD transitivity • If X  Y and Y  Z, then X  Z-Y

  10. Note • Splitting rule does not hold for MVDs • Ex: name  street city • Keys are determined using FD’s only • AB  C then AB  AC (i.e. adding attributes from left is okay) • A MVD X  Y is non-trivial • None of Y is in X • R  X  Y

  11. 4NF • R is said to be in 4NF if for every non-trivial MVD X  Y that holds on R, X is a superkey • Refer Example 2 • CTB is not in 4NF as CT is a nontrivial MVD and C is not a key (given CTB is the key) • 4NF decomposition • R1 (C,T) is in 4NF • R2 (C,B) is in 4NF • 4NF forbids non-trivial MVD’s unless they are actually FD’s allowed by BCNF

More Related