Mysql事务和隔离级别

事务

将一组SQL语句,封装到事务中(工作单元)执行,事务中的语句要么全部执行成功,要么全部执行失败!

Mysql中事务是由存储引擎层实现的,常用支持事务的存储引擎Innodb.

应用场景

对数据一致性要求非常高的业务场景,例如:经典转账场景,用户A转账100元给用户B, 如果执行失败,需要使A和B的账户余额恢复正常,否则会造成某一方账户余额错误

ACID原则

原子性 Atomicity

不可分割的最小独立工作单元,事务内的SQL被当做一个整体执行

一致性 Consistency

保证数据一致性.简单的讲就是事务内的SQL要么执行全部成功,要么全部失败,

隔离性 Isolation (隔离级别)

一个事务内的操作(执行的SQL)在最终提交以前,对其它事务是不可见的.

持久性 Durability

事务提交后,数据将永久存储

自动提交

Mysql默认的采用自动提交模式(AUTOCOMMIT),不显式开启事务(start transaction)时,每次执行的sql都会提交

查看事务是否自动开启 show variables status like ‘autocommit’



开启关闭 set autocommit = 0 (OFF) set autocommit = 1(ON)

隔离级别(事务的)

SQL标准提供了四种事务的隔离级别:

读未提交(read uncommitted)

事务中的(数据)变更,即使事务未提交,在其它事务中也可以读取.也称为脏读(dirty read)

应用场景:

读提交(read committed)

事务中的(数据)变更,在事务提交后,在其它事务中才可以被读取,也称为不可重复读,

执行同样的查询,两次可能会得到不同的结果

可重复读(repeatable read) MySQL默认隔离级别

事务在执行过程中读取的数据,总是跟事务启动时读取的到的数据保持一致,未提交变更对其它事务同样不可见.会出现幻读.事务A读取一行记录,此时事务B插入新的记录,事务A再次读取数据,先前读取的一行会出现重复.
Innodb通过MVCC解决了幻读.

串行化(serializable)

强制事务串行执行,写操作加写锁,读操作加读锁,出现写锁冲突是后一个事务必须等待前一个事务执行完成才能继续执行

对比

隔离级别脏读可能性不可重复读幻读可能性加锁读
READ UNCOMMITyesyesyes no
READ COMMITEDnoyesyesno
REPEATABLE READnonoyesno
SERIALIZABLEnononoyes

查看隔离级别指令

show variables like 'transaction_isolation';

配置的方式:

将mysql启动参数 transaction-isolation 的值设置成对应的隔离级别

隔离级别演示案例

Mysql 开启远程访问 给指定数据库创建用户并授权

Mysql 8.0

#创建用户 db_user 换成root表示root用户
MySQL [(none)]> create user db_user@'%' identified by 'db_pass';

#授权  db_name 用*替代表示访问所有数据库
MySQL [(none)]> grant all privileges on db_name.* to db_user@'%' with grant option; 

#退出数据库控制台,特别注意有分号
MySQL [(none)]> exit;

Mysql 8.0以下

MySQL [(none)]> grant all privileges on db_name.* to db_user@'%' identified by 'db_pass';
 
#授权语句,特别注意有分号
MySQL [(none)]> flush privileges;

#退出数据库控制台,特别注意有分号
MySQL [(none)]> exit;

创建用户

create user 'username'@'%' identified by 'password';
username 用户名 %所有ip password 密码

授权

grant all privileges on dbname.* to 'username'@'%';

flush privileges;

Mysql 锁的介绍和使用

参考

基本概念

并发

网络编程中,多个请求/查询(进程/线程) 修改同一数据时,就会产并发控制问题,即谁先修改谁后修改.锁机制就是用来解决和控制并发问题的.

Mysql根据加锁的范围(粒度)分为三种锁

全局锁

给整个数据库加锁

FLUSH TABLES WITH READ LOCK
//全局读锁,会使其它线程中的写操作 数据更新语句(删除更新) 数据定义语句(创建修改表) 和更新事物提交类语句被阻塞
//使用场景全库逻辑备份,存在无事物的存储引擎的数据库
mysqldump –single-transaction 适合全库innodb引擎(所有表都支持事物)备份

表级锁

一种是表锁,一种是元数据锁(meta data lock,MDL)

表锁 lock tables tablename… read/write 释放所unlock tables

表锁不仅仅会锁定其它线程对锁定表的操作,也会锁定当前执行lock 语句的线程接下里的操作对象.只能执行当前表的操作

当前线程锁定users表,无法操作其它表
其它线程可以正常读取users表

上图示例当前线程执行了所锁定users表,则无法查询或操作其它表,

此时其它线程中可以正常读取users表的数据,因为读锁不互斥.

锁定执行写操作
释放表锁
另一线程内的查询操作

上三图表示线程A锁定users表示,其它线程对users表的操作,在未释放表锁前,另一线程一直处于阻塞状态,直到释放表锁

另一类表级的锁是MDL(meta data lock)

不需要显示调用,数据库自动操作 Mysql5.5版本中开始引入.作用是保证读写的正确性.例如,一个线程进行遍历查询,另一个线程进行表结构变更.

对一个表进行增删改查操作时,加MDL读锁,对表执行结构变更时,加MDL写锁.

读锁之间不互斥,因此多个线程可以对同一张表做增删改查,

读写锁之间,读锁之间互斥,用户保证表结构变更操作的安全性.例如线程A进行表遍历查询,线程B进行表结构变更操作,线程B需要等线程A的读锁释放之后才能执行

MDL测试案例

测试数据20万
测试代码,事务中遍历数据表
sessionA执行代码中的数据表遍历
sessionB执行Alter语句修改users表结构

此时,sessionB会阻塞,因为sessionA中的MDL读锁还没有被释放

sessoinC中的所有查询都处于等待状态

sessionC所有查询被被阻塞,此时整个数据库不能正常查询,这就是生产环境数据库结构变更可能会踩的坑.

解决方案

1.解除长事务,变更前查询一下存在事物正在运行的事物(information_schema 库的 innodb_trx表),手动kill调.

2.alter table语句设定等待时间. MariaDB和AliSQL支持 DDL NOWWAIT?WAIT n 语法

ALTER TABLE tbl_name NOWAIT add column...

ALTER TABLE tbl_name WAIT n add column...

MDL总结

mysql 5.6之前的版本,执行alter语句时间会很长,通过建立新的临时表方案解决,导入数据,最后重命名.

mysql5.6引入online ddl,通常情况下不会锁库,但是存在长事务和慢查询时,也会出现

行锁

行锁是由存储引擎层实现的,是mysql最小锁定粒度,可以锁定一行数据.常用引擎Innodb支持行锁MyISAM不支持行锁

Innodb行锁介绍

两阶段加锁,,

Mysql 复制 Replication

参考

简介

复制是mysql自带数据同步功能,可用于构建大规模高可用Mysql集群.复制解决的基本问题就是将一台数据库上的数据同步到另一台数据库上.

解决的问题(应用场景)

1.数据分布,备份

在不同的数据中心存储备份数据,即使在不稳定的网络环境中,复制可以正常工作

作为数据备份的补充技术手段

2.负载均衡

解决读密集型业务,将读取请求分发到多台数据库服务器上

3.高可用和故障切换

避免单点问题一台数据库服务器宕机还有其它从库可用

4.mysql升级测试

升级更高版本的mysql时,使用一台备库升级测试,不影响正常业务使用.

工作原理和复制的方式

1. master(主实例)将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events)

2. slave(从实例)将master的binary log events拷贝到它的中继日志(relay log);

3.slave重做中继日志中的事件,更新数据,完成数据同步

复制方式

https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages


复制的方式
基于语句的复制(SBR)基于行的复制(RBR)
简介复制的执行Sql语句(逻辑复制)复制数据
优点1.成熟的技术
2.写入日志文件的数据较少(快地完成从备份中获取和恢复) 传输效率更高
3.日志文件包含进行任何更改的所有语句,因此可用于审计数据库(云数据库的sql审计应该是这样实现的?)
1.可以复制所有更改,安全考高
2.执行效率高,因为复制的是数据,没有消耗系统资源的Sql操作

缺点 安全性差一点,有些特殊语句可能会报错
消耗系统资源,主库的操作需要从库要做一遍,
日志文件大传输效率低,I/O操作耗时高

Mysql支持混合复制模式,动态切换语句复制和行复制

复制拓扑及配置

基本原则

  • Mysql一个备库实例只能有一个主库(只能从一个主库读取binlog)(5.7以后支持多源复制)
  • 每一个备库必须有一个唯一服务器ID(同一网络内)
  • 一个主库实例可以有多个备库(一个备库可以有多个兄弟备库)
  • 开启log_slave_updates 选项 备库可以把它从主库获取的数据变化传递给其它备库(slave 此备库的其它备库)

学习拓扑图是,可以把复制的slave关系理解为面向对象中继承关系

一主多从

常用读写分离,适用于多读少写

双主复制

用于多写场景时,需要更改主键自增策略

其它形式的复制,请参考高性能<<Mysql>>

配置(主从实例)

1.创建复制账号

#创建用户
CREATE USER 'tongbu'@'192.168.%.%' IDENTIFIED BY '123456';
#配置账号同步权限
GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'192.168.%.%';
#刷新权限
flush privileges

需要在主库和从库 执行上述操作

2更改配置文件

配置

#服务器id(保证主从的id都是唯一的)
server-id = 1
#开启binlog日志
log_bin = mysql-bin
#混合复制模式,支持语句复制和行复制
binlog_format = mixed
#备库记录更新
log-slave-updates = 1

查看主库日志和位置 show master status\G

MySQL [(none)]> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000320
Position: 154
Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2d8be082-e379-11e6-9e2c-d89d672bc9d4:1-759145,
48496646-e379-11e6-9e2d-008cfaf6f158:1-4,
7c64616c-23a4-11e7-809c-7cd30abda0c4:1-9629173,
98d692c9-07a7-11e7-8a1b-7cd30abd9f90:1-6919876
1 row in set (0.00 sec)

配置从库监听主库

change master to master_host='192.168.31.106',master_port=3306,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000320',master_log_pos=154;

master_log_file 和 master_log_pos 要根据主库的信息来设置

 #开启从库
 start slave;
 #查看从库状态
 show slave status\G; 
 #状态为yes表示成功 
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

开启从库root用户远程访问(方便测试时用户端查看数据)

use mysql;
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

测试

创建测试数据库 sync_test 刷新从库, 同步成功

注意

开启start slave 之前的数据不会同步,需要手动在从库创建并导入

MySql索引的使用总结

所有存储引擎都支持每个表至少 16 个索引,总索引长度至少为 256 字节。

组合索引KEY(col1,col2) 联合主键索引 PARIMARY KEY( col1,col2) 

最左匹配原则

创建了key(k1,k2,k3),相当于创建了(k1)、(k1,k2) 和 (k1,k2,k3) 三个索引

索引全部命中的情况where条件不区分顺序 where k1 = xxx and k2 = xxx and k3 = xxx 等同于 where k3 = xxx and k2 = xxx  and k1 = xxx 查询优化器会处理并使用索引

where k1 = xxx 或 where k1 = xxx and k2 = xxx 使用索引 where k2 = xxx 或 where k3 = xxx 无法使用索引

当查询优化器发现最优索引时,并不会遵循最左匹配原则,而是使用最优查询,如下图k1,k2,k3为连续字段时,不按最左匹配查询还是会命中索引,此时k1,k2,k3被查询优化器当成了一个字段,单次查询k1,k2,k3都会命中

EXPLAIN select * from 3k where k2 = 'sdfdsa'

当k1,k2,k3不在连续中间被其它字段隔开时

相同的查询语句将不会命中索引

联合索引,那么 key 也由多个列组成,同时,索引只能用于查找 key 是否存在(相等),遇到 范围查询(>、<、between、like 左匹配)等就不能进一步匹配了,后续退化为线性查找。因此,列的排列顺序决定了可命中索引的列数。

如有索引 (a, b, c, d),查询条件 a = 1 and b = 2 and c = 3 and d > 4,则会在每个节点依次命中 a、b、c,无法命中 d。也就是最左前缀匹配原则。

不需要考虑 =、in 等的顺序,MySQL 会自动优化这些条件的顺序,以匹配尽可能多的索引列。

如有索引 (a, b, c, d),查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的,MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4,依次命中 a、b。

离散度高原则,选择性(离散性)高的优先,即数据重复率低的列,像性别字段只有男/女两个值,因此选择性很差

离散度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要 join 的字段我们都要求是 0.1 以上,即平均 1 条扫描 10 条记录。

索引前缀

字符串字段过长时可以使用,索引前缀,截取字段前一部分的值作为索引

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Range类型 要使用单字段索引

当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符中的任何一个将键列与常量进行比较时,可以使用范围

where min <= 1 and max >= 1 语句使用min和max联合索引并不会生效,应该使用min和max的两个单字段索引

索引失效情况总结

查询条件包含 or,会导致索引失效

隐式类型转换,会导致索引失效,where条件左边字段类型为字符串类型时,传入其他类型会触发

like 通配符会导致索引失效,注意:”ABC%” 不会失效,会走 range 索引,”% ABC” 或 “%ABC%” 索引会失效

联合索引,最左匹配原则被中断时

对索引字段进行函数运算,列运算(算术,逻辑,移位等) 注意是对字段做运算,对字段的值做运算时并不影响,如下图

索引字段上使用(!= 或者 < >,not in)时,会导致索引失效

索引字段上使用 is null, is not null,可能导致索引失效

相 join 的两个表的字符编码不同,不能命中索引,会导致笛卡尔积的循环计算

mysql 优化器判断使用全表扫描要比使用索引快,则不使用索引

简单索引和联合索引如何抉择?

能扩展就不要新建索引。如果已有索引 (a),想建立索引 (a, b),尽量选择修改索引 (a) 为索引 (a, b),这样占用空间最小效果一致。

如果已有索引 (a, b),则不需要再建立索引 (a),但是如果有必要,则仍然需考虑建立索引 (b)。

索引尽量少原则

虽说索引可以加速查询,但索引未必是越多越好,因为:

  • 第一点、数据的增删都会涉及到随索引的修改,索引越多维护成本越高,所以频繁进行数据操作的表,不要建立太多的索引;
  • 第二点、索引越多也意味着存储空间需要越大;

因为索引是有代价的,所以用不到的索引,也需要清理掉。

如何定位哪些页面和接口需要加索引?

最简单直接方法是:往数据库里生成 100 万条数据,然后做黑盒测试。

数据有了以后,假装你是一个正常的用户,然后不断地点来点去,做各种操作,点赞、收藏、发文章、评论等,看看哪个动作或者页面加载很慢,就记录下来

如果使用laravel框架可以用telescope或者debug工具查看执行的sql语句,对访问频率高的接口和页面,增加索引,优化查询

参考

盘点那些被问烂了的 Mysql 面试题

MySQL 规约(转自阿里巴巴 Java 开发手册)

官方手册优化与索引

laravel优化教程

Mysql Explain命令

官方手册 :https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

Explain命令 查看语句的执行计划,用于sql语句优化

属性说明:

select_type:select 的语句的查询类型

类型值类型值说明

SIMPLE

简单SELECT(不使用UNION或子查询等)

PRIMARY

最外面的SELECT

UNION

UNION中的第二个或后面的SELECT语句

DEPENDENT UNION

UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT

UNION的结果

SUBQUERY

子查询中的第一个SELECT

DEPENDENT SUBQUERY

子查询中的第一个SELECT,取决于外面的查询

DERIVED派生表
DEPENDENT DERIVED派生表依赖于另一个表
MATERIALIZED物化子查询,子查询来自视图
UNCACHEABLE SUBQUERY一个子查询,其结果不能被缓存,必须对外层查询的每一行进行重新评估
UNCACHEABLE UNIONUNION中的第二个或以后的选择,属于不可缓存的子查询(参见不可缓存的子查询)

table:显示这一行的数据是关于哪张表的

partitions: 被查询记录所在的分区,没有返回null,分区功能参考mysql分区功能

type:这列最重要,显示了连接使用了哪种类别,有无使用索引,是使用Explain命令分析性能瓶颈的关键项之一

类型值类型值说明
system该表只有一行(= 系统表)。这是 const 连接类型的特例
const该表最多有一个匹配行,在查询开始时读取。因为只有一行,该行中该列的值可以被优化器的其余部分视为常量。 const 表非常快,因为它们只被读取一次。主键或唯一索引时类型为常量
eq_ref对于前面表中的每个行组合,从该表中读取一行。除了 system 和 const 类型之外,这是最好的连接类型。当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用它
ref所有具有匹配索引值的记录都从这个表中读出,用于前面表中的每一个记录组合。如果连接只使用键的最左边的前缀,或者键不是PRIMARY KEY或UNIQUE索引(换句话说,如果连接不能根据键值选择一条记录),就使用ref。如果使用的键只与几条记录相匹配,这就是一个好的连接类型
fulltext全文索引,类搜索引擎功能
ref_or_null这种连接类型就像ref,但增加了MySQL对包含NULL值的行进行额外的搜索。这种连接类型的优化在解决子查询时最常使用。在下面的例子中,MySQL可以使用一个ref_or_null连接来处理ref_table
index_merge这种连接类型表明使用了索引合并优化。在这种情况下,输出行中的key列包含了所使用的索引的列表,key_len包含了所使用的索引的最长的key部分的列表。更多信息,请参见章节8.2.1.3,”索引合并优化
unique_subquery对于以下形式的某些 IN 子查询,此类型替换 eq_ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery这种连接类型类似于 unique_subquery。它取代了 IN 子查询,但它适用于以下形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
range只检索给定范围内的行,使用索引来选择行。输出行中的key列表明使用的是哪个索引。key_len包含了所使用的最长的关键部分。对于这种类型,ref列是NULL
index索引连接类型与ALL相同,只是对索引树进行扫描。这有两种情况。
如果索引是查询的覆盖索引,并且可以用来满足表的所有数据要求,那么只有索引树被扫描。在这种情况下,Extra列显示使用索引。只扫描索引的速度通常比ALL快,因为索引的大小通常比表的数据小。
全表扫描是使用从索引中读出的数据来按索引顺序查找数据行。使用索引不会出现在Extra列中。
当查询只使用属于一个索引的列时,MySQL可以使用这种连接类型。
ALL没有使用任何索引,使用了全表扫描,性能非常差

range示例说明

当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符中的任何一个将键列与常量进行比较时,可以使用范围:
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

结果值从好到坏依次是:一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys:列指出MySQL能使用哪个索引在该表中找到行

当前查询可用的索引 多个逗号分隔

key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

查询优化器实际选择的索引

key_len:显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示使用哪个列或常数与key一起从表中选择

ref列显示哪些列或常量与列中指定的索引进行比较以 key从表中选择行。

如果值为func,则使用的值是某个函数的结果。要查看哪个功能,请使用 SHOW WARNINGS以下内容 EXPLAIN查看扩展 EXPLAIN输出。该函数实际上可能是一个运算符,例如算术运算符。

rows: rows列表示MySQL认为它必须检查以执行查询的行数,对于InnoDB表,这个数字是一个估计值,不一定准确

filtered: 表按照条件过滤行数的百分比

过滤列表示被表条件过滤的表行的估计百分比。最大值是100,这意味着没有发生过滤的行。从100开始递减的值表示过滤量的增加。rows显示了被检查的行的估计数量,rows × filtered显示了与下面表格连接的行的数量。例如,如果rows是1000,而filtered是50.00(50%),那么与下表中连接的行数是1000×50%=500

Extra:附加信息 包含MySQL解决查询的详细信息,也是关键参考项之一。

这一列包含关于MySQL如何解决查询的额外信息。关于不同值的描述,见EXPLAIN额外信息。

没有与Extra列相对应的单一JSON属性;然而,可能出现在这一列中的值被暴露为JSON属性,或作为消息属性的文本

具体信息看官方文档查询

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

其他一些Tip:

  1. 当type 显示为 “index” 时,并且Extra显示为“Using Index”, 表明使用了覆盖索引。

EXPLAIN ANALYZE

select * 和 select 具体字段差别

数据库设计Step by Step (11)——通用设计模式(系列完结篇)

阅读原文 转自博客园 知行思新

引言:前文(数据库设计Step by Step (10)——范式化)我们详细讨论了关系数据库范式,始于第一范式止于BCNF范式。至此我们完成了数据库的逻辑设计,如下图所示。

正如首篇博文数据库设计 Step by Step (1)——扬帆启航中介绍的,本系列博文关注通用于所有关系数据库的需求分析与逻辑设计部分。无论你使用的是Oracle,SQL Server,Sybase等商业数据库,亦或是如MySQL,SQLite等开源数据库都能运用这些设计方法来优化设计。数据库的物理设计及实现部分与数据库产品密切相关、各有差异,且内容也非常丰富,故不在本系列中讨论。

本篇博文将分为两个部分,第一部分将介绍数据库设计的一些通用模式,第二部分将对本系列的内容做一个整理并给出一些参考资料供大家参考。

这一小节我们将分析一些较为常见的业务场景,并给出对于这些场景的表结构设计方法。这些方法可以放入我们自己的数据库设计工具箱,当在面对现实需求时可灵活加以运用。

多值属性

多值属性很常见,如淘宝网中每个用户都可以设置多个送货地址,又如在CRM系统中客户可以有多个电话号,一个号码用于工作时间,另一个用于下班时间等。

以存储客户的联系电话为例。联系电话是客户的属性,所以首先可能想到的一种设计方案如下:

(图1  联系电话作为客户的属性)

图1这一设计满足了当前的需求,但不久我们发现客户的联系电话比我们想象的多,他们还有移动电话,而且有些客户有不止一个办公电话号码,我们需要记录这些电话号码,并标识不同的办公室地点。

对于这种需求,我们可以在Customer表中增加列,但这样做会有两方面的问题:首先,每次增加新列都需要修改数据表结构,需要DBA从后台写脚本完成,且前台显示联系电话的功能模块也需要相应进行修改。其次,每个客户具有的联系电话类型及数量各不相同,大量的联系电话单元格都是空的,浪费了许多存储空间。

我们换一种设计方案。每个客户有多个不同类型的联系电话,可以把联系电话作为弱实体从原先Customer实体中分离出来,如图2所示:

(图2  实体Customer与实体Phones一对多关系)

Customer与Phones之间为“一对多”关系,即一个客户可以有多个不同类型的联系电话。当我们需要给某客户增加联系电话时,我们不再需要修改表结构,只需要在Phones表中增加记录就可以了。这完全可以作为前台的功能让业务操作人员来完成,而且现在的Customer不再会存在大量空单元格了。在关系数据库中增加行比增加列的代价要小很多。

实体Phones的主键是什么?

CustID肯定是主键的一部分。主键包含的其他列根据我们想表达的不同语义,可以有所不同。

语义1:一个客户不能有重复的联系类型。即一个客户的每个PhoneType不能重复,但多个不同的PhoneType可能对应相同的PhoneNum(如:PhoneType为“Office”和“Home”对应同一个号码)。符合该语义的主键为:CustID,PhoneType;

语义2:一个客户不能有重复的联系电话。即一个客户的每个PhoneNum不能重复,但多个不同的PhoneNum可能对应相同的PhoneType(如:PhoneType为“Office”有多个不同号码)。符合该语义的主键为:CustID,PhoneNum;

语义3:一个客户的一个联系类型能有多个不同的联系电话,一个联系电话可能对应不同的联系类型。符合该语义的主键为:CustID,PhoneType,PhoneNum;

举一反三,该多值属性设计方法同样适用于维护客户的多个地址或Email等场景。

历史追溯

说到历史就会涉及时间。例如:当前物价持续上涨,同一产品的售价每个月都有可能调整,若要追溯产品价格变化的情况,仅仅记录该产品当前的一个售价是不足够的。同样对于银行中的利率变化,购入原材料的单价变化等,都需要进行历史追溯。

要跟踪一个实体随时间的变化可以在该实体中增加属性列,指明实体中每个实例的有效日期。图3展示了可追溯产品价格的订单结构(已经过简化)。

(图3  简化的订单表结构)

实体Orders记录订单的公共信息,包括订单号(OrderID),下订单的时间(OrderDate),客户编号(CustomerID)等。其中OrderID提供了到实体OrderItems的联接。实体OrderItems记录客户订购的产品条目,包括所属订单号(OrderID),产品编号(ProductID),订购数量(Quantity)等。其中ProductID能联接到实体Products。实体Products中包含每种产品的描述信息。实体ProductPrices记录了产品的价格,包括产品编号(ProductID)对应到Products实体,产品价格(Price),以及该价格的有效时间段(EffectiveStartDate,EffectiveEndDate)。

对于上述表结构,回溯历史某个订单的信息的步骤如下:

1. 根据订单号(OrderID)在Orders表中找到对应的记录,并记录下OrderDate

2. 在OrderItems表中根据OrderID找到对应的所有订单明细记录。对每一条明细,记录下Quantity和ProductID,之后:

a. 通过ProductID,在Products表中找到对应产品的产品描述(Description)

b. 在ProductPrices表中找到对应ProductID,且EffectiveStartDate <= OrderDate < EffectiveEndDate的记录。该记录中的Price为指定产品在历史下单时的价格。

这样我们就得到了该订单的历史“快照”信息。

需要注意的几点:

1. 如果我们只需要追溯订单中产品的历史价格,可省去上述步骤中的a。

2. 上述订单表结构在每次查看订单时都需要查询ProductPrices表。我们可以通过在OrderItems表中增加ItemPrice列,来避免对ProductPrices表的频繁查询。当创建订单明细记录时,把从ProductPrices中查询到的价格记录到ItemPrice列中,之后每次查看订单时就不需要再查询ProductPrices表了。

3. ProductPrices表的主键为ProductID,EffectiveStartDate。同时该表还隐含着约束:同一种产品的价格有效时间段不能重叠。

4. ProductPrices表结构中EffectiveEndDate列可省去,把该产品的下一个EffectiveStartDate作为上一个有效时间段的自然结束时间点。但这样做会增加查询的复杂度。

在举一个简单的例子,每个客户只有一个地址信息,但希望能跟踪客户地址的变更情况。我们能设计如下(图4)表结构:

(图4  跟踪客户地址的变更)

类似的场景包括:跟踪员工薪资的变化情况,跟踪汇率的变化情况等等。还有一种场景可使用该技术,当我们通过系统前台试图删除某信息时,系统的后台数据库并不真正去做删除操作,而是通过EffectiveEndDate标识记录的无效时间。通过EffectiveStartDate和EffectiveEndDate可回溯任何历史时间点存在的记录“快照”。

树型结构

树型结构最典型的例子是员工组织机构图,如图5所示。

(图5  员工组织结构图)

树型结构中除根节点之外,每一个子节点都有一个父节点。可以把节点建模为一个实体,父子之间的联系建模为“一对多回归关系”。图5中的员工组织结构可建模为图6所示的ER结构。

(图5  员工组织结构ER模型)

实体Employees中的EmpID,FirstName,LastName,HireDate,Salary等属性描述了员工的基本信息,树型层次关系通过ManagerID属性进行描述,该属性存储了该员工的经理ID,即指向其父节点。

在节点实体中存储指向父节点的属性已足够描述树型结构的语义,但为了提高查询的效率,设计中可增加树型结构层次(Lvl)和物化路径(Path)作为辅助信息。图5员工组织结构样例数据如下:

EmpIDFirstNameLastName  HireDate …ManagerIDLvlPath
1David……NULL0.1.
2Eitan……11.1.2.
4Seraph……22.1.2.4.
5Jiru……22.1.2.5.
10Sean……53.1.2.5.10.
8Lilach……53.1.2.5.8.
6Steve……22.1.2.6.
3Ina……11.1.3.
7Aaron……32.1.3.7.
11Gabriel……73.1.3.7.11.
9Rita……73.1.3.7.9.
12Emilia……94.1.3.7.9.12.
13Michael……94.1.3.7.9.13.
14Didi……94.1.3.7.9.14.

(表1  员工组织结构数据,其中Lvl列,Path列可选,利用该两列能提升某些查询的性能)

注:如何对树型结构数据表进行查询、遍历在这里不进行展开,可参考《Inside Microsoft SQL Server 2005 T-SQL Querying》一书。本例及以下两小节中的例子,引用自《Inside Microsoft SQL Server 2005 T-SQL Querying》,但同样适用于其他关系数据库。

有向无环图结构

有向无环图(DAG)的典型应用场景是物料清单(BOM)。BOM记录了产品的组装零件或配置方式,下图为某咖啡店的BOM图,描述了配置每种饮料的原料及剂量。

(图6  咖啡店BOM图)

我们如何把这一BOM信息存储到数据库中呢?

BOM场景以有向无环图为模型。有向无环图与树型层次结构的差异之处在于,有向无环图中的一个节点能有多个父节点。故ER模型中,有向无环图需建模成两个实体,一个实体用于描述节点,另一个实体用于描述节点之间的边。咖啡店BOM场景的ER模型如图7所示,实体Parts表示咖啡店的原料及饮品,实体Assemble表示原料配置的方向(即“有向边”),其中还包括边的权值,此例中边的权值为qty,表示配料的剂量,unit为配料的剂量单位(如:g,ml等)。

(图7  咖啡店BOM的ER模型)

把咖啡店BOM的ER图转化为SQL:

create table Parts
(
    PartID int not null primary key,
    PartName varchar(25) not null
);
 
create table Assemble
(
    PartID int not null references Parts,
    AssemblyID int not null references Parts,
    Unit varchar(3) not null,
    Qty decimal(8,2) not null,
    primary key(PartID, AssemblyID),
    check(PartID <> AssemblyID)
);

需要注意以下几点:

1. 上述代码在SQL Server 2008下测试通过。对于其他数据库产品,代码细节可能需稍作调整,但主体设计结构不变。

2. Assemble表的主键为:PartID,AssemblyID。

3. Assemble表的PartID列和AssemblyID列外键引用Parts表。

4. Assemble表的check约束保证其中任何记录的PartID与AssemblyID的值不会相同。

无向循环图结构

无向循环图的一个典型例子是城市道路系统。下图展示了美国主要城市之间的道路

(图8  美国道路系统)

图8中每个节点表示一个城市,城市之间的连线代表城市之间的道路,连线上的数值表示距离。道路系统以无向循环图为模型,无向循环图中的节点能与任意数量的其他节点相连,且相连接的节点之间没有父子或先后关系(即“边”没有方向)。对图8中的道路系统进行ER建模得:

(图9  道路系统ER模型)

把图9中的ER模型转化为SQL:

create table Cities
(
    CityID char(3) not null primary key,
    CityName varchar(30) not null,
    Region varchar(30) not null,
    Country varchar(30) not null
);
 
create table Roads
(
    CityID char(3) not null references Cities,
    DestID char(3) not null references Cities,
    Distance int not null,
    primary key(CityID, DestID),
    check(CityID < DestID),
    check(Distance > 0)
);

需要注意以下几点:

1. 上述代码在SQL Server 2008下测试通过。对于其他数据库产品,代码细节可能需稍作调整,但主体设计结构不变。

2. 为了更易于理解,图9道路系统ER模型中的关系connect,在转化为SQL表时更名为Roads。Roads表描述了一个无向循环赋权图。表中每一行表示一条边(道路)。Distance属性表示权值(城市间的距离)。

3. Roads表的CityID列和DestID列外键引用CityID表。

4. Roads表的主键为CityID,DestID。

5. Roads表中包含check约束(CityID < DestID),以避免存入两个相同的边(eg:“芝加哥到纽约”和“纽约到芝加哥”)。无向循环图中节点之间是平等的,故该约束很重要,避免冗余数据。

6. 若要扩展到“有向循环图”场景(如:道路系统中的单行道),我们只要去除check约束(CityID < DestID),此时不同方向的数据不再是冗余。

到这里整个系列将告一段落,希望大家能觉得该系列言之有物,读了能有些许收获。最后,对本系列博文作一个回顾,同时给出一些参考资料。

本系列篇目回顾

1. 数据库设计 Step by Step (1)——扬帆启航

2. 数据库设计 Step by Step (2)——数据库生命周期

3. 数据库设计 Step by Step (3)——基本ER模型构件

4. 数据库设计 Step by Step (4)——高级ER模型构件

5. 数据库设计 Step by Step (5)——理解用户需求

6. 数据库设计 Step by Step (6) —— 提取业务规则

7. 数据库设计Step by Step (7)——概念数据建模

8. 数据库设计 Step by Step (8)——视图集成

9. 数据库设计Step by Step (9)——ER-to-SQL转化

10. 数据库设计Step by Step (10)——范式化

11. 数据库设计Step by Step (11)——通用设计模式(系列完结篇)

参考资料

以下推荐的两本书虽然不是关于数据库设计,但对于程序开发人员会有帮助。熟练掌握数据库查询与编程能促进对数据库设计的理解与学习。

1. 《Inside Microsoft SQL Server 2005 T-SQL Querying》——这是我当初看的一本书,对于深入理解数据库查询很有帮助。现在已经出了《Inside Microsoft SQL Server 2008 T-SQL Querying》。

2.  《Inside Microsoft SQL Server 2005 T-SQL Programming》——对于学习T-SQL编程很有助益。

数据库设计Step by Step (10)——范式化

阅读原文 转自博客园 知行思新

引言:前文(数据库设计Step by Step (9)——ER-to-SQL转化)讨论了如何把ER图转化为关系表结构。本文将介绍数据库范式并讨论如何范式化候选表。我们先来看一下此刻处在数据库生命周期中的位置(如下图所示)。

前几篇博文中我们详细的讨论了ER建模的方法。精心设计的ER模型将帮助我们直接得到范式化的表或只需稍许修改即为范式化的表,设计、绘制ER图的重要性也体现在这里。概念数据建模(ER建模)从一开始就潜移默化的引导着我们走向范式化的数据库表结构。

本文的讨论将始于第一范式,止于BCNF范式。在现实数据库设计中,一般需达到的范式化目标是第三或BCNF范式,更高级别的范式更多的是理论价值,本文也不将涉及。

范式基础

关系数据库中的表有时会面对性能、一致性和可维护性方面的问题。举例来说,把整个数据库中的数据都定义在一张表中将导致大量冗余的数据,低效的查询和更新性能,对某些数据的删除将造成有用数据的丢失等。如图1所示,products, salespersons, customers, orders都存储在一张名为Sales的表中。

product_nameorder_nocust_namecust_addrcreditdatesales_name
vacuum cleaner1435DaveAustin62010-03-01Carl
computer2730QiangPlymouth102011-04-15Ted
refrigerator2460MikeAnn Arbor82010-09-12Dick
DVD player519PeterDetroit32010-12-05Fred
radio1986CharlesChicago72011-05-10Richard
CD player1817EricMumbai82010-08-03Paul
vacuum cleaner1865CharlesChicago72010-10-01Carl
vacuum cleaner1885BetsyDetroit82009-04-19Carl
refrigerator1943DaveAustin62011-01-04Dick
television2315SaktiEast Lansing62011-03-15Fred

(图1 Sales表)

在这张表中,某些产品和客户信息是冗余的,浪费了存储空间。某些查询如“上个月哪些客户订购了吸尘器”需要搜索整张表。当要修改客户Dave的地址需要更新该表的多条记录。最后删除客户Qiang的订单(2730)将造成该客户姓名、地址、信用级别信息的丢失,因为该客户只有唯一这个订单。

如果我们通过一些方法把该大表拆分成多个小表,从而消除上述这些问题使数据库更为高效和可靠,范式化就是为了达到这一目标。范式化是指通过分析表中各属性之间的互相依赖,并把大表映射为多个小表的过程。

第一范式(1NF)

定义:当且仅当一张表的所有列只包含原子值时,即表中每行中的每一个列只有一个值,则该表符合第一范式。

图1中的Sales表的每一行、每一列中只有原子值,故Sales表满足第一范式。

为了更好的理解第一范式,我们讨论一下域、属性、列之间的差异。

域是某属性所有可能值的集合,但同一个域可能被用于多个属性上。举例来说,人名的域包含所有可能的姓名集合,在图1的表中可用于cust_name或sales_name属性。每一列代表一个属性,有些情况下代表不同属性的多个列具有相同的域,这并不会违反第一范式,因为表中的值仍是原子的。

仅符合第一范式的表常会遇到数据重复、更新性能以及更新一致性等问题。为了更好的理解这些问题,我们必须定义键的概念。

超键是一个或多个属性的集合,其能帮助我们唯一确定一条记录。若组成超键属性列的子集仍为一个超键,但该子集少了任何一个属性都将使其不再是一个超键,则该属性列子集称为候选键。主键是从一张表的候选键集合中任意挑选出的,作为该表的一个索引。

作为一个例子,图2中表的所有属性组成一个超键。

report_noeditordept_nodept_namedept_addrauthor_idauthor_nameauthor_addr
4216woolf15designargus153manteics-tor
4216woolf15designargus144boltonmathrev
4216woolf15designargus171koenigmathrev
5789koenig27analysisargus226fryfolkstone
5789koenig27analysisargus238umarprise
5789koenig27analysisargus271koenigmathrev

(图2 Report表)

在关系模型中不允许有重复的行,因此一个明显的超键是表的所有列(属性)的组合。假设表中每一个部门的地址(dept_addr)都相同,则除dept_addr之外的属性仍然是一个超键。对其他属性作类似的假设,逐步缩小属性的组合。我们发现属性组合report_no, author_id能唯一确定表中的其他属性,即是一个超键。同时report_no或author_id中的任意一个都无法唯一确定表中的一行,故属性组合report_no, author_id是一个候选键。由于它们是该表的唯一候选键,它们也是该表的主键。

一张表能有多个候选键。举例来说,在图2中,若有附加列author_ssn(SSN:社会保险号),属性组合report_no, author_ssn也能唯一确定表中的其他属性。因此属性组合(report_no, author_id)和(report_no, author_ssn)都是候选键,可以任选其一作为主键。

第二范式(2NF)

为了解释第二以及更高级别范式。我们需引入函数依赖的概念。一个或多个属性值能唯一确定一个或多个其他属性值称为函数依赖。给定某表(R),一组属性(B)函数依赖于另一组属性(A),即在任意时刻每个A值只与唯一的B值相关联。这一函数依赖用A –> B表示。以图2中的表为例,表report的函数依赖如下:

report:  report_no –> editor, dept_no

                dept_no –> dept_name, dept_addr

                author_id –> author_name, author_addr

定义:一张表满足第二范式(2NF)的条件是当且仅当该表满足第一范式且每个非键属性完全依赖于主键。当一个属性出现在函数依赖式的右端,且函数依赖式的左端为表的主键或可由主键传递派生出的属性组,则该属性完全依赖于主键。

report表中一个传递函数依赖的例子:

report_no –> dept_no

dept_no –> dept_name

因为我们能派生出函数依赖(report_no –> dept_name),即dept_name传递依赖于report_no。

继续我们的例子,图2中表的复合键(report_no, author_id)是唯一的候选键,即为表的主键。该表存在一个FD(dept_no –> dept_name, dept_addr),其左端没有主键的任何组成部分。该表的另两个FD(report_no –> editor, dept_no和author_id –> author_name, author_addr)的左端包含主键的一部分但不是全部。故report表的任何一条FD都不满足第二范式的条件。

思考一下仅满足第一范式的report表的缺陷。report_no, editor和dept_no对该Report的每一位author都需要重复,故当Report的editor需要变更时,多条记录必须同步修改。这就是所谓的更新异常(update anomaly),冗余的更新会降低性能。当没有把所有符合条件的记录同步更新时,还会造成数据的不一致。若要在表中加入一位新的author,只有在该author参与了某Report的撰写才能插入该author的记录,这就是所谓的插入异常(insert anomaly)。最后,若某一张Report无效了,所有与该Report相关联的记录必须一起删除。这可能造成author信息的丢失(与该Report相关联的author_id, author_name, author_addr也被删除了)。这一副作用被称为删除异常(delete anomaly),使数据丧失了完整性。

上述这些缺陷可通过把仅满足第一范式的表转化为多张满足第二范式的表来克服。在保留原先函数依赖和语义关系的前提下,把Report表映射为三张小表(report1, report2, report3),其中包含的数据如图3所示。

Report 1

report_noeditordept_nodept_namedept_addr
4216woolf15designargus 1
5789koenig27analysisargus 2

Report 2

author_idauthor_nameauthor_addr
53manteics-tor
44boltonmathrev
71koenigmathrev
26fryfolkstone
38umarprise
71koenigmathrev

Report 3

report_noauthor_id
421653
421644
421671
578926
578938
578971

(图3  2NF表)

这些满足第二范式表的函数依赖为:

report1: report_no –> editor, dept_no

                 dept_no –> dept_name, dept_addr

report2: author_id –> author_name, author_addr

report3: report_no, author_id为候选键,无函数依赖

现在我们已得到了三张满足第二范式的表,消除了第一范式表存在的最糟糕的问题。第一、editor, dept_no, dept_name, dept_addr不再需要为每一位author重复。第二、更改一位editor只需要更新report1的一条记录。第三、删除report不再会造成author信息丢失的副作用。

我们可以注意到这三张满足第二范式的表可以直接从ER图转化得到。ER图中的Author、Report实体以及之间的“多对多”关系可根据上一篇博文(数据库设计Step by Step (9)——ER-to-SQL转化)的规则很自然的转化为三张表。

第三范式(3NF)

第二范式相对于第一范式已经有了巨大的进步,但由于存在传递依赖(transitive dependency),满足第二范式的表仍会存在数据操作异常(anomaly)。当一张表中存在传递依赖,其意味着该表中描述了两个单独的事实。每个事实对应于一条函数依赖,函数依赖的左侧各不相同。举例来说,删除一个report,其包含删除report1和report3表中的相应记录(如图3所示),该删除动作的副作用是dept_no, dept_name, dept_addr信息也被删除了。如果把表report1映射为包含列report_no, editor, dept_no的表report11和包含列dept_no, dept_name, dept_addr的表report12(如图4所示),我们就能消除上述问题。

Report11

report_noeditordept_no
4216woolf15
5789koenig27

Report12

dept_nodept_namedept_addr
15designargus 1
27analysisargus 2

Report 2

author_idauthor_nameauthor_addr
53manteics-tor
44boltonmathrev
71koenigmathrev
26fryfolkstone
38umarprise
71koenigmathrev

Report 3

report_noauthor_id
421653
421644
421671
578926
578938
578971

(图4  3NF表)

定义:一张表满足第三范式(3NF)当且仅当其每个非平凡函数依赖X –> A,其中X和A可为简单或复合属性,必须满足以下两个条件之一。1. X为超键 或 2. A为某候选键的成员。若A为某候选键的成员,则A被称为主属性。注:平凡函数依赖的形式为YZ –> Z。

在上述例子中通过把report1映射为report11和report12,消除了传递依赖report_no –> dept_no –> dept_name, dept_addr,我们得到了如图4所示的第三范式表及函数依赖:

report11: repot_no –> editor, dept_no

report12: dept_no –> dept_name, dept_addr

report2:   author_id –> author_name, author_addr

report3:   report_no, author_id为候选键(无函数依赖)

Boyce-Codd范式(BCNF)

第三范式消除了大部分的异常,也是商业数据库设计中达到的最普遍的标准。剩下的异常情况可通过Boyce-Codd范式(BCNF)或更高级别范式来消除。BCNF范式可看作加强的第三范式。

定义:一张表R满足Boyce-Codd范式(BCNF),若其每一条非平凡函数依赖X –> A中X为超键。

BCNF范式是比第三范式更高级别的范式因为其去除了第三范式中的第二种条件(允许函数依赖右侧为主属性),即表的每一条函数依赖的左侧必须为超键。每一张满足BCNF范式的表同时满足第三范式、第二范式和第一范式。

以下的例子展示了一张满足第三范式但不满足BCNF范式的表。这样的表和那些仅满足较低范式的表一样存在删除异常。

断言1:一个小组里的每一名员工只由一位领导来管理。一个小组可能有多位领导。

emp_name, team_name –> leader_name

断言2:每一位领导只会参与一个组的管理。

leader_name –> team_name

emp_nameteam_nameleader_name
SuttonHawksWei
SuttonCondorsBachmann
NivenHawksWei
NivenEaglesMakowski
WilsonEaglesDeSmith

(图5  team表)

team表满足第三范式,具有复合候选键emp_name, team_name

team表有如下删除异常:若Sutton离开了Condors组,Bachmann为Condors组的领导这一信息将丢失。

消除这一删除异常最简单的方法是根据两条断言创建两张表,通过两张表中冗余的信息来消除删除异常。这一分解是无损的并保持了所有原先的函数依赖,但这降低了更新性能,并需要更多存储空间。为了避免删除异常,这样做是值得的。

注:无损分解是指把一张表分解为两张小表后,通过对两张小表进行natural join得到的表与原始表相同,不会产生任何多余行。

数据库范式化示例

(图6  employee数据库ER图)

该案例基于图6中的ER模型和以下相关函数依赖。一般而言,函数依赖可通过分析ER图及业务经验推得。

  1. emp_id, start_date –> job_title, end_date
  2. emp_id –> emp_name, phone_no, office_no, proj_no, proj_name, dept_no
  3. phone_no –> office_no
  4. proj_no –> proj_name, proj_start_date, proj_end_date
  5. dept_no –> dept_name, mgr_id
  6. mgr_id –> dept_no

我们的目标是设计至少能达到第三范式(3NF)的关系数据库表结构,并尽可能减少表的数量。

如果将函数依赖1至6放入一张表,并设置复合主键:emp_id, start_date,那么我们违反了第三范式,因为函数依赖2至6的等式左侧不是超键。因此,我们需要把函数依赖1从其余的函数依赖中分离出来。如果将函数依赖2至6进行合并,我们将得到很多传递依赖。故函数依赖2、3、4、5必须分到不同的表中。我们再来考虑函数依赖5和6是否能在不违反第三范式的前提下进行合并。因为mgr_id和dept_no是相互依赖的,这两个属性在表中都是超键,所以可以合并。

通过合理的映射函数依赖1至6,我们能得到如下表:

emp_hist:      emp_id, start_date –> job_title, end_date

employee:    emp_id –> emp_name, phone_no, proj_no, dept_no

phone:            phone_no –> office_no

project:           proj_no –> proj_name, proj_start_date, proj_end_date

department: dept_no –> dept_name, mgr_id

                           mgr_id –> dept_no

这一解决方案涵盖了所有函数依赖。满足第三范式和BCNF范式,同时该方案创建了最少数量的表。

范式化从ER图得到的候选表

在数据库生命周期中,对表的范式化是通过分析表的函数依赖完成的。这些函数依赖包括:从需求分析中得到的函数依赖;从ER图中得到的函数依赖;从直觉中得到的函数依赖。

主函数依赖代表了实体键之间的依赖。次函数依赖代表实体内数据元素间的依赖。一般来说,主函数依赖可从ER图中得到,次函数依赖可从需求分析中得到。表1展示了每种基本ER构件所能得到的主函数依赖。

关系的度(Degree)关系的连通数(Connectivity)主函数依赖
二元或二元回归“一对一”
“一对多”
“多对多”
2个:键(“一”侧) –> 键(“一”侧)
1个:键(“多”侧) –> 键(“一”侧)
无(由两侧键组成的组合键)
三元“一对一对一”
“一对一对多”
“一对多对多”
“多对多对多”
3个:键(“一”),键(“一”) –> 键(“一”)
2个:键(“一”),键(“多”) –> 键(“一”)
1个:键(“多”),键(“多”) –> 键(“一”)
无(有三侧键组成的组合键)
泛化

每个候选表一般会有多个主函数依赖和次函数依赖,这决定了当前表的范式化程度。对每个表采用各种技术使其达到需求规格中要求的范式化程度,在范式化过程中要保证数据完整性,即范式化后得到的表应包含原先候选表的所有函数依赖。精心设计的概念数据模型通常能得到基本已范式化的表,后期的范式化处理不会很困难,所以概念数据建模非常重要。

主要内容回顾

1. 不良的表结构设计将导致表数据的更新异常(update anomaly)、插入异常(insert anomaly)、删除异常(delete anomaly)

2. 范式化通过消除冗余数据,来解决数据库存在的一致性、完整性和可维护性等方面的问题。

3. 在实际数据库设计中,范式化的目标一般是达到第三范式或BCNF范式。

4. 精心设计的概念数据模型(ER模型)能帮助我们得到范式化的表。

数据库范式化参考资料

1. Database Normalization(http://en.wikipedia.org/wiki/Database_normalization

2. 3 Normal Forms Database Tutorial(http://www.phlonx.com/resources/nf3/

下一篇:数据库设计Step by Step (11)——通用设计模式(系列完结篇)

数据库设计Step by Step (9)——ER-to-SQL转化

阅读原文 转自博客园 知行思新

引言:前文(数据库设计 Step by Step (8)——视图集成)讨论了如何把局部ER图集成为全局ER图。有了全局ER图后,我们就可以把ER图转化为关系数据库中的SQL表了。俯瞰整个数据库生命周期(如下图所示),找到我们的“坐标”。

把ER图转化为关系数据库中的表结构是一个非常自然的过程。许多ER建模工具除了辅助绘制ER图外,还能自动地把ER图转化为SQL表。

从ER模型到SQL表

从ER图转化得到关系数据库中的SQL表,一般可分为3类。

1. 转化得到的SQL表与原始实体包含相同信息内容。该类转化一般适用于:

二元“多对多”关系中,任何一端的实体

二元“一对多”关系中,“一”一端的实体

二元“一对一”关系中,某一端的实体

二元“多对多”回归关系中,任何一端的实体(注:关系两端都指向同一个实体)

三元或n元关系中,任何一端的实体

层次泛化关系中,超类实体

2. 转化得到的SQL表除了包含原始实体的信息内容之外,还包含原始实体父实体的外键。该类转化一般适用于:

二元“一对多”关系中,“多”一端的实体

二元“一对一”关系中,某一端的实体

二元“一对一”或“一对多”回归关系中,任何一端的实体

该转化是处理关系的常用方法之一,即在子表中增加指向父表中主键的外键信息。

3. 由“关系”转化得到的SQL表,该表包含“关系”所涉及的所有实体的外键,以及该“关系”自身的属性信息。该类转化一般适用于:

二元“多对多”关系

二元“多对多”回归关系

三元或n元关系

该转化是另一种常用的关系处理方法。对于“多对多”关系需要定义为一张包含两个相关实体主键的独立表,该表还能包含关系的属性信息。

转化过程中对于NULL值的处理规则

1. 当实体之间的关系是可选的,SQL表中的外键列允许为NULL。

2. 当实体之间的关系是强制的,SQL表中的外键列不允许为NULL。

3. 由“多对多”关系转化得到的SQL表,其中的任意外键列都不允许为NULL。

一般二元关系的转化

1. “一对一”,两实体都为强制存在

当两个实体都是强制存在的(如图1所示),每一个实体都对应转化为一张SQL表,并选择两个实体中任意一个作为主表,把它的主键放入另一个实体对应的SQL表中作为外键,该表称为从表。

(图1 “一对一”,两实体都为强制存在)

图1表示的语义为:每一张报表都有一个缩写,每一缩写只代表一张报表。转化得到的SQL表定义如下:

注:本节中所有SQL代码在SQL Server 2008环境中测试通过。

2. “一对一”,一实体可选存在,另一实体强制存在

当两个实体中有一个为“可选的”,则“可选的”实体对应的SQL表一般作为从表,包含指向另一实体的外键(如图2所示)

(图2 “一对一”,一实体可选存在,另一实体强制存在)

图2表示的语义为:每一个部门必须有一位经理,大部分员工不是经理,一名员工最多只能是一个部门的经理。转化得到的SQL表定义如下:

create table employee
(
  emp_id char(10),
  emp_name char(20),
  primary key(emp_id)
);
create table department
(
  dept_no integer,
  dept_name char(20),
  mgr_id char(10) not null unique,
  primary key(dept_no),
  foreign key(mgr_id) references employee
  on update cascade
);

另一种转化方式是把“可选的”实体作为主表,让“强制存在的”实体作为从表,包含外键指向“可选的”实体,这种方式外键列允许为NULL。以图2为例,可把实体Employee转化为从表,包含外键列dept_no指向实体Department,该外键列将允许为NULL。因为Employee的数量远大于Department的数量,故会占用更多的存储空间。

3. “一对一”,两实体都为可选存在

当两个实体都是可选的(如图3所示),可选任意一个实体包含外键指向另一实体,外键列允许为NULL值。

(图3 “一对一”,两实体都为可选存在)

图3表示的语义为:部分台式电脑被分配给部分工程师,一台电脑只能分配给一名工程师,一名工程师最多只能分配到一台电脑。转化得到的SQL表定义如下:

create table engineer
(
  emp_id char(10),
  emp_name char(20),
  primary key(emp_id)
);
create table desktop
(
  desktop_no integer,
  emp_id char(10),
  primary key(desktop_no),
  foreign key(emp_id) references engineer
  on delete set null on update cascade
);

4. “一对多”,两实体都为强制存在

在“一对多”关系中,无论“多”端是强制存在的还是可选存在的都不会影响其转化形式,外键必须出现在“多”端,即“多”端转化为从表。当“一”端实体是可选存在时,“多”端实体表中的外键列允许为NULL。

(图4 “一对多”,两实体都为强制存在)

图4表示的语义为:每名员工都属于一个部门,每个部门至少有一名员工。转化得到的SQL表定义如下:

create table department
(
  dept_no integer,
  dept_name char(20),
  primary key(dept_no)
);
create table employee
(
  emp_id char(10),
  emp_name char(20),
  dept_no integer not null,
  primary key(emp_id),
  foreign key(dept_no) references department
  on update cascade
);

5. “一对多”,一实体可选存在,另一实体强制存在

(图5 “一对多”,一实体可选存在,另一实体强制存在)

图5表示的语义为:每个部门至少发布一张报表,一张报表不一定由某个部门来发布。转化得到的SQL表定义如下:

create table department
(
   dept_no integer,
   dept_name char(20),
   primary key(dept_no)
);

create table report
(
   report_no integer,
   dept_no integer,
   primary key(report_no),
   foreign key(dept_no) references department
   on delete set null on update cascade
);

注:解释一下report表创建脚本的最后一行“on delete set null on update cascade”的用处。当没有这一行时,更新department表中dept_no字段会失败,删除department中记录也会失败,报出与外键约束冲突的提示。如果有了最后一行,更新department表中dept_no字段,report表中对应记录的dept_no也会同步更改,删除department中记录,会使report表中对应记录的dept_no值变为NULL。

6. “多对多”,两实体都为可选存在

在“多对多”关系中,需要一张新关系表包含两个实体的主键。无论两边实体是否为可选存在的,其转化形式一致,关系表中的外键列不能为NULL。实体可选存在,在关系表中表现为是否存在对应记录,而与外键是否允许NULL值无关。

(图6 “多对多”,两实体都为可选存在)

图6表示的语义为:一名工程师可能是专业协会的会员且可参加多个专业协会。每一个专业协会可能有多位工程师参加。转化得到的SQL表定义如下:

create table engineer
(
  emp_id char(10),
  primary key(emp_id)
);

create table prof_assoc
(
  assoc_name varchar(256),
  primary key(assoc_name)
);

create table belongs_to
(
  emp_id char(10),
  assoc_name varchar(256),
  primary key(emp_id, assoc_name),
  foreign key(emp_id) references engineer
  on delete cascade on update cascade,
  foreign key(assoc_name) references prof_assoc
  on delete cascade on update cascade
);

二元回归关系的转化

对于“一对一”或“一对多”回归关系的转化都是在SQL表中增加一列与主键列类型、长度相同的外键列指向实体本身。外键列的命名需与主键列不同,表明其用意。外键列的约束根据语义进行确定。

7. “一对一”,两实体都为可选存在

(图7 “一对一”,两实体都为可选存在)

图7表示的语义为:公司员工之间可能存在夫妻关系。转化得到的SQL表定义如下:

create table employee
(
  emp_id char(10),
  emp_name char(20),
  spouse_id char(10),
  primary key(emp_id),
  foreign key(spouse_id) references employee
);

8. “一对多”,“一”端为强制存在,“多”端为可选存在


(图8 “一对多”,“一”端为强制存在,“多”端为可选存在)

图8表示的语义为:工程师被分为多个组,每个组有一名组长。

转化得到的SQL表定义如下:

create table engineer
(
emp_id char(10),
leader_id char(10) not null,
primary key(emp_id),
foreign key(leader_id) references engineer
);

“多对多”回归关系无论是可选存在的还是强制存在的都需新增一张关系表,表中的外键列须为NOT NULL。

9. “多对多”,两端都为可选存在

(图9 “多对多”,两端都为可选存在)


图9表示的语义为:社交网站中人之间的朋友关系,每个人都可能有很多朋友。转化得到的SQL表定义如下:

create table person
(
   person_id char(10),
   person_name char(20),
   primary key(person_id)
);

create table friend
(
   person_id char(10),
   friend_id char(10), 
   primary key(person_id, friend_id),
   foreign key(person_id) references person,
   foreign key(friend_id) references person,
   check(person_id < friend_id) );
}

三元和n元关系的转化 无论哪种形式的三元关系在转化时都会创建一张关系表包含所有实体的主键。三元关系中,“一”端实体的个数决定了函数依赖的数量。因此,“一对一对一”关系有三个函数依赖式,“一对一对多”关系有两个函数依赖式,“一对多对多”关系有一个函数依赖式。“多对多对多”关系的主键为所有外键的联合。 10. “一对一对一”三元关系

(图10 “一对一对一”三元关系)

图10表示的语义为:
1名技术员在1个项目中使用特定的1本记事簿
1本记事簿在1个项目中只属于1名技术员
1名技术员的1本记事簿只用于记录1个项目
注:1名技术员仍可以做多个项目,对于不同的项目维护不同的记事簿。
转化得到的SQL表定义如下:

create table technician (
  emp_id char(10),
  primary key(emp_id)
);

create table project (
  project_name char(20),
  primary key(project_name)
);

create table notebook (
  notebook_no integer,
  primary key(notebook_no)
);

create table uses_notebook (
  emp_id char(10),
  project_name char(20),
  notebook_no integer not null,
  primary key(emp_id, project_name),
  foreign key(emp_id) references technician on delete cascade on update cascade,
  foreign key(project_name) references project on delete cascade on update      cascade,
  foreign key(notebook_no) references notebook on delete cascade on update cascade,
  unique(emp_id, notebook_no), unique(project_name, notebook_no)
);

函数依赖

emp_id, project_name -> notebook_no

emp_id, notebook_no -> project_name

project_name, notebook_no -> emp_id

11. “一对一对多”三元关系

(图11 “一对一对多”三元关系)

图11表示的语义为:

参与1个项目的1名员工只会在1个地点做该项目

1名员工在1个地点只能做1个项目

1个地点的1个项目可能有多名员工参与

注:1名员工可以在不同的地点做不同的项目

转化得到的SQL表定义如下:

create table employee
(
  emp_id char(10),
  emp_name char(20),
  primary key(emp_id)
);

create table project
(
  project_name char(20),
  primary key(project_name)
);

create table location
(
   loc_name char(15),
   primary key(loc_name)
);

create table assigned_to
(
  emp_id char(10),
  project_name char(20),
  loc_name char(15) not null,
  primary key(emp_id, project_name),
  foreign key(emp_id) references employee
  on delete cascade on update cascade,
  foreign key(project_name) references project
  on delete cascade on update cascade,  
  foreign key(loc_name) references location
  on delete cascade on update cascade,
  unique(emp_id, loc_name)
);

函数依赖:
emp_id, loc_name -> project_name

emp_id, project_name -> loc_name

12. “一对多对多”三元关系

(图12 “一对多对多”三元关系)


图12表示的语义为:

1个项目中的1名工程师只会有1名经理

1个项目中的1名经理会带领多名工程师做该项目

1名经理和他手下的1名工程师可能参与多个项目

转化得到的SQL表定义如下:

create table project
(
  project_name char(20),
  primary key(project_name)
);

create table manager
(
  mgr_id char(10),
  primary key(mgr_id)
);

create table engineer
(
  emp_id char(10),
  primary key(emp_id)
);

create table manages
(
  project_name char(20),
  mgr_id char(10) not null,
  emp_id char(10),
  primary key(project_name, emp_id),
  foreign key(project_name) references project
  on delete cascade on update cascade,
  foreign key(mgr_id) references manager
  on delete cascade on update cascade,
  foreign key(emp_id) references engineer
  on delete cascade on update cascade
);

函数依赖:

project_name, emp_id -> mgr_id

13. “多对多对多”三元关系

(图13 “多对多对多”三元关系)

图13表示的语义为:

1名员工在1个项目中可以运用多种技能

1名员工的1项技能可以在多个项目中运用

1个项目中的1项技能可以被参与该项目的多名员工运用

转化得到的SQL表定义如下:

create table employee
(
  emp_id char(10),
  emp_name char(20),
  primary key(emp_id)
);

create table skill
(
  skill_type char(15),
  primary key(skill_type)
);

create table project
(
  project_name char(20),
  primary key(project_name)
);

create table sill_used
(
  emp_id char(10),
  skill_type char(15),
  project_name char(20),
  primary key(emp_id, skill_type, project_name),
  foreign key(emp_id) references employee
  on delete cascade on update cascade,
  foreign key(skill_type) references skill
  on delete cascade on update cascade,
  foreign key(project_name) references project
  on delete cascade on update cascade
);

函数依赖:

泛化与聚合

泛化抽象结构中的超类实体和各子类实体分别转化为对应的SQL表。超类实体转化得到的表包含超类实体的键和所有公共属性。子类实体转化得到的表包含超类实体的键和子类实体特有的属性。

要保证泛化层次中数据的完整性就必须保证某些操作在超类表和子类表的之间的同步。若超类表的主键需做更新,则子类表中对应记录的外键必须一起更新。若需删除超类表中的记录,子类表中对应记录也需一起删除。我们可以在定义子类表时加入外键级联约束。这一规则对于覆盖与非覆盖的子类泛化都适用。

14. 泛化层次关系

(图14 泛化层次关系)

图14表示的语义为:

个人可能是一名员工,或是一位顾客,或同时是员工与顾客,或两者都不是

转化得到的SQL表定义如下:

create table individual
(
  indiv_id char(10),
  indiv_name char(20),
  indiv_addr char(20),
  primary key(indiv_id)
);

create table employee
(
  emp_id char(10),
  job_title char(15),
  primary key(emp_id),
  foreign key(emp_id) references individual
  on delete cascade on update cascade
);

create table customer
(
  cust_no char(10),
  cust_credit char(12),
  primary key(cust_no),
  foreign key(cust_no) references individual
  on delete cascade on update cascade
);

有些数据库开发者还会在超类表中增加一个鉴别属性。鉴别属性对于每一种子类有不同的值,表示从哪一个子类中能获得进一步的信息。

聚合抽象的转化方式也是为超类实体和每一个子类实体生成SQL表,但聚合中的超类与子类没有公共属性和完整性约束。聚合的主要功能是提供一种抽象来辅助视图集成的过程。

转化步骤

以下总结了从ER图到SQL表的基本转化步骤

1. 把每一个实体转化为一张表,其中包含键和非键属性。

2. 把每一个“多对多”二元或二元回归关系转化为一张表,其中包含实体的键和关系的属性。

3. 把三元及更高元(n元)关系转化为一张表。

让我们一一对这三个步骤进行讨论。

实体转化

若两个实体之间是“一对多”关系,把“一”端实体的主键加入到“多”端实体表中作为外键。若两实体间是“一对一”关系,把某个“一”端实体的主键放入另一实体表中作为外键,加入外键的实体理论上可以任选,但一般会遵循如下原则:按照实体间最为自然的父子关系,把父实体的键放入子实体中;另一种策略是基于效率,把外键加入到具有较少行的表中。

把泛化层次中的每一个实体转化为一张表。每张表都会包含超类实体的键。事实上子类实体的主键同时也是外键。超类表中还包含所有相关实体的公共非键属性,其他表包含每一子类实体特有的非键属性。

转化得到的SQL表可能会包含not null, unique, foreign key等约束。每一张表必须有一个主键(primary key),主键隐含着not null和unique约束。

“多对多”二元关系转化

每一个“多对多”二元关系能转化为一张表,包含两个实体的键和关系的属性。

这一转化得到的SQL表可能包含not null约束。在这里没有使用unique约束的原因是关系表的主键是由各实体的外键复合组成的,unique约束已隐含。

三元关系转化

每一个三元(或n元)关系转化为一张表,包含相关实体的n个主键以及该关系的属性。

这一转化得到的表必须包含not null约束。关系表的主键由各实体的外键复合组成。n元关系表具有n个外键。除主键约束外,其他候选键(candidate key)也应加上unique约束。

ER-to-SQL转化步骤示例

把数据库设计Step by Step (7)——概念数据建模中最后得到的公司人事和项目数据库的全局ER图(图9)转化为SQL表。

1. 直接由实体生成的SQL表有:

Division Department Employee Manager Secretary Engineer

Technician Skill Project Location Prof_assoc Desktop

Workstation

2. 由“多对多”二元关系及“多对多”二元回归关系生成的SQL表有:

belongs_to

3. 由三元关系生成的SQL表有:

skill_used assigned_to

总结与回顾

1. 通过一些简单的规则就能把ER模型中的实体、属性和关系转化为SQL表。

2. 实体在转化为表的过程中,其中的属性一一被映射为表的属性。

3. “一对一”或“一对多”关系中的“子”端实体转化成的SQL表必须包含另一端实体的主键,作为外键。

4. “多对多”关系转化为一张表,包含相关实体的主键,复合组成其自身的主键。同时这些键在SQL中定义为外键分别指向各自的实体。

5. 三元或n元关系被转化为一张表,包含相关实体的主键。这些键在SQL中定义为外键。这些键中的子集定义为主键,其基于该关系的函数依赖。

6. 泛化层次的转化规则要求子类实体从超类实体继承主键。

7. ER图中的可选约束在转化为SQL时,表现为关系的某一端实体允许为null。在ER图中没有明确标识可选约束时,创建表时默认not null约束。

下一篇:数据库设计Step by Step (10)——范式化

数据库设计 Step by Step (8)——视图集成

阅读原文 转自博客园 知行思新

引言:在前文(数据库设计Step by Step (7)——概念数据建模)最后的案例中,我们通过集成多个局部的实体关系(ER)模型最终得到了全局ER图。在现实项目中视图集成可能并不会那么容易。

俯瞰整个数据库生命周期(如下图所示)。在前面的内容中,我们已完成了“确定需求”和“数据模型”(图中以灰色标出),本小节我们将详细讨论“视图集成”(图中以红色标出)

把基于不同用户视角的局部ER图集成为一个统一的、没有冗余的全局ER图在数据库设计流程中非常重要。单个局部ER图是通过分析用户需求进行概念数据建模得到的;全局ER图是通过对各个局部ER图进行分析,解决其中存在的视角和术语差异,最终进行组合得到的。

为什么会产生不一致的局部ER图

当不同的用户或用户组从各自的视角来看业务时就会产生各异的ER图。举例来说市场部趋向于把整个产品作为销售的基本单元,但工程部可能更关注组成产品的单个零件。另一个例子,一个用户可能关注项目的目标和产生的价值,而另一个用户则关心项目需要占用的资源和所涉及的人员。上述的这些差异造成了各个ER图之间不一致的关系和术语。ER图的不一致性会表现为:不同的泛化程度;不同的关系连通数(一对多、多对多等);不同用户视角定义的实体、属性或关系(相同的概念,不同的人使用了不同的名称与建模形式)。

举例来说,同一个现实场景(客户下订单,订购产品),从三个不同视角建模得到的ER图如下。

(图1  把order看作实体)
(图2  把order看作关系)
(图3  把order看作属性)

图1中,Customer、Order、Product描述为实体,把“places”和“for-a”描述为关系。

图2中,“orders”定义为Customer和Product之间的关系。

图3中,“orders”关系被另一个关系“purchases”代替。“order-no”被作为关系“purchases”的一个属性。

同是订单(order),从不同视角出发在ER图中被表示为实体、关系、属性。

视图集成的步骤

局部ER图(概念数据模型)的集成一般有如下四个步骤。

  1. 集成策略选择
  2. 比较实体关系图
  3. 统一实体关系元素
  4. 合并、重构实体关系图

我们一一对这四个步骤进行讨论。

集成策略选择

通常的集成策略有:

1.每次集成2个局部ER图。

2.每次集成n个局部ER图(n大于2且小于等于总ER图数)。

相对来说第一种集成策略每次所涉及的实体、关系数量较少,也更容易掌控。

比较实体关系图

设计者需要仔细观察不同ER图中的对应实体,发现其中因视角不同而存在的冲突。

命名上的冲突包括“同物异名”和“异物同名”。“同物异名”是指同一个概念使用了不同的名称,可以通过检视数据字典(命名及其描述对应表)来发现。“异物同名”是指对不同的概念使用了相同的名称,需要通过检视不同ER图中相同的名称来发现。

结构性冲突的表现形式更多。类型冲突包括使用不同的构造方式建模同一概念。以图1、2、3为例,order这一概念可以建模为一个实体,一个关系或一个属性。依赖冲突是指类似或相同的关系在不同的局部ER图中被建模成不同的连通数。解决这种冲突的一种方法是使用最一般的连通数约束,如多对多。若这样做会造成语义上的错误,则说明两种关系概念不同不能合并,应进行改名并让每个关系保持各自的连通数。键冲突是指在不同的局部ER图中,同一概念的实体被分配了不同的键。举例来说,当一名员工的全名、员工号、员工身份证号在不同的局部ER图中被作为员工的键时,就出现了键冲突。

统一实体关系元素

基本目标是解决各局部ER图中的冲突,使这些元素一致化,为最终的ER图集成做准备。要解决各局部ER图之间的冲突通常需要设计开发人员与用户进行积极的沟通,了解、分析、理解冲突元素的真实语义。

我们可能需要对某些ER图中的实体及键属性进行改名。各局部ER图中被建模为实体、关系或属性的同一概念需要统一转化为三种形式之一。

集成具有相同的度、角色和连通数属性的关系相对较为容易,但集成上述特征不同的关系就较为困难。若同一关系在不同局部ER图中表现形式不一致,则必须进行统一。如:某一关系在一局部ER图中为泛化层次关系,在另一局部ER图中为排他性或(exclusive OR)关系,这种情况必须统一。

合并、重构实体关系图

合成和重构局部ER图,最终得到完整、最简约和可理解的全局ER图。

完整是要求在全局ER图中所有组件的语义完整。

最简约是要求去除全局ER图中的冗余。冗余的概念包括:重叠的实体、多余的语义关系等。例如“社会车辆”和“私家车”可能是重叠的两个实体;教授与学生之间的“指导”和“建议”关系可能代表了同一种活动,需要进一步确定是否存在冗余。

可理解要求全局ER图能被整个项目组成员和最终用户理解。

在进行ER图集成过程中,我们可以首先将相同概念的组件进行集成,之后对获得的初步全局ER图进行重构以使其满足上述三方面的要求。举例来说,集成后的ER图包含超类实体与子类实体的层次组合,若超类实体中的属性已涵盖子类实体中的某些属性,则子类实体的这些属性可以去除。

了解目标

让我们看一下两张具有重叠数据的局部ER图。这两张ER图是对两组不同用户访谈后画出的。

图4是一张以报表为关注点的ER图,其中包含发布报表的部门、报表中的主题和报表提交的对象。

(图4  关注报表)

图5的ER图以发布作为关注中心,把发布内容中的关键词建模为另一个实体。

(图5  关注发布)

我们的目标是整合这两张ER图,并保证合成后的ER图语义完整、形式最简约且易理解。

集成步骤

首先,在两张局部ER图中寻找是否存在“同物异名”与“异物同名”现象。图4中的实体Topic-area与图5中的实体Keyword为“同物异名”,虽然两个实体的属性不完全相同,但两者属性是兼容的,可以进行统一化。对图5进行修改,可得到图6。

(图6  Keyword换为Topic_area)

其次,再来看两张ER图之间的结构性冲突。图4中的实体Department与图5中的属性dept-name为类型冲突。解决该冲突的方法是保留强类型(实体Department),把属性dept-name移至实体Department中。解决该冲突,把ER图6转化为ER图7。

(图7  属性dept-name转化为一个实体和一个属性)

比较变化后的各局部ER图,寻找之间的“共同之处”进行合并。 在真正合并之前必须确认这些“共同之处”的语义概念完全等同,这也保证了合并后语义的完整性。在ER图4与ER图7中有两个共同实体:Department和Topic-area,且语义一致。初步合并后的全局ER图如图8所示。

(图8  初步合并图4和图7后的全局ER图)

图8中实体Publication和Report与实体Department和Topic-area之间的关系存在冗余。通过与用户的再次确认,了解到Publication是Report的泛化(报表只是发布材料中的一种),故不能简单的去除实体Publication及关系have和include来消除冗余,而可以引入泛化关系并去除冗余关系publish和contain。

图9展示了增加泛化关系后的ER图(Publication为超类型,Report为子类型)

(图9  加入泛化关系)

图10中实体Report与实体Department和Topic-area之间的冗余关系publish和contain被去除了。Report中的属性title也被去除了,因为该属性已经出现在其超类型实体Publication中了。

(图10  去除冗余关系)

最终得到的ER图10达到了语义完整、最简约、易理解的目标。ER图集成是一个持续优化和评估的过程。需要注意的是“最简约”未必会最高效。如ER图10中去除的“publish”和“contain”关系,保留它们可能对性能有帮助。在后期的分析或测试过程中可根据需要重构ER图。

1. 不同的用户或用户组视角将产生不同的局部ER图

2. 局部ER图之间的冲突包括:命名冲突、类型冲突、依赖冲突、键冲突

3. 视图集成的目标是得到语义完整、形式简约且易于理解的全局ER图

4. 视图集成能进一步加强项目组对系统整体需求的理解与把握

下一篇:数据库设计Step by Step (9)——ER-to-SQL转化