Properties of Relational Decomposition

When a relation in the relational model is not appropriate normal form then the decomposition of a relation is required. In a database, breaking down the table into multiple tables termed as decomposition. The properties of a relational decomposition are listed below :

  1. Attribute Preservation:
    Using functional dependencies the algorithms decompose the universal relation schema R in a set of relation schemas D = { R1, R2, ….. Rn } relational database schema, where ‘D’ is called the Decomposition of R.

    The attributes in R will appear in at least one relation schema Ri in the decomposition, i.e., no attribute is lost. This is called the Attribute Preservation condition of decomposition.



  2. Dependency Preservation:
    If each functional dependency X->Y specified in F appears directly in one of the relation schemas Ri in the decomposition D or could be inferred from the dependencies that appear in some Ri. This is the Dependency Preservation.

    If a decomposition is not dependency preserving some dependency is lost in decomposition. To check this condition, take the JOIN of 2 or more relations in the decomposition.

    For example:

    R = (A, B, C)
    F = {A ->B, B->C}
    Key = {A}
    
    R is not in BCNF.
    Decomposition R1 = (A, B), R2 = (B, C) 

    R1 and R2 are in BCNF, Lossless-join decomposition, Dependency preserving.
    Each Functional Dependency specified in F either appears directly in one of the relations in the decomposition.
    It is not necessary that all dependencies from the relation R appear in some relation Ri.
    It is sufficient that the union of the dependencies on all the relations Ri be equivalent to the dependencies on R.

  3. Non Additive Join Property:
    Another property of decomposition is that D should possess is the Non Additive Join Property, which ensures that no spurious tuples are generated when a NATURAL JOIN operation is applied to the relations resulting from the decomposition.


  4. No redundancy:
    Decomposition is used to eliminate some of the problems of bad design like anomalies, inconsistencies, and redundancy.If the relation has no proper decomposition, then it may lead to problems like loss of information.


  5. Lossless Join:
    Lossless join property is a feature of decomposition supported by normalization. It is the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations.

    For example:
    R : relation, F : set of functional dependencies on R,
    X, Y : decomposition of R,
    A decomposition {R1, R2, …, Rn} of a relation R is called a lossless decomposition for R if the natural join of R1, R2, …, Rn produces exactly the relation R.

    A decomposition is lossless if we can recover:
    R(A, B, C) -> Decompose -> R1(A, B) R2(A, C) -> Recover -> R’(A, B, C)
    Thus, R’ = R
    Decomposition is lossless if:
    X intersection Y -> X, that is: all attributes common to both X and Y functionally determine ALL the attributes in X.
    X intersection Y -> Y, that is: all attributes common to both X and Y functionally determine ALL the attributes in Y
    If X intersection Y forms a superkey of either X or Y, the decomposition of R is a lossless decomposition.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.