关系代数除法与其对应的 SQL 语句
用下表来解释内容。
sc 表:
sno | cno | grade |
---|---|---|
201215121 | 1 | 92 |
201215121 | 2 | 85 |
201215121 | 3 | 88 |
201215122 | 2 | 90 |
201215122 | 3 | 80 |
201215123 | 3 | 90 |
基本形式
可用
R
(
X
,
Y
)
,
S
(
Y
,
Z
)
R(X,Y), S(Y,Z)
R(X,Y),S(Y,Z) 来表示除法中的一般情况。
这是因为,假如
R
(
x
1
,
…
x
n
,
y
1
,
…
,
y
m
)
,
S
(
y
1
,
…
,
y
m
,
z
1
,
…
,
z
k
)
R(x_1,\dots x_n,y_1,\dots,y_m),S(y_1,\dots ,y_m,z_1,\dots,z_k)
R(x1,…xn,y1,…,ym),S(y1,…,ym,z1,…,zk)
令
X
=
(
x
1
,
…
,
x
n
)
,
Y
=
(
y
1
,
…
,
y
m
)
,
Z
=
(
z
1
,
…
,
z
k
)
X=(x_1,\dots,x_n),Y=(y_1,\dots,y_m),Z=(z_1,\dots,z_k)
X=(x1,…,xn),Y=(y1,…,ym),Z=(z1,…,zk)
这样一来,
X
X
X 表示在
R
R
R 中却不在
S
S
S 中的列
Y
Y
Y 表示既在
R
R
R 中也在
S
S
S 中的列
Z
Z
Z 表示在
S
S
S 中却不在
R
R
R 中的列
可以将关系
R
R
R 看成只有
X
,
Y
X,Y
X,Y 两列,
S
S
S 亦然,而例如判定
R
R
R 中两个元组的
Y
Y
Y 属性是否相等,则是判定两个元组的
Y
Y
Y 属性的每一个分量都相同。
象集
对关系代数除法的解释,需要借助一个叫做象集的概念。
t
∈
R
t\in R
t∈R 表示 t 是 R 的一个元组
t
[
X
i
]
t[X_i]
t[Xi] 表示元组
t
t
t 中相应于属性
X
X
X 的一个分量(
t
.
X
i
t.X_i
t.Xi 吧大概)
设
x
∈
X
x\in X
x∈X,则
x
x
x 在 R 中的象集为:
Y
x
=
{
t
[
Y
]
∣
t
∈
R
∧
t
[
X
]
=
x
}
Y_x=\{t[Y]|t\in R\wedge t[X]=x\}
Yx={t[Y]∣t∈R∧t[X]=x}
例如在 sc 表中,201215121 的象集就是
cno grade 1 92 2 85 3 88
和 group by 有相似之处
关系代数除法的定义
除法的定义式为(设除法的结果是关系 P ( X ) P(X) P(X)):
P ( X ) = R ÷ S = { t r [ X ] ∣ t r ∈ R ∧ Π Y ( S ) ⊆ Y t r [ X ] } P(X)=R\div S=\{t_r[X]|t_r\in R\wedge\Pi_Y(S)\subseteq Y_{t_r[X]}\} P(X)=R÷S={tr[X]∣tr∈R∧ΠY(S)⊆Ytr[X]}
其中, Y t r [ X ] = { t [ Y ] ∣ t ∈ R ∧ t [ X ] = t r [ X ] } Y_{t_r[X]}=\{t[Y]|t\in R\wedge t[X]=t_r[X]\} Ytr[X]={t[Y]∣t∈R∧t[X]=tr[X]},也就是说,是 t r [ X ] t_r[X] tr[X] 在 R R R 中的象集。
我这样解释除法:
- 遍历 R R R 中的每一个元组时,考察其 X X X 分量
- 如果如果 S S S 中的整个 Y Y Y 行是一个元组中的 X X X 分量在 R R R 中的象集的子集,则输出这个 X X X
- 对所有输出的 X X X 进行去重。
按除法的定义,可知:
- ( R ÷ S ) × S = R (R\div S)\times S=R (R÷S)×S=R 不一定成立
- ( R × S ) ÷ S = R (R\times S)\div S=R (R×S)÷S=R 成立
笛卡尔积的逆运算是这种除法,但这种除法的逆运算不是笛卡尔积,毋宁说想找到这个除法的逆运算是困难的。
例如:
给定这样一个表
S
S
S:
cno |
---|
1 |
2 |
考虑
Π
s
n
o
,
c
n
o
(
s
c
)
÷
S
\Pi_{sno,cno}(sc)\div S
Πsno,cno(sc)÷S
考虑
Π
s
n
o
,
c
n
o
(
s
c
)
\Pi_{sno,cno}(sc)
Πsno,cno(sc) 的第一行,201215121 在
Π
s
n
o
,
c
n
o
(
s
c
)
\Pi_{sno,cno}(sc)
Πsno,cno(sc) 中的象集为:
cno |
---|
1 |
2 |
3 |
显然,
S
S
S 是这个象集的子集,因此第一行中的
s
n
o
sno
sno 会输出,第二、三行亦然。
但是第 4 ~ 6 行则不会输出,因为
S
S
S 不是这几行中的
s
n
o
sno
sno 的象集的子集。
因此,
Π
s
n
o
,
c
n
o
(
s
c
)
÷
S
\Pi_{sno,cno}(sc)\div S
Πsno,cno(sc)÷S 的结果就是:只有一列
s
n
o
sno
sno,只有一行 201215121 的表。
而显然,
(
Π
s
n
o
,
c
n
o
(
s
c
)
÷
S
)
×
S
(\Pi_{sno,cno}(sc)\div S)\times S
(Πsno,cno(sc)÷S)×S 只会有两行,因此不等于
Π
s
n
o
,
c
n
o
(
s
c
)
\Pi_{sno,cno}(sc)
Πsno,cno(sc)
Mysql 实现关系代数除法
Mysql 并不提供直接实现除法的语法,但是可以通过现有语法实现和除法相同的效果。
预计使用 mysql 的 where 语句+条件表达式的形式来实现除法。
首先考察如何理解 mysql 中的 where 语句:
- 遍历这个表中的每一个元组
- 判定遍历到这个元组时 where 语句是否为真
- 如果为真,则输出这个元组
再来考察除法的定义式与我对除法的解释:
P ( X ) = R ÷ S = { t r [ X ] ∣ t r ∈ R ∧ Π Y ( S ) ⊆ { t [ Y ] ∣ t ∈ R ∧ t [ X ] = t r [ X ] } } P(X)=R\div S=\{t_r[X]|t_r\in R\wedge\Pi_Y(S)\subseteq \{t[Y]|t\in R\wedge t[X]=t_r[X]\}\} P(X)=R÷S={tr[X]∣tr∈R∧ΠY(S)⊆{t[Y]∣t∈R∧t[X]=tr[X]}}
- 遍历 R R R 中的每一个元组时,考察其 X X X 分量
- 如果如果 S S S 中的整个 Y Y Y 行是一个元组中的 X X X 分量在 R R R 中的象集的子集,则输出这个 X X X
- 对所有输出的 X X X 进行去重。
我的解释提到了遍历的概念且解释了每次遍历时的判定方法
现尝试给出某一
t
r
∈
R
t_r\in R
tr∈R 满足条件的逻辑表达式:
∀
y
,
y
∈
Π
Y
(
S
)
→
y
∈
{
t
[
Y
]
∣
t
∈
R
(
X
,
Y
)
∧
t
[
X
]
=
t
r
[
X
]
}
}
∀
y
,
y
∈
Π
Y
(
S
)
→
(
∃
t
,
t
∈
R
∧
t
[
X
]
=
t
r
[
X
]
∧
y
=
t
[
Y
]
)
}
¬
∃
y
,
¬
(
y
∈
Π
Y
(
S
)
→
(
∃
t
,
t
∈
R
∧
t
[
X
]
=
t
r
[
X
]
∧
y
=
t
[
Y
]
)
}
)
¬
∃
y
,
¬
(
y
∉
Π
Y
(
S
)
∨
(
∃
t
,
t
∈
R
∧
t
[
X
]
=
t
r
[
X
]
∧
y
=
t
[
Y
]
)
}
)
¬
∃
y
,
y
∈
Π
Y
(
S
)
∧
(
¬
∃
t
,
t
∈
R
∧
t
[
X
]
=
t
r
[
X
]
∧
y
=
t
[
Y
]
)
}
\forall y,y\in\Pi_Y(S)\to y\in\{t[Y]|t\in R(X,Y)\wedge t[X]=t_r[X]\}\}\\ \forall y,y\in\Pi_Y(S)\to (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\}\\ \neg\exist y,\neg(y\in\Pi_Y(S)\to (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\})\\ \neg\exist y,\neg(y\not\in\Pi_Y(S)\vee (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\})\\ \neg\exist y,y\in\Pi_Y(S)\wedge (\neg\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\}
∀y,y∈ΠY(S)→y∈{t[Y]∣t∈R(X,Y)∧t[X]=tr[X]}}∀y,y∈ΠY(S)→(∃t,t∈R∧t[X]=tr[X]∧y=t[Y])}¬∃y,¬(y∈ΠY(S)→(∃t,t∈R∧t[X]=tr[X]∧y=t[Y])})¬∃y,¬(y∈ΠY(S)∨(∃t,t∈R∧t[X]=tr[X]∧y=t[Y])})¬∃y,y∈ΠY(S)∧(¬∃t,t∈R∧t[X]=tr[X]∧y=t[Y])}
这样一来,可以用这样的 mysql 语句实现
R
(
X
,
Y
)
÷
S
(
Y
,
Z
)
R(X,Y)\div S(Y,Z)
R(X,Y)÷S(Y,Z)
select A.X from R as A
where not exists
select * from R as B(
where B.Y in (
select S.Y from S
)and not exists(
select * from R as C
where A.X=C.X and B.y=C.Y
)
)
值得一提的是,这里的 B.Y in(...)
可以替换成别的逻辑表达式,表示
B
.
Y
B.Y
B.Y 应当满足一个什么条件,或者说在哪个集合里。
测试:
尝试使用 Mysql 实现
Π
s
n
o
,
c
n
o
(
s
c
)
÷
S
\Pi_{sno,cno}(sc)\div S
Πsno,cno(sc)÷S
select distinct A.sno from (select sno, cno from sc) as A
where not exists(
select * from (select sno, cno from sc) as B
where B.cno in ('1', '2') and not exists(
select * from (select sno, cno from sc) as C
where A.sno = C.sno and B.cno = C.cno
)
)
当然,这是严格按照除法定义写出的,也可以直接写成:
select distinct A.sno from sc as A
where not exists(
select * from sc as B
where B.cno in ('1', '2') and not exists(
select * from sc as C
where A.sno = C.sno and B.cno = C.cno
)
)
这是因为在实际上的判断语句中并没有涉及到 sc 中 sno 和 cno 以外的列。
两个 mysql 语句输出均符合预期:
+-----------+
| sno |
+-----------+
| 201215121 |
+-----------+
例题
我开始关注的关系代数除法是因为这样一道题:求至少选修了学号为S1所选修的全部课程的学生学号。
给出的示例表就是这道题所用的表。
答案是:
set @S1 = "201215122";
select distinct sno from sc as x
where not EXISTS(
select * from sc as y
where y.sno=@S1 and not exists(
select * from sc as z
where z.sno=x.sno and z.cno=y.cno
)
);
观察题意,只需选出 S1 所选的全部 cno,与 sc 表相除即可得到答案。
这里判断 y.sno=@S1
就是判定
y
y
y 元组在这个规定好的集合内,剩下的就是除法的 mysql 语句写法。
其他解法
select sno from
(
select a.sno, COUNT(b.cno) as c from
(
select sc.cno from sc
where sc.sno = @S1
)as b
join sc as a
where a.cno = b.cno
group by sno
)as ttt
join (
select MAX(ttt2.c) as ma from (
select a.sno, COUNT(b.cno) as c from
(
select sc.cno from sc
where sc.sno = @S1
)as b
join sc as a
where a.cno = b.cno
group by sno
) as ttt2
) as af
where c = ma and sno <> @S1;
计算每个人的 cno 的集合与 S1 的 cno 的交集,如果这个交集的元素个数与 S1 的 cno 数量相同,说明 S1 的 cno 是这个象集的子集,满足条件,可输出。也是实现除法的另一种方法,你可以考虑一下这种思路来提出一种新的用 sql 语句实现除法的方法。