数据库范式例题实战：从1NF到BCNF的判断与分解

去年帮一个学弟复习数据库期末考试他抱着范式那章的习题册愁眉苦脸说定义背得滚瓜烂熟一拿到新表还是不知道从哪下手。我问他拿到题目第一步干什么他说“看它属于第几范式”。问题就出在这——数据库范式例题的正确打开方式不是先给表定级而是先做“底层排查”。这篇东西就想解决这一个问题把范式判断从“背定义”变成“按流程执行”。我会用一组有代表性的数据库范式例题把从1NF到BCNF的判断、分解、验证过程完整拆开每一步用的什么逻辑、踩过哪些坑、考试怎么答才不丢分统统讲清楚。备考期末、准备面试、或者自学数据库设计的朋友都能直接照着这套流程上手。1. 范式不是背出来的先用一个真实反例理解“为什么拆表”很多人学范式觉得抽象是因为把范式当成了“数据库的伦理道德”觉得它是强加的一套规矩。实际上范式完全是实用主义的产物——它解决的是数据冗余和更新异常这两个真实到不能再真实的存储问题。我们先不讨论理论只看一张设计得很差劲的表。1.1 一张“填鸭式”订单表藏着三类隐患假设你要做一个电商系统为了省事把订单全部信息塞进一张表订单号、客户名、客户电话、商品名、商品价格、数量同一客户下了三个订单买了三件不同商品那么客户名和客户电话会在三行里重复存储。这时候问题就来了修改异常这位客户换手机号了你得把涉及他的所有行全部改一遍漏改一行数据就前后矛盾。这叫更新异常。插入异常一个新客户刚注册还没下单他的信息根本没法录入因为订单号是主键主键不能为空。这叫插入异常。删除异常某个客户只下过一单后来订单被取消了你删除这条订单记录客户的基本信息也跟着一起没了。这叫删除异常。范式理论就是针对这类问题给出的整改方案每一个等级处理一类问题。1.2 范式等级的本质一张“问题排查清单”从这里开始请你把范式理解成一份“逐级排查清单”而不是一堆孤立定义1NF表中每个属性都不可再分所有属性都是原子的。这是数据库表的最基本底线。2NF在1NF基础上消除非主属性对候选码的部分函数依赖。3NF在2NF基础上消除非主属性对候选码的传递函数依赖。BCNF在3NF基础上要求每一个函数依赖的左边都必须包含候选码也就是左边必须是超码。注意这个层级关系满足BCNF一定满足3NF满足3NF一定满足2NF依此类推。反过来不成立。很多同学把“3NF比2NF要求更严格”理解成“3NF和2NF是两个独立的东西”这是考试丢分的第一步。再打个比方。如果候选码是一把能开全屋锁的总钥匙那么2NF查的是有没有某把房间钥匙脱离了总钥匙单独决定了一个非主属性部分依赖3NF查的是有没有通过“总钥匙→中间属性→非主属性”这种间接链条来开门的情况传递依赖BCNF查的是更狠的就算你决定的是主属性只要你的钥匙不是总钥匙一律不许通过。目标明确之后范式判断就有章法了。核心方法论都在下一节。2. 范式判断标准动作候选码、主属性、函数依赖三步走拿到一道范式题不管题干多长、表多复杂按三步走准没错。这三步是写全函数依赖、求候选码、逐级排查。大部分学生做错题不是概念不会而是这三步里的基本功出了问题。2.1 第一板斧把函数依赖写全、写对函数依赖用大白话说就是知道了X的值就能唯一确定Y的值记作X→Y。就像身份证号能唯一确定姓名这就是“身份证号→姓名”。如果你知道X但无法唯一确定Y那X→Y就不成立。写函数依赖集是整道题的基石这里有两个容易犯的错误。第一个漏写依赖。比如题目里给了“每个学生属于一个系每个系只有一个系主任”有些同学只写“学号→系名”忘了“系名→系主任”。这个依赖不写出来后面的传递依赖判断就全瞎了。第二个写了多属性依赖但不拆。比如(学号,课程号)→成绩这是对的因为成绩要靠学号和课程号两个一起才能确定。但有些同学会把(学号,课程号)→姓名也写上这就错了——姓名只要学号就能确定不需要课程号。多属性依赖的前提是左边任何一个真子集都无法确定右边属性否则就是冗余依赖需要拆开或删掉。判断函数依赖有个实用技巧从业务语义出发而不是从数据行出发。一份数据里碰巧没有重复不代表依赖关系成立反过来一份数据里碰巧有重复也可能是样本问题。要依据题目给定的语义约束来写。2.2 第二板斧用属性分类法求候选码候选码求错整个范式判断直接从根上崩掉。求候选码有标准方法叫属性分类法先给所有属性分四类L类只出现在函数依赖左边从不出现在右边。这类属性必属于候选码。R类只出现在函数依赖右边。这类属性必不属于候选码。LR类既出现在左边又出现在右边待定。N类不出现在任何函数依赖中。这类属性必属于候选码。一个简单例子的完整计算过程有关系R(A,B,C,D)函数依赖集F{B→D, D→C}求候选码。第一步分类。属性A不出现在任何依赖里属于N类必在候选码中。属性B只出现在B→D左边属于L类必在候选码中。属性C只出现在D→C右边属于R类必不在候选码中。属性D既出现在B→D右边又出现在D→C左边属于LR类待定。第二步拿着已知必选的属性求闭包。闭包就是从已知属性出发沿着所有能推出的依赖不断扩展可达的属性集合。当前已知A和B必选计算{A,B}的闭包由B→DD可加入由D→CC可加入。最终{A,B}的闭包等于全集{A,B,C,D}所以候选码就是AB。这个例子一次就凑齐了属于运气好。更多时候会遇到{A,B}的闭包凑不齐全集的情况这时就要从LR类属性里逐个尝试加入。举一个稍复杂的例子关系R(A,B,C,D,E)函数依赖集F{A→BC, CD→E, B→D, E→A}求候选码。分类一下。C只出现在CD→E左边属于L类必选。A、B、D、E四个属性都既出现在左部又出现在右部属于LR类。N类没有。先算{C}的闭包只有C本身不够。接下来依次尝试加入LR类属性。加A{A,C}的闭包根据A→BCB和C已可推出由B→DD可推出由CD→EE可推出。闭包达到全集所以AC是候选码。加E{C,E}的闭包由E→AA可推出由A→BCB和C可推出由B→DD可推出。闭包也达到全集所以CE是候选码。加B{B,C}的闭包由B→DD可推出但A和E推不出来不够。加D{C,D}的闭包由CD→EE可推出但A和B推不出来不够。于是得到候选码{AC, CE}。主属性是A、C、E非主属性是B、D。这个例子完整展示了LR类属性的试凑流程是非常典型的训练题。2.3 第三板斧逐级排查部分依赖和传递依赖候选码一出来主属性、非主属性自然就清晰了。接下来按钉子户式排查第一步判断1NF。看看有没有属性还能再拆分。比如“联系方式”里既装手机号又装邮箱这就违反1NF。现在绝大多数表设计默认满足1NF考试也基本不会卡在1NF上。第二步判断2NF。找出所有非主属性看它们是否依赖于候选码的整体是否依赖于候选码的某个真子集。如果存在“候选码的真子集→非主属性”的依赖就存在部分依赖不满足2NF。第三步判断3NF。在候选码是单属性的情况下单个属性当然不会再有真子集2NF自动满足。这时要检查传递依赖是否存在非主属性Z以及中间属性Y满足“候选码→Y→Z”而且Y不能决定候选码Z是非主属性。一旦存在就不满足3NF。第四步判断BCNF。看所有函数依赖的左边是不是都包含候选码。只要有一个依赖的左边不是超码就不满足BCNF。这一步不看右边是主属性还是非主属性一视同仁。四步检查完答案自然出来。下面用三道例题把整条流程跑一遍你会发现范式题根本不玄乎就是“查字典”式操作。3. 数据库范式例题精讲从1NF到BCNF的完整判断流程这一节我会带大家完整跑三道题难度依次递增。每道题都按“先写依赖、再求候选码、逐级判断”的顺序走重点展示中间的推导过程。3.1 例题一考试成绩表连2NF都不满足题目给出一张成绩表R(学号, 姓名, 系名, 课程号, 课程名, 成绩)语义约束一个学生属于一个系一门课程只有一个课程名一个学生选一门课得到一个成绩。第一步写函数依赖集学号→姓名学号→系名课程号→课程名(学号,课程号)→成绩第二步求候选码。先看属性分类。学号出现在左部也出现在右部学号→姓名右边有学号吗没有学号→姓名右边是姓名学号→系名右边是系名课程号→课程名右边是课程名(学号,课程号)→成绩右边是成绩学号从不出现在任何右边所以学号属于L类。课程号也是L类。姓名、系名、课程名、成绩都只出现在右边属于R类。L类属性学号和课程号组合求闭包{学号,课程号}的闭包可以推出姓名、系名、课程名、成绩正好凑齐全集。候选码是(学号,课程号)。主属性是学号、课程号非主属性是姓名、系名、课程名、成绩。第三步逐级判断。1NF默认满足。检查2NF学号是候选码(学号,课程号)的真子集但学号→姓名成立这意味着非主属性姓名依赖于候选码的一部分存在部分函数依赖。同样课程号→课程名也属于非主属性对候选码真子集的部分依赖。因此不满足2NF。结论R属于1NF。这个例子是最常见的送分题也是最早让学生意识到“候选码是两列”的启蒙题。只要候选码不止一个属性就得立刻警觉部分依赖。3.2 例题二学生班级辅导员卡在3NF门口的经典案例题目给出一张学生信息表R(学号, 姓名, 班级, 辅导员)语义约束每个学生有唯一姓名每个学生属于一个班级每个班级配备一名辅导员。注意一位辅导员可能带多个班级但这里题目明确是“一个班级一名辅导员”所以依赖是班级→辅导员而不是辅导员→班级。第一步函数依赖集学号→姓名学号→班级班级→辅导员第二步求候选码。学号只出现在依赖左边属于L类。姓名、班级、辅导员的情况班级出现在学号→班级的右边也出现在班级→辅导员的左边属于LR类姓名和辅导员只出现在右边属于R类。因为学号属于L类必选先算{学号}的闭包由学号→姓名姓名加入由学号→班级班级加入再由班级→辅导员辅导员加入。闭包已是全集候选码就是学号。第三步逐级判断。候选码是单个属性不存在真子集自然没有部分依赖所以满足2NF。但检查3NF时发现学号→班级班级→辅导员学号通过班级这个中间属性间接确定了辅导员而且班级不能决定学号辅导员是非主属性。典型的传递依赖所以不满足3NF。结论R属于2NF。把表实际填几行数据就能直观看到问题同一个班的十个学生十行里辅导员重复了十次。解决思路就是拆表R1(学号,姓名,班级)和R2(班级,辅导员)分别存学生基本信息和班级辅导员映射。3.3 例题三城市街道邮编3NF与BCNF的分水岭这道题几乎是所有数据库教材里区分3NF和BCNF的必选案例。R(城市, 街道, 邮编)语义约束一个城市、一条街道确定唯一邮编同一个邮编必然对应同一个城市。注意这里没有“一个邮编唯一对应一条街道”一个邮编通常包含多条街道。第一步函数依赖集(城市,街道)→邮编邮编→城市第二步求候选码。先分类。城市出现在(城市,街道)→邮编左边也出现在邮编→城市右边属于LR类。街道只出现在(城市,街道)→邮编左边属于L类。邮编只出现在(城市,街道)→邮编右边不对邮编出现在(城市,街道)→邮编右边也出现在邮编→城市左边所以邮编属于LR类。街道是L类必选。算{街道}的闭包只有街道自己不够。尝试加入城市{街道,城市}的闭包由(城市,街道)→邮编邮编加入闭包为全集。所以(城市,街道)是一个候选码。再尝试加入邮编{街道,邮编}的闭包由邮编→城市城市加入由(城市,街道)→邮编邮编已在。闭包为全集。所以(街道,邮编)是另一个候选码。候选码有两个(城市,街道)和(街道,邮编)。主属性是城市、街道、邮编——所有属性都是主属性。非主属性为空。第三步逐级判断。1NF满足。候选码没有真子集非主依赖满足2NF。检查3NF时3NF对依赖的要求是每个依赖X→A要么X包含候选码要么A是主属性。看邮编→城市邮编不包含候选码邮编单独无法确定街道所以邮编不是超码但城市是主属性所以这条依赖“右边是主属性”的豁免条款生效满足3NF。再看BCNFBCNF对依赖的要求是每个依赖X→A中X必须是超码。邮编→城市中邮编不是超码违反BCNF。结论R满足3NF但不满足BCNF。这就是3NF和BCNF最核心的区别3NF允许“依赖左边不含候选码”的情况存在只要右边是主属性就行BCNF直接一刀切不允许任何依赖左边缺候选码。这道题把两者的边界切得清清楚楚。4. 范式分解实操3NF合成法与BCNF分解法判断出范式等级只是第一步考试和面试里真正拉分的题目是如何把不达标的表分解成符合要求的多个表。分解算法有两个主流方向BCNF分解和3NF合成它们的思路截然不同使用场景也不同。4.1 BCNF分解的正确姿势用依赖“撞开”超码检查BCNF分解法也叫分解法核心思路是找到一条违反BCNF的依赖X→A把表劈成两个模式其中一个装X和A另一个装X和除A之外的所有属性然后递归处理。用3.3节的城市街道邮编例子R(城市, 街道, 邮编)违反BCNF的依赖是邮编→城市。按规则分解R1(邮编, 城市)R2(邮编, 街道)检查R1函数依赖邮编→城市邮编在R1里是候选码满足BCNF。检查R2R2只有邮编和街道两个属性候选码是(邮编,街道)没有任何非主属性也不存在依赖左边不含候选码的情况满足BCNF。分解完成。验证无损连接性R1和R2的交集是邮编而邮编是R1的候选码——两模式交集能唯一标识R1中的一个元组必然可以在连接时一一对应不会产生多余的“幽灵行”所以这是无损分解。这里我要特别强调一点BCNF分解一定是无损分解但不一定保持函数依赖。也就是说分解后的各模式上函数依赖的并集可能无法推出原来的全部依赖。最典型的反例是R(A,B,C)函数依赖集F{AB→C, C→B}。R的候选码是AB和ACC→B中C不含候选码违反BCNF。分解成R1(A,C)和R2(B,C)R1和R2上都没有非平凡函数依赖原来的AB→C丢失了。这种分解在数据一致性上是有代价的。所以生产环境里不一定非BCNF不可3NF往往是更务实的终点。4.2 3NF无损且保持依赖分解的五个步骤3NF合成法是一套“保底算法”保证分解结果既无损又保持函数依赖。步骤如下求函数依赖集F的最小函数依赖集右边都是单属性左边没有多余属性没有冗余依赖。把所有左边相同的函数依赖合并每一组依赖的属性组成一个模式。如果某个模式的属性集是另一个模式属性集的子集去掉这个冗余模式。检查候选码如果候选码没有被任何已有模式包含单独为候选码增加一个模式。合并所有模式输出结果。用一个稍复杂的例题完整走一遍。R(A,B,C,D,E)函数依赖集F{A→B, A→C, C→D}。求3NF无损且保持依赖分解。第一步检查最小依赖集。右边都是单属性左边的A→B、A→C里没有冗余属性可以删也没有依赖能被其他依赖推出。已经是极小化状态。第二步按左边相同合并。A→B和A→C合成一个模式(A,B,C)C→D单独成为一个模式(C,D)。第三步检查候选码。属性A是L类必选E不在任何依赖中属于N类必选所以候选码是AE。当前已有模式(A,B,C)和(C,D)属性E没有被任何模式包含所以需要增加模式(A,E)。第四步检查冗余。现有三个模式(A,B,C)、(C,D)、(A,E)互不包含保留。最终分解结果R1(A,B,C)、R2(C,D)、R3(A,E)。验证R1上依赖A→B、A→C成立候选码A满足BCNFR2上依赖C→DC是候选码满足BCNFR3上单属性A和E没有非平凡依赖满足BCNF。整个分解无损且保持依赖。这个例子里的关键教训是如果漏算了N类属性E会把候选码求错分解结果也会跟着出错所以第四步“候选码单独补模式”很容易被忽略却极其重要。4.3 验证分解质量无损连接性和函数依赖保持怎么查两个分解模式的时候无损连接性可以直接用“交集是否为其中一个模式的候选码”来判断。多于两个模式时用表格法chase算法更稳妥。表格法做法不复杂构造一张行为分解模式、列为属性的表格每个模式对应的属性列填记号不属于的留空然后逐条依赖把左边相同属性值的行右边属性值也统一成相同记号。如果最终某一行填满了所有属性说明分解无损。保持函数依赖的验证更简单把分解后每个模式上的函数依赖投影出来求并集再判断原依赖集中的每条依赖能否由这个并集的闭包推出。能推出就保持推不出就丢了。实际考试里只要用4.2节的3NF合成法结果天然满足保持依赖一般不需要再额外验证。5. 考试和面试中的高频易错点这些坑我帮你踩过了最后这部分是我自己教书和改作业时反复遇到的真实错误也是你最容易丢分的地方。每一条都是血泪总结。5.1 候选码求错后面全白算候选码是整道题的地基。我见过太多学生在一道看似复杂的题里直接“猜”候选码比如看到A→B就默认A是候选码完全忽略还有E这种孤立属性。前面已经示范过N类和L类属性的处理这里再强调一遍凡是L类和N类属性都是候选码的“钉子户”必须先拉进候选码再谈其他。如果L类和N类闭包不够再逐层尝试LR类属性不要跳步。检查候选码求对没有有个快速自测方法所有候选码的属性个数应该相等更准确地说每个候选码的基数相同且一个候选码不能是另一个候选码的真子集。5.2 传递依赖的边界条件最容易混淆X→YY→Z凭这三条能不能判定X→Z是传递依赖答案是不一定。必须同时满足Y不能决定X。如果Y→X也成立那么X和Y互相决定它们本质上是等价键X→Z就不算传递依赖。很多教材里的反例都藏这一手。再补充一个3NF的判断标准是“不存在非主属性对候选码的传递依赖”。如果依赖链的最终属性是主属性那这条链不违反3NF本文3.3节城市街道邮编案例就是活生生的例子。判断前先把主属性和非主属性分好再对照定义。5.3 标准化答题流程这样写不丢分阅卷和面试官最看重推导过程是否完整。即使你一眼就能看出答案是第几范式也请按下面流程写明确写出函数依赖集F。用属性分类法写清楚L、R、LR、N类求候选码列出候选码和主属性集合、非主属性集合。逐级判断每一步写清楚是否存在部分依赖是否存在传递依赖每条依赖左边是否为超码给出对应结论。如果题目要求分解写明用了什么算法分解结果是什么再验证无损连接性和依赖保持性。哪怕最终范式等级判断错了只要推导过程逻辑严密阅卷老师也会给步骤分——这是我自己参加统考阅卷时亲眼验证过的规则。最后再分享一个小技巧平时练习范式题不要只做“判断范式等级”这种单一题目一定要追着自己问“如果不满足怎么拆”。把拆表思路想清楚考试时即使题目换个问法也难不倒你。数据表重构这件事熟练之后会在真正的项目设计里让你少走不少弯路。