数据库第三章所有例题参考答案

11级信管，保密，图档上机考试题目与参考答案3.3Simple Select Statements1.EXAMPLE 3.3.1find the aid values and names of agents that are based in New York. select aid, aname from agents where city=’New York’;2.EXAMPLE3.3.3Retrieve all pid values of parts for which orders are placed.select distinct pid from orders;3.EXAMPLE 3.3.4retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent.select distinct ame,agents.anamefrom customers,orders,agentswhere customers.cid=orders.cid and orders.aid=agents.aid;4.EXAMPLE 3.3.6all pairs of customers based in the same city.select c1.cid, c2.cidfrom customers c1, customers c2where c1.city = c2.city and c1.cid < c2.cid;5.EXAMPLE 3.3.7find pid values of products that have been ordered by at least twocustomers.select distinct x1.pidfrom orders x1, orders x2where x1.pid = x2.pid and x1.cid < x2.cid;6.EXAMPLE 3.3.8Get cid values of customers who order a product for which an order is also placed by agent a06.select distinct y.cidfrom orders x, orders ywhere y.pid = x,pid and x.aid = ‘a06’;3.4Subqueries7.EXAMPLE 3.4.1Get cid values of customers who place orders with agents in Duluth or Dallas.select distinct cid from orderswhere aid in (select aid from agentswhere city= ‘Duluth’ or city = ‘Dallas’)8.EXAMPLE 3.4.2to retrieve all information concerning agents based in Duluth or Dallas (very close to the Subquery in the previous example).select * from agentswhere city in (‘Duluth’, ‘Dallas’ );or select *from agentswhere city = ‘Duluth’ or city = ‘Dallas’;9.EXAMPLE 3.4.3to determine the names and discounts of all customers who place orders through agents in Duluth or Dallas.select distinct cname, discnt from customerswhere cid in (select cid from orders where aid in(select aid from agents where city in (‘Duluth’, ‘Dallas’ ))); 10.EXAMPLE 3.4.4to find the names of customers who order product p05.select distinct cname from customers, orderswhere customers.cid = orders.cid and orders.pid = ‘p05’or select disti nct cname from customers where ‘p05’ in(select pid from orders where cid = customers.cid);11.EXAMPLE 3.4.5Get the names of customers who order product p07 from agent a03. select distinct cname from customerswhere cid in (select cid from orders where pid = ‘p07’ and aid = ‘a03’) 12.EXAMPLE 3.4.6to retrieve ordno values for all orders placed by customers in Duluth through agents in New York.select ordno from orders x where exists(select * from customers c, agents awhere c.cid = x.cid and a.aid = x.aid and c.city = ‘Duluth’ anda.city=‘New York’);13.EXAMPLE 3.4.7find aid values of agents with a minimum percent commission.select aid from agents where percent = (select min(percent) from agents);14.EXAMPLE 3.4.8find all customers who have the same discount as that of any of the customers in Dallas or Boston.select cid, cname from customerswhere discnt = some (select discnt from customerswhere city = ‘Dallas’ or city = ‘Boston’);15.EXAMPLE 3.4.9Get cid values of customers with discnt smaller than those of any customers who live in Duluth.select cid from customerswhere discnt <all (select discnt from customerswhere city = ‘Duluth’);16.EXAMPLE 3.4.10Retrieve all customer names where the customer places an order through agent a05.select distinct ame from customers cwhere exists (select * from orders xwhere c.cid = x.cid and x.aid = ‘a05’);or select distinct ame from customers c, orders xwhere c.cid = x.cid and x.ai d = ‘a05’ ;17.EXAMPLE 3.4.11Get cid values of customers who order both products p01 and p07. select distinct cid from orders xwhere pid = ‘p01’ and exsits (select * from orderswhere cid = x.cid and pid = ‘p07’);orselect distinct x.cid from orders x, orders ywhere x.pid = ‘p01’ and x.cid = y.cid and y.pid = ‘p07’;18.EXAMPLE 3.4.12Retrieve all customer names where the customer does not place an order through agent a05.select distinct ame from customers cwhere not exists (select * from orders xwhere c.cid = x.cid and x.aid = ‘a05’);19.EXAMPLE 3.4.13retrieving all customer names where the customer does not place an order through agent a05, but using the two equivalent NOT IN and <>ALLpredicates in place of NOT EXISTS.select distinct ame from customers cwhere c.cid not in (select cid from orders where aid = ‘a05’);or select ame from customers cwhere c.cid <>all (select cid from orders where aid = ‘a05’);20.EXAMPLE 3.4.14Find cid values of customers who do not place any order through agent a03.select distinct cid from orders xwhere not exists (select * from orderswhere cid = x.cid and aid = ‘a03’);orselect cid from customers cwhere not exists (select * from orderswhere cid = c.cid and aid = ‘a03’);21.EXAMPLE 3.4.15Retrieve the city names containing customers who order product p01. select distinct city from customers where cid in(select cid from orders where pid = ‘p01’);or select distinct city from customers where cid =some(select cid from orders where pid = ‘p01’);or select distinct city from customers c where exsits(select * from orders where cid = c.cid and pid = ‘p01’);or select distinct city from customers c, orders xwhere x.cid = c.cid and x.pid = ‘p01’;or select distinct city from customers c where ‘p01’ in(select pid from orders where cid = c.cid);3.5UNION Operators and FOR ALL Conditions 22.EXAMPLE 3.5.1to create a list of cities where either a customer or an agent, or both, is based.select city from customersunion select city from agents;23.EXAMPLE 3.5.2Get the cid values of customers who place orders with all agents based in New York.select c.cid from customers cwhere not exsits(select * from agents awhere a.city = ‘New York’ and not exsits(select * from orders xwhere x.cid = c.cid and x.aid = a.aid));24.EXAMPLE 3.5.3Get the aid values of agents in New York or Duluth who place orders forall products costing more than a dollar.select aid from agents awhere (a.city = ‘New York’ or a.city = ‘Duluth’)and not exsits(select p.pid from products pwhere p.price > 1.00 and not exsits(select * from orders xwhere x.pid = p.pid and x.aid = a.aid));25.EXAMPLE 3.5.4Find aid values of agents who place orders for product p01 as well as for all products costing more than a dollar.select a.aid from agents a where a.aid in(select aid from orders where pid = ‘p01’)and not exsits (select p.pid from products pwhere p.price > 1.00 and not exsits (select * from orders xwhere x.pid = p.pid and x.aid = a.aid));or select distinct y.aid from orders ywhere y.pid = ‘p01’ and not exsits(select p.pid from products pwhere p.price > 1.00 and not exsits(select * from orders xwhere x.pid = p.pid and x.aid = y.aid));26.EXAMPLE 3.5.6Find pid values of products supplied to all customers in Duluth.select pid from products pwhere not exsits(select c.cid from customers cwhere c.city = ‘Duluth’and not exists(select * from orders xwhere x.pid = p.pid and x.cid = c.cid));3.7 Set Functions in SQL27.EXAMPLE 3.7.1determine the total dollar amount of all orders.select sum(dollars) as totaldollars from orders28.EXAMPLE 3.7.2To determine the total quantity of product p03 that has been ordered. select sum(qty) as TOTAL from orders where pid=’p03’29.EXAMPLE 3.7.4Get the number of cities where customers are based.select count(distinct city) from customers30.EXAMPLE 3.7.5List the cid values of alt customers who have a discount less than the maximum discount.select cid from customerswhere discnt < (select max(discnt) from customers)31.EXAMPLE 3.7.6Find products ordered by at least two customers.select p.pid from products pwhere 2 <=(select count(distinct cid) from orders where pid=p.pid)图档的学生的上机考查的考题到此为止___________________________________________________________ ___________________________________________________________ 信管，保密的学生上机考查还包括下面的题目32.EXAMPLE 3.7.7Add a row with specified values for columns cid, cname, and city (c007, Windix, Dallas, null)to the customers table.insert into customers(cid, cname, city)values (‘c007’, ‘Windix’, ‘Dallas’)33.EXAMPLE 3.7.9After inserting the row (c007, Windix, Dallas, null) to the customers table in Example 3.7.7, assume that we wish to find the average discount of all customers.select avg(discnt) from customers3.8 Groups of Rows in SQL34.EXAMPLE 3.8.1to calculate the total product quantity ordered of each individual product by each individual agent.select pid, aid, sum(qty) as TOTAL from ordersgroup by pid, aid35.EXAMPLE 3.8.2Print out the agent name and agent identification number, and the product name and product identification number, together with the total quantity each agent supplies of that product to customers c002 and c003.select aname, a.aid, pname, p.pid, sum(qty)from orders x, products p, agents awhere x.pid = p.pid and x.aid = a.aid and x.cid in (‘c002’, ‘c003’)group by a.aid, a.aname, p.pid, p.pname36.EXAMPLE 3.8.3Print out all product and agent IDs and the total quantity ordered of the product by the agent, when this quantity exceeds 1000.select pid, aid, sum(qty) as TOTAL from ordersgroup by pid, aidhaving sum(qty) > 100037.EXAMPLE 3.8.4Provide pid values of all products purchased by at least two customers. select distinct pid from ordersgroup by pidhaving count(distinct cid) >= 23.9 A Complete Description of SQL Select38.EXAMPLE 3.9.1List all customers, agents, and the dollar sales for pairs of customers and agents, and order the result from largest to smallest sales totals. Retain only those pairs for which the dollar amount is at least equal to 900.00. select ame, c.cid, a.aname, a.aid, sum(dollars) as casalesfrom customers c, orders o, agents awhere c.cid = o.cid, and a.aid = o.aidgroup by ame, c.cid, a.aname, a.aidhaving sum(o.dollars) >= 900.00order by casales desc39.EXAMPLE 3.9.2listed the cid values of all customers with a discount less than the maximum discount.select cid from customerswhere discnt < (select max(discnt) from customers)40.EXAMPLE 3.9.3Retrieve the maximum discount of all customers.select max(discnt) from customers;select distinct discnt from customers cwhere discnt >= all (select discnt from customers dwhere d.cid<>c.cid)41.EXAMPLE 3.9.4Retrieve all data about customers whose cname begins with the letter “A”.select * from customers where cname like ‘A%’42.EXAMPLE 3.9.5Retrieve cid values of customers whose cname does not have a third letter equal to “%”.select cid from customers where cname not like ‘__[%]’43.EXAMPLE 3.9.6Retrieve cid values of customers whose cname begins “Tip_” and has an arbitrary number of characters following.select cid from customers where cname like ‘TIP\[_]%’44.EXAMPLE 3.9.7Retrieve cid values of customers whose cname starts with the sequence “ab\”.select cid from customers where cname like ‘ab\%’3.10 Insert, Update, and Delete Statements 45.EXAMPLE 3.10.1Add a row with specified values to the orders table, setting the qty and dollars columns null.insert into orders (ordno, month, cid, aid, pid)values (1107, ‘aug’, ‘c006’, ‘a04’, ‘p01’)46.EXAMPLE 3.10.2Create a new table called swcusts of Southwestern customers, and insert into it all customers from Dallas and Austin.create table swcusts (cid char(4) not null,cname varchar(13),city varchar(20),discnt real);insert into swcustsselect * from customerswhere city in (‘Dallas’, ‘Austin’)47.EXAMPLE 3.10.3Give all agents in New York a 10% raise in the percent commission they earn on an order.update agents set percent = 1.1 * percent where city = ‘New York’48.EXAMPLE 3.10.4Give all customers who have total orders of more than $1000 a 10% increase in the discnt.update agents set percent = 1.1 * discntwhere cid in(select cid from orders group by cid having sum(dollars) > 1000) 49.EXAMPLE 3.10.6Delete all agents in New York.delete from agents where city = ‘New York’50.EXAMPLE 3.10.7Delete all agents who have total orders of less than $600.Delete from agents where aid in(select aid from ordersGroup by aidHaving sum(dollars)<600)51.EXAMPLE 3.11.2Retrieve the names of customers who order products costing $0.50. delete from agents where aid in(select aid from orders group by aid having sum(dollars)<600)（完）。

第3章习题解答1．选择题（1）表设计器的“允许空”单元格用于设置该字段是否可输入空值，实际上就是创建该字段的（D）约束。

A．主键B．外键C．NULL D．CHECK（2）下列关于表的叙述正确的是（C）。

A．只要用户表没有人使用，则可将其删除B．用户表可以隐藏C．系统表可以隐藏D．系统表可以删除（3）下列关于主关键字叙述正确的是（ A ）。

A．一个表可以没有主关键字B．只能将一个字段定义为主关键字C．如果一个表只有一个记录，则主关键字字段可以为空值D．都正确（4）下列关于关联叙述正确的是（ C ）。

A．可在两个表的不同数据类型的字段间创建关联B．可在两个表的不同数据类型的同名字段间创建关联C．可在两个表的相同数据类型的不同名称的字段间创建关联D．在创建关联时选择了级联更新相关的字段，则外键表中的字段值变化时，可自动修改主键表中的关联字段（5）CREATE TABLE语句（C ）。

A．必须在数据表名称中指定表所属的数据库B．必须指明数据表的所有者C．指定的所有者和表名称组合起来在数据库中必须唯一D．省略数据表名称时，则自动创建一个本地临时表（6）删除表的语句是（A）。

A．Drop B．Alter C．Update D．Delete （7）数据完整性不包括（B ）。

A．实体完整性B．列完整性C．域完整性D．用户自定义完整（8）下面关于Insert语句的说法正确的是（A ）。

A．Insert一次只能插入一行的元组B．Insert只能插入不能修改C．Insert可以指定要插入到哪行D．Insert可以加Where条件（9）表数据的删除语句是（ A ）。

A．Delete B．Inser C．Update D．Alter （10）SQL数据定义语言中，表示外键约束的关键字是（B ）。

A．Check B．Foreign Key C．Primary Key D．Unique2．填空题（1）数据通常存储在表中，表存储在数据库文件中，任何有相应权限的用户都可以对之进行操作。

三、设计题1.(1)SELECT BAuth FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BName=’操作系统’ AND PName=’高等教育出版社’(2)查找为作者“张欣”出版全部“小说”类图书的出版社的电话。

SELECT PTel FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BType =’小说’ AND BAuth=’张欣’(3)查询“电子工业出版社”出版的“计算机”类图书的价格，同时输出出版社名称及图书类别。

SELECT BPrice, PName, BType FROM Book, PublishWHERE Book.PNo= Publish.PNo AND PName =’电子工业出版社’ AND BType =’计算机’(4)查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息。

SELECT * FROM BookWHERE BName =’高等数学’AND BPrice<ANY(SELECT BPrice FROM Book,PublishWHERE Book.PNo= Publish.PNo AND PName =’人民邮电出版社’ AND BName =’高等数学’）AND PName <>’人民邮电出版社’(5)查找书名中有“计算机”一词的图书的书名及作者。

SELECT BName, BAuth FROM BookWHERE BName LIKE’%计算机%’(6)在“图书”表中增加“出版时间”（BDate）项，其数据类型为日期型。

ALTER TABLE BookADD BDate datetime(7)在“图书”表中以“作者”建立一个索引。

CREATE INDEX Name ON Book(BAuth) desc2.(1)建立存书表和销售表。

Exercise 3.1.1Answers for this exercise may vary because of different interpretations. Some possible FDs:Social Security number Area code stateStreet address, city, statenamezipcodePossible keys:{Social Security number, street address, city, state, area code, phone number}Need street address, city, state to uniquely determine location. A person could have multiple addresses. The same is true for phones. These days, a person could have a landline and a cellular phoneExercise 3.1.2Answers for this exercise may vary because of different interpretations Some possible FDs:ID x-position, y-position, z-position ID x-velocity, y-velocity, z-velocity x-position, y-position, z-position IDPossible keys:{ID}{x-position, y-position, z-position}The reason why the positions would be a key is no two molecules can occupy the same point. Exercise 3.1.3a(n-1)1. Thus, there are 2 such subsets, sincen may independently be chosen in or out.Exercise 3.1.3b1 or A 2. There are2 (n-1) such subsets when (n-2)2 through A n . There are 2 such subsets when3 through A n . We do not count A 1 in these subsets becausethey are already counted in the first group of subsets. The total number of subsets is 2 (n-1) + 2 (n-2) .Exercise 3.1.3cThe superkeys are any subset that contains {A 1,A 2} or {A 3,A 4}. There are 2 such subsets when considering {A 小2} and the n-2 attributes A 3 through A n . There are 2(n-2)— 2 (n-4) suchsubsets when considering {A 3,A 4} and attributes A5 through A n along with the individualThe superkeys are any subset that contains A each of the n-1 attributes A 2 through AThe superkeys are any subset that contains A considering A 1 and the n-1 attributes A considering A 2 and the n-2 attributes Aattributes A 1 and A 2. We get the 2 (n-4) term because we have to discard the subsets thatcontain the key {A 1,A2} to avoid double counting. The total number of subsets is 2 (n-2) + 2(n-2) _ 2(n-4)Exercise 3.1.3dThe superkeys are any subset that contains {A 1,A2} or {A 1,A3}. There are 2 (n-2)such subsets when considering {A 1,A2} and the n-2 attributes A 3 through A n. There are 2 (n-3)such subsets when considering {A 1,A3} and the n-3 attributes A 4 through A n We do not count A 2 in these subsets because they are already counted in the first group of subsets. The total numberof subsets is 2(n-2)+ 2(n-3).Exercise 3.2.1aWe could try inference rules to deduce new dependencies until we are satisfied we have them all. A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have {A} + = A, {B} + = B, {C} + = ACD, and {D} + = AD. Thus, the only new dependency we get with a single attribute on the left is C A.Now consider pairs of attributes:{AB} + = ABCD, so we get new dependency AB D. {AC} + = ACD, and AC D is nontrivial. {AD} = AD, so nothing new. {BC} + = ABCD, so we get BC A, and BC D. {BD} + = ABCD, giving us BD A and BD C. {CD} + = ACD, giving CD A.+For the triples of attributes, {ACD} = ACD, but the closures of the other sets are eachABCD. Thus, we get new dependencies ABC D, ABD C, and BCD A.Since {ABCD} + = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above are:C A, AB D, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.Exercise 3.2.1bFrom the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.2.1cThe superkeys are all those that contain one of those three keys. That is, a superkey that is not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD.Exercise 3.2.2ai) For the single attributes we have {A} += ABCD, {B} + = BCD, {C} + = C, and {D} + = D. Thus,the new dependencies are AC and A D.Now consider pairs of attributes:{AB}+ = ABCD, {AC} + = ABCD, {AD} + = ABCD, {BC} + = BCD, {BD} + = BCD, {CD} + = CD. Thus the new dependencies are AB C, AB D, AC B, AC D, AD B, AD C, BC D and BD C.= BCD, but the closures of the other sets are eachABCD. Thus, we get new dependencies ABCSince {ABCD} + = ABCD, we get no new dependencies.The collection of 13 new dependencies mentioned above are:Now consider pairs of attributes:{AB}+ = ABCD, {AC} + = AC, {AD} + = ABCD, {BC} + = ABCD, {BD} + = BD, {CD} + = ABCD. Thus the new dependencies are ABD, AD C, BC A and CD B.For the triples of attributes, all the closures of the sets are each ABCD. Thus, we get new dependencies ABC D, ABD C, ACD B and BCD A.+Since {ABCD} = ABCD, we get no new dependencies.The collection of 8 new dependencies mentioned above are:AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A. += ABCD, {B} + = ABCD, {C} + = ABCD, and {D} + = C, A D, B D, BA, C A, C B, D B and D C.Since all the single attributes ' closures are ABCD, any superset of the singleattributes will also lead to a closure of ABCD. Knowing this, we can enumerate the rest of the newdependencies.The collection of 24 new dependencies mentioned above are:A C, A D,B D, B A,C A, C B,D B, D C, AB C, AB D, AC B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACD B and BCD A. Exercise 3.2.2bFor the triples of attributes, {BCD} D, ABD C, and ACD B. A C, A D, AB C, AB D, AC B, AC D, AD ACD B. ii)For the single attributes we have {A} +there are no new dependencies.B, AD C, BC D, BD C, ABC D, ABD C and = A, {B} + = B, {C} + = C, and {D} + = D. Thus, iii) For the single attributes we have {A} ABCD. Thus, the new dependencies are Ai) From the analysis of closures in 3.2.2a(i), we find that the only key is A. All other sets either do not have ABCD as the closure or contain A.ii) F rom the analysis of closures 3.2.2a(ii), we find that AB, AD, BC, and CD are keys. All other sets either do not have ABCD as the closure or contain one of these four sets.iii) From the analysis of closures 3.2.2a(iii), we find that A, B, C and D are keys. All other sets either do not have ABCD as the closure or contain one of these four sets. Exercise 3.2.2ci) The superkeys are all those sets that contain one of the keys in 3.2.2b(i). The superkeys are AB, AC, AD, ABC, ABD, ACD, BCD and ABCD.ii) The superkeys are all those sets that contain one of the keys in 3.2.2b(ii). The superkeys are ABC, ABD, ACD, BCD and ABCD.iii) The superkeys are all those sets that contain one of the keys in 3.2.2b(iii). The superkeys are AB, AC, AD, BC, BD, CD, ABC, ABD, ACD, BCD and ABCD. Exercise 3.2.3aSi nee A 1A 2 …AC con tai ns A 4 …A n , then the closure of A 1A …A n C contains B. Thus it followsthat A 1A 2 …A n C B. Exercise 3.2.3bFrom 3.2.3a, we know that A1A 2…A n C B. Using the concept of trivial dependencies, we can show that A 1A 2…A n C C. Thus A 1A 2 …A n C BC.Exercise 3.2.3cFrom A 1A 2…代巳匕…E , we know that the closure contains B BB 2…B m The B 1B 2…B m and the E 1E 2…E combine to form the C AA 2 …A n EE …E j con tai ns D as well. Thus, A 1A 2 …AE 1E 2 …E Exercise 3.2.3dFrom A 1A 2 …A n C i C 2 …C, we kn ow that the closure con tai ns B B B 2…B m The C 1C 2…C< also tell us that the closure of A AA 2…A n C i C 2…C k BiB>…B<DD …D. Exercise 3.2.4aIf attribute A represented Social Security Number and B represented a person ' s name,then we would assume A B but B A would not be valid because there may be many people with the same name and different Social Security Numbers. Exercise 3.2.4b1B 2…B m because of the FD A 1A 2 …A n 1C 2 …C k . Thus the closure ofD.1B 2…B m because of the FD A 1A 2 …A n1A 2 …AGG …C con tai ns D 1C 2 …D. Thus,Let attribute A represent Social Security Number, B represent gender and C represent name.Surely Social Security Number and gender can uniquely identify a person ' s name (i.e. AB C). A Social Security Number can also uniquely identify a person ' s name (i.e. A C). However, gender does not uniquely determine a name (i.e. B C is not valid).Exercise 3.2.4cLet attribute A represent latitude and B represent longitude. Together, both attributescan uniquely determine C, a point on the world map (i.e. AB C). However, neither A nor B can uniquely identify a point (i.e. A C and B C are not valid).Exercise 3.2.5Give n a relati on with attributes A \A…A n, we are told that there are no fun ctio naldependencies of the form B i E2…B-i C where B iB…B-i is n-1 of the attributes from A 1A2…A and C is the remaining attribute from A 1A2•…A n. In this case, the set B 1庄・…B>1 and any subset do not functionally determine C. Thus the only functional dependencies that we can make are ones where C is on both the left and right hand sides. All of these functional dependencies would be trivial and thus the relation has no nontrivial FD 's.Exercise 3.2.6Let's prove this by using the contrapositive. We wish to show that if X + is not a subsetof Y +, then it must be that X is not a subset of Y.If X + is not a subset of Y +, there must be attributes A 1A2…A in X + that are notin Y +. If any of these attributes were originally in X, then we are done because Y does not contain any of the A1A2…A. However, if the A iA…A were added by the closure, then we must examine the case further. Assume that there was some FD C 1C2…C m A1A2…A where A 1A2…A issome subset of A i A e …A. It must be then that C iG…C m or some subset of C 1C2 …C m is in X. However, the attributes C 1C2•…C m cannot be in Y because we assumed that attributes A 1A2•…A ++are only in X and are not in Y . Thus, X is not a subset of Y.++By proving the contrapositive, we have also proved if X ? Y, then X ? Y .Exercise 3.2.7+The algorithm to find X is outlined on pg. 76. Using that algorithm, we can prove that(X+)+ = X+. We will do this by using a proof by contradiction.+ + + + +Suppose that (X ) MX. Then for (X ) , it must be that some FD allowed additional attributes to be added to the original set X +. For example, X + A where A is someattribute not in X +. However, if this were the case, then X + would not be the closure of X.The closure of X would have to include A as well. This contradicts the fact that we weregiven the closure of X,X closure of X. . Therefore, it must be that (X) = X or else X is not theExercise 3.2.8a If all sets of attributes are closed, then there cannot be any nontrivial functionaldependencies. Suppose A 1A 2...A n B is a nontrivial dependency. Then {A 1A 2...A n } + contains B and thus A 1A 2...A n is not closed. Exercise 3.2.8b and {A,B,C,D}, then the following FDs hold: A BA C A DB AB CB DC AC BC D D AD BD CAB C AB DAC B AC DAD B AD CBC A BC DBD A BD CCD A CD BIf the only closed sets are ABC D ABD C ACD B BCD A Exercise 3.2.8c If the only closed sets are {A,B} and {A,B,C,D}, then the following FDshold:AB BA C A C B C D D A D B D CAC B AC D AD B AD C BC A BC D BD A BD C CD A CD B ABC DABD CACD BBCD AExercise 3.2.9We can think of this problem as a situation where the attributes A,B,C represent cities and the functional dependencies represent one way paths between the cities. The minimal bases are the minimal number of pathways that are needed to connect the cities. We do not want to create another roadway if the two cities are already connected.The systematic way to do this would be to check all possible sets of the pathways. However, we can simplify the situation by noting that it takes more than two pathways to visit the two other cities and come back. Also, if we find a set of pathways that is minimal, adding additional pathways will not create another minimal set.The two sets of minimal bases that were given in example 3.11 are:{A B, B C, C A}{A B, B A, B C, C B}The additional sets of minimal bases are:{C B, B A, A C}{A B, A C, B A, C A}{A C, B C, C A, C B}Exercise 3.2.10aWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A} +=A{B} +=B+{C} +=ACE{AB} +=ABCDE+{AC} +=ACE{BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: C A and AB C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted from our list.Exercise 3.2.10bWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A} +=AD{B} +=B{C} +=C{AB} +=ABDE{AC} =ABCDE {BC} +=BCWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC B.Exercise 3.2.10cWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: {A} +=A {B} +=B {C} +=C {AB} +=ABD {AC} +=ABCDE {BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC Band BC A. Exercise 3.2.10dWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: {A} +=ABCDE {B} +=ABCDE {C} +=ABCDE {AB} +=ABCDE+{AC} +=ABCDE {BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: A B,B C and C A. Exercise 3.2.11For step one of Algorithm 3.7, suppose we have the FD ABC DE. We want to use Armstrong s Axioms to show that ABC D and ABC E follow. Surely the functional dependencies DE and DE E hold because they are trivial and follow the reflexivity property.Using the transitivity rule, we can derive the FD ABC D from the FDs ABC we can do the same for ABCDE and DE E and derive the FD ABCFor steps two through four of Algorithm 3.7, suppose we have the initial set of attributes of the closure as ABC. Suppose also that we have FDs C to Algorithm 3.7, the closure should become ABCDE. Taking the FD C sides with attributes AB we get the FD ABC ABD. We can use the splitting method in step one to get the FD ABCD. Since D is not in the closure, we can add attribute D. Takingthe FD D E and augmenting both sides with attributes ABC we get the FD ABCDABCDE.DE and DE D. Likewise, E. D and D E. According D and augmenting bothUsing again the splitting method in step one we get the FD ABCD E. Since E is not in the closure, we can add attribute E.Given a set of FDs, we can prove that a FD F follows by taking the closure of the left side of FD F. The steps to compute the closure in Algorithm 3.7 can be mimicked by Armstrong ' s axioms and thus we can prove F from the given set of FDs using Armstrong ' s axioms.Exercise 3.3.1aIn the solution to Exercise 3.2.1 we found that there are 14 nontrivial dependencies, including the three given ones and eleven derived dependencies. They are: C A, C D,D A, AB D, AB C, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are: C A, C D,D A, AC D, and CD A.One choice is to decompose using the violation C D. Using the above FDs, we get ACD andBC as decomposed relations. BC is surely in BCNF, since any two-attribute relation is.Using Algorithm 3.12 to discover the projection of FDs on relation ACD, we discover that ACD is not in BCNF since C is its only key. However, D A is a dependency that holds inABCD and therefore holds in ACD. We must further decompose ACD into AD and CD. Thus, the three relations of the decomposition are BC, AD, and CD.Exercise 3.3.1bBy computing the closures of all 15 nonempty subsets of ABCD, we can find all the nontrivial FDs. They are BC, B D, AB C, AB D, BC D, BD C, ABC D and ABD C. Fromthe closures we can also deduce that the only key is AB. Thus, any dependency above that does not contain AB on the left is a BCNF violation. These are: B C, B D, BC D andBD C.One choice is to decompose using the violation B C. Using the above FDs, we get BCD andAB as decomposed relations. AB is surely in BCNF, since any two-attribute relation is.Using Algorithm 3.12 to discover the projection of FDs on relation BCD, we discover that BCD is in BCNF since B is its only key and the projected FDs all have B on the left side.Thus the two relations of the decomposition are AB and BCD.Exercise 3.3.1cIn the solution to Exercise 3.2.2(ii), we found that there are 12 nontrivial dependencies, including the four given ones and the eight derived ones. They are AB C, BC D, CD A,AD B, AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are AB, AD, BC, and CD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF. Exercise 3.3.1dIn the solution to Exercise 3.2.2(iii), we found that there are 28 nontrivial dependencies, including the four given ones and the 24 derived ones. They are A C D, D A, A C, A D, B D, B A, C A, C B, D B, D C, AB C, AB D, ACWe also found out that the keys are A,B,C,D. Thus, any dependency above that does not have one of these attributes on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF. Exercise 3.3.1eBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD ABE C, ABE D, ADE C, BCE D, BDE C, ABCE D, and ABDE C. From the closures we can also deduce that the only key is ABE. Thus, any dependency above that does not containABE on the left is a BCNF violation. These are: AB C, DE C, B D, AB D, BC D, BEBE D, ABC D, ABD C, ADE C, BCE D and BDE C. One choice is to decompose using the violation ABC. Using the above FDs, we get ABCD and ABE as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation ABCD, we discover that ABCD is not in BCNF since AB is its only key and the FD B D follows for ABCD. Using violation BD to further decompose, we get BD and ABC asdecomposed relations. BD is in BCNF because it is a two-attribute relation. Using Algorithm 3.12 again, we discover that ABC is in BCNF since AB is the only key and AB Cis the only nontrivial FD. Going back to relation ABE, following Algorithm 3.12 tells us that ABE is in BCNFbecause there are no keys and no nontrivial FDs. Thus the three relations of the decomposition are ABC, BD and ABE. Exercise 3.3.1fBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all thenontrivial FDs. They are: C B, C D, C E, D B, D E, AB C, AB D, AB E, AC B, AC D, AC E, AD B, AD C, AD E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, ABC D, ABC E, ABD C, ABD E, ABE C, ABE D, ACD B, ACD E, ACE B, ACE D, ADE B, ADE C, BCD E, BCE D, CDE B, ABCD E, ABCE D, ABDE C and ACDE B. From the closures we can also deduce that the keys are AB, AC and AD. Thus, any dependency above that does not contain one of the above pairs on the left is a BCNF violation. These are: CB, C D,B, B C,B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACDB and BCD A.C,C,C E,D B, D E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, BCD E, BCE D and CDE B. One choice is to decompose using the violatio n DB. Us ing the above FDs, we get BDE andABC as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation BDE, we discover that BDE is in BCNF since D, BD, DE are the only keys and all the projected FDs con tain D, BD, or DE in the left side. Goi ng back to relati on ABC, following Algorithm 3.12 tells us that ABC is not in BCNF because since AB and AC are its on ly keys and the FD C B follows for ABC. Usi ng violatio n C B to further decompose, weget BC and AC as decomposed relati ons. Both BC and AC are in BCNF because they are two- attribute relations. Thus the three relations of the decomposition are BDE, BC and AC. Exercise 3.3.2Yes, we will get the same result. Both A B and A BC have A on the left side and part ofthe process of decompositi on in volves finding {A}+to form one decomposed relati on and Aplus the rest of the attributes not in {A} +as the sec ond relati on. Both cases yield thesame decomposed relati ons. Exercise 3.3.3Yes, we will still get the same result. Both A part of the process of decompositi on in volves finding {A} relation and A plus the rest of the attributes not in {A} cases yield the same decomposed relati ons. Exercise 3.3.4This is take n from Example 3.21 pg. 95.Suppose that an in sta nee of relati on R only contains two tuples.The projections of R onto the relations with schemas {A,B} and {B,C} are:B and A BC have A on the left side and +to form one decomposed +as the sec ond relati on. BothThe result of the natural join is not equal to the original relation R.Exercise 3.4.1aSince there is not an un subscripted row, the decompositi on for R is no t lossless for this set of FDs.We can use the final tableau as an in sta nee of R as an example for why the join is not lossless. The projected relati ons are:This is the in itialA.The joined relati on is:Exercise 3.4.1bThis is the in itial tableau:This is the final tableau after applying FDs ACSince there is an un subscripted row, the decompositi on for R is lossless for this set of FDs. Exercise 3.4.1cThis is the in itial tableau:E and BC DD, D E and B D.Since there is an un subscripted row, the decompositi on for R is lossless for this set of FDs.Exercise 3.4.1dThis is the in itial tableau:This is the final tableau after applyi ng FDs A D, CD E and E DSince there is an un subscripted row, the decompositi on for R is lossless for this set of FDs.Exercise 3.4.2When we decompose a relati on into BCNF, we will project the FDs onto the decomposed relati ons to get new sets of FDs. These depe nden cies are preserved if the union of these new sets is equivale nt to the origi nal set of FDs.For the FDs of 3.4.1a, the depe nden cies are not preserved. The union of the new sets of FDs is CE A. However, the FD B E is not in the union and cannot be derived. Thus the two sets of FDs are not equivale nt.For the FDs of 3.4.1b, the depe nden cies are preserved. The union of the new sets of FDs is AC E and BC D. This is precisely the same as the origi nal set of FDs and thus the two sets of FDs are equivale nt.For the FDs of 3.4.1c, the dependencies are not preserved. The union of the new sets ofFDs is B D and A E. The FDs A D and D E are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.For the FDs of 3.4.1d, the dependencies are not preserved. The union of the new sets ofFDs is AC E. However, the FDs A D, CD E and E D are not in the union and cannot bederived. Thus the two sets of FDs are not equivalent.Exercise 3.5.1aIn the solution to Exercise 3.3.1a we found that there are 14 nontrivial dependencies.They are: C A, C D, D A, AB D, AB C, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.We also learned that the three keys were AB, BC, and BD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1bIn the solution to Exercise 3.3.1b we found that there are 8 nontrivial dependencies.They are B C, B D, AB C, AB D, BC D, BD C, ABC D and ABD C.We also found out that the only key is AB. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NF violations are B C, B D, BC D and BD C.Using algorithm 3.26, we can decompose into relations using the minimal basis B C andB D. The resulting decomposed relations would be BC and BD. However, none of these two sets of attributes is a superkey. Thus we add relation AB to the result. The final set of decomposed relations is BC, BD and AB.Exercise 3.5.1cIn the solution to Exercise 3.3.1c we found that there are 12 nontrivial dependencies.They are AB C, BC D, CD A, AD B, AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are AB, AD, BC, and CD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1d In the solution to Exercise 3.3.1d we found that there are 28 nontrivial dependencies.They are A B, B C, C D, D A, A C, A D, B D, B A, C A, C B, D B, D C, AB C, AB D, AC B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are A,B,C,D. Since all the attributes on the right sidesof the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1eIn the solution to Exercise 3.3.1e we found that there are 16 nontrivial dependencies.They are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD C, ABE C, ABE D, ADE C, BCE D, BDE C, ABCE D, and ABDE C.We also found out that the only key is ABE. FDs where the left side is not a superkey orthe attributes on the right are not part of some key are 3NF violations. The 3NF violations are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD C, ADE C,BCE D and BDE C.Using algorithm 3.26, we can decompose into relations using the minimal basis AB C,DE C and B D. The resulting decomposed relations would be ABC, CDE and BD. However, none of these three sets of attributes is a superkey. Thus we add relation ABE to the result. The final set of decomposed relations is ABC, CDE, BD and ABE.Exercise 3.5.1fIn the solution to Exercise 3.3.1f we found that there are 41 nontrivial dependencies.They are: C B, C D, C E, D B, D E, AB C, AB D, AB E, AC B, AC D, AC E, AD B, AD C, AD E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, ABC D, ABC E, ABD C, ABD E, ABE C, ABE D, ACD B, ACD E, ACE B, ACE D, ADE B, ADE C, BCD E, BCE D, CDE B, ABCD E, ABCE D, ABDE C and ACDE B.We also found out that the keys are AB, AC and AD. FDs where the left side is not a superkey or theattributes on the right are not part of some key are 3NF violations. The 3NF violations are C E, D E, BC E, BD E, CD E and BCD E.Using algorithm 3.26, we can decompose into relations using the minimal basis AB C, C D,D B and D E. The resulting decomposed relations would be ABC, CD, BD and DE. Since relation ABC contains a key, we can stop with the decomposition. The final set of decomposed relations is ABC, CD, BD and DE.Exercise 3.5.2aThe usual procedure to find the keys would be to take the closure of all 63 nonempty subsets. However, if we notice that none of the right sides of the FDs containsattributes H and S. Thus we know that attributes H and S must be part of any key. We eventually will find out that HS is the only key for the Courses relation.Exercise 3.5.2bThe first step to verify that the given FDs are their own minimal basis is to check to see if any of the FDs can be removed. However, if we remove any one of the five FDs, the remaining four FDs do not imply the removed FD.The second step to verify that the given FDs are their own minimal basis is to check to see if any of theleft sides of an FD can have one or more attributes removed without losing the dependencies. However, this is not the case for the four FDs that contain two attributes on the left side.Thus, the given set of FDs has been verified to be the minimal basis.Exercise 3.5.2cSince the only key is HS, the given set of FDs has some dependencies that violate 3NF. We also know that the given set of FDs is a minimal basis. Thus the decomposed relations are CT, HRC, HTR, HSR and CSG. Since the relation HSR contains a key, we do not need to add an additional relation. The final set of decomposed relations is CT, HRC, HTR, HSR and CSG.None of the decomposed relations violate BCNF. This can be verified by projecting the given set of FDs onto each of the decomposed relations. All of the projections of FDs have superkeys on their left sides.。

第3章关系数据库1. 试述关系模型的三个组成部分。

解：关系模型的三个组成部分(1) 关系数据模型的数据结构(2) 关系数据模型的操纵与完整性约束(3) 关系数据模型的存储结构2. 解释下列术语的含义：①笛卡尔积；②主码；③候选码；④外码；⑤关系；⑥关系模式；⑦关系数据库解：①笛卡尔积：两个分别为n目和m目的关系R和S的笛卡尔积是一个(n+m)列的元组的集合。

元组的前n列是关系R的一个元组，后m列是关系S的一个元组。

若R有k1个元组，S有K2个元组，则关系R和关系S的笛卡尔积有k1×k2个元组。

记作：R×S={trts|tr∈R⋀ts∈S}②主码：若关系中的某一属性组的值能唯一的标识一个元组，则称该属性组为候选码。

若一个关系有多个候选码，则选定其中一个为主码。

③候选码：若关系中的某一属性组的值能唯一的标识一个元组，则称该属性组为候选码。

④外码：如果关系模式R中的某属性集是另一个关系模式S的主码，则该属性集为关系模式R的外码。

⑤关系：关系是集合论的一个概念，也是关系模型的数据结构，它只包含单一的数据结构——关系。

在关系模型中，现实世界的实体以及实体间的各种联系均用关系来表示。

在用户看来，一个关系就是一张二维表，这种简单的数据结构能够表达丰富的语义。

⑥关系模式：关系的描述称为关系模式。

它可以形式化地表示为R(U，D，DOM，F)其中R为关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，DOM为属性向域的映像集合，F为属性间数据的依赖关系集合。

⑦关系数据库：在关系模型中，实体以及实体之间的联系都是通过关系来表示的。

因此，在一个给定的应用领域中，所有实体以及实体之间的联系所对应的关系的集合就构成一个关系数据库。

3.关系数据库的三个完整性约束是什么？各是什么含义？解：关系模式中有3类完整性约束：实体完整性、参照完整性和用户自定义完整性。

实体完整性：若属性(指一个或一组属性)A是基本关系R的主属性，则A不能取空值。

第3章习题参考答案3.1select readername,workunit,identitycardfrom readerwhere substring(identitycard,7,4)=‘1991’///字符串截取substr(字段名，起始点,个数) 或者select readername,workunit,identitycardfrom readerwhere identitycard like ‘______1991%’六个_3.2select readerno,readername,sexfrom readerwhere workunit=’信息管理学院’3.3select readerno,readername, workunit,bookno,bookname,borrowdatafrom reader,borrow,bookwhere reader.readerno=borrow.reader and borrow.bookno=book.booknoand year(returndata) between 2005 and 2008 and ifreturn =03.4select a.classno,max(price) 最高价格,avg(price) 平均价格from book a,bookclass bwhere a.classno=b.classnogroup by a.classnoorder by 最高价格desc3.5 select * from book where bookname like ‘%数据库%’3.6 select bookno,publishingdata,shopdata,booknameFrom bookWhere year(shopdata) between 2005 and 20083.7 select readerno,readernameForm readerWhere readerno not in (select distinct readerno from borrow where bookno like ‘001%’)3.8 select readerno,bookno,borrowdataForm borrowWhere bookno=’001-000029’3.9 select readernameForm readerWhere readerno not in (select distinct readerno from borrow)3.10 select classname,count（distinct book.classno），sum（shopnum）From book,bookclassWhere book.classno=’001’ and book.classno=bookclass.classnoGroup by book.classno3.11 select classname,sum(shopnum)From book,bookclassWhere book.classno=bookclass.classnoGroup by book.classno3.12 select a.readerno,readername,borrowdata,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowwhere bookno in(select bookno from bookwhere bookname=’离散数学’)) And b.readerno in (select readerno from borrowwhere bookno in(select bookno from bookwhere bookname=’数据库’))3.13 select a.readerno,readername,borrowdata,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd not exists (select * from book where bookno=’002’And not exi s ts (select * from borrowwhere book.bookno=borrow.bookno))3.14 select b.bookno,bookname.borrowdata,returndataFrom reader a,borrow b,book cWhere a.readerno=b.readerno and b.bookno=c.bookno and a.readername=’马永强’3.15 select a.readerno,readername,borrowdata,bookname，returndataFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoand b.workunit=’会计学院‘and c.ifreturn=03.16 select a.readerno,readername,borrowdata,bookname，returndataFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoand a.publishingname=’清华大学出版社‘3.17 select readerno,readername,workunitFrom readerWhere not exist(select * from borrow where reader.readerno=borrow.readerno)3.18 select a.readerno,readername,a.bookno,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowgroup by readernohaving count(*)>=3)order by a.readerno3.19 select a.readerno,readername,a.bookno,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.bookno=c.booknoAnd year(borrowdate) between 2007 and 20083.20 select readerno,readername,workunitFrom readerWhere not exist s(select * from readerwhere readername=’马永强’and not exist s(select * from borrow where reader.readerno=borrow.readerno))3.21 select a.readerno,readername,sum(price)From borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowgroup by readernohaving sum(price)>150)group by a.readerno,readername3.22 select readerno,readername, substring(identitycard,7,4)From readerWhere readerno not in(select readerno from borrow,book,bookclass where book.bookno=borrow.bookno and bookclass.classno=book.classno and bookclass.classname=’经济管理’)3.23 select a.readerno,readername, substring(identitycard,7,4)From borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknogroup by a.readernohaving sum(price)=(select max(sumprice) from (select sum(price) sumpricefrom borrowgroup by readerno) d)3.24 update bookSet price=price+price*0.1 (set price=price*1.1)From book,bookclassWhere book.classno=bookclass.classno and classname=’经济管理’3.28 create view view1AsSelect book.* from book,bookclassWhere book.classno=bookclass.classno and publishingname=’清华大学出版社’and year(publishingdate) between 2008 and 2009 and classname=’计算机类’补充内容--【字符串函数】--字符串截取substr(字段名，起始点,个数)select Name,substr(Name,2,4),substr(Name,0,3),substr(Name,-2,3),substr(Name,-2,1) from t1;--字符串从前面取三个(0开始)select Name,substr(Name,0,3) from t1;--字符串从后面取三个select Name,substr(Name,-3,3),length(Name) 串长度 from t1;SELECT ASCII('A'),ASCII('B') from dual;select CHR(100),CHR(80) from dual;select CONCAT(CHR(65),CONCAT(CHR(67),CHR(98))) from dual;select CHR(65)||CHR(66)||CHR(76) from dual;--将每个单词的第一个字母大写其它字母小写返回。

3-3 习题3在SQL Server中，创建一个名为students且包含有下列几个属性的表。

SNO char(10)；NAME varchar(10)；SEX char(1)；BDATE datetime；DEPT varchar(10)；DORMITORY varchar(10).要求：1.采用两种形式创建表，即用SQL语句和用图形界面的形式来创建。

2.定义必要的约束，包括主键SNO,NAME值不允许为空，且SEX取值为0或1。

【解答】·进入SQL查询分析器建立查询，创建students表的SQL语句如下，操作如图所示。

use mydb /* 假设在mydb库中建表 */create table students(SNO char(10) not NULL primary key,NAME varchar(10) not NULL,SEX char(1) not NULL check(sex='0' or sex='1'),BDATE datetime,DEPT varchar(10),DORMITORY varchar(10))图用SQL语句创建students表·进入企业管理器用基本操作创建students表。

用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。

或者，用右键单击“mydb”数据库下一级的“表”，从弹出的菜单中选择“新建表”。

然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图所示。

图用基本操作创建students表其中，SEX字段取值为0或1，需要建立约束。

操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图所示。

数据库作业第三章习题答案数据库作业第三章习题答案数据库作业是数据库课程中非常重要的一部分，通过完成作业可以帮助学生巩固和加深对数据库知识的理解和应用。

第三章习题主要涉及数据库设计和查询语言的使用。

在本篇文章中，我们将回答第三章习题，并探讨一些相关的概念和技巧。

1. 设计一个关系模式，用于存储学生的基本信息，包括学生编号、姓名、性别、年龄和专业。

请给出该关系模式的定义。

答案：学生（学生编号，姓名，性别，年龄，专业）2. 设计一个关系模式，用于存储课程的信息，包括课程编号、课程名称和学分。

请给出该关系模式的定义。

答案：课程（课程编号，课程名称，学分）3. 设计一个关系模式，用于存储学生选课的信息，包括学生编号、课程编号和成绩。

请给出该关系模式的定义。

答案：选课（学生编号，课程编号，成绩）4. 编写一个SQL查询语句，查询学生的姓名和年龄。

答案：SELECT 姓名, 年龄 FROM 学生;5. 编写一个SQL查询语句，查询选修了某门课程的学生的姓名和成绩。

答案：SELECT 学生.姓名, 选课.成绩FROM 学生, 选课WHERE 学生.学生编号 = 选课.学生编号AND 选课.课程编号 = '某门课程编号';6. 编写一个SQL查询语句，查询某个学生的选课情况，包括课程名称和成绩。

答案：SELECT 课程.课程名称, 选课.成绩FROM 课程, 选课WHERE 课程.课程编号 = 选课.课程编号AND 选课.学生编号 = '某个学生编号';通过以上习题的回答，我们可以看到数据库设计和查询语言的基本应用。

关系模式的定义是数据库设计的基础，它描述了数据表的结构和属性。

在查询语言的使用中，我们可以通过SELECT语句来检索和过滤数据，通过WHERE子句来指定查询条件。

除了上述习题的答案，我们还可以进一步探讨数据库设计的一些原则和技巧。

例如，为了提高数据库的性能和可扩展性，我们可以使用索引来加快数据的检索速度。

Exercise 3.1.1Answers for this exercise may vary because of different interpretations.Some possible FDs:Social Security number → nameArea code → stateStreet address, city, state → zipcodePossible keys:{Social Security number, street address, city, state, area code, phone number}Need street address, city, state to uniquely determine location. A person couldhave multiple addresses. The same is true for phones. These days, a person couldhave a landline and a cellular phoneExercise 3.1.2Answers for this exercise may vary because of different interpretationsSome possible FDs:ID → x-position, y-position, z-positionID → x-velocity, y-velocity, z-velocityx-position, y-position, z-position → IDPossible keys:{ID}{x-position, y-position, z-position}The reason why the positions would be a key is no two molecules can occupy the same point.Exercise 3.1.3aThe superkeys are any subset that contains A1. Thus, there are 2(n-1) such subsets, since each of the n-1 attributes A2 through A n may independently be chosen in or out.Exercise 3.1.3bThe superkeys are any subset that contains A1 or A2. There are 2(n-1) such subsets when considering A1 and the n-1 attributes A2 through A n. There are 2(n-2) such subsets when considering A2 and the n-2 attributes A3 through A n. We do not count A1 in these subsets because they are already counted in the first group of subsets. The total number of subsets is 2(n-1) + 2(n-2).Exercise 3.1.3cThe superkeys are any subset that contains {A1,A2} or {A3,A4}. There are 2(n-2) such subsets when considering {A1,A2} and the n-2 attributes A3 through A n. There are 2(n-2) – 2(n-4) such subsets when considering {A3,A4} and attributes A5 through A n along with the individual attributes A1 and A2. We get the 2(n-4) term because we have to discard the subsets that contain the key {A1,A2} to avoid double counting. The total number of subsets is 2(n-2) +2(n-2) – 2(n-4).Exercise 3.1.3dThe superkeys are any subset that contains {A1,A2} or {A1,A3}. There are 2(n-2) such subsets when considering {A1,A2} and the n-2 attributes A3 through A n. There are 2(n-3) such subsets when considering {A1,A3} and the n-3 attributes A4 through A n We do not count A2 in these subsets because they are already counted in the first group of subsets. The total number of subsets is 2(n-2) + 2(n-3).Exercise 3.2.1aWe could try inference rules to deduce new dependencies until we are satisfied we have them all. A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have {A}+ = A, {B}+ = B, {C}+ = ACD, and {D}+ = AD. Thus, the only new dependency we get with a single attribute on the left is C→A.Now consider pairs of attributes:{AB}+ = ABCD, so we get new dependency AB→D. {AC}+ = ACD, and AC→D is nontrivial. {AD}+= AD, so nothing new. {BC}+ = ABCD, so we get BC→A, and BC→D. {BD}+ = ABCD, giving us BD→A and BD→C. {CD}+ = ACD, giving CD→A.For the triples of attributes, {ACD}+ = ACD, but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC→D, ABD→C, and BCD→A.Since {ABCD}+ = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above are:C→A, AB→D, AC→D, BC→A, BC→D, BD→A, BD→C, CD→A, ABC→D, ABD→C, and BCD→A.Exercise 3.2.1bFrom the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.2.1cThe superkeys are all those that contain one of those three keys. That is, a superkeythat is not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD.Exercise 3.2.2ai) For the single attributes we have {A}+ = ABCD, {B}+ = BCD, {C}+ = C, and {D}+ = D. Thus, the new dependencies are A→C and A→D.Now consider pairs of attributes:{AB}+ = ABCD, {AC}+ = ABCD, {AD}+ = ABCD, {BC}+ = BCD, {BD}+ = BCD, {CD}+ = CD. Thus the new dependencies are AB→C, AB→D, AC→B, AC→D, AD→B, AD→C, BC→D and BD→C.For the triples of attributes, {BCD}+ = BCD, but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC→D, ABD→C, and ACD→B.Since {ABCD}+ = ABCD, we get no new dependencies.The collection of 13 new dependencies mentioned above are:A→C, A→D, AB→C, AB→D, AC→B, AC→D, AD→B, AD→C, BC→D, BD→C, ABC→D, ABD→C and ACD→B.ii) For the single attributes we have {A}+ = A, {B}+ = B, {C}+ = C, and {D}+ = D. Thus, there are no new dependencies.Now consider pairs of attributes:{AB}+ = ABCD, {AC}+ = AC, {AD}+ = ABCD, {BC}+ = ABCD, {BD}+ = BD, {CD}+ = ABCD. Thus the new dependencies are AB→D, AD→C, BC→A and CD→B.For the triples of attributes, all the closures of the sets are each ABCD. Thus, we get new dependencies ABC→D, ABD→C, ACD→B and BCD→A.Since {ABCD}+ = ABCD, we get no new dependencies.The collection of 8 new dependencies mentioned above are:AB→D, AD→C, BC→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.iii) For the single attributes we have {A}+ = ABCD, {B}+ = ABCD, {C}+ = ABCD, and {D}+ = ABCD. Thus, the new dependencies are A→C, A→D, B→D, B→A, C→A, C→B, D→B and D→C.Since all the single attributes’ closures are ABCD, any superset of the singleattributes will also lead to a closure of ABCD. Knowing this, we can enumerate the restof the new dependencies.The collection of 24 new dependencies mentioned above are:A→C, A→D, B→D, B→A, C→A, C→B, D→B, D→C, AB→C, AB→D, AC→B, AC→D, AD→B, AD→C, BC→A, BC→D, BD→A, BD→C, CD→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.Exercise 3.2.2bi) From the analysis of closures in 3.2.2a(i), we find that the only key is A. All other sets either do not have ABCD as the closure or contain A.ii) From the analysis of closures 3.2.2a(ii), we find that AB, AD, BC, and CD are keys.All other sets either do not have ABCD as the closure or contain one of these four sets.iii) From the analysis of closures 3.2.2a(iii), we find that A, B, C and D are keys. All other sets either do not have ABCD as the closure or contain one of these four sets.Exercise 3.2.2ci) The superkeys are all those sets that contain one of the keys in 3.2.2b(i). The superkeys are AB, AC, AD, ABC, ABD, ACD, BCD and ABCD.ii) The superkeys are all those sets that contain one of the keys in 3.2.2b(ii). The superkeys are ABC, ABD, ACD, BCD and ABCD.iii) The superkeys are all those sets that contain one of the keys in 3.2.2b(iii). The superkeys are AB, AC, AD, BC, BD, CD, ABC, ABD, ACD, BCD and ABCD.Exercise 3.2.3aSince A1A2…A n C contains A1A2…A n, then the closure of A1A2…A n C contains B. Thus it follows that A1A2…A n C→B.Exercise 3.2.3bFrom 3.2.3a, we know that A1A2…A n C→B. Using the concept of trivial dependencies, we can show that A1A2…A n C→C. Thus A1A2…A n C→BC.Exercise 3.2.3cFrom A1A2…A n E1E2…E j, we know that the closure contains B1B2…B m because of the FD A1A2…A n→B1B2…B m. The B1B2…B m and the E1E2…E j combine to form the C1C2…C k. Thus the closure ofA1A2…A n E1E2…E j contains D as well. Thus, A1A2…A n E1E2…E j→D.Exercise 3.2.3dFrom A1A2…A n C1C2…C k, we know that the closure contains B1B2…B m because of the FD A1A2…A n→B1B2…B m. The C1C2…C k also tell us that the closure of A1A2…A n C1C2…C k contains D1D2…D j. Thus, A1A2…A n C1C2…C k→B1B2…B k D1D2…D j.Exercise 3.2.4aIf attribute A represented Social Security Number and B represented a person’s name,then we would assume A→B but B→A would not be valid because there may be many peoplewith the same name and different Social Security Numbers.Exercise 3.2.4bLet attribute A represent Social Security Number, B represent gender and C represent name. Surely Social Security Number and gender can uniquely identify a person’s name (i.e.AB→C). A Social Security Number can also uniquely identify a person’s name (i.e. A→C). However, gender does not uniquely determine a name (i.e. B→C is not valid).Exercise 3.2.4cLet attribute A represent latitude and B represent longitude. Together, both attributes can uniquely determine C, a point on the world map (i.e. AB→C). However, neither A nor B can uniquely identify a point (i.e. A→C and B→C are not valid).Exercise 3.2.5Given a relation with attributes A1A2…A n, we are told that there are no functional dependencies of the form B1B2…B n-1→C where B1B2…B n-1 is n-1 of the attributes from A1A2…A n and C is the remaining attribute from A1A2…A n. In this case, the set B1B2…B n-1 and any subset do not functionally determine C. Thus the only functional dependencies that we can make are ones where C is on both the left and right hand sides. All of these functional dependencies would be trivial and thus the relation has no nontrivial FD’s.Exercise 3.2.6Let’s prove this by using the contrapositive. We wish to show that if X+ is not a subset of Y+, then it must be that X is not a subset of Y.If X+ is not a subset of Y+, there must be attributes A1A2…A n in X+ that are not in Y+. If any of these attributes were originally in X, then we are done because Y does not contain any of the A1A2…A n. However, if the A1A2…A n were added by the closure, then we must examine the case further. Assume that there was some FD C1C2…C m→A1A2…A j where A1A2…A j is some subset of A1A2…A n. It must be then that C1C2…C m or some subset of C1C2…C m is in X. However, the attributes C1C2…C m cannot be in Y because we assumed that attributes A1A2…A n are only in X+ and are not in Y+. Thus, X is not a subset of Y.By proving the contrapositive, we have also proved if X ⊆ Y, then X+⊆ Y+.Exercise 3.2.7The algorithm to find X+ is outlined on pg. 76. Using that algorithm, we can prove that (X+)+ = X+. We will do this by using a proof by contradiction.Suppose that (X+)+≠ X+. Then for (X+)+, it must be that some FD allowed additional attributes to be added to the original set X+. For example, X+→ A where A is some attribute not in X+. However, if this were the case, then X+ would not be the closure of X. The closure of X would have to include A as well. This contradicts the fact that we weregiven the closure of X, X+. Therefore, it must be that (X+)+ = X+ or else X+ is not the closure of X.Exercise 3.2.8aIf all sets of attributes are closed, then there cannot be any nontrivial functional dependencies. Suppose A1A2...A n→B is a nontrivial dependency. Then {A1A2...A n}+ contains B and thus A1A2...A n is not closed.Exercise 3.2.8bIf the only closed sets are ø and {A,B,C,D}, then the following FDs hold:A→B A→C A→DB→A B→C B→DC→A C→B C→DD→A D→B D→CAB→C AB→DAC→B AC→DAD→B AD→CBC→A BC→DBD→A BD→CCD→A CD→BABC→DABD→CACD→BBCD→AExercise 3.2.8cIf the only closed sets are ø, {A,B} and {A,B,C,D}, then the following FDs hold:A→BB→AC→A C→B C→DD→A D→B D→CAC→B AC→DAD→B AD→CBC→A BC→DBD→A BD→CCD→A CD→BABC→DABD→CACD→BBCD→AExercise 3.2.9We can think of this problem as a situation where the attributes A,B,C represent cities and the functional dependencies represent one way paths between the cities. The minimal bases are the minimal number of pathways that are needed to connect the cities. We do not want to create another roadway if the two cities are already connected.The systematic way to do this would be to check all possible sets of the pathways. However, we can simplify the situation by noting that it takes more than two pathways to visit the two other cities and come back. Also, if we find a set of pathways that is minimal, adding additional pathways will not create another minimal set.The two sets of minimal bases that were given in example 3.11 are:{A→B, B→C, C→A}{A→B, B→A, B→C, C→B}The additional sets of minimal bases are:{C→B, B→A, A→C}{A→B, A→C, B→A, C→A}{A→C, B→C, C→A, C→B}Exercise 3.2.10aWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A}+=A{B}+=B{C}+=ACE{AB}+=ABCDE{AC}+=ACE{BC}+=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: C→A and AB→C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted from our list.Exercise 3.2.10bWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A}+=AD{B}+=B{C}+=C{AB}+=ABDE{AC}+=ABCDE{BC}+=BCWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC→B.Exercise 3.2.10cWe need to compute the closures of all subsets of {ABC}, although there is no need tothink about the empty set or the set of all three attributes. Here are the calculationsfor the remaining six sets:{A}+=A{B}+=B{C}+=C{AB}+=ABD{AC}+=ABCDE{BC}+=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC→B and BC→A.Exercise 3.2.10dWe need to compute the closures of all subsets of {ABC}, although there is no need tothink about the empty set or the set of all three attributes. Here are the calculationsfor the remaining six sets:{A}+=ABCDE{B}+=ABCDE{C}+=ABCDE{AB}+=ABCDE{AC}+=ABCDE{BC}+=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: A→B, B→C and C→A.Exercise 3.2.11For step one of Algorithm 3.7, suppose we have the FD ABC→DE. We want to use Armstrong’s Axioms to show that ABC→D and ABC→E follow. Surely the functional dependencies DE→D and DE→E hold because they are trivial and follow the reflexivity property. Using the transitivity rule, we can derive the FD ABC→D from the FDs ABC→DE and DE→D. Likewise, we can do the same for ABC→DE and DE→E and derive the FD ABC→E.For steps two through four of Algorithm 3.7, suppose we have the initial set ofattributes of the closure as ABC. Suppose also that we have FDs C→D and D→E. Accordingto Algorithm 3.7, the closure should become ABCDE. Taking the FD C→D and augmenting both sides with attributes AB we get the FD ABC→ABD. We can use the splitting method in stepone to get the FD ABC→D. Since D is not in the closure, we can add attribute D. Taking the FD D→E and augmenting both sides with attributes ABC we get the FD ABCD→ABCDE.Using again the splitting method in step one we get the FD ABCD→E. Since E is not in the closure, we can add attribute E.Given a set of FDs, we can prove that a FD F follows by taking the closure of the left side of FD F. The steps to compute the closure in Algorithm 3.7 can be mimicked by Armstrong’s axioms and thus we can prove F from the given set of FDs using Armstrong’s axioms.Exercise 3.3.1aIn the solution to Exercise 3.2.1 we found that there are 14 nontrivial dependencies, including the three given ones and eleven derived dependencies. They are: C→A, C→D,D→A, AB→D, AB→ C, AC→D, BC→A, BC→D, BD→A, BD→C, CD→A, ABC→D, ABD→C, and BCD→A.We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are: C→A, C→D,D→A, AC→D, and CD→A.One choice is to decompose using the violation C→D. Using the above FDs, we get ACD and BC as decomposed relations. BC is surely in BCNF, since any two-attribute relation is. Using Algorithm 3.12 to discover the projection of FDs on relation ACD, we discover that ACD is not in BCNF since C is its only key. However, D→A is a dependency that holds in ABCD and therefore holds in ACD. We must further decompose ACD into AD and CD. Thus, the three relations of the decomposition are BC, AD, and CD.Exercise 3.3.1bBy computing the closures of all 15 nonempty subsets of ABCD, we can find all thenontrivial FDs. They are B→C, B→D, AB→C, AB→D, BC→D, BD→C, ABC→D and ABD→C. From the closures we can also deduce that the only key is AB. Thus, any dependency above that does not contain AB on the left is a BCNF violation. These are: B→C, B→D, BC→D andBD→C.One choice is to decompose using the violation B→C. Using the above FDs, we get BCD and AB as decomposed relations. AB is surely in BCNF, since any two-attribute relation is. Using Algorithm 3.12 to discover the projection of FDs on relation BCD, we discover that BCD is in BCNF since B is its only key and the projected FDs all have B on the left side. Thus the two relations of the decomposition are AB and BCD.Exercise 3.3.1cIn the solution to Exercise 3.2.2(ii), we found that there are 12 nontrivial dependencies, including the four given ones and the eight derived ones. They are AB→C, BC→D, CD→A, AD→B, AB→D, AD→C, BC→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.We also found out that the keys are AB, AD, BC, and CD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF.Exercise 3.3.1dIn the solution to Exercise 3.2.2(iii), we found that there are 28 nontrivial dependencies, including the four given ones and the 24 derived ones. They are A→B, B→C, C→D, D→A, A→C, A→D, B→D, B→A, C→A, C→B, D→B, D→C, AB→C, AB→D, AC→B, AC→D,AD→B, AD→C, BC→A, BC→D, BD→A, BD→C, CD→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.We also found out that the keys are A,B,C,D. Thus, any dependency above that does nothave one of these attributes on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF.Exercise 3.3.1eBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ABE→C, ABE→D, ADE→C, BCE→D, BDE→C, ABCE→D, and ABDE→C. From the closures we canalso deduce that the only key is ABE. Thus, any dependency above that does not contain ABE on the left is a BCNF violation. These are: AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ADE→C, BCE→D and BDE→C.One choice is to decompose using the violation AB→C. Using the above FDs, we get ABCDand ABE as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation ABCD, we discover that ABCD is not in BCNF since AB is its only key and the FD B→D follows for ABCD. Using violation B→D to further decompose, we get BD and ABC as decomposed relations. BD is in BCNF because it is a two-attribute relation. Using Algorithm 3.12 again, we discover that ABC is in BCNF since AB is the only key and AB→Cis the only nontrivial FD. Going back to relation ABE, following Algorithm 3.12 tells us that ABE is in BCNF because there are no keys and no nontrivial FDs. Thus the three relations of the decomposition are ABC, BD and ABE.Exercise 3.3.1fBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are: C→B, C→D, C→E, D→B, D→E, AB→C, AB→D, AB→E, AC→B, AC→D, AC→E, AD→B, AD→C, AD→E, BC→D, BC→E, BD→E, CD→B, CD→E, CE→B, CE→D, DE→B,ABC→D, ABC→E, ABD→C, ABD→E, ABE→C, ABE→D, ACD→B, ACD→E, ACE→B, ACE→D, ADE→B, ADE→C, BCD→E, BCE→D, CDE→B, ABCD→E, ABCE→D, ABDE→C and ACDE→B. From the closures we can also deduce that the keys are AB, AC and AD. Thus, any dependency above that does not contain one of the above pairs on the left is a BCNF violation. These are: C→B, C→D,C →E,D →B, D →E, BC →D, BC →E, BD →E, CD →B, CD →E, CE →B, CE →D, DE →B, BCD →E, BCE →D and CDE →B.One choice is to decompose using the violation D →B. Using the above FDs, we get BDE and ABC as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation BDE, we discover that BDE is in BCNF since D, BD, DE are the only keys and all the projected FDs contain D, BD, or DE in the left side. Going back to relation ABC,following Algorithm 3.12 tells us that ABC is not in BCNF because since AB and AC are its only keys and the FD C →B follows for ABC. Using violation C →B to further decompose, we get BC and AC as decomposed relations. Both BC and AC are in BCNF because they are two-attribute relations. Thus the three relations of the decomposition are BDE, BC and AC.Exercise 3.3.2Yes, we will get the same result. Both A →B and A →BC have A on the left side and part ofthe process of decomposition involves finding {A}+to form one decomposed relation and Aplus the rest of the attributes not in {A}+as the second relation. Both cases yield the same decomposed relations.Exercise 3.3.3Yes, we will still get the same result. Both A →B and A →BC have A on the left side andpart of the process of decomposition involves finding {A}+to form one decomposedrelation and A plus the rest of the attributes not in {A}+as the second relation. Both cases yield the same decomposed relations.Exercise 3.3.4This is taken from Example 3.21 pg. 95.Suppose that an instance of relation R only contains two tuples.The projections of R onto the relations with schemas {A,B} and {B,C} are:If we do a natural join on the two projections, we will get:The result of the natural join is not equal to the original relation R.Exercise 3.4.1aThis is the initial tableau:→A.Since there is not an unsubscripted row, the decomposition for R is not lossless for this set of FDs.We can use the final tableau as an instance of R as an example for why the join is not lossless. The projected relations are:The joined relation is:The joined relation has three more tuples than the original tableau.Exercise 3.4.1bThis is the initial tableau:This is the final tableau after applying FDs AC→E and BC→DSince there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.Exercise 3.4.1cThis is the initial tableau:This is the final tableau after applying FDs A→D, D→E and B→D.Since there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.Exercise 3.4.1dThis is the initial tableau:This is the final tableau after applying FDs A→D, CD→E and E→DSince there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.Exercise 3.4.2When we decompose a relation into BCNF, we will project the FDs onto the decomposed relations to get new sets of FDs. These dependencies are preserved if the union of these new sets is equivalent to the original set of FDs.For the FDs of 3.4.1a, the dependencies are not preserved. The union of the new sets of FDs is CE→A. However, the FD B→E is not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.For the FDs of 3.4.1b, the dependencies are preserved. The union of the new sets of FDs is AC→E and BC→D. This is precisely the same as the original set of FDs and thus the two sets of FDs are equivalent.For the FDs of 3.4.1c, the dependencies are not preserved. The union of the new sets of FDs is B→D and A→E. The FDs A→D and D→E are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.For the FDs of 3.4.1d, the dependencies are not preserved. The union of the new sets of FDs is AC→E. However, the FDs A→D, CD→E and E→D are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.Exercise 3.5.1aIn the solution to Exercise 3.3.1a we found that there are 14 nontrivial dependencies. They are: C→A, C→D, D→A, AB→D, AB→ C, AC→D, BC→A, BC→D, BD→A, BD→C, CD→A,ABC→D, ABD→C, and BCD→A.We also learned that the three keys were AB, BC, and BD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1bIn the solution to Exercise 3.3.1b we found that there are 8 nontrivial dependencies. They are B→C, B→D, AB→C, AB→D, BC→D, BD→C, ABC→D and ABD→C.We also found out that the only key is AB. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NF violations are B→C, B→D, BC→D and BD→C.Using algorithm 3.26, we can decompose into relations using the minimal basis B→C andB→D. The resulting decomposed relations would be BC and BD. However, none of these two sets of attributes is a superkey. Thus we add relation AB to the result. The final set of decomposed relations is BC, BD and AB.Exercise 3.5.1cIn the solution to Exercise 3.3.1c we found that there are 12 nontrivial dependencies. They are AB→C, BC→D, CD→A, AD→B, AB→D, AD→C, BC→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.We also found out that the keys are AB, AD, BC, and CD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1dIn the solution to Exercise 3.3.1d we found that there are 28 nontrivial dependencies. They are A→B, B→C, C→D, D→A, A→C, A→D, B→D, B→A, C→A, C→B, D→B, D→C, AB→C,AB→D, AC→B, AC→D, AD→B, AD→C, BC→A, BC→D, BD→A, BD→C, CD→A, CD→B, ABC→D,ABD→C, ACD→B and BCD→A.We also found out that the keys are A,B,C,D. Since all the attributes on the right sidesof the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1eIn the solution to Exercise 3.3.1e we found that there are 16 nontrivial dependencies. They are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ABE→C, ABE→D, ADE→C, BCE→D, BDE→C, ABCE→D, and ABDE→C.We also found out that the only key is ABE. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NFviolations are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ADE→C, BCE→D and BDE→C.Using algorithm 3.26, we can decompose into relations using the minimal basis AB→C,DE→C and B→D. The resulting decomposed relations would be ABC, CDE and BD. However,none of these three sets of attributes is a superkey. Thus we add relation ABE to the result. The final set of decomposed relations is ABC, CDE, BD and ABE.Exercise 3.5.1fIn the solution to Exercise 3.3.1f we found that there are 41 nontrivial dependencies. They are: C→B, C→D, C→E, D→B, D→E, AB→C, AB→D, AB→E, AC→B, AC→D, AC→E, AD→B, AD→C, AD→E, BC→D, BC→E, BD→E, CD→B, CD→E, CE→B, CE→D, DE→B, ABC→D, ABC→E,ABD→C, ABD→E, ABE→C, ABE→D, ACD→B, ACD→E, ACE→B, ACE→D, ADE→B, ADE→C, BCD→E, BCE→D, CDE→B, ABCD→E, ABCE→D, ABDE→C and ACDE→B.We also found out that the keys are AB, AC and AD. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The3NF violations are C→E, D→E, BC→E, BD→E, CD→E and BCD→E.Using algorithm 3.26, we can decompose into relations using the minimal basis AB→C, C→D, D→B and D→E. The resulting decomposed relations would be ABC, CD, BD and DE. Since relation ABC contains a key, we can stop with the decomposition. The final set of decomposed relations is ABC, CD, BD and DE.Exercise 3.5.2aThe usual procedure to find the keys would be to take the closure of all 63 nonempty subsets. However, if we notice that none of the right sides of the FDs contains。

3-3 习题33.4 在SQL Server中，创建一个名为students且包含有下列几个属性的表。

SNO char(10)；NAME varchar(10)；SEX char(1)；BDATE datetime；DEPT varchar(10)；DORMITORY varchar(10).要求：1.采用两种形式创建表，即用SQL语句和用图形界面的形式来创建。

2.定义必要的约束，包括主键SNO,NAME值不允许为空，且SEX取值为0或1。

【解答】·进入SQL查询分析器建立查询，创建students表的SQL语句如下，操作如图3.17所示。

use mydb /* 假设在mydb库中建表*/create table students(SNO char(10) not NULL primary key,NAME varchar(10) not NULL,SEX char(1) not NULL check(sex='0' or sex='1'),BDATE datetime,DEPT varchar(10),DORMITORY varchar(10))- １-图3.17 用SQL语句创建students表·进入企业管理器用基本操作创建students表。

用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。

或者，用右键单击“mydb”数据库下一级的“表”，从弹出的菜单中选择“新建表”。

然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图3.18所示。

图3.18用基本操作创建students表其中，SEX字段取值为0或1，需要建立约束。

操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图3.19所示。

第三章习题答案一、简答题（略）名词解释(1)关系模型：用二维表格结构表示实体集，外键表示实体间联系的数据模型称为关系模型。

(2)关系模式：关系模式实际上就是记录类型。

它的定义包括：模式名，属性名，值域名以及模式的主键。

关系模式不涉及到物理存储方面的描述，仅仅是对数据特性的描述。

(3)关系实例：元组的集合称为关系和实例，一个关系即一张二维表格。

(4)属性：实体的一个特征。

在关系模型中，字段称为属性。

(5)域：在关系中，每一个属性都有一个取值范围，称为属性的值域，简称域。

(6)元组：在关系中，记录称为元组。

元组对应表中的一行；表示一个实体。

(7)超键：在关系中能唯一标识元组的属性集称为关系模式的超键。

(8)候选键：不含有多余属性的超键称为候选键。

(9)主键：用户选作元组标识的一个候选键为主键。

（单独出现，要先解释“候选键”）(10)外键：某个关系的主键相应的属性在另一关系中出现，此时该主键在就是另一关系的外键，如有两个关系S和SC,其中S#是关系S的主键，相应的属性S#在关系SC中也出现，此时S#就是关系SC的外键。

(11)实体完整性规则：这条规则要求关系中元组在组成主键的属性上不能有空值。

如果出现空值，那么主键值就起不了唯一标识元组的作用。

(12)参照完整性规则：这条规则要求“不引用不存在的实体”。

其形式定义如下：如果属性集K是关系模式R1的主键，K也是关系模式R2的外键，那么R2的关系中，K的取值只允许有两种可能，或者为空值，或者等于R1关系中某个主键值。

这条规则在使用时有三点应注意：1)外键和相应的主键可以不同名，只要定义在相同值域上即可。

2)R1和R2也可以是同一个关系模式，表示了属性之间的联系。

3)外键值是否允许空应视具体问题而定。

为什么关系中的元组没有先后顺序?因为关系是一个元组的集合，而元组在集合中的顺序无关紧要。

因此不考虑元组间的顺序，即没有行序。

为什么关系中不允许有重复元组?因为关系是一个元组的集合，而集合中的元素不允许重复出现，因此在关系模型中对关系作了限制，关系中的元组不能重复，可以用键来标识唯一的元组。

数据库原理及应⽤第3章课后习题答案习题31.试述关系模型的3个组成部分。

1）数据结构关系模型的数据结构⾮常简单，只包括单⼀的数据结构——关系。

从⽤户⾓度，关系模型中数据的逻辑结构是⼀张扁平的⼆维表。

2）数据操作关系操作采⽤集合操作⽅式，即操作的对象和结果都是集合。

这种⽅式称为⼀次⼀集合的⽅式。

⽽⾮关系数据结构的数据操作⽅式为⼀次⼀记录⽅式。

关系模型中常⽤的关系操作包括查询操作和插⼊、删除、修改操作两⼤部分。

3）完整性约束关系模型提供了丰富的完整性控制机制，允许定义三类完整性：实体完整性、参照完整性和⽤户定义完整性。

2.定义并理解下列术语，说明它们之间的联系与区别：1）域、笛卡尔积、关系、元组、属性①域（Domain）域是⼀组具有相同数据类型的值的集合。

②笛卡尔积（Cartesian Product）定义 3.2 给定⼀组域D1，D2，…，D n，这些域中可以有相同的域。

D1，D2，…，D n 的笛卡尔积为：D1×D2×…×D n＝{（d1，d2，…，d n）｜d i D i，i＝1，2，…，n｝③关系D1×D2×…×D n的⼦集叫作在域D1，D2，…，D n上的关系，表⽰为：R（D1，D2，…，D n），这⾥R是关系名。

④表的每⾏对应⼀个元组，也可称为记录（Record）。

⑤表的每列对应⼀个域，也可以称为字段（Filed ）。

由于域可以相同，为了加以区分，必须为每列起⼀个名字，称为属性（Attribute）。

2）主码、候选码、外码①若关系中的某⼀属性或属性组的值能唯⼀地标识⼀个元组，则称该属性组为候选码或码（Key）。

其中属性组中不能含有多余的属性。

②若⼀个关系有多个候选码，则选定其中⼀个作为主码（Primary Key）。

每个关系有且仅有⼀个主码。

③如果⼀个属性或属性组不是所在关系的码，却是另⼀个关系的码，则称该属性或属性组为所在关系的外码。