第九章 数据库

第九章 数据库

JDBC是Java技术中访问数据库的方式，也是目前用Java技术实现的基于Web的应用的数据库访问方式。

1.数据模型

数据模型分为概念数据模型（E-R模型（实体-联系模型））和结构数据模型（用于DBMS的实现）（层次、网状、关系和面向对象模型）

1.1.E-R模型

它提供了表示实体型、属性和联系的方法。其中实体型用矩形表示，矩形框内写明实体名；属性用椭圆形表示，它表示实体某方面的特性，用无向边将其与相应实体连接起来；联系用菱形表示，菱形内写明联系名，并用无向边分别与有关实体连接起来，同时无向边旁边写明联系类型（1：1、1：n、m：n）

1.2关系模型

关系模型是一种用二维表格结构来表示实体以及实体之间联系的数据模型，每个二维表由行、列组成，又可称为关系。关系模式是对关系的描述，关系模型是由关系模式组成的集合。

关系模型是目前主流的数据模型，采用关系模型的数据管理系统有Oracle、Mysql、Microsoft SQL Server、Microsoft Access、Db2等

2.数据库的三级模式结构

数据的存储结构各不相同，但是体系结构都采用“三级模式和两级映像”。

2.1三级模式

三级模式	对应
外模式（也叫用户模式或子模式）	视图
概念模式（也叫模式）	基本表
内模式（也叫存储模式）	存储文件

2.2两级映像

数据库系统在三级模式之间提供了两级映像：模式/内模式映像、外模式/模式映像，正因为这两级映像保证了数据库中的数据具有较高的逻辑独立性和物理独立性。独立：数据和程序相分离。

两级映像	概念	独立性
模式/内模式映像	实现了概念模式和内模式之间的相互转换	为了保证物理独立性，需要修改概念模式和内模式之间的映像。
外模式/模式映像	实现了外模式和概念模式之间的相互转换	为了保证逻辑独立性，需要需修改外模式和概念模式之间的映像。

3.关系模式

基本术语	具体描述
关系	一个关系就是一张二维表，每个关系有一个关系名
元组	表中的一行即为一个元组，对应存储文件中的一个记录值
属性	表中的列称为属性，每一列有一个属性名，属性值相当于记录中的数据项或字段值
域	属性的取值范围，不同元组对同一属性的值所限定的范围
关系模式	对关系的描述称为关系模式，由关系名和其属性集合构成，格式：关系名（属性名1，属性名2，.....，属性名n）

关系：

 

关系模式：

 

名词	概念
候选码（键）	属性或属性组合，其值能够唯一地标识一个元组
主码（键）	在一个关系中可能有多个候选码，从中选择一个作为主码
主属性	包含在候选码中的属性称为主属性，不包含在候选码中的属性称为非主属性
外码（键）	不是该关系的主键，但是是另外一个关系的主键，则称其为该关系的外键
全码	关系模式的所有属性组是这个关系模式的候选码，称为为全码
超码	一个包含码的属性集叫超码，例如学号是码，则（学号，姓名）是超码

存储过程是数据库所提供的一种数据库对象，通过存储过程定义一段代码提供给应用程序调用执行，从安全性角度考虑，更新数据时，通过提供存储过程让第三方调用，将需要更新的数据传入存储过程，而在存储过程内部用代码分别对多个表进行更新，从而避免了向第三方提供系统的表结构，保证了系统的数据安全保证数据库的关系模式不会被第三方所获取。

4.完整性约束

关系完整性约束	具体描述
实体完整性	关系中主码的值不能为空或部分为空，主码中的属性即主属性不能取空值	例如部门关系中部门号不能为空。主键子句primary key 是实体完整性。
参照完整性	关系R1中的外码X的每个值必须在关系R2中主码的值中找到，或者该外码为空值	例如员工关系中外码部门号在部门关系中能找到，也可以员工关系中外码部门号为空则表示该员工没有还未被分配部门。外键子句foreign key是参照完整性。
用户定义完整性	用户对某一具体数据指定的约束条件进行检验	例如用户可以对关系中电话约束11位。检查子句check是用户定义完整性。

5.关系代数

元组也称为记录，元组的个数称为基数

关系代数	具体描述
关系的并	关系R与关系S的所有元组合并，再删去重复的元组，组成一个新的关系，称为R和S的并，记作R U S
关系的差	关系R中删去关系S中相同的元组，组成一个新的关系，记作R - S
关系的交	关系R与关系S取相同的元组，组成一个新的关系，记作R 反U S。R-（R-S）等于R交S
笛卡尔积	关系R中每一个元组依次乘关系S所有元组，记作R x S。若R元组数n，S元组数m则RxS中元组数为n乘m
投影	从关系的垂直方向进行运算，在关系R中选出若干属性列A组成新的关系，记作ΠA（R）
选择	从关系的水平方向进行运算，在关系R中选择满足给定条件的元组，记作σF（R），F一般为表达式
连接	连接运算是从两个关系R和S的笛卡儿积中选取满足条件的元组，连接分为θ连接、等值连接、自然连接；
除	不作学习

 

 

 

注意：

1.投影选择中数字代表的是列，例如下图ΠA，C（R）=Π1，3（R）；σ B=‘5’（S） = σ 1=‘5’（S）； 而σ 1=3（S）则是挑选关系S中第一列等于第二列的元组，σ 1>3（S）表示挑选关系S中第一列大于第三列的元组， σ 1>3 ^ 1>2（S）表示挑选关系S中第一列大于第三列并且第一列大于第二列的元组。

2.笛卡尔积中要注意重名问题，关系R的A写R.A，关系S的A写S.A

5.1.连接

1. θ连接，X θ Y是一个连接条件，θ是比较运算符

假设关系R和关系S如下

 

先求R和S的笛卡儿积

 

则条件为R.A<S.B，R和S的θ连接为：

 

 

2.等值连接：当θ为“=”时称为等值连接

 

3.自然连接：第一步计算RxS；第二步找出R和S的公共属性A，并挑选出R.A=S.A的元组；第三步去掉S.A的这些列。

以下R和S有两个公共属性，则自然连接就是挑选出RxS中R.A=S.A，R.B=S.B的元组

 

 

4.外连接：自然连接会丢失一些信息，使用外连接可以避免信息的丢失。外连接分为：左外连接、右外连接、全外连接

左外连接：左边表的元组也保留，是用null填充右侧关系的属性

 

右外连接：右边表的元组也保留，是用null填充左侧关系的属性

 

全外连接：完成左外连接和右外连接的操作

6.SQL语言

6.1关系代数转 SQL语言

关系代数	SQL语言
投影；Π A,B,C (R)	select A,B,C from R
选择；Π A,B,C (σ B>='5'(R))	select A,B,C from R where B>='5'
笛卡儿积；Π R.A,R.B,R.C,S.A,S.B,S.D (RxS)	select R.A,R.B,R.C,S.A,S.B,S.D from R,S
自然选择；R和S自然连接等于Π R.A,R.B,R.C,S.D(σ R.A=S.A^R.B=S.B(R x S))	select R.A,R.B,R.C,S.D from R,S where R.A=S.A and R.B=S.B

注意：

1.SQL语言不支持使用第几列作为语言中的一部分例如Π 1,2,3 (σ 2>='5'(R))不能转换为SQL语言，所以数字也可用不加单引号。所以如果是纯数值可以去掉引号。

2.可以用 order by 对查询的数据进行排序，asc升序（默认）、dsc降序

3.

 

若R(A,B,C,D,E)、S(B,C,F,G)，则上述关系代数转换为SQL语言为select A,C,S.F,S.G from R,S where R.B=S.B and R.C=S.C and R.C<S.F

6.2 SQL语言基础

1.SQL Server数据库的体系结构是由视图、基本表、存储文件三级结构组成（分别对应外模式、概念模式、内模式）

2.SQL语言按照用途可以分为

SQL语言
DDL（数据定义语言）	建立和删除一个数据库对象（数据库中的表、视图、索引等都是数据库对象），包括CREATE、ALTER、DROP等
DML（数据操作语言）	添加、修改和删除数据库中数据的语句，包括INSERT、DELETE、UPDATE等
DQL（数据查询语言）	查询是数据库的基本功能，包括SELECT
DCL（数据控制语言）	数据库对象的权限管理和事务管理

DDL(数据定义语言）

数据定义语言	具体语句	补充
建立数据库	create database 数据库名
建立表	create table <表名>（<列名><数据类型>[列级完整性约束条件]，......，[表级完整性约束条件]）	完整性约束主要有3种子句：主键子句（primary key）、检查子句（check）、外键子句（foreign key）
修改表	增加列：alter table 表名 add 列名 类型；修改列：alter table 表名 modify column 列名 类型；删除列：alter table 表名 drop column 列名
删除表	drop table 表名

DML（数据管理语言）

数据管理语言	具体语句	补充
插入语句	insert into 表名 [列名序列] values（元组值）
删除语句	delete from 表名 [where 条件表达式]	若不加where则该整个表数据都被删除
修改语句	update 表名 set 列名=值表达式,...,where 条件表达式	若不加where则表中该列的所有元组都被修改

DQL（数据查询语言）

数据查询语言	具体语句	补充
投影查询	select 列名 from 表名	在列名前加distinct可以去掉一列中重复的数据。在列名后加 “as 标题名”则查询结果表将以新的列名输出。
选择查询	select * from 表名 where 条件表达式	条件表达式中between and表示之间（闭区间）；like（like '匹配串'）表示进行匹配运算其含义是查找指定的属性列值与匹配串相匹配的元组，匹配串可以是字符串也可以含有通配符%和_的（a%b表示以a开头b结尾的任意长度的字符串）(a_b表示以a开头b结尾的长度为3的任意字符串)；集合比较运算 in(a1,a2,a3)表示查询的记录某值等于a1或a2或a3；也可以包含多个条件用and、or、not组合；某列名 is null表示查询某列为空的元组和 is not null。
排序查询	order by	order by语句必须是select语句中的最后一个子句，可以按升序acs(默认)、降序desc排序，也可以两个不同列一个降序一个升序。
聚合函数	AVG(列名)、MAX(列名)、MIN(列名)等	如果选择列中有聚合函数，则需对除聚合函数以外的列进行分组(group by)；where后不能有聚合函数，所以若需要用聚合函数来做条件则需要用Having来实现，但Having又只能与group by搭配，所以三者息息相关。
数据分组	group by	当一个聚合函数和一个group by一起使用时，聚合函数的范围就变成了每组的所有记录，所以都是先执行分组再执行聚合函数的。
内连接	INNER JOIN	不使用该关键字时默认均为内连接，例如之前关系代数转换为SQL语句中select R.A,R.B,R.C,S.D from R,S where R.A=S.A and R.B=S.B可以写为select R.A,R.B,R.C,S.D from R INNER JOIN S ON R.A=S.A and R.B=S.B
一般子查询(嵌套查询)	where后面接另一个select查询	一个子查询的返回值为多行时，需要使用ANY、ALL、IN、NOT IN等，例如where 分数 > ANY(select.....)
相关子查询	子查询不止执行依次	内层的条件涉及到外层属性的子查询成为相关性子查询，即每一次子查询都是不一样的，类似于循环。
查询结果的并、交、差	并：UNION;交：INTERSECT；差：EXCEPT	两个查询结果的表进行相应的运算，例如并就是两个表全部元组放一起去掉一样的元组。

3.若要查询表中所有列，可以直接写一个 * 代表所有列。

4.若from后有多个表，则系统首先进行笛卡儿积操作。

5.当where、group by、having和聚合函数同时出现在一个查询中时，select执行顺序如下：

​ （1）执行where语句，从表中选取行

​ （2）由group by对选取的行进行分组

​ （3）执行聚合函数

​ （4）执行HAVING子句选取满足条件的分组

DCL（数据控制语言）

数据控制控制的是用户对数据的存储权力，是由DBA（数据库管理员）决定的，但是某个用户对某类数据具有何种权力是个政策问题不是技术问题。SQL标准包括delete、insert、select、update权限。

数据控制语句	具体描述	补充
授权	grant <权限>on<对象类型><对象名>to <用户>[with grant option]	用户可以用public表示将权限赋给全体用户；权限可以用all privileges表示全部权限；with grant option子句表示获得权限的用户可以将权限赋予其他用户
收回权限	revoke<权限>on<对象类型><对象名>from<用户>	例如revoke select on table R from public表示将所有用户对表R的查询权限收回；revoke update（Sno）on table R from user1表示将user1的修改Sno的权限收回

SQL语言函数定义语法结构

create function 函数名（参数名 参数类型）

returns 返回值数据类型

begin

​ 函数体（function body）(里面可以定义变量（declare 变量名 变量类型）)

return 表达式；

end

视图

视图是从一个或多个基本表或视图中导出的表是一个虚拟表。基本表和视图的区别是基本表是实际存储在数据库的表，视图是虚拟表，数据库中只存放视图的定义不存放视图的数据，用户看来基本表和视图都一样。

视图操作	具体	补充
创建视图	create view 视图名（列表名）as select 查询子句 [with check option]	[with check option]表示对视图进行操作时保证其满足视图中定义的谓词条件（子查询中的表达式），例如创建视图的子查询中条件为专业为计算机，如果加了[with check option]语句那么在视图中插入数据时就不能插入除了计算机专业以外的信息了。
删除视图	drop view 视图名

索引

索引是对某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。另外索引存在于数据库三级模式中的内模式中，内模式存储文件索引存在于文件中。

索引	具体	补充
创建索引	create [unique或cluster] index 索引名	unique表示每个索引对应唯一的值；cluster表示该索引是聚簇索引即表中记录的物理顺序与索引项顺序一致。
删除索引	drop index 索引名

6.3列级、表级完整性约束

1.列级完整性约束：取值唯一（unique）、能取空和不能取空（null、no null）、默认（default）

 

上述语句执行后，a列的值不能为空，b列的值是唯一的其他列不能填写，c列的值不能为空且值唯一，d列每次默认值都是zst

2.表级完整性约束：主键子句（primary key）、检查子句（check）、外键子句（foreign key）

 

 

上述语句，约束sid为S表的主键；约束sid为R表的外键（references参照S表）；check为用户自定义的约束，约束score的值只能大于0小于等于100。

主键子句primary key 是实体完整性；外键子句foreign key是参照完整性；检查子句check是用户定义完整性。

7.关系模式的规范化

通常在逻辑设计阶段进行，目的是为了减少数据冗余从一定程度上消除修改、插入、删除异常。

7.1函数依赖

函数依赖是关系设计库设计理论的核心，衡量的标准是关系规划化的程序以及分解的无损连接和保持函数依赖性。

数据依赖	具体	补充
函数依赖	X函数决定Y函数或Y函数依赖于X函数，记作X->Y
非平凡的函数依赖	如果X->Y，但Y不包含于X，则称X->Y是非平凡的函数依赖。	一般情况下总讨论的是非平凡的函数依赖。例如学号—>姓名是一个非平凡的函数依赖。
平凡的函数依赖	如果X->Y，但Y包含于X，则称X->Y是平凡的函数依赖。	大的推出小的，例如（学号，课程号）—>学号是一个平凡的函数依赖。
完全函数依赖	如果X->Y，并且X中的任何一个真子集X'都有X’不能决定Y，则称X->Y是完全函数依赖，记作X—>Y线上有一个F。	例如（学号，课程号）—>成绩中学号，课程号）的真子集学号和课程号都不能决定成绩，所以该依赖是一个完全函数依赖。
部分函数依赖	如果X->Y，但Y不完全函数依赖于X，则称X->Y是部分函数依赖。	部分函数依赖也称为局部函数依赖，记作X—>Y线上有一个P。
传递依赖	如果X->Y，Y->Z则可以得到X->Z，称Z对X传递依赖。
码	设K为属性组，若K->U，且对于K中的任何一个K'都不能决定U，则称K为R的候选码或候选关键字。	若有多个候选码，则称选一个作为主码。候选码中的任何一个属性称为主属性，否则为非主属性。
外码	若R（U）中的属性或属性组X非R的码，但X是另一个关系的码则称X为外码	码也叫键。
函数依赖的公理系统	传递律：X->Y，Y->Z为F所蕴涵则X->Z为F所蕴涵；	合并规则：若X->Y，X->Z，则X->YZ为F所蕴涵；分解规则：若X->Y，Z包含于Y，则X->Z为F所蕴涵。

7.2求关系模式的闭包

一般用关系模式的闭包来求候选关键字（求某个属性的闭包若求出整个关系的属性则该属性为候选关键字），也可以用简便方法:

​ 直接寻找函数依赖中只出现在左边的属性一定为候选码或包含在候选码中。

求闭包：

 

7.3范式

关系数据库的关系必须满足一定的规范化要求，对于不同的规范化程度可用范式来衡量，达到范式的关系才是规范化的。目前主要有6种范式：1NF、2NF、3NF、BCNF、4NF、5NF。（在1NF的基础上进一步满足一些要求的为2NF），满足最低要求的称为1NF，包含关系1NF包含2NF依次下去。

一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式集合，这个过程称为规范化。

7.3.1第一范式

关系模式R属于第一范式当且仅当R中的每一个属性A的值域只包含原子项，即不可分割的数据项。（若属性工资可以分割为基本工资、加班工资等该关系就不属于1NF）

1NF不能排除数据冗余和更新异常等问题，因为其中可能存在部分函数依赖（非主属性部分函数依赖于候选码）。

7.3.2第二范式

存在部分依赖（非主属性部分函数依赖于候选码）则不满足第二范式。

关系模式R属于第二范式当且仅当R是1NF且每个非主属性都完全函数依赖于候选码

2NF也不能排除数据冗余和更新异常等问题，因为其中可能存在传递函数依赖。

关系分解消除部分函数依赖：将一个二维表分解成多个二维表（将一个关系分解成多个关系）。（单个属性作为候选码是不存在部分函数依赖）

7.3.3第三范式

关系模式R属于第三范式当且仅当R是2NF且每个非主属性都非传递函数依赖于候选码

关系分解消除传递函数依赖：分解表的条件是去除某一个函数依赖使其不能传递。

3NF也不能排除数据冗余和更新异常等问题，因为关系模式可能存在主属性对候选码的部分依赖和传递依赖。（BC范式就需要消除这一点）

7.3.4BC范式

关系模式R属于BC范式当且仅当R是3NF且其函数依赖中的每一个依赖的决定因素必定包含R的某一个候选码。（即一定要 候选码—>xxx）

BC范式已经消除了插入和删除的异常

7.4部分依赖函数判断技巧

若一个关系模式的候选键是一个属性组，则看有没有属性组中的子集决定某个属性的依赖，有则为部分函数依赖。（非主属性依赖于候选码的一部分）

7.5传递函数依赖判断技巧

伪传递率：若X->Y，WY->Z，则XW->Z为F所蕴涵。

通过伪传递率推出新的函数依赖，新的函数依赖又满足传递函数依赖。

8.关系分解

1.无损连接性是指对关系模式分解时，原关系模式下任一合法的关系实例在分解之后应能通过自然连接运算恢复起来。

通过将分解后的关系模式自然连接（合并所有属性消除相同属性若无公共属性列则不能自然连接）看看能否得到原关系模式，得不到则有损，得到则无损。

2.保持函数依赖的分解

若分解后每个关系模式的函数依赖等于分解前的关系模式的函数依赖，则称分解后的关系模式保持函数依赖。可以看分解后的属性与分解前的函数依赖是否一致（是否在一个关系中）

9.数据库分析与设计

新奥尔良法把数据库设计分为4个主要阶段：用户需求分析（收集用户需求，确定系统边界）、概念设计（描述概念模型E-R方法）、逻辑设计（E-R图转换为关系模式并把关系模式规范化）、物理设计（逻辑模型在计算机中的具体实现方案）。后面还需要进入数据库实施阶段和数据库运行、维护阶段。当各阶段发现不能满足用户需求时，均需返回到前面适当的阶段，进行必要的修正，如此经过不断地迭代和求精，直到各种性能均满足用户的需求为止。

9.1需求分析

这一过程是后续开发的基础，以后的逻辑设计和物理设计以及应用程序的设计都会以此（系统需求说明书（需求分析文档、数据流图、数据字典））为依据。

9.2概念设计

对需求分析阶段所得到的数据进行分类、聚集和概括，确定实体、属性和联系，步骤是选择局部应用、逐一设计分E-R图和E-R图合并。

E-R图合并，合并的目的在于在合并过程中解决分E-R图中相互间存在的冲突。合并剩余的分E-R图直到所有E-R图全部合并，就构成一张全局的E-R图。

分E-R图之间的冲突主要有三类：

冲突	具体描述	例子
属性冲突	同一属性可能存在于不同的分E-R图。合并后统一	学生的年龄与老师的年龄
命名冲突	相同意义的属性在不同的分E-R图上有着不同的命名或者名称相同的属性在不同的E-R图中有不同的意义。合并后统一	学号和学生编号
结构冲突	同一实体在不同的分E-R图上有着不同的属性或同一对象在某分E-R图中被抽象为实体，而在另一分E-R图中又被抽象为属性。合并需统一

9.2逻辑结构设计

逻辑结构设计即是在概念结构设计的基础上进行数据模型设计，可以是层次模型、网状模型和关系模型。主要工作步骤包括确定数据模型、将E-R图转换为指定的数据模型、确定完整性约束和确定用户视图

9.3物理结构设计

数据库的物理结构设计离不开DBMS，一般来说物理设计包括确定数据分布、存储结构和访问方式的工作

9.4数据库的实施与维护

实施阶段先定义数据库、编写应用程序装入数据、数据库试运行

10数据库控制功能

10.事务管理

事务具有原子性、一致性、隔离性、持久性、

事务特性	具体描述
原子性	事务是原子的，要么都走，要么都不做
一致性	事务执行的结构必须保证数据库从一个一致性状态变到另一个一致性状态
隔离性	事务相互隔离，当多个事务并发执行时任一事务的操作直其完成的整个过程，对其他事务都是不可见的
持久性	一旦事务成功提交，即使数据库崩溃，其对数据库的更新操作也永久有效

11数据库备份与恢复

数据库的关键技术在于建立冗余数据即备份数据，如何在系统出现故障后能够及时地使数据库恢复到故障前的正确状态就是数据库恢复技术。

11.1备份方法

恢复原理：建立冗余数据

建立冗余数据的方法是进行数据转储和登记日志文件，其中数据转储又分为静态转储、动态转储、海量转储、增量转储

备份方法	具体
静态转储	在转储期间不允许对数据库进行任何存取、修改操作
动态转储	在转储期间允许对数据库进行存取、修改操作
海量转储	每次转储全部数据
增量转储	每次只转储上次转储后更新的数据
日志文件	在事务处理过程中，DBMS把事务开始、事务结束以及对数据库的插入删除修改的每一次操作写入日志文件，一旦发送故障，DBMS的恢复子系统利用日志文件撤销事务对数据库的修改回退到事务的初始状态。

11.2恢复

事务恢复的三个步骤

步骤
反向扫描日志文件（从后向前扫描），查找该事务的更新操作
对事务的更新操作执行逆操作
继续反向扫描日志文件，查找该事务的其他更新操作并同样逆操作，直至事务的开始标志

12.并发控制技术

并发控制的主要技术是封锁。基本封锁有排它锁（X锁或写锁）、共享锁（S锁或读锁）

封锁	具体描述
排它锁（X锁、写锁）	若事务T对数据对象A加上排它锁，则只允许T读取和修改A，其他事务不能再对A加任何类型的锁，直至T释放A上的锁。
共享锁（S锁、读锁）	若事务T对数据对象A加上共享锁，则只允许T读取，但不能修改A，其他事务只能再对A加共享锁，直至T释放A上的锁。这就保证了其他事务能读取A但在事务T释放A上共享锁之前不能修改A。

13.分布式数据库

分布式数据库的特点如下

特点	具体描述
分片透明	指用户或应用程序不需要知道逻辑上访问的表具体怎么分块存储的
复制透明	指采用复制技术的分布方法，用户不需要知道数据是复制到哪些节点如何复制的
位置透明	指用户不需要知道数据存放的物理位置
逻辑透明	指用户或应用程序不需要知道局部场地使用的是哪种数据类型
共享性	指数据存储在不同的结点共享数据
自治性	指每个结点对本地数据都能独立管理
可用性	指当某一场地故障时，系统可以使用其他场地上的副本而不至于使整个系统瘫痪
分布性	指数据在不同的场地上的存储