Trajectory Data Warehouses - Data Warehouse Systems: Design and Implementation - page 494

12.9 Exercises
12.1 Consider the train company application described in Ex. 3.2 and whose
conceptual multidimensional schema was obtained in Ex. 4.3 .Add
spatiotemporal data to this schema to transform it into a trajectory
data warehouse. You must analyze the dimensions, facts, and measures,
and define which of them can be extended with spatiotemporal features.
12.2 Transform the conceptual schema obtained as solution for Ex. 12.1
into a relational one. This schema should correspond to the relational
schema without spatiotemporal features obtained in Ex. 5.3 .
12.3 Write in SQL the following queries on the relational schema obtained
in Ex. 12.2 :
(a) Give the trip number, origin, and destination of trips that contain
segments with a duration of more than 3 h and whose length is
shorter than 200km.
(b) Give the trip number, origin, and destination of trips that contain
at least two segments served by trains from different constructors.
(c) Give the trip number of trips that cross at least three cities in less
than 2h.
(d) Give the total number of trips that cross at least two country
borders in less than 4 h.
(e) Give the average speed by train constructor. This should be
computed taking the sum of the durations and lengths of all
segments with the same constructor and obtaining the average. The
result must be ordered by average speed.
(f) For each possible number of total segments, give the number of
trips in each group and the average length, ordered by number of
segments. The result should look like (5, 50, 85; 4, 30, 75; ...) ,
meaning that there are 50 trips with 5 segments with an average
length of 85km, 30 trips with 4 segments of average length of 75km,
and so on.
(g) Give the trip number and origin and destination stations for trips
such that at least one segment of the trip runs for at least 100km
within Germany.
12.4 Consider an application that monitors air quality measuring the values
of a set of pollutants (such as particulate matter or sulfur dioxide) at a
fixed number of stations. Measures are collected hourly or daily and are
expressed both in traditional units (like micrograms per cubic meter,
or parts per million) or using an air quality index, which in Europe has
5levelsusingascalefrom0(verylow) to greater than 100 (very high).
Stations are typically located alongside roads and obviously located in
districts. Finally, there is also field data corresponding to land use and
temperature.