Fundamental and Application of
Database
? o?¨$
? o?¨$
?=?
?=?
? e/
?D1???ù??
? e/
?D1???ù??
? ￥t
? ￥t
?
?
2c
c
1" ?
1" ?
? 2.1 'à Q
'à Q
? 2.2 1"}
?
1"}
?
? 2.3 °?a?
°?a?
2.1 1" ?￥'à Q
1" ?￥'à Q
1970 M
M
IBM E. F. Codd4
4
V
V =?V
=?V
1??Dy
1??Dy
?D
3 ?
?D
3 ?
Vh
Vh
D
D
|
|
?
?
? Y
? Y
3 ° ù
3 ° ù
! :
! :
& ú
& ú
20000121 |
|
3
3 1982-12
?
? 188
20000122
f v
?
f v
?
3
3 1983-02
a
a 165
20000123 $
$
Y
Y
o
o 1982-09
? ?
? ? 160
20000124
>
>
¤ ?
¤ ?
3
3 1982-09
P ?
P ? 176
……. ……. ……. ……. ……. …….
?
?
AV
AV
??
??
÷"
÷"
°?
°?
2.1 1" ?￥'à Q
1" ?￥'à Q
1"
1" ?S??S? ￥=?V￥=?V
…….…….…….…….…….…….
176
P?
P?1982-09
3
3
>
>
¤?
¤?20000124
160
??
??1982-09
o
o$
$
Y
Y
20000123
165
a
a1983-02
3
3
fv
?
fv
?20000122
188
?
?1982-12
3
3|
|
20000121
Student(Sno, Sname, Ssex, Sbirthday, Sorient, Sheight
1"
T
1"
T
íF
íF
1"
1"
L è
L è
2.1 1" ?￥'à Q
1" ?￥'à Q
1"
1"
? o , F Z
us?]íF￥
??"?
us?]íF￥
??"?
? ?l %?
? μKl? μKl?
?
? 4 V Q F S , F Z
c
?￥￥
??"?
c
?￥￥
??"?
?ê
?
?
? 1 S J N F , F Z
×
×
% P N B J O
??￥ |′S?
??￥ |′S?
2.1 1" ?￥'à Q
1" ?￥'à Q
1"￥?é
1"￥?é
^]é￥
^]é￥
?? ?]
?? ?]
￥Q?í1
￥Q?í1
?
?íF? ?? ?M]
?
?íF? ?? ?M]
íFQ?í1
íFQ?í1
?A? |e0′
1" ?
? ?
? ?
1"
1"
? ?T
? ?T
9F a ÷" a?? a°s
9F a ÷" a?? a°s
? ????
?
? ????
?
1.
L8???
L8??? (Entity Integrity)
2. ?v??? (Referential Integrity)
3. ¨??l???¨??l??? (User-defined Integrity)
1" ?
1" ?￥a?
1" ?￥a?
òe
ü?
e
ü?
óú ??
ú ??
?
? ? ??
? ? ??
? 2??T? ??i%Z
T
? 2??T? ??i%Z
T
2.2 1"}
?
1"}
?
?￥>?
?￥>?
1"}
?
?￥ ??1
í
?` 1"
?2T 1"
??
1 ?
à
?
à
?
(?
?
)
– "?
??
"?
??
z |1" A?íF￥"?|1" A?íF￥"?
z
?
^V1"￥
?
^V1"￥ “
￡ ü
￡ ü ”Z_'?￥? ?é?
–
ê￥1"
??
ê￥1"
??
z ??
#?7 O
#
??
#?7 O
#
–
?
?1??
?
?1??
z ￡ù
ê￥1"
??é??T￡ù
ê￥1"
??é??T
–
?
??
?
??
z ￡ù
ê￥1"
??é??T￡ù
ê￥1"
??é??T
V 2.4 1"}
?
??
à
? (? )
? ? c l
? ? c l
"
?
?
?
?
-
?
?
i
μ
?
< l 2
5 :
1
?
?
?
#
?
!
?
"
?
v ?
v ? ? ?
l ?
l ? ? ?
? ?
? ? ?
V 2.4 1"}
?
??
?
à
? (? )
? ? c l
? ? c l
ê ￥
1 "
? ?
6
4
?
ê 4
g ?
? ¤
"
?
? ?
?
?
?
d
D
à
?
à
?
(?
?
)
41"}
?
?￥s ?
.d￥"?
?
i aμ a? a<l2 5:
ê￥1"
?
ê4 ag? a ?¤ a"
2.2 1"}
?
1"}
?
z à
?
z .d￥"?
?
z
ê￥1"
?
2.2.1 .d￥"?
?
.d￥"?
?
z i
z μ
z ?
z <l2 5:
1. i
i
Union
z R?S
– μM]￥ " n
'
?1"?μ n?
??
– M?￥
?? |1]B?×
z R ?S
– ˉ1 n "1"?
?? R
?? S￥íFF?
R ?S = { t|t ∈ R ?t ∈S }
i
i
(?
?
)
ABC
a1 b1 c1
a1 b2 c2
a2 b2 c1
R
ABC
a1 b1 c1
a1 b2 c2
a1 b3 c2
a2 b2 c1
R ?S
ABC
a1 b2 c2
a1 b3 c2
a2 b2 c1
S
2. μ
μ
Difference
z R?S
– μM]￥ " n
– M?￥
?? |1]B?×
z R - S
– ˉ1 n "1"?
?? R7?
?? S￥
?μí
FF?
R -S = { t|t∈R ?t?S }
μ
μ
(?
?
)
ABC
a1 b1 c1
a1 b2 c2
a2 b2 c1
R
ABC
a1 b1 c1
R-S
ABC
a1 b2 c2
a1 b3 c2
a2 b2 c1
S
3. ?
?
Intersection
z R?S
– μM]￥ " n
– M?￥
?? |1]B?×
z R ?S
– ˉ1 n "1"?;
?? R?
?? S￥íFF?
R ?S = { t|t ∈ R ?t ∈S }
R ?S = R –(R-S
?
?
(?
?
)
ABC
a1 b1 c1
a1 b2 c2
a2 b2 c1
R
ABC
a1 b2 c2
a2 b2 c1
R ?S
ABC
a1 b2 c2
a1 b3 c2
a2 b2 c1
S
4. <l2 5:
<l2 5:
Extended Cartesian Product
z R
– n "1" k
1
?íF
z S
– m "1" k
2
?íF
z R ?S
–
n+m
￥íF￥"?
z íF￥ - n
^1" R￥B?íF
z a m
^1" S￥B?íF
– ? k
1
?k
2
?íF
z R ?S = {t
r
t
s
|t
r
∈R ?t
s
∈S }
<l2 5:
<l2 5:
(?
?
)
ABC
a1 b1 c1
a1 b1 c1
a1 b1 c1
a1 b2 c2
a2 b2 c1
a2 b2 c1
a2 b2 c1
a1 b2 c2
a1 b2 c2
ABC
a1 b2 c2
a1 b2 c2
a1 b2 c2
a1 b3 c2
a2 b2 c1
a1 b3 c2
a2 b2 c1
a1 b3 c2
a2 b2 c1
ABC
a1 b1 c1
a1 b2 c2
a2 b2 c1
R
R ?S
ABC
a1 b2 c2
a1 b3 c2
a2 b2 c1
S
2.2 1"}
?
1"}
?
z à
?
z .d￥"?
?
z
ê￥1"
?
2.2.2
ê￥1"
?
ê￥1"
?
z ê4
z g?
z ?¤
z "
1. ê4
ê4
Selection
z 1) ê4??1K?
Restriction
z 2) ê4
??￥cl
– 1" R?ê4
?@ó?Hq￥ííF
6
F
(R) = {t|t∈R ?F(t)= '?'}
– F ê4Hq
^B?
? Vr
T'?
T1
X
1
, Y
1
[9 X
2
, Y
2
]…
z , 1?
??
# ?! ?" <>
z X
1
Y
1
?
?? aè
ae?f
?
?? 9 V [ ¨
?
￥?| ?}9
z 9
?
??
? ?
z [ ] V
U ?ê[
z … V
U
?ì
T V[×ˉ/ ?
ê4
ê4
?
?
z 3) ê4
?
^V?￥?é?￥
?
z 4) è
!μB?D
3 - [?
? o ?D
31 "
Student a [?1" Course?ê?1" SC b
6
ê4
ê4
?
?
Student
D|
Sno
?
Sname
?Y
Ssex
M
-
Sage
? "
Sdept
95001
ù§ 3
20 CS
95002
>? o
19 IS
95003
|
ù o
18 MA
95004
f ? 3
19 IS
è 1 è 2 è 4 è 3
ê4
ê4
?
?
Course
(b)
[?| [? 5? [ Ds
Cno Cname Cpno Ccredit
1
? o
5 4
2
?D
2
3
?"d
1 4
4
?T"d
6 3
5
? 2
7 4
6
? ) ?
2
7 PASCAL?y 6 4
è 9
ê4
ê4
?
?
SC
D| [?| ?
Sno Cno Grade
95001 1 92
95001 2 85
95001 3 88
95002 2 90
95002 3 80
(c)
è 7 è 9
ê4
?
ê4
?
[ è 1] °??"
IS" ?8D
3
6
Sdept = 'IS'
(Student)
6
5 ='IS'
(Student)
2T
Sno Sname Ssex Sage Sdept
95002
>? o
19 IS
95004
f ? 3
19 IS
ê4
?
ê4
?
[ è 2] °? M
-l? 20
?￥D
3
6
Sage < 20
(Student)
6
4 < 20
(Student)
2T
Sno Sname Ssex Sage Sdept
95002
>? o
19 IS
95003
|
ù o
18 MA
95004
f ? 3
19 IS
2. g?
g?
Projection
z 1g?
??￥cl
– V R?ê4 ??
??
F??￥1"
4
A
(R) = { t[A] | t ∈R }
A R?31
=￥
??
2. g?
g?
Projection
z 2g??T?1
^V
￥?é?
?
– ?g?-a?? |h
e1"?￥ t
7 O? V ? |h tíF
E
?×ˉ?
4
g?
g?
?
?
z 3) è
[ è 3] °?D
3￥? ?
? "
' p Student1"
D
3? ?
? "
?
?
?
￥g?
4
Sname Sdept
(Student)
4
2 5
(Student)
2T
g?
g?
?
?
Sname Sdept
ù§
CS
>?
IS
|
ù
MA
f ?
IS
g?
g?
?
?
[ è 4] °?D
31" Student??μ 't"
4
Sdept
(Student)
2T
Sdept
CS
IS
MA
3. ?¤
?¤
Join
z 1 ?¤9?1, ?¤
z 2 ?¤
?￥cl
– V
?1"￥2 5:?ê |
??W
?@B?Hq
￥íF
R S = { | t
r
∈ R ?t
s
∈S ?t
r
[A], t
s
[B] }
z A?B sY1 R?S
?M? O V1￥
??F
z , 1?
??
– ?¤
?V R?S￥<l2 5: R ?S?ê |
R1
" A
??F
￥′D
S1" B
??F
′
?@1?1"￥íF b
A, B
t
r
t
s
?¤
?¤
(?
?
)
z 3
?è¨ ?¤
?
– ?′ ?¤
equijoin
z
I
1
^?′ ?¤
–,1 “" ”￥ ?¤
??1?′ ?¤
z ?′ ?¤￥cl
–V1" RD S￥<l2 5:?ê | A aB
??
′M?￥ *tíF'?′ ?¤1
R S = { | t
r
∈R ?t
s
∈S ?t
r
[A] = t
s
[B] }
A=B
t
r
t
s
?¤
?¤
(?
?
)
– 1 ? ?¤
Natural join
z
I
1
^1 ? ?¤
– 1 ? ?¤
^B?+
y￥?′ ?¤
?
?1"?é?1?￥s
A?
^M]
￥
??F
? 2T?ü×ˉ￥
??
??
z 1 ? ?¤￥cl
R?S μM]￥
??F B
RS= { | t
r
∈R ?t
s
∈S ?t
r
[B] = t
s
[B] }
t
r
t
s
?¤
?¤
(?
?
)
z 4B?￥ ?¤?T
^V?￥?é?
? b
1 ? ?¤?31 |h×ˉ
?[
^
]
HV??
￥?é?
? b
A, B
R
S
?¤
?¤
(?
?
)
z 5 è
[ è 5]
B E
b
1
3
b
2
7
b
3
10
b
3
2
b
5
2
A B C
a
1
b
1
5
a
1
b
2
6
a
2
b
3
8
a
2
b
4
12
R
S
?¤
?¤
(?
?
)
R S
A R.B C S.B E
a
1
b
1
5 b
2
7
a
1
b
1
5 b
3
10
a
1
b
2
6 b
2
7
a
1
b
2
6 b
3
10
a
2
b
3
8 b
3
10
C! E
?¤
?¤
(?
?
)
?′ ?¤ RS
R.B=S.B
A R.B C S.B E
a
1
b
1
5 b
1
3
a
1
b
2
6 b
2
7
a
2
b
3
8 b
3
10
a
2
b
3
8 b
3
2
?¤
?¤
(?
?
)
1 ? ?¤ RS
A B C E
a
1
b
1
5 3
a
1
b
2
6 7
a
2
b
3
8 10
a
2
b
3
8 2
4. "
"
Division
ó?1" 3 9 :
? 4 : ;
? 9 : ;1
??
F b
3?￥ :D 4?￥ : V[μ?]￥
?? ? A?1M]
￥×" b 3D 4￥"
?¤?B??￥1" 1 9
R ?S = P(x)
|t∈P(X), μ t ∈4
X
(R), 7 O t ?4
Y
(S) ? R
"
"
(?
?
)
z 2"?T
^]
HV??
?é?
?
z 3 è
[ è 6]
?
R
S
"
"
(?
?
)
A B C
a
1
b
1
c
2
a
2
b
3
c
7
a
3
b
4
c
6
a
1
b
2
c
3
a
4
b
6
c
6
a
2
b
2
c
3
a
1
b
2
c
1
B C D
b
1
c
2
d
1
b
2
c
1
d
1
b
2
c
3
d
2
R ?S
A
a
1
R
S
2.2.3 ¨1"}
?V
U1"?T
¨1"}
?V
U1"?T
[ è 1] °?D [?|1 1137￥D
3￥
D|??
4
Sno,Grade
(6
Cno=‘1137
( SC))
2.2.3 ¨1"}
?V
U1"?T
¨1"}
?V
U1"?T
[ è 2] °?D [?|1 1137￥D
3￥
D|??
4
Sno,Sname
( S (6
Cno=‘1137
( SC)) )
[ è 5] °?à
ê? [?|1 1024?1136
￥D
3￥D|
4
1
(6
1=4^2=‘1024’^5=’1136’
( SC ? SC) )
l2
l2
z1"}
?
?
– 1"}
?
?
i aμ a? a2 5: ag? aê4 a ?¤ a"
– '
?
i aμ a2 5: ag? aê4
– ? a ?¤ a"
V[¨ 5?'
? ?Vr
/ [
b b b
¨B?8 b b b
