《数据库系统基础教程第二章答案》由会员分享,可在线阅读,更多相关《数据库系统基础教程第二章答案(19页珍藏版)》请在金锄头文库上搜索。
1、Exercise 2.2.1a For relation Accounts, the attributes are: acctNo, type, balance For relation Customers, the attributes are: firstName, lastName, idNo, account Exercise 2.2.1b For relation Accounts, the tuples are: (12345, savings, 12000), (23456, checking, 1000), (34567, savings, 25) For relation C
2、ustomers, the tuples are: (Robbie, Banks, 901-222, 12345), (Lena, Hand, 805-333, 12345), (Lena, Hand, 805-333, 23456) Exercise 2.2.1c For relation Accounts and the first tuple, the components are: 123456 acctNo savings type 12000 balance For relation Customers and the first tuple, the components are
3、: Robbie firstName Banks lastName 901-222 idNo 12345 account Exercise 2.2.1d For 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.1e An example database schema is: Account
4、s ( acctNo, type, balance ) Customers ( firstName, lastName, idNo, account ) Exercise 2.2.1f A 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 Integer Exercise 2.2.1g
5、 Another equivalent way to present the Account relation: acctNobalancetype 3456725savings 234561000checking 1234512000savings Another equivalent way to present the Customers relation: idNofirstNamelastNameaccount 805-333LenaHand23456 805-333LenaHand12345 901-222RobbieBanks12345 Exercise 2.2.2 Exampl
6、es of attributes 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 Identifi
7、cation Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles. Exercise 2.2.3a We 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.3b We can order
8、the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880 Exercise 2.2.3c We 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
9、 is n!m! Exercise 2.3.1a CREATE TABLE Product ( makerCHAR(30), modelCHAR(10) PRIMARY KEY, type CHAR(15) ); Exercise 2.3.1b CREATE TABLE PC ( model CHAR(30), speed DECIMAL(4,2), ram INTEGER, hd INTEGER, price DECIMAL(7,2) ); Exercise 2.3.1c CREATE TABLE Laptop ( model CHAR(30), speed DECIMAL(4,2), ra
10、m INTEGER, hd INTEGER, screen DECIMAL(3,1), price DECIMAL(7,2) ); Exercise 2.3.1d CREATE TABLE Printer ( model CHAR(30), color BOOLEAN, type CHAR (10), price DECIMAL(7,2) ); Exercise 2.3.1e ALTER TABLE Printer DROP color; Exercise 2.3.1f ALTER TABLE Laptop ADD od CHAR (10) DEFAULT none; Exercise 2.3
11、.2a CREATE TABLE Classes ( class CHAR(20), type CHAR(5), country CHAR(20), numGuns INTEGER, bore DECIMAL(3,1), displacement INTEGER ); Exercise 2.3.2b CREATE TABLE Ships ( name CHAR(30), class CHAR(20), launched INTEGER ); Exercise 2.3.2c CREATE TABLE Battles ( name CHAR(30), date DATE ); Exercise 2
12、.3.2d CREATE TABLE Outcomes ( ship CHAR(30), battle CHAR(30), result CHAR(10) ); Exercise 2.3.2e ALTER TABLE Classes DROP bore; Exercise 2.3.2f ALTER TABLE Ships ADD yard CHAR(30); Exercise 2.4.1a R1 := speed 3.00 (PC) R2 := model(R1) Exercise 2.4.1b R1 := hd 100 (Laptop) R2 := Product (R1) model 10
13、05 1006 1013 R3 := maker (R2) Exercise 2.4.1c R1 := maker=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 R6 modelprice 1004649 1005630 10061049 20071429 Exercise 2.4.1d R1 := color = true
14、AND type = laser (Printer) R2 := model (R1) Exercise 2.4.1e R1 := type=laptop (Product) R2 := type=PC(Product) R3 := maker(R1) R4 := maker(R2) R5 := R3 R4 Exercise 2.4.1f R1 := PC1(PC) R2 := PC2(PC) R3 := R1 (PC1.hd = PC2.hd AND PC1.model speed2 AND speed3 model2) R2 R6 := R3 (maker3 = maker AND mod
15、el3 model) R5 R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3) R6 R8 := maker(R7) Exercise 2.4.2a model speed3.00 PC Exercise 2.4.2b maker B E maker A D E maker A B D E Laptop hd 100 Product maker Exercise 2.4.2c maker=B model,price maker=B model,price maker=B model,pric
16、e ProductPCLaptopPrinterProductProduct Exercise 2.4.2d Printer color = true AND type = laser model Exercise 2.4.2e type=laptoptype=PC makermaker ProductProduct Exercise 2.4.2f PC1PC2 (PC1.hd = PC2.hd AND PC1.model speed2 AND speed3 model2) (maker3 = maker AND model3 model) (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3) maker Exercise 2.4.3a R1 := bore 16 (Classes) R2 := class,country (R1) c