数据库系统（三）--范式与表的设计原则

一 、关系模式可能会存在的问题： 

       1. 数据冗余

  　  2. 更新异常 　

　    3. 插入异常

　　 4. 删除异常

 

二、相关概念

　　1、实体：现实世界中客观存在并可以被区别的事物。这里所说的“事物”不仅仅是看得见摸得着的“东西”，它也可以是虚拟的，比如说“老师与学校的关系”。

　　2、属性：实体所具有的某一特性。

　　3、分量：元组的某个属性值。在一个关系数据库中，它是一个操作原子，即关系数据库在做任何操作的时候，属性是“不可分的”。否则就不是关系数据库了。

　　4、码/键：表中可以唯一确定一个元组的某个属性（或者属性组）。

　　5、候选码或候选键：如果在关系的一个码或键中，不能从中移去任何一个属性，否则它就不是这个关系的码或键。则称这样的码或键为该关系的候选码或候选键。一个关系的候选码或候选键是这个关系的最小超码或超键。

　　6、全码/全键：如果一个码包含了所有的属性，这个码就是全码。

　　7、主码或主键：在一个关系的若干个候选码或候选键中指定一个用来唯一标识关系的元组，则 称这个被指定的候选码或候选键为该关系的主码或主键。

　　8、主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。

　　9、非主属性：与主属性相反，没有在任何候选码中出现过，这个属性就是非主属性。

　　10、外码/外键：一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。

　　11、域：域表示属性的取值范围。

　　12、参照关系： 参照关系也称为从关系，被参照关系也称为主关系，它们是指以外码相关联的两个关系。外码所在的关系称为参照关系。

　　13、被参照关系：以外码作为主码的关系称为被参照关系；被参照关系与参照关系是通过外码相联系的，这种联系通常是一对多的 联系。

　   14、关系模式：同数据模型一样，数据库也有型和值之分。在关系数据库中，关系模式是型， 关系是值，即关系模式是对关系的描述。关系模式是静态的、稳定的，而关系是动态的、随时间不断变化的。这是因为关系操作在不断地更新着数据库中的数据。 人们常常会把关系模式和关系都笼统地称为关系。

　   15、函数依赖：指关系中属性间的对应关系。  设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中的属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X，记作 X→ Y。其中 X 称为决定因素。 或 设X,Y是关系R的两个属性集合，当任何时刻R中的任意两个元组中的X属性值相同时，则它们的Y属性值也相同，则称X函数决定Y，或Y函数依赖于X。

      16、 完全函数依赖：设 R 为任一给定关系，X、Y 为其属性集，若 X→Y，且对 X 中的任何真子集 X ，都有 X ，Y，则称 Y 完全函数依赖于 X。

      17、部分函数依赖： 设 R 为任一给定关系，X、Y 为其属性集，若 X→Y，且 X 中存在一个真子集 X ，满足 X →Y，则称 Y 部分函数依赖于 X。

      18、传递函数依赖：设 R 为任一给定关系，X、Y、Z 为其不同属性子集，若 X→Y，Y X，Y→Z，则有 X→Z，称为 Z 传递函数依赖于 X。 知识点五 定义 2.5 关键字的定义 定义 2.5 设 R 为任一给定关系，U 为其所含的全部属性集合，X 为 U 的子集， 若有完全函数依赖 X→U，则 X 为 R 的一个候选关键字。

　　19、数据依赖：在计算机科学中，数据依赖是指一种状态，当程序结构导致数据引用之前处理过的数据时的状态。其中最重要的是函数依赖和多值依赖。

　　20、平凡函数依赖：当关系中属性集合Y是属性集合X的子集时(Y?X)，存在函数依赖X→Y，即一组属性函数决定它的所有子集，这种函数依赖称为平凡函数依赖。

　　21、非平凡函数依赖 、：当关系中属性集合Y不是属性集合X的子集时，存在函数依赖X→Y，则称这种函数依赖为非平凡函数依赖。

　　22、多值依赖：设R(U)是属性集U上的一个关系模式。X，Y，Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立，当且仅当对R(U)的任一关系r,给定的一对(x，z)值有一组Y的值，这组值仅仅决定于x值而与z值无关。 平凡的多值依赖与非平凡的多值依赖： 若X→→Y,而Z为空集，则称X→→Y为平凡的多值依赖；若Z不为空，则称其为非平凡的多值依赖。 

 

三、范式

  0、基础概念

     范式：即在关系型数据库中定义规范（优化数据数据存储方式、数据的存储性能等）就可以称为范式。

　 规范化：一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系 模式的集合，这种过程就叫规范化。

  1、第一范式：属性列不能再分（数据达到原子性，不可再分）。

　　   eg：

　　　　　　

　　 上图违反了第一范式，学生数据可再进行拆分，否则无法对姓名、性别、年龄等单独进行操作。

 

  2、第二范式：第二范式建立在第一范式的基础上，非主属性完全依赖于码。 

    　  即：消除部分依赖。

　　  说明：AB是R（A,B,C）的主键，那么如果有A->C，这就是一个部分函数依赖

　　  eg：

　　　　　　

学生	年龄	课程	学分	成绩	任课教师	课程课时
张三	18	java	2	80	胡	30
李四	19	c++	3	90	胡	40

 

　　　分析：

　　　　（1）该R的关键字（主键）为：学生 课程

　　　　　　R的函数依赖集：学生-->年龄； 学生+课程-->学分、成绩、年龄、任课教师等；即 年龄、学分、成绩、任课教师等 完全依赖于 学生 和 课程。

 　　　  （2）R违反了第二范式，属于第一范式

　　　　　　存在非主属性 年龄 依赖于 学生(学生-->年龄)，即非主属性（学生）对主属性（学生+课程）部分依赖。

　　　　（3）该关系模式R存在的问题

　　　　　　删除学生张三信息的同时会删除课程信息，即学生张三毕业，需要删除该学生信息，从此R中会同时删除课程信息，导致数据丢失；

　　　　　　数据冗余，每添加一个学生，需要将课程信息重新添加一遍，导致数据库保存数据大量冗余。

　　　　（4）规范该R到第二范式

　　　　　　R1（学生、年龄）

　　　　　　R2（学生、课程、学分、成绩）

　　　　　　R3（课程、任课教师、课程课时）

　　　　　　R1 学生-->年龄、学分、成绩；

　　　　　　R2 学生+课程-->学分、成绩；

　　　　　　R3 课程-->任课教师、课程课时；以上满足非主属性对主属性的完全函数依赖。

 

 3、第三范式：设 R 为任一给定关系，若 R 为 2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则 R 为第三范式（3NF）。即满足第二范式的条件下不存在传递函数依赖。

 　　R（A,B,C），A是主键（联合主键），如果同时有A->B和B->C，这就是一个传递依赖了

　　 eg：

　　　　结合2（第二范式）的示例（规范到第二范式的结果），分析是否符合第三范式，若不符合，将其规范到第三范式。

　　　　　　R1（学生、年龄）　　　　　　　　-----------------满足3NF

　　　　　　R2（学生、课程、学分、成绩）　　-----------------学生+课程-->成绩，成绩-->学分，但不存在成绩-->学生+课程，故不符合3NF

　　　　　　R3（课程、任课教师、课程课时）   -----------------满足3NF

　　　　R2再次拆分，

　　　　　  R21（学生、课程、成绩）

　　　　　  R22（成绩、学分）

 

  4、BCNF范式：鲍依斯-科得范式（BCNF是3NF的改进形式）

　　一个满足BCNF的关系模式的条件:

　　1. 所有非主属性对每一个码都是完全函数依赖。

　　2. 所有的主属性对每一个不包含它的码,也是完全函数依赖。

　　3. 没有任何属性完全函数依赖于非码的任何一组属性。

　　即在第三范式的基础上，消除了对主属性对码的部分和传递依赖。

　　eg：

　　 假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
　　 　　(仓库ID, 存储物品ID) →(管理员ID, 数量) 
　　　　 (管理员ID, 存储物品ID) → (仓库ID, 数量) 
　　 故，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
　　　　 (仓库ID) → (管理员ID)

　　　　 (管理员ID) → (仓库ID) 
　　 即，存在关键字段决定关键字段的情况，所以其不符合BCNF范式。

　　 把仓库管理关系表分解为二个关系表： 
　　     仓库管理：StorehouseManage(仓库ID, 管理员ID)； 
　　     仓库：Storehouse(仓库ID, 存储物品ID, 数量)。 
　　 这样的数据库表是符合BCNF范式的。

 

 5、第四范式（4NF）

　　4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。因为根据定义，对于每一个非平凡的多值依赖X→→Y，X都含有候选码，于是就有X→Y，所以4NF所允许的非平凡的多值依赖实际上是函数依赖。

　　和BC范式非常相像，对于所有的非平凡多值依赖，左边必须是超码。

 

 6、三大范式扩展

【例】设有关系模式 R(读者号，姓名，单位号，单位名，图书号，书名，借阅日期，还 书目期)存储读者借阅图书等信息。

　　如果规定：每个读者只属于一个单位；每个读者可以借阅多本图书，每本图书也可以被多名 读者借阅，每个读者也可以对某本图书多次借阅，但每个读者每本图书每天最多借一次。

　　1. 根据上述条件，写出模式 R 的关键字。

　　2. R 最高属于第几范式，为什么?

　　3. 举例说明 R 在何种情况下会发生删除异常。

　　4. 将 R 规范到 3NF。

  解析：

　　1、 答案： 读者号 图书号 借阅日期

　　解析： 设 R 为任一给定关系，U为其所含的全部属性集合，X为U的子集，若有完全函数依赖 X→U，则 X 为 R 的一个候选关键字。 作为候选关键字的属性集 X 唯一标识 R 中的元组，但该属性集的任何真子集不能唯一标识 R 中的元组。显然，一个关系 R 中可能存在多个候选关键字，通常选择其中之一作为主关键字， 即主键。 R 的函数依赖集={读者号→（姓名，单位号），单位号→单位名，图书号→书名，（读者号， 图书号，借阅日期）→还书目期}，故关键字为（读者号，图书号，借阅日期）。

　　2、答案： R∈1NF，因为存在非主属性对码的部分函数依赖。

　　解析： 设 R 为任一给定关系，如果 R 中每个列与行的交点处的取值都是不可再分的基本 元素，则 R 为第一范式（1NF）。 定义 2.7 设 R 为任一给定关系，若 R 为 1NF，且其所有非主属性都完全函数依赖于候选关键 字，则 R 为第二范式（2NF）。 因为存在非主属性姓名，单位号只依赖于读者号，和非主属性书名只依赖于图书号，即与关 键码（读者号，图书号，借阅日期）存在部分函数依赖。故不属于 2NF，最高属于 1NF。

　　3、答案： 删除读者借阅图书信息时可能把读者的基本信息也删除了。

　　解析： 1NF 是一个不含重复组的关系，1NF 存在着冗余高、插入和删 除操作异常等问题。比如： 若数据库系统原理这本书被同一个单位的人借 100 次，那么这个单位的单位名被重复记录 100 次，造成大量数据冗余。 若数据库系统原理这本书还没有被借出，那么这本书的基本信息将无法存储到这个关系中 去，就会出现插入异常的问题。 删除读者借阅图书信息时可能把读者的基本信息也删除了。

　　4、答案： R1（读者号，姓名，单位号） R2（单位号，单位名） R3（图书号，书名） R4（读者号，图书号，借阅日期，还书目期）

　　解析： 设 R 为任一给定关系，若 R 为 1NF，且其所有非主属性都完全函数依赖于候选关键 字，则 R 为第二范式（2NF）。 先将 1NF 先规范为 2NF，是消除非主属性对候选关键字的部分函数依赖。本题中仅有非主属 性“还书目期”完全函数依赖于（读者号，图书号，借阅日期），非主属性“姓名”，“单 位号”，“单位名”只依赖于“读者号”，和非主属性“书名”只依赖于“图书号”，故先 将 1NF 先规范为 2NF： R1（读者号，姓名，单位号，单位名）

　　5、 R3（图书号，书名） R4（读者号，图书号，借阅日期，还书目期） 

　　设 R 为任一给定关系，若 R 为 2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则 R 为第三范式（3NF）。 将 2NF 规范为 3NF，是消除非主属性对候选关键字的传递函数依赖。本题中 R1 中存在非主属性对主属性的传递函数依赖，即读者号→单位号，单位号→单位名，但不存在单位号→读 者号。故再将 2NF 关系 R1 进一步规范为 3NF： R1（读者号，姓名，单位号） R2（单位号，单位名）

 

 四、关系表的设计原则与建议

   1、一对多（外键关联，多的一方添加外键）

　　建表原则：在多的一方表中添加一个外键，指向一的一方表的主键 。

　　eg：一夫多妻制，需要在妻子信息表中添加一个外键，指向丈夫信息表的主键；涉及查询时，可以通过丈夫表的主键关联（借助于中间表）查询出所有妻子的信息。

　2、多对多（借助中间表）

　　建表原则：多对多的关系本质是一对多的关系的组合，需要借助于中间表，建立一张中间表，将多对多的关系，拆分成一对多的关系，中间表至少要有两个外键，别指向原来的那两张表。

　　补充：中间表的主键推荐使用联合主键。

　　eg：老师和学生，需要建一张中间表，老师学生关系表，该中间表至少包含三基本字段，外键一（指向老师表的主键）、外键二（指向学生表的主键）、主键（中间表的主键）。

   3、一对一（表的拆分与合并）

　   建表原则：

　　1）、通过主外键关联，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表的主键，即两张表的主键不一致（不相同）的情况；

　　2）、主键保持一致，将两张表的主键建立起连接，让两张表的主键相同；

　　3）、合并表，直接将两张表合并成一张表；

　　eg：中国公民和身份证。

　   应用场景：

　　1）、拆表及合表时需要考虑。

   4、外键

　　ON DELETE、ON UPDATE表示事件触发限制，可设参数：

　　① RESTRICT（限制外表中的外键改动，默认值）

　　② CASCADE（跟随外键改动）

　　③ SET NULL（设空值）

　　④ SET DEFAULT（设默认值）

　　⑤ NO ACTION（无动作，默认的）

  5、联合主键与复合主键

　　复合主键：数据库表的主键由两个及以上的字段组成。 即一表多主键，对表中两个以上的字段加主键（唯一性、创建了索引、非空约束）约束。

　　eg：

CREATE TABLE `auth_user_role_rel` (
  `ID` varchar(100) NOT NULL DEFAULT '' ,
  `USR_ID` varchar(100) NOT NULL DEFAULT '',
  `ROLE_ID` varchar(100) NOT NULL DEFAULT '',
  `EXT_STR1` varchar(255) DEFAULT NULL,
  `EXT_STR2` varchar(255) DEFAULT NULL,
  `EXT_STR3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`,`USR_ID`,`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

　　联合主键：多个主键联合形成一个主键的组合。当两个数据表是多对多的关系（中间表），需要通过两个数据表的主键来组成联合主键，来确定一条记录。即通过两张表的主键合成一个主键作为中间表的主键，优点是具备了唯一性且通过联合主键为后续数据维护和更新提供了方便（相对只针对唯一性生成的随机主键）。

 

 非常感谢您的阅读，如需转载请注明出处，本文链接https://www.cnblogs.com/huyangshu-fs/p/11632656.html