本文最后更新于：June 18, 2019 pm

《数据库原理》部分重点内容，小七自己整理的面向期末考考题的复习提纲。

1、题型

一共10题，每题10分。

问答题：3题（1题是第一章数据库概念相关、1题三级模式+二级映像–数据独立性、1题是数据库恢复或并发控制协议内容+协议解决的问题）
编程题：5题（4题查询相关的，1题是安全性控制或完整性控制）
分析题：2题（1题是模式分解、范式相关的，1题是设计小型数据库）

2、问答题（3*10）

2.1 DBMS

数据库管理系统（Database Management System），简称DBMS，是位于用户与操作系统之间的一层数据管理软件。

数据库系统的特点

数据结构化
数据的高共享性
数据独立性
DBMS对数据统一管理和控制

2.2 数据管理技术的发展过程

人工管理阶段(40年代中–50年代中)
文件系统阶段(50年代末–60年代中)
数据库系统阶段(60年代末–现在)

产生的背景	人工管理(40年代中–50年代中)	文件系统(50年代末–60年代中)	数据库系统(60年代末–现在)
应用需求	科学计算	科学计算、管理	大规模管理
硬件水平	无直接存取存储设备	磁盘、磁鼓	大容量磁盘
软件水平	没有操作系统	有文件系统	有数据库管理系统
处理方式	批处理	联机实时处理、批处理	联机实时处理,分布处理,批处理

2.3 数据模型

通俗地讲数据模型就是现实世界的模拟，在数据库中用数据模型这个工具来抽象、表示和处理现实世界中的数据和信息。

数据模型分成两个不同的层次

概念模型（用户），也称信息模型，它是按用户的观点来对数据和信息建模。
数据模型（计算机），主要包括网状模型、层次模型、关系模型、面向对象模型等，它是按计算机系统的观点对数据建模。

2.4 数据模型的组成要素

数据结构
- 什么是数据结构：对象类型的集合
- 两类对象：（1）与数据类型、内容、性质有关的对象（2）与数据之间联系有关的对象
- 数据结构是对系统静态特性的描述
数据操作
- 数据操作：对数据库中各种对象（型）的实例（值）允许执行的操作及有关的操作规则
- 数据操作的类型：（1）检索（2）更新（包括插入、删除、修改）
- 数据操作是对系统动态特性的描述。
数据的约束条件
- 一组完整性规则的集合。
- 完整性规则是给定的数据模型中数据及其联系所具有的制约和储存规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容。

2.5 数据库系统的三级模式结构

2.5.1 三级模式

三级模式结构：外模式、模式、内模式。
外模式（也称子模式或用户模式），数据库用户（包括应用程序员和最终用户）使用的局部数据的逻辑结构。
模式（也称逻辑模式），数据库中全体数据的逻辑结构
内模式（也称存储模式），是数据物理结构和存储方式的描述。
一个数据库只有一个模式和一个内模式，但是可以有多个外模式。
模式与外模式的关系：一对多
- 外模式通常是模式的子集
- 一个数据库可以有多个外模式。反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求
- 对模式中同一数据，在外模式中的结构、类型、长度、保密级别等都可以不同
外模式与应用的关系：一对多
- 同一外模式也可以为某一用户的多个应用系统所使用
- 但一个应用程序只能使用一个外模式

三级模式是对数据的三个抽象级别，二级映象在DBMS内部实现这三个抽象层次的联系和转换。

2.5.2 外模式/模式映象（逻辑独立性）

定义外模式与模式之间的对应关系
每一个外模式都对应一个外模式／模式映象
外模式／模式映象的用途：保证数据的逻辑独立性
- 当模式改变时，数据库管理员修改有关的外模式／模式映象，使外模式保持不变
- 应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

2.5.3 模式/内模式映象（物理独立性）

模式／内模式映象定义了数据全局逻辑结构与存储结构之间的对应关系。例如，说明逻辑记录和字段在内部是如何表示的
数据库中模式／内模式映象是唯一的
模式／内模式映象的用途：保证数据的物理独立性
- 当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式／内模式映象，使模式保持不变
- 应用程序不受影响。保证了数据与程序的物理独立性，简称数据的物理独立性。

2.6 事务

2.6.1 事务的基本概念

事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位，事务是恢复和并发控制的基本单位。

2.6.2 事务的特性（ACID）

原子性（Atomicity）

事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（Consistency）

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态

一致性状态：数据库中只包含成功事务提交的结果
不一致状态：数据库中包含失败事务的结果

隔离性（Isolation）

对并发执行而言

一个事务的执行不能被其他事务干扰。
一个事务内部的操作及使用的数据对其他并发事务是隔离的。
并发执行的各个事务之间不能互相干扰

持续性（Durability ）

持续性也称永久性（Permanence）

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。
接下来的其他操作或故障不应该对其执行结果有任何影响。

破坏事务ACID特性的因素

多个事务并行运行时，不同事务的操作交叉执行
事务在运行过程中被强行停止

2.7 故障的种类

2.7.1 事务故障（应该不会考）

2.7.1.1 什么是事务故障

某个事务在运行过程中由于种种原因未运行至正常终止点就夭折了

2.7.1.2 事务故障的恢复

先UBDO再ROLLBACK

（1）撤消事务（UNDO）（2）强行回滚**(ROLLBACK)**该事务

清除该事务对数据库的所有修改，使得这个事务像根本没有启动过一样。

2.7.1.3 事务故障的常见原因

输入数据有误、运算溢出、违反了某些完整性限制、某些应用程序出错、并行事务发生死锁

2.7.2 系统故障

2.7.2.1 什么是系统故障

整个系统的正常运行突然被破坏
所有正在运行的事务都非正常终止
内存中数据库缓冲区的信息全部丢失
外部存储设备上的数据未受影响

2.7.2.2 系统故障的恢复

先UNDO再REDO

正向扫描日志文件（即从头扫描日志文件）

对Undo队列事务进行UNDO处理

对Redo队列事务进行REDO处理

2.7.2.3 系统故障的常见原因

操作系统或DBMS代码错误、操作员操作失误、特定类型的硬件错误(如CPU故障)、突然停电

2.7.3 介质故障

2.7.3.1 什么是介质故障

硬件故障使存储在外存中的数据部分丢失或全部丢失。

介质故障比前两类故障的可能性小得多，但破坏性大得多

2.7.3.2 介质故障的恢复

先找备份点再重新执行备份点之后的成功事务

装入数据库发生介质故障前某个时刻的数据副本

（REDO）重做自此时始的所有成功事务，将这些事务已提交的结果重新记入数据库

恢复操作的基本原理：冗余
利用存储在系统其它地方的冗余数据来重建数据库中已被破坏或不正确的那部分数据

2.7.3.3 介质故障的常见原因

硬件故障：磁盘损坏、磁头碰撞、操作系统的某种潜在错误、瞬时强磁场干扰

2.8 数据转储

转储是指DBA将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的数据文本称为后备副本或后援副本。

2.8.1 静态转储与动态转储

静态转储

在系统中无运行事务时进行转储，转储开始时数据库处于一致性状态，转储期间不允许对数据库的任何存取、修改活动。

优点：实现简单；
缺点：降低了数据库的可用性（转储必须等用户事务结束、新的事务必须等转储结束）

动态转储

转储操作与用户事务并发进行，转储期间允许对数据库进行存取或修改。

优点：不用等待正在运行的用户事务结束、不会影响新事务的运行
缺点：不能保证副本中的数据正确有效

利用动态转储得到的副本进行故障恢复，需要把动态转储期间各事务对数据库的修改活动登记下来，建立日志文件，后备副本加上日志文件才能把数据库恢复到某一时刻的正确状态。

2.8.2 海量转储与增量转储

海量转储: 每次转储全部数据库

增量转储: 只转储上次转储后更新过的数据

海量转储与增量转储比较：

从恢复角度看，使用海量转储得到的后备副本进行恢复往往更方便

但如果数据库很大，事务处理又十分频繁，则增量转储方式更实用更有效

2.9 日志文件

日志文件(log)
是用来记录事务对数据库的更新操作的文件
日志文件的用途：
- 进行事务故障恢复
- 进行系统故障恢复
- 协助后备副本进行介质故障恢复

2.10 并发控制

2.10.1 三种数据不一致的情况

丢失修改(Lost Update)

丢失修改是指事务1与事务2从数据库中读入同一数据并修改，事务2的提交结果破坏了事务1提交的结果，导致事务1的修改被丢失。
不可重复读(Non-Repeatable Read)

不可重复读是指事务1读取数据后，事务2执行更新操作，使事务1无法再现前一次读取结果。
读“脏”数据(Dirty Read)

事务1修改某一数据，并将其写回磁盘
事务2读取同一数据后
事务1由于某种原因被撤消，这时事务1已修改过的数据恢复原值
事务2读到的数据就与数据库中的数据不一致，是不正确的数据，又称为“脏”数据。

产生三种数据不一致的主要原因就是操作时破坏了事务的隔离性。

2.10.2 封锁(Locking)

封锁的基本类型：

排它锁(Exclusive Locks，简称X锁)，通常X锁用于事务修改数据(写入数据)时，所以也称为写锁。

共享锁(Share Locks，简称S锁)通常S锁用于事务读取数据时，所以也称为读锁。
封锁协议

一级封锁协议：事务T在修改数据R之前须先进行X锁，直到事务结束才释放。

二级封锁协议：在一级上增加事务T在读取数据R之前先进行S锁，读完之后立即释放这个S锁。

三级封锁协议：在一级上增加事务T在读取数据R之前先进行S锁，直到事务结束释放。

2.10.3 二段锁

二段协议是指所有事务必须分二个阶段对数据对象进行加锁和解锁：

在对任何数据进行读、写操作之前，首先申请并获得对该数据的封锁。
在释放一个封锁后，事务不再申请和获得任何其他的封锁。

	不丢失修改	不读脏数据	可重复读
二段锁	√	×	√

3、编程题（5*10）

3.1 视图的用法

-- [例4.14]建立计算机系学生的视图
-- 把对该视图的SELECT权限授予王平
-- 把该视图上的所有操作权限授予张明。

-- 先建立视图CS_Student
CREATE VIEW CS_Student	
AS SELECT*
FROM Student 
WHERE Sdept='CS';

-- 王平老师只能检索计算机系学生的信息
GRANT SELECT	
ON CS_Student 
TO 王平;

-- 系主任具有检索和增删改
--计算机系学生信息的所有权限
GRANT ALLPRIVILEGES	
ON CS_Student 
TO 张明;

3.2 9个SQL动词的用法

3.2.1 CREATE，DROP，ALTER

3.2.1.1 CREATE

-- SQL CREATE DATABASE 语法
CREATE DATABASE dbname;

-- SQL CREATE TABLE 语法
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

-- Create table S
CREATE TABLE S 
(
    SNO VARCHAR(20),
    SNAME VARCHAR(50),
    STATUS INT,
    CITY VARCHAR(50)
) CHARSET = UTF8;

3.2.1.2 DROP

-- 用于 MySQL 的 DROP INDEX 语法：
ALTER TABLE table_name 
DROP INDEX index_name

-- DROP TABLE 语句用于删除表。
DROP TABLE table_name

-- DROP DATABASE 语句用于删除数据库。
DROP DATABASE database_name

3.2.1.3 ALTER

-- 在表中添加列:
ALTER TABLE 表名
ADD 列名 数据类型

--删除表中的列:
ALTER TABLE 表名
DROP COLUMN 列名

-- 创建主键
ALTER TABLE E 
ADD PRIMARY KEY (ENO);

ALTER TABLE P 
ADD PRIMARY KEY (PNO);

-- 创建外键
ALTER TABLE E 
ADD FOREIGN KEY (PNO) REFERENCES P(PNO);

-- 创建约束C1，
-- 定义职工年龄不得超过60岁
ALTER TABLE E 
ADD CONSTRAINT C1 
CHECK (AGE < 60);

3.2.2 SELECT，INSERT，DELETE，UPDATE

3.2.2.1 SELECT

-- 5.1 找出所有供应商的姓名和所在城市
SELECT SNAME,CITY 
FROM S;

-- 5.2 找出所有零件的名称、颜色、重量
SELECT PNAME,COLOR,WEIGHT 
FROM P;

-- 5.3 找出使用供应商S1所供应零件的工程号码
SELECT JNO 
FROM SPJ 
WHERE SNO = 'S1' 
ORDER BY JNO;

-- 5.4 找出工程项目J2使用的各种零件的名称及其数量
SELECT PNAME,QTY 
FROM P,SPJ 
WHERE P.PNO = SPJ.PNO 
AND SPJ.JNO = 'J2';

-- 5.5 找出上海厂商供应的所有零件号码
SELECT DISTINCT PNO 
FROM S,SPJ 
WHERE S.SNO = SPJ.SNO 
AND CITY = '上海' 
ORDER BY PNO;

-- 5.6 找出使用上海产的零件的工程名称
SELECT DISTINCT JNAME 
FROM S,J,SPJ 
WHERE S.SNO = SPJ.SNO 
AND J.JNO = SPJ.JNO 
AND S.CITY = '上海';


-- 5.7 找出没有使用天津产的零件的工程号码
SELECT DISTINCT JNO FROM J
WHERE JNO NOT IN 
(
    SELECT DISTINCT SPJ.JNO FROM S,J,SPJ 
    WHERE S.SNO = SPJ.SNO AND J.JNO = SPJ.JNO 
    AND S.CITY = '天津'
    ORDER BY SPJ.JNO
);

3.2.2.2 INSERT

-- Insert data into table S
INSERT INTO S 
(SNO,SNAME,STATUS,CITY) 
VALUES 
('S1','精益',20,'天津');

3.2.2.3 DELETE

-- DELETE完整语法
DELETE FROM table_name
WHERE some_column=some_value;

-- 5.10 从供应商关系中删除S2的记录，
-- 并从供应情况关系中删除相应的记录
-- 先删除外键再删除主键
DELETE FROM SPJ 
WHERE SNO = 'S2'; 

DELETE FROM S 
WHERE SNO = 'S2';

3.2.2.4 UPDATE

-- UPDATA完整语法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

-- 5.8 把全部红色零件的颜色改为蓝色
UPDATE P 
SET COLOR = '蓝' 
WHERE COLOR = '红';

-- 5.9 由S5供给J4的零件P6改为由S3供应
UPDATE SPJ 
SET SNO = 'S3' 
WHERE SNO = 'S5' AND JNO = 'J4' AND PNO = 'P6';

3.2.3 GRANT，REVOKE（安全性控制）

-- ALL PRIVILIGES 表示所有权限
-- PUBLIC或者ALL 表示所有用户

-- GRANT完整语法
GRANT <权限>[,<权限>]…
ON <对象类型 对象名>[,<对象类型 对象名>…]
TO <用户>[,<用户>]…
[WITH GRANT OPTION];
-- 指定了WITH GRANT OPTION子句：
-- 获得某种权限的用户还可以把这种权限再授予别的用户

-- 例题：把查询Student表和修改学生学号的权限授给用户U4。
GRANT SELECT, UPDATE(Sno)
ON TABLE Student 
TO U4;

-- REVOKE完整语法
REVOKE <权限>[,<权限>]…
ON <对象类型 对象名>[,<对象类型 对象名>…]
FROM <用户>[,<用户>]…
[CASCADE|RESTRICT];
-- CASCADE表示级联收回权限
-- RESTRICT是默认选项，不级联收回

3.3 主键和外键的用法（完整性控制）

-- 插入主键语法
ALTER TABLE 表名 
ADD PRIMARY KEY 主键列;

-- 设置主键
ALTER TABLE S 
ADD PRIMARY KEY (SNO);

-- 插入外键语法
ALTER TABLE 表名 
ADD FOREIGN KEY 外键列 
REFERENCES 表名（主键列）;

-- 设置外键
ALTER TABLE SPJ 
ADD FOREIGN KEY (SNO) 
REFERENCES S(SNO);

3.4 约束CONSTRAINT

--[例10] 创建Student表，要求：
--（1）学号在90000～99999之间
--（2）姓名不能为空
--（3）年龄小于30
--（4）性别只能是 '男' 或 '女' 

CREATE TABLE Student
( 
    Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999), 
    Sname CHAR(20) CONSTRAINT C2 NOT NULL, 
    Sage SMALLINT CONSTRAINT C3 CHECK(Sage < 30), 
    Ssex CHAR(2) CONSTRAINT C4 CHECK(Ssex IN('男','女')), 
    Sdept CHAR(20), 
    CONSTRAINT StudentPK PRIMARY KEY(Sno)
)


-- 创建约束C1，定义职工年龄不得超过60岁
ALTER TABLE E 
ADD CONSTRAINT C1 
CHECK (AGE < 60);


-- 当表已被创建时，
-- 如需在 "P_Id" 列创建 UNIQUE 约束，
-- 请使用下面的 SQL：
ALTER TABLE Persons
ADD UNIQUE (P_Id)


-- 如需命名 UNIQUE 约束（此处为C1），
-- 并定义多个列的 UNIQUE 约束，
-- 请使用下面的 SQL 语法：
ALTER TABLE Persons
ADD CONSTRAINT C1 UNIQUE (P_Id,LastName)

4、分析题（2*10）

4.1 范式

看课本6.2章对应的例题

4.1.1 概念（了解即可）

范式：Normal Format，是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题：保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储：终极目标是为了减少数据的冗余。

范式：是一种分层结构的规范，分为六层：每一次层都比上一层更加严格：若要满足下一层范式，前提是满足上一层范式。

六层范式：1NF，2NF，3NF，BCNF，4NF，5NF，1NF要求最低；5NF要求最严格。

Mysql属于关系型数据库：有空间浪费：也是致力于节省存储空间：与范式所有解决的问题不谋而合：在设计数据库的时候，会利用到范式来指导设计。

但是数据库不单是要解决空间问题，要保证效率问题：范式只为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现：一般情况下，只有前三种范式需要满足。范式在数据库的设计当中是有指导意义：但是不是强制规范。

一个低一级范式的关系模式通过模式分解（schema decomposition）可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化（normalization）。

4.1.2 1NF

第一范式：在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理（拆分），那么说表的设计不满足第一范式：第一范式要求字段的数据具有原子性：不可再分。（关键还要看实际业务需求）

4.1.3 2NF

第二范式：在数据表设计的过程中如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键的部分）：存在字段依赖主键的部分的问题，称之为部分依赖：第二范式就是要解决表设计不允许出现部分依赖。

4.1.4 3NF

第三范式：理论上讲，应该一张表中的所有字段都应该直接依赖主键（逻辑主键：代表的是业务主键），如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键：把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。

4.2 数据库设计

4.2.1 需求说明

先陈述清楚所有的需求。需求分析就是分析用户的需要与要求。

需求分析是设计数据库的起点
需求分析的结果是否准确地反映了用户的实际要求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用

4.2.2 E-R图和关系模式

E-R图

画一些简单的ER图来描述数据表。

关系模式

对每个实体定义的属性如下所示。加粗的为实体的码。

顾客：{顾客号，顾客名，地址，电话，信贷状况，账目余额}

订单：{订单号，顾客号，订货项数，订货日期，交货日期，工种号，生产地点}

订单细则：{订单号，细则号，零件号，订货数，金额}

应收账款：{顾客号，订单号，发票号，应收金额，支付日期，支付金额，当前余额，货款限额}

产品：{产品号，产品名，单价，重量}

折扣规则：{产品号，订货量，折扣}

4.2.3 建表叙述

sre

stu database

Frp内网穿透实现MS远程桌面和SSH Previous

Ubuntu18.04升级Linux内核 Next

数据库原理部分重点内容