Advanced Database Management System - Tutorials and Notes: Define lossless-join decomposition with example

Saturday, 16 April 2016

Define lossless-join decomposition with example



Lossless Decomposition, Lossless-Join Decomposition, Define lossless decomposition, lossless join decomposition example, properties of lossless decomposition


Lossless Decomposition

Assume that a relation R with set of functional dependencies F. If R is decomposed into relations R1 and R2, then this decomposition is said to be lossless decomposition (or lossless-join decomposition) if and only if at least one of the following functional dependencies holds in the closure of set of functional dependencies F+;
R1 ∩ R2 R1
R1 ∩ R2 R2

R1 ∩ R2 gives you the attribute or set of attributes that is/are used in joining R1 and R2. The above functional dependencies ensure that the attributes involved in the natural join of R1 and R2 are candidate keys for at least one of the relations R1 and R2.
In other words, R1 ∩ R2 should be a determinant that determines all the attributes of one of relations R1 and R2.

Example:
Assume a relation Student_Grades with the attributes Reg_no, Name, Semester, and Grade. Let us suppose the following functional dependencies are held in Student_Grades table;
F = { Reg_no Name, Reg_No Semester Grade}

Key for Student_Grades will be (Reg_No, Semester), a composite primary key (whole primary key) [Note: find closureof attributes to identify keys]

Is Student_Grades in 2NF?

NO. The reason is the partial dependency Reg_No Name holds on Student_Grades.
The solution is to decompose Student_Grades. Let us decompose into the following relations (keys are underlined);
Student (Reg_No, Name)
SGrades (Reg_No, Semester, Grade)

Is this decomposition a lossless-join decomposition?

YES.
Student ∩ SGrades Student.
i.e., Reg_No Reg_No, Name
Reg_No is the common attribute in both relations and it determines all the attributes of one of the relations. Hence, the decomposition is lossless-join decomposition.

******************



2 comments:

  1. not able yo understand clearly....... plz help

    ReplyDelete
    Replies
    1. One can decompose a table into two or more tables. This decomposition simplifies the structure of the table (one big table to two or more small tables). But, when we need data that are stored in the decomposed tables, then the only solution is to join the decomposed tables into the original relation. Here, this join may get back all original records (lossless decomposition) or all records except few (lossy decomposition). To ensure lossless property, we need ensure either of the functional dependencies given above.
      If you are not clear, please write more specific doubt.

      Delete

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents