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:

 Lossless                              and        dependency-preserving

• Given:

 R is a relation ℉    is a set of functional dependencies on R

• The Synthesis Algorithm:

1. Find a minimal cover   min   of

2. For each left-hand side X of a functional dependency in   min:

 Create a relation schema using X+

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

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

4. If a key (say: (A,B,C)) of R is not found in any relation , then:

 Add a trivial relation RK = (A, B, C) that consists of the key attributes of R

(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:

 R1 = ( A, B, C, D, E ) // Key: (A,B,C) R2 = ( E, B, C, G ) // Key: (E) R3 = ( F, A, H ) // Key: (F))

• Step 3: make a relation with any missing attributes

 There are no missing attributes So don't need to add any relation

• Step 4: is any of the keys of R missing in R1 R2 and R3 ?

• 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:

 F

• 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:

 FE and FBC

• Keys:

 FE and FBC

Relations:

 R1 = ( A, B, C, D, E ) R2 = ( E, B, C, G ) R3 = ( F, A, H )

• FE is not in R1 R2 and R3

• FBC is not in R1 R2 and R3

• 3NF decomposition that preserves the functional dependencies:

 R1 = (A, B, C, D, E) R2 = (E, B, C, G) R3 = (F, A, H) R4 = (E, F) R5 = (B, C, F)

• Verify the decomposition preserves all the functional dependencies:

 R1 = (A, B, C, D, E) R2 = (E, B, C, G) R3 = (F, A, H) R4 = (E, F) R5 = (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:

 FE and FBC

• Recall the definition of 3NF:

• If X → A then:

 X is super key, or A is key attribute

• 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:

 The decomposition that result from the Synthesis Algorithm is guaranteed to be lossless

No proof... - See Chapter 11 of text book