《数据库原理》总纲

数据库原理复习专栏

• $\LARGE{}说明$
  
  • 标题后标注☆的为重点考点
  • 标题后标注%的不是考点
  • 未标注的为一般考点
• $\LARGE{}更新日志$
  
  • [2016-01-13 14:34] 完成第3章作业习题、弱实体集相关和扩展关系代数
  • [2016-01-12 10:19] 移除非考点
  • [2016-01-12 22:20] 完成了PPT上布置的课后习题
• $\LARGE{}目录$

1. 关系数据模型

数据模型概述

• 关系数据模型，包括对象关系模型的扩展
• 半结构化数据模型，包括XML和相关的标准

关系模型基础

• 属性：关系的列
• 模式：关系名及其属性的组合，如Movie(title, year, ...)
• 元组：关系中除属性名所在行意外的其它行
• 域：如string，date, integer等
• 关系的等价描述：两列交换得到的两个关系等价
• 关系实例：一个给定关系中元组的集合
• 关系上的键：由关系的一组属性集组成，任何两个元组在定义键的属性集上取值均不同

在SQL中定义关系模式

• SQL中的关系
  
  • 表：存储的关系
  • 视图：通过计算来定义的关系
  • 临时表：执行数据查询和更新时由SQL处理程序临时构造
• 数据类型
  
  • 固定长度或可变长字符串：CHAR(n)和VARCHAR(n)
  • 固定或可变长位串：BIT(n)和BIT VARYING(n)
  • 逻辑类型：BOOLEAN，取值有{TRUE, FALSE, UNKNOWN}
  • 整数：INT或INTEGER
  • 浮点数：FLOAT或REAL单精度，DOUBLE双精度。FLOAT(n,d)表示小数后d位且有效数字共n位，DOUBLE(n,d类似
  • 日期和时间：DATE和TIME
• 创建和修改关系模式：add, drop, create
• 默认值: default ...
• 键声明：primary key、unique

代数查询语言(关系代数)☆

• 交并差 $\cap\cup -$
• 投影 $\pi$
• 选择 $\sigma$
• 笛卡尔积 $\times$
• 自然连接 $\Join$
• $\theta~$连接 ${\Join}_c$
• 重命名 $\rho$

关系上的约束☆

• 键约束 $\emptyset\subseteq$
• 引用完整性 $\subseteq\emptyset$

关系代数符号表(含扩展)

• 标注*为扩展操作符

习题

• 2.3.1
• a)

create table Product (
    maker   varchar(64),
    model   varchar(32),
    type    varchar(16),
    primary key (model)
);

• b)

create table PC (
    model   varchar(16),
    speed   float(6,2),
    ram     int,
    hd      int,
    price   float(8,2),
    primary key (model)
);

• c)

create table Laptop (
    model   varchar(16),
    speed   float(6,2),
    ram     int,
    hd      int,
    screen  float(3,1),
    price   float(8,2),
    primary key(model)
);

• d)

create table Printer (
    model   varchar(16),
    color   boolean default false,
    type    varchar(16),
    price   float(8,2),
    primary key (model)
);

• e)

alter table Printer drop color;

• f)

alter table Laptop add od varchar(6) default 'none';

• 2.3.2
• e)

alter table Classes drop bore;

• f)

alter table Ships add yard varchar(32);

• 2.4.1

• 2.4.3

• 2.5.1

• a) $${\sigma}_{{speed}<2.0\,and\,price>500}(PC)=\emptyset$$
• b) $${\sigma}_{screen>15.4\,and\,(hd<100\,or\,price>1000)}(Laptop) = \emptyset$$
• c) $${\pi}_{maker}({\sigma}_{type=PC}(Product)) \Join {\pi}_{maker}({\sigma}_{type=Laptop}(Product)) = \emptyset$$
• d) $${\pi}_{model}(PC)\subseteq\\{\pi}_{model}\left({\rho}_{s_1}(Product\Join PC){\Join}_{{s_1}.maker={s_2}.maker\,and\,{s_1}.speed<{s_2}.speed}{\rho}_{s_2}(Product\Join Laptop)\right)$$
• e) $${\sigma}_{l.ram>p.ram\,and\,l.price\le p.price}({\rho}_{l}(Laptop\Join {\rho}_{p}(PC)) = \emptyset$$
• 2.5.2
• a) $${\pi}_{class}\left({\rho}_{bore>16}(Classes)\right)=\emptyset$$
• b) $${\rho}_{numGune>9\,and\,bore>14}(Classes)=\emptyset$$
• c) $$R_1={\rho}_{{R_1}(classes1, name1, classes2, name2)}\left({\rho}_{s_1}({\pi}_{classes,name}(Ships))\\{\Join}_{{s_1}.classes={s_2}.class\,and\,{s_1}.name<>{s_2}.name}{\rho}_{s_2}({\pi}_{classes,name}(Ships))\right)$$ $${R_1}{\Join}_{classes1={s_3}.classes\,and\,name1<>{s_3}.name\,and\,name2<>{s_3}.name}({\rho}_{s_3}(Ships))=\emptyset$$
• d) $${\Large\rho}_{c_1}(Classes){\Join}_{{c_1}.country={c_2}.country\,and\,{c_1}.type=battleship\,and\,{c_2}.type=battlecruiser}{\Large\rho}_{c_2}(Classes)=\ \large\emptyset$$
• e) $$R={Classes\Join Ships {\Join}_{name=ship} Outcomes}$$ $${\Large{}\rho}_{r_1}(R){\Join}_{{r_1}.battle={r_2}.battle\,and\,{r_1}.numGuns<9\,and\,{r_1}.result=sunk\,and\,{r_2}.numGuns>9}{\Large{}\rho}_{r_2}(R) =\ \large\emptyset$$

2. 关系数据库设计理论

函数依赖☆

• 函数依赖：两个元组在一些属性上一致，在另一些上也一致。
• 键、超键：这些属性决定其他所有属性。最小为键，包含键为超键。

函数依赖的规则

• 依赖推导：可通过等价、推断得到。
• 分解/结合: $$title\to year\ length \Longrightarrow \\title\to year\textrm{和}\\title\to length$$
• 平凡函数依赖: $~title\ year\to title~$真平凡……
• 闭包计算☆
  
  1. 分解FD，使得每个FD右边只有一个属性
  2. 初始化集合为该属性集合
  3. 反复寻找这样的FD：该FD使得其右边的属性不在当前求得的集合中，然后把该属性加入集合
  4. 重复第(3)步知道无法添加任何属性，该集合即为所求的属性集合的闭包
• 传递: $$A_1A_2\dots A_n\to B_1B_2\dots B_m$$ 和 $$B_1B_2\dots B_m\to C_1C_2\dots C_k$$ 可以得到 $$A_1A_2\dots A_n\to C_1C_2\dots C_k$$
• 最小化基本集(minimal basis)：任何和FD集合S等价的FD集都是S的基本集。
  
  • 一个最小化基本集B满足：
    
    • B中所有FD的右边均为单一属性
    • 从B中删除任何一个FD，B都不再是基本集
    • 对B中任何一个FD，如果从其左边删除任何一个或多个属性，B都不再是基本集
  • 最小化方法：如果某个FD能从其它FD推出，则删除；如果某个FD左边的属性可以缩减，则缩减。重复上述两步直至无法继续删除。
• 投影
  
  • 设$R_1={\pi}_{L}(R)$，则满足：从S推断而来且只包含$R_1$的属性的那些FD便是投影到$R_1$后的FD。
  • 求法：对每一个$R_1$的子集计算闭包$X^{+}$，然后添加$X\to A(A\in {X^{+}})$。最后最小化该基本集。

BCNF☆

• BCNF: 每个非平凡FD左边都是超键。
• 分解方法：对每个违例$~X\to Y$，把原关系$~R~$分为$~R_1=X^{+}~$和$~R_2=\{X\}\cup\{不在X^{+}中的属性\}$，然后使用FD的投影算法得到两个新关系的FD集合。继续递归分解直到不存在违例。

chase检验

• 用来检验分解是否有损。若无损，则自然连接分解后的关系可以得到的关系和原关系将保持一致。
• 步骤：构造图例。如果分解出的关系有$\{a,c,d\}$则令一行为$ab_1cd$，然后搞啊搞看看最后能否有一行全不含下标。
• 无损连接和依赖保持有时无法同时保证。举例$\huge{}?$

3NF

• 比BCNF稍宽松，即其每个非平凡FD或者左边是超键，或者右边仅由主属性(主属性：键的成员)构成。可以同时保证无损连接和依赖保持。
• 算法
  
  • 找一个最小基本集G
  • 对G中每一个FD$~X\to A$，将XA分解出来，如果XA不包含原关系中的超键，则加一个原关系的键。
• 各种范式
  
  • 第一范式：元组分量不可分
  • 第二范式：有主键，其它属性依赖主键
  • 第四范式

多值依赖MVD%

• 多值依赖
  

• 第四范式：在MVD上应用BCNF条件

3. 高级数据库模型

ER模型☆

• 实体集: 实体--某种抽象对象，实体集--相似实体的集合
• 属性: 实体集中实体所具有的性质
• 联系: 两个或多个实体集的连接
• ER图
  
  • 矩形表示实体集
  • 椭圆表示属性
  • 菱形表示联系
  • 边连接(实体集-属性)和(联系-实体集)，边上可注明“角色”
  • 多对一，则用箭头连接。注意一对一是多对一的特例。
• 联系的属性
• 多路联系到二元联系的转换：把多路联系用矩形表示(称为连接实体集)，然后增加中间件(中间联系)
• ER模型中的子类：从实体集中引出一个$isa$联系，用三角形表示，连接实体集和它的子类

设计原则

• 忠实性：忠于实际应用的要求
• 避免冗余：去掉不必要的冗余信息
• 简单性：不要添加多余的成分
• 选择正确的联系：精简联系
• 选择正确的元素种类：总不至于真的把电影公司的地址写进电影的关系中咯？

ER模型中的约束

• 键：习惯上选一个作为主键，如果包含isa，则键应至少包含在根实体集中
  
  • 在ER图中的属性添加下划线来表示键
• 引用完整性
  
  • 不仅要去多对一，被引用对象必须存在
  • 在ER图上，把箭头改成圆箭头来表示
• 度约束：在边上添加如$~``\le 10"$来表示数量限制

弱实体集

• 举例说明，如一个电影公司可能有几套拍摄班子，另一个电影公司也类似，命名都分别是"crew1"、"crew2"等。于是引入弱实体集，用"isa"联系和其从属的电影公司联系起来
• 弱实体集的键：零个或多个自己的属性，加上到其它实体集的多对一联系连接的键属性。这些联系称为支持联系，到达的实体集称为支持实体集

ER图到关系设计

• 联系到关系：联系连接的实体集的键属性是转换后的关系的键属性，可能需要重命名。
• 弱实体集到关系：自身所有属性，相连的支持联系的所有属性，以及支持实体集的所有键属性，可能需要重命名。

子类结构到关系的转化

• 直接ER法
• 每个实体集建立一个关系。若该实体集不是层次(isa)中的根，则给它加上根的键属性。
• 面向对象方法
• 枚举isa层次中所有可能子树，每个子树一个关系。如
  
  • Movies
  • Movies and Cartoons
  • Movies and Murder-Mysteries
  • Movies and Cartoons and Murder-Mysteries
• 空值法
• 只建一个关系，包含层次中所有实体集的所有属性，没有对应属性的实体相应地将其元组在该属性上的分量设置为空(NULL)

ER图元素及其含义

4. 代数和逻辑查询语言

关系代数的扩展操作☆

• 消除重复 $\delta$
• 聚集操作 SUM AVG MIN/MAX COUNT
• 分组 $~{\gamma}_{L}(R)~$按$~L~$分组
• 扩展的投影操作 ${\pi}_{L}(R)$，L可为：
  
  • R的一个属性
  • $x\to y~$的表达式，用于投影并重命名
  • $E\to z~$的表达式，如$~a+b\to c~$表示将原a列加上b列投影至结果命名为c。又如$~c||d\to e$
• 排序 ${\tau}_{C,B}(R)$先按C排再按B排
• 外连接 $~R\,\mathring{\mathcal \Join}\,S~$先做$~R\Join S~$然后把无人配对的元组加入并置其它属性为空(NULL)

5. SQL语句

简单查询☆

• 投影: select title, length from ...
• 选择: select .... from ... where <condition>
• 字符串比较: 字典序比较
• 模式匹配(LIKE): ... where title LIKE 'Star'。下划线_表示一个字符，%表示若干个字符，''两个连续单引号表示单引号(相当于转义了)。使用ESCAPE 'x'将x作为一个转义字符来转义如%这样的特殊字符。
• 日期和时间
• 空值(NULL)
  
  • 空值运算NULL + 3、NULL - NULL结果均为NULL
  • 空值参与比较NULL < 3、NULL = NULL结果均为UNKNOWN
• 布尔值$~unknown$：“一颗老鼠屎坏了一锅粥”
• 输出排序: ... ORDER BY ... <ASC|DESC>。默认升序即ASC

多关系查询

• 积和连接
• 消除属性歧义: 使用MovieStar.address == MovieExec.address。
• 元组变量: select A.name, B.name from Movie A, Movie B ...
• 查询的交并差: UNION、INTERSECT、EXCEPT。如(select name from MovieStar) EXCEPT (select name from MovieExec);
  
  • 注：MySQL不资瓷EXCEPT，可以使用NOT IN或者LEFT JOIN来得到等价的语句。其中LEFT JOIN有一些trick: (select s.no from s) except (select t.no from t where t.name = 'Bob');$\Longleftrightarrow$select s.no from s left join t on s.no = t.no where ifnull(t.name, '') != 'Bob';，其中的ifnull(exp1, exp2)在exp1非空时返回exp1否则返回exp2。

子查询

• 产生标量值的子查询
• 关系的条件表达式
  
  • EXISTS R，当且仅当R非空时为真
  • s IN R
  • s > ALL R
  • s > ANY R
  • NOT EXISTS……
• 元组的条件表达式
• 关联子查询 P162查询语句?
• FROM子句中的查询: select name from (select ... ) pod where ...;
• 交叉连接CROSS JOIN
• 自然连接NATURAL JOIN
• 外连接OUTER JOIN
• SQL的连接表达式
  
  • 交叉连接：即笛卡尔积，最简单暴力的逐个元组组合。R CROSS JOIN S
  • 内连接：在笛卡尔积的基础上，加上了限制条件，一般都是R.name <> S.name或者R.name = S.name这样的简单形式。R INNER JOIN S ... ON R.属性1 = S.属性2或R JOIN。
  • 外连接：在内连接的基础上，把那些没有和别人配对的单身狗也选出来，但是其配偶栏为空(NULL)。
    
    • 左(外)连接，只保留左边关系的单身狗。LEFT JOIN或LEFT OUTER JOIN
    • 右(外)连接，只保留右边关系的单身狗。RIGHT JOIN或RIGHT OUTER JOIN
    • 全外连接，两边的单身狗都保留。FULL JOIN或FULL OUTER JOIN。P.S. MySQL不资瓷全外连接
  • 自然连接：其实对同名字段做相等条件判断的内连接。设R和S的所有同名字段为A1,A2,...,An，除此之外R还有B1,B2,...,Bm，S还有C1,C2,...,Ck,那么自然连接select * from R NATURAL JOIN S就等价于内连接select R.A1,R.A2,...,R.An, B1,B2,...,Bm,C1,C2,...,Ck from R inner join S on R.A1=S.A1 and R.A2=S.A2 and ... and R.An=S.An。自然连接不能像内连接那样使用ON R.id = S.emp_no来指定连接条件，它仅对同名字段做相等条件判断。

全关系操作

• 消除重复: select distinct ...
• 并交差中的重复: 默认消除重复，用<UNION|INTERSECT|EXCEPT> ALL来避免消除重复。
• 分组和聚集
  
  • 聚集操作符: SUM、AVG、MIN、COUNT
  • 分组: GROUP BY ... HAVING...
• 分组、聚集和空值
  
  • 计数(COUNT)时对空值(NULL)不计数
  • 其它聚集涉及空值(NULL)运算时，若该分组内只包含NULL值，则结果为NULL，否则自动忽略空值的存在$\to remember\ your\ test\textrm{^_^}$。
• HAVING子句
  
  • 只作用到正在检测的分组上--当然，GROUP BY的属性不被HAVING中的聚集操作作用。

数据库更新

• 插入insert into ... (...) values (...);
• 删除DELETE FROM ... (where ...);
• 修改UPDATE ... set ... where ...;

SQL中的事务

• 可串行化
• 原子性
• 事务
• 只读事务
• 读脏数据
• 其它隔离层次
• 各隔离层次(级别)
  
  • 脏读：会读到其它事务没提交的数据
  • 不可重复读：重复读可能得到不一样的结果
  • 幻读：对所有数据的修改可能因为其它事务的如插入动作导致该修改“似乎没有对所有数据都起作用”

• 补充：MySQL中的事务、隔离和检查点
  
  • InnoDB默认为Repeatable read，Oracle等默认为Committed read
  • set transaction read only;
  • set transaction read write;
  • set transaction isolation read uncommitted
  • read committed、repeatable read、serializable>
  • 创建检查点：savepoint p1
  • 回滚至检查点：rollback to p1

习题

• 手撕一遍第一次上机的所有语句

6. 约束和触发器

键和外键

• 外键约束: constraint fk_name foreign key (...) references table_name (...);
• 维护引用完整性
  
  • 缺省原则：拒绝违法更新
  • 级联原则：CASCADE
  • 置空值原则：SET NULL

create table education (
    from_date date not null,
    to_date date not null,
    id int not null
        references student (id)
        on update cascade
        on delete set null,
    primary key (id, from_date)
);

• 延迟约束检查: references ... deferrable initially deferred。事务结束后才检查约束。

属性和元组上的约束☆

• 非空值约束: not null
• 基于属性的CHECK约束: check
• 基于元组的CHECK约束: check ...

修改约束

• 给约束命名: constraint ...
• 修改约束: alter table ... drop constraint ...，alter table ... add constraint ...。

断言☆

• 创建断言: create assertion <ast_name> check <condition>;
  
  • 作用域是整个模式(schema)
• 使用断言

create assertion richPres check
    (not exists
        (select Studio.nam
        from Studio, MovieExec
        where pres = cert and netWorth < 1000000
        )
    );

触发器

• 触发器
• 触发器设计选项

create trigger <name>
<after|before> <update|delete|insert|update of <column name>>
referenecing
    old row as oldtuple
    new row as newtuple
for each row
...

习题

• 7.1.1
• a)

alter table Movies add constraint fkMoviesMovieExec
foreign key (producer) references MovieExec (name);

• b)

alter table Movies add constraint fkMoviesMovieExec
foreign key (producer) references MovieExec (name)
on update set null
on delete set null;

• c)

alter table Movies add constraint fkMoviesMovieExec
foreign key (producer) references MovieExec (name)
on update cascade
on delete cascade;

• d)

alter table StarsIn add constraint fkStarsInMovies
foreign key (movieTitle) refereneces Movies (title);

• e)

alter table StarsIn add constraint fkStarsInMovieStar
foreign key (movieStar) references MovieStar(name)
on delete cascade;

• 7.2.3 abcd
• a)

alter table StarsIn add check (
    movieYear < (select year(now()) - year(birthdate) from MovieStar where name = starName)
);

或者

alter table StarsIn add check (
    starName in (select name from MovieStar where year(birthdate) > movieYear)
);

• b)

alter table Studio add constraint studioAddressUnique
unique key (address);

• c)

alter table MovieExec add check (
    name not in (select name from MovieStar)
);

• d)

alter table Studio add check (
    name in (select studioName from Movies)
);

• e)

alter table Movies add check (
    producer not in (select name from MovieExec)
    or
    (select cert from MovieExec where name = producer)
        = studioName
);

• 7.5.1
• 删除

create trigger AvgNetWorthDeleteTrigger
after delete on MovieExec
referencing
    old tuple as OldTuple
for each row
begin
    if (600000 > (select avg(netWorth) from MovieExec)) then
        insert into MovieExec
        (name, address, cert, netWorth)
        values
        (OldTuple.name, OldTuple.address, OldTuple.cert, OldTuple.netWorth);
    end if;
end

• 插入

create trigger AvgNetWorthInsertTrigger
after insert on MovieExec
referencing
    new tuple as NewTuple
for each row
begin
    if (600000 > (select avg(netWorth) from MovieExec)) then
        delete from MovieExec where name = NewTuple.name;
        --此处应该用主键来定位
    end if;
end

7. 视图与索引

虚拟视图

• 视图定义: create view <name> as <...>;
• 视图查询: select ... from <view_name> ...
• 属性重命名: create view <name>(column names...) as ...，其它方案：create view <name> as select <column> as <new_name> ...

视图更新☆

• 视图删除: drop view <view name>;
• 可更新视图: 选出的必须属性足够多，使得至少如下要求满足
  
  • WHERE子句在子查询中不能使用该关系
  • FROM语句只能包含一个关系，不能再有其他关系
  • SELECT语句必须选出所有没有默认值的属性，使得插入时没有选出的属性可以被赋予默认值或者空值(NULL)
  • 对可更新视图，删除时可以转换成在原关系上的等价语句，但需要添加上建立视图时的条件；添加时未选出的属性均被赋予默认值。注意，向可更新视图中插入数据时可能插入某些之后再视图中查询不到的元组。
• 视图中的替换触发器: instead of trigger

索引☆

• 建立索引的动机：提高查找效率。
• 索引的声明: create index <index name> on <table name>(column name(s));
  
  • 额外说明：像MySQL，在查找时使用索引是基于所谓“最长匹配前缀”的原则，因此根据关系的索引结构来设计查询语句中的查询顺序是灰常重要的。

索引的选择☆

• 简单代价模型
  
  • 一些有用的索引: 主键，以及那些在磁盘上存放比较集中的属性。
• 计算最佳索引: 一般地，注意访问索引需要一次磁盘访问，插入时如果有索引需要更新索引(多一次磁盘访问)。

物化视图

• 物化视图的维护 create materialized view <view name> as ...
• 物化视图的定期维护
• 利用物化视图重写查询：物化视图中没有的属性在条件中剔除，包含的属性注意名称是否一致，对应即可。
• 物化视图的自动创建= =

习题

• 8.2.2
• a) 原关系模式为Movies(title, year, length, genre, studioName, producer)，若原关系中studioName、genre和producer均有默认值或者可以为空则该视图满足可更新视图的定义，因此是可更新视图。
• b)

create trigger insteadInsertDisneyComediesTrigger
instead of insert on DisneyComedies
referencing
    new tuple as NewTuple
for each row
begin
    insert into Movies
    (title, year, length, genre, studioName)
    values
    (NewTuple.title, NewTuple.year, NewTuple.year, 'comedy', 'Disney');
end

• c)

create trigger insteadUpdateDisneyComediesTrigger
instead of update on DisneyComedies
referencing
    old tuple as OldTuple
    new tuple as NewTuple
for each row
begin
    update Movies set length = NewTuple.length
    where title = OldTuple.title and year = Oldtuple.year;
end

• 8.4.2
• 东子要吐了(づ｡◕‿‿◕｡)づ

8. 服务器环境下的SQL

三层体系结构

• 参考《第9章 服务器环境下的SQL》
  
  • Web服务器层
  • 应用层
  • 数据库层

存储过程(PSM)☆

• 创建 procedure和function
  
  • 创建过程：create procedure <name> (参数) <局部声明> <过程体>；
  • 创建函数：create function <name> (<参数>) returns <返回类型> <局部声明> <函数体>
  • 调用过程call <过程名> (<参数>);，在宿主语言中则是EXEC SQL CALL Foo(:x, 3);
  • 调用函数如select test(3);
  • 局部变量声明: declare <name> <type> (default <value>);
  • 赋值: set <val> = <expression>;
  • 语句组: begin ... end
  • 语句标号(label): label1: ...

• 条件分支

  • 条件分支: if ... then ... (elseif ... then ...) end if;

• 循环

  • loop循环: LOOP <body> END LOOP;，退出循环LEAVE <循环标识>;，使用"EOF": DECLARE eof CONDITION FOR SQLSTATE '02000';，声明游标: DECLARE myCursor CURSOR FOR <select length from ... where ...>;然后fetch from myCursor into ...

delimiter //
create procedure test (
    in s char(15),
    out mean real,
    out variance real
)
declare eof condition for sqlstate '02000';
declare MovieCursor cursor for
select length from Movies where studioName = s;
declare newLength interger;
declare movieCount integer;
begin
    set mean = 0.0;
    set variance = 0.0;
    set movieCount = 0;
    open MovieCursor;
    movieLoop: LOOP
        fetch from MovieCursor into newLength;
        if eof then leave movieLoop
        end if;
        set movieCount = movieCount + 1;
        set mean = mean + newLength;
        set variance = variance + newLength * newLength;
    end loop;
    set mean = mean / movieCount;
    set variance = variance / movieCount - mean * mean;
    close MovieCursor;
end//
delimiter ;

• for循环: 处理游标，但避免了一些如游标声明打开关闭等的麻烦

delimiter //
create procedure test (
    in s char(15),
    out mean real,
    out variance real
)
declare movieCount integer;
begin
    set mean = 0.0;
    set variance = 0.0;
    set movieCount = 0;
    for movieLoop as MovieCursor cursor for
        select length from movies where studioName = s;
    do
        set movieCount = movieCount + 1;
        set mean = mean + length;
        set variance = variance + length * length;
    end for;
    set mean = mean / movieCount;
    set variance = variance / movieCount - mean * mean;
end//
delimiter ;

JDBC

习题

• 9.4.1 abef
• a)

create procedure solveA (
    in studioName char(32),
    out netWorth real
)
begin
    select sum(netWorth) into netWorth
    from MovieExec
    where cert = studioName;
end

• b)

create function solveB (
    name char(32),
    address char(64)
) returns int
declare isMovieStar boolean default false;
declare isMovieExec boolean default false;
begin
    if (name, address) in (select name, address from MovieStar) then
        set isMovieStar = true;
    end if;
    if (name, address) in (select name, address from MoveExec) then
        set isMovieExec = true;
    end if;
    if isMovieStar and not isMovieExec then
        return 1;
    elseif isMovieExec and not isMovieStar then
        return 2;
    elseif isMovieExec and isMovieStar then
        return 3;
    else
        return 4;
    end if;
end

• c)

create procedure solveC (
    in name char(32),
    out longest varchar(64),
    out longest2 varchar(64)
)
begin
    declare resCursor cursor for
        select title from Movies
        where studioName = name
        order by length desc;
    declare eof condition for sqlstate '02000';
    open resCursor;
    if eof then
        set longest = NULL;
        set longest2 = NULL;
    else
        fetch from resCursor into longest;
        if eof then
            set longest2 = NULL;
        else
            fetch from resCursor into longest2;
        end if;
    end if;
    close resCursor;
end

• d)

create function SolveD (
    sname char(32)
) returns int
declare res int default 0;
begin
    select min(year) into res from
    Movies inner join StarsIn on title = movieTitle
        inner join MovieStar on starName = name
    where starName = sname and length > 120;
    if isnull(res) then
        set res = 0;
    end if;
    return res;
end

• e)

create procedure solveE (
    sname char(32)
)
begin
    delete from MovieStar where name = sname;
    delete from Movies
    where title in (
        select movieTitle from StarsIn
        where starName = sname
    );
    delete from StarsIn where starName = sname;
end

9. 关系数据库的新课题

SQL中的安全机制和用户认证☆

• 授权图
  
  • $P^{*}$、$P^{**}$
• 补充：MySQL中的授权$~\%~$
  
  • create user dong identified by 'qyetfu';
  • grant all privileges on *.* to dong@localhost;
  • revoke delete on *.* from dong@localhost;
  • drop user dong@localhost
  • rename user dong@localhost to dong;

习题

• 10.1.2 画授权图
  
  • 第4步：
  • 第5步： 注意这一步并不会循环删除$BP^{*}$本身。
  • 第6步：

10. 系统故障对策

可恢复操作的问题和模型

• 故障模式
  
  • 错误数据输入：如“号码少输一位”。
  • 介质故障：磁盘的局部故障。可以用如RAID避免。
  • 灾难性故障：备份。
  • 系统故障：掉电、软件错误等。
• 事务及其原语
  
  • 缓冲区管理器、恢复管理器、日志管理器、事务管理器、查询处理器
  • INPUT(X)：把包含元素X的磁盘块拷贝到主存缓冲区
  • READ(X, t)：把元素X拷贝到变量t中，如果X不在主存缓冲区，则先INPUT(X)
  • WRITE(X, t)：把局部变量t的值拷贝到主存缓冲区中的数据库元素X中，如果X不在主存缓冲区，则先INPUT(X)
  • OUTPUT(X)：将包含X的缓冲区中的块拷贝回磁盘

undo日志

• 几种undo日志形式
  
  • <START T>：事务T已开始。
  • <COMMIT T>：事务T已成功完成并且对数据库元素不再会有修改。
  • <ABORT T>：事务T不能成功完成。
  • <T, X, v>：更新日志，表示事务T改变了元素X，且X原来的值为v。
• undo日志规则
  
  • 更新日志必须在元素值写入磁盘前写入磁盘
  • COMMIT日志必须在所有事务相关内容写入磁盘之后写入磁盘
    
    • 事实上，上述规则是为了把日志记录维护成一个类似栈的顺序，从而方便恢复
  • 总体顺序
    
    • 指明改变数据库元素的日志
    • 改变的数据库元素本身
    • COMMIT日志
  • FLUSH LOG 刷新日志，将日志从主存缓冲区写入磁盘
• 恢复
  
  • 从尾部开始扫描日志，在扫描过程中，假设扫描出一个记录<T, X, v>，则：若事务T的COMMIT记录已经被扫描到，则什么也不做。否则，T是一个未完成的事务，将数据库元素X的值改为v，然后刷新日志。
• 检查点
  
  • 停止接收新事务
  • 等待所有当前活跃的事务提交或中止并在日志中写入了COMMIT或ABORT记录
  • 将日志刷新到磁盘
  • 写入日志记录<CKPT>，并再次刷新日志
  • 重新开始接收新事务
• 非静止检查点
  
  • 写入日志记录<START CKPT(T1, ..., TK)>并刷新日志，其中$T_1,\dots,T_k$是当前所有的活跃事务的标识符
  • 等待$T_1,\dots,T_k$中的所有事务提交或中止，但允许其他事务开始
  • 当$T_1,\dots,T_k$都已完成，写入日志记录<END CKPT>并刷新日志

redo日志

• 和undo的区别

  • redo的初衷是为了减少磁盘IO，所有对数据库的修改都暂时保存在主存中
  • undo恢复时消除未完成的事务并忽略已提交事务，redo恢复时消除未完成的事务并重复已提交事务的改变
  • undo要求COMMIT日志在被修改数据库元素之后写入磁盘，而redo要求COMMIT日志在被修改数据库元素之前写入磁盘
  • undo恢复时，需要被修改元素的旧值，redo需要的是新值

• redo日志规则

  • 日志更新记录<T, X, v>表示事务T为元素X写入新值v
  • 日志写入磁盘的顺序
    
    • 被修改元素的日志记录
    • COMMIT日志记录
    • 改变的数据库元素本身
    • 上述顺序保证了，只要磁盘中没有发现COMMIT日志，数据库元素本身的修改就一定还没有写入磁盘，恢复时就可以当做“它们没有被修改过”

• redo日志恢复

  • 确定已提交的事务
  • 从首部开始扫描日志，若遇到记录<T, X, v>：
    
    • 如果T是未提交事务，则忽略
    • 如果T是已提交事务，则为数据库元素X写入值v
  • 对没有未完成事务T，在日志中写入<ABORT T>并刷新日志
• redo检查点

undo/redo日志

• 更新日志为<T, X, v, w>：事务T改变元素X的值，从v变为w
• 规则：更新日志必须出现在修改磁盘元素前
• 恢复：从前往后扫，重做所有已提交事务；从后往前扫，撤销所有未提交事务。

针对介质故障的保护

• 备份
  
  • 完全转储：拷贝整个数据库
  • 增量转储：完全转储是0级，i级增量转储拷贝最后一次≤i级的转储之后改变过的所有内容
  • 在大型数据库中，日志常可能比数据库本身大得多，数据备份可以缩减日志，减少存储开销
• 非静止转储
  
  • 备份时允许修改数据库，最后使用备份过程中产生的日志更新一遍备份出的数据即可。undo日志不适合与备份一同使用(undo更大的意义在于“恢复”，对“重更新”并没有用处)。
  • 步骤：参书中文译本$~P_{202}$

习题

• 17.2.3(6.2.3)
• 日志记录如下(木有加入检查点昂(⊙o⊙))：

<START U>
<U, A, 10>
    <START T>
    <T, B, 20>
<U, C, 30>
    <T, D, 40>
    <COMMIT T>
<U, E, 50>
<COMMIT U>

• a) 第3行后故障
  
  • 从第3行往回扫
  • 发现<U, A, 10>，U是一个没COMMIT的事务，因此把A的值恢复为10
• b) 第7行后故障
  
  • 从第7行往回扫
  • 发现<T, D, 40>，但T已经COMMIT，跳过
  • 发现<U, C, 30>，U未COMMIT，把C的值恢复为30
  • 发现<T, B, 20>，但T已经COMMIT，跳过
  • 发现<U, A, 10>，U未COMMIT，把A恢复为10
• c) 第8行后故障
  
  • 从第8行往回扫
  • 发现<U, E, 50>，U未COMMIT，把E的值恢复为50
  • 发现<T, D, 40>，但T已经COMMIT，跳过
  • 发现<U, C, 30>，U未COMMIT，把C的值恢复为30
  • 发现<T, B, 20>，但T已经COMMIT，跳过
  • 发现<U, A, 10>，U未COMMIT，把A恢复为10
• d) 第9行后故障
  
  • 从第9行往回扫
  • 发现<U, E, 50>，但U已经COMMIT，跳过
  • 发现<T, D, 40>，但T已经COMMIT，跳过
  • 发现<U, C, 30>，但U已经COMMIT，跳过
  • 发现<T, B, 20>，但T已经COMMIT，跳过
  • 发现<U, A, 10>，但U已经COMMIT，跳过

11. 并发控制

可串行化

• 可串行化调度：可以找到一个等价串行调度的调度

• 冲突可串行化

  • 非冲突交换：不同事务的任何两个动作可以交换，除非它们涉及同一数据库元素且至少有一个是写。
  • 冲突等价：通过一些列相邻动作的非冲突交换能将一个调度转换为另一个
    
    • 注：事务序列$r_i(X)$表示第$i$个事务$T_i$读元素$X$

  • 冲突可串行化判断：事务$T_1$优先于$T_2$记作$T_1{<}_{s}T_2$，条件是存在$T_1$中的事务$A_1$和$T_2$中的事务$A_2$满足：

    • 在调度中$A_1$在$A_2$前
    • $A_1$和$A_2$都涉及同一数据库元素
    • $A_1$和$A_2$中至少有一个是写动作

    对应优先图上，有一个$T_1$指向$T_2$的有向边。最后如果优先图中包含环，则其调度就不是冲突可串行化的。

锁实现可串行化

• 只有一种锁，无论读或者写，都要先请求锁，读写完成后释放相应的锁。注：所有对未释放锁的请求都会被阻塞。
• $l_i(X)$表示事务$T_i$请求元素$X$上的锁
• $u_i(X)$表示事务$T_i$释放它在元素$X$上的锁
• 对有锁元素，锁表指示当前持有该元素的锁的事务
• 两段锁：在每个事务中，所有封锁(lock)请求先于解锁请求(unlock)。满足两段锁条件的调度是冲突可串行化的。
• 死锁：多个事务永远地在等待另一个事务持有的锁。

其它锁

• 共享锁和排他锁：解决单种锁下多个事务无法同时读的低效问题
  
  • 共享锁(读锁)：用于读。用${sl}_i(X)$(share lock)表示
  • 排他锁(写锁)：用于读写。用${xl}_i(X)$(exclusive lock)表示
  • 对任何一个有锁的元素，其上可以有任意个共享锁，或者仅有一个排他锁

• 相容矩阵

  		申请的锁
  		共享锁S	排他锁X
  持有锁的模式	共享锁S	是	否
  	排他锁X	否	否

• 更新锁

  • 可以解决死锁问题：在元素X上已经有共享锁S后，可授予X上的更新锁U(update)，但是如果X上已经有更新锁，就禁止任何其他锁(包括共享锁、更新锁和排他锁)，这样可以保证当前想要更新的持有该更新锁的事务在后面的更新可以正常进行，保证X不会在该事务撤销更新锁之前被其它事务获得锁，进而避免死锁。其相容性矩阵如下：

  \	共享锁S	排他锁X	更新锁U
  共享锁S	是	否	是
  排他锁X	否	否	否
  更新锁U	否	否	否

习题

• 7.2.5 c
  
  • i) 如图所示
    
  • ii) 优先图中存在环，所以不是
  • iii) 说的啥？

a
b
c
d
e
f
g
h
i
j
k