Graph Database style explorations of relational database with Formula One data– Oracle Database 23c Property Graph

The core Formula One data could be represented in a graph like this:

Drivers compete in races that are hosted on circuits. A result is achieved by the driver (position and points for the championship).

If this is the graph, some questions can easily be answered:

With whom has a specific driver (say Max Verstappen) shared the podium?

MATCH (a IS driver where a.name = ‘Max Verstappen’) -[b IS result where b.position <= 3]-> (c IS race) <-[d is result where d.position <= 3]- (e IS driver) 
WHERE NOT VERTEX_EQUAL(a,e)

Which drivers from the same country have shared the podium?

MATCH (a IS driver ) -[b IS result where b.position <= 3]-> (c IS race) <-[d is result where d.position <= 3]- (e IS driver) 
WHERE NOT VERTEX_EQUAL(a,e) and a.nationality = e.nationality

Lucky day: who won two or more races on the same day in the year (different years obviously)?

MATCH (a IS driver ) -[b IS result where b.position = 1]-> (c IS race)
  ,     (a IS driver ) -[d IS result where d.position = 1]-> (e IS race)
WHERE NOT VERTEX_EQUAL(c,e) and to_char(c.race_date,’DDMM’) = to_char(e.race_date,’DDMM’)

There are many more questions that can be answered using graph patterns in a graph pattern matching query. Patterns that are often more intuitive – easier to write, understand, debug and maintain – than their pure SQL equivalents. Of course the questions answered here can be answered using SQL as well – but these statements may not be as straightforward.

The formula one data is available to me (and you: see how in this article that allows you to fire up an Oracle Database 23c Free instance in a Gitpod workspace with a single click) in a relational database schema – 14 tables:

I have create a SQL Property Graph on top of a subset of these tables. That can be visualized like this:

The SQL statement I used to create the property graph:

This statement defines the three vertex-tables (driver, race, circuit) and the two edge tables (results, hostings). It uses the relational tables drivers, races, results and circuits to draw the data from. Edge table hostings is a little bit special: it is based on table races that is also used to define the vertex table races. This edge table defines the link from races to circuits. This link is implemented in the relational schema through the foreign key (column) circuitid. This means that every record in table races produces a vertex entry (race) and an edge (hosted_by).

Using this property graph, the questions posed earlier in this article can easily be answered:

With whom has a specific driver (say Max Verstappen) shared the podium?

If you want to know how often he was on the podium with a specific other driver:

Which drivers from the same country have shared the podium?

Zooming in: which nationalities have ever had two or more drivers on the podium?

Lucky day: who won two or more races on the same day in the year (different years obviously)?

Property Graph can help make seemingly complex questions – when approached relationally – become fairly straightforward. SQL Property Graph allows us to combine the two approaches – to refine or enrich the result from the property graph query or even combine multiple query results. I expect t0 be working on this more frequently because I like this approach. I would like to learn how well it scales.

And, as I mentioned, I am hoping for SQL Property Graphs to support Views as underpinning for edge and vertex tables in addition to tables and materialized views.

Resources

Source for this article: https://github.com/lucasjellema/gitpod-oracle-database-23c-free/blob/main/explorations/formula-one-sql-property-graph.sql.

Oracle Database 23c Documentation on SQL Property Graph: https://docs.oracle.com/en/database/oracle/property-graph/23.1/spgdg/getting-started-oracle-property-graphs.html

GitHub Repo with sources for this article: https://github.com/lucasjellema/gitpod-oracle-database-23c-free.

Oracle Database 23c Free environment with the Formular One data schema can be started using a Gitpod workspace definition. Read about it in this article .