第 11 章物理数据库设计

第11章物理数据库设计 面向对象系统分析与设计 Joey F. George, Dinesh Batra, Joseph S. Valacich, Jeffrey A. Hoffer

本章目标 学习本章后应该能够： • 设计数据库域. • 评估反规范化的情形. • 设计文件组织结构. • 设计对象关系特征.

11.1 物理数据库设计 • 数据库设计的一部分，主要考虑数据访问的效率 • 数据处理速度，存储空间, 和数据操作是物理数据库设计的关键内容

有时，分析人员和设计人员可以由同一人员担任有时，分析人员和设计人员可以由同一人员担任交付产品

物理数据库设计的步骤

11.2 结构化查询语言SQL • 结构化查询语言可以读作 “sequel” • 创建和使用关系型数据库的标准语言 • ANSI 标准 • SQL-92 – 通用的 • SQL-99 – 包含对象-关系特征

常用的SQL命令 • CREATE TABLE – 用于定义表结构和实现表间的联接 • INSERT – 用于往表中增加新的行 • SELECT – 使用特殊格式或选择标准从表中抽取数据 • UPDATE – 用于对已有的表中数据的更新 • DELETE – used to remove rows from tables

1. CREATE TABLE 命令实例 一个名为DEPT的表被创建, 有一个数字域和两个文本域；其中的数字域为主键.

2. INSERT命令实例 • 这条命令往DEPT表中插入一个新的行 • DEPTNO的值为 50 • DNAME的值为“DESIGN” • LOC的值为“MIAMI”

3. SELECT命令 • SELECT和FROM命令字是必须的. • 其它的命令字为可选. • WHERE 是比较常用的可选命令字.

(1)SELECT 命令实例 1 • Select * from DEPT; • 结果: DEPT表中的所有行和域

(2)SELECT 命令实例2 • Select * from EMP where ENAME = ‘SMITH’; • 结果: 雇员“Smith”的所有域

(3)SELECT 命令实例3 • Select EMPNO, ENAME From EMP where JOB = ‘SALESMAN’ order by ENAME; • 结果: EMP表中的工作为salesmen的所有雇员编号、姓名和工作域, 以姓名排序

(4)连接操作 SELECT 命令实例4 • Select EMPNO, ENAME, DNAME from EMP, DEPT where EMP.DEPT_NO = DEPT.DEPT_NO and DEPT.LOC = ‘CHICAGO’;

结果: 所有雇员的编号和姓名 (来自EMP表）, 以及相关联的部门名称, 通过基于DEPT_NO的联接表获取. • 只有工作单位在Chicago的雇员信息才会显示连接操作是将主表中的主键与关联表中的外键相匹配.

(5) 聚集查询 • 产生一系列记录的汇总信息的查询例如sums, counts, or averages • 聚集功能体现在SELECT的 (SUM, AVG, COUNT)子句中 • 聚集可以使用HAVING 子句过滤或使用GROUP BY 子句分组

SELECT 命令实例5 • Select JOB, Avg(SALARY) from EMP Group by JOB Having Avg(SALARY) >= 3000; • EMP表中的每种工作的名称和平均薪水. • 只有平均工资高于$3000 会显示注意 clerks and salesmen 没有出现，是因为这些类型的工作低于 $3000.

4. 数据操作: Update & Delete • Update EMP set SAL = 3000 where EMPNO = 7698; 修改现有雇员(7698)的薪水 • Delete from EMP where EMPNO = 7844 将雇员7844 从EMP 表中移除

11.3 设计域 • 域 – 诸如DBMS类的系统软件所能识别的应用数据的最小单位 • 在概念和逻辑数据模型中域一般被识别为属性 • 设计域时应考虑identity标识符, 数据类型, 大小和限制

11.3.1 选择数据类型 (1) 数据类型 –由系统软件识别的表示组织数据的一种编码模式

(2) 选择数据类型时应考虑的问题 在下列四个目标之间平衡: • 最小化存储空间 • 表示域的所有可能值 • 提高域的数据完整性 • 支持域上想要的所有数据操作

(3)创建和使用复合数据类型 实例: 将一个复合属性映射到多个有不同数据类型的域

11.3.2 控制数据完整性 • 缺省值 – 没有明确输入一个域的值时采用的值 • 格式控制– 限制域中各个位置的宽度和可能值 • 范围控制 – 取值必须在允许的范围内 • 参照完整性 – 外键与主键的匹配 • 空值控制 – 决定域的取值能否为空

参照完整性是非常重要的，它能确保数据间的关系是准确而一致的参照完整性是非常重要的，它能确保数据间的关系是准确而一致的

11.4 设计物理表和反规范化 • 外键是物理数据库设计中的主要方法 • 物理数据库设计不同于逻辑设计(规范化) • 物理数据库设计的目标: 使用主存& 处理速度

反规范化是根据行和域使用的密切关系并根据表的检索和更新频率将规范化关系合并为物理表的过程。反规范化是根据行和域使用的密切关系并根据表的检索和更新频率将规范化关系合并为物理表的过程。 • 反规范化提升了访问的速度，但是降低了数据集成度并且增加了数据冗余；因此要根据访问实践判断是否应该使用反规范化。

反规范化情况1: 这会导致某些行的应用数据产生空值.

反规范化情形2:

这将导致在CanSupplyDR表中的某些行中商品数据的描述出现重复.这将导致在CanSupplyDR表中的某些行中商品数据的描述出现重复.

反规范化情形3: regionManager 数据重复

11.5 文件组织 • 文件组织: 物理地安排一个文件的行对象的技术 • 文件组织的主要目的是优化数据访问速度和改善性能

11.5.1 辅助存储器概念 • 块：从辅助存储器中检索数据的单位 • 盘区：一组连续的块 • 扫描：从块到快对文件的一次完整读取 • 分块因子：可以装入一个快的行对象数目

读取块的时间取决于寻道、旋转和传送. • 平均扫描时间= 表中的行数目÷分块因子× 读取块的时间

什么是堆? • 没有组织结构的文件 • 要提取数据就必须对整个表进行扫描 • 堆只适用于小型的缓冲表

11.5.2 散列文件组织 • 使用一种算法将一个键值转换为一个逻辑行地址的技术 • 对随机存取非常有用, 但是对顺序存取没有用

11.5.3 索引文件组织 • 包含索引的存储结构, 索引是指向行地址的键值和指针 • 索引文件组织可以实现快速的随机和顺序访问 • 索引后的文件可以实现快速查询, 但需要额外的空间用于插入、删除和修改

使用B+ 树索引的随机访问处理 索引通常以B+ 树的方式实现 B+树就是平衡树, 当记录被添加时保留顺序的升序. 10ms 10ms 10ms

使用索引的顺序处理 • 索引时间=20ms+n×10ms=(n+2)×10ms≈ n×10ms • 当索引时间低于扫描时间, 就应该采用索引结构组织文件使用索引处理范围查询 • 当范围很小时, 应采用索引 • 当索引时间小于扫描时间,应采用索引

选择文件组织时要考虑的因素 • 文件大小 • 数据查询的频率 • 数据更新的频率 • 与主键和外键相关的因素 • 与非键属性相关的因素

使用索引的启发式方法

11.6 对象关系特征的设计 对象-关系数据库可以支持: • 泛化和继承 • 聚合多值属性 • 对象标识符 • 引用关系(指针)

11.6.1 泛化 (基于Oracle 9i/10g)

11.6.2 聚合 (基于Oracle 9i/10g)

11.6.3 多值属性(基于Oracle 9i/10g)

11.6.4 对象标识符(基于Oracle 9i/10g) Select REF(E), EmpName from HOURLY_EMP E

第 11 章 物理数据库设计