Lossless Decomposition - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Lossless Decomposition - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Lossless Decomposition

Decomposition

 Goal : Eliminate redundancy by decomposing a

relation into several relations in a higher normal form.

 It is important to check that a decomposition does

not introduce new problems.

-- A good decomposition allows us to recover the

original relation

Problems with Decomposition

• Some queries become more expensive
• Given instances of the decomposed relations,

we may not be able to reconstruct the corresponding instance of the original relation – information loss.

Certain Decomposition May Cause Problem

Name Price Category Gizmo 19.99 Gadget OneClick 24.99 Camera DoubleClick 29.99 Camera

Price Category 19.99 Gadget 24.99 Camera 29.99 Camera

Name Category Gizmo Gadget OneClick Camera DoubleClick Camera

R

R1 R

Lossy Decomposition

Employee Project Branch

Brown Mars L.A.

Green Jupiter San Jose

Green Venus San Jose

Hoskins Saturn San Jose

Hoskins Venus San Jose

Functional dependencies:

Employee Branch, Project Branch

T

Lossy Decomposition

Decomposition of the previous relation

Employee Branch

Brown L.A

Green San Jose

Hoskins San Jose

Project Branch Mars L.A. Jupiter San Jose Saturn San Jose Venus San Jose

T1 T

Lossless Decompostion

A decomposition is lossless if we can recover:

R(A, B, C)

Decompose

R1(A, B) R2(A, C)

Recover

R’(A, B, C)

What is lossless decomposition?

The decomposition of a relation R on X 1 and X 2

is lossless if the join of the projections of R on X 1 and X 2 is equal to R itself (that is, not containing false tuples).

Armstrong’s Axioms

X, Y, Z are sets of attributes

1. Reflexivity: If X ⊇ Y, then X → Y

2. Augmentation: If X → Y, then XZ → YZ for any Z

3. Transitivity: If X → Y and Y → Z, then

X → Z

4. Union: If X → Y and X → Z, then X → YZ

5. Decomposition: If X → YZ, then X → Y and

X → Z

Example of Lossless Decomposition

GIVEN:

LENDINGSCHEME=(BRANCHNAME, ASSETS, BRANCHCITY,

LOANNUMBER, CUSTOMERNAME, AMOUNT)

REQUIRED FD'S:

 BRANCHNAME  ASSETS BRANCHCITY

 LOANNUMBER  AMOUNT BRANCHNAME

DECOMPOSE LENDINGSCHEME INTO:

1. BRANCHSCHEME=(BRANCHNAME, ASSETS, BRANCHCITY)

2. BORROWSCHEME=(BRANCHNAME, LOANNUMBER,

CUSTOMERNAME, AMOUNT)

Example 2

GIVEN:

BORROWSCHEME=(BRANCHNAME, LOANNUMBER,

CUSTOMERNAME, AMOUNT)

REQUIRED FD'S:

 LOANNUMBER  AMOUNT BRANCHNAME

DECOMPOSE LENDINGSCHEME INTO:

1. LOAN-INFO-SCHEME=(BRANCHNAME, LOANNUMBER,

AMOUNT)

2. CUSTOMER-LOAN-SCHEME=(LOANNUMBER,

CUSTOMERNAME)

Example 2 (con’t)

SHOW THAT THE DECOMPOSITION IS A LOSSLESS

DECOMPOSITION

1. USE AUGMENTATION RULE ON FD TO OBTAIN:

LOANNUMBER  LOANNUMBER AMOUNT

BRANCHNAME

2. INTERSECTION OF LOAN-INFO-SCHEME AND CUSTOMER-

LOAN-SCHEME IS LOANNUMBER

3. LOANNUMBER  LOAN-INFO-SCHEME

4. SO, INITIAL DECOMPOSITION IS A LOSSLESS

Example

R1 (A1, A2, A3, A5)

R2 (A1, A3, A4)

R3 (A4, A5)

FD1: A1  A3 A

FD2: A5  A1 A

FD3: A3 A4  A

A1 A2 A3 A4 A

R1 a(1) a(2) a(3) b(1,4) a(5)

R2 a(1) b(2,2) a(3) a(4) b(2,5)

R3 b(3,1) b(3,2) b(3,3) a(4) a(5)

Example (con’t)