ORACLE数据库设计

1、、设计数据库的必要性1.为什么要设计数据库修建茅犀需要设计吗?修建大厦需要设计结论：当数握库比较复杂时我们需要设计数堀库当数据库比较复杂（如数据量大，表较多，业务关系复杂）时，我们需要先设计 数据库，因为：*良好的数据库设计：*节省数据的存储空间能够保证数据的完整性*方便进行数据库应用系统的开*糟糕的数据库设计：数据冗余、存储空间浪费*数据更新和插入的异常、软件项目开发周期中的数据库开发邀据库世弄1.数据库生命周期数据库的生命周期主要分为四个阶段：1. 需求分析阶段：（此阶段在软件项目开发周期中的需求分析阶 段进行）这个阶段主要是收集并分析用户的业务和数据处理需 求收集信息（我们需要与数据的创

2、造者和使用者进行访谈。 对访谈获得的信息进行整理、分析，并撰写正式的需求文 档。需求文档中需包含：需要处理的数据；数据的自然关 系；数据库实现的硬件环境、软件平台等；）标识对象标识每个对象的属性标识对象之间的关系2. 逻辑设计阶段：（此阶段在软件项目开发周期中的概要设计和 详细设计阶段进行）使用ER或UML建模技术，创建概念数据模型图，展示所 有数据以及数据间关系。最终概念数据模型必须被转化为 范式化的表。a. 绘制E-R图b. 将E-R图转换为表格c. 应用三大范式规范化表格3. 物理设计阶段：根据特定数据库管理系统所提供的多种存储结构和存取方 法等依赖于具体计算机结构的各项物理设计措施，对

3、具体 的应用任务选定最合适的物理存储结构（包括文件类型、索 引结构和数据的存放次序与位逻辑等）、存取方法和存取路径等。这一步设计的结果就是所谓“物理数据库”。a. 数据库物理设计的目标是尽可能优化性能。b. 物理设计阶段，表结构可能需要进行重构来满足性能上的 需求，这被称为反范式化。c. 反范式化的步骤包括：I. 辨别关键性流程，如频繁运行、大容量、高优先级的处理操作II. 通过增加冗余来提高关键性流程的性能III. 评估所造成的代价（对查询、修改、存储的影响） 和可能损失的数据一致性d.实现维护阶段：I.当设计完成之后，使用数据库管理系统（DBMS ）中的数据定义语言（DDL ）来创建数据结

4、构。II. 数据库创建完成后，应用程序或用户可以使用数据操作语言（DML ）来使用（查询、修改等）该数据库。III. 一旦数据库开始运行，就需要对其性能进行监视。当数据库性能无法满足要求或用户提出新的功能需求时，就需要 对该数据库进行再设计与修改。这形成了一个循环：监视 再设计修改监视。三、数据库设计实现步骤a.需求分析阶段I. 收集信息与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务BBS论坛的基本功能.用户注册和登录-后台数据库需要存放用户的注册倍息和在线 状态信息:用户发贴.后台数据库需要存放贴子相关信息，如贴子内容 葆题等；.论坛版块管理三后台数据库需要存放各个版块信息.如

5、版主 版块名称、贴子数等：b.标识对象（实体Entity ）标识数据库要管理的关键对象或实体实体一般是名词；用户论坛普通用户.各版块的版主冃戸发的主贴用户发的跟贴回贴)版块；论坛的各个版块信息标识每个实体的属性(Attribute)论坛用户：主贴回贴版块*呢称*发贴人*贴子编号*版块名称邨-发贴表情回贴人版主于电子邮牛.回复数量回贴表情本版格盲*生日*标题-标题点击率性别*正文正文*发贴数*用户的等级*发贴时间*回贴时间*备注信息律点击数点击数*注册日期*状态：*状态*最后回复时间*积分4.标识对象之间的关系(Relationship).跟贴和主贴有主从关系：我们需要在跟貼对象中表朋它是堆的跟

6、贴; *版块和用户有关系从用户对象中可以根据版块对象查出对应的版上 用户的情况i.主贴和版块有主从关系；需要表朋发贴是属于哪个版块的；*跟贴和版块有主从关系：需要表期跟贴是属于哪个版块的；2.逻辑设计阶段1. 绘制E-R图实体关系图（Entity Relationship Diagram），是指提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。ER模型中的基本元素基本的ER模型包含三类元素：实体、关系、属性诞念E&构困Eiapioyef弱实体Employe e-job-标识杆（連）福述特C emp-aamc多值描述符复合属性元素说明:1.实体（Entities）:实体是首要的数据

7、对象，常用于表示一个人、地方、某样事物或某个事件。一般名称单词的首字母大写。辨别强实体与弱实体：*在现实世界中，有时某些实体对于另一些实体有很 强的依赖关系，即一个实体的存在必须以另一实体 的存在为前提。前者就称为“弱实体”，后者称为“强实体”。比如在人事管理系统中，职工子女的 信息就是以职工的存在为前提的，子女实体是弱实 体，子女与职工的联系是一种依赖联系。强实体内部有唯一的标识符。 弱实体(weak entities )的标识符来自于一个或多 个其它强实体。弱实体用双线长方形框表示，依赖 于强实体而存在。2. 关系(Relationships):关系表示一个或多个实体之间的联系。关系依赖于

8、实体，一般没有物理概念上的存在。关系最常用来表示实体之间，联系可分为以下3种类型：a. 一对一联系(1 : 1):例如，一个部门有一个经理， 而每个经理只在一个部门任职，则部门与经理的联 系是一对一的。b. 一对多联系(1 : N):例如，部门与员工之间存在一 对多的联系“所属”，即每个部门可以有多个员工， 但是每个员工只能隶属一个部门c. 多对多联系(M : N):例如，学生与课程间的联系(“学”)是多对多的，即一个学生可以学多门课程， 而每门课程可以有多个学生来学。联系也可能有属 性。例如，学生“学”某门课程所取得的成绩，既 不是学生的属性也不是课程的属性。由于“成绩”既依赖于某名特定的学

9、生又依赖于某门特定的课程， 所以它是学生与课程之间的联系“学”的属性。关系的构图是一个菱形，关系的名称一般为动词。3. 属性(Attributes ):属性为实体提供详细的描述信息。一个特定实体的某个属性被称为属性值。Employee实体的属性可能有：emp-id, emp-name, emp-address, phone-no。属性一般以椭圆形表示，并与描述的实体连接。属性可被分为两类：a. 标识符(identifiers ) : Identifiers 可以唯一标识实体的一个实例(key )，可以由多个属性组成。ER图中通过在属性名下加上下划线来标识。b. 描述符(descriptors

10、):多值属性(multivalued attributes )用两条线与实体连接，eg : hobbies属性(一个人可能有多个 hobby，如reading , movies)。 复合属性(Complex attributes )本身还有其它属性。1.绘制实体对象及关系2.确定表之间的关系，完成E-R图多对bbsUser/ 、bbsSection用户，版主）3埋 / X /（版块丄 F断拴版城詁子珀号營理、cgyjgxjgxgij；迫虬用户（BBSUatr匝块(BBSSsctlon)7Q/登贴(BBSTopic)Y跟醮盅貼(BBSRfpty)”1该鮎心刚2.将E-R图转换为表*具体做法1.实

11、体类型的转换a. 将每个实体类型转换成一个关系模式。b. 实体的属性即为关系模式的属性。c. 实体标识符即为关系模式的主键。标识每个表的主键列，需要注意的是：没有主键的表添加ID编号列，它没有实际含义，用于做主键或外键，例如用户表中的“UID ”列，版块表中添加“ SID”列，发贴表和跟贴表中的“ TID ”列注意：database system concepts里面提到主键的定义是表中唯一的标识，“应该选择从不变化或极少变化的属性”。否则，建议大家根据实际情况使用没有意义的逻辑主键在表之间建立主外键，体现实体之间的映射关系UID主键TID主键BKSUier丄论転用）轰|鬼户掘匸AttJT1D

12、 林识主ETHUNjm* UU户耗耳)7Mu（nkHr。點 了昭乌 1UPiKsword iFFj jTOOUEmail 彳电 jPf1 .1U40 （肚貼人UBidh/ (1tH)TH叩 1/C ount f HV B)U $* UCIU怕厂址TTopIc上吃）USIiltmant 门II户無许TCofltvnto i. iT)UR*gBai& 1逹币日訓,TTime OWH)UttoUJChckCount .JntdMT时何RID主键KID （肛出主僅冒）ftNumtr I 狀 f ) ftTlD 阪的 tU) RSID (Jffd厳輪瞪号】RUlO点MA勒ftEmolicon 1ftT舞

13、此I ft)RCmiIsNIi C tFI) RTime t %MHMSID主5ID犢皿弓S冷nw 4脈址SMaaierlD “3 SWint nt SClitkCvuntS lopicCount实体间关系的转换a. 对于二元联系，按各种情况处理，如下面所示。二元关系ER图转换成的关系联系的处理主键外键1: 1（2个关 系）模式A模式B（有两 种） 处理方 式1 : 把模式 B的主 键，联 系的属 性加入 模式A 处理方 式2 : 把模式 A的主 键，联 系的属 性加入（略）（依据 联系的 处理方 式） 方式1 :模 式B的 主键为 模式A 外键 方式2 :表A的主 键为表 B的外 键1NMN

14、屯话系JiffNA数丄Q姓名教学管理ER图的转换模式B(2把模式(略)模式A个关A的主的主键系)键，联为模式模式系的属B的外A模式B性加入 模式B键(3联系类两端实两端实个关型转换体类型体类型系)成关系的主键的主键模式模式一起构分别为AA-B;成模式模式A-模式模式A-B主B的外B模式A-BA-B的属性：(a) 联系的属性(b) 两端实体 类型的 主键键键转换步骤:（1）把三个实体类型转换成三个模式： 系（系编号，系名，电话） 教师（教工号，姓名，性别，职称） 课程（课程号，课程名，学分）对于1:1联系“主管”，可以在“系”模式中加 入教工号（教工号为外键）。 系（系编号，系名，电话，主管人的

15、教工号） 对于1:N联系“聘用”，可以在“教师”模式中加入系编号和聘期两个属性（系编号为外键） 教师（教工号，姓名，性别，职称，系编号， 聘期）（3）第三步：对于M:N联系“任教”，则生成一个新 的关系模式：任教（教工号，课程号，教材）（4）第四步：转换成以下模式：关系模型主键外键系（系编号，系名，电系号主管人教话，主管人的教工号）工号教师（教工号，姓名， 性别，职称，系编号， 聘期）教工号系编号课程（课程号，课程名，学分）课程号任教（教工号，课程教工号，教工号，号，学分）课程号课程号b. 一元联系类型的转换和二元联系类型的转换类似。职工之间存在上下级联系，即1:N联系*駅工可以转换成以下模式

16、:关系模型主键职工（工号，姓名，年龄，性别，经理工号）工号工厂的零件之间存在着组合关系（ M:N联系） 零件（零件号，零件名，规格） 组成（零件号，子零件号，数量）可以转换成以下模式:关系模型主键零件（零件号，零件名，规格）零件号组成（零件号，子零件号，数 量）零件号，子零件号c. 三元联系类型转换(1) 若实体间联系是1:1:1，可以在三个实体类型转换成的三个关 系模式中任意一个关系模式的属性中加入另两个关系模式的键(作为外键)和联系类型的属性。(2) 若实体间联系是1:1:N，则在N端实体类型转换成的关系模 式中加入两个1端实体类型的键(作为外键)和联系类型的属 性。(3) 若实体间联系是

17、1:M:N，则将联系类型也转换成关系模式， 其属性为M端和N端实体类型的键(作为外键)加上联系类型 的属性，而键为 M端和N端实体键的组合。(4) 若实体间联系是M:N:P，则将联系类型也转换成关系模式， 其属性为三端实体类型的键(作为外键)加上联系类型的属性， 而键为三端实体键的组合。三元联系的ER图技术员在项目中使用手1:1:1关系册的关系I cvliiiidanProjcci说明：一名技术主管对于每一个项目使用一本说明手册 一本说明手册对于每一个项目属于一名技术主管 一个项目有一个技术主管一本说明书可以转换成以下模式:关系模型主键Tech nician (Tech nicianlD ，T

18、ech nicianN ame)Tech nicianlDNotebook ( NotebookID ，NotebookName )NotebookIDProject ( ProjectID ，ProjectName,NotebooklD,Tech nicia nID)ProjectID1:N:M经理管理项目与工程师的关系说明：* 一名经理手下的一名工程师可能参与多个项目* 一名经理管理的一个项目可能会有多名工程师* 做某一个项目的一名工程师只会有一名经理可以转换成以下模式:关系模型主键Engineer ( EngineerID ，Engin eerName )Engin eerIDMa na

19、ger ( Man agerID ，Man agerName )Ma nagerlDProject ( ProjectID ，ProjectName )ProjectIDMa nages(E ngi neerlD,ProjectlD,Ma n agerlD)Engin eerID,Proje ctID三元联系ER图的转换商品号可以转换成以下模式:关系模型主键仓库（仓库号，仓库名名，地 址）仓库号商店（商店号，商店名）商店号商品（商品号，商品名）商品号进货（商店号，商品号，仓库 号，日期，数量）商店号，商品号，仓库号规范化关系模型表格仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创

20、 建好的表结构范式是具有最小冗余的表结构。这些范式是：a. 第一范式(1st NF First Normal Fromate)b. 第二范式(2nd NF Seco nd Normal Fromate)c. 第三范式(3rd NF Third Normal Fromate)1.第一范式(lNF)*第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元(也称为最小的原子单 元)，则满足第一范式(1NF )平椅砂第蘊实的釁廉BuywiDAddress1中園北麻币2美国妞釣市3英国利物浦4日本东总市* B B iBiryerlDCountryChy1中国北隶1中国北京4H*2美匡堀釣Bl

21、d!b a ttaEifiaxsIODI5D0.D060.DOIQ027W.O010030000SQ.DO2.第二范式(2NF)*所谓第二范式，首先要求满足第一范式，并且除了主键以外 的其他列，都依赖与该主键，则满足第二范式(2NF )*第二范式要求每个表只描述一件事情例如:m瞬究ttjg号表关系虽满足INF，但不满足2NF ,原因：因为它的非主属性不完全依赖于由教师代码和课题 代码组成的主关键字。姓名，职称部分依赖于教师代码研究课题名称部分依赖于研究课题号导致问题：这种关系会引起数据冗余更新异常，当要插入新的研究课题数据时，往往缺少相 应的教师代码，以致无法插入当删除某位教师的信息时，常会引

22、起丢失有关研究课题 信息。解决办法：将一个非2NF的关系模式分解为多个 2NF的关系模 式。表被分解为如下三个关系：教师表：教师代码、姓名、职称；课题表：研究课题号、研究课题名；教师与课题表：教师代码、研 究课题号。这些关系都符合2NF要求。字段例子001姓名谨三硏究課题代号A001研究滦題岔关于踰题数晒視谦题黄系五字段例于|001职称教怖志宇段例于硏虫探諏代号A001冊究课逊名关于林加谦猱1字段例子研究课再代号A 001教肺代诃001 |3.第三范式（3NF）如果一个关系满足2NF，并且除了主键以外的其他列都不传递 依赖于主键列，则满足第三范式（3NF）例如：不符合第三葩式的产品关系半产品代

23、码产品色生产厂名成产厂:表产品关系属第二范式，但不是第二范式。原因：由于生产厂名依赖于产品代码（产品代码惟一确定该产品 的生产厂家），生产厂地址又依赖于厂名，因而，生产厂地址传 递依赖于产品代码。导致问题：存在数据冗余更新异常问题当删除一个产品记录时，可能造成所连带的唯一一条生产 厂商信息连带删除掉，造成数据丢失。解决办法：消除传递依赖关系的办法，是将原关系分解：产品表：产品代码、产品名、生产厂名； 生产厂表：生产厂名、生产厂地址。3NF消除了插入、删除异常及数据冗余、修改复杂等问题，已经是比较规范的关系?段例子产品代円001分屈件产厂名爆州日化生产地址郑州4.规范化和性能 范式可以避免数据冗

24、余，减少数据库的空间，减轻维护数据完整性的麻烦， 但是操作难，因为需要联系多个表才能得到所需要数据，而且越高范式性 能就会越差。要权衡是否使用更高范式是比较麻烦。为满足某种商业目标，数据库性能比规范化数据库更重要通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所 需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询 进行规范化的同时，还需要综合考虑数据库的性能。5.案例假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员 等公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、 技术员）等公司按照

25、工时和小时工资率支付工资，小时工资率由职工的职务决定 （例如，技术员的小时工资率与工程师不同）公司定期制定一个工资报表和项目工时表，如图 工资报表工程号工程名称职工号姓名职务小时工资率工时实发工资A1花园大厦1001齐光明工程师6513845.001002李思岐技术员6016960.001004葛宇宏律师60191140.00小计2945.00A2立交桥1001齐光明工程师6515975.001003晌亮工人5517935.00小计1910.00A3临江饭店1002李思岐技术员60181080.001004葛宇洪技术员6014840.00小计1920.00项目工时表工程号工程名称职工号姓名职务小时工资率工时A1花园大厦1001齐光明工程师6513A1花园大