数据库系统基础教程第二章答案汇编.docx

1、数据库系统基础教程第二章答案汇编Exercise 2.2.1aFor 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

2、 relation Customers, 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 acctNo savings type 12000 balanceFor relation Customers and the first tuple, the compon

3、ents are: Robbie firstName Banks lastName 901-222 idNo 12345 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:Accou

4、nts ( acctNo, type, balance)Customers ( firstName, lastName, idNo, account)Exercise 2.2.1fA suitable domain for each attribute: acctNo Integer type String balance Integer firstName String lastName String idNo String (because there is a hyphen we cannot use Integer) account IntegerExercise 2.2.1gAnot

5、her equivalent way to present the Account relation:acctNobalancetype3456725savings234561000checking1234512000savingsAnother equivalent way to present the Customers relation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Exercise 2.2.2Examples of attributes

6、 that are created for primarily serving 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

7、), a unique serial number used by the automotive 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

8、 of 5! = 120 ways. Also, the columns can 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.1aCR

9、EATE TABLE Product ( maker CHAR(30), model CHAR(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 DEC

10、IMAL(3,1), price DECIMAL(7,2);Exercise 2.3.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), ty

11、pe CHAR(5), country CHAR(20), numGuns INTEGER, 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 CH

12、AR(30), result CHAR(10);Exercise 2.3.2eALTER TABLE Classes DROP bore;Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30);Exercise 2.4.1a R1 := speed 3.00 (PC)R2 := model(R1)model100510061013 Exercise 2.4.1b R1 := hd 100 (Laptop)R2 := Product (R1)R3 := maker (R2)makerEABFG Exercise 2.4.1c R1 := maker=

13、B (Product PC)R2 := maker=B (Product Laptop)R3 := maker=B (Product Printer)R4 := model,price (R1)R5 := model,price (R2)R6: = model,price (R3)R7 := R4 R5 R6modelprice100464910056301006104920071429Exercise 2.4.1d R1 := color = true AND type = laser (Printer)R2 := model (R1) model30033007 Exercise 2.4.

14、1e R1 := type=laptop (Product)R2 := type=PC(Product)R3 := maker(R1)R4 := maker(R2)R5 := R3 R4makerFG Exercise 2.4.1f R1 := PC1(PC) R2 := PC2(PC) R3 := R1 (PC1.hd = PC2.hd AND PC1.model PC2.model) R2 R4 := hd(R3)hd25080160 Exercise 2.4.1g R1 := PC1(PC) R2 := PC2(PC) R3 := R1 (PC1.speed = PC2.speed AN

15、D PC1.ram = PC2.ram AND PC1.model PC2.model) R2 R4 := PC1.model,PC2.model(R3)PC1.modelPC2.model10041012 Exercise 2.4.1h R1 := model(speed 2.80(PC) model(speed 2.80(Laptop) R2 := maker,model(R1 Product) R3 := R3(maker2,model2)(R2)R4 := R2 (maker = maker2 AND model model2) R3R5 := maker(R4)makerBEExer

16、cise 2.4.1i R1 := model,speed(PC) R2 := model,speed(Laptop) R3 := R1 R2 R4 := R4(model2,speed2)(R3)R5 := model,speed (R3 (speed speed2 ) R4)R6 := R3 R5R7 := maker(R6 Product)makerBExercise 2.4.1j R1 := maker,speed(Product PC) R2 := R2(maker2,speed2)(R1) R3 := R3(maker3,speed3)(R1) R4 := R1 (maker =

17、maker2 AND speed speed2) R2R5 := R4 (maker3 = maker AND speed3 speed2 AND speed3 speed) R3R6 := maker(R5)makerADE Exercise 2.4.1k R1 := maker,model(Product PC) R2 := R2(maker2,model2)(R1) R3 := R3(maker3,model3)(R1) R4 := R4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model model2) R2 R6 := R3 (

18、maker3 = maker AND model3 model2 AND model3 model) R5 R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3) R6 R8 := maker(R7)makerABDE Exercise 2.4.2aExercise 2.4.2bExercise 2.4.2cExercise 2.4.2dExercise 2.4.2eExercise 2.4.2fExercise 2.4.2gExercise 2.4.2hExercise 2.4.2iExerc

19、ise 2.4.2jExercise 2.4.2kExercise 2.4.3a R1 := bore 16 (Classes)R2 := class,country (R1)classcountryIowaUSANorth CarolinaUSAYamatoJapanExercise 2.4.3b R1 := launched 1921 AND displacement 35000 (R1)R3 := name (R2) nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoExercise 2.4.3

20、e R1 := battle=Guadalcanal(Outcomes) R2 := Ships (ship=name) R1 R3 := Classes R2R4 := name,displacement,numGuns(R3)namedisplacementnumGunsKirishima320008Washington370009Exercise 2.4.3f R1 := name(Ships) R2 := ship(Outcomes) R3 := R3(name)(R2) R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMiss

21、ouriMusashiNew JerseyNorth CarolinaRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of WalesRodneyScharnhorstSouth DakotaWest VirginiaYamashiroExercise 2.4.3g From 2.3.2, assuming that every class

22、has one ship named after the class. R1 := class(Classes) R2 := class(name class(Ships) R3 := R1 R2classBismarckExercise 2.4.3h R1 := country(type=bb(Classes) R2 := country(type=bc(Classes) R3 := R1 R2countryJapanGt. Britain Exercise 2.4.3i R1 := ship,result,date(Battles (battle=name) Outcomes) R2 :=

23、 R2(ship2,result2,date2)(R1) R3 := R1 (ship=ship2 AND result=damaged AND date date2) R2 R4 := ship(R3) No results from sample data.Exercise 2.4.4aExercise 2.4.4bExercise 2.4.4cExercise 2.4.4dExercise 2.4.4eExercise 2.4.4fExercise 2.4.4gExercise 2.4.4h在调查中我们注意到大多数同学都比较注重工艺品的价格，点面氛围及服务。关于DIY手工艺制品的消费调查

24、Exercise 2.4.4i公司成功地创造了这样一种气氛：商店和顾客不再是单纯的买卖关系，营业员只是起着参谋的作用，顾客成为商品或者说是作品的作参与者，营业员和顾客互相交流切磋，成为一个共同的创作体Exercise 2.4.5据上述部分的分析可见，我校学生就达4000多人。附近还有两所学校，和一些居民楼。随着生活水平的逐渐提高，家长给孩子的零用钱也越来越多，人们对美的要求也越来越高，特别是大学生。他们总希望自己的无论是衣服还是首饰都希望与众不同，能穿出自己的个性。但在我们美丽的校园里缺少自己的个性和琳琅满目的饰品，所以我们的小饰品店存在的竞争力主要是南桥或是市区的。这给我们小组的创业项目提供

25、了一个很好的市场机会。The 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 both columns of the attributes and their values are identical.Exercise 2.4.6大学生对手工艺制作兴趣的调研根据调查资料分析：大学生的消费购买能力还是有限的，为此DIY手工艺品的消费不能高，这才有广阔的市场。Union

26、（2）物品的独一无二If 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 tuple. Thus the union operator is monotone.（3） 心态问题价格便宜些 服务热情周到 店面装饰有

27、个性 商品新颖多样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 relation, then the result set will incl

28、ude the added tuple. Thus the intersection operator is monotone.图1-3 大学生偏爱的手工艺品种类分布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 also that tuple t is in R but not in S. The resu