计算机数据库(经济会计类)五讲数据库设计随堂讲义.

1、第五章第五章 数据库设计数据库设计 数据库设计是开发数据库应用系统的数据库设计是开发数据库应用系统的 关键。简单地讲数据库设计就是创建数据关键。简单地讲数据库设计就是创建数据 库系统的管理对象。库系统的管理对象。 本章以学生信息管理系统为例本章以学生信息管理系统为例, ,介绍数介绍数 据库设计的有关内容。据库设计的有关内容。 数据库设计步骤数据库设计步骤 数据库的设计六个阶段：数据库的设计六个阶段： 1. 需求分析需求分析 2. 概念结构设计阶段概念结构设计阶段 3. 逻辑结构设计阶段逻辑结构设计阶段 4. 物理结构设计阶段物理结构设计阶段 5. 数据库实施数据库实施 6. 数据库运行与维护数

2、据库运行与维护 概念结构设计阶段概念结构设计阶段 需求分析需求分析 物理结构设计阶段物理结构设计阶段 逻辑结构设计阶段逻辑结构设计阶段 数据库运行维护数据库运行维护 数据库实施数据库实施 开始实施开始实施-施工建造施工建造 前期准备前期准备-图纸设计图纸设计 提出方案提出方案-小区规划小区规划 开始调研开始调研可行性分析可行性分析 业主入住业主入住 小区管理小区管理物业维护物业维护 建一个居民小区建一个居民小区 打个比喻：打个比喻： 需求分析是数据库设计的第一步。这一阶段主要是对数据需求分析是数据库设计的第一步。这一阶段主要是对数据 库应用系统所要处理的对象进行全面了解，包括了解用户需求，库应

3、用系统所要处理的对象进行全面了解，包括了解用户需求， 收集信息，提出问题及解决问题的思路等。收集信息，提出问题及解决问题的思路等。 例如：建立例如：建立“学生信息管理系统学生信息管理系统”，目的是对学院、系、，目的是对学院、系、 班级、教研室等部门的信息进行管理，还要管理教师和学生班级、教研室等部门的信息进行管理，还要管理教师和学生 的基本信息，以及学生选课的情况。的基本信息，以及学生选课的情况。 信息要求：系统涉及到学院、系、教研室、教师、学生、信息要求：系统涉及到学院、系、教研室、教师、学生、 班级、课程、选课等各方面的信息。班级、课程、选课等各方面的信息。 处理要求：增加、删除、修改各数

4、据对象中的数据，各处理要求：增加、删除、修改各数据对象中的数据，各 类查询、统计及报表打印等等。类查询、统计及报表打印等等。 安全性要求：只允许学院内部人员使用该系统，允许学安全性要求：只允许学院内部人员使用该系统，允许学 生查询成绩但不能修改，允许教师录入和修改学生成绩等。生查询成绩但不能修改，允许教师录入和修改学生成绩等。 概念设计的目标是产生反映需求信息的数据库概念设计的目标是产生反映需求信息的数据库 概念结构，它与具体的概念结构，它与具体的DBMS无关。概念设计用实无关。概念设计用实 体体-联系模型联系模型(简称简称E-R图图) 表示。在表示。在E-R模型中，用模型中，用 于描述数据的

5、概念主要有实体、属性、实体集、关于描述数据的概念主要有实体、属性、实体集、关 键字及实体之间的联系等。键字及实体之间的联系等。 现实世界现实世界概念模型概念模型 将现实世界将现实世界客观存在的事物及其客观存在的事物及其 相互间联系通过符号记录下来相互间联系通过符号记录下来抽抽 象为信息世界。象为信息世界。 (1) 实体：客观存在并相互区别的事物。实体：客观存在并相互区别的事物。 实体可以是可触及的对象，如一个学生，一本实体可以是可触及的对象，如一个学生，一本 书，一辆汽车；也可以是抽象的事件，如一堂课，书，一辆汽车；也可以是抽象的事件，如一堂课， 一次比赛等。一次比赛等。 (2) 属性：实体所

6、具有的特征和性质。属性：实体所具有的特征和性质。 如：学生实体有学号、姓名、年龄、性别等。如：学生实体有学号、姓名、年龄、性别等。 (3) 码：能够唯一的标识一个实体的属性称为这码：能够唯一的标识一个实体的属性称为这 个实体的关键字，即主键。个实体的关键字，即主键。 如：学生的学号。而学生的姓名可能有重名，如：学生的学号。而学生的姓名可能有重名， 不能作为学生实体的关键字不能作为学生实体的关键字(码码)。 (4) 实体集：具有相同属性的同一类事物，是实体集：具有相同属性的同一类事物，是 一个实体的集合。一个实体的集合。 如：所有的学生信息记录、所有的课程信息如：所有的学生信息记录、所有的课程信

7、息 记录等。记录等。 两个或两个以上的实体集间的关联关系的描述。两个或两个以上的实体集间的关联关系的描述。 如：系与系主任、班级与班长如：系与系主任、班级与班长(1:1关系关系) ；系与；系与 教研室、班级与学生教研室、班级与学生(1:n关系关系) ；学生与课程、教；学生与课程、教 师与课程师与课程(m:n关系关系) 。 实体集之间的联系分一对一实体集之间的联系分一对一(1:1)、一对多、一对多(1:n) 、多对多、多对多(m:n)3种。种。 E-R模型模型(概念模型的描述方法概念模型的描述方法) ，是用实体，是用实体-联系联系 图图(简称简称E-R图）来描述的，是通过图形来描述实体集图）来描

8、述的，是通过图形来描述实体集 、实体属性和实体集之间的联系。、实体属性和实体集之间的联系。 实体集实体集矩形框矩形框 属性属性椭圆框椭圆框 联系联系菱形框菱形框 例如：例如： 学院实体集的学院实体集的E-R图图 学院学院 学院编号学院编号学院名称学院名称院长姓名院长姓名学院电话学院电话学院地址学院地址 例如：教师与班级、学院与系和班级、学生例如：教师与班级、学院与系和班级、学生 和课程实体集之间联系的和课程实体集之间联系的E-R图。图。 教师教师 班级班级 班主任班主任 1 1 学生学生 课程课程 选课选课 m n 学院学院 系系 设置设置 n 1 班级班级 设置设置 1 n 例如：学生、教师

9、和课程三个实体共同参与了例如：学生、教师和课程三个实体共同参与了 一个选课联系，一般称这种由三类实体参与的联系一个选课联系，一般称这种由三类实体参与的联系 为三元联系。因为某教师选定一门课讲授，学生选为三元联系。因为某教师选定一门课讲授，学生选 中同一门课要学习时，三者才会联系起来。中同一门课要学习时，三者才会联系起来。 教师、学生与课程的教师、学生与课程的E-R图图 m m nn 多对多关系实际上是某两个表多对多关系实际上是某两个表 与第三个表的两个一对多关系，第与第三个表的两个一对多关系，第 三个表的主键包含两个字段，分别三个表的主键包含两个字段，分别 是前两个表的主键。是前两个表的主键。

10、 字段名字段名字段类型字段类型字段长度字段长度小数点小数点索引类型索引类型 学号学号字符型字符型6 6主索引主索引 姓名姓名字符型字符型6 6 性别性别字符型字符型2 2 出生年月出生年月日期日期/ /时间型时间型短日期短日期 籍贯籍贯字符型字符型5050 班级编号班级编号字符型字符型8 8外键外键 0短整型短整型数值型数值型学分学分 短整型短整型数值型数值型学时学时 12字符型字符型课程名课程名 主索引主索引5字符型字符型课程编号课程编号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 0短整型短整型数值型数值型学分学分 短整型短整型数值型数值型学时学时 12字符型

11、字符型课程名课程名 主索引主索引5字符型字符型课程编号课程编号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 2单精度单精度数值型数值型成绩成绩 普通索引普通索引7字符型字符型教师编号教师编号 普通索引普通索引5字符型字符型课程编号课程编号 普通索引普通索引6字符型字符型学号学号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 2单精度单精度数值型数值型成绩成绩 普通索引普通索引7字符型字符型教师编号教师编号 普通索引普通索引5字符型字符型课程编号课程编号 普通索引普通索引6字符型字符型学号学号 索引类型索引类型小数点小数点字段长度字段长度

12、字段类型字段类型字段名字段名 选课选课 学生学生- -选课选课(1:n)(1:n) 课程课程- -选课选课(1:n) (1:n) n M M n 进一步扩展到进一步扩展到2组多对多关系时，组多对多关系时， 第四个表的主键包含第四个表的主键包含3个字段，分个字段，分 别是前别是前3个表的主键。个表的主键。 字段名字段名字段类型字段类型字段长度字段长度小数点小数点索引类型索引类型 学号学号字符型字符型6 6主索引主索引 姓名姓名字符型字符型6 6 性别性别字符型字符型2 2 出生年月出生年月日期日期/ /时间型时间型短日期短日期 籍贯籍贯字符型字符型5050 班级编号班级编号字符型字符型8 8外键

13、外键 0短整型短整型数值型数值型学分学分 短整型短整型数值型数值型学时学时 12字符型字符型课程名课程名 主索引主索引5字符型字符型课程编号课程编号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 0短整型短整型数值型数值型学分学分 短整型短整型数值型数值型学时学时 12字符型字符型课程名课程名 主索引主索引5字符型字符型课程编号课程编号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 2单精度单精度数值型数值型成绩成绩 普通索引普通索引7字符型字符型教师编号教师编号 普通索引普通索引5字符型字符型课程编号课程编号 普通索引普通索引6字符型字

14、符型学号学号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 2单精度单精度数值型数值型成绩成绩 普通索引普通索引7字符型字符型教师编号教师编号 普通索引普通索引5字符型字符型课程编号课程编号 普通索引普通索引6字符型字符型学号学号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 选课选课 学生学生- -选课选课(1:n)(1:n) 课程课程- -选课选课(1:n) (1:n) 外键外键6字符型字符型 教研室编教研室编 号号 8字符型字符型职务职务 2字符型字符型性别性别 6字符型字符型姓名姓名 主索引主索引7字符型字符型教师编号教师编号 索

15、引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 外键外键6字符型字符型 教研室编教研室编 号号 8字符型字符型职务职务 2字符型字符型性别性别 6字符型字符型姓名姓名 主索引主索引7字符型字符型教师编号教师编号 索引类型索引类型小数点小数点字段长度字段长度字段类型字段类型字段名字段名 教师教师- -选课选课(1:n) (1:n) n M M n 此图为此图为 3 3 个 表个 表 与 第与 第 4 4 个表的个表的 联系。联系。 学生信息管理系统学生信息管理系统 ( (E-R概念模型概念模型) ) 逻辑结构设计：将已设计好的概念模型逻辑结构设计：将已设计好的概念模型(E

16、-R图图) 转换为与转换为与DBMS支持的数据模型相符的逻辑结构。支持的数据模型相符的逻辑结构。 概念模型概念模型逻辑模型逻辑模型 E-R模型与关系模型的转换规则：模型与关系模型的转换规则： 一个实体集转换为一个关系模式。实体的属一个实体集转换为一个关系模式。实体的属 性就是关系的属性，实体的键就是关系的键。性就是关系的属性，实体的键就是关系的键。 学院学院 学院编号学院编号学院名称学院名称院长姓名院长姓名学院电话学院电话学院地址学院地址 例如：将学院实体转换为一个关系，其属性取原例如：将学院实体转换为一个关系，其属性取原 来实体的属性，利用规则来实体的属性，利用规则1，得到关系模式：，得到关

17、系模式： 学院学院(学院编号学院编号,学院名称学院名称,院长姓名院长姓名,学院电话学院电话,学学 院地址院地址) 一个一个1:1联系可以转换为一个独立的关系模式联系可以转换为一个独立的关系模式 ，也可以与任意一端对应的关系模式合并。，也可以与任意一端对应的关系模式合并。 举例：两个实体举例：两个实体:一对一联系一对一联系 系系(系编号系编号,系名字系名字,系性质系性质) 系主任系主任 (工号工号,姓名姓名,年龄年龄) 系与系主任系与系主任(工号工号,系编号系编号) 系系(系编号系编号,系名字系名字,系性质系性质, 系主任姓名系主任姓名,年龄年龄) 方式方式1 方式方式2 注：一般注：一般1:1

18、联系采用将两个关系模式合并。联系采用将两个关系模式合并。 联系联系 系系 系编号系编号系名字系名字系性质系性质 系主任系主任 年龄年龄姓名姓名 拥有拥有 11 工号工号 一个一个1:n联系可以转换为一个独立的关系模式联系可以转换为一个独立的关系模式 ，也可以与，也可以与n端对应的关系模式合并。端对应的关系模式合并。(一般用合并一般用合并) 例如：两个实体例如：两个实体1:n关系关系 属于属于 n1 学生学生 姓姓 名名 性性 别别 学学 号号 出出 生生 年年 月月 籍籍 贯贯 班班 级级 编编 号号 班级班级 班班 级级 名名 班班 长长 姓姓 名名 专专 业业 系系 编编 号号 班班 级级

19、 编编 号号 联系联系 利用规则利用规则1将学生和班级实体各转换为一个关系，其属性将学生和班级实体各转换为一个关系，其属性 取原来实体的属性，得到关系模式：取原来实体的属性，得到关系模式： 学生学生(学号学号,姓名姓名,性别性别,出生年月出生年月,籍贯籍贯) 班级班级(班级编号班级编号，班级名称，班长姓名，专业，系编号，班级名称，班长姓名，专业，系编号) 由于班级与学生之间存在一对多的联系，利用规则由于班级与学生之间存在一对多的联系，利用规则3可将可将 这种联系合并到这种联系合并到n端，即将班级关系的码加入到学生关系中，端，即将班级关系的码加入到学生关系中， 得到最终结果：得到最终结果：学生学

20、生(学号学号,姓名姓名,性别性别,出生年月出生年月,籍贯籍贯,班级编号班级编号) 实体之间多对多的联系转换为一个独立的关系实体之间多对多的联系转换为一个独立的关系 模式。与该联系相关系的各实体的码及联系本身的属模式。与该联系相关系的各实体的码及联系本身的属 性均转换为此关系模式的属性，而关系的码为各实体性均转换为此关系模式的属性，而关系的码为各实体 的码的组合。的码的组合。 三个或以上的实体间的一个多元联系可以转换三个或以上的实体间的一个多元联系可以转换 为一个关系模式。为一个关系模式。 具有相同键的关系模式可以合并。具有相同键的关系模式可以合并。 例如：三个实体例如：三个实体m:n关系关系

21、选课选课 mm 学生学生 姓名姓名性别性别学号学号 出生出生 年月年月 籍贯籍贯 班级班级 编号编号 教师教师 姓名姓名性别性别 教研教研 室编室编 号号 教师教师 编号编号 联系联系 课程课程 课程课程 名名 学时学时学分学分 课程课程 编号编号 nn 成绩成绩 使用规则使用规则1：三个实体集转换为关系；：三个实体集转换为关系； 使用规则使用规则4和和5：为联系单独建立一个关系，即将：为联系单独建立一个关系，即将 学生学生课程、教师课程、教师课程实体之间多对多的多元课程实体之间多对多的多元“选选 课课”联系转换为一个独立的关系模式。联系转换为一个独立的关系模式。 实体之间多对多的多元实体之间

22、多对多的多元“选课选课”联系转换为一个联系转换为一个 独立的关系模式如下：独立的关系模式如下： 学生学生(学号学号,姓名姓名,性别性别,出生年月出生年月,籍贯籍贯,班级编号班级编号) 教师教师(教师编号教师编号,姓名姓名,性别性别,职务职务,教研室编号教研室编号) 课程课程(课程编号课程编号,课程名课程名,学时学时,学分学分) 选课选课(学号学号,课程编号课程编号,教师编号教师编号,成绩成绩) (新关系包含两组多对多实体的主键及其联系自新关系包含两组多对多实体的主键及其联系自 身的属性身的属性) 概念模型理论概念模型理论关系模型理论关系模型理论关系数据库关系数据库 实体集实体集关系关系二维表记

23、录集二维表记录集 实体实体元组元组记录记录 属性属性属性属性字段字段 属性值属性值分量分量数据项数据项 码码主键主键主键主键 学院学院(学院编号学院编号,学院名称学院名称,院长姓名院长姓名,电话电话,地址地址) 系系(系编号系编号,系名称系名称,系主任系主任,学院编号学院编号) 教研室教研室(教研室编号教研室编号,教研室名称教研室名称,系编号系编号) 教师教师(教师编号教师编号,姓名姓名,性别性别,职务职务,教研室编号教研室编号) 班级班级(班级编号班级编号,班级名称班级名称,班长姓名班长姓名,专业专业,系编号系编号) 学生学生(学号学号,姓名姓名,性别性别,出生年月出生年月,籍贯籍贯,班级编

24、号班级编号) 课程课程(课程编号课程编号,课程名课程名,学时学时,学分学分) 选课选课(学号学号,课程编号课程编号,教师编号教师编号,成绩成绩) 学生信息管理系统学生信息管理系统( (逻辑逻辑模型模型) ) 2. 函数依赖和键的基本概念函数依赖和键的基本概念(略略) 函数依赖它反映属性或属性组之间相依存，互函数依赖它反映属性或属性组之间相依存，互 相制约的关系。相制约的关系。 简单地说，若想设计一个性能良好的数据库，简单地说，若想设计一个性能良好的数据库， 就要尽量满足关系规范化原则。就要尽量满足关系规范化原则。 1. 数据库设计中的问题数据库设计中的问题 如果一个关系没有经过规范化，可能会出

25、现数如果一个关系没有经过规范化，可能会出现数 据冗余、更新异常、插入异常、删除异常。据冗余、更新异常、插入异常、删除异常。 3. 范式及其关系范式及其关系(简介简介) 关系规范化理论简称范式，它提供了判别关系关系规范化理论简称范式，它提供了判别关系 模式设计的优劣标准，为数据库设计提供了严格的模式设计的优劣标准，为数据库设计提供了严格的 理论基础。理论基础。 使用范式表示关系模式满足规范化的等级，满使用范式表示关系模式满足规范化的等级，满 足最低要求的为第一范式，在第一范式的基础上满足最低要求的为第一范式，在第一范式的基础上满 足进一步要求的可升级为第二范式，其余以此类推。足进一步要求的可升级

26、为第二范式，其余以此类推。 范式范式(1NF5NF)，主要为，主要为1NF-3NF。也就是。也就是 说能满足前说能满足前3个范式的关系基本上是合理的。个范式的关系基本上是合理的。 (1) 1NF：若一个关系模式：若一个关系模式R的所有属性都是不可再分的所有属性都是不可再分 的基本数据项，则该关系模式属于的基本数据项，则该关系模式属于1NF。 例如：教师关系表如下，判断是否为第一范式，并规范例如：教师关系表如下，判断是否为第一范式，并规范 教师关系。教师关系。 教师编号教师编号姓名姓名系别系别 联系电话联系电话 联系电话联系电话1联系电话联系电话2 95010张乐张乐经济经济1234123456

27、78 95011赵希明赵希明经济经济234523456789 95012李小平李小平经济经济345634567890 教师关系教师关系 教师编号教师编号姓名姓名系别系别联系电话联系电话1联系电话联系电话2 95010张乐张乐经济经济123412345678 95011赵希明赵希明经济经济234523456789 95012李小平李小平经济经济345634567890 修改后的教师关系修改后的教师关系 不符合第一范式，因为不符合第一范式，因为“联系电话联系电话”不是基本数据项。不是基本数据项。 属于第一范式的关系应该满足的基本条件是每个元组属于第一范式的关系应该满足的基本条件是每个元组 的每个属

28、性中只能包含一个数据项，不能将两个以上数据的每个属性中只能包含一个数据项，不能将两个以上数据 项项“挤入挤入”到一个属性中。到一个属性中。 (2) 2NF：若关系模式：若关系模式R属于属于1NF，且每个非主属性都，且每个非主属性都 完全依赖于主关键字，则该关系模式属于完全依赖于主关键字，则该关系模式属于2NF。 例如：学生选课成绩关系表如下，判断是否为第二范例如：学生选课成绩关系表如下，判断是否为第二范 式，并规范学生选课关系。式，并规范学生选课关系。 学生编号学生编号姓名姓名课程编号课程编号课程名称课程名称周学时周学时学分学分成绩成绩 20020102刘力刘力101数据库数据库3377 20

29、020104刘红刘红102英语英语6667 学生选课成绩关系学生选课成绩关系 在这个关系中学生编号和课程编号共同组成主关键字，在这个关系中学生编号和课程编号共同组成主关键字， 其中成绩完全依赖于主关键字，而姓名却完全依赖于学号，其中成绩完全依赖于主关键字，而姓名却完全依赖于学号， 课程名称、周学时、学分又完全依赖于课程编号，因此，课程名称、周学时、学分又完全依赖于课程编号，因此， 此关系不符合第二范式。此关系不符合第二范式。 上述关系可能带来以下几个问题：上述关系可能带来以下几个问题： 数据冗余：假设数据冗余：假设100个学生选同一门课，重复个学生选同一门课，重复100次次 相同的学分。相同的

30、学分。 更新复杂：若调整某门课的学分，与其相关的记录更新复杂：若调整某门课的学分，与其相关的记录 都要修改。都要修改。 删除异常：若学生已毕业，由于学生编号不存在，删除异常：若学生已毕业，由于学生编号不存在， 选课记录也要删除。选课记录也要删除。 插入异常：或开一门新课，还没有学生选，由于没插入异常：或开一门新课，还没有学生选，由于没 有学生编号，课程编号、学分均无法输入。有学生编号，课程编号、学分均无法输入。 可以将上述关系分解为三个关系：可以将上述关系分解为三个关系： 学生学生(学生编号学生编号，姓名，年龄，入校日期，姓名，年龄，入校日期) 选课选课 (学生编号，课程编号学生编号，课程编号，成绩，成绩) 课程课程(课程编号课程编号，课程名称，周学时，学分，课程名称，周学时，学分) (3) 3NF：若关系模式：若关系模式R属于属于1NF，且每个非，且每个非 主属性都不传递依赖于主关键字，则关系模式属主属性都不传递依赖于主关键字，则关系模式属 于于3NF。(略略) 学生选课成绩的三个关系：学生选课成绩的三个关系： 学生学生(学生编号学生编号，姓名，年龄，入校日期，姓名，年龄，入校日期) 选课成绩选课成绩(学生编号，课程编号学生编号，课程编号，成绩，成绩) 课程课程(课程编号课程编号，课程名称，周学时，