软考 · D3 技巧图解 · 数据库

1. 1
   

   数据库的下午试题二结构很稳定：26 套里，补 E-R 图的联系和类型、补关系模式缺失的属性，每套都考；指出主键和外键考了 22 套。这些全靠今天这几条转换规则。

2. 2
   

   先看一对多。院系和学生是 1 对 n：不用单独建表，把它并入 n 端，也就是学生表，再加上 1 端的主键院系号作外键。

3. 3
   

   多对多必须独立成表。学生和课程是 m 对 n 的选课联系：新表里放两端的码，加上联系自己的属性成绩，主键是学号和课程号的组合，两个码各自又是外键。

4. 4
   

   一对一最灵活：可以并入任意一端，加上对方的主键作外键，比如在学校表里加校长号。

5. 5
   

   三个实体之间 m:n:p 的联系，也必须独立成表，主键由三个实体的码组成，比如供应表的供应商号、项目号、零件号。

6. 6
   

   最后一个坑：同一个读者会多次借同一本书，只用读者号加书号就区分不开每一次，主键还要再加上借阅日期。

一句话一对一并入任意一端；一对多并入多的一端，加一端主键作外键；多对多和三元联系必须独立成表，主键是各端码的组合；同一对实体多次发生，主键再加时间。这些就是下午试题二补联系、补属性、指主外键的全部依据。

1. 1
   

   关系代数先认四个运算。这里有两张小表：R 有 A、B、C 三列，S 有 B、D 两列。

2. 2
   

   σ 是选择，选行：σC>5(R) 只留下 C 大于 5 的两行。

3. 3
   

   π 是投影，选列：πA,C(R) 只留下 A 和 C 两列。

4. 4
   

   × 是笛卡尔积：每一行和每一行配对，列数相加，三列加两列是五列；行数相乘，三行乘两行是六行。

5. 5
   

   ⋈ 是自然连接：按同名属性 B 做等值连接，B 相等的行才拼起来，再去掉重复的 B 列，结果是四列。

6. 6
   

   考题里的下标常常是数字，它是列序号，不是值：R 有 A、B、C、D 四列，π1,3(R) 就是取第 1 列和第 3 列，等价于 πA,C(R)。

7. 7
   

   做笛卡尔积时，S 的列接在 R 的后面：R 有五列、S 有四列，R×S 一共九列，S 的 B 是第 6 列。

8. 8
   

   自然连接要去掉重复的同名列：R 和 S 有 B、C 两个同名属性，所以 R⋈S 是五加四减二，等于七列。

9. 9
   

   真题还爱把关系代数翻译成 SQL。看这个式子：π1,4(σ3>5(R⋈S))，先把 R⋈S 的列号排出来：1 是 A，2 是 B，3 是 C，4 是 D。

10. 10
    

    投影的列号翻成列名，写进 SELECT：SELECT A, D。

    连接翻成 FROM，列出两张表：FROM R, S。

    自然连接的条件是同名属性相等，写进 WHERE：R.B = S.B，同名属性一定要加表名。

11. 11
    

    选择条件 C > 5 也放进 WHERE，用 AND 连起来，不能用 OR。

    最后记一条查询优化的原则：尽可能早地执行选择运算，先在单表上筛掉行，再做连接。

一句话σ 选行，π 选列，下标数字是列序号；笛卡尔积列数相加、行数相乘；自然连接按同名属性等值连接再去掉重复列；转 SQL 时投影进 SELECT，表进 FROM，条件全进 WHERE 用 AND；优化先做选择。

1. 1
   

   这一段的任务：在关系 R 里找出候选键。R 里住着五个属性：A、B、C、D、E，它们之间有三条函数依赖。

   X→Y 的意思是：知道了 X，就能唯一确定 Y，比如学号→姓名。候选键，就是能确定全部属性、而且最小的属性组。

2. 2
   

   怎么检验一组属性？算它的闭包：从它们出发，按依赖把能确定的属性一个个拉进队伍。先只派 A 出发。

3. 3
   

   属性 BA 在队伍里，按 A→B，我也能确定，我来了！

   属性 CB 到了，按 B→C，我也来。

4. 4
   

   属性 ECD→E 要 C 和 D 都在。D 不在，我动不了。

5. 5
   

   所以 A 的闭包是 {A,B,C}，没拉到 D 和 E，A 单独不是候选键。

6. 6
   

   一个个去试太慢。考场上有个快办法：看每个属性出现在箭头的哪一边。

7. 7
   

   A、D 只出现在箭头左边；E 只出现在右边；B、C 两边都有；这次没有两边都不出现的。

   只在左边的：没有谁能确定它，它必在候选键里。

8. 8
   

   只在右边的：一定会被别人确定，必不在。两边都不出现的：谁也确定不了它，也必在。两边都有的，先待定。

9. 9
   

   那就让必在的 A 和 D 组队出发！

   A 拉来 B，B 拉来 C；C 和 D 都在，CD→E 成立，E 也来了！

10. 10
    

    五个属性全部到齐！AD 的闭包等于全部属性，所以 AD 是唯一的候选键。

11. 11
    

    再看一个真题里的坑。换一个 R：只有 A、B、C，F 是 AB→C，C→B。

    分类：A 只在左边，必在；B 和 C 两边都有，待定。可 A 的闭包只有它自己，不够。那就从待定的属性里，一次加一个试试。

12. 12
    

    A 加 B：按 AB→C，C 也来了，三个到齐。AB 是候选键！

13. 13
    

    A 加 C：按 C→B，B 也来了，也到齐了。AC 也是候选键！

14. 14
    

    所以候选键可以不止一个：AB 和 AC。A、B、C 都出现在某个候选键里，都是主属性，非主属性一个也没有。

    这正是 2011 年上半年、2016 年上半年都考过的原题。

15. 15
    

    函数依赖还有两个概念常考。传递依赖：X 决定 Y，Y 决定 Z，而 Y 不能决定 X，那么 X 决定 Z 就是传递依赖。冗余依赖：F 里能由其余依赖推出来的那一条。

    比如 F 里有 A1→A2、A2→A4，还直接写了 A1→A4。由前两条按传递律就能推出 A1→A4，所以这一条是冗余的。

一句话只在左边、或者两边都不出现的属性必在候选键里，只在右边的必不在；先算必在属性的闭包，等于全集就是唯一候选键；不够就逐个加入两边都有的；出现在任一候选键里的是主属性；能推出来的依赖是冗余的；公理只有三条。

1. 1
   

   范式一级比一级严。1NF：属性不可再分。2NF：消除非主属性对码的部分依赖。3NF：再消除非主属性对码的传递依赖。BCNF：每个决定因素都包含码。

2. 2
   

   判断最高范式，第一步永远是求码。看这个元件关系，有三条函数依赖。

   元件号和供应商只在左边出现，必在码里，它们的闭包等于全部属性，所以码是元件号加供应商。

3. 3
   

   第二步看部分依赖：元件号单独就能决定元件名称，元件名称依赖的只是码的一部分，这就是部分依赖，所以它连 2NF 都不满足，最高只到 1NF。

4. 4
   

   只到 1NF 会带来一串问题：数据冗余、修改不一致、插入异常和删除异常。

5. 5
   

   解决办法是分解：把部分依赖和传递依赖涉及的属性另立成表，再保留由原来的码构成的那张表。分成元件 1、元件 2、元件 3，每张都达到 3NF。

6. 6
   

   再看传递依赖。学生关系里，学号决定系名，系名决定系主任。

   码只有学号一个属性，单属性的码不可能有部分依赖，所以至少是 2NF。

7. 7
   

   可是学号决定系名、系名又决定系主任，系主任传递依赖于码，所以只到 2NF。

   把系名和系主任拆出去单独成表，两张表就都到 3NF 了。

8. 8
   

   最后看 BCNF。回到上一段的 R(A,B,C)，F 是 AB→C 和 C→B，候选键是 AB 和 AC，三个属性都是主属性，没有非主属性，所以满足 3NF。

   但 C→B 的决定因素 C 不包含码，不满足 BCNF。所以它最高只到 3NF。

9. 9
   

   分解还有两个检验。第一个是无损连接：分成两个子模式时，两者的交集能决定任意一边独有的属性，就是无损的。

   比如学生和系两张表，交集是系名，系名能决定系主任，所以这个分解无损。

10. 10
    

    第二个是保持函数依赖：原来的每条依赖，都能在某个子模式里找到，或者由各子模式的依赖推出来。这两个检验互相独立，满足一个不代表满足另一个。

一句话1NF 属性不可再分，2NF 消除部分依赖，3NF 消除传递依赖，BCNF 每个决定因素都包含码；判范式先求码；单属性码至少 2NF；无损连接看交集能否决定一边；保持依赖看每条依赖是否留在子模式里。

1. 1
   

   SQL 先记执行顺序，它和书写顺序不一样。看这条查询：按部门统计平均工资，只看工资 3000 以上的人，只要人数超过 2 的部门。

2. 2
   

   第一步 FROM，取出员工表。

   第二步 WHERE，按行过滤，只留工资大于 3000 的人。WHERE 里不能用聚集函数。

3. 3
   

   第三步 GROUP BY，按部门分组。题目说“各部门”“每个”，就要用 GROUP BY。

   第四步 HAVING，对分好的组再过滤，比如人数大于 2 的部门。

4. 4
   

   第五步 SELECT，每组算出一行，别名写成“表达式 AS 别名”。

   最后 ORDER BY 排序。

一句话执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY；WHERE 过滤行、不能用聚集函数，HAVING 过滤组。