sql语句中出现笛卡尔乘积 SQL查询入门篇
我的图书馆
sql语句中出现笛卡尔乘积 SQL查询入门篇
本篇文章中，主要说明SQL中的各种连接以及使用范围，以及更进一步的解释关系代数法和关系演算法对在同一条查询的不同思路。&多表连接简介在关系数据库中，一个查询往往会涉及多个表，因为很少有数据库只有一个表，而如果大多查询只涉及到一个表的，那么那个表也往往低于第三范式，存在大量冗余和异常。因此，连接(Join)就是一种把多个表连接成一个表的重要手段.比如简单两个表连接学生表(Student)和班级(Class)表，如图:进行连接后如图：笛卡尔积笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积(交叉连接)在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义，下面看内连接内连接如果分步骤理解的话，内连接可以看做先对两个表进行了交叉连接后，再通过加上限制条件(SQL中通过关键字on)剔除不符合条件的行的子集,得到的结果就是内连接了.上面的图中，如果我加上限制条件对于开篇中的两个表，假使查询语句如下：&
[Student] s
c.ClassID=s.StudentClassID&可以将上面查询语句进行分部理解，首先先将Class表和Student表进行交叉连接,生成如下表:然后通过on后面的限制条件，只选择那些StudentClassID和ClassID相等的列（上图中划了绿色的部分）,最终，得到选择后的表的子集当然，内连接on后面的限制条件不仅仅是等号，还可以使用比较运算符,包括了&（大于）、&=（大于或等于）、&=（小于或等于）、&（小于）、!&（不大于）、!&（不小于）和&&（不等于）。当然，限制条件所涉及的两个列的数据类型必须匹配.对于上面的查询语句，如果将on后面限制条件由等于改为大于:&
[Student] s
c.ClassID&s.StudentClassID&则结果从第一步的笛卡尔积中筛选出那些ClassID大于StudentClassID的子集:虽然上面连接后的表并没有什么实际意义，但这里仅仅作为DEMO使用:-)关系演算上面笛卡尔积的概念是关系代数中的概念，而我在前一篇文章中提到还有关系演算的查询方法.上面的关系代数是分布理解的，上面的语句推导过程是这样的：“对表Student和Class进行内连接，匹配所有ClassID和StudentClassID相等行，选择所有的列”而关系演算法，更多关注的是我想要什么,比如说上面同样查询，用关系演算法思考的方式是“给我找到所有学生的信息，包括他们的班级信息，班级ID，学生ID，学生姓名”用关系演算法的SQL查询语句如下：&
[Student] s
c.ClassID=s.StudentClassID&当然，查询后返回的结果是不会变的：外连接假设还是上面两个表，学生和班级.我在学生中添加一个名为Eric的学生，但出于某种原因忘了填写它的班级ID：&当我想执行这样一条查询：给我取得所有学生的姓名和他们所属的班级:&
s.StudentName,c.ClassName
[fordemo].[dbo].[Student] s
[fordemo].[dbo].[Class] c
s.StudentClassID=c.ClassID&结果如下图:可以看到，这个查询“丢失”了Eric..这时就需要用到外连接，外连接可以使连接表的一方，或者双方不必遵守on后面的连接限制条件.这里把上面的查询语句中的inner join改为left outer join:&
s.StudentName,c.ClassName
[fordemo].[dbo].[Student] s
[fordemo].[dbo].[Class] c
s.StudentClassID=c.ClassID&结果如下：Eric又重新出现.右外连接右外连接和左外连接的概念是相同的，只是顺序不同，对于上面查询语句，也可以改成:&
s.StudentName,c.ClassName
[fordemo].[dbo].[Class] c
[fordemo].[dbo].[Student] s
s.StudentClassID=c.ClassID&效果和上面使用了左外连接的效果是一样的.全外连接全外连接是将左边和右边表每行都至少输出一次,用关键字”full outer join”进行连接，可以看作是左外连接和右外连接的结合.自连接谈到自连接，让我们首先从一个表和一个问题开始：上面员工表(Employee)，因为经理也是员工的一种，所以将两种人放入一个表中，MangerID字段表示的是当前员工的直系经理的员工id.现在，我的问题是，如何查找CareySon的经理的姓名?可以看出，虽然数据存储在单张表中，但除了嵌套查询(这个会在后续文章中讲到)，只有自连接可以做到.正确自连接语句如下:&
m.EmployeeName
[fordemo].[dbo].[Employee] e
[fordemo].[dbo].[Employee] m
e.ManagerID=m.id
e.EmployeeName='Careyson'&在详细解释自连接的概念之前，请再看一个更能说明自连接应用之处的例子:这个表是一个出席会议记录的表，每一行表示出席会议的记录(这里，由于表简单，我就不用EmployeeID和MeetingID来表示了，用名称对于理解表更容易些)好了，现在我的问题是:找出既参加“谈论项目进度”会议，又参加”讨论职业发展”会议的员工乍一看上去很让人迷惑是吧,也许你看到这一句脑中第一印象会是:&
EmployeeName
[fordemo].[dbo].[MeettingRecord] m
MeetingName='¨???????????¨¨'
meetingName='¨???????¨°???¤?é?1'&（我用的代码高亮插件不支持中文，所以上面where子句后面第一个字符串是’谈论项目进度’，第二个是’讨论职业发展’）恩，恭喜你，答错了…如果这样写将会什么数据也得不到.正确的写法是使用自连接！自连接的是一种特殊的连接，是对物理上相同但逻辑上不相同的表进行连接的方式。我看到百度百科上说自连接是一种特殊的内连接,但这是错误的,因为两个相同表之间不光可以内连接，还可以外连接，交叉连接…在进行自连接时，必须为其中至少一个表指定别名以对这两个表进行区分！回到上面的例子，使用自连接，则正确的写法为:&
m.EmployeeName
[fordemo].[dbo].[MeettingRecord] m,
[fordemo].[dbo].[MeettingRecord] m2
m.MeetingName='¨???????????¨¨'
m2.MeetingName='¨???????¨°???¤?é?1'
m.EmployeeName=m2.EmployeeName&（关于乱码问题，请参考上面)多表连接多个表连接实际上可以看成是对N个表进行n-1次双表连接.这样理解会让问题简单很多!&&比如上面三个表，前两个表是我们已经在文章开始认识的，假设现在又添加了一个教师表，对这三个表进行笛卡尔积如下:&
[fordemo].[dbo].[Class]
[fordemo].[dbo].[Teacher]
[fordemo].[dbo].[Student]&结果可以如图表示:总结文中对SQL中各种连接查询方式都做了简单的介绍，并利用一些Demo实际探讨各种连接的用处，掌握好各种连接的原理是写好SQL查询所必不可少的！&&&-------------------------------------------------------------&&没有join条件导致笛卡尔乘积学过线性代数的人都知道，笛卡尔乘积通俗的说，就是两个集合中的每一个成员，都与对方集合中的任意一个成员有关联。可以想象，在SQL查询中，如果对两张表join查询而没有join条件时，就会产生笛卡尔乘积。这就是我们的笛卡尔乘积导致的性能问题中最常见的案例：开发人员在写代码时遗漏了join条件。发生笛卡尔乘积的sql：view plaincopy to clipboardprint?select sum(project_fj.danjia*project_fj.mianji) from project_fj,orderform where project_fj.zhuangtai='未售' and project_fj.project_id=30&select sum(project_fj.danjia*project_fj.mianji) from project_fj,orderform where project_fj.zhuangtai='未售' and project_fj.project_id=30这个语句其实只是sql语句的一部分，问题是另一部分用到了表orderform，所以from中有orderform，但是上面的这部分语句完全没有用到orderform，但是不设置条件就导致了笛卡尔乘积。解决方法：使用LEFT JOINview plaincopy to clipboardprint?select sum(project_fj.danjia*project_fj.mianji) from project_fj LEFT JOIN orderform ON project_fj.id=orderform.project_id&where project_fj.zhuangtai='未售' and project_fj.project_id=30&select sum(project_fj.danjia*project_fj.mianji) from project_fj LEFT JOIN orderform ON project_fj.id=orderform.project_idwhere project_fj.zhuangtai='未售' and project_fj.project_id=30本文出自“suixufeng的专栏”
TA的最新馆藏[转]&[转]&[转]&[转]&[转]&
喜欢该文的人也喜欢【图文】06-第六章 关系系统及其优化-new_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
06-第六章 关系系统及其优化-new
登录百度文库，专享文档复制特权，财富值每天免费拿！
你可能喜欢百度题库旨在为考生提供高效的智能备考服务，全面覆盖中小学财会类、建筑工程、职业资格、医卫类、计算机类等领域。拥有优质丰富的学习资料和备考全阶段的高效服务，助您不断前行！
京ICP证号&&
京网文[3号&&
Copyright (C) 2017 Baidu数据库原理教程习题答案(科学出版社)97
您的位置： →
数据库原理教程习题答案(科学出版社)
第1章数据库系统概述习题参考答案1.1数据处理和数据库应用的例子:电话公司使用数据库存储客户基本信息、客户的每次通话信息以及雇员基本信息、雇员业绩信息等。典型的数据处理包括电话收费、生成客户长途电话话单、计算雇员工资等图书馆使用数据库存储图书资料信息、读者基本信息、图书借阅信息等。典型的数据处理包括新书登记、处理读者借还图书等税务局使用数据库存储纳税人（个人或公司）信息、纳税人缴纳税款信息等。典型的数据处理包括纳税、退税处理、统计各类纳税人纳税情况等。银行使用数据库存储客户基本信息、客户存贷款信息等。典型的数据处理包括处理客户存取款等。超市使用数据库存储商品的基本信息、会员客户基本信息、客户每次购物的详细清单。典型的数据处理包括收银台记录客户每次购物的清单并计算应交货款。1.2DBMS是数据库管理系统的简称，是一种重要的程序设计系统。它由一个相互关联的数据集合和一组访问这些数据的程序组成。数据库是持久储存在计算机中、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和存储，可以被各种用户共享，具有较小的冗余度、较高的数据独立性，并且易于扩展。数据库系统由数据库、DBMS（及其开发工具）、应用系统和数据库管理员组成。数据模型是一种形式机制，用于数据建模，描述数据、数据之间的联系、数据的语义、数据上的操作和数据的完整性约束条件。数据库模式是数据库中使用数据模型对数据建模所产生设计结果。对于关系数据库而言，数据库模式由一组关系模式构成。数据字典是DBMS维护的一系列内部表，用来存放元数据。所谓元数据是关于数据的数据。1.3DBMS提供如下功能：（1）数据定义：提供数据定义语言DDL，用于定义数据库中的数据对象和它们的结构。（2）数据操纵：提供数据操纵语言DML，用于操纵数据，实现对数据库的基本操作（查询、插入、删除和修改）。（3）事务管理和运行管理：统一管理数据、控制对数据的并发访问，保证数据的安全性、完整性，确保故障时数据库中数据不被破坏，并且能够恢复到一致状态。（4）数据存储和查询处理：确定数据的物理组织和存取方式，提供数据的持久存储和有效访问；确定查询处理方法，优化查询处理过程。（5）数据库的建立和维护：提供实用程序，完成数据库数据批量装载、数据库转储、介质故障恢复、数据库的重组和性能监测等。（6）其他功能：包括DBMS与其它软件通信、异构数据库之间数据转换和互操作等。1.4使用数据库进行信息管理具有如下优点：（1）数据整体结构化：在数据库中，数据的组织面向整个机构、面向所有可能的应用，而不是某个具体部门或某个特定的应用。数据结构不仅描述现实世界的对象，而且（2）（3）（4）（5）描述对象之间的联系。数据可以充分共享：数据库中的数据的面向整个机构组织使得它能够更好地被多个用户、多个应用程序共享。数据独立性：数据独立性是指数据与应用程序相互独立，包括数据的物理独立性和数据的逻辑独立性。数据的结构用数据模型定义，无需程序定义和解释。数据由DBMS同一管理和控制，使得系统能够为数据管理提供更多的支持。这些支持包括：提供事务支持、增强安全性、保证完整性、平衡相互冲突的请求和面对故障的弹性。标准化：使用数据库进行信息管理有利于制定部门标准、行业标准、工业标准、国家标准和国际标准，促进数据库管理系统和数据库开发工具的研制、开发，推动数据管理应用的健康发展。1.5数据模型的三个基本要素是：数据结构：描述数据库的对象和对象之间的联系，是对数据的静态描述。数据操作：数据库中各种对象允许的操作和操作规则，使对系统的动态描述。完整性约束：一组完整性规则，用以限定符合数据模型的数据库状态和状态的变化，保证数据的正确、有效和相容。对于关系数据库而言，关系模型只有一种数据结构――关系。现实世界中的对象和对象之间的联系都用关系表示。关系是元组的集合。从用户角度来看，关系是一张二维表。在关系模型中，定义数据操作的方法有两种：关系代数和关系演算。关系代数显式地定义了一些关系运算，而关系演算的基础是一阶谓词逻辑，它用逻辑公式表示查询结果必须满足的条件。关系模型的完整性约束包括实体完整性、参照完整性和用户定义的完整性。其中实体完整性和参照完整性是通用完整性约束，由关系模型明确定义。1.6数据库系统的三级模式是指外模式、模式和内模式。外模式是特定数据库用户的数据视图，是与某一具体应用相关的数据局部逻辑结构的描述。模式是数据库中全体数据的总体逻辑结构描述，是所有用户的公共数据视图。内模式是数据物理结构和存储方式的描述，定义数据在数据库内部的表示方式。数据库系统的三级模式提供了三个层次的数据抽象。这样做的一个优点是可以隐蔽数据存储细节，从而隐蔽系统内部的复杂性，简化系统的用户界面。另一个优点是可以带来数据的独立性。1.7所谓数据独立性是指数据独立于应用程序，分数据的逻辑独立性和数据的物理独立性两种。数据的逻辑独立性是指应用程序与数据库的逻辑结构之间的相互独立性。当数据的逻辑结构改变时，通过修改外模式-模式映像，保持外模式不变，从而使得建立在外模式上的应用程序也可以不变。数据的物理独立性是指应用程序与存储在磁盘上的数据库中数据之间的相互独立性。当数据的物理存储结构改变时，通过修改模式-内模式映像，保持模式不变。由于外模式是定义在模式上的，模式不变，则外模式不需要改变，从而使得建立在外模式上的应用程序也可以不变。数据的逻辑独立性是指数据的逻辑结构改变不影响应用程序，而数据的物理独立性是指数据的物理组织（存储结构）改变不影响应用程序。1.8DBA的主要职责包括：（1）决定数据库中的信息内容和数据的逻辑结构。（2）决定数据库的存储结构和存取策略。（3）定义数据的安全性要求和完整性约束条件。（4）数据库系统的日常维护：周期性转储数据库、故障恢复、监督系统运行、优化系统性能、设置必要的审计。（5）重组和重构数据库。第2章实体-联系模型部分习题参考答案2.1解释术语：实体是客观存在并且可以相互区分的任何事物。实体集是具有相同属性的实体的集合。联系是多个实体之间的相互关联。联系集是相同类型联系的集合。形式地说，设E1,E2,…,En是n（n?2）个实体集，它们不必互不相同。联系集R是{(e1,e2,…,en)|e1?E1,e2?E2,…,en?En}的一个子集，其中(e1,e2,…,en)?R是一个联系，并称ei（1?i?n）是该联系的参与者，n是联系的度（元）。简单属性是不能划分成更小的部分的属性。复合属性是可以划分成更小部分的属性（即可以分成一些其他属性）。单值属性是一个特定的实体在该属性上只能取单个值的属性。多值属性是特定的实体在该属性上可以取多个值的属性。基本属性是其值不能通过其他属性的值推导出来的属性。派生属性又称计算属性，是其值可以从其他相关属性或实体计算得到的属性。码是主码或候选码的简称。主码是指数据库的设计者选中的，用来区分同一实体集中不同实体的候选码。候选码：其真子集都不是超码的极小超码称为候选码。超码：其值可以惟一确定实体集中每个实体的属性集称为该实体集的超码。一对一联系：如果E1中的每个实体最多与E2中的一个实体相关联，并且E2中的每个实体也最多与E1中的一个实体相关联，则称E1和E2之间联系为一对一联系。一对多联系：如果E1中的每个实体都可以与E2中任意多个实体相关联，而E2中的每个实体最多与E1中一个实体相关联，则称这种联系为E1到E2的一对多联系。多对一联系：如果E1中的每个实体最多与E2中的一个实体相关联，而E2中的每个实体都可以与E1中任意多个实体相关联，则称这种联系为E1到E2的多对一联系。多对多联系：如果E1中的每个实体都可以与E2中任意多个实体相关联，并且E2中的每个实体也可以与E1中任意多个实体相关联，则称E1和E2之间联系为多对多联系。2.2商品应当包含如下属性：商品条码：标识商品。商品名称：用户识别。商品类别：用于商品分类。生产商：生产时间：进价：销售价：存货数量：2.3所有部门形成一个实体集，所有经理形成一个实体集。假定每个部门最多只有一个经理，而每个人只能在一个部门出任经理，那么部门与经理之间的联系“管理”是一对一联系。如果允许部门经理空缺，但一个人是经理的话，必须在一个部门任职，那么经理对联系“管理”的参与是全部参与，而部门是部分参与。所有学生形成一个实体集，所有院系形成一个实体集。每个院系由多个学生，而每个学生只能在一个院系。因此，学生与院系之间的联系是多对一联系。通常，一个学生总在一个院系中，而每个院系都有学生。因此，学生和院系对该联系都是全部参与。商品是一个实体集，订单是一个实体集。每个订单可以包括多种商品，而一种商品可以被多个订单订购。这样，商品与订单之间的联系“订购”是多对多联系。通常，每个订单至少包含一种商品，而每种商品都会被某个订单订购（否则就不再销售这种商品）。这样，商品和订单对该联系的参与都是全部参与。2.4按以下要求各举一个实际例子：（1）三个实体集两两之间都存在多对多联系（在你的例子中，三个实体集之间还存在有意义的联系吗？），（2）三个实体集之间存在多对多联系（在你的例子中，其中两个实体集之间还存在有意义的联系吗？）。(1)实体集教师、课程和学生两两之间的多对多联系教师和课程之间的联系“讲授”是多对多的：一个教师教多门课程，一门课程由多位教师讲授课程和学生之间的联系“选修”是多对多的：一门课程可以被多个学生选修，一个学生可以选多门课程。学生和教师之间的联系“师生”也是多对多的：一个学生可以有多位教师，一个教师可以有多个学生。教师、课程和学生三者之间也存在有意义的联系，表明特定的学生选修了特定教师讲授的特定课程。(2)供应商、零件和项目之间的多对多联系“供应”一个供应商向多个项目提供多种零件；一种零件由多个供应商提供，并用于多个项目；一个项目使用多个供应商提供的多种零件。这三个实体集中两个实体集之间的有意义联系实际上“供应”的投影。2.5弱实体集的主码可以通过它与强实体集的联系推断。如果将强实体集的主码属性添加到弱实体集，那么这些属性将通过实体集和联系两种方式提供，从而导致冗余。此外，实体集应当只包含描述该实体的属性，强实体集的主码属性并不是描述弱实体集的，因此添加它们使得模型不清晰。2.6如果一部分实体集通过E-R图的一条路径相连接，则这些实体集是相关的，或许是间接相关的。一个非连通的图意味一部分实体集与另一部分实体集是不相关的。如果我们将E-R图划分成连通分支，则事实上我们就有了一些分离的数据库，每个对应一个连通分支。如上所述，一对实体集之间的路径指明这两个实体集之间的一种联系（可能是间接的）。如果图中存在环，则环中每对实体集至少可以通过两种不同的方式相关联。如果E-R图是无环的，则每对实体集之间至多存在一条路径，因此每对实体集之间至多存在一种联系。2.7假定每辆汽车只属于一位客户。涉及的实体集有：客户、汽车和事故。需要建立如下联系：拥有：客户与汽车之间的多对一联系发生：客户、汽车和事故之间的多对多联系。损坏估计最好作为联系“发生”的属性，因为损坏估计不仅与事故有关，而且与特定客户的特定汽车有关。E-R图如图2.1所示。图2.1习题2.7的E-R图2.8假定一个客户可以有多个账户，但一个账户只属于一个客户。涉及的实体集有：账户、支行、客户和贷款。题中已经清楚描述。建立如下联系：账户-支行：账户与支行之间的多对一联系，其中账户全部参与。贷款-支行：贷款与支行之间的多对一联系，其中贷款全部参与。借贷：客户与贷款之间的多对一联系，其中贷款全部参与。账户与客户之间有两种联系：存取款：客户与账户之间的多对多联系，包括属性存取金额和存取日期。属于：客户与账户之间的一对多联系。E-R图如图2.2所示。2.9方法一：使用弱实体建立弱实体集“贷款偿还”，包括属性：偿还编号（顺序号）、偿还日期、偿还金额；建立建立“贷款偿还”与其标识实体集“贷款”之间的标识性联系“还贷”方法二：使用多值属性将“贷款偿还”作为贷款的多值复合属性，它包括属性：偿还编号（顺序号）、偿还日期、偿还金额方法三：使用强实体集建立强实体集“贷款偿还”，包括属性：贷款编号、偿还编号（顺序号）、偿还日期、偿还金额；建立建立“贷款偿还”与“贷款”之间的联系“还贷”方法一最好，方法三最差，理由与职工-家属的例子类似。2.10假定：每位职工在同一时间段只从事一项工作。每位职工不能同时在多个部门工作，也不能是多个部门的经理。每位职工不能同时参加多个项目。每位职工的办公室唯一。一个项目只由一个部门承担。一个办公室职能属于一个部门。该问题涉及的实体集有：部门、职工、项目、办公室和职工的工作经历，其中工作经历存在依赖于职工，是弱实体集，其余是强实体集。电话只有一个属性“电话号码”，不把它视为实体集。一个办公室有多部电话，但假定每位职工只有一部电话。需要建立如下联系：管理：职工与部门之间一对一联系工作：职工与部门之间多对一联系图2.3习题2.10的E-R图承担：部门与项目之间一对多联系部-办：部门与办公室之间一对多联系参加：职工与项目之间多对一联系职-办：职工与办公室之间多对一联系属于：弱实体集工作简历与标识实体集职工之间的多对一联系E-R图如图2.3所示。第3章关系模型习题参考答案3.1解释术语：域是具有相同类型的值的集合。笛卡尔积：给定n个域D1,D2,…,Dn（它们不必互不相同）上的笛卡尔积记作D1?D2?…?Dn，定义为{(d1,d2,…,dn)|d1?D1?d2?D2?…?dn?Dn}。关系：域D1,D2,…,Dn上的关系r是笛卡尔积D1?D2?…?Dn的任意子集。元组：笛卡尔积或关系的每个元素(d1,d2,…,dn)称为一个n-元组（简称元组）属性：关系用一个二维表表示。列通常是命名的，称为属性。关系的码：关系R的属性集K是它的码，如果K是R的超码，并且K的任何真子集都不是R的超码（即K是极小超码）。或X是关系R的超码，如果t1和t2是R的任意实例中的元组，并且t1[X]=t2[X]，则t1=t2。候选码：所有的码都称候选码。主码：由多个码中选出的作为惟一识别关系元组的码外码：如果FK是关系R的属性集，并且不是R的码，但是FK与关系R’的主码K’对应，则称FK是关系R的外码。关系模式：关系模式用关系模式名、关系模式的诸属性和属性对应的域，以及属性间的数据依赖集定义。通常简单地用关系模式名和属性列表表示R(A1,A2,…,An)。关系数据库模式：由若干域的定义和一组定义在这些域上的关系模式组成。3.2实体完整性：关系R的所有元组在主码上的值必须惟一，并且在主码的任何属性上都不能取空值。参照完整性：如果属性集FK是关系R的外码，它参照关系S的主码Ks，则R的任何元组在FK上的值或者等于S的某个元组在主码Ks上的值，或者为空值。3.3除了语义约束之外，关系数据库对关系的主要限制是：(1)在关系数据库中，我们只考虑有限关系（笛卡尔积的有限子集），因为无限关系既不能显式存储，也不能有效地显示。(2)关系的每个属性都必须是原子的，即每个属性只能取原子值。在关系数据库中，原子值是数据访问的最小单位。属性的原子性要求是规范化关系的基本要求。3.4事实上，关系R的外码参照被参照关系S（目标关系）反映R的某些元组每个都与S的某个特定元组之间存在联系。有些实际问题允许R的某些元组与S的任何元组都没有联系，在这种情况下，允许R的这些元组在外码上取空值。例如，在关系Employees(Eno,Ename,Salary,Dno)中，Dno是外码。有些公司允许某些职工（如公司总裁）不属于任何特定的部门，这些职工的元组在Dno上可以取空值。假定所有的关系模式都是E-R图转换得到的。(1)如果关系R是联系集转换的，则R代表联系集，其外码的值代表参与联系的特定实体集的一个特定实体。此时，R外码都不能取空值。(2)设关系R的外码FR参照被参照关系S。如果关系R是实体集E1转换的，则E1必然通过某个联系R’与S对应的实体集相关联。当这种联系不要求是完全的时，R的某些元组可以不参照S的任何元组，此时外部码FR的属性值可以为空；反之不能为空。3.5自然连接和等值连接的相同之处是二者都是根据属性值相等进行连接。二者的不同之处是：自然连接在相同属性上进行相等比较，并投影去掉重复属性；等值连接并不要求一定在相同属性上进行相等比较，也不删除重复属性。3.6由强实体集得到的关系模式：EmployeesEname,Salary)DepartmentsDptname)SuppliersSname,Saddress)ItemsIname,Stocks)OrdersData)CustomersCname,Caddress,Balance)其中主码用下横线标示（下同）。注意，Departments的Dptname也是码，但我们选择Dno为主码。由弱实体集Dependents得到如下关系模式：DependentsReltoEmp,Birthday)将联系转换成关系模式时，不再考虑弱实体集的存在依赖联系。其余6个联系产生如下关系模式：ManagesWorks_inDno)CarriesIno)SuppliesPrice)IncludesQuantity)Placed_byOno)其中Works_in和Manages有相同的属性，但它们的实际意义不同。下一步，我们合并具有相同码的关系模式。首先，考虑Manages。它与Employees和Departments都包含相同的码。Maneges与Employees合并更容易回答“某职工的经理是谁”这类问题，而与Departments合并更容易回答“某部门的经理是谁”这类问题。考虑到后一类问题更经常出现，我们决定将Manages合并到Departments，并将Manages中属性Eno改名为Mrgno（表示经理的职工号），得到如下关系模式：DepartmentsDptname,Mrgno)还有三对关系具有相同的码，它们是Employees和Works_in，Items和Carries，Orders和Placed_by。它们都可以直接合并。最后，我们得到图3.11所示E-R模型的一组关系模式：EmployeesEname,Salary,Dno)DepartmentDptname,Mrgno)SuppliersSname,Saddress)ItemsIname,Stocks,Dno)OrdersData,Cno)CustomersCname,Caddress,Balance)DependentsReltoEmp,Birthday)SuppliesPrice)IncludesQuantity)3.7习题3.6的关系模式的模式图如图3.1所示。DepartmentsDnoDptnameMrgnoEmployeesEnoEnameSalaryDnoIncludesOnoInoQuantityDependentsEnoDnameReltoEmpBirthdayOrdersOnoDateCnoCustomersItemsInoStocksDnoSuppliesSnoInoQuantitySuppliersSnoSnameSaddressCnoCnameCaddressBalance图3.1某公司数据库系统的数据库模式图3.8得到的一组关系模式如下：客户（客户ID，姓名，地址，电话）ID）事故（事故编号，发生时间，事故地点）发生（驾照号，车辆编号，事故编号，损坏估计）其中，客户与汽车之间的联系已经合并到关系模式“汽车”中，并将“客户ID”改为“车主ID”。3.9由实体集得到如下关系模式：账户（账号，余额）支行（支行名称，城市，街道，资产）客户（客户ID，姓名，地址，联系电话）由联系得到如下关系模式：账户-支行（账号，支行名称）贷款-支行（贷款号，支行名称）借贷（客户ID，贷款号）存取款（客户ID，账号，存取金额，存取日期）属于（账号，客户ID）合并具有相同码的关系模式：账户、账户-支行、属于具有相同码，合并成一个关系模式账户，并用开户行替换支行名称，开户人替换客户标识。合并后的关系模式如下：账户（账号，余额，开户行，开户人）贷款和贷款-支行具有相同码，合并成一个关系模式贷款，并用贷款支行替换支行名称。合并后的关系模式如下：贷款（贷款号，贷款日期，贷款金额，贷款支行）最后得到的一组关系模式如下：账户（账号，余额，开户行，开户人）支行（支行名称，城市，街道，资产）贷款（贷款号，贷款日期，贷款金额，贷款支行）存取款（客户ID，账号，存取金额，存取日期）3.10电话号码是多值属性，需要创建一个关系模式。我们称该关系模式为电话，它的码为电话号码。该关系模式定义如下：由强实体集得到如下关系模式：部门（部门号，部门名称，预算）办公室（办公室名称，位置）由弱实体集“工作简历”得到如下关系模式：工作简历（职工号，开始时间，任务，工资，截止时间）由联系集得到如下关系模式：承担（项目名称，部门号）部-管理（职工号，部门号）参加（职工号，项目名称）职-办（职工号，办公室名称）合并具有相同码的关系模式：项目与承担具有相同码，合并为项目，并将部门号改为承担部门，得到：项目（项目名称，项目预算，承担部门）办公室与部-办具有相同码，合并为办公室，并将部门号改为所属部门，得到：办公室（办公室名称，位置，所属部门）管理有两个码，可以与职工合并（有利于回答“某职工的经理是谁”这类问题），也可以与部门合并（有利于回答“某部门的经理是谁”这类问题）。考虑“某部门的经理是谁”这类问题更常出现，决定于部门合并，并将职工号改为经理，得到：职工、参加、工作和职-办都具有相同码，合并为职工，得到：最后，我们得到如下关系模式：部门（部门号，部门名称，预算，经理）办公室（办公室名称，位置，所属部门）工作简历（职工号，开始时间，任务，工资，截止时间）电话（电话号码，办公室名称）3.11(1)求上海的所有供应商的信息。?Scity=’上海’(Suppliers)(2)求位于郑州的所有工程的信息。?Jcity=’上海’(Projects)(3)求数量在100~150之间的供应。?Quantity?100?Quantity?150(SPJ)(4)求为工程J1提供零件的供应商号。?Sno(?Jno=’J1’(SPJ))(5)求供应工程J1红色零件的供应商号。?Sno(?Jno=‘J1’?Color=‘红色’(SPJParts))(6)求至少提供一种红色零件的供应商名称。?Sname(?Color=‘红色’(SuppliersSPJParts))(7)求不提供零件P2的供应商名称?Sname(Suppliers)??Sname(?Pno=‘P2’(SuppliersSPJ))(8)求没有使用天津供应商生产的红色零件的工程号。使用了天津供应商生产的红色零件的工程号?Jno(?Scity=‘天津’(Suppliers)SPJ?Color=‘红色’(Parts))该题的解?Jno(Projects)??Jno(?Scity=‘天津’(Suppliers)SPJ?Color=‘红色’(Parts))(9)求使用了本地供应商提供的零件的工程号和工程名称。?Jno,Jname(?Scity=Jcity(ProjectsSPJSuppliers))(10)求未使用本地供应商提供的零件的工程号和工程名称。?Jno,Jname(Projects)??Jno,Jname(?Scity=Jcity(ProjectsSPJSuppliers))(11)求至少用了供应商S1所供应的全部零件的工程号。?Jno,Pno(SPJ)??Pno(?Sno=‘S1’(SPJ))(12)求提供所有零件的供应商名称。?Sname?Sno,Pno(SPJ)??Pno(Parts))Suppliers)3.12对于供应商-工程-零件数据库，用元组/域关系演算表示习题3.9中的查询(1)求上海的所有供应商的信息。{t|Suppliers(t)?t[Scity]=’上海’)}(2)求位于郑州的所有工程的信息。{t|Projects(t)?t[Jcity]=’郑州’)}(3)求数量在100~150之间的供应。{t|SPJ(t)?t[Quantity]?100?t[Quantity]?150)}(4)求为工程J1提供零件的供应商号。{t(1)|(?u)(SPJ(u)?u[Jno]=’J1’?t[Sno]=u[Sno])}(5)求供应工程J1红色零件的供应商号。{t(1)|(?u)(?v)(SPJ(u)?Parts(v)?u[Pno]=v[Pno]?u[Jno]=’J1’?v[Color]=’红色’?t[Sno]=u[Sno])}(6)求至少提供一种红色零件的供应商名称。{t(1)|(?u)(?v)(?w)(Suppliers(u)?SPJ(v)?Parts(w)?u[Sno]=v[Sno]?v[Pno]=w[Pno]?v[Color]=’红色’?t[Sname]=u[Sname])}(7)求不提供零件P2的供应商名称{t(1)|(?u)(Suppliers(u)?t[Sname]=u[Sname]??(?v)(SPJ(v)?u[Sno]=v[Sno]?v[Pno]=’P2’))}(8)求没有使用天津供应商生产的红色零件的工程号。{t(1)|(?u)(Projects(u)?t[Jno]=u[Jno]??((?v1)(?v2)(?v3)(Suppliers(v1)?SPJ(v2)?Parts(v3)?u[Jno]=v2[Jno]?v1[Sno]=v2[SnoO]?v2[Pno]=v3[Pno]v1[Scity]=‘天津’?v3[Color]=‘红色’))}(9)求使用了本地供应商提供的零件的工程号和工程名称。{t(2)|(?u)(?v)(?w)(Projects(u)?SPJ(v)?Suppliers(w)??u[Jno]=v[Jno]?v[Sno]=w[Sno]t[Jno]=u[Jno]?t[Jname]=u[Jname])}(10)求未使用本地供应商提供的零件的工程号和工程名称。{t(2)|(?s)(Projects(s)u?Jcity]=w[Scity]??((?u)(?v)(?w)(Projects(u)?SPJ(v)?Suppliers(w)??u[Jno]=v[Jno]?v[Sno]=w[Sno]uJcity]=w[Scity]t[Jno]=u[Jno]?t[Jname]=u[Jname])(11)求至少用了供应商S1所供应的全部零件的工程号。{t(1)|(?u)(Projects(u)?t[Jno]=u[Jno]??(?v)(SPJ(v)?v[Sno]=‘S1’??(?w)(SPJ(w)?u[Jno]=w[Jno]?v[Sno]=w[Sno])))}(12)求提供所有零件的供应商名称。{t(1)|(?u)(Suppliers(u)?t[Sname]=u[Sname]??(?v)(Parts(v)?(?w)(SPJ(w)?u[Sno]=w[Sno]?v[Pno]=w[Pno])))}3.13用域关系演算完成例3.12和例3.13中的查询例3.12(1)列出系编号为MA（数学系）的所有学生的详细信息。{(x1,x2,x3,x4,x5,’MA’)|Students(x1,x2,x3,x4,x5,’MA’)}(2)列出所有课程的课程号、课程名和学分。{(x1,x2,x3)|(?y)(Courses(x1,x2,y,x3))}(3)列出年龄不超过45岁的所有副教授的姓名、性别和年龄。{(x1,x2,x3)|(?y1,y2)(Teachers(y1,x1,x2,x3,’副教授’,y2))}(4)列出选修了课程号为CS201的课程的所有学生的学号。{(x)|(?y)(SC(x,’CS201’,y))}例3.13(1)列出选修了课程号为CS201的课程的所有学生的学号和姓名。?{(x1,x2)|(?x3,x4,x5,x6)(?y)(Students(x1,x2,x3,x4,x5,x6)?SC(x1,’CS201’,y))}(2)列出每个学生选修的每门课程的成绩，要求列出的学号、姓名、课程名和成绩。{(x1,x2,x3,x4)|(?(y1,y2,y3,y4)(?z)(?w1,w2,w3,w4)(Students(x1,x2,y1,y2,y3,y4)?SC(x1,w1,z)?Courses(w1,w2,w3,w4)}(3)求评估得分高于90分的教师所在院系名称、教师姓名、课程名和评估得分。{(x1,x2,x3,x4)|(?(y1,y2)(?z1,z2,z3,z4,z5)(?w1,w2,w3)(?v)(Departments(y1,x1,y2)?Teachers(z1,x2,z2,z3,z4,z5)?Courses(w1,x3,w2,w3)?Teaches(x1,w1,v))}3.14(1)求提供了零件的供应商的个数。gcount-distinct(Sno)(SPJ)(2)求所有零件的平均重量。gavg(Weight)(Parts)(3)求供应商S1提供的每种零件的总数量。Pnosum(Quantity)(?Sno=’S1’g(SPJ))(?(4)求供应商S1供应工程J1的每种零件的总重量。Pnosum(Weight)g(?Pno,WeightSno=’S1’?Jno=’J1’(PartsSPJ)))3.15右外连接RS=(RS)?{(null,…,null)}?(S??S(R限制在R的不属于S的属性S))全外连接限制在S的不属于R的属性上RS=(RS)?(R??R(RS))?…,?…,?(S??S(RS))限制在R的不属于S的属性上3.16(1)将Cno、Cname、Caddress和Balance分别为C0199、李华、郑州市大学北路46号、6000的客户信息插入Customers。Customers?Customers?{(C0199,李华,郑州市大学北路46号,6000)}(2)从Dependents（家属）中删除删除1979年前出生的子女（ReltoEmp=‘子女’）。Dependents?Dependents??year(Birthday)&1979?ReltoEmp=’子女’(Dependents)(3)将销售部门（Dname=‘销售’）的职工工资（Salary）提高4%。Employees??Eno,Ename,Salary*1.04,Dno(?Dname=’销售’(EmployeesDepartments))?(Employees??Eno,Ename,Salary,Dno(?Dname=’销售’(EmployeesDepartments)))第4章关系数据库标准语言SQL习题参考答案4.1SQL的基本特点是：(1)集多种数据库语言于一体：SQL语言集数据定义、数据操纵和数据控制（DCL）功能于一体，语言简洁、风格统一，使用SQL就可以独立完成数据管理的核心操作。(2)高度非过程化：使用SQL语言时，用户只需要说明做什么，而不必指出怎么做。(3)面向集合的操作方式：SQL语言采用集合操作方式，其运算对象、运算结果均是元组的集合。(4)一种语法两种使用方式：SQL既可以作为一种自含式语言独立使用，也可以作为一种嵌入式语言与通用程序设计语言配合使用。在两种使用方式下，SQL语言的语法结构基本一致。(5)功能强大，语言简洁：SQL是一种完整地数据库语言，其功能涵盖数据定义、数据操纵、数据控制等数据管理的主要需求。但SQL语言相对比较简洁，其核心动词只有9个。另外，SQL语言的语法简单，与英语口语的风格类似，易学易用。4.2SQL的基本功能包括：(1)SQL的数据定义语言（DDL）提供了模式定义、修改和删除，基本表定义、修改和删除、域定义修改和删除。(2)SQL的数据操纵语言（DML）提供了数据查询子语言。SQL的数据查询子语言是关系完备的，并且具有关系代数和关系演算的双重特征。(3)SQLDML不仅包括数据查询，而且包括数据（数据插入、删除和修改）语句，允许用户更新数据库。(4)SQLDDL还允许用户定义视图，并且SQLDML允许用户对视图进行查询和受限的更新操作。(5)SQLDDL允许用户定义各种完整性约束条件，并在数据库访问时自动检查，确保数据库操作不会破坏完整性约束条件。(6)SQLDDL还包括授权定义，用来定义用户对数据库对象（基本表、视图等）的访问权限，防止非法访问，确保数据库的安全性。(7)SQL还支持事务，提供了定义事务开始和结束的语句。4.3SQL的数据定义语言DDL包括模式定义、修改和删除；基本表定义、修改和删除；域定义、修改和删除；视图的定义、修改和删除；断言的定义、修改和删除；授权的定义与回收。4.4CREATETABLESuppliers(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8)NOTNULL,StatusINT,ScityCHAR(10));CREATETABLEParts(PnoCHAR(8)PRIMARYKEY,PnameCHAR(16)NOTNULL,ColorCHAR(4),WeightNUMERIC(7,2));CREATETABLEProjects(JnoCHAR(8)PRIMARYKEY,JnameCHAR(20)NOTNULL,JcityCHAR(10));CREATETABLESPJ(SnoCHAR(8),PnoCHAR(8),JnoCHAR(8),QuantityINT,PRIMARYKEY(Sno,Pno,Jno),FOREIGNKEYSnoREFERENCESSuppliers(Sno),FOREIGNKEYPnoREFERENCESParts(Pno),FOREIGNKEYJnoREFERENCESProjects(Jno));4.53.8题对应的表CREATETABLEClients(Driver_idCHAR(10)PRIMARYKEY,CnameCHAR(8)NOTNULL,AddressCHAR(20));CREATETABLECars(Car_noCHAR(10)PRIMARYKEY,ModelCHAR(20),YearDATE);CREATETABLEAccidents(Report_noCHAR(8)PRIMARYKEY,Accident_DateDateTime,LocationCHAR(40));CREATETABLEParticipated(Driver_idCHAR(10),Car_noCHAR(10),Report_noCHAR(8)Damage_amountNUMERIC(8,2),PRIMARYKEY(Driver_id,Car_no,Report_no),FOREIGNKEYDriver_idREFERENCESClients(Driver_id),FOREIGNKEYCar_noREFERENCESCars(Car_no),FOREIGNKEYReport_noREFERENCESAccidents(Report_no));3.9题对应的表CREATETABLEAccounts(Account_noCHAR(10)PRIMARYKEY,BalanceNUMERIC(11,2),Branch_nameCHAR(20),Client_idCHAR(18),FOREIGNKEYBranch_nameREFERENCESBranchs(Branch_name),FOREIGNKEYClient_idREFERENCESClients(Client_id));CREATETABLEBranches(Branch_nameCHAR(20)PRIMARYKEY,CityCHAR(10),StreetCHAR(20)AssetNUMERIC(11,2));CREATETABLEClients(Client_idCHAR(18)PRIMARYKEY,CnameCHAR(8)NOTNULL,AddressCHAR(40),PhoneCHAR(11));CREATETABLELoans(Loan_idCHAR(5)PRIMARYKEY,Loan_DateDATE,AmountNUMERIC(11,2),Branch_nameCHAR(20),FOREIGNKEYBranch_nameREFERENCESBranchs(Branch_name));CREATETABLEBorrow(Client_idCHAR(18),Loan_idCHAR(5),PRIMARYKEY(Client_id,Loan_id),FOREIGNKEYClient_idREFERENCESClients(Client_id),FOREIGNKEYLoan_idREFERENCESLoans(Loan_id));CREATETABLEDeposit(Client_idCHAR(18),Account_noCHAR(10),AmountNUMERIC(11,2),Deposit_DateDATE,PRIMARYKEY(Client_id,Account_no),FOREIGNKEYClient_idREFERENCESClients(Client_id),FOREIGNKEYAccount_idREFERENCESAccounts(Account_id));3.10题对应的表项目（项目名称，项目预算，承担部门）职工（职工号，姓名，地址，电话号码，项目名称，部门号，办公室名称）工作简历（职工号，开始时间，任务，工资，截止时间）CREATETABLEDepartments(DnoCHAR(5)PRIMARYKEY,DptnameCHAR(20),BudgetNUMERIC(11,2),MrgnoCHAR(8),FOREIGNKEYEnoREFERENCESEmployees(Eno));CREATETABLEProjects(PnameCHAR(20)PRIMARYKEY,PbudgetNUMERIC(11,2),DnoCHAR(5),FOREIGNKEYDnoREFERENCESDepartments(Dno));CREATETABLEOffices(OnameCHAR(20)PRIMARYKEY,LocationCHAR(20),DnoCHAR(5),FOREIGNKEYDnoREFERENCESDepartments(Dno));CREATETABLEEmployees(EnoCHAR(8)PRIMARYKEY,EnameCHAR(8),AddressCHAR(40),PhoneCHAR(11),PnameCHAR(20),DnoCHAR(5),OnameCHAR(20),FOREIGNKEYPnameREFERENCESProjects(Pname),FOREIGNKEYDnoREFERENCESDepartments(Dno),FOREIGNKEYOnameREFERENCESOffices(Oname));CREATETABLEResume(EnoCHAR(8),Start_timeDATE,TaskCHAR(20),SalaryNUMERIC(8,2),End_timeDATE,PRIMARYKEY(Eno,Start_time),FOREIGNKEYEnoREFERENCESEmployees(Eno));CREATETABLEPhones(Phone-noCHAR(11)PRIMARYKEY,OnameCHAR(20)FOREIGNKEYOnameREFERENCESOffices(Oname));4.6(1)求上海的所有供应商的信息SELECT*FROMSuppliersWHEREScity=‘上海’;(2)求位于郑州的所有工程的信息SELECT*FROMProjectsWHEREJcity=‘郑州’;(3)求数量在100~150之间的供应SELECT*FROMSPJWHEREQuantityBETWEEN100AND150;(4)求为工程J1提供零件的供应商号SELECTSnoFROMSPJWHEREJno=‘J1’;(5)求供应工程J1红色零件的供应商号SELECTSnoFROMSPJ,PartsWHERESPJ.Pno=Parts.PnoANDJno=‘J1’ANDColor=‘红色’;(6)求至少提供一种红色零件的供应商名称SELECTSnameFROMSPJ,Parts,SuppliersWHERESPJ.Pno=Parts.PnoANDSPJ.Sno=Suppliers.SnoANDColor=‘红色’;(7)求不提供零件P2的供应商名称SELECTSnameFROMSuppliersWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.Sno=Suppliers.SnoANDPno=‘P2’);(8)求没有使用天津供应商生产的红色零件的工程号SELECTJnoFROMProjectsWHERENOTEXISTS(SELECT*FROMSPJ,Parts,SuppliersWHERESPJ.Sno=Suppliers.SnoANDParts.Pno=SPJ.PnoANDColor=‘红色’ANDScity=‘天津’);(9)求使用了本地供应商提供的零件的工程号和工程名称SELECTJno,JnameFROMProjects,Suppliers,SPJWHERESPJ.Sno=Suppliers.SnoANDProjects.Jno=SPJ.JnoANDProjects.Jcity=Suppliers.S或SELECTJno,JnameFROMProjectsWHEREEXISTS(SELECT*FROMSPJ,SuppliersWHERESPJ.Sno=Suppliers.SnoANDProjects.Jno=SPJ.JnoANDProjects.Jcity=Suppliers.Scity);(10)求未使用本地供应商提供的零件的工程号和工程名称SELECTJno,JnameFROMProjectsWHERENOTEXISTS(SELECT*FROMSPJ,SuppliersWHERESPJ.Sno=Suppliers.SnoANDProjects.Jno=SPJ.JnoANDProjects.Jcity=Suppliers.Scity);(11)求至少用了供应商S1所供应的全部零件的工程号SELECTJnoFROMProjectsWHERENOTEXISTS(SELECT*FROMSPJSPJ1WHERESPJ1.Sno=‘S1’ANDNOTEXISTS(SELECT*FROMSPJSPJ2WHERESPJ2.Jno=Projects.JnoANDSPJ2.Pno=SPJ1.Pno));(12)求提供所有零件的供应商名称SELECTSnameFROMSuppliersWHERENOTEXISTS(SELECT*FROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESPJ2.Sno=Suppliers.SnoANDSPJ2.Pno=SPJ1.Pno));4.7(1)求提供了零件的供应商的个数SELECTdistinctSnoFROMSPJ;(2)求所有零件的平均重量SELECTavg(weight)FROMP(3)求供应商S1供应工程J1的每种零件的总重量SELECTsum(weight)FROMParts,SPJWHERESPJ.Pno=Parts.PnoANDSno=‘S1’ANDJno=‘J1’GROUPBYP(4)求供应商S1提供的每种零件的总数量SELECTPno,sum(weight)FROMSPJWHERESno=‘S1’GROUPBYP4.8(1)将Cno、Cname、Caddress和Balance分别为C0199、李华、郑州市大学北路46号、6000的客户信息插入CustomersINSERTINTOCustomersVALUES(‘C0199’,’李华’,’郑州市大学北路46号’,6000)(2)从Dependents（家属）中删除删除1979年前出生的子女（ReltoEmp=‘子女’）DELETEFROMDependentsWHEREReltoEmp=‘子女’ANDyear(Birthday)&1979;(3)将销售部门（Dname=‘销售’）的职工工资（Salary）提高4%UPDATEEmployeesSETSalary=Salary*1.04WHEREDnoIN(SELECTDnoFROMDepartmentsWHEREDname=‘销售’);4.9(1)找出2006年其车辆出过事故的总人数SELECTCount(DistinctDriver_id)FROMParticipated,AccidentsWHEREParticipated.Report_no=Accidents.Report_noANDDate=2006;(2)找出与王明的车有关的事故数量SELECTCount(Report_no)FROMParticipated,ClientsWHEREParticipated.Driver_id=Clients.Driver_idANDCname=‘王明’;(3)删除李莉的Mazada车DELETEFROMCarsWHEREModel=‘Mazada’ANDCar_noIN(SELECTCar_noFROMOwns,ClientsWHEREOwns.Driver_id=Clients.Driver_idANDCname=‘李莉’);(4)对于一个新事故，需要将相应信息插入表Accidents和Participated中。4.10所谓基本表是其关系元组存储在数据库中的表。视图是一种用查询定义的命名的导出表，其关系不存储在数据库中，而是在查询时执行定义视图的查询，由基本表导出。基本表与视图之间的主要区别是前者对应的关系存储在数据库中，而后者对应的关系不在数据库中存储（物化视图除外）。从使用角度而言，对于查询，二者没有区别；而对于更新，只有可更新视图才可以更新。二者之间的联系体现在：所有视图都是直接或间接由基本表定义的。4.11使用视图的优点有：(1)使用视图可以使一些查询表达更加简洁。(2)视图提供了一定程度的逻辑独立性。(3)视图与授权配合使用，可以在某种程度上对数据库起到保护作用。(4)视图使得用户能够以不同角度看待相同的数据。4.12并非所有的视图都是可以更新的，因为对视图的更新要转化为对相应基本表的更新，而对于某些视图，有些更新不能唯一地转换成对基本表的更新。所有更新都能唯一转换成对基本表的更新的视图是可更新的，否则是不可更新的。目前，二者之间的明确边界尚不清楚。我们知道“行列子集”视图是可更新的，而使用聚集函数定义的视图不是可更新的。4.13在需要使用数据库管理数据时，使用SQL语言建立数据库，并对数据进行操作比使用其他通用程序设计语言更便捷，常常也更有效。然而，由于（1）SQL能够表达常见的查询，但是不能表达所有查询。（2）一些非数据库操作，如打印报表、将查询结果送到图形用户界面中，都不能用SQL语句实现。一个应用程序通常包括多个组件，查询、更新只是一个组件，而许多其他组件都需要用通用编程语言实现。这时，我们需要使用嵌入式SQL，而不是单独使用SQL或某种通用程序设计语言。第5章完整性和安全性习题参考答案5.1数据库的完整性是指数据库中的数据的正确性、一致性和相容性。所谓计算机系统安全性是指为计算机系统建立和采取各种安全保护措施，以保护计算机系统中的硬件、软件及数据，防止因偶然或恶意的原因使系统遭到破坏，数据遭到更改或泄露。所谓数据库的安全性是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。数据的完整性和安全性是一个问题的两个方面，都是为了保护数据库中的数据。前者旨在保护数据库中的数据，防止合法用户对数据库进行修改时破坏数据的一致性；而后者旨在保护数据库，防止未经授权的访问和恶意破坏和修改。5.2为了维护数据库的完整性，完整性控制应当作为DBMS核心机制，必须提供：(1)说明和定义完整性约束条件的方法：DBMS的DDL允许用户根据实际问题的语义说明和定义各种完整性约束条件。(2)完整性检查机制：DBMS在数据更新可能破坏完整性时自动进行完整性检查。检查可以在更新操作执行时立即执行，也可以在事务提交时进行。(3)违约处理：当数据更新违反完整性约束时，DBMS应当采取相应的措施，确保数据的完整性。5.3当违反参照完整性时，除了简单的拒绝之外，还可以：(1)级联：进行更新，并且对更新导致违反参照完整性的参照关系元组进行相应更新。通常，这种情况发生在被参照关系的主码改变时。例如，部门调整时将部分或所有部门重新编号，可以级联地更新其他包含部门号的关系元组。(2)置空值：进行更新，并且对更新导致违反参照完整性的参照关系元组的外码置空值。这种处理方法仅当外码允许取空值时才能使用。通常，这发生在被参照关系的元组被删除，并且允许参照关系的元组不参照时。例如，通常允许某个职工不在任何具体部门，这种职工的部门号（外码）可以取空值。当一个部门被撤销时，可以删除该部门在Departments中的对应元组，并且同时将原来在该部门的职工的EMPS元组置空值。(3)置缺省值：进行更新，并且对更新导致违反参照完整性的参照关系元组的外码置缺省值；其中缺省值必须是被参照关系某元组主码上的值。通常，这发生在被参照关系的元组被删除，并且参照关系的元组在外码上有合理缺省值时。例如，许多单位都有人才交流中心这样的部门，对职工进行再培训和重新安排。当一个部门被撤销时，该部门原有职工可以到人才交流中心。这时，可以将这些职工的部门号设置成缺省值――人才交流中心的部门号。5.4CREATETABLESuppliers(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8)NOTNULL,StatusINT,ScityCHAR(10));CREATETABLEParts(PnoCHAR(8)PRIMARYKEY,PnameCHAR(16)NOTNULL,ColorCHAR(4),WeightNUMERIC(7,2));CREATETABLEProjects(JnoCHAR(8)PRIMARYKEY,JnameCHAR(20)NOTNULL,JcityCHAR(10));假定只能删除不向任何工程提供任何零件的供应商，只能删除没有任何供应商提供的零件，但是允许工程下马不依赖于是否有供应商提供零件。CREATETABLESPJ(SnoCHAR(8),PnoCHAR(8),JnoCHAR(8),QuantityINT,PRIMARYKEY(Sno,Pno,Jno),FOREIGNKEYSnoREFERENCESSuppliers(Sno)ONUPDATECASCADE);FOREIGNKEYPnoREFERENCESParts(Pno)ONUPDATECASCADE);FOREIGNKEYJnoREFERENCESProjects(Jno)ONUPDATECASCADEONDELETECASCADE);5.5如果被删除的Employee元组不是经理，则没有任何Employee参照它。此时，仅仅删除该元组。然而，如果被删除的Employee元组是经理，则导致该经理的直接和间接下属的Employee元组被级联地删除。即初始的删除将触发该经理的直接下属的Employee元组被删除。如果这些被删除的元组有经理的话，将触发第二层下属的Employee元组被删除。如此下去，直到初始被删除的经理的所有的直接和间接下属的Employee元组都被删除。5.6断言如下：CREATEASSERTIONsumConstraintCHECK(SELECTsum(amount)FROMloanWHEREloan.branch-name=”大学路支行”)&=(SELECTsum(amount)FROMaccountWHEREloan.branch-name=”大学路支行”))更一般地，下面的断言保证每个支行的贷款总额不超过该支行的资产：CREATEASSERTIONsum-constraintCHECK(NOTEXISTS(SELECT*FROMBranchWHERE(SELECTsum(amount)FROMloanWHEREloan.branch-name=branch.branch-name)&=(SELECTsum(amount)FROMaccountWHEREloan.branch-name=branch.branch-name)))5.7假设(Sno,Cname)是IEGrades的码(1)CREATETRIGGERInsertionTriggerONSCAFTERINSERTONSCREFERENCINGNEWROWASnrowFOREACHROWWHENnrow.SnoIN(SELECTSnoFROMStudentsWHEREDno=‘IE’)BEGINATOMICINSERTINTOIEGradesSELECTS.Sno,Sname,Cname,GradeFROMStudentsS,SC,CoursesCWHERES.Sno=nrow.SnoANDC.Cno=nrow.CnoANDS.Sno=SC.SnoANDC.Cno=SC.CEND(2)CREATETRIGGERUpdateTriggerONSCAFTERUPDATEOFGradeONSCREFERENCINGNEWROWASnrowFOREACHROWWHENnrow.SnoIN(SELECTSnoFROMStudentsWHEREDno=‘IE’)BEGINATOMICUPDATEIEGradesSETGrade=nrow.GradeWHERES.Sno=nrow.SnoANDCname=(SELECTCnameFROMSC,CoursesCWHEREC.Cno=nrow.CnoANDSC.Sno=nrow.SnoANDC.Cno=SC.CEND(3)CREATETRIGGERDeleteTriggerONSCAFTERDELETEONSCREFERENCINGOLDROWASorowFOREACHROWWHENorow.SnoIN(SELECTSnoFROMStudentsWHEREDno=‘IE’)BEGINATOMICDELETEFROMIEGradesWHERES.Sno=orow.SnoANDCname=(SELECTCnameFROMSC,CoursesCWHEREC.Cno=orow.CnoANDSC.Sno=orow.SnoANDC.Cno=SC.CnoEND5.8(1)(2)(3)(4)(5)保证银行数据的安全性的措施包括：机房的安全性：防止入侵者强行闯入或非法潜入（物理层次）。数据的远程备份：防止火灾等灾害性事件破坏数据（物理层次）。安全的网络系统：专用网络和安全的网络软件（网络层次）。安全的操作系统（操作系统层面）和安全的数据库管理系统。严格授权管理：银行要制定严格的权限规定和授权审批制度（行政制度层面），DBA只能对允许的权限实施授权（数据库系统层面）。(6)审计跟踪：对数据库数据的所有修改都必须记录到审计日志中（数据库系统层面），并且只有经过行政授权的高级管理人员才能查阅审计记录（行政制度层面）。5.9SQL中涉及自主存取控制的语句包括：(1)创建用户：在创建用户的同时对创建数据库模式和创建表授权CREATEUSER&用户名&WITHDBA|RESOURSE;(2)授权与授权回收:是自主存取控制的主要手段.包括以下语句授权语句GRANT&权限&,…,&权限&ON&对象类型&&对象名&TO&用户&,…,&用户&[WITHGRANTOPTION];授权回收语句REVOKE&权限&,…,&权限&ON&对象类型&&对象名&,…,&对象类型&&对象名&FROM&用户&,…,&用户&[CASCADE|RESTRICT];(3)关于角色的语句。这些语句祥见下题。5.10当一组用户必须具有相同的存取权限时，使用角色定义存取权限，并对用户授权是方便的，可以简化授权，并有利于授权管理。关于角色的语句：联合使用实现灵活的授权。这些语句是(1)创建角色：CREATEROLE&角色名&;(2)给角色授权：GRANT&权限&,…,&权限&ON&对象类型&&对象名&TO&角色&,…,&角色&;(3)将角色授予其他角色或用户：GRANT&角色&,…,&角色&TO&角色&|&用户&,…,&角色&|&用户&[WITHADMINOPTION];(4)回收角色权限：REVOKE&权限&,…,&权限&ON&对象类型&&对象名&FROM&角色&,…,&角色&;5.11在MAC机制中，主体是系统中的活动实体。主体可以是DBMS管理的实际用户、代表用户的各进程。客体是系统中的被动实体，是受主体操纵的，如文件、基本表、索引、视图等。对于主体和客体，DBMS为它们的每个实例（值）指派一个敏感度标记（Label）。敏感度标记分成若干级别，如绝密（TopSecret）、机密（Secret）、秘密（Confidential）、公开（Public）。主体的敏感度标记称为许可证级别（ClearanceLevel）。客体的敏感度标记称为密级（ClassificationLevel）5.12(1)GRANTALLPRIVILIGESONEmployee,DepartmentTOWanglanWITHGRANTOPTION;(2)GRANTSELECTONDepartmentTOPUBLIC;(3)我们需要在Employee上创建一个视图EMPS：CREATEVIEWEMPSASSELECTEname,Title,DnoFROME然后，将EMPS上的查询权限授予所有用户：GRANTSELECTONEMPSTOPUBLIC;(4)GRANTUPDATE(Salary)ONTABLEEmployeeTOLiY(5)我们需要定义一个视图，包含每个部门的最低、最高和平均工资：CREATEVIEWDeptSalaryASSELECTDname,MIN(Salary)MinSalary,MAX(Salary)MaxSalary,AVG(Salary)averageSalaryFROMEmployeeE,DepartmentDWHEREE.Dno=D.DnoGROUPBYDname然后，将DeptSalary上的查询权限授予ShangHua：GRANTSELECTONDeptSalaryTOShangHua；(6)首先创建一个角色Secretary：CREATEROLESecretary；然后，对角色Secretary授权：GRANTALLPRIVILIGESONDepartmentTOSGRANTSELECT,DELETEONEmployeeTOSGRANTUPDATE(Eno,Ename,Birthday,Title,Dno)ONTABLEEmployeeTOS(7)GRANTSecretaryTOL5.13(1)REVOKEALLPRIVILIGESONEmployee,DepartmentFROMWanglanCASCADE;REVOKESELECTONDepartmentFROMPUBLIC;REVOKESELECTONEMPVIEWFROMPUBLIC;REVOKEUPDATE(Salary)ONTABLEEmployeeFROMLiY(2)REVOKEUPDATE(Title)ONTABLEEmployeeFROMS第6章习题参考答案关系数据库的设计理论6.2函数依赖Driver-id?Name，Driver-id?Address，Driver-id?Phone-noCar-no?Model，Car-no?YearReport-no?Date，Report-no?Location，Report-no?Damage6.3函数依赖Cno?Balance，Cno?CreditLimitOno?Date，Ono?AddressOno,Ino?QTYIno?Description（假设同一种货物可以由不同制造商制造，但具有相同描述）Ino,Plant?QTYOH6.4(1)S→D表示股票红利由股票唯一确定I→B表示每个投资人至多有一个经纪人IS→Q表示投资人和股票唯一确定他/她对该股票的拥有量B→O表示每个经纪人都有唯一的办公室(2)R(B,O,I,S,Q,D)的一个码为IS6.5初始：Result?BDResult?BDEG（使用D?EG）Result?BCDEG（使用BE?C）使用CG?BD不改变ResultResult?ABCDEG（使用CE?AG）Result已包含所有属性，不可能再增大。因此(BD)+=ABCDEG6.6初始：Result=ACResult?ABC（使用A→B）Result?ABCDE（使用BC→DE）再无可用的函数依赖。因此(AC)+=ABCDE。F不逻辑蕴涵函数依赖ACF→DG，因为DG不在AC的闭包中。6.7F1和F2等价。这是因为显然A→B在F2+中。而AB关于F2的闭包为ABC，因此AB→C在F2+中。而D关于F2的闭包为ABCDE，因此D→AC和D→E都在F2+中。这就证明了F1?F2+。反过来，A关于F1的闭包为ABC，因此A→BC在F1中。D关于F1的闭包为ABCDE，因此D→AE在F1中。这表明F2?F1+。综上，F2+?F1+。6.8事实上，F还有3个极小依赖集。对F右端极小化，得到：A?B,A?C,B?A,B?C,C?A,C?B依次删除A?C和C?A得到F的一个极小覆盖Fm2={A?B,B?A,B?C,C?B}依次删除B?C和C?B得到F的一个极小覆盖Fm3={A?B,A?C,B?A,C?A}依次删除A?B和B?A得到F的一个极小覆盖Fm4={A?C,B?C,C?A,C?B}6.9F不是极小函数依赖集。显然，F右部是极小的。考虑ABD?E：(AB)+=ABGH，不包含E；(AD)+=AD，不包含E；(BD)+=BDK，不包含E；因此，ABD?E的左部是极小的。考虑AB?G：A+=A，不包含G；B+=BK，不包含G；因此，AB?G的左部是极小的。考虑CJ?I：C+=CIJ，包含I；因此CJ?I的左部不是极小的。可以删除J，得到C?I。现在，我们有F的等价函数依赖集F’={ABD?E,AB?G,B?K,C?J,C?I,G?H}。容易验证它是极小的。关系模式R的码是ABCD。6.10初始化表在（a）中。A?C将b23和b53改变为b13，B?C将b33改变为b13，结果表在（b）中。Aa1a1b31b41a1Bb12a2a2b42b52CDEABCDEb13a4b15a1b12b13a4b15b23b24b25a1a2b13b24b25b33b34a5b31a2b13b34a5a3a4a5b41b42a3a4a5b53b54a5a1b52b13b54a5(a)(b)C?D将b24、b34和b54改变为a4，DE?C将b13改变为a3，结果表在（c）中。CE?A将b31和b41改变为a1，结果表在（d）中。此时，表的第三行为a1a2a3a4a5，因此?是无损连接分解。Aa1a1b31b41a1Bb12a2a2b42b52Ca3a3a3a3a3(c)Da4a4a4a4a4Eb15b25a5a5a5Aa1a1a1a1a1Bb12a2a2b42b52Ca3a3a3a3a3(d)Da4a4a4a4a4Eb15b25a5a5a56.11因为R1?R2=S，R1?R2=A，而S?A?F，所以?={R1(S,A),R2(S,I,P)}是无损连接分解。6.12使用完全函数依赖概念，2NF的等价定义如下：如果关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF。6.13证明：我们用反证法证明。设R满足6.5.2节的3NF定义。假设R不满足习题中的3NF定义，则存在非主属性A，它传递地依赖于R的某个码，设为K。于是，存在Y使得Y→K在R中不成立，但是A既不属于K也不属于Y，并且Y→A。由于Y→K在R中不成立，Y不是R的超码。又因为A不是主属性，因此Y→A违反6.5.2节的3NF定义，这与R满足6.5.2节的3NF定义相矛盾。这一矛盾表明“R不满足习题中的3NF定义”的假设不成立。设R满足习题中的3NF定义。假设R不满足6.5.2节中的3NF定义，则存在属性A，属性集Y使得Y→A，但是A不是主属性，而Y不是R的超码。设K为R的超码，则K→Y，但Y→K在R中不成立。于是非主属性A传递地依赖于R的码K，与R满足习题中的3NF定义相矛盾。这一矛盾表明“R不满足6.5.2节中的3NF定义”的假设不成立。综上，习题中的3NF定义与6.5.2节的3NF定义等价。6.14证明：(1)如果R的所有属性都是主属性，则R中的任何函数依赖都不违反3NF条件，因此R是3NF。(2)（反证法）设R的码包含R的的所有属性（全码），但R不是BCNF。于是，R中存在X→A（即X?A?F+），并且A?X，但是X不是R的超码。令Y=R?{A}。由于A?X，于是X?Y。由此，Y→X。注意到X→A，于是Y→A。又因为Y=R?{A}，于是Y→R。这与R的码包含所有属性矛盾。这一矛盾表明“R不是BCNF”的假设不成立。6.15证明：(1)证明：如果关系模式R是3NF，则R一定是2NF。举例说明其逆不真。设R是3NF。假设R不是2NF。于是，R中存在X→A，并且A?X，但是A不是主属性，而X是R的某个码（设为K）的真子集。因为X是K的真子集，因此X不可能是R的超码（否则与K是R的码矛盾）。X→A违反3NF定义，于是R不是3NF，与设R是3NF矛盾。这一矛盾表明“R不是2NF”的假设不成立。例如，考虑关系模式SDL(Sno,Sdept,Sloc)，其中Sn0、Sdept和Sloc分别为学生的学号、所在系和住处。假设每个系的学生住在同一座楼，我们有函数依赖：Sno→Sdept，Sdept→SlocSDL的码为Sno。容易验证SDL是2NF，但不是3NF。(2)如果关系模式R是BCNF，则R一定是3NF。举例说明其逆不真。设R是BCNF。假设R不是3NF。于是，R中存在X→A，并且A?X，但是A不是主属性，并且X不是R的超码。这显然与R是BCNF矛盾。这一矛盾表明“R不是3NF”的假设不成立。例如，考虑关系模式STC(S,T,C)，其中S、T和C分别表示学生、教师和课程。这里，非平凡的函数依赖为SC→T和ST→C。STC有两个码(S,C)和(S,T)。STC∈3NF，但STC不是BCNF。(3)如果关系模式R是4NF，则R一定是BCNF。举例说明其逆不真。设R是4NF。假设R不是BCNF。于是，R中存在X→A，并且A?X，但是X不是R的超码。如果XA=R，则X是超码。因此，XA不包含所有属性。根据A8，X→A意味X→→A。由于XA?R，并且A?X，X→→A违反4NF条件，与R是4NF矛盾。例如，考虑关系模式CTB(C,T,B)，其中C表示课程，T表示教师，B表示参考书。一门课程可以由多位教师讲述，但他们必须使用通一组参考书。CTB中存在如下多值依赖：C→→T,C→→BCTB为全码，因此是BCNF（习题16.14）。但C→→T违反4NF条件，因此CTB不是4NF。6.16该关系模式的码是HS（例6.14），所以不是BCNF。C?T违反BCNF条件，使用它将CTHRSG分解为{CT,CHRSG}。CHRSG的码仍然是HS，不是BCNF。CS?G违反BCNF条件，使用它将CHRSG分解为{CSG,CHRS}。CHRS的码仍然是HS，不是BCNF。HR?C违反BCNF条件，使用它将CHRS分解为{HRC,HRS}。现在，所有的模式都是BCNF。我们得到的具有无损连接性的BCNF分解{CT,CSG,HRC,HRS}6.17R(B,O,I,S,Q,D)的函数依赖为S→D，I→B，IS→Q，B→O，码为IS。(1)S→D违反BCNF条件，使用它将R分解为{SD,BOISQ}。BOISQ的码为IS。B→O违反BCNF条件，使用它将BOISQ分解为{BO,BISQ}。BISQ的码为IS。I→B违反BCNF条件，使用它将BISQ分解为{IB,ISQ}。最后得到R的一个具有无损连接性的BCNF分解{SD,BO,IB,ISQ}(2)R的函数依赖已经是正则的，R的一个具有无损连接性和保持函数依赖的3NF分解为{SD,IB,ISQ,BO}6.18容易明白，R的码为VD和SD。(1)S?T违反BCNF条件，使用它将R分解为{ST,SVCPD}.SVCPD的码为VD和SD。V?SC违反BCNF条件，使用它将SVCPD分解为{VSC,VPD}。最后得到R的一个具有无损连接性的BCNF分解{ST,VSC,VPD}(2)R的函数依赖已经是正则的，R的一个具有无损连接性和保持函数依赖的3NF分解为{ST,VSC,SDPV}(3)解释R为什么不存在具有无损连接性和保持函数依赖的BCNF分解.S?T，V?SC和SD?PV6.19例子很多，但需要注意的是：多值依赖的成立依赖于属性集，举例时需要说明属性集。6.20(1)用定义和公理两种方法证明，其余用公理证明。(1)多值依赖的合并规则：如果X→→Y，X→→Z成立，则X→→YZ成立。证明：方法一：使用公理证明：对X→→Y用X增广得到X→→XY。对X→→Z用Y增广得到XY→→YZ。使用自反律，得到XY→→(U?XY?YZ)。而U?XY?YZ=U?X?Y?Z，于是XY→→(U?X?Y?Z)。由X→→XY和XY→→(U?X?Y?Z)，使用多值依赖的传递律得到X→→((U?X?Y?Z)?XY)。而(U?X?Y?Z)?XY=U?X?Y?Z。因此，我们有X→→(U?X?Y?Z)。再次利用自反律，得到X→→(U?X?(U?X?Y?Z))。由于U?X?(U?X?Y?Z)=YZ，于是我们有X→→YZ。方法二：使用定义证明：设U为属性全集。设r是属性集U上的任意关系，满足X→→Y和X→→Z。设t1和t2是r的任意元组，满足t1[X]=t2[X]。为证明X→→YZ成立，我们只要证明存在t3,t4?r，使得(1)t3[X]=t4[X]=t1[X]=t2[X]，(2)t3[YZ]=t1[YZ]，并且t3[U?XYZ]=t2[U?XYZ]，(3)t4[YZ]=t2[YZ]，并且t4[U?XYZ]=t1[U?XYZ]。由于t1,t2?r，X→→Y，存在u1,u2?r，使得(1)u1[X]=u2[X]=t1[X]=t2[X]，(2)u1[Y]=t1[Y]，并且u1[U?XY]=t2[U?XY]，(3)u2[Y]=t2[Y]，并且u2[U?XY]=t1[U?XY]。t1,u1?r，X→→Z，存在u3,u4?r，使得(1)u3[X]=u4[X]=t1[X]=u1[X]，(2)u3[Z]=t1[Z]，并且u3[U?XZ]=u1[U?XZ]，(3)u4[Z]=u1[Z]，并且u4[U?XZ]=t1[U?XZ]。t2,u2?r，X→→Z，存在u5,u6?r，使得(1)u5[X]=u6[X]=t2[X]=u2[X]，(2)u5[Z]=t2[Z]，并且u5[U?XZ]=u2[U?XZ]，(3)u6[Z]=u2[Z]，并且u6[U?XZ]=t2[U?XZ]。这些元组如下所示：XYZUXY11ii+1jj+1kk+1nt2a1…aibi+1…bjbi+1…bkbk+1…bn11ii+1jj+1kj+1nu2a1…aibi+1…bjai+1…akaj+1…an31ii+1jj+1kj+1nu4a1…aiai+1…ajbj+1…bkaj+1…anu5a1…aibi+1…bjbj+1…bkaj+1…anu6a1…aibi+1…bjai+1…akbj+1…bn令t3=u3，t4=u5，于是t3,t4?r，并且(1)t3[X]=t4[X]=t1[X]=t2[X]，(2)t3[YZ]=t1[YZ]，并且t3[U?XYZ]=t2[U?XYZ]，(3)t4[YZ]=t2[YZ]，并且t4[U?XYZ]=t1[U?XYZ]。(2)多值依赖的伪传递规则：如果X→→Y，WY→→Z成立，则WX→→(Z?WY)成立。证明：用W增广X→→Y，得到WX→→WY。注意到WY→→Z成立，使用多值依赖的传递率，我们有WX→→(Z?WY)。(3)混合伪传递规则：如果X→→Y，XY→Z成立，则X→(Z?Y)成立。证明：：用X增广X→→Y，得到X→→XY。由XY→Z和A7得到XY→→Z。由X→→XY和XY→→Z，利用多值依赖的传递率得到X→→(Z?XY)。由于X→→(Z?XY)，(Z?XY)?(Z?XY)，XY与(Z?XY)不相交，并且XY→Z，A8表明X→(Z?Y)成立。(4)多值依赖的分解规则：如果X→→Y，X→→Z成立，则X→→(Y?Z)，X→→(Y?Z)，X→→(Z?Y)成立。证明：由(Y?Z)?Y得到Y→(Y?Z)。再利用A7，得到Y→→(Y?Z)。由X→→Y和Y→→(Y?Z)，利用多值依赖的传递率得到X→→((Y?Z)?Z)。此即X→→(Y?Z)。类似地，可以证明X→→(Z?Y)。由X→→Y和Y→→(Y?Z（上面已证）)，利用多值依赖的传递率，我们有X→→(Y?(Y?Z))。而Y?(Y?Z)=Y?Z，因而有X→→(Y?Z)。6.21只考虑函数依赖，?={CHR,CT,CSG}不是无损连接分解。这可以用算法6.3检测。初始表如（a）所示。CTHRSGCTHRSGa1a1a1b12a2b32a3b23b33(a)a4b24b34b15b25a5b16b26a6a1a1a1a2a2a2a3b23b33(b)a4b24b34b15b25a5b16b26a6C?T将b12和b32改为a2，结果如（b）所示。任何函数依赖都不能再改变该表，算法结束。由于不存在一行为a1a2a3a4a5a6，因此?={CHR,CT,CSG}不是无损连接分解。6.22通过模式分解产生关系模式（仅考虑函数依赖）的三个目标是：无损连接分解、BCNF和保持函数依赖的分解。分解的无损连接性是对分解的基本要求，是必须保证的。不具有无损连接性的分解是有害的。这是因为关系模式R的分解?={R1,R2,…,Rk}将原关系模式下的关系r分解成k个关系r1,r2,…,rk。除了自然连接之外，没有其他一般性方法从这些关系得到原来的关系。因此，对r和r1,r2,…,rk进行相同的查询可能得到不同的结果。这样，我们就不能认为R和{R1,R2,…,Rk}反映相同的现实世界。分解的目的是减少冗余，从而消除存储异常。仅考虑函数依赖，不达到BCNF不能完全避免冗余，因此分解后的关系模式，如果可能的话，应该达到BCNF。函数依赖反映了数据之间的一种约束。如果分解保持函数依赖，这些约束可以比较有效地在分解后的一个关系中进行验证，否则有些约束需要将多个关系连接才能验证。因此，分解要尽可能保持函数依赖。在某些情况下，BCNF和保持函数依赖这两个目标不可兼得，因为有些关系模式不存在保持函数依赖的BCNF分解。在这种情况下，我们必须在BCNF和保持函数依赖之间进行取舍。6.23在关系数据库设计时，我们可能选择非BCNF设计的原因是有些关系模式不存在保持函数依赖的BCNF分解。当函数依赖是重要的时，为了使的函数依赖的验证更加有效，我们需要选择保持函数依赖的分解，而放弃BCNF.6.24在关系数据库设计时，没有理由设计一个属于2NF，但不属于更高范式的关系模式。因为关系模式达到2NF，但不属于更高的范式将存在明显的数据冗余，进而导致存储异常。另一方面，对于任意2NF，我们总可以通过模式分解将它转换成更高的关系模式，这些关系模式与原来的关系模式反映相同的现实世界（分解具有无损连接性），并且能够消除或减少数据冗余。第7章数据库设计部分习题参考答案7.17.27.37.47.57.67.77.87.97.107.117.127.137.147.157.16数据库设计人员应具备哪些方面的知识？简述数据库设计的步骤。什么是数据库设计？简述需求分析的步骤。简述数据字典的内容及其作用。概念数据库设计使用那些策略？简述概念结构设计的基本方法和步骤。什么是数据库的逻辑设计？简述其步骤。在合并局部E-R图中，如何消除各种冲突？E-R图向关系模型转换的原则是什么？简述物理数据库设计的任务、目标和步骤。在数据库的物理设计中如何选择索引方法？试述聚簇设计的原则。为什么要对数据库进行重组和重构？DBA的作用是什么？考察自己学校的学生成绩管理方法，编写出建立成绩管理的可行性分析、需求分析说明书、系统的概念结构设计和逻辑结构设计。7.17一个图书借阅管理数据库有以下需求，请设计该数据库的E－R模式和关系模式。(1)希望能查询书库中现有书籍的种类、数量和存放位置。这里假设各种书籍均由书号惟一标识。(2)希望能查询书籍的借还情况信息，包括借书人单位、借书人单位电话、姓名、借书证号、借书日期、还书日期。这里规定，每个人最多可以借6本书，借书证是读者的惟一标识符。(3)希望通过查询出版社的名称、电话、邮编、通讯地址等信息能向出版社订购有关书籍。这里假设一个出版社可以出版多种图书，同一书名的书仅由一个出版社出版，出版社名称具有惟一性。第8章查询处理与优化部分习题参考答案8.1对于相同的查询，不同的查询执行的时间开销可能相差几个数量级。这意味好的执行计划可能是可行的，而差的执行计划在实践上是不可行。因此，即使查询只执行一次，查询优化也是必须的。在非关系系统中，用户使用过程化的语言表达查询要求，执行何种操作，以及操作的序列都是由用户来决定的。系统为用户提供选择存取路径的手段，而用户必须了解存取路径，为自己的查询选择合适的存取路径。如果用户做了不当的选择,系统很难加以改进。关系数据库语言（如SQL）是非过程化语言；为了表达查询，用户只需要说明做什么（查询什么、在什么关系上查询和查询结果满足的条件），而不必说明怎么做。这就为查询优化提供了更大的空间，使得系统可以分析查询语义，选择最佳的查询处理。8.2系统进行查询优化的优点是：系统进行查询优化减轻了用户选择存取路径的负担，使得用户可以将注意力放在如何正确地表达查询请求上，而不必考虑如何最有效地表达查询。此外，系统进行查询优化还可以比用户做得更好，因为(1)优化器可以从数据字典中获取许多统计信息，如每个关系的当前元组数目、每个属性上的不同值个数、有哪些索引等。这些信息对于选择高效的执行策略是重要的，但是用户难以获得这些信息。(2)当数据库的统计信息改变时，可能需要改变执行策略。优化器可以自动对查询重新进行优化，以选择相适应的执行策略。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。(3)优化器可以更全面地考虑，考察数百种不同的执行计划，从中确定最佳的执行计划。而用户一般只能考虑有限的几种可能性（很难超过三、四种）。(4)优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。8.3代数优化的启发式规则包括：选择尽可能先做、投影机可能先做和尽量避免计算笛卡尔积等。选择尽可能先做是最重要的启发式规则。这是因为尽管通常一个关系很大，但是满足某种选择条件的元组的数量却相对较小，并且选择条件越强，满足条件的元组越少。尽早进行选择能够大幅度减少下一步参与运算的元组数目，使得其后的运算可以更加有效的进行。8.4由于索引是非聚簇索引，如果对于连接属性的相同值，内层关系存在多个元组，这些元组可能散布在多个物理块，则对于外层关系的每个元组，我们可能需要访问内层关系的多个块。因此，索引嵌套循环的效率不高。8.5如果关系r和s在公共属性上无序，也没有索引。如果内存足够大，就I/O开销而言，计算rs的最有效方法是：将较小的关系整个读入内存，逐块读入较大的关系，使用较大的关系作为外层关系，执行嵌套循环连接。此时，I/O操作数为br+bs，而内存需求为min(br,bs)+2页，其中br和bs分别为关系r和s所占用的块数。8.6假设关系r和s的公共属性JoinAttrs是r的主码、s的外码，并且r和s在连接属性上是有序的（假设为递增序）。用排序-归并方法计算r指针pr和ps分别指向r和s的第一个元组；while(pr?nullandps?null)do{tr?pr指向的元组；ts?ps指向的元组；s的伪代码如下：while(ps?nullandts[JoinAttrs]&tr[JoinAttrs])do让ps指向s的下一个元组；while(ps?nullandts[JoinAttrs]=tr[JoinAttrs])do{把tr.ts添加到结果中；让ps指向s的下一个元组；}if(pr?nulll)then让pr指向r的下一个元组；}8.7可以采用排序或散列方法来消除重复元组。排序时等值元组相互邻近，删除其他副本，只留一个元组副本即可。如果使用外部归并排序，则可以直接在归并时删除重复元组。散列时，重复元组会分到同一桶中。可以在元组加入桶中时检查元组是否已在桶中，无须加入已在桶中的元组。8.8(1)使用散列方法实现集合运算r?s的算法初始：结果关系为空；使用相同的散列函数H对两个关系r和s进行划分，产生r0,r1,...,rn和s0,s1,...,for(i=1;i&=n;i++)do{对ri建立内存散列索引；for(si中的每个元组ts)doif(ts不在ri的散列索引中)then将ts加入到ri的散列索引中；将ri的内存散列索引中的元组加入结果关系中；}设关系r和s分别占br和bs块。使用散列函数H对两个关系r和s进行划分，需要读入和写出br+bs块。读入每个ri建立它的内存散列索引，并将si的不在ri的散列索引中的元组插入需要读入br+bs块。整个算法需要的I/O量为3(br+bs)块。如果内存足够大，可以将关系r和s读入到内存，I/O减少到br+bs块。(2)使用散列方法实现集合运算r?s的算法初始：结果关系为空；使用相同的散列函数H对两个关系r和s进行划分，产生r0,r1,...,rn和s0,s1,...,for(i=1;i&=n;i++)do{对ri建立内存散列索引；for(si中的每个元组ts)doif(ts在ri的散列索引中)then将ts加入到结果关系中；}使用散列方法实现集合运算r?s的算法初始：结果关系为空；使用相同的散列函数H对两个关系r和s进行划分，产生r0,r1,...,rn和s0,s1,...,for(i=1;i&=n;i++)do{对ri建立内存散列索引；for(si中的每个元组ts)doif(ts在ri的散列索引中)then将ts从ri的散列索引中删除；将ri的内存散列索引中的元组加入结果关系中；}8.9(1)图8.2的语法树见（a），分解合取选择后的语法树在（b）中。选择条件Pno=‘P001’只涉及关系SP，因此可以将对应的选择下移，结果见（c）。将选择与其下的笛卡尔积转换成自然连接，结果见（d）。引入新的投影，去掉其后运算不需要的属性：关系Suppliers的属性Sno和Sname是其后需要的，其余属性不需要。使用条件Pno=‘P001’选择后，关系SP的属性Sno是需要的，其余属性不需要。引入投影后的结果在（e）中。?Sname?Suppliers.Sno=SP.Sno?Pno=‘P001’?SuppliersSP?Sname?Suppliers.Sno=SP.Sno?Pno=‘P001’?SuppliersSP?Sname?Suppliers.Sno=SP.Sno?Suppliers?Pno=‘P001’SP(a)(b)(c)?Sname?Sname?Sname流水线Suppliers?Pno=‘P001’SP?Sno,Sname?SnoSuppliers?Sno,Sname?Sno流水线Suppliers?Pno=‘P001’SP?Pno=‘P001’SP(d)(e)图8.1习题8.9的初始语法树及其优化(f)不假定自然连接可以有效计算。自然连接上方投影与该自然连接形成一个流水线，SP上方两个相继的一目运算形成一个流水线，得到代数优化结果在（f）中。(2)假设数据库中有100个Suppliers记录，10000个SP记录，其中涉及产品P001的发货记录为50个。不对Suppliers和SP做任何有序或存在索引的假定。将Suppliers投影到Sno和Sname上可以用线性扫描实现。由于结果元组只有100个，每个只有两个属性，结果可以放在内存。从SP选择满足条件Pno=‘P001’的可以用线性扫描实现。由于只有50个记录，投影到Sno上之后可以放在内存。自然连接可以用简单的内存排序连接（排序-归并连接的一种简单情况）实现。结果查询计划在（g）中。?Sname流水线（内存排序连接）（线性扫描）?Sno,Sname?Sno流水线Suppliers?Pno=‘P001’SP（线性扫描）(g)图8.1（续）习题8.9的初始语法树及其优化8.10查询的初始语法树在（a）中，其中关系Accounts、Branches和Customers分别简写为A、B和C（下同）。?Cname??A.Ano=C.Ano?Cname?B.Bname=A.Bname??City=‘郑州市’?Balance&10000??BranchesCustomersAccountsBranchesAccounts?City=‘郑州市’?Balance&10000??Customers(a)(b)图8.2习题8.10的初始语法树及其优化分解合取选择后的语法树在（b）中。选择条件City=‘郑州市’只涉及关系Branches，Balance&10000只涉及关系Accounts，可以分别下移到对应关系的上方；选择条件B.Bname=A.Bname只涉及关系Branches和Accounts，可以下移到对应关系的笛卡尔积的上方。结果在（c）中。选择条件A.Ano=C.Ano和B.Bname=A.Bname可以分别与其下的笛卡尔积结合成自然连接，结果在（d）中。引进新的投影，去掉其后运算不再需要的属性，结果在（e）中。建立流水线：相继的一目运算形成流水线，上面自然连接上方的投影和Customers上方的投影形成流水线。不考虑诸关系的序和索引的话，不能再建立其他流水线。结果在（f）中。?Cname?A.Ano=C.Ano??CnameCustomersCustomers?B.Bname=A.Bname??City=‘郑州市’?Balance&10000BranchesAccounts?City=‘郑州市’?Balance&10000BranchesAccounts(c)(d)?Cname?Cname流水线流水线?Ano,Cname?Bname?Ano,BnameCustomers?Ano,Cname?Bname?Ano,Bname流水线Customers流水线?City=‘郑州市’?Balance&10000BranchesAccounts?City=‘郑州市’?Balance&10000BranchesAccounts(e)(f)图8.2（续）习题8.10的初始语法树及其优化8.11例8.6假设：关系数目分别为nC=400，nS=20000，nSC=100000；所占的物理块数分别为bC=8，bS=500，bSC=500；这些关系在部分属性上的不同值包括：V(C,Cno)=nC=400，V(S,Sno)=nS=20000，V(S,Dno)=20，V(SC,Sno)=nS=20000，V(SC,Cno)=nC=400；Students在Dno上有聚簇索引，SC在Sno和Cno上分别有非聚簇索引，并在(Sno,Cno)上有组合索引此外，假设内存可以存放20个物理块（每块2K字节）。由于V(S,Dno)=20，Students在Dno上的聚簇索引只占1块。由bs=?ns/fs?得到500=?20000/fs?，从而fs=40。使用聚簇索引、等值选择计算?S.Dno=’IE’(Students)，结果元组大约1000个，大约在个物理块中，加上读索引1块I/O，共需26块I/O。结果元组直接投影到Sno上，结果5块放在内存。这样，计算?S.Sno(?S.Dno=’IE’(Students))需要26块I/O。使用块嵌套循环计算E2=?SC.Sno,SC.Cno(SC)?S.Sno(?S.Dno=’IE’(Students))，?SC.Sno,SC.Cno(SC)为内层关系。?S.Sno(?S.Dno=’IE’(Students))已经在内存，读入SC的元组时可以直接投影到Sno和Cno上，需要读入SC的所有元组，共500块。自然连接结果大约需要72块，需要作为临时关系保存。这样，计算E2的I/O开销为572块。使用块嵌套循环计算自然连接?C.Cno,C.Cname(Courses)E2，?C.Cno,C.Cname(Courses)为外层关系。由于Courses只有8块，可以一次读入内存，读入时直接投影得到?C.Cno,C.Cname(Courses)。计算自然连接时只需要读入E2的72块，总I/O开销为80块。最终的计算结果“信息工程学院学生选修的所有课程的课程号和课程名”可以在产生元组时直接插入排序放删除重复，因此不需要附加的I/O。整个查询计划的I/O开销为：26+572+80=678（块）第9章事务与并发控制部分习题参考答案9.1数据库中要有并发机制的主要有如下两条理由：(1)提高吞吐量和资源的利用率；(2)减少平均等待时间和平均响应时间。并发控制技术能够保证事务的ACID性质，主要是保证事务的隔离性和数据库的一致性。9.2事务并发执行可能导致丢失修改、读“脏”数据和不可重复读等问题。数据库系统这些问题的主要方法是采用封锁技术。一级锁协议可以保证不丢失修改，二级封锁协议可以避免读“脏”数据，而三级封锁协议可以保证可重复读。9.3封锁就是事务T在对某个数据对象操作之前，先向系统发出加锁请求，加锁后事务T就对该数据对象有了一定的控制权，在事务T释放它的锁之前，其它事务不能更新该数据对象。共享锁和排它锁是两种基本锁类型。共享锁又称读锁。如果事务T获得了数据对象Q上的共享锁，则T可以读但不能写Q，并且在T释放Q上的S锁之前，其它事务只能获得Q上的S锁，而不能获得Q上的X锁。排他锁又称写锁。如果事务T获得了数据项Q上的排它锁，则T既可以读又可以写Q，但是在T释放Q上的X锁之前，其它事务既不能获得Q上的S锁，也不能获得Q上的X锁。9.4如何用封锁机制保证数据的一致性？9.5活锁又称饥饿，是某个事务因等待锁而处于无限期等待状态。活锁是不公平的锁调度导致的。可以采用先来先服务的策略来避免某个事务无限期期等待。即当多个事务请求封锁同一数据对象时，封锁子系统按请求封锁的先后次序对事务排队。数据对象上的锁一旦释放，就将锁授予申请队列中的第一个事务。9.6死锁是两个或两个以上的事务之间的循环等待现象。死锁发生时，两个或多个事务都处于等待状态，每个事务都等待其它事务释放锁，以便可以继续执行。预防死锁的基本方法是破坏死锁产生的条件，可以采用一次封锁、顺序封锁方法。9.7请给出检验死锁发生的一种方法，当发生死锁后如何解除死锁？9.8什么样的并发调度是正确的调度？9.9设T1、T2、T3是如下的3个事务，设A的初值为0：T1：A:=A+2T2：A:=A*2T3：A:=A**2；（A←A2）(1)若这3个事务允许并发执行，则有多少种可能的正确结果，请一一列出；(2)(3)(4)(5)请给出一个可串行化的调度，并给出执行结果；请给出一个非串行化调度，并给出执行结果；若这3个事务都遵守两段锁协议，请给出一个不产生死锁的可串行化调度；若这3个事务都遵守两段锁协议，请给出一个产生死锁的调度。9.10为什么要求并发事务}