ER 和 数据库关系模式

http://lianghuanyue123.blog.163.com/blog/static/130423244201162011850600/

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

什么是关系？关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性，每个元组是该关系涉及到属性集合笛卡尔积的一个元素。（笛卡尔积是这样的一个集合。集合中的元素是有序对，若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表示的语义为：部分台式电脑被分配给部分工程师，一台电脑只能分配给一名工程师，一名工程师最多只能分配到一台电脑。转化得到的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表示的语义为：一名工程师可能是专业协会的会员且可参加多个专业协会。每一个专业协会可能有多位工程师参加。转化得到的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表示的语义为：工程师被分为多个组，每个组有一名组长。转化得到的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名工程师只会有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. “多对多对多”三元关系

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