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.

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

Go back to Normalization – solved exercises page.