双语数据库

精品课件
15
Natural Join(自然连接)
R ⋈S
Pair only those tuples from R and S that agree in whatever attributes are common(共同的) to the schemas of R and S.
Eliminating one copy of each pair of equated attributes.
rbaraddrbars30dependentindependentoperations等价操作31operators运算符含义运算符含义集合运算符setoperators并union差difference交intersection广义笛卡尔积product比较运算符大于大于等于小于小于等于等于不等于专门的关系运算符选择selection投影projection连接join逻辑运算符32设关系r和s的元数分别为r和s设rs0那么rs是一个rs元的元组的集合rs是满足下列条件的最大关系
精品课件
3
Overview of Relational Algebra
Relational Algebra is another example of an algegra.
An algebra whose operands are relations or variables that represent relations.
：
Result
genre sciFi drama comedy
s
精品课件
9
Example
Relation Sells: bar Joe’s Joe’s Sue’s Sue’s
beer
price
Bud
2.50
Miller
Bud
2.50
Miller
2.75 3.00
精品课件
πbeer,price(Sells):
constants like 15.
Operators --- are the usual arithmetic ones:addition,subtraction,multiplication and division.
X + 15
Operator
Operands
精品课件
2
What is Relational Algebra?
Pair(配对) each tuple t1 of R with each tuple t2 of S. Result: a new relation with new tuples, each of them concatenation(连接) a pair of t1t2, the attributes of R and S are in ordered.
Bars 2.75
name
addr
Joe’s
M
Sue’s
⋈ BarInfo := Sells Sells.bar = Bars
BarInfo bar beer price name addr
Joe’s Bud 2.50 Joe’s Maple St.
Joe’s Miller 2.75 Joe’s Maple St.
BarInfo bar beer price addr
Joe’s Bud 2.50 Maple St.
Joe’s Milller 2.75 Maple St.
Sue’s Bud 2.50 River Rd.
Sue’s Coors精品3课.件00 River Rd.
18
Example: Natural Join
beer Bud Miller Miller
price 2.50 2.75 3.100 0
Selection（选择）
The selection operator, applied to a relation R,produces a new relation with a subset of R’s tuples.
πL (R)
L is a list of attributes from the schema
of R.
The result is a new relation that has only
some of R’s columns.
精品课件
8
Example
title, year, length (Movies)
Relational Algebra is another example of an algegra.
It’s atomic operands are: 1. Variables that stand for relations 2. Constants,which are finite relations
精品课件
16
Example: Natural Join
R
S
R S C<E
R S 等值连接
R.B=S.B
AB C
a1 b1 5
a1 b2 6
a2 b3 8
a2 b4 12 自然连接
RS
B E
b1 3
b2 7
b3 10
b3 Ａ 2
a1 b35 2
a1 7
a2 10
A
R.B
C
S.B E
A
R.B
C
S.B E
Beware: attribute 精A品课o件f the same name in 14R
Example: R3 := R1 Χ R2
R1 A B 12 34
R2 B C
56
78
9
10
R3 A 1 1 1 3 3 3
R1.B 2 2 2 4 4 4
R2.B C 56 78 9 10 56 78 9 10
Sue’s Bud 2.50 Sue’s River Rd.
Sue’s Coors 3.00 Sue’s River Rd.
精品课件
22
Example: Theta Join
ABC
BCD
123
234
Байду номын сангаас
678 978
235 7 8 10
(a) Relation U
(b) Relation V
A U.B U.C V.B V.C D
Set Operators on Relations (关系集合运算符)
RUS
union, the set of elements that are in R or S or both.
R∩S
intersection, the set of elements that are in both R and S.
精品课件
20
R
AB C
a1 b1 5
a1 b2 6
a2 b3 8
a2 b4 12
自然连接
R ⋈S
S
B E
b1 3
b2 7
b3 10
b3 2Ａ
b5 a1 23
a1 7
a2 10
R S C<E
A
R.B
C
S.B E
a1
b1
5
b2 7
a1
b1
5
b3 10
a1
b2
6
b2 7
a1
b2
6
bＢ3 10Ｃ Ｅ
ab21
R ∪S
精品课件
4
Four Classes of Relational Algebra(四类关系代数)
Union, intersection, difference(并,交,叉)
Usual set operations, but both operands must have the same relation schema.
ABC 123 679 978
(a) Relation U
BCD 234 235 7 8 10
(b) Relation V
(c) Result U ⋈ V
AB
1
2
12
97
精品课件
CD
3
4
3
5
8 10
19
Theta-Join(θ连接)
R ⋈C S
Take the product R Χ S.
Then apply σC to the result.
σC (R)
C is a condition (as in “if”
statements) that refers to attributes of R.
The result is a new relation with a
subset of R’s tuples that satisfy C.
精品课件
a1
b1
5
a1
b1
5
b2 7
b1 3
a1
b1
5
a1
b2
6
b3 10
b2 7
a1
b2
6
a2
b3
8
b2 7
b3 10
Ｂ a1 Ｃ b2Ｅ
6
a2
b3
8
b3 10
b3 2
b1
5
a2
b3
8
b3 10
b2
6
b3
8
精品课件
17
Example: Natural Join
Sells bar beer price
Bars bar
R –S
difference, the set of elements that are in R but no in S.
Required
R and S must have schem精a品课w件ith identical set of 6
R∪S
AB
C
a1 b1 c1
a1 b2 c2
a2 b2 c1
(c) Result U ⋈A<D V
12 12 12 67 9 7 精品课件
323 4 323 5 3 7 8 10 8 7 8 10 8 7 8 23 10
Combining Operations to Form Queries (查询组合操作)
Example:What are the titles and years of movies made by Fox that are at least 100 minutes long?
Operators are designed to do the most common things that we need to do with relations in a database.
The result is an algebra that can be used as a
query language for relations.
11
Example
lentgh>=100 AND studioName=‘FOX‘ (Movie)
studioName Fox Disney Paramount
Result
精品课件
studioName Fox Disney Paramount
12
Example
Relation Sells: bar Joe’s Joe’s Sue’s Sue’s
Joe’s
Bud 2.50
Joe’s
Miller
2.75
Sue’s
Bud 2.50
Sue’s
Coors 3.00
addr
Joe’s
M
Sue’s
BarInfo := Sells ⋈ Bars
Note: has become Bars.bar to make the natural
join “work.”
Selection, Projection (选择，投影)
Remove parts of a relation.
Cartesian Product, join (笛卡尔积,连接)
Combine the tuples of two relations
Renaming(重命名)
Change the relation schema,i.e.,the names of attributes or the nam精e品课o件f the relation itself. 5
1. Select those Movie tuples that have length≥100.
a1 b3 c2
R
Ａ
Ｂ
a1
b1
a1
b2
a2
b2
R-S
A
B
a1 b1
C
c1
精品课件
S
Ｃ
Ａ
c1
a1
c2
a1
c1
a2
ＢＣ
b2
c2
b3
c2
b2
c1
R∩S
A
B
C
a1 b2 c2 a2 b2 c1
7
Projection（投影）
The projection operator is used to
produce from a relation R a new relation that has only some of R’s columns.
beer
price
Bud
2.50
Miller
Bud
2.50
Miller
2.75 3.00
σbar=“Joe’s”(Sells):
bar
beer
price
Joe’s
Bud
2.50
Joe’s
Miller
2.75
精品课件
13
Cartesian Product（笛卡尔积）
Or cross-product(交叉积),or just product(积) R S
b3 5 8
b3 10
b2
6
b3 精品课件 8
R S 等值连接
R.B=S.B
A
R.B
C
S.B E
a1
b1
5
b1 3
a1
b2
6
b2 7
a2
b3
8
b3 10
a2
b3
8
b3 2
21
Example: Theta Join
Sells bar beer Joe’s Joe’s Sue’s Sue’s
price Bud 2.50 Miller Bud 2.50 Coors 3.00
Relational Algebra (关系代数)
Operators（操作符）
Expression Trees（表达式树 ）
精品课件
1
What is an “Algebra”(代数)
An algebra consists of operators and atomic operands:
Operands --- are variables like X and