数据库系统基础教程第二章答案解析

1、完美WORD格式Exercise 221aFor relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExercise 2.2.1bFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000), (34567, savings, 25)For relation Cust

2、omers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)Exercise 2.2.1cFor relation Accounts and the first tuple, the components are:123456 acctNosavings type 12000 balanceFor relation Customers and the first tuple, the components are:Robbiefirs

3、tNameBanks lastName901-222idNo12345 accountExercise 2.2.1dFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)Exercise 2.2.1eAn example database schema is:Accounts (acctNo, type, balance

4、)Customers (firstName,lastName, idNo, account )Exercise 221fA suitable domain for each attribute:acctNo Integertype StringbalanceIntegerfirstNameStringlastNameStringidNo String (because there is a hyphen we cannot use Integer) accountIntegerExercise 2.2.1gAnother equivalent way to present the Accoun

5、t relation:acctNobalancetype3456725savings234561000checking1234512000savingsAnother equivalent way to present the Customers relation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Exercise 2.2.2Examples of attributes that are created for primarily serving

6、as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the au

7、tomotive industry to identify vehicles.Exercise 2.2.3aWe can order the three tuples in any of 3! = 6 ways. Also, the columns can be ordered in any of 3! = 6 ways. Thus, the number of presentations is 6*6 = 36.Exercise 2.2.3bWe can order the three tuples in any of 5! = 120 ways. Also, the columns can

8、 be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880Exercise 2.2.3cWe can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!Exercise 2.3.1aCREATE TABLE Product (makerCHAR(30),modelC

9、HAR(10) PRIMARY KEY,type CHAR(15) );Exercise 2.3.1bCREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2), ram INTEGER, hd INTEGER, price DECIMAL(7,2) );Exercise 2.3.1cCREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2), ram INTEGER, hd INTEGER, screen DECIMAL(3,1), price DECIMAL(7,2) );Exercise 2.3.

10、1dCREATE TABLE Printer (model CHAR(30), color BOOLEAN, type CHAR (10), price DECIMAL(7,2) );Exercise 2.3.1eALTER TABLE Printer DROP color;Exercise 2.3.1fALTER TABLE Laptop ADD od CHAR (10) DEFAULT none；Exercise 2.3.2aCREATE TABLE Classes ( class CHAR(20), type CHAR(5), country CHAR(20), numGuns INTE

11、GER, bore DECIMAL(3,1), displacement INTEGER );Exercise 2.3.2bCREATE TABLE Ships ( name CHAR(30), class CHAR(20), launched INTEGER );Exercise 2.3.2cCREATE TABLE Battles ( name CHAR(30), date DATE );Exercise 2.3.2dCREATE TABLE Outcomes ( ship CHAR(30), battle CHAR(30), result CHAR(10) );Exercise 2.3.

12、2eALTER TABLE Classes DROP bore;Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30);Exercise 2.4.1aR1 := speed 13.00(PC)R2 :=麻odel (R1)Exercise 2.4.1bR1 := hd o 100 (Laptop)R2 := Product : 4 (R1)R3 := TWer (R2)Exercise 2.4.1cR1 :=ma ker=B (Product IPC)R2 :=maker=B(Product 、- Laptop)R3 :=ma ker=B (Pro

13、duct，-Printer)R4 :=model,price(R1)R5 ：=modprice(R2)R6： =mod,pnce(R3)R7 ：= R4,.J R5 - R6modelprice100464910056301006104920071429(Printer)Exercise 2.4.1dR1 ：=colOT= true AND type = laserR2 := modes (R1)model 3003 3007一Exercise 2.4.1eR1 ：= typ e=iaptop(Product)R2 := typ e=PC (Product)R3 ：= ma画(R1)R4 ：=

14、麻aker (R2)R5 := R3 R4专业整理分享Exercise 2.4.1fR1 ：= pc p(PC)R2 := pc 2(PC)PC2.hd AND PCI.model PC2.model)R2R3 := R1=二(Pci.hdR4 := hd (R3)Exercise 2.4.1gR2PC1.modelPC2.model10041012R1 :=pc p(PC)R2 :=pc 2(PC)R3 ：= R1 (PCI.speed = PC2.speed AND PCI.ram = PC2.ram AND PCI.model 2.8o(PC)Tmodel ( Cspeed 2.80(L

15、aptop)maker,model (R1Product)R3 :=R3 (maker2,model2)(R2)R4 := R2(maker = maker2 AND model model2)R3R5 := maker (R4)makerBExercise 2.4.1iR1 ：= modspeed (PC)R2 ：= mod8,speed (Laptop)R3 := R1 . J R2R4 : = R4(model2,speed2) (R3)R5 :=model,speed(R3 ；l(speed ：l(maker3 = maker AND speed3 speed2 AND speed3

16、speed)R3R6 := R3 R5R7 := ma砥(R6二二 Product) maker-BExercise 2.4.1jR1 := ma砥,speed (Product .XI PC)R2 := R2 (maker2,speed2) (R1)R3 := R3 (maker3,speed3) (R1)(maker = maker2 AND speed speed2)R6 := ma砥(R5)Exercise 2.4.1kR1 := maker,model (Product I 二二. PC)R2 := R2 (maker2,model2) (R1)R3 := R3 (maker3,mo

17、del3) (R1)R4 :=R4 (maker4,model4)(R1)R5 := R1(maker = maker2 AND model model2)R2R6 := R3(maker3 = maker AND model3 model2 AND model3 model)R5R7 := R4(maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)R6R8 := ma画(R7)Exercise 2.4.2aTmodelOspeed 3.00PCExercise 242bTmakerX z chd j00Prod

18、uctLaptopExercise 2.4.2cTImodel,pricecmaker=BXZPrinterProductU而odel,price丽odel,pricecmaker=Bomaker=BXX/XZLaptopProductPCProductExercise 242dmodelOcolor = true AND type = laserPrinterExercise 2.4.2eOtype=PCotype=laptopProduct ProductExercise 2.4.2f二;，(PC1.hd = PC2.hd AND PC1.model PC2.model)PCPCExerc

19、ise 2.4.2g脱C1.model,PC2.modelI (PCI.speed = PC2.speed AND PCI.ram = PC2.ram AND PCI.model PC2.model)PCPCExercise 2.4.2hTmaker工”(maker = maker2 AND model model2)pR3(maker2,model2)丽aker,model(speed 芟.80Ospeed 芟.80PCExercise 2.4.2iLaptopExercise 2.4.2j厢akerX丽odel,speed丽odel,speedPCLaptopTlmakerI & (mak

20、er3 = maker AND speed3 speed2 AND speed3 speed)I (maker = maker2 AND speed speed2)fR3(maker3,speed3)ProductExercise 2.4.2kPCTlmakerI (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)二(maker3 = maker AND model3 model2 AND model3 model)pR4(maker4,model4)ProductPCExercise 2.4.3aR2 :=

21、(R1)R1 := abore 16(Classes)classcountryIowaUSANorth CarolinaUSAYamatoJapanclas sCountryExercise 2.4.3bR1 := lau nched 1921 AND disp acement 35000(R1)R3 := name (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoR1R2R3R4Exercise 2.4.3e=batt(e=Guadalcanal(Outcomes)=Ships - (sh

22、ip=name) R1=Classes I R2namedisplacementnumGunsKirishima320008Washington370009narrie,displacement,numGuns(R3)Exercise 2.4.3fR1 := na侬(Ships)R2 := ship Outcomes)R3 := R3 (name) (R2)R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaRamilliesRenownRepulseResolu

23、tionRevenge型脸Royaa SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDukeforkFaeonGoodritainKing George VPrince of WalesRodneyScharnhorstSouth DakotaWest VirginiaYamashiroNo results from sample data.Exercise 243gFrom 2.3.2, assuming that every class has one ship named after the class.R1 :=Tl

24、class (Classes)R2 := clas 虱R3 := R1 (name classR2(Ships)Exercise 2.4.4a佗lass,countryObore 导6ClassesExercise 2.4.4bExercise 2.4.3hR1 ：=cou nty ( pe=bb(Classes)R2 ：= cou nGy ( pe=bcR3 := R1A R2(Classes)Exercise 2.4.3iR1 := ship,result,date(Battles - (battle=name) Outcomes)R2 := R2(ship2,result2,date2)

25、 (R1)R3 := R1 1二(ship=ship2 AND result=damaged AND date date2)R2R4 := ship t(R3)而amelaunched 1921 AND displacement 35000ClassesShipsExercise 2.4.4e用ame,displacement,numGuns的attle=GuadalcanalShipsOutcomesExercise 2.4.4fTishipShipsOutcomesExercise 2.4.4g序ame classClassesShipsExercise 2.4.4hOtype=bbOty

26、pe=bcClassesClassesExercise 2.4.4iTfehip(ship=ship2 AND result=damaged AND date date2)pR2(ship2,result2,date2)府hip,result,dateBattlesOutcomesExercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has b

27、oth columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that

28、 tuple. Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other rela

29、tion, then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result.Suppose we have relations R and S and we are computing R- S. Suppose als

30、o that tuple t is in R but not in S. The resultof R - S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tupl

31、es of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get

32、all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone.

33、Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x

34、S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have ( m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of t

35、he tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join opera

36、tor is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create a

37、dditional tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tupl

38、es of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.Exercise 2.4.7aIf all the tuples of R and S ar

39、e different, then the union hasn + m tuples, and this number is the maximum possible.The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Then the union has max( m , n) tuples.Exercise 2.4.7bIf all the tuples in one relation can

40、pair successfully with all the tuples in the other relation, then the natural join hasn * m tuples. Thisnumber would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other

41、relation. Then the natural join has zero tuples.Exercise 2.4.7cIf the condition C brings back all the tuples of R, then the cross product will containn * m tuples. This number would be themaximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings bac

42、k none of the tuples of R. Then the cross product has zero tuples.Exercise 2.4.7dAssuming that the list of attributes L makes the resulting relationl(R) and relation S schema c ompatible, then the maximumpossible tuples is n. This happens when all of the tuples of l(R) are not in S 兀The minimum numb

43、er of tuples that can appear in the result occurs when all of the tuples inl(R) appear in S. Then the differ encehas max( n - m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1. MR X S)2. R X 8 Ge(S)where 8 is the duplicate -elimination operator in Section 5.2 pg. 2

44、133. R - (R - ti(R X S)Exercise 2.4.9Defining r as the schema of R1. R -成R ;4 S)Exercise 2.4.10%,A2 An(RS)Exercise 2.5.1a(Jspeed 500(PC) = ?Model 1011 violates this constraint.Exercise 2.5.1b(Jscreen 15.4 AND hd PC.ram AND Laptop.pricePC.prKcPC x Laptop)=Models 2002,2006,2008 violate the constraint.

45、Exercise 2.5.2a尼lass ( cbore 16 (Classes)=The Yamato class violates the constraint.Exercise 2.5.2b尼lass (CnumGuns 9 AND bore 14(Classes)=No violations to the constraint.Exercise 2.5.2cThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) :=cgname (Classes CX Ships)R2(clas