Day 11 - 数据库70题：综合题 48-61

参考重点：教材第 7 章数据库设计，第 5 章完整性，第 11-12 章事务、恢复、并发控制。原文件缺综合题 53。

48. 某大学实行学分制，学生可根据自己情况选修课程。每名学生可同时选修多门课程，每门课程可由多位教师讲授；每位教师可讲授多门课程。不完整 E-R 图含学生、课程、教师三个实体。

要求：

指出学生与课程的联系类型，完善 E-R 图。
指出课程与教师的联系类型，完善 E-R 图。
若每名学生有一位教师指导，每一位教师指导多名学生，则学生与教师是何联系？
在原 E-R 图上补画教师与学生的联系。

答案：

学生与课程是多对多联系。一个学生可选多门课，一门课可被多名学生选。
课程与教师是多对多联系。一门课可由多位教师讲授，一位教师可讲多门课。
学生与教师是多对一联系，从教师到学生看是一对多：一名学生只有一位指导教师，一位教师可指导多名学生。
完整 E-R 可表示为：

erDiagram
    学生 }o--o{ 课程 : 选修
    教师 }o--o{ 课程 : 讲授
    教师 ||--o{ 学生 : 指导

讲解：E-R 联系类型判断只看业务约束。选修 和 讲授 都是 M:N，转换为关系模式时一般需要单独的联系表；指导 是 1:N，可在学生表中加入指导教师外键。

49. 将图 5.6 的 E-R 图转换为关系模式，菱形框中的属性自己确定。

题面图示：实体“单位”含属性单位号、地址、电话；实体“职工”含属性职工号、姓名、性别、年龄、单位号；单位与职工之间有联系 D-3。

答案：

按常见语义，一个单位有多个职工，一个职工属于一个单位，是 1:N 联系。可转换为：

单位(单位号, 地址, 电话)，主键：单位号
职工(职工号, 姓名, 性别, 年龄, 单位号)，主键：职工号，外键：单位号 引用 单位(单位号)

如果菱形联系 D-3 有自定属性，例如 入职日期，则该属性可并入 N 端的 职工 关系：

职工(职工号, 姓名, 性别, 年龄, 单位号, 入职日期)

讲解：1:N 联系转换为关系模式时，一般把 1 端主键加入 N 端作为外键；只有 M:N 联系才必须单独生成联系表。

50. 设有商业销售记账数据库。一个顾客（顾客姓名，单位，电话号码）可以买多种商品，一种商品（商品名称，型号，单价）供应多个顾客。试画出对应的 E-R 图。

答案：

顾客和商品是多对多联系，联系可命名为“购买”或“销售”，联系属性可按记账需要加入数量、日期、金额等。

erDiagram
    顾客 }o--o{ 商品 : 购买
    顾客 {
        string 顾客姓名
        string 单位
        string 电话号码
    }
    商品 {
        string 商品名称
        string 型号
        decimal 单价
    }

可转换为关系模式：

顾客(顾客姓名, 单位, 电话号码)，主键可暂取 顾客姓名，实际系统建议增加 顾客编号
商品(商品名称, 型号, 单价)，主键可取 (商品名称, 型号)，实际系统建议增加 商品编号
购买(顾客姓名, 商品名称, 型号, 数量, 购买日期)，主键可取 (顾客姓名, 商品名称, 型号, 购买日期)

讲解：题面没有给出顾客编号和商品编号，考试可直接用自然属性作码；真实数据库设计中更建议使用稳定的代理键。

51. 某医院病房计算机管理中需要如下信息，并完成 E-R 图、关系模型和候选码。

题面信息：

科室：科名，科地址，科电话，医生姓名
病房：病房号，床位号，所属科室名
医生：姓名，职称，所属科室名，年龄，工作证号
病人：病历号，姓名，诊断，主管医生，病房号

约束：一个科室有多个病房、多个医生；一个病房只能属于一个科室；一个医生只属于一个科室，但可负责多个病人的诊治；一个病人的主管医生只有一个。

51.1 E-R 图

答案：

erDiagram
    科室 ||--o{ 病房 : 拥有
    科室 ||--o{ 医生 : 隶属
    医生 ||--o{ 病人 : 主管
    病房 ||--o{ 病人 : 收治

    科室 {
        string 科名
        string 科地址
        string 科电话
    }
    病房 {
        string 病房号
        string 床位号
    }
    医生 {
        string 工作证号
        string 姓名
        string 职称
        int 年龄
    }
    病人 {
        string 病历号
        string 姓名
        string 诊断
    }

51.2 转换为关系模型结构

答案：

科室(科名, 科地址, 科电话)。
医生(工作证号, 姓名, 职称, 年龄, 科名)，科名 为外键。
病房(病房号, 科名)，科名 为外键。
床位(病房号, 床位号)，病房号 为外键。若题目把“病房号+床位号”整体看作病房实体，也可写成 病房床位(病房号, 床位号, 科名)。
病人(病历号, 姓名, 诊断, 主管医生工作证号, 病房号)，主管医生和病房号为外键。

51.3 每个关系模式的候选码

答案：

科室：科名
医生：工作证号
病房：病房号
床位：(病房号, 床位号)
病人：病历号

讲解：原题“科室”属性中列了“医生姓名”，但因为一个科室有多个医生，医生不应作为科室表中的多值属性，应拆成医生实体并用外键表示所属科室。

52. 叙述数据库实现完整性检查的方法。

答案：

DBMS 实现完整性检查一般包括：

提供完整性约束定义机制，如 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL、触发器。
在执行插入、删除、修改时自动检查约束。
发现违约时执行违约处理，如拒绝操作、级联更新、级联删除、置空、置默认值。

SQL Server 示例：

CREATE TABLE dbo.Enrollment (
    Sno varchar(20) NOT NULL,
    Cno varchar(20) NOT NULL,
    Grade decimal(5, 2) NULL,
    CONSTRAINT PK_Enrollment PRIMARY KEY (Sno, Cno),
    CONSTRAINT CK_Enrollment_Grade CHECK (Grade BETWEEN 0 AND 100)
);

讲解：完整性检查应尽量靠 DBMS 统一执行，而不是分散在多个应用程序中。