Day 06 - 数据库70题：试题 1-11

参考重点：教材第 3 章 SQL、第 6 章关系数据理论。SQL 均按 SQL Server 写法。

1. 简述在 SQL 中使用视图的优点。

答案：

视图的主要优点有：

简化用户操作，把复杂查询封装成一个虚表。
提高数据安全性，让用户只能看到授权范围内的列和行。
提供一定的逻辑独立性，基本表结构局部变化时可通过修改视图屏蔽影响。
使同一数据能按不同用户需求呈现不同外模式。
便于复用常用查询逻辑，统一统计口径。

讲解：视图不一定保存数据，普通视图保存的是查询定义。教材第 3 章把视图作为 SQL 的重要对象，重点强调“虚表”“简化查询”“安全控制”“逻辑独立性”。

2. 简述游标的作用。

答案：

游标用于在过程化程序中逐行处理 SQL 查询结果集。它可以打开一个结果集，定位当前行，逐行 FETCH，并可结合当前行进行更新或删除。

讲解：SQL 本身是面向集合的语言，但宿主语言或存储过程有时需要逐行处理，例如复杂业务校验、逐行调用过程。游标就是集合操作和逐行过程逻辑之间的桥。能用集合 SQL 解决时，SQL Server 中通常优先使用集合操作，游标只在确实需要逐行处理时使用。

3. 对于下列四个关系模式，试分别用 SQL 语句表示各类查询。

题面：

供应商关系：SUPPLIERS(SNUMBER, SNAME, STATUS, SCITY)
零件关系：PARTS(PNUMBER, PNAME, COLOR, WEIGHT)
工程项目关系：JOBS(JNUMBER, JNAME, JCITY)
供应关系：SPJ(SNUMBER, PNUMBER, JNUMBER, QUANTITIES)

属性含义：SNUMBER 供应商编号，SNAME 供应商姓名，STATUS 状态，SCITY 城市，PNUMBER 零件编号，PNAME 零件名，COLOR 颜色，WEIGHT 重量，JNUMBER 工程项目编号，JNAME 工程项目名，JCITY 城市，QUANTITIES 数量。

3.1 用 `CREATE TABLE` 建立上述四张表，并指出主键和外键。

CREATE TABLE dbo.Suppliers (
    SNumber varchar(20) NOT NULL,
    SName nvarchar(50) NOT NULL,
    [Status] int NULL,
    SCity nvarchar(50) NULL,
    CONSTRAINT PK_Suppliers PRIMARY KEY (SNumber)
);

CREATE TABLE dbo.Parts (
    PNumber varchar(20) NOT NULL,
    PName nvarchar(50) NOT NULL,
    Color nvarchar(20) NULL,
    [Weight] decimal(10, 2) NULL,
    CONSTRAINT PK_Parts PRIMARY KEY (PNumber)
);

CREATE TABLE dbo.Jobs (
    JNumber varchar(20) NOT NULL,
    JName nvarchar(50) NOT NULL,
    JCity nvarchar(50) NULL,
    CONSTRAINT PK_Jobs PRIMARY KEY (JNumber)
);

CREATE TABLE dbo.SPJ (
    SNumber varchar(20) NOT NULL,
    PNumber varchar(20) NOT NULL,
    JNumber varchar(20) NOT NULL,
    Quantities int NOT NULL,
    CONSTRAINT PK_SPJ PRIMARY KEY (SNumber, PNumber, JNumber),
    CONSTRAINT FK_SPJ_Suppliers FOREIGN KEY (SNumber) REFERENCES dbo.Suppliers(SNumber),
    CONSTRAINT FK_SPJ_Parts FOREIGN KEY (PNumber) REFERENCES dbo.Parts(PNumber),
    CONSTRAINT FK_SPJ_Jobs FOREIGN KEY (JNumber) REFERENCES dbo.Jobs(JNumber),
    CONSTRAINT CK_SPJ_Quantities CHECK (Quantities >= 0)
);

讲解：Suppliers、Parts、Jobs 的主键分别是各自编号。SPJ 表描述三者联系，常用 (SNumber, PNumber, JNumber) 作为组合主键，并分别引用三张实体表。

3.2 检索供应工程 J1 零件 P1 的供应商编号。

SELECT SNumber
FROM dbo.SPJ
WHERE JNumber = 'J1'
  AND PNumber = 'P1';

3.3 检索零件重量在 16-19 之间的零件的全部信息。

SELECT *
FROM dbo.Parts
WHERE [Weight] BETWEEN 16 AND 19;

讲解：BETWEEN 16 AND 19 包含边界，相当于 Weight >= 16 AND Weight <= 19。

3.4 检索上海的供应商姓名。

SELECT SName
FROM dbo.Suppliers
WHERE SCity = N'上海';

3.5 检索住在同一个城市的一对供应商编号。

SELECT s1.SNumber AS SNumber1,
       s2.SNumber AS SNumber2
FROM dbo.Suppliers AS s1
JOIN dbo.Suppliers AS s2
  ON s1.SCity = s2.SCity
 AND s1.SNumber < s2.SNumber;

讲解：自连接用于比较同一张表中的不同行。s1.SNumber < s2.SNumber 避免自己和自己配对，也避免 (S1,S2) 与 (S2,S1) 重复。

3.6 检索供应商 S1 提供零件的工程项目名称。

SELECT DISTINCT j.JName
FROM dbo.SPJ AS spj
JOIN dbo.Jobs AS j
  ON j.JNumber = spj.JNumber
WHERE spj.SNumber = 'S1';

3.7 求没有使用天津生产的红色零件的工程项目号。

SELECT j.JNumber
FROM dbo.Jobs AS j
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.SPJ AS spj
    JOIN dbo.Suppliers AS s
      ON s.SNumber = spj.SNumber
    JOIN dbo.Parts AS p
      ON p.PNumber = spj.PNumber
    WHERE spj.JNumber = j.JNumber
      AND s.SCity = N'天津'
      AND p.Color = N'红色'
);

讲解：题面中“天津生产”没有单独的零件产地属性，只能按供应商所在城市理解为“天津供应商供应的红色零件”。双重语义题优先写清假设。

3.8 求使用全部零件的工程项目名称。

SELECT j.JName
FROM dbo.Jobs AS j
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.Parts AS p
    WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.SPJ AS spj
        WHERE spj.JNumber = j.JNumber
          AND spj.PNumber = p.PNumber
    )
);

讲解：这是关系除法的 SQL 写法。外层意思是“不存在某个零件，使得该工程没有使用它”。

3.9 求使用供应商 S1 所提供的全部零件的工程项目号。

SELECT j.JNumber
FROM dbo.Jobs AS j
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.SPJ AS s1parts
    WHERE s1parts.SNumber = 'S1'
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.SPJ AS spj
          WHERE spj.JNumber = j.JNumber
            AND spj.PNumber = s1parts.PNumber
      )
);

讲解：这里“全部零件”的范围不是 Parts 表的所有零件，而是供应商 S1 曾经供应过的零件集合。

3.10 求由一个以上供应者提供的零件号。

SELECT PNumber
FROM dbo.SPJ
GROUP BY PNumber
HAVING COUNT(DISTINCT SNumber) > 1;

3.11 将工程名为“环保”的所有供应数量提高 10%。

UPDATE spj
SET Quantities = CAST(ROUND(Quantities * 1.10, 0) AS int)
FROM dbo.SPJ AS spj
JOIN dbo.Jobs AS j
  ON j.JNumber = spj.JNumber
WHERE j.JName = N'环保';

讲解：SQL Server 的多表更新常用 UPDATE 别名 SET ... FROM ... JOIN ...。如果 Quantities 允许小数，可去掉 ROUND 和 CAST。

3.12 建立一个视图，包括每个工程项目使用零件的总数。

CREATE VIEW dbo.ProjectPartTotals
AS
SELECT JNumber,
       SUM(Quantities) AS TotalQuantities
FROM dbo.SPJ
GROUP BY JNumber;

讲解：视图中可以包含聚合查询。SQL Server 创建普通视图时不要在视图定义中写裸 ORDER BY，排序应放在查询视图时完成。

4. 设关系模式 `R(A, B, C, D)`，`F={AB -> CD, A -> D}`。说明为什么 R 不是 2NF，并分解成 2NF 模式集。

答案：

候选码：AB。
因为 AB -> CD，所以 AB+ = ABCD；A+ = AD，B+ = B，因此只有 AB 能推出全部属性。
非主属性 D 部分依赖于候选码的一部分 A，即 A -> D，所以 R 不满足 2NF。
分解为：R1(A, D) 与 R2(A, B, C)。

讲解：2NF 的核心是“非主属性不能部分依赖于候选码”。分解后，R1 保存 A -> D，R2 保存 AB -> C，消除了 D 对组合码 AB 的部分依赖。

5. 设关系模式 `R(A, B, C)`，`F={C -> B, B -> A}`。说明为什么 R 不是 3NF，并分解成 3NF 模式集。

答案：

候选码：C，因为 C -> B 且 B -> A，所以 C+ = ABC。
存在传递依赖：C -> B -> A。
B 不是超码，A 是非主属性，因此违反 3NF。
分解为：R1(C, B) 与 R2(B, A)。

讲解：3NF 允许依赖 X -> A 的情况是：X 是超码，或 A 是主属性。这里 B -> A 中 B 不是超码，A 不是主属性，所以不满足 3NF。

6. 设关系模式 `R(S#, C#, TNAME)`，属性为学生学号、课程号、任课教师姓名。每个学生可选多门课，每门课只有一个教师任教。

要求：写出基本 FD 和关键码；说明为什么不是 2NF；举例说明冗余；分解成 2NF。

答案：

基本函数依赖：C# -> TNAME，(S#, C#) -> TNAME 可由前者推出。
候选码：(S#, C#)。
不满足 2NF：TNAME 是非主属性，却依赖于组合码的一部分 C#。
冗余示例：很多学生选同一门课时，该课程教师姓名会重复存储多次。
2NF 分解：SC(S#, C#) 与 CT(C#, TNAME)。

讲解：选课事实和课程任课教师事实是两类信息，混在一个关系里会导致教师名重复。分解后，一个学生选一门课记录在 SC，一门课对应一个教师记录在 CT。

7. 设关系模式 `R(S#, TNAME, TADDR)`，属性分别表示学生学号、指导教师姓名、指导教师地址。每个学生只有一个指导老师；每个教师只有一个联系地址；教师不允许同名同姓。

答案：

基本函数依赖：S# -> TNAME，TNAME -> TADDR。
候选码：S#。
不满足 3NF：存在传递依赖 S# -> TNAME -> TADDR，且 TNAME 不是超码，TADDR 是非主属性。
冗余示例：同一导师指导多个学生时，导师地址重复出现多次。
3NF 分解：ST(S#, TNAME) 与 TA(TNAME, TADDR)。

讲解：单属性候选码时不会有部分依赖，所以它至少满足 2NF；问题在于传递依赖，因此要分解到 3NF。

8. 设关系模式 `R(A, B, C, D)`，函数依赖集 `F={A -> B, A -> C}`。写出 R 的关键码，并说明理由。

答案：候选码是 AD。

讲解：A 能推出 B 和 C，但不能推出 D；D 不出现在任何函数依赖右部，所以任何候选码都必须包含 D。AD+ = ABCD，且 A+ 和 D+ 都不能推出全部属性，所以 AD 是候选码。

9. 设某商业集团数据库中有关系模式 `R(商店编码, 顾客编码, 消费总额, 顾客单位, 地址, 电话)`。

规定：

每个顾客在每个商店只有一个消费总额。
每个顾客只属于一个单位。
每个顾客单位只有一个地址、一个电话。

答案：

基本函数依赖：
- (商店编码, 顾客编码) -> 消费总额
- 顾客编码 -> 顾客单位
- 顾客单位 -> 地址, 电话
候选码：(商店编码, 顾客编码)。
不满足 2NF：顾客单位 部分依赖于组合码的一部分 顾客编码，并且地址、电话也间接依赖于顾客编码。
2NF 分解：
- R1(商店编码, 顾客编码, 消费总额)
- R2(顾客编码, 顾客单位, 地址, 电话)
3NF 分解：
- R1(商店编码, 顾客编码, 消费总额)
- R2(顾客编码, 顾客单位)
- R3(顾客单位, 地址, 电话)

讲解：2NF 先消除组合码上的部分依赖，3NF 继续消除 顾客编码 -> 顾客单位 -> 地址, 电话 这种传递依赖。

10. 设计一个记录各个球队队员每场比赛进球数的关系模式：`R(队员编号, 比赛场次, 进球数, 球队名, 队长名)`。每个队员只能属于一个球队，每个球队只有一个队长。

答案：

基本函数依赖：
- (队员编号, 比赛场次) -> 进球数
- 队员编号 -> 球队名
- 球队名 -> 队长名
候选码：(队员编号, 比赛场次)。
不满足 2NF：球队名 部分依赖于组合码中的 队员编号。
2NF 分解：
- R1(队员编号, 比赛场次, 进球数)
- R2(队员编号, 球队名, 队长名)
3NF 分解：
- R1(队员编号, 比赛场次, 进球数)
- R2(队员编号, 球队名)
- R3(球队名, 队长名)

讲解：比赛进球数是“队员-比赛”的事实；队员所属球队是“队员”的事实；球队队长是“球队”的事实。分开存储后冗余更少，更新异常也更少。

11. 设有关系模式 `R(职工名, 项目名, 工资, 部门名, 部门经理)`。每个职工可参加多个项目，各领一份工资；每个项目只属于一个部门；每个部门只有一个经理。

答案：

基本函数依赖：
- 职工名 -> 工资
- 项目名 -> 部门名
- 部门名 -> 部门经理
候选码：(职工名, 项目名)，用来标识某职工参加某项目这一事实。
不满足 2NF：工资 部分依赖于 职工名，部门名 部分依赖于 项目名。
2NF 分解：
- R1(职工名, 项目名)
- R2(职工名, 工资)
- R3(项目名, 部门名, 部门经理)
3NF 分解：
- R1(职工名, 项目名)
- R2(职工名, 工资)
- R3(项目名, 部门名)
- R4(部门名, 部门经理)

讲解：题中“各领一份工资”按每个职工有一份工资理解。若某课程老师要求“工资随项目不同而不同”，则应把 (职工名, 项目名) -> 工资 写入依赖，并相应调整分解；但按题面常规语义，工资是职工属性。