数据库原理及应用（MySQL项目式版）- 课件 项目2 数据库设计

项目2数据库设计项目目标了解数据库设计阶段。掌握E-R图的绘制方法。掌握关系模式的设计方法。了解关系模式的规范化。任务2.1认识数据库设计数据库设计大多采用生命周期法，将整个数据库设计分解为6个阶段。1.需求分析根据用户的需求收集并分析数据。通过调查和分析用户的业务活动和数据使用情况，分析所用数据的类型、范围、数量以及它们在业务活动中的应用情况，确定用户对数据库系统的使用要求和各种约束等。2.概念结构设计通过对需求分析阶段收集的数据进行综合、归纳与抽象，形成一个不依赖于任何数据库管理系统的概念模型。概念结构设计最常用的方法之一是绘制E-R图。概念结构设计是整个数据库设计的关键。3.逻辑结构设计将概念模型转换为选用的数据库管理系统所支持的逻辑模型，即将用E-R图表示的实体集以及实体集之间的联系转换为所选择的数据库管理系统支持的数据模型，然后对其进行规范化。4.物理结构设计为所设计的逻辑模型选择合适的存储结构和存取方法，以提高数据库的访问速度和存储空间的利用率。目前，大量关系数据库屏蔽了数据库内部的存储结构，因此需要设计人员参与物理结构设计的任务很少。5.数据库实施设计人员运用数据库管理系统提供的数据库语言及实用程序，根据逻辑结构设计和物理结构设计的结果建立数据库，编写与调试应用程序，组织数据入库，并进行试运行。6.数据库运行和维护数据库正式投入运行后，就进入数据库运行和维护阶段。在这个阶段，必须不断地对数据库进行评估、调整、修改以及备份。本项目主要介绍前3个阶段，即需求分析、概念结构设计和逻辑结构设计。任务2.2需求分析需求分析是整个数据库设计的基础。需求分析的充分程度和准确程度，决定了在其上构建数据库的速度与质量。2.2.1需求分析的任务和重点1.任务详尽地调查现实世界中要处理的对象（如图书馆、院系等），充分了解该对象当前所属数据管理系统（手工系统或计算机系统）的工作概况，尽可能多地收集数据，明确用户的各种需求，然后在此基础上确定新系统的功能。2.重点调查、收集与分析用户对于数据管理的信息要求、处理要求、安全性与数据完整性要求。2.2.2需求分析的方法1.调查用户的实际需求2.分析和表达用户需求3.得到用户的认可1.调查用户的实际需求调查用户实际需求的具体步骤如下。①调查要处理的对象（如图书馆、院系等）的基本情况，包括了解其部门组成情况，以及各个部门的职能等。②调查各个部门的业务活动情况，包括了解各个部门输入和使用什么数据、如何处理这些数据、输出什么格式的数据、数据输出到什么部门等。③协助用户明确对于数据管理的各种要求，包括信息要求、处理要求、安全性与数据完整性要求。④确定新系统的边界，确定哪些功能由计算机完成或将来准备让计算机完成、哪些功能由人工完成。2.分析和表达用户需求结构化分析方法是最简单、最实用的分析和表达用户需求的方法之一。结构化分析方法采用自顶向下、逐层分解的方式分析需求，建立需求的处理流程。如图所示为自顶向下、逐层分解的需求分析示意。3.得到用户的认可对用户需求进行分析与表达后，必须将结果提交给用户，得到用户的认可后，数据库中需要存储的具体数据和新系统的功能就确定了。任务2.3概念结构设计概念结构设计的任务是将需求分析阶段收集的数据抽象为概念模型。概念结构设计是整个数据库设计的关键，它的最终结果为整个系统的E-R图。按照自顶向下、逐层分解的需求分析示意，概念结构设计可以采用自底向上、逐层设计与合并的方法，先设计底层的局部概念结构（局部E-R图），然后将它们逐层合并起来，最终形成完整的全局概念结构（全局E-R图）。2.3.1局部E-R图设计【例2-1】设有如下4个实体集及其属性，请分析各个实体集之间的联系并绘制局部E-R图。（1）书目实体集，用于描述图书馆的书目信息，每个图书编号对应一种图书，一种图书可以馆藏多本。书目（图书编号、书名、作者、出版社、馆藏数量）（2）馆藏图书实体集，用于描述图书馆中的所有藏书，图书状态有2种：在馆和借出。馆藏图书（馆藏编号、书名、作者、出版社、存放位置、图书状态）（3）读者类型实体集，用于描述不同读者群体的分类以及最大借阅数量的差异。读者类型（类型编号、类型名称、最大借阅数量）（4）读者实体集，用于描述读者的身份信息。读者（读者编号、姓名、性别、出生日期、读者类型）1.各个实体集之间的联系①一个读者可以借阅多本馆藏图书，一本馆藏图书也可以先后被多个读者借阅，读者实体集和馆藏图书实体集之间是多对多联系。②一种读者类型可以包含多个读者，但一个读者只能属于一种读者类型，读者类型实体集和读者实体集之间是一对多联系。③一种图书可以馆藏多本，但一本馆藏图书只能属于书目中的一种图书，书目实体集与馆藏图书实体集之间是一对多联系。根据给定的实体集及其属性直接绘制出以上3个联系的局部E-R图。一个读者可以借阅多本馆藏图书，一本馆藏图书也可以先后被多个读者借阅，读者实体集和馆藏图书实体集之间是多对多联系。读者借阅馆藏图书时要记录借出时间和归还时间。读者借阅馆藏图书的局部E-R图读者与读者类型的局部E-R图一种读者类型可以包含多个读者，但一个读者只能属于一种读者类型，读者类型实体集和读者实体集之间是一对多联系。书目与馆藏图书的局部E-R图一种图书可以馆藏多本，但一本馆藏图书只能属于书目中的一种图书，书目实体集与馆藏图书实体集之间是一对多联系。2.3.2全局E-R图设计各个局部E-R图绘制好后，需要将它们合并，形成完整的概念模型，即全局E-R图。全局E-R图设计主要分为合并和优化两个步骤。1.合并合并并不是简单地将各个局部E-R图拼接在一起，而是在拼接的同时消除各个局部E-R图之间的冲突，局部E-R图中的冲突主要有3种：属性冲突：分为属性值域冲突和属性的取值单位冲突。命名冲突：实体集名、属性名或联系名之间存在的同名异义或异名同义。结构冲突：有3种情况。第1种情况是同一个对象，一个抽象为实体集，另一个抽象为属性；第2种情况是同一个实体集的属性组成不同；第3种情况是实体集之间的联系类型不同，一个是一对一，另一个是多对多。消除合并过程中的冲突存在结构冲突：“读者”实体集中的“读者类型”是属性，同时有“读者类型”实体集。2.优化优化的目的是消除不必要的冗余。冗余的属性：是指可由基本属性推导或计算得到的属性。冗余的实体集之间的联系：是指可由其他联系推导出的联系。不是所有冗余的属性和冗余的实体集之间的联系都必须消除，有些情况下为了提高系统的效率，不得不以包含冗余信息为代价。在优化过程中，需要根据实际情况和整体需求来确定可消除的冗余信息。2.优化冗余的属性：读者：读者类型书目：馆藏数量馆藏图书：书名、作者、出版社、图书状态合并和优化后得到的全局E-R图考虑到数据库的查询性能，采用通过空间换时间的设计策略，保留了书目实体集冗余的“馆藏数量”和馆藏图书实体集冗余的“图书状态”，这样可避免查询这两种属性时跨其他实体集或者联系产生的开销，加快查询响应速度。任务2.4逻辑结构设计逻辑结构设计就是将E-R图中的各个实体集以及实体集之间的联系转换为一组关系模式。2.4.1关系模式设计将E-R图中各个实体集以及实体集之间的联系转换为一组关系模式时，需要遵循以下原则。1.实体集的转换原则一个实体集转换为一个独立的关系模式。实体集的属性就是关系的属性，实体集的主键就是关系的主键。2.实体集之间的联系的转换原则根据不同的联系类型做不同的处理，有5种情况。2.4.1关系模式设计2.实体集之间的联系的转换原则（1）一对一联系：转换为一个独立的关系模式，或者与任意一端的关系模式合并。（2）一对多联系：转换为一个独立的关系模式，或者与n端的关系模式合并。（3）多对多联系：转换为一个独立的关系模式。（4）3个或3个以上实体集之间的多元联系：转换为一个独立的关系模式。（5）具有相同主键的关系模式可以合并：将其中一个关系模式的全部属性加入另一个关系模式中，然后去掉其中的同义属性。2.4.1关系模式设计【例2-2】将合并和优化后得到的全局E-R图中各个实体集以及实体集之间的联系转换为一组关系模式。2.4.1关系模式设计方案1：将4个实体集（读者类型、读者、书目和馆藏图书）分别转换成独立的关系模式，将3个联系（包含、馆藏、借阅）分别转换成3个独立的关系模式。①读者类：读者类型表（类型编号，类型名称，最大借阅数量）②读者：读者表（读者编号，姓名，性别，出生日期）③书目：图书表（图书编号，书名，作者，出版社，馆藏数量）④馆藏图书：馆藏表（馆藏编号，存放位置，图书状态）⑤包含：读者与读者类型联系表（读者编号，类型编号）⑥馆藏：馆藏图书与书目联系表（馆藏编号，图书编号）⑦借阅：借还书表（读者编号，馆藏编号，借出时间，归还时间）2.4.1关系模式设计方案2：将“包含”和“馆藏”两个一对多联系分别合并到n端对应的关系模式中。①读者类型：读者类型表（类型编号，类型名称，最大借阅数量）②读者：读者表（读者编号，类型编号，姓名，性别，出生日期）③书目：图书表（图书编号，书名，作者，出版社，馆藏数量）④馆藏图书：馆藏表（馆藏编号，图书编号，存放位置，图书状态）⑤借阅：借还书表（读者编号，馆藏编号，借出时间，归还时间）2.4.2关系模式的规范化为了提升数据库的性能，还应该根据应用的实际要求适当进行调整，即对关系模式进行规范化。按照规范化的级别，可以将范式分为6种，从低到高依次为：第一范式（1NF）第二范式（2NF）第三范式（3NF）BC范式（BCNF）第四范式（4NF）第五范式（5NF）在实际的数据库设计中，通常只需要用到前3种范式。1.第一范式（1NF）1NF要求关系（数据表）中的每一个分量（属性值）都是不可再分割的数据项，即一个属性不能有多个值。“作者”中包含两个人的姓名（多值存储）2.第二范式（2NF）2NF是在1NF的基础上建立的，即要想满足2NF，必须先满足1NF。2NF要求数据表中的每条记录都是唯一的，而且每个属性完全依赖主键。完全依赖主键是指不能仅依赖主键中的一部分属性。如果存在不完全依赖主键的属性，就应该将其分离出来形成一个新的数据表。表的主键：读者编号+书名，“借出时间”完全依赖主键，而“姓名”和“性别”仅依赖“读者编号”，“存放位置”仅依赖“书名”。可能存在数据冗余、更新异常、插入异常、删除异常等问题。2.第二范式（2NF）分离为3张表增加了“馆藏编号”属性，便于区分书名相同的不同馆藏图书。3.第三范式（3NF）3NF是在2NF的基础上建立的，即要想满足3NF，必须先满足2NF。3NF要求数据表不存在非主属性对任意主属性的传递函数依赖。主属性是指能够唯一标识一条记录的所有属性（一张数据表可以有多个主属性，但只能有一个主键，通常指定某个主属性作为主键）。传递函数依赖是指如果存在主属性A决定非主属性B，而非主属性B决定非主属性C，则称非主属性C传递函数依赖主属性A。主属性：读者编号，读者编号决定了非主属性读者类型，而读者类型又可以决定非主属性最大借阅数量。可能存在数据冗余、更新异常、插入异常、删除异常等问题。3.第三范式（3NF）分离为2张表增加了“类型编号”属性，便于快速索引3.第三范式（3NF）3NF还要求不在数据表中存储可以通过简单计算得出的数据。这样不但可以节省存储空间，而且在函数依赖中的决定方发生变化时，可以避免成倍修改数据的麻烦，以及修改过程中可能引入的人为错误。主属性：读者编号，读者编号决定了出生日期，而年龄可以根据出生日期计算得到（年龄=当前年份-出生年份），不同的年份所计算出的年龄不同，没有必要存储年龄。关系模式的规范化举例【例2-3】检验例2-2方案1和方案2中的关系模式是否满足3NF规范化要求。（1）判断是否满足1NF。由于每个关系模式中的所有属性值都是不可再分割的数据项，因此满足1NF。（2）判断是否满足2NF。以读者类型表为例，“类型编号”为主键，其余的所有属性都完全依赖类型编号。其他关系模式也都如此，因此满足2NF。（3）判断是否满足3NF。由于所有关系模式中都不存在非主属性对任意主属性的传递函数依赖，因此满足3NF。综上，例2-2方案1和方案2中的关系模式满足3NF规范化要求。项目案例：设计图书馆借还书管理数据库1．需求分析（收集并分析数据）以小型图书馆为应用场景，通过与图书馆管理人员进行交流，确定了以下需求。（1）书目中的一种图书可以馆藏多本。（2）用户可以很方便地获取图书的书名、作者（仅第一作者）、出版社等数据。（3）每本馆藏图书拥有唯一编号，不可重复。（4）读者分为学生和教师两种类型。每个学生最多可借阅5本，最多可借阅60天；每个教师最多可借阅8本，最多可借阅90天。（5）删除某个读者时，必须归还借阅的所有图书。（6）暂不考虑图书续借以及财务相关的功能。2.5课堂案例：设计学生成绩管理数据库2．概念结构设计（绘制E-R图）对需求分析收集的数据及信息进行抽象，得出图书馆借还书管理数据库包含的4个实体集及其属性如下