8.2 数据库的设计

数据库的设计一般要从E-R模型开始，之后的步骤如下。

1．为每个实体建立一张表。

2．为每个表选择一个主键（建议添加一个没有实际意义的字段作为主键）。

3．增加外键以表示一对多关系。

4．建立新表表示多对多关系。

5．定义约束条件。

6．评价关系的质量，并进行必要的改进。（关于范式等知识请参考其他数据库书籍）。

7．为每个字段选择合适的数据类型和取值范围。

下面将详细讨论每个步骤并介绍数据库设计相关的一些知识。

8.2.1 E-R模型

传统的系统开发方法都把重点集中在系统的数据存储需求上，数据的存储需求包括实体、实体的属性以及实体间的关系。E-R（Entity-Relationship）模型即实体-关系模型，主要用于定义数据的存储需求，该模型已经广泛用于关系数据库设计中。E-R模型由实体、属性和关系3个基本要素构成。

实体：现实世界中的人或物。在E-R模型中，实体使用矩形表示。一个实体对应数据库表中的一条记录，若干个实体构成的实体集合对应于数据库中的一张表。例如学生管理系统中，学生张三是一个实体，张三对应于学生表（student）中的一条记录，若干个学生记录构成了数据库中的学生表（student）。

关系：实体之间存在的联系。在E-R模型中，关系使用线段表示。实体间的关系可以分为3类：一对一关系（1:1）、一对多关系（1:m）和多对多关系（m:n）。例如学校里一个班级只有一个班主任，一个班主任只在一个班级中任职，则班级实体与班主任实体之间存在一对一关系（班主任实体与班级实体之间也存在一对一关系）；一个班级中有若干名学生，而每名学生只属于某一个班级，则班级实体与学生实体之间存在一对多关系（而学生实体与班级实体之间存在一对一关系）；一门课程允许有若干个学生选修，而一个学生允许选修多门课程，则课程实体与学生实体之间存在多对多关系（学生实体与课程实体之间也存在多对多关系）。

属性：表示实体的某种特征。在E-R模型中，属性画在实体矩形的内部。一个属性对应于数据库表中的一列，也称为一个字段。例如学生实体存在学号属性、姓名属性等。

图8-2所示为某学生管理系统的E-R模型。

图8-2说明如下。

（1）该E-R模型使用PowerDesigner建模工具绘制，PowerDesigner是SAP公司的建模工具，使用它可以方便地实现数据库的分析与设计。关于PowerDesigner的使用请参考其他专业书籍。

（2）该E-R模型中共有课程、学生、班级和班主任4个实体，班级和班主任之间为1:1关系（班主任和班级之间为1:1关系），班级和学生之间为1:m关系（学生和班级之间为1:1关系），学生和课程之间为m:n关系（课程和学生之间为m:n关系）。注意这些关系的图形表示方法。

（3）必要时可以为实体间的关系命名。例如学生和课程实体间是多对多关系，可以将该关系命名为“成绩”。

（4）存在关系的两个实体间可以相互扮演对应的角色。例如学生实体对于课程实体扮演了“选修”的角色，课程实体对于学生实体扮演了“被选”的角色。

（5）实体名与属性名尽量使用语义化的英文。例如学生实体名可以命名为student，学号属性名可以命名为 student_no。本章牵涉到的实体名及属性名、数据库表名及字段名的命名方法为单词所有字母小写，单词间用下划线分隔。

（6）E-R模型中Characters(10)表示的是该属性的数据类型为长度为10的字符串。

（7）E-R模型中的<M>是单词mandatory的首字母，表示该字段满足非空约束。

学生管理系统的E-R模型制作完毕后，根据数据库设计的第1个步骤“为每个实体建立一张表”，得到学生管理系统的以下4张表。

学生（学号，姓名），使用语义化的英文表示为：student(student_no,student_name)。

课程（课程号，课程名），使用语义化的英文表示为：course(course_no,course_name)。

班级（班级号，班级名），使用语义化的英文表示为：classes(class_no,class_name)。

班主任（工号，姓名），使用语义化的英文表示为：teacher(teacher_no,teacher_name)。

班级的英文单词为class，这里使用语义化英文class的复数形式classes，目的是避免与面向对象编程中使用的“类”关键字 class 混淆。类似地，用户表使用 users 表示，为了避免与数据库管理系统中的user关键字混淆。

8.2.2 主键（Primary Key）

关系数据库中的表是由列和行构成的，和电子表格不同的是，数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键（Primary Key）。数据库表中主键有以下两个特征。

（1）表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）。

（2）数据库表中主键的值具有唯一性且不能取空值（NULL），当数据库表中的主键由多个字段构成时，每个字段的值不能取NULL。例如区号和地方号码的组合才能标识一个电话号码，此时区号和地方号码共同构成了电话号码的主键。对于电话号码而言，区号和地方号码都不能取NULL。

设计数据库表时，不建议使用复合主键，否则会给数据库表的维护带来极大的不便。有些程序员将学生student表中的学号student_no设置为该表的主键，因为在学校内完全可以通过学号唯一标识一个学生。但这里建议为每个表中增加一个没有实际意义的字段作为该表的主键。

通过向各数据库表中添加一个没有实际意义的字段作为该表的主键，既可避免“复合主键”情况的发生，同时又可以避免“意义更改”的可能性，防止主键数据被“业务逻辑”修改。例如在学生student表中加入student_id作为该表的主键，此时的student_id字段并没有实际意义，如果学生管理系统E-R模型中涉及的其他实体也进行相应的处理，此时E-R模型修改为如图8-3所示。

图8-3说明如下。

（1）E-R模型中的<pi>表示该字段为主键。

（2）E-R 模型中 Serial 表示的是该字段的数据类型为整数，且为自增型数据。Serial 对应于MySQL数据库中的auto_increment关键字，对应于SQLServer数据库中的identity(1,1)关键字。

经过数据库设计的第1和第2步骤，可以得到学生管理系统的如下4张表。

student(student_id,student_no,student_name)

course(course_id,course_no,course_name)

classes(class_id,class_no,class_name)

teacher(teacher_id,teacher_no,teacher_name)

8.2.3 实体间的关系与外键（Foreign Key）

图8-3所示的E-R模型中共有3个关系，其中班级实体和班主任实体之间为一对一关系，班级实体和学生实体之间为一对多关系，学生实体和课程实体之间为多对多关系。实体间的关系可以通过外键来表示。如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键。此时存储在表A中字段a的值，同时也是表B主键b的值。按照实体关系的分类，使用外键表示实体间的关系可分为以下几种情形。

（1）如果实体间的关系为一对多关系，则需要将“一”端实体的主键放到“多”端实体中，并作为“多”端实体的外键。以班级实体和学生实体之间的一对多关系为例，需要将班级实体的主键 class_id 放到学生实体中，作为学生实体的外键。修改后的学生实体对应的学生表为：student(student_id,student_no,student_name,class_id)，其中 class_id 为外键，它的值来自于 classes表中主键class_id的值。

（2） 实体间的一对一关系，可以看成一种特殊的一对多关系：将“一”端实体的主键放到另“一”端的实体中，并作为另“一”端的实体的外键，然后将该外键定义为唯一性约束。以班级实体和班主任实体之间的一对一关系为例，可以选择下面任何一种方案（学生管理系统采用的是方案1）。

方案1：将班级实体的主键class_id放入到班主任实体中作为班主任实体的外键，然后将该外键定义为唯一性约束。

修改后的班主任表为：teacher(teacher_id,teacher_no,teacher_name,class_id)，其中class_id为外键，并将该外键定义为唯一性约束，它的值来自于classes表中主键class_id的值。

方案2：将班主任实体的主键teacher_id放入到班级实体中作为班级实体的外键，然后将该外键定义为唯一性约束即可。

修改后的班级表为：classes(class_id,class_no,class_name,teacher_id)，其中teacher_id为外键，并将该外键定义为唯一性约束，它的值来自于teacher表中主键teacher_id的值。

（3）如果两个实体间的关系为多对多关系，则需要添加新表表示该多对多关系，并将两个实体的主键分别放入到新表中作为新表的字段。以学生实体和课程实体之间的多对多关系为例，需要创建一个成绩表 score，且成绩表 score 至少包含学生表的主键 student_id 和课程表的主键course_id两个字段。由于成绩表score本身存在成绩grade字段，并且还需要给成绩表score添加一个没有实际意义的主键score_id，经过这些步骤后，修改后的成绩表为：score(score_id,student_id,course_id,grade)，其中student_id和course_id是成绩表score中的两个外键，student_id的值来自于student表中主键student_id的值，course_id的值来自于course表中主键course_id的值。

经过数据库设计的前4个步骤，可以得到学生管理系统的5张表（5张表之间的关系如图8-4所示），分别如下。

student(student_id,student_no,student_name, class_id）（其中class_id字段是外键，该字段的值来自于classes表中class_id字段的值）

course(course_id,course_no,course_name)

classes(class_id,class_no,class_name)

teacher(teacher_id,teacher_no,teacher_name, class_id)（其中class_id字段是外键，该字段的值来自于classes表中class_id字段的值，且teacher表中class_id字段需满足唯一性约束条件）

score(score_id,student_id,course_id,grade）（其中 student_id 字段是外键，该字段的值来自于 student 表中 student_id 字段的值；course_id 字段也是外键，该字段的值来自于 course 表中course_id字段的值）

8.2.4 约束（Constraint）

设计数据库时，可以对表中的一些字段设置约束条件。常用的约束条件有6种：主键（primary key）约束、外键（foreign key）约束、唯一性（unique）约束、默认值（default）约束、非空（not NULL）约束以及检查（check）约束。

主键（primary key）约束：用来保证数据库表中记录的唯一性。在一张表中只允许设置一个主键，当然这个主键可以是一个字段，也可以是多个字段的组合（虽然不建议这么做）。在录入数据的过程中，必须在所有主键字段中输入数据，即任何主键字段不允许为NULL。建议添加一个没有实际意义的字段作为数据库表的主键，例如score表中的score_id以及student表中的student_id都是一些没有实际意义的字段作为主键。

外键（foreign key）约束：用来保证外键字段值与主键字段值的一致性，即当对一个表的数据进行操作，和它有关联的一个或多个表的数据能够同时发生改变。这就要求：外键字段与主键字段的数据类型（包括长度）必须相似或者可以相互转换（建议外键字段与主键字段的数据类型相同）；外键字段值要么是NULL，要么是主键字段值的“复制”。外键字段所在的表称为子表，主键字段所在的表称为父表。父表与子表通过主键字段与外键字段建立起了外键约束关系。例如score 表中 student_id 字段的数据类型与 student 表中 student_id 字段的数据类型完全相同，score表中student_id字段的值要么是NULL，要么是来自于student表中student_id字段的值。score表为student表的子表，student表为score表的父表。如果试图删除父表中的“主键值”记录，由于子表和父表之间的外键约束关系，该操作不可实现；只有先删除子表中的“外键值”记录，才能删除父表中的“主键值”记录。如果修改父表中的“主键值”记录，由于子表和父表之间的外键约束关系，子表中的“外键值”记录可以自动地进行修改。

唯一性（unique）约束：如果希望表中的某个字段值不重复，则应当为该字段添加唯一性约束。与主键约束不同，在一张表中可以存在多个唯一性约束，并且满足唯一性约束的字段可以是NULL（为了保持唯一性，最多只能出现一次NULL）。例如student表中学号student_no字段不允许重复，可以为该字段添加唯一性约束。

非空（not NULL）约束：如果希望表中的字段值不为空值，则应当对该字段添加非空约束。例如 student 表中学号 student_no 字段不允许为空值，可以为该字段添加非空约束。此时 student表中的student_no字段既要满足唯一性约束，又要满足非空约束，在同一个字段上可以应用多种约束。

检查（check）约束：检查约束用于检查字段的输入值是否满足指定的条件。在同一个字段上可以应用多个检查约束。添加或修改记录时，若字段中的数据不符合检查约束指定的条件，则数据不能写入该字段。例如成绩表score中grade字段需要满足大于等于0且小于等于100的约束条件，可以为grade字段建立检查约束（grade>=0 and grade<=100），确保grade字段值的取值范围在该区间内。

默认值（default）约束：默认值约束用于指定一个字段的默认值，如果没有在该字段填写数据，该字段将自动填入这个默认值。