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	100
150	200
200	250

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.

Design and implement ADTs that can be used to store sets of intervals.
Examine and implement how ADTs can be indexed for in an object-relational DBMS such as Oracle 10g.
Remodel existing an application that uses relational tables to use nested tables or ADTs.
Conduct a performance comparison of modeling intervals using relational table, nested tables and ADTs. The performance study should cover inner and outer joins and the set operations (intersection, union, and except).
Find and describe the advantages and disadvantages of relational tables, nested tables, and ADTs in modling interval data.
Generalize the idea of using intervals to also support points.

Best regards
Kristian