studytonight.com
    •                  Sign in
    New Tutorials:   JAVASCRIPT    SASS/SCSS    PL/SQL  
    See the Tutorial List

    Fourth Normal Form (4NF)

    Fourth Normal Form comes into picture when Multi-valued Dependency occur in any relation. In this tutorial we will learn about Multi-valued Dependency, how to remove it and how to make any table satisfy the fourth normal form.

    Follow the video above for complete explanation of 4th Normal Form. Or, if you want, you can even skip the video and jump to the section below for the complete tutorial.

    In our last tutorial, we learned about the boyce-codd normal form, we suggest you to follow the last tutorial before this one.

    Rules for 4th Normal Form

    For a table to satisfy the Fourth Normal Form, it should satisfy the following two conditions:

    1. It should be in the Boyce-Codd Normal Form.
    2. And, the table should not have any Multi-valued Dependency.

    Let's try to understand what multi-valued dependency is in the next section.

    What is Multi-valued Dependency?

    A table is said to have multi-valued dependency, if the following conditions are true,

    1. For a dependency A → B, if for a single value of A, multiple value of B exists, then the table may have multi-valued dependency.
    2. Also, a table should have at-least 3 columns for it to have a multi-valued dependency.
    3. And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other.

    If all these conditions are true for any relation(table), it is said to have multi-valued dependency.

    Time for an Example

    Below we have a college enrolment table with columns s_id, course and hobby.

    s_idcoursehobby
    1ScienceCricket
    1MathsHockey
    2C#Cricket
    2PhpHockey

    As you can see in the table above, student with s_id 1 has opted for two courses, Science and Maths, and has two hobbies, Cricket and Hockey.

    You must be thinking what problem this can lead to, right?

    Well the two records for student with s_id 1, will give rise to two more records, as shown below, because for one student, two hobbies exists, hence along with both the courses, these hobbies should be specified.

    s_idcoursehobby
    1ScienceCricket
    1MathsHockey
    1ScienceHockey
    1MathsCricket

    And, in the table above, there is no relationship between the columns course and hobby. They are independent of each other.

    So there is multi-value dependency, which leads to un-necessary repetition of data and other anomalies as well.

    How to satisfy 4th Normal Form?

    To make the above relation satify the 4th normal form, we can decompose the table into 2 tables.

    CourseOpted Table

    s_idcourse
    1Science
    1Maths
    2C#
    2Php

    And, Hobbies Table,

    s_idhobby
    1Cricket
    1Hockey
    2Cricket
    2Hockey

    Now this relation satisfies the fourth normal form.

    A table can also have functional dependency along with multi-valued dependency. In that case, the functionally dependent columns are moved in a separate table and the multi-valued dependent columns are moved to separate tables.

    If you design your database carefully, you can easily avoid these issues.