Week 5 Third Normal Form (2)
.pptx
School
Nanyang Technological University**We aren't endorsed by this school
Course
CSC SC2207
Subject
Electrical Engineering
Date
Dec 17, 2024
Pages
17
Uploaded by MateRhinocerosMaster985
Third Normal Form (2)Week 5
Content-3NF Decomposition-Properties of 3NF
ExerciseS = {A → C, AC → D, AD → B}1.Transform the FDs to ensure that the right hand side of each FD has only one attribute2.See if any FD can be derived from the other FDs. Remove those FDs one by one3.Check if we can remove any attribute from the left hand side of any FD
Exercise (cont.)M = {A → C, AC → D, AD → B}2.See if any FD can be derived from the other FDs. Remove those FDs one by one.Try A → C first-If A → C is removed, then the ones left would be AC → D, AD → B-With the remaining FDs, we have {A}+= {A}-Since {A}+does not contain C, we know that A → C cannot be derived from the remaining FDs-Therefore, A → C cannot be removed
Exercise (cont.)M = {A → C, AC → D, AD → B}2.See if any FD can be derived from the other FDs. Remove those FDs one by one.Next, try AC → D-If AC → D is removed, then the ones left would be A → C, AD → B-With the remaining FDs, we have {AC}+= {ACD}-Since {AC}+does not contain D, we know that AC → D cannot be derived from the remaining FDs-Therefore, AC → D cannot be removed
Exercise (cont.)M = {A → C, AC → D, AD → B}2.See if any FD can be derived from the other FDs. Remove those FDs one by one.Next, try AD → B-If AD → B is removed, then the ones left would be A → C, AC → D-With the remaining FDs, we have {AD}+= {AD}-Since {AD}+does not contain B, we know that AD → B cannot be derived from the remaining FDs-Therefore, AD → B cannot be removed
Exercise (cont.)M = {A → C, AC → D, AD → B}3.Check if we can remove any attribute from the left hand side of any FDFirst, try to remove A from AC → D -It results in C → D-Can C → D be derived from M?-{C}+= {C} given M.-Since {C}+does not contain D, we know that C → D cannot be derived from M-Therefore, A cannot be removed from AC → D
Exercise (cont.)M = {A → C, AC → D, AD → B}3.Check if we can remove any attribute from the left hand side of any FDNext, try to remove C from AC → D-It results in A → D-Can A → D be derived from M?-{A}+= {ABCD} given M.-Since {A}+contains D, we know that A → D can be derived from M-Therefore, C can be removed from AC → DNew M = {A → C, A → D, AD → B}
Exercise (cont.)New M = {A → C, A → D, AD → B}3.Check if we can remove any attribute from the left hand side of any FDNext, try to remove A from AD → B-It results in D → B-Can D → B be derived from M?-{D}+= {D} given M.-Since {D}+does not contain B, we know that D → B cannot be derived from M-Therefore, A cannot be removed from AD → B
Exercise (cont.)M = {A → C, A → D, AD → B} 3.Check if we can remove any attribute from the left hand side of any FDNext, try to remove D from AD → B-It results in A → B-Can A → B be derived from M?-{A}+= {ABCD} given M.-Since {A}+contains B, we know that A → B can be derived from M-Therefore, D can be removed from AD → BNew M = {A → C, A → D, A → B}; done
3NF Decomposition Algorithm -Given:-Table R(A, B, C, D)-A minimal basis {A → B, A → C, C → D}-Step 1: Combine those FDs with the same left hand side-Result: {A → BC, C → D}-Step 2: For each FD, create a table that contains all attributes in the FD-Result: R1(A, B, C), R2(C, D)-Step 3: Remove redundant tables (if any)-Tricky issue: Sometimes we also need to add an additional table (see the next slide)
3NF Decomposition Algorithm (cont.)-Given-Table R(A, B, C, D)-A minimal basis {A → B, C → D}-Step 1: Combine those FDs with the same left hand side-Result: {A → B, C → D}-Step 2: For each FD, create a table that contains all attributes in the FD-Result: R1(A, B), R2(C, D) -Step 3: Remove redundant tables (if any)-Problem: R1and R2do not ensure lossless join-Solution: Add a table that contains a key of the original table R-Key of R: {A, C}-Additional table to add: R3(A, C)-Final result: R1(A, B), R2(C, D), R3(A, C)
3NF Decomposition Algorithm (cont.)-Given-Table R(A, B, C, D)-A minimal basis {A → B, C → D}-Step 1: Combine those FDs with the same left hand side-Result: {A → B, C → D}-Step 2: For each FD, create a table that contains all attributes in the FD-Result: R1(A, B), R2(C, D) -Step 3: If no table contain a key of the original table, add a table that contains a key of the original table-Result: R1(A, B), R2(C, D), R3(A, C)-Step 4: Remove redundant tables (if any)
Minimal Basis is not Always UniqueFor given set of FDs, its minimal basis may not be uniqueExample:-Given R(A, B, C) and {A → B, A → C, B → C, B → A, C → A, C → B}-Minimal basis 1: {A → B, B → C, C → A}-Minimal basis 2: {A → C, B → C, C → A, C → B}Different minimal basis may lead to different 3NF decompositions
BCNF vs. 3NFBCNF: For any non-trivial FD-its left hand side (lhs) is a superkey3NF: For any non-trivial FD-Either its lhs is a superkey-Or each attribute on its right hand side either appear in the lhs or in a keyObservation: BCNF is stricter than 3NFTherefore-A table that satisfies BCNF must satisfy 3NF, but not vice versa-A table that violates 3NF must violate BCNF, but not vice versa
BCNF vs. 3NF (cont.)-BCNF Decomposition:-Avoids insertion, deletion, and update anomalies-Eliminates most redundancies-But does not always preserve all FDs-3NF Decomposition:-Avoids insertion, deletion, and update anomalies-May lead to a bit more redundancy than BCNF-Always preserve all FDs-So which one to use?-A logical approach-Go for a BCNF decomposition first-If it preserves all FDs, then we are done-If not, then go for a 3NF decomposition instead
Why Does 3NF Preserve All FDs?Given: A table R, and a set S of FDsStep 1: Derive a minimal basis of SStep 2: In the minimal basis, combine the FDs whose left hand sides are the sameStep 3: Create a table for each FD remainedStep 4: If none of the tables contain a key of the original table R, create a table that contains a key of RStep 5: Remove redundant tablesRationale: Because of Step 3 (minimal basis preserves FDs; no redundant FDs)