SQL Server数据库及PHP技术第5章 数据库设计

SQLServer数据库及PHP技术第5章数据库设计2/135.1数据库设计概述什么是数据库设计数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求）在数据库领域内，常常把使用数据库的各类系统统称为数据库应用系统。3/13数据库和信息系统数据库是信息系统的核心和基础把信息系统中大量的数据按一定的模型组织起来提供存储、维护、检索数据的功能使信息系统可以方便、及时、准确地从数据库中获得所需的信息数据库是信息系统的各个部分能否紧密地结合在一起以及如何结合的关键所在数据库设计是信息系统开发和建设的重要组成部分4/13数据库设计人员应该具备的技术和知识数据库的基本知识和数据库设计技术计算机科学的基础知识和程序设计的方法和技巧软件工程的原理和方法应用领域的知识5/13数据库设计的特点数据库建设是硬件、软件和干件的结合三分技术，七分管理，十二分基础数据技术与管理的界面称之为“干件”数据库设计应该与应用系统设计相结合结构（数据）设计：设计数据库框架或数据库结构行为（处理）设计：设计应用程序、事务处理等6/13数据库设计概述（Cont.）数据库系统设计概念结构设计需求分析物理结构设计逻辑结构设计数据库实施功能模型设计功能分析程序设计事务设计调试运行功能说明静态结构设计动态结构设计7/13数据库设计概述（Cont.）数据库设计的步骤（迭代）1）需求分析需求调查、需求分析2）概念结构设计局部、全局E-R模型3）逻辑结构设计关系模式（内、外）、规范化4）物理结构设计存储结构、存取方法（索引）5）数据库实施调试、评价、维护需求分析信息需求处理需求概念结构设计逻辑结构设计物理结构设计数据库实施正式运行转换规则DBMS特性规范化方法硬件特性DBMS特性数据流图、数据字典E-R图数据库模式数据库脚本8/13数据库设计概述（Cont.）数据库设计的内容行为特性设计确定数据库用户的行为和动作设计应用程序和用户界面结构特性设计确定数据库的框架（总体逻辑模式与子模式）确定数据库的数据结构数据库设计的方法

E-R模型→关系模型→关系规范化

IDEF1X＋关系规范化省去了将E-R模型转换为关系模型的过程9/13数据库设计概述（Cont.）术语需求分析对应用对象的功能、性能、业务规则等进行分析。概念设计对应用对象精确地抽象和概括，以形成独立于计算机系统的企业信息模型（E-R图）。逻辑设计将抽象的概念模型转化为与选用的DBMS产品所支持的数据模型相符合的逻辑模型。10/13物理设计逻辑模型在计算机中的具体实现方案。数据字典（Datadictionary）关于数据库信息的信息库：数据的名称、类型和含义，数据流向的描述，数据加工的描述；逻辑模式、子模式、存储模式、索引、存取策略、安全控制，完整性约束等方面的信息以及存储过程、触发器等各种数据库对象的信息。数据流图（Data-flowdiagram）描述系统数据加工流程的图，是用户和软件开发人员都能理解的一种描述系统功能的方法。11/134.2需求分析收集和分析用户要求：需求调查＋需求分析完成两件事：“数据流”和“事务处理过程”的调查分析1.需求调查搜集与信息系统相关的所有信息经营方针策略，组织结构、人员/物/资金、各种票据、报表…重点：信息需求：有哪些信息？信息之间的关系？完整性约束？处理需求：需要对信息如何处理？每一种处理有哪些输入/输出要求？处理方法？处理的特殊限制（业务规则）？系统需求：安全性：有几种用户？每种用户的权限？使用方式：使用环境？多少用户同时使用？系统的响应时间？扩充性：目前所要实现的功能?以后要否进行扩充、升级?12/13需求分析（Cont.）需求调查的方法：阅读手册、文档等一切有关的数据资料；搜集各种业务表格、报表、单据、凭证，仔细询问每一个数据项的来龙去脉；与各类用户（领导、管理人员、业务人员）交谈；跟班作业，从用户的业务活动中了解有关信息；召集相关人员进行讨论座谈；分发问卷调查表来调查用户的需求。13/13需求分析（Cont.）2.需求分析需求分析的任务：整理在需求调查中得到的资料；确定系统的功能范围：哪些由计算机处理，哪些由人工处理；综合各种信息包含的数据，数据间的关系，数据的类型、值域，数据的源头、流向与终点；将需求调查写成需求说明文档：提交给用户，以确认是否符合他们的业务；用于下一步数据库的概念结构设计。需求分析包括以下步骤：分析用户活动（业务现状），确定系统范围，分析用户活动所涉及的数据，分析系统数据。14/13需求分析（Cont.）例如：图书馆业务办借书证图书查询借书逾期查询借书丢失图书赔偿1.分析用户活动，生成“用户活动图”，包括有哪些用户活动用户的工作流程如何每个步骤的处理方法和数据处理的算法15/13需求分析（Cont.）接受申请读者信息存档填写借书证办借书证发给读者接受申请查图书信息输出图书目录接受申请查借书记录计算日期输出逾期读者一览表接受申请查借书记录登记借书信息接受处理查借书记录查读者档案查图书价格计算罚金打印单据查图书信息查询图书查询逾期借书丢失赔偿显示打印催还图书书交给读者交给读者“用户活动图”的例子：虚线框内为计算机处理的范围16/13需求分析（Cont.）2.分析用户活动所涉及的数据，画出“数据流图”用“数据流图”表示数据的流向和对数据所进行的加工。用椭圆表示加工，用双线表示数据存储，用方框表示外部实体。用有向边表示流向，数据流名称(可选)标注在有向边上。自顶向下：先画外层数据流图，再画内部数据流图。画外层数据流图时，其内部细节可暂不考虑，当作一个黑盒子看待。也不必要强求完整，因为以后还可以补上。图书借阅管理读者图书管理员读者读者服务请求业务请求借书证，图书赔偿金额17/13需求分析（Cont.）逐步细化数据流图对外层数据流图进行分解、细化，形成第二层乃至更详细的数据流图。输入查询信息打印查书请求图书信息图书列表图书输入借书信息更新借阅表借书请求借阅信息图书借阅借阅读者输入读者信息打印办证请求借书证信息借书证读者更新读者表读者读者信息18/13需求分析（Cont.）3.分析系统数据对数据流图中基本要素的具体内容进行定义、描述和说明——即建立“数据字典”，其中包括：数据流的描述：定义数据流的组成（数据结构）。文件（数据表）的描述：定义数据文件的组成。数据项的描述：定义数据项，包括数据项的名称、类型、长度、范围。加工的描述：1）说明加工的输入和输出的逻辑关系；2）加工的触发条件和出错处理。注意，只需要说明“做什么”即可，而不需要说明“怎样做”。19/13需求分析（Cont.）例：数据项的例子：数据项名：单价别名：定价描述：图书的价格，即印刷在书中扉页上的价格定义：数值型，带两位小数取值范围：0～9999.99文件（表）的例子：文件（表）名：图书描述：库存图书的信息定义：图书＝编号＋书名＋作者＋出版社＋单价＋库存量读文件（表）：提供各项数据的显示，提取图书的信息写文件（表）：对图书信息进行增加、删除、修改20/13需求分析（Cont.）数据结构的例子：数据结构名：查书请求别名：图书查询描述：查询图书信息时要求输入的关键字定义：查书请求＝书名＋作者＋出版社说明：书名、作者和出版社这三项任意一项都可以为空，同时为空时表示列出所有图书的信息。加工的例子：加工名：更新借阅表输入数据流：读者编号，图书编号，数量，借阅日期输出数据流：借阅表加工逻辑：把读者编号，图书编号，数量和借阅日期记录进借阅文件中触发条件：每当有借/还书业务发生并被确认时处理频率：比较分散，非集中式处理21/13需求分析（Cont.）需求分析的输出文档包括：用户活动图；数据流图（外层和内部）；联系判定表；数据字典。22/135.3概念结构设计根据需求分析所得到的信息确定：系统中有哪些实体？每个实体有哪些属性？哪些实体间存在联系？每种联系有哪些属性？系统中有哪些业务规则？明确了以上问题后，即可设计局部E-R模型和全局E-R模型。业务规则可在数据库设计中实现，也可在程序设计中实现。（尽可能在数据库设计中实现）23/13概念结构设计（Cont.）业务规则的例子：借阅者必须出示借书证，每张借书证的编号唯一；学生的借书数最多为5本，教职工为10本；学生的借书周期为15天，教职工为20天；一旦有图书逾期未还，就不能再借阅任何图书；未全部归还图书的学生不能办理毕业离校手续；当删除一个读者时，同时删除其借阅记录。实现方式：在数据库设计中实现：触发器、存储过程、属性值约束、指定默认值在程序设计中实现（不推荐）：在程序中加入进行业务规则检查的判断语句；利用程序设计语言提供的数据合法性检查功能。24/13概念结构设计（Cont.）概念结构设计的输出文档包括：各子系统的局部E-R模型；系统总体E-R模型；改进后的数据字典；概念模型应具有的业务规则及实现方法。25/13(Entity-RelationshipApproach）的简称。它是描述现实世界概念结构模型的有效方法。用E-R方法建立的概念结构模型称为E-R模型，或称为E-R图。E-R图基本成分包含实体型、属性和联系。回顾信息世界中的基本概念实体（Entity）客观存在并可相互区别的事物称为实体。实体可以是具体的人、事、物，也可以是抽象的概念或联系.属性（Attribute）实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。码（Key）唯一标识实体的属性集称为码。域（Domain）属性的取值范围称为该属性的域。E-R模型的设计方法26/13E-R模型的设计方法实体型（EntityType）用实体名及其属性名集合来抽象和刻画同类实体，称为实体型实体集（EntitySet）同型实体的集合称为实体集联系（Relationship）实体内部的联系和实体之间的联系一对一联系（1:1）一对多联系（1:n）多对多联系（m:n）27/13E-R模型的设计方法①实体型：用矩形框表示，框内标注实体名称。如图3.11（a）所示。②属性：用椭圆形框表示，框内标注属性名称。如图3.11（b）所示。③联系：指实体之间的联系，有一对一（1：1），一对多（1：n）或多对多（m

：n）三种联系类型。例如系主任领导系，学生属于某一系，学生选修课程，工人生产产品，这里“领导”、“属于”、“选修”、“生产”表示实体间的联系，可以作为联系名称。联系用菱形框表示，框内标注联系名称。如图3.11（c）所示。

28/135.11E-R图的三种基本成份及其图形的表示方法学生选修学号(a)实体(c)联系(b)属性29/13图5.12实体及其联系图(a)两个实体之间的联系学生选修成绩课程系主任领导系学生属于系11n1系主任n(c)实体集内部的联系m职工领导1n供应商供应数量零件项目mnn(b)多个实体之间的联系30/13图5.13学生与课程联系的完整的E－R图课程名学生学号姓名性别年龄系别课程课程号学分n选修成绩m31/13E-R模型的设计方法E-R模型要在需求分析的基础上进行设计。1.根据需求分析确定实体、属性和实体间的联系2.设计局部E-R模型3.对局部E-R模型进行综合，设计出总体E-R模型4.消除冗余，优化总体E-R模型E-R模型的设计三原则：相对原则E-R模型没有绝对的结果，不同的设计者基于不同的考虑所建立的模型很可能不同。但只要能满足业务的需求，它们都是正确的。简单原则现实世界的事物能作为属性对待的，尽量作为属性处理，而不要作为实体处理。（设计局部E-R模型时应考虑的问题）一致原则同一对象在不同的业务子系统（即局部E-R模型）中的抽象结果要求保持一致。（对局部E-R模型进行综合时应考虑的问题）32/13E-R模型的设计方法（Cont.）如何确定一个事物是属性还是实体？应根据应用环境来确定——是否关心该事物的“细微结构”。例1：职工和工资。若不需要了解工资的构成，则工资可作为职工的属性；否则就应把工资作为实体。职工职工号姓名性别年龄职称工资职工职工号姓名性别年龄职称基本工资拥有工资奖金补贴工资号33/13E-R模型的设计方法（Cont.）例2：课程和教室。若不需要考虑教室的特性，则教室可作为课程的属性；否则就应把教室作为实体。课程课程号课程名学分学时类型教室课程课程号课程名学分学时类型设施情况地点教室座位数面积教室号34/13E-R模型的设计方法（Cont.）例3：货物和仓库。如果货物只存放在一个仓库，则仓库可作为货物的属性；若应用还关心仓库的地点、面积、类型、储运成本等，则应把仓库作为实体。货物货号名称型号批次存量仓库货物货号名称型号批次存量地点存放仓库类型面积仓库号距离35/13E-R模型的设计步骤E-R模型的设计步骤分为三个阶段：要旨：化整为零，化零为整。

化整为零：进行子系统划分，即把整个应用系统分为若干个相对独立的应用，这样就可以对每一个子系统分别进行设计，得出局部E-R模型。化零为整：把局部E-R模型进行整合，设计出总体E-R模型。1.设计局部E-R模型

关键是确定子系统中有哪些实体，实体又包含哪些属性，它们之间的联系如何。2.设计总体E-R模型 对局部E-R模型进行综合：对相同实体进行合并；为属于不同的局部

E-R模型的实体间建立联系。3.优化总体E-R模型 消除由于合并带来的冗余和冲突。36/13说明局部E-R模型设计举例在简单的教务管理系统中，有如下语义约束。①一个学生可选修多门课程，一门课程可为多个学生选修，因此学生和课程是多对多的联系；②一个教师可讲授多门课程，一门课程可为多个教师讲授，因此教师和课程也是多对多的联系；③一个系可有多个教师，一个教师只能属于一个系，因此系和教师是一对多的联系，同样系和学生也是一对多的联系。根据上述约定，可以得到如图3.16所示的学生选课局部Ｅ－Ｒ图和如图3.17所示的教师任课局部Ｅ－Ｒ图。形成局部E-R模型后，应该返回去征求用户意见，以求改进和完善，使之如实地反映现实世界。E-R图的优点就是易于被用户理解，便于交流。

37/13图5.16学生选课局部Ｅ－Ｒ图mmnm１名称系拥有１学生学号姓名性别年龄开课课程教师号课程号课程名选修成绩平均成绩38/131m教师号姓名性别职称课程号教师讲授课程n属于单位单位名电话m图5.17教师任课局部Ｅ－Ｒ图39/134．全局E-R模型设计局部E-R模型设计完成之后，下一步就是集成各局部E-R模型，形成全局E-R模型，即视图的集成。视图集成的方法有两种：①多元集成法，一次性将多个局部E-R图合并为一个全局E-R图，如图3.18（a）所示。②二元集成法，首先集成两个重要的局部视图，以后用累加的方法逐步将一个新的视图集成进来，如图3.18（b）所示。在实际应用中，可以根据系统复杂性选择这两种方案。 ①合并，消除各局部E-R图之间的冲突，生成初步E-R图。 ②优化，消除不必要的冗余，生成基本E-R图。40/13图5.18局部视图合并成全局视图(a)多元集成法局部E－R图1…局部E－R图2局部E－R图n初步E－R图基本E－R图局部E－R图1局部E－R图2合并E－R图12局部E－R图3…初步E－R图基本E－R图(b)二元集成法41/13下面以教务管理系统中的两个局部E-R图为例，来说明如何消除各局部E-R图之间的冲突，进行局部E-R模型的合并，从而生成初步E-R图。首先，这两个局部E-R图中存在着命名冲突，学生选课局部Ｅ－Ｒ图中的实体“系”与教师任课局部Ｅ－Ｒ图中的实体“单位”，都是指“系”，即所谓的异名同义，合并后统一改为“系”，这样属性“名称”和“单位”即可统一为“系名”。其次，还存在着结构冲突，实体“系”和实体“课程”

在两个不同应用中的属性组成不同，合并后这两个实体的属性组成为原来局部E-R图中的同名实体属性的并集。解决上述冲突后，合并两个局部E-R图，生成如图3.20所示的初步的全局E-R图。

42/13图5.20教务管理系统的初步E－R图mn1系属于教师拥有学生开课讲授选修课程mmnm1m1学号姓名性别年龄平均成绩成绩教师号课程号课程名教师号姓名性别职称系名电话43/13(2)消除不必要的冗余，生成基本E-R图所谓冗余，在这里指冗余的数据和实体之间冗余的联系。冗余的数据是指可由基本的数据导出的数据，冗余的联系是由其他的联系导出的联系。我们把消除了冗余的初步E-R图称为基本E-R图。通常采用分析的方法消除冗余。数据字典是分析冗余数据的依据，还可以通过数据流图分析出冗余的联系。如在图3.20所示的初步E-R图中，“课程”实体中的属性“教师号”可由“讲授”这个教师与课程之间的联系导出，而学生的平均成绩可由“选修”联系中的属性“成绩”中计算出来，所以“课程”实体中的

“教师号”

与“学生”实体中的“平均成绩”均属于冗余数据。

44/13另外，“系”和“课程”之间的联系“开课”，可以由“系”和“教师”之间的“属于”联系与“教师”和“课程”之间的“讲授”联系推导出来，所以“开课”属于冗余联系。这样，图4.20的初步E-R图在消除冗余数据和冗余联系后，便可得到基本的E-R模型，如图4.21所示。最终得到的基本E-R模型是企业的概念模型，它代表了用户的数据要求，是沟通“要求”和“设计”的桥梁。它决定数据库的总体逻辑结构，是成功建立数据库的关键。45/13图5.21教务管理系统的基本E－R图n1系属于教师拥有学生讲授选修课程mmnm1m学号姓名性别年龄成绩课程号课程名教师号姓名性别职称系名电话46/135.4逻辑结构设计逻辑结构设计的任务概念结构是各种数据模型的共同基础为了能够用某一DBMS实现用户需求，还必须将概念结构进一步转化为相应的数据模型，这正是数据库逻辑结构设计所要完成的任务。47/13把E-R模型转换为关系模式关系模式规范化模式评价是否需要调整进入物理设计阶段模式调整是否E-R模型DBMS特性处理需求把数据库概念模型转换为关系模式。步骤见图48/13把E-R模型转换为关系模式1．转换原则概念设计中得到的E-R图是由实体、属性和联系组成的，而关系数据库逻辑设计的结果是一组关系模式的集合。所以将E-R图转换为关系模型实际上就是将实体、属性和联系转换成关系模式。在转换中要遵循以下原则：

(1)一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的键就是关系的键。

(2)一个联系转换为一个关系模式，与该联系相连的各实体的键以及联系的属性均转换为该关系的属性。49/13把E-R模型转换为关系模式

该关系的键有三种情况： ①如果联系为1:1，则每个实体的键都是关系的候选键； ②如果联系为1:n

，则n端实体的键是关系的键； ③如果联系为n:m，则各实体键的组合是关系的键。50/132．具体做法(1)把每一个实体转换为一个关系首先分析各实体的属性，从中确定其主键，然后分别用关系模式表示。例如，以图4.21的E-R模型为例，四个实体分别转换成四个关系模式： 学生（学号，姓名，性别，年龄） 课程（课程号，课程名） 教师（教师号，姓名，性别，职称） 系（系名，电话）其中，有下划线者表示是主键。51/13(2)把每一个联系转换为关系模式由联系转换得到的关系模式的属性集中，包含两个发生联系的实体中的主键以及联系本身的属性，其关系键的确定与联系的类型有关。例如，还以图3.21的E-R模型为例，四个联系也分别转换成四个关系模式：属于（教师号，系名）讲授（教师号，课程号）选修（学号，课程号，成绩）拥有（系名，学号）52/13数据模型的优化数据库逻辑设计的结果不是唯一的。得到初步数据模型后，还应该适当地修改、调整数据模型的结构，以进一步提高数据库应用系统的性能，这就是数据模型的优化。关系数据模型的优化通常以规范化理论为指导。53/13优化数据模型的方法

⒈确定数据依赖⒉对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。⒊按照数据依赖的理论对关系模式逐一进行分析，考查是否存在部分函数依赖、传递函数依赖、多值依赖等，确定各关系模式分别属于第几范式。54/13优化数据模型的方法(续)

⒋按照需求分析阶段得到的各种应用对数据处理的要求，分析对于这样的应用环境这些模式是否合适，确定是否要对它们进行合并或分解。⒌按照需求分析阶段得到的各种应用对数据处理的要求，对关系模式进行必要的分解或合并，以提高数据操作的效率和存储空间的利用率55/13模式评价功能评价：检查用户的每一项业务是否都能被关系模式支持；性能评价：主要采用定性判断的方法衡量不同设计方案的优劣。性能评价的依据：尽量不用笛卡尔积运算：用连接代替尽量减少连接运算：适当冗余减小关系的数据量：关系分割（横向/纵向）例如，把学生信息分解为历史学生