数据库第三章作业assignment1
数据库第三章所有例题参考答案
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)（完）。
(完整word版)数据库系统基础教程第三章答案
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 could havemultiple addresses. The same is true for phones. These days, a person could have alandline 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 subsetsbecause 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 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 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 single attributes will also lead to a closure of ABCD. Knowing this, we can enumerate the rest of 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 nB1B2…B m. The B1B2…B m and the E1E2…E j combine to form the C1C2…C k. Thus the closure of A1A2…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 nB1B2…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 people with 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 theA1A2…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 ofA1A2…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 were given 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 BC B DC A C B C DD A D B D C。
数据库原理及应用教程第三章作业
数据库原理及应用教程第三章作业3，设计问题1，有以下两个数据表，每个表的结果和字段名如下:书(书)包括书名(BNo)、类型(BType)、书名(BName)、作者(BAuth)、单价(BPrice)、出版社(PNo)出版社(publish)包括出版社编号(PNo)、出版社名称(PName)、城市(PCity)和使用SQL实现以下功能:(1)在“高等教育出版社”出版的《操作系统》一书的作者姓名；a:从图书中选择bauth，publishwhere book。
pno =发布。
pno和bname = ‘操作系统’和PName= ‘高等教育出版社’(2)查找为作者“张欣”出版所有“小说”书籍的出版社的电话号码；答:从书中选择PTEL，出版WHEREBOOK。
PNO = PUBLISH。
PNO和鲍思= ‘张欣’和BType = ‘小说’(3)查询“电子工业出版社”出版的“计算机”书籍的价格，输入出版社的名称和类别。
a:选择bprice，pname，btype frombook，Publish WHEREBOOK。
PNO = PUBLISH。
PNO和PNAME = ‘电子工业出版社’和BType=‘BType’(4)查找与《人民邮电出版社》出版的《高等数学》同名但价格较低的书籍；答:从中选择*其中bname =“高等数学”和bprice 其中PName= ‘人民邮电出版社’和BName= ‘高等数学’并发布。
书。
PNo) (5)找到书名和书名中有“计算机”一词的作者；a:选择bname，bauth from book，其中像“% computer %”(6)这样的bname正在“book”表中添加“发布时间”(BDate)项，其数据类型为日期类型；答:ALTER TABLE BOOKADDBDATE DATETIME(7)在“BOOK”表中用“author”建立索引答:创建唯一索引鲍思_图书(鲍思)2。
假设有一家书店，书店的经理需要建立一个数据库来管理书店的经营，该数据库包括两个表:本书(书号、书名、出版社、版本、出版日期、作者、购买价格、数量)销售额(日期、书号、数量、金额)请使用SQL来满足书店经理的以下要求:(1)建立一个图书存储表和一个销售表；a:创建table book(b point，bnamechar (10)，publishchar (20)，editionint，datedatetime，authchar (10)，priceint，inpriceit。
数据库 第三章习题参考答案
三、设计题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)建立存书表和销售表。
【精选】数据库第三章课后习题
• 14、 • (1)GRANT SELECT ON 职工,部门TO 王明 • (2) GRANT INSERT,DELETE ON 职工,部门TO
李勇
• (3) GRANT SELECT ON 职工WHEN USER() = NAME TO ALL
• (4) GRANT SELECT,UPDATE(工资) ON 职工 TO 刘星
• 7、视图的优点 • 视图能够简化用户的操作 • 视图使用户能以多种角度看待同一数据 • 视图对重构数据库提供了一定程度的逻辑
独立性； • 视图能够对机密数据提供安全保护。
• 8、所有的视图是否都可以更新？
• 不是。视图是不实际存储数据的虚表，因 此对视图的更新，最终要转换为对基本表 的更新。因为有些视图的更新不能惟一有 意义地转换成对相应基本表的更新，所以 ，并不是所有的视图都是可更新的。
SPJ TO 李天明；
• 13、 • （1）INSERT INTO SC(Sno,Cno,Grade)
VALUES("2000012", "1128", NULL); • （2）SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
• （3）SELECT cname,grade FROM course,Sc WHERE o=o AND cname="英语"
• (1) SELECT DIST PNO,QTY FROM SPQ
• (2) SELECT DIST * FROM SPQ WHERE SNO="S1‘
• 12、 • （1）GRANT INSERT
ON TABLE S TO 张勇
数据库系统作业1-3章
《数据库系统》作业要求所有作业必须手写完成，不可打印，复印第一章作业：1、解释下列术语：数据，数据库，数据库系统，数据库管理系统答：( l ）数据（Data ) ：描述事物的符号记录称为数据。
( 2 ）数据库（DataBase ，简称DB ) ：数据库是长期储存在计算机内的、有组织的、可共享的数据集合。
( 3 ）数据库系统（DataBas 。
Sytem ，简称DBS ) ：数据库系统是指在计算机系统中引入数据库后的系统构成，一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员构成。
( 4 ）数据库管理系统（DataBase Management sytem ，简称DBMs ) ：数据库管理系统是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据。
2、试述数据库系统的主要特点。
答：( l ）数据结构化数据库系统实现整体数据的结构化，这是数据库的主要特征之一，也是数据库系统与文件系统的本质区别。
( 2 ）数据的共享性高，冗余度低，易扩充数据库的数据不再面向某个应用而是面向整个系统，因此可以被多个用户、多个应用以多种不同的语言共享使用。
( 3 ）数据独立性高数据独立性包括数据的物理独立性和数据的逻辑独立性。
( 4 ）数据由DBMS 统一管理和控制数据库的共享是并发的共享，即多个用户可以同时存取数据库中的数据甚至可以同时存取数据库中同一个数据。
3、试述数据库系统的三级模式结构及每级模式的作用？答：数据库系统的三级模式结构是指数据库系统是由外模式，模式，和内模式三级构成。
作用：模式是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
外模式是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是某一应用有关的数据的逻辑表示。
内模式是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。
4、什么是数据的独立性？数据库系统中为什么能具有数据独立性？答：数据与程序的逻辑独立性：当模式改变时（例如增加新的关系、新的属性、改变属性的数据类型等），由数据库管理员对各个外模式／模式的映像做相应改变，可以使外模式保持不变。
分享：数据库系统原理第三章基本概念及课后习题有答案
分享：数据库系统原理第三章基本概念及课后习题有答案一、关系模式的设计准则1．数据冗余：同一个数据在系统中多次重复出现。
2．关系模式设计不当引起的异常问题：数据冗余、操作异常（包括修改异常、插入异常和删除异常）3．关系模式的非形式化设计准则1）关系模式的设计应尽可能只包含有直接联系的属性，不要包含有间接联系的属性。
也就是，每个关系模式应只对应于一个实体类型或一个联系类型。
2）关系模式的设计应尽可能使得相应关系中不出现插入异常、删除和修改等操作异常现象。
3）关系模式的设计应尽可能使得相应关系中避免放置经常为空值的属性。
4）关系模式的设计应尽可能使得关系的等值连接在主键和外键的属性上进行，并且保证以后不会生成额外的元组。
4．习惯使用的一些符号：1）英文字母表首部的大写字母“A，B，C，…”表示单个的属性。
2）英文字母表尾部的大写字母“…，U，V，W，X，Y，Z”表示属性集。
3）大写字母R表示关系模式，小写字母r表示其关系。
4）关系模式的简化表示方法：R（A，B，C，…）或R（ABC…）5）属性集X和Y的并集简写为XY。
二、函数依赖1．函数依赖（FD）的定义：设有关系模式R（U），X和Y是属性集U的子集，函数依赖是形成X→Y的一个命题，只要r是R的当前关系，对r中任意两个元组t和s，都有t[X]=s[X]蕴涵t[Y]=s[Y]，那么称FD X→Y在关系模式R（U）中成立。
说明： 1）t[X]表示元组t在属性集X上的值，其余类同。
2）X→Y读作“X函数决定Y”或“Y函数依赖于X”。
3）FD是对关系模式R的一切可能的关系r定义的。
对于当前关系r的任意两个元组，如果X值相同，则要求Y值也相同，即有一个X 值就有一个Y值与之对应，或者说Y值由X值决定。
例：设关系模式R（ABCD），在R的关系中，属性值间有这样的联系：A值与B值有一对多联系；C值与D值之间有一对一联系。
试根据这些规则写出相应的函数依赖。
B→A C→D D→C2．如果X→Y和Y→X同时成立，则可记为：X↔Y3．FD的逻辑蕴涵：设F是在关系模式R上成立的函数依赖的集合，X→Y是一个函数依赖。
数据库第三章习题及答案
第3章关系数据库标准语言SQL一、选择题1、SQL语言是的语言，易学习。
A．过程化 B．非过程化 C．格式化 D．导航式答案：B2、SQL语言是语言。
A．层次数据库 B．网络数据库 C．关系数据库 D．非数据库答案：C3、SQL语言具有的功能。
A．关系规范化、数据操纵、数据控制 B．数据定义、数据操纵、数据控制C．数据定义、关系规范化、数据控制 D．数据定义、关系规范化、数据操纵答案：B4、SQL语言具有两种使用方式，分别称为交互式SQL和。
A．提示式SQL B．多用户SQL C．嵌入式SQL D．解释式SQL 答案：C5、假定学生关系是S(S#，SNAME，SEX，AGE)，课程关系是C(C#，CNAME，TEACHER)，学生选课关系是SC(S#，C#，GRADE)。
要查找选修“COMPUTER”课程的“女”学生姓名，将涉及到关系。
A．S B．SC，C C．S，SC D．S，C，SC 答案：D6、若用如下的SQL语句创建一个student表：CREATE TABLE student(NO C(4) NOT NULL，NAME C(8) NOT NULL，SEX C(2)，AGE N(2))可以插入到student表中的是。
A．(‘1031’，‘曾华’，男，23) B．(‘1031’，‘曾华’，NULL，NULL)C．(NULL，‘曾华’，‘男’，‘23’) D．(‘1031’，NULL，‘男’，23) 答案：B7、当两个子查询的结果时，可以执行并，交，差操作．Ａ.结构完全不一致 B.结构完全一致C.结构部分一致D.主键一致答案：B第8到第10题基于这样的三个表即学生表S、课程表C和学生选课表SC，它们的结构如下：S(S#，SN，SEX，AGE，DEPT)C(C#，CN)SC(S#，C#，GRADE)其中：S#为学号，SN为姓名，SEX为性别，AGE为年龄，DEPT为系别，C#为课程号，CN为课程名，GRADE为成绩。
数据库第三章部分习题答案
数据库第三章部分习题答案3.2对于教学数据库的三个基本表s(s#,sname,age,sex)sc(s#,c#,grade)c（c#，cname，教师）试用sql的查询语句表达下列查询：3.2.1搜索17岁以下女生的学号和姓名，选择#，snamefromswhereage<17andsex=f;3.2.2检索男孩学习的课程编号和课程名称，从C中选择C#，CNAMEwherec#in(selectdistinctc#fromscwheres#in(selects#fromswheresex=m))3.2.3检索男生学习课程的教师的职务编号和姓名selectt#,tnamefromt其中#in（从C中选择distinctt#wherec#in(selectdistinctc#fromsc#in（选择）在哪里#froms其中性别=1）；3.2.4检索至少选修两门课程的学生的学号挑选#fromscgroupbys#拥有计数（c#）>=2；3.2.5检索至少有学号为s2和s4所学的课程和课程名selectc#,cnamefromc其中c#in（（选择c#fromscwheres#='s2')intersect（选择C#fromsc#='s4'）3.2.6检索‘wang’同学不学的课程号从CEXcept中选择C#(selectdistinctc#fromscwheres#=(selects#fromswheresname='wang'))；3.2.7检索所有学生的课程号和课程名称selectc#,cname弗洛姆wherenotexists(selects#弗洛姆wherec.c#notin(selectc#fromscwheresc.s#=s.s#))；3.2.8检索选修课程包括“刘”老师教授的所有课程的学生的学号和姓名。
选择#，snamefromswherenotexists((selectc#弗洛姆wheret#=(selectt#fromtwhere name='liu'））除了（选择C#fromsc wheresc.s#=s.s#));3.4有两个基本表R（a、B、c）和S（a、B、c）。
数据库原理及应用第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）。
每个关系有且仅有一个主码。
③如果一个属性或属性组不是所在关系的码，却是另一个关系的码，则称该属性或属性组为所在关系的外码。
3）关系模型、关系、关系数据库①关系数据库中关系模式是型，关系是值，关系模式是对关系的描述，关系模式可以用一个五元组表示：R（U，D，DOM，F）。
数据库原理及应用教程第三章作业
三、设计题1、设有以下两个数据表，各表的结果及字段名如下：图书（Book）包括书名（BNo）、类型（BType）、书名（BName）、作者（BAuth）、单价（BPrice）、出版社（PNo）出版社（Publish）包括出版社号（PNo）、出版社名称（PName）、所在城市（PCity）、电话（PTel）。
用SQL实现下述功能：（1）在“”高等教育出版社出版、书名为“操作系统”的图书的作者名；答：select BAuthfrom Book,Publishwhere Book.PNo =Publish.PNoand BName='操作系统'and PName='高等教育出版社出版'（2）查找为作者“张欣”出版全部“小说”类图书的出版社的电话；答：select PTelfrom Book,Publishwhere Book.PNo=Publish.PNoand BAuth='张欣'and BType='小说'（3）查询“电子工业出版社”出版的“计算机”类的图书的价格，同时输出版社名称及图书类别；答：select BPrice,PName,BTypefrom Book,Publishwhere Book.PNo=Publish.PNoand PName='电子工业出版社'and BType='BType'（4）查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息；答：select *from Bookwhere BName='高等数学'and BPrice< ANY (select BPricefrom Book,Publishwhere PName='人民邮电出版社'and BName='高等数学'and Publish.PNo=Book.PNo )（5）查找书名中有“”计算机一词的图书的书名及作者；答：select BName,BAuthfrom Bookwhere BName like '%计算机%'（6）在“图书”表中正增加“出版时间”（BDate）项，其数据类型为日期型；答：alter table BookaddBDate datetime（7）在“图书”表中以“作者”建立一个索引。
数据库系统概论第三章课后作业
第三章作业参考答案3.用SQL 语句建立第二章习题5中的4个表。
CREATE TABLE S（SNO CHAR（3）primary key，SNAME CHAR（10）not null，STATUS CHAR（2），CITY CHAR（10））；CREATE TABLE P（PNO CHAR（3）primary key，PNAME CHAR（10），COLOR CHAR（4），WEIGHT INT）；CREATE TABLE J（JNO CHAR（3）primary key，JNAME CHAR（10），CITY CHAR（10））；CREATE TABLE SPJ（SNO CHAR（3），PNO CHAR（3），JNO CHAR（3），QTY INTPrimary key(sno,pno,jno)）；4.针对上题中建立的4个表试用SQL 语言完成第二章习题5中的查询。
（1）求供应工程J1零件的供应商号码SNO；关系代数：SELECT SNOFROM SPJWHERE JNO =‘J1’；（2）求供应工程J1零件P1的供应商号码SNO；关系代数：SELECT SNOFROM SPJWHERE JNO =‘J1’AND PNO =‘P1’；（3）求供应工程J1零件为红色的供应商号码SNO；关系代数：FROM SPJWHERE JNO =‘J1’AND PNO IN（SELECT PNOFROM PWHERE COLOR =‘红’）；或者是SELECT SNOFROM SPJ，PWHERE JNO =‘J1’AND SPJ.PNO = P.PNOAND COLOR =‘红’；（4）求没有使用天津供应商生产的红色零件的工程号JNO；注意：从J表入手，以包含那些尚未使用任何零件的工程号。
关系代数：SELECT JNOFROM JWHERE NOT EXISTSFROM SPJWHERE SPJ.JNO = J.JNOAND SNO IN（SELECT SNOFROM SWHERE CITY =’天津’）AND PNO IN（SELECT PNOFROM PWHERE COLOR =’红’））；或者SELECT JNOFROM JWHERE NOT EXISTS（SELECT *FROM SPJ，S，PWHERE SPJ.JNO = J.JNOAND SPJ.SNO = S.SNOAND SPJ.PNO = P.PNOAND S.CITY =‘天津’AND P.COLOR =‘红’）；（5）求至少用了供应商S1所供应的全部零件的工程号JNO（类似于《概论》P113例44）。
数据库第3章习题解答PPT教学课件
CREATE TABLE S (SNO CHAR(4) NOT NULL ，
SNAME CHAR(20) NOT NULL，
STATUS CHAR(10)，
CITY CHAR(20)，
PRIMARY KEY （SNO））；
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL，
PNO CHAR(4) NOT NULL，
JNO CHAR(4) NOT NULL，
QTY SMALLINT，
PRIMARY KEY (SNO，PNO，JNO)，
FOREIGN KEY (SNO) REFERENCES S(SNO),
2)求供应工程J1零件P1的供应商号码SNO; SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’;
3)求供应工程J1零件为红色的供应商号SNO; SELECT DISTINCT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO IN (SELECT PNO FROM P WHERE COLOR=‘红’）;
FOREIGN KEY (PNO) REFERENCES P(PNO),
2020/12/10
FOREIGN KEY (JNO) REFERENCES J(JNO))； 9
4.针对上题中建立的四个表试用SQL语言完成第二 章习题5中的查询
1)求供应工程J1零件的供应商号码SNO;
2)求供应工程J1零件P1的供应商号码SNO;
2020/12/10
3
(1) 检索“程军”老师所授课程的课程号CNO和课程名CNAME。
数据库第三章部分习题答案
3.2 对于教学数据库的三个基本表S(S#,SNAME,AGE,SEX)SC(S#,C#,GRADE)C(C#,CNAME,TEACHER)试用SQL的查询语句表达下列查询：3.2.1检索年龄小于17岁的女学生的学号和姓名select s#,sname from Swhere age<17 and sex=F;3.2.2检索男生所学课程的课程号和课程名select c#,cname from Cwhere c# in (select distinct c#from SCwhere s# in (select s# from S where sex=M)) 3.2.3检索男生所学课程的任课老师的工号和姓名select t#,tname from Twhere t# in(select distinct t#from Cwhere c# in(select distinct c#from SCwhere s# in(select s#from Swhere sex=1)));3.2.4检索至少选修两门课程的学生的学号select s#from SCgroup by s#having count(c#)>=2;3.2.5检索至少有学号为S2和S4所学的课程和课程名select c#,cnamefrom Cwhere c# in((select c#from sc where s#='S2')intersect(select c# from sc where s#='S4') )；3.2.6检索‘WANG’同学不学的课程号select c# from cexcept(select distinct c#from scwhere s# =(select s# from s where sname='WANG'))；3.2.7检索全部学生都选修的课程号和课程名select c#,cnamefrom cwhere not exists(select s#from swhere c.c# not in (select c# from sc where sc.s#=s.s# ))；3.2.8检索选修课程包含'LIU'老师所授课程的全部课程的学生的学号和姓名select s#,snamefrom swhere not exists((select c#from cwhere t#=(select t#from twhere tname='LIU')) except(select c# from sc where sc.s#=s.s#) );3.4 设有两个基本表R（A，B，C）和S（A，B，C），试用SQL查询语句表达下列关系代数表达式：① R∪S ② R∩S ③ R－S ④ R×S ⑤πA,B(R) πB,C（S）⑥π1，6（σ3=4（R×S）⑦π1，2，3（S）⑧R÷πC（S）解：①(SELECT * FROM R)UNION(SELECT * FROM S)；②(SELECT * FROM R)INTERSECT(SELECT * FROM S)；③(SELECT * FROM R)MINUS(SELECT * FROM S)；④SELECT *FROM R, S；⑤SELECT R.A, R.B, S.CFROM R, SWHERE R.B=S.B；⑥SELECT R.A, S.CFROM R, SWHERE R.C=S.A；⑦SELECT R.* （R.*表示R中全部属性）FROM R, SWHERE R.C=S.C；⑧R÷πC（S）的元组表达式如下：{ t |（∃u）（∀v）（∃w）（R（u）∧S（v）∧R（w）∧w[1]=u[1] ∧w[2]=u[2] ∧w[3]=v[3] ∧t[1]=u[1] ∧t[2]=u[2]）}据此，可写出SELECT语句：SELECT A, BFROM R RXWHERE NOT EXISTS( SELECT *FROM SWHERE NOT EXISTS( SELECT *FROM R RYWHERE RY.A=RX.A AND RY.B=RX.B ANDRY.C=S.C))；3.6 试叙述SQL语言的关系代数特点和元组演算特点。
数据库原理第三章作业
第三章关系数据库系统RDBS一. 简答题1.表间数据完整性的实现方式？外键约束。
2.对于表中几个特殊的列，如主键、候选键和外键，分别用什么限制来保证它们的完整性？对表中其它一般性的列，用什么限制来保证它们的完整性？主键：主键约束。
候选键：唯一约束。
外键：外键约束。
一般性列：一般性约束。
3.SQL-92标准支持的完整性限制是否一定会在SQL SERVER中实现，举例说明？否。
SQL-92标准推荐断言，SQL Server不支持断言。
SQL Server支持触发器，SQL-92标准没有定义触发器。
4.SQL SERVER中规则的目的？指定列的取值范围。
5.SQL SERVER中在定义某些限制时，分列级与表级，其分类的原则是什么？列级：针对表中一列。
表级：针对同一表中多列。
6.外键限制定义的条件？定义外键约束的列必须是另一个表中的主键或候选键。
7.请说明在维护表间数据完整时外键限制与触发器的异同。
外键约束的行为是被DBMS固定的，触发器中的行为是由用户定义的，因此利用触发器可以完成更加灵活的表间数据一致性保护。
8.关系代数的基本操作符？笛卡尔乘积最大的作用是什么？基本操作符：SELECTION （选择）、PROJECTION（投影）、UNION（并，或称联合）、INTERSECTION （交）、DIFFERENCE（差）、CROSS PRODUCT（积，或称笛卡尔乘积）。
最大的作用：可将两张或多张有关联（即有相同的列）或无关联的表的数据组合起来。
9.为什么说在实际查询中自然连接是用得比较多的？自然联结的结果，是从两个关系实例的笛卡尔乘积中，选出同时满足一个或多个条件等式的行，每个条件等式中的列名相同。
同时，在结果模式中，对重复的字段只保留一个。
当两个关系通过外键联系时，自然联结将用得非常频繁。
10.关系代数中对结果有重复元组时，如何处理？SQL中呢？关系代数中：去掉重复元组。
SQL 中：保留重复元组。
数据库原理与应用Assignment01
第1页（共5页）
管理学作业答题纸
数据库原理与应用01次作业（第1-4单元）答题纸
学籍号：姓名：
学习中心：_ 分数：班级：
批改老师：
本次作业满分为100分。
请将每道题的答案写在对应题目下方的横线上。
题目1 [50 分]
第2页（共5页）
第3页（共5页）
一、建立学籍管理数据库
启动ACCESS，单击新建按钮之后，在右侧任务栏中选择“空数据库”，建立学籍管理数据库。
二、建立课程表
先建立表结构后输入记录。
双击数据库窗口中的“使用设计器创建表”，打开表设计器，根据题目的要求，建立课程表中的三个字段。
2. 输入记录
三、建立学生信息表
双击数据库窗口中的“使用设计器创建表”，打开表设计器，要按照题目要求建立字段名称及类型、大小。
2. 输入记录
删除“学生信息表”中的最后一条记录（张庆华）
当这个操作执行之后，成绩表中与团员表中的学号为09111111、张庆华的记录也自动被删除，这是因为我们建立了参照完整性中的级联删除相关记录。
第4页（共5页）
第5页（共5页）。
数据库系统基础教程第三章答案
数据库系统基础教程第三章答案本页仅作为文档封面，使用时可以删除This document is for reference only-rar21year.MarchExercise 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 could have multipleaddresses. The same is true for phones. These days, a person could have a landline and a cellularphoneExercise 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 wehave 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 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 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 dependenciesABC 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 single attributes will also lead to a closure of ABCD. Knowing this, we can enumerate the rest of 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 thatA1A2…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 of A1A2…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 people with 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 . AB C). A Social Security Number can also uniquely identify a person’s name . A C). However, gender does not uniquely determine a name .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 . AB C). However, neither A nor B can uniquely identify a point .A C andBC 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 onlyfunctional 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.Let’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 theA1A2…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 were given 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. SupposeA1A2...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 BC 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 AIf 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 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 B and 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 , 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 , suppose we have the initial set of attributes of the closure as ABC. Suppose also that we have FDs C D and D E. According to Algorithm , 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 theTaking the FD D E and augmenting both sides with attributes ABC we get the FD ABCD ABCDE. Usingattribute 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 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 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 the nontrivial FDs. They areB 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 and BD 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 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 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 C, 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 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 ABCD and ABE as decomposed relations. Using Algorithm 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 again, we discover that ABC is in BCNF since AB is the only key and AB C is the only nontrivial FD. Going back to relation ABE, following Algorithm 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 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 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 of the process of decomposition involves finding {A}+ to form one decomposed relation 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.3Yes, we will still get the same result. Both A B and A BC have A on the left side and part of the process of decomposition involves finding {A}+ to form one decomposed relation 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 pg. 95.Suppose that an instance of relation R only contains two tuples.A B C1 2 3 425The projections of R onto the relations with schemas {A,B} and {B,C} are:A B1 2 42If we do a natural join on the two projections, we will get:A B C1 2 3 1 2 5 4 2 3 425The result of the natural join is not equal to the original relation R.Exercise 3.4.1aThis is the initial tableau:A B C D Ea b c d 1 e 1 a 1 b c d e 1 ab 1cd 1eA B C D Eabcd 1e 1B C 2 3 25Since 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 DA B C D Ea b c d ea1b c d e1a b1c d1eSince there is an unsubscripted row, the decomposition for R is lossless for this set of FDs. Exercise 3.4.1cThis is the initial tableau:A B C D Ea b c d1e1a1b c d e1a b1c d1eThis is the final tableau after applying FDs A D, D E and B D.A B C D Ea b c d ea1b c d ea b1c d eSince there is an unsubscripted row, the decomposition for R is lossless for this set of FDs. Exercise is the initial tableau:A B C D Ea b c d1e1a1b c d e1a b1c d1eThis is the final tableau after applying FDs A D, CD E and E DA B C D Ea b c d ea1b c d eSince there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.Exercise 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 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 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 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 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 the solution to Exercise 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 the solution to Exercise 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 , we can decompose into relations using the minimal basis B C and B 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 the solution to Exercise 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 the solution to Exercise 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 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 the solution to Exercise 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 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 , 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 the solution to Exercise 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. The 3NF violations are C E, D E, BC E, BD E, CD E and BCD E.Using algorithm , 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 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 attributes 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 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 the left 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 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。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Assignment1
∙Run createtables.sql script which creates tables according to the following schema: ∙Faculty (URL, FirstName, LastName, LoginID)
∙GradStudents (URL, LastName, FirstName, LoginID, Office, EntryYear, College)
∙UndergradStudents (LastName, FirstName, LoginID, EntryYear, URL)
∙TA (LoginID, ClassNumber, Quarter)
∙ResearchInterests (ResearchArea, FacultyLogin)
∙Advise (Student, Advisor)
∙Office (Location, PhoneNumber)
∙Run data.sql script, which fills the tables with sample data.
∙Answer the following questions (write and test SQL queries) on the data and schema provided:
∙1. Which research areas are our faculties interested in?
∙2. How many graduate students have offices in 428?
∙3. What are the first and last names of the undergrads that have homepages on the server?
∙4. Give the first and last name of all faculty members who advise students who have offices in 224. Note that the table advise stores loginid s of faculty members
in the field advisor.
∙5. What's the total number of undergraduates, graduates, and faculty members in the department?
∙6. What is the average year that students entered the department? Note, you do not need to get SQL to give you an integer.
∙7. What are the logins of all the people in our database have the first name "Brian"?
∙8. Which first name(s) is most common in department members?
∙9. How many graduate students with the first name "Michael" entered the department for each year since 1995? Note, you do not have to list years that had
no "Michael"s in them, but the years must be in descending order, and you must
use the HA VING clause in your answer.
∙10. Which first names are represented among exactly two of Faculty, Undergrads, and Grads? (a name does not appear in one of the three tables of people, and
appears at least once in each of the other two.)
∙11. Give the login of all TAs for 142, and, if possible, give their URLs as well.
∙12. Give an interesting query, English explanation, and answer that involves at least two joins that is not already in the assignment.。