关系模型及oracle对象关系数据库

S = (B1, …, Bn)
The result of r s is a relation on schema
R – S = (A1, …, Am)
r s={t | t
R-S(r)
u
s ( tu r ) }
注：商来自于 R-S(r) ，并且其元组t与s的拼
Division Operation – Example
customer-name (depositor) customer-name (borrower)
〔4〕Set Difference Operation – Example
Relations r, s:
r – s:
AB
1 2 1
r
AB
2 3
s
AB
1 1
Notation r – s
Defined as:
basic operators Set intersection 交 Union 并 set difference 差〔纠合差〕 Cartesian product 笛卡儿积 Select 选择 Project 投影 Natural join 天然毗连 Division 除
The operators take two or more relations as inputs and give a new relation as a result.
p(r) = {t | t r and p(t)}
Where p is a formula in propositional calculus consisting of terms connected by : (and), (or), (not) Each term is one of:
Relation r:
A,C (r)
ABC
10 1 20 1 30 1 40 2
AC
AC
1
1
1 = 1
1
2
2
Notation:
A1, A2, …, Ak (r) where A1, A2 are attribute names and r is a relation name. The result is defined as the relation of k columns obtained by erasing the columns that are not listed Duplicate rows removed from result, since relations are sets E.g. To eliminate the branch-name attribute of account
关系的定义
关系〔relation〕:一个关系对应一张二维表 元组〔tuple〕：表中的一行即为一个元组 属性〔attribute〕：表中的一列为一个属性，给每一个属性
起一个名字即属性名〔ID，NAME，PHONE，SALESREP-ID〕
主码〔primary key〕:表中的某个属性组，它可以唯一一定 元组。each row of data in a table is uniquely
2. The attribute domains must be compatible (e.g., 2nd column
of r deals with the same type of values as does the 2nd
column of s)
E.g. to find all customers with either an account or a loan
(8) Division Operation
r s
Suited to queries that include the phrase “for all”.
Let r and s be relations on schemas R and S respectively where
R = (A1, …, Am, B1, …, Bn)
identified by a primary key. It must contain a value
外码〔foreign key〕：you can logically relate
information from multiple tables using foreign keys.
关系模型
关系模型由关系数据布局，关系操作和关系完全性束缚三 局部构成。 数据布局 ——关系〔二维表〕，它由行和列构成。 关系操作—— 常见的关系操作包孕：选择select，投影project，毗连join， 除divide，并union，交intersection，差difference等查询 query操作和增加insert，删除delete，改削update操作两 大年夜局部。 完全性——关系的三类完全性束缚
(1) Select Operation – Example来自• Relation r
ABCD
1 7 57 12 3 23 10
• A=B ^ D > 5 (r) ABCD
1 7 23 10
注：选择前提必需是针对 同一元组中的响应属性值 进展对照
Notation: p(r)
p is called the selection predicate Defined as:
第三章 关系模型及oracle对 象关系数据库
3.1 关系模型和根底概念
3.2 关系代数
3.3 oracle对象关系数据库系统
关系数据库系统是撑持关系模型的数据库系统。
Vendors: ORACLE(9i ),IBM(DB2) , Microsoft (ACESS and SQLServer), Foxbase(Foxpro), Sybase
<attribute> op <attribute> or <constant> where op is one of: =, , >, . <. Example of selection:
branch-name=“Perryridge”(account)
(2) Project Operation – Example
(6) Set-Intersection Operation
Notation: r s Defined as: r s ={ t | t r and t s } Assume:
r, s have the same arity attributes of r and s are compatible
account-number, balance (account)
(3) Union Operation – Example
Relations r, s:
r s:
AB
1 2 1
r
AB
2 3
s
AB
1 2 1 3
Notation: r s
Defined as:
r s = {t | t r or t s} For r s to be valid： 1. r, s must have the same arity (等目,同元,same number of attributes)
*.log
control file
*.ctl
parameter iles
Initoraid.ora
PMON
LCKn
RECO
RECO
shared Pool
SGA
Database Buffer Cache
Redo Log Buffer
Server User
DBW R
Data Data Data
r – s = {t | t r and t s} Set differences must be taken between compatible relations.
r and s must have the same arity attribute domains of r and s must be
add a tuple t to the result, where t has the same value as tr on r t has the same value as ts on s
Example: R = (A, B, C, D) S = (E, B, D) Result schema = (A, B, C, D, E) r s is defined as:
Note: r s = r - (r - s)
Set-Intersection Operation Example
Relation r, s:
AB
1 2 1
r
AB 2 3
s
rs
AB 2
(7) Natural-Join Operation
Notation: r s
Let r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows: Consider each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in R S,
Example of Cartesian product
例如， D1=导师纠合= {张清玫，刘逸}， D2 =专业纠合= {计较机，信息}， D3 =研究生纠合={ 李勇，刘晨，王名}
那么 D1 X D2 X D3 ={ (张清玫，计较机，李勇 ) , (张清玫，计较机，刘晨 ) , (张清玫，计较机，王名 ) , (张清玫，信 息，李勇 ) , (张清玫，信 息，刘晨 ) , (张清玫，信 息，王名 ) , (刘 逸，计较机，李勇 ) , (刘 逸，计较机，刘晨 ) , (刘 逸，计较机，王名 ) , (刘 逸，信 息，李勇 ) ,
compatible
〔5〕Cartesian-Product OperationExample〔广义笛卡儿积〕
Relations r, s: r x s:
AB
1 2
r
CDE
10 a 10 a 20 b 10 b
s
ABCDE
1 10 a 1 10 a 1 20 b 1 10 b 2 10 a 2 10 a 2 20 b 2 10 b
r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s))
Natural Join Operation – Example
Relations r, s:
ABCD
1a 2a 4b 1 a 2b
r
r s ABCDE
1a 1a 1 a 1 a 2b
实体完全性 、参照完全性、用户定义的完全性
返回
关系代数
The relational algebra(关系代数) is the basis of SQL.
Basic Structure ——Cartesian product(笛卡儿积)
Formally, given sets D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x Dn , Thus a relation is a set of n-tuples (a1, a2, …, an) where each ai Di
BDE
1a 3a 1a 2b 3 b
s
注：
(1)r,s必需含有共同属性 (名, 域对应一样), (2) 毗连二个关系中同名属性值相等的元组 (3) 了局属性是两者属性集的并集，但消去 重名属性。
Theta join: r s = (r x s)
is the predicate on attributes in the schema. • Theta Join is the extension to the Nature Join.
Relations r, s:
AB
B
1
1
2
3
2
1
s
1
1
3
4
6
1
2
r s:
A
r
返回
Oracle 系统布局 (1)物理布局
datafiles parameter file
redo log files
control files
数据文件
日志文件
控制文件 参数文件
data files
*.dbf
redo log files