数据库设计贯通逻辑及物理设计

1、数据库设计贯通逻辑及物理设计 数据库设计贯通 逻辑结构设计部分 数据库设计贯通逻辑及物理设计 参考书目： 数据库系统概论 高等教育出版社 王珊 萨师煊 数据库系统教程 高等教育出版社 施伯乐等 数据库设计、应用开发与管理 电子工业出版社 Michael V.Mannino 著 唐常杰 等译 UML和统一过程-实用面向对象的分析和设计 机械工业出版社 方贵宾 等译 UML应用建模实践过程 机械工业出版社尤克滨 编著 数据库设计贯通逻辑及物理设计 逻辑结构设计步骤 ER图向关系模型的转换 关系模型的优化 视图（view）设计 数据库设计贯通逻辑及物理设计 ER图向关系模型的转换 Entity 1:

2、M relationship N:M relationship Identifying relationship Optional 1-M relationship Generalization Hierarchy 1-1 Relationships 数据库设计贯通逻辑及物理设计 基本转化规则 basic conversion rules 规则一、Each entity type becomes a table(实 体转化为表，实体的主码成为表的主码， 不包括弱实体，属性转化成列) 数据库设计贯通逻辑及物理设计 基本转化规则 basic conversion rules 规则二、Each 1-

3、M relationship becomes a foreign key in the table corresponding to the child entity （一对多联系转化为子表中参照父表的外键，如 果父表对子表来说是强制mandatory的，则外键不 能为NULL）或或 1-M relationship becomes a table with a primary key from M-side of the relationship（也可转化成独 立的关系，与该联系相连的各实体的码以及联系 本身的属性组成关系的属性，关系的码为M端实 体的码）【少采用，比较规则五】 数据库设计贯

4、通逻辑及物理设计 规则三、 Each M-N relationship becomes an associative table with a combined primary key.(多对多联系转化成为一张独立的表，其 主码由联系两端的实体的主码共同组成) 数据库设计贯通逻辑及物理设计 规则四、 Each identifying relationship adds a column to a primary key.（标识依赖在转化 时，将父表的主码添加到弱实体中，弱实 体的主码包括：1 弱实体自己的主码 2标识 实体【也就是父表】的主码） 数据库设计贯通逻辑及物理设计 规则五、Optio

5、nal 1-M Rule （可选的一对多 联系，所谓“可选”，指“1”端，也就是 父表端不强制，最小基数可为0的情况） 如： 数据库设计贯通逻辑及物理设计 下图表示是一个可选的一对多联系，学生可以住 宿舍，也可以不住宿舍（比如有回家住的学生）， 这里，dorm对student来说是可选的，因此 dormNo作为Student表的外码，可以为空（Null） 为了避免这种空值，我们将为了避免这种空值，我们将live联系转化成关系表联系转化成关系表 Optional 数据库设计贯通逻辑及物理设计 该表的主码是子表（Student实体）的主码 该表的外码是两端的实体的主码的组合 该表的外码不允许空NU

6、LL 注意：规则五可以避免NULL，但是多生成了一个表，增加 了查询的复杂性，许多应用中，避免额外的表比避免NULL 更重要 数据库设计贯通逻辑及物理设计 规则六、转化泛化层次结构。RDBMS不直 接支持泛化，各CASE工具的实现方法有别 数据库设计贯通逻辑及物理设计 规则七、1:1relationship （转化一对一联系） 方法一：直接将1：1联系转化成两个外码（可 能会产生NULL） 方法二：如果联系的某一端实体是可选的 （optional），则可以在另一个实体中取消外码， 以消除NULL 举例： 数据库设计贯通逻辑及物理设计 方法一： 冗余联系冗余联系 数据库设计贯通逻辑及物理设计 方

7、法二： 在Power Designer中可通过设置dominant关系来实现 数据库设计贯通逻辑及物理设计 EmpNo EmpName Employee OfficeNo OffAddress OffPhone Office Manages CREATE TABLE Office ( PRIMARY KEY (OfficeNo) , FOREIGN KEY(EmpNo) REFERENCES Employee, UNIQUE (EmpNo) ) 数据库设计贯通逻辑及物理设计 逻辑模型的优化 规范化 分析数据依赖关系，消除部分依赖、传递依赖和多值 依赖 反规范化 涉及到多个关系的查询时，需进行关

8、系的连接运算， 产生很高的代价 关系模式的分解 80/20原则，大关系中，经常被使用的数据只占所有数 据的20%，把这20%分解出来，形成独立的子关系 水平分解和垂直分解 注意保持无损连接性和函数依赖 数据库设计贯通逻辑及物理设计 考虑查询某笔贷款的贷款人和担保人等情况 在表Loan中存储LenderNo和LenderName是不符合BCNF范式的，但可以 减少表Loan和Lender的连接 仔细定夺，考虑是否要在Loan表中增加来自Guarantor、Lender和 Institution表中的Name 如果需要，则反规范化就是一个好方法， 因为Guarantor、Lender和 Insti

9、tution表是相对稳定的 虽然不符合BCNF范式，如果只查询，不更新，则没有更新异常的问题 数据库设计贯通逻辑及物理设计 视图（view）设计 定义 视图是一个虚拟表，其内容由查询定义 同真实的表一样，视图包含一系列带有名称的 列和行数据 但是，视图并不在数据库中以存储的数据值集 形式存在。行和列数据来自由定义视图的查询 所引用的表，并且在引用视图时动态生成 对其中所引用的基础表来说，视图的作用类似 于筛选 数据库设计贯通逻辑及物理设计 视图定义 视图是一个虚拟表，其内容由查询定义 从用户角度来看 一个视图是从一个特定的角度来查看数据库中的数 据 从数据库系统内部来看 视图是由一张或多张表中

10、的数据组成的 从数据库系统外部来看 视图就如同一张表一样，对表能够进行的一般操作 都可以应用于视图，例如查询，插入，修改，删除 操作等 数据库设计贯通逻辑及物理设计 视图的作用 视点集中 视图集中即是使用户只关心它感兴趣的某些特定数据 和他们所负责的特定任务 设：产品（产品号，产品名，规格，单价，生产车间， 负责人，产品成本，产品合格率，质量等级） 可在产品关系上建立两个视图： 为一般顾客建立视图： 产品产品1（产品号，产品名，规格，单价）（产品号，产品名，规格，单价） 为产品销售部门建立视图： 产品产品2（产品号，产品名，规格，（产品号，产品名，规格， 单价，生产车间，负责人，产品成本）单价

11、，生产车间，负责人，产品成本） 数据库设计贯通逻辑及物理设计 视图的作用 简化操作 视图大大简化了用户对数据的操作。因为在定义视图 时，若视图本身就是一个复杂查询的结果集，这样在 每一次执行相同的查询时，不必重新写这些复杂的查 询语句,只要一条简单的查询视图语句即可 可见视图向用户隐藏了表与表之间的复杂的连接操作 数据库设计贯通逻辑及物理设计 视图的作用 定制数据 视图能够实现让不同的用户以不同的方式看到不同或 相同的数据集。因此，当有许多不同水平的用户共用 同一数据库时，这显得极为重要 合并分割数据 在有些情况下，由于表中数据量太大，故在表的设计 时常将表进行水平分割或垂直分割，但表的结构的

12、变 化却对应用程序产生不良的影响。如果使用视图就可 以重新保持原有的结构关系，从而使外模式保持不变， 原有的应用程序仍可以通过视图来重载数据 数据库设计贯通逻辑及物理设计 视图的作用 安全性 视图可以作为一种安全机制 通过视图用户只能查看和修改他们所能看到的 数据，其它数据库或表既不可见也不可以访问 如果某一用户想要访问视图的结果集，必须授 予其访问权限。视图所引用表的访问权限与视 图权限的设置互不影响 如：社会保险基金表，可以用视图只显示姓名，社会保险基金表，可以用视图只显示姓名， 地址，而不显示社会保险号和工资数等地址，而不显示社会保险号和工资数等 数据库设计贯通逻辑及物理设计 视图可以由

13、以下任意一项组成: 1 一个基表的任意子集 2 两个或两个以上的基表的合集 3 两个或两个以上基表的交集 4 一个或者多个基表运算的结果集合 5 另一个视图的子集. 数据库设计贯通逻辑及物理设计 利用企业管理器创建与管理视图 用T-SQL语句创建与管理视图 创建视图： CREATE VIEW 【(列名组)】AS 删除视图： DROP VIEW 数据库设计贯通逻辑及物理设计 举例： 创建视图，显示学生的姓名和该学生的选课号 和成绩 CREATE VIEW v_sc AS SELECT s.sn,o,sc.score FROM s inner join sc ON s.sno=sc.sno 练习：

14、 创建显示学生姓名、课程名和成绩的视图 数据库设计贯通逻辑及物理设计 举例 现有员工表和项目表，选择其中的部 分字段和记录创建视图，并且限制表员工表 中的记录只能是部门为“项目部”的记录集合， 视图定义为view_part 代码： CREATE VIEW view_part AS SELECT 员工表.编号, 员工表.姓名,员工表.性别,员工 表.所属部门, 项目表.项目编号, 项目表.名称 FROM 项目表 INNER JOIN 员工表 ON 项目表.负责人 = 员工表.编号 WHERE 员工表.所属部门=项目部 数据库设计贯通逻辑及物理设计 可以在设计视图时，重新定义列的命名， 使其与用户

15、命名习惯一致 例：如第一题，但重新定义列名为中文 CREATE VIEW v_sc (姓名,选课号,成绩) AS SELECT s.sn,o,sc.score FROM s inner join sc ON s.sno=sc.sno 数据库设计贯通逻辑及物理设计 两个参数： With encryption 数据加密，显示的是乱码 With check option 强制视图的修改操作必须符合select语句设定的条件， 否则不予执行 数据库设计贯通逻辑及物理设计 With check option例： CREATE VIEW ygb_view AS SELECT * FROM 员工表 WHER

16、E 员工表.性别=女 执行下列语句,插入新记录: INSERT INTO ygb_view (姓名,性别,工资) values(李立 三,男,2300) 插入操作成功插入操作成功,但不合理但不合理!解决办法:使用with check option ： CREATE VIEW ygb_view AS SELECT * FROM 员工表 WHERE 员工表.性别=女With check option 同样,插入新记录:INSERT INTO ygb_view (姓名,性别,工资) values(李立三,男,2300)时，插入操作将失败插入操作将失败! 数据库设计贯通逻辑及物理设计 数据库设计贯通

17、物理设计部分 数据库设计贯通逻辑及物理设计 从索引说起 分类 唯一索引唯一索引 /非唯一索引非唯一索引唯一索引是不允许其中 任何两行具有相同索引值的索引 主键索引主键索引/非主键索引非主键索引 主键索引是唯一索引的 特定类型 聚集索引聚集索引 表中行的物理顺序与索引顺序相同 一个表只能包含一个聚集索引 非聚集索引非聚集索引 不改变行的物理顺序 由于在建立聚集索引时会随时改变行的物理顺序， 应在非聚集索引前建立聚集索引 聚集索引比非聚集索引快 数据库设计贯通逻辑及物理设计 索引 在“学生选课数据库”中，创建索引如下： 列名列名聚集索引聚集索引唯一索引唯一索引非聚集索引非聚集索引是否主键是否主键

18、s.sno s.sn o Sc.sno So 数据库设计贯通逻辑及物理设计 方法一：企业管理器 选中学生表，右击 “设计表” 右击“索引/键”打 开“属性”对话框 为学号sno创建聚集、 唯一索引 参见右图 数据库设计贯通逻辑及物理设计 方法一：企业管理器 选中学生表，右击 “设计表” 右击“索引/键”打 开“属性”对话框 为姓名sn创建非聚集 索引 参见右图 数据库设计贯通逻辑及物理设计 方法二：使用SQL命名 为课程号cno创建聚集、唯一索引的命令如下： Create unique clustered index IN_CNO on c(cno) With pad_index, fillfactor=80 为课程名cn创建非聚集、唯一索引的命令如下： Create unique index IN_CN on c(cn DESC) 注：pad_index需与fillfactor一起使用 Fillfactor=填充因子：用于指定在创建索引时，每个索 引页的数据占索引页大小的百分比，范围为1100 DESC 表示为降序索引，默认是升序 数据库设计贯通逻辑及物理设计 为选课表sc创建聚集、唯一索引的命令如下：