数据库设计（ER图）实战指南：从概念模型到SQL查询 - 指南

数据库设计是信息系统开发的基石。其核心流程是：通过ER图进行概念设计，然后转换为逻辑模型（关系模式），最后通过SQL实现物理模型。

第一阶段：ER图概念设计

ER（Entity-Relationship）图用于描述现实世界的信息结构，它独立于任何具体的数据库管理系统。

1. 识别实体

实体是客观存在并可相互区分的事物。在问题描述中，名词往往是实体的候选。

• 示例： 在“学生选课系统”中，很容易识别出 学生、课程 这两个核心实体。还可能包括 教师、学院 等。
• 表示： 在ER图中用矩形表示。

2. 识别实体的属性

属性是实体的特征或性质。

• 示例： 学生 实体可能有 学号、姓名、性别 等属性。课程 实体可能有 课程号、课程名、学分 等属性。
• 表示： 在ER图中用椭圆表示，并连接到对应的实体。
• 关键属性（主键）： 唯一标识一个实体的属性，如 学号。在属性名下加下划线。

3. 识别联系及其类型

联系是实体之间的相互关系。在问题描述中，动词往往是联系的候选。联系的类型是核心考点。

• 表示： 在ER图中用菱形表示。

• 联系类型（基数约束）：

  • 一对一（1:1）： 实体集A中的一个实体至多与实体集B中的一个实体相关联，反之亦然。
    • 例： 班级 与 班长。一个班级只有一个班长，一个班长只负责一个班级。
    • 判断口诀： “一对应一”。
  • 一对多（1:N）或多对一（N:1）： 实体集A中的一个实体可以与实体集B中的任意多个（零个或多个）实体相关联，而B中的一个实体至多与A中的一个实体相关联。
    • 例： 班级 与 学生。一个班级有多个学生，一个学生只属于一个班级。
    • 判断口诀： “一对应多，多对应一”。
  • 多对多（M:N）： 实体集A中的一个实体可以与实体集B中的任意多个实体相关联，反之亦然。
    • 例： 学生 与 课程。一个学生可以选择多门课程，一门课程可以被多个学生选择。联系“选课”本身还可能拥有属性，如 成绩。
    • 判断口诀： “多对应多”。

第二阶段：将ER图转换为关系模式

关系模式就是数据库中的“表结构”。转换有明确的规则。

1. 实体类型的转换

• 规则： 一个实体型转换为一个关系模式（一张表）。
• 属性： 实体的属性转换为表的列（字段）。
• 主键： 实体的主键转换为表的主键。
• 示例：
  • 学生(学号, 姓名, 性别, 年龄) -> Student(StuID, StuName, Gender, Age)，主键为 StuID。
  • 课程(课程号, 课程名, 学分) -> Course(CourseID, CourseName, Credits)，主键为 CourseID。

2. 联系类型的转换

• （1）1:1 联系的转换

  • 方法一（常用）： 将联系与任意一端实体对应的关系模式合并。需要将另一端的主键作为外键和联系本身的属性（如果有）加入该关系模式中。
    • 例： 班级(班号, 班级名) 和 班长(学号, 姓名) 是1:1联系。可以将班长的主键 学号 作为外键加入到 班级 表中。
    • 班级表：Class(ClassID, ClassName, MonitorID)，其中 MonitorID 是外键，引用 Student(StuID)。
  • 方法二： 将联系独立转换为一个关系模式，其属性包括两端实体的主键（作为复合主键）和联系自身的属性。

• （2）1:N 联系的转换

  • 规则： 将联系与 N端（多的一方） 实体对应的关系模式合并。需要将 1端 的主键作为外键和联系本身的属性（如果有）加入到 N端 的关系模式中。
    • 例： 班级(班号, ...) 和 学生(学号, ...) 是1:N联系。将班级的主键 班号 作为外键加入到 学生 表中。
    • 学生表：Student(StuID, StuName, ..., ClassID)，其中 ClassID 是外键，引用 Class(ClassID)。

• （3）M:N 联系的转换

  • 规则： 必须将联系独立转换为一个新的关系模式（连接表）。新关系模式的属性由两端实体的主键（作为复合主键）和联系自身的属性构成。
    • 例： 学生(学号, ...) 和 课程(课程号, ...) 是M:N联系“选课”。
    • 需要新建一个“选课”表：Enrollment(StuID, CourseID, Grade)。
    • 其中 (tuID, CourseID) 是复合主键，同时 StuID 是外键引用Student(StuID)，CourseID 是外键引用 Course(CourseID)。Grade（成绩）是联系自身的属性。

第三阶段：编写简单的SQL查询

基于转换后的关系模式，我们可以使用SQL进行数据操作。

假设我们有以下三个表（关系模式）：

• Student(StuID, StuName, Gender, Age, ClassID)
• Course(CourseID, CourseName, Credits)
• Enrollment(StuID, CourseID, Grade)

1. 单表查询

• 查询所有学生的信息：

  SELECT * FROM Student;

• 查询所有男生的姓名和年龄：

  SELECT StuName, Age FROM Student WHERE Gender = '男';

• 插入一名新学生：

  INSERT INTO Student (StuID, StuName, Gender, Age, ClassID)
  VALUES ('2024001', '张三', '男', 20, 'CS01');

2. 连接查询（多表查询）

• 查询所有学生的选课情况，显示学生姓名和课程名：

  -- 使用 WHERE 进行连接（旧语法）
  SELECT S.StuName, C.CourseName
  FROM Student S, Enrollment E, Course C
  WHERE S.StuID = E.StuID AND E.CourseID = C.CourseID;
  -- 使用 JOIN ON 进行连接（推荐，更清晰）
  SELECT S.StuName, C.CourseName
  FROM Student S
  JOIN Enrollment E ON S.StuID = E.StuID
  JOIN Course C ON E.CourseID = C.CourseID;

3. 聚合查询

• 查询每个学生的选课门数：

  SELECT S.StuID, S.StuName, COUNT(E.CourseID) AS '选课数量'
  FROM Student S
  LEFT JOIN Enrollment E ON S.StuID = E.StuID -- 使用LEFT JOIN以确保没选课的学生也能被统计到
  GROUP BY S.StuID, S.StuName;

• 查询平均成绩大于80分的学生学号和平均分：

  SELECT StuID, AVG(Grade) AS '平均成绩'
  FROM Enrollment
  GROUP BY StuID
  HAVING AVG(Grade) > 80; -- HAVING 用于对分组后的结果进行过滤

---

总结：核心流程与要点

步骤	核心任务	关键考点/规则
1. 识别实体与属性	找出系统中的核心名词	确定主键（唯一标识符）
2. 识别联系与类型	找出实体间的动词	1:1, 1:N, M:N 的判断口诀
3. ER图转关系模式	设计数据库表结构	黄金法则：1:1/1:N合并，M:N新建表
4. 编写SQL查询	实现数据检索	SELECT…JOIN…WHERE…GROUP BY…HAVING