数据库规范化.ppt
例1：有一关系模式：供应商（供应商编号、供应商名称，供应商地址，供应商电话，供货名称，供货单价）；一个供应商可以供应多种货物，同一种货物也可以由多个供应商供应；1. 写出该关系模式的函数依赖集F ？2. 评价该关系模式，并举例说明是否存在下列问题数据冗余 ；更新异常；插入异常 ；删除异常 3. 如何改造该关系模式？关系模式关系模式TCSTCS（T T，C C，S S），），一个教师可以讲授多门课程，一门课程可以为多个教一个教师可以讲授多门课程，一门课程可以为多个教师讲授，师讲授，同样一个学生可以选听多门课程，一门课程可以为多同样一个学生可以选听多门课程，一门课程可以为多个学生选听，个学生选听，(T,C,S)(T,C,S)三个属性的组合是码，三个属性的组合是码，T,C,ST,C,S都是主属性，而无都是主属性，而无非主属性，所以也就不可能存在非主属性对码的部分非主属性，所以也就不可能存在非主属性对码的部分函数依赖，函数依赖，TCSTCS 2NF2NF。经以上分析，可以得到两个结论：经以上分析，可以得到两个结论：1 1从从1NF1NF关系中消除非主属性对码的部分函数依赖，关系中消除非主属性对码的部分函数依赖，则可得到则可得到2NF2NF关系。关系。2 2如果如果R R的码为单属性，或的码为单属性，或R R的全体属性均为主属性，的全体属性均为主属性，则则R R 2NF2NF。 2nf分解方法（1）用组成主键的属性集合的每一个子集作为主键构成一个表。（2）将依赖于这些主键的属性放置到相应的表中。（3）最后去掉只由主键的子集构成的表。 分解S-C-D表 （1）将该表分解为如下的三张表（下划线部分表示主键）：SD（Sno，）C（Cno，）SC（Sno, Cno,）分解S-C-D表（续） （2）将依赖于这些主键的属性放置到相应的表中，形成如下三张表： SD（Sno，Sn, Age, Dept, MN） C（Cno） SC（Sno, Cno, Score） （3）去掉只由主键的子集构成的表，也就是去掉C（Cno）表。SCD关系模式最终被分解的形式为： SD（Sno，Sn, Age, Dept, MN） SC（Sno, Cno, Score）分解后的函数依赖关系分解后的关系模式的函数依赖关系： SD：SnofDept，SnofSn SnofAge， SnofMn 是是2NF SC:（Sno, Cno）fSCORE：是：是2NF SD(SNO,SN,AGE,DEPTSD(SNO,SN,AGE,DEPT，MN)MN)，描述学生实体；，描述学生实体； SC(SNO,CNO,SCORE)SC(SNO,CNO,SCORE)，描述学生与课程的联系。，描述学生与课程的联系。SNOSNOSNSNAGEAGEDEPTDEPTMNMNS1S1赵亦赵亦1717计算机计算机刘伟刘伟S2S2钱尔钱尔1818信息信息王平王平S3S3孙珊孙珊2020信息信息王平王平S4S4李思李思2121自动化自动化刘伟刘伟SCSC SNOSNOCNOCNOSCORESCORES1S1C1C19090S1S1C2C28585S2S2C5C55757S2S2C6C68080S2S2C7C7 S2S2C5C57070S3S3C1C10 0S3S3C2C27070S3S3C4C48585S4S4C1C19393关系关系SDSD和和SCSC SD例例将将SD(SNO,SN,AGE,DEPT,MN)规范到规范到3NF。 分析分析SD的属性组成，可以判断，关系的属性组成，可以判断，关系SD实际上描述了实际上描述了两个实体：两个实体： 一个为学生实体，属性有一个为学生实体，属性有SNO，SN，AGE，DEPT； 另一个是系的实体，其属性另一个是系的实体，其属性DEPT和和MN。分解办法分解办法（1）对于不是候选码的每个决定因子，从表中删去依赖于它的）对于不是候选码的每个决定因子，从表中删去依赖于它的所有属性。所有属性。（2）新建一个表，新表中包含原表中所有依赖于该决定因子的）新建一个表，新表中包含原表中所有依赖于该决定因子的属性。属性。（3）将决定因子作为新表的主码。）将决定因子作为新表的主码。根据分解的原则，我们可以将根据分解的原则，我们可以将SD分解成如下两个关系，如图所分解成如下两个关系，如图所示。示。 S(SNO,SN,AGE,DEPT)，描述学生实体；，描述学生实体； D(DEPT，MN)，描述系的实体。，描述系的实体。S DSNOSNOSNSNAGEAGEDEPTDEPT DEPTDEPTMNMNS1S1赵亦赵亦1717计算机计算机 计算机计算机刘伟刘伟S2S2钱尔钱尔1818信息信息 信息信息王平王平S3S3孙珊孙珊2020信息信息 自动化自动化刘伟刘伟S4S4李思李思2121自动化自动化 对于分解后的两个关系对于分解后的两个关系S S和和D D，主键分别为，主键分别为SNOSNO和和DEPTDEPT，不存在非主属性对主键的传递函数依赖。因此，不存在非主属性对主键的传递函数依赖。因此，S S 3NF3NF，D D 3NF3NF。关系关系S S和和D D 属于3NF的关系模式一定是好模式？仓库号仓库号设备号设备号职工号职工号C1C1M1M1W1W1C1C1M2M2W1W1C1C1M3M3W2W2C1C1M4M4W2W2C2C2M5M5W3W3C3C3M6M6W4W4例例1：建立一个描述仓库管理的关系模式：建立一个描述仓库管理的关系模式 管理（仓库号，设备号，职工号）管理（仓库号，设备号，职工号） 3NF 语义：一个仓库有若干职工，一个职工仅在一个仓库工作语义：一个仓库有若干职工，一个职工仅在一个仓库工作 一个仓库可存放多种设备，一种设备可放在多个仓库一个仓库可存放多种设备，一种设备可放在多个仓库 一个仓库的一种设备仅由一名职工保管一个仓库的一种设备仅由一名职工保管 一个职工可以保管多种设备一个职工可以保管多种设备 函数依赖：职工号函数依赖：职工号仓库号仓库号 (仓库号，设备号仓库号，设备号) 职工号职工号 仓库号仓库号 设备号设备号职工号职工号 起因于主属性对非主属性的函数依赖起因于主属性对非主属性的函数依赖 分析：存在异常现象分析：存在异常现象属于3NF的关系模式一定是好模式？例例2：关于学生：关于学生(S)-教师教师(T)-课程课程(C)的关系模式的关系模式 STC（S，T，C） 3NF 语义：每个教师只教一门课程，每门课程有若干教师教语义：每个教师只教一门课程，每门课程有若干教师教 某一学生选定某门课程就对应于一个确定的教师某一学生选定某门课程就对应于一个确定的教师 函数依赖：函数依赖：T C (S，C)T (S，T)C 候选码：候选码：(S，C)和和(S，T) C S T T S C 分析：存在异常现象分析：存在异常现象 起因于主属性对码的部分和传递函数依赖起因于主属性对码的部分和传递函数依赖T TC CS ST1T1C1C1S1S1T1T1C1C1S2S2T2T2C1C1S3S3T2T2C1C1S4S4T3T3C2C2S2S2T4T4C2C2S2S2T4T4C3C3S2S2TCS的异常1数据冗余。数据冗余。虽然每个教师只开一门课，但每个选修该教师该该门课程的学虽然每个教师只开一门课，但每个选修该教师该该门课程的学生元组都要记录这一信息。生元组都要记录这一信息。2插入异常。插入异常。当某门课程本学期不开，自然就没有学生选修。没有学生选修，当某门课程本学期不开，自然就没有学生选修。没有学生选修，因为主属性不能为空，教师上该门课程的信息就无法插入。同样原因，学生因为主属性不能为空，教师上该门课程的信息就无法插入。同样原因，学生刚入校刚入校,尚未选课，有关信息也不能输入。尚未选课，有关信息也不能输入。3删除异常。删除异常。如果选修某门课程的学生全部毕业，删除学生记录的同时如果选修某门课程的学生全部毕业，删除学生记录的同时,随随之也删除了教师开设该门课程的信息。之也删除了教师开设该门课程的信息。4更新异常。更新异常。当某个教师开设的某门课程改名后，所有选修该教师该门课程当某个教师开设的某门课程改名后，所有选修该教师该门课程的学生元组都要进行修改，如果漏改某个数据，则破坏了数据的完整性。的学生元组都要进行修改，如果漏改某个数据，则破坏了数据的完整性。实例模式的分解管理（仓库号，设备号，职工号）管理（仓库号，设备号，职工号） 3NF BCNF 可分解为：可分解为： 职工（职工号，仓库号，职工（职工号，仓库号，职工号职工号仓库号）仓库号） BCNF 存放（仓库号，设备号）存放（仓库号，设备号） BCNF 管理（管理（职工号，职工号，设备号）设备号） BCNFSTC（ S，T，C ） 3NF BCNF 可分解为：可分解为： ST（ S，T ） BCNF TC（ T，C，T C ） BCNF注意：以上分解可以保证无损连接，不能保持函数依赖。注意：以上分解可以保证无损连接，不能保持函数依赖。总结：总结：BCNF在函数依赖的范畴内，已实现了彻底的分解。在函数依赖的范畴内，已实现了彻底的分解。 关系模式关系模式TCS由规范到由规范到BCNF后，使原来存在的四个异常问题得到解决。后，使原来存在的四个异常问题得到解决。 1数据冗余降低。数据冗余降低。每个教师开设课程的信息只在每个教师开设课程的信息只在TC关系中存储一次。关系中存储一次。 2不存在插入异常。不存在插入异常。对于所开课程尚未有学生选修的教师信息可以直对于所开课程尚未有学生选修的教师信息可以直接存储在关系接存储在关系TC中，而对于尚未选修课程的学生可以存储在关系中，而对于尚未选修课程的学生可以存储在关系ST中。中。 3不存在删除异常。不存在删除异常。如果选修某门课程的学生全部毕业，可以只删除如果选修某门课程的学生全部毕业，可以只删除关系关系ST中的相关学生记录，而不影响系关系中的相关学生记录，而不影响系关系TC中相应教师开设该门课中相应教师开设该门课程的信息。程的信息。 4不存在更新异常。不存在更新异常。当某个教师开设的某门课程改名后，只需修改关当某个教师开设的某门课程改名后，只需修改关系系TC中的一个相应元组即可，不会破坏数据的完整性。中的一个相应元组即可，不会破坏数据的完整性。 如果一个关系数据库中所有关系模式都属于如果一个关系数据库中所有关系模式都属于3NF，则已在很大程度上消除，则已在很大程度上消除了插入异常和删除异常，但由于可能存在主属性对候选键的部分依赖和传了插入异常和删除异常，但由于可能存在主属性对候选键的部分依赖和传递依赖，因此关系模式的分离仍不够彻底。递依赖，因此关系模式的分离仍不够彻底。 如果一个关系数据库中所有关系模式都属于如果一个关系数据库中所有关系模式都属于BCNF，那么在函数依赖的范，那么在函数依赖的范畴内，已经实现了模式的彻底分解，消除了产生插入异常和删除异常的根畴内，已经实现了模式的彻底分解，消除了产生插入异常和删除异常的根源，而且数据冗余也减少到极小程度。源，而且数据冗余也减少到极小程度。 例例 关系模式关系模式C（Cno，Cname，Pcno）码为码为CNOC3NFCBCNF例例 关系模式关系模式S（Sno，Sname，Sdept，Sage）假定假定S有两个码有两个码Sno，Sname (不重名的情况不重名的情况)S3NF。S BCNF例关系模式例关系模式SJP（S，J，P）学生学生S选修课选修课J有一确定名次有一确定名次P（设名次无并列）（设名次无并列）函数依赖：（函数依赖：（S，J）P；(J，P）S（S，J）与（）与（J，P）都可以作为候选码）都可以作为候选码,属性相属性相交交SJP3NF，SJPBCNF例例在关系模式在关系模式STJ（S，T，J）中，）中，S表示学生，表示学生，T表示表示教师，教师，J表示课程。表示课程。学生可选多门课，一门课有多个老师，每个老师只上一门课。学生可选多门课，一门课有多个老师，每个老师只上一门课。函数依赖：函数依赖： (S，J)T，(S，T)J，TJ(S，J)和和
