数据库第二章答案
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:
Another equivalent way to present the Customers relation:
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)
Exercise 2.4.1b
R1 := σhd ≥ 100 (Laptop)
R2 := Product (R1)
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
Exercise 2.4.1d
R1 := σcolor = true 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 PC 1.model <> PC 2.model) R2
R4 := πhd (R3)
Exercise 2.4.1g
R1 := ρPC1(PC)
R2 := ρPC2(PC)
R3 := R1 (PC1.speed = PC 2.speed AND PC 1.ram = PC 2.ram AND PC 1.model < PC2.model) R2
R4 := πPC1.model,PC 2.model (R3)
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) R3
R5 := πmaker (R4)
Exercise 2.4.1i
R1 := πmodel,speed (PC)
R2 := πmodel,speed (Laptop)
R3 := R1 R2
R4 := ρR4(model2,speed 2)(R3)
R5 := πmodel,speed (R3 (speed < speed2 ) R4)
R6 := R3 – R5
(R6 Product)
Exercise 2.4.1j
R1 := πmaker,speed (Product PC)
R2 := ρR2(maker2,speed 2)(R1)
R3 := ρR3(maker3,speed 3)(R1) R4 := R1 (maker = maker2 AND speed <> speed 2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5)
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
(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)
Exercise 2.4.2a
πmodel σspeed≥3.00
PC
Exercise 2.4.2b
Laptop
σhd ≥
100 Product
π
maker
Exercise 2.4.2c
σmaker=B
πmodel,price σmaker=B πmodel,price
σmaker=B πmodel,price
Product PC Laptop Printer Product
Product
Exercise 2.4.2d
Printer σcolor = true AND type = laser
πmodel
σtype=laptop
σtype=PC πmaker
πmaker –
Product Product
Exercise 2.4.2f
ρPC1
ρPC2 (PC1.hd = PC2.hd AND PC1.model <> PC2.model)
πhd PC PC
Exercise 2.4.2g
ρPC1
ρPC2PC PC (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model)
πPC1.model,PC2.model
PC Laptop
σspeed ≥ 2.80
σspeed ≥ 2.80πmodel
π
model
πmaker,model
ρR3(maker2,model2)
(maker = maker2 AND model <> model2)
πmaker
Exercise 2.4.2i
PC Laptop
Product
πmodel,speed πmodel,speed ρR4(model2,speed2)
πmodel,speed
(speed < speed2 )
maker
Exercise 2.4.2j
Product
PC
πmaker,speed ρR3(maker3,speed3)
ρR2(maker2,speed2)
(maker = maker2 AND speed <> speed2)
(maker3 = maker AND speed3 <> speed2 AND speed3 <> speed)
maker
Exercise 2.4.2k
Product PC
πmaker,model ρR2(maker2,model2)
ρR3(maker3,model3)ρR4(maker4,model4)
(maker = maker2 AND model <> model2)
(maker3 = maker AND model3 <> model2 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)
Exercise 2.4.3b
R1 := σlaunched < 1921 (Ships)
R2 := πname (R1)
Exercise 2.4.3c
R1 := σbattle=Denmark Strait AND result=sunk(Outcomes)
R2 := πship (R1)
Exercise 2.4.3d
R1 := Classes Ships
R2 := σlaunched > 1921 AND displacement > 35000 (R1)
R3 := πname (R2)
Exercise 2.4.3e
R1 := σbattle=Guadalcanal(Outcomes)
R2 := Ships (ship=name) R1
R3 := Classes R2
R4 := πname,displacement,numGuns(R3)
Exercise 2.4.3f
R1 := πname(Ships)
R2 := πship(Outcomes)
R3 := ρR3(name)(R2)
R4 := R1 R3
Exercise 2.4.3g
From 2.3.2, assuming that every class has one ship named after the class.
R1 := πclass(Classes)
R2 := πclass(σname <> class(Ships))
R3 := R1 – R2
Exercise 2.4.3h
R1 := πcountry(σtype=bb(Classes))
R2 := πcountry(σtype=bc(Classes))
R3 := R1 ∩ R2
Exercise 2.4.3i
R1 := πship,result,date(Battles (battle=name) Outcomes)
R2 := ρ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.4a
πclass,country
σbore ≥ 16
Classes
Exercise 2.4.4b
πname
σlaunched < 1921
Ships
Exercise 2.4.4c
πship
σbattle=Denmark Strait AND result=sunk
Outcomes
Exercise 2.4.4d
πname
σlaunched > 1921 AND displacement > 35000
Classes Ships Exercise 2.4.4e
σ
battle=Guadalcanal Outcomes Classes
(ship=name)πname,displacement,numGuns
Exercise 2.4.4f Ships Outcomes
πname
πship ρR3(name)
Exercise 2.4.4g
Classes Ships πclass σname <> class πclass
–
Exercise 2.4.4h
Classes Classes σtype=bb σtype=bc
πcountry πcountry
∩
Exercise 2.4.4i
Battles Outcomes (battle=name)
πship,result,date
ρR2(ship2,result2,date2)
(ship=ship2 AND result=damaged AND date < date2)
πship
Exercise 2.4.5
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