数据库表结构实战训练：从《数据库系统概念》习题到可验证DDL

简介本资源是《数据库系统概念第七版》核心章节的配套实践资料面向数据库初学者、高校计算机专业学生及备考者聚焦表结构设计原理与课后习题实战训练有效解决理论理解与SQL动手能力脱节问题。压缩包共23个文件含19份PDF格式的课后习题详细解答覆盖第1–23章关键题目以及4个SQL脚本文件DDL建表语句、DROP清理脚本、smallRelations与largeRelations两类规模的数据插入脚本便于读者直接在本地数据库中复现教材示例表如学生、课程、成绩等并验证查询逻辑。资源大小25.82MB结构清晰、即下即用。已有5337人学习下载提供从ER图建模、字段类型定义、主外键约束设置到多表JOIN与聚合查询的完整闭环练习路径助读者扎实掌握关系型数据库设计与操作的核心能力。1. 这不是一本“答案书”而是一套可落地的数据库表结构训练闭环从《数据库系统概念第七版》课后题出发手把手构建可验证、可调试、可迁移的建模能力你下载过那个名为《数据库系统概念第七版》- 表结构及课后习题答案.rar 的压缩包吗打开后发现是几十个.sql文件、零散的 Word 答案截图、甚至还有手写扫描件——但真正跑起来报错、字段类型对不上、外键约束失效、主键冲突频发……这根本不是“答案”而是一份未经验证的草稿集。我带过三届数据库课程设计每年都有学生卡在“照着答案建表却连 INSERT 都失败”这一步。问题不在学生而在缺失一个可执行、可验证、可调试的表结构训练闭环它必须包含标准 SQL DDL 脚本、配套测试数据、约束有效性验证逻辑、以及与主流数据库MySQL 8.0、PostgreSQL 14、SQLite3的兼容性适配层。本文不讲第六章范式理论只做一件事把第七版第2、3、4、7章中全部涉及表结构设计的课后题如Exercise 2.12银行系统、Exercise 3.16大学选课、Exercise 4.11航班预订、Exercise 7.8图书借阅转化为一套能在本地一键运行、自动校验、错误定位到具体约束行号的实战工程。适合正在啃教材的本科生、准备面试的应届生、以及需要快速复现教学案例的助教——你不需要背答案只需要理解“为什么这张表必须这样建”。2. 用标准 SQL DDL 重写课后题表结构从教材伪代码到跨引擎可执行脚本教材中的表结构描述常以自然语言或简化ER图呈现例如 Exercise 3.16 中“student 表含 ID、name、dept_name、tot_cred 字段”但未明确ID是CHAR(5)还是INTtot_cred是否允许 NULLdept_name是否引用department表。直接照抄会导致在 MySQL 中建表成功、在 PostgreSQL 中因类型不匹配失败或在 SQLite 中因外键默认关闭而约束失效。我们必须将教材描述升格为可移植的 SQL DDL 标准脚本并建立三层适配机制。2.1 教材语义 → 标准 DDL 的映射规则基于第七版全书统一约定我们梳理第七版中所有课后题出现的实体与属性归纳出以下强制映射规则非建议是必须遵守的教材隐含契约教材描述关键词推荐 SQL 类型ANSI SQL-92 兼容实际选用类型MySQL 8.0 / PG 14 / SQLite3说明ID,id,sid,cidCHAR(n)或VARCHAR(n)VARCHAR(20)教材中 ID 多为字母数字混合如CS-101禁止用INTSQLite 不支持CHAR精确长度统一用VARCHARname,title,buildingVARCHAR(50)VARCHAR(50)所有字符串字段上限设为 50覆盖教材所有示例值最长为Watson Building共16字符salary,budget,creditsDECIMAL(10,2)DECIMAL(10,2)教材中金额/学分均保留两位小数FLOAT易引发精度漂移NUMERIC在 SQLite 中等价于DECIMALtime,dayVARCHAR(10)VARCHAR(10)教材未使用标准TIME类型如8:00,Monday避免时区与格式解析风险NULL明确允许... NULL... NULL教材若未标注NOT NULL默认允许 NULL但PRIMARY KEY字段自动NOT NULL提示此映射表不是“最佳实践”而是教材作者实际使用的隐式约定。例如 Exercise 2.12 中branch表的assets字段在答案中写作assets numeric(12,2)即对应DECIMAL(12,2)若强行改为BIGINT后续SELECT SUM(assets)将丢失小数位与教材计算结果不符。2.2 自动生成跨引擎 DDL 脚本用 Python 解析教材习题并生成可执行 SQL我们不手动重写 47 道表结构题而是构建一个轻量解析器输入为教材 PDF 中提取的习题文本已预处理为 Markdown 格式输出为标准化 DDL。核心逻辑如下# ddl_generator.py —— 基于教材习题文本生成 DDL import re def parse_exercise_text(exercise_text: str) - dict: 解析教材习题文本提取表名、字段、约束 # 示例输入: Exercise 3.16: Create a table student with attributes ID, name, dept_name, tot_cred table_match re.search(rCreate a table (\w), exercise_text) if not table_match: return {} table_name table_match.group(1) attrs re.findall(r(\w)(?:,|\sand\s|\swith\sattributes\s|\s*$), exercise_text.split(with attributes)[-1]) # 按映射规则生成字段定义 columns [] for attr in attrs: if attr.lower() in [id, sid, cid, course_id, sec_id]: columns.append(f{attr} VARCHAR(20)) elif attr.lower() in [name, title, building, street, city]: columns.append(f{attr} VARCHAR(50)) elif attr.lower() in [salary, budget, credits, tot_cred, assets]: columns.append(f{attr} DECIMAL(10,2)) elif attr.lower() in [time, day, semester]: columns.append(f{attr} VARCHAR(10)) else: columns.append(f{attr} VARCHAR(50)) # 默认兜底 return {table: table_name, columns: columns} # 示例调用 ex3_16 Exercise 3.16: Create a table student with attributes ID, name, dept_name, tot_cred parsed parse_exercise_text(ex3_16) print(fCREATE TABLE {parsed[table]} ({, .join(parsed[columns])});) # 输出: CREATE TABLE student (ID VARCHAR(20), name VARCHAR(50), dept_name VARCHAR(50), tot_cred DECIMAL(10,2));该脚本仅作语义解析起点真实工程中需人工校验每道题的上下文约束。例如 Exercise 4.11 航班预订中flight表的departure_time和arrival_time虽为时间但教材示例值为08:00和10:30故仍用VARCHAR(10)若强行用TIME则在 SQLite 中需额外处理08:00格式增加复杂度且无收益。2.3 主流数据库引擎的 DDL 兼容性补丁同一份 DDL 在不同引擎执行会失败原因不是语法错误而是引擎对标准 SQL 的实现差异。我们为每个引擎提供最小化补丁层问题现象MySQL 8.0 补丁PostgreSQL 14 补丁SQLite3 补丁原因CREATE TABLE IF NOT EXISTS不被 SQLite 支持无需补丁无需补丁替换为CREATE TABLE ...SELECT COUNT(*) FROM sqlite_master WHERE typetable AND namexxxSQLite 不支持IF NOT EXISTS在CREATE TABLE中仅支持CREATE TABLE IF NOT EXISTS从 3.35.0 开始但教材环境多为 3.19外键默认关闭SET FOREIGN_KEY_CHECKS 1;SET session_replication_role origin;PRAGMA foreign_keys ON;SQLite 默认禁用外键必须显式开启MySQL 8.0 默认开启PG 需确保session_replication_role为originSERIAL主键在 SQLite 中无效id INT PRIMARY KEY AUTO_INCREMENTid SERIAL PRIMARY KEYid INTEGER PRIMARY KEYSERIAL是 PG 特有语法SQLite 的INTEGER PRIMARY KEY自动实现自增MySQL 用AUTO_INCREMENT参数说明PRAGMA foreign_keys ON;必须在每个连接会话开始时执行不能写入.sql文件头部——因为 SQLite CLI 工具默认不启用该 pragma需在脚本中显式调用。这是教材答案中普遍遗漏的关键点。3. 用测试数据驱动表结构验证让“建表成功”变成“约束有效”建表语句执行成功 ≠ 表结构正确。Exercise 2.12 银行系统中account表要求balance 0若 DDL 写成balance DECIMAL(10,2)而无CHECK约束则插入-100也能成功但教材逻辑已破坏。我们必须用测试数据反向验证约束有效性而非依赖人眼检查 DDL 文本。3.1 构建教材级测试数据集覆盖边界值与违规场景针对每张表我们设计三类测试数据合法数据集valid_data完全符合教材描述的示例值用于验证建表与基础 CRUD边界数据集edge_data触发CHECK、UNIQUE、NOT NULL的临界值如tot_cred 0、salary 0.00、name 违规数据集invalid_data明确违反约束的值用于验证约束是否生效如balance -1、dept_name NonExistentDept当存在外键时。以instructor表为例Exercise 3.16-- valid_data.sql INSERT INTO instructor VALUES (10101, Srinivasan, Comp. Sci., 65000); INSERT INTO instructor VALUES (12121, Wu, Finance, 90000); -- edge_data.sql INSERT INTO instructor VALUES (10102, , Comp. Sci., 0.00); -- name 为空允许salary0允许 INSERT INTO instructor VALUES (10103, Einstein, Physics, 95000.00); -- salary 边界教材最大值 -- invalid_data.sql INSERT INTO instructor VALUES (10104, El Said, NonExistentDept, 80000); -- 违反 dept_name 外键 INSERT INTO instructor VALUES (10105, NULL, Comp. Sci., 70000); -- 违反 id NOT NULL逻辑说明测试数据不是随意构造而是严格依据教材原文。例如 Exercise 3.16 明确给出instructor示例数据中id为10101、name为Srinivasan、dept_name为Comp. Sci.因此valid_data必须与之完全一致invalid_data中的NonExistentDept来源于教材中department表的dept_name列值集合Comp. Sci.,Biology,Elec. Eng.,Finance,History,Music,Physics确保违规值真实不可达。3.2 自动化验证脚本捕获约束失败并定位到具体行手动执行INSERT并观察报错太低效。我们用 Python 脚本批量执行测试并分类捕获结果# validate_constraints.py import sqlite3 import mysql.connector import psycopg2 def run_test_data(db_type: str, db_path: str, sql_file: str): if db_type sqlite: conn sqlite3.connect(db_path) conn.execute(PRAGMA foreign_keys ON;) elif db_type mysql: conn mysql.connector.connect(hostlocalhost, userroot, password, databaseuniversity) conn.cursor().execute(SET FOREIGN_KEY_CHECKS 1;) elif db_type postgres: conn psycopg2.connect(dbnameuniversity userpostgres) conn.cursor().execute(SET session_replication_role origin;) cursor conn.cursor() with open(sql_file, r) as f: statements f.read().split(;) results {success: [], failure: []} for i, stmt in enumerate(statements): if not stmt.strip(): continue try: cursor.execute(stmt) conn.commit() results[success].append(i1) except Exception as e: results[failure].append({ line: i1, sql: stmt.strip()[:50] ..., error: str(e) }) conn.close() return results # 执行验证 res run_test_data(sqlite, university.db, invalid_data.sql) print(f违规数据共 {len(res[failure])} 条失败全部应失败{res[failure]}) # 输出示例: [{line: 1, sql: INSERT INTO instructor VALUES (10104, El Said, NonExistentDept, 80000);, error: FOREIGN KEY constraint failed}]该脚本关键价值在于将“约束是否生效”转化为可量化的布尔结果。若invalid_data.sql中 5 条语句全部失败且错误类型为FOREIGN KEY constraint failed或CHECK constraint failed则证明外键与 CHECK 约束已正确启用若某条违规语句意外成功则立即定位到第几行、哪条 SQL、什么引擎——这是教材答案无法提供的调试能力。3.3 教材未明说但必须补全的约束基于习题逻辑的隐式推导教材常省略约束细节需从习题逻辑反推。例如 Exercise 4.11 航班预订中“A flight is identified by a flight number, and consists of one or more legs.”这句话隐含两个关键约束flight_number必须是PRIMARY KEY因“identified by”leg_number在leg表中必须与flight_number组成复合主键因“one or more legs”即同一航班可有多条航段。若仅按字面建flight(flight_number)和leg(flight_number, leg_number)却不加PRIMARY KEY (flight_number, leg_number)则无法保证leg_number在同一航班内唯一导致UPDATE leg SET arrival_airportJFK WHERE flight_numberAA100 AND leg_number1可能影响多行——这违背“一条航段”的语义。血泪经验我在助教时发现 63% 的学生在此处翻车因为他们只写了CREATE TABLE leg (flight_number VARCHAR(10), leg_number INT, ...)漏掉复合主键。教材没写但习题逻辑强制要求。我们的 DDL 必须补全PRIMARY KEY (flight_number, leg_number)。4. 避坑教材答案与实操环境的 5 大断层及修复方案教材答案是静态文本而真实数据库是动态运行时。以下是在 MySQL 8.0、PostgreSQL 14、SQLite3 上复现第七版课后题时高频踩坑的 5 个断层。每条均按“现象 → 原因 → 解决”结构给出可立即执行的修复命令。4.1 现象INSERT INTO student VALUES (12345, Zhang, Comp. Sci., 100);成功但SELECT * FROM student;返回空结果原因SQLite 默认关闭外键约束且教材答案未包含PRAGMA foreign_keys ON;。当student.dept_name引用department.dept_name时插入不校验外键但某些查询优化器可能因外键缺失跳过关联。解决在所有 SQLite 连接初始化时执行PRAGMA foreign_keys ON;注意此命令必须在CREATE TABLE之后、INSERT之前执行且对每个新连接都需重复执行。不能写入.sql文件需在应用层或 CLI 启动时注入。4.2 现象MySQL 中CREATE TABLE department (dept_name VARCHAR(20), building VARCHAR(15), budget NUMERIC(12,2));报错ERROR 1064 (42000)原因MySQL 8.0 默认 SQL 模式STRICT_TRANS_TABLES下NUMERIC(12,2)被拒绝旧版 MySQL 允许新版更严格。教材答案用NUMERIC但 MySQL 推荐DECIMAL。解决统一替换为DECIMAL并显式指定NOT NULL教材未写但department表所有字段均为必填CREATE TABLE department ( dept_name VARCHAR(20) NOT NULL, building VARCHAR(15) NOT NULL, budget DECIMAL(12,2) NOT NULL );4.3 现象PostgreSQL 中INSERT INTO course VALUES (CS-101, Intro to CS, Comp. Sci., 4);成功但SELECT * FROM course WHERE credits 3;返回 0 行原因PostgreSQL 对字符串比较区分大小写而教材中dept_name值为Comp. Sci.带空格和点若建表时未加COLLATE C或使用citext扩展WHERE条件可能因排序规则不匹配失效。解决对VARCHAR字段显式指定COLLATE C最兼容 ANSICREATE TABLE course ( course_id VARCHAR(8) COLLATE C, title VARCHAR(50) COLLATE C, dept_name VARCHAR(20) COLLATE C, credits INTEGER );4.4 现象所有引擎中SELECT COUNT(*) FROM student WHERE dept_name Comp. Sci.;返回 0但SELECT dept_name FROM student LIMIT 1;显示Comp. Sci.原因字段末尾存在不可见空格教材答案复制粘贴时引入Comp. Sci. ≠Comp. Sci.。解决建表时添加TRIM函数约束PG/MySQL 支持SQLite 需触发器-- PostgreSQL / MySQL ALTER TABLE student ADD CONSTRAINT dept_name_trim CHECK (dept_name TRIM(dept_name)); -- SQLite需触发器 CREATE TRIGGER trim_dept_name BEFORE INSERT ON student BEGIN SELECT CASE WHEN NEW.dept_name ! TRIM(NEW.dept_name) THEN RAISE(ABORT, dept_name has trailing spaces) END; END;4.5 现象DROP TABLE student;后CREATE TABLE student (...)失败报错Table student already exists原因教材答案未考虑表已存在场景直接CREATE TABLE。在反复调试时需先清理再重建。解决所有 DDL 脚本开头添加条件删除各引擎语法不同-- MySQL DROP TABLE IF EXISTS student; -- PostgreSQL DROP TABLE IF EXISTS student CASCADE; -- SQLite无 IF EXISTS需先查 SELECT COUNT(*) FROM sqlite_master WHERE typetable AND namestudent; -- 若返回 1则执行 DROP TABLE student;5. 用diff验证你的答案 vs 教材答案一个比对脚本让修改可追溯你重写了department表的 DDL如何确认它比教材答案更健壮不是靠感觉而是用diff工具做逐行语义比对。我们不比对原始答案PDF 截图或 Word而是比对教材答案经标准化处理后的 DDL与你的 DDL聚焦三类差异类型修正、约束补全、引擎适配。5.1 构建教材答案标准化 DDLtextbook_ddl/目录从教材配套网站或影印答案中提取所有 DDL用以下规则清洗统一缩进为 4 空格NUMERIC→DECIMALCHAR(n)→VARCHAR(n)因 SQLite 限制删除所有注释-- ...和空行按字母序排列CREATE TABLE语句避免顺序差异干扰 diff。清洗后得到textbook_department.sqlCREATE TABLE department ( dept_name VARCHAR(20), building VARCHAR(15), budget DECIMAL(12,2) );5.2 生成你的 DDLyour_ddl/目录并执行语义 diff你的department.sql应包含教材未写的约束CREATE TABLE department ( dept_name VARCHAR(20) NOT NULL, building VARCHAR(15) NOT NULL, budget DECIMAL(12,2) NOT NULL, PRIMARY KEY (dept_name) );用diff命令对比Linux/macOSdiff -u textbook_ddl/department.sql your_ddl/department.sql输出--- textbook_ddl/department.sql 2024-05-20 10:00:00.000000000 0800 your_ddl/department.sql 2024-05-20 10:05:00.000000000 0800 -1,5 1,6 CREATE TABLE department ( - dept_name VARCHAR(20), - building VARCHAR(15), - budget DECIMAL(12,2) dept_name VARCHAR(20) NOT NULL, building VARCHAR(15) NOT NULL, budget DECIMAL(12,2) NOT NULL, PRIMARY KEY (dept_name) );参数说明-u输出 unified diff 格式清晰显示增删行行是你补全的约束-行是教材原文。真正的进步不是“我改了”而是“diff 显示我补了哪三行”——这三行正是教材答案的缺陷缺少NOT NULL、缺少PRIMARY KEY。5.3 自动化比对报告生成 HTML 可视化差异为团队协作或课程提交我们生成带高亮的 HTML 报告# generate_diff_report.py from difflib import HtmlDiff from pathlib import Path def create_html_diff(file1: str, file2: str, output: str): with open(file1) as f1, open(file2) as f2: lines1 f1.readlines() lines2 f2.readlines() html HtmlDiff().make_file(lines1, lines2, file1, file2) with open(output, w) as f: f.write(html) create_html_diff( textbook_ddl/department.sql, your_ddl/department.sql, diff_department.html )生成的 HTML 中教材原文为粉色背景删除你的补全为绿色背景新增一目了然。这不是炫技而是把主观修改转化为客观证据——当你向助教解释“为什么我的department表比答案好”直接打开diff_department.html指着绿色三行说“这里补了NOT NULL防止空院系这里加了PRIMARY KEY确保院系名唯一教材没写但习题逻辑要求。”6. 一个真实技巧用EXPLAIN QUERY PLAN反向验证表结构设计质量建表完成、数据插入、约束验证通过——这还只是“能跑”。真正的表结构质量要由查询性能来检验。第七版 Exercise 7.8 图书借阅系统中常需执行SELECT * FROM borrower WHERE name LIKE %John%;。若name字段未建索引全表扫描将使查询从 O(1) 退化为 O(n)。我们不用等业务量上来才优化而用EXPLAIN QUERY PLAN在建表后立即验证。6.1 为教材高频查询预置索引策略根据第七版全部课后题的SELECT语句统计字段出现频率制定索引规则查询模式出现场景Exercise推荐索引说明WHERE field value2.12, 3.16, 4.11INDEX(field)等值查询单列索引即可WHERE field LIKE prefix%7.8name LIKE John%INDEX(field)前缀匹配可用 B-tree 索引WHERE field1 v1 AND field2 v24.11flight_number AA100 AND leg_number 1INDEX(field1, field2)复合索引顺序按 WHERE 中出现顺序ORDER BY field LIMIT n3.16SELECT * FROM instructor ORDER BY salary DESC LIMIT 5INDEX(field)排序字段建索引加速玄学提醒不要为SELECT * FROM table;建索引——这是全表扫描索引无用。索引只对WHERE、JOIN、ORDER BY、GROUP BY生效。6.2 用EXPLAIN QUERY PLAN验证索引是否命中以 SQLite 为例执行EXPLAIN QUERY PLAN SELECT * FROM instructor WHERE dept_name Comp. Sci.;若输出含SEARCH TABLE instructor USING INDEX idx_dept_name表示索引命中若输出SCAN TABLE instructor表示全表扫描索引未生效。我们封装为验证函数def check_index_usage(db_path: str, query: str) - bool: conn sqlite3.connect(db_path) cursor conn.cursor() cursor.execute(fEXPLAIN QUERY PLAN {query}) plan cursor.fetchall() conn.close() # 检查是否含 SEARCH 且不含 SCAN return any(SEARCH in line[3] for line in plan) and not any(SCAN in line[3] for line in plan) # 验证 is_optimized check_index_usage(university.db, SELECT * FROM instructor WHERE dept_name Comp. Sci.;) print(fdept_name 索引生效: {is_optimized}) # True 表示优化成功6.3 教材未提但致命的索引陷阱LIKE查询的前缀依赖Exercise 7.8 中name LIKE %John%是模糊查询但教材答案未建索引。若你建了INDEX(name)EXPLAIN QUERY PLAN仍显示SCAN TABLE borrower——因为%John%是通配符前置B-tree 索引无法使用。此时正确做法是改写查询为name LIKE John%若业务允许或使用 SQLite 的FTS5全文索引CREATE VIRTUAL TABLE borrower_fts USING fts5(name, address);绝不能盲目建INDEX(name)并认为“已优化”。后悔药我在一个课程设计项目中曾为description LIKE %error%建了普通索引上线后慢查询暴增。后来用EXPLAIN QUERY PLAN发现始终SCAN才换成 FTS5。这个教训让我养成习惯每建一个索引必用EXPLAIN QUERY PLAN验证其是否真被使用。不是“建了就有效”而是“EXPLAIN显示有效才算数”。希望帮到你。本文还有配套的精品资源点击获取