Question

what do you mean by lossless-join decomposition, give an example of lossless join decomposition of:

R=(A,B,C) in R1=(A,B) and R2=(B,C) by drawing table?

this question its for fundimental database subject I need defferent answer to avoid high percentage of plegrisem

Answer 1

lossless join decomposition

Normalization is used in a relation to remove inconsistencies,redundancy etc.In a relational model ,a relation is not normalized it is normalized using normalization techniques,we use decomposition as one of normalization technique.

Lossless join decomposition is used for decomposition of a relation in normalization.

Consider a relation R in relational model,lossless join decomposes this relation R into R₁ and R₂ relations if       

R1  ⋈  R2 = R

( ⋈ is the symbol of natural join)

that is natural join of R₁ and  R₂ equals to the original relation R.

If a relation R decomposed into R₁ and R₂ using lossless join ,the following conditions hold.

1. Union of attributes in R₁ and R₂ is equal to the attributes in relation R.
2. Intersection of attributes in R₁ and R₂ is not null.
3. The intersection of R₁ and R₂ must be the key for atleast one relation R₁ or R₂

R=(A,B,C) the relation is decomposed in to  R₁=(A,B) and R₂=(B,C)

here, attribute(R₁)   attribute(R₂ ) = (A,B) ( B,C) = (A,B,C)=R

attribute(R₁)  ∩ attribute(R₂ ) = (A,B) ∩ ( B,C) =(B)

  attribute(R₁)  ∩ attribute(R₂ )=(B)= key of relation R₂

The all conditions hold ,then the decomposition of relation R is lossless join decomposition.

Example

consider a relation in student database with attributes student_id,subject_name and subject_teacher. Each teacher teaches only one subject and for each subject have one or more students.

• R(student_id,subject name,subject_teacher)

  

student_id	subject _name	subject_teacher
1500001	Computer Science	Daisy Vargheese
1500002	English	Deepika M
1500003	Mathematics	Febin John
1500004	Hindi	Franklin K
1500005	History	Jorge K Jose
1500006	Computer Science	Daisy Vargheese

  

In this relation the subject_teacher attribute depends the subject_name attribute not the student_id .So here to remove this problem we decompose the relation into two.

R₁(student_id(PK),subject_name)

R₂(subject_name(PK),subject_teacher)

so the relations R₁ and R₂ are follows,

• R₁(student_id(PK),subject_name)

student_id	subject_name
1500001	Computer Science
1500002	English
1500003	Mathematics
1500004	Hindi
1500005	History
1500006	Computer Science

• R₂(subject_name(PK),subject_teacher)

subject _name	subject_teacher
Computer Science	Daisy Vargheese
English	Deepika M
Mathematics	Febin John
Hindi	Franklin K
History	Jorge K Jose

The natural join of the R₁ and R₂ ,we get the relation R .So the decomposition is lossless join.

• R₁(student_id,subject_name)    R₂(subject_name,subject_teacher)

= R(student_id,subject_name,subject_teacher)

• R₁(student_id,subject_name)  ∩   R₂(subject_name,subject_teacher) = R(student_name)​​​​​​​

​​​​​​​

• R(student_name) is the key for relation  R₂

​​​​​​​

• R₁(student_id(PK),subject_name) ⋈  R₂(subject_name(PK),subject_teacher)