数据库基础与项目实训教程-基于SQLServer第二章到第十章课件

第2章关系数据库设计理论永嘉电大陈权威1知识导航2学习目标了解：不恰当关系模式的存储异常问题；函数依赖的概念；数据库设计方法、工具和原则；数据库应用系统开发与设计过程的六个阶段及每阶段的任务和目标。理解：平凡函数依赖、非平凡函数依赖和完全函数依赖的概念。掌握：部分依赖、传递函数依赖的概念；第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和BCNF的概念并逐级规范。3关系数据库设计理论2.1规范化概述2.2函数依赖概念2.3关系范式2.4关系数据库应用系统设计概述42.1规范化概述任务描述：在关系数据库中，如何设计和构造一个合理的关系模式，使它准确地适合应用；对于给出的一组数据，如何构造一个合适的数据库模式。本任务通过分析关系模式中存在的问题，引出关系规范化。任务目标：了解关系模式存在的问题。52.1规范化概述如有教师任课关系模式：教师任课TDC（TNO，TNAME，TITLE，ADDR，DNO，DNAME，LOC，CNO，CNAME，LEVEL，CREDIT）也可以表示为：教师任课（教师号，姓名，职称，家庭住址，系号，系名称，系址，课程号，课程名，教学水平，学分）表2-1教师任课关系TDC教师号姓名职称家庭住址系号系名系址教学水平课程号课程名学分1001齐胜副教授A1D1计算机L1好C1PC组成51002肖冬讲师A1D1计算机L1优C2程序设计31002肖冬讲师A1D1计算机L1良好C3数据库362.1规范化概述该关系在使用过程中存在以下四个方面的问题。（1）数据冗余每当教师开设一门课程时，该教师的职称、地址等信息就重复存储一次。一般每位教师都开设几门课，数据冗余不可避免，一个系有很多教师，将导致关系中的数据冗余度增大。（2）更新异常由于数据的重复存储，会给更新带来麻烦。如果一位任三门课的教师改变了地址，三行记录的地址都要更新，一旦一个元组的地址未修改就会导致数据不一致。如果某个系改变办公地址，所要修改的数据量会更大。（3）插入异常如果学校新调入一个教师，暂时未主讲任何课程，主码不允许出现空值，新教师就不能插入到此关系中去，只有当他们开设了课程之后才能插入，这显然是不合理的。（4）删除异常与插入异常相反，如果某些教师致力于科研，不担任教学任务了，因为主码不全，就要从当前数据库中删除有关记录，那么关于这些教师的其他信息将无法记载，这也是极不合理的现象。72.1规范化概述如果用下面四个关系模式代替原来的一个关系模式，上述四个方面的问题就基本解决了。T（教师号，教师名，职称，地址，系号）T（TO，TNAME、TITLE，ADDR，DNO）D（系号，系名，系地址） D（DNO，NAME，LOC）C（课程号，课程名，学分） C（CNO，CNAME，CREDIT）TC（教师号，课程号，教学水平） TC（TNO，CNO，LEVEL）82.2函数依赖概念2.2.1函数依赖函数依赖：设一个关系R（U），X和Y为属性集U上的子集，若对于元组中X的每个值都有Y上的一个唯一的具体值与之对应，则称Y函数依赖于X，或X函数决定Y，记作：X→Y，X称作决定因素。【例2-1】

设一个职工关系为（职工号，姓名，性别，年龄，职务），职工号为主码，则：职工号→姓名，职工号→性别，职工号→年龄，职工号→职务。在该关系中除职工号外，其他属性都不能成为决定因素形成函数依赖，因为对于它们的每个属性值，都可能对应另一属性的多个不同的取值，如对于性别属性的一个取值“男”就会对应多个而不是一个职工号。92.2函数依赖概念2.2.2非平凡的函数依赖和平凡的函数依赖函数依赖具有非平凡的函数依赖和平凡的函数依赖的性质。非平凡的函数依赖和平凡的函数依赖定义：如果X→Y，并且Y不是X的子集，则称X→Y是非平凡的函数依赖。我们讨论的总是非平凡的函数依赖，全体总是能够决定部分的，若Y是X的子集，则称X→Y是平凡的函数依赖；若Y中没有一个属性在X中，则称完全非平凡的函数依赖。【例2-2】指出下列函数依赖的性质。SnoCnameGrade→CnameGrade：平凡函数依赖（右边的属性集是左边的属性集的子集）。SnoCname→CnameGrade：非平凡函数依赖（右边属性集中至少有一个不在左边属性集里）。SnoCname→SnameGrade：完全非平凡函数依赖（右边属性集没有一个在左边的属性集里）。102.2函数依赖概念2.2.3完全和部分函数依赖完全和部分函数依赖定义：设X→Y是关系模式R的一个函数依赖，如果存在X的真子集X'，使得X'→Y成立，则称Y部分依赖于X，记作X→Y。否则，称Y完全依赖于X，记作X→Y。【例2-4】设一个教师任课关系为（教工号，姓名，职称，课程号，课程名，课时数，课时费），该关系给出某个学校每个教师在一个学期内任课安排的情况，假定每个教师可以讲授多门课程，每门课程可以由不同教师来讲授。在该教师任课关系中也存在许多部分函数依赖，如（教工号，课程号）→姓名，（教工号，课程号）→职称，（教工号，课程号）→课程名等。fp112.2函数依赖概念2.2.4传递函数依赖函数依赖的传递定义：在同一关系模式中，如果存在非平凡的函数依赖X→Y，Y→Z，而Y↛X，则称Z传递依赖于X。【例2-5】设一个学生关系（学号，姓名，性别，系号，系名，系主任名），通常每个学生只属于一个系，每个系有许多学生，每个系都对应唯一的系名和系主任。在学生关系中还存在“学号→系名”和“学号→系主任名”这两个函数依赖，由于它们是通过从学号开始的间接函数依赖得到的，所以系名和系主任名是传递依赖于学号。122.2函数依赖概念2.2.5最小函数依赖最小函数依赖定义：设一个关系为R（U），X和Y为U的子集，若X→Y，并且为完全非平凡函数依赖，同时Y为单属性，则称X→Y为R的最小函数依赖。由R中所有最小函数依赖构成R的最小函数依赖集，其中不包含有冗余的传递函数依赖。132.2函数依赖概念【例2-6】设一个关系为R（A，B，C，D），它的函数依赖为FD={A→B，B→C，A→C，B→D}，判断它是否为R的最小函数依赖集。分析：由FD中的A→B和B→C可得到A→C，也就是说A→B和B→C中已经蕴含A→C（传递律），所以给出的A→C是冗余的，应去掉。原FD不是R的一个最小的依赖集，若修改为：FD={A→B，B→C，B→D}，就成为R的最小依赖集。142.2函数依赖概念2.2.6主码（候选码和主码）如果一个或多个属性的集合{A1，A2，…，An}满足以下条件，则称该集合为关系R的候选码（Key）。1）这些属性函数决定该关系R的所有其他属性。2）{A1，A2，…，An}的任何真子集都不能函数决定该关系R的所有其他属性，也就是说，候选码必须是最小的。我们把候选码所在的属性称为主属性，把候选码以外的属性称为非主属性。例如学生关系中{学号}是主属性，而另外几个属性（姓名，性别，系号，系名，系主任名）则为非主属性。152.2函数依赖概念2.2.7超键码包含候选码的属性集称为“超键码”（SuperKey），是“键码的超集”的简称。每个超键码都满足键码（候选码）的第一个条件：属性函数决定该关系R的所有其他属性。但是，超键码不必满足键码的第二个条件：键码（候选码）必须是最小的。例如在学生关系中学生号能够函数决定其他所有属性，所以学号是该关系的一个候选码，则（学号，姓名）是关系的超键码。该超键码可以决定学生关系中的其他属性，但是，它不是最小的。162.3关系范式任务描述：设计关系数据库时，关系模式不可以随意建立，它们必须满足一定的规范化要求。一个关系模式满足某一指定的约束，称此关系模式为特定范式的关系模式。满足不同程度的要求构成不同的范式级别。本任务将逐步讲述下面常见的关系模式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、BCNF和逐级规范化。任务目标：了解各个范式的定义，掌握逐级规范化范式。172.3关系范式2.3.1第一范式（1NF）第一范式：在关系模式R中的每一个具体关系r中，如果每个属性值都是不可再分的最小数据单位，则称R属于第一范式的关系，记为R∈lNF。18表2-3教师电话表教师号姓名性别电话号码1001齐胜女88812345（O）88696547（H）1003李月男88078549（O）88182883（H）1004江成男861256781005李林女88563467（O）87954325（H）电话属性不是一个单属性，它包含了两个子属性，所以必须把每个子属性提升为一般属性，才能满足第一范式的规范化关系。192.3关系范式2.3.2第二范式（2NF）第二范式：设一个关系R（U），它是满足第一范式的，若R中不存在非主属性对候选码的部分依赖，则称关系R是属于第二范式的关系，记为R∈2NF。【例2-10】有教师任课关系模式：TDC（TNO，TNAME，TITLE，ADDR，DNO，DNAME，LOC，CNO，CNAME，LEVEL，CREDIT）。用汉字表示（教师号，姓名，职称，家庭住址，系号，系名称，系址，课程号，课程名，教学水平，学分）根据语义分析函数依赖：（TNO，CNO）→U，U表示所有属性，所以（TNO，CNO）是候选码，是主属性。单个TNO或CNO不是主属性，由于CNO→（CNAME，CREDIT），TNO→（TNAME，TITLE，ADDR，DNO）存在非主属性对候选码（TNO，CNO）的部分依赖，因此原关系不属2NF。202.3关系范式消除部分依赖的方法：对于一个关系R（U），假定W、X、Y、Z是U的互不相交的属性子集，其中（W，X）是主码，X完全函数决定Y，（W，X）函数决定Z，但Z中不含依赖于X的属性，则把R（U）分解为两个关系R1（X，Y）和R2（W，X，Z）后就取消了Y对（W，X）的部分依赖。其中X是R1的主码和R2的外码，通过X使R1和R2自然连接仍可得到原来的R（U），同样，若R1（X，Y）、R2（W，X，Z）中仍存在着部分依赖，仍可按此方法继续分解，直到消除全部部分依赖为止。212.3关系范式为了消除部分依赖，将TDC投影分解成三个关系模式：TC（TNO，CNO，LEVEL），主码是（TNO，CNO）；TD（TNO，TNAME，TITLE，ADDR，DNO，DNAME，LOC），主码是TNO；C（CNO，CNAME，CREDIT），主码是CNO。222.3关系范式2.3.3第三范式（3NF）第三范式：设一个关系R（U），它是满足第二范式的，若R中不存在非主属性对候选码的传递依赖，则称关系R属于第三范式的关系，记为R∈3NF。【例2-11】

我们再来看例2-10所生成的关系模式：TD（TNO，TNAME，TITLE，ADDR，DNO，DNAME，LOC），主码是TNO。在TD中，TNO→DNO，DNO↛TNO，DNO→NAME，DNO→LOC，故非主属性DNAME和LOC传递依赖于主码TNO，所以关系模式TD不是3NF关系。232.3关系范式消除传递依赖的方法：消除关系中的传递依赖也是通过关系分解的方法来实现的。设一个关系R（U），X、Y、Z、W是U的互不相交的属性子集，其中X是主码，Y→Z是直接函数依赖（也可能包含部分函数依赖），X→Z是传递函数依赖，则把R（U）分解为两个关系R1（Y，Z）和R2（X，Y，W），其中Y是R1的主码和R2的外码，这样就消除了Z对X的传递函数依赖，通过Y对R1和R2自然连接仍可得到原来的R（U），同样，R1和R2中仍存在着传递函数依赖，仍可按此方法继续分解，直到消除全部传递函数依赖为止。242.3关系范式为消除传递依赖，又将TD进一步投影分解成如下两个关系模式：T（TNO，TNAME，ADDR，DNO），主码是TNOD（DNO，DNAME，LOC），主码是DNO因此，可以用下面四个关系模式代替例2-10最初的关系模式TDC：T（TNO，TNAME，TITLE，ADDR，DNO）；D（DNO，DNAME，LOC）；C（CNO，CNAME，CREDIT）；TC（TNO，CNO，LEVEL）。在这四个关系模式组成的关系模型中消除了传递依赖，达到了3NF。252.3关系范式2.3.4BCNF比第三范式规范程度更高的是BCNF，简称BC范式。第三范式虽然消除了非主属性对候选码的部分函数依赖和传递函数依赖，但满足3NF的有些关系模式仍具有不合适的性质。262.3关系范式【例2-12】关系模式STC（学生，教师，课程）。语义假设是，每一位教师仅教一门课；每门课有若干个教师任教；某一学生选定某门课，就对应于一个确定的教师。由函数依赖可知，关系模式STC的主码是（学生，课程），教师是非主属性，STC中不存在非主属性对主码的部分依赖和传递依赖，因此，STC∈3NF。但从STC的一个关系分析仍有一些问题272.3关系范式因此第三范式的关系模式必须进一步分解，以消除数据库操作的异常现象。我们把关系模式STC（学生，教师，课程），分解成ST（学生，教师）和TC（教师，课程），把学生选课的信息与教师任课的信息分开，这样就消除了大量冗余，也解决了插入异常与删除异常现象。分析关系模式ST和TC可见，它们的主码分别是（学生，教师）和教师。而ST中的函数依赖是（学生，教师）→学生，（学生，教师）→教师。282.3关系范式BCNF的定义：如果一个关系R（U），当R中所有属性（主属性和非主属性）都不传递依赖于R的任何候选码，那么称关系是属于BCNF的。还可以采用另一种等价的方式叙述：若R中所有属性都完全直接依赖于候选码，或者说R的最小函数集中所有函数依赖的决定因素都是候选码，则R是符合BCNF的，记为R∈BCNF。292.3关系范式【例2-13】指出下列关系模式是第几范式，并说明理由。1）R（X，Y，Z） F={XY→Z}2）R（X，Y，Z） F={Y→Z，XZ→Y}3）R（X，Y，Z） F={Y→Z，Y→X，X→YZ}4）R（W，X，Y，Z） F={X→Z，WX→Y}5）R（A，B，C，D，E）F={A→B，A→C，A→D，D→E}302.3关系范式1）R是BCNF。R候选码为XY，F中只有一个函数依赖，而该函数依赖的左部包含了R的候选码XY。2）R是3NF。R候选码为XY和XZ，R中所有属性都是主属性，不存在非主属性对候选码的传递依赖。3）R是BCNF。R候选码为X和Y，因为X→YZ，所以X→Y，X→Z，由于F中有Y→Z，Y→X，因此Z直接函数依赖于X，而不是传递依赖X。又因为F的每一个函数依赖的左部都包含了任一个候选码，所以R是BCNF。4）R是1NF。R候选码为WX，则Y，Z为非主属性，又由于X→Z，因此F存在非主属性对候选码的部分依赖。5）R是2NF。因A→D，D→E，存在传递函数依赖，不是3NF。312.4关系数据库应用系统设计概述任务描述：有了关系数据库设计理论，就可以对数据库应用系统作具体的设计，数据库应用系统是在计算机软硬件和数据库管理系统的支持下，针对某一方面的信息管理系统。如常见的学生教务管理系统、银行存取款系统、民航售票系统、图书馆管理系统、财务管理系统、网上购物管理系统等。本任务将讲述数据设计的方法和工具以及设计数据库的原则和步骤。任务目标：了解数据库设计的方法和工具；掌握数据库设计的原则与步骤。322.4关系数据库应用系统设计概述2.4.1数据库设计方法和工具1．数据库设计方法（1）新奥尔良（NewOrleans）方法新奥尔良方法属于规范设计法，它运用软件工程的思想，按一定的设计规程用工程化方法设计数据库，它把数据库设计分为若干阶段和步骤，并采用一些辅助手段实现每一过程。规范设计法从本质上看仍然是手工设计方法，其基本思想是过程迭代和逐步求精。（2）基于E-R模型的数据库设计方法基于E-R模型的数据库设计方法用E-R模型来设计数据库的概念模型，是数据库概念设计阶段广泛采用的方法。（3）3NF的设计方法3NF的设计方法用关系数据理论为指导来设计数据库的逻辑模型，是数据库逻辑设计阶段采用的一种有效方法。（4）ODL（ObjectDefinitionLanguage）方法ODL方法是面向对象的数据库设计方法。该方法用面向对象的概念和术语来说明数据库结构，可以描述面向对象数据库结构设计，也可以直接转换为面向对象的数据库。332.4关系数据库应用系统设计概述2．数据库设计工具数据库工作者和数据库开发商一直在研究和开发数据库设计工具，经过几十多年的努力，数据设计工具实用化和产品化逐步成熟。1）SyBasePowerDesigner。它支持PB、VB、Delphe等语言，通过ODBC可以连接市面上流行的30多个数据库，包括dBase、FoxPro、VisualFoxPro、SQLServer等。2）ERWin。ERWin/ERX3.0是美国LogicWorks公司提供的数据库设计工具，ERWin/ERX可以进行逆向工程，能够自动生成文档，支持与数据库同步，支持团队式开发，所支持的数据库多达20多种。ERWin/ERX数据库设计工具可以用于设计生成客户机/服务器、Web、Intranet和数据仓库等应用程序数据库。

第三方数据库设计工具还有Rational公司的RationalRose和Microsoft公司的Visio。RationalRose与ERwin类似，而Visio则以其方便的办公图表绘制著称。342.4关系数据库应用系统设计概述2.4.2数据库设计原则和步骤1．数据库设计原则为了合理地组织数据，应遵从以下原则。（1）关系数据库的设计应遵从概念单一化、“一事一表”原则（2）避免在表之间出现重复字段（3）表中的字段必须是原始数据和基本数据元素2．数据库设计步骤一个数据库应用系统开发和设计过程大致包括六个阶段：需求分析、概念设计、逻辑设计、物理设计、应用开发和运行维护。352.4关系数据库应用系统设计概述（1）需求分析需求分析是整个数据库设计过程中最重要的步骤之一，是后继各阶段的基础。在需求分析阶段，从多方面对整