Question

1.Given the schema R= ( Name, ZipCode, AverageIncome) , where the AverageIncome represents the average income of the people living in the area of ZipCode. Assume the following dependencies:

Name is a key, i.e. Name→ ZipCode, AverageIncome

ZipCode→ AverageIncome

i. Determine whether the decomposition of R into R1= ( Name, ZipCode) and R2=( ZipCode, AverageIncome) is lossless-join decomposition or not. Show your work.

ii. Determine whether the decomposition of R into R1= ( Name, AverageIncome) and R2=( ZipCode, AverageIncome) is lossless-join decomposition or not. Show your work.

Answer 1

Let for simplicity Name = N, ZipCode = Z , AverageIncome = A

So,relation R(N,Z,A)

Functional dependency:-

N->ZA

Z->A

Condition for decomposition to be lossless:-

If a relation R is deconposed into R1 and R2 then When R1 joins R2 it must result in R.To make sure that this happen there must be atleast one attribute in common between R1 and R2 and then that attribute(or set of attributes) must be a key in atleast one relation.

1).R1(N,Z) , R2(Z,A)

R1(N,Z)

N->Z

Here, N is key

R2(Z,A)

Z->A

Here,Z is key

The attribute common between R1 and R2 is 'Z' and 'Z' is key in R2.so,when R1 joins R2 the result will be definitely R.

So,This decomposition is lossless-join decomposition.

2).R1(N,A), R2(Z,A)

R1(N,A)

N->A

Here, N is key.

R2(Z,A)

Z->A

Here, Z is key.

The attribute common in R1 and R2 is 'A' but 'A' is not key in any of relation.so,when we R1 joins R2 the result is not exactly R because there will be some spurious tuples.

So,This decomposition is not lossless-join decomposition