数据库原理及应用

1. 数据库原理及应用 《数据库原理及应用》课程组 Jmun_jsjxy@163.com 荆楚理工学院

2. 第五章 数据库应用系统设计 荆楚理工学院

3. 【本章要点】 数据库设计是解决信息抽象描述、构造数据模型、实现数据存储的关键技术。 本章将重点介绍有关数据库设计方面的理论与方法。 荆楚理工学院

4. 5.1 数据库设计 5.1.1 数据库设计的重要性 数据库是MIS(管理信息系统)、DSS(决策支持系统)、OAS(办公自动化系统)的基础和重要组成部分。数据库设计是指对一个实际的应用环境，设计最优的数据模型，然后据此建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求。 数据库设计的优劣将直接影响信息系统的质量和运行效果。因此，设计一个结构优化的数据库是对数据进行有效管理的前提和产生正确信息的保证。 荆楚理工学院

5. 5.1 数据库设计 5.1.2 数据库设计方法和步骤 从本质上看，这些设计方法仍然属于手工设计的范畴，其基本思想是设计过程的迭代和逐步求精。 著名的New Orleans方法将数据库设计分为：需求分析、概念结构设计、逻辑结构设计、物理结构设计4个阶段。 根据数据库设计的任务，我们将数据库设计步骤分为如下页图所示的6个阶段。 荆楚理工学院

6. 需求分析阶段 概念结构设计 逻辑结构设计 物理结构设计 数据库实施阶段 数据库运行与维护 5.1 数据库设计 数据库设计的步骤： 荆楚理工学院

7. 5.1 数据库设计 (1) 需求分析阶段 　需求分析阶段的任务是通过详细的调查研究，充分了解用户的组织机构、业务规则、数据需求、完整性约束条件、事务处理和安全性要求等，为概念设计奠定基础。通常采用组织机构图、业务流程图等方法，详细描述用户应用环境的业务流程、数据需求。 　　需求分析阶段的后期将编写系统分析报告(也称需求规范说明书)，提交用户的决策部门讨论审查。文档中应包括：①系统的概况、目标、范围、背景、历史和现状。②系统的总体结构和子系统的结构说明。③系统功能划分和说明。④系统组织机构、组织之间的联系图、数据流程图、功能模块图和数据字典等图表。 荆楚理工学院

8. 5.1 数据库设计 (2) 概念结构设计 　　在需求分析阶段，数据库设计人员充分地调查和分析用户的应用需求，概念结构设计的目标是产生出一个能反映组织信息需求的概念模型。概念模型的特点是： 　简单明确表达用户业务环境数据需求、数据之间的联系、数据约束条件。 　易于交流和理解，便于设计人员和用户之间沟通和交流。 　易于向各种数据模型转换。 　　最常用的概念模型的表示方法是实体-联系模型，简称E-R模型。 荆楚理工学院

9. 5.1 数据库设计 (3) 逻辑结构设计 　　逻辑结构设计的任务就是把概念结构转换成所选择的DBMS支持的数据模型(关系、层次或网状数据模型之一)。目前，绝大多数是转换成关系数据模型。 荆楚理工学院

10. 5.1 数据库设计 (4) 物理结构设计 　　数据库最终是要存储在物理设备上的。数据库在计算机物理设备上的存储结构与存取方法称为数据库的物理结构。 荆楚理工学院

11. 5.1 数据库设计 通常，关系数据库的物理设计的内容主要包括以下5个部分： 　①从提高系统性能的角度，确定数据的存储安排。 　②将比较大的表分别放在两个磁盘上，可以加快存取速度。 　③将日志文件和数据库对象(表、索引等)分别放在不同的磁盘上，可以改进系统的性能。 　④索引结构设计。 　⑤系统配置的优化。 荆楚理工学院

12. 5.1 数据库设计 (5)数据库实施 确定了数据库的逻辑结构和物理结构后，就可以利用DBMS提供的数据定义语言建立数据库的结构。当数据库的结构建立之后，就可以向数据库中装载数据(也称为数据加载)。可以使用命令向数据库插入数据。由于数据库中的数据量非常大，为了提高效率，满足用户的要求，通常专门设计一个数据录入子系统(数据录入程序模块)完成数据输入任务。该子系统应该包括数据录入和录入过程的数据校验、代码转换、数据完整性约束，、安全性检查等。通常是用DBMS提供的FORMS生成器实现数据录入程序的设计。 荆楚理工学院

13. 5.1 数据库设计 (6) 数据库的运行与维护 　数据库设计与应用开发工作完成之后，系统便进入运行与维护阶段。运行与维护阶段的主要任务如下： 　①维护数据库的安全性和数据完整性。 　②监测并改善数据库性能。 　③增加新的功能和数据。要根据用户工作环境的扩大，适时地向数据库增加一些新的数据和新功能 　④及时修改错误。系统运行过程中难免要发生一些错误，这些错误大多数是由于应用程序设计的欠缺所致，有时也会是信息需求描述不清造成的后果。所以，需要及时修改运行错误，弥补需求设计的欠缺。 荆楚理工学院

14. 5.2 将E-R模型转换成关系数据库的方法 • E-R模型转换成关系数据库的一般规则： • ①将每一个实体转换成一个关系。 • ②将所有主码定义为非空(NOT NULL)。 • ③对于二元联系，按照下列规则定义外码： • 1:M联系将“一”表中的主码作为外码放在“多”表中。因此，外码总是在“多”的一方。 • 1:1联系将一个表的主码作为外码放在另一个表中。 • M:N多联系在两个关系之间建立联系表，联系表的主码由两个父实体的主码组成。联系表的主码也是外码，所以，它们必须是NOT NULL。

15. M 1 聘 系 教师 5.2 将E-R模型转换成关系数据库的方法 【例5.1】一个系聘用多位教师，—位教师只能属于一个系。 定义E-R模型： 转换成2个关系，分别确定表结构为： 系（系号，系名称） 教师（职工号，姓名，性别，职称，系号） FK放在教师表中，FK=<系号>，NOT NULL 说明：PK表示主码，FK表示外码。 荆楚理工学院

16. 系 系号 系名称 教师 职工号 姓名 性别 职称 系号 5.2 将E-R模型转换成关系数据库的方法 数据结构图： 荆楚理工学院

17. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义系表的结构： CREATE TABLE 系 (系号SMALLINT NOT NULL UNIQUE， 系名称CHAR(12) NOT NULL， PRIMARY KEY (系号))； 荆楚理工学院

18. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义教师表的结构： CREATE TABLE教师 (职工号SMALLINT NOT NULL UNIQUE， 　　姓名CHAR(8) NOT NULL， 　　性别CHAR(2)， 　　工资DECIMAL(6，2)， 　　系号SMALLINT Not null， PRIMARY KEY (职工号)， FOREIGN KEY (系号) REFERENCES系)； 荆楚理工学院

19. 1 1 乘坐 旅客 机票 5.2 将E-R模型转换成关系数据库的方法 【例5.2】航空公司出售的飞机票，旅客与机票是一一对应的，一位旅客一张机票。 定义E-R模型： 转换成2个关系： 机票(日期，航班号，座位号，价格) 旅客(日期，身份证号，姓名，航班号，座位号) FK放在旅客表中，FK=<日期，航班号，座位号>，NOT NULL。 荆楚理工学院

20. 机票 日期 航班号 座位号 价格 旅客 日期 身份证号 姓名 航班号 座位号 5.2 将E-R模型转换成关系数据库的方法 数据结构图： 荆楚理工学院

21. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义飞机票表的结构： CREATE TABLE 机票 (日期DATE NOT NULL， 　　　航班号CHAR(8) NOT NULL， 　　　座位号SMALLINT NOT NULL， 　　　价格DECIMAL(6，2)， PRIMARY KEY (日期，航班号，座位号))； 荆楚理工学院

22. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义旅客表的结构： CREATE TABLE 旅客 (日期DATE NOT NULL， 　　身份证号CHAR(18) NOT NULL， 　　姓名CHAR(10)NOT NULL， 　　航班号CHAR(8)NOT NULL， 　　座位号 SMALLINT NOT NULL， PRIMARY KEY (日期，身份证号)， FOREIGN KEY (日期) REFERENCES 机票， FOREIGN KEY (航班号) REFERENCES 机票， FOREIGN KEY (座位号) REFERENCES 机票)； 荆楚理工学院

23. N M 成绩 课程 学生 5.2 将E-R模型转换成关系数据库的方法 【例5.3】一名学生可以选修多门课程，一门课程可能有多人选修。 定义E-R模型： 转换成3个关系： 课程(课程号，课程名称，学时，性质) 学生(学号，姓名，性别，专业) 成绩(学号，课程号，分数) FK放在成绩表中，FK=<学号，课程号> 荆楚理工学院

24. 学生 课程 学号 姓名 性别 专业 课程号 课程名称 学时 性质 成绩 学号 课程号 分数 5.2 将E-R模型转换成关系数据库的方法 数据结构图 ： 荆楚理工学院

25. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义课程表的结构： CREATE TABLE课程 (课程号CHAR(4) NOT NULL， 　　课程名称CHAR(12) NOT NULL， 　　学时SMALLINT， 　　性质CHAR(4)， PRIMARY KEY (课程号))； 荆楚理工学院

26. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义学生表的结构： CREATE TABLE 学生 (学号CHAR(6) NOT NULL， 　　姓名CHAR(8) NOT NULL， 　　性别CHAR(2)， 　　专业CHAR(10)， PRIMARY KEY (学号))； 荆楚理工学院

27. 5.2 将E-R模型转换成关系数据库的方法 用SQL语言定义成绩表的结构： CREATE TABLE 成绩 (学号CHAR(6) NOT NULL， 　　课程号CHAR(4) NOT NULL， 　　分数DECIMAL(4，1)， PRIMARY KEY (学号，课程号)， FOREIGN KEY(学号) REFERENCES学生， FOREIGN KEY (课程号) REFERENCES课程)； 荆楚理工学院

28. 5.3 E-R模型设计实例 E-R模型是数据库设计的重要部分，也是本课程的重点内容。为了加深理解，提高设计能力，这里将给出JM大学的应用实例。 荆楚理工学院

29. 1 1 主管 院长 学院 5.3 E-R模型设计实例 5.3.1 JM大学数据库概念模型设计 ①该大学有多个学院：理学院、工学院、文学院、法学院，商学院、医学院等。每一个学院有一个院长，且每一位院长只能主管一个学院。因此，学院和院长实体之间是一对一联系。根据上述语义画出局部E-R图为： 荆楚理工学院

30. M 1 设置 学院 系 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ②每一个学院有多个系。例如，商学院有会计系、市场营销系、经济系、财务系等。每一个系仅属于一个学院。所以，学院与系是一对多联系。根据上述语义画出局部E-R图为： 荆楚理工学院

31. M M 1 1 开设 安排 系 课程 课表 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ③每一个系根据专业的特点和分工开设多门课程。将这些课程统一编码、列入教学培养计划。另外，在每一学期的教学执行计划(课表)中，教学培养计划中的一门课程可能要划分成几个部分，分布在几个学期内讲授。这里规定将列入教学培养计划中的课程简称为课程，将教学执行计划中所安排的课程简称为课表。所以，课程与课表之间是1:M的联系，局部E-R图为： 荆楚理工学院

32. 1 聘请 系 1 M 主任 教师 1 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ④每一个系有多名教师，但每一位教师只属于一个系；每一个系有一个系主任，系主任是一位教师。局部E-R图为： 荆楚理工学院

33. M 1 讲授 教师 课表 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ⑤一位教师可能讲多门课，且课表中每一门课必须有一位教师讲授。这里的课是指课表中安排的课程。局部E-R图为： 荆楚理工学院

34. N M 成绩 学生 课表 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ⑥假设规定每一名学生可选修多门课(指课表中的课)，且一门课有多名学生选。局部E-R图为： 荆楚理工学院

35. M 1 管理 系 学生 5.3 E-R模型设计实例 JM大学数据库概念模型设计（续） ⑦每一个系有多名学生，且每一名学生只能属于一个系。局部E-R图为： 荆楚理工学院

36. 实体 联系 实体 学院 设置 系 院长 主管 学院 系 聘请 教师 系 开设 课程 系 管理 学生 课程 安排 课表 教师 讲授 课表 教师 主任 系 学生 成绩 课表 5.3 E-R模型设计实例 归纳上述7项可以定义7个实体：学院、院长、系、教师、课程、课表、学生，其相互联系有： 荆楚理工学院

37. 5.3 E-R模型设计实例 5.3.2 逻辑结构设计 　　逻辑结构设计是将概念模型(E-R模型)转换成关系数据库。按照前面介绍的转换规则，将JM大学E-R模型转换成关系数据库。 　①学院(学院编号，学院名称，学院地址，院长编号) PK=学院编号NOT NULL FK=院长编号 荆楚理工学院

38. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ②院长(院长编号，姓名，电话号码，办公室)。 PK=院长编号NOT NULL 说明：虽然学院与院长是1:1联系，可以合并成一个实体，但是根据学院的业务需求，仍然保留两个实体(院长和学院)。将FK放在存取比较频繁的学院表中，这里FK=<院长编号>。 荆楚理工学院

39. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ③系(系代号，系名称，电话号码，系地址，学院编号，教工号)。 PK=系代号NOT NULL FK=学院编号参照学院表 FK=教工号参照教师表 说明：学院编号是系与所在学院之间的连接属性，教工号表示系主任的教工号。 荆楚理工学院

40. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ④教师(教工号，姓名，学历，职称，工资，电话号码，系代号)。 PK=教工号NOT NULL FK=系代号参照系表 说明：系代号是教师与所在系之间的连接属性。 荆楚理工学院

41. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ⑤课程(课程号，课程名称，学分，系代号)。 PK=课程号NOT NULL FK=系代号参照系表 说明：系代号是课程与开设这门课的系之间的连接属性。 荆楚理工学院

42. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ⑥课表(开课号，课程号，部分，教工号，学时数，时间，地点)。 PK=开课号NOT NULL FK=课程号参照课程表 FK=教工号参照教师表 说明：课程号体现教学培养计划所列的课程与每个学期具体执行的课表之间的联系；教工号是教工与课表之间的联系。 荆楚理工学院

43. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ⑦学生(学号，身份证号，姓名，性别，出生年月，专业，电话号码，系代号)。 PK=学号NOT NULL FK=系代号参照系表 说明：系代号是学生与所在系之间的连接属性。 荆楚理工学院

44. 5.3 E-R模型设计实例 JM大学数据库中的表的结构（续） ⑧成绩(学号，开课号，分数)。 PK=<学号，开课号> NOT NULL FK=开课号参照课表 FK=学号参照学生表 说明：开课号是成绩与课表之间的连接属性；学号是成绩与学生之间的连接属性。 荆楚理工学院

45. 5.3 E-R模型设计实例 5.3.3 数据库的实施 （1） 建立数据库 CREATE DATABASE University 说明：假设数据库名称是University 荆楚理工学院

46. 5.3 E-R模型设计实例 (2) 用SQL语言定义数据库的表结构 CREATE TABLE 学院 (学院编号SMALLINT NOT NULL UNIQUE， 学院名称VCHAR(20) NOT NULL， 学院地址VCHAR(36)， 院长编号SMALLINT NOT NULL， PRIMARY KEY (学院编号)， FOREIGN KEY (院长编号) REFERENCES 院长)； 注意：VCHAR是可变长字符型数据类型。 荆楚理工学院

47. 5.3 E-R模型设计实例 (2) 用SQL语言定义数据库的表结构（续） CREATETABLE 院长 (院长编号SMALLINT NOT NULL UNIQUE， 院长姓名CHAR(8) NOT NULL， 电话号码CHAR(12)， PRIMARY KEY (院长编号))； 荆楚理工学院

48. 5.3 E-R模型设计实例 (2) 用SQL语言定义数据库的表结构（续） CREATE TABLE 系 (系代号SMALLINT NOT NULL UNIQUE， 系名称VCHAR(20) NOT NULL， 系地址VCHAR(20)， 电话号码CHAR(12)， 学院编号SMALLINT NOT NULL， 教工号CHAR(4) NOT NULL，&&系主任的教工号 PRIMARY KEY (系代号)， POREIGN KEY (学院编号) REFERENCES 学院， FOREIGN KEY (教工号) REFERENCES 教师)； 荆楚理工学院

49. 5.3 E-R模型设计实例 (2) 用SQL语言定义数据库的表结构（续） CREATE TABLE 教师 (教工号SMALLINT NOT NULL UNIQUE， 姓名CHAR(8) Not null， 性别CHAR(2)， 学历CHAR(6)， 电话号码 CHAR(12)， 工资Decimal(5)， 系代号SMALLINT， PRIMARY KEY (教工号)， FOREIGN KEY (系代号) REFERENCES 系)； 荆楚理工学院

50. 5.3 E-R模型设计实例 (2) 用SQL语言定义数据库的表结构（续） CREATE TABLE 课程 (课程号CHAR(6) NOT NULL UNIQUE， 课程名CHAR(20) NOT NULI， 学分SMALLINT， 系代号SMALLINT， PRIMARY KEY (课程号)， FOREIGN KEY (系代号) REFERENCES 系)； 荆楚理工学院