数据库基本知识第五章课后答案解析
数据库第五章选择题
1.在视图上不能完成的操作是（C）A．更新视图B．查询C．在视图上定义新的表D．在视图上定义新的视图2.SQL语言中删除一个视图的命令是（B）A．DELETEB．D ROPC．C LEARD．REMOVE3.在SQL语言中的视图VIEW是数据库的（A）A．外模式B．模式C．内模式D．存储模式4.创建存储过程的语句是(C)A．CREA TE STOREB．C REA TE FUNCTIONC．C REA TE PROCEDURED．CREA TE TRIGGER5.执行存储命令的是(B)A．DOB．E XECUTEC．E XED．GO6.创建视图的语句是(A)A.CREA TE VIEWB.CREA TE FUNCTIONC.CREA TE PROCEDURED.CREA TE TRIGGER7.创建触发器的语句是(D)A.CREA TE STOREB.CREA TE FUNCTIONC.CREA TE PROCEDURED.CREA TE TRIGGER8.创建索引的语句是(B)A.CREA TE VIEWB.CREA TE INDEXC.CREA TE PROCEDURED.CREA TE TRIGGER9.下面关于存储过程的描述不正确的是(C)A.存储过程实际上是一组T-SQL语句B.存储过程预先被编译存放在服务器的系统表中C.存储过程独立于数据库而存在D.存储过程可以完成某一特定的业务逻辑10.系统存储过程在系统安装时就已创建，这些存储过程放在（A）系统数据库中A.masterB.tempdbC.modelD.pubs11.带有前缀名为sp_的存储过程属于（B）A.用户自定义的存储过程B.系统存储过程C.扩展存储过程D.以上都不是12.下面函数中不能进行数据类型转换的是（D）A.CONVERTB.STRC.CASTD.LTRIM13.T-SQL中的全局变量以（A）作前缀A.@@B.@C.#D.##14.下面关于唯一索引描述不正确的是（A）A.某列创建了唯一索引则这一列为主键B.不允许插入重复的列值C.某列创建为主键，则该列会自动创建唯一索引D.一个表中可以有多个唯一索引15.以下关于视图的描述中，错误的是（D）A．视图不是真实存在的基础表，而是一张虚表B．当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化C．在创建视图时，若其中某个目标列是聚合函数，必须指明视图的全部列名D．在一个语句中，一次可以修改一个以上的视图对应的基表16.在使用T-SQL创建视图时，不能使用的关键字是（A）A．ORDER BYB．W HEREC．A SD．WITH CHECK OPTION17.视图是从（基本表或视图）中导出的表，数据库中实际存放的是视图的（定义）18.触发器和存储过程的区别在于，存储过程的是（命令调用）执行，触发器是（事件）执行的19.视图删除后，只会删除视图（定义），与其相关的基本表数据（不会）受到影响。
数据库系统第五版课后习题答案
第1章绪论1 ．试述数据、数据库、数据库系统、数据库管理系统的概念。
答：( l ）数据（ Data ) ：描述事物的符号记录称为数据。
数据的种类有数字、文字、图形、图像、声音、正文等。
数据与其语义是不可分的。
解析在现代计算机系统中数据的概念是广义的。
早期的计算机系统主要用于科学计算，处理的数据是整数、实数、浮点数等传统数学中的数据。
现代计算机能存储和处理的对象十分广泛，表示这些对象的数据也越来越复杂。
数据与其语义是不可分的。
500 这个数字可以表示一件物品的价格是 500 元，也可以表示一个学术会议参加的人数有 500 人，还可以表示一袋奶粉重 500 克。
( 2 ）数据库（ DataBase ，简称 DB ) ：数据库是长期储存在计算机内的、有组织的、可共享的数据集合。
数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。
( 3 ）数据库系统（ DataBas 。
Sytem ，简称 DBS ) ：数据库系统是指在计算机系统中引入数据库后的系统构成，一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员构成。
解析数据库系统和数据库是两个概念。
数据库系统是一个人一机系统，数据库是数据库系统的一个组成部分。
但是在日常工作中人们常常把数据库系统简称为数据库。
希望读者能够从人们讲话或文章的上下文中区分“数据库系统”和“数据库”，不要引起混淆。
( 4 ）数据库管理系统（ DataBase Management sytem ，简称 DBMs ) ：数据库管理系统是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据。
DBMS 的主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立和维护功能。
解析 DBMS 是一个大型的复杂的软件系统，是计算机中的基础软件。
目前，专门研制 DBMS 的厂商及其研制的 DBMS 产品很多。
数据库第五章习题及答案
数据库第五章习题及答案本文档为数据库第五章的习题及答案，帮助读者巩固数据库相关知识。
习题1. 数据库的优点有哪些？数据库具有以下优点： - 数据共享：多个用户可以同时访问和共享数据库中的数据。
- 数据一致性：数据库提供事务管理能力，保证了数据的一致性。
- 数据持久性：数据在数据库中是永久存储的，不会因为系统关机或程序结束而丢失。
- 数据冗余度低：数据库通过规范化设计，减少了数据的冗余性，提高了数据的存储效率。
- 数据独立性：数据库支持数据与应用程序的独立性，提高了系统的灵活性和维护性。
- 数据安全性：数据库提供了用户权限管理和数据备份机制，保证了数据的安全性。
2. 数据库的三级模式结构是什么？数据库的三级模式结构包括： - 外模式（视图层）：外模式是用户所看到的数据库的子集，用于描述用户对数据库的逻辑视图。
每个用户可以有不同的外模式来满足自己的需求。
- 概念模式（逻辑层）：概念模式是全局数据库的逻辑结构和组织方式，描述了数据的总体逻辑视图。
概念模式独立于具体的应用程序，是数据库管理员的角度来看待数据库的。
- 内模式（物理层）：内模式是数据库的存储结构和物理组织方式，描述了数据在存储介质上的实际存储方式。
3. 数据库的完整性约束有哪些？数据库的完整性约束包括： - 实体完整性约束：确保表的主键不为空，每个实体都能够唯一标识。
- 参照完整性约束：确保外键的引用关系是有效的，即外键值必须等于被引用表中的主键值或者为空。
- 用户定义完整性约束：用户可以自定义额外的完整性约束，如检查约束、唯一约束、默认约束等。
4. 数据库的关系模型有哪些特点？数据库的关系模型具有以下特点： - 数据用二维表的形式进行组织，表由行和列组成，每一行表示一个实体，每一列表示一个属性。
- 表与表之间通过主键和外键建立关联关系，形成关系。
- 关系模型提供了一种数据独立性的设计方法，使得应用程序与数据的逻辑结构相分离，提高了系统的灵活性和可维护性。
数据库系统概论第五版课后习题答案
第1章绪论1 .试述数据、数据库、数据库系统、数据库管理系统的概念。
答:( l )数据( Data ) :描述事物的符号记录称为数据。
数据的种类有数字、文字、图形、图像、声音、正文等。
数据与其语义就是不可分的。
解析在现代计算机系统中数据的概念就是广义的。
早期的计算机系统主要用于科学计算,处理的数据就是整数、实数、浮点数等传统数学中的数据。
现代计算机能存储与处理的对象十分广泛,表示这些对象的数据也越来越复杂。
数据与其语义就是不可分的。
500这个数字可以表示一件物品的价格就是 500元,也可以表示一个学术会议参加的人数有 500人,还可以表示一袋奶粉重 500克。
( 2 )数据库( DataBase ,简称 DB ) :数据库就是长期储存在计算机内的、有组织的、可共享的数据集合。
数据库中的数据按一定的数据模型组织、描述与储存,具有较小的冗余度、较高的数据独立性与易扩展性,并可为各种用户共享。
( 3 )数据库系统( DataBas。
Sytem ,简称 DBS ) :数据库系统就是指在计算机系统中引入数据库后的系统构成,一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员构成。
解析数据库系统与数据库就是两个概念。
数据库系统就是一个人一机系统,数据库就是数据库系统的一个组成部分。
但就是在日常工作中人们常常把数据库系统简称为数据库。
希望读者能够从人们讲话或文章的上下文中区分“数据库系统”与“数据库”,不要引起混淆。
( 4 )数据库管理系统( DataBase Management sytem ,简称 DBMs ) :数据库管理系统就是位于用户与操作系统之间的一层数据管理软件,用于科学地组织与存储数据、高效地获取与维护数据。
DBMS的主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立与维护功能。
解析 DBMS就是一个大型的复杂的软件系统,就是计算机中的基础软件。
目前,专门研制 DBMS的厂商及其研制的 DBMS产品很多。
数据库原理与应用教程第四版 第五章答案
免责声明：私人学习之余整理，如有错漏，概不负责1.视图的优点简化数据查询语句、使用户能从多角度看待同一数据、提高了数据的安全性、提供了一定程度的逻辑独立性2.使用视图可以加快数据的查询速度吗？为什么？不对。
其本质上还是执行视图内部的查询语句，通过视图查询数据时，都是转换为对基本表的查询，其简化了数据查询语句但是并不能加快数据查询速度。
3.写出创建满足以下要求的视图的SQL语句。
1)查询学生的学号、姓名、所在系、课程号、课程名、课程学分。
CREATE VIEW v1(Sno,Sname,Sdept,Cno,Cname,Credit)ASSELECT s.Sno,Sname,Sdept,o,Cname,CreditFROM Student s JOIN SC ON s.Sno = SC.Sno JOIN Course c ON o = o2)查询学生的学号、姓名、选修的课程名和考试成绩。
CREATE VIEW v2(Sno,Sname,Cname,Grade)ASSELECT s.Sno,Sname,Cname,GradeFROM Student s JOIN SC ON s.Sno = SC.Sno JOIN Course c ON o = o3)统计每个学生的选课门数，列出学生学号和选课门数。
CREATE VIEW v3(Sno,选课门数)ASSELECT s.Sno,COUNT(*)FROM Student s JOIN SC ON s.Sno = SC.SnoGROUP BY Sno4)统计每个学生的修课总学分，列出学生学号和总学分。
(成绩大于等于60)CREATE VIEW v4(Sno,总学分)ASSELECT s.Sno,SUM(Credit)FROM Student s JOIN SC ON s.Sno = SC.Sno JOIN Course c ON o = oWHERE Grade > 60GROUP BY Sno5)查询计算机系Java考试成绩最高的学生的学号、姓名和Java考试成绩。
数据库系统基础教程课后答案第五章
Exercise 5.1.1 As a set:Average = 2.37 As a bag:Average = 2.48 Exercise 5.1.2 As a set:Average = 218 As a bag:Average = 215 Exercise 5.1.3a As a set:As a bag:Exercise 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 Dor 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.Exercise 5.2.1bExercise 5.2.1cExercise 5.2.1dExercise 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 π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(mode l,_,_,_,_,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,mod el2,_) 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) ← 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) AND PCs(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=’Denmark Strait’ AND result= ‘sunk’d)Answer(name) ← Classes(class,_,_,_,_,displacement) AND Ships(name,class,launched)AND displacement > 35000 AND launched > 1921e)Answer(nam e,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)MoreThan One(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.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 ≥ 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,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 ≥ 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模式：SQL模式是表和授权的静态定义。
一个SQL模式定义为基本表的集合。
一个由模式名和模式拥有者的用户名或账号来确定,并包含模式中每一个元素(基本表、视图、索引等)的定义。
(2)SQL数据库：SQL(Structured Query Language)，即‘结构式查询语言’，采用英语单词表示和结构式的语法规则。
一个SQL数据库是表的汇集，它用一个或多个SQL模式定义。
(3)基本表：在SQL中，把传统的关系模型中的关系模式称为基本表(Base Table)。
基本表是实际存储在数据库中的表，对应一个关系。
(4)存储文件：在SQL中，把传统的关系模型中的存储模式称为存储文件(Stored File)。
每个存储文件与外部存储器上一个物理文件对应。
(5)视图：在SQL中，把传统的关系模型中的子模式称为视图(View)，视图是从若干基本表和（或）其他视图构造出来的表。
(6)行：在SQL中，把传统的关系模型中的元组称为行(row)。
(7)列：在SQL中，把传统的关系模型中的属性称为列(coloumn)。
(8)实表：基本表被称为“实表”，它是实际存放在数据库中的表。
(9)虚表：视图被称为“虚表”，创建一个视图时，只把视图的定义存储在数据词典中，而不存储视图所对应的数据。
(10)相关子查询：在嵌套查询中出现的符合以下特征的子查询：子查询中查询条件依赖于外层查询中的某个值，所以子查询的处理不只一次，要反复求值，以供外层查询使用。
(11)联接查询：查询时先对表进行笛卡尔积操作，然后再做等值联接、选择、投影等操作。
联接查询的效率比嵌套查询低。
(12)交互式SQL：在终端交互方式下使用的SQL语言称为交互式SQL。
(13)嵌入式SQL：嵌入在高级语言的程序中使用的SQL语言称为嵌入式SQL。
(14)共享变量：SQL和宿主语言的接口。
共享变量有宿主语言程序定义，再用SQL 的DECLARE语句说明， SQL语句就可引用这些变量传递数据库信息。
数据库第五章习题及答案
第五章 关系数据理论一、 单项选择题1、设计性能较优的关系模式称为规范化，规范化主要的理论依据是 （ ）A 、关系规范化理论B 、关系运算理论C 、关系代数理论D 、数理逻辑2、关系数据库规范化是为解决关系数据库中（ ）问题而引入的。
A 、插入、删除和数据冗余B 、提高查询速度C 、减少数据操作的复杂性D 、保证数据的安全性和完整性3、当关系模式R （A ，B ）已属于3NF ，下列说法中（ ）是正确的。
A 、它一定消除了插入和删除异常B 、一定属于BCNFC 、仍存在一定的插入和删除异常D 、A 和C 都是4、在关系DB 中，任何二元关系模式的最高范式必定是（ ）A 、1NFB 、2NFC 、3NFD 、BCNF5、当B 属性函数依赖于A 属性时，属性A 与B 的联系是（ ）A 、1对多B 、多对1C 、多对多D 、以上都不是6、在关系模式中，如果属性A 和B 存在1对1的联系，则说（ ）A 、A B B 、B A C 、A B D 、以上都不是7、关系模式中，满足2NF 的模式，（ ）A 、可能是1NFB 、必定是1NFC 、必定是3NFD 、必定是BCNF8、关系模式R 中的属性全部是主属性，则R 的最高范式必定是（ ）A 、2NFB 、3NFC 、BCNFD 、4NF9、关系模式的候选关键字可以有（ c ），主关键字有（ 1个 ）A 、0个B 、1个C 、1个或多个D 、多个10、如果关系模式R 是BCNF 范式，那么下列说法不正确的是（ ）。
A 、R 必是3NFB 、R 必是1NFC 、R 必是2NFD 、R 必是4NF11、图4.5中给定关系R （ ）。
A 、不是3NFB 、是3NF 但不是2NFC 、是3NF 但不是BCNFD 、是BCNF12、设有如图4.6所示的关系R ，它是（ ）A 、1NFB 、2NFC 、3NFD 、4NF二、 填空题1、如果模式是BCNF ，则模式R 必定是（3NF ），反之，则（ 不一定 ）成立。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
5.4对于教学数据库的三个基本表学生S(S#,SNAME,AGE,SEX)学习SC(S#,,GRADE)课程C(C#,CNAME,TEACHER)试用SQL的查询语句表达下列查询：(1 )检索LIU老师所授课程的课程号和课程名。
SELECT C#，CNAMEFROM CWHERE TEACHER= ' LIU '(2)检索年龄大于23岁的男学生的学号和姓名。
SELECT S#，SNAMEFROM SWHERE (AGE>23) AND (SEX= ' M ')(3)检索至少选修LIU老师所授课程中一门课程的女学生姓名SELECT SNAMEFROM SWHERE SEX= ' F' AND S# IN(SELECT S#FROM SCWHERE C# IN(SELECT C#FROM CWHERE TEACHER= ' LIU ')NOTICE :有多种写法，比如联接查询写法：SELECT SNAMEFROM S,SC,CWHERE SEX= 'F' AND SC.S#=S.S#AND SC.C#=C.C#AND TEACHER='LIU'但上一种写法更好一些。
(4)检索WANG同学不学的课程的课程号。
SELECT C#FROM CWHERE C# NOT IN(SELECT C#FROM SCWHERE S# IN(SELECT S#FROM SWHERE SNAME='WANG'))(5 )检索至少选修两门课程的学生学号。
SELECT DISTINCT X.SNOFROM SC X,SC YWHERE X.SNO=Y.SNO AND O<>O Notice :对表SC进行自连接，X，Y是SC的两个别名。
(6)检索全部学生都选修的课程的课程号与课程名。
SELECT C#,CNAMEFROM CWHERE NOT EXISTS(SELECT *FROM SWHERE S# NOT IN(SELECT *FROM SCWHERE SC.C#=C.C#))要从语义上分解：(1)选择课程的课程号与课程名，不存在不选这门课的同学。
其中，“不选这门课的同学”可以表示为：或者SELECT *SELECT * FROM SFROM S WHERE NOT EXISTS WHERE S# NOT IN (SELECT * (SELECT * FROM SC FROM SC WHERE S.S#=C.S#WHERE SC.C#=C.C#) ANDSC.C#=C.C# )(7)检索选修课程包含LIU老师所授课的学生学号。
SELECT DISTINCT S#FROM SCWHERE C# IN(SELECT C#FROM CWHERE TEACHER='LIU'))(8 )统计有学生选修的课程门数。
SELECT COUNT(DISTINCT C#) FROM SC(9)求选修C4课程的学生的平均年龄。
SELECT AVG(AGE)FROM SWHERE S# IN(SELECT S#FROM SCWHERE C#='C4')或者，SELECT AVG(AGE)FROM S , SCWHERE S.S#=SC.S# AND C#='OO4'(10 )求LIU老师所授课程的每门课程的学生平均成绩。
SELECT CNAME,AVG(GRADE)FROM SC ,CWHERE SC.C#=C.C# AND TEACHER='LIU'GROUP BY C#查询(11 )统计每门课程的学生选修人数(超过10人的课程才统计)。
要求输出课程号和选修人数,结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT DISTINCT C#,COUNT(S#)FROM SCGROUP BY C#HAVING COUNT(S#)>10ORDER BY 2 DESC, C# ASC(12 )检索学号比WANG同学大，而年龄比他小的学生姓名。
SELECT X.SNAMEFROM S AS X, S AS YWHERE Y.SNAME='WANG' AND X.S#>Y.S# AND X.AGE<Y.AGE(13 )检索姓名以WANG打头的所有学生的姓名和年龄。
SELECT SNAME,AGEFROM SWHERE SNAME LIKE 'WANG%'(14 )在SC中检索成绩为空值的学生学号和课程号。
SELECT S#,C#FROM SCWHERE GRADE IS NULL(15 )求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT SNAME,AGEFROM S AS XWHERE X.SEX='男'AND X.AGE>(SELECT AVG(AGE) FROM S AS Y WHERE Y.SEX='女')(16 )求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT SNAME,AGEFROM S AS XWHERE X.SEX='男'AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE Y.SEX='女')5.6 试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作：(1 )往基本表S中插入一个学生元组(’S9' ‘ WU ',18 )。
INSERT INTO S(S#,SNAME,AGE) VALUES('59','WU',18)(2)在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别, 送往另一并把检索到的值个已存在的基本表STUDENT ( S#, SANME , SEX)。
INSERT INTO STUDENT(S#,SNAME,SEX)SELECT S#,SNAME,SEXFROM S WHERE NOT EXISTS(SELECT * FROM SC WHEREGRADE<80 AND S.S#=SC.S#)(3 )在基本表SC中删除尚无成绩的选课元组。
DELETE FROM SCWHERE GRADE IS NULL(4 )把WANG同学的学习选课和成绩全部删去。
DELETE FROM SCWHERE S# IN(SELECT S#FROM SWHERE SNAME='WANG')(5)把选修MATHS课不及格的成绩全改为空值。
UPDATE SCSET GRADE=NULLWHERE GRADE<60 AND C# IN(SELECT C#FROM CWHERE CNAME='MATHS')(6 )把低于总平均成绩的女同学成绩提高5%。
UPDATE SCSET GRADE=GRADE*1.05WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM S WHERE SEX='F')(7 )在基本表SC中修改C4课程的成绩，若成绩小于等于75分时提高5% ,若成绩大于75分时提高4% (用两个UPDATE语句实现)。
UPDATE SCSET GRADE=GRADE*1.05WHERE C#='C4' AND GRADE<=75UPDATE SCSET GRADE=GRADE*1.04WHERE C#='C4' AND GRADE>755.7在第1章例1.4中提到“仓库管理”关系模型有五个关系模式：零件PART(P#, PNAME , COLOR , WEIGHT )项目PROJECT (J #, JNAME , DATE)供应商SUPPLIER (S#, SNAME , SADDR )供应P_ P (J#, P#, TOTOAL )采购P_S ( P#, S#, QUANTITY )(1 )试用SQL DDL语句定义上述五个基本表，并说明主键和外键。
CREATE TABLE PART(P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,COLOR CHAR(10),WEIGHT REAL,PRIMARY KEY(P#))CREATE TABLE PROJECT(J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,DATE DATE,PRIMARY KEY(J#))CREATE TABLE SUPLIER(S# CHAR (4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),PRIMARY KEY(S#))CREATE TABLE P_P(J# CHAR(4),P# CHAR(4),TOTAL INTEGER,PRIMARY KEY(J#,P#),FOREIGN KEY(J#) REFERENCE PROJECT(J#),FOREIGN KEY(P#) REFERENCE PART(P#))CREATE TABLE P_S(P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,PRIMARY KEY(P#,S#),FOREIGN KEY(P#) REFERENCE PART(P#),FOREIGN KEY(S#) REFERENCE SUPLIER(S#))(2)试将PROGECT、P—P、PART三个基本表的自然联接定义为一个视图VIEW1 , PART、P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。
CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTALFROM PROJECT,PART,P_PWHERE PART.P#=P_P .P# AND P_P .J#=PROJECT.J#CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)AS SELECTPART .P #,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITYFROM PART,P_S,SUPPLIERWHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#(3 )试在上述两个视图的基础上进行数据查询：1)检索上海的供应商所供应的零件的编号和名字。