# Associative Data Modeling Demystified – Part2

Guest blog post by Athanassios Hatzis

## Introduction

In the previous article of this series we examined the association construct from the perspective of Entity-Relationship data model. In this post we demonstrate how Topic Map data model represents associations. In order to link the two we continue with another SQL query from our relational database

“
SELECT suppliers.sid,
suppliers.sname,
suppliers.scountry,
catalog.catcost,
catalog.catqnt,
parts.pid,
parts.pname,
parts.pcolor
FROM suppliers
INNER JOIN (parts
INNER JOIN [catalog]
ON parts.pid = catalog.catpid)
ON suppliers.sid = catalog.catsid
WHERE (( ( parts.pid ) = 998 ))
ORDER BY catalog.catcost;
“

This will fetch all the rows of a result set where we are looking for the minimum catalogue price of a Red Fire Hydrant Cap and who is the supplier that manufactures this part. The reader will notice that apart from the deficiensy of the nested JOINs, (see here), we had to formalize our search in SQL language in order to get back our result. Wouldn’t be nice if we could engage the user in a codeless style of search, independent of the business case. Let us see the difference with the Topic Map data model first.

## Associations in Topic Map Data Model

Perhaps there is not a better software tool out there to introduce you to Topic Maps than Wandora information management application, see how.

### Tuples to Associations

Our first step is to build a Topic Map data model from the SQL result set above. With Wandora this is easy thanks to its powerful set of extractors. Here we use an Excel adjacency list extractor to convert each spreadsheet row of this Excel file to a Topic MapAssociation.

Tuples of a Relation – Wandora Associations

In the right panel of the screen capture, you may see that we have four associations of typeTuple. They are all sorted by the catcost column. This is the role that cells of this column play in the Tuple association. In our example each Tuple is an instance of the Excel class with a maximum of 8 members and each member plays a role in the association. You may agree that this Topic Map model view of data looks already very familiar to the user that is accustomed with tables.

But behind the scenes Topic Map associations are notably different from the n-ary tuples of the relational model. In the left panel of our screen capture you can see all the data that are extracted from the spreadsheet. Notice that no data value is repeated. Each association is constructed from single instance values and this also means that associations are sharing values among them. We can visualize the network of associations by switching to Wandora’s Graph topic panel. From the left panel, we select the minimum price of the part, which is 11.7 and then we expand this node on the Graph topic panel. This way the first association will be drawn that includes as members all the other values that this cell is associated with. One of them is USA and plays the scountry role. We can right click on the value and expand again the nodes (associated members). Two associations are displayed now on the graph that share four common values between them.

Two Associations in the Graph Topic Panel of Wandora. Brown labels indicate the type (Tuple) of association and the role (sname) of one of its members

### SQL to Topic Map Filtering

Another important observation we should make at this point is that instead of writing any query to fetch those suppliers that are located in USA we simply filtered the table based on this value. We are able to do this, because Topic Map data model works with single instance values that are linked bidirectionally. Accordingly, data is always normalized and the main operations of the relational algebra such as set operations, selection and projection, can be performed. For instance, filtering associations that have USA member is equivalent to selecting rows from SQL. Moreover, the user can traverse interactively the graph starting from any value without writing a single line of code.

### Topic Map Serialization

To understand better the underlying structure of data in the previous example, we have serialized a Topic Map in LTM format. Dropping this LTM file into an empty topic panel, we invoke the import function of Wandora. Then we expand the topic tree and double click on the 998 cell. The following screen capture looks pretty much the same as the one we have generated from extracting the Excel spreadsheet above. The main difference is that now we have two association types, one for Catalogue tuples and another for Part tuples. Part 998participates in five associations (tuples) in total, four of them are from Catalogue table and one from Part table. We have also taken a minimum number of members, i.e. fields (columns), for our associations to keep it simple.

 
/* 1 Association of catalog part no 998 with "Red" and "Fire Hydrant Cap" */ Prt( prtName08:pname, prtID08:pid, prtColorRed:pcolor )  /* 4 Associations of catalog part no 998 with supplier Ids and catalog prices */ Cat( prtID08:catpid, supPrice18:catcost, supID18:catsid ) Cat( prtID08:catpid, supPrice14:catcost, supID14:catsid ) Cat( prtID08:catpid, supPrice16:catcost, supID16:catsid ) Cat( prtID08:catpid, supPrice12:catcost, supID12:catsid ) 



Associations of part no. 998

Because of the single instance feature of Topic Maps, If we switch to Wandora’s Graph topic panel we can visualize these associations.

Associations of part no. 998

### R3DM Type System in Wandora

We expand our previous example with tuples from three tables and a rich type system.