数据库系统基础教程第二章复习资料.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.4hExercise 2.4.4iExercise 2.4.5The result of the na

24、tural 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.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the

25、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.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result a

26、nd 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 include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we

27、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 result of 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 o

28、perator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples 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.Selection