Microsoft PowerPoint - Chap05-数据库技术.ppt

Transcription

1 Chap05 数据库技术 信息科学与技术学院 程勇 2009 年秋

2 课程提纲 Chap00 课前导学 Chap01 算法 Chap02 基本数据结构及其运算 Chap03 查找与排序技术 Chap04 资源管理技术 Chap05 数据库技术 Chap06 应用软件设计与开发技术 Chap07 课程复习

3 本章提纲 5.1 数据库系统概述 5.2 关系数据库 5.3 结构化查询语言 SQL 5.4 数据库设计 5.5 课后作业 5.6 本章小结

4 数据库系统概述 基本概念数据管理技术的产生与发展数据模型数据库系统结构数据库系统组成

5 基本概念 基本概念 数据数据 (Data) 是描述事物的符号记录 数据具有多种描述形式, 数字 文字 图形 图象 声音等, 它们都可以经过数字化后存入计算机 数据处理 o 将数据转换成信息的过程, 包括对数据的收集 存储 加工 检索 传输等一系列活动 其目的是从大量的原始数据中抽取和推导出有价值的信息, 作为决策的依据 o 数据处理的中心问题是数据管理 数据管理数据管理是指对数据的组织 分类 编码 存储 检索和维护

6 基本概念 ( 续 ) 数据库 数据库 (DB,Database) 是长期储存在计算机内的 有组织的 可共享的数据集合 数据库中的数据按一定的数据模型组织 描述和储存, 具有较小的冗余度 较高的数据独立性和易扩展性, 并可为各种用户共享 - 萨师煊, 王珊. 数据库系统概论,3 rd 数据库系统 数据库系统 (Database System) 指在计算机系统中引入数据库后的系统, 一般由数据库 数据库管理系统 ( 及其开发工具 ) 应用系统 数据库管理员和用户组成 - 萨师煊, 王珊. 数据库系统概论,3 rd

7 人工管理阶段 随着计算机硬件和软件的发展, 数据管理经历了人工管理 文件系统和数据库系统三个发展阶段 人工管理阶段 背景 o 计算机发展初期 (50 年代中期以前 ), 计算机主要用于科学计算 o 硬件中的外存只有卡片 纸带 磁带, 没有磁盘等直接存取设备 o 软件只有汇编语言, 没有操作系统和管理数据的软件 o 数据基本上采用批处理方式进行处理 主要特点 o 数据不保存 当时的计算机主要用于科学计算, 对于数据保存的需求尚不迫切

8 人工管理阶段 ( 续 ) o 系统没有专用的软件对数据进行管理 每个应用程序都要包括数据的存储结构 存取方法 输入方式等, 程序员编写应用程序时, 还要安排数据的物理存储, 因此负担很重 o 数据不共享 数据是面向程序的, 一组数据只能对应一个程序 多个应用程序涉及某些相同的数据时, 也必须各自定义, 因此程序之间有大量的冗余数据 o 数据不具有独立性 程序依赖于数据, 如果数据的类型 格式 或输入输出方式等逻辑结构或物理结构发生变化时, 必须对应用程序做出相应的修改 示意图 应用程序 1 数据 1 : : 应用程序 n 数据 n

9 文件系统阶段 文件系统阶段 背景 o 50 年代后期至 60 年代中期, 计算机不仅用于科学计算, 而且还大量用于信息管理 o 大量的数据存储 检索和维护成为紧迫的需求 o 硬件有了磁盘 磁鼓等直接存储设备 o 在软件方面, 出现了高级语言和操作系统 o 操作系统中有了专门管理数据的软件, 称为文件系统 o 处理方式有批处理, 也有联机处理 主要特点 o 数据以文件形式可长期保存下来 用户可随时对文件进行查询 修改和增删等处理

10 文件系统阶段 ( 续 ) o 文件系统可对数据的存取进行管理 程序员可按名存取文件, 而不负责其物理存储, 因此大大减轻了负担 o 文件形式多样化 有顺序文件 倒排文件 索引文件等, 可对文件进行顺序访问或随机访问 o 程序与数据间有一定独立性 由操作系统的文件系统负责数据管理, 数据存储发生变化不一定要求程序改变 示意图 应用程序 1 文件 1 应用程序 2 文件 2 文件系统 : : 应用程序 n 文件 n

11 文件系统阶段 ( 续 ) 与人工管理阶段相比, 文件系统阶段对数据的管理有了很大的进步, 但一些根本性的问题仍然没有得到彻底解决, 主要表现在以下三个方面 : 数据冗余度大 各数据文件之间没有有机的联系, 一个文件基本上对应于一个应用程序, 数据不能共享 数据独立性低 数据和程序相互依赖, 一旦改变数据的逻辑结构, 必须修改相应的应用程序 而应用程序发生变化, 如改用另一种程序设计语言来编写程序, 也需修改数据结构 数据一致性差 由于相同数据的重复存储 各自管理, 在进行更新操作时, 容易造成数据的不一致性

12 数据库系统阶段 数据库系统阶段 背景 o 60 年代后期, 计算机应用于管理的规模更加庞大, 数据量急剧增加 o 硬件方面出现了大容量磁盘, 使计算机联机存取大量数据成为可能 o 硬件价格下降, 而软件价格上升, 使开发和维护系统软件的成本增加 o 文件系统的数据管理方法已无法适应开发应用系统的的需要 o 为解决多用户 多个应用程序共享数据的需求, 出现了统一管理数据的专门软件系统, 即数据库管理系统

13 数据库系统阶段 ( 续 ) 特点 o 数据共享性高 冗余少数据不再面向某个应用程序而是面向整个系统 因此不仅可以节约存储空间 减少数据冗余, 而且还避免数据间的不相容性与不一致性 o 数据结构化数据库系统不仅描述数据本身, 还描述数据之间的联系 o 数据独立性高数据的独立性包括逻辑独立性和物理独立性两个方面 逻辑独立性是指当数据的总体逻辑结构改变时, 数据的局部逻辑结构不变, 由于应用程序是依据数据的局部逻辑结构编写的, 所以应用程序不必须修改, 从而保证了数据与程序间的逻辑独立性 物理独立性是指当数据的存储结构改变时, 数据的逻辑结构不变, 从而应用程序也无需改变

14 数据库系统阶段 ( 续 ) o 统一的数据控制功能数据库为多个应用程序所共享, 对数据的存取往往是并发的 为确保数据库数据的正确有效和数据库系统的有效运行, 数据库管理系统提供下述四方面的数据控制功能 : 数据的安全性 (Security) 控制防止不合法使用数据造成数据的泄露和破坏, 保证数据的安全和机密 ; 数据的完整性 (Integrity) 控制系统通过设置一些完整性规则以确保数据的正确性 有效性和相容性 并发 (Concurrency) 控制多用户同时存取或修改数据库时, 防止相互干扰而提供给用户不正确的数据, 并使数据库受到破坏 数据恢复 (Recovery) 当数据库被破坏或数据不可靠时, 系统有能力将数据库从错误状态恢复到最近某一时刻的正确状态

15 数据库系统阶段 ( 续 ) 示意图 应用程序 1 数据库 应用程序 2 应用程序 3 : 数据库管理系统 (DBMS) 应用程序 n

16 数据模型 数据模型数据模型是现实世界特征的模拟和抽象 抽象过程

17 概念模型和数据模型 概念模型概念模型, 也称信息模型, 它是按用户的观点来对数据和信息建模, 主要用于数据库设计 数据模型数据模型是按计算机系统的观点对数据建模, 主要用于数据库管理系统 DBMS 的实现 数据模型是数据库系统的核心和基础, 主要的数据模型有层次模型 网状模型 关系模型和面向对象模型 基本要求 o 能比较真实地模拟现实世界 o 容易为人所理解 ( 从人的角度 ) o 便于在计算机上实现 ( 从机器的角度 )

18 信息世界 基本概念 实体 (Entity) 客观存在的可区别的事物 实体可以是具体的人 事 物, 也可以是抽象的概念和联系 属性 (Attribute) 实体所具有的某一特性称为属性 一个实体可由若干属性来刻画 例如学生实体可由学号 姓名 性别 年龄 系 专业等属性组成 码 (Key) 唯一标识实体的属性集

19 信息世界 ( 续 ) 域 (Domain) 属性的取值范围 实体型 (Entity Type) 实体名及其属性名集合来抽象和刻画同类实体, 称为实体型 实体集 (Domain) 同型实体的集合称为实体集 联系 (Relationship) 实体内部的联系通常指组成实体的各属性之间的联系 实体之间的联系通常指不同实体集之间的联系

20 信息世界 ( 续 ) 两个实体型之间的联系可以分三类 : o 一对一 对于实体集 A 中的每一个实体, 实体集 B 中至多有一个 ( 或没有 ) 实体与之联系, 反之亦然 记做 1:1, 如图 (a) 所示 o 一对多 对于实体集 A 中的每一个实体, 实体集 B 中有 n 个实体 (n>=0) 与之联系 反之, 对于实体集 B 中的每一个实体, 实体集 A 中至多有一个实体与之联系 记做 1:n, 如图 (b) 所示 o 多对多 对于实体集 A 中的每一个实体, 实体集 B 中有 n 个实体 (n>=0) 与之联系 反之, 对于实体集 B 中的每一个实体, 实体集 A 中也只有 m(m>=0) 个实体与之联系 记做 m:n, 如图 (c) 所示 A B A B A B (a) (b) (c)

21 概念模型表示方法 概念建模是对信息世界建模, 因此要求概念模型能方便准确地表示信息世界中的常用概念 1976 年 P.P.S.Chen 提出的实体 - 联系方法 (Entity- Relationship Approach) 是最常用的概念模型表示方法, 该方法用 E-R 图来描述现实世界的概念模型 E-R 图提供了表示实体型, 属性和联系的方法 : 实体型用矩形表示, 矩形框内写明实体名 属性用椭圆形表示, 并用无向边将其与相应实体连接起来 实体型用菱形表示, 菱形框内写明联系名, 并用无向边分别与有关实体连接起来, 同时在边旁边写上联系的类型

22 E-R 模型示例 实体型 A 1 联系名 1 实体型 B 实体型 A 1 联系名 n 实体型 B 实体型 A m 联系名 n 实体型 B 两个实体型的三种联系 学生 学号姓名性别年龄系专业 学生实体及属性

23 数据模型组成要素 数据模型通常由数据结构, 数据操作和完整性约束三部分组成 数据结构 数据结构是所研究的对象类型的集合 数据结构是刻画一个数据模型性质最重要的方面 人们通常按照其数据结构来命名数据模型, 如层次结构 网状结构和关系结构的数据模型分别命名为层次模型 网状模型和关系模型 数据操作 数据操作是指对数据库中各种对象 ( 型 ) 的实例 ( 值 ) 允许执行的操作集合, 包括操作及有关操作规则 数据操作必须定义这些操作的确切含义 操作符号 操作规则以及实现操作的语言 数据操作是对系统动态特性的描述 约束条件 数据的约束条件是一组完整型规则的集合 完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则, 用以限定符号数据模型的数据库状态及其状态的变化, 确保数据的正确 有效和相容

24 层次模型 层次模型 层次模型是最早出现的数据模型 层次数据库系统采用层次模型作为数据的组织方式 IBM 公司 1968 年推出的 IMS(Information Management System) 是第一个大型的商用数据库管理系统, 曾经得到广泛应用 满足以下两个条件的基本层次联系的集合称为层次模型 : o 有且仅有一个结点没有双亲结点, 这个称为根结点 o 根结点以外的其它结点有且仅有一个双亲结点 层次模型的一个基本特点是任何给定的记录值只有按其路径查看时, 才能显示出它的全部意义, 没有一个子女记录值能够脱离双亲记录值而独立存在

25 层次模型 ( 续 ) 教师学生层次模型示例 记录型 D( 系 ) 是根结点, 由字段 D#( 系编号 ) DN( 系名 ) DL( 办公地点 ) 组成, 它有两个孩子结点 R 和 S 记录型 R( 教研室 ) 是 D 的孩子结点, 同时又是 T 的双亲结点, 它由 R#( 教研室编号 ) RN( 教研室名称 ) 两个字段组成 记录型 S( 学生 ) 由 S#( 学号 ) SN( 姓名 ) SS( 成绩 ) 三个字段组成 记录型 T( 教师 ) 由 T#( 教师编号 ) TN( 姓名 ) TD( 研究方向 ) 三个字段组成 S 与 T 是叶结点, 它们没有子女结点, 由 D 到 R,R 到 T, 由 D 到 S 均是一对多的关系 系 D# DN DL 教研室 R# RN 学生 S# SN SS 教师 T# TN TD

26 层次模型 ( 续 ) 多对多联系在层次模型中的表示 用层次模型表示多对多联系, 必须首先将其分解为一对多联系 学生 S 学号 姓名 m 成绩 课程 C S-C n 课程号课程名 主要分解方法有两种 : 冗余结点法 增设冗余结点将多对多联系转换为两个一对多联系, 如图 (a) 所示 虚拟结点法 将冗余结点换为虚拟结点, 虚拟结点是一个指针, 指向所替代的结点, 如图 (b) 所示

27 层次模型 ( 续 ) 学生 S 学号 姓名 成绩 课程 C 课程号 课程名 课程 C 课程号 课程名 学生 S 学号 姓名 成绩 (a) S 学号 姓名 成绩 C 课程号 课程名 C V.C S V.S (b) 两种方法比较 冗余结点法结构清晰, 允许结点改变存储位置 缺点是需要额外的存储空间, 有可能导致不一致性 虚拟结点法减少了存储空间的浪费, 避免产生潜在的不一致性, 缺点是结点改变可能引起虚拟结点中指针的修改

28 层次模型小结 层次模型主要优点 结构清晰, 结点间联系简单 对于实体间联系是固定的, 且预先定义好的应用系统, 采用层次模型来实现, 其性能优于关系模型, 不低于网状模型 层次模型提供了良好的数据完整性支持 层次模型主要缺点 层次模型难以表示现实世界中非层次性联系, 如多对多联系, 一个结点具有多个双亲等 一般通过引入冗余数据 ( 易产生不一致性 ) 或创建非自然数据组织 ( 虚拟结点 ) 来解决 对插入和删除操作的限制比较多 查询子女结点必须通过双亲结点 由于结构严密, 层次命令趋于程序化

29 网状模型 网状模型 网状模型的典型代表是 DBTG 系统, 又称为 CODASYL 系统 该系统是 20 世纪 70 年代数据系统语言研究会 CODASYL (Conference On Data System Language) 下属的数据库任务组 (Data Base Task Group) 提出的一个系统方案 DBTG 系统并不是实际的软件系统, 但它提出的基本概念 方法和技术具有普遍意义 后来不少系统, 如 o IDMS(Cullinet Software 公司 ) o DMS1100(Univac 公司 ) o IDS/2(Honeywell 公司 ) o IMAGE(HP 公司 ) 等采用 DBTG 模型或是简化的 DBTG 模型 网状模型取消了层次模型的两个限制, 两个或两个以上的结点都可以有多个双亲结点, 则此时有向树变成了有向图, 该有向图描述了网状模型

30 网状模型 ( 续 ) 满足以下两个条件的基本层次联系集合称为网状模型 允许一个以上的结点无双亲 一个结点可以有多于一个的双亲 网状模型示例 R 1 R 1 R 2 R 2 R 1 L 1 L 2 L 1 L 2 R 3 L 1 L 2 L 3 R 3 R 2 R 4 L5 R 5 L 4 (a) (b) (c)

31 网状模型小结 网状模型主要优点 能更为直接地描述客观世界, 可表示实体间的多种复杂联系, 如一个结点可以有多个双亲 具有良好的性能, 较高的存储效率 网状模型的缺点 结构复杂, 而且随着应用环境的扩大, 数据库的结构将变得越来越复杂, 用户难以掌握 其 DDL DML 语言较复杂, 用户难以使用 数据独立性差, 由于实体间的联系本质上是通过存取路径表示的, 因此应用程序在访问数据时要指定存取路径, 因此加重了编写应用程序的负担

32 关系模型 关系模型 1970 年, 美国 IBM 公司研究员 E.F.Codd 发表了题为 大型共享数据银行数据的关系模型 (A Relational Model of Data for Large Shared Data Banks) 的文章 文中解释了关系模型, 定义了某些关系代数运算, 研究了数据的函数相关性, 定义了关系的第三范式, 从而提出了数据库系统的关系模型理论 1977 年,IBM 公司研制的关系数据库系统 System R 开始运行, 其后又进行了不断的改进和扩充, 出现了基于 System R 的数据库系统 SQL/DB 目前, 关系数据库已成为目前应用最广泛的数据库系统 如现在广泛使用的小型数据库系统 Foxpro Acess, 大型数据库系统 Oracle DB2 MySQL Informix Sybase SQL Server 等都是关系数据库系统

33 关系模型小结 关系模型主要优点 结构比较简单 在关系模型中, 实体的描述以及实体之间的联系都采用关系来表示 很高的数据独立性 关系模型中, 用户无需涉及数据的存储特性 可以直接处理多对多联系 由于使用表格来描述实体之间的联系, 因此可以直接描述多对多的实体联系 具有坚实的理论基础 关系模型以严格的数学理论为基础, 避免了层次模型和网状模型系统中存在的一些问题

34 数据库系统结构 可以从不同角度考察数据库系统的结构 从数据库管理系统角度数据库管理系统内部系统结构 ( 三级模式结构 ) o 外模式 o 模式 o 内模式 从数据库最终用户角度数据库系统外部体系结构 o 集中式结构 o 分布式结构 o 客户 / 服务器结构 o 并行结构

35 数据库系统结构 ( 续 ) 应用 A 应用 B 应用 C 应用 D 应用 E 子模式 1 子模式 2 子模式 / 模式映象模式模式 / 内模式映象内模式 子模式 3 模式是数据库的中心与关键 数据库 数据库系统的三级模式结构

36 模式 模式模式 (Schema), 也称作逻辑模式, 是数据库中全体数据的逻辑结构和特征的描述, 是所有用户的公共数据视图 模式实际上是数据库数据在逻辑级上的视图, 一个数据库只有一个模式 模式处于三级结构的中间层, 既不涉及数据的物理存储细节, 也与具体的应用程序, 开发工具和高级语言无关 定义模式不仅要定义数据的逻辑结构, 而且要定义数据之间的联系 模式一般由数据库管理系统 (DBMS) 提供模式描述语言 ( 模式 DDL) 来定义

37 外模式 外模式外模式, 也称子模式或用户模式, 它是数据库用户 ( 包括应用程序和最终用户 ) 能够看见和使用的局部数据的逻辑结构和特征的描述, 是数据库用户的数据视图, 是与某一应用有关的数据的逻辑表示 从逻辑关系上看, 外模式通常是模式的子集, 一个数据库可以有多个外模式 如果不同用户在应用需求 看待数据的方式和数据保密要求等方面存在差异, 则其外模式描述是不同的 一个应用程序只能使用一个外模式 外模式是保证数据库安全性的有力措施, 用户只能看见和访问对应外模式中的数据 子模式一般由 DBMS 提供的子模式描述语言来定义

38 内模式 内模式内模式, 又称存储模式或内视图, 它是数据物理结构和存储方式的描述, 是数据在数据库内部的表示方式 一个数据库只有一个内模式 内模式是三级结构中的最内层, 也是靠近物理存储的一层, 但它并非物理层, 并不必关心具体的存储位置 内模式主要描述记录的存储方式 ( 顺序存储 B 树结构存储 Hash 方式存储 ) 索引方式 数据是否压缩和加密等 DBMS 提供内模式描述语言 ( 内模式 DDL) 来严格定义内模式

39 数据库的两级映象 数据库系统的三级模式是对数据的三个抽象级别, 它使用户能逻辑地抽象地处理数据, 而不必关心数据在计算机内部的存储方式, 把数据的具体组织交给 DBMS 管理 为了能够在内部实现这三个抽象层次的联系和转换, DBMS 在三级模式之间提供了二级映象功能 : 外模式 / 模式映象模式变 / 映象变 / 外模式不变 / 软件不变 数据逻辑独立性 模式 / 内模式映象 内模式变 / 映象变 / 模式不变 / 软件不变 数据物理独立性 两层映象保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性

40 外模式 / 模式映象 外模式 / 模式映象 模式描述数据的全局逻辑结构, 外模式描述数据的局部逻辑结构 数据库中的同一模式可以有任意多个外模式, 对于每一个外模式, 都存在一个外模式 / 模式映象 外模式 / 模式映象定义了外模式和模式之间的对应关系 当模式改变时 ( 如增加新的关系 新的属性 改变属性的数据类型等 ), 由数据库管理员对各个外模式 / 模式的映象作相应的改变, 可以使外模式保持不变 应用程序是依据数据的外模式编写的, 因此应用程序不必修改, 保证了数据与程序的逻辑独立性, 简称数据的逻辑独立性

41 模式 / 内模式映象 模式 / 内模式映象 数据库中只有一个模式, 也只有一个内模式, 因此模式 / 内模式映象是唯一的 数据库内模式依赖于数据库全局逻辑结构, 但独立于数据库的用户视图 ( 即外模式 ), 也独立于具体的存储设备 模式 / 内模式映象定义了数据库全局逻辑结构和存储结构之间的对应关系, 如说明逻辑记录和字段在内部是如何表示的 当数据库的存储结构改变了, 由数据库管理员对模式 / 内模式的映象作相应的改变, 可以使模式保持不变 从而应用程序也不必修改, 保证了数据与程序的物理独立性, 简称数据的物理独立性

42 三级模式结构小结 三级模式结构的优点 保证数据的独立性 o 将外模式和模式分开, 保证了数据的逻辑独立性 o 将模式和内模式分开, 保证了数据的物理独立性 简化了用户接口 o 按照外模式编写应用程序或敲入命令, 而不需了解数据库内部的存储结构, 方便用户使用系统 有利于数据共享 o 在不同的外模式下可有多个用户共享系统中数据, 减少了数据冗余 利于数据的安全保密 o 在外模式下根据要求进行操作, 不能对限定的数据操作, 保证了其他数据的安全

43 数据库系统组成 数据库系统一般由数据库 数据库管理系统 应用系统 数据库管理员和用户组成 硬件平台及数据库数据库系统对硬件资源的基本要求 : o 足够大的内存空间 o 足够大的磁盘等直接存储设备存放数据库 o 要求系统有较高的通信能力, 以提高数据传送率 软件操作系统 /DBMS/ 以 DBMS 为核心的应用开发工具 / 具有与数据库接口的高级语言及其编译系统 / 数据库应用系统 相关人员 o 数据库管理员 (DBA) o 系统分析和数据库设计人员 o 用户

44 数据库系统组成 ( 续 ) 用户 A 用户 B 用户 C 应用系统 应用开发工具 数据库管理系统 数据库管理员 操作系统 数据库 数据库系统示意图

45 数据库管理系统 DBMS 数据库管理系统数据库管理系统 (DBMS, DataBase Management System) 是对数据进行管理的大型系统软件, 它是数据库系统的核心组成部分 用户在数据库系统中的一切操作, 包括数据定义 查询 更新及各种控制, 都是通过 DBMS 进行的 DBMS 就是实现把用户意义下的抽象的逻辑数据处理转换成计算机中的具体的物理数据的处理软件, 这给用户带来很大的方便 数据库管理系统主要功能 数据定义 DBMS 提供数据定义语言 DDL(Data Define Language), 用户可以定义数据的模式 外模式和内模式三级模式结构, 定义模式 / 内模式和外模式 / 模式二级映象, 定义有关的约束条件

46 数据库管理系统 DBMS( 续 ) 数据操纵 DBMS 提供数据操纵语言 DML(Data Manipulation Language) 实现对数据库的基本操作, 包括检索 插入 修改 删除等 数据库运行管理数据库在建立 运行和维护时由 DBMS 统一管理 同一控制 以确保数据的安全性 完整性 多用户对数据的并发使用及发生故展后的系统恢复 数据库的建立和维护功能包括数据库初始数据的装入, 数据库转储 恢复 重组织, 系统性能监视 分析等功能 数据通信 DBMS 提供与其他软件系统进行通信的功能 实现用户程序与 DBMS 之间的通信, 通常与操作系统协调完成

47 DBMS 存取数据过程 DBMS 数据存取过程 在数据库系统中,DBMS 与操作系统 应用程序 硬件等协同工作, 共同完成数据各种存取操作, 其中 DBMS 起着关键的作用 DBMS 存取数据通常需要执行以下步骤 : o 用户使用某种特定的数据操作语言向 DBMS 发出存取请求 o DBMS 接受请求并解释 o DBMS 依次检查外模式 外模式 / 模式映象 模式 模式 / 内模式映象及存储结构定义 o DBMS 对存储数据库执行必要的存取操作 上述存取过程中还包括安全性控制 完整性控制, 以确保数据的正确性 有效性和一致性

48 数据库相关人员职责 数据库管理员 (DBA) 在数据库系统环境下, 有两类共享资源, 一类是数据库, 另一类是数据库管理系统软件 它们需要专门的管理机构来监督和管理 DBA 则是这个机构中的一个 ( 组 ) 人员, 负责全面管理和控制数据库系统 具体职责如下 : 决定数据库中信息内容和结构 决定数据库的存储结构和存取策略 定义数据的安全性要求和完整性约束条件 监控数据库的使用和运行 数据库的改进和重组策略

49 数据库相关人员职责 ( 续 ) 系统分析和数据库设计人员 系统分析员负责应用系统的需求分析和规范说明, 确定应用系统的软硬件配置, 参与数据库系统的概要设计 数据库设计人员负责数据库中数据的确定, 数据库各级模式的设计 很多情况下, 数据库设计人员一般由数据库管理员担任 应用程序员 负责设计和编写应用系统的程序模块, 进行调试和安装 最终用户 通过应用系统的用户接口使用数据库 偶然用户 / 简单用户 / 复杂用户

50 数据库相关人员数据视图 用户 应用程序员 { { 数据表示 外模式 应用系统 DBA 系统分析员 模式 内模式 DBMS DB 操作系统 数据库相关人员数据视图

51 关系数据库 关系模型 关系代数

52 关系模型概述 关系数据库系统支持关系模型的数据库系统 关系模型关系模型由关系数据结构 关系操作集合和关系完整性约束三部分组成 关系模型中数据的逻辑结构是一张二维表 关系操作包括查询 插入 删除和修改等 其特点是集合操作方式, 即操作对象和操作结果都是集合 这种方法也称为一次一集合 (set-at-a-time) 方式, 而非关系数据模型数据操作则为一次一记录 (recored-at-a-time) 方式 关系模型定义了三类完整性约束 : 完体完整性 参照完整性和用户定义的完整性 其中前两者是关系模型必须满足的完整性约束条件, 应该由关系系统自动支持

53 关系数据结构 关系模型的数据结构 学号 姓名 性别 年龄 专业 年级 张亚勤 男 39 CS 张靓颖 女 21 MU 刘 翔 男 22 PE 2003 关系数据结构特点 关系模型中数据的逻辑结构是一张二维表 关系数据结构非常单一, 现实世界中的实体及实体之间的联系均用关系来表示

54 关系数据结构 ( 续 ) 域 (Domain) 一组具有相同数据类型的值的集合 如整数 实数 长度小于 25 的字符串集合等, 都可以是域 笛卡尔积 (Cartesian Product) 给定一组域 D 1,D 2,,D n, 这些域可以是相同的 D 1,D 2,,D n 的笛卡尔积为 D 1 D 2 D n ={(d 1,d 2,,d n ) d i D i,i=1,2,,n} 其中, 每一个元素 (d 1,d 2,,d n ) 称作一个 n 元组 (n-tuple) 或简称元组 (Tuple) d i 叫做元组的第 i 个分量 (Component) 若 D i (i=1,2,,n) 为有限集, 其基数 (Cardinal Number) 为 m i (i=1,2,,n), 则 D 1,D 2,,D n 笛卡尔积的基数 M 为 M n = i = 1 m i

55 关系数据结构 ( 续 ) 关系 (Relation) D 1 D 2 D n 的子集称作在域 D 1,D 2,,D n 上的关系, 表示为 R(D 1,D 2,,D n ) R 表示关系的名字 n 是关系的目或度 (Degree) 当 n=1 时, 称该关系为单元关系 (Unary Relation), 当 n=2 时, 该关系称为二元关系 (Binary Relation) 关系是笛卡尔积的有限子集, 所以关系也是一个二维表, 表的每行对应一个元组, 每列对应一个域 由于域可以相同, 为了区别起见, 可以给每列起一个名字, 叫做属性 (Attribute) 关系可以有三种类型 : 基本关系 查询表和视图表 基本表是实际存在的表, 它是实际存储数据的逻辑表示 查询表是查询结果对应的表 视图表是由基本表导出的表, 是虚表, 并不对应实际存储的数据

56 关系数据结构 ( 续 ) 基本关系的重要性质 o 列是同质的, 即每一列中的分量来自同一个域 o 不同的列可以出自同一个域, 称其中的一列为一个属性 o 列的顺序无关紧要, 可以任意交换 o 任意两个元组不能完全相同 o 行的顺序无关紧要, 可以任意交换 o 分量必须取自原子值, 即每一分量都要求是不可分的数据项 候选码 (Candidate Key) 如果关系中某一属性组的值可以唯一地标识一个元组, 则称该属性组为候选码 主码 (Primary Key) 若一个关系有多个候选码, 则选定其中一个称之为主码 主码的各属性称为主属性 (Prime Attribute) 不包含在任何候选码中的属性称作为非码属性 (Non-key Attribute)

57 关系数据结构 ( 续 ) 关系的型与值 关系的型 指关系的结构 ( 字段名 字段个数 域等 ) 关系的值 指关系中具体的元组, 也称关系的实例 (Instance) 关系模式关系的描述成为关系模式 它可以形式化表示为 R(U,D,dom,F) R 为关系名 U 为组成该关系的属性名的集合 D 为 U 中属性所来自的域 dom 为属性到域的映象集合 F 为属性间依赖关系的集合 关系是关系模式在某一时刻的状态或内容 关系模式是静态的 稳定的, 而关系是动态的, 是随时间不断变化的

58 关系操作 关系模型中常用的关系操作 查询操作 ( 最主要部分 ) o 并 (Union) o 交 (Intersection) o 差 (Difference) o 选择 (Select) o 投影 (Project) o 连接 (Join) o 除 (Divide) 插入 (Insert) 删除 (Delete) 修改 (Update)

59 关系操作 ( 续 ) 关系操作特点 集合操作方式, 即操作对象和结果均为集合 关系操作能力用代数方式和逻辑方式来表示, 分别称为关系代数和关系演算 前者用对关系的运算来表达查询要求, 后者用谓词来表达查询要求 关系演算按照谓词变元的基本对象是元组变量还是域变量分为元组关系演算和域关系演算 值得注意的是关系代数 元组关系演算和域关系演算三者在表达能力上是完全等价的 关系语言是一种高度非过程化的语言 分类如下 : 关系数据语言 { 关系代数语言 (ISBL) 元组关系演算语言 (APLHA,QUEL) 关系演算语言 { 域关系演算语言 (QBE) 具有关系代数和关系演算双重特点的语言 (SQL)

60 关系完整性约束 关系模型的完整性约束是对关系的某种约束条件 关系模型种可以有三类完整性约束 : 实体完整性 参照完整性 用户定义完整性, 其中前两种是关系模型必须满足的约束条件 实体完整性 (Entity Integrity) 若属性 A 是基本关系 R 的主属性, 则属性 A 不能取空值 实体完整性约束的几点说明 实体完整性规则是针对基本关系而言的 一个基本表通常对应现实世界的一个实体集 现实世界中的实体和实体间的联系都是可区分的, 即它们具有某种唯一性标识 关系模型中以主码作为唯一性标识 空值就是 不知道 或 无意义 的值 主属性取空值, 就说明存在某个不可标识的实体, 这与第 (2) 矛盾

61 关系完整性约束 ( 续 ) 基本概念设 F 是基本关系 R 的一个或一组属性但不是关系 R 的码 如果 F 与基本关系 S 的主码 K S 相对应, 则称 F 是基本关系 R 的外码 (Foreign Key) 基本关系 R 称为参照关系, 基本关系 S 称为被参照关系或目标关系 例如 : S(Sno,Sname,Sex,Age,Dept) 被参照关系 C(Cno,Cname,Credit) 被参照关系 SC(Sno,Cno,Grade) 参照关系 几点说明 关系 R 和 S 不一定是不同的关系 目标关系 S 的主码 K S 和参照关系的外码 F 必须定义在同一个 ( 或一组 ) 域上 外码并不一定要与相应的主码同名, 当外码与相应的主码属于不同关系时, 往往取相同的名字, 以便于识别

62 关系完整性约束 ( 续 ) MO 40 女巩俐 PE 22 男刘翔 MU 21 女张靓颖 PE 32 女邓亚萍 CS 39 男张亚勤 专业年龄性别姓名学号 3 数据库 C4 4 操作系统 C3 4 数据结构 C2 4 算法分析 C1 学分课程名课程号 79 C C C C C C 成绩课程号学号 S SC C

63 关系完整性约束 ( 续 ) 参照完整性 (Referential Integrity) 参照完整性定义了外码和主码之间的引用规则 若属性 ( 或属性组 )F 是基本关系 R 的外码, 它与基本关系 S 的主码 K S 相对应 ( 基本关系 R 和 S 不一定是不同的关系 ), 则对于 R 中每个元组在 F 上的值必须为 : 或者取空值 (F 的每个属性值均为空值 ) 或者等于 S 中某个元组的主码值 用户定义完整性 (User-defined Integrity) 用户定义的完整性是针对某一具体关系数据库的约束条件, 反映某一具体应用所涉及的数据必须满足的语义要求 如性别只能取 男 女 值, 成绩必须在 0~100 分之间等 关系模型应提供定义和检验这类完整性的机制, 以便用统一的系统的方法处理它们, 而不要由应用程序承担这一功能 通常由 DBMS 的 Check 约束提供这类检查

64 关系代数 关系代数 (Relational Algebra) 以集合为基础, 定义一组运算, 由已知关系经过一系列运算, 得到需要的查询结果 过程化的查询语言 关系代数运算符

65 传统集合运算 传统集合运算 关系代数继承传统的集合运算 : 如并 ( ) 交 ( ) 差 (-) 上述运算要求参与运算的两个关系要有相同的结构 将关系看成元组的集合, 从关系的 水平 方向进行运算 并 (Union) 交 (Intersection) 差 (Difference) 有下式成立 : R S={t t R t S} R S={t t R t S} R S={t t R t S} R S=R (R S)

66 传统集合示意图 R A B C R S A B C a 1 b 1 c 1 a 1 b 1 c 1 a 1 b 2 c 2 a 1 b 2 c 2 a 2 b 2 c 1 a 2 b 2 c 1 a 1 b 3 c 2 S A B C R S A B C a 1 b 2 a 1 b 3 c 2 a 2 c 2 a 1 b 2 c 1 c 2 b 2 c 1 a 2 b 2 R S A B C a 1 b 1 c 1 传统集合运算示意图

67 广义笛卡尔积 广义笛卡尔积 (Extended Cartesian Product) 设 R 和 S 分别是 m 目和 n 目关系, 则 R 和 S 的广义笛卡儿积是一个 (m+n) 列的元组的集合 元组的前 m 列是关系 R 的一个元组, 后 n 列是关系 S 的一个元组 若 R 有 R 个元组,S 有 S 个元组, 则关系 R 和关系 S 的广义笛卡儿积有 S * R 个元组, 并记作 : 关系的广义笛卡尔积可用于两关系的连接操作

68 广义笛卡尔积示意图 R S R.A R.B R.C S.A S.B S.C a 1 b 1 c 1 a 1 b 2 c 2 a 1 b 1 c 1 a 1 b 3 c 2 a 1 b 1 a 1 c 1 b 2 c 1 c 2 a 2 a 1 b 2 b 2 c 2 a 1 b 2 c 2 a 1 b 3 c 2 a 1 b 2 a 2 c 1 b 2 c 2 c 1 a 2 a 1 b 2 b 2 c 2 a 2 b 2 c 1 a 1 b 3 c 2 a 2 b 2 c 1 a 2 b 2 c 1

69 选择运算 选择 (Selection) 选择运算从关系 R 中选取满足给定条件 ( 使逻辑表达式 F 为真 ) 的元组 它是从行的角度进行的运算, 记作 : σ F (R) = { t t R F( t) = 'true'} F 表示选择条件, 它是一个逻辑表达式, 取逻辑值 true 或 false,, 逻辑表达式 F 由逻辑运算符连接各算术表达式组成 算术表达式基本形式为 X 1θ X, θ 2 表示比较运算符, X 1, X 2等是属性名, 或是常量, 或是简单函数

70 选择运算示意图 ' ' (S) Sex σ = 女 S PE 32 女邓亚萍 MO 40 女巩俐 MU 21 女张靓颖 Dept Age Sex Sname Sno FS MO 40 女巩俐 PE 32 女邓亚萍 PE 22 男刘翔 MU 21 女张靓颖 CS 39 男张亚勤 Dept Age Sex Sname Sno

71 投影运算 投影 (Projection) 投影运算从关系 R 中选取若干属性列组成新的关系 记作 : π A(R) = { ta [ ] t R} A 为关系 R 中的属性列 t[a] 表示元组 t 中相应于属性 A 的分量 投影操作是从列的角度进行的运算 投影之后不仅取消了原关系中的某些列, 而且还可能取消某些元组 因为取消某些列属性后, 就可能出现重复行, 应该取消这些完全相同的行

72 投影运算示意图 S Sno Sname Sex Age Dept 张亚勤 男 39 CS 张靓颖 女 21 MU 刘 翔 男 22 PE 巩 俐 女 40 MO SNN Sno π Sno, Sname (S) Sname 张亚勤 张靓颖 刘 巩 翔 俐

73 连接运算 连接 (Join) θ 连接也称作连接 它是从两个关系的笛卡儿积中选取属性间满足一定条件的元组, 记作 : 上述运算从 R 和 S 的广义笛卡尔乘积中选取 (R 关系 ) 在 A 属性组上的值与 (S 关系 ) 在 B 属性组上的值满足比较关系 θ 的元组 A 和 B 分别是关系 R 和 S 上度数相等且可比较的属性组 θ 是比较运算符 连接运算中两种最为重要且常用的连接是等值连接 (Equijoin) 和自然连接 (Natural Join)

74 连接运算 ( 续 ) 等值连接 (Equijoin) θ 比较运算符为 = 时的连接运算称为等值连接 它是从关系 R 与 S 的广义笛卡尔乘积中选取 A,B 属性值相等的那些元组, 可表示为 : 自然连接 (Natural Join) 自然连接是一种特殊的等值连接, 它要求两个关系比较的分量必须是相同的属性组, 并在结果中把重复的属性列去掉, 可表示为 :

75 连接运算示意图 R A B C A B C E A R.B C S.B E a 1 b 1 5 a 1 b a 1 b 1 5 b 1 3 a 1 b 2 6 a 1 b a 1 b 2 6 b 2 7 a 2 b 3 8 a 2 b a 2 b 3 8 b 3 10 a 2 b 4 12 a 2 b a 2 b 3 8 b 3 2 S B E A R.B C S.B E b a 1 b 1 5 b 2 7 b 2 10 a 1 b 1 5 b 3 10 b 3 2 b 2 6 b 2 7 b 3 2 a 1 b 2 6 b 3 a 1 10 b 5 a 2 b 3 8 b 3 10

76 结构化查询语言 SQL SQL 概述数据定义 定义 / 修改 / 删除基本表 建立 / 删除索引数据查询 单表 / 连接 / 嵌套 / 集合数据更新 插入 / 修改视图 定义 / 查询 / 更新数据控制 授权 / 收回权限嵌入式 SQL

77 SQL 概述 SQL 语言简史 1974 年,Boyce 和 Chamberlin 将 SQUARE 语言修改为 SEQUEL(Structured English QUEry Language) 语言, 采用英文和结构式的语法规则 后来 SEQUEL 简称为 SQL (Structured Query Language, 结构化查询语言 ) 1975~1979 年,IBM 公司 San Jose 研究实验室研制了著名的关系数据库系统原型 System R 并实现了 SQL 语言 1986 年,ANSI 批准 SQL 作为关系数据库语言的美国标准 1987 年 ISO 采纳此标准, 统称为 SQL-86 并于 1989 进行扩充, 增强完整性约束, 称为 SQL 年,ANSI 公布新标准 SQL-92, 增加了许多新特性, 如新的数据类型, 更丰富的数据操作, 更强的完整性 安全性支持, 又称为 SQL 年,ANSI 公布 SQL-99 标准, 增加了对面向对象的支持, 又称为 SQL3

78 SQL 语言特点 SQL 是一个通用的 功能极强的关系数据库语言 并成为国际标准 SQL 语言集数据查询 数据操纵 数据定义和数据控制功能于一身, 主要特点如下 : 综合统一 SQL 集 DDL DML DCL 功能于一体, 语言风格统一, 可以独立完成数据库生命周期中的全部活动 高度非过程化 用户只需要提出 做什么, 不关心 怎么做, 因此大大减轻了用户负担 面向集合的操作方式 查询 插入 删除 更新操作对象及结果都是集合 以同一种语法结构提供两种使用方法 既可交互式使用, 也可嵌入式使用

79 SQL 语言特点 ( 续 ) 语言简捷, 易学易用 SQL 定义了少量的关键字实现对数据库的定义 操纵和控制功能 并且语法接近英语口语, 容易学习和使用 SQL 功能数据查询数据定义数据操纵数据控制 动词 SELECT CREATE,DROP,ALTER INSERT,UPDATE,DELETE GRANT,REVOKE SQL 语言的动词

80 SQL 与三级模式体系结构 SQL 视图 1 视图 2 外模式 基本表 1 基本表 2 基本表 3 基本表 4 模式 存储文件 1 存储文件 2 内模式 SQL 对关系数据库模式的支持

81 示例学生 - 课程数据库 Student Course SC

82 数据定义 数据定义 SQL 的数据定义功能包括定义基本表 视图和索引 由于视图是基于基本表的虚表, 而索引要依附于基本表, 因此 SQL 通常不提供修改视图和索引定义的操作 如果用户想修改视图定和索引定义, 只能先删除, 再重建 SQL 数据定义语句 操作对象 创建 删除 修改 基本表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX

83 创建基本表 定义基本表是数据库创建的重要一步,SQL 语言使用 CREATE TABLE 语句来定义基本表, 一般格式如下 : CREATE TABLE < 表名 > (< 列名 > < 数据类型 > [< 列级完整性约束条件 > ] [,< 列名 > < 数据类型 > [< 列级完整性约束条件 >] ] [,< 表级完整性约束条件 >]); < 表名 > 为所要定义的基本表的名字 < 列名 > 是组成该表的各个属性 ( 列 ) < 列级完整性约束条件 > 涉及相应属性列的完整性约束条件 < 表级完整性约束条件 > 涉及一个或多个属性 ( 列 ) 的完整性约束条件

84 创建基本表 ( 续 ) 不同的关系数据库系统支持的数据类型是不同的, 如 IBM DB2 主要支持以下数据类型 SMALLINT INTEGER 或 INT DECIMAL (p[,q]) FLOAT CHARTER(n) 或 CHAR(n) VARCHAR(n) GRAPHIC(N) VARGRAPHIC(n) DATE TIME TIMESTAMP 半字长二进制整数全字长二进制整数压缩十进制数双字长浮点数长度为 n 的定长字符串最大长度为 n 的变长字符串长度为 n 的定长图形字符串最大长度为 n 的变长图形字符串日期型, 格式为 yyyy-mm-dd 时间型, 格式为 hh.mm.ss 日期加时间

85 创建基本表 ( 续 ) 完整性约束条件 主码约束 PRIMARY KEY; 唯一性约束 UNIQUE( 不能取相同值但允许多个空值 ); 非空值约束 NOT NULL; 参照完整性约束 FOREIGN KEY < 列名 > REFERENCES < 表名 >(< 列名 >); 上述完整性约束条件将存入系统的数据字典中, 当用户操作表中数据时由 DBMS 自动检查该操作是否违背这些条件

86 创建基本表 ( 续 ) 例 5.1 建立一个学生表 Student, 它由学号 Sno 姓名 Sname 性别 Sex 年龄 Age 所在系 Dept 五个属性构成 其中学号不能为空, 值是唯一的, 并且姓名取值也唯一 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Sex CHAR(1), Age INT, Dept CHAR(15), PRIMARY KEY Sno);

87 修改基本表 SQL 语言使用 ALTER TABLE 语句来修改基本表, 其一般格式如下 : ALTER TABLE < 表名 > [ADD < 新列名 > < 数据类型 > [< 完整性约束 > ]] [DROP < 完整性约束名 >] [MODIFY < 列名 > < 数据类型 >]; < 表名 > 是要修改的基本表 ADD 子句增加新列和新的完整性约束条件 DROP 子句删除指定的完整性约束条件 MODIFY 子句用于修改列名和数据类型 SQL 没有提供删除属性列的语句, 用户只能间接实现这一功能, 即先把要保留的列和内容进行复制, 在删除原表并重新命名

88 修改基本表 ( 续 ) 例 5.2 向 Student 表增加 入学时间 列, 其数据类型为日期型 ALTER TABLE Student ADD Senrolldate DATE; 不论基本表中原来是否已有数据, 新增加的列一律为空值 例 5.3 将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Age SMALLINT; 修改原有的列定义有可能会破坏已有数据 例 5.4 删除学生姓名必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sname);

89 删除基本表 删除基本表当某个基本表不再需要时, 可以使用 DROP TABLE 语句加以删除 该操作删除表中的数据 表上建立的索引和视图等 因此删除操作要倍加小心 有的系统 ( 如 Oracle) 等在执行删除操作后仍保留表上的视图, 但无法引用 删除操作语句基本格式如下 : DROP TABLE < 表名 >; 例 5.5 删除 Student 表 DROP TABLE Student;

90 创建索引索引 是数据库中广泛采用的加快查询速度的有效手段 索引表的 Search-key 项常用顺序或 Hash 等方法进行组织 MO 40 女巩俐 Dept Age Sex Sname Sno PE 32 女邓亚萍 PE 22 男刘翔 MU 21 女张靓颖 CS 39 男张亚勤 Student 索引概念 Pointer Sno Idx_Sno Idx_Age Pointer Age

91 创建索引 ( 续 ) 创建索引在 SQL 语言中,CREATE INDEX 语句用来建立索引, 一般格式如下 : CREATE [UNIQUE] [CLUSTER] INDEX < 索引名 > ON < 表名 > (< 列名 >[< 次序 >][,< 列名 >[< 次序 >]] ); < 表名 > 指定要建索引的基本表名字 索引可以建立在该表的一列或多列上, 各列名之间用逗号分隔 用 < 次序 > 指定索引值的排列次序 ASC 表示升序,DESC 表示降序, 缺省值为 ASC UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER 表示要建立的索引是聚簇索引

92 创建索引 ( 续 ) 例 5.6 为学生课程数据库中的 Student,Course,SC 三个表建立索引 其中 Student 表按学号升序建唯一索引,Course 表按课程号升序建唯一索引,SC 表按学号升序和课程号降序建唯一索引 CREATE UNIQUE INDEX Idx_Sno ON Student(Sno); CREATE UNIQUE INDEX Idx_Cno ON Course(Cno); CREATE UNIQUE INDEX Idx_SC ON SC(Sno ASC, Cno DESC); 例 5.7 在 Student 表的 Sname( 姓名 ) 列上建立一个聚簇索引, 而且 Student 表中的记录将按照 Sname 值的升序存放 CREATE CLUSTER INDEX Idx_Sname ON Student(Sname);

93 删除索引 删除索引如果数据增删频繁, 系统需花较多时间维护索引 这时需要删除一些不必要的索引 在 SQL 语言中, 使用 DROP INDEX 来删除索引 DROP INDEX < 索引名 >; 删除索引时, 系统会从数据字典中删去有关该索引的描述 SQL 标准中没有定义对索引的修改功能, 而采用删除后重新定义索引的方式实现 例 5.8 删除 Student 表的 Idx_Sno 索引 DROP INDEX Idx_Sno;

94 数据查询 数据查询数据查询是 SQL 核心操作 查询语句一般格式为 : SELECT [ALL DISTINCT] < 目标列表达式 > [,< 目标列表达式 >] FROM < 表名或视图名 >[, < 表名或视图名 >] [WHERE < 条件表达式 >] [GROUP BY < 列名 1> [HAVING < 条件表达式 >]] [ORDER BY < 列名 2> [ASC DESC]]; SELECT 子句, 指定要显示的属性列 FROM 子句, 指定查询对象 ( 基本表或视图 ) WHERE 子句, 指定查询条件 GROUP BY 子句, 对查询结果按指定列的值分组, 该属性列值相等的元组为一个组 通常会在每组中作用集函数 HAVING 短语, 筛选出只有满足指定条件的组 ORDER BY 子句, 对查询结果按指定列值的升序或降序排序

95 单表查询 选择表中的若干列例 5.9 查询全体学生的学号与姓名 SELECT Sno,Sname FROM Student; // 等价于例 5.10 查询全体学生的姓名 学号 所在系 SELECT Sname,Sno,Dept FROM Student; 例 5.11 查询全体学生的详细记录 SELECT Sno,Sname,Sex,Age,Dept FROM Student; 或 SELECT * // 表示将表中的列全部按序输出 FROM Student; π Sno, Sname ( Student)

96 单表查询 ( 续 ) 目标列 (SELECT 子句 ) 为表达式的查询 目标列形式 : 算术表达式 字符串常量 函数 列别名等 例 5.12 查全体学生的姓名及其出生年份 SELECT Sname,2005-Age FROM Student;

97 单表查询 ( 续 ) 例 5.13 查询全体学生的姓名 出生年份和所在系, 要求用小写字母表示所在系名 SELECT Sname,'Year Of Birth',2005-Age,LOWER(Dept) FROM Student;

98 单表查询 ( 续 ) 使用列别名改变查询结果的列标题例 5.14 查询全体学生的姓名 出生年份和所在系, 并将输出字段依次更名为 :NAME BIRTH BIRTHDAY DEPARTMENT SELECT Sname NAME, 'Year of Birth' BIRTH, 2005-Age BIRTHDAY, LOWER(Dept) DEPARTMENT FROM Student;

99 单表查询 ( 续 ) 消除结果中取值重复的行在 SELECT 子句中使用 DISTINCT 短语 例 5.15 查询选修了课程的学生学号 方法 (1) SELECT Sno FROM SC; 方法 (2) SELECT DISTINCT Sno FROM SC 查询 (1) 结果 查询 (2) 结果 DISTINCT 短语的作用范围是所有目标列

100 单表查询 ( 续 ) 查询满足条件的元组 使查询满足条件的元组可以通过 WHERE 子句来实现 WHERE 子句常用的查询条件如下表所示 查询条件比较确定范围确定集合字符匹配空值多重条件 谓词 =,<,>,<=,>=,<>,!=,!>,!<; NOT+ 上述比较运算符 BETWEEN AND, NOT BETWEEN AND IN,NOT IN LIKE,NOT LIKE IS NULL,IS NOT NULL AND,OR

101 单表查询 ( 续 ) 使用比较运算符确定元组例 5.16 查询计算机系全体学生的信息 SELECT * FROM Student WHERE Dept= CS ; 例 5.17 等价于 查询所有年龄在 20 岁以下的学生姓名及其年龄 SELECT Sname,Age FROM Student WHERE Age<20; σ ( ) Dept ='CS' Student

102 单表查询 ( 续 ) 确定范围例 5.18 查询年龄在 20~35 岁 ( 包括 20 岁和 35 岁 ) 之间的学生的姓名 系别和年龄 SELECT Sname,Dept,Age FROM Student WHERE Age BETWEEN 20 AND 35; 例 5.19 查询年龄不在 20~35 岁之间的学生姓名 系别和年龄 SELECT Sname,Dept,Age FROM Student WHERE Age NOT BETWEEN 20 AND 35;

103 单表查询 ( 续 ) 确定集合例 5.20 查询信息系 (CS) 体育系 (PE) 和电影系 (MO) 的学生姓名和性别 SELECT Sname,Sex FROM Student WHERE Dept IN( CS', PE', MO'); 例 5.21 查询不是信息系 (CS) 体育系(PE) 和电影系 (MO) 的学生姓名和性别 SELECT Sname,Sex FROM Student WHERE Dept NOT IN( CS', PE', MO');

104 单表查询 ( 续 ) 字符串匹配 [NOT] LIKE < 匹配串 > [ESCAPE < 换码字符 > ] o < 匹配串 >, 指定匹配模板 o 匹配模板, 固定字符串或含通配符的字符串 当匹配模板为固定字符串时, 可用 = 运算符取代 LIKE 谓词, 用!= 或 <> 运算符取代 NOT LIKE 谓词 %( 百分号 ) 代表任意长度 ( 长度可以为 0) 的字符串 例如,a%b 表示以 a 开头, 以 b 结尾的任意长度的字符串, 如 acb,addgb,ab 等都满足该匹配串 _( 下横线 ) 代表任意单个字符 例如,a_b 表示以 a 开头以 b 结尾的长度为 3 的任意字符串, 如 acb,afb 等都满足该匹配串 当用户要查询的字符串本身就含有 % 或 _ 时, 要使用 ESCAPE '< 换码字符 >' 短语对通配符进行转义

105 单表查询 ( 续 ) 匹配模板为固定字符串例 5.22 查询学号为 的学生的详细情况 方法 (1) SELECT * FROM Student WHERE Sno LIKE ; 方法 (2) SELECT * FROM Student WHERE Sno= ; 上述两种查询方法是等价的

106 单表查询 ( 续 ) 匹配模板为含通配符的字符串例 5.23 查询所有姓张学生的姓名 学号和性别 SELECT Sname,Sno,Sex FROM Student WHERE Sname LIKE ' 张 %'; 例 5.24 例 5.23 查询结果查询姓 巩 且全名为两个汉字的学生的姓名 SELECT Sname FROM Student WHERE Sname LIKE ' 巩 _'; 例 5.25 查询名字中第 2 个字为 亚 字的学生的姓名和学号 SELECT Sname,Sno FROM Student WHERE Sname LIKE '_ 亚 %';

107 单表查询 ( 续 ) 例 5.26 查询所有不姓张的学生姓名 SELECT Sname FROM Student WHERE Sname NOT LIKE 张 % ; 用转义符将通配符转义为普通字符例 5.27 查询 Data_Structure 课程的课程号和学分 SELECT Cno,Credit FROM Course WHERE Cname LIKE Data\_Structure ESCAPE '\'; 例 5.28 查询以 Data_ 开头, 且倒数第 3 个字符为 u 的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE Data\_%u ESCAPE '\';

108 单表查询 ( 续 ) 涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL IS NULL 不能用 =NULL 代替 例 5.29 某些学生选修课程后没有参加考试, 所以有选课记录, 但没有考试成绩 查询缺少成绩的学生的学号和相应的课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL; 例 5.30 查询所有有成绩的学生学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

109 单表查询 ( 续 ) 多重条件查询用逻辑运算符 AND 和 OR 来联结多个查询条件 AND 的优先级高于 OR 可以用括号改变优先级 可用来连接多种其他谓词条件, 如 : AθB [NOT] BETWEEN AND [NOT] IN LIKE,NOT LIKE IS NULL,IS NOT NULL 可用作其他部分谓词条件的等价替换 [NOT] BETWEEN AND [NOT] IN

110 单表查询 ( 续 ) 例 5.31 查询计算机系年龄在 20 岁以下的学生姓名 SELECT Sname FROM Student WHERE Dept= CS AND Age<20; 例 5.32 查询年龄在 20~35 岁 ( 包括 20 岁和 35 岁 ) 之间的学生的姓名 系别和年龄 SELECT Sname,Dept,Age FROM Student WHERE Age BETWEEN 20 AND 35;

111 单表查询 ( 续 ) 对查询结果排序输出 用户可以使用 ORDER BY 子句对查询结果按照一个或多个属性列的升序 (ASC) 或降序 (DESC) 排列, 缺省值为升序 例 5.33 查询选修了 C5 号课程的学生的学号及其成绩, 查询结果按分数降序排列 SELECT Sno,Grade FROM SC WHERE Cno='C5' ORDER BY Grade DESC; 对于空值, 若按照升序排列 (ASC), 含空值的元组将最后显示 若按按降序排列 (DESC), 含空值的元组将最先显示

112 单表查询 ( 续 ) 集函数 (Aggregate Functions) 为了增强检索功能,SQL 提供了 5 类集函数, 主要有 : 计数 o COUNT([DISTINCT ALL] *) o COUNT([DISTINCT ALL] < 列名 >) 计算总和 o SUM([DISTINCT ALL] < 列名 >) 计算平均值 o AVG([DISTINCT ALL] < 列名 >) 求最大值 o MAX([DISTINCT ALL] < 列名 >) 求最小值 o MIN([DISTINCT ALL] < 列名 >)

113 单表查询 ( 续 ) 例 5.34 查询学生总人数 SELECT COUNT(*) FROM Student; 例 5.35 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 用 DISTINCT 以避免重复计算学生人数例 5.36 查询选修 C1 号课程的学生最高分数 SELECT MAX(Grade) FROM SC WHERE Cno= C1 ;

114 单表查询 ( 续 ) 对查询结果分组 GROUP BY 子句将查询结果表按照某一列或多列值分组, 值相等的为一组 其目的是为了细化集函数的作用对象 如果未对查询结果分组, 集函数将作用于整个查询结果 对查询结果分组后, 集函数将分别作用于每个组 GROUP BY 子句的作用对象是查询的中间结果表 使用 GROUP BY 子句后,SELECT 子句的列名列表中只能出现分组属性和集函数 如果分组后还要求按一定条件对这些组进行筛选, 可以使用 HAVING 短语指定筛选条件

115 单表查询 ( 续 ) 例 5.37 查询选修了 3 门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3; 例 5.38 查询有一门以上课程是 90 分以上的学生的学号及 (90 分以上的 ) 课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade>=90 GROUP BY Sno HAVING COUNT(*)>=1; 例 5.38 查询结果

116 连接查询 连接查询 将两个 ( 以上 ) 表连接进行查询 连接查询的意义等价于关系代数中的 θ 连接 等值连接和自然连接 WHERE 子句中参与比较的两个属性名若在另一表中存在, 则要加所属表名作前辍以区别, 无同名则可省前辍 连接查询是关系数据库中最主要的查询, 包括 : o 等值连接 o 自然连接 o 非等值连接 o 自身连接查询 o 外连接查询 o 复合条件连接查询

117 连接查询 ( 续 ) 例 5.39 查询每个学生及其选修课程的情况 SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno; // 等值连接 ;

118 连接查询 ( 续 ) 例 5.40 查询每个学生及其选修课程的情况 SELECT Student.Sno,Sname,Sex,Age,Dept,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno; // 自然连接

119 连接查询 ( 续 ) 自身连接 一个表也可以与自身进行连接, 称为表的自身连接 需要给表起别名以示区别 由于所有属性名都是同名属性, 因此必须使用别名前缀 例 5.41 查询每一门课的间接先修课 ( 即先修课的先修课 ) SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno;

120 连接查询 ( 续 ) 复合条件连接 WHERE 子句中含多个连接条件, 称为符合条件连接 例 5.42 查询选修 C2 号课程且成绩在 90 分以上的所有学生的学号 姓名 SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='C2' AND SC.Grade>90; // 连接谓词 // 其他限定条件 // 其他限定条件 例 5.43 查询每个学生的学号 姓名 选修的课程名及成绩 SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

121 嵌套查询 嵌套查询将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询, 一个 SELECT-FROM-WHERE 语句称为一个查询块 SELECT Sname // 外层查询 / 父查询 FROM Student WHERE Sno IN (SELECT Sno // 内层查询 / 子查询 FROM SC WHERE Cno='C2'); 子查询的结果用于建立其父查询的查找条件, 且子查询的 SELECT 语句不能使用 ORDER BY 子句 层层嵌套方式反映了 SQL 语言的结构化 有些嵌套查询可以用连接运算替代

122 嵌套查询 ( 续 ) 带有 IN 谓词的子查询例 5.44 查询与 邓亚萍 在同一个系学习的学生 方法 (1), 分步完成 : 1 确定 邓亚萍 所在系名 SELECT Dept FROM Student WHERE Sname= 邓亚萍 '; 2 查找所有在 PE 系学习的学生 SELECT Sno,Sname,Dept FROM Student WHERE Dept='PE'; 第 (1) 步查询结果 第 (2) 步查询结果

123 嵌套查询 ( 续 ) 方法 (2), 用自身连接完成本查询要求 SELECT S1.Sno,S1.Sname,S1.Dept FROM Student S1,Student S2 WHERE S1.Dept=S2.Dept AND S2.Sname=' 邓亚萍 '; 方法 (3), 将第一步查询嵌入到第二步查询的条件中 o 先执行子查询, 得到结果集 {PE}, 再执行父查询 WHERE Dept IN {PE} o 如果子查询的执行不依赖于父查询的条件, 称为不相关子查询 其效率要优于连接查询 SELECT Sno,Sname,Dept FROM Student WHERE Dept IN (SELECT Dept FROM Student WHERE Sname= 邓亚萍 );

124 嵌套查询 ( 续 ) 带有比较运算符的子查询 当能确切知道内层查询返回单值时, 可用比较运算符 (>,<,=,>=,<=,!= 或 <>) 例 5.45 查询与 邓亚萍 在同一个系学习的学生 假设一个学生只可能在一个系学习, 并且必须属于一个系, 则可以用 = 代替 IN, 如下所示 SELECT Sno,Sname,Dept FROM Student WHERE Dept IN (SELECT Dept FROM Student WHERE Sname=' 邓亚萍 '); IN 可以用 = 来代替 子查询一定要跟在比较符之后

125 嵌套查询 ( 续 ) 带有 ANY 或 ALL 谓词的子查询谓词语义 :(1)ANY(SOME): 某些值,(2)ALL: 所有值 需要配 合使用比较运算符有 : >ANY, 大于子查询结果中的某个值 >ALL, 大于子查询结果中的所有值 <ANY, 小于子查询结果中的某个值 <ALL, 小于子查询结果中的所有值 >=ANY, 大于等于子查询结果中的某个值 >=ALL, 大于等于子查询结果中的所有值 <=ANY, 小于等于子查询结果中的某个值 <=ALL, 小于等于子查询结果中的所有值 =ANY, 等于子查询结果中的某个值 =ALL, 等于子查询结果中的所有值 ( 通常没有实际意义 )!=( 或 <>)ANY, 不等于子查询结果中的某个值!=( 或 <>)ALL, 不等于子查询结果中的任何一个值

126 嵌套查询 ( 续 ) 例 5.46 查询其他系中比信息系某些学生年龄小的学生姓名和年龄 方法 (1), 用 ANY 谓词 SELECT Sname,Age FROM Student WHERE Age<ANY(SELECT Age FROM Student WHERE Dept='CS') AND Dept<> CS ; 方法 (2), 使用集函数 SELECT Sname,Age FROM Student WHERE Age<(SELECT MAX(Age) FROM Student WHERE Dept='CS') AND Dept<>'CS';

127 嵌套查询 ( 续 ) 例 5.47 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄 方法 (1), 用 ALL 谓词 SELECT Sname,Age FROM Student WHERE Age<ALL(SELECT Age FROM Student WHERE Dept='CS') AND Dept<>'CS'; 方法 (2), 使用集函数 SELECT Sname,Age FROM Student WHERE Age<(SELECT MIN(Age) FROM Student WHERE Dept='CS') AND Dept<>'CS';

128 嵌套查询 ( 续 ) 带有 EXISTS 谓词的子查询 EXISTS 谓词的意义 : EXISTS 代表存在量词 带有 EXISTS 谓词的子查询不返回任何数据, 只产生逻辑真值 true 或逻辑假值 false o 若内层查询结果非空, 则返回真值 o 若内层查询结果为空, 则返回假值 由 EXISTS 引出的子查询, 其目标列表达式通常都用 *, 因为带 EXISTS 的子查询只返回真值或假值, 给出列名无实际意义

129 嵌套查询 ( 续 ) 例 5.48 查询所有选修了 C1 号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC 例 5.48 查询结果 WHERE Sno=Student.Sno AND Cno='C1'); 上述查询执行过程 : 首先取外层查询中表的第一个元组, 根据它与内层查询相关的属性值处理内层查询, 若 WHERE 子句返回值为真, 则取此元组放入结果表 然后再取外层表的下一个元组, 重复这一过程, 直至外层表全部检查完为止 这种查询称为相关子查询, 即子查询条件与父查询当前值相关 此例也可用连接运算加以实现

130 嵌套查询 ( 续 ) 例 5.49 查询没有选修 C1 号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='C1'); 此例为相关子查询, 但不能用连接运算实现

131 嵌套查询 ( 续 ) 使用嵌套查询时需注意 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换 所有带 IN 谓词 比较运算符 ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换 例 5.50 查询与 邓亚萍 在同一个系学习的学生 SELECT Sno,Sname,Dept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Dept = S1.Dept AND S2.Sname=' 邓亚萍 ');

132 数据更新 插入单个元组语句格式 : INSERT INTO < 表名 > [(< 属性列 1>[,< 属性列 2 > )] VALUES(< 常量 1>[,< 常量 2>] ); INTO 子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列 : 表示要插入的是一条完整的元组, 且属性列属性与表定义中的顺序一致 指定部分属性列 : 插入的元组在其余属性列上取空值 VALUES 子句 提供的值的个数和值的类型必须与 INTO 子句匹配

133 数据更新 ( 续 ) 例 5.51 将一个新学生记录 ( 学号 :030019; 姓名 : 大长今 ; 性别 : 女 ; 年龄 :34 岁 ; 所在系 :ME 插入到 Student 表中 INSERT INTO Student VALUES ('030019',' 大长今 ', ' 女 ', '34', 'ME');

134 数据更新 ( 续 ) 插入子查询结果语句格式 : INSERT INTO < 表名 > [(< 属性列 1> [,< 属性列 2> )] 子查询 ; INTO 子句 ( 与插入单元组类似 ) 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 如果没有指定属性列, 表示要插入的是一条完整的元组 指定部分属性列, 插入的元组在其余属性列上取空值 子查询 SELECT 子句目标列属性的个数和类型必须与 INTO 子句匹配

135 数据更新 ( 续 ) DBMS 在执行插入语句时会检查欲插入的元组是否会破坏表上已定义的完整性规则 如果破坏, 系统会提示, 且该记录不会被插入到数据库中 实体完整性对于插入的记录主码不为空且不可以与表中已有记录主码相同 参照完整性外码必须取空值或被参照关系中对应属性的值 用户定义的完整性 对于有 NOT NULL 约束的属性列是否提供了非空值 对于有 UNIQUE 约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内

136 数据更新 ( 续 ) 修改数据修改指定表中满足 WHERE 子句条件的元组 语句格式 UPDATE < 表名 > SET < 列名 >=< 表达式 >[,< 列名 >=< 表达式 >] [WHERE < 条件 >]; SET 子句指定修改方式, 要修改的列和修改后取值 WHERE 子句 o 指定要修改的元组 o 缺省表示要修改表中的所有元组

137 数据更新 ( 续 ) 用 WHERE 子句指定修改单个记录例 5.52 将学生 的姓名改为 徐长今 UPDATE Student SET Sname=' 徐长今 ' WHERE Sno ='030019';

138 数据更新 ( 续 ) 用 WHERE 子句 ( 或不用 ) 指定修改多个记录例 5.53 将信息系所有学生的年龄增加 1 岁 UPDATE Student SET Age=Age+1 WHERE Dept= CS ; 用子查询指定要修改的记录 WHERE 子句中的比较表达式再不可以交换 例 5.54 将计算机科学系全体学生的成绩置零 UPDATE SC SET Grade=0 WHERE 'CS'=(SELECT Dept FROM Student WHERE Student.Sno=SC.Sno);

139 数据更新 ( 续 ) DBMS 在执行修改语句时会检查欲修改的元组是否会破坏表上已定义的完整性规则 如果破坏, 系统会提示, 且该记录不会被修改 实体完整性对于修改的记录主码不为空且不可以与表中已有记录主码相同 参照完整性外码必须取空值或被参照关系中对应属性的值 用户定义的完整性 对于有 NOT NULL 约束的属性列是否提供了非空值 对于有 UNIQUE 约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内

140 数据更新 ( 续 ) 删除数据从指定表中删除满足 WHERE 子句条件的所有元组 删除的是表中的数据, 表的定义仍在存在 语句格式 : DELETE FROM < 表名 > [WHERE < 条件 >]; WHERE 子句指定要删除的元组 o 如果省略 WHERE 语句表示要删除表中所有的元组 例 5.55 删除学号为 的学生记录 DELETE FROM Student WHERE Sno=' ;

141 数据更新 ( 续 ) 用子查询指定要删除的记录例 5.56 删除计算机科学系所有学生的选课记录 方法 (1) DELETE FROM SC WHERE 'CS'=(SELECT Dept FROM Student WHERE Student.Sno=SC.Sno); 方法 (2) DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Student.Sno='CS');

142 数据更新 ( 续 ) DBMS 在执行删除语句时会检查所删除的元组是否会破坏表上已定义的完整性规则 如果破坏, 系统会提示, 且该记录不会被删除 参照完整性 不允许删除 级联删除 ( 用 CASCADE 参数指定 ) 例 5.57 删除学号为 的学生记录 DELETE FROM Student WHERE Sno='030019'; 则此时在 SC 表中依然有学号为 的学生的选课记录, 破坏了参照完整性 需要采用级联删除的方法或是用显式的 SQL 语句删除 SC 表中的数据

143 视图 基本概念视图是从一个或几个基本表 ( 或视图 ) 导出的表, 它与基本表不同, 是一个虚表 在数据字典中只存放视图的定义, 不会出现数据冗余 基表中的数据发生变化, 从视图中查询出的数据也随之改变 视图一经定义, 就可以和基本表一样被查询和删除, 并且可以在视图之上再定义新的视图 视图的更新 ( 增加 删除 修改 ) 操作会受到一定的限制 视图对应三级模式体系结构中的外模式 视图的作用 视图能够简化用户的操作 视图使用户能以多种角度看待同一数据 视图对重构数据库提供了一定程度的逻辑独立性 视图能够对机密数据提供安全保护

144 视图 ( 续 ) 建立视图语句格式为 : CREATE VIEW < 视图名 > [(< 列名 > [,< 列名 >] )] AS < 子查询 > [WITH CHECK OPTION]; CREATE VIEW 子句中的列名可以省略, 此时视图的属性由子查询中 SELECT 目标列中的诸字段组成 但在下列情况下需要明确指定视图的所有列名 : o 某个目标列是集函数或列表达式 o 多表连接时选出了几个同名列作为视图的字段 o 需要在视图中为某个列启用新的更合适的名字 子查询中的属性列不允许定义别名, 不允许含有 ORDER BY 子句和 DISTINCT 短语 WITH CHECK OPTION 表示对视图进行更新操作的数据必须满足视图定义的谓词条件 ( 子查询的条件表达式 )

145 视图 ( 续 ) DBMS 执行 CREATE VIEW 语句时只是把视图的定义存入数据字典, 并不执行其中的 SELECT 语句 在对视图查询时, 按视图的定义从基本表中将数据查出 例 5.58 建立信息系学生的视图 CREATE VIEW CS_Student AS SELECT Sno,Sname,Age FROM Student WHERE Dept='CS'; 若一个视图是从单个基本表导出的, 并且只是去掉了基本表的某些行和某些列, 但保留了码, 这类视图称为行列子集视图 例 5.58 所建立的视图即为行列子集视图

146 视图 ( 续 ) 例 5.59 建立信息系学生的视图, 并要求透过该视图进行的更新操作只涉及信息系学生 CREATE VIEW CS_Student AS SELECT Sno,Sname,Age FROM Student WHERE Dept='CS' WITH CHECK OPTION; 针对此视图, 当进行以下操作时, 插入操作,DBMS 自动检查 Dept 属性值是否为 'CS' o 如果不是, 则拒绝该插入操作 o 如果没有提供 Dept 属性值, 则自动定义 Dept 为 CS 修改操作,DBMS 自动加上 Dept='CS' 的条件 删除操作,DBMS 自动加上 Dept='CS' 的条件

147 视图 ( 续 ) 例 5.60 建立信息系选修了 C1 号课程的学生视图 CREATE VIEW CS_C1(Sno,Sname,Grade) AS SELECT Student.Sno,Sname,Grade FROM Student,SC WHERE Sdept='CS' AND Student.Sno=SC.Sno AND SC.Cno='C1'; 例 5.61 建立信息系选修了 C1 号课程且成绩在 90 分以上的学生的视图 CREATE VIEW CS_C1_EXC AS SELECT Sno,Sname,Grade FROM CS_C1 WHERE Grade>=90;

148 视图 ( 续 ) 例 5.62 定义一个反映学生出生年份的视图 CREATE VIEW Stud_Birth(Sno,Sname,Birth) AS SELECT Sno,Sname,2005-Age FROM Student; 当子查询中有由表达式构成的派生属性列 ( 也称为虚拟列 ) 时, 必须明确定义组成视图的各个属性列名 例 5.63 将学生的学号及他的平均成绩定义为一个视图 CREATE VIEW Stud_AG(Sno,Avegrade) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

149 视图 ( 续 ) 查询视图从用户角度看, 查询视图与查询基本表相同 DBMS 实现视图查询方法 实体化视图 (View Materialization) o 有效性检查 : 检查所查询的视图是否存在 o 执行视图定义, 将视图临时实体化, 生成临时表 o 查询视图转换为查询临时表 o 查询完毕删除被实体化的视图 ( 临时表 ) 视图消解法 (View Resolution) o 进行有效性检查, 检查查询的表 视图等是否存在 如果存在, 则从数据字典中取出视图的定义 o 把视图定义中的子查询与用户的查询结合起来, 转换成等价的对基本表的查询 o 执行修正后的查询

150 视图 ( 续 ) 例 5.64 在信息系学生的视图中找出年龄小于 20 岁的学生 SELECT Sno,Age FROM CS_Student WHERE Age<20; 视图实体化法 : 通过视图定义建立视图结构下的临时表并对临时表进行查询, 在查询结束后删除临时表 视图消解法 : 根据视图定义将对视图的查询转换为对基本表的查询, 转换后的查询语句如下 : SELECT Sno,Age FROM Student WHERE Dept='CS' AND Age<20;

151 视图 ( 续 ) 例 5.65 在 Stud_AG 视图 ( 见例 5.63) 中查询平均成绩在 90 分以上的学生学号和平均成绩 SELECT * FROM Stud_AG WHERE Avegrade>=90; 使用视图消解转换的查询 SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90; 由于对定义中含有集函数的视图不能完全正确地转换为对基本表的查询, 这类查询应当直接对基本表进行

152 视图 ( 续 ) 视图更新 从用户角度看, 更新视图与更新基本表相同 DBMS 实现视图更新的方法 o 视图实体化法 (View Materialization) o 视图消解法 (View Resolution) 指定 WITH CHECK OPTION 子句后,DBMS 在更新视图时会进行检查, 防止用户通过视图对不属于视图范围内的基本表数据进行更新 实际系统对视图更新的限制 从理论上讲, 对其更新能够唯一转换为对应基本表更新的视图是可更新的 实际使用中的商品化 RDBMS 系统只允许对可更新视图的更新操作 通常都只允许对行列子集视图进行更新

153 视图 ( 续 ) 例 5.66 将信息系学生视图 CS_Student 中学号为 的学生姓名改为 '' 李开复 '' UPDATE CS_Student SET Sname=' 李开复 ' WHERE Sno='030014'; 例 5.67 向信息系学生视图 CS_Student 中插入一个新的学生记录 : (030020, 胡伟武,37 岁 ) INSERT INTO CS_Student VALUES('030020',' 胡伟武 ',37);

154 视图 ( 续 ) 例 5.68 删除视图 CS_Student 中学号为 的记录 DELETE FROM CS_Student WHERE Sno='030020'; 有些视图是不可更新的 因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新 例 5.69 UPDATE Stud_AG SET Avegrade=90 WHERE Sno='030020'; 无论实体化法还是消解法都无法将其转换成对基本表 SC 的更新

155 视图 ( 续 ) 删除视图语句格式 : DROP VIEW < 视图名 >; 该语句从数据字典中删除指定的视图定义 由该视图导出的其他视图定义仍在数据字典中, 但已不能使用, 必须显式删除 删除基表时, 由该基表导出的所有视图定义都必须显式删除 例 5.70 删除视图 CS_C1 DROP VIEW CS_C1; 由于有在视图 CS_C1 之上建立的视图 CS_C1_EXC, 在 CS_C1 被删除后 CS_C1_EXC 已经无法使用, 这时还应该使用下列语句删除 CS_C1_EXC DROP VIEW CS_C1_EXC;

156 数据控制 数据控制是指 DBMS 用来保证数据库正确稳定运行所提供的对数据的管理机制 SQL 中的数据控制包括事务管理和数据保护, 即数据库的恢复 并发控制 数据库的安全性和完整性控制 数据库的完整性是用来防止合法的用户对数据库的无意破坏, 即防止允许更新的用户不小心对数据的错误更新而破坏数据库 ; 数据库的完整性控制是通过数据库定义过程来指定, 在数据更新过程中由系统来进行检查并予以保证 ; 数据库的安全性是用来防止非法用户有意窃取或恶意破坏数据库中的数据 数据库的安全性控制通过授权机制来实现, 即通过赋予用户对数据库的使用权限来保证数据的安全

157 数据控制 ( 续 ) 授权将数据库中的某些对象的某些操作权限赋予某些用户 GRANT 语句 GRANT < 权限 >[,< 权限 >]... [ON < 对象类型 > < 对象名 >] TO < 用户 >[,< 用户 >]... [WITH GRANT OPTION]; DBA 拥有数据库操作的所以权限, 他可以将权限赋予其他用户 建立数据库对象有用户称为该对象的属主 (OWNER), 他拥有该对象的所有操作权限 接受权限的用户可以是一个或多个具体用户, 也可以是全体用户 (PUBLIC) 指定了 WITH GRANT OPTION 子句, 获得某种权限的用户还可以把这种权限再授予别的用户 ; 没有指定 WITH GRANT OPTION 子句, 获得某种权限的用户只能使用该权限, 不能传播该权限

158 数据控制 ( 续 ) 权限一览表 对 象 对象类型 操作权限 属性列 TABLE SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES 视 图 TABLE SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 基本表 数据库 TABLE DATABASE SELECT, UPDATE, DELETE, ALTER, INDEX, ALL PRIVILEGES CREATETAB 例 5.71 把查询 Student 表权限授给用户 User1 GRANT SELECT ON TABLE Student TO User1;

159 数据控制 ( 续 ) 例 5.72 把对 Student 表和 Course 表的全部权限授予用户 User2 和 User3 GRANT ALL PRIVILIGES ON TABLE Student,Course TO User2,User3; 例 5.73 把对表 SC 的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC; 例 5.74 把查询 Student 表和修改学生学号的权限授给用户 User4 GRANT UPDATE(Sno),SELECT ON TABLE Student TO User4;

160 数据控制 ( 续 ) 例 5.75 把对表 SC 的 INSERT 权限授予 User5 用户, 并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO User5 WITH GRANT OPTION; 执行例 5.75 后,User5 不仅拥有了对表 SC 的 INSERT 权限, 还可以传播此权限 GRANT INSERT ON TABLE SC TO User6 WITH GRANT OPTION; 同样,User6 还可以将此权限授予 User7 GRANT INSERT ON TABLE SC TO User7; 但 User7 不能再传播此权限 (User6 未给 User7 传播权限 ) User5 User6 User7;

161 数据控制 ( 续 ) 收回权限从指定用户那里收回对指定对象的指定权限 REVOKE 语句 : REVOKE < 权限 >[,< 权限 >]... [ON < 对象类型 > < 对象名 >] FROM < 用户 >[,< 用户 >]...; 例 5.76 把用户 User4 修改学生学号的权限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM User4; 例 5.77 收回所有用户对表 SC 的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC;

162 嵌入式 SQL 什么是嵌入式 SQL(Embedded SQL)? SQL 语句嵌入到其他高级语言中, 这时高级语言被称为 ( 宿 ) 主语言 为什么要引入嵌入式 SQL? SQL 语言是非过程性语言 事务处理应用需要高级语言 嵌入 SQL 语句的分类 嵌入 SQL 语句 { 说明性语句 { 可执行语句 数据定义 数据控制数据操纵

163 嵌入式 SQL( 续 ) 嵌入式 SQL 语句执行必须解决的三个问题 如何区分主语言与 SQL 语句? o 以 EXEC SQL 开始 o 以分号 (;) 或 END_EXEX 结束 如 C 语言中使用 SQL,EXEC SQL <SQL 语句 >; EXEC SQL DROP TABLE Student; SQL 与主语言如何交互? o SQL 中可以使用宿主语言的程序变量 ( 主变量 ), 这些变量名前加冒号 (:) 作为标志 o SQL 语句执行后, 系统要反馈给应用程序若干信息, 并将这些信息送至 SQL 的通信区 SQLCA 如何协调两种不同的计算模型? o SQL 语句是描述性的面向集合的语句, 负责操纵数据库 o 高级语言语句是过程性的面向记录的语句, 负责控制程序流程 使用游标来协调上述两种处理方式

164 嵌入式 SQL( 续 ) 预编译过程 由 DBMS 的预处理程序对源程序进行扫描, 识别出 SQL 语句 把它们转换成主语言调用语句, 以使主语言编译程序能识别它 最后由主语言的编译程序将整个源程序编译成目标码 嵌入式 SQL 执行流程 主语言进行变量声明和界面处理, 并读取用户数据 SQL 语句用主变量从主语言中接收执行参数, 操纵数据库 SQL 语句的执行状态由 DBMS 送至 SQLCA 中 主语言程序从 SQLCA 中取出状态信息, 据此决定下一步操作 如果 SQL 语句从数据库中成功地检索出数据, 则通过主变量传给主语言做进一步处理, 否则退出主程序 如果 SQL 语句返回的是结果集, 则主语言通过游标逐一处理各记录

165 不用游标的 SQL 语句 说明性语句说明性语句是专为在嵌入式 SQL 中说明主变量 SQLCA 等而设置的 说明主变量 o EXEC SQL BEGIN DECLARE SECTION; o EXEC SQL END DECLARE SECTION; 这两条语句必须配对出现, 相当于一个括号, 两条语句中间是主变量的说明 说明 SQLCA o EXEC SQL INCLUDE SQLCA;

166 不用游标的 SQL 语句 ( 续 ) 数据定义语句数据定义语句中不允许使用主变量 例 5.78 建立一个学生表 Student EXEC SQL CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Sex CHAR(1), Age INT, Dept CHAR(15));

167 不用游标的 SQL 语句 ( 续 ) 查询结果为单记录的 SELECT 语句语句格式 EXEC SQL SELECT [ALL DISTINCT] < 目标列表达式 >[,< 目标列表达式 >]... INTO < 主变量 >[< 指示变量 >][,< 主变量 >[< 指示变量 >]]... FROM < 表名或视图名 >[,< 表名或视图名 >]... [WHERE < 条件表达式 >] [GROUP BY < 列名 1> [HAVING < 条件表达式 >]] [ORDER BY < 列名 2> [ASC DESC]]; 把从数据库中找到的符合条件的记录, 放到 INTO 子句指出的主变量中去

168 不用游标的 SQL 语句 ( 续 ) 说明 主变量的使用范围 o INTO 子句 o WHERE 子句的条件表达式 o HAVING 短语的条件表达式 使用指示变量 o 指示变量只能用于 INTO 子句中 o 如果 INTO 子句中主变量后面跟有指示变量, 则当查询得出的某个数据项为空值时, 系统会自动将相应主变量后面的指示变量置为负值, 但不向该主变量执行赋值操作, 即主变量值仍保持执行 SQL 语句之前的值 o 当指示变量值为负值时, 不管主变量为何值, 均应认为主变量值为 NULL 查询结果为空集 o 如果数据库中没有满足条件的记录, 即查询结果为空, 则 DBMS 将 SQLCODE 的值置为 100 查询结果为多条记录 o 程序出错,DBMS 会在 SQLCA 中返回错误信息

169 不用游标的 SQL 语句 ( 续 ) 例 5.79 根据学生号码查询学生信息 EXEC SQL SELECT Sno,Sname,Sex,Age,Dept INTO :hsno,:hsname,:hsex,:hage,:hdept FROM Student WHERE Sno=:givensno; 假设将要查询的学生的学号赋给了主变量 givensno,hsno, hsname,hsex,hage,hdept 和 givensno 均是主变量, 并已预先说明 非 CURRENT 形式的 INSERT 语句例 5.80 某个学生新选修了某门课程, 将有关记录插入 SC 表 假设学生的学号 课程号和成绩已分别赋给主变量 studno,couseno,newgrade EXEC SQL INSERT INTO SC(Sno,Cno,Grade) VALUES(:studno,:couseno,:newgrade);

170 不用游标的 SQL 语句 ( 续 ) 非 CURRENT 形式的 UPDATE 语句例 5.81 将全体学生 C1 号课程的考试成绩增加若干分 假设增加的分数已赋给主变量 raise EXEC SQL UPDATE SC SET Grade=Grade+:raise WHERE Cno='C1'; 例 5.82 修改某个学生 C1 号课程的成绩 假设该学生的学号已赋给主变量 givensno, 修改后的成绩已赋给主变量 newgrade EXEC SQL UPDATE SC SET Grade=:newgrade WHERE Sno=:givensno;

171 不用游标的 SQL 语句 ( 续 ) 非 CURRENT 形式的 DELETE 语句例 5.83 某个学生退学了, 现要需要将有关他的所有选课记录删除掉 假设该学生的姓名已赋给主变量 studname EXEC SQL DELETE FROM SC WHERE Sno=(SELECT Sno FROM Student WHERE Sname=:studname); 数据控制语句例 5.84 把查询 Student 表权限授给用户 User1 EXEC SQL GRANT SELECT ON TABLE Student TO User1;

172 使用游标的 SQL 语句 ( 续 ) 查询结果为多条记录的 SELECT 语句使用游标的步骤 说明游标 EXEC SQL DECLARE < 游标名 > CURSOR FOR <SELECT 语句 >; 打开游标 EXEC SQL OPEN < 游标名 >; 移动游标指针, 然后取当前记录 EXEC SQL FETCH [[NEXT PRIOR FIRST LAST] FROM] < 游标名 > INTO < 主变量 >[< 指示变量 >][,< 主变量 >[< 指示变量 >]]...; 关闭游标 EXEC SQL CLOSE < 游标名 >;

173 使用游标的 SQL 语句 ( 续 ) CURRENT 形式的 UPDATE 语句和 DELETE 语句使用游标的步骤 说明游标 打开游标, 把所有满足查询条件的记录从指定表取至缓冲区 推进游标指针, 并把当前记录从缓冲区中取出来送至主变量 检查该记录是否是要修改或删除的记录, 是则处理之 重复第 (3) 和 (4) 步, 用逐条取出结果集中的行进行判断和处理 关闭游标, 释放结果集占用的缓冲区和其他资源

174 数据库设计 数据库设计基本概念 数据库设计过程 数据字典

175 基本概念 数据库设计是指对于一个给定的应用环境, 构造最优的数据库模式, 建立数据库及其应用系统, 使之能够有效地存储数据, 满足各种用户的应用需求 ( 信息要求和处理要求 ) 在数据库领域内, 常常把使用数据库的各类系统统称为数据库应用系统 数据库是信息系统的核心和基础 把信息系统中大量的数据按一定的模型组织起来 提供存储 维护 检索数据的功能 使信息系统可以方便 及时 准确地从数据库中获得所需的信息 数据库是信息系统的各个部分能否紧密地结合在一起以及如何结合的关键所在 数据库设计是信息系统开发和建设的重要组成部分

176 基本概念 ( 续 ) 数据库设计过程的本质是将数据库系统与实际应用对象紧密结合起来, 从而构成一个有机的整体 一般来说, 数据库设计要经过三个大的阶段 : 可行性分析研究对现有计算机系统和实际情况进行详细调查 对数据库设计中的问题以及效益等进行综合的评估, 完成可行性报告 系统设计主要包括概念设计 逻辑结构设计和物理结构设计三部分, 从三个不同层次上把概念世界中的实体及其关系转换成为 数据 并落实在计算机上 设计实施与系统运行主要工作为完善系统设计 整体相关文档 编制数据字典 培训人员, 为日后的维护与运行作准备 数据库设计是一个不断提高和完善的过程 为了达到用户满意的效果, 往往需要反复调试与修改

177 数据库设计过程 参照软件工程的思想, 将数据库系统的生命周期 (Life Cycle) 划分为以下七个阶段 : 规划阶段建立数据库的必要性和可行性分析, 确定数据库系统在组织中和信息系统中的地位以及数据库之间的关系 需求分析全面 准确了解用户的实际要求 概念结构设计设计数据库的概念结构, 即通过对用户需求进行综合 归纳与抽象, 形成一个独立于具体 DBMS 的概念模型 逻辑结构设计逻辑结构设计是将抽象的概念结构转换为所选用的 DBMS 支持的数据模型, 并对其进行优化

178 数据库设计过程 ( 续 ) 数据库物理设计数据库物理设计是对为逻辑数据模型选取一个最适合应用环境的物理结构 ( 包括存储结构和存取方法 ) 数据库实施在数据库实施阶段, 设计人员运用 DBMS 提供的数据语言及其宿主语言, 根据逻辑设计和物理设计的结果建立数据库, 编制与调试应用程序, 组织数据入库, 并进行试运行 数据库运行和维护数据库应用系统经过试运行后即可投入正式运行 在数据库系统运行过程中必须不断地对其进行评价 调整与修改 上述七个阶段构成了数据库系统的整个生命周期 严格地说, 从第二阶段需求分析开始到第五阶段数据库的物理设计为数据库的设计阶段

179 需求分析 需求分析的目的是了解用户要求, 对现实世界中的处理对象进行调查和分析, 制定出数据库设计的具体目标, 并在此基础上确定新系统的功能 需求信息的收集又称为系统调查, 即了解用记的组织机构设置 主要业务活动和职能及对新系统的要求 通常包括以下内容 : 组织机构信息 组织机构的调查是分析信息流程的基础 调查要求得到详细的组织机构图及各机构的职能等 具体业务现状 即现行业务中的信息种类 信息的流程 信息的处理方式以及各种业务的工作过程 外部要求 如响应时间要求 数据安全性 数据完整性等 长远规划中的应用范围和要求 要充分考虑今后发展的需要, 要留有余地, 增加系统的科修改性和可维护性

180 需求分析 ( 续 ) 对获得的各种信息进行认真分析是需求分析阶段的另一个重要任务 分析结果以数据流图和数据字典的形式进行描述, 作为需求分析阶段的成果, 指导下一步设计工作 确定系统边界确定哪些业务是由计算机来处理, 哪些业务手工处理 业务流程分析分析各项业务的执行过程和期间的数据联系, 结果以数据流图 (DFD,Data Flow Diagram) 形式加以描述 建立数据字典数据字典是各类数据描述的集合, 通常以表格的形式详细地描述业务处理过程中用到的各类数据

181 需求分析 ( 续 ) 数据字典主要有以下几方面的作用 o 对数据进行标准化管理 o 使收集信息文本化 o 为数据库设计和系统分析提供有力工具 o 为数据库管理系统对数据库的存取控制和管理提供条件 o 为数据库的维护和扩充提供依据数据字典的内容包括数据项 数据结构 数据流 数据存储 处理过程等五部分 o 数据项是数据的最小单位, 对数据项的描述一般包括项名 含义说明 别名 类型 长度 取值范围及该项与其它项的逻辑关系 常以表格的形式给出 o 数据结构是若干数据项的有意义的集合, 通常代表某一具体的事物 包括数据结构名 含义 组成成份等 o 数据流可以是数据项, 也可以是数据结构, 表示某一次处理的输入 / 输出数据 包括数据流名, 说明, 数据来源, 数据去向, 及需要的数据项或数据结构

182 需求分析 ( 续 ) o 加工中需要存储的数据 包括数据存储名 说明 输入数据流 输出数据流 组成成份 数据量 存取方式 存取频度等 o 加工处理过程定义和说明 包括处理名称 输入数据 输出数据 数据存储 响应时间等 例 5.85 下面是某公司采购业务流程图 原材料报价单 供应商 库存量 报价 地址 生产计划 确定采购计划 采购计划 编制订货单 订货单 订单细节 零配件采购记录

183 需求分析 ( 续 ) 订货单号数据项描述 数据项名 : 订货单号说明 : 用来唯一标识每张定货单类型 : 字符型长度 :8 别名 : 采购单号取值范围 : ~ 采购单的描述 数据结构 : 采购单含义 : 记录采购信息, 包括采购什么材料及其数据组成成份 : 采购单号材料名称数量

184 需求分析 ( 续 ) 采购计划数据流 数据流名 : 采购计划说明 : 根据生产需要的原材料选定供应商, 编制采购计划来源 : 原材料需求表去向 : 采购单数据结构 : 原材料需求表供应商 原材料的价目表存储数据 数据存储 : 原材料价目表说明 : 记录每一原材料的名称 供应商 价目, 在计算产品成本和采购费用支付处理中使用输入数据 : 订购单输出数据 : 支付费用表

185 需求分析 ( 续 ) 数据描述 : 原材料名称供应商单价数据量 : 约 50 条记录存取方式 : 随机存取频度 :30 次 / 月 采购支付处理 处理过程 : 采购支付说明 : 根据采购单 原材料价目表, 计算出应付原材料采购费用输入数据 : 采购单数据存储 : 原材料价目表输出数据 : 支付费用表

186 需求分析小结 需求分析的目的在于了解用户对新系统的要求, 即用户需要新系统来做些什么 需求调查就是收集用户各项业务活动中所使用的各类数据的样本, 以及了角用户各项业务处理的流程及其对数据的使用情况 需求信息的分析是分析人员根据收集的用户需求 业务处理流程及各类数据, 进行分析处理, 以数据流图和数据字典的方式加工整理, 即以专业人员的语言来描述企业的数据及业务 需求分析的结果应能正确地描述企业现实信息, 分析人员的分析结果应得到用户的认可, 数据流图和数据字典被认为是分析人员和用户交流的语言 新系统必须充分考虑今后可能的扩充和改变, 不能仅仅按当前应用需求来设计数据库

187 概念结构设计 概念结构设计就是将现实事物以不依赖于任何数据模型的方式加以描述, 目的在于以符号化的形式正确地反映现实事物及事务与事物间的联系 概念结构设计的内容就是建立概念模型 概念模型要求 概念模型是对现实世界的抽象和概括, 它应真实 充分地反映现实世界中的事务和事物之间的联系 概念模型应简洁 明晰 独立自于机器 容易理解, 方便数据库设计人员与用户交换意见, 合用户能积极参与数据库的设计 概念模型应易于变动 当应用环境和应用要求改变时, 容易对概念模型修改和扩充 概念模型应容易向关系 层次或网状数据模型转换 描述概念模型的最常用方法是 E-R 图方法

188 概念结构设计 ( 续 ) 概念结构设计的四种策略 自顶向下首先定义全局概念结构的框架, 然后逐步细化 自底向上即首先定义各局部应用的概念结构, 然后将它们集成起来, 得到全局概念结构 逐步扩张即首先确定核心业务的概念结构, 然后以此为中向外扩张, 最终实现全局概念结构 混合策略即将自顶向下和自底向下两种策略结合使用, 首先确定全局框架, 划分为若干个局部概念模型, 再采取自底向上的策略实现各局部概念模型, 加以合并实现全局概念模型

189 概念结构设计 ( 续 ) 概念结构设计过程 分 E-R 图 基本 E-R 图 合并 ( 消除冲突 ) 修改与重构 ( 消除冗余 ) 视图集成 初步 E-R 图 分析 规范化理论

190 概念结构设计 ( 续 ) 概念结构设计是对实体的抽象过程, 一般先设计局部视图, 再进行视图集成, 将多个局部 E-R 图合并成一张完整的 E-R 图 设计局部视图的步骤 选择局部应用恰当选择各级数据流图中的某一层的某一部分业务, 准备设计局部 E-R 图 通常就选择数据流图的中间级, 这一级实体描述准确, 实体间的联系也比较清晰, 如需求分析阶段的采购业务流程图 设计分 E-R 图对于每一局部应用, 其所用到的数据都应该收集在数据字典中了, 依照该局部应用的数据流图, 从数据字典中提取出数据, 使用抽象机制, 确定局部应用中的实体 实体的属性 实体标识符及实体间的联系及其类型

191 概念结构设计 ( 续 ) 例 5.86 根据上述的业务流程图和数据字典, 可以确定以下数据结构 生产计划 ( 计划编号, 产品编号, 开始日期, 完成日期, 产量 ); 原材料 ( 原材料编号, 名称 ); 库存 ( 原材料编号, 库存量 ); 供应商 ( 供应商名称, 地址, 电话, 邮编, 负责人 ); 订货单 ( 订货单编号, 供应商名称, 交付日期, 结算日期 ); 订单细节 ( 订货单编号, 原材料编号, 名称, 单价, 数量 ); 设计局部 E-R 图如下 :

192 概念结构设计 ( 续 ) 确定实体与属性的两条准则 属性是不可再分的数据项, 属性不可以再有属性 ; 属性不能与其他实体发生联系, 联系只能存在于实体与实体之间 例 5.87 职工 ( 职工号, 姓名, 年龄, 职称 ), 其中的职称如果与工资 住房和福利挂勾 ( 即有联系 ), 则应该单独作为实体, 而职工与职称间构成联系 职工 职工 聘任 职称 职工号 姓名 年龄 职称 职工号 姓名 年龄 职称名 工资 住房 福利

193 概念结构设计 ( 续 ) 视图集成将多个局部 E-R 图合并成一张完整的 E-R 图的过程称为视图集成 视图集成的作用 可以完整地描述企业的信息及其联系 在集成过程中可以解决冲突和消除冗余 局部 E-R 图冲突 : 属性冲突 同一属性可能会存在于不同的分 E-R 图 命名冲突 相同意义的属性, 在不同的分 E-R 图上有着不同的命名, 或是名称相同的属性在不同的分 E-R 图中代表着不同的意义 结构冲突 同一实体在不同的分 E-R 图中有不同的属性, 同一对象在某一分 E-R 图中被抽象为实体而在另一分 E-R 图中又被抽象为属性

194 概念结构设计 ( 续 ) 视图集成方法选择两个具有相同实体的 E-R 图, 通过相同实体将两个 E-R 图连结起来构成一个 E-R 图, 然后再与其它 E-R 图连结直到将所有的局部 E-R 图全部连结成一个 E-R 图, 称为全局 E-R 图 例 5.88

195 概念结构设计小结 概念结构设计是对企业信息的描述, 建立在需求分析的基础上 概念结构设计是对需求分析的数据 ( 数据字典 ) 依照业务处理流程 ( 数据流图 ) 抽象出事物之间的联系, 以 E-R 图的形象加以描述 概念结构设计是独立于具体 DBMS 的, 信息世界的范畴, 即只考虑正确体现事实世界中的事物及联系的描述, 而不考虑如何存储这些信息 概念结构设计所得到的 E-R 图应该得到用户的认可 进行概念结构设计的同时进行的是系统的总体设计

196 逻辑结构设计 逻辑结构设计是对数据在计算机中的组织形式的设计, 即依照 DBMS 支持的数据模型, 设计用户数据的组织形式 ( 模式设计 ) 逻辑结构设计主要任务逻辑结构设计就是将概念结构设计阶段所得到的概念模型 ( 通常是 E-R 图 ) 转换成某一数据模型的数据结构, 其中最主要是关系模式 此外, 逻辑结构设计还应该对关系模式进行规范化和优化以及设计面向应用程序的用户子模式 逻辑结构设计的步骤 依照选用的 DBMS 支持的数据模型, 确定欲转换的数据模型 根据确定的数据模型进行 E-R 图向该数据模型的转换 对得到的数据模型进行优化 设计子模式

197 逻辑结构设计 ( 续 ) 逻辑结构设计过程 逻辑模型 概念结构设计 转换为一般数据模型 转换为特定 DBMS 支持下的数据模型 优化模型 逻辑结构设计 基本 E-R 图 转换规则 特定 DBMS 的特点与限制 优化方法如规范化理论

198 逻辑结构设计 ( 续 ) 转换原则 E-R 图由实体 实体的属性和实体之间的联系三个要素组成 关系模型的逻辑结构是一组关系模式的集合 将 E-R 图转换为关系模型 : 将实体 实体的属性和实体之间的联系转化为关系模式 转换方法 一个实体型转换为一个关系模式 o 实体型的属性转换为关系的属性 o 实体标识符转换为关系的码 联系的转换 o 一个 1:1 联系可以转换为一个独立的关系模式, 也可以与任意一端对应的关系模式合并 o 一个 1:n 联系可以转换为一个独立的关系模式, 也可以与 n 端对应的关系模式合并 o 一个 m:n 联系转换为一个关系模式

199 逻辑结构设计小结 逻辑结构设计与拟使用的 DBMS 相关 逻辑结构设计首先应该将概念模型转换为指定 DBMS 所支持的数据模型的数据结构 对 RDBMS 而言, 应将 E-R 图转换为关系模式 对关系模式进行规范化和优化, 以实现系统的正确性, 提高系统的性能 与数据库的逻辑结构设计同期进行的是应用系统的模块设计, 应考虑不同模块所使用的数据的特点, 对关系模式进行必要的分解, 以提高各应用模块的执行效率 进行外模式设计也是为应用系统各模块而服务

200 物理结构设计 数据库在物理设备上的存储结构与存取方法称为数据库的物理结构, 它依赖于给定的计算机系统 数据库物理设计指为某个逻辑数据模型选取一个最适合应用环境的物理结构的过程 数据库物理设计主要内容 确定数据的存储结构需要在存取时间 存储空间利用率和结构维护等方面进行折衷考虑 选择存储路径存取路径直接影响数据存取效率, 一般需提供多条存取路径 确定数据存放位置根据应用情况对数据进行分组, 按存取频率和速度分别存放在不同的存储设备上 确定存储分配根据 DBMS 提供的存储分配参数, 确定块大小 缓冲区大小和个数 溢出空间大小等

201 物理结构设计 ( 续 ) 物理结构设计过程 物理模型 物理结构设计 逻辑结构设计 确定数据库的物理结构 评价数据库的物理结构 物理结构设计 逻辑模型

202 物理结构设计 ( 续 ) 物理结构设计评价对数据库物理设计过程中产生的多种方案进行细致的评价, 从中选择一个较优的方案作为数据库的物理结构 评价方法 定量估算各种方案 o 存储空间 o 存取时间 o 维护代价 对估算结果进行权衡 比较, 选择出一个较优的合理的物理结构 如果该结构不符合用户需求, 则需要进行全面修改, 并重新设计

203 物理结构设计小结 在物理设计阶段根据 DBMS 特点和处理的需要, 进行物理存储安排, 设计索引, 形成数据库内模式 针对用户的需求, 制定评价标准, 对设计结果进行认真评价, 并进行修改和再设计

204 课后作业 课后作业

205 本章小结 数据库系统概述 关系数据库结构化查询语言 SQL 数据库设计