ER 和 数据库关系模式

我们眼下所接触的数据库基本上是关系数据库，关系数据库中的关系模式是型，而关系是值。关系模式是对关系的描写叙述。

什么是关系？关系实质上是一张二维表，当中每一行是一个元组，每一列是一个属性，每一个元组是该关系涉及到属性集合笛卡尔积的一个元素。（笛卡尔积是这种一个集合。集合中的元素是有序对，若A=｛0,1｝B=｛a，b｝则：A*B={<0,a>，<0,b>,<1,a>,<1,b>}）,关系是元组的集合，所以关系模式要描写叙述元组的集合，当中包含那些属性，属性来自域，属性与域之间的映射关系。

关系模式能够用五元组形式表示：R（U，D，Dom，F），当中R:表示关系名，U:表示属性集合，Dom,表示属性域（来自那个域）,F:表示函数依赖。

可是普通情况下，我们通常把关系模式表示为：R（U）或者R（A，B）（当中A、B代表U中的属性）

 

那么E-R图转化成关系模式的步骤。

前提：是已经把需求中的实体，以及实体中联系确定。

第一：把每一个实体都转化成关系模式R（A、B）形式。（A、B代表属性）

第二：实体中的属性即关系模式中的属性要求是满足第一范式（原子性，不可再分）

第三：也是最重要的步骤，实体之间联系的转换。

       实体之间的联系分为：1：1 ，1：n，m：n 三种形式。

    （1）1:1之间的转换。

          将两个实体转化成关系模式之后，然后把随意实体的主键和联系的属性放到还有一个实体的关系模式中。如图：

 

 

    

 结果例如以下：

   R1（教师编号、姓名、性别）

   R2（班级号、专业号、学院号、教师编号、任职日期）

   或者：

   R1（教师编号、姓名、性别、班级号、任职日期）

   R2（班级号、 专业号、学院号）

  （2）1：n的转换。

      将两个实体各自转化成关系模式后，然后，把联系数量为1的实体的主键和联系的属性放到联系数量为n的实体关系模式中。

       比方：学生和宿舍之间的关系。

    

 

 

        转换之后的结果例如以下：

        R1（学号、姓名、性别、宿舍号）

        R2（宿舍号、床位数）

 （3）n：n的转换。

         将两个实体各自转换成关系模式后，然后，把两个实体中的主键和联系的属性放到还有一个关系模式中.(注意多生成一个关系模式)

       

 

 

   

转换后的结果例如以下：

 R1（学号、姓名、性别）

 R2（奖励编号、奖励名称、奖励金额）

 R3（学号、奖励编号、奖励日期）

 

   注：E-R图中联系没有联系属性，EE-R图中含有联系属性。

   含有下划线的属性代表是主属性，在表中当做主键。红字体代表的外键。

 

 

 

以上的比較简单

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

这个非常专业

 

 

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

1. 当实体之间的关系是可选的，SQL表中的外键列同意为NULL。

2. 当实体之间的关系是强制的，SQL表中的外键列不同意为NULL。

3. 由“多对多”关系转化得到的SQL表，当中的随意外键列都不同意为NULL。

 

一般二元关系的转化

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

当两个实体都是强制存在的（如图1所看到的），每个实体都相应转化为一张SQL表，并选择两个实体中随意一个作为主表，把它的主键放入还有一个实体相应的SQL表中作为外键，该表称为从表。

（图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所看到的）。

（图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值。

（图3  “一对一”，两实体都为可选存在）

图3表示的语义为：部分台式电脑被分配给部分project师，一台电脑仅仅能分配给一名project师，一名project师最多仅仅能分配到一台电脑。转化得到的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。

（图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. “一对多”，一实体可选存在，还有一实体强制存在

（图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值无关。

（图6  “多对多”，两实体都为可选存在）

图6表示的语义为：一名project师可能是专业协会的会员且可參加多个专业协会。每个专业协会可能有多位project师參加。转化得到的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. “一对一”，两实体都为可选存在

（图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. “一对多”，“一”端为强制存在，“多”端为可选存在

（图8  “一对多”，“一”端为强制存在，“多”端为可选存在）

图8表示的语义为：project师被分为多个组，每一个组有一名组长。转化得到的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. “多对多”，两端都为可选存在

（图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. “一对一对一”三元关系

（图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. “一对一对多”三元关系

（图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. “一对多对多”三元关系

（图12  “一对多对多”三元关系）

图12表示的语义为：

1个项目中的1名project师仅仅会有1名经理

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

1名经理和他手下的1名project师可能參与多个项目

转化得到的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. “多对多对多”三元关系

（图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 );