数据库系统概论考试心得+复习指南

红太狼 2023-02-23 08:26 143阅读 0赞

仅适用于 LSU 的数据库系统概论课程。

## 一、数据库系统概论总结 ##

> 根据马老师提供的大纲加以补充

试后感悟：老师给的大纲完全就是按照试卷的顺序来的，比如触发器是第一题，数据库恢复是第二题。

### 1、题型 ###

1、简答 `4` 题 共 `20` 分；  
2、编程题 `13` 题 共 `40` 分；  
3、综合题 `4` 题 共 `40` 分；

### 2、内容梳理 ###

1、触发器概念；

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，触发器又叫 `事件-条件-动作` 规则。

注意：

*  是定义在关系表上的，所以用 `SQL` 语句的时候要加上 `on` 表名；
 *  由事件驱动的，满足条件就会启动，这里的条件一般都是 insert 或者 delete 啥的；
 *  `事件-条件-动作` 是指：由事件触发，满足一定的条件就执行动作；

试后感悟：考了触发器的特点，然后给你一段触发器的代码让你说明这段代码的作用。

2、数据库故障，如何恢复，日志文件，备份；

数据库故障有哪些，及恢复策略：

*  事务内部的故障：反向扫描日志文件并对事务的操作执行逆操作，直到事务的开始标记；
 *  系统故障：正向扫描日志文件，对在故障发生之前提交的事务执行重做操作，重做操作即正向扫描日志文件然后再次执行事务，对在故障发生之前没完成的事务执行撤销操作，撤销操作即反向扫描日志文件然后执行事务的逆操作；
 *  介质故障：重装数据库然后重做已完成的事务；
 *  计算机病毒：一般无法恢复。

数据库的日志文件：日志文件是用来记录事务对数据库的更新操作的文件，日志文件中记录事务的开始和结束标志以及事务的更新操作，使用检查点技术可以改善恢复的效率；

数据库备份：是指数据库管理员定期的将整个数据库复制到其他存储介质上保存起来的过程。

试后感悟：考了简述系统故障，问你需要恢复数据库大的故障是哪一个。

3、数据库不一致，各级封锁协议；

数据库不一致带来的问题：事务是并发控制的基本单位，而并发会破坏事务的隔离性和一致性，带来丢失修改、不可重复读、读脏数据的问题；

各级封锁协议解决的问题：[使用封锁协议解决事务的并发问题][Link 1]

试后感悟：考了三级封锁协议的内容，给你一个图让你判断有什么并发问题。

4、自主权限控制，强制权限控制；

数据库安全性控制那一节：书本 `P140`

自主存取控制 `MAC`：即使用 `grant` 和 `revoke` 语句进行操作，用户可以”自主“的决定将数据的存取权限授予何人、决定是否也将”授权“的权限授予别人。所以叫做自主存取控制。

强制存取控制：将全部实体分为 **主体** 和 **客体** 两大部分。

试后感悟：考了 MAC 的特点，然后给你一段代码让你说出他的意思，用了 `grant` ，我觉得另一份试卷会考强制存取控制。

5、三层模式两级映像，数据独立性；

三层模式两级映像：模式-描述了数据库中全体数据的逻辑结构；外模式-描述了数据库中局部数据的逻辑结构；内模式-描述了数据的物理结构和存储方式。

一个数据库只有一个模式，也只有一个内模式，可以有多个外模式；

数据独立性：在模式之间有 外模式/模式 映像 和 模式/内模式 映像；这样当数据库的逻辑结构或者存储方式改变时只只需要改变相应的映像即可。

试后感悟：没考，我觉得另一份试卷会考。

6、事务特性、死锁；

事务的特性：`ACID` 原子性、一致性、隔离性、持久性；

死锁：

*  两个事务互相等待，永远不能结束，形成死锁。
 *  死锁的诊断与解除，可以使用超时法和等待图法，等待图即设置一个有向图，将节点作为正在进行的事务，边代表事务的等待情况，当出现环时即代表出现了死锁；
 *  解除的方式是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁。

试后感悟：没考，我觉得另一份试卷会考。

7、`sql` 语句：建表、查询、插入、删除、修改、视图、授权、收回权限；

试后感悟：第二部分全是 SQL 语句，首先是给你一个用中文写好的表名字段名，让你根据这个写出建表语句，自己定义数据类型和约束，然后根据这几个表考察 SQL 语句；只要掌握最简单的就可以了，真的很简单，也不用担心英语不好，这里居然是要求使用中文字段名，也没考到触发器和存储过程等高级应用。

常用的语法:
    create table student ( -- 建表语句
    	id int primary key identity(1,1), -- 主键自增,从1开始每次增加1,可以简写为: identity
        name varchar(8) not null,
        age tinyint,
        bid int,
        constraint ch_age check (age between 16 and 40),
        foreign key (bid) references Books (id)
    );
    
    alert table student add constraint primary key (id); -- 添加约束: 主键约束: 表级
    alert table student alert column name char(6) null; -- 修改属性类型及约束
    alert table student add column class varchar(10) not null; -- 添加属性
    
    select * from student; -- 查询所有
    select * from student where 1=1; -- 条件查询
    select * from student join sc using (sid) join course using (cid); -- 连接查询
    
    insert into student(name, age) values ('小王', 18); -- 插入数据
    
    delete from student where id=1; -- 删除
    
    update student set name='小李', age=19 where id=2; -- 修改
    
    create view vi_student as -- 创建视图
    select * from student where id > 10;
    
    grant select (name) on student to user1 -- 授予权限
    with grant option; -- 允许他再给别人授权
    
    invoke select (name) on student from user1; -- 收回权限
    
    -- 存储过程: 相当于函数接收参数必须使用 @,后面的类型和定义时一致
    create procedure proc_stud @age tinyint, @name varchar(8) as
    select * from student where age=@age and name=@name;
    
    -- 执行存储过程: 传递参数
    execute proc_stud @age=18, @name='小王';
    
    -- 触发器-特殊的过程: 在用户删除的时候限制他不能同时删除多个: 不然提示它信息
    create trigger tr_stud on student
    for delete
    as
    if ((select count(*) from deleted) > 1)
    	begin
    		print '一次只能删除一条数据';
    		rollback transaction;
    	end
    	
    -- 无论是删除存储过程还是触发器都是用 drop
    drop trigger/procedure tr_stud;
    
    -- SQL 中定义变量
    create trigger tr_stud on Students
    for delete
    as
        declare @count int;
    	select @count = count(*) from deleted;
    	if (@count > 1)
        	begin
            	print '不能一次删除多个数据';
            	rollback transaction;
        	end
    
    -- 我认为最难的查询: 相关子查询: 举个例子模拟题的综合体的第二题的第8小问
    -- 检索出既购买了《平凡的世界》又购买了《软件设计师真题讲解》的会员的会员号、会员名
    select u1.uid, u1.uname from Users u1 
    	join Buy y1 using (uid) 
    	join Books b1 using(bookid) 
    where b1.bookname='平凡的世界' and exists (
        		select * from Users u2 
                	join Buy y2 using (uid) 
                	join Books b2 using(bookid) 
                where u1.uid=u2.uid and b2.bookname='软件设计师真题讲解'
    );
    -- 当然也可以使用那种比较简单的方式,但是这种方法应该掌握,效率比那个高;

8、关系模式的 `FD` 、范式、关键字分析，模式分解；

[数据库关系模式的函数依赖习题讲解][Link 2]

试后感悟：专门拿出来一个大题考察的，这个必须掌握，我那篇文章已经写得很详细了。

9、`ER` 模型绘制；

必考 ER 图直接看书，一看就懂；

或者语言阐述一下：

*  分为两块，一块是表的结构，另一块是表之间的联系，后者更难；
 *  表的结构就是：表画个方块，属性画个圈；
 *  联系就是一个菱形，连接刚才画的方块；
 *  一对一，一对多，多对多写在横线上`1-1`,`1-n`，`m-n`；

试后感悟：有两个大题考了 ER 图，加起来 30 分吧，你看着办，第一个是根据那些描述让你画 ER 图，另一个是给你 ER 图了，让你写出关系模式。

10、关系数据库概念模型（`ER` 图）到逻辑模型的转换（关系模式）；

必考——关系模式指的是这种： 学生（学号，姓名，年龄） 主码是学号；

这是逻辑模型，物理模型是那种真正的表结构，手动画个表；

试后感悟：太重要了！！！ 后面我给的模拟题一定要做！多练。

11、`armstrong` 公理系统、属性集 `X` 关于函数依赖集 `F` 的闭包求取算法。

> 直接看书，一看就懵。

-- 关系代数
    select A,B,D from R,S where R.C=S.C 等价于 πA,B,D(σR,C= S.C(R×S))

试后感悟：没考到，也没考关系代数，我觉得另一份试卷会考。

## 二、模拟题 ##

点击下方链接移步百度文库，我上传到那里了。

数据库系统概论模拟题 : [https://wenku.baidu.com/view/be0a494dcebff121dd36a32d7375a417876fc147][https_wenku.baidu.com_view_be0a494dcebff121dd36a32d7375a417876fc147]

一定要自己做，做完了或者不会做再看答案。

## 三、参考答案 ##

做完记得对答案：

## 模拟题参考答案+解析 ##

> 自己做的，不一定正确，有错误指出：wangsuoo@foxmail.com

### 1、选择题 ###

1.  选 C，本来要两个才能确定，你只给一个肯定不能确定；
2.  选 B，行控制的是查看学生的数量，列控制还是能查看到所有学会，只是信息变了；
3.  选 B，`Undefined` 的意思是未定义；
4.  选 A，存在部分依赖；
5.  选 D，笛卡尔积；
6.  选 C，外码一般都为 id；
7.  选 B，一对多；
8.  选 C，并连接选择，连接即笛卡尔积；
9.  选 D，等值连接，选择相同的并且去掉重复行；
10. 选 B，和书本例题一样，属于丢失修改；
11. 选 C，这一道题 C 看起来更对一点，肯定不能直接复制粘贴；
12. 选 D，多对多关系；
13. 选 C，外键一定要搞清楚，一般都是 `id` 作为外键，其余信息越多越好，因为考虑到现实情况嘛；
14. 选 B，索引不是越多越好，他会占用存储空间，聚集索引一个表只能有一个，一般我们建立的都是非聚集索引，经常被用来检索的字段适合建立索引，图书的种类就那几个，肯定不用建立索引；
15. A，这就是脏读的定义，脏读都会有回滚操作，即 rollback；
16. B，要查询部门号肯定要按照部门号分组，where 子句中不能使用聚合函数；
17. B，distinct 是 独特的 意思；
18. C，这啥玩意没看懂，貌似 `TempDB` 是一个临时的数据库或者系统数据库，不像用户自定义的，应该不能分离；
19. D，不加 where 子句就表示删除所有数据，drop 是把表给删了；
20. B，一个数据库只能有一个模式，只能有一个内模式，可以有多个外模式，其他几个选项需熟记；

### 2、简答题 ###

> 解法不唯一，仅供参考。

1.  `F = {学号 —> 姓名，学号 —> 性别，学号 —> 出生日期，学号 —> 专业编号，专业编号 —> 专业名称，专业编号 —> 学院，学院 —> 院长}`

这样划分的原因是：有了学号就可以把后面的全部确定了，有了专业编号可以确定后面的学院之类的。

这是不符合 `3NF` 的，因为存在传递依赖，但是不存在部分依赖，所以他是 `2NF`；

传递依赖如下：

*  `学号 —> 专业编号，专业编号 —> 专业名称`
 *  `专业编号 —> 学院，学院 —> 院长`

规范化为 `3NF`：

R1  (学号, 姓名, 性别, 出生日期, 专业编号)
    R21 (专业编号, 专业名称, 学院)
    R21 (学院, 院长)

1.  在用户检索的时候，检索就是查询，其他人无法更改数据了，说明已经加了共享锁，因为在共享锁上面只能再加共享锁，不能加排它锁，而用户不能修改就说明他修改需要加排它锁，而系统不准，所以肯定是二级封锁协议以上，再来看：直到事务终止为止，二级封锁协议是查询完了就可以释放共享锁，三级封锁协议是事务终止才可以释放共享锁，所以是三级封锁协议。
2.  这一题有 4 小问：

我们规定各信息如下: employer(id, name, sex, birthday, card, department, education)
    
    (1) 第一小问:
    
    1. 为身份证号设置唯一约束 uq_employer: 确保唯一;
    alter table employer add constraint uq_employer unique (card) ;
    
    2. 为出生日期设置检查约束 ch_employer: 确保所有员工均年满 20 周岁
    alter table employer add constraint ch_employer check (birthday < 1996-06-31)
    
    (2) 第二小问:
    
    0.为用户 U1 授予对员工表的 select、update 操作的权限并允许 U1 将权利授予其他用户
    grant select, update on employer to U1
    with grant option;
    
    (3) 第三小问:
    
    0.创建带参数的存储过程 pr_ employer ,查询出员工表中所在部门字段为某个部门且学历为某个学历的员工的所有信息
    create procedure pr_employer @department varchar(10), @education varchar(10) as
    select * from employer where department=@department and education=@education;
    
    (4) 第四小问:
    
    0.执行上述存储过程，查询财务部学历为本科的所有员工。
    execute pr_employer @department='财务部', @education='本科';

### 3、综合题 ###

第一道题和第二道题密切相关，都是 `SQL` 语句的考察，所以写在一起了。

实体如下:
    Books（bookid，bookname，class，author，pubcompany，price，pdate）
    Users（uid，uname，password，mobilephone，address）
    Buy（id，uid，bookid， bdatetime，number，note）
    
    一、首先设计中的 5 处错误:
    1. bookid 应该是 not null
    2. price 应该是 money 类型的， 因为他说可计算
    3. password 应该是 not null
    4. Buy 表中的 id 设置为自增了，自增的数据类型只能是数字，这里是 char，不对，应该改为 int；
    5. Buy 中的 uid 和 bookid 应该是外键，所以数据类型应该和依赖的表中的数据类型保持一致；
    6. Buy 中的电话号码应该是 char(11)，因为电话号码都是 11 位,而且这里 int 最大才 10 位,2的32次方嘛；
    
    二、改好之后做编程题：
    1. 使用关系代数表达式表达出：检索价格高于30元的图书的图书名和出版社。完成后，再使用SQL语句表达该关系代数描述的检索。
    
    关系代数那个符号好难打: sigma=σ 
    
    ∏ bookname,pubcompany (σ price>30 (Books))
    
    select bookname, pubcompany 
    from Books 
    where price>30;
    
    2.使用关系代数表达式表达出：检索购买了《平凡的世界》的会员的用户名、手机号码、地址。
    那个符号实在打不出来,用这个代替了: |><| 连接
    
     ∏ uname,mobilephone (σ bookname>'平凡的世界' (Users |><| Buy |><| Books))
     
    3.由于书名经常被检索，在该列上创建了非聚集索引index_bookname，请写出创建索引的SQL语句
     create index index_bookname on Books(bookname);
     
    4.小王想购买一本关于软件设计师的书进行备考复习，在网站的搜索栏里输入了“软件设计师”几个字，相关的书籍都搜索出来了;
     select * from Books where bookname like '%软件设计师%';
     
    5.网站首页显示了销量最高的10本图书的图书名，请写出实现这一操作的SQL语句
     
    select top 10 bookname 
    from Books  
    	join Buy using (bookid)
    group by bookname
    order by count(number) desc;
    
    6.将用户号为‘13221’且书号为‘010125’的订单的定购数量改为100
    update Buy set number=100 
    where uid='13221' and bookid='010125';
    
    7.网站的管理人员想知道每位会员的购书情况，包括购买了书和未购书的，显示会员号，购书本数
    select u.uid, count(number)
    from Users u
    	left join Buy
    	using (bookid)
    group by u.uid;
    
    8.检索出既购买了《平凡的世界》又购买了《软件设计师真题讲解》的会员的会员号、会员名
    select u1.uid, u1.uanme
    from Users u1
    	join Buy b1 on u1.uid=b1.uid
    	join Books k1 on b1.uid=k1.uid
    where k1.bookname='平凡的世界' and exists (
        select *
        from Users u2
        	join Buy b2 on u2.uid=b2.uid
        	join Books k2 on b12.uid=k2.uid
        where k2.bookname='软件设计师真题讲解' and u1.uid=u2.uid
    );
    
    9.某用户通过网站注册成了会员，他填写的信息是用户号“3309”，用户名“可爱多”，密码“123”，其余信息由于网站显示不是必选项，该用户未填写。请写出该用户信息插入数据库中的SQL语句。
    insert into Users (uid, uanme, password) values ('3309', '可爱多', '123');
    
    10.网站的某管理员经常查看“小说”类型的高档书籍(价格高于50元的书籍)的情况，请为他定义一个视图V_book，方便其查看书籍的书号、书名、价格。要求该对视图的修改和插入必须满足视图的定义。请写出实现这一操作的SQL语句。
    
    create view V_book as
    select bookid, bookname, price
    from Books
    where price>50 and class='小说'
    with check option;
    
    11.网站会对一些书籍在数据库中进行删除操作，要求一次只能对一本书籍进行删除，若对不止一本书籍进行删除则显示“操作不成功，一次只能删除一条记录”。请编写触发器delbook来实现上述操作。
    
    create trigger delbook on Books
    for delete as
    	if ((select count(*) from deleted) > 1)
    	begin
    		print '操作不成功，一次只能删除一条记录';
    		rollback transaction;
    	end

第三题，主要是画图：

运动队，属性有队号、队名、教练姓名；
    运动员，属性包括运动员号、姓名、性别、参赛项目；
    比赛项目，属性有项目名、比赛场地。
    
    其中：
    - 一个运动队有一个教练，多个运动员；
    - 一个运动员仅属于一个运动队；
    - 一个比赛项目有多个运动员参加，一个运动员可以参加多个项目，对每个运动员的每一个参赛项目均记录名次。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70][]

关系模式如下:
    运动队(队号,队名,教练姓名), 主码为队号;
    运动员(运动员号,姓名,性别,所在队伍), 主码为运动员号,外码为所在队伍;
    比赛项目(项目名,比赛场地), 主键为项目名;
    比赛(运动员号,项目名,名次), 主码为运动员号和项目名,同时为外码。
    
    create table Team
    (
        tid    int primary key identity,
        tname  varchar(20) not null,
        tcoach varchar(8)  not null
    );
    
    create table Sportor
    (
        sid   int primary key identity,
        sname varchar(8) not null,
        sex   char(2),
        tid   int,
        constraint fk_team foreign key (tid) references Team (tid)
    );
    
    create table Item
    (
        mid   int primary key identity,
        place varchar(50) not null
    );
    
    create table Competition
    (
        sid  int,
        mid  int,
        rank char(10) not null,
        constraint pk_sm primary key (sid, mid),
        constraint fk_sportor foreign key (sid) references Sportor (sid),
        constraint fk_Item foreign key (mid) references Item (mid)
    );

[Link 1]: https://blog.csdn.net/weixin_43941364/article/details/107098955
[Link 2]: https://blog.csdn.net/weixin_43941364/article/details/106143475
[https_wenku.baidu.com_view_be0a494dcebff121dd36a32d7375a417876fc147]: https://wenku.baidu.com/view/be0a494dcebff121dd36a32d7375a417876fc147
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200706152440333.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70