完整版数据库系统基础教程第二章答案.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 rel

2、ation 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 acctNosavings type12000 balanceFor relation Customers and the first tuple, the components are:R

3、obbie firstNameBanks lastName901-222 idNo12345 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

4、, balance)Customers (firstName,lastName,idNo, account)Exercise 2.2.1fA suitable domain for each attribute:acctNo Integertype Stringbalance Integer firstName String lastName StringidNo String (because there is a hyphen we cannot use Integer) account IntegerExercise 2.2.1gAnother equivalent way to pre

5、sent the Account relation:acctNobalancetype34567P25-savings23456p 1000checking1234512000savingsAnother equivalent way to present the Customers relation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Exercise 2.2.2Examples of attributes that are created for

6、 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), a unique serial nu

7、mber 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 of 5! = 120 ways. Al

8、so, 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.1aCREATE TABLE Product (m

9、aker 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,scree n DECIMAL(3,1),price DECIMAL(7,2);Exer

10、cise 2.3.1dCREATE TABLE Pri nter (model CHAR(30), color BOOLEAN, type CHAR (10), price DECIMAL(7,2);Exercise 2.3.1eALTER TABLE Prin ter DROP color;Exercise 2.3.1fALTER TABLE Laptop ADD od CHAR (10) DEFAULT noneExercise 2.3.2aCREATE TABLE Classes (class CHAR(20), type CHAR(5), cou ntry CHAR(20), n um

11、Gu ns INTEGER, bore DECIMAL(3,1), displaceme nt INTEGER);Exercise 2.3.2bCREATE TABLE Ships (n ame CHAR(30),class CHAR(20), lau nched 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

12、.3.2eALTER TABLE Classes DROP bore;Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30);Exercise 2.4.1aR1 := spfeed 3妒C)R2 := nwdei(RI)R1 := h 10(Laptop)R2 := Product汇丨(R1)R3 ：= nmaker (R2)Exercise 2.4.1cR1 ：= maker=B (Product 1 PC)R2 := maker=B (Product 1 Laptop)R3 := maker=B (Product 匚：.Printer)R4 :

13、= nd ,price (R1R5 := nd ,price (R2R6:= mSde price (R3R7 := R4 R5、丿 R6modelprice100464910056301006104920071429Exercise 2.4.1dR1 := color = true AND type = laser (Printer) R2 := ndel (R1) model30033007-Exercise 2.4.1eR1 ：= type=iaptop (Product)R2 := type=Pc (Product)R3 ：= mnker(R1)R4 ：= nmaker(R2)R5 :

14、= R3 -R4makerFGExercise 2.4.1fR1 := pCi(PC)R2 ：= PC2(PC)R3 := R1 匸:二 I (PCI.hd = PC2.hd AND PCI.model PC2.model) R2R4 := hd(R3)Exercise 2.4.1gR1 ：= pci(PC)R2 := pc2(PC)R3 ：= R1 - (PCI.speed = PC2.speed AND PCI.ram = PC2.ram AND PCI.model 2. 8PC) nnker,model Product)p(maker2,model2) (R2)R3Exercise 2.4.1kExercise 2.4.2aTtnodelPCR4 ：= R2 ，- (maker = maker? AND model model2)R5 ：= nker(R4)makerBEExercise 2.4.1iR1=rrndel,speed(PC)R2=rndel,speed(Laptop)R3=R1 U R2R4= P(model2,speed2) (R3)R5= mndel,speed (R3 (s