自用数据库复习

数据字典

数据库系统中存放三层结构定义的数据库称为数据字典（DD），
对数据库的操作都要通过DD才能实现。
DD系统中还存放数据库运行是的统计信息。
管理DD的系统称为DD系统

DBMS

是指数据库系统中对数据进行管理的软件系统，它是数据库系统
的核心组成部分。对DB的一切操作，包括定义、查询、更新及控制，
都是通过DBMS进行的。

DBMS的主要功能

• DDL
• DML
• 数据库保护功能：4个数据控制子系统
• 数据库的维护功能：各个实用程序构成
• 数据字典：DD子系统

DBS的组成

• 数据库
• 硬件
• 软件
• 数据库管理员

数据库

全部数据的集合。DB分为两类，一类是应用数据集合，称为物理数据库，
它是数据库的主体；另一类是各级数据结构的描述，称为描述数据库，由DD系统管理。

软件

包括DBMS、OS、各种主语言和应用开发支撑软件等程序。

DBA数据库管理员

• 定义模式
• 定义内模式
• 与用户联络
• 定义安全性规则
• 定义完整性规则
• 数据库的转存与恢复工作

三层模式两级映像

• 外模式 用户与数据库的接口
• 逻辑模式 数据库中全部数据的整体逻辑结构的描述。
• 内模式

高度的数据独立性

应用程序和数据库的数据结构之间相互独立。其分为两类，一是物理数据独立性；二是逻辑数据独立性。
目的是为了保存外模式和应用程序不受影响。

关系模型基本概念

字段 、 属性 、 列
字段值 、 属性值
记录类型、 关系模式
记录 元组、 行
属性相同的元组集合、 关系、 表
属性个数、 元数
元组个数、 基数
域 值得集合、 属性值的取值范围
元组数目无限、 无限关系、 计算机研究有限关系

关系模式的3类完整性规则

实体完整性、 主键不为空
参照完整性、 外键是否为空视具体而定

备注：1外键的值必须参照相应主键的表中的主键值，或者可以为空。
2主键和外键可以在同一个关系模式中
用户自定义完整性、 具体的数据约束

关系模式的3层体系结构

关系模式、 记录类型、 逻辑模式
子模式、 用户描述、 外模式
存储模式、 描述如何物理存储、 内模式

关系模式的形式定义和优点

数据结构、 简明精确
数据操作、 独立于数据存储
数据完整性规则

关系查询语言和关系演算

DML、 查询 更新
关系查询语言: 关系代数、 关系演算、 关系逻辑

关系代数

5基本操作： 并 差 笛卡尔积 投影 选择
4组合操作： 交 连接 自然连接 除法
7扩充操作： 改名 广义投影 赋值 外连接 外部并 半连接 聚集操作

等值连接、 笛卡尔积+等值
自然连接、 等值连接+去除重复等值属性列

否定的操作、 差操作
检索不学C2课的学生

错误方式 πSNAME，AGE（σCNO≠‘C2’（S⋈SC））
一定要用“差”的形式：
πSNAME，AGE（S）－πSNAME，AGE（σCNO=‘C2’（S⋈SC））

全部特征、 除法操作

πSNO，CNO（SC）÷πCNO（C）

SQL基本概念

基本表、 本身独立的表、 一个关系一个基本表、 一个基本表一个存储文件 一个表诺干索引

视图、 一个以上基本表导出的表

复习题库

数据库操纵功能、 DML、 查询更新
数据库控制功能、 DCL、 revoke commint等

函数依赖

FD（Function Dependency）、 形为x->Y的命题 、 函数依赖
F、 函数依赖集
被F逻辑蕴涵的函数依赖、 F |=X->Y
函数依赖集（F）的闭包、 F+、 被（F）逻辑蕴涵的函数依赖的集合
FD和关键码联系、 超键、 候选码
属性集X的闭包、 X+ = {属性A|F|=X->A}
FD推理规则完备性、 函数依赖集推出的X->Y都在其闭包中
FD集的最小依赖集、 （超键之于候选键）

• 每个FD左边不冗余
• FD间不冗余
• 每个FD右边单属性

候选码的求法

首先来看候选键的定义：若关系中的某一属性组的值能唯一地标识一个元组。

若W是候选键，则必须满足两个条件：W的闭包是U；W没有冗余。

设关系模式R中U=ABC.......等N个属性，U中的属性在FD中的情况：

1. 只在左部出现; 一定存在于某候选码当中
2. 不在左右出现; 一定存在于所有候选码中
3. 只在右部出现; 一定不在候选码中
4. 左右出现; 与候选码组合，满足属性集闭包是U，确定候选码。

关系模式的分解

模式分解、 计算机中数据存储、 不在泛关系r、 在数据库模式中
无损分解、 损失分解、 投影连接能否恢复、 寄生元组
泛关系假设、 先存在r泛关系再讨论分解、 无假设之与悬挂元组（自然连接被丢失的）
模式分解优点、 消除冗余操作异常、 分解的数据库可以存储悬挂元组存储泛关系中无法存储的信息
模式分解缺点、 检索需连接、 有泛关系可能寄生元组、 无泛关系可能悬挂元组

无损分解测试

建立表格

• 属性Aj做列、 模式Ri做行；
• 行列有交叉填aj、 行列无交叉填bij。
  追踪Chase
• 根据依赖集F|=X->Y；
• 两行X相同则修改使两行Y也相同
• 两行Y、 aj > 下标小的bij
  有一个行全aj 是无损分解

无损分解充分必要定理：

• Ri是关系模式R的一个分解
• （R1交R2）->（R1-R2）或(R2-R1) 注释：即推导属于FD，在依赖集中

保持函数依赖FD的分解特性

• 1验证F中的每个FD是否被任意数据库模式逻辑蕴涵
• 2 关系模式函数依赖闭包F+ = 全部的数据库模式函数依赖闭包
• 无损分解特性与保持函数依赖分解特性无关、 结合模式分解的优缺点
• 模式分解与模式等价、 数据等价+语义等价
• 数据等价、 不会丢失信息，无损分解特性
• 语义等价、 数据库模式有相同的依赖集闭包，保持FD分解特性

分解特性举例、 两个特性

关系模式R(ABC),ρ={AB,AC}，分析在F1={A->B},
F2={A->C,B->C},F3={B->A},F4-{C->B},B->A}下的分解特性？
1分析无损分解：R1交R2=A 、 AB-AC=B 、 AC-AB=C;
即满足A->B或者A->C依赖的是无损分解：F1、F2。
2分析保持FD分解：关系模式函数依赖闭包 = 全部数据库模式函数依赖闭包；
即：F1满足；F2不满足，丢失B->C；F3满足； F4不满足，丢失C->B；

关系模式的范式、 （1NF、 2NF）过时、 3NF、 BCNF

• 1NF、 关系r的属性值不可再分
• 2NF、 1NF+每个非主属性完全函数依赖于候选键
• 3NF、 1NF+每个非主属性都不传递依赖与R的候选键。（如果有，分解后，这便是外键的由来）
• DCNF、 1NF+每个属性都不传递依赖于R的候选键
• 平凡FD: 类似 A ->AB ;ABC->ABC等无价值的FD
• 主属性：包含候选码的属性组
• 传递依赖：类似A->C,C->B;

定理：满足BCDF->满足3NF

• BCNF、 无损分解、 不一定保持FD分解
• 关系模式R优先BCDF分解,如果BCDF不满足保持FD，则分解为3NF

3NF分解算法

关系模式R(U),主键W，R上还有FD X->Z。并且Z是非主属性（Z不属于X）,
X不是候选键，这样W->Z就是一个传递依赖。此时需要R分解为2个模式
R1(XZ),主键X;
R2(Y),其中Y=U-Z,主键仍然W，外键X（参照R1）。
如果R1、R2还不是3NF，那么在重复上述，直到数据库模式为3NF。

BCNF分解算法

和3NF分解算法很像，只是FD X->Z，其中Z也可以是主属性。

总结3NF BCNF分解算法： 消除传递依赖，产生外键

分解成3NF模式集的合成算法？

• 求F的最小依赖集，把左部相同的FD合并性
• 对每个最小依赖集中的FD X->Y 去构成一个模式XY
• 构成的模式集中，如果每个模式都不包含R的候选键，那么把候选键单独作为一个模式。

分解3NF举例
关系模式R（ABCDE）,R的最小依赖集{A->B,C->D}。
从依赖集可知R的候选码为ACE。
先根据最小依赖集 生成ρ{AB,CD}模式，不包含候选码ACE
加入候选码的模式，得到 ρ{AB,CD,ACE}

局部依赖和传递依赖是模式产生冗余和异常的两个重要原因，
由于3NF只消除了非主属性对候选键的局部依赖和传递依赖，
虽然性能已经很好了，但仍然还可能有小部分存储异常。

模式设计方法

• ρ是BCNF或3NF模式集
• 无损分解
• 保持函数依赖集

错题集

1. 数据库系统的核心是（ D ）
   A） 编译系统 B）数据库 C）操作系统 D）数据库管理系统

2. 表达用户使用观点的数据库局部逻辑结构的模型是（ C ）
   A） 概念模型 B）逻辑模型 C）外部模型 D）内部模型
   备注： 概念模型；表达用户使用观点的数据库全局逻辑结构的模型

3．对关系数据库来讲，下面（ C ）说法是错误的
A）外键和相应的主键可以不同名，只要定义在相同值域上即可
B）不同的属性可对应于同一值域
C）行的顺序可以任意交换，但列的顺序不能任意交换
D） 关系中的任意两个元组不能完全相同

注释
1 外键的值必须参照相应主键的表中的主键值，或者可以为空。
2 主键和外键可以在同一个关系模式中

4．数据库系统生存期分七个阶段：规划、需求分析、概念设计、逻辑设计、物理设计、实现、运行维护。

5．下面有关模式分解的叙述中，不正确的是（D ）
A） 若一个模式分解保持函数依赖，则该分解一定具有无损连接性。
B） 若要求分解保持函数依赖，那么模式分解可以达到3NF，但不一定能达到BCNF
C） 若要求分解既具有无损连接性，又保持函数依赖，则模式分解可以达到3NF，但不一定能达到BCNF
D） 若要求分解具有无损连接性，那么模式分解一定可以达到BCNF

注释
A选项，保持FD—>无损分解，不能逆推
B选项，BCNF->3NF,不能逆推
C选项, BCNF 满足无损分解，不一定满足保持FD分解；所以优先采用BCNF分解，如果不行才选用3NF分解。

3．下述哪一条不是由于关系模式设计不当而引起的（B ）
A） 数据冗余 B) 丢失修改 C) 插入异常 D) 修改异常

1．任何一个二元关系在函数依赖的范畴内必能达到（ D ）
A） 1NF B）2NF C）3NF D）BCNF

备注 二元关系

设关系模式R（ABCD），F是R上成立的FD集，F={AB→CD，A→D}。
1）试说明R不是2NF模式的理由。
2）试把R分解成2NF模式集。

注释：
1 R的候选键是AB，则非主属性为C和D，并且AB→D成立。而已知A→D，因此AB→D为非主属性D对候选键的局部依赖。
2 R分解为：R1（AD）主键是A；
R2（ABC）主键是AB，外键是A。
对于3NF的判断：是否？非主属性传递依赖于候选键

SQL模式定义

SQL模式、 模式名和模式拥有者的账户、 包含每个元素定义（基本表、视图、索引等）；
一个模式 、 一个存储空间、 基本表的集合；

模式建立 : Creat Schema <模式名> Authorization 用户名;
模式撤销、 Drop Schema STU [Cascade（级联）|Restrict(约束)]；
Cascade级联销毁全部元素、 Restrict约束。

习惯性采用DATABASE替换Schema词, 语句为 Create DATABASE 。
SQL语句不区分大小写。

基本表

基本表创建

Create Table <表名> （<列名 类型>，<完整性约束>）;

Creat Table T (t# char(4)，tname char(8) Not NULL, Primary Key(t#),Foreign Key(tname)References T2(tname2#));
完整性约束了t#为主键，tname为外键且非空且指定了对应T2表中tname2的参照完整性

基本表修改

Alter（改变成分）、 Add、 Drop、 Modify(修改)

• 新列 Alter Table <表名> Add <列名> <类型>
• 删列 Alter Table <表名> Drop <列名> [Cascade|Restrict]
• 修改原有列的类型\宽度 Alter Table <表名> Modify <列名> <类型>

基本表撤销

Drop Table <表名> [Cascade|restrict]
索引 : 功能仅限查询、 起到主键的作用

• 创建索引 Create [Unique] Index <索引名> On <表名>（<列名>）
• 撤销索引 Drop Index <索引名>

SQL数据查询

句型 Select - From - Where -;

Where 子句、 条件表达式

• 算术运算符
• 逻辑运算符 and 、 or 、 not
• 集合成员资格运算符 in 、 not in
• 谓词 exists、 all、 some、 unique(唯一)
• 聚合函数 avg、min 、 max 、 sum 、 count
• 子句的运算对象可以是另一个select子句，即嵌套select语句

where s# in (select语句)
where exists (select语句)

Select子句

• 聚合函数 count(*) count|sum|avg|max|min(<列名>)

select Count(*)，Avg(age)
select count(distinct s#) form sc 因为可以选修多门，加了distinct，说明统计选课的人数，而不是上选修课的人次树

select完整句法

where行条件子句、 group分组子句、 having组条件子句、 order排序子句

select<列名序列或列表达式序列>
from<表和(或)视图序列>
[where<行条件表达式>]
[group by<列名序列>
    [having<组条件表达式>]]
[order by <列名[asc|desc]>,...]

select句法执行过程：

1. 读取form子句表、视图的数据，执行笛卡尔积操作
2. 选取满足where子句中条件表达式的元组
3. 按group子句指定列的值分组，同时提取满足having子句中组条件表达式的那些组
4. 按select子句中给出的列名或列表达式求值输出
5. order子句对输出的目标进行排序，升序排列Asc或者降序排列Desc。

(1)使用带有EXISTS谓词的子查询：查询所有选修了1号课程的学生姓名。

select sname from student where
exists (select sno from sc where sno=student.sno and cno='1')

> (2)查询选修了全部课程的学生姓名。
>>  ```
1.
select Sname from student where Sno IN    
(select Sno from SC 
group by Sno    
having count(*) = (select count(*) from course ))

select Sname from student S,
(select Sno from SC
group by Sno
having count() = (select count() from course )) C
where S.sno=C.sno

>> ```
3.
select sname from student 
where not exists ( select * from course where not exists (select * from sc where sc.sno=student.sno and sc.cno=course.cno))

(3)使用带有EXISTS谓词的子查询：查询所有选修了1号课程的学生姓名

select sname from student where
exists (select sno from sc where sno=student.sno and cno='1)

Unique和distinct
> Unique是约束完整性的一种，distinct用于查询


####SQL更新 insert into 、 update set 、delect from
> 数据插入格式：
（1）单元组的插入
```INSERT  INTO  基本表名 [（列名表）] VALUES（元组值）```
（2）子查询结果的插入
``` INSERT  INTO  基本表名 [(列名表)]＜SELECT查询语句＞```
数据修改格式：
```UPDATE  基本表名
SET 列名=值表达式[，列名=值表达式…]  
[WHERE 条件表达式]```
数据删除格式：
```DELETE FROM基本表名
[WHERE 条件表达式]```
>>将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

insert into student values('95020','陈冬','男','18','IS')

>>Student_1表的内容插入到Student表

insert into Student1
select * from student

>>将学生95001的年龄改为22岁

update student set sage=22 where sno='95001'

>>将计算机科学系全体学生的成绩置零。

UPDATE SC
SET grade=0
WHERE 'CS'=(select Sdept FROM Student WHERE Student.Sno = SC.Sno)

>>删除计算机科学系所有学生的选课记录。

delete FROM sc where 'CS'= (select sdept from student where student.sno=sc.sno)

####视图 （ CREATE、DROP 、 INSERT、UPDATE、DELETE）
> 
CREATE VIEW <视图名>（列表序列）
AS <SELECT 查询语句>

CREATE VIEW STUDENT_GRADE as
select STUDENT.Sno,SNAME,CNAME,GRADE from STUDENT,sc,course where STUDENT.sno=sc.sno and sc.cno=course.cno

DROP VIEW 视图名
更新同表，但是更新有条件？
1. 行列子集视图（从单个基本表选择、投影导出的，且包含主键）
2. 允许更新的视图在定义时必须加上"with check option"