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

## Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

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

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)

Is this decomposition a lossless-join decomposition?

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

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