数据库第四章习题_完整版

数据库系统概念第 4 章课后习题（完整版）

---

1. 实践习题

1.1 请考虑以下 SQL 查询，该查询旨在查找 2017 年春季讲授的所有课程的标题以及教师的姓名的列表。

select name, title
from instructor natural join teaches natural join section natural join course
where semeser = 'Spring' and year = 2017

请问这个查询有什么问题?

首先 section 中并没有我们需要使用到的属性，所以这里 “natural join setion” 是多余的。

其次，更重要的一点是：在 instructor 关系和 course 关系中都有 dept_name 属性，但是它们代表着不同的含义，一个指教师所属的院系，一个指开课院系。这两个含义不同的属性因为名称相同，会被 natural join 合并，结果是：那些由非开课院系的老师教授的课程不会被显示在查询结果中。

1.2 用 SQL 写出下面的查询:

1. 请显示所有教师的列表，展示每位教师的 ID 以及所讲授课程段的编号(据网上查到的信息，应该是讲授的课程数量)。对于没有讲授任何课程段的教师，确保将课程段编号显示为 0。你的查询应该使用外连接，且不能使用子查询。

   select ID, coalesce(sec_id,0)	# 如果 sec_id 为空，则返回 0，否则返回 sec_id
   from instructor natural left outer join teaches
   order by ID;

   select ID, count(sec_id)
   from instructor natural left join teaches
   group by ID;

2. 请使用标量子查询且不使用外连接来写出 1 中的查询。

   SELECT
   	ID,
   	(SELECT count(sec_id) FROM teaches
   		WHERE teaches.ID = instructor.ID)
   FROM instructor;
   

3. 请显示 2018 年春季开设的所有课程段的列表，包括讲授课程段的每位教师的 ID 和姓名。如果一个课程段有不止一位教师讲授，那么有多少位教师，此课程段在结果中就出现多少次。如果一个课程段并没有任何教师，它也要出现在结果中，相应的教师姓名被置为“一” 。

   网上查到的需要使用 decode() 函数，但是 decode() 函数是 oracle 特有的，在 mysql 中可以用 case 或 if 实现。

   select
       year,
       semester,
       sec_id,
       case
           when ID is null then "--"
           ELSE ID
       end,
       case
           when name is null then "--"
           else name
       end
   from
       section natural left join teaches natural left join instructor
   where
       year = 2018
       and semester = "Spring";
   

4. 请显示所有系的列表，包括每个系中教师的总数，不能使用子查询。请确保显示没有教师的系，并用教师计数为零的方式来列出这些系。

   insert into department values("tmp", "tmp", 666);
   select dept_name, count(ID)
   from department natural left join instructor
   group by dept_name;
   

1.3 不使用 SQL 外连接 (outer join) 运算也可以在 SQL 中计算外连接表达式。为了阐明这个事实，请展示如何不使用外连接 (outer join) 表达式来重写下面的每个 SQL 查询:

1. select* from student natural left outer join takes

   (select * from student natural join takes)
   UNION
   (select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
   	from student
   	where ID not in (select ID from takes)
   );
   
   -- not exists
   (select * from student natural join takes)
   union(
   	select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
       from student
       where not exists(select * from takes where takes.ID = student.ID)
   )
   

2. select* from student natural full outer join takes

   (select * from student natural join takes)
   UNION(
       select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
   	from student
   	where ID not in (select ID from takes)
   )
   UNION(
       select NULL, NULL, NULL, NULL, course_id, sec_id, semester, year, grade
       from takes
       where ID not in (select ID from student
   ));
   

1.4 假设我们有三个关系: r(A, B)、s(B, C) 和 t(B, D),其中所有属性都声明为非空 (not null)。

1. 请给出关系r、s 和 t 的实例，使得在 (r natural left outer join s ) natural left outer join t 的结果中，属性 $C$ 具有一个空值，但属性 $D$ 具有一个非空值。

   满足条件的关系实例如下图：

2. 是否存在 r、s 和 t 的实例，使得在 r natural left outer join (s natural left outer join t) 的结果中，$C$ 有一个空值， 但 $D$ 有一个非空值? 请解释为什么存在或为什么不存在。

   答：不存在。上述式子会先执行：s natural left outer join t，因为它是左外连接，所以 s 中 的元组会被全部保留，也就是结果中不可能会有 C 为空值的元组；接下来会执行：r natural left outer join res(B, C, D)，它也是左外连接，所以会保留 r 中的全部元组，对那些 B 值只出现在 r 中而不出现在 res(B, C, D) 中的元组，C, D 的值都会被置为空值。也就是说 C D要么同时非空，要么同时为空值。【res(B, C, D) $\leftarrow$ s natural left outer join t】

1.5 测试 SQL 查询: 为了测试是否正确地用 SQL 写出了一个用文字表达的查询，通常会让 SQL 查询在多个测试数据库上执行，并人工检测每个测试数据库上的 SQL 查询结果是否与用文字表达的意图相匹配。

1. 在4.1.1节中我们见过一个错误的 SQL 查询示例，它希望找出每位教师讲授了哪些课程; 该查询计算instructor, teaches 和 course 的自然连接，其结果是它无意地造成了让 instructor 和course 的 dept_name 属性取值相等。请给出一个数据集的样例，它能有助于捕捉这种特别的错误。

   错误的原因是：连接后的关系中只会显示那些所在院系和所讲授课程的院系相同的元组，讲授非本院院系的老师不会被显示。所以满足题目的数据集样例是那些具有如上数据的。

2. 在创建测试数据库时, 对每个外码来说，在被引用关系中创建与引用关系中任何元组都不匹配的元组是很重要的。请用大学数据库上的一个查询样例来解释原因。

   原因：用于检测查询的连接条件是否合适。比如要查询学生的ID及他所选修过的课程数目时，如果 takes 表中引用了 student 表中所有的 ID 值，那么使用自然连接和外连接都可以得到正确的查询结果；但是如果 student 表中有一些 ID 值在 takes 表中没有出现，那么就只有外连接可以得到正确的查询结果，如果使用自然连接的话，那些没有在 takes 表中出现的 ID 就不会在查询结果中出现了（而实际上他们应该出现，且被显示为：(ID, 0) ）

3. 在创建测试数据库时,倘若外码属性可空，为外码属性创建具有空值的元组是重要的 ( SQL允许外码属性取空值，只要它们不是主码的一部分， 并且没有被声明为非空 ( not null )。请用大学数据库上的一个查询样例来解释原因。

   原因：用于检测查询的连接条件是否合适。比如要查询每个课程的 ID 及开课院系所在的办公楼时，如果 course 表中的外码 dept_name 全部非空，那么使用自然连接和外连接都可以得到正确的查询结果；但是如果 course 表中有一个元组在外码 dept_name 上取空值，那就只能使用外连接了，如果使用自然连接的话，course 表中 dept_name 取值为空的元组就不会被显示在查询结果中了。

提示: 请使用来自实践习题 2 的查询。

1.6 基于实践习题 3.2 中的查询，请展示如何定义视图 student_ grades (ID, GPA), 它给出了每名学生的平均绩点; 请回忆一下，我们用关系 grade_ points (grade, points) 来把用字母表示的成绩等级转换为用数字表示的得分。请确保你的视图定义能够正确处理在 takes 关系的 grade 属性上取空 (null) 值的情况。

答：这里 takes 关系的 grade 属性为空代表这门课程正在修读中，还没有成绩，所以在计算均绩时 需忽略这门课程，也就是使用自然连接。

create view student_grade(ID, GPA) as
select ID, sum(points)/count(points)
from takes natural join grade_points
group by ID;

select * from student_grade;

1.7 请考虑图 4-12中的员工数据库。请给出这个数据库 SQL DDL 定义。请指出应该保持的引用完整性约束，并把它们包括在 DDL 定义中。

create database employees;

create table employee(
    ID  varchar(5) primary key,
    person_name     varchar(20) not null,
    street          varchar(30),
    city            varchar(10)
);

create table company(
    company_name    varchar(25) primary key,
    city            varchar(10)
);

create table works(
    ID              varchar(5) primary key,
    company_name    varchar(25),
    salary          decimal(10, 2),
    foreign key (ID) references employee(ID),
    foreign key (company_name) references company(company_name)
);

create table manages(
    ID              varchar(5) primary key,
    manager_id      varchar(5),
    foreign key (ID) references employee(ID),
    foreign key (manager_id) references employee(ID)
);

1.8 正如在 4.4.8 节中所讨论的，我们希望能够满足约束“每位教师不能在同一个学期的同一个时间段在两个不同的教室授课"。

1. 请写出一个SQL查询，它返回违反此约束的所有(Instructor, section)组合。

   select * from (select * from teaches natural join section) as S
   where exists(
   	select * from (select * from teaches natural join section) as T
       where T.ID = S.ID
       and T.year = S.year
       and T.semester = S.semester
       and T.sec_id = S.sec_id
       and (T.building <> S.building or T.room_number <> S.room_number)
   ) order by ID;
   

2. 请写出一个SQL断言来强制实现此约束 (正如在4.4.8节中所讨论的那样，当今的数据库系统并不支持这样的断言，尽管它们是SQL标准的一部分)。

   create assertion teacher_time check(
       not exists(
           select * from ( select * teaches natural join section ) as S
           where
               exists (
                   select * from ( select * teaches natural join section ) as T
                   where
                       T.ID = S.ID
                       and T.year = S.year
                       and T.semester = S.semester
                       and T.sec_id = S.sec_id
                       and (
                           S.building <> T.building
                           or S.room_number <> T.room_number
                       )
               )
       )
   );
   

1.9 SQL 允许外码依赖引用同一个关系，如下所示:

create table manager (
    employee_ID char(20),
    manager_ID char(20),
    primary key employee_lD,
    foreign key (manager.ID) references manager(employee.ID) on delete cascade
)

在此，employee. ID 是 manager 表的码，这意味着每位员工最多只有一位经理。外码子句要求每
位经理也是一位员工。请准确解释当 manager 关系中-一个元组被删除时会发生什么情况。

答：DDL 中 manager_ID 被定义为外键的语句中有 “on delete cascade”，说明：当 manager 关系中一个元组被删除时，假设这个被删除元组中的 employee_ID 为 xxx，那么 manager 关系中所有 manager_ID 为 xxx 的元组也都会被删除（就是经理被删掉了，经理手下直接或间接带的员工也都会被删掉），接着那些以被删掉的员工为经理的员工也会被删掉…即会发生级联删除。

1.10 SQL-92 provides an n-ary operation called coalesce, which is defined as follows: $coalesce(A_1,A_2,\dots ,A_n)$ returns the first nonull $A_i$ in the list $A_1,A_2,\dots ,A_n$ and return null if all of $A_1,A_2,\dots ,A_n$ are null.

Let a and b be relations with the schemas $A(name,address,title)$ and $B(name,address,salary)$, respectively. Show how to express a natural full outer join b using the full outer join operation with an on condition and the coalesce operation. Make sure that the result relation does not contain two copies of the attributes $name$ and $address$, and that the solution is correct even if some tuples in $a$ and $b$ have null values for attributes $name$ or $address$.

(select coalesce(a.name, b.name), coalesce(a.address, b.address), title, salary
from a left outer join b on a.name = b.name and a.address = b.address)
UNION
(select coalesce(b.name, a.name), coalesce(b.address, a.address), title, salary
from b left outer join a on a.name = b.name and a.address = b.address);


-- 或者不用 coalesce()
(select a.name, a.address, title, salary
from a left outer join b on a.name = b.name and a.address = b.address)
UNION
(select b.name, b.address, title, salary
from b left outer join a on a.name = b.name and a.address = b.address);

NOTE：下面这样的代码是错误的噢

(select a.name, a.address, title, salary
from a left outer join b on a.name = b.name and a.address = b.address)
UNION
(select a.name, a.address, title, salary
from b left outer join a on a.name = b.name and a.address = b.address);

这里面要注意的点在于：b left outer join a 的时候，如果 b 中有一个元组，它的 addresss 属性值为空（name 属性值不为空），那么在连接时，由于空值的特殊性，是找不到一个 a 中的元组与这个 address 为空的元组匹配的，也就是说，这个 b 中的 address 属性为空的元组对应的连接结果中 a.name, a.address, a.title 都为空值；由于 select a.name 所以结果中 name 值也为空了，实际上不应该为空而应该是 b.name 的值。所以需要把下面一个左外连接改为 select b.name, b.address。

当然，如果使用 coalesce() 就不会这么麻烦了，因为：

• a.name, b.name 都不为空时，on 条件限制了 a.name = b.name，所以返回哪一个都是对的
• a.name, b.name 中有一个为空时，需要返回不为空的那一个
• a.name, b.name 都为空时，返回空值即可

1.11 操作系统通常只为数据文件提供两种类型的授权控制: 读访问和写访问。为什么数据库系统提供这么多种授权?

通过操作系统实现安全性约束比较复杂？

（我赌期中考试不会考）

1.12 假设一个用户想授予另一个用户一个关系上的选择 (select)访问权限。为什么该用户应该在授权 (grant) 语句中包含(或不包含) granted by current_role子句?

答：

granted by current_role 是指使用和当前会话相关联的角色而不是当前用户来进行授权行为。

如果使用角色来授权，那么即使授予权限的用户将来被撤销了权限，只要这个角色还具有 select 权限，那么被授予权限的用户将会继续拥有 select 权限，即可以避免不必要的级联回收。

而如果使用当前用户来授权，那么当授予权限的用户被撤销权限时，被授予权限的用户也会被撤销 select 权限，即会进行级联回收。

1.13 请考虑一个视图 v, 其定义仅引用关系 r。

1. 如果一个用户被授予 v 上的选择 (select) 权限，该用户是否也需要在 r 上具有选择权限? 试问为什么需要或者为什么不需要?

   答：需要。因为视图是一个虚关系，被存储起来的是定义它的查询语句，在使用视图的时候，数据库会执行定义视图的查询语句，也就是会在视图的引用关系 r 上执行查询，所以用户需要在 r 上具有选择权限。

2. 如果一个用户被授予 v 上的更新 (update) 权限，该用户是否还需要在 r 上具有更新权限?试问为什么需要或者为什么不需要?

   答：需要。因为所有在视图上执行的更新（和插入）操作，都是在视图的引用关系 r 上进行的，所以需要在 r 上的更新权限。

3. 请在视图 v 上给出一个插入 (insert) 操作的示例，以添加在 select * from v 的结果中不可见的元组 l。请解释你的答案。

   答：对于没有 with check option 的视图定义。比如说如下的视图 v 定义：

   create view v as
   select * from instructor where dept_name="History";
   

   如果对该视图执行如下的插入操作：

   insert into v values("66666", "bio_teacher", "Biology", 20000);
   

   虽然可以成功执行，但是 select * from v 的结果中不会出现上面插入的元组。

2 习题

2.1 请考虑查询:

select course_id, semester, year, sec_id, avg (tot_cred)
from takes natural join student
where year = 2017
group by course_id, semester, year, sec_id
having count (ID) >= 2;

请解释为什么在 from 子句中添加 natural join section 不会改变结果。

答：

首先， takes 关系上 ID, course_id, sec_id, semester, year 都为外码而且被声明为了非空，所以它们必定满足外码约束，所以 takes natural join student 的结果中不会有某个属性值为空值的元组，takes natural join student natural join section 的结果中也不会有某个属性值为空值的元组，也就是，takes natural join student 结果中的元组数目和 takes natural join student natural join section 结果中的元组数目是一样的。

其次，section 关系中和 (takes natural join student) 同名的属性只有：course_id, semester, year, sec_id ，而且这些属性在 section 表和在 takes 表中的含义是一样的，所以即使添加了 natural join section 进行了合并之后，也不会改变结果。

2.2 请重写查询:

select *
from section natural join classroom

不使用自然连接，而是使用具有 using 条件的内连接

答：

select *
from section join classroom using(building, room_number);

2.3 请使用大学模式来编写一个 SQL 查询，以查找从未在大学上过课的每名学生的 ID。不使用子查询和集合运算(使用外连接)来执行此操作。

答：

select student.ID
from student natural left outer join takes
where course_id is null;

2.4 在 SQL 中不使用子查询和集合运算来表达下述查询。

(
    seleet ID
    from
        student
)
except
    (
        select
            s_ld
        from
            advlsor
        where
            I_ID is not null
    )

答：上述查询查找所有没有导师的学生的 ID。

select *
from student left outer join advisor on student.ID = advisor.s_ID
where advisor.i_ID is null;

2.5 对于图 4-12 中的数据库，请编写一个查询来找出没有经理的每位员工的 ID。请往意，一位员工可能只是没有列出经理，或者可能经理值为空。请使用外连接来编写你的查询，然后根本不使用外连接再重写查询。

答：使用外连接

select ID
from employee left outer join manages using(ID)
where manager_id is null;

不使用外连接

select ID
from employee
where ID not in (select ID from manages where manager_id is not null);


select ID
from student
where not exists(
	select * from advisor
	where s_ID = ID
);

2.6 在什么情况下，查询

select *
from student natural full outer join takes natural full outer join course

会为 title 属性包含具有空值的元组?

答：当 student 中有学生一节课都没有选过时。此时，student natural full outer join 的结果中会有一个元组，它在 course_id, sec_id, semester, year, grade 属性上的取值为空值，接着再执行 natural full outer join course 时，因为 course_id 的属性为空，所以这个元组连接之后得到的元组中 title 的取值也为空。

2.7 请说明如何定义视图 tot_credits(year, num_credits), 它给出每年所修的总学分数。

drop view if exists tot_credits;

create view tot_credits(year, num_credits) as
select year, sum(credits)
from takes natural join course
where grade is not NULL and grade <> 'F'
group by year;

select * from tot_credits;

2.8 对于习题 2.7 中的视图，请解释为什么数据库系统不允许通过该视图将元组插入数据库中。

答：

1. select 子句中有聚集函数
2. from 子句中出现了多于一个关系
3. 视图定义中有 group by 子句
4. 没有出现在 select 子句中的属性，比如：ID, course_id, sec_id, semester, year 等不能为空值

2.9 请说明：如何使用 case 结构来表达 coalesce 函数。

-- coalesce(salary, 0)
case
    when salary is null then 0
    else salary
end;

2.10 请解释为什么当一位经理(比如 Satoshi )授予权限时，授权应该由经理角色而不是由用户 Satoshi 来完成。

如果是由用户 Satoshi 来执行授权，那么当 Satoshi 被撤销这项权限时，被 Satoshi 授予权限的用户也会被收回权限，即级联回收。

而如果是由经理角色来执行授权，那么即使 Satoshi 用户被撤销了这项权限，只要经理角色还具有这项权限，那么被授予权限的用户就会继续拥有这项权限。这避免了不必要的权限回收。

2.11 假设具有关系 r 上的所有授权特权的用户 A 使用 grant option 将关系 r 上的选择权限投给公众 (public)。然后假设用户 B 将对 r 的选择权限又授予 A。这是否会导致授权图中出现环路?请解释为什么。

答：会出现环路，但是并不会使得 A 和 B 所拥有的 select 权限不可撤销。因为用户具有某项权限的充分必要条件是：在授权图中存在一条从根节点（管理员用户）到该用户的路径。所以即使授权图中 A 和 B 之间有回路，只要管理员撤销了 A 上的权限，从根节点到 A、B 就不存在路径了，即仍然可以成功撤销 A B 上的权限。

2.12 假设一个用户创建了一个带外码的新关系 r1,且该外码引用另一个关系 r2。则此用户在 r2 上需要什么授权特权? 为什么在没有任何此类权限的情况下，不能简单地允许这样做?

答：此用户需要具有 r2 上的 references 授权。

因为，当 r1 引用了关系 r2 后，外码约束会限制 r2 上的删除和更新操作，所以不能简单地允许 r1 印用 r2，而是需要先进行 references 授权。

2.13 请解释完整性约束和授权约束之间的区别

答：

完整性约束是针对为了保证数据库中数据一致性、防止对数据的意外破坏的约束。

授权约束则是对允许用户对什么关系进行哪些操作的约束。

用户想要修改数据库中的某个关系，需要首先具有修改这个关系的权限，然后还要保证所做出的修改满足完整性约束，才能成功进行修改。