关系数据库的范式与反范式设计

最新推荐文章于 2021-06-30 22:13:49 发布

aidanzheng

最新推荐文章于 2021-06-30 22:13:49 发布

阅读量227

点赞数

分类专栏： MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/qq_32680371/article/details/109322462

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

范式种类

范式主要包括：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)及第五范式(5NF，又叫做完美范式)。数据库的设计范式越高，冗余度就越低，高级别的范式总是包含低级别的范式。数据库设计我们一般至少做到3NF，但是也不是绝对的，有时我们也需要反范式化，引入冗余。

基本概念

1、超键：一组能够唯一标识元组的属性集。
2、候选键：如果超键不包括多余的属性，也能唯一标识元组，那这个超键就是候选键。
3、主键：用户可以选择候选键集合中的一个候选键，作为主键。
4、外键：如果一个表T1中某个属性集，不是表T1的主键，但是却是表T2的主键，那么这个属性集就称为表T1的外键。
5、主属性：包含在任意一个候选键的属性称为主属性。
6、非主属性：不包含在任意一个候选键的属性称为非主属性。
7、依赖：如果一个表中某属性集X的值是由另外一个属性集Y的值来确定的，就称为X依赖于Y。
看到这些概念你可能不是很理解，我举一个例子：

CREATE TABLE teacher (
   `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `number` BIGINT(11) UNSIGNED NOT NULL COMMENT '教师编号',
   `name` VARCHAR(64) NOT NULL COMMENT '教师姓名',
   `tel` VARCHAR(64) NOT NULL COMMENT '教师电话',
   `subject` VARCHAR(256) NOT NULL COMMENT '学科',
   `credit` INT(11) NOT NULL DEFAULT '1' COMMENT '学分',
   PRIMARY KEY (`id`),
   UNIQUE KEY `number` (`number`) USING BTREE,
   KEY `subject` (`subject`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'teacher表';

对于如上的表，如上的概念风别是：
1、超键：只要属性集中包含属性[id]或[number]，那么就是超键。如[id,name]、[id,tel]、[id,name,tel]等。
2、候选键：只有[id]、[number]是候选键。
3、主键：如上表中我们选择[id]作为主键。
4、外键：如上表中没有外键。
5、主属性：id与number是主属性。
6、非主属性：除了id与number，其他的属性都是非主属性。
7、依赖：由于id的值决定了，name、tel等的值，我们就说name、tel等依赖于id。

1NF

如果表中的所有的属性都是原子的不可再分的，那么我们就称为这个表满足原子性的。所有的关系型数据库中的表都是满足原子性的。

1NF问题

比如如下的教师班级信息表：

CREATE TABLE teacher_class (
   `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `number` BIGINT(11) UNSIGNED NOT NULL COMMENT '教师编号',
   `name` VARCHAR(64) NOT NULL COMMENT '教师姓名',
   `tel` VARCHAR(64) NOT NULL COMMENT '教师电话',
   `subject` VARCHAR(256) NOT NULL COMMENT '学科',
   `credit` INT(11) NOT NULL DEFAULT '1' COMMENT '学分',
   `class` VARCHAR(64) NOT NULL COMMENT '所教班级名称',
   `class_student_num` INT(11) UNSIGNED NOT NULL COMMENT '班级学生个数',
   PRIMARY KEY (`id`),
   UNIQUE KEY `number_class` (`number`,`class`) USING BTREE,
   KEY `subject` (`subject`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'teacher-class信息表';

显然它是满足1NF的。但是它有如下的问题：
1、修改多行：比如如果某个班级一个新加入了一个插班生，那么班级的学生数量就要增加1个，但是由于一个班级可能会有很多个老师，为此会有很多行数据，从而导致需要修改很多行数据。
2、删除异常：比如某个教师A只教一个班级的学生，但是那个班级后面由于某些原因，需要解散了，那么就会删除那个班级的所有行，从而导致教师A的教师信息连带被删除了。
3、插入异常：假如我们成立了一个新的班级B，但是这个班级还没有排教师，那么就无法插入数据。
4、冗余：一个班级可能会有很多个教师，一个教师可能会教很多个班级的学生，那么教师与班级信息就存在多份。

2NF

如果表中的任何非主属性都是完全依赖于任意的候选键，那么就满足第二范式。所谓的完全依赖指的是不能有任何属性依赖于候选键中的某个属性子集。还是如上的信息表：
1、候选键有[id]、[number、class]，主键是[id]。
2、name、tel、class_student_num依赖于候选键[number、class]。但name、tel又依赖于number，class_student_num依赖于class，为此不满足完全依赖关系。
这个就是导致如上描述的1NF的问题的原因，我们可以将表拆分为3个表，分别是教师表，教师班级映射表与班级信息表，以满足第二范式。

CREATE TABLE class (
   `class` VARCHAR(64) NOT NULL COMMENT '所教班级名称',
   `class_student_num` INT(11) UNSIGNED NOT NULL COMMENT '班级学生个数',
   PRIMARY KEY (`class`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'class信息表';
  
CREATE TABLE teacher (
   `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `number` BIGINT(11) UNSIGNED NOT NULL COMMENT '教师编号',
   `name` VARCHAR(64) NOT NULL COMMENT '教师姓名',
   `tel` VARCHAR(64) NOT NULL COMMENT '教师电话',
   `subject` VARCHAR(256) NOT NULL COMMENT '学科',
   `credit` INT(11) NOT NULL DEFAULT '1' COMMENT '学分',
   PRIMARY KEY (`id`),
   UNIQUE KEY `number` (`number`) USING BTREE,
   KEY `subject` (`subject`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'teacher表';
 
 CREATE TABLE teacher_class (
   `teacher_number` BIGINT(11) UNSIGNED NOT NULL COMMENT '教师编号',
   `class` VARCHAR(64) NOT NULL COMMENT '所教班级名称',
   PRIMARY KEY (`number`,`class`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'teacher-class映射表';

2NF问题

如上的teacher 表虽然满足2NF，但是1NF的问题都还存在：
1、修改多行：比如要修改某个学科A的学分信息，但是由于学科A可能有多个教师教，为此需要修改多行的学科信息。
2、删除异常：比如某个学科B只有一个教师教，那么如果学校取消这门学科B，那么就会把这个学科删除了，从而导致那个教师信息被删除。
3、插入异常：假如我们成立了一个新的学科C，但是学科C还没有招到教师，那么就无法插入数据。
4、冗余：一个学科可能会有多个老师教，那么学科信息就存在很多行数据。

3NF

如果表中不存在非主属性传递依赖于某个候选键，那么就满足3NF。如上的teacher表中由于credit依赖于subject，而subject又依赖于number与id。为此其不满足3NF的要求，其也是导致如上描述的2NF的问题的原因。我们可以把teacher拆分成2个表：

 CREATE TABLE teacher (
   `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `number` BIGINT(11) UNSIGNED NOT NULL COMMENT '教师编号',
   `name` VARCHAR(64) NOT NULL COMMENT '教师姓名',
   `tel` VARCHAR(64) NOT NULL COMMENT '教师电话',
   `subject` VARCHAR(256) NOT NULL COMMENT '学科',
   PRIMARY KEY (`id`),
   UNIQUE KEY `number` (`number`) USING BTREE,
   KEY `subject` (`subject`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'teacher表';
 
 CREATE TABLE subject (
   `subject` VARCHAR(256) NOT NULL COMMENT '学科',
   `credit` INT(11) NOT NULL DEFAULT '1' COMMENT '学分',
   PRIMARY KEY (`subject`),
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT 'subject表';

明显着其解决了2NF描述的问题：修改多行、插入异常与冗余。对于删除异常，如果允许teacher的外键subject的值，在表subject 中不存在，那么删除异常也是解决了。当然我们也可以引入teacher与subject的映射表来解决。

3NF问题

但是3NF就彻底解决了修改多行、插入异常、删除异常与冗余的问题么？其实插入异常问题还是没有解决，比如如下的仓库负责人表：

CREATE TABLE warehouse (
   `warehouse` VARCHAR(64) NOT NULL COMMENT '仓库名称',
   `charge` VARCHAR(64) NOT NULL COMMENT '负责人',
   `production` VARCHAR(64) NOT NULL COMMENT '产品',
   `production_num` INT(11) NOT NULL COMMENT '产品数量',
   PRIMARY KEY (`warehouse`,`production`),
   UNIQUE KEY `charge_production` (`charge`,`production`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT '仓库表';

由于一个仓库只能对应一个负责人，为此候选键有[charge,production]与[warehouse,production]，剩下的产品数量production_num完全依赖于任意的候选键，因此其满足2NF。同时也不存在非主属性传递依赖于某个候选键，为此满足3NF。但是其还是存在如下的问题：
1、修改多行：比如要修改某个仓库的负责人，但是每个仓库存在多个产品，存在多行，为此需要修改多行。
2、插入异常：假如我们成立了一个新的仓库，但是这个仓库最开始没有产品，那么会导致插入不可插入的问题。
3、删除异常：如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。
4、冗余：每个仓库存在多个产品，存在多行，为此仓库负责人存在多行数据

BCNF

BCNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。根据 BCNF 的要求，我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样：

CREATE TABLE warehouse_charge (
   `warehouse` VARCHAR(64) NOT NULL COMMENT '仓库名称',
   `charge` VARCHAR(64) NOT NULL COMMENT '负责人',
   PRIMARY KEY  (`warehouse`),
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT '仓库负责人表';
CREATE TABLE warehouse (
   `warehouse` VARCHAR(64) NOT NULL COMMENT '仓库名称',
   `production` VARCHAR(64) NOT NULL COMMENT '产品',
   `production_num` INT(11) NOT NULL COMMENT '产品数量',
   PRIMARY KEY (`warehouse`,`production`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT '仓库表';

这样就不存在主属性对于候选键的部分依赖或传递依赖。明显着其解决了3NF如上描述的问题：修改多行、插入异常与冗余。

反范式设计

一般来说我们的库表设计至少需要达到3NF，最好达到BCNF。但是有时我们也需要反范式化设计。反范式是相对于范式的，其是通过引入少量的冗余，减少查询的关联，以提高数据库。反范式只有在数据规模达到一定程度了，反范式才有意义，如果数据量很少，反范式设计增加了复杂度，但是对于查询性能却没有显著的提升。比如如下的商品评论表：

CREATE TABLE comment(
   `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `commodity_id INT(11) NOT NULL COMMENT '商品ID',
   `content` VARCHAR(64) NOT NULL COMMENT '评论内容',
   `author_acct` VARCHAR(64) NOT NULL COMMENT '评论作者账户',
   `author_acct_id BIGINT(11) NOT NULL COMMENT '评论作者账户ID',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COMMENT '仓库表';

由于展示评论的时候，还要展示评论人，而评论表往往很大，像京东这种量级的电商，一天的评论可能就有几百几千万条，要是展示评论信息的时候去关联账户表，那么将会非常耗时。为此添加了冗余的author_acct信息。