Quick Contact

    Fifth Normal Form (5NF)

    A relation R is in 5NF, also known as projection join normal form (PJ/NF)-If and only if each join dependency in R is a consequence of the candidate keys of R.

    Example

    Consider a relation SPJ shown below this relation is “all key” and involves no FDs or MVDs and so is 4NF. Figure shows

    • The three projections SP, PJ and JS of SPJ and
    • The effect of joining SP and PJ over P# and then joining the result and JS over (J#, S#). Note that the result of the first join is to produce a copy of the original SPJ plus one spurious tuple and then the effect of the second. Join is to eliminate that tuple.

    The statement SPJ is equal to join of its three projection SP, PJ and JS are equivalent to the statement.

    Fifth Normal Form (5NF)
    If the pair < s1,p1 > appears in SP
    And the pair < p1,j1 > appears in PJ
    And the pair < j1,s1 > appears in JS
    Then the triple < s1,p1,j1 > appears in SPJ.

    This is a constant, just like an FD or an MVD. This constraint is known as a join dependency (J.D). So, in this example, we say that SPJ satisfies the join dependency(“SP,PJ,JS“).

    Definition of Join Dependency

    A relation R satisfies join dependency (R1,R2,R3….Rn) if and only if R is equivalent to the join of (R1,R2,R3….Rn) where Ri is the subsets of the collection of attributes of R.

    Relation SPJ suffers from several problems over update operations.

    Example

    Consider the relation SPJ

    SPJ
    S# P# J#
    S1 P1 J1
    S1 P2 J1
    1. If < S2,P1,J1 > inserted. < S1,P1,J1 > must also be inserted.
    2. Yet converse is not true.

    Note:It is that constraint on SPJ is equivalent to < S1,P1,J2 >, < S2,P1,J1 >, appears in SPJ then < S1,P1,J1 > also appears in SPJ, so it is due to the presence of JD. This problem is removed when it is three decomposed.

    What is Relational Decomposition?

    Decomposition is the mechanism of busting the relation into multiple relations. Decompositions of relations must be certain that from the decomposed relations, an original relation can be reconstructed. If decomposition of a relation is treated carelessly, it can lead to information loss.

    Types of Decomposition

    Two types of decomposition are as follows.

    Fifth Normal Form (5NF)
    1. Lossless Decomposition

    Given the data is not hidden from the relationship because it is decomposed, later the decomposition would be lossless.

    The lossless decomposition authenticates that the joining of the relationship will outcome in an equivalence relation as it was decomposed.

    A relation is a lossless decomposition because the natural joins of all over the decomposition provide a standard relation.

    Example

    Consider the relation schema Employee_Department.

    EMP-ID EMP-NAME DESG SALARY DEPT-ID DEPT-NAME DEPT-LOC
    111 Rohan Manager 20000 D01 Finance Delhi
    112 Kumar Developer 25000 D02 Production Noida
    113 Mahesh Tester 20000 D03 Sales Mumbai
    114 Anita Professor 30000 D04 Marketing Delhi
    115 Raman Manager 22000 D05 Testing Gurugram
    116 Gaurav Developer 15000 D06 Sales Mumbai

    Decompose the above relation into two relations to check whether decomposition is lossless or lossy.

    Therefore, we have decomposed the connection, which is Employee and Department.

    Relation1: Employee Table
    EMP-ID EMP-NAME DESG SALARY
    111 Rohan Manager 20000
    112 Kumar Developer 25000
    113 Mahesh Tester 20000
    114 Anita Professor 30000
    115 Raman Manager 22000
    116 Gaurav Developer 15000
    Relation2: Department Table
    DEPT-ID EMP-ID DEPT-NAME DEPT-LOC
    D01 111 Finance Delhi
    D02 112 Production Noida
    D03 113 Sales Mumbai
    D04 114 Marketing Delhi
    D05 115 Testing Gurugram
    D06 116 Sales Mumbai

    Now, when these two relations are joined on the common field “EMP-ID”, then the resultant relation will show as.

    Employee ⋈ Department
    EMP-ID EMP-NAME DESG SALARY DEPT-ID DEPT-NAME DEPT-LOC
    111 Rohan Manager 20000 D01 Finance Delhi
    112 Kumar Developer 25000 D02 Production Noida
    113 Mahesh Tester 20000 D03 Sales Mumbai
    114 Anita Professor 30000 D04 Marketing Delhi
    115 Raman Manager 22000 D05 Testing Gurugram
    116 Gaurav Developer 15000 D06 Sales Mumbai

    Therefore, the decomposition is a lossless join decomposition.

    Hence, the Employee table includes (EMP-ID, EMP-NAME, DESG, SALARY) and Department table include (DEPT-ID, DEPT-NAME, and DEPT-LOC), then it isn’t always feasible to connect these two tables or relations, because there may be no common field between them. And it develops into lossy join decomposition.

    2. Dependency Preservation

    Dependency is an essential constraint on the information.

    Each dependency needs to be fulfilled by always one decomposed table.

    If (P → Q) contains, therefore two sets are functional dependent. And, it gets more helpful for inspecting the dependency only if both sets in a similar relation.

    This decomposition features can simply completed by supporting the functional dependency.

    In this feature, it permits to analysis the redevelops without calculating the natural join of the database mechanism.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.