Transcript 從 ER 到 Logical Schema

資料庫查詢：關係數學
──Relational Algebra and Calculus
國立中央大學
資訊管理系
范錚強、楊書成
2008.12
中央大學。范錚強
1
關係數學：algebra & calculus
兩種正規的數學
Relational algebra
Operational
Procedural language (at a SET level)
一歩歩描述計算結果的步驟
Relational calculus
邏輯的一部份
Declarative
Non-procedural language
只描述想要的結果
SQL的數學表示法SQL的重要基礎
中央大學。范錚強
2
Relational algebra (1)
5個基本的運算子(operators)
Selection (σ) - select rows from relation
Projection (Π) - retain only wanted columns
from relation
Cross-product (X) - combine 2 relations
Set-difference (－) - tuples in r1, but not in r2
Union (∪) - tuples in r1 and/or in r2
中央大學。范錚強
3
Relational algebra (2)
Hypothesized relation schema
S (sid, sname, age)
C (cid, cname)
R (sid, cid)
語法範例
σage>20(S1)
Πsname, age(S1)
Πsname, age(σage>20(S1))
S1 ∪ S2, S1－ S2, S1 X R
中央大學。范錚強
4
Relational algebra (3)
sid
sname
s001 Henry
s003 σ Vivian
age>20(S1)
sid
s001
s002
s003
s004
sname
Henry
Jason
Vivian
Susan
S1
age
23
18
21
19
age
23
21
sname
age
Henry
23
Jason
18
Vivian
21
Susan
19
Πsname, age(S1)
sname
Henry
Vivian
age
23
21
Πsname, age(σage>20(S1))
中央大學。范錚強
5
Relational algebra (4)
sid
s001
s002
s003
s004
sname
Henry
Jason
Vivian
Susan
S1
age
23
18
21
19
sid
s001
s002
s003
s004
s006
sname
Henry
Jason
Vivian
Susan
Candy
S1
∪ S2
age
23
18
21
19
22
sid
s002
s004
s006
sname
Jason
Susan
Candy
age
18
19
22
sid sname
s001 Henry
s003 Vivian
age
23
21
S2
S1 － S2
中央大學。范錚強
6
Relational algebra (5)
sid
s001
s002
s003
s004
sname
Henry
Jason
Vivian
Susan
S1
sid
s002
s003
R
cid
c101
c082
age
23
18
21
19
(sid)
s001
s001
s002
s002
s003
s003
s004
s004
sname age
Henry
23
Henry
23
Jason
18
Jason
18
Vivian 21
Vivian 21
Susan
19
Susan S119
XR
中央大學。范錚強
(sid)
s002
s003
s002
s003
s002
s003
s002
s003
cid
c101
c082
c101
c082
c101
c082
c101
c082
7
Relational algebra (6)
使用union及set-difference時，兩個
relation必須是union-compatible
欄位數相同
相對應的欄位具有相同的資料型態
使用cross-product時，發生欄位名稱衝突
使用renaming(ρ)運算子
ρ(C(1sid1, 5sid2), S1 X R)
中央大學。范錚強
8
Relational algebra (7)
其他運算子
Intersection (∩) - tuples in r1 and in r2
S1∩S2 = S1－(S1－S2)
Condition join (∞c) - cross-product followed by a selection
S1S1.sid<R.sidR = σS1.sid<R.sid (S1X R)
Equijoin - special case of condition join
利用兩個relation中欄位相等之條件進行合併
相同欄位只會出現一次
S1∞S1.sid=R.sidR
Natural join
利用兩個relation中所有相同名稱欄位相等之條件進行合併
S1∞R
Division (／)
中央大學。范錚強
9
Relational algebra (8)
sid
s001
s002
s003
s004
sid
s002
s004
s006
sname
Henry
Jason
Vivian
Susan
S1
age
23
18
21
19
sname
Jason
Susan
Candy
S2
age
18
19
22
sid
s002
s004
中央大學。范錚強
sname
Jason
Susan
S1 ∩ S2
age
18
19
10
Relational algebra (9)
sid
s001
s002
s003
s004
sname
Henry
Jason
Vivian
Susan
S1
sid
s002
s003
R
cid
c101
c082
age
23
18
21
19
(sid)
s001
s001
s002
sname
Henry
Henry
Jason
age
23
23
18
(sid)
s002
s003
s003
S1∞S1.sid<R.sidR
cid
c101
c082
c082
(sid) sname
s002 Jason
s003 Vivian
age
18
21
cid
c101
c082
(sid) sname
s002 Jason
s003 Vivian
age
18
21
cid
c101
c082
S1∞S1.sid=R.sidR
S1∞R
中央大學。范錚強
11
Relational calculus (1)
Tuple relational calculus (TRC)
General form: {t|Ψ(t)}
{tuple variable|TRC formula}
Tuple vairable = set of rows from relations
Domain relational calculus (DRC)
General form: {a, b, c, …|Ψ(a, b, c, …)}
{predicate (domain variables)|DRC formula}
Domain vaiable = set of columns from relations
QBE: Query by Example
{ predicate | formula }
true
中央大學。范錚強
12
Relational calculus (2)
運算子 (operators)
<, >, =, ≤, ≥, ≠
¬, ∧, ∨
資格限制 (quantifiers)
∃- exist (有一個)
∀- for all (每一個)
¬∃- not exist
¬∀- not for all
中央大學。范錚強
13
Relational calculus (3)
Hypothesized relation schema
S (sid, did, sname)
R (sid, cid, grade)
列出所有資管系學生的sid, did, sname
TRC - {t|S(t)∧t[2]=“43”}
DRC - {abc|S(abc)∧b=“43”}
列出所有資管系學生的sid及sname
TRC - {t|∃uS(u)∧u[2]=“43”∧t[1]=u[1]∧t[2]=u[3]}
DRC - {ab|∃cS(acb)∧c=“43”}
中央大學。范錚強
14
Relational calculus (4)
列出曾經被當的學生sid, did, sname
TRC - {t|S(t)∧∃uR(u)∧t[1]=u[1]∧u[3]<“70”}
DRC - {abc|S(abc)∧∃d∃eR(ade)∧e<“70”}
or {abc|S(abc)∧∃eR(a．e)∧e<“70”}
SQL – select * from S where sid in (select sid
from R where grade<“70”)
or select S.sid, S.did, S.sname from S, R
where S.sid = R.sid and R.grade<“70”
中央大學。范錚強
15
For all 下的排除條款
在大部分需要 For all 的查詢（例如：每一門課都當掉）
中，我們需要排除一些不需要的東西
只要是我們檢驗的學生的課，我們才需要檢查是否及格
如果不是該學生，就不需要檢查
For 每一筆學生成績，如果是該位同學 (If A)，都是不及格
的成績 (then B)  For 每一筆學生成績，如果不是是該位同
學 (not A ) ，不需檢查，否則需要不及格 (or B)
B
A
¬B
If A then B  (¬A ∨ B)
¬A
中央大學。范錚強
16
Relational calculus (5)
列出所有課都被當的學生sid, did, sname
TRC - {t|S(t)∧∀uR(u)∧(t[1]≠u[1]∨u[3]<“70”)}
or {t|S(t)∧¬∃uR(u)∧(t[1] =u[1]∧u[3] ≥“70”)}
SQL - select * from S where not exists (select *
from R where S.sid = sid and grade>=“70”)
SQL有exist和not exist，沒有for all
B
A
¬B
¬(¬A ∨ B) = (A∧¬B)
¬A
中央大學。范錚強
17
Relational calculus (6)
Hypothesized relation schema
S (sid, did, sname)
R (sid, tid)
T (tid, tname, did)
列出被所有老師教過的學生sid, did, sname
{t|S(t)∧∀uT(u)∧∃vR(v)∧t[1]=v[1]∧u[1]=v[2]}
列出學生，修過所有資管系老師的課
{t|S(t)∧∀uT(u)∧(u[3]≠”43”∨(∃vR(v)∧t[1]=v[1]∧u[1]=v[2]))}
中央大學。范錚強
18