SQL 数据基础原理 11

数据库设计学习目标

掌握关系数据库的规范化理论。

掌握数据库设计的基本步骤。

掌握如何将概念模型转换成关系模式。

了解数据库设计的基本原则。章节内容2.1关系数据库的规范化2.2数据库设计步骤2.1关系数据库的规范化

2.1.1关系数据库规范化理论2.1.2第一范式（1NF） 2.1.3第二范式（2NF） 2.1.4

第三范式（3NF）和BCNF

2.1.1关系数据库规范化理论关系数据库的规范化理论最早可追溯至1970年，当时E.F.Codd提出了关系模型，规范化问题也随之被引入。规范化理论的核心在于通过分析关系模式的属性间的依赖关系，将关系模式逐步分解为不同的范式，以优化数据库结构。这些范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、Boyce-Codd范式（BCNF）和第四范式（4NF）2.1.1关系数据库规范化理论规范化理论的提出和发展，为关系数据库的设计提供了一套科学的指导方法。通过逐步分解关系模式，消除不合适的函数依赖和多值依赖，规范化理论能够优化数据库结构，减少数据冗余，避免更新异常关系模型基于严格的数学理论基础，这使得它成为研究和设计数据库的理想对象，并提高查询效率。2.1.1关系数据库规范化理论好的数据库设计是高效、可扩展且易于维护的关键。那么，什么是好的数据库呢？在设计数据库关系模式时，是否可以将所有信息都放在一张表中呢？答案是否定的。虽然单表设计可能在某些情况下简化查询操作，但它往往会导致一系列严重问题，从而降低数据库的整体性能和可维护性。因此，构建合适的数据库模式是数据库设计的核心问题。如果数据库没有经过规范设计，可能会引发以下主要问题：冗余：将所有信息存储在一张表中会导致大量数据冗余，不仅浪费存储空间，还可能引发数据一致性问题。例如，重复的信息在更新时需要多次修改，增加了出错的风险。更新异常：冗余信息不仅浪费存储空间，还会增加更新操作的复杂性和难度。当需要更新某条信息时，可能需要在多个地方进行修改，这不仅低效，还容易导致数据不一致。插入异常：在某些情况下，由于表结构设计不合理，某些必要的信息可能无法插入数据库，或者插入操作会违反数据完整性约束。删除异常：在某些情况下，删除一行数据可能会导致有用信息的丢失。例如，删除某个实体的记录可能会意外删除与其他实体相关的重要信息。2.1.1关系数据库规范化理论假设需要设计一个数据库来存储学生学习情况的数据。如果将数据库里的所有信息都放在一张表里，也就是只设计一个关系模式SCG（学号,姓名,年龄,所在系,课程号,课程名,学分,成绩），这个关系模式存放学生学习情况的数据是否会存在问题？明显存在，该数据库设计的关系模式存在的问题如下。冗余度大：每选一门课，学生信息和有关课程信息都要重复一次。插入异常：插入一门课，若没有学生选修，则不能把该课程插入表中。删除异常：如删除S11号学生，当有一门课只有他选时，会造成课程的丢失。更新复杂：更新一名学生的信息时，要同时更新很多条记录。更新选修课时也存在同样的情况。2.1.1关系数据库规范化理论上述数据库设计出现异常的原因是数据存在依赖约束，其解决方法就是将数据库设计规范化，即分解，使其每个相对独立，依赖关系比较单纯，如分解为第三范式。例如，采用分解的方法，将上述SCG关系模式分解成以下三个模式（也就是将一张表分为三张表）：S(学号,姓名,年龄,所在系)C(课程号,课程名,学分)SC(学号,课程号,成绩)2.1.1关系数据库规范化理论函数依赖（FunctionalDependency，FD）是指一个或一组属性可以（唯一）决定其他属性的值。函数依赖通过数学语言描述如下。设有关系模式R(U)，其中U=｛A1,A2,…,An｝是关系的属性全集，X、Y是U的属性子集，设t和u是关系R的任意两个元组，如果t和u在X的投影是t[X]=u[X]则推出t[Y]=u[Y]，即t[X]=u[X]=>t[Y]=u[Y]，称X函数决定Y或Y函数依赖于X，记为X→Y。在上述的关系模式S（学号,姓名,年龄,所在系）中，存在以下的函数依赖：学号→年龄学号→姓名学号→所在系2.1.1关系数据库规范化理论完全函数依赖和部分函数依赖：设X、Y是关系R的不同属性集，若X→Y（Y函数依赖于X），且不存在X’⊂X，使X’→Y，则称Y完全函数依赖于X；(即不存在真子集仍然是函数依赖关系的函数依赖是完全函数依赖)。否则则称Y部分函数依赖于X。例如，在上例关系S中，姓名是完全函数依赖于学号。在属性Y与X之间，除了完全函数依赖和部分函数依赖关系等直接函数依赖，还存在间接函数依赖关系。如果在关系S中增加系的电话号码办公电话字段，从而有学号→系名，系名→办公电话，于是学号→办公电话。在这个函数依赖中，办公电话并不直接依赖于学号，是通过中间属性系名间接依赖于学号。这就是传递函数依赖。2.1.1关系数据库规范化理论一个包含了关键字的属性集合也能够函数决定（但不是完全函数决定，而是部分决定）属性全集，我们把这种包含了关键字的属性集合称为超关键字(SuperKey)。规范化理论是将一个不合理的关系模式如何转化为合理的关系模式的理论，规范化理论是围绕范式而建立的。规范化理论认为，一个关系型数据库中所有的关系，都应满足一定的规范。规范化理论把关系应满足的规范要求分为几级，满足最低要求的一级叫做第一范式(1NF)，在第一范式的基础上提出了第二范式(2NF)，在第二范式的基础上又提出了第三范式(3NF)，以后又提出了BCNF范式，4NF，5NF。范式的等级越高，应满足的约束条件也越严格。规范的每一级别都依赖于它的前一级别，例如若一个关系模式满足2NF，则一定满足1NF。2.1.2第一范式（1NF） 范式（NormalForms）表示构造数据库必须遵循一定的规则，以满足特定规则的模式称为范式第一范式（1NF）：如果一个关系模式R的每个属性的域都只包含单纯值，而不是一些值的集合或元组，则称关系R是第一范式，记为R∈1NF。如果关系模式R的每个关系r的属性值都是不可分的原子值，那么称R是第一范式（每个元组中的每个属性只含有一个单纯值，即要求属性是原子的，原子属性是每个字段不可再分割成多个属性）。2.1.3第二范式（2NF）

2.1.3第二范式（2NF） 【例2-2】学生（学号，课程号，成绩，学分)就不满足第二范式，将其改为满足2NF的关系模式。这个关系的关键字是学号和课程号的组合，主属性是学号和课程号，非主属性是成绩和学分。因为非主属性学分是由课程号决定的，与学号无关，也就是说非主属性学分部分依赖于码，所以这个关系存在部分依赖，也就不满足第二范式。但是这个关系满足1NF（因为每个属性都是原子属性，不能再细分）。将学生（学号，课程号，成绩，学分)分为：成绩表（学号，课程号，成绩）课程表（课程号，学分)上述两个表都满足第二范式。推论：如果关系模式R∈1NF，且它的每一个候选码都是一个属性，则R∈2NF。符合第二范式的关系模式仍可能存在数据冗余、更新异常等问题。2.1.4

第三范式（3NF）和BCNF2.1.4

第三范式（3NF）和BCNF注意：有时将关系分解为BCNF可能会导致某些函数依赖无法保持（即不能通过自然连接和函数依赖推导出原关系中的所有函数依赖）。在这种情况下，设计者可能需要在BCNF和保持所有函数依赖之间做出权衡。注意：当R∈3NF时，R未必属于BCNF。因为3NF比BCNF放宽了一个限制，它允许决定因素不包含码。2.1.4

第三范式（3NF）和BCNF

【例2-7】关系模式S(学号,姓名,年龄,系名,办公电话)是否满足第三范式。由于关系模式S中存在函数依赖：学号→系名，系名→办公电话，所以存在办公电话字段对关键字学号字段的传递函数依赖，因此，关系S不满足第三范式。可以分解为下面两个关系模式：S(学号,姓名,年龄,系名)D(系名,办公电话)上述两个关系就满足第三范式了，每个非主属性既不部分依赖，也不传递依赖于候选关键字。2.1.4

第三范式（3NF）和BCNF

对于关系规范化可从下面几个方面进行理解。（1）规范化的目的是解决插入、修改异常及数据冗余度高。（2）规范化的方法是从模式中各属性间的依赖关系（函数依赖及多值依赖）入手，尽量做到每个模式表示客观世界中的一个“事物”。（3）规范化的实现可采用模式分解的方法。在实际设计数据库模式中，需要综合多种正反因素，统一权衡利弊得失，最后设计出一个较为适合实际的模式来。2.2数据库设计步骤

2.2.1数据库设计概述2.2.2需求分析2.2.3概念结构设计 2.2.4逻辑结构设计 2.2.5数据库物理设计2.2.6数据库实施运行和维护2.2.7数据库设计案例2.2数据库设计步骤数据库设计是软件开发的重要内容。数据库设计的各个阶段包括：

(l）需求分析

(2）概念结构设计

(3）逻辑结构设计

(4）数据库物理设计

(5）数据库实施(6）数据库运行和维护2.2.1数据库设计概述数据库设计既是一项涉及多学科的综合性技术又是一项庞大的工程项目。数据库结构设计的不同阶段形成数据库的各级模式，在概念设计阶段形成独立于机器特点，独立于各个DBMS产品的概念模式，通常概念设计阶段是得到实体联系图；在逻辑设计阶段将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式，然后在基本表的基础上再建立必要的视图，形成数据的外模式；在物理设计阶段，根据DBMS特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式。2.2.2需求分析需求分析简单地说就是分析用户的需求，它是设计数据库的起点，需求分析结果是否准确反映用户的实际要求将直接影响到后面各阶段的设计，并影响到设计结果是否合理和实用。需求说明书是需求分析阶段的成果，也是以后设计的依据。数据库设计的第一阶段是需求分析，需求分析阶段的目标是通过详细调查现实世界要处理的对象（组织、部门、企业等），充分了解原有系统（包括手工系统或计算机系统）工作概况，明确用户的各种需求，然后在此基础上确定新系统的功能。系统分析师对系统的需求分析往往决定软件项目是否成功。2.2.3概念结构设计数据库的概念结构设计是整个数据库设计的关键，它先将在需求分析阶段得到的应用需求抽象成概念结构，并以此作为各种数据模型的共同基础，从而能更好地、更准确地用某一种数据库管理系统实现这些需求。采用E-R图的方式是抽象和描述现实世界的有力工具。它表示概念模型独立于具体数据库管理系统所支持的数据模型，是各种数据模型的共同基础，因此比数据模型更抽象、更接近现实世界。2.2.4逻辑结构设计数据库的逻辑结构设计就是把概念结构设计阶段设计好的基本E-R图转换为与选用的DBMS产品所支持的数据模型相符合的逻辑结构。将E-R图向关系数据模型的转换要解决的是如何将实体型和实体间的联系转换为关系模式，并且如何确定这些关系模式的属性和码。将E-R图向关系数据模型的转换要解决的是如何将实体型和实体间的联系转换为关系模式，并且如何确定这些关系模式的属性和码。

一个实体自动转化为一个关系模式。对于实体间的联系，其具体转换方法如下。（1）一个1

:

1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式，与联系相连的各实体的码及联系本身的属性均应转换为关系的属性，则每个实体的码都可以作为这个关系的候选码。如果和某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。（2）一个1

:

n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并，但不能合并到1端。如果转换为一个独立的关系模式，与联系相连的各实体码及联系本身的属性均应转换为关系的属性，则关系码为n端实体的码。（3）一个m

:

n联系转换为一个关系模式，与联系相连的各实体码及联系本身的属性均应转换为关系的属性，各实体码成为关系码或关系码的一部分。（4）三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体码及联系本身的属性均应转换为关系的属性，各实体码可组成关系码或关系码的一部分。（5）具有相同码的关系模式可合并。2.2.5数据库物理设计物理结构依赖于给定的DBMS和硬件系统，因此开发设计人员必须充分了解所用RDBMS的内部特征、存储结构、存取方法。数据库的物理设计通常分为两步：第一步，确定数据库的物理结构；第二步，评价实施的空间效率和时间效率。确定数据库的物理结构内容如下：（1）确定数据的存储结构；（2）设计数据的存取路径；（3）确定数据的存放位置；（4）确定系统配置。2.2.6数据库实施运行和维护数据库物理设计后进入实施运行和维护阶段。数据库实施阶段包括数据的载入与应用程序的编码和调试。根据逻辑和物理设计的结果，在计算机上建立起实际的数据库结构，并装入数据进行试运行和评价的过程，称为数据库的实施（或实现）。数据库实施阶段包括数据的载入与应用程序的编码和调试。数据库的维护工作主要包括：1.数据库的转储和恢复。2.数据库的安全性、完整性控制。3.数据库性能的监督、分析和改造。4、数据库的重组织与重构造。2.2.7数据库设计案例下面通过设计一个教务管理系统来讲解数据库的设计流程，主要包括需求分析、概念模型设计、逻辑模型设计和物理设计。1.需求分析教务管理系统主要有学生选课管理、成绩管理、教学计划的制定等功能。经过对实际教学业务的调查、数据的收集和信息流程分析处理，明确了该系统的主要功能，分别为：制定学校各专业各年级的教学计划以及课程的设置；学生根据学校对所学专业的培养计划以及自己的兴趣，选择自己本学期所要学习的课程；学校的教务部门对新入学的学生进行学籍注册，对毕业生办理学籍档案的归档工作，任课教师在期末时登记学生的考试成绩；学校教务部门根据教学计划进行课程安排等。2.2.7数据库设计案例2.概念模型设计要建立系统的E-R模型的描述，需进一步从数据流图和数据字典中提取系统所有的实体及其属性。这种提出实体的指导原则如下：属性必须是不可分的数据项，即属性中不能包含其它的属性或实体。E-R图中的关联必须是实体之间的关联，属性不能和其它实体之间有关联。根据前面的需求描述，可以抽象得到实体主要有5个：学生、教师、课程、院系（部门）、班级。2.2.7数据库设计案例（1）学生实体属性有：学号、姓名、性别、出生日期、政治面貌、民族、籍贯、专业名称、年级、年龄和简历。学生实体的E-R图如图2-1所示。2.2.7数据库设计案例（2）课程实体属性有：课程编号、课程名称、课程学时、课程学分。课程实体的E-R图如图2-2所示。2.2.7数据库设计案例（3）教师实体属性有：教师编号、教师姓名、性别、职称、出生年月、电话、电子邮件。教师实体的E-R图如图2-3所示。2.2.7数据库设计案例（4）院系（部门）实体属性有：部门编号、部门名称、负责人、联系电话。院系实体的E-R图如图2-4所示。2.2.7数据库设计案例（5）班级实体属性有：班级编号、班级名称、班级人数。班级实体的E-R图如图2-5所示。2.2.7数据库设计案例实体间存在如下联系：（1）学生实体和课程实体存在选修的联系，一个学生可以选修多门课程，而每门课也可以被多个学生选修，所以它们之间是多对多的联系(n:m)。（2）教师实体和课程实体存在讲授的联系，一名教师可以讲授多门课程，而每门课也可以被多个教师讲授，所以它们之间是多对多的联系(n:m)。（3）学生实体和班级实体存在归属的联系，一个学生只能属于一个班级，而每个班级可以包含多个学生，所以班级和学生之间是一对多的联系(1:n)。（4）班级实体和系之间存在归属的联系，一个班级只能属于一个系，而每个系可以包含多个班级，所以班级和系之间是一对多的联系(1:n)。（5）教师实体和院系实体之间存在归属的联系，一个教师只能属于一个系，而每个系可以拥有多名教师，所以教师和系之间是一对多的联系(1:n)，但是教师中会有一位充当该系的主任(正)，可见教师和系之间也存在一种一对一的领导关系(1:1)。2.2.7数据库设计案例2.2.7数据库设计案例3.逻辑模型设计逻辑设计就是把E-R图转换成关系模式，并对其进行优化。根据前面的转换规则，一个实体可以转换成一个关系模式，而不同种类的联系转换方法有多种，因此，设计的关系数据库模式如下：（1）学生(学号,姓名,性别,出生日期,政治面貌,民族,籍贯,学院专业名称,年级,年龄,简历,班级编号)。（2）教师(教师编号,教师姓名,性别,职称,出生年月,联系电话,电子邮件,所属院系)。（3）课程(课程编号,课程名称,学分,课时,教师编号)。（4）部门(部门编号,部门名称,负责人,联系电话)。（5）班级(班级编号,班级名称,班级人数,部门编号)。学生实体与课程之间是多对多的联系，可以使用如下关系模式来表示：（6）选课(课程编号,学号,成绩)。2.2.7数据库设计案例4.物理设计根据逻辑设计得到的关系模式，选取合适的字段名及数据类型，得到各个关系模式对应的表结构。各表结构如下：（1）部门代码表bmdmb结构如表2-2所示。字段名数据类型非空主键注释bmhvarchar(10)NO主键部门编号bmmcvarchar(50)NO

部门名称managervarchar(20)YES

负责人telvarchar(12)YES

联系电话2.2.7数据库设计案例字段名数据类型非空主键注释bjbhvarchar(10)NO主键班级编号bmhvarchar(10)NO外键部门编号bjzwmcchar(50)YES

班级名称bjrsintYES

班级人数字段名数据类型非空主键注释kcdmch