## Lossless Join Decomposition

__Question:__
Let R = {ssn, ename, pnumber, pname,
plocation, hours} and R is decomposed into three relations R1, R2, and R3 as
follows;

R1 = EMP = {ssn, ename}

R2 = PROJ = {pnumber, pname,
plocation}

R3 = WORKS_ON = {ssn, pnumber,
hours}

Assume that the following functional
dependencies are holding on relation R.

F = {ssn → ename; pnumber → {pname,
plocation}; {ssn, pnumber} → hours}.

**Find whether the decomposition into R1, R2, and R3 is lossless join decomposition or not**.

__Answer:__
In theory, if a relation R is
decomposed into relations R1 and R2 then the decomposition is lossless if
either of the following holds;

- (R
_{1}∩ R_{2}) → R_{1}

- (R
_{1}∩ R_{2}) → R_{2}

In our problem, if we apply
intersection between R1 and R2, we shall get nothing, that is, no attribute is
common between R1 and R2.

Hence, let us apply intersection
between R1 and R3. Now we shall get

**ssn**as result.
(R

_{1}∩ R_{3}) ⇒ ({ssn, ename} ∩ {ssn, pnumber, hours}) ⇒ {ssn}.
From the given set of functional
dependencies F, we understand that, ssn → ssn, ename. That is,

({

**ssn**, ename} ∩ {**ssn**, pnumber, hours}) → {ssn, ename} ⇒ {ssn} → {ssn, ename} ⇒**(R**._{1}∩ R_{3}) → R_{1}
Hence,

**the decomposition into R1 and R3 is lossless**.
Similarly,

**the decomposition into R2 and R3 is also lossless**.
({

**pnumber**, pname, plocation} ∩ {ssn,**pnumber**, hours}) → {pnumber, pname, plocation} ⇒**(R**._{2}∩ R_{3}) → R_{2}
So, we can conclude that
decomposition of R into R1, R2, and R3 is lossless join decomposition.

********

Go to Normalization solved exercises page

Go to Solved exercises in DBMS page

Normalization solved examples

normalization exercises solved

what is lossless decomposition

rules for lossless join decomposition

lossless decomposition example

how to find whether a decomposition is lossless or not

lossless join decomposition one more example

since r2 and r3 decompose, r3 and r1 decompose so by transitivity r2 and r1 must also be a lossless decomposition

ReplyDelete