数据库关系模式:求属性集X的闭包,判断依赖关系是否逻辑蕴含 ,判断无损连接分解
结构说明
[“A”,“D”]表示一个依赖关系:A→B
属性集X的闭包,判断依赖关系是否逻辑蕴含
F__ = [["A","D"],["AB","E"],["BI","E"],["CD","I"],["E","C"]] # 依赖集,例如其中["A","D"]表示属性A决定属性BD
X__ = "AE"
F1 = [["AB","C"],["B","D"],["CD","E"],["CE","GH"],["G","A"]]
def member(FD, F):
b = instr(FD[1], closure(FD[0], F))
return b
def closure(x, F):
"""
:param x: 属性
:param F: 依赖集
:return: 属性X的闭包
"""
result = x
var = ""
while not result == var:
var = result
for i in range(len(F)):
if instr(F[i][0], result):
str = F[i][1]
result = union(str, result)
return result
def instr(str1, str2):
"""
:param str1: 字符串1
:param str2: 字符串2
:return: 判断字符串1的每个字符都在字符串2中,若都在,返回true
"""
b = True
for j in range(len(str1)):
if str1[j] not in str2:
b = False
break
return b
def union(str, result):
"""
:param str: 字符串
:param result: 字符串
:return: 求字符串1与字符串2的字符的并集
"""
for j in range(len(str)):
if str[j] in result:
continue
else:
result = result + str[j]
return result
print(closure("AB",F1)) # 求属性集AB的闭包,F1为依赖集
print(member(["AB","G"],F1)) # 判断依赖关系["AB","G"]是否逻辑蕴涵于F1
判断无损连接分解
如关系R的依赖集F={A→C,B→C,C→D,DE→C,CE→A};分解ρ={AD,AB,BE,CDE,AE}
构造初始表:以属性X:A,B,C,D,E为行,分解的属性集Y:AD,AB,BE,CDE,AE为列。构造原则是:如果第i行j列满足Xj∈Yi,则值为aj,否则值为bij
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23 | b24 | b25 |
BE | b31 | a2 | b33 | b34 | a5 |
CDE | b41 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53 | b54 | a5 |
修改表:遍历依赖集中的每个依赖
对A→C,根据依赖的定义,A的值能确定C的值,因此,A中相同的值所决定的C的值也应该相同,将C对应的位置改为相同,如果C列对应位置没有a则以第一个值为修改值,否则修改为a。
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23→b13 | b24 | b25 |
BE | b31 | a2 | b33 | b34 | a5 |
CDE | b41 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53→b13 | b54 | a5 |
同理,对B→C
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23→b13 | b24 | b25 |
BE | b31 | a2 | b33→b13 | b34 | a5 |
CDE | b41 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53→b13 | b54 | a5 |
对C→D
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23→b13 | b24→a4 | b25 |
BE | b31 | a2 | b33→b13 | b34→a4 | a5 |
CDE | b41 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53→b13 | b54→a4 | a5 |
对DE→C
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23→b13 | b24→a4 | b25 |
BE | b31 | a2 | b33→b13→a3 | b34→a4 | a5 |
CDE | b41 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53→b13→a3 | b54→a4 | a5 |
对CE→A
A | B | C | D | E | |
---|---|---|---|---|---|
AD | a1 | b12 | b13 | a4 | b15 |
AB | a1 | a2 | b23→b13 | b24→a4 | b25 |
BE | b31→a1 | a2 | b33→b13→a3 | b34→a4 | a5 |
CDE | b41→a1 | b42 | a3 | a4 | a5 |
AE | a1 | b52 | b53→b13→a3 | b54→a4 | a5 |
至此,修改完毕,如果存在全为a的行,如本例第三行,则该分解为无损分解。