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

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 Customers, 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:

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:

Accounts (

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

Another equivalent way to present the Account relation:

acctNo

balance

type

34567

25

savings

23456

1000

checking

12345

12000

savings

Another equivalent way to present the Customers relation:

idNo

firstName

lastName

account

805-333

Lena

Hand

23456

805-333

Lena

Hand

12345

901-222

Robbie

Banks

12345

Exercise 2.2.2

Examples 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 Identification 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 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 is n!m!

Exercise 2.3.1a

CREATE TABLE Product (

maker

CHAR(30),

model

CHAR(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),

ram 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.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.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)

model

1005

1006

1013

Exercise 2.4.1b

R1 := σ

hd ≥ 100

(Laptop)

R2 := Product

(R1)

R3 :=

π

maker

(R2)

maker

E

A

B

F

G

Exercise 2.4.1c

R1 := σ

maker=B

(Product

R2 := σ

maker=B

(Product

R3 := σ

maker=B

(Product

R4 := π

model,price

(R1)

R5 := π

model,price

(R2)

R6: = π

model,price

(R3)

R7 := R4

R5

model

1004

1005

1006

2007

R6

PC)

Laptop)

Printer)

price

649

630

1049

1429

Exercise 2.4.1d

R1 := σ

color = true AND type = laser

(Printer)

R2 := π

model

(R1)

model

3003

3007

Exercise 2.4.1e

R1 := σ

type=laptop

(Product)

R2 := σ

type=PC

(Product)

R3 := π

maker

(R1)

R4 :=

π

maker

(R2)

R5 := R3

–

R4

maker

F

G

Exercise 2.4.1f

R1 := ρ

PC1

(PC)

R2 := ρ

PC2

(PC)

R3 := R1

( = AND <> )

R2

R4 := π

hd

(R3)

hd

250

80

160

Exercise 2.4.1g

R1 := ρ

PC1

(PC)

R2 := ρ

PC2

(PC)

R3 := R1

( = AND = AND < )

R2

R4 := π

,

(R3)

1004

1012

Exercise 2.4.1h

R1 :=

π

model

(σ

speed ≥ 2.80

(PC))

π