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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,数据库设计贯通,逻辑结构设计部分,参考书目：,数据库系统概论 高等教育出版社 王珊 萨师煊,数据库系统教程 高等教育出版社 施伯乐等,数据库设计、应用开发与管理,电子工业出版社,Michael V.Mannino,著 唐常杰 等译,UML,和统一过程,-,实用面向对象的分析和设计,机械工业出版社 方贵宾 等译,UML,应用建模实践过程,机械工业出版社尤克滨 编著,逻辑结构设计步骤,ER,图向关系模型的转换,关系模型的优化,视图（,view,）设计,ER,图向关系模型的转换,Entity,1: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-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,端实体的码）,【,少采用，比较规则五,】,规则三、,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,标识实体,【,也就是父表,】,的主码）,规则五、,Optional 1-M Rule,（可选的一对多联系，所谓“可选”，指“,1,”端，也就是父表端不强制，最小基数可为,0,的情况）,如：,下图表示是一个可选的一对多联系，学生可以住宿舍，也可以不住宿舍（比如有回家住的学生），,这里，,dorm,对,student,来说是可选的，因此,dormNo,作为,Student,表的外码，可以为空（,Null,）,为了避免这种空值，我们将,live,联系转化成关系表,Optional,该表的主码是子表（,Student,实体）的主码,该表的外码是两端的实体的主码的组合,该表的外码不允许空,NULL,注意：规则五可以避免,NULL,，但是多生成了一个表，增加了查询的复杂性，许多应用中，避免额外的表比避免,NULL,更重要,规则六、转化泛化层次结构。,RDBMS,不直接支持泛化，各,CASE,工具的实现方法有别,规则七、,1:1relationship,（转化一对一联系）,方法一：直接将,1,：,1,联系转化成两个外码（可能会产生,NULL,）,方法二：如果联系的某一端实体是可选的（,optional,），则可以在另一个实体中取消外码，以消除,NULL,举例：,方法一：,冗余联系,方法二：,在,Power Designer,中可通过设置,dominant,关系来实现,CREATE TABLE Office(PRIMARY KEY(OfficeNo),FOREIGN KEY(EmpNo)REFERENCES Employee,UNIQUE(EmpNo),逻辑模型的优化,规范化,分析数据依赖关系，消除部分依赖、传递依赖和多值依赖,反规范化,涉及到多个关系的查询时，需进行关系的连接运算，产生很高的代价,关系模式的分解,80/20,原则，大关系中，经常被使用的数据只占所有数据的,20%,，把这,20%,分解出来，形成独立的子关系,水平分解和垂直分解,注意保持无损连接性和函数依赖,考虑查询某笔贷款的贷款人和担保人等情况,在表,Loan,中存储,LenderNo,和,LenderName,是不符合,BCNF,范式的，但可以减少表,Loan,和,Lender,的连接,仔细定夺，考虑是否要在,Loan,表中增加来自,Guarantor,、,Lender,和,Institution,表中的,Name,如果需要，则反规范化就是一个好方法，,因为,Guarantor,、,Lender,和,Institution,表是相对稳定的,虽然不符合,BCNF,范式，如果只查询，不更新，则没有更新异常的问题,视图（,view,）设计,定义,视图是一个虚拟表，其内容由查询定义,同真实的表一样，视图包含一系列带有名称的列和行数据,但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成,对其中所引用的基础表来说，视图的作用类似于筛选,视图定义,视图是一个虚拟表，其内容由查询定义,从用户角度来看,一个视图是从一个特定的角度来查看数据库中的数据,从数据库系统内部来看,视图是由一张或多张表中的数据组成的,从数据库系统外部来看,视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等,视图的作用,视点集中,视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务,设：产品,（,产品号,，产品名，规格，单价，生产车间，负责人，产品成本，产品合格率，质量等级）,可在产品关系上建立两个视图：,为一般顾客建立视图,：,产品,1,（产品号，产品名，规格，单价）,为产品销售部门建立视图,：产品,2,（产品号，产品名，规格，单价，生产车间，负责人，产品成本）,视图的作用,简化操作,视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可,可见视图向用户隐藏了表与表之间的复杂的连接操作,视图的作用,定制数据,视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要,合并分割数据,在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据,视图的作用,安全性,视图可以作为一种安全机制,通过视图用户只能查看和修改他们所能看到的数据，其它数据库或表既不可见也不可以访问,如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响,如：,社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等,视图可以由以下任意一项组成,:,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,练习：,创建显示学生姓名、课程名和成绩的视图,举例,现有员工表和项目表，选择其中的部分字段和记录创建视图，并且限制表员工表 中的记录只能是部门为“项目部”的记录集合，视图定义为,view_part,代码：,CREATE VIEW view_part AS,SELECT,员工表,.,编号,员工表,.,姓名,员工表,.,性别,员工表,.,所属部门,项目表,.,项目编号,项目表,.,名称,FROM,项目表,INNER JOIN,员工表,ON,项目表,.,负责人,=,员工表,.,编号,WHERE,员工表,.,所属部门,=,项目部,可以在设计视图时，重新定义列的命名，使其与用户命名习惯一致,例：如第一题，但重新定义列名为中文,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,员工表,WHERE,员工表,.,性别,=,女,执行下列语句,插入新记录,:,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),时，,插入操作将失败,!,数据库设计贯通,物理设计部分,从索引说起,分类,唯一索引,/,非唯一索引,唯一索引是不允许其中任何两行具有相同索引值的索引,主键索引,/,非主键索引,主键索引是唯一索引的特定类型,聚集索引,表中行的物理顺序与索引顺序相同,一个表只能包含一个聚集索引,非聚集索引,不改变行的物理顺序,由于在建立聚集索引时会随时改变行的物理顺序，应在非聚集索引前建立聚集索引,聚集索引比非聚集索引快,索引,在“学生选课数据库”中，创建索引如下：,列名,聚集索引,唯一索引,非聚集索引,是否主键,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,创建聚集、唯一索引的命令如下：,Create unique clustered index IN_SC o