CS457 Syllabus & Progress

The synthesis algorithm (for dependency-preserving decompositions)

The Synthesis Algorithm to obtain 3NF with Preservation of Functional Dependencies

Synthesis Algorithm:

Synthesis algorithm = algorithm to obtain a decomposition of a relation into 3NF that is:

Given:

The Synthesis Algorithm:

Find a minimal cover ℉_min of ℉
For each left-hand side X of a functional dependency in ℉_min:

Place any remaining attributes that has not been placed in a relations in step 2 in a single relation:

R_non-placed = ( U, V ) // U, V = attrs not found in // relations created in Step 2

If a key (say: (A,B,C)) of R is not found in any relation , then:
(Often, this trivial relation is useless , you may be able to omit it)

Example of the Synthesis Algorithm

Relation and its functional dependencies:

R = (A, B, C, D, E, F, G, H) ℉ = { ABC → DEG E → BCG F → AH }

Step 1: Find a minimal cover for ℉.

Initialization:

℉_min = ℉ = { ABC → DEG E → BCG F → AH }

Break the RHS up into single attributes:

℉_min = { ABC → D ABC → E ABC → G E → B E → C E → G F → A F → H }

Test for minimality of LHS ---- (Only test for LHS with ≥ 2 attributes)

1. Testing ABC → D 1. Compute: (ABC - A)⁺ = BC⁺ = BC (Initialization) Done D ∈ (ABC - A)⁺ ? No. ==> A is necessary 2. Compute: (ABC - B)⁺ = AC⁺ = AC (Initialization) Done D ∈ (ABC - B)⁺ ? No. ==> B is necessary 3. Compute: (ABC - C)⁺ = BC⁺ = BC (Initialization) Done D ∈ (ABC - C)⁺ ? No. ==> C is necessary Conclusion: ABC → D is minimal
2. Testing ABC → E 1. Compute: (ABC - A)⁺ = BC⁺ = BC (Initialization) Done E ∈ (ABC - A)⁺ ? No. ==> A is necessary 2. Compute: (ABC - B)⁺ = AC⁺ = AC (Initialization) Done E ∈ (ABC - B)⁺ ? No. ==> B is necessary 3. Compute: (ABC - C)⁺ = BC⁺ = BC (Initialization) Done E ∈ (ABC - C)⁺ ? No. ==> C is necessary Conclusion: ABC → E is minimal
3. Testing ABC → G (Similar result as above.....) Conclusion: ABC → G is minimal

Result:

℉_min = { ABC → D ABC → E ABC → G E → B E → C E → G F → A F → H }

Test for minimality of the RHS:

1. Testing ABC → D Compute: ABC⁺ using: ℉_min = { ~~ABC → D~~ ABC → E ABC → G E → B E → C E → G F → A F → H } ABC⁺ = ABC (Initialization) = ABCE (ABC → E) = ABCEG (ABC → G) Done D ∈ ABC⁺ ??? No. ===> ABC → D is necessary
2. Testing ABC → E Compute: ABC⁺ using: ℉_min = { ABC → D ~~ABC → E~~ ABC → G E → B E → C E → G F → A F → H } ABC⁺ = ABC (Initialization) = ABCD (ABC → D) = ABCDG (ABC → G) Done E ∈ ABC⁺ ??? No. ===> ABC → E is necessary
3. Testing ABC → G Compute: ABC⁺ using: ℉_min = { ABC → D ABC → E ~~ABC → G~~ E → B E → C E → G F → A F → H } ABC⁺ = ABC (Initialization) = ABCD (ABC → D) = ABCDE (ABC → E) = ABCDEG (E → G) Done G ∈ ABC⁺ ??? Yes !! ===> ABC → G is NOT necessary
Update: ℉_min = { ABC → D ABC → E E → B E → C E → G F → A F → H }
4. Testing E → B Compute: E⁺ using: ℉_min = { ABC → D ABC → E ~~E → B~~ E → C E → G F → A F → H } E⁺ = E (Initialization) = EC (E → C) = ECG (E → G) Done B ∈ E⁺ ??? No. ===> E → B is necessary
5. Testing E → C Compute: E⁺ using: ℉_min = { ABC → D ABC → E E → B ~~E → C~~ E → G F → A F → H } E⁺ = E (Initialization) = EB (E → B) = EBG (E → G) Done C ∈ E⁺ ??? No. ===> E → C is necessary
6. Testing E → G Compute: E⁺ using: ℉_min = { ABC → D ABC → E E → B E → C ~~E → G~~ F → A F → H } E⁺ = E (Initialization) = EB (E → B) = EBC (E → C) Done G ∈ E⁺ ??? No. ===> E → G is necessary
7. Testing F → A Compute: F⁺ using: ℉_min = { ABC → D ABC → E E → B E → C E → G ~~F → A~~ F → H } F⁺ = F (Initialization) = FH (F → H) Done A ∈ F⁺ ??? No. ===> F → A is necessary
8. Testing F → H Compute: F⁺ using: ℉_min = { ABC → D ABC → E E → B E → C E → G F → A ~~F → H~~ } F⁺ = F (Initialization) = FA (F → A) Done H ∈ F⁺ ??? No. ===> F → H is necessary

Result:

℉_min = { ABC → D ABC → E E → B E → C E → G F → A F → H }

Collect similar LHS:

℉_min = { ABC → DE E → BCG F → AH }

Step 2: make one relation with each functional dependency:

R₁ = ( A, B, C, D, E ) // Key: (A,B,C) R₂ = ( E, B, C, G ) // Key: (E) R₃ = ( F, A, H ) // Key: (F))

Step 3: make a relation with any missing attributes

Step 4: is any of the keys of R missing in R₁ R₂ and R₃ ?

Find the keys in R:

Relation and its functional dependencies:

R = (A, B, C, D, E, F, G, H) ℉ = { ABC → DE E → BCG F → AH }

Find all the keys:

Necessary attribute to include in a key:

Check for sufficiency:

F⁺ = F (Initialization) = AFH (F → AH) Done

===> insufficient to be key.....

Augment F to a key:

(In the interest of time, I will give you the answers. You must try: FA⁺, FB⁺, ..., FH⁺, FAB⁺, ... )

FE⁺ = FE (Initialization) = BCEFG (E → BCG) = ABCEFGH (F → AH) = ABCDEFGH (ABC → DE) Done FBC⁺ = FBC (Initialization) = ABCFH (F → AH) = ABCDEFH (ABC → DE) = ABCDEFGH (E → BCG) Done

Keys:

Add missing trivial relations:

Keys:

FE and FBC

Relations:

R₁ = ( A, B, C, D, E ) R₂ = ( E, B, C, G ) R₃ = ( F, A, H )

FE is not in R₁ R₂ and R₃
FBC is not in R₁ R₂ and R₃

3NF decomposition that preserves the functional dependencies:

Verify the decomposition preserves all the functional dependencies:

R₁ = (A, B, C, D, E) R₂ = (E, B, C, G) R₃ = (F, A, H) R₄ = (E, F) R₅ = (B, C, F) ℉ = { ABC → DEG E → BCG F → AH }

BTW: it's is also in 3NF....

Compare with the result of a naive decomposition

Relation and its functional dependencies:

R = (A, B, C, D, E, F, G, H) ℉ = { ABC → DEG E → BCG F → AH }

Recall that the keys of R were:
Recall the definition of 3NF:

Suppose we pick the following 3NF violation: F → AH

R = (A, B, C, D, E, F, G, H) Keys: FE and FBC ℉ = { ABC → DEG E → BCG F → AH } ==> F is not a superkey and AH are not key attributes

Decomposition:

R( A, B, C, D, E, F, G, H ) / \ / \ (F⁺ = FAH) / \ R1(F, A, H) R2(B, C, D, E, F, G)

Notice that:

We have lost the functional dependency ABC → DE in this decomposition step !!!

Postscript
- Lemma:
  No proof... - See Chapter 11 of text book