数据库基础必看

数据库基础

绪论

数据模型

• 概念模型（ER）

  • 实体型：矩形框

  • 联系：菱形框

  • 实体型和联系的属性：椭圆框

  • 直线：连接实体类型和联系类型

• （结构）数据模型

  • 层次模型

    • 树

  • 网状模型

    • 图

  • 关系模型

    • 关系：一个关系对应一张表，记作R

    • 元组：表中的一行

    • 属性：表中的一列

    • 域：属性的取值范围

    • 分量：元组中的一个属性

    • 关系模式：R(U,D,DOM,F)

      • U：表的字段集合

      • D：表中字段类型的集合

      • DOM：每个字段具体的取值

      • F：表字段的关系

    • 候选键：唯一标示关系中的元组

    • 主键：被指定正在使用的候选键

    • 超键：所有候选键都是超键，可以有多余属性

    • 组合键与全键：主键不是单一属性、组合键包括所有属性时称为全键

    • 外部键

      • 属性是关系模式2的主键，对关系模式1是外键

    • 主属性：包含在任意候选键中的属性

DBMS

• 功能

  • 数据定义DDL

  • 数据操纵DML、DCL

  • 数据库运行管理

  • 数据组织、存储、管理

  • 数据库建立与维护功能

  • 数据通信接口功能

• 组成

  • 数据定义语言DDL

  • 数据操纵语言DML

  • 数据库运行控制语言DCL

  • 实用程序

关系数据库

关系数据结构

• 关系

  • 定义：笛卡尔乘积的任一子集为相应关系

  • 候选码

    • 关系中的某一属性组的值能唯一地标识一个元组，而其真子集不可以。

  • 主码

    • 多个候选码中，可选定其中一个为主码

  • 主属性

    • 候选码的各个属性都称为主属性。

  • 非主属性

    • 不包含在任何候选码中的属性。

  • 全码

    • 关系模式的所有属性构成这个关系模式的候选码，称为全码。

• 关系模式

  • 一般表示为R(U,D,DOM,F)

    • R 是关系名

    • U 是组成该关系的属性集合

    • D 为属性组U 中属性所来自的域

    • DOM 是属性向域的映象集合

    • F 是属性间数据的依赖关系集合

关系的完整性

• 域完整性

  • 关系中属性的值应是对应域中的值，并由语意决定其是否为空值（NULL）。

• 实体完整性

  • 若属性 A 是关系R的主属性，则A不能取空值

• 参照完整性

  • 设F 是基本关系R 的一个或一组属性，但不是R 的码(主码或候选码)，如果F与基本关系S的主码相对应，则称F是基本关系R的外码。称R是参照关系(referencing relation)，S是被参照关系(referenced relation)。

• 自定义完整性

关系代数

• 交、并、差、笛卡尔积（Easy）

• 专门的关系运算

  • 投影

    • 显示表格的对应属性列

  • 选择

    • 选出满足对应条件的元组

  • 连接

    • 从两个关系的笛卡尔乘积中选取属性满足一定条件的元组

    • 等值连接

      • 所要求属性相等的元组，无需删重复列

    • 自然连接

      • 两个关系有相同属性，在相同属性做等值连接，但需要删除重复列

  • 除法

    • 给定关系 R(X，Y)和S(Y，Z)，其中X，Y，Z 为属性组，R中的Y 与S 中的Y 可以不同属性名，但必须有相同的域。记R÷S。令P(X)＝R÷S，则P是R中满足以下条件的元组在X属性列上的投影：元组在X上的分量值x的象集Y_x包含S在Y上投影的集合

  • 外连接：R和S自然连接时，舍弃的元组也保存在新关系中，在新增加的属性上填null

    • 左连接

      • 把R中该舍弃的元组保留

    • 右连接

      • 把S中该舍弃的元组保留

  • 外部并

    • 若关系 R 和S 不同类，则新关系的属性由R 和S的属性组成，公共属性只取一次，新关系的元组由属于R 或S 的元组构成，新增的属性上均填空（null）

  • 半连接

    • R和S的半连接为R和S的自然连接在关系R的属性集上的投影

  • 些许例子

    • 检索至少选修课程为 C2 和C4 的学生学号

      • δ S#,C#（SC）÷δ C#=’C2’ ∨ C#=’C4’（C）

    • 检索选修全部课程的学生姓名。

      • ΠSN(S ∞(ΠS#，C#(SC)÷Π C#(C)))

    • 检索所学课程包含学生S3所学课程的学生学号。

      • ΠS#，C#(SC)÷Π C#(δ S#=’S3’(SC))

    • 检索选修课程名为 Maths 的学生学号与姓名。

      • ΠS#，SN(δ CN=’Maths’(S∞SC∞C))

关系数据库标准查询语言SQL

数据定义语言（DDL）

• 定义、删除与修改基本表

  • create table 表名(列名 数据类型 约束条件);

  • alter table 表名

    • add 列名 数据类型 约束条件

    • modify 列名 数据类型

    • drop 完整性约束条件

  • drop table 表名

• 建立和删除索引

  • create unique index 索引名 on 表名(列名 次序)

  • drop index [表名.] 索引名

SQL的数据查询（DML）

• 单表查询

  • 投影运算

    • select SD from s

  • 选择运算

    • 消除取值重复行

      • select distinct SD from S

    • 查询满足条件的元组

      • 比较大小(<,>,<>...)

        • select * from s where SA < 20

      • 确定范围(between..and)

        • select * from s where SA between 20 and 21

      • 确定集合(in)

        • select * from s where SD in ('CS','IS','MA')

      • 字符匹配(like)

        • select * from s where S# like 'T%'

      • 涉及空值的查询(NULL)

        • select * from SC where GR is NULL

      • 多重条件查询(and)

        • select * from S where SD='CS' and SA < 20

  • 查询结果排序(order by 字段表达式 ASC/DESC)

    • select * from SC where C#='C3' order by GR desc

  • 使用集(聚合)函数

    • select COUNT(distinct S#) from SC

    • select SUM(SA) from S

    • select AVG(GR) from SC where S#='95001'

    • select MAX(GR) from SC where C#='1'

    • select MIN(GR) from SC where C#='1'

  • 查询分组(GROUP BY)

    • select C#, COUNT(C#) from SC group by C#

• 连接查询

  • 自然连接

    • select S.* SC.* from S,SC where S.S#=SC.S#

  • 自身连接

    • select f.C#,s.CP from C f,C s where f.CP=s.C#

  • 外连接

    • T-SQL(*=)

    • PL/SQL (+)

    • MYSQL/MSSQL left outer join

  • 复合条件连接

    • selectS.S#,SN,C.CN,SC.GR from S,SC,C whereS.S# = SC.S# andSC.C# = C.C#

• 嵌套查询

  • 带IN谓词的子查询

    • select S#,SN,SD from S where SD IN(select SD from S where SN＝‘刘晨’)

  • 带比较运算的子查询

    • select S# , SN from S where SD=(select SD fromS where CN=’刘晨’)

  • 带ANY和ALL的子查询

    • select S＃, SN from S where SA < ANY ( select SA from S where SD＝‘IS’)and SD<>‘IS’ order by SA DESC

  • 带EXISTS的子查询

    • select title from titles where exists (select * from sales where title_id = titles.title_id and qty > 10)

      • 等价于select title from titles where title_id in (select title_id from sales where qty > 10);

• 集合查询

  • 使用交、并、差的集合运算概念

    • INTERSECT

    • UNION

    • MINUS

SQL的数据更新（DML）

• 数据插入(insert into 表名 values)

  • 插入单个元组

    • insert into S values ('S001','张三','男','18','IS‘)

  • 插入子查询结果

    • insert into SC select S#,'C01',null,from S

• 数据修改(update 表名 set 修改后 where 条件)

  • 修改某个元组

    • update S set SA=22 where S#='S001'

  • 修改多个元组

    • update S set SA = SA + 1

  • 带子查询的修改语句

    • update SC set gr = 0 where S# in (select S# from S where SD = 'CS')

• 数据删除(delete from 表名 where 条件)

  • 删除某个元组

    • delete from S where S# = 'S001'

  • 删除多个元组

    • delete from SC

  • 带子查询的删除语句

    • delete from SC where 'CS'=(select SD from S where S# = SC.S#)

视图(只是一个窗口，数据依赖于表)

• 定义视图

  • 建立视图(create view 视图名 as 条件)

    • create view IS_S as select S#,SN,SA from S where SD = 'IS'

  • 删除视图(drop view 视图名)

    • drop view IS_S

• 查询视图

  • select S#,SA from IS_S where SA < 20

  • SELECT * FROM S_G WHERE AVG_GR>90

    • 消解为：SELECT S#, AVG(GR) FROM SC GROUP BY S# HAVING AVG(GR)>90

• 更新视图

  • 视图的修改

    • update IS_S set SN = ’刘辰‘ where S#='S001'

  • 视图的插入

    • insert into IS_S values('S001','刘辰',20)

  • 视图的删除

    • delete from IS_S where S#='S001'

• 视图的用途

  • 简化用户操作

  • 使用户多角度看待同一数据

  • 视图对重构数据库提供了一定的逻辑独立性

  • 视图能对数据提供安全保护

数据控制语言（DCL）

• 授权(grant..on table 表名 to)

  • grant select on table S to USER1

  • grant all privileges on table S,C to U2,U3

  • grant select on table SC to PUBLIC

  • grant update(SD),select on table S to U4

• 收回权限(revoke..on table 表名 from)

  • revoke select on table SC from public

  • revoke update(SD),select on table S from U4

  • revoke insert on table SC from U5

嵌入式SQL语言

• 一般形式：EXEC SQL <SQL语句>

• 不使用游标

  • 说明性语句

  • 数据定义语句

  • 数据控制语句

  • 查询结果为单记录的select语句

  • 非current形式的update语句

  • 非current形式的delete语句

  • insert语句

• 使用游标

  • 查询结果为多条的select语句

  • current形式的uodate语句

  • current形式的delete语句

• 游标

  • 说明游标：EXEC SQL DECLARE 游标名 cursor for <select语句>

  • 打开游标：EXEC SQL OPEN 游标名

  • 读取当前记录并推进游标指针：EXEC SQL FETCH 游标名 INTO 主变量

  • 检查是否需要修改或删除，若用使用where current of 游标名

  • EXEC SQL CLOSE 游标名

存储过程

• create procedure 函数名 @变量1 变量类型...as SQl语句（declare定义变量，@@全局变量，@用户变量）

  • create procedure get_gr @sno varchar(10),@GR output int as declare @cno varchar(5) BEGIN select @cno=C#,@GR=GR from S where S#=@sno if(@cno='C01')then select @GR=@GR+1 else select @GR=@GR+2 END; 执行：declare @gr int execute get_gr 'S001',@gr output select @gr 或 print @gr

数据库的保护

数据库的安全性

• 用户表示和鉴别

  • create USER 用户名 identified by 密码

• 存取控制

  • 权限限制的种类

    • 对用户进行控制

    • 对操作类型进行控制

    • 对数据对象的控制

  • 权限组（角色）概念

    • 角色是数据库预先设置的一系列具有某种常用权限的组合

    • Oracle中三类角色

      • Connect

      • Resource

      • DBA

• 定义视图

  • 屏蔽用户不该看到的数据内容

• 审计（AUDIT）

  • 自动记录对数据库的访问存取痕迹

• 数据加密

数据库的完整性

• DBMS检查数据库中的数据是否满足语义规定的条件，这些加在数据库数据之上的语义约束条件称为数据库完整性约束条件

• DBMS中检查数据是否满足完整性条件的机制称完整性检查

• 完整性约束条件

  • 静态列级约束

    • 对数据类型的约束

    • 对数据格式的约束

    • 对取值范围或集合的约束

    • 对空值的约束

  • 静态元组级约束

    • 只作用于单个元组上

  • 静态关系级约束

    • 实体完整性约束

    • 参照完整性约束

    • 函数依赖约束

    • 统计约束

  • 动态列级约束

    • 修改定义时的约束

    • 修改列值时的约束

  • 动态元组级约束

    • 修改后的值与原来多个字段相关

  • 动态关系级约束

    • 关系变化前后的状态限制

• 完整性控制

  • 完整性规则的形式化表示(D,O,A,C,P)

    • D：数据对象

    • O：触发完整性检查的数据库操作

    • A：数据对象必须满足的语义约束

    • C：选择数据对象的条件，可包含在D内

    • P：违反完整性规则所触发的操作过程

  • 完整性应考虑的问题

    • 删除被参照关系的元组

      • 级联删除：删除参照关系中相应外键值得元组

        • 当主表(parent table)中的一条记录被删除。子表中关联的记录也对应的自己主动删除

      • 受限删除：只有参照关系中没有相应外键值得元组时才允许删除

        • 若删除主表的主键时，如果子表中存在子记录，系统将产生一个错误提示

      • 置空值删除：将参照关系中相应外键置空

        • 若删除主表的主键时，把子表中参照的外键列设置为空值（NULL）

    • 修改被参照关系的元组

      • 级联修改：修改参照关系中相应元组的外键值

        • 当主表(parent table)中的一条记录被修改。子表中关联的记录也对应的自己主动修改

      • 受限修改：只有参照关系中没有相应外键值的元组时才允许修改

        • 若修改主表的主键时，如果子表中存在子记录，系统将产生一个错误提示

      • 置空值修改：将参照关系中相应外键置空

        • 若修改主表的主键时，把子表中参照的外键列设置为空值（NULL）

  • Oracle的完整性控制

    • 实体（主表）完整性

      • CONSTRAINT pk_1 PRIMARY KEY (S#));

    • 参照（子表）完整性

      • CONSTRAINT fk_1 FOREIGN KEY (S#) REFERENCES S (S＃) ON {DELETE CASCADE|SET NULL});

    • 列级

      • CREATE TABLE S（ S＃ char（10）CONSTRAINT CHK1 CHECK （substr (S#,3,8) BETWEEN ‘00000001’ AND ‘50999999’）， SS char（2） CONSTRAINT CHK2 CHECK （ SS IN(‘男’,’女’)）， SD char（5）， CONSTRAINT pk_1 PRIMARY KEY （S#））；

    • 元组级

      • CREATE TABLE EMP（ sal number(7,2)， deduct number（7,2）， CONSTRAINT CHK1 CHECK（sal+deduct<3000））；

    • 触发器

      • CREATE TRIGGER UPDATE_SAL: BEFORE INSERT OR UPDATE OF sal, pos ON Teacher FOR EACH ROW WHEN(:new.pos=’教授’) BEGIN IF :new.sal<800 THEN :new.sal:=800; END IF END

• 数据库的并发控制

  • 事务：是并发控制的单位，是数据库的逻辑工作单位，使用户定义的一组操作序列，表现为一组SQL语句或一个程序。

    • 事物的特性

      • 原子性

      • 一致性

      • 隔离性

      • 持久性

  • 并发操作带来的四大问题

    • 修改丢失问题

    • 污（脏）读问题

    • 不可重读问题

    • 幻影读问题

  • 数据库的并发操作调度

    • 可串行性是衡量并发事务正确性的唯一标准：多个事务并发时是正确的，当且仅当其结果和某一种次序串行地执行它们时地结果相同。称这种调度策略为可串行化（Serializable）的调度。

  • 数据库的封锁机制

    • 锁类型

      • 排它锁（X）

      • 共享锁（S）

      • 修改锁

  • 数据库的封锁协议——事物隔离级别

    • 事务隔离级别0/一级封锁协议

      • 防止了修改丢失

    • 事务隔离级别1/二级封锁协议

      • 防止了脏读

    • 事务隔离级别2/三级封锁协议

      • 保证重复读

    • 事务隔离级别3/可串行性封锁协议

      • 防止幻影读

  • 死锁与活锁

    • 活锁

      • 某个事务可能永远等下去

    • 死锁

      • 两个事务互相封锁

• 数据库的恢复

  • 恢复的原理

    • 事务故障

      • Rollback,回到事务运行前的状态(UNDO)

    • 系统故障

      • 先UNDO,同时需要将已提交的事务但可能存在内存且尚未写回外设的事务重新写回外设(REDO)

    • 介质故障

      • 装入数据库发生介质故障前的某个时刻的数据库副本，并重做自此时刻开始的所有成功事务

  • 恢复的实现技术

    • 数据转储：数据库DBA将数据库复制到磁带或磁盘的其他地方的过程，备份

      • 海量转储（完全转储）

      • 增量转储：每次备份在前一次基础上增加的数据

      • 差值转储：每次备份自上次完全转储依赖的数据库增加的数据

    • 日志文件

      • 以记录为单位

      • 以数据块为单位

• 数据库的复制与镜像

  • 数据库的复制

    • 对等复制：各个结点平等，可互相复制数据

    • 主从复制：数据只会从主数据库复制到从数据库

    • 级联复制：数组从主数据库复制到从数据库后，再从从数据库复制到其他数据库

  • 数据库的镜像

    • DBMS自动把整个数据库关键数据复制到另一个磁盘上，保证镜像数据与主数据一致性。

关系数据库设计理论

数据依赖

• 关系模式是静态的、稳定的；关系是动态的，不同时刻关系模式中的关系可能不同，但关系都必须满足关系模式中数据依赖关系集合F 指定的完整性约束。

• 影响数据库模式设计的主要是 U 和F，所以一般关系模式简化为：R（U， F）

• 分类

  • 函数依赖

  • 多值依赖

  • 连接依赖

• 相关概念

  • 函数依赖：R(U)是一个关系模式，U 是R 的属性集合，X和Y是U 的子集，对于R(U)的任意一个可能的关系r，如果r中不存在两个元组w，v，使得w[X]=v[X]而w[Y]<>v[Y]，称X函数决定Y，或Y函数依赖于X，记X->Y。

  • 平凡的和非平凡的函数依赖：关系模式R(U)，X和Y是U 的子集，如果X->Y，且Y⊄X,则称X->Y是非平凡的函数依赖，否则称平凡的函数依赖，我们讨论的都是非平凡的函数依赖。

  • 完全函数依赖和部分函数依赖：关系模式R(U)，如果X->Y，且对于X的真子集X’都有X’ -->Y，则称Y完全函数依赖于X，记X-f->Y。若Y不完全依赖于X则称Y部分依赖于X，记X-P- >Y。

  • 传递函数依赖：关系模式R(U)，如果X->Y，Y->Z，且Y-->X，则称Z传递函数依赖于X，记X-t->Z。

  • 码的重新定义：关系模式R(U,F)，K 为属性组合，若K-f->U，则K 是一个候选码。

  • 多值依赖：关系模式 R(U)属性集U，X、Y和Z是U不相交的子集，且Z＝U-X-Y，若关系模式R的任一关系r对于X的一个给定值，存在Y的一组值与之对应，且Y的这一组值与Z无关，称Y多值依赖于X，记X->->Y。当Z非空时称非平凡的多值依赖。

  • 连接依赖：关系模式R，其属性集U,X1,X2......Xn分别为U的子集，∪Xi＝U，如果对于R的每一个关系r都有r＝∞Xi，则称连接依赖(JD)在关系模式R上成立，记为*(X1,X2,......Xn),若某个Xi 就是R，称平凡的连接依赖。

范式

• 第一范式（1NF）

  • 如果一个关系模式 R 的所有属性都是原子的，即不可再分的基本数据项，则R∈1NF。

• 第二范式（2NF）

  • 如果一个关系模式R∈1NF，并且每一非主属性都完全依赖于R 的码，则R∈2NF。

• 第三范式（3NF）

  • 如果一个关系模式 R 中不存在非主属性对码的传递依赖，则R∈3NF。

• BC范式（BCNF）

  • 如果一个关系模式 R(U,F)∈1NF，对R中的任意一个非平凡的函数依赖X->Y，X 都含有候选码，则R∈BCNF。

• 第四范式（4NF）

  • 如果一个关系模式 R（U,F）∈1NF，对R 中的任意一个非平凡的多值依赖X－>－>Y，X 都含有候选码，则R∈4NF。

• 第五范式（5NF）

  • 如果一个关系模式 R(U,F)∈1NF，对R 中的任意一个连接依赖都都有候选码蕴涵，则R∈5NF。

关系模式的规范化

• 关系模式的规范化步骤

  • 1NF->2NF

    • 消除非主属性对码的部分函数依赖关系

  • 2NF->3NF

    • 消除非主属性对码的传递函数依赖关系

  • 3NF->BCNF

    • 消除主属性对码的部分函数依赖关系

  • BCNF->4NF

    • 消除非主属性的非平凡的多值依赖

  • 4NF->5NF

    • 消除非候选码蕴含的连接依赖

• 关系模式的分解

  • 具有无损连接性（可达4NF）

  • 保持函数依赖（可达3NF）

数据库设计

数据库设计的步骤

• 需求分析

• 概念结构设计

• 逻辑结构设计

• 数据库物理设计

• 数据库实施

• 数据库运行维护

需求分析

• 任务

  • 调查对象了解原系统工作概况，明确用户需求

• 重点

  • 调查、收集与分析用户在数据库管理中的信息要求、处理要求、安全要求和完整性要求

• 结果

  • DD(数据字典)、DFD(数据流图)

• 数据字典：详细数据收集和数据分析的结果

  • 数据项：不可再分的数据单位

  • 数据结构：数据之间的组合关系

  • 数据流：数据结构在系统内传输的路径

  • 数据存储：数据结构停留或保存的地方

  • 处理过程

概念结构设计

• 逐一设计E-R图并合并

逻辑结构设计

• 概念结构转化为某一数据模型

  • 一个实体型转化为一个关系模式

  • 一个m:n的联系转化为一个关系模式，码为各实体码的组合

  • 一个1:n的联系转化为一个独立的关系模式，码为n端实体码，也可以与n端关系模式合并

  • 一个1:1的联系转化为一个独立的关系模式，每个实体的码均是候选码，也可与任一端关系模式合并

  • 三个及三个以上实体间的一个多元联系转化为一个关系模式

  • 同一实体集的实体间的联系即自联系

  • 具有相同码的各模式可以合并

数据库物理设计

• 确定数据库的物理结构

  • 综合考虑存取事件、存储空间利用率和维护代价

  • 设计数据存取路径

  • 确定数据存放位置

  • 确定系统配置

数据库实施

• 定义数据库结构

• 数据装载

• 编制和调试应用程序

• 数据库试运行

数据库运行维护

• 数据库的转储和恢复

• 数据库的安全性和完整性控制

• 数据库性能的监督、分析和改进

• 数据库的重组织和重构造