04 数据库设计、E-R 图与规范化

数据库设计题通常 15-20 分，是最能拉开差距的大题。核心不是画得漂亮，而是语义识别准确、联系类型正确、关系模式主外码明确、范式理由能自圆其说。

一、数据库设计步骤

教材常见步骤：

1. 需求分析：调查用户需求，形成数据字典、数据流图、需求说明。
2. 概念结构设计：用 E-R 图描述实体、属性、联系，独立于具体 DBMS。
3. 逻辑结构设计：把 E-R 图转换成关系模式，确定主码外码，并进行规范化。
4. 物理结构设计：选择存储结构、索引、存取路径、分区等。
5. 数据库实施：建库、建表、装载数据、编写应用。
6. 运行和维护：监控、调优、备份恢复、安全管理。

题目问“设计关系模式属于哪一阶段”，答：逻辑结构设计。

二、E-R 图怎么画

找实体

实体通常是名词，能独立存在并需要保存信息。例如学生、课程、教师、学院、图书、读者、供应商、零件、工程。

不要把所有名词都当实体。若只是某实体的特征，如姓名、年龄、地址、电话，通常是属性。

找联系

联系通常来自动词或业务规则：

• 学生主修专业：学生 n:1 专业。
• 学生选修课程：学生 m:n 课程，联系属性是成绩。
• 教师讲授课程：教师 m:n 课程，联系属性是教室、课时。
• 读者借阅图书：读者 m:n 图书，联系属性是借阅日期、还书日期。

判断联系类型

题目中的“一”“多”就是线索：

• “每个学生主修一个专业，一个专业可招收多名学生”：学生 n:1 专业。
• “每个学院可设置多个专业，每个专业属于一个学院”：学院 1:n 专业。
• “每个学生可选多门课，每门课可被多名学生选修”：学生 m:n 课程。

联系属性

若某属性不属于单个实体，而属于一次联系，就放在联系上。

例：

• 选课成绩属于“学生选修课程”这次联系，不属于学生，也不属于课程。
• 授课教室、课时属于“教师讲授课程”这次联系。
• 借阅日期、还书日期属于“读者借阅图书”这次联系。

三、E-R 转关系模式规则

实体转表

每个实体型转换为一个关系模式，实体的属性成为表的属性，实体码成为主码。

Student(SID, SName, MajorID)
PK: SID

1:1 联系

可把任一方主码加入另一方作外码，也可单独建联系表。若联系有属性，通常单独建表更清晰。

1:n 联系

把 1 方主码加入 n 方作为外码。若联系有属性，也一并放入 n 方，除非联系本身需要独立记录。

College(CollegeID, CollegeName)
Major(MajorID, MajorName, CollegeID)
FK Major.CollegeID -> College

m:n 联系

必须单独转换为联系表，主码通常是双方主码组合，联系属性也放入该表。

SC(SID, CID, Grade)
PK: (SID, CID)
FK: SID -> Student, CID -> Course

多元联系

如供应商-零件-工程三元供应关系，单独建联系表，主码通常是各实体主码组合。

SPJ(Sno, Pno, Jno, Qty)
PK: (Sno, Pno, Jno)

四、函数依赖与码

函数依赖 X -> Y 的含义：在关系模式 R 的任一可能关系中，若两个元组 X 值相等，则 Y 值也相等。

候选码求法：

1. 先找从未出现在任何依赖右部的属性，这些属性必须在候选码中。
2. 求闭包，看是否能推出全部属性。
3. 若不能，补充属性继续求闭包。
4. 删除多余属性，得到最小属性组。

闭包例：

U = {A,B,C,D,E}
F = {AB -> C, B -> D, C -> E, EC -> B, AC -> B}

(AC)+:
初始 {A,C}
C -> E，得 {A,C,E}
EC -> B，得 {A,B,C,E}
B -> D，得 {A,B,C,D,E}
所以 AC 是超码。若 A+ 或 C+ 不能推出全部，则 AC 是候选码。

五、范式判断

1NF

每个属性都是不可再分的数据项。关系数据库中的规范关系至少应满足 1NF。

2NF

在 1NF 基础上，每个非主属性完全函数依赖于候选码，不能部分依赖候选码的一部分。

只有候选码是组合码时，才需要特别检查部分依赖。

例：

R(Sno, Cno, Grade, Cname)
候选码：(Sno, Cno)
(Sno, Cno) -> Grade
Cno -> Cname

Cname 只依赖组合码的一部分 Cno，存在部分依赖，不满足 2NF。

3NF

在 2NF 基础上，非主属性不能传递依赖于候选码。

例：

Student(Sno, Sname, DeptNo, DeptName)
Sno -> DeptNo
DeptNo -> DeptName
所以 Sno -> DeptName 是传递依赖

应拆为：

Student(Sno, Sname, DeptNo)
Dept(DeptNo, DeptName)

BCNF

对每个非平凡函数依赖 X -> Y，X 都必须是超码。BCNF 比 3NF 更严格。

全码关系模式通常可达到 BCNF，因为所有属性都是主属性，常见异常较少；但严格判断仍应看所有函数依赖左部是否为超码。

六、操作异常

不合理关系模式常见异常：

• 插入异常：应该插入的数据因缺少主码一部分而插不进去。
• 删除异常：删除某条记录时，把不该删除的信息也删掉。
• 更新异常：同一事实重复存储，修改时必须多处更新，否则不一致。
• 数据冗余：相同信息反复存储。

例：R(读者编号, 姓名, 单位编号, 单位名称, 图书号, 书名, 借阅日期, 还书日期)。

若某单位只有一个读者，且删除该读者最后一条借阅记录，则单位名称也随记录消失，这就是删除异常。

七、3NF 分解通用流程

1. 写全集 U。
2. 写基本函数依赖集 F。
3. 求候选码。
4. 判断最高范式。
5. 按依赖拆表。
6. 每张表标主码、外码。
7. 说明分解后消除了什么依赖和异常。

八、典型设计题

高校教学管理系统

业务：

• 学院 1:n 专业。
• 学院 1:n 课程。
• 学院 1:n 教师。
• 专业 1:n 学生。
• 学生 m:n 课程，联系属性：成绩。
• 教师 m:n 课程，联系属性：教室、课时。

关系模式：

College(CollegeID, CollegeName)
Major(MajorID, MajorName, CollegeID)
Student(SID, SName, MajorID)
Course(CID, CName, CollegeID)
Teacher(TID, TName, CollegeID)
SC(SID, CID, Grade)
TC(TID, CID, Classroom, Hours)

主外码：

College: PK(CollegeID)
Major: PK(MajorID), FK(CollegeID) -> College
Student: PK(SID), FK(MajorID) -> Major
Course: PK(CID), FK(CollegeID) -> College
Teacher: PK(TID), FK(CollegeID) -> College
SC: PK(SID, CID), FK(SID) -> Student, FK(CID) -> Course
TC: PK(TID, CID), FK(TID) -> Teacher, FK(CID) -> Course

范式说明：各表属性原子；非主属性完全依赖主码；无非主属性对主码的传递依赖，一般可说明达到 3NF。若进一步假定编号唯一决定其余属性，且无其他非平凡依赖，则达到 BCNF。

读者借阅图书

原关系：

R(读者编号, 姓名, 单位编号, 单位名称, 图书号, 书名, 借阅日期, 还书日期)

语义：

• 每个读者属于一个单位。
• 每个读者可借多本书，每本书可被多名读者借。
• 每个读者可对同一本书多次借阅。
• 每个读者每天每本书最多借一次。

函数依赖：

读者编号 -> 姓名, 单位编号
单位编号 -> 单位名称
图书号 -> 书名
(读者编号, 图书号, 借阅日期) -> 还书日期

候选码：

(读者编号, 图书号, 借阅日期)

3NF 分解：

Reader(读者编号, 姓名, 单位编号)
Unit(单位编号, 单位名称)
Book(图书号, 书名)
Borrow(读者编号, 图书号, 借阅日期, 还书日期)

主外码：

Reader: PK(读者编号), FK(单位编号) -> Unit
Unit: PK(单位编号)
Book: PK(图书号)
Borrow: PK(读者编号, 图书号, 借阅日期),
        FK(读者编号) -> Reader,
        FK(图书号) -> Book

商品-销售数据库

实体：

Employee(员工)
SalesGroup(销售组)
Product(商品)
Supplier(供应者)
SaleRecord(销售记录)

语义：

• 员工只在一个销售组。
• 销售组可销售多种商品。
• 一种商品只能由一个组销售。
• 一种商品每天有一个销售量。
• 供应者和商品是多对多。

关系：

SalesGroup(GroupID, GroupName, Manager)
Employee(EmpID, EmpName, Wage, GroupID)
Product(PID, PName, Price, GroupID)
SaleRecord(PID, SaleDate, SaleQty)
Supplier(SupplierID, SupplierName, SupplierAddress)
Supply(SupplierID, PID)

依赖：

GroupID -> GroupName, Manager
EmpID -> EmpName, Wage, GroupID
PID -> PName, Price, GroupID
(PID, SaleDate) -> SaleQty
SupplierID -> SupplierName, SupplierAddress

“互联网+教育”项目

可设计实体：

Teacher(TeacherID, TeacherName)
Course(CourseID, CourseName)
Member(MemberID, MemberName, Balance)
Teach(TeacherID, CourseID)
Enroll(MemberID, CourseID)
LearningRecord(MemberID, CourseID, StartTime, EndTime, Cost)
Recharge(MemberID, RechargeTime, Amount)
TeacherIncome(TeacherID, CourseID, IncomeDate, Amount)
CompanyIncome(IncomeID, Source, Amount, IncomeDate)

说明：

• 教师与课程多对多，用 Teach。
• 会员与课程多对多，用 Enroll 或学习记录。
• 充值、学习消费、教师分成、公司收益都属于独立业务事件，最好单独成表，避免把多次事件塞成重复列。

九、答题时画图可以用文字结构代替吗

正式试卷要求画 E-R 图时，最好画矩形、菱形、椭圆，并标 1:n/m:n。若时间紧，可以用清晰的文本 E-R 辅助：

学院 1 --- n 专业
专业 1 --- n 学生
学生 m --- n 课程 [成绩]
教师 m --- n 课程 [教室, 课时]
学院 1 --- n 教师
学院 1 --- n 课程

再写“矩形为实体，方括号为联系属性”，通常能保住主要分数。