《数据库设计贯通逻辑及物理设计幻灯片.ppt》由会员分享，可在线阅读，更多相关《数据库设计贯通逻辑及物理设计幻灯片.ppt（42页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库设计贯通逻辑及物理设计第1页，共42页，编辑于2022年，星期六参考书目：数据库系统概论 高等教育出版社 王珊 萨师煊数据库系统教程 高等教育出版社 施伯乐等数据库设计、应用开发与管理电子工业出版社 Michael V.Mannino 著 唐常杰 等译UML和统一过程-实用面向对象的分析和设计机械工业出版社 方贵宾 等译UML应用建模实践过程机械工业出版社尤克滨 编著第2页，共42页，编辑于2022年，星期六逻辑结构设计步骤ER图向关系模型的转换关系模型的优化视图（view）设计第3页，共42页，编辑于2022年，星期六ER图向关系模型的转换Entity1:M relationshipN
2、:M relationshipIdentifying relationshipOptional 1-M relationshipGeneralization Hierarchy1-1 Relationships第4页，共42页，编辑于2022年，星期六基本转化规则basic conversion rules规则一、Each entity type becomes a table(实体转化为表，实体的主码成为表的主码，不包括弱实体，属性转化成列)第5页，共42页，编辑于2022年，星期六基本转化规则basic conversion rules规则二、Each 1-M relationship b
3、ecomes 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端实体的码）【少采用，比较规则五】第6页，共42页，编辑于2022年，星期六规则三、Each
4、 M-N relationship becomes an associative table with a combined primary key.(多对多联系转化成为一张独立的表，其主码由联系两端的实体的主码共同组成)第7页，共42页，编辑于2022年，星期六规则四、Each identifying relationship adds a column to a primary key.（标识依赖在转化时，将父表的主码添加到弱实体中，弱实体的主码包括：1 弱实体自己的主码 2标识实体【也就是父表】的主码）第8页，共42页，编辑于2022年，星期六规则五、Optional 1-M Rule（
5、可选的一对多联系，所谓“可选”，指“1”端，也就是父表端不强制，最小基数可为0的情况）如：第9页，共42页，编辑于2022年，星期六下图表示是一个可选的一对多联系，学生可以住宿舍，也可以不住宿舍（比如有回家住的学生），这里，dorm对student来说是可选的，因此dormNo作为Student表的外码，可以为空（Null）为了避免这种空值，我们将为了避免这种空值，我们将live联系转化成关系表联系转化成关系表Optional第10页，共42页，编辑于2022年，星期六该表的主码是子表（Student实体）的主码该表的外码是两端的实体的主码的组合该表的外码不允许空NULL注意：规则五可以避免N
6、ULL，但是多生成了一个表，增加了查询的复杂性，许多应用中，避免额外的表比避免NULL更重要第11页，共42页，编辑于2022年，星期六规则六、转化泛化层次结构。RDBMS不直接支持泛化，各CASE工具的实现方法有别第12页，共42页，编辑于2022年，星期六规则七、1:1relationship（转化一对一联系）方法一：直接将1：1联系转化成两个外码（可能会产生NULL）方法二：如果联系的某一端实体是可选的（optional），则可以在另一个实体中取消外码，以消除NULL举例：第13页，共42页，编辑于2022年，星期六方法一：冗余联系冗余联系第14页，共42页，编辑于2022年，星期六方法
7、二：在Power Designer中可通过设置dominant关系来实现第15页，共42页，编辑于2022年，星期六CREATE TABLE Office(PRIMARY KEY(OfficeNo),FOREIGN KEY(EmpNo)REFERENCES Employee,UNIQUE(EmpNo)第16页，共42页，编辑于2022年，星期六逻辑模型的优化规范化分析数据依赖关系，消除部分依赖、传递依赖和多值依赖反规范化涉及到多个关系的查询时，需进行关系的连接运算，产生很高的代价关系模式的分解80/20原则，大关系中，经常被使用的数据只占所有数据的20%，把这20%分解出来，形成独立的子关系水
8、平分解和垂直分解注意保持无损连接性和函数依赖第17页，共42页，编辑于2022年，星期六考虑查询某笔贷款的贷款人和担保人等情况在表Loan中存储LenderNo和LenderName是不符合BCNF范式的，但可以减少表Loan和Lender的连接仔细定夺，考虑是否要在Loan表中增加来自Guarantor、Lender和Institution表中的Name如果需要，则反规范化就是一个好方法，因为Guarantor、Lender和Institution表是相对稳定的虽然不符合BCNF范式，如果只查询，不更新，则没有更新异常的问题第18页，共42页，编辑于2022年，星期六视图（view）设计定义
9、视图是一个虚拟表，其内容由查询定义同真实的表一样，视图包含一系列带有名称的列和行数据但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成对其中所引用的基础表来说，视图的作用类似于筛选第19页，共42页，编辑于2022年，星期六视图定义视图是一个虚拟表，其内容由查询定义从用户角度来看一个视图是从一个特定的角度来查看数据库中的数据从数据库系统内部来看视图是由一张或多张表中的数据组成的从数据库系统外部来看视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等第20页，共42页，编辑于2022年，星
10、期六视图的作用视点集中视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务设：产品（产品号，产品名，规格，单价，生产车间，负责人，产品成本，产品合格率，质量等级）可在产品关系上建立两个视图：为一般顾客建立视图：产品产品1（产品号，产品名，规格，单价）（产品号，产品名，规格，单价）为产品销售部门建立视图：产品产品2（产品号，产品名，规格，单价，生产（产品号，产品名，规格，单价，生产车间，负责人，产品成本）车间，负责人，产品成本）第21页，共42页，编辑于2022年，星期六视图的作用简化操作视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在
11、每一次执行相同的查询时，不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可可见视图向用户隐藏了表与表之间的复杂的连接操作第22页，共42页，编辑于2022年，星期六视图的作用定制数据视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要合并分割数据在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据第23页，共42页，编辑于2022年，星期六视图的作
12、用安全性视图可以作为一种安全机制通过视图用户只能查看和修改他们所能看到的数据，其它数据库或表既不可见也不可以访问如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响如：社会保险基金表，可以用视图只显示姓名，地社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等址，而不显示社会保险号和工资数等第24页，共42页，编辑于2022年，星期六视图可以由以下任意一项组成:1 一个基表的任意子集 2 两个或两个以上的基表的合集 3 两个或两个以上基表的交集 4 一个或者多个基表运算的结果集合 5 另一个视图的子集.第25页，共42页，编辑
13、于2022年，星期六利用企业管理器创建与管理视图用T-SQL语句创建与管理视图创建视图：CREATE VIEW【(列名组)】AS 删除视图：DROP VIEW 第26页，共42页，编辑于2022年，星期六举例：创建视图，显示学生的姓名和该学生的选课号和成绩CREATE VIEW v_sc AS SELECT s.sn,o,sc.score FROM s inner join sc ON s.sno=sc.sno 练习：创建显示学生姓名、课程名和成绩的视图第27页，共42页，编辑于2022年，星期六举例现有员工表和项目表，选择其中的部分字段和记录创建视图，并且限制表员工表 中的记录只能是部门为“
14、项目部”的记录集合，视图定义为view_part代码：CREATE VIEW view_part AS SELECT 员工表.编号,员工表.姓名,员工表.性别,员工表.所属部门,项目表.项目编号,项目表.名称 FROM 项目表 INNER JOIN 员工表 ON 项目表.负责人=员工表.编号WHERE 员工表.所属部门=项目部第28页，共42页，编辑于2022年，星期六可以在设计视图时，重新定义列的命名，使其与用户命名习惯一致例：如第一题，但重新定义列名为中文CREATE VIEW v_sc(姓名,选课号,成绩)AS SELECT s.sn,o,sc.score FROM s inner jo
15、in sc ON s.sno=sc.sno 第29页，共42页，编辑于2022年，星期六两个参数：With encryption数据加密，显示的是乱码With check option强制视图的修改操作必须符合select语句设定的条件，否则不予执行第30页，共42页，编辑于2022年，星期六With check option例：CREATE VIEW ygb_view AS SELECT*FROM 员工表 WHERE 员工表.性别=女执行下列语句,插入新记录:INSERT INTO ygb_view(姓名,性别,工资)values(李立三,男,2300)插入操作成功插入操作成功,但不合理但不
16、合理!解决办法:使用with check option：CREATE VIEW ygb_view AS SELECT*FROM 员工表 WHERE 员工表.性别=女With check option 同样,插入新记录:INSERT INTO ygb_view(姓名,性别,工资)values(李立三,男,2300)时，插入操作将失败插入操作将失败!第31页，共42页，编辑于2022年，星期六数据库设计贯通物理设计部分第32页，共42页，编辑于2022年，星期六从索引说起分类唯一索引唯一索引/非唯一索引非唯一索引唯一索引是不允许其中任何两行具有相同索引值的索引主键索引主键索引/非主键索引非主键索引
17、 主键索引是唯一索引的特定类型聚集索引聚集索引 表中行的物理顺序与索引顺序相同一个表只能包含一个聚集索引非聚集索引非聚集索引 不改变行的物理顺序由于在建立聚集索引时会随时改变行的物理顺序，应在非聚集索引前建立聚集索引聚集索引比非聚集索引快第33页，共42页，编辑于2022年，星期六索引在“学生选课数据库”中，创建索引如下：列名列名聚集索引聚集索引唯一索引唯一索引非聚集索引非聚集索引是否主键是否主键Sc.snoSo第34页，共42页，编辑于2022年，星期六方法一：企业管理器选中学生表，右击“设计表”右击“索引/键”打开“属性”对话框为学号sno创建聚集、唯一索引参见右图第35页，共42页，编辑
18、于2022年，星期六方法一：企业管理器选中学生表，右击“设计表”右击“索引/键”打开“属性”对话框为姓名sn创建非聚集索引参见右图第36页，共42页，编辑于2022年，星期六方法二：使用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=填充因子：用于
19、指定在创建索引时，每个索引页的数据占索引页大小的百分比，范围为1100DESC 表示为降序索引，默认是升序第37页，共42页，编辑于2022年，星期六为选课表sc创建聚集、唯一索引的命令如下：Create unique clustered index IN_SC on sc(sno,cno)With pad_index,fillfactor=90删除索引Drop index 索引名第38页，共42页，编辑于2022年，星期六索引的选择规则主关键字作为聚集索引的候选比较合适为了支持表间的连接查询，外关键字考虑建立索引若果查询存在等值条件，考虑在存在多值的条件（列）上建立索引如：查询高校中职称=“
20、教授”的信息，职称是一个多值属性，可以考虑为其建立索引使用order by或group by子句检索时，索引可显著减少分组和排序的时间第39页，共42页，编辑于2022年，星期六索引的选择规则需要精确匹配的查询（指使用where子句）考虑使用索引如：where sname=张三查询关键字是范围（介于两个值之间的任何条目）时，考虑采用索引如：where grade between 70 and 85如：where grade=70 and grade=82不进行显式排序操作的情况下，产生排序的查询输出第40页，共42页，编辑于2022年，星期六索引的选择规则使用like进行查询时，如果以特定的串如”abc%”开头，则考虑索引会提高效率频繁更新的列不宜作为索引不稳定的表（频繁的插入和删除）不应有太多索引表上索引太多影响表的操作性能对小型表进行索引可能不会产生优化结果在视图和计算列上也可以指定索引第41页，共42页，编辑于2022年，星期六The End第42页，共42页，编辑于2022年，星期六