ch7 关系数据库设计理论PPT.ppt

Canonical Cover（正则覆盖） Sets of functional dependencies may have redundant dependencies that can be inferred from the others Eg: A ? C is redundant in: {A ? B, B ? C, A ? C} Parts of a functional dependency may be redundant E.g. on RHS: {A ? B, B ? C, A ? CD} can be simplified to {A ? B, B ? C, A ? D} E.g. on LHS: {A ? B, B ? C, AC ? D} can be simplified to {A ? B, B ? C, A ? D} Intuitively, a canonical cover of F is a “minimal” set of functional dependencies equivalent to F, with no redundant dependencies or having redundant parts of dependencies （最小覆盖） Extraneous Attributes无关属性 去掉F中该属性，不影响闭包结果 Example: Given F = {A ? C, AB ? C } B is extraneous in AB ? C because A ? C logically implies AB ? C. Example: Given F = {A ? C, AB ? CD} C is extraneous in AB ? CD since A ? C can be inferred even after deleting C Canonical Cover To compute a canonical cover for F:repeat Use the union rule to replace any dependencies in F ?1 ? ?1 and ?1 ? ?1 with ?1 ? ?1 ?2  Find a functional dependency ? ? ? with an  extraneous attribute either in ? or in ?  If an extraneous attribute is found, delete it from ? ? ? until F does not change Note: Union rule may become applicable after some extraneous attributes have been deleted, so it has to be re-applied Example of Computing a Canonical Cover R = (A, B, C)F = {A ? BC，B ? C， A ? B，AB ? C} Combine A ? BC and A ? B into A ? BC Set is now {A ? BC, B ? C, AB ? C} A is extraneous in AB ? C because B ? C logically implies AB ? C. Set is now {A ? BC, B ? C} C is extraneous in A ? BC since A ? BC is logically implied by A ? B and B ? C. The canonical cover is: A ? B B ? C Normalization Using Functional Dependencies When we decompose a relation schema R with a set of functional dependencies F into R1, R2,.., Rn we want Lossless-join decomposition: Otherwise decomposition would result in information loss. No redundancy: The relations Ri preferably should be in either Boyce-Codd Normal For