数据库复习 第五章习题
数据库系统概论第一章第五章部分习题答案
第一章11 ．试给出一个实际部门的 E 一R 图，要求有三个实体型，而且 3 个实体型之间有多对多联系。
3 个实体型之间的多对多联系和三个实体型两两之间的三个多对多联系等价吗？为什么？答：3 个实体型之间的多对多联系和 3 个实体型两两之间的 3 个多对多联系是不等价，因为它们拥有不同的语义。
3 个实体型两两之间的三个多对多联系如下图所示。
12 ．学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。
请用 E 一R 图画出此学校的概念模型。
答：13 ．某工厂生产若干产品，每种产品由不同的零件组成，有的零件可用在不同的产品上。
这些零件由不同的原材料制成，不同零件所用的材料可以相同。
这些零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中。
请用 E 一R 图画出此工厂产品、零件、材料、仓库的概念模型。
答：第五章6．假设有下面两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码。
用sQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：定义每个模式的主码；定义参照完整性；定义职工年龄不得超过60 岁。
答CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC PRIMARY KEY(Deptno));CREATE TABLE EMP(Empno NUMBER(4) PRIMARY KEY,Ename VARCHAR(10),Age NUMBER(2),CONSTRAINT C1 CHECK ( Aage<=60),Job VARCHAR(9),Sal NUMBER(7,2),Deptno NUMBER(2),CONSTRAINT FK_DEPTNOFOREIGN KEY(Deptno)REFFERENCES DEPT(Deptno));。
数据库系统概论习题及答案 填空题
数据库系统概论复习资料：第一章：一选择题：1．在数据管理技术的发展过程中，经历了人工管理阶段、文件系统阶段和数据库系统阶段。
在这几个阶段中，数据独立性最高的是阶段。
A．数据库系统 B．文件系统 C．人工管理 D．数据项管理答案：A2．数据库的概念模型独立于。
A．具体的机器和DBMS B．E-R图 C．信息世界 D．现实世界答案：A3．数据库的基本特点是。
A．(1)数据可以共享(或数据结构化) (2)数据独立性 (3)数据冗余大，易移植 (4)统一管理和控制B．(1)数据可以共享(或数据结构化) (2)数据独立性 (3)数据冗余小，易扩充 (4)统一管理和控制C．(1)数据可以共享(或数据结构化) (2)数据互换性 (3)数据冗余小，易扩充 (4)统一管理和控制D．(1)数据非结构化 (2)数据独立性 (3)数据冗余小，易扩充 (4)统一管理和控制答案：B4. 是存储在计算机内有结构的数据的集合。
A．数据库系统 B．数据库 C．数据库管理系统 D．数据结构答案：B5．数据库中存储的是。
A．数据 B．数据模型 C．数据以及数据之间的联系 D．信息答案：C6. 数据库中，数据的物理独立性是指。
A．数据库与数据库管理系统的相互独立 B．用户程序与DBMS的相互独立C．用户的应用程序与存储在磁盘上数据库中的数据是相互独立的 D．应用程序与数据库中数据的逻辑结构相互独立答案：C7. ．数据库的特点之一是数据的共享，严格地讲，这里的数据共享是指。
A．同一个应用中的多个程序共享一个数据集合 B．多个用户、同一种语言共享数据C．多个用户共享一个数据文件D．多种应用、多种语言、多个用户相互覆盖地使用数据集合答案：D8.数据库系统的核心是。
A．数据库B．数据库管理系统C．数据模型D．软件工具答案：B9. 下述关于数据库系统的正确叙述是。
A．数据库系统减少了数据冗余 B．数据库系统避免了一切冗余 C．数据库系统中数据的一致性是指数据类型一致D．数据库系统比文件系统能管理更多的数据答案：A10.将数据库的结构划分成多个层次，是为了提高数据库的①和②。
(完整版)数据库课后习题及答案
第一章数据库系统概述选择题1实体-联系模型中，属性是指（C）A.客观存在的事物B.事物的具体描述C.事物的某一特征D.某一具体事件2对于现实世界中事物的特征，在E-R模型中使用（A）A属性描述B关键字描述C二维表格描述D实体描述3假设一个书店用这样一组属性描述图书（书号，书名，作者，出版社，出版日期），可以作为“键”的属性是（A）A书号B书名C作者D出版社4一名作家与他所出版过的书籍之间的联系类型是（B）A一对一B一对多C多对多D都不是5若无法确定哪个属性为某实体的键，则（A）A该实体没有键B必须增加一个属性作为该实体的键C取一个外关键字作为实体的键D该实体的所有属性构成键填空题1对于现实世界中事物的特征在E-R模型中使用属性进行描述2确定属性的两条基本原则是不可分和无关联3在描述实体集的所有属性中，可以唯一的标识每个实体的属性称为键4实体集之间联系的三种类型分别是1：1 、1：n 、和m：n5数据的完整性是指数据的正确性、有效性、相容性、和一致性简答题一、简述数据库的设计步骤答:1需求分析：对需要使用数据库系统来进行管理的现实世界中对象的业务流程、业务规则和所涉及的数据进行调查、分析和研究，充分理解现实世界中的实际问题和需求。
分析的策略：自下而上——静态需求、自上而下——动态需求2数据库概念设计：数据库概念设计是在需求分析的基础上，建立概念数据模型，用概念模型描述实际问题所涉及的数据及数据之间的联系。
3数据库逻辑设计：数据库逻辑设计是根据概念数据模型建立逻辑数据模型，逻辑数据模型是一种面向数据库系统的数据模型。
4数据库实现：依据关系模型，在数据库管理系统环境中建立数据库。
二、数据库的功能答：1提供数据定义语言，允许使用者建立新的数据库并建立数据的逻辑结构2提供数据查询语言3提供数据操纵语言4支持大量数据存储5控制并发访问三、数据库的特点答：1数据结构化。
2数据高度共享、低冗余度、易扩充3数据独立4数据由数据库管理系统统一管理和控制：（1）数据安全性（2）数据完整性（3）并发控制（4）数据库恢复第二章关系模型和关系数据库选择题1把E-R模型转换为关系模型时，A实体（“一”方）和B实体（“多”方）之间一对多联系在关系模型中是通过（A）来实现的A将A关系的关键字放入B关系中B建立新的关键字C建立新的联系D建立新的实体2关系S和关系R集合运算的结果中既包含S中元组也包含R中元组，但不包含重复元组，这种集合运算称为（A）A并运算B交运算C差运算D积运算3设有关系R1和R2，经过关系运算得到结果S，则S是一个（D）A字段B记录C数据库D关系4关系数据操作的基础是关系代数。
数据库应用答案第五章
⑶ 请 用 BETWEEN … … AND 形 式 改 写 条 件 子 句 WHRER mark>=560 AND mark<=600。
答： WHERE mark BETWEEN 560 AND 600
⑷ 什么聚合函数能对数值类型的列值进行求和？什么聚合函数能用来确定一个表中 包含多少行？在一个包含聚合函数的 SELECT 语句中，GROUP BY 子句有那些用途？
⑺ 首先显示“计算机工程系”、“计算机网络技术专业”、02 班全体学生的基本信息， 然后再统计“计算机工程系”、“计算机网络技术专业”、02 班的学生人数。
SELECT * FROM stud_info WHERE substring(stud_id,3,6)='010202' GO SELECT substring(stud_id,3,6) 专业编号, count(substring(stud_id,3,6)) 人数 FROM stud_info WHERE substring(stud_id,3,6)='010202' GROUP BY substring(stud_id,3,6)
C. 第 3 行
D. 第 4 行
⑹ 在一个查询中，哪一个子句将限制返回的行？[ B ]
A. ORDER BY B. WHERE
C. SELECT
D. FROM
⑺ 哪一个运算符可以替代 WHERE 子句中的 OR 运算符？[ A ]
A. IN
B. >=
C. LIKE
⑻ 下列 SQL 语句中哪一行有错？[ C ]
A. SUM
B. AVG
C. GREATEST
信息技术第5章 数据管理与应用练习题库(附答案)
第五章数据管理与应用一、单项选择题1.下列软件哪个不是数据库管理系统（）。
A.ExcelB.AccessC.OracleD.SQL Server参考答案:A试题解析:Excel是office的电子表格，常说的Oracle、Access、SQL Server等数据库，其实准确地说就是数据库管理系统。
知识点：信息技术类--专业知识与技能--第五章数据管理与应用--第二节结构化查询语言SQL2.SQL的视图是从（）中导出的。
A.基本表B.视图C.基本表或视图D.数据库参考答案:C试题解析:视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。
它是用户查看数据库表中数据的一种方式，是基于某个查询结果的虚拟表，用户通过它来浏览表中感兴趣的部分或全部数据，而数据的物理存放位置仍然在表中，这些表称作视图的基表。
数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。
视图就如一个用于查看数据的窗口。
知识点：信息技术类--专业知识与技能--第五章数据管理与应用--第二节结构化查询语言SQL3.SELECT语句要把重复记录屏蔽使用关键字（）A.DISTINCTB.UNIONC.ALLD.GROUP参考答案:A试题解析:distinct这个关键字来过滤掉多余的重复记录只保留一条。
知识点：信息技术类--专业知识与技能--第五章数据管理与应用--第二节结构化查询语言SQL4.如果需要计算部门中所有雇员的薪金总额，应使用以下哪个组函数（）A.MAXB.SUMC.VARIANCED.COUNT参考答案:B试题解析:sum（）函数用于计算数值列的合计数。
知识点：信息技术类--专业知识与技能--第五章数据管理与应用--第二节结构化查询语言SQL5.在数据库查询语句中，判断字段为空的关键字是（）。
A.IS NULLB.=NULLC.IS IND.IN参考答案:A试题解析:当字符串为空时，在where 子句中使用 is null 来判断。
数据库技术复习—ACCESS习题及答案汇总
第1章认识Access2003一、选择题1.Access2003是一种〔〕。
A. 数据库B. 数据库系统C. 数据库管理软件D. 数据库管理员答案：C2.菜单命令名称的右侧带有三角符号表示〔〕A. 该命令已经被设置为工具栏中的按钮B. 将光标指向该命令时将翻开相应的子菜单C. 当前状态下该命令无效D. 执行该命令后会出现对话框答案：B3.Access数据库的对象包括〔〕A. 要处理的数据B. 主要的操作容C. 要处理的数据和主要的操作容D. 仅为数据表答案：C4.Access2003数据库7个对象中，〔〕是实际存放数据的地方。
A. 表B. 查询C. 报表D.窗体答案：A5.Access2003数据库中的表是一个〔〕。
A. 穿插表B. 线型表C. 报表D.二维表答案：D6.在一个数据库中存储着假设干个表，这些表之间可以通过〔〕建立关系。
A. 容不一样的字段B. 一样容的字段C. 第一个字段D. 最后一个字段答案：B7.Access2003中的窗体是〔〕之间的主要接口。
A. 数据库和用户B. 操作系统和数据库C. 用户和操作系统D. 人和计算机答案：A二、填空题1.Access2003是中的一个组件，它能够帮助我们。
答案：Office2003办公软件，数据库管理2.Access2003的用户界面由、、、、和组成。
答案：标题栏，菜单栏，工具栏，工作区，状态栏，任务窗格3.Access2003数据库中的表以行和列来组织数据，每一行称为，每一列称为。
答案：一条记录，一个字段4.Access2003数据库中表之间的关系有、和关系。
答案：一对一，一对多，多对多5.查询可以按照不同的方式、和数据，查询也可以作为数据库中其他对象的。
答案：查看，分析，更改数据，数据来源6.报表是把数据库中的数据的特有形式。
答案：打印输出7. 数据访问页可以将数据库中的数据发布到上去。
答案：Internet三、判断题1．数据就是能够进展运算的数字。
(完整版)数据库系统基础教程第五章答案
Exercise 5.1.1 As a set:speed2.662.101.422.803.202.202.001.863.06 Average = 2.37 As a bag:speed2.662.101.422.803.203.202.202.202.002.801.862.803.06 Average = 2.48 Exercise 5.1.2 As a set:hd25080320200300160 Average = 218 As a bag:hd2502508025025032020025025030016016080 Average = 215 Exercise 5.1.3a As a set:bore15161418As a bag:bore1516141615151418Exercise 5.1.3bπbore(Ships Classes)Exercise 5.1.4aFor bags:On the left-hand side:Given bags R and S where a tuple t appears n and m times respectively, the union of bags R and S will have tuple t appear n + m times. The further union of bag T with the tuple t appearing o times will have tuple t appear n + m + o times in the final result.On the right-hand side:Given bags S and T where a tuple t appears m and o times respectively, the union of bags R and S will have tuple t appear m + o times. The further union of bag R with the tuple t appearing n times will have tuple t appear m + o + n times in the final result.For sets:This is a similar case when dealing with bags except the tuple t can only appear at most once in each set. The tuple t only appears in the result if all the sets have the tuple t. Otherwise, the tuple t will not appear in the result. Since we cannot have duplicates, the result only has at most one copy of the tuple t.Exercise 5.1.4bFor bags:On the left-hand side:Given bags R and S where a tuple t appears n and m times respectively, the intersectionof bags R and S will have tuple t appear min( n, m ) times. The further intersection of bag T with the tuple t appearing o times will produce tuple t min( o, min( n, m ) ) times in the final result.On the right-hand side:Given bags S and T where a tuple t appears m and o times respectively, the intersection of bags R and S will have tuple t appear min( m, o ) times. The further intersection of bag R with the tuple t appearing n times will produce tuple t min( n, min( m, o ) ) times in thefinal result.The intersection of bags R,S and T will yield a result where tuple t appears min( n,m,o ) times. For sets:This is a similar case when dealing with bags except the tuple t can only appear at most once in each set. The tuple t only appears in the result if all the sets have the tuple t. Otherwise, the tuple t will not appear in the result.Exercise 5.1.4cFor bags:On the left-hand side:Given that tuple r in R, which appears m times, can successfully join with tuple s in S,which appears n times, we expect the result to contain mn copies. Also given that tuple tin T, which appears o times, can successfully join with the joined tuples of r and s, weexpect the final result to have mno copies.On the right-hand side:Given that tuple s in S, which appears n times, can successfully join with tuple t in T,which appears o times, we expect the result to contain no copies. Also given that tuple rin R, which appears m times, can successfully join with the joined tuples of s and t, weexpect the final result to have nom copies.The order in which we perform the natural join does not matter for bags.For sets:This is a similar case when dealing with bags except the joined tuples can only appear at most once in each result. If there are tuples r,s,t in relations R,S,T that can successfully join, then the result will contain a tuple with the schema of their joined attributes.Exercise 5.1.4dFor bags:Suppose a tuple t occurs n and m times in bags R and S respectively. In the union of these two bags R ⋃ S, tuple t would appear n + m times. Likewise, in the union of these two bags S ⋃ R, tuple t would appear m + n times. Both sides of the relation yield the same result.For sets:A tuple t can only appear at most one time. Tuple t might appear each in sets R and S one or zero times. The combinations of number of occurrences for tuple t in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t appears in both sets R and S will the union R ⋃ S have the tuple t. The same reasoning holds when we take the union S ⋃ R.Therefore the commutative law for union holds.Exercise 5.1.4eFor bags:Suppose a tuple t occurs n and m times in bags R and S respectively. In the intersection of these two bags R ∩ S, tuple t would appear min( n,m ) times. Likewise in the intersection of these two bags S ∩ R, tuple t would appear min( m,n ) times. Both sides of the relation yield the same result.For sets:A tuple t can only appear at most one time. Tuple t might appear each in sets R and S one or zero times. The combinations of number of occurrences for tuple t in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t appears in at least one of the sets R and S will the intersection R ∩ S have the tuple t. The same reasoning holds when we take the intersection S ∩R.Therefore the commutative law for intersection holds.Exercise 5.1.4fFor bags:Suppose a tuple t occurs n times in bag R and tuple u occurs m times in bag S. Suppose also that the two tuples t,u can successfully join. Then in the natural join of these two bags R S, the joined tuple would appear nm times. Likewise in the natural join of these two bags S R, the joined tuple would appear mn times. Both sides of the relation yield the same result.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuples u,v might appear respectively in sets R and S one or zero times. The combinations of number of occurrences for tuples u,v in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple u exists in Rand tuple v exists in S will the natural join R S have the joined tuple. The same reasoning holds when we take the natural join S R.Therefore the commutative law for natural join holds.Exercise 5.1.4gFor bags:Suppose tuple t appears m times in R and n times in S. If we take the union of R and S first, we will get a relation where tuple t appears m + n times. Taking the projection of a list of attributes L will yield a resulting relation where the projected attributes from tuple t appear m + n times. If we take the projection of the attributes in list L first, then the projected attributes from tuple t would appear m times from R and n times from S. The union of these resulting relations would have the projected attributes of tuple t appear m + n times.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuple t might appear in sets R and S one or zero times. The combinations of number of occurrences for tuple t in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t exists in R or S (or both R and S) will the projected attributes of tuple t appear in the result.Therefore the law holds.Exercise 5.1.4hFor bags:Suppose tuple t appears u times in R, v times in S and w times in T. On the left hand side, the intersection of S and T would produce a result where tuple t would appear min(v , w) times. With the addition of the union of R, the overall result would have u + min(v , w) copies of tuple t. On the right hand side, we would get a result of min(u + v, u + w) copies of tuple t. The expressions on both the left and right sides are equivalent.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuple t might appear in sets R,S and T one or zero times. The combinations of number of occurrences for tuple t in R, S and T respectively are (0,0,0), (0,0,1), (0,1,0), (0,1,1), (1,0,0), (1,0,1), (1,1,0) and (1,1,1). Only when tuple t appears in R or in both S and T will the result have tuple t.Therefore the distributive law of union over intersection holds.Exercise 5.1.4iSuppose that in relation R, u tuples satisfy condition C and v tuples satisfy condition D. Suppose also that w tuples satisfy both conditions C and D where w≤ min(v , w). Then the left hand side will return those w tuples. On the right hand side, σC(R) produces u tuples and σD(R) produces v tuples. However, we know the intersection will produce the same w tuples in the result.When considering bags and sets, the only difference is bags allow duplicate tuples while sets only allow one copy of the tuple. The example above applies to both cases.Therefore the law holds.Exercise 5.1.5aFor sets, an arbitrary tuple t appears on the left hand side if it appears in both R,S and not in T. The same is true for the right hand side.As an example for bags, suppose that tuple t appears one time each in both R,T and two times in S. The result of the left hand side would have zero copies of tuple t while the right hand side would have one copy of tuple t.Therefore the law holds for sets but not for bags.Exercise 5.1.5bFor sets, an arbitrary tuple t appears on the left hand side if it appears in R and either S or T. This is equivalent to saying tuple t only appears when it is in at least R and S or in R and T. The equivalence is exactly the right side’s expression.As an example for bags, suppose that tuple t appears one time in R and two times each in S and T. Then the left hand side would have one copy of tuple t in the result while the right hand side would have two copies of tuple t.Therefore the law holds for sets but not for bags.Exercise 5.1.5cFor sets, an arbitrary tuple t appears on the left hand side if it satisfies condition C, condition D or both condition C and D. On the right hand side, σC(R) selects those tuples that satisfy condition C while σD(R) selects those tuples that satisfy condition D. However, the union operator will eliminate duplicate tuples, namely those tuples that satisfy both condition C and D. Thus we are ensured that both sides are equivalent.As an example for bags, we only need to look at the union operator. If there are indeed tuples that satisfy both conditions C and D, then the right hand side will contain duplicate copies of those tuples. The left hand side, however, will only have one copy for each tuple of the original set of tuples.A+B A2B210154910164167916 Exercise 5.2.1bB+1C-1103334431143 Exercise 5.2.1cA B0101232434 Exercise 5.2.1dB C010224253434A B01232434 Exercise 5.2.1fB C0124253402 Exercise 5.2.1gA SUM(B)022734 Exercise 5.2.1hB AVG(C)0 1.52 4.534 Exercise 5.2.1iA23Exercise 5.2.1jA MAX(C)24 Exercise 5.2.1kA B C23423401┴01┴24┴34┴Exercise 5.2.1lA B C234234┴01┴24┴25┴02 Exercise 5.2.1mA B C23423401┴01┴24┴34┴┴01┴24┴25┴02Exercise 5.2.1nA R.B S.B C0124012501340134012401250134013423┴┴24┴┴34┴┴┴┴01┴┴02Exercise 5.2.2aApplying the δ operator on a relation with no duplicates will yield the same relation. Thus δ is idempotent.Exercise 5.2.2bThe result of πL is a relation over the list of attributes L. Performing the projection again will return the same relation because the relation only contains the list of attributes L. Thus πL is idempotent.Exercise 5.2.2cThe result of σC is a relation where condition C is satisfied by every tuple. Performing the selection again will return the same relation because the relation only contains tuples that satisfy the condition C. Thus σC is idempotent.Exercise 5.2.2dThe result of γL is a relation whose schema consists of the grouping attributes and the aggregated attributes. If we perform the same grouping operation, there is no guarantee that the expression would make sense. The grouping attributes will still appear in the new result. However, the aggregated attributes may or may not appear correctly. If the aggregated attribute is given a different name than the original attribute, then performing γL would not make sense because it contains an aggregation for an attribute name that does not exist. In this case, the resultingrelation would, according to the definition, only contain the grouping attributes. Thus, γL is not idempotent.Exercise 5.2.2eThe result of τ is a sorted list of tuples based on some attributes L. If L is not the entire schema of relation R, then there are attributes that are not sorted on. If in relation R there are two tuples that agree in all attributes L and disagree in some of the remaining attributes not in L, then it is arbitrary as to which order these two tuples appear in the result. Thus, performing the operation τmultiple times can yield a different relation where these two tuples are swapped. Thus, τ is not idempotent.Exercise 5.2.3If we only consider sets, then it is possible. We can take πA(R) and do a product with itself. From this product, we take the tuples where the two columns are equal to each other.If we consider bags as well, then it is not possible. Take the case where we have the two tuples (1,0) and (1,0). We wish to produce a relation that contains tuples (1,1) and (1,1). If we use the classical operations of relational algebra, we can either get a result where there are no tuples or four copies of the tuple (1,1). It is not possible to get the desired relation because no operation can distinguish between the original tuples and the duplicated tuples. Thus it is not possible to get the relation with the two tuples (1,1) and (1,1).Exercise 5.3.1a)Answer(model) ← PC(model,speed,_,_,_) AND speed ≥ 3.00b)Answer(maker) ← Laptop(model,_,_,hd,_,_) AND Product(maker,model,_) AND hd ≥100c)Answer(model,price) ← PC(model,_,_,_,price) AND Product(maker,model,_) ANDmaker=’B’Answer(model,price) ← Laptop(model,_,_,_,_,price) AND Product(maker,model,_)AND maker=’B’Answer(model,price) ← Printer(model,_,_,price) AND Product(maker,model,_) ANDmaker=’B’d)Answer(model) ← Printer(model,color,type,_) AND color=’true’ AND type=’laser’e)PCMaker(maker) ← Product(maker,_,type) AND type=’pc’LaptopMaker(maker) ← Product(maker,_,type) AND type=’laptop’Answer(maker) ← LaptopMaker(maker) AND NOT PCMaker(maker)f)Answer(hd) ← PC(model1,_,_,hd,_) AND PC(model2,_,_,hd,_) AND model1 <>model2g)Answer(model1,model2) ← PC(model1,speed, ram,_,_) ANDPC(model2,_speed,ram,_,_) AND model1 < model2h)FastComputer(model) ← PC(model,speed,_,_,_) AND speed ≥ 2.80FastComputer(model) ← Laptop(model,speed,_,_,_,_) AND speed ≥ 2.80Answer(maker) ← Product(maker,model1,_) AND Product(maker,model2,_) ANDFastComputer(model1) AND FastComputer(model2) AND model1 <> model2i)Computers(model,speed) ← PC(model,speed,_,_,_)Computers(model,speed) ← Laptop(model,speed,_,_,_,_)SlowComputers(model) ← Computers(model,speed) AND Computers(model1,speed1) AND speed < speed1FastestComputers(model) ← Computers(model,_) AND NOT SlowComputers(model)Answer(maker) ← FastestComputers(model) AND Product(maker,model,_) j)PCs(maker,speed) ← PC(model,speed,_,_,_) AND Product(maker,model,_) Answer(maker) ← PCs(maker,speed) AND PCs(maker,speed1) ANDPCs(maker,speed2) AND speed <> speed1 AND speed <> speed2 AND speed1 <>speed2k)PCs(maker,model) ← Product(maker,model,type) AND type=’pc’Answer(maker) ← PCs(maker,model) AND PCs(maker,model1) ANDPCs(maker,model2) AND PCs(maker,model3) AND model <> model1 AND model <> model2 AND model1 <> model2 AND (model3 = model OR model3 = model1 ORmodel3 = model2)Exercise 5.3.2a)Answer(class,country) ← Classes(class,_,country,_,bore,_) AND bore ≥ 16b)Answer(name) ← Ships(name,_,launched) AND launched < 1921c)Answer(ship) ← Outcomes(ship,battle,result) AND battle=’Denmark Strait’ AND result= ‘sunk’d)Answer(name) ← Classes(class,_,_,_,_,displacement) AND Ships(name,class,launched)AND displacement > 35000 AND launched > 1921e)Answer(name,displacement,numGuns) ← Classes(class,_,_,numGuns,_,displacement)AND Ships(name,class,_) AND Outcomes (ship,battle,_) AND battle=’Guadalcanal’AND ship=namef)Answer(name) ← Ships(name,_,_)Answer(name) ← Outcomes(name,_,_) AND NOT Answer(name)g)MoreThanOne(class) ← Ships(name,class,_) AND Ships(name1,class,_) AND name <>name1Answer(class) ← Classes(class,_,_,_,_,_) AND NOT MoreThanOne(class)h)Battleship(country) ← Classes(_,type,country,_,_,_) AND type=’bb’Battlecruiser(country) ← Classes(_,type,country,_,_,_) AND type=’bc’Answer(country) ← Battleship(country) AND Battlecruiser(country)i)Results(ship,result,date) ← Battles(name,date) AND Outcomes(ship,battle,result) ANDbattle=nameAnswer(ship) ← Results(ship,result,date) AND Results(ship,_,date1) ANDresult=’damaged’ AND date < date1Exercise 5.3.3Answer(x,y) ← R(x,y) AND z = zExercise 5.4.1aAnswer(a,b,c) ← R(a,b,c)Answer(a,b,c) ← S(a,b,c)Exercise 5.4.1bAnswer(a,b,c) ← R(a,b,c) AND S(a,b,c)Exercise 5.4.1cAnswer(a,b,c) ← R(a,b,c) AND NOT S(a,b,c)Exercise 5.4.1dUnion(a,b,c) ← R(a,b,c)Union(a,b,c) ← S(a,b,c)Answer(a,b,c) ← Union(a,b,c) AND NOT T(a,b,c)Exercise 5.4.1eJ(a,b,c) ← R(a,b,c) AND NOT S(a,b,c)K(a,b,c) ← R(,a,b,c) AND NOT T(a,b,c)Answer(a,b,c) ← J(a,b,c) AND K(a,b,c)Exercise 5.4.1fAnswer(a,b) ← R(a,b,_)Exercise 5.4.1gJ(a,b) ← R(a,b,_)K(a,b) ← S(_,a,b)Answer(a,b) ← J(a,b) AND K(a,b)Exercise 5.4.2aAnswer(x,y,z) ← R(x,y,z) AND x = yExercise 5.4.2bAnswer(x,y,z) ← R(x,y,z) AND x < y AND y < z Exercise 5.4.2cAnswer(x,y,z) ← R(x,y,z) AND x < yAnswer(x,y,z) ← R(x,y,z) AND y < zExercise 5.4.2dChange:NOT(x < y OR x > y)To:x ≥ y AND x ≤ yThe above simplifies to x = yAnswer(x,y,z) ← R(x,y,z) AND x = yExercise 5.4.2eChange:NOT((x < y OR x > y) AND y < z)NOT(x < y OR x > y) OR y ≥ z(x ≥ y AND x ≤ y) OR y ≥ zTo:x = y OR y ≥ zAnswer(x,y,z) ← R(x,y,z) AND x = yAnswer(x,y,z) ← R(x,y,z) AND y ≥ zExercise 5.4.2fChange:NOT((x < y OR x < z) AND y < z)NOT(x < y OR x < z) OR y ≥ z To:(x ≥ y AND x ≥ z) OR y ≥ zAnswer(x,y,z) ← R(x,y,z) AND x ≥ y AND x ≥ zAnswer(x,y,z) ← R(x,y,z) AND y ≥zExercise 5.4.3aAnswer(a,b,c,d) ← R(a,b,c) AND S(b,c,d)Exercise 5.4.3bAnswer(b,c,d,e) ← S(b,c,d) AND T(d,e)Exercise 5.4.3cAnswer(a,b,c,d,e) ← R(a,b,c) AND S(b,c,d) AND T(d,e)Exercise 5.4.4a)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = syb)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx < sy AND ry < szc)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx < syAnswer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry < szd)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = sye)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = syAnswer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry ≥ szf)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx ≥ sy AND rx ≥ szAnswer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry ≥ szExercise 5.4.5aR1 := πx,y(Q R)Exercise 5.4.5bR1 := ρR1(x,z)(Q)R2 := ρR2(z,y)(Q)R3 := πx,y(R1 (R1.z = R2.z) R2)Exercise 5.4.5cR1 := πx,y(Q R)R2 := σx < y(R1)。
数据库应用基础第5章习题参考答案
1．选择题（1）SQL 语言中，删除一个视图的命令是（ B ）。
A. DELETEB. DROPC. CLEARD. REMOVE（2）建立索引的作用之一是 ( D )。
A ． 节省存储空间 B. 便于管理C ． 提高查询速度 D. 提高查询和更新的速度（3）以下关于主索引和候选索引的叙述正确的是 ( C )。
A ．主索引和候选索引都能保证表记录的惟一性B ．主索引和候选索引都可以建立在数据库表和自由表上C ．主索引可以保证表记录的惟一性，而候选索引不能D ．主索引和侯选索引是相同的概念（4）在数据库设计器中，不能完成的操作是（ ）。
A ．创建数据表关联BC ．修改关联中的主键表和外键表D ．删除关联 （5）下面所列条目中，（ C ）不是标准的SQL 语句。
A. ALTER TABLE B. CREATE TABLE C. ALTER VIEW D. CREATE VIEW2．填空题（1）索引是数据库中一种特殊类型的对象，它与（ 数据库表 ）有着紧密的关系。
（2）在数据库中，索引使数据库程序无需对整个表进行（ 扫描 ），就可以在其中找到所需数据。
（3）在SQL Server 2000中可创建3种类型的索引，即惟一性索引、（ 主键索引 ）和聚集索引。
（4）视图是一个（ 虚拟表 ），并不包含任何的物理数据。
（5）视图属性包括视图（ 视图名称、权限、所有者、创建日期 ）和用于创建视图的文本等几个方面。
3．问答题（1）聚集索引与非聚集索引之间有哪些不同点？在一个表中是否可以建立多少个聚集索引和非聚集索引？答：在建立了聚集索引的基本表中，表中各记录的物理顺序与索引键值的逻辑顺序相同；数据表中数据更改后需要对记录重新物理排序。
而在只建立了非聚集索引的表中，记录的物理顺序不一定与索引键值保持一致；数据表中数据更改后，不需要对表中记录重新排序，只需要更新对应的索引即可。
一个基本表中只能建立一个聚集索引，但可以建立多个非聚集索引。
数据库系统基础教程第五章答案
Exercise 5.1.1 As a set:Average = 2.37 As a bag:Average = 2.48 Exercise 5.1.2Average = 218 As a bag:Average = 215 Exercise 5.1.3a As a set:161418As a bag:bore1516141615151418Exercise 5.1.3b(Ships Classes)πboreExercise 5.1.4aFor bags:On the left-hand side:Given bags R and S where a tuple t appears n and m times respectively,the union of bags R and S will have tuple t appear n + m times. Thefurther union of bag T with the tuple t appearing o times will havetuple t appear n + m + o times in the final result.On the right-hand side:Given bags S and T where a tuple t appears m and o times respectively,the union of bags R and S will have tuple t appear m + o times. Thefurther union of bag R with the tuple t appearing n times will havetuple t appear m + o + n times in the final result.For sets:This is a similar case when dealing with bags except the tuple t can only appear at most once in each set. The tuple t only appears in the result if allthe sets have the tuple t. Otherwise, the tuple t will not appear in the result. Since we cannot have duplicates, the result only has at most one copy of the tuple t.Exercise 5.1.4bFor bags:On the left-hand side:Given bags R and S where a tuple t appears n and m times respectively,the intersection of bags R and S will have tuple t appear min( n, m )times. The further intersection of bag T with the tuple t appearing otimes will produce tuple t min( o, min( n, m ) ) times in the finalresult.On the right-hand side:Given bags S and T where a tuple t appears m and o times respectively,the intersection of bags R and S will have tuple t appear min( m, o )times. The further intersection of bag R with the tuple t appearing ntimes will produce tuple t min( n, min( m, o ) ) times in the finalresult.The intersection of bags R,S and T will yield a result where tuple t appears min( n,m,o ) times.For sets:This is a similar case when dealing with bags except the tuple t can only appear at most once in each set. The tuple t only appears in the result if all the sets have the tuple t. Otherwise, the tuple t will not appear in the result.Exercise 5.1.4cFor bags:On the left-hand side:Given that tuple r in R, which appears m times, can successfully joinwith tuple s in S, which appears n times, we expect the result tocontain mn copies. Also given that tuple t in T, which appears o times,can successfully join with the joined tuples of r and s, we expect the final result to have mno copies.On the right-hand side:Given that tuple s in S, which appears n times, can successfully joinwith tuple t in T, which appears o times, we expect the result tocontain no copies. Also given that tuple r in R, which appears m times, can successfully join with the joined tuples of s and t, we expect the final result to have nom copies.The order in which we perform the natural join does not matter for bags.For sets:This is a similar case when dealing with bags except the joined tuples can only appear at most once in each result. If there are tuples r,s,t in relations R,S,T that can successfully join, then the result will contain a tuple with the schema of their joined attributes.Exercise 5.1.4dFor bags:Suppose a tuple t occurs n and m times in bags R and S respectively. In the union of these two bags R ⋃ S, tuple t would appear n + m times. Likewise, in the union of these two bags S ⋃ R, tuple t would appear m + n times. Both sides of the relation yield the same result.For sets:A tuple t can only appear at most one time. Tuple t might appear each in sets R and S one or zero times. The combinations of number of occurrences for tuple t in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t appears in both sets R and S will the union R ⋃ S have the tuple t. The same reasoning holds when we take the union S ⋃ R.Therefore the commutative law for union holds.Exercise 5.1.4eFor bags:Suppose a tuple t occurs n and m times in bags R and S respectively. In the intersection of these two bags R ∩ S, tuple t would appear min( n,m ) times. Likewise in the intersection of these two bags S ∩ R, tuple t would appear min( m,n ) times. Both sides of the relation yield the same result.For sets:A tuple t can only appear at most one time. Tuple t might appear each in sets R and S one or zero times. The combinations of number of occurrences for tuplet in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t appears in at least one of the sets R and S will the intersection R ∩ S have the tuple t. The same reasoning holds when we take the intersection S ∩ R.Therefore the commutative law for intersection holds.Exercise 5.1.4fFor bags:Suppose a tuple t occurs n times in bag R and tuple u occurs m times in bag S. Suppose also that the two tuples t,u can successfully join. Then in thenatural join of these two bags R S, the joined tuple would appear nm times. Likewise in the natural join of these two bags S R, the joined tuple would appear mn times. Both sides of the relation yield the same result.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuples u,v might appear respectively in sets R and S one or zero times. The combinations of number of occurrences for tuples u,v in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple u exists in R and tuple v exists in S will the natural join R S have the joined tuple. The same reasoning holds when we take the natural join S R.Therefore the commutative law for natural join holds.Exercise 5.1.4gFor bags:Suppose tuple t appears m times in R and n times in S. If we take the union of R and S first, we will get a relation where tuple t appears m + n times. Taking the projection of a list of attributes L will yield a resultingrelation where the projected attributes from tuple t appear m + n times. If we take the projection of the attributes in list L first, then the projected attributes from tuple t would appear m times from R and n times from S. The union of these resulting relations would have the projected attributes of tuple t appear m + n times.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuple tmight appear in sets R and S one or zero times. The combinations of number of occurrences for tuple t in R and S respectively are (0,0), (0,1), (1,0), and (1,1). Only when tuple t exists in R or S (or both R and S) will the projected attributes of tuple t appear in the result.Therefore the law holds.Exercise 5.1.4hFor bags:Suppose tuple t appears u times in R, v times in S and w times in T. On the left hand side, the intersection of S and T would produce a result where tuple t would appear min(v , w) times. With the addition of the union of R, the overall result would have u + min(v , w) copies of tuple t. On the right hand side, we would get a result of min(u + v, u + w) copies of tuple t. The expressions on both the left and right sides are equivalent.For sets:An arbitrary tuple t can only appear at most one time in any set. Tuple tmight appear in sets R,S and T one or zero times. The combinations of number of occurrences for tuple t in R, S and T respectively are (0,0,0), (0,0,1), (0,1,0), (0,1,1), (1,0,0), (1,0,1), (1,1,0) and (1,1,1). Only when tuple t appears in R or in both S and T will the result have tuple t.Therefore the distributive law of union over intersection holds.Exercise 5.1.4iSuppose that in relation R, u tuples satisfy condition C and v tuples satisfy condition D. Suppose also that w tuples satisfy both conditions C and D where w≤ min(v , w). Then the left hand side will return those w tuples. On the right hand side, σ(R) produces u tuples and σD(R) produces v tuples. However,Cwe know the intersection will produce the same w tuples in the result.When considering bags and sets, the only difference is bags allow duplicate tuples while sets only allow one copy of the tuple. The example above applies to both cases.Therefore the law holds.Exercise 5.1.5aFor sets, an arbitrary tuple t appears on the left hand side if it appears in both R,S and not in T. The same is true for the right hand side.As an example for bags, suppose that tuple t appears one time each in both R,T and two times in S. The result of the left hand side would have zero copies of tuple t while the right hand side would have one copy of tuple t.Therefore the law holds for sets but not for bags.Exercise 5.1.5bFor sets, an arbitrary tuple t appears on the left hand side if it appears in R and either S or T. This is equivalent to saying tuple t only appears when it is in at least R and S or in R and T. The equivalence is exactly the right side’s expression.As an example for bags, suppose that tuple t appears one time in R and two times each in S and T. Then the left hand side would have one copy of tuple tin the result while the right hand side would have two copies of tuple t.Therefore the law holds for sets but not for bags.Exercise 5.1.5cFor sets, an arbitrary tuple t appears on the left hand side if it satisfies condition C, condition D or both condition C and D. On the right hand side,σC (R) selects those tuples that satisfy condition C while σD(R) selects thosetuples that satisfy condition D. However, the union operator will eliminate duplicate tuples, namely those tuples that satisfy both condition C and D. Thus we are ensured that both sides are equivalent.As an example for bags, we only need to look at the union operator. If there are indeed tuples that satisfy both conditions C and D, then the right hand side will contain duplicate copies of those tuples. The left hand side, however, will only have one copy for each tuple of the original set of tuples.Exercise 5.2.1aExercise 5.2.1bExercise 5.2.1cExercise 5.2.1dExercise 5.2.1eExercise 5.2.1fExercise 5.2.1gExercise 5.2.1hExercise 5.2.1iExercise 5.2.1jExercise 5.2.1kExercise 5.2.1lExercise 5.2.1mExercise 5.2.1nExercise 5.2.2aApplying the δ operator on a relation with no duplicates will yield the same relation. Thus δ is idempotent.Exercise 5.2.2bThe result of πis a relation over the list of attributes L. Performing theLprojection again will return the same relation because the relation only contains the list of attributes L. Thus πis idempotent.LExercise 5.2.2cis a relation where condition C is satisfied by every tuple. The result of σCPerforming the selection again will return the same relation because the relation only contains tuples that satisfy the condition C. Thus σisC idempotent.Exercise 5.2.2dThe result of γis a relation whose schema consists of the groupingLattributes and the aggregated attributes. If we perform the same grouping operation, there is no guarantee that the expression would make sense. The grouping attributes will still appear in the new result. However, the aggregated attributes may or may not appear correctly. If the aggregated attribute is given a different name than the original attribute, thenwould not make sense because it contains an aggregation for an performing γLattribute name that does not exist. In this case, the resulting relation would,according to the definition, only contain the grouping attributes. Thus, γLis not idempotent.Exercise 5.2.2eThe result of τ is a sorted list of tuples based on some attributes L. If Lis not the entire schema of relation R, then there are attributes that are not sorted on. If in relation R there are two tuples that agree in all attributes L and disagree in some of the remaining attributes not in L, then it is arbitrary as to which order these two tuples appear in the result. Thus, performing the operation τ multiple times can yield a different relation where these two tuples are swapped. Thus, τ is not idempot ent.Exercise 5.2.3If we only consider sets, then it is possible. We can take π(R) and do aAproduct with itself. From this product, we take the tuples where the two columns are equal to each other.If we consider bags as well, then it is not possible. Take the case where we have the two tuples (1,0) and (1,0). We wish to produce a relation that contains tuples (1,1) and (1,1). If we use the classical operations of relational algebra, we can either get a result where there are no tuples or four copies of the tuple (1,1). It is not possible to get the desired relation because no operation can distinguish between the original tuples and the duplicated tuples. Thus it is not possible to get the relation with the two tuples (1,1) and (1,1).Exercise 5.3.1a)Answer(model) ← PC(model,speed,_,_,_) AND speed ≥ 3.00b)Answer(maker) ← Laptop(model,_,_,hd,_,_) AND Product(maker,model,_) ANDhd ≥ 100c)Answer(model,price) ← PC(model,_,_,_,price) AND Product(maker,model,_)AND maker=’B’Answer(model,price) ← Laptop(mode l,_,_,_,_,price) ANDProduct(maker,model,_) AND maker=’B’Answer(model,price) ← Printer(model,_,_,price) ANDProduct(maker,model,_) AND maker=’B’d)Answer(model) ← Printer(model,color,type,_) AND color=’true’ ANDtype=’laser’e)PCMaker(maker) ← Product(maker,_,type) AND type=’pc’LaptopMaker(maker) ← Product(maker,_,type) AND type=’laptop’Answer(maker) ← LaptopMaker(maker) AND NOT PCMaker(maker)f)Answer(hd) ← PC(model1,_,_,hd,_) AND PC(model2,_,_,hd,_) AND model1 <>model2g)Answer(model1,model2) ← PC(model1,speed, ram,_,_) ANDPC(model2,_speed,ram,_,_) AND model1 < model2h)FastComputer(model) ← PC(model,speed,_,_,_) AND speed ≥ 2.80FastComputer(model) ← Laptop(model,speed,_,_,_,_) AND speed ≥ 2.80Answer(maker) ← Product(maker,model1,_) AND Product(maker,mod el2,_) AND FastComputer(model1) AND FastComputer(model2) AND model1 <> model2i)Computers(model,speed) ← PC(model,speed,_,_,_)Computers(model,speed) ← Laptop(model,speed,_,_,_,_)SlowComputers(model) ← Computers(model,speed) ANDComputers(model1,speed1) AND speed < speed1FastestComputers(model) ← Computers(model,_) AND NOTSlowComputers(model)Answer(maker) ← Fast estComputers(model) AND Product(maker,model,_)j)PCs(maker,speed) ← PC(model,speed,_,_,_) AND Product(maker,model,_) Answer(maker) ← PCs(maker,spe ed) AND PCs(maker,speed1) ANDPCs(maker,speed2) AND speed <> speed1 AND speed <> speed2 AND speed1 <> speed2k)PCs(maker,model) ← Product(maker,model,type) AND type=’pc’Answer(maker) ← PCs(maker,model) AND PCs(maker,model1) ANDPCs(maker,model2) AND PCs(maker,model3) AND model <> model1 AND model <> model2 AND model1 <> model2 AND (model3 = model OR model3 = model1 ORmodel3 = model2)Exercise 5.3.2a)Answer(class,country) ← Classes(class,_,country,_,bore,_) AND bore ≥16b)Answer(name) ← Ships(name,_,launch ed) AND launched < 1921c)Answer(ship) ← Outcomes(ship,battle,result) AND battle=’DenmarkStrait’ AND result = ‘sunk’d)Answer(name) ← Classes(class,_,_,_,_,displacement) ANDShips(name,class,launched) AND displacement > 35000 AND launched > 1921e)Answer(nam e,displacement,numGuns) ←Classes(class,_,_,numGuns,_,displacement) AND Ships(name,class,_) ANDOutcomes (ship,battle,_) AND battle=’Guadalcanal’ AND ship=namef)Answer(name) ← Ships(name,_,_)Answer(name) ← Outcomes(name,_,_) AND NOT Answer(name)g)MoreThan One(class) ← Ships(name,class,_) AND Ships(name1,class,_) ANDname <> name1Answer(class) ← Classes(class,_,_,_,_,_) AND NOT MoreThanOne(class)h)Battleship(country) ← Classes(_,type,country,_,_,_) AND type=’bb’Battlecruiser(country) ← Classes(_,type,country,_,_,_) AND type=’bc’Answer(country) ← Battleship(country) AND Battlecruiser(country)i)Results(ship,result,date) ← Battles(name,date) ANDOutcomes(ship,battle,result) AND battle=nameAnswer(ship) ← Results(ship,result,date) AND Results(ship,_,date1) AND result=’damaged’ AND date < date1Exercise 5.3.3A nswer(x,y) ← R(x,y) AND z = zExercise 5.4.1aAnswer(a,b,c) ← R(a,b,c)Answer(a,b,c) ← S(a,b,c)Exercise 5.4.1bAnswer(a,b,c) ← R(a,b,c) AND S(a,b,c)Exercise 5.4.1cAnswer(a,b,c) ← R(a,b,c) AND NOT S(a,b,c)Exercise 5.4.1dUnion(a,b,c) ← R(a,b,c)Union(a,b,c) ← S(a,b,c)Answer(a,b,c) ← Union(a,b,c) AND NOT T(a,b,c)Exercise 5.4.1eJ(a,b,c) ← R(a,b,c) AND NOT S(a,b,c)K(a,b,c) ← R(,a,b,c) AND NOT T(a,b,c)Answer(a,b,c) ← J(a,b,c) AND K(a,b,c)Exercise 5.4.1fAnswer(a,b) ← R(a,b,_)Exercise 5.4.1gJ(a,b) ← R(a,b,_)K(a,b) ← S(_,a,b)Answer(a,b) ← J(a,b) AND K(a,b)Exercise 5.4.2aAnswer(x,y,z) ← R(x,y,z) AND x = yExercise 5.4.2bAnswer(x,y,z) ← R(x,y,z) AND x < y AND y < z Exercise 5.4.2cAnswer(x,y,z) ← R(x,y,z) AND x < yAnswer(x,y,z) ← R(x,y,z) AND y < zExercise 5.4.2dChange: NOT(x < y OR x > y)To: x ≥ y AND x ≤ yThe above simplifies to x = yAnswer(x,y,z) ← R(x,y,z) AND x = yExercise 5.4.2eChange: NOT((x < y OR x > y) AND y < z)NOT(x < y OR x > y) OR y ≥ z(x ≥ y AND x ≤ y) OR y ≥ zTo: x = y OR y ≥ zAnswer(x,y,z) ← R(x,y,z) AND x = yAnswer(x,y,z) ← R(x,y,z) AND y ≥ zExercise 5.4.2fChange: NOT((x < y OR x < z) AND y < z)NOT(x < y OR x < z) OR y ≥ zTo: (x ≥ y AND x ≥ z) OR y ≥ zAnswer(x,y,z) ← R(x,y,z) AND x ≥ y AND x ≥ zAnswer(x,y,z) ← R(x,y,z) AND y ≥zExercise 5.4.3aAnswer(a,b,c,d) ← R(a,b,c) AND S(b,c,d)Exercise 5.4.3bAnswer(b,c,d,e) ← S(b,c,d) AND T(d,e)Exercise 5.4.3cAnswer(a,b,c,d,e) ← R(a,b,c) AND S(b,c,d) AND T(d,e)Exercise 5.4.4a)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = syb)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx < sy ANDry < szc)Answer(rx,ry,rz,sx,sy,s z) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx < syAnswer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry < szd)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = sye)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx = syAnswe r(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry ≥ szf)Answer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND rx ≥ syAND rx ≥ szAnswer(rx,ry,rz,sx,sy,sz) ← R(rx,ry,rz) AND S(sx,sy,sz) AND ry ≥ sz Exercise 5.4.5aR1 := πx,y(Q R) Exercise 5.4.5bR1 := ρR1(x,z)(Q)R2 := ρR2(z,y)(Q)R3 := πx,y (R1(R1.z = R2.z)R2)Exercise 5.4.5cR1 := πx,y(Q R)R2 := σx < y(R1)。
数据库原理第五章作业
第五章数据库保护一. 简答题1.导致数据库破坏的四种类型？DBMS分别用何措施来保护之？非法用户，非法数据，各种故障，多用户的并发访问。
权限机制，完整性约束，故障恢复能力，并发控制机制。
2.SQL SERVER的安全体系？SQL Server安全体系由三级组成，从外向内，分别是DBMS或数据库服务器级，数据库级，语句与对象级，并且一级比一级要求高，即内部级比外部级高。
3.数据库的完整性约束分几种类型？关系模型上的完整性约束与数据库的完整性约束的关系？数据库完整性约束的实现步骤？静态完整性约束，动态完整性约束。
数据库的完整性约束，不仅仅只由数据模型上的完整性约束构成，还会有其他形式。
定义，验证。
4.显式完整性约束有哪几种定义方式？过程化定义，断言，触发器。
5.事务的特点是什么？它有哪几个特性或性质？事务由多个步骤构成，只有所有步骤都成功执行，则该事务才可提交完成，否则，其中任一个步骤执行失败，则该事务失败，事务中已执行的步骤应撤销或回退。
原子性，一致性，隔离性，持久性。
6.所谓的故障恢复，是对什么进行恢复？恢复主要是恢复数据库本身，即在故障引起当前数据库状况不一致后，利用备份副本，将数据库恢复到某个正确状态或一致状态。
7.故障恢复时，对事务处理的总的原则是什么？对提交的事务，用后像AI重做，即将后像写入磁盘。
对未提交的事务，用前像回退，即将前像写入磁盘。
8.提交规则与先记后写规则之间的关系？先记后写规则实际上是对提交规则的补充，即是对提交规则所说的第一种情况进行补充。
9.日志的基本内容？活动事务表，提交事务表，前像，后像。
10.系统失效是否会导致事务失效？为什么？不会。
系统故障的恢复策略，是撤销故障发生时未提交的事务，重做已提交的事务。
11.为何要并发？何谓并发调度？何谓串行调度？提高系统的资源利用率，改善短事务的响应时间。
并发调度：控制事务并发执行的调度。
串行调度：控制事务串行执行的调度。
12.并发执行可能引起哪些问题？产生这些问题的原因各是什么？丢失更新，读脏数据，读值不可复现。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第五章习题一、选择题：1．关系规范化中的删除操作异常是指①，插入操作异常是指②。
A．不该删除的数据被删除B．不该插入的数据被插入C．应该删除的数据未被删除D．应该插入的数据未被插入答案：①A ②D2．设计性能较优的关系模式称为规范化，规范化主要的理论依据是____。
A．关系规范化理论B．关系运算理论C．关系代数理论D．数理逻辑答案：A3．规范化理论是关系数据库进行逻辑设计的理论依据。
根据这个理论，关系数据库中的关系必须满足：其每一属性都是____。
A．互不相关的B．不可分解的C．长度可变的D．互相关联的答案：B4．关系数据库规范化是为解决关系数据库中____问题而引人的。
A．插入、删除异常和数据冗余B．提高查询速度C．减少数据操作的复杂性D．保证数据的安全性和完整性答案：A5．规范化过程主要为克服数据库逻辑结构中的插入异常，删除异常以及____的缺陷。
A．数据的不一致性B．结构不合理C．冗余度大D．数据丢失答案：C6．当关系模式R（A，B）已属于3NF，下列说法中____是正确的。
A．它一定消除了插入和删除异常B．仍存在一定的插入和删除异常C．一定属于BCNF D．A和C都是答案：B7．关系模型中的关系模式至少是____。
A．1NF B．2NF C．3NF D．BCNF答案：A8．在关系DB中，任何二元关系模式的最高范式必定是____。
A．1NF B．2NF C．3NF D．BCNF答案：D9．在关系模式R中，若其函数依赖集中所有候选关键宇都是决定因素，则R最高范式是____。
A．2NF B．3NF C．4 NF D．BCNF答案：C10．当B属性函数依赖于A属性时，属性A与B的联系是____。
A．1对多B．多对1 C．多对多D．以上都不是答案：B11．在关系模式中，如果属性A和B存在1对1的联系，则说____。
A．A→B B．B→A C．A↔B D．以上都不是答案：C12．候选码中的属性称为____。
A．非主属性B．主属性C．复合属性D．关键属性答案：B13．关系模式中各级模式之间的关系为____。
A．3NF⊂2NF⊂ 1NF B．3NF⊂ 1NF⊂2NFC．1NF⊂2NF⊂3NF D．2NF⊂1NF⊂3NF答案：A14．关系模式中，满足2NF的模式____。
A．可能是1NF B．必定是1NFC．必定是3NF D．必定是BCNF答案：B15．关系模式R中的属性全部是主属性，则R的最高范式必定是____。
A．2NF B．3NF C．BCNF D．4NF答案：B16．消除了部分函数依赖的INF的关系模式，必定是____。
A．1NF B．2NF C．3NF D．4NF答案：B17．关系模式的候选码可以有①，主码有②。
A．0个B．l个C．l个或多个D．多个答案：①C ②B18．候选码中的属性可以有____。
A．0个B．l个C．l个或多个D．多个答案：C19．关系模式的分解____。
A．惟一B．不惟一答案：B20．根据关系数据库规范化理论，关系数据库中的关系要满足第一范式。
下面“部门”关系中，因哪个属性而使它不满足第一范式？部门（部门号，部门名，部门成员，部门总经理）A．部门总经理B．部门成员C．部门名D．部门号答案：B21．图5.5中给定关系R____。
图5.5 关系RA．不是3NF B．是3NF但不是2NFC．是3NF但不是BCNF D．是BCNF答案：D22．设有如图5.6所示的关系R，它是____。
A．1NF B．2NF C．3NF D．4NF答案：B图5.6 关系R23．设有关系W（工号，姓名，工种，定额），将其规范化到第三范式正确的答案是____。
A．W1（工号，姓名）W2（工种，定额）B．W1（工号，工种，定额）W2（工号，姓名）C．W1（工号，姓名，工种）W2（工号，定额）D．以上都不对答案：C24．设有关系模式W（C，P，S，G，T，R），其中各属性的含义是：C为课程，P为教师，S为学生，G为成绩，T为时间，R为教室，根据定义有如下函数依赖集：F=｛C→G，（S，C）→G，（T，R）→C，（T，P）→R，（T，S）→R｝关系模式W的一个候选码是①，W的规范化程度最高达到②。
若将关系模式W分解为3个关系模式W1（C，P），W2（S，C，G），W3（S，T，R，C），则W1的规范化程度最高达到③，W2的规范化程度最高达到④，W3的规范化程度最高达到⑤。
①A．（S，C）B．（T，R）C．（T，P）D．（T，S）E．（T，S，P）②③④⑤A．1NF B．2NF C．3NF D．BCNF E．4NF答案：①E ②B ③E ④E ⑤B1．属于BCNF的关系模式________。
A．已消除了插入、删除异常B．已消除了插入、删除异常和数据冗余C．仍然存在插入、删除异常D．在函数依赖范畴内，已消除了插入和删除的异常答案：D2．设R(U)是属性集U上的关系模式。
X,Y是U的子集。
若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称________。
A．Y函数依赖于X B．Y对X完全函数依赖C．X为U的候选码D．R属于2NF答案：A3．在R(U)中，如果X→Y，并且对于X的任何一个真子集X'，都有X' Y，则________。
A．Y函数依赖于X B．Y对X完全函数依赖C．X为U的候选码D．R属于2NF答案：B4．包含在_______中的属性，叫做主属性。
A．主码B．候选码C．外码D．全码答案：B5．多值依赖的毛病在于________。
A．插入异常B．删除异常C．数据冗余太大D．插入异常、删除异常、及数据冗余太大答案：C6．1NF________规范为2NF。
A．消除非主属性对码的部分函数依赖B．消除非主属性对码的传递函数依赖C．消除主属性对码的部分和传递函数依赖D．消除非平凡且非函数依赖的多值依赖答案：A7．2NF________规范为3NF。
A．消除非主属性对码的部分函数依赖B．消除非主属性对码的传递函数依赖C．消除主属性对码的部分和传递函数依赖D．消除非平凡且非函数依赖的多值依赖答案：B8．3NF________规范为BCNF。
A．消除非主属性对码的部分函数依赖B．消除非主属性对码的传递函数依赖C．消除主属性对码的部分和传递函数依赖D．消除非平凡且非函数依赖的多值依赖答案：C9．3NF________规范为4NF。
A．消除非主属性对码的部分函数依赖B．消除非主属性对码的传递函数依赖C．消除主属性对码的部分和传递函数依赖D．消除非平凡且非函数依赖的多值依赖答案：D二、填空题：1．关系规范化的目的是____。
答案：控制冗余，避免插入异常和删除异常，从而增强数据库结构的稳定性和灵活性。
2．在关系A（S，SN，D）和B（D，CN，NM）中，A的主码是S，B的主码是D，则D 在S中称为____。
答案：外码3．对于非规范化的模式，经过①转变为1NF，将1NF经过②转变为2NF，将2NF经过③转变为3NF。
答案：①使属性域变为简单域。
②消除非主属性对码的部分函数依赖③消除非主属性对码的传递函数依赖4．在一个关系R中，若每个数据项都是不可分割的，那么R一定属于____。
答案：1NF5．1NF，2NF，和3NF之间，相互是一种____关系。
答案：3NF2NF lNF成立6．若关系为1NF，且它的每一非主属性都____候选码，则该关系为2NF。
答案：不部分函数依赖于7．在关系数据库的规范化理论中，在执行“分解”时，必须遵守规范化原则：保持原有的函数依赖和____。
答案：无损连接三、简答题和综合题：1．指出下列关系模式是第几范式？并说明理由。
⑴R（X，Y，Z）F = { XY→Z}。
⑵R（X，Y，Z）F = { Y→Z，XZ→Y}。
⑶R（X，Y，Z）F = { Y→Z，Y→X，X→YZ }。
⑷R（X，Y，Z）F = { X→Y，X→Z }。
⑸R（W，X，Y，Z）F = { X→Z，WX→Y }。
解：（1）R是BCNF。
侯选码为XY，F中只有一个函数依赖，而该函数依赖的左部包含了R 的侯选码XY 。
（2）R 是3NF 。
侯选码为XY 和XZ ，R 中所有属性都是主属性，不存在非主属性对码的传递依赖。
（3）R 是BCNF 。
侯选码为X 和Y ，⊥ X →YZ ，⎽ X →Y ，X →Z ，由于F 中有Y →Z ，Y →X ，因此Z 是直接函数依赖于X ，而不是传递依赖于X 。
又⊥ F 的每一函数依赖的左部都包含了任一侯选码，⎽ R 是BCNF 。
（4）R 是BCNF 。
侯选码为X ，而且F 中每一个函数依赖的左部都包含了侯选码X 。
（5）R 是1NF 。
侯选码为WX ，则Y ，Z 为非主属性，又由于X →Z ，因此F 中存在非主属性对侯选码的部分函数依赖。
2．设有关系模式R （U ，F），其中：U={A ，B ，C ，D ，E ，P}，F={ A →B ，C →P ，E →A ，CE →D}。
求R 的所有候选码。
解：根据候选码的定义：如果函数依赖X →U 在R 上成立，且不存在任何X ′⊆X ，使得X ′→U 也成立，则称X 是R 的一个候选码。
由此可知，候选码只可能由A 、C 、E 组成，但有E →A ，所以组成候选码的属性可能是CE 。
计算可知：(CE)+=ABCDEP ，即CE →U而：C +=CP ，E +=ABE⎽ R 只有一个候选码CE 。
3．设有关系模式R（U，F），其中U={C，T，S，N，G}，F={C→T，CS→G，S→N}。
求R的所有候选码。
解：根据候选码的定义，R的候选码只可能由F中各个函数依赖的左边属性组成，即C，S，所以组成候选码的属性可能是CS。
①所有只在函数依赖的左边出现的属性一定是某一候选码的成员，②所有只在函数依赖的右边出现的属性一定不在任何候选码中，③所有在函数依赖的左右两边均未出现的属性一定是某一候选码的成员，④所有在函数依赖的左右两边均出现的属性则需要进一步判断。
计算可知：(CS)+=CGNST，即CS→U而：C+=CT，S+=NS⎽ R只有一个候选码CS。
4．设有关系模式R（U，F），其中：U={A，B，C，D，E}，F = { A→BC，CD→E，B→D，E→A}。
⑴计算B+。
⑵求R的所有候选码。
解：⑴令X={B}，X(0)=B，X(1)=BD，X(2)=BD，故B+=BD。
⑵根据候选码的定义，R的候选码只可能由F中各个函数依赖的左边属性组成，即A，B，C，D，E，由于A→BC（A→B，A→C），B→D，E→A，故：可除去A，B，C，D，⎽组成候选码的属性可能是E。
计算可知：E+=ABCDE，即E→U，⎽ E是一个候选码。
可除去A，B，E，⎽组成候选码的属性可能是CD。