##
**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**

**→***holds on*

**Name***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.

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

Go back to Normalization – solved exercises page.

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

ReplyDeleteOne 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.

DeleteIf you are not clear, please write more specific doubt.