Handling Interval Data with Object-Relational DBMSs

dat5 project proposal, autumm 2004

Intervals are often used in modeling spatial data. As an example consider a table that stores the pavement type on roads in Denmark. Using the relational model this can be model with the table shown below where the id column is the primary key of the table, the road_id column is the logical id for the road, the pavement_type is the type of pavement on a part (or interval) of the road, the from column is the number of meters from the start of the road where a new part of the road starts, and similar the to column is the number of meters from the start of the road where a road part ends.

id road_id pavement_type from to
101 1 A 1 100
102 1 B 100 150
103 1 A 150 200
104 1 A 200 250
105 1 B 250 300

Here we only show data for a single road identified by road_id = 1. The road is split into five parts (or intervals). This data modeling only uses the features of the relational data model and does not take advantages of the object-relational features available in DBMSs such as Oracle 10g, PostgreSQL 7.4, and IBM DB2 8.1. In these object-relational DBMSs the table above can be remodeled using either a nested table or a user-defined abstract data type (ADT). The latter is similar to a class in object-oriented programming languages such as Java or C#.

If we try to remodel the previous example using nested tables it could look as follows.

road_id pavement_type distance
1 A
1 100
150 200
200 250
1 B
100 150
250 300

As can be seen from the remodeled table there are now only three columns and the distance column is it self a table (a nested table). A different way to remodel the orginal table would be to use an ADT. In this case the remodeled table would look as follows.

road_id pavement_type distance
1 A MyADT(1,100,150,200,200,250)
1 B MyADT(100, 150, 250, 300)

Here MyADT represents some internal representation of an ADT.

Having introduced the three ways interval can be modeled using an object-relational DBMS the goal of the project could be the following.

Best regards