Fourth Normal Form (4NF)

Posted By on October 10, 2014

Download PDF
Multivalued Dependencies
Fifth Normal Form (5NF)

Fourth Normal Form (4NF)

  1. We saw that BC-schema was in BCNF, but still was not an ideal design as it suffered from repetition of information. We had the multivalued dependency cname tex2html_wrap_inline2000 street ccity, but no non-trivial functional dependencies.
  2. We can use the given multivalued dependencies to improve the database design by decomposing it into fourth normal form.
  3. A relation schema R is in 4NF with respect to a set D of functional and multivalued dependencies if for all multivalued dependencies in tex2html_wrap_inline2010 of the form tex2html_wrap_inline1970 , where tex2html_wrap_inline1732 and tex2html_wrap_inline1734 , at least one of the following hold:
    • tex2html_wrap_inline1970 is a trivial multivalued dependency.
    • tex2html_wrap_inline1740 is a superkey for schema R.
  4. A database design is in 4NF if each member of the set of relation schemas is in 4NF.
  5. The definition of 4NF differs from the BCNF definition only in the use of multivalued dependencies.
    • Every 4NF schema is also in BCNF.
    • To see why, note that if a schema is not in BCNF, there is a non-trivial functional dependency tex2html_wrap_inline1730 holding on R, where tex2html_wrap_inline1740 is not a superkey.
    • Since tex2html_wrap_inline1730 implies tex2html_wrap_inline1970 , by the replication rule, R cannot be in 4NF.
  6. We have an algorithm similar to the BCNF algorithm for decomposing a schema into 4NF:
     result :=  tex2html_wrap_inline1754 ;

    done := false;

    compute tex2html_wrap_inline2010 ;

    while (not done) do

    if (there is a schema tex2html_wrap_inline1556 in result

    that is not in 4NF)

    then begin

    let tex2html_wrap_inline1970 be a nontrivial multivalued

    dependency that holds on tex2html_wrap_inline1556 such that

    tex2html_wrap_inline1764 is not in tex2html_wrap_inline2010 , and

    tex2html_wrap_inline1768 ;

    result = tex2html_wrap_inline2252


    else done = true;

  7. If we apply this algorithm to BC-schema:
    • cname tex2html_wrap_inline2000 loan# is a nontrivial multivalued dependency and cname is not a superkey for the schema.
    • We then replace BC-schema by two schemas:
       Cust-loan-schema=(cname, loan#)

      Customer-schema=(cname, street, ccity)

    • These two schemas are in 4NF.
  8. We can show that our algorithm generates only lossless-join decompositions.
    • Let R be a relation schema and D a set of functional and multivalued dependencies on R.
    • Let tex2html_wrap_inline1620 and tex2html_wrap_inline1622 form a decomposition of R.
    • This decomposition is lossless-join if and only if at least one of the following multivalued dependencies is in tex2html_wrap_inline2010 :


    • We saw similar criteria for functional dependencies.
    • This says that for every lossless-join decomposition of R into two schemas tex2html_wrap_inline1620 and tex2html_wrap_inline1622 , one of the two above dependencies must hold.
    • You can see, by inspecting the algorithm, that this must be the case for every decomposition.
  9. Dependency preservation is not as simple to determine as with functional dependencies.
    • Let R be a relation schema.
    • Let tex2html_wrap_inline2282 be a decomposition of R.
    • Let D be the set of functional and multivalued dependencies holding on R.
    • The restriction of D to tex2html_wrap_inline1556 is the set tex2html_wrap_inline2294 consisting of:
      • All functional dependencies in tex2html_wrap_inline2010 that include only attributes of tex2html_wrap_inline1556 .
      • All multivalued dependencies of the form tex2html_wrap_inline2300 where tex2html_wrap_inline2302 and tex2html_wrap_inline1970 is in tex2html_wrap_inline2010 .
    • A decomposition of schema R is dependency preserving with respect to a set D of functional and multivalued dependencies if for every set of relations tex2html_wrap_inline2312 such that for alli, tex2html_wrap_inline1578 satisfies tex2html_wrap_inline2294 , there exists a relation r(R) that satisfies D and for which tex2html_wrap_inline2324 for all i.
  10. What does this formal statement say? It says that a decomposition is dependency preserving if for every set of relations on the decomposition schema satisfying only the restrictions on D there exists a relation r on the entire schema R that the decomposed schemas can be derived from, and that r also satisfies the functional and multivalued dependencies.
  11. We’ll do an example using our decomposition algorithm and check the result for dependency preservation.
    • Let R=(A,B,C,G,H,I).
    • Let D be



    • R is not in 4NF, as we have tex2html_wrap_inline2144 and A is not a superkey.
    • The algorithm causes us to decompose using this dependency into


    • tex2html_wrap_inline1620 is now in 4NF, but tex2html_wrap_inline1622 is not.
    • Applying the multivalued dependency tex2html_wrap_inline2174 (how did we get this?), our algorithm then decomposes tex2html_wrap_inline1622 into


    • tex2html_wrap_inline2366 is now in 4NF, but tex2html_wrap_inline2368 is not.
    • Why? As tex2html_wrap_inline2160 is in tex2html_wrap_inline2010 (why?) then the restriction of this dependency to tex2html_wrap_inline2368 gives us tex2html_wrap_inline2376 .
    • Applying this dependency in our algorithm finally decomposes tex2html_wrap_inline2368 into


    • The algorithm terminates, and our decomposition is tex2html_wrap_inline2384 and tex2html_wrap_inline2386 .
  12. Let’s analyze the result.  figure803
    Figure 1:   Projection of relation r onto a 4NF decomposition of R.

    • This decomposition is not dependency preserving as it fails to preserve tex2html_wrap_inline2146 .
    • Figure 1 shows four relations that may result from projecting a relation onto the four schemas of our decomposition.
    • The restriction of D to (A,B) is tex2html_wrap_inline2144 and some trivial dependencies.
    • We can see that tex2html_wrap_inline2448 satisfies tex2html_wrap_inline2144 as there are no pairs with the same A value.
    • Also, tex2html_wrap_inline2454 satisfies all functional and multivalued dependencies since no two tuples have the same value on any attribute.
    • We can say the same for tex2html_wrap_inline2456 and tex2html_wrap_inline2458 .
    • So our decomposed version satisfies all the dependencies in the restriction of D.
    • However, there is no relation r on (A,B,C,G,H,I) that satisfies D and decomposes into tex2html_wrap_inline2468 and tex2html_wrap_inline2458 .
    • Figure 2 shows tex2html_wrap_inline2472 .
    • Relation r does not satisfy tex2html_wrap_inline2146 .
    • Any relation s containing r and satisfying tex2html_wrap_inline2146 must include the tuple tex2html_wrap_inline2484 .
    • However, tex2html_wrap_inline2486 includes a tuple tex2html_wrap_inline2488 that is not in tex2html_wrap_inline2454 .
    • Thus our decomposition fails to detect a violation of tex2html_wrap_inline2146 .

    Figure 2 :   A relation r(R) that does not satisfy tex2html_wrap_inline2146 .

  13. We have seen that if we are given a set of functional and multivalued dependencies, it is best to find a database design that meets the three criteria:
    • 4NF.
    • Dependency Preservation.
    • Lossless-join.
  14. If we only have functional dependencies, the first criteria is just BCNF.
  15. We cannot always meet all three criteria. When this occurs, we compromise on 4NF, and accept BCNF, or even 3NF if necessary, to ensure dependency preservation.


Multivalued Dependencies
Fifth Normal Form (5NF)

Download PDF

Posted by Akash Kurup

Founder and C.E.O, World4Engineers Educationist and Entrepreneur by passion. Orator and blogger by hobby