qinghuan
← 数据系统 / 全部笔记

单元 12 / 数据系统

P0 · 必会主线L0 · 零基础

从零设计关系表:先确定事实,再添加约束

先明确每行记录的事实,再用主键、唯一、外键和必填规则保护数据,区分无意重复与必要的历史记录。

本篇目录 · 按需要跳转

读完这篇,你能做什么

能从订单需求画出表关系,并用约束拒绝重复或不完整的数据。

学习理由、准备要求与知识点

为什么学它

表结构决定数据能否保持正确,也是后续 SQL、事务和项目练习的共同起点。

先读这些内容

知识点清单

  • 实体与关系
  • 主键与候选键
  • NOT NULL
  • 唯一约束
  • 外键动作
  • 第一至第三范式
  • 金额精度
  • 业务快照

运行前先确认MySQL8.4、InnoDB与Java17。Connector/J9.0.0用于解释主键元数据;闭包算法不从样本数据推断业务规则。

01先弄懂它是什么

设计表时先写一句话:每一行代表什么?订单表一行是一笔订单,明细表一行是其中一项商品。商品现在叫什么,和下单时叫什么,是两个不同时间的事实。先分清记录的对象和时间,才能判断重复字段是设计失误,还是为了保留历史必须保存的内容。

主键用来稳定识别一行;候选键是一组足以确定整行、且不能再删减的字段。增加人工 id 并不意味着其他字段可以任意重复:外部请求号、订单中的明细序号等仍可能需要唯一约束。外键检查引用的记录存在,NOT NULL 规定必填,字段类型、长度和 CHECK 则可以限制其他基本数据规则。

范式用来减少这样的麻烦:同一个事实写在多行,改了一处却漏了另一处。第一范式要求每个字段按选定模型表示一个值,例如不要把多条订单项用逗号塞进一格。第二范式要求:不属于任何候选键的字段,不能只由某个候选键的一部分决定。

第三范式仍按严格条件判断:每条非平凡依赖 X→A(X 能确定不在 X 中的字段 A),要么 X 能确定整行,是超键;要么 A 属于某个候选键,是主属性。把常见的“非键字段又决定另一个非键字段”拆出去,有助于理解,但不能代替涉及多个候选键时的完整判断。

文中这几个词,先认一认

函数依赖
业务规则保证 X 相同时 Y 一定相同,例如一个用户编号对应一个当前姓名;不能仅凭几行样本判断。
属性闭包
从某组字段出发,按已知依赖规则一路能确定出的全部字段。
候选键
能唯一确定整行的一组最少字段,再去掉其中任何一个就不够了。

还有陌生词?到术语小抄里看一个具体例子 ↗

02看看它怎么工作

设混合表包含order_id、line_no、product_id、product_name、qty、order_time。订单号决定时间,商品号决定当前商品名,订单号与行号共同决定商品和数量。每条明细重复订单时间会产生更新异常,每条商品重复当前名称也产生维护冲突;根因是不同事实有不同决定因素。

可以拆成订单、商品和订单明细三张表,再用编号关联。但如果 product_name 保存的是成交时的商品名称,它属于这条明细的历史事实,不应机械删掉。拆表还要检查两件事:重新 JOIN 后能否准确还原、不会凭空多出组合,这叫无损连接;重要约束能否继续有效检查,这叫依赖保持。范式帮助减少错误重复,不是表越多越好,也不保证查询总是最快。

在练习库先建父表再建子表,必填外键配NOT NULL并明确删除策略。金额使用精确表示,文本说明字符集与排序规则。用SHOW CREATE TABLE或JDBC元数据核验真正落地的结构;Java字段注释不会自动成为数据库约束。

正在加载流程图…

函数依赖来自业务,范式指导拆分,历史快照和查询冗余需明确维护责任。
查看流程图源码(Mermaid)
flowchart TD
 A["每行事实与时间含义"] --> B["业务函数依赖"]
 B --> C["候选键与唯一规则"]
 C --> D["识别更新异常"]
 D --> E["拆分或保留历史快照"]
 E --> F["落实数据库约束"]
 F --> G["核验真实结构与重组"]

按顺序看,每一步做了什么

  1. 定义每行含义,区分当前状态与历史事实。
  2. 列出业务唯一键及函数依赖。
  3. 计算闭包并检查候选键最小性。
  4. 拆分关系,检查重组是否无损。
  5. 落实约束并测试重复、缺失引用和非法值拒绝。

03对照实现代码

读取主键元数据与列序 (新标签页)↗

MySQL Connector/J9.0.0 · DatabaseMetaData#getPrimaryKeys、#getPrimaryKeysFields

原创整理模型,非源码节选。真实驱动查询服务器并转换JDBC结果;本模型只整理已获取行,不执行SQL或推断业务规则。

核心机制代码 · java
import java.util.*;
class SourceModel {
    record IndexRow(String keyName,String column,int sequence) {}
    static List<IndexRow> primary(List<IndexRow> rows) {
        List<IndexRow> selected=new ArrayList<>();
        for (IndexRow row:rows) {
            // 仅选已声明主键,普通索引不等同主键。
            if ("PRIMARY".equalsIgnoreCase(row.keyName())) selected.add(row);
        }
        // JDBC展示行次序不等同联合索引列次序。
        selected.sort(Comparator.comparing(IndexRow::column,String.CASE_INSENSITIVE_ORDER));
        // sequence保留真实列序,调用者需据此重建联合键。
        return List.copyOf(selected);
    }
}

真实方法通过SHOW KEYS读取信息,再筛选PRIMARY等名称,客户端不负责服务端唯一性裁决。

COLUMN_NAME与KEY_SEQ共同描述主键列;结果按列名排序,恢复联合键次序应看KEY_SEQ。

表名通过标识符引用机制处理,不是普通值占位符,体现SQL标识符与参数的区别。

元数据能告诉你数据库声明了什么结构,却不能证明业务要求都已表达。即使已经有 id 主键,用户和券等业务组合仍可能需要额外唯一约束。

代码先调用谁,再调用谁

  1. Connection.getMetaData
  2. getPrimaryKeys
  3. 执行SHOW KEYS
  4. 转换JDBC字段
  5. 读取COLUMN_NAME与KEY_SEQ

04动手写一个例子

位集合计算函数依赖闭包

最多26个属性用位表示,给出X→Y依赖,求起始集合闭包。拒绝越界位,支持循环依赖和空左部规则;闭包覆盖全部属性表示超键,候选键还需最小性判断。

输入 1
A→B、B→C,起点A
输出 1
ABC
输入 2
A→B、B→A,起点C
输出 2
C

从起始集合开始,若某规则左侧全部已知,就加入右侧。单次扫描不一定够,因为后面的规则可能提供前面规则尚缺的条件。

已知集合只增不减,最多增加有限个属性,因此循环依赖仍会收敛。停止时没有任何规则可以继续增加结果。

超键只要求覆盖全部属性,候选键还要求没有多余属性。算法结论依赖输入规则真实,不能用几行样本碰巧唯一替代业务确认。

为什么这样写是对的已知集合内每项均可由起始集合和规则推出;结束时所有已满足左侧的规则,其右侧也已包含。

时间 O(BF)额外空间 O(1)额外空间

B为属性数,F为规则数,每个有变化轮次至少增加一位,最多B轮,固定宽度位运算为常数。

保存为 Main.java,运行 javac --release 17 -encoding UTF-8 Main.java,再运行 java Main。程序包含固定输入自测,断言失败时会抛出错误。

展开收起代码 · Main.java · 完整可运行程序Java · 43 行

长行可在代码区域内左右滚动,按 Tab 可把焦点移入代码区。

Main.java · 完整可运行程序 · Java
import java.util.*;
public class Main {
    record FD(int left,int right) {}
    static int closure(int bits,int seed,List<FD> rules) {
        if (bits<0||bits>26) throw new IllegalArgumentException("bits");
        int all=(1<<bits)-1;
        if ((seed&~all)!=0) throw new IllegalArgumentException("seed");
        for (FD fd:rules)
            if (((fd.left()|fd.right())&~all)!=0) throw new IllegalArgumentException("rule");
        int known=seed;
        boolean changed;
        do {
            changed=false;
            for (FD fd:rules) {
                // 左部每一位已知,才能推出右部。
                if ((known&fd.left())==fd.left()) {
                    int expanded=known|fd.right();
                    if (expanded!=known) { known=expanded; changed=true; }
                }
            }
            // 只有实际新增属性才继续,循环依赖自然停止。
        } while (changed);
        return known;
    }
    static void eq(int expected,int actual) {
        if (expected!=actual) throw new AssertionError(actual);
    }
    public static void main(String[] args) {
        // A、B、C对应第0、1、2位,规则故意逆序测试多轮推导。
        eq(7,closure(3,1,List.of(new FD(2,4),new FD(1,2))));
        eq(4,closure(3,4,List.of(new FD(1,2),new FD(2,1))));
        eq(3,closure(3,1,List.of(new FD(1,2),new FD(2,1))));
        eq(2,closure(3,0,List.of(new FD(0,2))));
        eq(0,closure(0,0,List.of()));
        try { closure(2,4,List.of()); throw new AssertionError("seed"); }
        catch (IllegalArgumentException expected) {}
        // O、L、P、Q、T、N依次对应订单、行、商品、数量、时间、名称。
        List<FD> detail=List.of(new FD(1,16),new FD(4,32),new FD(3,12));
        eq(63,closure(6,3,detail));
        eq(17,closure(6,1,detail));
        System.out.println("PASS 8 attribute closure tests");
    }
}

这些特殊情况也要试一试

  • 空属性空间只允许空集合;空左侧规则可推出常量。
  • 循环和重复规则不影响收敛,规则顺序不改变最终结果。
  • 闭包覆盖全集只证明超键,仍需检查最小性。
进阶选读 · 枚举候选键并解释分解

场景一个关系有多个候选键,只按选中的主键判断可能漏掉问题。

做法按集合大小枚举子集,计算闭包;覆盖全集且不包含已知候选键的集合可作为候选,再结合依赖检查无损连接与依赖保持。

复杂度朴素枚举O(2^B×BF),需要限制属性数并利用必需属性剪枝。

好处与代价算法只能检查给定规则的逻辑结果,错误业务依赖会得到形式精确却无用的结论。

05项目里怎么用

规范化帮助减少重复修改造成的错误,读得慢时可先评估索引和查询改写,确需冗余再规定维护办法。每个重复字段要说明以谁为准、怎样更新、最多延迟多久、出错怎样修复。订单成交金额和商品当前价格记录不同时间的事实,不能因为看似重复就删掉历史金额。

增加非空、唯一或外键约束前检查已有数据。历史脏数据可能使迁移失败,需要明确清理规则与变更影响,避免临时绕过约束变成永久行为。Java与数据库类型还要对齐精度、范围和空值,不能让默认零值掩盖缺失数据。

主键会影响索引占用和写入是否集中在附近位置,业务唯一键负责并发时阻止不允许的重复;人工 id 和业务唯一约束可以同时存在。删除和级联还要考虑历史引用、审计和恢复,不能只为少写代码就把关联记录都删掉。

容易踩的坑

  • 根据当前样本无重复推断永久函数依赖。
  • 添加id主键后认为自动达到第三范式。
  • 机械删除历史成交快照。
  • 按元数据返回次序误读联合主键列序。

几种做法,怎么选

表格有四列,窄屏可左右滑动查看代价和适用条件。

方案优势代价与限制适用条件
规范化关系减少更新异常。查询可能需要连接。核心事实和一致性约束。
历史快照保留过去时点事实。需明确不可变语义。订单、审计和结算。
查询冗余降低热点读取成本。需要同步与修复策略。收益已测量的读模型。

06检验是否读懂了

先自己想、自己写,再展开答案核对。场景题是假设的练习情境;遇到还没学过的内容,可以做完基础后再回来。

先用自己的话解释

01超键与候选键的区别?

展开参考答案与推导

候选键是没有多余属性的最小超键。

02id主键能阻止所有业务重复吗?

展开参考答案与推导

不能,外部单号或业务组合仍可能需唯一约束。

03函数依赖由几行样本决定吗?

展开参考答案与推导

不是,应对所有业务允许状态成立。

换个情况,还会处理吗

01商品改名使历史订单展示改变如何修正?

展开参考答案与推导

区分当前商品名与成交快照,明细保存其历史事实。

02新增唯一约束失败怎么办?

展开参考答案与推导

检查既有重复,按明确规则清理后进行受控迁移。

03联合主键元数据按字母排序如何还原?

展开参考答案与推导

按KEY_SEQ恢复索引列序。

写代码试一试

01为什么闭包需要重复扫描?

输入 / 条件
B→C在A→B之前,起点A
输出 / 预期
ABC

提示 后面规则提供前面条件。

展开参考答案与推导

持续到没有新增属性,避免只推导一轮。

02如何测试规则顺序无关?

输入 / 条件
相同规则的不同排列
输出 / 预期
闭包相同

提示 不动点结果。

展开参考答案与推导

用逆序链和多种排列计算并断言一致。

03如何检查键最小性?

输入 / 条件
ABC与AB都是超键
输出 / 预期
ABC不是候选键

提示 逐项移除属性。

展开参考答案与推导

移除某属性后闭包仍全覆盖,说明原集合有多余属性。

读完之后,独立完成

课后作业:自己做一遍

本篇共 10 道作业,先完成 8 道基础与综合任务,再按前置知识挑战进阶题。其中包含 1 道思维拓展,试着换个条件,看看原来的办法是否还管用。不附参考答案。

这次练什么

能从订单需求画出表关系,并用约束拒绝重复或不完整的数据。

本篇重点加练

唯一性规则与历史快照决定数据库能否保留稳定业务身份及可解释的历史事实。

  • 唯一约束
  • 业务快照

重点内容会多练几次。先说清楚怎么做,再试试空数据、出错或条件改变时会发生什么。

先自己做,卡住时记下问题,再回来查。问答题写清“为什么、什么时候适用”;动手题留下输入、输出和运行步骤,方便下次重做。

基础必做

  1. 作业 01原理问答

    设计校园借书记录:一本书有多个馆藏副本,读者可多次借同一副本。分别定义书目、副本、读者和借阅的身份及候选键。

    提交内容
    实体图与键清单
    怎样算完成
    • 区分业务对象与一次行为
    • 键的依据不能只来自当前样本

    练到的知识点实体与关系 · 主键与候选键

  2. 作业 02代码推演

    注册表有可空邮箱与必填账号,产品允许暂不填写邮箱。列出空值、空字符串、重复账号和重复邮箱的输入组合,定义数据库与业务的预期契约。

    提交内容
    输入矩阵与约束草案
    怎样算完成
    • 明确 NULL 和空字符串口径
    • 注明数据库版本相关假设

    练到的知识点NOT NULL · 唯一约束

  3. 作业 03面试场景

    租赁账单采用“日价乘天数减优惠”,结算后允许商品调价。制定金额存储、舍入时点与账单快照字段。

    提交内容
    计价契约与边界样本
    怎样算完成
    • 注明金额单位和精度
    • 覆盖零价、折扣及数值范围

    练到的知识点金额精度 · 业务快照

  4. 作业 04代码推演

    会员账号采用不区分大小写的业务规则,外部系统却区分大小写。构造输入集合并评审不同排序规则下唯一约束应表达哪个身份域。

    提交内容
    身份规则及约束样例
    怎样算完成
    • 注明数据库字符集和排序规则
    • 包含空白及 Unicode 样本

    练到的知识点唯一约束

综合应用

  1. 作业 05设计评审

    借阅表重复保存读者班级和班主任,班级调整后历史信息需可解释。分析依赖关系,评审删除读者时的关联动作。

    提交内容
    依赖图与迁移提案
    怎样算完成
    • 区分当前信息与历史快照
    • 包含仍有未归还记录的删除请求

    练到的知识点外键动作 · 第一至第三范式

  2. 作业 06编程实践

    在独立本地 MySQL 库实现精简借阅模型,写入合法与冲突数据并导出约束验证记录;随后修改读者信息验证历史查询口径。

    提交内容
    DDL、数据脚本和观察报告
    怎样算完成
    • 每类约束有失败用例
    • 记录初始化与清理步骤

    练到的知识点实体与关系 · 主键与候选键 · NOT NULL · 唯一约束 · 外键动作 · 第一至第三范式 · 金额精度 · 业务快照

  3. 作业 07设计评审

    电子发票需保存开票时税率,但客户地址更正后又要求显示最新地址。逐字段区分历史事实、当前引用和允许修订的记录。

    提交内容
    字段时间语义表
    怎样算完成
    • 不对所有字段一律快照
    • 保留更正的可追溯要求

    练到的知识点业务快照

  4. 作业 08编程实践

    在独立本地 MySQL 建立版本化价格与成交快照小模型,尝试并发版本新增和历史字段更正,记录约束实际覆盖范围。

    提交内容
    DDL、操作记录与边界说明
    怎样算完成
    • 初始数据及连接设置固定
    • 不把约束通过当作业务口径正确

    练到的知识点唯一约束 · 业务快照

进阶挑战 · 掌握前置后选做

  1. 作业 09设计评审

    旧系统允许重复借阅流水号,新系统拟使用它作为业务键。制定数据核查、冲突处理和分阶段上线方案。

    提交内容
    迁移检查单
    怎样算完成
    • 不凭增加约束消除旧冲突
    • 保留历史引用和回滚边界

    练到的知识点主键与候选键 · 唯一约束 · 外键动作 · 第一至第三范式 · 业务快照

思维拓展

先写出你的猜想,再用例子或实验验证。答案可以不同,但要说清依据、适用情况,以及还有什么没弄懂。

  1. 作业 10思维拓展 · 跨域迁移

    迁移到科研数据:同一样本允许重复测量,仪器校准后旧结果可能需要重新解释。唯一身份与历史快照规则应怎样调整?

    提交内容
    跨域数据契约
    怎样算完成
    • 区分观测身份和样本身份
    • 包含结果修订及原始事实保留

    练到的知识点唯一约束 · 业务快照

完成后,再检验一次

给每题标记“独立完成 / 查阅后完成 / 尚未完成”。查过资料或没做出的题,记下卡在哪里。隔一天换一组输入再试,能解释结果和出错原因,再把这个知识点标为掌握。

继续阅读

先看能帮你完成当前练习的部分。例子跑通后,再读更深入的解释。

打开资料库,找下一份学习材料 ↗