数据库设计核心：ER图、SQL与范式化到BCNF的实战指南

简介悉尼大学Database Management System课程学习资料包适合数据库初学者和计算机相关专业学生用于系统掌握数据库管理系统核心原理。内容涵盖数据模型、关系代数、SQL查询、事务处理、并发控制、数据库设计及安全性等知识点并配有每周教程与作业答案。包体共65个文件以PDF讲义与习题解答为主另含SQL脚本、PPT课件及少量压缩包总大小17.04MB按week1至week13组织定位清晰。已有191人学习下载。每周专题均配有练习题和参考解法例如关系代数、复杂SQL、事务处理、查询优化、存储索引等同时提供大学模式建表SQL、示例数据及期末复习资料有助于巩固理论与实践提升数据库设计与应用能力。1. 悉尼大学 Database Management System 课程一门把“库表设计”变成硬功夫的必修课选这门课之前很多人以为它只是“教你怎么写 SQL”。上过两个星期才发现课程真正的重心是数据库管理系统背后的设计逻辑你凭什么这样建表、为什么这个查询慢、范式化到第几步才算合格。它面向两类人——以后要做后端开发、数据分析的学生以及想把数据库从“会 CRUD”往上抬一个台阶的从业者。课程把数据建模、关系代数、SQL、事务和索引串成一条线学完以后至少能回答“这张表设计得烂不烂”和“这条慢查询卡在哪”。你不用把它当纯理论课每一章节几乎都对应一套能直接在 PostgreSQL 或 MySQL 里复现的练习跟着走完收获比背概念大得多。2. 课程主线与三张必须吃透的图ER 模型、关系模式与依赖关系悉尼大学 Database Management System 课程的推进节奏业界不少数据库入门课也照这个走先让你学会“把现实世界翻译成表结构”然后才是 SQL 操纵。第一个里程碑是 ER 图第二个是关系模式第三个是函数依赖与范式。三个环节环环相扣跳过任何一个后面的作业都会还回来。2.1 ER 图阶段实体、联系和基数的一对多陷阱ER 图阶段要交付的不是“画得好看”而是“能让人照着建表”。常见作业是给一段业务描述比如“一个学生可以选多门课一门课有多个学生选修每个老师只能带一个班级”让你画出实体、属性和联系。这个阶段最容易翻车的点是联系上的基数约束一对多还是多对多直接决定后续外键放在哪张表。判定方法其实很机械先找名词实体再找动词联系最后检查每一对实体之间的“每”字。出现“每个 A 可以对应多个 B而每个 B 只能对应一个 A”就是一对多外键放 B 侧。出现“一个 A 对应多个 B一个 B 也对应多个 A”就是多对多必须拆出一张中间表中间表的主键通常是两个外键的组合。画完 ER 图后课程一般会要求你用专属的图形标记法常见的是 crows foot 或 UML 风格标注参与约束。这里有个血泪经验多对多联系里如果联系本身带属性比如“选课”这个联系带“成绩”属性这个属性不能挂在任何一端的实体上只能放在中间表里。否则后面转关系模式时会发现属性无处安放。2.2 从 ER 图转关系模式外键位置与命名规范ER 图到关系模式的转换有固定套路课程通常要求按“实体成表、属性成列、联系定外键”的步骤来写。一对多联系中外键加在“多”的那一侧实体表多对多联系则新建一张关联表表中只放两个外键加联系自身的属性一对一联系一般把外键放在任意一侧但更常见的是合并成一张表。转换结果要用统一格式呈现常见交作业格式是这样的表格关系模式名属性主键加下划线外键参照表Studentstudent_id, name, email, advisor_idadvisor_idFacultyFacultyfaculty_id, name, dept无无Enrolmentstudent_id, course_id, gradestudent_id, course_idStudent, Course注意主键要加下划线或直接标注 PK外键要写明参照哪张表。这个阶段我一般会顺手做一件事把每个关系的属性清单打印出来人工走一遍“这个属性不再依赖主键吗”的检查。虽然范式化的正式检查在后面但这时候先扫一遍能省掉后面一大半返工。2.3 函数依赖比范式定义更值得手推的关系函数依赖是这门课里最像“数学题”的部分也是作业里区分度最高的考点。给定一张表的关系实例要求你写出其中的函数依赖或者反过来给定函数依赖集合让你判断候选键。这两个题型本质是一个能力看出“哪些列决定哪些列”。写法上X → Y 表示“X 的值唯一确定 Y 的值”。判断候选键时先找出所有只出现在箭头左侧或没出现的属性它们一定在候选键里然后用函数依赖闭包计算看它能不能推出全属性。闭包算法虽然简单但手算特别容易漏依赖尤其是传递依赖 X → Y, Y → Z 这种链条。我常用的验证方式是把每条依赖倒回数据表里检查手动找两行看 X 相同而 Y 不同的情况是否存在。只要存在这条依赖就不成立。这个方法看着笨但比空想靠谱得多。课程作业里不少“判断下列函数依赖是否成立”的题目用这个方法基本不会错。3. 用 SQL 完成课程作业里的三类必考任务查询、聚合与多表连接SQL 部分在悉尼大学 Database Management System 课程里占了整整一个阶段作业往往要求你在一套预置的数据库上完成一系列查询。虽然不同年份的题目和数据不同但考点非常固定基础过滤、多表连接、分组聚合和子查询。下面用一套最小的学生选课库演示这三类必考任务这套 SQL 在 MySQL 8.x 和 PostgreSQL 上都能直接跑。先建一张选课表-- 选课表学生与课程的关联带成绩 CREATE TABLE enrolment ( student_id INT NOT NULL, course_id INT NOT NULL, grade DECIMAL(3,1), semester VARCHAR(10), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );这个建表语句包含了三个课程作业里反复考的要点复合主键、外键约束、以及NOT NULL的合理使用。grade没有加NOT NULL因为“还没出成绩”是真值不能用 0 或者 NULL 以外的东西代替。复合主键(student_id, course_id)直接对应 ER 阶段多对多联系的中间表设计这是连接知识点的关键位置。第一类必考任务是带条件的过滤常见写法是 WHERE 加 AND/OR 组合-- 查 2024 Semester 2 成绩不低于 70 分的选课记录按成绩降序 SELECT student_id, course_id, grade FROM enrolment WHERE semester 2024 S2 AND grade 70 ORDER BY grade DESC;这里有个新手常犯的错误把ORDER BY放在WHERE前面。SQL 的执行顺序其实是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BYORDER BY永远在最后但它写在查询语句的末尾。记住执行顺序而不是书写顺序能避免很多玄学报错。第二类必考任务是聚合常配合 GROUP BY 统计人数、平均分、最高最低-- 每门课的平均分与选课人数只显示选课人数超过 2 的课程 SELECT course_id, COUNT(*) AS cnt, ROUND(AVG(grade), 1) AS avg_grade FROM enrolment WHERE grade IS NOT NULL GROUP BY course_id HAVING COUNT(*) 2 ORDER BY avg_grade DESC;这段代码展示了WHERE和HAVING的本质差别WHERE在分组前过滤行HAVING在分组后过滤组。AVG(grade)会自动忽略 NULL但COUNT(*)会数所有行如果只想数有成绩的要写COUNT(grade)。这些细节课程作业里都是扣分点多写一句注释能帮阅卷人看出你懂。第三类必考是连接查询常见的是内连接查“学生-选课-课程”三张表-- 查学生姓名、课程名与成绩只显示已选课学生 SELECT s.student_name, c.course_name, e.grade FROM student AS s JOIN enrolment AS e ON s.student_id e.student_id JOIN course AS c ON e.course_id c.course_id WHERE e.semester 2024 S2;连接顺序不是随便写的先 JOIN 两张小表缩减中间结果集再 JOIN 第三张比把大表放前面快得多。另一个常见坑是忘了加WHERE过滤就 JOIN 三张表造成笛卡尔积膨胀。自连接、左连接和子查询在作业里也常见尤其是“查没有选任何课的学生”这类题用LEFT JOIN ... WHERE ... IS NULL比用NOT IN更不容易踩 NULL 的坑。4. 范式化到 BCNF一个订单表的分解全过程范式化是悉尼大学 Database Management System 课程里理论性最强、也最让新手头疼的部分。考试和作业里常见的题型是给定一个表结构和函数依赖集合判断它属于第几范式如果不满足 BCNF 就做分解。难点不在定义本身而在“怎么拆得干净又不丢依赖”。4.1 从 1NF 到 BCNF四条判断标准的速查逻辑判断范式有一套固定的检查顺序按层级往上推范式检查标准违反时的典型症状1NF所有属性都是原子值不出现多值或重复组一个字段里存多个值逗号分隔、JSON 数组2NF在 1NF 基础上所有非主属性完全依赖主键无部分依赖复合主键下某个非主属性只依赖主键的一部分3NF在 2NF 基础上无传递依赖非主属性不依赖其他非主属性某列依赖另一列而不是主键BCNF每个函数依赖 X → Y 的 X 都包含候选键判定更严格3NF 也拦不住某些异常检查时按“先看主键是不是复合的再看非主属性之间有没有依赖链”的顺序走比逐条背定义快。作业里经常给一张“订单明细表”让你判断它有订单号、订单日期、客户名、商品名、单价、数量。主键是 (订单号, 商品名)但订单日期和客户名只依赖订单号这就是部分依赖2NF 都没到。4.2 订单表到 BCNF 的两步分解示例用上面订单明细表做完整分解。假设初始表和函数依赖如下订单明细(订单号, 下单日期, 客户名, 商品名, 单价, 数量) 函数依赖 订单号 → 下单日期, 客户名 商品名 → 单价 (订单号, 商品名) → 数量主键是 (订单号, 商品名)。非主属性“下单日期”和“客户名”只依赖订单号主键的一部分存在部分依赖所以需要先拆 2NF。把部分依赖的属性拆出去-- 订单头表以订单号为主键 CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE NOT NULL, customer VARCHAR(50) NOT NULL ); -- 订单明细表只留下订单号、商品名和数量 CREATE TABLE order_items ( order_id INT NOT NULL, product VARCHAR(50) NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, product), FOREIGN KEY (order_id) REFERENCES orders(order_id) ); -- 商品表商品名带单价 CREATE TABLE product ( product VARCHAR(50) PRIMARY KEY, price DECIMAL(8,2) NOT NULL );这个拆分动作对应课程里说的“投影分解法”把违反范式的函数依赖单独成表原表只保留主键和外键。拆分到这一步原表已经满足 3NF但要到 BCNF 还得再检查3NF 允许“商品名 → 单价”这样依赖键以外属性的依赖存在吗允许但 BCNF 不允许因为商品名不是候选键。所以还需要把商品拆出去——上面代码里已经完成了这一步。4.3 分解无损的判断公用属性与依赖保留分解完最怕的是丢了函数依赖或者分解不可逆。课程里教了两个判定方法无损连接判定看两个分解后的表是否有至少一个共同属性并且该属性在其中一张表是主键依赖保留则看每个函数依赖是否都能在某一张分解后的表里直接验证。上面三次分解都满足这两个条件orders 和 order_items 通过 order_id 连接order_items 和 product 通过 product 连接每条函数依赖都能在对应表里用主键唯一性直接确认。实际做作业时我的习惯是把每个分解后的表在草稿上重写一遍函数依赖逐条划勾。只要有一条依赖在两个表里都找不到位置说明分解丢依赖了这种答案在考试里扣分非常狠。特别注意BCNF 分解有时会破坏依赖保留出现这种情况时要不要退而求其次用 3NF是课程里值得跟老师讨论的经典取舍。5. 数据库课程作业常见的 5 个翻车现场现象、原因与解法作业写得再顺跑数据时总有几个反复出现的坑。下面五条是按出现频率排的每一条都是我见过的真实翻车记录。5.1 外键约束插入失败明明有数据却说不满足完整性现象向从表插入记录时报外键约束错误但去主表查参照的那行明明存在。原因最常见的是数据字符集不一致。主表和从表的连接列一个用了 utf8mb4一个用了 latin1 或 utf8看起来内容一样但二进制比较不相等。其次是从表连接列的数据类型与主表主键不一致比如一个是 INT一个是 VARCHAR(11)MySQL 在做隐式转换时走了不同的索引路径。解决统一字符集和排序规则连接列类型严格对齐主键类型。建议在初始化表的时候把CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci统一写到库级配置而不是每张表各写各的。排错时先执行SHOW CREATE TABLE检查两边定义再比对列类型基本两步定位。5.2 聚合查询结果看着对一验算全是错的GROUP BY 的隐式分组踩坑现象执行SELECT student_id, course_id, COUNT(*) FROM enrolment没写 GROUP BYMySQL 低版本能跑出一行结果但结果毫无意义。原因MySQL 5.7 之前默认关闭了ONLY_FULL_GROUP_BY模式允许 select 列表里出现既不参与分组也不在聚合函数里的列取的是随机行的值。6.0 以后默认开启但很多教学环境用的老镜像还是旧行为。解决把sql_mode里加上ONLY_FULL_GROUP_BY一劳永逸写查询时养成“SELECT 里出现的每个非聚合列都必须出现在 GROUP BY 里”的习惯。这条规则比背任何 SQL 文档都管用能避免 90% 的聚合踩坑。5.3 字符集乱码中文注释和表数据全变问号现象插入中文后查询显示??或者建表时注释里的中文直接变乱码。原因连接层未指定字符集。数据库表是 utf8mb4但客户端连接用的还是 latin1字符在传输过程中被转坏。课程作业里因为是本机测试环境很多人不会刻意设连接字符集一提交代码给多人协作库就暴露了。解决每次建立连接后立刻执行SET NAMES utf8mb4;或者把 JDBC 连接串加上characterEncodingutf8mb4。建库时用DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci避免每张表单独声明时写错。5.4 无法确定后端是什么数据库SQL 方言带来的工具失灵现象用一个通用数据库工具或导入脚本批量执行 SQL工具提示类似“was not able to fingerprint the back-end database management system”的信息或者某个查询在一个库里能跑在另一个库里直接语法报错。原因不同数据库管理系统的 SQL 方言有差异。MySQL 用反引号包裹表名PostgreSQL 用双引号LIMIT 语法两者都支持但分页参数写法不同字符串连接 MySQL 用CONCATSQL Server 用。自动识别工具如果拿到一段带强烈方言特征的 DDL 或查询就可能无法确认后端数据库类型而拒绝执行。解决课程作业如果是指定数据库环境先确认目标管理系统版本如果是写一份通用脚本尽量用 ANSI SQL 标准语法避免反引号和方言函数。遇到工具报“无法识别后端数据库管理系统”时先把脚本里最有方言特色的语句比如AUTO_INCREMENT、反引号换成标准写法再试通常就能通过。查表操作尽量走INFORMATION_SCHEMA不同数据库都有兼容接口比直接查询系统表更稳。5.5 删除父表数据时外键冲突忘了 ON DELETE 的行为现象删除一条课程记录时报外键约束错误明明子表已经没有相关记录但删除仍然失败。原因外键约束的默认行为是ON DELETE RESTRICT只要子表历史上存在过记录即便已删除某些数据库在检查时依然报冲突或者你记得清过子表数据但用的删除语句没有提交事务另一个会话还持有行锁。解决确认删除目标前先执行SELECT COUNT(*) FROM enrolment WHERE course_id 该课程ID如果确认无引用检查事务是否提交。在创建外键时明确写ON DELETE CASCADE或SET NULL而不是靠默认值行为就可预期了。6. 用 EXPLAIN 和 INFORMATION_SCHEMA 验证作业设计让数据库自己回答“设计得怎样”最后一个环节不是加新功能而是验证你已经做完的库表设计和查询是否合理。课程作业提交前如果能把下面这套验证流程跑一遍设计问题基本都能暴露。先看查询计划。对作业里每个核心查询执行EXPLAIN SELECT ...检查输出里的type列。出现ALL表示全表扫描对超过几千行的表来说就是慢查询的预警出现index或者ref表示走了索引基本合格。重点看连接顺序和是否用到覆盖索引把常用查询里的所有列都放进同一个复合索引可以让Extra列出现Using index意思是查询不用回表这在作业答辩里是加分项。再看表结构信息。用 INFORMATION_SCHEMA 核对设计一致性-- 检查所有表的字符集避免出现混用 SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema 你的数据库名; -- 检查外键关系是否完整建立 SELECT table_name, constraint_name, referenced_table_name FROM information_schema.referential_constraints WHERE constraint_schema 你的数据库名;这两条查询能快速发现两张最常见的低级错误表与表之间字符集不统一以及外键漏建。前者会导致查询慢和莫名其妙的连接失败后者则直接违背 ER 阶段的设计意图。建议把这两条查询固定存成一个小脚本每次作业提交前跑一遍。我的习惯是所有主键和外键列都起表名_id的命名格式建表时顺手把字符集写进建表语句而不是依赖库默认值每次写完查询都跑一次 EXPLAIN 看有没有全表扫描。这套流程看着朴素但确实帮我挡掉了不少交作业前才发现的设计问题。这门课真正的收获不是记住范式定义而是学会让数据库管理系统用执行计划这类客观输出告诉你设计哪里不对——希望帮到你。本文还有配套的精品资源点击获取