数据库的物理设计、运行与维护(ppt 67页).ppt

1、第九章 数据库系统设计,之二,本章学习要点: （1）掌握数据库设计的基本步骤。 （2）熟悉需求分析过程。 （3）掌握数据库设计的概念结构设计。 （4）掌握数据库设计的逻辑结构设计。 （5）了解数据库的物理设计。 （6）掌握E-R图设计、E-R图向关系模式的转换、及优化方法。 （7）掌握索引的概念、分类、基本方法。,9.1数据库设计概述 9.2需求分析 9.3概念结构设计 9.4逻辑结构设计 9.5数据库的物理设计 9.6数据库实施 9.7数据库运行与维护 9.8小结,内容提要:,9.4逻辑结构设计（重点）,逻辑结构设计的任务 基本E-R模型 数据模型 (概念设计阶段) (DBMS) 关系数据库

2、逻辑结构设计的步骤 (1)将概念模型转换为关系数据模型。 (2)将关系数据模型转换成特定DBMS支持下的关系数据模型。 (3)对关系数据模型进行优化。,转换,关系模型的逻辑结构是一组关系模式的集合。 目标：将实体、实体的属性和实体之间的联系转换为关系模式。,9.4.1E-R模型向关系模型的转换 1、转换原则及具体方法：,（1）一个实体转换为一个关系模式： 关系的属性：实体的属性； 关系的码：实体的码。 （2）联系的转换原则 1）1:1联系 A、转换为独立的关系模式： 属性=两关联实体各自的主码+联系本身的属性 码=每个关联的实体主码的均是候选码。 B、与任一方关系模式合并： 将一方实体的码与联

3、系属性一并加入另一方。 主码：不变。,实例：部门与经理：1：1联系 各实体属性 部门：部门号、部门名称 经理：工号、姓名、性别、出生年月。 联系的属性：任期 A、联系转换为独立的关系模式： 部门（部门号，部门名称） 经理（工号、姓名、性别、出生年月） 负责（部门号，工号，任期） B、与任一方关系模式合并： 部门（部门号，部门名称，工号，任期） 经理（工号、姓名、性别、出生年月）,部门,经理,负责,1,1,2）1:m联系: A、转换为独立的关系模式 属性=两关联实体各自的主码+联系本身的属性 主码=“m”方实体主码的主码。 B、将为“1”方的实体的主码（作为外码）与联系属性一并加入“m”方实体。

4、 合并后，码不变。,3）m:n联系：须单独建立一个关系模式。 属性=两关联实体各自的主码+联系本身的属性 主码=两关联实体主码的组合。 4）同一实体之间的1:m联系 在该实体对应的关系模式中加入为“1”实体的主码和联系的属性。,实例：,关系模式为： 员工（工号，姓名，性别，出生年月，经理工号，民意测验）。,6）超类和子类的转换 超类、子类实体可转换为一个关系，并将超类实体的主码加到子类实体中。 实例：,员工,管理员,职工号,性别,出生年月,参加工作时间,姓名,部门号,学历,岗位津贴,级别,职务,职称,学历,专业职称,该E-R模型转换为关系模式为： 员工（职工号，姓名，性别，出生年月，参加工作时

5、间，部门号） 部门经理（职工号，学历，级别，岗位津贴） 管理员（职工号，职务，职称） 工程师（职工号，学历，专业职称）,为方便查询，还可在超类实体中增加一个指示属性，根据指示的值直接查询子类实体表。 员工（职工号、姓名、性别、出生年月，参加工作时间，部门号，员工类别） 7）同一实体间的m:n联系 须单独建立一个关系模式。 属性=两关联实体各自的主码+联系本身的属性 主码=两关联实体主码的组合。 注：由于“联系”仅涉及一个实体，加入的主码要取不同的名字。,实例：,零件号,零件,组装,名称,型号,数量,价格,n,m,关系模式为： 零件（零件号，名称，型号，价格） 组装（零件号，组装零件号，数量）,

6、9）三个或三个以上实体间的m:n联系 须单独建立一个关系模式。 属性=所联系的各实体的主码+联系本身的属性 主码=关联实体主码的组合。,实例：,零件号,零件,供应,名称,型号,数量,价格,n,m,项目,供应商,p,项目号,工期,项目名称,地址,供应商名,供应商号,该E-R模型转换为关系模式为：,零件（零件号，名称，型号，价格） 项目（项目号，项目名称，工期） 供应商（供应商号，供应商名，地址） 供应（零件号，项目号，供应商号，数量）,综合实例：,各实体的属性为： 车间：车间号、车间主任姓名、地址和电话； 工人：职工号、姓名、年龄、性别、工种; 工厂：工厂名、厂长名； 产品：产品号、产品名、型号

7、、价格； 零件：零件号、零件名称、重量、价格； 仓库：仓库号、仓库主任姓名、电话； 家属：家属姓名、亲属关系。,将E-R模型转换为关系模式为：,工厂（工厂名、厂长名）； 车间（车间号，车间主任姓名，地址，电话，工厂名）； 工人（职工号，姓名，年龄，性别，工种，车间号）； 仓库（仓库号，仓库主任姓名，电话，工厂名） 产品（产品号，产品名，型号，价格，车间号，仓库号）； 零件（零件号，零件名称，重量，价格，仓库号）； 制造（车间号，零件号）； 装配（零件号，产品号）； 亲属（职工号，家属姓名，亲属关系）,9.4.2向特定的RDBMS的模型转换。（略） 9.4.3关系数据模型的优化 根据应用需要适当

8、地修改、调整数据模型结构。 1、应用关系规范化理论对关系模式进行优化，具体步骤如下： （1）确定数据依赖：确定每个关系模式内部各个属性之间的数据依赖以及不同关系模式属性之间的数据依赖。 （2）对各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。,（3）确定各关系模式为第几范式。 （4）按照需求分析阶段的需求，确定要对哪些模式进行合并或分解。 采用技术：反规范化 （5）对关系模式进行必要的分解，提高数据操作的效率和存储空间利用率。 两种分解方法：水平分解 垂直分解,2、反规范化 （了解） 一个完全规范化的设计并不总能生成最优的性能，因此通常是先按照3NF设计，如果有性能问题，再通过反规范

9、来解决。 数据库中的数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度，但是对完全规范的数据库查询，通常需要更多的连接操作，从而影响查询的速度。因此，有时为了提高某些查询或应用的性能而破坏规范规则，即反规范。,是否规范化的程度越高越好? 这要根据需要来决定，因为“分离”越深，产生的关系越多，关系过多，连接操作越频繁，而连接操作是最费时间的，特别对以查询为主的数据库应用来说，频繁的连接会影响查询速度。所以，关系有时故意保留成非规范化的，或者规范化以后又反规范了，这样做通常是为了改进性能。,（了解）,例如，某校成绩管理系统，学生基本信息表s

10、tudent（sno，sname，sex，birth，zgrade）,其中，总成绩就违反规范，其中的值可以通过下面的查询获得： select sno,sum(grade) from sc group by sno,（了解）,如果student中没有该列，若想获得该生的姓名和总成绩，则需要做连接操作： select sname,zgrade from student,(select sno,sum(grade)as grade from sc group by sc.sno) s where s.sno=student.sno 如果经常做这种查询，则就有必要在student表中加入列zgrade

11、，相应的代价则是必须在表student上创建增、删、改的触发器来维护student表上zgrade列的值。类似的情况在决策支持系统中经常发生。,（了解）,反规范的优点是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目；加快了查询速度。 缺点：可能出现数据的完整性问题；会降低修改速度。 因此决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其它方法经常能够解决性能问题，而不必采用反规范这种方法。,3、几种常用的反规范化方法： 在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算列(如合计)、数据的物理存储位置等。 常用的反规

12、范技术有增加冗余列、增加派生列、重新组表和分割表。,（了解）,（1）增加冗余列 增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。 例如，前面例子中，如果经常检索一门课的任课教师姓名，若课程表中仅有教师编号teacherno 列，则需要做course和teacher表的连接查询: select cname,tname from course,teacher where course.teacherno=teacher.teacherno,（了解）,由于要频繁的查询每门课程任课老师的姓名，则就可以在course表中增加一列tname，就不需要做连接操作了。 增加冗长余列的优点：

13、 查询时可避免连接操作。 增加冗长余列的缺点： 需要更多的磁盘空间，同时增加表维护的工作量。,（2）增加派生列 增加派生列指增加的列来自其它表中的数据，由它们计算生成。 优点：在查询时减少连接操作，避免使用集函数。 缺点：具有与冗余列。 例如，前例student表中的zgrade列就是派生列。,（了解）,（3）重新组表 重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。 例如，用户经常需要同时查看课程号，课程名称，任课教师号，任课教师姓名，则可把表course(cno,cname,tno)和 表teacher(teacherno,tname

14、) 合并成一个表: course(cno,cname,teacherno,tname)。这样可提高性能，但需要更多的磁盘空间，同时也损失了数据在概念上的独立性。,（了解）,(4)分割表 有时对表做分割可以提高性能。表分割有两种方式： 1)水平分解：把（基本表）关系的元组分为若干子集合，定义每个子集合为一个子关系，以提高系统的效率。 水平分割通常在下面的情况下使用: A、表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。,（了解）,B、表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。

15、C、需要把数据存放到多个介质上。 分解原则：80/20原则。 例如 、法规表law就可以分成两个表active_law和inactive_law。Activea_law表中的内容是正生效的法规，是经常使用的;而inactive_law表则是已经作废的法规，不常被查询，但须保留的。,（了解）,水平分割的缺点：会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。 在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。,（了解）,2）垂直分割：把主码和一些列放到一个表，然

16、后把主码和另外的列放到另一个表中。（即把关系模式R的属性分解为若干子集合。） 如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要join操作。 注：要确保无损连接性和保持函数依赖。,（了解）,4、反规范技术需要维护数据的完整性 无论使用何种反规范技术，都需要一定的管理来维护数据的完整性。 常用的方法：批处理维护 应用逻辑 触发器 1）批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性

17、要求不高的情况下使用。,（了解）,2）用应用逻辑：数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。 该方法实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。 3）使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题的最好的办法。,（了解）,9.4.4设计用户子模式(创建视图) 全局关系模型设计完成后，还应根据局部应用的需求，结合具体DBMS的特点，设计用户的子模式。 设计子模式时应注意考虑用户的

18、习惯和方便性，主要包括： （1）使用更符合用户惯的别名 如按不同部门的习惯给视图中的列取名。 （2）以不同级别的用户定义不同的视图，以保证系统的安全性。,例如、设有一关系模式：产品（产品编号，产品名称，型号，价格，生产车间，生产负责人，产品成本，产品合格率，质量等级） 面向一般顾客的视图： 产品1（产品编号，产品名称，型号，价格，质量等级） 面向质量监督部门的视图： 产品格（产品编号，产品名称，型号，生产车间，产品合格率，质量等级）,（3）简化用户对系统的使用 针对某些局部应用中经常使用某些十分复杂的查询情况，为方便用户，可先将这些复杂查询定义为视图，用户每次只需对定义好的视图进行查询，大大简

19、化了复杂度。,9.5数据库的物理设计 （主考虑是否建立相关索引）,什么是数据库的物理设计 数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于给定的计算机系统。 为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程，就是数据库的物理设计。,逻辑 模型,数据库物理设计的步骤：,9.5.1 数据库的物理设计的内容和方法,设计物理数据库结构的准备工作 1、充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数。 2、充分了解所用RDBMS的内部特征，特别是系统提供的存取方法和存储结构。,选择物理数据库设计所需参数 数据库查询事务 （1）查询的关系 （2）查

20、询条件所涉及的属性 （3）连接条件所涉及的属性 （4）查询的投影属性 数据更新事务 （1）被更新的关系。 （2）每个关系上的更新操作条件所涉及的属性。 （3）修改操作要改变的属性值。 （4）每个事务在各关系上运行的频率和性能要求。,关系数据库物理设计的主要内容 1、为关系模式选择存取方法(建立存取路径)。 2、设计关系、索引等数据库文件的物理存储结构。 9.5.2 关系模式存取方法选择 DBMS常用存取方法 1、索引方法，目前主要是B+树索引方法 2、聚簇（Cluster）方法 3、HASH方法,9.5.2.1索引方法 选择索引存取方法的主要内容： 根据应用要求确定 对哪些属性列建立索引 对哪

21、些属性列建立组合索引 对哪些索引要设计为唯一索引,9.5.2.2聚簇存取方法 什么是聚簇 为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码/聚集键）上具有相同值的元组集中存放在连续的物理块称为聚簇/聚集。 许多关系型DBMS都提供了聚簇功能。 聚簇存取的优点 1、大大提高按聚簇属性进行查询的效率 2、节省存储空间,聚簇的局限性（了解） 1、聚簇只能提高某些特定应用的性能 2、建立与维护聚簇的开销相当大 对已有关系建立聚簇，将导致关系中元组移动其物理存储位置，并使此关系上原有的索引无效，必须重建。 当一个元组的聚簇码改变时，该元组的存储位置也要做相应移动。,9.5.2.3HA

22、SH存取方法 当一个关系满足下列两个条件时，可以选择HASH存取方法： 1、该关系的属性主要出现在等值连接条件中或主要出现在相等比较选择条件中。 2、该关系的大小可预知，而且不变； 3、该关系的大小动态改变，但所选用的DBMS提供了动态HASH存取方法。,9.5.3 确定数据库的存储结构（了解） 确定数据库物理结构的内容： 1、确定数据的存放位置和存储结构： 关系、索引、聚簇、日志、备份。 2、确定系统配置。 影响数据存放位置和存储结构的因素： （1）硬件环境 （2）应用需求：存取时间、存储空间利用率、维护代价。 这三个方面常常是相互矛盾的。 例如：消除一切冗余数据虽能够节约存储空间和减少维护

23、代价，但往往会导致检索代价的增加。必须进行权衡，选择一个折中方案。,1、确定数据的存放位置： （了解） 基本原则 根据应用情况将 易变部分与稳定部分； 存取频率较高部分与存取频率较低部分 分开存放，以提高系统性能。 实例1：数据库数据备份、日志文件备份等由于只在故障恢复时才使用，而且数据量很大，可以考虑存放在磁带上。,2. 确定系统配置（了解）,DBMS产品一般都提供了一些存储分配参数 同时使用数据库的用户数 同时打开的数据库对象数 使用的缓冲区长度、个数 时间片大小 数据库的大小 填充因子 锁的数目 等等,9.5.4 评价物理结构（了解）,评价内容 对数据库物理设计过程中产生的多种方案进行细致的评价，从中选择一个较优的方案作为数据库的物理结构。,评价方法 定量估算各种方案 存储空间 存取时间 维护代价 对估算结果进行权衡、比较，选择出一个较优的合理的物理结构。 如果该结构不符合用户需求，则需要修改设计。,9.6 数据库的实施,数据库实施的工作内容 用DDL定义数据库结构 组织数据入库 编制与调试应用程序 数据库试运行,在数据库运