1 / 34

第三章 关系模型及 oracle 对象关系数据库

第三章 关系模型及 oracle 对象关系数据库. 3.1 关系模型和基本概念. 3.2 关系代数. 3.3 oracle 对象关系数据库系统. 关系数据库系统是支持关系模型的数据库系统。 1970 年初， IBM 公司的高级研究员 Dr.E.F.Codd 发表论文提出了关系模型，奠定了关系数据库的理论基础。 Dr.E.F.Codd proposed the relational model for database systems in 1970s.

kioko
Download Presentation

第三章 关系模型及 oracle 对象关系数据库

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第三章 关系模型及oracle对象关系数据库 3.1 关系模型和基本概念 3.2 关系代数 3.3 oracle对象关系数据库系统

  2. 关系数据库系统是支持关系模型的数据库系统。关系数据库系统是支持关系模型的数据库系统。 • 1970年初，IBM公司的高级研究员Dr.E.F.Codd发表论文提出了关系模型，奠定了关系数据库的理论基础。Dr.E.F.Codd proposed the relational model for database systems in 1970s. • Vendors: ORACLE(9i ),IBM(DB2) , Microsoft (ACESS and SQLServer), Foxbase(Foxpro), Sybase

  3. 关系的定义 关系（relation）:一个关系对应一张二维表 元组（tuple）：表中的一行即为一个元组 属性（attribute）：表中的一列为一个属性，给每个属性起一个名字即属性名（ID，NAME，PHONE，SALES-REP-ID）

  4. 主码（primary key）:表中的某个属性组，它可以唯一确定元组。each row of data in a table is uniquely identified by a primary key. It must contain a value 外码（foreign key）：you can logically relate information from multiple tables using foreign keys.

  5. 关系模型 关系模型由关系数据结构，关系操作和关系完整性约束三部分组成。 • 数据结构 ——关系（二维表），它由行和列组成。 • 关系操作——常见的关系操作包括：选择select，投影project，连接join，除divide，并union，交intersection，差difference等查询query操作和增加insert，删除delete，修改update操作两大部分。 • 完整性——关系的三类完整性约束 实体完整性 、参照完整性、用户定义的完整性 返回

  6. 关系代数 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

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

  8. 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.

  9. (1) Select Operation – Example A B C D • Relation r         1 5 12 23 7 7 3 10 • A=B ^ D > 5(r) 注：选择条件必须是针对同一元组中的相应属性值进行比较 A B C D     1 23 7 10

  10. Notation: p(r) • p is called the selection predicate • Defined as: p(r) = {t | t  rand p(t)} Where p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not)Each term is one of: <attribute> op <attribute> or <constant> where op is one of: =, , >, . <.  • Example of selection:branch-name=“Perryridge”(account)

  11. (2) Project Operation – Example A B C • Relation r:     10 20 30 40 1 1 1 2 A,C (r) A C A C     1 1 1 2    1 1 2 =

  12. 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account-number, balance (account)

  13. (3) Union Operation – Example A B A B • Relations r, s:    1 2 1   2 3 s r r  s: A B     1 2 1 3

  14. 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) 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)

  15. （4）Set Difference Operation – Example A B A B • Relations r, s:    1 2 1   2 3 s r r – s: A B   1 1

  16. Notation r – s • Defined as: r – s = {t | t rand 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 compatible

  17. （5）Cartesian-Product Operation-Example（广义笛卡儿积） Relations r, s: A B C D E   1 2     10 10 20 10 a a b b r r xs: s A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b

  18. (6) Set-Intersection Operation • Notation: r s • Defined as: • rs ={ t | trandts } • Assume: • r, s have the same arity • attributes of r and s are compatible • Note: rs = r - (r - s)

  19. Set-Intersection Operation - Example • Relation r, s: • r  s A B A B    1 2 1   2 3 r s A B 2

  20. (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, 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) • rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))

  21. r s Natural Join Operation – Example B D E A B C D • Relations r, s: 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s 注： (1)r,s必须含有共同属性 (名, 域对应相同), (2) 连接二个关系中同名属性值相等的元组 (3) 结果属性是二者属性集的并集，但消去重名属性。 A B C D E      1 1 1 1 2      a a a a b     

  22. Theta join: r s = (r x s) •  is the predicate on attributes in the schema. • Theta Join is the extension to the Nature Join.

  23. (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) • 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的拼接被r覆盖。

  24. Division Operation – Example A B Relations r, s: B            1 2 3 1 1 1 3 4 6 1 2 1 2 s r s: A r   返回

  25. Oracle 体系结构(1)物理结构datafiles redo log files control files parameter file数据文件 日志文件 控制文件 参数文件 control file parameter iles redo log files data files *.ctl *.log Initoraid.ora *.dbf

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

  27. shared_pool database_buffer_cache redo_log buffer

  28. (3)instance=SGA+background process 实例 = 内存分配 + 一组后台进程 如果把Oracle比作一部汽车，instance相当于汽车的发动机一样,启动oracle前提应先启动instance. SGA background process

  29. (4)session(连接) Oracle是多用户、多任务、可分布式管理的数据库，同时可有许 多个用户对数据库操作。 oracle session user user user

  30. (5)transaction(一组修改动作的集合) 交易事务Eg: 1、 insert DDL (数据定义语句) delete 例如：create,alter,drop,conmit等commit 每两个DDL语句间是一个transaction 2、update DML (数据控制语句) rollback 例如：Insert,Delete,Update

  31. (6)后台进程 PMON，LCLN，RECO，SMON，DBWR，LGWR，CKPT，ARCHPMON 做程序的清洁工作，处理一些不正常退出的事件．SMON 做系统的清洁工作，执行系统出错后自动恢复工作．LCKN Oracle系统表级或行级加锁的进程.RECO 恢复进程．DBWR 数据写进程　LGWR 日志文件写的进程CKPT 检测点ARCH 归档方式备份进程

  32. (7)分析一个SQL语句是怎样在Oracle内部工作的。 A、用户发出SQL请求，打开游标； B、把SQL语句语法分析，执行计划，数据字典等信息存入内存中共享池内； C、从数据文件中把相关数据块读入数据缓冲区； D、做相应操作，若做修改，先加上行级锁，经确认后，把改过前后记录内容存入重做日志缓冲区内； E、返回结果给用户，关闭游标。 备注：SQL语句大小写敏感的，同样的一个语句，若大小写 不同，oracle需分析执行两次，每句后必以“；”结束。

  33. 返回

More Related