数据库系统概论复习

完成进度

时间线

完成过程

2022-06-26

优化绪论部分内容
增加绪论例题模块
增加评论区扩展功能
关系数据库理论提供百度网盘习题以及增加推理规则内容
修改部分tip
新增时间线版块和完成进度
新增习题
修改错误：关系数据库中关系操作的特点：数据操作是集合操作，操作对象和操作结果都是集合（之前写了关系）
修改错误：绪论中两级映像和数据独立性括号内逻辑独立性与物理独立性写反了，现已修正

2022-06-25

数据库恢复技术章节内容
并发控制与封锁
考试题型
新增tip
优化页面布局和字体

2022-06-24

绪论
关系数据库
SQL与T-SQL
数据库安全性
数据库完整性
关系数据理论
数据库设计概述

考试题型

单选题可以参考我们平时做的练习。SQL语句部分可以直接看SQL与T-SQL的部分，也可以看老师平时布置的实验题，我这里的也是摘自习题并进行了挑选，并附上自己的答案。T-SQL建议稍微过一下PPT，老师说不会刻意考，在触发器和存储过程中需要使用，会声明变量等基本操作即可。如果文章中存在问题，还请指出。绪论部分内容有例题，摘自百度文库，供大家参考。祝考试顺利！！！

题型与分值

单选（30）
简答（30）
SQL语句填空（20）
写SQL语句（20）

绪论

新增了E-R图的概念和表示方法，考试中可能会让你画图或者解释这些概念（个人猜测，我在百度文库中看到类似题目）

数据库定义
answer P4
1. 数据库（Database，简称DB）是长期储存在计算机内、有组织的、可共享的大量数据的集合。
2. 数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。
3. 具有永久存储、有组织和可共享三个基本特点。
数据库管理系统
answer P5
1. 数据库管理系统位于用户与操作系统之间的一层数据管理软件
2. 是基础软件，是一个大型复杂的软件系统
3. 科学地组织和存储数据、高效地获取和维护数据
4. 主要功能：数据库定义功能；数据库的操纵功能；数据库保护功能；数据库维护功能；数据字典
5. 数据库保护（控制）功能：数据安全性控制；数据完整性控制；并发控制；恢复功能
数据库的特点
answer P11
1. 数据结构化
2. 数据的共享性高，冗余度低且易扩充
3. 数据独立性高
  1. 物理独立性
  2. 逻辑独立性
4. 数据由数据库管理系统统一管理和控制
  1. 数据安全性控制
  2. 数据完整性控制
  3. 并发控制
  4. 恢复功能
概念模型
answer P17
1. 实体：客观存在并可相互区别的事物
2. 属性：实体所具有的某一特性
3. 码：唯一标识实体的属性集
4. 实体型：用实体名及其属性名集合来抽象和刻画同类实体
5. 实体集：同一类型实体的集合
6. 域：一组具有相同数据类型的值的集合
E-R模型三要素、联系的种类
answer P216
1. E-R模型三要素
  1. 实体
  2. 属性
  3. 实体间的联系
2. 联系
  1. 实体内部联系：各属性间联系
  2. 实体外部联系：不同实体集之间的联系
3. 实体间的联系
  1. 一对一1:1
  2. 一对多1:n
  3. 多对多n:m
  4. 三实体型之间
  5. 单实体型内一对多的联系
4. E-R图：提供了表示实体型、属性和联系的方法
  - 实体型：用矩形表示，矩形框内写明实体名。
  - 属性：用椭圆形表示，并用无向边将其与相应的实体连接起来。
  - 联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型。
数据模型三要素
answer P18
1. 数据结构：描述数据库的组成对象，以及对象之间的联系
2. 数据操作：对数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关的操作规则
3. 数据的完整性约束条件：一组完整性规则的集合
数据库系统三模式结构
answer P28
1. 外模式：也称子模式或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式是保证数据库安全性的一个有力措施，每个用户只能看见和访问所对应的外模式中的数据
2. 模式：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图，一个数据库只有一个模式，是数据库的中心与关键，独立于数据库的其他层次，设计数据库模式结构时应首先确定数据库的逻辑模式
3. 内模式：也称存储模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式，一个数据库只有一个内模式
例题
1. 试述数据库系统三级模式结构，这种结构的优点是什么。
  数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级构成。数据库系统的三级模式是对数据的三个抽象级别，它把数据的具体组织留给DBMS管理，使用户能逻辑地抽象地处理数据，而不必关心数据在计算机中的具体表示方式与存储方式。为了能够在内部实现这三个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映象：外模式／模式映象；模式／内模式映象。正是这两层映象保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。
两级映像和数据独立性
answer P30
1. 外模式／模式映像（逻辑独立性）
  当模式改变时，数据库管理员对外模式／模式映象作相应改变，使外模式保持不变，应用程序是依据数据的外模式编写的，应用程序不必修改。保证了数据与程序的逻辑独立性，简称数据的逻辑独立性
2. 模式／内模式映像（物理独立性）
  当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式／内模式映象，使模式保持不变，应用程序不受影响。保证了数据与程序的物理独立性，简称数据的物理独立性。
3. 数据与程序的独立性概念：
  特定的应用程序是在外模式描述的数据结构上编制的，它依赖于特定的外模式，与数据库的模式和存储结构独立。数据库的二级映像保证了数据库外模式的稳定性，从底层保证了应用程序的稳定性，除非应用需求本身发生变化，否则应用程序一般不需要修改
4. 数据与程序之间的独立性，使得数据的定义和描述可以从应用程序中分离出去。数据的存取由数据库管理系统管理，简化了应用程序的编制，大大减少了应用程序的维护和修改
数据库系统的组成
answer P31
1. 硬件平台及数据库
  1. 足够大的内存
  2. 足够的大的磁盘或磁盘阵列等设备
  3. 较高的通道能力，提高数据传送率
2. 软件
  1. 数据库管理系统
  2. 支持数据库管理系统运行的操作系统
  3. 与数据库接口的高级语言及其编译系统
  4. 以数据库管理系统为核心的应用开发工具
  5. 为特定应用环境开发的数据库应用系统
3. 人员
  1. 数据库管理人员
  2. 系统分析员和数据库设计人员
  3. 应用程序员
  4. 最终用户
例题
1. 试述数据库系统的组成
  数据库系统一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户构成
2. DBA的职责是什么
  决定数据库中的信息内容和结构、决定数据库的存储结构和存取策略、定义数据的安全性要求和完整性约束条件、监控数据库的使用和运行、数据库的改进和重组重构。

关系数据库

候选码、主属性等概念（理解掌握）
answer P40
1. 码
  1. 候选码：若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码
  2. 全码：关系模式的所有属性组是这个关系模式的候选码，称为全码
  3. 主码：若一个关系有多个候选码，则选定其中一个为主码
2. 属性
  1. 主属性：候选码的诸属性称为主属性
  2. 非主属性：不包含在任何侯选码中的属性称为非主属性
关系的六条性质（理解掌握）
answer P41
1. 同质的列
2. 不同列可出自同一个域，但要求不同的属性名
3. 属性无序
4. 元组候选码不重复
5. 元组无序
6. 分量是原子
关系操作的特点
answer P44
数据操作是集合操作，操作对象和操作结果都是集合
关系三类完整性
answer P45
1. 实体完整性
2. 参照完整性
3. 用户定义的完整性
关系代数（选择题）
answer P49
1. 预备知识
  预备知识
2. 集合运算符
  集合运算符
3. 专门关系运算符
  选择、投影
  
  连接

SQL与T-SQL

基本表操作

answer P82

USE sct;

-- 在学院表中，学院号为主键
CREATE TABLE coll
(
 collnum nchar(8) primary key,
 collname nvarchar(30) not null,
 tnum nchar(8)
);

-- 在专业表中，专业号为主键，所在学院为外键
CREATE TABLE d
(
 dnum nchar(8) primary key,
 dname nvarchar(30) not null,
 collnum nchar(8),
 FOREIGN KEY(collnum) REFERENCES coll(collnum)
);

-- 在学生表中，学号为主键，专业号为外键，性别为‘男’或’女’
CREATE TABLE s
(
 snum nchar(8) primary key,
 sname nvarchar(30) not null,
 ssex nchar(1) CHECK (ssex = '男' or ssex = '女'),
 sbirth date,
 spolity nvarchar(30),
 snation nvarchar(30),
 sorigin nvarchar(30),
 enterdata date,
 sphone nvarchar(30),
 dnum nchar(8),
 FOREIGN KEY(dnum) REFERENCES d(dnum)
);

-- 在课程表中课程号为主键，学分带1位小数
CREATE TABLE c
(
 cnum nchar(8) primary key,
 cname nvarchar(30) not null,
 credit numeric(4,1)
);

-- 在选修表中，学号+课号为主键，学号和课号分别为外键,分数在[0,100]之间
CREATE TABLE sc
(
 snum nchar(8),
 tnum nchar(8),
 score int CHECK (score between 0 and 100),
 PRIMARY KEY(snum, tnum),
 FOREIGN KEY(snum) REFERENCES s(snum),
 FOREIGN KEY(tnum) REFERENCES t(tnum)
)

-- 在已经存在的学生表中增加一个邮件地址“Semail”的新属性列，允许为空；
ALTER TABLE s
add semail nvarchar(30);

-- 修改教师表中tsalary的数据类型为money；
ALTER TABLE t
alter column tsalary money;

-- 把学生表中的电话属性删掉；
ALTER TABLE s
drop column sphone;

-- 删除学生性别是男或女的约束条件;
ALTER TABLE s
drop constraint CK__s__ssex__571DF1D5;

-- 增加学生性别是男或女的约束条件。
ALTER TABLE s
add constraint CK__s__ssex__571DF1D5 CHECK(ssex = '男' or ssex = '女');

数据查询

answer P89

简单查询

-- 查询学院编号为‘22’的教师姓名和工资情况，工资增加1000后输出。
SELECT tname, tsalary + 1000 as tsalary
FROM t
WHERE collnum = '22'
-- 查询全体学生的系编号、学号、姓名、性别和年龄，
-- 查询结果按所在系的系编号升序排列，同一系中的学生按年龄降序排列。
SELECT dnum, snum, sname, ssex, YEAR(GETDATE()) - YEAR(sbirth) as sage
FROM s
ORDER BY dnum, sage DESC;

-- 从学生表中查出名字第2个字是
-- ’睿’   或  ‘晓’  或 ‘子’ 或’涵’ 的学生资料。
SELECT *
FROM s
WHERE sname LIKE '_[睿,晓,子,涵]%';

--查询所有电话号码是空的学生信息。
SELECT *
FROM s
WHERE sphone IS NULL;

-- 列出职称是副教授或工程师或教授的教师信息。
SELECT *
FROM t
WHERE ttitle IN ('副教授', '工程师', '教授');

聚合分组

-- 统计选修了课程的学生人数。
select count(distinct snum)
from sc;

-- 按职称统计所有男教师的最高、最低和平均工资工资
-- 筛选出平均工资高于7000的记录，并按平均工资降序排列。
select ttitle,max(tsalary) ,min(tsalary),avg(tsalary)
from t
where tsex='男'
group by ttitle
having avg(tsalary)>=7000
order by avg(tsalary) desc;

-- 按平均工资从高到低的顺序分别列出男、女教师各种职称（非空）的
-- 人数（任职人数多于100）和平均工资。
select ttitle,tsex,avg(tsalary),count(tnum)
from t
where ttitle is not null and ttitle <> ''
group by ttitle, tsex
having count(tnum)>100
order by avg(tsalary) desc;

-- 查询有50门以上课程是90分以上的学生姓名、学号、课程数，
-- 查询结果按课程数从多到少的顺序排列。
select s.sname,sc.snum,count(cnum)
from sc,s
where s.snum=sc.snum and score>=90
group by s.sname,sc.snum
having count(cnum)>50
order by count(cnum) desc;

-- 按职称统计信息工程学院平均工资高于6000的各种职称的人数和平均工资，
-- 查询结果按平均工资从大到小顺序排列。
select ttitle,avg(tsalary),count(t.tnum)
from t, coll
where t.collnum = coll.collnum and collname = '信息工程学院'
group by ttitle
having avg(tsalary)>=6000
order by avg(tsalary) desc;

-- 查询‘计算机科学与技术’专业选修课程的最低成绩大于等于90分的学生的姓名。
select sname,min(score)
from d,s,sc
where d.dnum=s.dnum and s.snum=sc.snum  and dname =  '计算机科学与技术'
group by sname,s.snum
having min(score)>=90;

连接查询

-- 查询所有学生的学号、姓名、选修的课程号和成绩。
select s.snum,sname,sc.cnum,sc.score
from s,sc
where s.snum=sc.snum;

-- 查询所有学生的姓名、选修的课程名和相应的学分。
select sname,c.cname,c.credit
from s,sc,c
where s.snum=sc.snum and sc.cnum=c.cnum;

-- 查询选修了数据库原理与技术并且成绩在90分以上的学生的姓名。
select s.sname
from sc,c,s
where s.snum=sc.snum and sc.score>90 and sc.cnum=c.cnum and c.cname='数据库原理与技术';

-- 查询计算机科学与技术专业选修了数据库原理与技术
-- 并且成绩在90分以上的学生的姓名。
select s.sname
from sc,c,s,d
where s.snum=sc.snum and sc.score>90 and sc.cnum=c.cnum and c.cname='数据库原理与技术'
    and s.dnum=d.dnum and d.dname='计算机科学与技术专业';

-- 查询工资高于杨平的所有教师的姓名、职称和工资。
select t1.tname,t1.ttitle,t1.tsalary
from t t1,t t2
where t1.tsalary>t2.tsalary and t2.tname='杨平';

-- 查询与“沈羿”在同一个专业学习的学生姓名和性别。
select s1.sname,s1.ssex
from s s1, s s2
where s1.dnum=s2.dnum and s2.sname='沈羿';

嵌套查询

--查询选修了‘B3503021 ’号课程的学生的学号和姓名
SELECT snum,sname
FROM s
WHERE  snum IN (SELECT snum FROM sc  WHERE cnum='B3503021');

SELECT snum,sname
FROM s
WHERE  EXISTS (SELECT * FROM sc  WHERE sc.snum=s.snum and cnum='B3503021');

--查询没有选修‘B3503021’号课程的学生的学号和姓名
SELECT snum,sname
FROM s
WHERE  snum NOT IN (SELECT snum FROM sc  WHERE cnum='B3503021');

SELECT snum,sname
FROM s
WHERE  NOT EXISTS (SELECT * FROM sc  WHERE sc.snum=s.snum and cnum='B3503021');

-- 查询选修了‘数据库原理与技术’的学生姓名
SELECT sname
FROM s
WHERE  snum IN (SELECT snum FROM sc  WHERE cnum IN (
                SELECT cnum FROM  c WHERE cname='数据库原理与技术'));

SELECT sname
FROM s
WHERE   EXISTS (SELECT * FROM sc  WHERE sc.snum=s.snum AND EXISTS(
                SELECT *FROM c  WHERE c.cnum=sc.cnum and cname='数据库原理与技术' ));

-- 查询工资高于杨平的所有教师的姓名、职称和工资
SELECT tname ,ttitle,tsalary
FROM t t1
WHERE tsalary>(SELECT tsalary  FROM t t2 WHERE t2.tname='杨平');

SELECT tname, ttitle,tsalary
FROM t t1
WHERE EXISTS (SELECT * FROM t t2  WHERE  t1.tsalary>t2.tsalary AND t2.tname='杨平');

-- 查询比‘信息工程学院’最低工资高的其他学院教师信息
SELECT *
FROM t t1
WHERE tsalary>ANY(SELECT tsalary FROM t t2 WHERE t2.collnum IN(
                    SELECT collnum from coll WHERE collname = '信息工程学院'));

-- 找出每个学生超过他自己选修课程平均成绩的学号、课程号和成绩
SELECT snum,cnum,score
FROM sc sc1
WHERE score>(SELECT AVG(score) FROM sc sc2 WHERE sc1.snum=sc2.snum );

-- 列出工资低于教师平均工资的教师的工号、姓名、职称和工资情况。
SELECT tnum,tname ,ttitle,tsalary
FROM t t1
WHERE tsalary<(SELECT AVG(tsalary) FROM t t2);

-- 查询与“沈羿”在同一个专业学习的学生姓名和性别。
SELECT sname ,ssex
FROM s s1
WHERE EXISTS (SELECT * FROM s s2 WHERE  s2.sname='沈羿' AND s1.dnum=s2.dnum);

查询综合

USE sct;
-- 查询既选修了课程c01又选修了课程c02的学生的学号和姓名。
--（四种方法：集合、自身连接、相关和不相关子查询）
SELECT s.snum, sname
FROM s, sc
WHERE s.snum = sc.snum AND cnum = 'c01'
INTERSECT
SELECT s.snum, sname
FROM s, sc
WHERE s.snum = sc.snum AND cnum = 'c02';

SELECT s.snum, sname
FROM s, sc sc1, sc sc2
WHERE s.snum = sc1.snum AND sc1.snum = sc2.snum AND sc1.cnum = 'c01' AND sc2.cnum = 'c02';

SELECT snum, sname
FROM s
WHERE snum IN (SELECT snum FROM sc sc1 WHERE cnum ='c01' AND EXISTS (
                SELECT * FROM sc sc2 WHERE sc1.snum = sc2.snum AND sc2.cnum = 'c02'));

SELECT snum, sname
FROM s
WHERE snum IN (SELECT snum FROM sc WHERE cnum = 'c01' AND snum IN (
                SELECT snum FROM sc WHERE cnum = 'c02'));

-- 查询选修了课程c01没有选修课程c02的学生的学号和姓名。
SELECT s.snum, sname
FROM s, sc
WHERE s.snum = sc.snum AND cnum = 'c01'
EXCEPT
SELECT s.snum, sname
FROM s, sc
WHERE s.snum = sc.snum AND cnum = 'c02';

SELECT snum, sname
FROM s
WHERE snum IN (SELECT snum FROM sc WHERE cnum = 'c01' AND snum NOT IN (
                SELECT snum FROM sc WHERE cnum = 'c02'));

-- 查询学校中所有师生的姓名。
SELECT sname name
FROM s
UNION
SELECT tname
FROM t;

-- 查询学校中所有与教师同名的学生姓名。
SELECT DISTINCT sname
FROM s
WHERE sname IN (SELECT tname FROM t WHERE sname = tname);

-- 查询同名同姓的学生姓名和重名数量
-- 要求输出姓名和重名数，按数量从多到少排列。
SELECT DISTINCT s1.sname, COUNT(s1.sname) count
FROM s s1, s s2
WHERE s1.sname = s2.sname
GROUP BY s1.sname
HAVING COUNT(s1.sname) > 1
ORDER BY COUNT(s1.sname) DESC

数据更新

answer P115

-- 1.将新生（学号：202201010101；姓名：伍俊；性别：男；出生日期：2004-08-08；电话：88485561；系编号：0501）
--的记录插入学生表，并为其选：c01 、c02与c03三门课程。
INSERT
INTO s(snum, sname, ssex, sbirth, sphone, dnum)
VALUES ('202201010101', '伍俊', '男', '2004-08-08', '88485561', '0501');

INSERT
INTO sc(snum, cnum)
VALUES ('202201010101', 'c01'),  ('202201010101', 'c02'),  ('202201010101', 'c03')

-- 2.建立课程号、课程名、平均成绩与选课人数表。并将数据存入到该表中。

CREATE TABLE class
(
 cnum nchar(12),
 cname nvarchar(50),
 avg_score numeric(5,1),
 stu_num int
);

INSERT
INTO class(cnum, cname, avg_score, stu_num)
SELECT sc.cnum, c.cname, AVG(sc.score), COUNT(*)
FROM sc, c
GROUP BY sc.cnum, c.cnum, c.cname
HAVING sc.cnum = c.cnum;

-- 3.事后发现‘雷吉平’考试作弊，将其所有成绩改为0分。
UPDATE sc
SET score = 0
WHERE snum IN (SELECT snum FROM s WHERE sname = '雷吉平')

-- 4.将所有教师的工资增加1000元。
UPDATE t
SET tsalary += 1000

-- 5.将‘计算机科学与技术’专业全体学生的成绩置零。
UPDATE sc
SET score = 0
WHERE snum IN (SELECT snum FROM s WHERE dnum IN (
                SELECT dnum FROM d WHERE dname = '计算机科学与技术'));

-- 6.将杨平任命为‘信息工程学院’院长。
UPDATE coll
set tnum = (SELECT tnum FROM t WHERE tname = '杨平')
WHERE collname = '信息工程学院';

-- 7.将‘信息工程学院’院长的工资降为该学院的平均工资。
UPDATE t
set tsalary = (SELECT AVG(tsalary) FROM t WHERE collnum IN (
                SELECT collnum FROM coll WHERE collname = '信息工程学院'))
WHERE tnum = (SELECT tnum FROM coll WHERE collname = '信息工程学院');

-- 8.在学生表中增加flag标志列，如果该同学选课，则flag置1,如果未选课，则flag置0。
ALTER TABLE s
add flag tinyint;

UPDATE s
set flag = 1
WHERE snum IN (SELECT snum FROM sc)

UPDATE s
set flag = 0
WHERE snum NOT IN (SELECT snum FROM sc)

-- 9.学号为‘S030404’的学生由于退学，删除该学生记录及其选课记录。
DELETE
FROM sc
WHERE snum = 'S030404';

DELETE
FROM s
WHERE snum = 'S030404';

-- 10.删除低于平均工资的老师记录以及任课记录。
DELETE
FROM t
WHERE tsalary < (SELECT AVG(tsalary) FROM t)

DELETE
FROM tc
WHERE tnum IN (SELECT tnum FROM t WHERE tsalary < (
                SELECT AVG(tsalary) FROM t));

-- 11.删除‘计算机科学与技术’专业全体学生的选课记录。
DELETE
FROM sc
WHERE snum IN (SELECT snum FROM s WHERE dnum IN (
                SELECT dnum FROM d WHERE dname = '计算机科学与技术'));

-- 12.删除没有选课的学生信息。
DELETE
FROM s
WHERE snum NOT IN (SELECT snum FROM sc);

视图

answer P121

USE sct;
--1.创建一个视图VIEW_S，显示成绩等于100分的学生的学号、姓名、课程名和成绩。
IF object_id('VIEW_S', 'V') IS NOT NULL
    DROP VIEW  VIEW_S
GO

CREATE VIEW VIEW_S
AS
SELECT s.snum, sname, cname, score
FROM s, sc, c
WHERE s.snum = sc.snum AND sc.cnum = c.cnum AND score = 100;

--2.建立‘计算机科学与技术’专业选修了‘数据库原理与技术’这门课程的学生视图VIEW_CS。（学号，姓名，成绩）
IF object_id('VIEW_CS', 'V') IS NOT NULL
    DROP VIEW  VIEW_CS
GO

CREATE VIEW VIEW_CS
AS
SELECT s.snum, sname, score
FROM s, sc, d, c
WHERE s.snum = sc.snum AND s.dnum = d.dnum AND c.cnum = sc.cnum AND d.dname = '计算机科学与技术' AND c.cname = '数据库原理与技术'

--3.建立‘计算机科学与技术’专业选修了‘数据库原理与技术’这门课程且成绩在90分以上的学生视图VIEW_CS90。（学号，姓名，成绩）

--要求创建基于视图的视图。

IF object_id('VIEW_CS90', 'V') IS NOT NULL
    DROP VIEW  VIEW_CS90
GO

CREATE VIEW VIEW_CS90
AS
SELECT snum, sname, score
FROM VIEW_CS
WHERE score > 90

--4.建立‘计算机科学与技术’专业的学生的学号，姓名和平均成绩的视图。
IF object_id('VIEW_D', 'V') IS NOT NULL
    DROP VIEW  VIEW_D
GO

CREATE VIEW VIEW_D
AS
SELECT s.snum, sname, AVG(score) as avg_score
FROM s, sc, d
WHERE s.snum = sc.snum AND s.dnum = d.dnum AND dname = '计算机科学与技术'
GROUP BY s.snum, sname

--5.将学院名称及该学院教师的平均工资定义为一个视图。

IF object_id('VIEW_TS', 'V') IS NOT NULL
    DROP VIEW  VIEW_TS
GO

CREATE VIEW VIEW_TS
AS
SELECT collname, AVG(tsalary) as avg_salary
FROM coll, t
WHERE t.collnum = coll.collnum
GROUP BY collname;

--6.建立一视图显示教师的工号，姓名和年龄。（可考虑用datediff()函数）
IF object_id('VIEW_T', 'V') IS NOT NULL
    DROP VIEW  VIEW_T
GO

CREATE VIEW VIEW_T
AS
SELECT tnum, tname, datediff(YEAR, tbirth, GETDATE()) as tage
FROM t

--7.删除视图VIEW_CS并观察VIEW_CS90是否可用，显示删除VIEW_CS90
IF object_id('VIEW_CS', 'V') IS NOT NULL
    DROP VIEW  VIEW_CS
GO

触发器

answer PPT

-- 在选课表SC上创建触发器，当向该表中插入记录时，若学生选课门数超过5门时,
-- 则向客户端发出警告信息，同时将插入记录自动删除。
create trigger tri_insc
on sc
after insert
as
if(select count(*) from sc,inserted where sc.snum=inserted.snum)>5
begin
    rollback
    print'限选五门！'
end
else
    print'选课成功！'

-- 在sc表上创建触发器，当删除学生的选课记录时，
-- 首先检查要删除的行数，如果是删除多行，则返回错误信息。
create trigger tr_del
on sc
after delete
as
if(select count(*) from deleted)>1
begin
    rollback
    print'不能删除多行'
end
else
    print'删除成功'

-- 创建一触发器tr_inc,在向C表插入或者修改课程记录时，
-- 先检查是否与该课程同名的课程已经存在，以避免课程的混淆。
create trigger tr_inc
on c
after insert
as
if exists(select * from c,inserted where c.cname=inserted.cname)
begin
    rollback
    print'已存在该课程'
end
else
    print'修改成功'

-- 创建一触发器,在对学生的姓名进行更新时，显示更新前后的学生姓名。
create trigger tr_up
on s
after update
as
if update(sname)
begin
    select sname'更名前'from deleted
    select sname'更名后'from inserted
end

存储过程

answer PPT

-- 1.创建一个加密存储过程，从学生选课数据库中查询学号,姓名,课程名和成绩。
-- 执行该存储过程，并用sp_helptext查看该存储过程代码。
CREATE PROCEDURE proc_sc_select
WITH ENCRYPTION
AS
SELECT s.snum, sname, cname, score
FROM s, sc, c
WHERE s.snum = sc.snum AND sc.cnum = c.cnum
GO

EXEC proc_sc_select;
GO

SP_HELPTEXT proc_sc_select;
DROP PROCEDURE proc_sc_select;



--  2.创建一存储过程，完成向课程表（C）中插入一条记录
-- 记录值通过输入参数传递到存储过程中，并执行该存储过程。
CREATE PROCEDURE proc_c_insert(@cnum AS nchar(12), @cname AS nvarchar(50), @credit AS numeric(5, 1))
AS
INSERT INTO c
VALUES (@cnum, @cname, @credit);
GO

EXEC proc_c_insert '00001005', VUE3, 12.0;
GO

-- 3.创建一存储过程，在删除学生(s)表时,同时删除该学生的选课记录，
-- 输入参数为学号。并执行该存储过程。
CREATE PROCEDURE proc_delete_s_sc(@snum AS nchar(14))
AS
DELETE FROM sc
WHERE snum = @snum;

DELETE FROM s
WHERE snum = @snum;
GO

EXEC proc_delete_s_sc '10103060209'
GO

-- 4.创建一存储过程proc2,显示其指定课程的成绩在指定范围内的学生学号、姓名、课程名和成绩，
-- 并执行该存储过程。
CREATE PROCEDURE proc2(@cname AS nvarchar(50), @min AS tinyint, @max AS tinyint)
AS
SELECT s.snum, s.sname, cname, score
FROM s, sc, c
WHERE s.snum = sc.snum AND sc.cnum = c.cnum AND score >= @min AND score <= @max AND c.cname = @cname;
GO

EXEC proc2 '中药学', 40, 50
GO


-- 5. 创建一存储过程，显示某一同学的平均成绩，
-- 学号通过输入参数传递到存储过程中，并执行该存储过程。
CREATE PROCEDURE proc_avg(@snum AS nchar(14))
AS
SELECT s.snum, sname, AVG(score) AS 'avg_score'
FROM s, sc
WHERE s.snum = sc.snum AND s.snum = @snum
GROUP BY sname, s.snum;
GO

EXEC proc_avg '10103060229'
GO

数据库安全性

数据库安全性控制
answer P137
数据库安全性控制的常用方法：用户标识和鉴定；存取控制；视图；审计；数据加密

权限授予和收回

answer P141

-- 把查询S表权限授给用户U1
GRANT SELECT
ON S
TO U1;

-- 把C表的增、删、改权限授予用户U2和U3
GRANT INSERT,DELETE,UPDATE
ON C
TO U2,U3;

-- 把对表SC的查询权限授予所有用户
GRANT SELECT
ON SC
TO PUBLIC;

-- 把查询Student表和修改学生学号的权限授给用户U4
GRANT UPDATE(Snum), SELECT
ON S
TO U4;

-- 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户
GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION;

-- 收回所有用户对表SC的查询权限
REVOKE SELECT
ON SC
FROM PUBLIC;

-- 把用户U5对SC表的INSERT权限收回
-- 将用户U5的INSERT权限收回的时候应该使用CASCADE，否则拒绝执行该语句
-- 如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，
-- 系统只收回直接或间接从U5处获得的权限
REVOKE INSERT
ON SC
FROM U5 CASCADE ;

数据库完整性

数据库完整性定义
answer P157
数据库完整性是指数据的正确性和相容性(P13 还有一个有效性)

关系数据理论

建议直接看书本与PPT中的函数依赖、码、1NF、2NF、3NF、BCNF、P189图、Armstrong公理系统、推理规则、算法。这章内容会出现在简答题，要求会计算和判断。同时这里提供了对应的习题供大家练习，只需要看考试内容，做题步骤还建议参考老师的PPT百度网盘的下载地址6hei

候选码
属性集闭包
范式判断

推理规则

自反律(reflexivity)：若Y ⊆ X，则X → Y
增广律(augmentation)：若X → Y ，则XZ → YZ
传递律(transitivity)：若X → Y，Y → Z，则X → Z
合并律(union rule)：若X → Y，X → Z，则X → YZ
分解律(decomposition rule)：若X → YZ ，则X → Y，X → Z
伪传递律(pseudotransitivity rule)：若X → Y，WY → Z，则WX → Z

数据库设计概述

数据库设计六大阶段
answer P209
1. 需求分析阶段
2. 概念结构设计阶段
3. 逻辑结构设计阶段
4. 物理结构设计阶段
5. 数据库实施阶段
6. 数据库运行和维护阶段

数据库恢复技术

注意理解把握P303图10.4中每个阶段系统所做的事情，看懂原理非常重要，考试中很可能出现类似的图然后进行分析

事务定义
answer P293
1
2
3
BEGIN TRANSACTION;
COMMIT;
ROLLBACK;
事务四大特性
answer P293
ACID原则：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability）

故障的种类

answer P295

事物内部故障

--例如，银行转账事务，这个事务把一笔金额从一个账户甲转给另一个账户乙。
BEGIN TRANSACTION
读账户甲的余额BALANCE;
BALANCE=BALANCE-AMOUNT; /*AMOUNT 为转账金额*/
IF(BALANCE < 0 ) THEN
{
    打印‘金额不足，不能转账’；/*事务内部可能造成事务被回滚的情况*/
    ROLLBACK; /*撤销刚才的修改，恢复事务*/
}
ELSE
{
    读账户乙的余额BALANCE1;
    BALANCE1=BALANCE1+AMOUNT;
    写回BALANCE1;
    COMMIT;
}

事务内部更多的故障是非预期的，是不能由应用程序处理的。
事物内部故障的常见原因：
- 运算溢出
- 并发事务发生死锁而被选中撤销该事务
- 违反了某些完整性限制而被终止
恢复操作：事务撤销（UNDO）

系统故障
1. 称为软故障，是指造成系统停止运转的任何事件，使得系统要重新启动。整个系统的正常运行突然被破坏，所有正在运行的事务都非正常终止，不破坏数据库，内存中数据库缓冲区的信息全部丢失。发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，造成数据库可能处于不正确状态。
2. 系统故障的常见原因：
  - 特定类型的硬件错误（如CPU故障）
  - 操作系统故障
  - 数据库管理系统代码错误
  - 系统断电
3. 恢复操作：系统重新启动时，恢复程序让所有非正常终止的事务回滚，强行撤消（UNDO）所有未完成事务，重作（REDO）所有已提交的事务
介质故障
1. 称为硬故障，指外存故障。介质故障破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务介质故障比前两类故障的可能性小得多，但破坏性大得多
2. 介质故障的常见原因：
  - 磁盘损坏
  - 磁头碰撞
  - 瞬时强磁场干扰
计算机病毒（知道即可，不属于典型故障）

建立冗余数据常用技术
answer P297
1. 恢复操作的基本原理：冗余
2. 建立冗余数据最常用的技术是数据转储和登记日志文件
3. 数据转储的四种方法：
  - 动态海量转储
  - 动态增量转储
  - 静态海量转储
  - 静态增量转储
登记日志文件
answer P298
1. 日志文件定义：是用来记录事务对数据库的更新操作的文件
恢复的策略
answer P300
1. 事务故障的恢复：DBMS自动完成，不需要用户操作，UNDO操作。
  - 反向扫描文件日志，查找该事物的更新操作
  - 对该事务的更新操作执行逆操作
2. 系统故障的恢复：DBMS自动完成，不需要用户操作，Undo故障发生时未完成的事务，Redo已完成的事务
  - 正向扫描日志文件
  - 对撤销(UNDO)队列事务进行撤销(UNDO)处理
  - 对重做(REDO)队列事务进行重做(REDO)处理
3. 介质故障的恢复：介质故障的恢复需要DBA介入
  - 装入最新的后备数据库副本,使数据库恢复到最近一次转储时的一致性状态。注意：对于动态转储的数据库副本，还须同时装入转储时刻的日志文件副本，利用恢复系统故障的方法（即REDO+UNDO），才能将数据库恢复到一致性状态
  - 装入有关的日志文件副本，重做已完成的事务。
具有检查点的恢复技术
answer P303
1. 问题指出
  - 搜索整个日志将耗费大量的时间
  - 重做处理：重新执行，浪费了大量时间
2. 检查点技术
  1. 暂时终止现有事务的执行
  2. 把主存中被修改的数据缓冲区强制写入磁盘
  3. 在日志中写入检查点记录，并把日志强制写入磁盘
  4. 重新开始执行事务
3. 检查点恢复步骤
  1. 从重新开始文件中找到最后一个检查点记录
  2. 由该检查点得到检查点建立时刻正在执行的事务清单ACTIVE-LIST
    建立事务队列：UNDO-LIST和REDO-LIST
    将ACTIVE-LIST暂时放入UNDO-LIST，REDO-LIST为空
  3. 从检查点正向扫描事务
    新开始的事务放入UNDO-LIST
    提交的事务放入REDO-LIST
  4. 处理每个队列中的事务

并发控制与封锁

可以通过阅读P311的图理解掌握丢失修改、不可重复读、读“脏”数据的相关概念

并发操作与数据的不一致性
answer P310
1. 丢失修改：两个事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失
2. 不可重复读：事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。
3. 读“脏”数据：事务T1修改某一数据，并将其写回磁盘。事务T2读取同一数据后，T1由于某种原因被撤销。这时T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致
封锁
answer P310
1. 封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁
2. 加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。
3. 封锁是实现并发控制的一个非常重要的技术
4. 一个事务对某个数据对象加锁后究竟拥有什么样的控制由封锁的类型决定。
5. 基本封锁类型：排他锁（写锁，Exclusive Locks，简记为X锁）；共享锁（共享锁，Share Locks，简记为S锁）
6. 排他锁：
  - 若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。
  - 保证其他事务在T释放A上的锁之前不能再读取和修改A
7. 共享锁：
  - 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁
  - 保证其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改