任务二 E-R模型设计

关系数据库中的数据表都是二维表，所谓二维表是指数据表中的每行有相同的列数，每列有相同的行数，不可以对表中的列再分子列或者对表中的行再分子行。数据表中的每行用来记录实体集中的一个实体，称为一条记录，关系数据库不允许出现重复记录。数据表的每列用来描述实体集某一方面的属性特征，称为字段。

用E-R图可以描绘并建立数据模型——E-R模型。关系数据库的设计是指根据系统需求分析来设计系统数据库的E-R模型。数据表是数据库中最为重要的对象，采用“一事一地”的原则绘制出E-R图后，可以通过如下几个步骤由E-R图生成数据表：

① 为E-R图中的每个实体建立一张数据表；

② 为每张数据表定义一个主键（如果需要，可以向数据表中添加一个没有实际意义的字段作为该表的主键）；

③ 数据表与数据表之间有一定的联系时，可以添加数据表外键来表示一对多联系；

④ 通过建立新数据表来表示多对多联系；

⑤ 为数据表中的字段选择合适的数据类型；

⑥ 对数据表中的数据有特定要求时，可以定义约束条件。

一、任务描述

学生竞赛项目管理系统数据库中涉及的实体主要有参赛学生、班级信息、指导教师、系部信息、参赛信息、赛前培训信息等。本任务根据设计完成的E-R图，为每个实体建立一张数据表。

二、任务分析

关系数据库中的数据表是二维表，是由列和行构成的。二维表是规范表，要求每行有相同的列数，每列有相同的行数，并且数据表中的每条记录都必须是唯一的，即在同一张数据表中不允许出现完全相同的两条记录。关系数据库表中必须存在关键字，关键字是能够唯一标识表中记录的字段或字段组合。例如，在学生表中，由于学号字段不允许重复且不允许取空值（NULL），因此学号可以作为学生表的关键字。在所有的关键字中选择一个关键字作为该数据表的主关键字，称为主键（Primary Key）。数据表中的主键可以是一个字段，也可以是多个字段的组合，表中主键的值具有唯一性且不能取空值。一张数据表中可以有多个关键字，但只能有一个主键，且主键一定属于关键字。

定义数据表的主键时，一般把取值简单的关键字作为主键。在设计数据表时，应慎用复合主键，复合主键会给维护数据表带来不便。数据库开发人员如果不能从已有的字段中选择一个主键，则可以向数据表中添加一个没有实际意义的字段作为该表的主键，例如，为数据表添加一个序号，通过序号确定每条记录，该序号可以设置为由程序自动生成，以免人工录入时出错。

三、任务完成

定义数据表时需要确定字段的数据类型，表中字段类型设计得是否恰当关系到数据库的存储空间，为每张数据表中的字段选择最合适的数据类型是数据库设计过程中的一个重要步骤，切忌为字段随意设置数据类型。为字段设置合适的数据类型还可以提升数据库的计算性能，节省数据检索时间，提高效率。MySQL数据库管理系统中常用的数据类型包括数值类型、字符串类型和日期类型。

① 数值类型：分为整数类型和小数类型，小数类型分为精确小数类型和浮点数类型。如果字段值需要参加算术运算，则应将这个字段设为数值类型。

② 字符串类型：分为定长字符串类型和变长字符串类型，字符串类型的数据使用单引号括起来，其字段值不能参加算术运算。

③ 日期类型：分为日期类型和日期时间类型，日期类型的数据是一个符合“YYYY-MM-DD”格式的字符串。日期时间类型的数据符合“YYYY-MM-DD hh:mm:ss”格式。日期类型的数据可以参加简单的加、减法运算。图1-8列出了MySQL数据库的数据类型。

数据库完整性（Database Integrity）是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。数据库完整性由各种各样的完整性约束（Constraint）来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。MySQL数据库定义的约束条件主要有主键（Primary Key）约束、外键（Foreign Key）约束、唯一性（Unique）约束、默认值（Default）约束、非空（Not Null）约束、检查（Check）约束6种。

① 主键能够唯一标识表中的每条记录。一张表只能有一个主键，但可以有多个候选键。主键常常与外键构成参照完整性约束，防止出现数据不一致的问题。主键可以保证记录的唯一性和主键域非空。数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。如学生表中有学号和姓名，姓名可能有重复的，但学号却是唯一的，要从学生表中搜索一条记录，就只能根据学号去查找，才能找出唯一的这名学生，这就是主键。可以将主键设为自动增长的类型，例如：

② 外键是用于建立和加强两张数据表之间的链接的一个或多个字段。外键约束主要用来维护两张表之间数据的一致性。一张数据表的外键就是另一张数据表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其他表中的没有相同记录值的外键（即该表中的主键没有一个外键和它相关联）。如果表A的一个字段a对应于表B的主键b，则字段a称为表A的外键，此时存储在表A中字段a的值，要么是NULL，要么是表B中主键b的值。

③ 唯一性约束是对数据表的字段强制执行唯一值，例如，学生表中学生的学号必须具有唯一性，学生的姓名可以不具有唯一性，也就是允许一张数据表中有相同名字的学生。但为了区分学生实体集间的个体信息，可以将学生的学号设置为唯一性约束，通过唯一性约束来区分相同姓名的学生。MySQL数据库可以用唯一性约束对字段进行约束，它定义了限制字段或一组字段中值的唯一规则。若要限制数据表中的字段值不重复，则可为该字段添加唯一性约束。与主键约束不同，一张表中可以存在多个唯一性约束，并且满足唯一性约束的字段值可以为NULL。

④ 默认值约束。数据表在创建字段时可以指定默认值，当插入数据且未主动输入值时，为其自动添加默认值，默认值与NOT NULL配合使用。例如，学生表中学生的性别有男或女两种情况，但机电专业的男学生比较多，则可以将该性别字段设为默认值“男”，在录入学生性别信息时，如果没有录入数据，则系统自动设置其性别信息为“男”。

⑤ 非空约束限制数据表中的字段值不能取NULL值，例如，学生表中学生的姓名不能为空，则可为该字段添加非空约束。

⑥ 检查约束用于检查字段的输入值是否满足指定的条件。输入（或者修改）数据时，若字段值不符合检查约束指定的条件，则数据不能写入该字段中。如在学生表中将学生的年龄字段约束为在15～35岁，设为检查约束后，如果录入学生的年龄超过35岁或低于15岁，则该条记录是一条无效记录，不能录入数据表中。

经过分析，根据学生竞赛项目管理系统中数据库的实体集，可设计以下几张具体的数据表，如表1-2至表1-9所示。

设计数据库时，需要制定一套数据表设计的质量标准，根据质量标准检测数据表的质量，减少数据库中数据的冗余。一套质量好的数据表应该尽量减少数据冗余，避免数据经常发生变化。冗余的数据需要额外维护，并且容易导致数据不一致、插入异常、删除异常等问题。

范式（Normal Form）是英国人E.F.Codd在20世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。

第一范式（1NF）：第一范式是指数据表中的每个字段都是不可分割的基本数据项，同一字段中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果一张数据表中的同类字段不重复出现，则该表满足第一范式；如果数据表不满足第一范式，则对数据表的操作将会出现插入异常、删除异常、修改复杂等问题。

例如，在表1-10所示的学生表中包括学号、姓名、性别、专业、联系方式字段，但在实际生活中，一个人的联系方式有多种，则这个联系方式字段在数据表结构设计时就没有达到第一范式。要符合第一范式，需把联系方式拆分成具体的联系方式字段，如手机、E-mail、QQ等。

第二范式（2NF）：第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据表中的每个实例（或行）必须可以被唯一地区分。一张数据表在满足第一范式的基础上，如果每个“非关键字”字段仅仅函数依赖于主键，那么该数据表满足第二范式。第二范式首先满足第一范式，另外包含两部分内容，一是表中必须有一个主键；二是没有包含在主键中的字段必须完全依赖于主键，而不能只依赖于主键的一部分。

例如，组合关键字（学号，竞赛项目号），由于非主属性“竞赛项目名称”仅依赖于“竞赛项目号”，对关键字（学号，竞赛项目号）只是部分依赖，而不是完全依赖，因此会导致数据冗余及更新异常等问题。解决办法是将其分为两张表：学生表（学号，竞赛项目号，成绩）和竞赛项目表（竞赛项目号，竞赛项目名称），两张表通过学生表中的外关键字——竞赛项目号联系，在需要时进行连接。

函数依赖：在一张数据表内，两个字段值之间的一一对应关系称为函数依赖，如果字段A的值能够唯一确定字段B的值，那么称字段B函数依赖于字段A，记为A→B。

第三范式（3NF）：一张数据表满足第二范式的要求，并且不存在“非关键字”字段函数依赖于任何其他“非关键字”字段，那么该数据表满足第三范式。满足第三范式的数据表不会出现插入异常、删除异常、修改复杂等现象。

例如，数据表student（st_no，st_name，dp_id，dp_name，location）中，关键字st_no决定各个属性。由于是单个关键字，没有部分依赖的问题，所以其一定满足第二范式。但此表存在大量的冗余，有关学生的几个属性dp_id，dp_name，location将重复存储，在插入、删除和修改时也将产生类似重复的情况，原因是表中存在传递依赖。即st_no→dp_id，而dp_id→st_no却不存在，dp_id→location，因此关键字st_no对location的函数决定是通过传递依赖dp_id→location实现的。也就是说，st_no不直接决定非主属性location。解决方法：将原数据表分为两张表student（st_no，st_name，dp_name）和department（dp_id，dp_name，location）。

第二范式和第三范式的概念很容易混淆，区分它们的关键点在于：

第二范式：非主键列是完全依赖于主键，还是依赖于主键的一部分；

第三范式：非主键列是直接依赖于主键，还是直接依赖于非主键。

四、任务总结

本次任务是根据学生竞赛项目管理系统E-R图来设计具体的数据表。对数据表中各字段的数据类型进行说明。字段类型值设置过大会导致数据库存储容量庞大，浪费存储空间，字段类型值设置过小会导致数据表中的字段值存储不进去。设计数据表时，需要遵循一定的质量规范及设计原则。范式即数据库设计范式，是符合某一种级别的关系模式的集合。构造关系数据库时必须遵循范式。关系数据库中的关系必须满足一定的要求，即满足不同的范式。特别强调设计过程中要遵循第一范式、第二范式、第三范式。