CS457 Syllabus & Progress

### A sufficient condition for a lossless decomposition

• A sufficient condition for content(R1) * content(R2) = content(R)

• Lemma 2

Let R1 and R2 be a decomposition of R

If either   R1 ∩ R2 → R1   or   R1 ∩ R2 → R2 , then:

 content(R) = content(R1) * content(R2)

• Proof:

 Omitted for brevity.....

• Comment:

 I used the lemma number from the text book.... It's Lemma 2 in the book

• Example using Lemma 2

• Recall: the following decomposition of Employee1 was a "good" decomposition:

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) into: R1 (SSN, FName, LName) R2 (SSN, PNumber, PName, Hours) ```

• Result of the decomposition:

Original relation: Employee1
SSN FName LName PNumber PName Hours
111-11-1111 John Smith pj1 DBApplet 20
111-11-1111 John Smith pj2 WebServer 10
111-22-3333 Jane Doe pj1 DBApplet 5

Decomposition:

R1
SSN FName LName
111-11-1111 John Smith
111-22-3333 Jane Doe
R2
SSN PNumber PName Hours
111-11-1111 pj1 DBApplet 20
111-11-1111 pj2 WebServer 10
111-22-3333 pj1 DBApplet 5

• Reconstruction:

R1
SSN FName LName
111-11-1111 John Smith
111-22-3333 Jane Doe
* R2
SSN PNumber PName Hours
111-11-1111 pj1 DBApplet 20
111-11-1111 pj2 WebServer 10
111-22-3333 pj1 DBApplet 5
=

SSN FName LName PNumber PName Hours
111-11-1111 John Smith pj1 DBApplet 20
111-11-1111 John Smith pj2 WebServer 10
111-22-3333 Jane Doe pj1 DBApplet 5

• The reconstructed content is the same as the original content !!!

I.e., we have perserve the exact content - no more and no less

• The Lemma 2 tells us that this decomposition will be a lossless decomposition:

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) into: R1 (SSN, FName, LName) R2 (SSN, PNumber, PName, Hours) ```

This decomposition is lossless because:

 ``` R1 ∩ R2 = (SSN) and SSN → SSN, FName, LName = R1 !!! Therefore: R1 ∩ R2 → R1 Lemma 2 guarantees that the decomposition is lossless !!! ```