08 大题答题模板

这一章是考场版。遇到类似题，照模板填表名、属性、条件即可。

一、SQL 大题模板

建表

CREATE TABLE 表名 (
    A char(10) NOT NULL,
    B varchar(20),
    C int CHECK (C BETWEEN 下限 AND 上限),
    D char(10) NULL,
    CONSTRAINT PK_表名 PRIMARY KEY (A),
    CONSTRAINT FK_表名_父表 FOREIGN KEY (D)
        REFERENCES 父表(D)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

视图

CREATE VIEW 视图名(列1, 列2, 统计列) AS
SELECT 分组列1, 分组列2, SUM(数值列)
FROM ...
JOIN ... ON ...
WHERE ...
GROUP BY 分组列1, 分组列2;

更新关联表

UPDATE p
SET p.wage = p.wage * 1.10
FROM Pe AS p
JOIN Dept AS d ON p.dptno = d.dptno
WHERE d.dname = N'设计室';

“没有”查询

SELECT *
FROM S AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM SP AS sp
    WHERE sp.Sno = s.Sno
      AND sp.Pno = 'P3'
);

“全部”查询

SELECT s.Sno
FROM Student AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM Course AS c
    WHERE NOT EXISTS (
        SELECT 1
        FROM SC AS sc
        WHERE sc.Sno = s.Sno
          AND sc.Cno = c.Cno
    )
);

二、存储过程模板

输入参数 + 条件判断

CREATE PROCEDURE 过程名
    @参数1 int,
    @参数2 int
AS
BEGIN
    IF @参数1 IS NULL
    BEGIN
        PRINT N'参数错误';
        RETURN;
    END;

    -- 核心 SQL
END;
GO

输出参数

CREATE PROCEDURE proc_get_name
    @id int,
    @name varchar(50) OUTPUT
AS
BEGIN
    SELECT @name = Name
    FROM T
    WHERE ID = @id;

    IF @id IS NULL OR @name IS NULL
        SET @name = N'无此记录';
END;
GO

库存判断

DECLARE @qty int;
SELECT @qty = QTY FROM BOOK WHERE BID = @bid;

IF @qty IS NULL
    PRINT N'无此图书';
ELSE IF @qty >= @s
BEGIN
    UPDATE BOOK SET QTY = QTY - @s WHERE BID = @bid;
    SELECT QTY FROM BOOK WHERE BID = @bid;
END
ELSE
    PRINT N'库存不足';

三、游标模板

DECLARE Cur CURSOR
FOR SELECT 列1, 列2 FROM 表 WHERE 条件;

OPEN Cur;

DECLARE @v1 数据类型, @v2 数据类型;

FETCH NEXT FROM Cur INTO @v1, @v2;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT 标签1 = @v1, 标签2 = @v2;
    FETCH NEXT FROM Cur INTO @v1, @v2;
END;

CLOSE Cur;
DEALLOCATE Cur;

四、触发器模板

营业时间限制

CREATE TRIGGER trg_table_time
ON 表名
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @hour int, @weekday int;
    SELECT @hour = DATEPART(hh, GETDATE());
    SELECT @weekday = DATEPART(weekday, GETDATE());

    IF NOT (@hour BETWEEN 9 AND 17 AND @weekday IN (2,3,4,5,6))
    BEGIN
        PRINT N'Not business hours!';
        ROLLBACK;
    END;
END;
GO

用 inserted/deleted

CREATE TRIGGER trg_check_update
ON T
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM inserted
        WHERE 某列 < 0
    )
    BEGIN
        PRINT N'违反约束';
        ROLLBACK;
    END;
END;
GO

五、嵌入式 SQL 填空模板

EXEC SQL INCLUDE SQLCA;

EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE Sdept = :deptname;

EXEC SQL OPEN SX;

EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;

EXEC SQL UPDATE Student
SET Sage = :NEWAGE
WHERE CURRENT OF SX;

填空记忆：

• 定义 SQL 通信区：EXEC SQL INCLUDE SQLCA;
• 定义游标：EXEC SQL DECLARE SX CURSOR FOR
• 打开游标：EXEC SQL OPEN SX;
• 推进游标：EXEC SQL FETCH SX INTO :...;
• 当前游标行更新：EXEC SQL UPDATE 表名 SET ... WHERE CURRENT OF SX;

六、E-R 设计模板

答题顺序：

1. 实体：
   E1(...), E2(...), E3(...)

2. 联系和联系类型：
   E1 1 --- n E2
   E2 m --- n E3 [联系属性]

3. 关系模式：
   R1(属性...), PK: ..., FK: ...
   R2(属性...), PK: ..., FK: ...

4. 范式说明：
   各属性原子，满足 1NF；
   非主属性完全依赖主码，满足 2NF；
   无非主属性传递依赖主码，满足 3NF；
   若每个非平凡函数依赖左部都是候选码，则满足 BCNF。

七、规范化模板

U = {所有属性}
F = {
  A -> B,
  C -> D,
  (A, C) -> E
}

候选码：
先求 (A,C)+ = ...
因为 (A,C)+ = U，且 A+、C+ 均不能推出 U，
所以候选码为 (A,C)。

范式判断：
R 满足 1NF。
由于存在 C -> D，D 为非主属性，C 是候选码 (A,C) 的真子集，
所以存在部分依赖，不满足 2NF。

分解：
R1(C, D), PK(C)
R2(A, C, E, ...), PK(A,C)

若是传递依赖：

A -> B, B -> C，因此 A -> C 为传递依赖。
分解为 R1(A,B), R2(B,C)。

八、查询优化模板

原始表达式：
π_{输出列}(σ_{连接条件 ∧ 单表条件1 ∧ 单表条件2}(R × S))

原始树：
π 输出列
└── σ 全部条件
    └── ×
        ├── R
        └── S

优化规则：
1. 分解选择条件。
2. 单表选择下推。
3. 笛卡尔积 + 连接条件改为连接。
4. 投影下推，只保留输出属性和连接属性。

优化树：
π 输出列
└── ⋈ 连接条件
    ├── π R需要列
    │   └── σ R条件
    │       └── R
    └── π S需要列
        └── σ S条件
            └── S

优化表达式：
π_{输出列}(π(...σ...(R)) ⋈_{条件} π(...σ...(S)))

九、检查点恢复模板

检查点时正在执行的事务：
ACTIVE-LIST = {...}

从检查点向故障点扫描日志：
遇到 START Ti：Ti 加入 UNDO-LIST。
遇到 COMMIT Ti：Ti 从 UNDO-LIST 移出，加入 REDO-LIST。

故障时：
UNDO-LIST = {...}  这些事务未提交，要撤销。
REDO-LIST = {...}  这些事务已提交，要重做。

判断口诀：

• 故障前已提交：REDO。
• 故障时未提交：UNDO。
• 检查点前已提交：通常不处理。

十、两阶段封锁模板

T1:
SLock(读对象);
XLock(写对象);
read(...);
write(...);
Unlock(...);
Unlock(...);

T2:
SLock(读对象);
XLock(写对象);
read(...);
write(...);
Unlock(...);
Unlock(...);

说明：

上述调度中，每个事务在释放任何锁之前已经申请完所需锁，
释放锁之后不再申请新锁，因此满足两阶段封锁协议。
但如果两个事务按相反顺序申请锁，可能互相等待，因此可能死锁。

十一、简答题万能结构

视图优点

视图能够简化查询，使用户以不同角度观察数据；
能够提供一定逻辑独立性；
能够通过只授权访问视图来保护数据安全；
也有利于屏蔽复杂连接和敏感字段。

DBMS 功能

DBMS 提供数据定义、数据组织存储和管理、数据操纵、
数据库运行管理、数据库建立和维护、通信接口等功能。
其中运行管理包括安全性、完整性、并发控制和恢复。

完整性与安全性区别

安全性是防止非法用户非法访问数据库；
完整性是防止合法用户对数据库加入不符合语义的数据。
安全性通过用户标识、授权、视图、审计等实现；
完整性通过主码、外码、CHECK、触发器等实现。

恢复基本原理

数据库恢复利用冗余数据把数据库从错误状态恢复到一致状态。
冗余数据主要包括后援副本和日志文件。
事务故障通常 UNDO，系统故障对未提交事务 UNDO、对已提交但可能未落盘事务 REDO，
介质故障先装入后援副本再利用日志 REDO。