How to deal with a "many to many" relationship from a RDB in our CSV2RDF work?

[candlecao]

How to deal with a "many to many" relationship from a RDB in our CSV2RDF work?

[candlecao]

A "many to many" relationship is a term within the scope of mapping cardinality, also of schema designing as to an relational database(RDB). Such as one album has many tracks and otherwise one track can appear in more than one albums. This is a quite common phenomenon. For many-to-many relationships, It's determined to use a "relation set" spreadsheet for modeling instead of an "entity set" spreadsheet. So, in our process of CSV2RDF, we should distinguish between "entity set" and "relation set" if possible.

In views of schema of an RDB, the fundamental difference between "entity set" and "relation set" can be seen from the composition of primary key of a spread sheet(or CSV table):

1. For an entity set, We usually render the primary using a single attribute on the first column of the table.
2. For an relation set, it often has a composite primary key, typically formed by combining the primary keys of the participating entity sets, thereby uniquely identifying each relationship instance.

The significance of a relation set is, to contain the attributes that describe the relation, so in perspective of a triple of subject-predicate-object, the attributes of a relation set are like an expression of "adverbial"...

Let's take some from TheSession DataBase for example:

1. The dumped CSVs don't align with the original schema, I supposed. (I mean, it probably doesn't differentiate the tables the way the tables are organized in the database.)
2. So we can find examples like:

2.1 in recordings.csv

<style> </style>

recording_id	artist	recording	track	number	tune	tune_id	artist_url
https://thesession.org/recordings/1032	Various Artists	Music For The Sets Volume 3: Pay The Piper	12	1	Cooley's	https://thesession.org/tunes/1	https://thesession.org/recordings/artists/39
https://thesession.org/recordings/1032	Various Artists	Music For The Sets Volume 3: Pay The Piper	12	2	Bird In The Bush, The	https://thesession.org/tunes/629	https://thesession.org/recordings/artists/39
https://thesession.org/recordings/3536	Accordéonistes Du Québec	Dans Tous Les Cantons	23	1	Cooley's	https://thesession.org/tunes/1	https://thesession.org/recordings/artists/2004

--we can see <https://thesession.org/recordings/1032> has 2 "tune_id"s and <https://thesession.org/tunes/1> appears in 2 "recording_id"s.

In this table, the track column and the number column are attributes of the "recording-tune" relationship instead of attributes solely of recording_id only. The track value indicates the track NO. of the tune within the recording; the number value represents the position or sequence of the tune in the recording or possibly something else. Hence we have to use named graph or blank node.(see the following)

2.2 in sets.csv

<style> </style>

tuneset_id	username	name	tune_id
https://thesession.org/members/1/sets/5	Jeremy	Cooley's	https://thesession.org/tunes/1
https://thesession.org/members/1/sets/5	Jeremy	Wise Maid, The	https://thesession.org/tunes/118
https://thesession.org/members/46273/sets/22	michaelkeogh	Cooley's	https://thesession.org/tunes/1

--we can see <https://thesession.org/members/1/sets/5> has 2 "tune_id"s and <https://thesession.org/tunes/1> appears in 2 "tuneset_id"s.

[fujinaga]

Can you give us the example from The Session?

[candlecao]

Yes. I am doing that right now.

[candlecao]

To render "many to many" relationship with 2 methods:

Let us convert the 3 rows of data from aforementioned excerpt of "recordings.csv" into(only choose columns--recording_id, track, number, tune_id):

1. Named Graph

@prefix wdt: <http://www.wikidata.org/prop/direct/> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

<https://thesession.org/recordings/1032> <https://thesession.org/tunes> <https://thesession.org/tunes/1> <urn:relationship:1032_1> .
<https://thesession.org/tunes/1> wdt:P658 "12"^^xsd:int <urn:relationship:1032_1> .
<https://thesession.org/tunes/1> wdt:P1114 "1"^^xsd:int <urn:relationship:1032_1> .

<https://thesession.org/recordings/1032> <https://thesession.org/tunes> <https://thesession.org/tunes/629> <urn:relationship:1032_629> .
<https://thesession.org/tunes/629> wdt:P658 "12"^^xsd:int <urn:relationship:1032_629> .
<https://thesession.org/tunes/629> wdt:P1114 "2"^^xsd:int <urn:relationship:1032_629> .

<https://thesession.org/recordings/3536> <https://thesession.org/tunes> <https://thesession.org/tunes/1> <urn:relationship:3536_1> .
<https://thesession.org/tunes/1> wdt:P658 "23"^^xsd:int <urn:relationship:3536_1> .
<https://thesession.org/recordings/3536> <https://thesession.org/tunes/1> wdt:P1114 "1"^^xsd:int <urn:relationship:3536_1> .                         

If you upload it on Virtuoso, then you can query in SPARQL like this:

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
SELECT * where {
  graph ?g {
    ?s ?p ?o
  }
}

or refer to #98 (which however might be a hallucination)

2. Blank node

@prefix wdt: <http://www.wikidata.org/prop/direct/> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
<https://thesession.org/recordings/1032> <https://thesession.org/tunes> [
  <https://thesession.org/tunes> <https://thesession.org/tunes/1>;
 wdt:P658 "12"^^xsd:int;  
 wdt:P1114 "1"^^xsd:int
].

<https://thesession.org/recordings/1032> <https://thesession.org/tunes> [
  <https://thesession.org/tunes> <https://thesession.org/tunes/629>;
 wdt:P658 "12"^^xsd:int;  
 wdt:P1114 "2"^^xsd:int
].

<https://thesession.org/recordings/3536> <https://thesession.org/tunes> [
  <https://thesession.org/tunes> <https://thesession.org/tunes/1>;
 wdt:P658 "23"^^xsd:int;  
 wdt:P1114 "1"^^xsd:int
].

(That will directly impact our programing for CSV2RDF into Virtuoso.)

[candlecao]

Hi Ich(@fujinaga ), it's so weird that I cannot upload the Named Graph content to Virtuoso (by putting them in a file with suffix .nq) But if I don't use the @prefix xsd: <http://www.w3.org/2001/XMLSchema#> ., it then can be uploaded. Like this:

<https://thesession.org/recordings/1032> <http://example.org/includes> <https://thesession.org/tunes/1> <urn:relationship:1032_1> .
<https://thesession.org/recordings/1032> <http://www.wikidata.org/prop/direct/P658> "12"^^<http://www.w3.org/2001/XMLSchema#int> <urn:relationship:1032_1> .
<https://thesession.org/tunes/1> <http://www.wikidata.org/prop/direct/P1114> "1"^^<http://www.w3.org/2001/XMLSchema#int> <urn:relationship:1032_1> .
#...

Do you know why?

[candlecao]

Any way, I put up the issue on openlink/virtuoso-opensource#1313.