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

12.6 Consider the alternative Northwind trajectory data warehouse given
in Fig. 12.7 , which is obtained by partitioning the deliveries into zones
instead of into roads. Translate the conceptual schema into a logical
one.
12.7 Write in SQL the following queries on the relational schema obtained
in Ex. 12.6 :
(a) For each truck, give the total number of hours serviced per country.
(b) For each delivery and each zone, give the total time driven in the
zone and the average and maximum speed within the zone.
(c) List the deliveries that started and ended in the same zone and
have passed through a zone different from the former.
(d) Give the deliveries, together with their length, for deliveries that
started in a zone that belongs to two different states and ended in
a zone that belongs to exactly one state.
(e) Total number of deliveries that started in a zone that contains the
city of Brussels, drove for at least 2h within France, and ended in
a zone belonging to Antwerp.
(f) For each delivery and each zone, give the total number of hours
that the delivery drove within the zone in rainy conditions and at
more than 20 ◦ C.
(g) Trucks that drove in March 2012 in zones such that more than 50%
of their area is at more than 1,000m above sea level.