Exercise 10.3.5
Section 10.4
Exercise 10.4.1
Movies(
title TitleType,
year YearType,
length DurationType,
)
StarsIn(
movieTitle TitleType,
movieYear YearType,
starName PersonNameType
)
12
)
Exercise 10.4.2
(a) CREATE TYPE NameType AS(
first VARCHAR(30),
middle VARCHAR(50),
);
Exercise 10.4.3
CREATE TYPE ProductType AS(
maker CHAR(5),
model INTEGER,
type CHAR(8)
);
CREATE TABLE PC(
model REF(ProductType) SCOPE Product,
speed DECIMAL(5,2),
ram INTEGER,
hd INTEGER
price DECIMAL(10,2)
);
Exercise 10.4.4
Model attribute in Products cannot be a reference to the tuple in the relation for
Exercise 10.4.5
CREATE TYPE ClassType AS (
class VARCHAR(30),
type CHAR(2),
country VACHAR(30),
bore INTEGER,
disp INTEGER
);
CREATE TYPE OutcomeType AS (
ship REF(ShipType),
battle REF(BattleType),
result VARCHAR(10)
);
Section 10.5
Exercise 10.5.1
(a) SELECT star->name
FROM StarsIn
WHERE movie->title = ’Dogma’;
Exercise 10.5.2
(a) SELECT model->maker
FROM PC
WHERE hd > 60;
(b) SELECT DISTINCT model->maker
Exercise 10.5.3
(a) SELECT x.name
FROM Ships x
WHERE x.class->disp > 35000;
Exercise 10.5.4
CREATE FUNCTION StarLEG(p1 StarType,
p2 StarType )
RETURNS INTEGER
17
Exercise 10.5.5
CREATE PROCEDURE DeleteStar(IN pName VARCHAR(50))
BEGIN
Section 10.6
Exercise 10.6.1
(a) Dimension attributes are: cust, date, proc, memory, hd, od.
Exercise 10.6.2
First we could select the number of orders that had DVD disks and the number of
orders that had CD disks. This would show just the totals over all orders.
SELECT D1.type, COUNT(*)
18
SELECT MONTH(F.date) MONTHS, D1.type, COUNT(*)
Section 10.7
Exercise 10.7.1
(a) The ratio is 11
10!10
, or about 2.59.
Exercise 10.7.2
(a) Assuming the column name for SUM(val) in SalesCube is val:
SELECT dealer, val
FROM SalesCube
(b) Assuming the column name for SUM(cnt) in SalesCube is cnt:
19
SELECT cnt
;
(c) Assuming the column names for SUM(cnt) and SUM(val) in SalesCube are
cnt and val:
SELECT val/cnt
;
Exercise 10.7.3
The rollup would not help and would make it more difficult to ensure that we do
Exercise 10.7.4
CREATE MATERIALIZED VIEW OrdersCube(
20
Exercise 10.7.5
(a) SELECT D1.speed, MONTH(F.date), SUM(F.tquant)
FROM OrdersCube F, Proc D1
WHERE F.proc = D1.procID
(b) SELECT D1.type, D2.type, SUM(F.tquant)
FROM OrdersCube F, Proc D1, HD D2
WHERE F.proc = D1.procID
(c) SELECT MONTH(F.date), SUM(tprice)/SUM(F.tquant)
FROM OrdersCube F, Proc D1
WHERE F.proc = D1.procID
;
21
Exercise 10.7.6
Yes, other rollups could contain these tuples. Those rollups can be formed by
rearranging the group by list so that columns we need to be aggregated are at the
Exercise 10.7.7
In the worst case, the fact table could have only one row, the CUBE(F) would add
22