数据库系统原理（BNU_党德鹏_慕课）超详细听课笔记

完整笔记PDF版：
链接：https://pan.baidu.com/s/1K-5y9yTLBGz7mOCYeOMtrw
提取码：jbhp
（需要Word版请私信）

第一章 绪论

第一节 数据库

包含关于某单位、机构、部门，或是某领域、业务、主题，或是某对象的信息、互相关联的大量数据的集合称作数据库。

数据库的种类：



本课程所讲的数据库特指用专门通用软件管理，长期储存在计算机内、有组织、可共享的大量数据的集合。

第二节 数据库管理系统

1. 定义： 英语为Database Management System，缩写为DBMS。DBMS是位于用户与操作系统之间的一层软件，它是一个大型的复杂的系统软件/领先的著名数据库管理系统。
2. 种类： DBMS有PostgreSQL, IBM DB2, Oracle, Microsoft SQL Serve等。
3. 目标： 简单、高效、安全地共享数据
4. 模型： 数据库库管理系统基于特定数据模型来组织管理数据
数据模型是数据结构和语义的概括（以树结构组织数据是层次模型，以表组织就是关系模型）
数据库模型：面相特定数据模型、针对特定应用的数据库结构
关系型数据库中具体的表结构就称为关系模式或表模式（考生表的模式就是考生表包括的那些属性组成的属性集合）
特定数据库中特定时刻存储的数据的集合称为该数据库的一个实例
模式与实例：数据库模式相对稳定，很少需要修改, 实例是其对应模式的一个具体值，反映的是某一时刻数据库的状态。同一个模式可以有很多实例，实例的值随数据库中数据的更新不断变化
5. 数据抽象的层次：

• 概念层：对现实世界事物的状态进行选择 加工 组织，形成人对全部用户数据需求在大脑中的认识（实体—联系模型，也就是E-R模型）

• 逻辑层：描述全部用户数据的整体结构，由DBMS提供，通过便于人理解的相对简单的结构来描述数据库当中存储的数据，以及这些数据之间存在的联系（模式）

• 视图层：从某个或某类用户角度出发，只描述与其相关的那一部分数据（外模式）

• 物理层：描述数据实际上是怎样在磁盘设备上组织的（典型模型：B+树）（内模式）

支持逻辑、视图、物理三层模式的建立和之间的两级映射，三层的数据结构可以不同。
三层模式两级映射，既为简单、安全地共享数据提供支持，又为应用系统易于扩展来适应应用需求的变化奠定了基础。
6. 数据逻辑独立性： 当模式改变时，由数据库管理员DBA对各外模式/模式映射作相应的改变，外模式保持不变，应用程序依据外模式编写，完全无需修改。
7. 数据物理独立性： 如果数据库的内模式改变，即物理改变，只要对模式/内模式映射作相应的修改，可以使模式保持不变
8. 基本功能： 数据库管理系统最基本功能就是允许用户逻辑地使用数据而无需关注这些数据在计算机中是如何存放 如何处理的


9. DBMS体系架构：

• 数据字典：存放元数据，如数据模式 外模式 内模式的定义
• 索引：帮助快速定位特定数据项
• 统计数据：反应数据库数据分布特征
• 日志：依次登记对数据的修改

10.数据库的语言（SQL语言）:

• 数据定义语言：定义数据库结构
• 数据操纵语言：操纵数据库
• 数据保护语言：数据保护相关

第三节 数据管理技术发展趋势

随着大数据现象出现，为应对新需求新挑战，数据管理技术正面临基础架构带来的深刻变革
利用计算机集群这种新架构来存储和处理，大数据的NoSQL系统已成为关系数据库系统的巨大危险，关系型数据库要与其竞争需要重新设计以适应新架构，尽快适应硬件的快速发展（主存成本急剧下降，计算机内存容量大幅提高）

• 关系型数据库以关系为数据模型，采用声明性语言，支持完全ACID特性的数据一致性。
• NoSQL系统强调可扩展性和高性能，放弃了关系模型 声明性语言 完全ACID特性。
• NewSQL可兼顾以上5种性能。
• spanner使模式演变更简单，支持的广域（网范围）复制一致性更强。

第二章 关系模型

第一节 关系结构和约束

1.关系表
关系数据库使用一个或多个表来存储数据。数学上把一系列域上的笛卡尔积的子集关系称为关系。
这里的表正好符合这个定义：每个表有多个行，每个行有多个列，每个行列单元都是不能再分的原子值。
同一表中，各行相异，不能重复出现完全相同的行，行次序和列次序无关紧要。
实际应用中，数据库通常给每个列一个唯一名字（属性/字段），每个属性有一个允许值的集合，称为该属性的域或取值范围。
表中的行（Row），也称作元组（Tuple）或记录（Record）。表中的数据是按行存储的。表中的一行数据即为一个元组或一条记录，其每行由若干字段值组成，每个字段值描述该对象的一个属性或特征。
给定一个关系表，习惯上把关系模式写成表名后面跟一对圆括号里面罗列列名，如
examinee（eeid,eename,eesex,eeage,eedepa)

数据库保存现实世界中的状态，并服务现实世界中的应用，数据库中的数据应与现实世界时时保持一致才有意义。
理想情况下，系统能判断数据库中各个数据项值是否与现实世界一致（数据是否真实正确），然而这个目标无法实现。退而求其次，可在系统中定义一些正确数据应满足的约束，系统自动检测数据是否满足约束条件，并且只允许满足这些约束条件的数据进入数据库。
软件系统无法保证数据的真实正确性，可以保证数据符合明确定义的约束（完整性约束，数据安全性的一部分）。

2. 常见的简单约束：
1 对属性取值范围的限定，如性别只有男、女两种取值
2 对属性值之间相互关系的限定，最典型为关系模型中“键”的概念。
键： 超键 候选键 主键 外键
超键： 在给定关系模式中，能唯一标识出各个元组的属性集合，被称为该关系模式的超键。超键中可能包含无关紧要的属性，也就是说超键的真子集也可能是超键。

候选键： 在给定关系模式中，能够唯一标识出各个元组属性集合，并且不包含多余属性，称这个属性集合为该关系模式的候选键。（候选键是超键，但超键不一定是候选键）

主键： 一个关系中可能有多个候选键，通常指定其中一个，并且只能是一个，用来标识元组，该候选键称为主键。（主键具有唯一性，主键是候选键，但候选键不一定是主键。）

主关键字(主键，primary key)是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。在数据库中能够唯一地标识一个记录被称为主键。
主键是数据库中具有唯一性的字段，也就是说数据表中的任意两条记录都不可能拥有相同的主键字段。

关系模式R2中的某属性不是R2的候选键，而是关系模式R1中的候选键，则这个属性集对模式R2而言是外键。

外键： 如果关系表S1的一个属性子集A，必须匹配另外一个关系表S2中出现的数值，则称A是关系表S1的外键。其中S1称为引用关系，S2称为被引用关系。
外键的值，或与被引用关系中出现的数值对应，或为空值。

examinee表中的考生所在院系eedepa属性就是一个外键，取值必须是院系表中院系名dname列已有的某个值或者空值，否则可能是出现了数据值错误。

第二节 基本关系代数运算

关系代数： 可以用代数、逻辑等方法描述关系操作，最基本最常用的是代数方法，即关系代数。关系代数也是一门代数，关系代数包括一个运算集合，这些运算以一个或两个关系作为运算数，产生一个新的关系作为结果。

关系代数把表看作元组集合，既然是集合，就不包括重复元组，也就是说，关系代数每个运算都是去重的。

通常一门代数总是包括一些运算符和一些运算数。

基本关系代数运算包括：选择、投影、集合并、集合差、笛卡尔积、更名运算

• 选择运算是选出满足给定谓词（条件）的元组，结果关系和原关系有着相同的模式。用希腊字母δ表示。
  
• 投影运算用来从给定关系产生一个只有其部分列的新关系。用希腊字母π表示。
  
  并、差、交运算需要满足的条件：

1. 关系r和s必须是同元的，即它们的属性数目必须相同。
2. 对所有的i, r的第i个属性的域必须和s的第i个属性的域相同。

• 集合并运算
  关系是相容的
  两个关系必须是同元的，即它们所包含的属性个数必须相同（列数相同）
  两个关系对应属性的域必须相同或相容。
  
• 集合差运算
  集合差运算是用来查询，在一个关系中而不在另一个关系中的那些元组，和并运算一样，集合差运算只能在相容的关系间进行。
  
• 笛卡尔积运算
  结果关系的模式是参与运算的两个关系的模式的串接
  运算符左侧关系中的每一个元组与右侧关系的每一个元组拼接，形成结果关系中的一个元组
  （在属性名称前加上来自的关系名，中间用小数点分隔，但对那些只在两个关系模式之一中出现的属性，一般省略其关系名前缀。）
  
• 更名运算
  可以对关系进行更名或给关系代数运算的结果关系起个名字。
  
  

一个关系运算的结果可以作为另一个关系运算的参数，从而可以把多个关系运算组合为复杂关系运算。

第三节 附加关系代数运算

关系代数基本运算是完备的，足以表达任何普通关系代数查询，但是基本运算的缺陷在于许多查询的表达式显得复杂、冗长。
定义附加运算不能增加关系代数的表达能力，但可以简化一些查询的表达。
常见的附加运算：

 集合交：

• 集合交运算的结果是由那些同时在参与运算关系中存在的元组组成
• 集合交运算也必须在相容的关系间进行。
  查询既有考官也有报考考生的试卷eid：erexam在eid属性上的投影 交 eeexam在eid属性上的投影
  

 自然连接：可以将特定选择运算和笛卡尔积运算合并为一个运算

• 首先计算笛卡尔积
• 然后在笛卡尔积的结果上，基于两个关系模式中都出现的属性，即两个关系模式的所有同名属性值相等的选择运算
• 最后去除重复列（因为在任何元组中同名属性上的值都是相等的）

计算examine自然连接eeexam（examine ∞ eeexam）：
由于examine 和 eeexam只有一个相同属性dname，选择examine.dname和 eeexam.dname值相等的元组，最后去掉重复列，只保留一个dname列


用自然连接查询

 属性连接：在笛卡尔积的基础上选取指定同名列上取值相等的行，结果关系中这些指定同名列只出现一次

• 首先计算笛卡尔积
• 然后在笛卡尔积的结果上，基于两个关系模式中都出现的属性，即按指定同名属性进行属性值相等的选择运算
• 最后去除重复列（因为在任何元组中指定的同名属性上的值都是相等的）

计算examinee按属性dname与department进行联结，算出笛卡尔积，由于运算指定按属性dname进行连接，所以选择examine.dname和 eeexam.dname值相等的元组，最后去掉重复列，只保留一个dname列


用属性联接查询，按属性eeid进行联接

自然连接和属性连接的区别：
当参与联接运算的两个表有多个同名列时，自然联接的匹配条件是所有同名列全部取值相等；而属性联接的匹配条件是指定其中若干同名列取值相等。如果属性联接指定全部同名列来匹配则等价于自然联接。

 条件连接：在笛卡尔积的基础上，选取满足给定条件的元组

• 首先计算笛卡尔积
• 然后选取满足给定条件的元组

examinee和department按照examine.dname = eeexam.dname进行联接


条件联接查询

 赋值
赋值运算是将←右侧的表达式的结果赋给←左侧的关系变量，该关系变量可以在后续的表达式中使用

第四节 扩展关系代数运算

除了基本关系代数运算、附加关系代数运算，为了表达用户其他方面的查询需求，需对关系代数运算做进一步扩充。
关系代数运算的进一步扩充包括三方面：

 广义投影：

广义投影的结果对应F1, F2, …,Fn包括n列，它是对关系代数表达式t的每一行计算F1, F2, …,Fn作为对应行相应输出表达式的值。

 聚集函数：用G表示聚集计算

计算examiner中erage属性的平均值，查询结果只包含一个元组，只有单个属性，可以运用更名运算指定属性名的关系，对应的是学校所有考官的平均年龄。

 分组聚集：

G左下标是分组属性，右下标是聚集函数，后面一对圆括号给出参与聚集运算的关系代数表达式。
对examiner表按院系名dname的值分组，并计算每组erage的平均值，也就是查询每个院系考官的平均年龄。

 外联接：内联接（自然联接、属性联接、条件联接）可能产生悬浮元组，也就是说有些元组不能跟另外关系的任何一个元组匹配，一些实际应用系统可能希望在结果中保留悬浮元组，这就有了外联接运算。

外联接运算的三种形式：

步骤：


对应左（右，全）外联接左侧（右侧，两侧）关系的元组都要在连接结果中出现。

首先计算两个关系的内联接，然后再在左侧（右侧、两侧）关系中找出那些没有在联接结果中出现的元组，把这些元组也加入到联接结果中去，并且用空值null来填充那些来自相对的另一侧关系的属性值。

右外联接先算内联接得到三个元组，找出右侧关系department中与左侧关系任何元组都不匹配的悬浮元组，向联接结果中加入元组，从右侧关系得到的属性被赋为悬浮元组当中的值。而其他属性，也就是从左侧关系得到的属性，这里的eename被赋值为空值null

即使有的考官没有参加组卷，他个人的信息也会出现在结果关系当中。

第三章 PG数据定义与操作

第一节 SQL概述

关系数据库的标准语言是Structured Query Language，即结构化查询语言，简称SQL。虽然SQL字面含义是“查询语言”，但其功能却包含数据定义、查询、修改和保护等。
PostgreSQL(PG)目前最强大，特性最丰富和开源数据库系统软件。
网上考试数据库系统：

网上考试数据库系统设计6个表：






基本Select语句的一般形式：

WHERE子句与HAVING子句的区别：

整个语句的执行过程：

注意点：

• 在实际执行时数据库管理系统为了尽可能快地获得等价查询结果而采取完全不同的执行步骤。
• 大小写不敏感（系统默认小写）
• 用单引号做字符串常量的标识（只有引号中的字符才区分大小写）

第二节 数据定义与修改

数据定义包括数据库对象的创建、删除和更改三部分。
表的定义：表模式的创建、修改和删除。表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。
数据修改：给表里添加数据、更新数据以及删除数据
表的创建：约定表中各个属性的属性名及其数据类型

定义表的属性时需要指明数据类型，PG支持的数据类型远比其他数据库管理系统多得多，有点、线、框、圆、多边形等几何类型，网络地址类型，xml类型，JSON类型等
PG中常用的基本数据类型：

PG中使用单引号做字符串常量的标识，对于包含单引号的字符串，使用两个单引号表示一个单引号，注意不能写成双引号。

修改表名：

当不再需要一个表时，可以用“DROP TABLE”语句删除


PG使用ALTER TABLE语句进行表结构修改：

• 给表中添加一个新列
• 把表中对应的列的名称进行修改
• 把表中给定的列的数据类型进行修改
• 删除表中给定的列
  
  不论基本表当中原来是否已有数据，新增加列的列值一律为空值
  
  
  
  
  向表中插入数据的语句是INSERT语句，有以下三种方法：
  （1） 向表中直接插入常量元组
  
  
  
  
  

（2）向表中插入查询结果，这个语句可以把一个SELECT语句的查询结果插到一个表中

（3）向表中插入表

删除关系中的元组

执行时先从表中找出所有满足<条件表达式>的元组，然后把它们从表中删去，如果没有任何条件限制，将删除所有元组


当需要更新表中元组的某些列值时，用UPDATE语句

这个UPDATE语句会更新指定表中满足WHERE子句条件的元组，SET子句指定要更新的列以及更新后的列值

第三节 简单查询

投影、选择、聚集查询
（1）投影：
基本投影：指选取表中的某些列的列值
广义投影：指在选取属性列时，允许进行适当运算

查询全部试卷的试卷号与试卷名, 查询要求列出每一个试卷号和每一个试卷名的值

如果SELECT后面是最简单的形式（单独一个*），这种情况输出FROM子句给出表中的所有列值

查询试卷的详细信息

SELECT后面可以是表达式，也就是广义投影，为FROM后面给出表的每一行计算一个表达式值，作为对应行中相应输出表达式的值

查询全体考生成绩与满分（100分）之差

还可以用ORDER BY子句让查询结果中的行按一个或多个列或列表达式的值进行排序，升序用ASC，排序列为空值的行最后显示；降序用DESC, 排序列为空值的行最先显示。默认为升序。

查询全体考官的情况:

查询结果按所在学院名升序排列，同一学院的考官按年龄降序排列

注意： 投影结果当中可能出现所有列值均相等的重复行，但从数据库管理系统实现的角度看，投影过程会对每个新产生的结果进行标识，即系统能区分每个行，也就是说，由于去重是一项耗时的工作，DBMS采取惰性原则：除非在SELECT后跟DISTINCT明确指出要求去重，否则，默认情况下、或者SELECT后跟ALL时都保留重复。（ 默认情况下SELECT的执行不会自动去重 ）

（2）选择
选择操作用WHERE子句实现，从表中选择满足给定条件的行。

查询年龄大于20的所有考生的信息

如果没有WHERE子句就是无条件选择所有行，WHERE子句中的选择可以用各种运算符组合而成（比较运算符、逻辑运算符、SQL语言专有运算符）
比较运算符：

逻辑运算符：

SQL语言专有运算符：

查询历史学院和心理学院年龄在58岁以上工资在6000~9000元之间所有姓罗的考官

（3）聚集： 从多个输入行中计算出一个结果
聚集函数

查询考官总人数

查询考生218811011013的平均成绩

SUM和AVG的输入必须是数值型的，其他聚集函数还可以用在非数值数据类型的列上。另外，聚集函数不能进行复合运算

GROUP BY子句利用指定列进行分组，所有给出列上取值相同的行被分在一个组。还可以使用GROUP BY子句将聚集函数作用在组上

查询各个学院考官的平均年龄，按平均年龄升序排列

这里AS使用了别名

HAVING子句对GROUP BY子句形成的分组进行筛选
HAVING子句给出的条件只针对GROUP BY子句形成的分组起作用，也可以使用聚集函数

查询报考了3门以上试卷的报考号，按报考门数降序排列

COUNT()实际就是每个eeid的报考门数
COUNT() 函数返回匹配指定条件的行数。
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
COUNT() 函数返回表中的记录数
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

第四节 联接查询

如果查询的数据涉及两个或多个表，可以使用联接操作，称为联接查询。

联接查询涉及联接条件和联接类型两个方面

如果联接条件为永真则等价于笛卡尔积，也叫交叉联接

联接类型： 是按照对悬浮行的不同处理方式来分的，分为内联接和（左/右/全）外联接。

在写多表联接查询语句的时候，如果查询涉及多个表，最简单直接的方法就是在FROM后面依次写上这些表名，并以逗号或CROSS JOIN分隔，FROM子句的结果表就是这些表的 笛卡尔积 。
结果表包含所有这些表的所有列，如果两个表中有同名列，在列名前加上表名作前缀，表名该列的来源表。
根据查询需要，还可通过WHERE子句对笛卡尔积结果表施加选择操作，以撷取那些符合查询条件的行。

查询每个考生及其报考的试卷



进而需要在笛卡尔积结果表中选出examinee.eeid等于eeexam.eeid的行，只有这样的行才是其左边来自examinee的列值和右边来自eeexam的列值是同属同一个eeid的。

完整的查询语句

• 自然联接
  即自然内联接，是在笛卡尔积的基础上选取所有同名列上取值相等的行，结果表中同名列只出现一次。

查询每个考生及其报考的试卷

由于自然联接包含了选取所有同名列取值相等行的操作，与使用笛卡尔积的查询语句不同，这里不再需要WHERE子句，在一个查询的FROM子句中，可以用自然联接将多个表结合在一起。

• 属性联接
  即属性内联接，是在笛卡尔积的基础上选取指定同名属性上取值相等的行，结果表中这些指定同名属性只出现一次

查询每个考生及其报考的试卷

由于属性联接包含了选取指定同名属性取值相等行的操作，虽然都是查询每个考生及其报考的试卷，但与使用笛卡尔积的查询语句不同，这里不再需要WHERE子句

属性联接与自然联接的区别：
当参与联接运算的两个表有多个同名属性时，自然联接的匹配条件是所有同名属性全部取值相等，而属性联接的匹配条件是指定若干同名属性取值相等，如果属性联接指定全部同名列来匹配，则等价于自然联接。

• 条件联接
  即条件内联接，是在笛卡尔积运算的基础上选取满足给定条件的行

查询每个考生及其报考的试卷

内联接可能会出现左表当中的一些行在右表中没有相匹配的行，或右表当中的一些行在左表当中没有相匹配的行，这些没有找到匹配的行称为悬浮行

内联接和外联接的区别就在对于悬浮行的处理不同

三种外联接的处理方式都要首先计算内联接，然后再在内联接的结果中加上相应的左（右、左和右）表中的悬浮行

• 左外联接
  

• 右外联接就是把右侧表中的悬浮行补上空值后加入结果表

• 全外联接是左外联接和右外联接的结合，左侧表中的悬浮行补上空值后加到结果表中，同时右侧表中的悬浮行补上空值后也加到结果表中
  

pgSQL的联接的计算

查询表erexam和表exampaper自然左外联接的语句

第五节 嵌套查询

PG提供嵌套子查询机制
查询块：一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT、OFFSET、WITH子句中的查询称为嵌套查询

在写嵌套查询语句时，由于SELECT语句的结果就是一个表，所以查询块可以出现在另外一个查询中表名可以出现的任何地方。

• 表式嵌套：查询块可以出现在另外一个查询中表名可以出现的任何地方
  

用WITH子句定义临时表
查询平均成绩良好（>=80）的考生人数

在SELECT子句前给出WITH子句定义临时表，WITH后面给出临时表的模式，这里是表名avgach，包括两个属性，AS后面一对圆括号里给出查询语句的查询结果就是临时表的内容。这里是对eeexam表中的行按eeid分组后，每组的eeid和相应的achieve平均值，也就是报考号及平均成绩，定义好临时表就可以基于它来查询。
这里是对临时表avgach选择avgachieve>=80的行并计算行数。
注意点：WITH子句只在包含它自己的查询语句中有效，WITH子句中的AS不能省略。

查询平均成绩>=80的考生人数也可以用FROM子句嵌套来实现

FROM子句中出现表名的地方可以是临时表的定义，像这样AS前面一对圆括号里的查询语句的查询结果是临时表的内容，这里是对eeexam表中的行按eeid分组后，每组的eeid和相应的achieve平均值，也就是报考号及平均成绩，AS后面给出临时表的模式，这里是表名avgach，包括两个属性。
FROM子句定义好临时表就可以基于它来写其他查询子句，这里WHRER子句选择avgachieve>=80的行，SELECT子句计算行数，就得到了平均成绩80分以上的人数。FROM子句中的AS可以省略不写。
以上就是把查询块执行结果看作表的FROM子句和WITH子句嵌套的情形。

• 集合式嵌套
  既然查询语句的执行结果是一个表，而表又可以看作行的集合，从而查询块也可以出现在集合能够出现的任何合适的地方，主要有WHERE子句嵌套和HAVING子句嵌套
  

查询218811011013号考生报考的试卷号和试卷名
实质上就是要列出eeexam表中保存的218811011013号考生报考了的eid集合中的每一个eid对应的exampaper表中的eid和ename的列值

IN后面括号中给出一个内层子查询，WHERE子句中IN后面括号中给出的子查询的结果是一个eid集合，IN就是判断exampaper表每一行的eid是否属于此集合。执行这个查询语句时，首先执行内层子查询，然后用其结果重写外层查询并执行。
也可以这么理解，对exampaper表中的每一行，如果eid属于eeexam表中保存的218811011013号考生报考了的eid集合，则输出此行的eid和ename值。
像这样如果内层子查询不依赖于外层查询，称为不相关嵌套查询，可由内向外逐层处理，也就是每个内层子查询在上一层查询处理之前求解，内层子查询的结果用于建立其外层查询。

查询所有报考了0205000002号试卷的考生详细信息

eeexam保存考生报考数据，但只有这里需要的考生报考号，没有考生姓名年龄等其他数据，要查询考生详细信息还需要examinee表，但是对examinee表并不是列出其所有行，而只需要列出报考了0205000002号试卷的考生对应的那些行。这个查询实质上就是对examinee表中的每一行，如果该eeid出现在eeexam表中的一些行里面，并且其中一行的eid值是0205000002，就输出该examinee表的行。

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真“true”或逻辑假“false”。

该查询的一个解释是：
在examinee中依次取每个行的eeid值，用此值去检查eeexam表，如果eeexam表中存在有这样的行，eeid=examinee.eeid值，并且eid=’ 0205000002’，则把examinee表的这个行送入结果表。
像这样，如果内存子查询依赖于外层查询称为相关嵌套查询，这种情况下，对外层查询表中的每一行，根据它与内层子查询相关的列值处理内层查询，直至外层查询处理完为止。

HAVING子句嵌套和WHERE子句嵌套类似
查询有考生名叫刘诗诗的学院的考生平均年龄，列出学院名和平均年龄

（当select后面同时出现聚集和不聚集的属性，不聚集的属性一定要出现在GROUP BY子句中）
HAVING子句中IN后面给出的子查询，就是查询有考生名叫刘诗诗的学院名集合，整个查询就是对examinee表中的每一行按eedepa值分组，HAVING子句选出有考生名叫刘诗诗的那些组，输出eedepa和每组的eeage平均值。

标量式嵌套
在写嵌套查询语句时，如果能确定查询块只返回单行单列的单个值，查询块可以出现在单个属性名、单个表达式、单个常量，即单值表达式能够出现的任何地方。
查询各个院系名及其考官人数

该查询实质上就是对department表中的每一行，输出dname列值，以及examiner表中erdepa列值与此dname值相等的行数。这里是SELECT子句嵌套。

查询与218810011028号考生同院系的考生的报考号、姓名（假设一个考生只能属于一个院系，并且必须属于一个院系）

WHERE子句中的子查询是找出218810011028号考生的院系名，由于一个考生只能属于一个院系，并且必须属于一个院系，这个子查询肯定返回单个值，所以这里第三行可以用“=”。
整个查询的解释：
对于examinee表中的每一行，列出eedepa值与218810011028号考生相等的那些行的eeid、eename。

查询每个院系的平均分

该查询的实质是对eeexam表中的每一行，按行中eeid值在examinee表中，找到的eedepa值分组，输出每组的eedepa值和平均值，每个eeid对应考生的eedepa值是通过一个子查询来查的，子查询出现在GROUP BY子句和SELECT子句。
查询每个院每个考生得分，列出院名、考生名和分数，按院名升序排列，同院按分数升序排序

查询的实质是：（先看SELECT再看ORDER BY）
对eeexam表中的每一行，输出按行中eeid值在examinee表中找到的eedepa、eename值，eeexam的achieve值按此eedepa值升序排列，同院按achieve值升序排列。此查询中对eeexam表中每个eeid值，对应考生的eedepa、eename是用子查询在examinee中找的。子查询出现在ORDER BY子句和SELECT子句。

子查询还可以出现在LIMIT子句
对examinee表按照eeid升序，从考生人数的四分之一处开始，列出四分之一的考生信息
这个查询语句中输出行数和偏移量都是用子查询查到的examinee表行数计算得到的。

嵌套查询的执行逻辑

• 父子不相关嵌套查询
  
  
  要点：
  父子独立 自内向外 逐层执行
  先执行最内层，再执行次内层，……，直到次外层，最后执行最外层。

• 父子相关嵌套查询
  
  
  取父查询的第一行。因为查询结果有一行，EXISTS返回true，于是第一行就按照SELECT*作为查询结果来输出
  取父查询的第二行。
  ……
  步骤总结：
  依次取examinee每个行eeid值，用此值执行子查询：检查eeexam表
  若eeexam中存在有这样的行：eeid值等于此examinee当前行eeid值，并且eid=‘0205000002’，则此examinee当前行送入结果表
  查询所有报考了0205000002号试卷的的考生详细信息

第四章 PG应用

第一节 数据库应用体系结构

网络上的数据库系统通常把数据表示、处理和存储分为两层、三层甚或多层
常见的有C/S(Client/Server)结构，B/S(Browser/Server)结构两类

1. C/S(Client/Server)结构
包括服务器、客户机两层
数据存储由服务器负责，数据处理任务可以在客户机和服务器之间灵活分配，数据表示由客户机负责，客户机需要时向服务器发出请求，服务器响应这些请求并把结果或状态信息返回给客户机

从开发的角度看，既需要SQL语言访问数据库，又需要C/C++/Java/C#等高级语言进行数据处理和表示。
两类语言混合编程，可以将静态或动态SQL语句嵌入高级语言，也可以让高级语言通过ODBC、JDBC、ADO等桥梁调用SQL。

C/S结构的软件需要针对不同的操作系统开发不同版本的软件，每台客户机需要安装专门的客户端，而且当系统升级时，每台客户机都需要重新安装客户端新版本，其维护和升级成本也比较高。
如腾讯QQ、网络多人游戏等的就是典型的基于C/S体系结构

2. B/S(Browser/Server)结构
也就是浏览器和服务器结构，客户机不需要安装任何特定的软件，只需要有一个浏览器就可以与服务器进行交互，比如很多很多采用ASP、PHP、JSP技术的网站就是典型的基于B/S体系结构的

B/S结构随着intranet（内联网）而兴起，并在internet上得到了广泛的应用，它是对C/S结构的一种变形或者改进，现在几乎已经完全取代了C/S，在这种结构下，用户工作界面通过浏览器来实现，浏览器只负责发送请求、接受数据，几乎不进行数据处理，主要的任务在服务器端处理

B/S结构通常包括多层
第一层为客户端即浏览器层，也就是安装在客户机上的浏览器，用网页制作软件的网页一般是用HTML标记语言编写，它通过互联网向Web服务器提出访问Web页面的请求，并接受从Web服务器返回的页面
第二层为Web服务器，它接受从浏览器发来的请求，然后根据情况有两种处理方式，或者直接返回HTML格式Web页面，或者调用应用服务器中的应用程序，并接受从应用服务器返回的数据，整合成HTML格式页面发送给浏览器
第三层是应用服务器，由中间件与应用程序组成，应用服务器接收Web服务器的调用请求，访问互联网上的数据库服务器，并将结果返回Web服务器，Web服务器和应用服务器有时在同一层，将访问数据库的结果数据以HTML格式作为输出，同普通HTML一起返回给浏览器
第四层为数据库服务器，可以是PG、Oracle、Sybase、DB2、MySql、SQLServer等数据库管理系统，接受从应用服务器发来的SQL请求，经相应处理后将结果返回给应用服务器
第五层为数据库，也就是由相应数据库管理系统管理的数据集

Web与数据库是当今互联网上两大无处不在的关键基础技术，数据库管理具有严格的数据模型与数据模式，有标准查询语言SQL，Web结构松散、随意、访问自由

浏览器和Web服务器主要使用HTML语言，应用程序通常使用高级语言编写的，从HTML到SQL就需要两个桥梁，HTML与高级语言之间的CGI、ASP、JSP等桥梁，高级语言与数据库之间的JDBC、ODBC、ADO等桥梁

B/S结构的系统有一个极大的优点就是维护和升级方式简单，由于B/S结构的固有特点，所以无论客户的规模有多大，系统都只需要在服务端维护升级就可以了，所有的客户端只是浏览器，一般不需要做特殊的专门维护，这样极大地减少了维护升级的费用和时间，还有另外一个好处，由于客户端只是一个浏览器，所以需要对客户进行的培训很少。

第二节 嵌入式pgSQL

PG提供的SQL语言pgSQL不仅可以作为独立的数据语言直接以交互式的方式使用，还可以作为子语句嵌入在宿主语言中使用，这里所说的宿主语言就是指我们常见的高级程序设计语言，如C、C++语言等，实现pgSQL嵌入。

宿主语言编程，比如C语言，
首先使用任意文本编辑器编写，包含了pgSQL语句的宿主语言语句的混合源代码，并将这种混合式源程序文件的扩展名指定为pgc；
然后用嵌入式pgSQL预处理程序ECPG对混合式源程序.pgc文件进行预处理，主要就是把其中的pgSQL语句转换成主语言能够识别的SQL函数调用的形式，结果是同名.c文件，这样就可以利用宿主语言开发环境，在宿主语言SQL函数定义库的帮助下，编译、连接、运行这个包含了数据库访问的宿主语言程序。

在具体程序编写方面，把pgSQL嵌入到宿主语言中使用，还必须要解决以下四个方面的问题
第一， 连接数据库，高级语言需要与数据库服务器建立连接
第二， 嵌入识别问题，宿主语言的编译程序不能识别pgSQL语句，要解决如何区分宿主语言的语句和pgSQL语句
第三， 宿主语言与pgSQL语言的数据交互问题，pgSQL语句的查询结果，必须能够交给宿主语言处理，宿主语言的数据也要能够交给pgSQL语句使用
第四， 宿主语言的单记录与pgSQL的多元组的协调问题，宿主语言一般一次处理一个记录，相当于数据库中的一行，而pgSQL常常处理的是行的集合，这个矛盾必须解决

1.如何解决连接数据库问题？
首先，宿主语言如何连接数据库，通常需要给出数据库服务器地址、端口号、数据库名、用户名、口令等，另外，必要时可能还需要安装和加载数据库驱动程序，C语言连接PG数据库的语句格式为

2.如何解决嵌入式识别问题？
为了区分宿主语言和pgSQL语句，为pgSQL语句加一个识别前缀标识EXEC SQL和结束标志分号 ;

3.如何解决宿主语言和pgSQL语言的数据交换问题？
引入共享变量的概念。
共享变量的声明格式是：在EXEC SQL BEGIN DECLARE SECTION和EXEC SQL END DECLARE SECTION之间给出共享变量的声明
比如，int v1声明整型共享变量v1，varchar v2声明变长字符串型共享变量v2，等等

共享变量声明后，可以用在pgSQL语句中任何需要和允许常量的地方，在pgSQL语句中使用共享变量时，变量名前需加一个冒号

现在，假定我们声明了共享变量长度为12的定长字符串变量bs_eeid，长度为20的定长字符串变量bs_eename，整型变量bs_eeage

如果要修改examinee表中一个考生的年龄eeage，需要修改年龄的考生的报考号由共享变量bs_eeid给出，年龄要修改为共享变量bs_eeage当中的值可以用这个语句

4.如何解决宿主语言的单记录与pgSQL的多元组问题？
为了解决宿主语言一次只能处理一个记录而pgSQL语言一次处理多个元组的矛盾，引入游标cursor的概念
相关语句有：DECLARE CURSOR, OPEN, FETCH, CLOSE

当无法确定SELECT语句查询结果至多是一个元组时，需要用游标机制把多个元组一次一个地传送给宿主语言程序进行处理；另外，在游标处于活动状态时，也可以更新或删除游标指向的元组

比如这是一个在基本表examinee表中检索各位考生的各门考试成绩信息的程序

SQLSTATE是一个特殊变量，用于连接pgSQL执行系统和宿主语言，它是一个五字符的数组，每次调用pgSQL的库函数，向SQLSTATE变量中存放一个代码，以反应调用中出现的问题
比如00000表示没有产生任何错误，02000表示没有找到结果元组
程序首先定义了一个宏，NO_MORE_TUPLES，显然当SQLSTATE为02000时，也就是没有找到元组时，(SQLSTATE, “02000”)，字符串比较结果的非，这个逻辑值为真，此时程序中宏NO_MORE_TUPLES出现的地方就会是逻辑真；如果SQLSTATE不是02000，也就是找到了元组时，程序中宏NO_MORE_TUPLES出现的地方就会是逻辑假

函数queryachieve当中定义了共享变量长度为12的字符串seeid，长度为10的字符串seid，整型sachieve.
语句EXEC SQL DECLARE quecur CURSOR WITH HOLD FOR SELECT eeid, eid, achieve FROM eeexam;这个语句声明游标quecur，用于查询SELECT eeid, eid, achieve FROM eeexam
接着语句EXEC SQL OPEN quecur打开游标quecur，游标打开初始时指向第一行的前面
然后程序中是一个永真while循环，循环体第一句使得游标前进一行并把对应行内容放入共享变量seeid，seid，sachieve，
循环体第二句是一个条件语句，意味着没有找到元组，也就是查询结果为空或元组依次处理完时循环终止，如果FETCH子句成功获得一个元组，NO_MORE_TUPLES为假时，程序会接着执行循环体第三句，输出共享变量seeid，seid，sachieve这些变量的值，然后继续下一轮循环
while循环后面是EXEC SQL CLOSE quecur，是在循环结束后关闭游标quecur

如果应用程序处理过程中需要游标做后向移动，则需将游标定义为卷游标，也称卷标，就是在游标定义语句中CURSOR前加上SCROLL

句法格式如下:
EXEC SQL DECLARE <游标名>
卷标的推进句法可以是下面这些中的任意一个
EXEC SQL FETCH NEXT FROM <游标名> INTO <变量表>

这些语句与前面FETCH语句的区别在于，这里的FETCH后面给出了卷游标移动的方向和行数，NEXT表示把游标从当前位置推进一行，PRIOR表示把游标从当前位置返回一行，FIRST表示把游标移向查询结果的第一行，LAST表示把游标移向查询结果的最后一行，FETCH后面是RELATIVE、ABSOLUTE时，我们可以举例来说明：
RELATIVE 3是指把游标从当前位置推进3行，RELATIVE -3是指把游标从当前位置返回3行；
ABSOLUTE 3是指把游标移向查询结果的第3行，ABSOLUTE -3是指把游标移向查询结果的倒数第3行

我们截至目前所讲的嵌入式编程，pgSQL语句本身都是固定不变的，但是实际应用中有时SQL语句只能在实际运行时才能完全确定，这就需要动态pgSQL语句。
动态pgSQL语句需要先准备再执行

动态pgSQL预备语句的格式：

这里字符串的值应该在运行时结合用户输入，可以生成完整的pgSQL语句，使用动态pgSQL语句时还可以在两种情况下作改进
第一种情况，当pgSQL语句只需执行一次时，预备语句和执行语句可合并成一个语句：

EXEC SQL EXECUTE IMMEDIATE <字符串> 第二种情况，当在预备语句中组合pgSQL语句时， 允许暂时缺某些条件值，这些值可以在执行语句中用USING短语以共享变量给出，语句格式是： EXEC SQL EXECUTE <动态pgSQL语句名> USING <共享变量>

pgSQL动态嵌入式编程的例子

第一行,指定了数据库名postgres

EXEC SQL BEGIN 和 EXEC SQL END之间，定义了四个共享变量，字符指针tt指向字符串“update…;”，是一个pgSQL语句的雏形
字符指针ttc指向字符串“commit”，是一个完整的pgSQL语句“commit”，整型变量ii，jj分别赋值

EXEC SQL PREPARE mmtt from :tt; 用共享变量tt准备语句
EXEC SQL EXECUTE mmtt USING :jj, :tt; 执行共享变量tt准备的语句，第一个？用jj的值替换，第二个？的值用ii替换。这样共享变量tt准备的语句就是把eeid为ii值的行中eeyear属性值加上jj
EXEC SQL EXECUTE IMMEDIATE :ttc; 把上一句对数据的修改提交到数据库中
EXEC SQL DISCONNECT; 最后断开数据库连接

左边给出了程序执行前的数据库状态，右边给出了程序执行后的数据库状态，可以看到执行前eeid是199的那一行中eeyear的值是2000，程序执行后该行的eeyear的值就是加20000后的22000

第三节 JDBC编程

JDBC（Java Database Connectivity）是一种用于执行SQL语句，不仅是PG的SQL语句，而且是包含各种关系数据库系统SQL语言语句的Java API，它定义了用来访问数据库的标准Java类库，使用这个类库可以以一种标准的方法方便地访问各种关系数据库，包括pgSQL

JDBC的目标是使应用程序开发人员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，为访问不同的数据库提供了一种统一的途径。

Java通过JDBC来访问数据库，这些包含了JDBC连接访问数据库语句的Java程序就是普通Java程序，用Java语言编译器编译成字节码就可以在Java虚拟机上运行，只是需要导入相应的包，也就是import Java.sql.* **

具体到编写程序**：
首先用Class.forName(…)加载PG的JDBC驱动程序
然后用DriverManager的getConnection方法，指定访问协议jdbc、数据库管理系统postgresql、数据库服务器主机及端口号、数据库名postgres、用户名、口令，创建连接Connection对象
继而用Connection的createStatement()方法创建可以执行pgSQL语句的Statement类对象，比如stmt
执行完数据库访问，关闭Statement和Connection对象，用Statement对象的executeUpdate方法可以执行数据库修改

数据修改

往eeexam中插入一个218811011116号考生的新元组

数据查询

用Statement的executeQuery方法可以执行数据库查询
查询每个考生的平均成绩，结果放在ResultSet对象中，当打开结果集对象时，游标指向结果集第一行的前面，第一次使用ResultSet的next()方法，使游标指向结果集的第一行，，使用next()方法一次就使游标指向前进一行，可以按属性名或属性在结果表中的从1开始的列序号，用getString()撷取字符串型属性值，getFloat()撷取浮点型属性值，getInt()撷取整型属性值，等等

比如myrset.getString(“eeid”)与myrset.getString(1)，都是返回结果集中的第一列eeid的属性值。
也可以通过用“？”代表以后再给出实际参数值来创建一个预备语句，pgSQL允许在准备的时候预先对查询语句进行编译，每次以新赋值代替“？”执行的时候，重用预先编译的查询形式
使用预备语句的JDBC程序例子

首先，用Connection的prepareStatement()方法对给出的pgSQL语句创建，PrepareStatement对象此时虽然给出了查询语句但并不执行，直到PrepareStatement的executeUpdate()或executeQuery()方法才执行
每次调用PrepareStatement的executeUpdate()或executeQuery()方法执行前，需要用PrepareStatement的setString、setInt等类似方法对“？”代表的参数设定新值，特别是当需要参数以不同值多次执行相同语句时，预备语句的执行速度通常比分开的pgSQL语句快得多
JDBC还提供了查询结果集模式和查询数据库模式的机制

ResultSet有一个getMetaData()方法，用来获得结果集元数据ResultSetMetaData对象

用ResultSet接口来输出结果集所有属性的名称和类型的JDBC程序的例子

首先用ResultSet的getMetaData()方法获取结果集元数据ResultSetMetaData对象
然后通过一个循环来依次输出结果集模式中的每个属性名和类型，循环的次数由ResultSetMetaData的getColumnCount()方法返回结果集模式中的列数来控制，每次输出ResultSetMetaData的getColumnName()返回的属性列名和getColumnType()返回的属性列类型

接口Connection有一个getMetaData方法，该方法返回一个DatabaseMateData对象，DatabaseMateData接口又有大量查询数据库及数据库系统元数据的方法，有的方法返回数据库系统产品名称、版本号以及其它特性。还有其他方法返回数据库本身信息，比如返回表名、外键、授权、数据库最大连接数等等元数据。

查询数据库中属性列信息的JDBC程序的例子

首先，用Connection的getMetaData方法，返回程序当前连接数据库的元数据到DatabaseMateData对象
然后，用DatabaseMateData的getCollumns(null, null, “eeexam”, “%”)方法获取eeexam表中的所有属性列信息到ResultSet对象
最后，利用游标通过一个循环依次用ResultSet的getString()方法获取列名和列类型
DatabaseMateData的getCollumns有四个参数，第一个是目录名，第二个是模式名模板，第三个是表名模板，第四个是列名模板，可以使用pgSQL字符串匹配特殊字符如”%”、”_”，也就是说该方法返回给定目录下满足模式名模板和表名模板表中满足列名模板的列信息

使用JDBC连接PG数据库的JSP程序例子


JSP是一种使用Java开发Web应用程序的服务器端脚本技术，允许将静态html和Java动态生成的html混合在一起，它把Java代码嵌入在html文档中，Web服务器在将html页面传送给客户端浏览器之前，会抽取页面中的Java代码形成单独Java程序，调用相关机制执行这些来自html页面中的Java代码，按照执行结果在原始html文档中，Java代码向out对象输出的地方，用输出信息替换输出语句，并把其余Java代码从页面中删除。
客户端浏览器收到的就是这样得到的html页面，根本无法察觉页面中原来是否曾经包含Java代码
用JSP编写的动态Web页面

其中大部分是用html编写的静态内容，只有中间用<% %>括起来的部分是Java代码，包括通过JDBC访问数据库的加载驱动程序、建立数据库连接，创建Statement对象，执行pgSGL语句以及善后处理等，最后向out对象输出一个字符串。

当有客户浏览器请求该页面时，服务器抽取其中的Java代码并执行

向exam表插入一行元组，并准备好像out对象输出的内容，在原来的html文档中，用拟向out对象输出的结果替换相应的Java输出语句，并删除原来html文档中的<% %>以及所有非输出Java语句。最后Web服务器将这个最终得到的html文档返回给发出请求的客户浏览器

实际中的JSP页面，其中html代码和Java代码可以根据需要任意交替出现，只需要注意这么几点：
第一， 每段Java代码用<% %>括起来，从html代码的角度看，仅关注Java中的输出语句，只要求输出语句位置合适，能最终生成合适的页面，
第二， 关于Java代码，假设去掉所有html代码和分界符<% %>后符合Java语法

第四节 PG中的函数

PG允许使用各种不同的程序设计语言来编写函数，特别是内建了PL/pgSQL，函数经编译和优化后，存储在数据库服务器中，可以反复被调用
存储函数的优点
第一， 由于存储函数不需要额外的语法分析步骤，因而运行效率高
第二， 客户端不需要的中间结果无需在服务器端和客户端来回传递，降低了客户机和服务器之间的通信量，客户机上的应用程序只需向服务器发出存储函数的名字和参数，就可以调用执行存储函数
第三， 便于实施业务规则，通常把业务规则计算程序写成存储函数，由数据库管理系统集中管理，方便进行维护

基本的pgSQL是非过程化的声明语言
PL/pgSQL是为PG扩展的过程语言部分，它为pgSQL增加了控制结构，结合了pgSQL的数据、操作能力和过程化语言的流程控制能力，用于创建存储函数，在服务器执行复杂的计算等
PL/pgSQL是基于块结构的，块中的每个声明和每条语句都是以分号结束，如果某子块在另一块中，那么该子块的END保留字后面必须以分号结束，函数体的最后一个END保留字后面可以省略分号

PL/pgSQL可以使用pgSQL的所有数据类型来声明变量，比如integer、varchar、char等
比如uid integer定义了一个整型变量uid
变量achieve是小数，两边总共五个数字，小数点后含两位小数的数值型，就可以定义为numeric(5,2);

PL/pgSQL中定义变量的一般形式是

初始表达式给变量赋初值

PL/pgSQL中常量的定义类似于变量的定义，格式如下

定义常量时必须要给常量一个初始值，并且该值存在期间或常量的作用域内不能改变，如果试图修改它，PL/pgSQL将返回一个异常

PL/pgSQL中的赋值号是 := ，赋值语句格式如下

除了基本的变量、常量定义和赋值语句，PL/pgSQL还提供了流程控制语句，主要有条件控制语句和循环控制语句，这些语句的语法、语义和一般的高级语言比如C语言非常类似

PL/pgSQL中条件控制语句一共有五种形式的IF语句：
第一种 条件控制语句是IF-THEN结构，语句的形式是

如果逻辑表达式为真，则执行THEN和END IF之间的语句序列，否则，直接执行END IF后面的语句序列

第二种 条件控制语句是IF-THEN-ELSE结构，语句形式是

如果逻辑表达式为真，则执行THEN和ELSE之间的这些语句；如果逻辑表达式为假，则执行ELSE和END IF之间的这些语句

第三种 条件控制语句是IF-THEN-ELSE IF结构，语句形式是

实质上就是在一个IF语句的ELSE部分嵌套了另一个IF语句。如果逻辑表达式1为真，则执行语句序列1；如果逻辑表达式1为假，而逻辑表达式2为真，则执行语句序列2

第四种 条件控制语句是IF-THEN-ELSIF-ELSE结构，语句形式是

这种形式就是多层嵌套，可以方便地在一条语句中检查许多候选条件。如果逻辑表达式1为真，则执行语句序列1；如果逻辑表达式1为假，而逻辑表达式2为真，则执行语句序列2；如果逻辑表达式2为假，而逻辑表达式3为真，则执行语句序列3等等；最后如果所有ELSIF后面给出的逻辑表达式都为假，则执行ELSE后面的语句序列n

第五种 条件控制语句和第四种条件控制语句完全类似，只是把ELSIF可以写作ELSEIF，ELSIF和ELSEIF完全是等价的

PL/pgSQL有多种循环控制语句LOOP、WHILE、FOR，也有控制循环执行状态的语句，比如EXIT、CONTINUE

LOOP循环语句的格式

LOOP循环是无限循环，反复执行循环体，直到EXIT或RETURN语句终止，其中label是可选的，由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环

WHILE循环语句的格式

对WHILE循环来说，在每次进入循环体的时候检查逻辑表达式的值，只要WHILE后面的逻辑表达式为真，就不停地循环执行循环体

FOR循环语句的格式

变量count会被自动定义为整数类型，bound1和bound2给出范围上下界。这两个表达式在第一次进入循环的时候计算BY后面的expression，指定循环的步长，默认为1，但如果声明了REVERSE，步长将变为相应的负值。
FOR循环的基本执行过程是将count设置为循环的下界bound1，检查是否小于上界bound2，当指定REVERSE时则将count设置为循环的上界bound2，检查count是否大于下界bound1，如果越界则跳出循环，否则执行循环体，然后按照步长更新count值，重新判断循环条件

EXIT语句用于结束整个循环过程，使流程跳转到循环体之外，接着执行循环体后面的语句，不再判断执行循环的条件是否成立

如果没有label则退出最内层的循环，然后执行跟在最内层END LOOP后面的语句；如果有label退出label对应层的循环然后执行跟在对应层END LOOP后面的语句；如果有WHEN，循环退出只有在WHEN后表达式为真的时候才发生，否则继续执行EXIT后面的语句

CONTINUE语句用于结束本轮循环，意即提前结束本轮循环，接着执行下一轮循环

如果没有label，就开始最内层循环的下一次执行；如果出现了label，继续执行标签对应的循环；如果有WHEN，那么循环的下一次执行只有在WHEN后表达式为真的情况下才能进行，否则继续执行CONTINUE后的下一个语句

CONTINUE可以用于所有类型的循环，可以通过pgSQL语言创建、执行或删除使用PL/pgSQL编写的存储函数

创建存储函数的例子

CREATE OR REPLACE FUNCTION后面的add是函数名，add后面括号中的参数列表是调用该函数时应该给出的参数值，必须指定值的数据类型，这里是两个整数。
RETURNS后面是函数返回值类型名
DECLARE部分定义了一个整数变量intsum，$1和$2依次引用函数调用时给出的参数值，SELECT $1+$2 INTO intsum计算$1+$2的和，并传给intsum，然后RETURN intsum返回intsum的值
最后一行LANGUAGE plpgsql声明函数编写使用的语言是plpgsql
对于编写好的PL/pgSQL存储函数，有两种方式执行

如果需要存储函数执行返回的结果用 SELECT 函数名([参数1，参数2……])
如果只是需要调用执行存储函数，执行结果的内容无关紧要可以丢弃，那么用PERFORM，语句格式是PERFORM 函数名([参数1，参数2……])

对于不再需要的存储函数，可以予以删除

第五章 PG数据保护

第一节 数据保护

作为实用化最成功的软件系统之一，数据库系统如今已经成为全球化经济基础设施的重要基础部件，对商务管理、业务管理、数据分析、电子商务、慕课……来说，它都是必不可少的。人类社会对数据的依赖达到了前所未有的地步。

数据安全关系到社会的每个组织单位和个人，数据安全建立在数据保密性、数据完整性、数据可用性之上

数据库系统的特点之一就是由数据库管理系统提供统一的机制保护数据的保密性、完整性、可用性

• 数据保密性是指对数据资源的隐藏，通常在数据库中，保护保密性是指仅仅允许经过授权才能读数据，涉及数据值的保密和数据存在性的保密，数据保密性的需求源于数据的敏感性，数据存在性有时候比数据值本身更需要保护
  

• 数据完整性指的是数据的可信度，保护数据完整性通常是指防止非法或者未经授权的数据修改，数据完整性包括数据值的完整性和数据来源的完整性，完整性遭到破坏，也就是产生无效或损坏的数据，将导致用户由于错误的或无效的数据做出错误的决策
  

• 数据可用性是指对数据的期望使用能力，保护数据可用性通常指减少数据库系统停工时间，保持数据持续可访问，可用性遭到破坏将影响用户正常获取数据，意味着用户不能访问数据库或操作困难
  

数据保护允许说明数据资源使用的安全策略，并提供机制予以支持。策略和机制不同，策略决定做什么，也就是对允许什么、禁止什么的规定；机制决定怎么做，也就是实施策略的功能、方法、工具和规程
描述系统访问策略的最简单模型是访问控制矩阵，矩阵的行对应用户或角色，列对应数据库对象，矩阵中的元素表示相应用户对相应数据库对象的访问权限，访问控制矩阵通常依据用户在系统应用中担任的角色确定。

PG中的授权和收权语句可以赋予或撤销用户相应的访问权限，数据库管理系统确保只有获得授权的用户有资格访问数据库对象，令所有未授权人员无法接近，从而保护数据保密性和完整性。
视图作为外模式的实现，支持“见之所需”原则，不仅简化了用户操作，而且结合访问控制可以保护视图数据保密性和完整性，更重要的是提供了一种保护数据存在性的手段。

PG提供加密函数，以允许对存储数据进行加密处理，支持保护数据保密性和完整性验证。
数据库系统都允许对数据库的并发访问以改进系统响应性能，提高可用性，但是，如果对数据库访问的并发执行不加以控制，将可能破坏数据完整性，另外数据库系统总会面临这样那样的故障，故障也可能破坏数据完整性。

数据库管理系统通过将数据库操作组织成事务，以事务为单位实施并发控制和故障恢复，PG允许显示或隐式的事务边界定义。数据库管理系统中的并发控制机制，维护并发访问情况下的数据完整性。数据库管理系统中的故障恢复机制，不仅维护故障情况下的数据完整性，并且由于故障恢复机制对故障的有效处理，它也是保护数据可用性的重要手段。

访问控制、视图、加密、事务等这几种方法，都是从数据值以外的因素考虑数据保护。
理想情况下，数据值发生变化时，比如进行插入或更新时，系统能够判断数据库中的各个数据项值是否与其对应的现实世界状态一致，也就是数据是否真实正确，然而这个目标是无法实现的，退而求其次，可以在系统中定义一些正确数据应该满足的约束，系统自动检查数据库中的数据是否满足这些约束条件，并且只允许满足这些约束条件的数据进入数据库。
也就是说，软件系统无法保证数据的真实正确性，但可以保证数据符合可明确定义的约束，这种约束通常称为完整性约束。

总的来说，数据安全是数据库技术广泛使用的前提条件之一，也是数据库管理系统的重要目标和优势之一，利用技术，保护数据安全，是以数据库为中心的应用系统必不可少的重要方面。

第二节 视图

视图能够使不同用户从不同角度看待同一数据，从而对数据存在性方面的保密性，提供安全保护，同时视图还有如下作用：
视图可以简化用户操作，适当的利用视图，可以更清晰的表达查询，创建视图用CREATE VIEW语句实现，语句的格式如下

组成视图的属性列名，可以全部省略或全部指定，视图属性名省略时，取select结果关系属性名。
比如对于组卷系统，数据库中的基本表eeexam，用户经常要查询报考号eeid及其平均成绩这两项，那么可以用下列语句建立视图

这个语句创建了视图avgachieve，它有两个属性eeid和average，average属性的值就是对应eeid考生的平均成绩

也可以使用视图定义视图，比如可以在examinee、eeexam、exampaper三个表的基础上创建视图eeexamv1，语句如下

这个语句创建了视图eeexamv1有四个属性eeid，eedepa，ename，achieve，也就是考生报考号、考生院系名、试卷名和成绩

然后基于视图eeexamv1，可以进一步创建视图eeexamv2，语句如下

这个视图eeexamv2有三个属性eeid，ename，achieve，也就是历史学院的考生报考号、试卷名和成绩.

创建视图后，就可以在SQL语句中使用，视图名能够出现在表名可以出现的任何地方
比如我们已经定义了视图avgachieve(eeid,average)，要查询平均分86分以上的人数就可以用这样的查询语句

视图和表的异同
虽然视图和表都是关系，都可在查询中直接使用，但是视图与表有一点重要不同，数据库中存储表的模式定义和数据，而只存储视图的定义，不存储视图的数据，视图中的数据是在使用视图时按照视图定义中的查询临时计算的

视图定义中引用的表成为基表，当基表中的数据发生变化时，相应的视图数据也随之改变

视图可以把基表结构的细节封装起来，表可以随应用进化而变化，但视图以及基于视图的应用程序，可以尽可能少地受表变化的影响，这也就是数据的逻辑独立性

通过视图，用户只能查询或修改视图中见得到的数据，看不见数据库的其他数据，这也就提供了数据存在性的保密性保护，由于视图是基于最基本的查询来定义的，系统只存储视图的定义，不存储视图的数据，对视图的更新化通常是转化为基表的相应操作来执行，这就要求对视图的更新慎之又慎。
PG只允许对可更新视图进行修改操作，可更新视图需要满足如下条件：

• 视图是从单个关系只使用投影、选择操作导出
• SELECT子句中只包含属性名，不包含其它表达式、聚集、DISTINCT声明等，查询中没有GROUP BY或HAVING子句，WHERE子句子查询不出现基表名
  -并且对视图的更新操作符合一般更新语句的规则，比如插入时主键不能为空，执行插入操作的视图的投影列需包含基础关系的键
  

关于视图更新的例子
有一个男生视图eemale的定义， 视图eemale是考生表examinee表中的男生报考号，姓名和年龄

执行左1系统会将此对视图eemale的插入操作传递到基本表examinee上执行以下语句左2
提示：
如果在视图定义的末尾包含WITH CHECK OPTION，数据库管理系统自动检查对视图的更新应满足视图定义中WHERE的条件，这种情况下此插入操作便会被拒绝执行投影

第三节 访问控制

PG提供访问控制，以便声明给定用户拥有在给定数据库对象上的给定操作权限，这里关键包括用户、数据库对象和操作权限三个方面

PG使用角色来同一管理用户，分登陆角色和组角色。登陆角色就是具有登陆权限的角色，相当于用户。组角色就是作为组使用的角色，一般不应当具有LOGIN属性。

在创建数据库对象，比如说表属性等的时候，对象会被赋予一个所有者，通常所有者就是执行创建语句的角色，对大多数类型的对象，初始状态是只有所有者或者超级用户可以对该对象做任何事情，要允许其他角色使用这个对象，必须赋予相应的权限。

默认情况下，新建立的数据库总是包含一个超级用户角色，并且默认这个角色名是postgres。创建组角色yanni用语句：CREATE ROLE yanni
创建具有登陆权限的组角色yuxiaotong，可以用CREATE ROLE yuxiaotong LOGIN
创建可创建数据库的组角色masu，用语句CREATE ROLE masu CREATEDB
创建可创建角色的组角色lichen，可以用语句CREATE ROLE lichen CREATEROLE
创建具有口令的组角色wangxi，可以用一句CREATE ROLE wangxi PASSWORD ‘654321’
创建数据库超级用户角色nini，可以用语句CREATE ROLE nini SUPERUSER
注意：只有超级用户才有权创建超级用户

如果要修改组角色名yanni为newyanni，就可以用语句ALTER ROLE yanni RENAME TO newyanni
如果要给组角色yangchen添加创建角色和数据库的这个权限，就可以用一句ALTER ROLE yangchen CREATEROLE CREATEDB
收回组角色yangchen创建角色和数据库的权限，用语句ALTER ROLE yangchen NOCREATEROLE NOCREATEDB
为了与SQL标准兼容，PostgreSQL也有用户管理命令，该角色管理的命令很相似。
创建用户lini可以用语句CREATE USER nini和CREATE ROLE nini login等价
删除组角色yangni，用语句DROP ROLE yangni
删除用户wangni，用语句DROP USER wangni
一旦组角色已经存在了，那么就可以用GRANT和REVOKE命令添加和撤销权限，但是不能建立循环的成员关系

GRANT和REVOKE命令:

GRANT group_role TO rolet REVOKE group_role FROM rolef

一个组角色的成员可以用两种方法使用组角色的权限
首先，一个组的每个成员都可以明确用SET ROLE临时“扮演”该组的成员，在这个状态下，数据库会话具有该组角色的权限，而不是登录角色，这个时候创建的数据库对象被认为是由角色拥有，而不是登录角色
第二，拥有INHERIT属性的角色成员自动具有它们所属组角色的权限

比如我们执行了下列语句

CREATE ROLE Alice LOGIN INHERT
CREATE ROLE Bob NOINHERIT
CREATE ROLE Rose NOINHERIT
GRANT Bob TO Alice
GRANT Rose TO Bob

那么，在以角色Alice连接之后，该数据库会话将立刻拥有直接赋予Alice的权限加上任何赋予Bob的权限，因为Alice“继承”Bob的权限，不过属于Rose的权限不可用，因为即使Alice是Rose的一个间接成员，但该成员关系是通过Bob过来的，而该组有NOINHERIT属性，
在SET ROLE Bob之后，该会话将只拥有那些已经赋予Bob的权力，而不包含那些已经赋予Alice的权限，
在CREATE ROLE Rose之后，该会话将只能使用已赋予Rose的权限，而不包括已赋予Alice或Bob的权限

原来的权限需要的时候可以用下列方式中的任何一个来进行恢复：
可以用SET ROLE Alice，SET ROLE NONE，RESET ROLE

PG提供GRANT语句和REVOKE语句来给角色授予或撤销数据库操作权限
GRANT语句的一般格式

这个语句可以赋予给定角色对给定对象的给定操作权限
当这个GRANT语句包含WITH GRANT OPTION选项的时候，在此语句获得权限的角色可以将所获得权限授予其他角色

例如，我们把查询examiner表和修改考官号的权限授予角色uYing，可以用下列这个语句

GRANT UPDATE(erid)，SELECT
ON TABLE examiner
TO uYing；

剩余的权限可以由DBA或授权者用REVOKE语句收回
REVOKE语句的一般格式：

PG中的授权和收权语句可以赋予或撤销用户相应的访问权限，数据库管理系统确保只有获得授权的用户有资格访问数据库对象，从而保护数据保密性和完整性。

第四节 完整性约束

基于完整性约束的数据保护。
假如DBMS足够智能，理想情况下当数据库中的任意数据项发生变化时，比如执行插入或更新操作时，DBMS能够判断出数据项的新值是否与其对应的现实世界状态一致，也就是数据是否真实正确。
当然这个目标是没有办法实现的，我们能做的是，在系统中定义一些正确数据应该满足的约束条件，系统自动检查数据是否满足这些约束条件，并且只允许满足这些约束条件的数据进入数据库。
换句话说DBMS无法保证数据始终与其对应的现实世界状态一致，但可以保证数据始终与系统中明确定义的约束一致。这种约束通常称为完整性约束

完整性约束的种类

• 主键约束
  意味着各元组主键值不能重复，并且不能为空
  

主键约束在CREATE TABLE语句当中用PRIMARY KEY定义。单属性构成的主键约束，在属性级，也可以在元组级定义主键；对多个属性构成的主键约束，需要在元组级进行定义

比如，将examinee表中的eeid属性声明为主键，可以在eeid属性声明后面直接写上PRIMARY KEY，这就是属性级定义的主键。
也可以在属性声明后面单独附加一行PRIMARY KEY(eeid)，那么这就是元组级定义主键。

又比如，eeexam表的主键它是由eeid和eid两个属性一起组成，因为主键有两个属性只能在元组级进行定义，那就要在所有属性声明的后面单独附加一行PRIMARY KEY(eeid, eid)

将一个表的一个或几个属性定义为主键后，插入元组或对主键列进行修改操作时，系统自动检查元组的各个属性是否为空，只要有一个为空就拒绝插入或修改，并且检查主键值是否唯一，如果不唯一就拒绝插入或修改

由于实际上删除操作不会导致违背主键约束，只有插入元组或修改涉及主键列时，才可能发生违背主键约束，因此只有对关系进行插入或修改时，系统才检验主键约束。

• 外键约束
  声明一个字段或者一组字段的数值必须匹配另外一个表中出现了这个数值
  
  在CREATE TABLE中用FOREIGN KEY子句定义外键，并用REFERENCES子句指明这些外键引用哪个表哪个的属性
  

比如定义eeexam表中外键，eeexam中的eeid引用examinee表当中的eeid，eeexam中的eid引用exampaper当中的eid

将一个表的一个或几个属性定义为外键后，引用表插入元组或对外键列进行修改操作时，系统自动检查是否会导致违背外键约束，如果将违背外键约束，那么就拒绝插入或修改。

将一个表的一个或几个属性定义为外键后，被引用表删除或修改时，系统也会自动检查是否会违背外键约束，如果会违背外键约束，有以下几种处理策略：
1 拒绝执行，当且仅当在引用关系中构造出一个或多个悬浮元组时，对于被引用关系的删除和修改操作将予以禁止，生成¬¬一个错误，表名删除或更新将产生一个违反外键约束的动作，如果约束被声明为DEFERABLE，那么约束检查将推迟到当前事务完成时执行，也就是NO ACTION，是默认动作；
2 RESTRICT操作，禁止对被引用行的操作，并生成一个表明删除或更新将导致违反外键约束的错误，RESTRICT不允许约束检查推迟到事务的晚些时候
3 级联，删除任何引用了被删除的行，或者分别把引用行的属性值更新为被引用属性的新数值
4 设置为空值或默认值，把每个悬浮元组当中的外键值都设置为NULL或默认的一个值

• 非空约束
  意味着每个元组对应列值不能为空，将一个或几个属性声明为非空后，系统会阻止相应属性值为空的数据输入或更新。非空约束用NOT NULL声明
  

比如在创建eeexam时，声明属性achieve值非空

• 唯一值约束
  意味着相应的属性（或属性组）为候选键，将一个表的一个或几个属性声明为唯一值约束（也就是候选键）后，系统自动检查是否违背唯一值约束。
  与主键约束不同，除非同时显式定义为非空，候选键的属性可为空值。
  唯一值约束用UNIQUE声明
  

比如建立部门表department时，部门电话dtele列取值唯一

• CHECK约束
  CHECK( P )子句指定一个谓词P，关系中的每一个元组都必须满足谓词P。
  PostgreSQL目前仅支持属性级和元组级CHECK约束，尚不允许CHECK约束出现子查询
  
  假设examinee表的eeage只允许15~60周岁，由于该约束只涉及一个属性，可以在CREATE语句当中属性eeage类型声明后附加上CHECK(0<eeage AND eeage<60)，这种方式称为属性CHECK约束。
  也可在所有属性类型声明后单独一行做CHECK约束声明，这种方式称为元组CHECK约束。涉及多个属性的CHECK约束，只能以元组CHECK约束方式来声明。
  
  由于元组修改或插入可能给属性引入新值，对于在属性处声明的属性CHECK约束，每当插入元组或修改此属性值时，系统自动检查是否违背相应的CHECK约束，也就是每当任何元组得到该属性的新值时，就对基于该属性的CHECK约束进行检验，如果新值与约束相违背，那么就拒绝更新，如果数据库修改，并没有改变同约束相关的属性值，不会导致与约束相违背，那么就不会检查基于该属性CHECK约束。
  作为元组CHECK约束，即使该约束只涉及一个属性，当关系插入元组或修改一行中任意属性时，即使修改的属性与CHECK约束的约束属性无关，都要进行完整性检查，也就是每当插入元组或者修改表当中的元组时，就对基于元组的CHECK约束中的条件进行检验。
  
• 命名约束
  可以给约束取一个独立的名字，并通过这个名字在需要时对约束进行修改，声明一个命名约束，使用关键字CONSTRAINT后面跟一个作为名字的标识符，然后再跟上约束定义。如果我们没有为约束命名，PG会自动为每个约束指定一个名字。
  

比如将department属性dtele值唯一约束命名为dnu，就可以用这样的语句CONSTRAINT dnu UNIQUE(dtele)，然后需要时可以用ALTER TABLE department DROP CONSTRAINT dnu，从department表中删除唯一值约束dnu。
也可以用ALTER TABLE这个语句来添加约束，比如需要的时候也可以用ALTER TABLE department ADD CONSTRAINT dnu1 UNIQUE(dloca);，添加约束dloca这个值唯一的约束。

第五节 触发器

触发器（Trigger）是用户定义在关系表上的由事件驱动调用的函数的机制

触发器比CHECK约束更灵活，可以实施各种复杂的检查和操作，具有更精细和更强大的数据保护能力，在创建触发器之前，必须首先创建触发器函数，触发器函数的语法如下：

触发器函数中有两对$ $ ，第一对$ $ 之前是函数头部 CREATE FUNCTION function_name() RETURNS TRIGGER AS；
需要注意的是，触发器函数定义的头部RETURNS后面只能是TRIGGER，并且触发器这个函数不能带任何参数；
两对$ $ 之间是函数体，包括DECLARE部分的变量声明，以及BEGIN和END之间的函数执行代码，DECLARE部分是可选的，由于PG允许使用各种语言，比如PL/pgSQL、C、Python等来编写函数；
第二对$$之后是对函数编写语言的说明，这里是PL/pgSQL

触发器函数创建之后，使用CREATE TRIGGER命令创建触发器，语法格式如下

EXECUTE PROCEDURE后面是触发器函数，这里的事件{event[OR…]}包括数据库数据修改操作，比如INSERT、DELETE、UPDATE命令等等;
○1在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据
○2在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据
○3在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据

BEFORE | AFTER的意思是触发器可以分为BEFORE和AFTER触发器，分别在操作完成前和操作完成后出发执行触发器;
ON TABLE后面给出触发器所在表的表名
触发器可以按行或语句出发，也就是行级触发器和语句级触发器

注意：

• 行级触发器的触发器函数为触发语句影响的每一行执行一次
• 语句级触发器的触发器函数为每条触发语句执行一次
  假设在examiner表上创建了一个AFTER UPDATE触发器，如果表examiner有10000行，执行如下这样一个语句UPDATE examiner SET erage=erage+1

如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次，如果是行级触发器，触发动作将执行10000次

WHEN子句
WHEN子句的condition是个布尔表达式，指明触发条件：触发器被激活时，只有触发条件为真触发器函数才执行；否则触发器函数不执行；如果没有WHEN子句，则触发器函数在触发器激活后立即执行

EXECUTE PROCEDURE后的触发器函数给出的就是触发动作
注意：

• 触发器函数必须返回一个NULL或者一个元组类型的变量

1. 语句级触发器应返回NULL
2. 行级after触发器的值总是被忽略，可以返回null

• 行级before触发器的返回值不同，对触发器操作的影响也不同。

1. 如果返回null则忽略该触发器的行级操作，其后的触发器也不会被执行；
2. 如果返回非null值则返回的行将成为被插入或者更新的行

通常用行级before触发器检查或修改将要插入或者更新的数据，用行级的after触发器更新其他表或进行一致性检查
注意：

• 如果是行级触发器，可以在触发器函数中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/INSERT事件之前的旧值。
• 如果是语句级触发器，则不能在触发器函数中使用NEW或OLD

比如，如果要建立INSERT触发器，如果插入examiner表的考号长度不为10位，提示“考号格式错误！”

函数CHAR_LENGTH()，能够取得这个参数的字符串的长度
1用plpgsql创建触发器函数，函数名是examineeid()，
BEGIN和END之间的代码：NEW代表INSERT或UPDATE操作产生的新的数据行，函数CHAR_LENGTH(new.eeid)返回的是新插入行eeid值的长度，RAISE EXCEPTION是抛出一个异常信息，这段代码的意思是：如果新插入的eeid的值的长度不等于10，则提示考号格式错误，返回NULL放弃插入；否则返回NEW，正常插入新元组。
然后就可以创建触发器，触发名是examineeid_insert

在examinee表的INSERT前触发，也就是行级BEFORE触发器，触发器函数时examineeid()，所以完整的创建触发器语句如下右

又比如，如果要建立UPDATE触发器，对examinee表进行UPDATE操作之后，若报考号被修改，则将eeexam表中报考号进行相应修改。

用plpgsql创建触发器函数examinee_up()，BEGIN和END之间的代码：
与NEW代表INSERT或UPDATE操作产生的新的数据行类似，OLD代表被UPDATE或DELETE操作修改或删除的旧的数据行。
这段代码的意思是，如果对examinee表进行UPDATE操作，那么就用UPDATE eeexam SET eeexam.eeid=new.eeid WHERE eeexam.eeid=old.eeid对应修改eeexam表中的报考号。

有了这个触发器函数，就可以用来创建触发器，触发器名是examineeid_updata，在examinee表的UPDATE后触发，也就是行级AFTER触发器，触发器函数是examineeid_up()，所以完整的创建触发器语句就是，下图右

再比如，如果要建立DELETE触发器，当examinee表中元组被删除后，删除eeexam中相应的考生考试信息
首先创建触发器函数eedele()，BEGIN和END之间的代码如下。这个语句对应删除eeexam表当中相应的考生考试信息，有了这个触发器函数，我们就可以来创建触发器，触发器名字是ee_delete，在examinee表的DELETE后触发，也就是建一个行级AFTER触发器，触发器函数时eedelete()，所以完整的创建触发器的语句如下图右

定义触发器的事件一旦发生，数据库管理系统就会自动激活触发器
自动激活触发器

• 语句级before触发器在触发语句开始之前触发
• 行级before触发器在对特定行进行操作之前触发
• 行级after触发器在修改由触发SQL语句影响的每一行元组之后触发
• 语句级after触发器在触发语句结束时触发

通常，用行的after触发器更新其它表，或进行一致性检查。

一个关系表上可以定义多个触发器，同一个表上定义的多个触发器，激活时按照这样一个顺序来执行，若在执行激活触发器的SQL语句之前，先执行这个表上语句级before触发器，然后执行这个表上行级before触发器，在执行激活触发器的SQL语句之后，先执行这个表上的行级after触发器，然后再执行这个表上的语句级after触发器。

对已经存在的触发器，如果不在需要，具有相应权限的用户可以删除它。
删除触发器的语句的格式如下

第六节 事务

事务是对数据库进行操作的程序单位，具有原子性、一致性、隔离性、持久性。

• 一致性：事务一致性指应用程序所定义的事务，其单独成功的执行，必定是使数据库从一个一致性状态变到另一个一致性状态。当多个事务同时对数据库进行更新时，即使每个单独的事务都是正确的，数据的一致性也可能被破坏。
• 隔离性：事务隔离性指一个事务的执行不能被其他事务干扰。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改，有一部分已经写入数据库，这时数据库就处于一种不正确的状态。
• 原子性：事务原子性指事务中包含的所有操作要么都做，要么都不做。
• 持久性：事务持久性指一个事务一旦被提交，它对数据库中数据的改变就应该是持久性的，接下来的其他操作或故障不应该对其执行结果有任何影响。

ACID特性

比如，假设考试数据库包括表examroom

相应的SQL语句非常简单

UPDATE examinee这一句登记考生座位号
UPDATE examroom这一句登记相应的座位已经被分配

命令的细节不做过多关注，关键是这里的两个更新操作，给考生分配座位的更新座位状态，共同完成一个报考事务，这两个更新要么都生效，要么都不生效。
如果一次偶发事故导致给218811001166分配的座位，比如说是五号座位在examroom表中仍然是空，系统就可能再次将该座位分配给另一个考生，这样就带来实际当中混乱。

把这些更新组成一个事务，DBMS就会保证如果操作过程中出现了故障，那么所有这些步骤都不会发生效果，也就是说事务是原子的，它的操作要么是全部发生，要么完全不发生，另外，一旦一个事务完成并且得到数据库管理系统的认可，那么它的结果必须被永久地存储，并且不会受此后故障的影响而消失。比如，如果一个考生报考成功，不应该在他报考完后的某一天的一次系统崩溃就导致他的报考信息丢失。

把这两个更新操作组成一个事务，DBMS会保证一个事务所做的所有更新在事务提交时就永
久保存起来，当多个考生并发的进行报考时，每个考生的报考都不应受到其他报考操作的影响。比如，如果两个考生同时查看到room001的0号座位为空，系统把这个座位分配给两个考生的情况不应该发生。

把这些更新组合成一个事务，DBMS会保证并发事务之间相互隔离，也就是互相影响不到对方结果的正确性。

PG用BEGIN和COMMIT(或ROLLBACK)将数据库访问操作指令序列包围起来，以声明一个事务。如果没有显示的BEGIN命令，PG把每个SQL语句当做一个事务来看待。

PG提供三种独立的事务隔离级别：

• 读已提交READ COMMITTED（默认），在这个隔离级别事务中的语句，看到的是其开始执行时瞬间数据库的一个快照，在同一个事务里两个相邻的SELECT命令可能看到不同的快照，因为其他事务会在第一个SELECT执行期间提交，读已提交提供的这种部分隔离对于许多应用而言就足够，并且这个模式速度快，使用简答。
• 可重复读REPEATABLE READ，如果一个事务需要连续做若干个命令，而这几个命令必须看到完全相同的数据库视图，可以选择可重复读隔离级别，同一个事务内部后面的SELECT命令总是看到同样的数据。在PG中用SET TRANSACTION ISOLATION LEVEL REPEATABLE READ来设置隔离级别为REPEATABLE READ。
• 可串行化SERIALIZABLE。REPEATABLE READ和SERIALIZABLE隔离级别事务中的语句，看到的是该事务开始时的快照，而不是该事务内部当前查询开始时的快照，这一点和读已提交不一样。REPEATABLE READ和SERIALIZABLE隔离级别的事务里面的SELECT命令总是看到相同的数据状态。可串行化级别提供最严格的事务隔离，就像事务是一个接着一个那样串行执行似的。
  

第七节 加密

数据加密技术已经广泛应用于因特网、电子商务、手机网络、银行自助取款机等领域。
数据被称为明文，用某种方法伪装数据以隐藏它的内容的过程称为加密。加密所用的方法称作加密算法，数据被加密后的结果被称为密文，把密文还原为明文的过程称为解密，解密所用的算法称为解密算法。

加密就是加密算法利用加密密钥将明文转换为密文，解密就是解密算法利用解密密钥将密文还原为明文，可以看到，加密体系中最核心的是用于加密解密的算法和密钥

密钥的重要性
现代加密体系中算法通常是公开的，密钥是保密的并且需要向可信权威机构申请，安全性完全取决于密钥的保密性。

加密算法的分类
加密算法一般可分为加密解密密钥相同的对称加密、加密解密密钥不同的非对称加密、单向加密三种。
对称加密体系当中代表性的算法有DES算法、三重DES、AES算法等等。
非对称加密体系当中的代表性算法有RSA算法、DSA算法。
单向加密体系当中的代表性算法有MD5、SHA算法等等。

pgcrypto扩展包
在PG中使用加密技术，首先需要通过CREATE EXTENSIOIN pgcrypto创建pgcrypto扩展包。
该扩展包包含了当前主流加密解密算法的函数定义。
比如MD6(str)计算str里面给定字符串的MD5的128位校验和，以十六进制返回结果，若参数为null则返回null。
比如我们可以用SELECT MD5(‘43543’)，得到对字符串43543的MD5加密结果。

再如，假设有examiner有两个属性(erid, password)，可以使用函数MD5(str)对要插入表中的元组的password进行加密，SQL语句如下
INSERT INTO examiner (erid, password)
VALUES(‘28113699’, MD5(‘82180588’));
然后需要的时候，可以检验保存在变量:pw当中的用户口令是否与数据库当中的匹配，SQL语句如下
SELECT MD5(:pw) = (select password from examiner WHERE erid=’28113699’);
等于号右边是一个子查询

再比如encrypt(‘123456’, ‘key’, ‘aes’),实现用AES算法以字符串key做密钥对’123456’进行对称加密

decrypt函数可以实现用aes算法以字符串key做密钥，对’123456’的对称加密密文进行解密，还原出’123456’

其他加密算法的使用方法完全类似。

第六章 数据库设计：实体-联系方法

第一节 数据库设计方法和生命周期

主要包括两种不同的数据库设计方法及其对应的数据库生命周期，以及实际当中的数据库生命周期。
在实际生活当中，当我们置办了电脑硬件，并安装操作系统和数据库管理系统软件以后，怎样利用这个环境，面向特定的需求，构造最优数据库模式，据此建立数据库以及相应的实际应用系统呢，这个问题就称为数据库设计问题，对这个问题的回答就是数据库设计方法。

数据库模式的优劣直接影响相应信息系统运行的稳定性和质量，所以正确运用数据库设计方法非常重要。
数据库设计方法主要包括实体–联系方法和属性–联系方法两种：

• 第一种，实体–联系设计方法围绕实体展开，从软件工程角度来看，数据库生命周期经历需求分析、概念设计、逻辑设计、物理设计、数据库实现以及运行维护等阶段。
  
  实体–联系设计方法主要过程如下：
  1.分析用户业务需求，得出数据库需要保存的信息内容，也就是数据需求，有了数据需求，就可以进行概念设计，即设计概念模式，概念模式与具体DBMS无关，通常使用实体–联系图表示，也叫E-R图。
  2.在概念模式的基础上，进行逻辑设计，即将概念模式转换成相应的逻辑模式，获得符合选定DBMS数据模型的逻辑结构，比如关系模式。
  3.物理设计，关系数据库的物理设计已经非常简单，只是在需要的时候进行一些参数的设置和选择，比如索引机制、块大小，由于关系数据库系统的物理数据独立性，在逻辑模式确定后伴随着物理设计，就可以进行应用程序的设计
  4.物理设计完成后，就可以开始实现数据库，用DDL/DPL定义数据库结构、把数据入库、编制与调试应用程序并进行数据库试运行，如果试运行中功能指标和性能指标都达到了设计目标，数据库系统就可以正式投入运行。
  
  5.在运行过程当中，还要经常对数据库进行维护
  数据库维护的内容主要包括以下几个方面：
  ○1定期对数据库和事务日志进行备份，保证发生故障时，能利用这些备份，尽快将数据库恢复到一个一致的状态
  ○2当应用环境、用户、完整性约束等出现变化时往往需要根据实际情况调整原有安全策略、完善原有安全机制
  ○3利用DBMS提供的系统性能监测工具监督系统运行，必要时调整某些参数，改进系统性能
  ○4针对数据库性能随着数据库运行逐渐下降问题，必要时重组数据库，回收垃圾，减少指针链，提高系统性能
  ○5针对应用需求的变化，适当调整数据库模式也叫重构数据库

6.如果实际应用发生了根本性的变化，重构数据库的代价就会非常大，那么这个时候，我们就应该终止现有的数据库应用系统生命周期，重新建立新的数据库系统，开始新的生命周期

• 第二种，属性–联系设计方法，主要围绕属性展开，数据库生命周期与实体–联系设计方法类似，唯一的区别是属性–联系方法没有概念设计阶段，而是在需求分析的基础上直接采用属性–联系方法进行逻辑设计，也就是把数据库保存的所有属性放在一张关系表中，进而通过属性之间的联系，来不断的优化这个关系表的模式，得到期望的结果模式。

两种方法的区别：
实体–联系方法以实体为中心，着重于一个关系模式，基本对应一个实体或联系，即关系模式与实体或联系之间基本是一一对应的。
属性–联系方法以属性为中心，着重于属性之间的依赖关系。

属性–联系方法已经发展处非常严密的数学理论和实用算法，但是，随着应用规模的增大，属性数会增加得非常快，要厘清属性间的关系会变得越来越繁琐，甚至非常困难，所以实际当中的数据库设计，通常是将两种方法相结合，宏观上采用实体–联系方法，微观上采用属性–联系方法，也就是对由概念模式转换而来的关系表运用属性–联系方法进行分析优化。有经验的设计师，甚至在概念模式设计阶段，就会运用属性–联系方法来分析各个实体或联系的属性集。

总的来说，数据库设计有两种方法：实体–联系方法和属性–联系方法；它们的区别在于实体–联系方法是先建立概念模式再转换为关系模式，属性–联系方法是把需要数据库保存的所有属性放在一张关系表中，进而通过属性之间的联系来优化这个关系模式。实际中总是把这两种方法结合使用，宏观上采用实体–联系方法把握关键，微观上采用属性–联系方法进行优化

第二节 第一小节 E-R模型元素

对数据库设计，实体–联系方法是先基于实体–联系模型也叫E-R模型，基于这个E-R模型，首先建立起来概念模式，然后再转换为关系模式，概念模式与具体的DBMS无关，通常使用实体–联系图表示，也叫E-R图。

从数据管理的角度看，现实世界存在的就是一切事物，就是一个个实体，现实世界当中，各种人类的活动就是实体和实体之间的联系。
E-R图主要包括实体和联系以及它们各自的属性。实体指现实世界中客观存在的一个个事物或者对象，可以是具体可触及的事物，如一个人、一匹马、一棵树、一个零件等，当然也可以是抽象的对象，比如一次会议、一次演出等。
同类实体组成的集合称为实体集。比如一个考生就是一个实体，实体考生都有相同的属性，报考号、姓名、年龄、性别，所有考生就组成一个实体集考生集。

考生集中的每个考生都用这些属性来描述，但每个考生在这些属性上都有各自相应的不同的值，习惯上，我们把实体和实体集统称为实体。在E-R图当中，实体用方框表示，方框里面写上实体的名字。

现实世界当中，实体不是孤立的，实体之间总是存在一些联系。
比如说“考官工作于某个学院”，就是实体“考官”和实体“学院”之间有联系“工作于”。
比如说“考生报考试卷”，就是说实体“考生”和实体“试卷”之间具有一种联系“报考”。

联系就是一个或多个实体之间的关联关系。同类联系组成的集合称为联系集。习惯上，把联系和联系集统称为联系。在E-R图当中，联系用菱形框表示并用线段将联系与参与这个联系的实体连接起来

实体通常通过一组属性来描述。同类实体通常使用相同的属性组来描述。属性可能取值的范围我们把它称为属性域，也称为属性的域值。

现实世界当中，经常需要区分同类实体集中一个个不同的实体。比如，由于有可能出现重命重姓的考生，考试系统给考生分配一一对应的一个报考号，不同考生报考号不同，就像不同的人具有不同的身份证号一样。
像这样能够并且用来区分一个实体集中不同的一个个实体的最小属性集或者是属性组（一组属性）称为实体标识符（简称标识符），也称为实体主键（简称主键），组成主键的属性称为标识属性。

联系也可能会有属性，数据库存储的数据主要的就是属性值，在E-R图中属性用椭圆表示，并且用线段把属性与这个属性所属于的实体或者联系连接起来，并在那些用于标识实体或者联系的属性下面加上下划线

E-R图主要包括实体、联系以及它们各自的属性。
E-R图的实际例子
假设要建立一个数据库系统，保存和查询考生报考试卷以及考试成绩的信息，E-R图如下：

1该系统涉及两类实体：考生和试卷（用方框表示，方框内标注实体的命名）
2这两个实体之间有联系“报考”（用菱形框表示）
3 实体考生有属性：报考号、姓名、年龄、性别，主键报考号下面加划下划线
实体试卷有属性：考试号、试卷名、时长、类型，主键试卷号下面加划下划线
4联系“报考”它有属性“成绩”，

例子当中的E-R图，已经包括了概念模式当中最主要的成分实体、联系和属性，但这个图还只是部分完成的E-R图。E-R图能表达更为丰富的语义。

1、E-R图主要包括实体和联系以及它们各自的属性。
2、像这样能够并且用来区分一个实体集中不同的一个个实体的最小的属性集或者是属性组（一组属性），称为实体标识符，（简称标识符）也称为实体主键（简称主键）
3、同类实体集内部实体与实体之间的联系，称为一元联系。
4、两个不同实体集中实体之间的联系，称为二元联系。
5、如果实体集E1中，每个实体可以与实体集E2中任意个（零个或多个）实体之间具有联系，并且E2中每个实体至多和E1中一个实体有联系，那么我们就把E1对E2的联系称为“一对多联系”。
6、联系关联的实体个数称为该联系的元数或度数

第二节 第二小节 基本E-R图设计

主要包括属性和联系，在实际当中各种不同情况时的处理，E-R图主要包括实体和联系以及它们各自的属性，而属性和联系的设计一般会碰到多种不同情况。

在E-R图设计过程中，除了像报考号、姓名这种简单的原子属性，还可能碰到复合属性。假设考试实体有报考号、姓名和地址三个属性，而地址是一个由多个成分组成的复合属性。
对于复合属性，按其组成把它分解成一系列子属性，从而这个例子中的考生就有了报考号、姓名、邮政编码、省（市）名、区名、街道路名、门牌号码七个简单的属性。

在E-R图设计过程中，除了像报考号、姓名这种单值属性，我们还有可能碰到多值属性。
单值属性指的是同一个实体在该属性上只能取一个值。比如一个考生只能有一个报考号、一个考生只能有一个姓名。
多值属性指同一个实体的某些属性可能取多个值。一个考官可能有多种联系方式，移动电话、固定电话、电子邮件等等，可以用双线椭圆来表示多值属性

为了关系数据库实现的方便，通常把多值属性转换成多个单值属性，在考官例子当中就可以用移动电话、固定电话、电子邮件三个单值属性代替联系方式这个多值属性

在E-R图设计过程中，我们可能还会碰到派生属性，也就是能够从其他属性值推导出值的属性。
一般来说派生属性的值不必存储在数据库内，而其他需要存储的属性称为存储属性，派生属性用虚线椭圆表示，并且用虚线把派生属性和它相关联的实体连接起来。

在E-R图设计过程中，联系也会碰到几种特别的情况需要考虑，主要涉及联系的元数。

如果实体集S中的每个实体都参与联系集L的至少一个联系，称实体集S“完全参与”联系集L。如果实体集S中只有部分实体参与联系集L的联系，称实体集S“部分参与”联系集L

在E-R图中，完全参与用双线表示，部分参与用单线表示

例子当中护士护理伤员这样的一个联系，护士是部分参与，因为有的护士可能不参与护理活动，伤员是完全参与，每个伤员都必须有护士进行护理。

考生和试卷考生，考生是完全参与，试卷是部分参与，因为每个考生都必须参加考试，但是有的试卷可以没有学生报考

第三节 基本E-R图转换为关系模式

基本E-R图转换为关系模式的一般规则主要包括三个要点：

• 一个实体转换为一个关系模式；实体的一个属性对应为该表的一个列；实体的主键就是表的主键

例：在考生报考试卷的E-R图当中，有两个实体，即考生和试卷，分别转换为考生表和试卷表，表的属性和主键都与实体相对应

• 一个联系转换为一个关系模式；联系的属性对应表的属性，另外并上所有参与联系的各实体主键的并集
  关于由联系转换来的表的主键有三种情况：
  联系是M:N，主键是所有参与联系的各实体主键的并集
  联系是1:N，主键是多端实体主键
  联系是1:1，主键是任一端实体主键

例：在考生报考试卷的E-R图中有一个联系“报考”，对应转换为报考表，联系的属性“成绩”，并上考生实体的主键“考生号”以及试卷实体的主键“考卷号”得到的属性集合就是报考表的属性集

由于联系报考时M:N的，那么主键是考生实体的主键考生号，并上试卷实体的主键试卷号

• 主键相同的关系模式可合并
  模式合并：
  一个1:1联系转换的表可与任一端实体对应的表合并
  一个1:N联系转换的表可与N端对应的表合并

例1：1:1联系转换
在班级辅导员的E-R图中，有两个实体“班级”和“辅导员”，分别转换为班级表和辅导员表，主键分别是“班号”和“教工号”，该E-R图中还有一个联系是“担任”，对应转换为担任表，联系自己的属性是“担任年月”，表“担任”的属性包括班级实体的主键“班号”以及辅导员实体的主键“教工号”，由于联系“担任”是1:1的，担任表的主键可以与实体班级的主键相同，为“班号”，也可以与实体辅导员的主键相同，为“教工号”，二者选其一。

由于主键相同的关系模式可以合并，担任表可以和班级表合并，当然，跟辅导员表合并的情况也是类似的
例2：1:N联系转换
在考官属于院系的E-R图中有两个实体考官和院系，分别转换为考官表和院系表，主键分别是考官号和院系号，E-R还有一个联系“属于”，对应转换为属性表，联系没有自己的属性，表“属于”的属性包括考官实体的主键“考官号”以及院系实体的主键“院系名”。由于联系“属于”是1:N的，主键与多端实体考官的主键相同，也就是考官号，由于主键相同的关系模式可以合并，属于表和考官表可以进行合并

第四节 扩展E-R图及其转换

基本E-R图已经能够满足许多实际应用当中数据建模的要求，但是，现实世界中还存在一些现象，需要对E-R图进行扩展，通常有两个方面的扩展：弱实体和父子实体
与弱实体相对，前面我们提到的实体都可以成为强实体。

弱实体：
如果一个实体对另一个实体（称为强实体）具有很强的依赖性，而且该实体主键的一部分或全部从其所依赖的强实体中获得，则称该实体为弱实体。换句话说，所有属性都不足以形成主键的实体称为弱实体，其属性可形成主键的实体称为强实体。
E-R图中，弱实体用双线矩形框表示，与弱实体关联的联系，用双线菱形框表示，弱实体与联系间的联系也画成双线，弱实体集的标识属性用虚下划线标明

举例： 对一般学校来说，我们主要只关注学生，但在需要的时候，我们还是会给家长联系，那么涉及家长的通常也是说，因为学生管理上的需要，所以在学校这个数据库当中，学生是数据库当中关注的重点和要点，对于家长，通常的数据库只是把它作为一种附属的信息进行存储，若学生已毕业，家长的信息就可以删除了，像这样我们就可以把家长作为一个弱实体，家长就是一个依赖于强实体学生的弱实体。

弱实体E-R图向关系模式的转换：

• 一个关联弱实体的联系和弱实体一起转换为一个关系表
• 弱实体属性集、关联弱实体的联系的属性集、依赖的强实体主键，并集就是表的属性集
• 如果弱实体在联系的基数约束上处于多端，主键是参与联系的强实体主键并上弱实体标识属性
• 如果弱实体在联系的基数约束上处于1端，主键是参与联系的强实体主键

考生家长E-R图转换为两个表，除了考生表，另一个就是弱实体家长和联系一起转换成的家长表。
家长表包括弱实体的所有属性与强实体的主键的并，由于一个考生会有多个家长，一个家长也可能会有多名考生，考生号和家长姓名的并是家长表的主键

关于父子实体的设计，有一般化和特殊化两种方法

• 一般化：

假设洪水事件实体有属性：编号、名称、经济损失、受灾人口、淹没面积
地震事件实体有属性：编号、名称、经济损失、受灾人口、震级
公共卫生事件实体有属性：编号、名称、疫情描述

这三个独立的实体集中，洪水和地震都有属性 编号、名称、经济损失、受灾人口，综合洪水和地震的共同属性，我们可以设计一个父实体：自然灾害，让自然灾害有编号、名称、经济损失、受灾人口这四个属性，而洪水仅保留自己特有的属性 淹没面积，地震保留自己特有的属性 震级

自然灾害和公共卫生都有属性编号和名称，我们可以综合自然灾害和公共卫生，设计父实体突发事件，突发事件拥有共同属性 编号和名称，自然灾害保留属性经济损失、受灾人口，公共卫生保留属性 疫情描述


像这样根据实体间具有的共同特征，将多个实体集综合成一个较高实体集的过程称为一般化

高层实体集和底层实体集也称作父实体集和子实体集，在E-R图中，父实体集和子实体集之间的联系称为“父子联系”，通过标记为ISA的三角形构件来表示。

ISA的意思就是：是一个什么

一般化是一种自底向上的方法，特殊化是自顶向下的方法

特殊化：
比如，初始时有一个实体集突发事件，有编号、名称、经济损失、受灾人口、淹没面积、震级、疫情描述七个属性。

实际上我们大家都知道，突发事件有多种，只有公共卫生才有疫情描述，只有自然灾害才有经济损失、受灾人口、淹没面积、震级，也就是说，突发事件可以划分为以下两类，自然灾害和公共卫生，突发事件都有编号和名称，公共卫生事件实体集通过属性疫情描述进一步刻画，而自然灾害实体集通过属性经济损失、受灾人口、淹没面积、震级进一步描述。

自然灾害也有多种，只有地震才有震级，只有洪水才有淹没面积，也就是说自然灾害进一步划分为地震和洪水，自然灾害都有经济损失、受灾人口属性，地震通过震级进一步描述，洪水通过属性淹没面积进一步描述

像这样，根据实体间的区别在实体集内部进行分组的过程，我们就称为特殊化。
特殊化从单一的实体集出发，通过创建不同的低层实体集，来强调同一实体集中不同实体集间的差异。一般化是在多个不同实体集的共性基础上将它们综合成一个高层实体集。一般化和特殊化互为逆过程，在实际应用中可以配合使用。

父子实体的继承关系

• 通过特殊化或一般化得到的父子实体集，高层实体集的属性被低层实体集继承
• 低层实体集继承其高层实体集所参与的那些联系
• 低层实体集所特有的属性和联系仅仅适用于某个特定的低层实体集

用表表示父子实体集
有以下两种方法:
方法一：

• 为高层实体集创建一个表；
• 为每个低层实体集创建一个表，并加入高层实体集主键属性

比如，为高层实体突发事件创建一个突发事件表，属性是事件编号、事件名称，其中事件编号是主键；为低层实体自然灾害创建一个自然灾害表，属性是经济损失、受灾人口，然后继承高层实体的主键；为低层实体公共卫生事件创建一个公共卫生事件表，属性是疫情描述，然后继承高层实体的主键

方法二：

• 如果每个高层实体肯定会对应于某个低层实体，并且只会对应于一个低层实体，则只需为每个低层实体集创建表

比如，假定数据库只管理公共卫生事件和自然灾害的数据，其他的突发灾害事件一概不管，并不可能有一个事件既是公共卫生事件又是自然灾害，这时就没有必要为高层实体突发事件单独创建表，而只需为每个低层实体集创建表，那么这种情况下E-R图转换为关系模式的结果为两个表

第五节 大数据E-R图及其转换

传统数据库系统主要针对事务性应用，比如火车票的买卖、股票交易等，由于受到存储等资源的限制，过往业务数据都通常不被重视，更多关注支持业务的数据当前状态，随着大数据浪潮的兴起，人们意识到数据就是财富，对大尺度时间里的过往数据的积累和分析，挖掘潜在的知识，价值可能会很大，这也对数据库提出了新的要求。

假定借还书系统包括两个实体，读者和图书。图书状态T表示图书已经被还回，可以供其他读者借阅；图书状态F表示图书已经被借出。E-R图可以转换为下面两个表。

图书表当中每本书只能有一个元组，每当一本书被借出或者还回的时候，我们都只能用update语句更新这一本书的图书状态读者号、借书时间的属性值。这个时候的数据库，已经能够存放每一本书最后一次借阅信息。
对图书管理基本业务已经足够，但是想分析每本书的利用率，或者分析读者的兴趣，以便于更有针对性的预定新书，因为这些分享需要访问每本书的历次借阅信息，这时数据库无能为力。
解决方法：
把E-R图当中的联系可以改为M:N。按照转换规则，E-R图可以转换为三个表，其中读者号和ISBN一起是借阅表的主键。也就是说，数据库可以保持一个读者借阅多本书、一本图书被多个读者历次借阅的信息

由于读者号、ISBN一起是借阅表的主键，虽然不同读者多次结束的信息可以保存在数据库中，但对同一个读者多次借阅同一本书时，数据库当中只能保存最后一次借阅信息

考虑到同一个读者多次借阅同一本图书对读者兴趣分析非常重要，我们可以进一步改进E-R图。
为了区分同一个读者多次借阅同一本图书，只能用“借阅时间”来标识，这个时候我们把借阅时间作为联系借阅的标识属性，当给定借阅时间时，一个读者可以同时借多本书，而一本书只能借给一个读者，借阅是读者和图书的1:N联系，这时E-R图转化为下面三个表。

注意：因为联系借阅有标识属性借阅时间，借阅表的主键需要加上借阅时间，也就是说ISBN和借书时间一起是借阅表的主键。尽管这时候的借阅联系是一个1:N的联系，但是由于联系的标识属性的影响，借阅表和图书表的主键并不相同，也就是说这两个表不能合并

现在的数据库可以保存一本图书被多个读者历次借阅、同一个读者多次借阅同一本图书的信息。
但是同一本书，图书馆往往会购买多个版本，即使给定借书时间，同一ISBN的图书也可以借给多个读者，需要作出进一步改进：
把联系借阅作为M:N的联系，并且有标识属性“借书时间”，这时E-R图转换为如下：

因为联系借阅是M:N的，而且有标识属性借阅时间，借阅表的主键就是读者表的主键，加上图书表的主键，加上借阅时间，三个一起是借阅表的主键

第七章 数据库设计：属性-联系方法

第一节 函数依赖

数据依赖的定义：
数据库中所保存的数据值是对现实世界状态的反映，无论现实世界的状态如何变化，一个关系模式中不同属性在取值上总会存在相互依赖又相互制约，这种属性与属性之间的联系，称为数据依赖。
数据依赖是属性-联系数据库设计方法的基础。

数据依赖的种类：
数据依赖有多种，比如函数依赖、多值依赖、联接依赖等等，具有实用价值、最重要的数据依赖是函数依赖。



设考官表examiner有属性erid，ername，ersex，erage，dname，主键是erid，分别代表考官号、姓名、性别、年龄和所在院系名。在这个关系模式上就有函数依赖4个：

假设有一个关系模式，名字叫考官院系，有三个属性分别是考官号、考官院系名、考官院系办公地点。
其中每个考官只在一个院系工作，所以考官号——>考官院系名
每个院系只有一个办公地点，所以考官院系名——>考官院系办公地点
由于每个考官只在一个院系工作，每个院系只有一个办公地点，所以考官名——>考官院系办公地点

也就是说，我们能从考官名——>考官院系名，考官院系名——>考官院系办公地点，两个函数依赖一起推导出考官名——>考官院系办公地点

逻辑蕴涵
给定关系模式S的函数依赖集D，可以证明其它一些函数依赖也成立，就称这些被证明成立的函数依赖是被D逻辑蕴涵

闭包
给定关系模式S的函数依赖集D，D逻辑蕴涵的所有函数依赖的集合称为D的闭包，记作$D^{+}$

实际中经常需要判断一个函数依赖是否被一个函数依赖集逻辑蕴涵，或者求一个函数依赖的闭包，像这样从一些已知的函数依赖，推导另外一些函数依赖都需要一套推理规则。
Armstrong进行了总结，提出把其中三条作为公理，这就是著名的Armstrong公理
 
Armstrong公理的三条推理规则：
1.反射律

2.增广律

3.传递律


可以证明Armstrong公理是正确的、完备的。Armstrong公理的正确性保证推出的所有函数依赖是正确的；完备性保证可以推出所有被蕴含的函数依赖、换句话说，Armstrong公理能够保证函数依赖推导的有效性和可靠性。

实际中有三条非常实用的推论，很许多时候使函数依赖的推导更为方便
Armstrong公理三条实用推论： 合并规则、分解规则、伪传递规则
1合并规则

2分解规则

3伪传递规则

考虑到函数依赖闭包的求解时间复杂度很高，实际当中经常用属性闭包。

属性闭包
设D为属性集A上的一组函数依赖，α是A的子集，属性集α关于函数依赖集D的闭包写作${\alpha }_D^{+}$。
α关于函数依赖集D的闭包就是能由D根据Armstrong公理推导出的左部为α的所有函数依赖的右部组成的集合，也就是能够由D根据Armstrong公理推导出α能够函数决定的所有属性的集合。

根据Armstrong公理及其推论、以及属性闭包的概念，对S <A, D>


步骤：
用N保存闭包计算结果，首先将N初始化为M，即N包括属性{A_1,A_2,…,A_k}。
在D中反复寻找这样的函数依赖：B——>C，其左部B是N的子集但C不是N的子集，如果找到了这样的B——>C就把右部C并入N中，并重复这个过程。
当最终再也不能添加任何属性时，集合N就是M关于D的闭包。

例：计算属性闭包。
对关系模式S<A,D>的属性全集，D是A上的函数依赖集，假设A={a,b,c,g,h,i} 6个属性，D是A上的函数依赖集，计算ag关于D的闭包。

左边列出所用到的函数依赖，右边给出每一步属性闭包计算结果。要计算ag关于D的闭包，首先把ag加入结果集中；
对a——>b，a已经在结果中而b不在，所以将b加入到结果中；
对a——>c，a已经在结果中而c不在，所以将c加入到结果中；
对cg——>h，cg已在结果中而h不在，所以将h加入到结果中；
对cg——>i，cg已在结果中而i不在，所以将i加入到结果中；
现在再也无法往结果中加入新的属性，所以(ag)关于D的闭包就是agbchi

Armstrong公理及其推论能够推导出函数依赖的闭包，有可能不同函数依赖集的闭包是相等的，如果G的闭包和H的闭包相等，则我们称G与H等价

H与G等价就意味着H中的每个函数依赖属于G的闭包，并且G中的每个函数依赖属于H的闭包。

既然多个不同的函数依赖集可以是等价的，那么我们能否只关注等价函数依赖集中的最精简者？答案是肯定的。
如果函数依赖集D满足下列条件，则称D为一个极小函数依赖集，也称最小函数依赖集

• D中任意函数依赖的右部仅含有一个属性

• D中不存在这样的函数依赖M——>N，函数依赖左部M有真子集Q使得D-{M→N}∪{Q→N}与D等价。 换句话说，D中任意一个函数依赖的左部替换为其任意一个真子集后都不再与原始的D等价，简单地说，就是D中每一个函数依赖的左部都不包含多余的属性

• D中不存在这样的函数依赖M——>N，使得D-{M→N}与D等价。简单地说，就是D中不包含多余的函数依赖。

把以上三点总结起来，就是每个函数依赖左部不包含多余属性，右部只包括单个属性，整个函数依赖集不包含多余的函数依赖。
满足这三个条件的函数依赖集，我们就称其为最小函数依赖集或极小函数依赖集

事实上，每一个函数依赖集都有等价的极小函数依赖集$D_m$，极小函数依赖集的定义正好给出了计算一个给定函数依赖集D的极小函数依赖集方法。
（1）逐一检查D中每一个函数依赖，对右部包括两个或两个以上属性的函数依赖运用分解规则进行分解。比如，假设M→N，右部N包括属性$A_1,A_2,\dots ,A_k,k\ge 2$，也就是说，M→N的右部N包括两个或两个以上的属性，则用
（2）逐一检查D中每一个函数依赖，对左部包含两个或两个以上属性的函数依赖，如果左部的一个子集的属性闭包等于右部，就把左部替换为该子集。比如M→N，M包括多个属性$B_1,B_2,\dots ,B_m$，这个时候我们就逐一的考察$B_i$，如果$M-B_i$关于函数依赖集D的闭包等于N，那么我们就用$M-B_i$取代M
（3）逐一检查D中每一个函数依赖，如果去掉一个函数依赖后，与原来的函数依赖集是等价的，就去掉该函数依赖。比如M→N，如果D-{M→N}与D等价，也就是说M关于D-{M→N}的闭包也是N，则从D中去掉此函数依赖

例：计算最小函数依赖集的例子。
计算D={a→b,b→a,b→c,a→c,c→a}的极小函数依赖集。
方法一：
D中的5个函数依赖每个的左部和右部都是单个属性，所以只需判断是否有多余的函数依赖。由于b→c和 c→a能推导出b→a，所以去掉b→a后与原来的D是等价的。剩下的四个函数依赖当中a→b和 b→c能推导出a→c，所以去掉a→c后与原来的D是等价的。再没有类似的情况，也就是说再也没有办法去掉其中的函数依赖，这样就得到了D的一个极小函数依赖集

方法二：
在D包含的5个函数依赖中，b→a和 a→c能推导出b→c，所以去掉b→c后与原来的D是等价的。再没有类似的情况，也就是说再也没有办法去掉其中的函数依赖，这样就得到了D的另一个极小函数依赖集，包括四个依赖。

启示：

• 一个函数依赖集的极小依赖集不唯一；
• 一个函数依赖集与其极小依赖集以及其闭包都是等价的

在关系模式S(A)中，对于A的子集$A_i$ 和$A_j$，如果$A_{(}i)\to A_j$，但$A_j不是A_i$的子集，则称$A_{(i)}\to A_j$是非平凡的函数依赖；如果$A_{(i)}\to A_j$，但$A_j$是$A_{(i)}$的子集，则称$A_{(i)}\to A_j$是平凡的函数依赖。

比如考官表中，erid→ername是非平凡的函数依赖，erid→erid、(erid,ername)→erid都是平凡的函数依赖。除非特别说明，一般总是只考虑非平凡的函数依赖。

第二节 模式分解

模式分解是属性-联系数据库设计方法的基础。
关系数据库设计的属性-联系方法，就是把需要数据库保存的所有属性放在一张关系表中，进而基于数据依赖来优化这个模式，得到期望的结果，这一过程的基本操作就是模式分解。

当i,j=1,2,……,n时不存在
$D_i$是D的闭包当中左部和右部都属于属性Ai的函数依赖
${\pi }_{(S_i)}(t)$表示t在Si上的投影

模式分解有两个方面的特性需要关注：
1.是否无损联接
2.是否保持依赖

有的模式分解是无损联接的，而有的不是。
比如有关系模式考官院系erdepa (erid, dname, dsum) /（考官号，考官所在院系，考官所在院系总人数），由于每个考官只在一个学院工作，这个模式上的函数依赖D={erid→dname,dname→dsum}

假设这个表有四个元素，如果把模式分解为idsum(erid, dsum)和dnamesum(dname,dsum)后，
把这个模式分解为这样两个子模式后，分解后得到的两个表也分别投影得到四个元组，在分解后的两个表上执行SELECT * FROM idsum NATURAL JOIN dnamesum，查询结果是八个元组，会比原来没有分解的表erdepa多出四个元组。这里元组的增多意味着信息的丢失，这种模式分解方式就不是无损连接的分解。

如果把该模式分解为idsum(erid,dsum)和idname(erid,dname)后，得到的两个表也分别投影得到四个元组，在分解后的两个表上执行SELECT * FROM idsum NATURAL JOIN idname，查询结果也是四个元组，与分解前原始表中的信息完全相同，而且不管原始表中的数据如何变化，分解后两个表的自然联接的结果都与原来表中的内容保持一致，这种模式分解就是无损联接的分解。


可以用追赶算法来检验一个分解是否无损联接。
追赶算法：

追赶算法示例
首先构造一个五行五列的表，每行依次用分解的子模式{ad, ab, be, cde, ae}来标记，每列用S的属性(a,b,c,d,e)来标记，在每个子模式标记行中对应属性出现的列上填写x并且下标为列号。
比如在ab行a列填，在每个子模式标记行中对应属性没有出现的列上填写y并且下标为行号和列号。
比如在ab行c列填，以此类推。然后反复考察D中的每个函数依赖，并修改表中的元素。

a列的一、二、五行相同；c列的一、二、五行同为y则一起改为行号最小的

b列的二、三行相同，c列的二、三行同为y则一起改为行号最小的

c列的一、二、三、五行相同，d列的一、二、三、五行第一行为，所以d列的二、三、五行同改为x4

d和e列在三、四、五行都相同，c列的三、四、五行第四行为x3，c列的三、五行一同改为x3

c和e列在三、四、五行都相同，a列的三、四、五行第五行为x1，a列的三、四行一同改为x1

这个时候我们会发现表里面的第三行已经全部变成了x1, x2, x3, x4, x5，即全都是x，所以这个分解具有无损联接性。

我们能够用追赶算法可以判断关系模式的任意一个分解是否是无损联接。如果是把一个模式分解为两个模式，那么使用下面的定理会更方便：

可以这样来直观的理解这个定义：
它的意思是说，把一个关系模式分解为两个关系模式时，分解具有无损联接性当且仅当两个关系模式的公共属性是其中一个模式的键。

D+中左部和右部都属于那些函数依赖，如果D等价于i=1…n所有Di的并，称关系模式S的这个分解是保持函数依赖的。
利用触发器等机制能将函数依赖定义为数据库中的完整性约束，从而减少或解决一些数据异常。

保持依赖：
如果某个分解能保持函数依赖，那么就可以在分解后的模式上定义等价的完整性约束，在数据输入或更新时，要求每个函数依赖被满足，就可以保证数据库中数据的语义完整性，显然这是一种良好的特性。

如果某个分解不能保持函数依赖，则分解后的模式利用函数依赖约束来保护数据完整性的能力将会被削弱

分解是否保持依赖示例
我们假定将关系表考官院系erdepa(erid, dname, dsum)这个表上的函数依赖集D，包括两个函数依赖，分解为两个关系模式，由于erid——>dname无法从erid——>dsum和dname——>dsum推导出来，该分解不保持函数依赖

把该模式分解为dnamesum(dname, dsum)，这个分解保持函数依赖

无损联接和依赖保持是两个互相独立的标准，具有无损联接性的分解不一定能够保持函数依赖，同样保持函数依赖的分解也不一定具有无损连接性。
模式分解有可能属于以下四种情况之一

第三节 范式

同一关系模式中属性之间往往亲疏不同，数据依赖反映的是关系模式中不同属性在取值上的相互依赖、相互制约，这种依赖和制约有完全的也有部分的、有直接的也有间接的。

完全和直接意味着属性之间的依赖和制约性更强，属性之间的联系更亲密、更亲近；
部分和间接则意味着属性之间依赖和制约性更弱，属性之间的联系更松散、更疏远。

用函数依赖具体表示有以下几个概念：

• 完全依赖
  比如考官表中，erid——>ername、erid——>ersex都是完全函数依赖
  

• 部分依赖
  比如考官表中(erid, ername)——>erage、(erid, ername)——>ersex，都是部分函数依赖
  

• 传递依赖
  比如考官表中，考官只在一个院系工作，每个院系只有一个办公地点，一个院系有多个考官
  
  
  同一关系模式中属性之间不仅亲疏不同，往往不同属性在关系模式中的地位也不同。比如键，包括超键、候选键、主键在关系模式中具有决定性地位，是关系模型中非常重要的概念，可以基于函数依赖给出这些概念的另一种形式的定义：
  

候选键中的属性，称作主属性；不包含在任何候选键中的属性称为非主属性

候选键在关系模式中处于决定地位，与其他属性有可能亲疏不同，完全决定和直接决定意味着更强的决定力，而部分决定和间接决定意味着更弱的决定力。
正如管理学中职权越明晰执行力越强一样，属性分组使每个模式中候选键的决定力越纯粹统一，则对事务处理的支持越强。反之，正如管理学中广泛参与的座谈会是获取各方信息的有效途径一样，如果把不同亲疏度的属性集中放在一个组里，则有利于及时查询全面的信息。

根据模式中候选键决定力的纯粹性级别，将模式分为不同的六种范式。
第1范式一般写成$1NF,2NF,3NF,BCNF,4NF,5NF$等等
如果关系模式S是第n范式的，通常简写为$S\in nNF$

每一级范式都是在其前面第一级范式的基础上附加上新的限制条件，所以这些范式之间的关系为1NF包含2NF包含3NF…

实际当中常用的是3NF和BCNF

• 第1范式
  如果关系模式S的每个关系的每个属性值都是不可分的原子值，称S是第一范式的模式。（存在非主属性部分依赖于S的候选键）
  关系数据库只研究满足1NF的关系，1NF是关系模式应具备的最起码的条件。
  在1NF中所有域都是简单的，并且在一个简单域中所有的元素都是原子的。

• 第2范式
  如果关系模式$S\in 1NF$，且每一个非主属性都不部分依赖于S的任何候选键，则$S\in 2NF$。
  

• 第3范式
  如果关系模式S<A,D>是1NF，且每个非主属性都既不部分也不传递依赖于S的任何候选键，那么称S是第三范式（3NF）。
  比如有关系模式，候选键为“考官号”，由于“考官号——>院系名”和“院系名——>院系总人数”，存在考官号传递决定院系总人数是非主属性院系总人数对候选键考官号的传递依赖。
  如果把该报考模式分解为两个模式，它们都属于第三范式
  

对于BCNF可以从三个不同角度来进行定义

• BC范式
  
  (第三条，键=候选键)

所以“(研究生号，导师号)——>院系名”是一个主属性对候选键的部分依赖，这说明研究生导师模式没有非主属性，虽是第三范式但不是BC范式
如果分为两个关系模式，它们都属于BC范式

第四节 规范化

传统关系数据库主要面向事务性业务系统，诸如铁路、航空等票务系统、银行贷款管理系统，这些系统都是“写中心”式的。
属性分组使候选键的决定力越纯粹统一，模式达到的范式越高，则对事务处理的支持越强。

规范化:
一个较低范式的关系模式，依据其中的数据依赖、通过模式分解可以转换为高范式关系模式的集合，这个过程称为规范化。

关系模式规范化的模式分解过程，把逻辑上相对独立的信息放在独立的关系模式中。
关系模式规范化实际上就是一个模式分解过程：把逻辑上相对独立的信息放在独立的关系模式中。
把较低范式的关系模式分解为较高范式的关系模式的方法不是唯一的，只有保证分解后的关系模式与原关系模式等价，分解才有意义。

模式分解具有无损连接性和保持依赖性，无损联接的分解不一定能够保持依赖，同样保持函数依赖的分解也不一定是无损联接的。
如果仅仅要求分解具有无损联接性，那么一定能够达到BCNF。
如果要求分解既具有无损联接性，又具有保持依赖性，则一定能够达到3NF，但不一定能够达到BCNF。

达到BCNF无损连接分解算法:

反复检查当中的每一个关系模式，一直到其中再也没有不属于BCNF的模式为止。这个算法能够保证把一个关系模式无损联接的分解成BCNF，但不一定能保持函数依赖。

无损联接分解关系模式以达到BCNF的例子
研究生号、导师号、院系名都是主属性，存在“导师号——>院系名”，所以“（研究生号，导师号）——>院系名”是一个主属性对候选键的部分依赖，这说明研究生导师模式不是BCNF

为了将该模式无损联接地分解为BCNF，首先要初始化，之所以该模式不是BCNF，是因为D中函数依赖“导师号——>院系名”的左部没有包含候选键“（研究生号，导师号）”或“（研究生号，院系名）”。
将该函数依赖左右两端的属性一起作为一个新的模式并给起名“导师院系”，将研究生导师模式中的属性“院系名”移去后剩下的属性成为一个模式并给起个名字“师生”。这样分解后包括导师院系模式和师生模式，导师院系有函数依赖，师生模式的函数依赖集中没有非平凡的函数依赖，此时导师院系和师生两个关系模式都已经达到BCNF，分解结束.

无损分解且保持依赖地分解成3NF的算法:

能保证分解既是无损联接的又是保持函数依赖

考官院系不是BCNF，为了保证分解无损联接和保持函数依赖，
首先求解该模式上的函数依赖集的极小依赖集（一共三个依赖）；
合并左部相同的函数依赖得到第三行；
分别将这两个函数依赖的两端出现的属性作为单独的模式，并给起上合适的名字就得到两个模式（第四行），原模式“考官院系”的候选键“考官号”已经出现在刚刚产生的“考官”模式中。
总共分解为两个模式，分解结束。

有关系模式报考，它仅仅是1NF，为了将该模式无损联接且保持依赖地分解以达到3NF
首先求解该模式上的函数依赖集的极小依赖集，并合并左部相同的函数依赖，得到第二行；
分别将这两个函数依赖两端出现的属性作为单独的模式并给起上合适的名字，得到第三行；
原模式“报考”的候选键“（报考号，试卷号）”没有出现在改刚刚产生的两个模式当中，所以单独作为一个模式并且一个合适的名字，总共分为三个模式（第三行）；
分解结束。

第五节 大数据与反规范化

关系数据库设计的属性联接方法，就是把需要数据库保存的所有属性依据它们之间的数据依赖来进行分组。
同一个数据库，设计达到的范式越高，结果表的个数越多。
同一个数据库，较低范式的形式和较高范式的形式之间，在无损联接和保持依赖方面可以是等价的。

什么时候选择更高范式？什么时候选择更低范式？
1不同级别范式在数据写操作上的表现：
关系模式“报考试卷（eeid, eid, ename, erid）”
达不到第二范式，而只是第一范式

如果除了218811011116号考生报考大学外语还有1000人报考了，那么试卷名和试卷考官号会伴随试卷号02100000001还重复存储一千次，这种相同信息的重复存储称为冗余。
假设大学外语组卷考官号出现变化，如果有1000个考生报考大学外语，就意味着要修改1000次，这种现象称为修改复杂。
当需要将一门新的尚未有考生报考的试卷加入数据库中时，由于eeid属性值为空、主键不完整，从而无法插入，这种现象称为插入异常。

对目前只有一个考生报考的试卷，如中国近现代史纲要，如果该学生要退报，在删除报考信息时会删除整行元组，连同中国近现代史纲要试卷信息一起被删掉，这种现象被称为删除异常。

假设把同样的信息保存在两个模式中，可以看到这两个模式都是BC范式的，是原来“报考试卷（eeid, eid, ename, erid）”模式的无损联接和保持依赖的分解。现在有218811011013号考生报考了史纲和外语，如果又有考生报考外语，则仅在“报考”中加入一个新行，无论有多少考生报考外语，大学外语的试卷名和组卷考官号仅在“试卷（eid，ename，erid）”中存储一次，没有了冗余，假设大学外语组卷考官号出现变化，无论有多少考生只需修改一处，也就是无修改复杂问题。

当需要将一门新的尚无考生报考的试卷加入数据库中时，只需在“试卷”中插入一行，和有没有考生报考没有关系，也就无插入异常。

对于目前只有一个考生报考的试卷比如中国近现代史纲要，如果该考生要退报，只需在“报考”中删除相应报考信息的行，完全与“试卷”中的行无关从而也就无删除异常。

不同级别范式在查询操作上面的表现：
如果现在要执行的操作不涉及对数据进行修改，只是查询数据库中的内容，比如查询所有考生报考试卷详细信息。
查询某些考生报考试卷详细信息，对于仅属于1NF的关系模式“报考试卷（eeid，eid，ename，erid）”，可以直接从表中选择相应的行输出（直接查询输出）；

对于属于BCNF的关系模式“报考（eeid，eid）、试卷（eid，ename，erid）”，查询所有考生报考试卷详细信息或查询某些考生报考时间详细信息，需要先对报考（eeid，eid）和试卷（eid，ename，erid）进行自然联接，然后从中选择相应的行输出。
数据库实际应用中发现，联接运算非常耗时，是关系数据库中最关键的性能瓶颈之一。

这些例子体现出的现象具有普遍性。
如果关系模式的规范化程度越高，优势在于数据冗余、插入异常、删除异常、修改复杂等问题越少。当一个应用的查询中经常涉及到两个或多个关系模式的属性时，系统必须经常地进行联接运算，而联接运算的执行代价是非常高的。劣势就在于查询效率越低。
如果关系模式的规范化程度越低，优势在于可以减少查询所要联接表的个数，减少I/O和CPU时间，提高查询效率。但是劣势在于数据冗余造成的空间代价以及插入异常、删除异常、修改复杂等问题

较高的范式更适合以写为中心的系统，而较低的范式更适合以读为中心的系统。

在设计数据库模式结构时，必须对现实世界的实际情况、用户应用需求以及数据特征做进一步的分析，确定一个合适的性能和冗余的折中处理。
对于证券交易银行收票系统等事务性应用，一般强调规范化设计，在数据库设计中最常用的是3NF和BCNF。
对社交平台、搜索引擎等互联网应用，通常用户数量极大、分布极广，内容通常是一次追加多次读，这种以读为中心的数据分析型应用，即使采用关系模型也是常采用低范式或对高范式的设计反规范化，甚至更有甚者完全不采用关系模型，以便提高性能。

Google的BigTable虽然沿用了一些关系数据库的术语，但它是非关系型的，它允许属性值可以是一个表，对并不使用关系模型的大数据存储系统，仍可先按关系模型设计然后再转化为相应的数据模型，这样就可以充分借鉴利用关系模式分析方法来知道模式设计。
具体做法：

• 根据数据分析和用户的需求确定每一实体内各属性的相互制约，即依赖关系，并表示成函数依赖。
• 用实体的主键代替相应的实体，将实体之间的联系表示成函数依赖。
• 用属性-联系方式分析函数依赖集。
• 根据应用中数据访问特征，设计数据存储模式，对以读为主的表常采用低范式或非关系模式，并在应用程序中以适当方式处理数据冗余及其带来的问题。

不能简单地说数据冗余就不好，因为数据冗余有利有弊。不能简单地说关系模式满足的范式级别越高越好，因为高范式和低范式各有千秋。在设计数据库模式结构时，必须对现实世界的实际情况和用户应用需求作进一步分析，以选择一个合适的规范化和冗余的折中处理。

第八章 大数据技术

第一节 大数据及其特征

大数据的时代已经来临。大数据的大是相对而言的，是指数据规模大到无法通过当前主流技术工具以可接受的质量完成数据的存储、处理、传输、管理或分析，需要研究新的相关关键技术。
目前典型大数据应用中的数据与传统技术面对的数据显著不同，体现出独有的特征。

大数据的特征：

• 数据量大。当今世界需要进行及时处理以提取有用信息的数据，数量级已经从TB级别跃升到PB乃至EB级别
• 数据类型多样。大数据的挑战不仅是数据量大，也体现在数据类型的多样化。除了普通关系型数据，还有网络日志位置相关信息等半结构化数据以及音频视频图片等非结构化数据。很多大数据应用以半结构化数据或非结构化数据为中心。
• 数据产生和需要处理的速度快。许多大数据应用中数据高度动态、随着时间快速变化，比如股票价格、拍卖竞价、检测数据等都是“短暂有效”的，也就是只能在一定的时间范围内有效，过时则对决策或推导无意义，另一方面应用活动有很强的时间要求，需要对数据处理的速度快。
• 价值高而密度稀疏。大数据当中可能蕴涵极富商业价值的信息，但是价值密度很低，只有将大量数据聚合起来分析，才能借助历史数据预测未来走势。

不同大数据应用中的数据在这四个方面中的一个或多个与传统技术面对的数据表现出显著不同，由于这四个特征的核心词都是以字母V开头，所以常说大数据的特征是4个V。

平时我们对大数据的谈论往往并非仅仅指数据本身：通常特别指的是大数据技术，尤其在计算机、互联网、信息领域。
大数据技术的目标是简单高效安全的共享大数据

大数据技术的关键需求：

• 可伸缩性。能够有效处理越来越多的数据和越来越多的访问
• 可靠性。能够容忍实际合理的故障
• 高效性。能够满足应对数据处理性能的要求

目前大数据技术主要涉及存储和处理两方面
大数据关键技术

• 利用分布式技术实现对结构化、半结构化和非结构化海量数据的可扩展、容错、高可用地存储。
• 利用分布式并行编程模型和计算框架，实现对海量数据的处理和分析。

在大数据存储方面，主要涉及的关键技术包括：计算机集群，分布式文件系统，NoSQL数据模型；在大数据处理和分析方面，主要包括：MapReduce批处理，Storm流计算

第二节 大数据存储技术

由于大数据的数据规模远远超过单节点存储能力，通常在计算机集群中使用分布式文件系统来存储。
以前为了提高计算或存储能力通常不断提高CPU、内存、外存的性能与容量，采用多处理器或拥有高级专业硬件的并行计算机系统，这种方式称为垂直扩展。
与早前垂直扩展不同，目前面对大数据挑战，总是采用水平扩展的方式，也就是由普通商用计算机构成计算机集群，需要时不断扩展集群规模，这种方式可以大大降低硬件成本。实际中，成千上万的计算机节点构成计算机集群，分布式文件系统，把文件分布存储到这些节点上。
据估计，像Google、Amazon、Microsoft等跨过公司都拥有数百个数据中心，分布在全球不同国家和地区，每个数据中心安放若干个计算机集群。一个集群通常由40个或更多机架，以及两个提供与其他数据中心或互联网联接的高带宽交换机组成。每个机架上可以放置40或80台计算机，每个机架也有两个交换机，配备两个交换机的好处是即使其中一个交换机出现故障，集群仍能正常工作。

当前大数据应用中的分布式文件系统，通常都采用主从结构，物理上包括计算机集群中的多个节点，这些节点分为两类，一类叫“主节点”或者是“名称节点”；另一类叫“从节点”或“数据节点”，通常将文件存储在固定大小的块中，Google GFS每个块的大小是64MB。
主节点的作用是管理有关文件系统的元数据，包括名字空间、访问控制和每个文件到相关数据块集合的映射。
访问数据时，客户端首先将其请求发送给主节点，主节点回复相应的块标识和副本位置，然后客户端可以缓存，并利用这些信息从相应副本的数据节点访问数据。

为了应对可能随时发生的故障，文件的每个块都在不同节点上存有多个副本，当面对数据修改，需要分布式并发控制、提交和恢复机制来维护多个副本间的一致性，归根结底需要一个异步系统中的分布式共识协议，比如Google中并不完美但够实用的Paxos协议，就是这一领域的领导者。
当前，典型大数据应用要求数据访问性能高，灵活性强，期望给定key值后容易映射到各种数据值，但通常不强调数据的强一致性。
典型的NoSQL系统可以分为四类：键值存储系统、列族存储系统、文档存储系统、图存储系统

1.键值存储系统： 就是一个通过键来访问对应存储值的哈希表。
类似有两列的表，一列对应特定的“键”Key，另一列对应“值”value，value可以是任意类型，如字符串、数值甚至集合等复杂对象时，并没有直接访问内部特定部分域的原生方法，键值存储系统中，可以按照Key值来存储和提取value原子值，也就是给定一个“键”，返回一个“值”，但不能通过“值”来获取一个“键”。
键值存储系统的主要操作：

• get：实现按“键”找“值”
• put：在不存在“键”的情况下插入一个键/值对，否则为这个“键”更新“值”
• delete：删除一个键/值对

键值对用于自然灾害应急预案编著系统的例子

给“键”加了相应的前缀以区分分别存储用户名，左边一列是六个键Key，右边一行是对应的值value，值value中每个元素值前也给出了相应的属性名。当预案编制人员使用用户名“myplan”，和口令“bnu3399”，通过internet登录系统时，系统按照用户名寻找相应键值对，若“bnu3399”与用户口令一致就，登录成功。然后从用户编号中提取用户号“10099”，按照用户号获取相应值，包括用户所有其他信息。如果还需要获取该用户参考了的其他用户信息，从值value中“参考用户"元素中提取出用户号，即可找到相应信息。

注意点： 键值存储系统没有Join操作，如果需要只能编码实现

Key/value模型的优势在于简单、易部署，但通常只适合绝大部分频繁访问的键/值对能够予以缓存的情形

2.列族存储系统：
列族存储系统，用表来组织数据。表中的每一行由行键来标识，一般是一个字符串。每一行标识出若干列簇，列簇包括若干个列,通常通过“列簇名:列限定符”来标识一个列，也就是一个单元格,一个单元格中的数据可以有多个版本。
注意点： 物理上按列簇切分存储，以行键为标识，单元格中的数据按时间戳降序排列。

列族用于存储应急人员信息：

左边第一列是每一行的行键，右边是一个列族，由三个列组成，分别是姓名，性别和电话号码。用户张航电话号码修改过一次，有两个版本，另一个用户有三个版本，列簇存储通过列聚集可以大大减少特定查询的IO行为。另外，由于数据和时间戳的结合，可以有效支持时间相关处理。

3.文档存储系统：
文档存储系统，数据模型是包含键/值集合的文档集合。在一个文档存储中,“值”可以是嵌套文档、序列、数组、标量值，甚至可以是图片、视频等二进制大对象。

一个文档以存储系统能够理解的格式存储数据。
有关用户刘涛在预案编著系统中的JSON格式的信息片段

理论上，存储格式可以是XML、JSON、Binary JSON或者存储系统够理解复文档内部结构的其它任意格式，文档型存储系统在内容管理、博客等领域特别受青睐。

4.图存储系统：
图存储系统使用图作为数据模型,有一个节点集合和表征了节点关系的边集合。
由于图数据库实际存储了边信息，从节点沿相关的边遍历图来处理查询，避免了诸如关系数据库管理系统中的连接等计算开销，通常是一个很有效的方法。
然而遍历一个图，需要在成白万或者上亿的节点中找到起点，因此也离不开好的索引。
注意点： 绝大多数图存储系统都利用特定图数据的特质来改进查询效率。社交网络、电子商务和推荐系统经常使用图数据库系统。
社交网络、电子商务和推荐系统经常使用图数据库系统。
图数据库的例子：

如果要查询考生eeLiu的考生报考的机考科目，处理步骤如下：
从eeLiu节点开始，查找标签为“关注”的所有出边，沿着关注的出边找到eeYang节点，然后从该节点出发，查找所有标签为“报考”的出边，可以找到两个节点，对于它们中的每一个，查找标签为“机考”的边，只有C++符合查询要求。

第三节 大数据计算

大数据计算有多种模式，最常见的是批处理和流计算两种。
流计算，处理的源数据一般是开放的，都是流数据，也称流式数据，是指将数据看作数据流的形式来处理，也就是会有源源不断的数据输入。

经处理提取有用信息后把垃圾丢弃，每一瞬时的数据量并不一定很大，通常可以在内存中处理完成。典型的用户查询，一般涉及一个时间区间里的数据，比如每分钟每小时每天每周每月，它的一些统计数、平均值、最大值、最小值等等，或者是自某时刻开始到当前的状态信息或统计分析等，也就是说流式计算模型处理的是瞬时、有序的数据流，数据即来即处理，并且只要数据流不中断，数据的处理就持续不断的进行

流式计算模型处理
处理过程直接在内存中进行，不涉及对硬盘的访问，只有处理完成后才将结果输出到磁盘。

批处理计算模型如下：

批处理处理的源数据通常是封闭的，一般将需要处理的大批量数据事先存入硬盘，处理的时候再从硬盘中读取数据进行一次性处理，如果有中间结果，—般也会把中间结果写入磁盘再继续后面的处理，因此批处理的I/O操作相对更加频繁。

流计算的代表Storm框架由Twitter公司开源，用于数据量头对实时响应要求高的实时计算场景，它实现了一种实时的数据流处理模型，可以源源不断获取数据流，并且让这些数据流在该模型中流转和处理。

STORM集群架构示意图

Storm流式计算框架结构中包括Spout和Bolt两种组件，Spout用于从外部数据源接收数据，然后将其喷发到拓扑中的相应组件中去；Bolt是处理逻辑，用来完成对数据的处理，它是数据流的处理节点，使用多个Bolt可以完成数据流的多重变换。第一个Bolt用于接收来自Spout的数据流，当第一个Bolt执行完后，数据形式会发生一定的变化，进而产生一个新的数据流，新的数据流可能继续流通到下一个Bolt中，输出到磁盘中。
STORM集群架构示意图

Storm集群中有两类结点，主结点和工作结点。守护进程Nimbus运行在主结点上，负责代码分发，为工作结点分配任务、故障监测；守护进程Supervisor运行在工作结点上，负责监听分配给所有工作节点的任务，也就是根据Nimbus的任务分配来决定启动或停止工作进程，执行storm拓扑，一个Supervisor可能执行拓扑的一部分，也可能执行完整的拓扑；Zookeeper是一个分布式协调器，负责Nimbus和多个Supervisor之间的所有协调工作，另外Zookeeper还保存了storm的状态信息，所有无状态的守护进程，一旦因故障终止被重启后，可以恢复到之前的状态继续工作，极大促进了storm的稳定性。

Storm工作流程：

• 客户端提交拓扑到Storm集群
• Nimbus将给Supervisor分配的任务写入Zookeeper
• Supervisor从Zookeeper中获悉所分配的任务，并启动工作进程执行具体任务

流式计算在一些数据量大、实时性要求高的应用场景中发挥着重要作用，如微博话题的实时分析就需要流式计算技术的支撑。

批处理的代表MapReduce框架最早由Google提出，MapReduce采用分而治之的思想，首先把大规模数据集分解成很多小数据集，然后对每个小数据集进行处理，最后将得到的结果汇总，MapReduce将这一处理过程高度抽象为map阶段和reduce阶段，用户只需编写map和
reduce这两个函数，系统管理map和reduce任务的并行执行以及任务之间的协调，并能应对任何故障

MapReduce的处理过程：

• 在Map阶段，首先将输入的数据集分解成固定大小的块，每个块将由一个Map任务处理，Map任务将执行用户自定义的map函数，将输入块转换成一个Key-Value键-值对序列。
• 系统主控器：从每个Map任务收集键-值对序列，并将它们按键大小排列，键相同的键-值对被发送给同一个Reduce任务。
• 在Reduce阶段，Reduce任务接受从不同Map任务得到的键相同的键-值对，执行用户编写的reduce函数，将键相同的键-值对中的所有值以reduce函数指定的方式组合起来，得到键-值对并输出
  
  MapReduce是一种处理海量数据的并行编程模型和计算框架，通过处理用户的map和reduce函数，能够自动地并行执行在可伸缩的大规模集群上，从而可以对大规模数据进行处理和分析，具有可扩展、容错等特点。

第四节 大数据应用

互联网应用的流行度与用户数量成正比

推荐系统通过研究用户的兴趣爱好，帮助用户从大数据中发觉自己潜在的需求，进行个性化推荐，缓解或解决信息过载问题。
基于内容推荐方法根据用户兴趣模型与每一个物项特征模型之间的相似性来进行推荐。
基于用户的协同过滤推荐方法是根据用户相似性进行推荐。
基于物项的协同过滤推荐方法是根据物项相似性推荐。

推荐

• 推荐是指根据用户的过往行为、活动、态度等，为用户推荐感兴趣的各种对象的软件系统。
• 在推荐系统中，通常把为用户推荐的对象称为“物项”。

现今比较流行的推荐算法主要包括两大类：基于内容的推荐，协同过滤推荐

这些方法各有其长，各有其短，实际当中总是采用混合的方法来改进推荐效果。

基本思想：

• 通过分析用户的属性，比如性别、年龄、专业和显示兴趣描述文本信息，发掘出与用户兴趣相关的关键词或标签，构建用户画像也就是兴趣模型；
• 接着，建立物项的特征模型；
• 然后，选一种相似性计算方案来计算用户的兴趣模型与每一个物项的特征模型之间的相似性；
• 最后，系统按照相似度高低对物项进行标记，并以一定的形式推荐给目标用户。
  
  基于内容的推荐关键是模型建立与相似性计算。用户兴趣模型是用户兴趣偏好的数学表示，如果用户兴趣模型建立不够好，推荐结果会更不好。
  模型的重要性
  使用怎样的模型，提取怎样的用户和物项信息，一直是推荐算法研究的重点和难点,并是推荐算法进一步发展的关键。

由于用户兴趣模型是基于历史信息或个人属性产生的，这就使得用户模型更具有记忆性而非预测性，并且系统无法区分不同属性的相关关键词表示，这种局限对推荐质量也有一定的影响。
基于内容推荐的优点

• 可解释性好。用户以前喜欢什么样的物项，系统就推荐给目标用户具有相似特征的物项，这种解释几乎对所有用户都是可接受的。
• 新物项可以得到推荐。每当有新物项进入系统，一般已经确立好其属性，可以利用这些经确定好的信息对新物项进行建模，这种效果与老物项并没有什么大区别，所以新物项具有与老物项同等的被推荐概率。

基于用户的协同过滤推荐方法的基本思想，是用一个数据库来存储用户的历史标注数据，比如评分、观看、浏览、购买点击等，认为用户会喜欢与他具有相似历史标注习惯的用户所喜欢的物项，据此来向用户推荐。
基于用户的协同过滤推荐： 根据已知信息推测用户间相似性,找到用户“邻居”,进而把“邻居"感兴趣的物项推荐给用户。
步骤：
1．根据用户的历史标注信息,计算用户相似性，发现某一特定用户的“邻居”集合
2．推荐“邻居”感兴趣的但此用户尚未选择过的物项给该用户

基于用户的协同过滤推荐方法，邻居用户是基于用户对物项的共同标注来计算的，在数据稀疏的情况下，用户共同标注的物项往往非常少，相似度估计就会很不准确，无法有效找到邻居用户，从而影响推荐结果的准确性。

基于物项的协同过滤推荐方法，它的基本思想也是用一个数据库来存储用户的历史标注数据，认为用户会喜欢与他自己曾经喜欢过的物项相似的物项，据此向用户推荐。

基于物项的协同过滤推荐： 根据已知信息推测物项间相似性,找到物项“邻居”,进而把“邻居"物项推荐给感兴趣的用户
步骤：
1．根据历史标注信息,计算物项相似性,发现某物项的“邻居”集合
2.推荐“邻居”物项给可能感兴趣的用户

基于物项的协同过滤推荐方法，邻居物项是基于用户对物项标注的共性来计算的，在数据稀疏的情况下，物项相似度估计就会很不准确，无法有效找到邻居物项，从而导致推荐结果的准确度不高。

推荐系统
推荐系统是自动联系用户和物项的一种工具，和搜索引擎相比，推荐系统通过研究用户的兴趣爱好,帮助用户从大数据中发觉自己潜在的需求,进行个性化推荐,缓解或解决信息过载问题。
为用户提供高质量的推荐能够提高用户忠诚度，如果进一步把用户对推荐项的标注记录下来用以丰富推荐数据库，就可以不断改进推荐质量，吸引更多的用户和用户使用的更多。

互联网应用都依托数量极其庞大的用户群，不仅用户发布数据量巨大，用户历史行为、用户特征属性数据也都是大数据，不论是基于内容的推荐还是协同过滤推荐，系统都需要管理张高维度、大规模的用户信息表，而且表中的列通常不是固定的，其次，很多属性存在空值或多值，最后，这张表的数据读写负载非常巨大，而且一点也不均衡。

用户信息大数据
管理和分析大规模用户信息通常都是使用NoSQL数据存储和处理技术。

像推荐技术一样，许多互联网应用中的关键功能，不论是早期基于批处理的离线分析，还是更好的实时在线数据分析，都离不开有效的大数据管理、分析、处理和应用技术。
大数据技术从信息搜索起步，如今已经融合到社会生产生活的方方面面，电子商务、智能电网、网上交易、智慧物流