文档章节

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

自由的角马
 自由的角马
发布于 2015/01/10 13:56
字数 5056
阅读 12
收藏 0

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

image

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

 

image

从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表中作为外键,该表称为从表。

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create  table  report
(
     report_no integer ,
     report_name varchar (256),
     primary  key (report_no)
);
create  table  abbreviation
(
     abbr_no char (6),
     report_no integer  not  null  unique ,
     primary  key (abbr_no),
     foreign  key (report_no) references  report
         on  delete  cascade  on  update  cascade
);

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

 

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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值。

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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。

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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. “一对多”,一实体可选存在,另一实体强制存在

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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值无关。

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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. “一对一”,两实体都为可选存在

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

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

?
1
2
3
4
5
6
7
8
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. “一对多”,“一”端为强制存在,“多”端为可选存在

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

图8表示的语义为:工程师被分为多个组,每个组有一名组长。转化得到的SQL表定义如下:

?
1
2
3
4
5
6
7
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. “多对多”,两端都为可选存在

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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. “一对一对一”三元关系

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

图10表示的语义为:

1名技术员在1个项目中使用特定的1本记事簿

1本记事簿在1个项目中只属于1名技术员

1名技术员的1本记事簿只用于记录1个项目

注:1名技术员仍可以做多个项目,对于不同的项目维护不同的记事簿。

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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. “一对一对多”三元关系

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

图11表示的语义为:

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

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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. “一对多对多”三元关系

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

图12表示的语义为:

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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. “多对多对多”三元关系

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

图13表示的语义为:

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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. 泛化层次关系

image(图14  泛化层次关系)

图14表示的语义为:

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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表,但聚合中的超类与子类没有公共属性和完整性约束。聚合的主要功能是提供一种抽象来辅助视图集成的过程。

 

image

转化步骤

以下总结了从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约束。


原文:http://www.cnblogs.com/DBFocus/archive/2011/07/25/2116609.html

本文转载自:http://blog.csdn.net/luoweifu/article/details/8871311

上一篇: 线性表
下一篇: 工作第一天
自由的角马
粉丝 1
博文 269
码字总数 0
作品 0
文山
私信 提问
机房合作之ER模型图“生成SQL文件”并“转换ER实体联系图”

前言 小编前一周在编写数据库文档时,涉及到ER图的设计和数据库的生成,不再使用之前机械式的手动设计ER图和添加一张张表了,下面小编将这种方法分享给大家。 一、 构建ER模型图 1.新建一个图...

m18633778874
2017/12/08
0
0
Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I

Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I Published 3 years ago by Jignesh Jethwa Here in this article I am going to cover steps by s......

rootliu
2017/10/28
3K
0
12c:CREATE DATABASE——DBCA

界面1:Welcome 选择Create a Database,点击next 界面2:Step 2 of 14: Creation Mode 选择Advanced configuration,点击next 界面3:Step 3 of 14: Database Templates 选择Custom Databa......

ryan_bai
2018/01/04
0
0
史上最简单的 MySQL 教程(三十九)「事务(下)」

温馨提示:在「事务(上)」中,我们已经了解了如何在 MySQL 中开启事务,以及事务的一些基本操作。在本文中,我们将进一步学习事务的知识,包括事务原理、自动事务、回滚点和事务特性等。 ...

qq_35246620
2017/11/18
0
0
SQLite之C++增删改查(详解)

很高兴加入开源中国社区,开源可以让我们成长更快、更强大,如果每个人都能多多交流。。。我们的未来必将不可限量!毫无疑问,这并不夸张,但是现在我还是从一个菜鸟级程序员开始入门吧! 进...

╰☆╮絠ㄚòひ才倖❤福`
2012/08/23
5.4K
1

没有更多内容

加载失败,请刷新页面

加载更多

JS基础-该如何理解原型、原型链?

JS的原型、原型链一直是比较难理解的内容,不少初学者甚至有一定经验的老鸟都不一定能完全说清楚,更多的"很可能"是一知半解,而这部分内容又是JS的核心内容,想要技术进阶的话肯定不能对这个...

OBKoro1
今天
7
0
高防CDN的出现是为了解决网站的哪些问题?

高防CDN是为了更好的服务网络而出现的,是通过高防DNS来实现的。高防CDN是通过智能化的系统判断来路,再反馈给用户,可以减轻用户使用过程的复杂程度。通过智能DNS解析,能让网站访问者连接到...

云漫网络Ruan
今天
14
0
OSChina 周一乱弹 —— 熟悉的味道,难道这就是恋爱的感觉

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @xiaoshiyue :好久没分享歌了分享张碧晨的单曲《今后我与自己流浪》 《今后我与自己流浪》- 张碧晨 手机党少年们想听歌,请使劲儿戳(这里)...

小小编辑
今天
3K
24
SpringBoot中 集成 redisTemplate 对 Redis 的操作(二)

SpringBoot中 集成 redisTemplate 对 Redis 的操作(二) List 类型的操作 1、 向列表左侧添加数据 Long leftPush = redisTemplate.opsForList().leftPush("name", name); 2、 向列表右......

TcWong
今天
46
0
排序––快速排序(二)

根据排序––快速排序(一)的描述,现准备写一个快速排序的主体框架: 1、首先需要设置一个枢轴元素即setPivot(int i); 2、然后需要与枢轴元素进行比较即int comparePivot(int j); 3、最后...

FAT_mt
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部