Exercises - Chapter: 03

* 5.1 Consider the data warehouse of a telephone provider given in Ex. 3.1. Draw a
star schema diagram for the data warehouse.

A star schema for the data warehouse of Ex. 3.1

* 5.2 For the star schema obtained in the previous exercise, write in SQL the queries
given in Ex. 3.1.
(a) List the total amount collected by each call program in 2012.
SELECT ProgramName, SUM(Amount)
FROM Calls C, Time T, CallProgram P
WHERE [Link] = [Link] AND [Link] = 2012 AND
[Link] = [Link]
GROUP BY ProgramName
(b) List the total duration of calls made by customers from Brussels in 2012.
SELECT SUM(TotalDuration)
FROM Calls C, Time T, Customer U
WHERE [Link] = [Link] AND [Link] = 2012 AND
[Link] = [Link] AND
[Link] = ’Brussels’

PAGE \* MERGEFORMAT 5
(c) List the total number of weekend calls made by customers from Brussels to
customers in Antwerp in 2012.
SELECT SUM(NumberCalls)
FROM Calls C, Time T, Customer F, Customer To
WHERE [Link] = [Link] AND [Link] = 2012 AND
( [Link] = ’Saturday’ OR
[Link] = ’Saturday’) AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = ’Brussels’ AND [Link] = ’Antwerp’
(d) List the total duration of international calls started by customers in Belgium in
2012.
SELECT SUM(TotalDuration)
FROM Calls C, Time T, Customer F, Customer To
WHERE [Link] = [Link] AND [Link] = 2012 AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = ’Belgium’ AND [Link] <> ’Belgium’
(e) List the total amount collected from customers in Brussels who are enrolled in
the corporate program in 2012.
SELECT SUM(Amount)
FROM Calls C, Time T, Customer U, CallProgram P
WHERE [Link] = [Link] AND [Link] = 2012 AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = ’Brussels’ AND [Link] = ’Corporate’
* 5.3 Consider the data warehouse of the train application given in Ex. 3.2. Draw a
snowflake schema diagram for the data warehouse with hierarchies for the train
and station dimensions.

PAGE \* MERGEFORMAT 5
 A snowflake schema for the data warehouse of Ex. 3.2
* 5.4 For the snowflake schema obtained in the previous exercise, write in SQL the
queries given in Ex. 3.2.
(a) List the total number of kilometers made by Alstom trains during 2012
departing from French or Belgian stations.
SELECT SUM(NoKilometers)
FROM Segments F, Time T, Train TR, Model M,
Constructor C, Station S, City CI, State ST, Country CO
WHERE [Link] = [Link] AND [Link] = ’2012’ AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND

PAGE \* MERGEFORMAT 5
[Link] = ’Alstom’ AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
( [Link] = ’France’ OR
[Link] = ’Belgium’ )
(b) List the total duration of international trips during 2012, that is, trips departing
from a station located in a country and arriving at a station located in another
country.
SELECT SUM(Duration)
FROM Segments F, Time T, Station A1, City C1, State S1,
Station A2, City C2, State S2
WHERE [Link] = [Link] AND [Link] = ’2012’ AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] <> [Link]
(c) List the total number of trains that departed from or arrived at Paris during
July 2012.
SELECT COUNT(*)
FROM Segments F, Time T, Trip TR, Station S, City C
WHERE [Link] = [Link] AND
[Link] = ’July’ AND [Link] = ’2012’ AND
( [Link] = [Link] OR
[Link] = [Link] ) AND
[Link] = [Link] AND
[Link] = ’Paris’
(d) List the average duration of train segments in Belgium in 2012.
PAGE \* MERGEFORMAT 5
SELECT SUM(Duration)
FROM Segments F, Time T, Station A1, City C1, State S1,
Country CO1, Station A2, City C2, State S2, Country CO2
WHERE [Link] = [Link] AND [Link] = ’2012’ AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = ’Belgium’ AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link] = ’Belgium’
(e) For each trip, list the average number of passengers per segment, that means,
take all the segments of each trip, and average the number of passengers.
SELECT [Link], AVG(NoPassengers)
FROM Segments F, Trip T
WHERE [Link] = [Link]
GROUP BY [Link]
* 5.5 Consider the university data warehouse described in Ex. 3.3. Draw a
constellation schema for the data warehouse taking into account the different
granularities of the time dimension.

PAGE \* MERGEFORMAT 5
 A constellation schema for the data warehouse in Ex. 3.3
* 5.6 For the constellation schema obtained in the previous exercise, write in SQL
the queries given in Ex. 3.3.
(a) List by department the total number of teaching hours during the academic year
2012-2013.
SELECT DepartmentName, SUM(NoHours)
FROM Teaching T, AcademicSemester S, Department D
WHERE [Link] = [Link] AND
[Link] = [Link]
AcademicYear = ’2012-2013’
GROUP BY DepartmentName
(b) List by department the total amount of research projects during the calendar
year 2012.
SELECT DepartmentName, SUM(Amount)

PAGE \* MERGEFORMAT 5
FROM Research R, Professor P, Department D, Time T
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND Year = ’2012’
GROUP BY DepartmentName
(c) List by department the total number of professors involved in research projects
during the calendar year 2012.
SELECT DepartmentName, COUNT(ProfessorID)
FROM Research R, Professor P, Department D, Time T
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND Year = ’2012’
GROUP BY DepartmentName
(d) List by department the total number of courses delivered during the academic
year 2012{2013.
SELECT DepartmentName, COUNT(CourseID)
FROM Teaching T, AcademicSemester S, Department D
WHERE [Link] = [Link] AND
[Link] = [Link]
AcademicYear = ’2012-2013’
GROUP BY DepartmentName
(e) List by department and funding agency, the total number of projects started in
2012.
SELECT DepartmentName, AgencyName, COUNT(ProfessorID)
FROM Research R, Professor P, Department D,
FundingAgency F, Time T
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND Year = ’2012’ AND
[Link] = [Link]
GROUP BY DepartmentName, AgencyName

PAGE \* MERGEFORMAT 5
* 5.7 Translate the MultiDim schema obtained for the French horse race application
in Ex. 4.5 into the relational model.

A logical translation of the conceptual schema in Fig. 5.4

* 5.8 Translate the MultiDim schema obtained for the Formula One application in
Ex. 4.7 into the relational model.

PAGE \* MERGEFORMAT 5
 Constellation schema of the French horse racing data warehouse in Ex. 4.5
* 5.9 The Research and Innovative Technology Administration (RITA) coordinates the
US Department of Transportation’s (DOT) research programs. It collects several statistics
about many kinds of transportation means, including the information about flight
segments between airports summarized by month. There is a set of tables T T100I
Segment All Carrier XXXX, one by year, ranging from 1990 up until now. These tables
include information about the scheduled and actually departured flights, the number of
seats sold, the freight transported, and the distance traveled, among other ones. The
schema and description of these tables is given in Table 5.1. A set of lookup tables given
in Table 5.2 include information about airports, carriers, and time. The schemas of these

PAGE \* MERGEFORMAT 5
lookup tables are composed of just two columns called Code and Description. The
mentioned web site describes all tables in detail.
From the information above, construct an appropriate data warehouse schema. Analyze
the input data and motivate the choice of your schema.

Snowflake schema of the Formula One data warehouse in Ex. 4.7

PAGE \* MERGEFORMAT 5
* 5.10 Implement in Analysis Services the MultiDim schema obtained for the French
horse race application in Ex. 4.5 and the relational data warehouse obtained in Ex. 5.7.
* 5.11 Implement in Mondrian the MultiDim schema obtained for the Formula One
application in Ex. 4.7 and the relational data warehouse obtained in Ex. 5.8.

PAGE \* MERGEFORMAT 5
PAGE \* MERGEFORMAT 5
PAGE \* MERGEFORMAT 5
PAGE \* MERGEFORMAT 5