数据库——无损将关系模型分解成高级范式的两种算法
算法1:Losslessly decompose a relation schema into a set of 3NF subschemas with FDs preserved
步骤1:给出F的规范覆盖Fc
例:
F={AC->A,C->B,B->A,C->D,C->A,AC->D,CB->BE}
F’={C->B,B->A,C->D,C->E}
Fc={C→BDE, B→A}
应该满足一下的3个要求:
(1) Fc is equivalent to F;
(2) Fc contains no derived FD, each FD contains no redundant attributes:
Delete trivial FD and FDs that can be derived by other FDs in F, delete redundant attributes from each FD, obtain F’
删除平凡函数依赖,例如AC->A
删除可以通过传递得到的函数依赖,如果有C->B,B->A和C->A,由于通过C->B,B->A可以推出C->A,所以删除C->A
删除可以推出的函数依赖,例如有C->D和AC->D,由于通过C->D可以推出AC->D,所以删除AC->D
将含有两个属性的函数依赖分解成一个属性的函数依赖,然后再将重复的函数依赖删除
(3) No two FDs in Fc that have the same determinants:
combine X→Y1 and X→Y2 into X→Y1Y2(Union rule)
合并左边属性相同的函数依赖,将C->B,C->D,C->E合并成C→BDE
可得如下图结论:
例:
算法2:无损分解成BCNF关系模式集
目标:给定R(U)和FD集F ,求R的无损分解:ρ={R1,…,Rk},满足: 每个Ri相对于FD集PRi(F) 都是BCNF (i=1,…,k)
步骤如下图:
例: