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

1、第三章 关系模型及oracle对象关系数据库3.1 关系模型和基本概念3.2 关系代数3.3 oracle对象关系数据库系统关系的定义关系（关系（relation）:一个关系对应一张二维表 元组（元组（tuple）：表中的一行即为一个元组属性（属性（attribute）：表中的一列为一个属性，给每个属性起一个名字即属性名（ID，NAME，PHONE，SALES-REP-ID） 主码（主码（primary key）:表中的某个属性组，它可以唯一确定元组。each row of data in a table is uniquely identified by a primary key. It

2、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操作两大部分。

3、完整性关系的三类完整性约束 实体完整性 、参照完整性、用户定义的完整性 返回关系代数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例如， D1=导师集合= 张清玫，刘逸， D

4、2 =专业集合= 计算机，信息， D3 =研究生集合= 李勇，刘晨，王名 则 D1 X D2 X D3 = (张清玫，计算机，李勇 ) , (张清玫，计算机，刘晨 ) , (张清玫，计算机，王名 ) , (张清玫，信 息，李勇 ) , (张清玫，信 息，刘晨 ) , (张清玫，信 息，王名 ) , (刘 逸，计算机，李勇 ) , (刘 逸，计算机，刘晨 ) , (刘 逸，计算机，王名 ) , (刘 逸，信 息，李勇 ) , (刘 逸，信 息，刘晨 ) , (刘 逸，信 息，王名 ) , 共共12个元组。个元组。笛卡儿积笛卡儿积可对应一张二维表。Example of Cartesian prod

5、uctn basic operatorsSet intersection 交Union 并set difference 差（集合差）Cartesian product 笛卡儿积Select 选择Project 投影Natural join 自然连接Division 除nThe operators take two or more relations as inputs and give a new relation as a result.(1) Select Operation ExampleRelation rABC=B D 5 (r)ABCD123710注：选择

6、条件必须是针对注：选择条件必须是针对同一元组中的相应属性值同一元组中的相应属性值进行比较进行比较Notation: p(r)p is called the selection predicateDefined as: 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:op or where op is one of: =, , , . . Example of

7、selection: branch-name=“Perryridge”(account)(2) Project Operation ExampleRelation r:ABC102030401112AC1112=AC112A,C (r)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 lis

8、tedDuplicate rows removed from result, since relations are setsE.g. To eliminate the branch-name attribute of account account-number, balance (account) (3) Union Operation ExampleRelations r, s: r s:AB121AB23rsAB1213Notation: r sDefined as: r s = t | t r or t sFor r s to be valid：1. r, s must have t

9、he same arity (等目,同元,same number of attributes)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 customer-name (depositor) customer-name (borrower)（4）Set Differ

10、ence Operation ExampleRelations r, s:r s:AB121AB23rsAB11Notation r sDefined as: r s = t | t r and t sSet differences must be taken between compatible relations.lr and s must have the same aritylattribute domains of r and s must be compatible（5）Cartesian-Product Operation-Example（广义笛卡儿积）Relations r,

11、s:r x s:AB12AB11112222CD 1010201010102010EaabbaabbCD10102010Eaabbrs(6) Set-Intersection OperationNotation: r sDefined as:r s = t | t r and t s Assume: lr, s have the same arity lattributes of r and s are compatibleNote: r s = r - (r - s)Set-Intersection Operation - ExampleRelation r, s:r sA B121A B2

12、3rsA B 2n Notation: r s(7) Natural-Join OperationLet r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows:lConsider each pair of tuples tr from r and ts from s. lIf tr and ts have the same value on each of the attributes in R S, add a tuple

13、t to the result, wherelt has the same value as tr on rlt has the same value as ts on sExample:R = (A, B, C, D)S = (E, B, D)lResult schema = (A, B, C, D, E)lr s is defined as: r.A, r.B, r.C, r.D, s.E ( r.B = s.B r.D = s.D (r x s)Natural Join Operation ExampleRelations r, s:AB12412CDaababB13123DaaabbE

14、rAB11112CDaaaabEsr 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.(8) Division OperationSuited to queries that include the phrase “for all”.Let r and s

15、be relations on schemas R and S respectively wherelR = (A1, , Am, B1, , Bn)lS = (B1, , Bn)The result of r s is a relation on schemaR S = (A1, , Am)r s = t | t R-S(r) u s ( tu r ) 注：商来自于 R-S(r) ，并且其元组t与s的拼接被r覆盖。r s Division Operation ExampleRelations r, s:r s:AB12AB12311134612rs返回Oracle 体系结构(1)物理结构 d

16、atafiles redo log files control files parameter file 数据文件 日志文件 控制文件 参数文件 data filesredo log filescontrol fileparameter iles*.dbf*.logInitoraid.ora*.ctlPMONLCKnRECORECOshared PoolDatabase Buffer CacheRedo LogBufferSGADataDataDataDataDataDataDataDataDataDataDatafilesRedo LogfilesParameterFileControlfi

17、lesServerUserDBWRLGWRCKPTARCHOfflinestorage(2)内存结构(SGA) 占OS内存的60-70%，大小可由参数文件内参数计算 shared pool(共享池)， database buffer cache(数据缓冲区), redo log buffer(重做日志缓冲区) (如以下图所示)SGA=share_pool_size+db_block_size*db_block_buffers+log_buffersshared_pooldatabase_buffer_cacheredo_log bufferSGAbackground process(3)ins

18、tance=SGA+background process 实例 = 内存分配 + 一组后台进程 如果把Oracle比作一部汽车，instance相当于汽车的发动机一样,启动oracle前提应先启动instance.(4)session(连接) Oracle是多用户、多任务、可分布式管理的数据库，同时可有许 多个用户对数据库操作。oracleuseruserusersession (5)transaction(一组修改动作的集合) 交易事务Eg: 1、 insert DDL (数据定义语句) delete 例如：create,alter,drop,conmit 等 commit 每两个DDL语句间是一个transaction 2、 update DML (数据控制语句) rollback 例如：Insert,Delete,Update (6)后台进程 PMON，LCLN，RECO，SMON，DBWR，LGWR，CKPT，ARCHPMON 做程序的清洁工作，处理一些不正常退出的事件SMON 做系统的清洁工作，执行系统出错后自动恢复工作LCKN Oracle系统表级或行级加锁的进程.RECO 恢复进程DBWR 数据写进程LGWR 日志文件写的进程CKPT 检测点AR