《SQL Server数据库应用开发技术》课件第二章

第2章

数据库设计

知识技能目标：

1．理解数据库设计过程 2．掌握数据库设计的方法步骤第2章

数据库设计

引导案例：

小型教务管理系统，可进行选课，录入、修改、查询、统计成绩，管理基本信息 这些工作涉及到学生的数据、课程的数据、学生每门课程的成绩、系部、专业和班级的数据等，这些数据需要合理地组织存储 这就需要进行数据库设计，即设计教务管理数据库。 本章来介绍教务管理数据库的设计。

第2章

数据库设计

2.1数据库设计过程

2.2教务管理系统需求

2.3建立教务管理E-R模型

2.4建立教务管理关系模型

2.5设计数据完整性

2.6教务管理数据库的逻辑结构

小结2.1数据库设计过程数据库设计:

是指按照应用要求为某一组织设计一个结构合理、使用方便、性能良好的数据库及其应用系统。返回目录2.1数据库设计过程返回目录数据库设计的内容:

两个方面： 数据库的结构设计(静态) 数据库的行为设计(动态)2.1数据库设计过程数据库设计过程：数据库运行与维护需求分析数据库逻辑设计数据库物理设计数据库实施需求说明逻辑结构物理结构数据库系统返回目录2.1数据库设计过程

需求分析

需求分析的任务是对现实环境进行详细调查，对用户业务活动进行仔细分析，在此基础上充分理解和明确用户的各种需求，包括用户对数据的需求、对数据处理的需求、安全性需求、完整性需求。 需求分析是整个数据库设计的基础，以后的设计都将依赖于这一步。因此需求分析是至关重要的一步。需求分析的结果是否准确反映了用户的要求，将决定最终建立的数据库是否实用。

返回目录2.1数据库设计过程

数据库逻辑设计

包括：概念结构设计逻辑结构设计

概念结构设计：是在需求分析的基础上，对用户需求进行抽象，建立一个独立于具体数据库管理系统的概念模型，如E-R模型。概念结构设计是整个数据库设计的关键，建立的概念模型是否合理，将决定整个数据库设计的成败。

逻辑结构设计：是将在概念结构设计中建立的概念模型转换为具体某一数据库管理系统支持的数据模型，并对这一数据模型进行优化。如将E-R模型转换为关系模型。返回目录2.1数据库设计过程 数据库物理设计

针对逻辑结构设计中获得的逻辑数据模型，设计数据库在物理设备上的存储结构和存取方法，即设计数据库的物理结构。 对于关系数据库，在进行物理设计时要考虑以下几个问题：

估算数据库的数据存储量

确定数据的存储位置设计索引返回目录2.1数据库设计过程 数据库实施

根据前面逻辑设计和物理设计的结果，进行以下工作： 建立数据库 编写、调试应用程序 组织数据入库 试运行应用系统 最后整理文档，编写应用系统的技术说明和使用说明书。返回目录2.1数据库设计过程 数据库运行维护 数据库应用系统试运行 投入正式运行 在运行过程中对数据库进行维护，包括： 数据库备份与恢复 数据库安全性与完整性控制 不断对系统进行评价、调整和完善返回目录2.1数据库设计过程

说明：

一个理想的数据库应该安全、可靠、便于维护、易于扩充、冗余最小，并且对用户的数据访问有较快的响应速度。 这样的数据库不可能一蹴而就，需要不断反复上述步骤，反复探寻、逐步求精。 数据库设计过程是一个反复探寻、逐步求精的过程。返回目录2.2教务管理系统需求系统需求分析:详细调查了解具体院校的教务管理工作情况，明确用户的各种需求；确定教务管理系统的边界，即确定哪些工作由计算机完成，哪些工作由人工完成；具体分析系统应具有哪些功能，完成哪些任务，确定教务管理系统的功能功能需求；仔细分析教务管理活动流程，分析教务管理活动中所涉及到的数据和数据格式，如何加工处理这些数据，从而确定系统对哪些数据完成什么样的处理，有什么样的安全性要求和完整性要求，即确定系统的数据需求。返回目录2.2教务管理系统需求教务管理系统功能需求：

（1）选课管理 提供选课界面，列出有关课程信息供学生查询选课。记录存储学生选课信息，随时查询学生选课信息，同时允许学生退课。 （2）成绩管理 对学生所选课程的成绩进行管理，包括成绩登记、成绩查询、统计汇总以及成绩的修改和删除。 （3）基本信息的存储、检索和维护 包括学生信息、课程信息、班级信息、专业信息、系部信息和用户信息等基本信息的输入存储，按多种方式进行检索，并能对这些信息进行修改和删除。返回目录2.2教务管理系统需求教务管理系统数据需求：

（1）数据 有关学生的数据；有关课程的数据；有关成绩的数据；有关班级、专业、系部的数据。 （2）数据处理 选课管理，成绩管理，对学生、课程、班级、专业、系部以及用户等进行管理。 （3）完整性要求 每门课程分别属于公共课、专业基础课和专业课三个类型，每门课程可以分别是必修课、选修课或限选课，每门课程可以分别以考试或考查的方式进行考核，课程分别设置在第一至第八学期，每门课程的考试成绩取百分制成绩。返回目录（4）安全性要求学生、教师、教务人员三类不同用户分别具有不同的使用权限。2.3建立教务管理E-R模型

进行概念结构设计，将现实世界的用户需求抽象为信息世界的概念模型。 E-R模型是用E-R图来表示的概念模型。它独立于具体的DBMS，是现实世界与机器世界的中介，既能够充分反映现实世界，又易于向机器世界的数据模型转换。 建立E-R模型是整个数据库设计的关键。返回目录2.3建立教务管理E-R模型

建立E-R模型，一般采用自底向上的方法：建立局部的E-R模型将局部E-R模型进行集成建立总体的E-R模型

返回目录2.3建立教务管理E-R模型2.3.1建立局部E-R模型

根据需求分析的结果，针对每个具体应用确定：实体实体的属性实体的键实体间的联系

返回目录2.3建立教务管理E-R模型2.3.1建立局部E-R模型

具体讲，按现实世界中事物的自然划分来定义实体和属性，然后再进行必要的调整。 调整的原则：属性本身不能再有需要描述的性质；能作为属性对待的尽量作为属性对待。

返回目录2.3建立教务管理E-R模型2.3.1建立局部E-R模型

选课管理局部E-R模型mn学号学生课程选修课程号课程名成绩姓名返回目录2.3建立教务管理E-R模型2.3.1建立局部E-R模型

成绩管理局部E-R模型学生成绩课程成绩姓名学号返回目录2.3建立教务管理E-R模型2.3.1建立局部E-R模型

基本信息管理的局部E-R模型

学生学号出生日期政治面貌姓名性别入学时间联系电话班级班号班名班主任学生人数专业专业号专业名

系部系部号系部名系主任教师人数课程课程号课程名学时学分学期考核方式课程性质课程类型返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

各个局部E-R模型建立好后，将这些局部E-R模型进行集成，建立全局E-R模型。

1.合并局部E-R模型，解决冲突，生成初步E-R图2.修改和重构初步E-R图，消除冗余，生成基本 E-R图。返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

合并局部E-R模型生成初步E-R图学生学号出生日期政治面貌姓名性别入学时间联系电话属于班级班号班名班主任学生人数属于专业专业号专业名属于系部系部号系部名系主任教师人数n11n1n返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

合并局部E-R模型生成初步E-R图学生学号出生日期政治面貌姓名性别入学时间联系电话属于班级班号班名班主任学生人数属于专业专业号专业名属于系部系部号系部名系主任教师人数n11n1n成绩选修mn课程号课程名学时学分学期课程类型考核方式课程性质课程学生成绩学号姓名成绩具有1n返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

合并局部E-R模型生成初步E-R图

关键要消除结构冲突、属性冲突和命名冲突。

结构冲突：同一事物在不同应用中具有不同的抽象；同一实体在不同的应用中属性组成不同(个数,次序)；同一联系，在不同应用中呈现不同类型。

属性冲突：属性域冲突，即属性值的类型和取值范围不同、属性的取值单位冲突。

命名冲突：同名异义、异名同义，即不同意义的事物在不同的局部应用中具有相同的名字，同一意义的事物在不同的局部应用中具有不同的名字。

返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

消除冗余生成基本E-R图

生成初步E-R图后，进一步检查是否存在冗余的数据和冗余的联系。

冗余的数据是指可由基本数据导出的数据；

冗余的联系是指可由其他联系导出的联系。 如果存在这些冗余，就需要对初步E-R图进行修改和重构，消除不必要的冗余，生成基本E-R图，这样就建立起了全局E-R模型。

返回目录2.3建立教务管理E-R模型2.3.2集成全局E-R模型

消除冗余生成基本E-R图学生学号出生日期政治面貌姓名性别入学时间联系电话属于班级班号班名班主任学生人数属于专业专业号专业名属于系部系部号系部名系主任教师人数n11n1n课程号课程名学时学分学期课程类型考核方式成绩课程性质选修课程mn返回目录2.4建立教务管理关系模型

E-R模型是概念结构设计的结果 接下来进行逻辑结构设计:

将E-R模型转换为关系模型 对关系模型进行优化返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型

关系模型实质上就是一组关系模式的集合

E-R模型转换为关系模型，就是将E-R模型中的实体、实体间的联系转换为关系模式。 具体转换方法如下：返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型（1）一个实体型转换为一个关系模式，实体的属性就是关系的属性，实体的键就是关系的键。

例如，“学生”实体转换为关系模式： 学生（学号，姓名，性别，出生日期，政治面貌，入学时间，联系电话)返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型（2）一个联系转换为一个关系模式，相联系实体的键和联系的属性转换为关系的属性。对“多对多”联系，关系的键为各实体的键的组合；对“一对多”联系，关系的键为“多”端实体的键，或者也可以将此关系模式与“多”端实体对应的关系模式合并；对“一对一”联系，每个实体的键均为该关系的候选键，或者也可以将此关系模式与任意一端实体对应的关系模式合并。返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型

例如，“学生”与“课程”间的联系“选修”,是一个“多对多”联系 可将它转换为关系模式： 选课（学号，课程号，成绩）

又如，“系部”与“专业”间的联系“属于”,是一个“一对多”联系 可将它转换为关系模式： 属于（专业号，系部号） 也可以将此关系模式与“多”端实体“专业”对应的关系模式合并 合并后的关系模式为： 专业（专业号，专业名，系部号）返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型（3）三个或三个以上实体间的一个多元联系转换为一个关系模式，相联系实体的键和联系的属性转换为关系的属性，关系的键为各实体的键的组合。返回目录2.4建立教务管理关系模型2.4.1E-R模型转换为关系模型按此方法，将教务管理E-R模型转换为如下关系模型：学生（学号，姓名，性别，出生日期，政治面貌，入学时间，联系电话，班号）课程（课程号，课程名，学时，学分，课程类型，课程性质，考核方式，学期）选课（学号，课程号，成绩）班级（班号，班名，班主任，学生人数，专业号）专业(专业号，专业名，系部号)系部(系部号，系部名，系主任，教师人数)返回目录2.4建立教务管理关系模型2.4.2优化关系模型 将E-R模型转换为关系模型后，还需要进行关系规范化处理，对关系模型进行优化，对关系模型的结构作进一步调整与完善，以达到提高数据库性能的目的。返回目录2.4建立教务管理关系模型2.4.2优化关系模型 关系规范化处理以规范化理论为指导1．范式 满足不同程度规范的关系模式，称之为不同的范式（NormalForm，简称NF）。

返回目录2.4建立教务管理关系模型2.4.2优化关系模型 第一范式： 如果一个关系模式R的所有属性都是不可再分的数据项，则R为第一范式。记作：R∈1NF。例如，关系模式： R(学号，课程号，成绩，姓名，性别，班级，班主任)其中每个属性都不可再分，因此满足1NF。

第一范式是对关系的一个最低要求。一个规范化的关系至少应满足第一范式的要求。返回目录2.4建立教务管理关系模型2.4.2优化关系模型 第二范式： 若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的关键字，则R为第二范式。记作：R∈2NF。返回目录2.4建立教务管理关系模型2.4.2优化关系模型

例如，关系模式： R(学号，课程号，成绩，姓名，性别，班级，班主任)其中函数依赖为： （学号，课程号）→成绩学号→姓名学号→性别学号→班级班级→班主任学号→班主任“姓名”、“性别”等非主属性部分依赖于关键字。因此关系R不满足第二范式。返回目录2.4建立教务管理关系模型2.4.2优化关系模型

又如，关系模式：

R1(学号，姓名，性别，班级，班主任) 其中每个属性都不可再分，因而属于第一范式。 并且每个非主属性都完全函数依赖于R1的关键字“学号”。 因此关系R1满足第二范式。

第二范式消除了非主属性对关键字的部分函数依赖。即，满足2NF的关系，其非主属性完全函数依赖于关键字。

返回目录2.4建立教务管理关系模型2.4.2优化关系模型 第三范式： 若关系模式R∈2NF，并且它的每一个非主属性不传递依赖于R的关键字，则R为第三范式。记作：R∈3NF。

返回目录2.4建立教务管理关系模型2.4.2优化关系模型

例如，关系模式： R1(学号，姓名，性别，班级，班主任)第二范式

其中存在函数依赖： 学号→班主任班级→班主任 非主属性“班主任”传递函数依赖于关键字“学号”。因此关系R1不满足第三范式。返回目录2.4建立教务管理关系模型2.4.2优化关系模型

又如，关系模式： R1(学号，姓名，性别，班级)

每个属性不可再分，非主属性“姓名”、“性别”、“班级”都完全函数依赖于关键字“学号”，并且每个非主属性都不传递函数依赖于关键字“学号”。 因此关系R11满足第三范式。 第三范式消除了非主属性对关键字的传递函数依赖，保证非主属性既不部分依赖于关键字，也不传递依赖于关键字。

返回目录2.4建立教务管理关系模型2.4.2优化关系模型2．规范化

低级范式的关系模式中存在非主属性对关键字的部分函数依赖和传递函数依赖，这些不合适的数据依赖将导致关系中存在删除异常、插入异常、修改复杂、数据冗余的问题。 规范化就是将一个低一级范式的关系模式转化为若干个高一级范式的关系模式的集合，消除原来关系模式中存在的不合适的数据依赖，使得各个关系模式达到某种程度的分离，从而解决关系中存在的数据冗余、插入异常、修改复杂、删除异常等问题。返回目录2.4建立教务管理关系模型2.4.2优化关系模型2．规范化

例如，关系模式：

R(学号，课程号，成绩，姓名，性别，班级，班主任)

R不属于第二范式 对其进行投影，将其分解为两个关系模式：

R1(学号，姓名，性别，班级，班主任) R2(学号，课程号，成绩) R1、R2属于第二范式

返回目录2.4建立教务管理关系模型2.4.2优化关系模型2．规范化

又如，关系模式：

R1(学号，姓名，性别，班级，班主任) 不属于第三范式 对其进行投影，将其分解为两个关系模式：

R11(学号，姓名，性别，班级) R12(班级，班主任) R11、R12属于第三范式返回目录2.4建立教务管理关系模型2.4.2优化关系模型2．规范化

可见，规范化就是通过投影运算把低一级范式的关系模式分解为若干个高一级范式的关系模式，从而消除原来关系模式中存在的不合适的数据依赖，使得各个关系模式达到某种程度的分离。 规范化实质上是概念的单一化，用一个关系表示一个实体或实体间的联系。 规范化过程就是对关系模式不断分解的过程。返回目录2.4建立教务管理关系模型2.4.2优化关系模型2．规范化注意： 范式级别越高，规范化程度越高。但在具体应用中并不是说规范化程度越高越好。因为，如果规范化程度太低，则存在如前所述的数据冗余严重等问题，但如果规范化程度过高，则会影响系统速度。因此在实际应用中应该具体应用具体对待。返回目录2.5设计数据完整性

为保证数据库中的数据正确、有效、一致可靠，需要根据教务管理实际应用的需求进行数据完整性设计，包括：实体完整性设计

域完整性设计参照完整性设计用户定义完整性设计

返回目录2.5设计数据完整性1．实体完整性设计

为保证实体完整性，为每个关系设计主关键字。如:学生（学号，姓名，性别，出生日期，政治面貌，入学时间，联系电话，班号）课程（课程号，课程名，学时，学分，课程类型，课程性质，考核方式，学期）选课（学号，课程号，成绩）班级（班号，班名，班主任，学生人数，专业号）专业(专业号，专业名，系部号)系部(系部号，系部名，系主任，教师人数) 主关键字的值不能为空，这就保证了关系中每一个元组都是可识别的。一个元组对应一个实体，因此保证了每个实体都区别于其他实体，是可识别、可区分的。返回目录2.5设计数据完整性2．域完整性设计

根据教务管理实际应用，为每个关系的每个属性设计数据类型、格式和有效的数据范围。

如：学生关系中的属性“学号”设计为字符型，长度为11个字符；属性“性别”设计为字符型，取值范围为“男”或“女”；选课关系中的属性“成绩”设计为数值型，取值范围为0-100等。

域完整性保证了每个属性具有正确的数据类型、格式和有效的数据范围。返回目录2.5设计数据完整性3．参照完整性设计

为保证数据的一致性，作如下设计：

学生关系中的属性“班号”的值参照班级关系中的主键“班号”的值；

选课关系中的属性“学号”的值参照学生关系中的主键“学号”的值；

选课关系中的属性“课程号”的值参照“课程”关系中主键“课程号”的值； 班级关系中的属性“专业号”的值参照专业关系中主键“专业号”的值； 专业关系中的属性“系部号”的值参照系部关系中主键“系部号”的值。 参照完整性要求参照关系中外键的值，或者等于被参照关系中主键的值，或者为空。这就保证了同一属性在不同关系中值的一致性。返回目录2.5设计数据完整性4．用户定义完整性设计

用户定义完整性是根据具体应用环境的要求和实际需要，对某一具体应用所涉及的数据提出约束性条件。针对教务管理具体应用，设计如下约束性条件：“系部号”的组成为：“00”+系部编号；“专业号”的组成为：系部号+专业编号；“学号”的组成为：入学年份+系部编号+专业编号+学生编号“课程号”的组成为：系部编号+专业编号+课程编号公共课的“课程号”组成为：“0000”+课程编号用户定义完整性保证了具体应用所要求的数据完整性

返回目录2.6教务管理数据库的逻辑结构

通过以上设计得到一组关系模式，这组关系模式的集合即为教务管理数据库模式。 每一个关系模式就是教务管理数据库中的一个表，每个属性就是表中的一个字段。 教务管理数据库的逻辑结构如下：返回目录2.6教务管理数据库的逻辑结构返回目录字段名数据类型约束学号char(11)主键姓名nchar(5)非空性别nchar(1)值为‘男’或‘女’出生日期datetime政治面貌varchar(10)默认为‘共青团员’入学时间datetime联系电话char(20)班级char(5)外键（参照班级表的班号），非空备注varchar(200)学生表2.6教务管理数据库的逻辑结构返回目录课程表字段名数据类型约束课程号char(6)主键课程名varchar(20)非空，唯一学时tinyint学分tinyint课程类型nchar(5)非空值为‘公共课’、‘专业基础课’或‘专业课’课程性质nchar(2)值为‘必修’、‘选修’或‘限选’考核方式nchar(20)值为‘考试’或‘考查’，默认为‘考试’学期char(1)值为‘1’，‘2’，‘3’，‘4’，‘5’，‘6