MySQL数据库教程课件

第2章数据库设计学习目标了解数据库设计步骤。掌握E-R图的绘制方法。掌握关系模式的设计方法。了解关系模式规范化的相关范式。2.1数据库设计步骤数据库设计大多采用生命周期法，将整个数据库设计分解为6个阶段。1.需求分析2.概念结构设计3.逻辑结构设计4.物理结构设计5.数据库实施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图设计将每个局部需求所涉及的数据抽象为实体集、属性以及实体集之间的联系。1.标识实体集在现实世界中，一组具有某些共同特性和行为的人、事或物可以抽象为一个实体集。例如在学校环境中，可以把宋洪博、刘向志、李媛媛等学生抽象为学生实体集。2.标识实体集的属性及主键属性即实体集的特征，主键是能够唯一标识实体集中某个实体的属性集。例如学号、姓名、性别、班级等可以抽象为学生实体集的属性，其中学号是主键。3.标识实体集之间的联系如果实体集之间存在联系，要确定联系的类型是一对一、一对多还是多对多。1.局部E-R图设计【例2-1】设有如下4个实体集及其属性，其中带下划线的属性为主键。学生（学号、姓名、性别、班级、院系、绩点）课程（课程编号、课程名称、学时、学分、开课院系）教师（教师工号、姓名、性别、职称、所属部门）院系（院系代码、院系名称、负责人）请给出院系学生选课的局部E-R图和院系教师讲授课程的局部E-R图。

首先要标识出局部需求中所涉及的实体集、实体集的属性及主键，以及实体集之间的联系，然后才能绘制出E-R图。院系学生选课的局部E-R图学生（学号、姓名、性别、班级、院系、绩点）课程（课程编号、课程名称、学时、学分、开课院系）院系（院系代码、院系名称、负责人）（1）院系和学生是一对多联系。（2）院系和课程是一对多联系。（3）学生和课程是多对多联系。院系教师讲授课程的局部E-R图课程（课程编号、课程名称、学时、学分、开课院系）教师（教师工号、姓名、性别、职称、所属部门）院系（院系代码、院系名称、负责人）（1）院系和教师是一对多联系。（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-2】将合并与优化后得到的全局E-R图中各个实体集以及实体集之间的联系转换为一组关系模式。关系模式设计举例方案1：将4个实体集“院系”“学生”“课程”“教师”和4个联系“拥有”“属于”“选修”“讲授”分别转换成8个独立的关系模式。院系（院系代码、院系名称、负责人）学生（学号、姓名、性别、班级）课程（课程编号、课程名称、学时）教师（教师工号、姓名、性别、职称）属于（学号、院系代码）拥有（教师工号、院系代码）选修成绩（学号、课程编号、成绩）讲授安排（班级、教师工号、课程编号）关系模式设计举例方案2：将“拥有”和“属于”联系分别合并到n端对应的关系模式中。院系（院系代码、院系名称、负责人）学生（学号、姓名、性别、班级、院系代码）课程（课程编号、课程名称、学时）教师（教师工号、姓名、性别、职称，院系代码）选修成绩（学号、课程编号、成绩）讲授安排（班级、教师工号、课程编号）2.4.2关系模式的规范化为了提高数据库的性能，还应该对关系模式进行优化。关系模式的优化通常以关系数据库范式理论为指导。按照规范化的级别，可以将范式分为5种：第一范式（1NF）第二范式（2NF）第三范式（3NF）第四范式（4NF）第五范式（5NF）在实际的数据库设计中，通常只需要用到前3种范式。1.第一范式（1NF）1NF要求关系（数据表）中的每一个属性值都是不可再分割的数据项，即一个属性不能有多个值。2.第二范式（2NF）2NF是在1NF的基础上建立的。2NF要求数据表中的每条数据都是唯一的，而且每个属性完全依赖主键。完全依赖主键是指不能仅依赖主键中的一部分属性。如果存在不完全依赖主键的属性，就应该分离出来形成一个新的数据表，新数据表与原数据表之间是一对多联系。2.第二范式（2NF）分离为3张表3.第三范式（3NF）3NF是在2NF的基础上建立的。3NF要求数据表不存在非主属性对任意主属性的传递函数依赖。主属性是指能够唯一标识一条记录的所有属性。传递函数依赖是指如果存在主属性A决定非主属性B，而非主属性B决定非主属性C，则称非主属性C传递函数依赖主属性A。3.第三范式（3NF）分离为2张表3.第三范式（3NF）3NF还要求不在数据库中存储可以通过简单计算得出的数据。关系模式的规范化举例【例2-3】检验例2-2方案1和方案2中的关系模式是否满足规范化要求。从1NF开始，逐步进行规范化检验。（1）判断是否满足1NF。由于每个关系模式中的所有属性值都是不可再分割的数据项，因此满足1NF。（2）判断是否满足2NF。以“学生”关系模式为例，“学号”为主键，其余的所有属性都完全依赖学号。其他关系模式都如此，因此满足2NF。（3）判断是否满足3NF。由于所有模式都不存在非主属性对主属性的传递函数依赖，因此满足3NF。综上，例2-2方案1和方案2中的关系模式满足规范化要求。2.5课堂案例：设计学生成绩管理数据库1．需求分析（收集数据）通过与学生和教务管理人员进行交流，得到以下需求。（1）数据库要存储每个学生的基本信息、各院系的基本信息、任课教师的基本信息、开设课程的基本信息。（2）能够输出学生的成绩单。（3）可以按要求查询相关数据，如查询某院系的学生信息、学生某门课程的成绩等。（4）可以实现各种数据统计，如学生人数、学生平均成绩、已修学分、绩点等。2.5课堂案例：设计学生成绩管理数据库2．概念结构设计（绘制E-R图）对需求分析得到的信息进行抽象，得出如下系统包含的实体集及其属性如下。学生（学号、姓名、性别、出生日期、政治面貌、班级、院系名称、入学总分、绩点、奖惩情况）课程（课程编号、课程名称、学时、学分、开课院系、任课教师）教师（教师工号、姓名、性