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

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

Decomposition

Undesirable Properties of Bad Design

• Redundancy, resulting in waste of space and complicated updates (inconsistencies.)
• Inability to represent certain information – ex Null values.
• Loss of information.

Decompositions

• There are careless, “bad” decompositions.
• There are three desirable properties:
  1. Lossless.
  2. Dependency preservation.
  3. Minimal redundancy.

Relation Decomposition

• One of the properties of bad design suggests to decompose a relation into smaller relations.
• Must achieve lossless-join decomposition.

Lossless Join Decomposition

• Definition:
  • Let { R1, R2 } be a decomposition of R (R1 U R2 = R); the decomposition is lossless if for every legal instance r of R: r = ΠR1(r) |X| ΠR2(r)

Testing Lossless Join

• Lossless join property is necessary if the decomposed relation is to be recovered from its decomposition.
• Let R be a schema and F be a set of FD’s on R, and α = (R1, R2) be a decomposition of R. Then α has a lossless join with respect to F iff R1 ∩ R2 -> R1 (or R1 - R2 ) or R2 ∩ R1 -> R2 (or R2 - R1 ) where such FD exist in Closure of F PS This is a sufficient condition, but not a necessary condition.

Another example

R = (City, Street, Zip) F = {CS -> Z, Z -> C}

R1 = (CZ) R2 = (SZ) R1 ∩ R2 = Z , R1 – R2 = (SZ) check Z -> C in F? Yes

Therefore, the decomposition to be (CZ) (SZ) is lossless join decomposition.

Why do we preserve the dependency?

• We would like to check easily that updates to the database do not result in illegal relations being created.
• It would be nice if our design allowed us to check updates without having to compute natural joins.

Test of Dependency Preservation

• If a decomposition is not dependency-preserving, some dependency is lost in the decomposition.
• One way to verify that a dependency is not lost is to take joins of two or more relations in the decomposition to get a relation that contains all of the attributes in the dependency under consideration and then check that the dependency holds on the result of the joins.

Test of Dependency Preservation II

• Find F - F ', the functional dependencies not checkable in one relation.
• See whether this set is obtainable from F ' by using Armstrong's Axioms.
• This should take a great deal less work, as we have (usually) just a few functional dependencies to work on.

Non-Dependency Preserving Example

• Consider relation ABCD, with FD’s:

A ->B, B ->C, C->D

• Decompose into two relations: ACD and BC.
• ACD supports the FD B ->C and implied FD A ->C.
• BC supports the FD B->C.
• However, no relation supports A ->B.

So the dependency is not preserved.

Minimal Redundancy

• In order to achieve the lack of redundancy, we do some decomposition which is represented by several normal forms.

Lossless Check Example

• Consider five attributes: ABCDE
• Three relations: ABC, AD, BDE
• FD’s: A ->BD, B ->E

A B C D E ABC a1 a2 a3 b14 b AD a1 b22 b23 a4 b BDE b21 a2 b33 a4 a

Lossless Check Example