University tutorial

This tutorial is an adaptation of the classical Ontop university tutorial (opens new window) to Ontopic Studio.

Preparation

Install the tutorial database

Windows Desktop

The tutorial database is provided in the folder %AppData%/OntopicStudio/sample-db with the name university .

Docker-compose

The tutorial database is normally installed as part of the recommended procedure for Docker-Compose (see the dedicated section). It should be available with destination-tutorial-db as hostname.

If it is not the case:

1. Go to the studio git repository
2. Run the command

docker compose -f docker-compose.yml -f tutorial/docker-compose-tutorial.yml pull && docker compose -f docker-compose.yml -f tutorial/docker-compose-tutorial.yml up`

Create a project

1. Connect to Ontopic Studio (by default http://localhost:8081 (opens new window)) and login with your user account
2. Create a project University_Tutorial and add an optional description. Select type Sample DB and save
3. Open the project

Provide the database credentials

1. Go to the Settings page

   Windows Desktop

   2. You can see the prefilled credentials for connecting to the database
      • Database: H2
      • Driver: org.h2.Driver
      • Database name: postgres
      • Username: sa
      • Connection URL: jdbc:h2:tcp://localhost:9092/C:\Users\<username>\AppData\Roaming\OntopicStudio\sample-db\destination-db;ACCESS_MODE_DATA=r
   3. Change the end of the connection URL from destination-tutorial-db to university:
      • Connection URL: jdbc:h2:tcp://localhost:9092/C:\Users\<username>\AppData\Roaming\OntopicStudio\sample-db\university;ACCESS_MODE_DATA=r

        

   Docker-compose

   2. You can see the prefilled credentials for connecting to the database
      • Database: PostgreSQL
      • Host: destination-tutorial-db
      • Database name: postgres
      • Username: postgres
      • Password: postgres2
   3. Change the database name:
      • Database name: university

4. Save the credentials and go to the Lenses page. You see there, on the left, the list of database tables and views.

Dataset

University 1

The dataset is composed of the following tables:

• uni1.student

The table uni1.student contains the local ID (s_id), first and last name of each student.

s_id	first_name	last_name
1	Mary	Smith
2	John	Doe

The column s_id is the primary key.

• uni1.academic

Similarly, the table uni1.academic contains the local ID (a_id), first and last name of each academic staff member, but also information about their position.

a_id	first_name	last_name	position
1	Anna	Chambers	1
2	Edward	May	9
3	Rachel	Ward	8

The column position is populated with magic numbers:

• 1 -> Full Professor
• 2 -> Associate Professor
• 3 -> Assistant Professor
• 8 -> External Teacher
• 9 -> PostDoc

The column a_id is the primary key.

• uni1.course

The table uni1.course contains the local ID (c_id) and title of the courses.

c_id	title
1234	Linear Algebra
1235	Analysis

The column c_id is the primary key.

• uni1.teaching

The table uni1.teaching contains the n-n relationship between courses and teachers.

c_id	a_id
1234	1
1234	2

There is no primary key, but two foreign keys to the tables uni1.course and uni1.academic.

• uni1.course-registration

The table uni1.course-registration contains the n-n relationship between courses and students.

c_id	s_id
1234	1
1234	2

There is no primary key, but two foreign keys to the tables uni1.course and uni1.student.

University 2

The dataset is composed of the following tables:

• uni2.person

The table uni2.person describes the students and the academic staff of the university.

pid	fname	lname	status
1	Zak	Lane	8
2	Mattie	Moses	1
3	Céline	Mendez	2

The column status is populated with magic numbers, which differ from the ones of the table uni1.academic:

• 1 -> Undergraduate Student
• 2 -> Graduate Student
• 3 -> PostDoc
• 7 -> Full Professor
• 8 -> Associate Professor
• 9 -> Assistant Professor

As you can see, undergraduate and graduate students are now distinguished.

The column pid is the primary key.

• uni2.course

The table uni2.course contains information about courses, their topic, their lecturer and their lab teacher.

cid	lecturer	lab_teacher	topic
1	1	3	Information security
2	8	5	Software factory
3	7	8	Software process management

The column cid is the primary key. Note that in this data source, there are at most two teachers per course. Lecturers and lab teachers are now distinguished.

• uni2.registration

The table uni2.registration contains the n-n relationship between courses and attendees.

pid	cid
2	1
10	4

There is no primary key, but two foreign keys to the tables uni2.course and uni2.person.

Modelling

In this tutorial, the ontology file is provided. We will focus on the mapping and the lenses. Our goal is to reproduce the following diagram.

Ontology

1. Download this OWL ontology file
2. Go to the Ontology page
3. Import the downloaded file through Import ontology
4. In the tab Classes you can visualize the class hierarchy

5. In the tab Properties you can visualize the properties hierarchy. In particular the Data properties are firstName, lastName and title, and the Object properties are teaches (wth two sub-properties givesLecture and givesLab), isTaughtBy, isSupervisedBy, isGivenAt and attends.

First data source: university 1

1. Go to the Lenses page
2. Among the Source Tables (on the left), select the schema uni1 to filter the tables
3. Select the table uni1.student and click on Generate mirror lens
4. Click on lenses.uni1.student on the right to open the newly created lens
5. Click on Constraints and observe that unique constraints are defined on the s_id and ssn column, while no foreign key is given

Mapping uni1.student: first class mapping entry

1. Go to the Mapping page and click on the lens lenses.uni1.student
2. Map all the rows to a class by clicking on the button C+ for creating a mapping entry
3. First of all, we need to specify the subject as a template. Make sure the option Template is selected. Open the template menu and click on Create new template. Ontopic Studio automatically proposes the template data:uni1-student/{s_id} out of the lens name and the unique constraint on the column s_id. Accept it by clicking on Create.

4. Let's now assign a class. Make sure Constant is selected for the class. In the drop-down menu below select the class uni:Student. Click and save and you are having your first mapping entry!

Mapping uni1.student: first data property mapping entry

5. Let's now map the first name of each student. Let's create a property mapping entry for the same subject by clicking on the button P+ on the right of the IRI template data:uni1-student/{s_id}.

6. You should see that subject section is already filled with the same values as before. If it is not the case, you probably clicked on the other P+ button at the top of the page. In that case, go back and click on the correct P+ button.
7. Select the data property foaf:firstName in the property menu.
8. For the object, we want to directly map the value from the column first_name. Select Literal and the type Column and then the first_name in the column drop-down menu.
9. Notice that the datatype field get automatically filled after selecting the column as xsd:string. This is because the column type is text and, according to R2RML (opens new window) character string types are mapped to that RDF datatype. Click and save to have your first data property mapping entry!

10. Repeat the same steps to also map the last name of each student.

Mapping uni1.academic

Similarly to what we did for students, we create a class mapping entry for each academic staff member and data property mappings for their first and last name.

1. Go to the Lenses page, select uni1.academic and generate a mirror lens lenses.uni1.academic. We observe that there are two unique constraints on the columns a_id and ssn, while there is no foreign key.
2. Go to the Mapping page and select the lens lenses.uni1.academic
3. Map all the rows to a class by clicking on the button C+ for creating a mapping entry. Create a new class mapping entry by clicking on the button C+.
4. As subject we create a new template: Ontopic Studio automatically proposes the template data:uni1-academic/{a_id} using the unique constraint on the column a_id.

5. Let's now assign a class. Make sure Constant is selected and in the drop-down menu select as class the data property uni:FacultyMember. Save to create a class mapping for the academic staff.

6. As for students, we map the first name of each academic member. Let's create a property mapping entry for the same subject by clicking on the button P+ on the right of the IRI template data:uni1-academic/{a_id} and select the property foaf:firstName in the drop-down menu. Finally select as object the column first_name and save.
7. Repeat the steps above also for the last name.

Mapping uni1.course: first object property mapping

1. Go to the Lenses page, select uni1.course and generate a mirror lens lenses.uni1.course. We observe that there is a unique constraint c_id.
2. Map all the rows to a class by clicking on the button C+ for creating a mapping entry.
3. We need first to specify the subject as a template. Select the option Template and click on Create new template. Ontopic Studio automatically proposes the template data:uni1-course/{c_id} out of the lens name and the unique constraint on the column c_id. Accept it by clicking on Create.
4. Let's now assign a class. Make sure Constant is selected for the class. In the drop-down menu below select the class uni:Course. Click and save.
5. Map the course titles: create a property mapping entry for the same subject by clicking on the button P+ on the right of the IRI template data:uni1-course/{c_id} and select the property uni:title from the menu. Finally select as object the column title and save.
6. Now we create a new object property mapping for the same subject. With the usual template as subject, select the property uni:isGivenAt. For object, you can see that the pre-selected kind value is not Literal as before but IRI/BNode. This is because the property uni:isGivenAt is declared in the ontology as an object property, so it expects an IRI or a BNode as value. The type Template is also pre-selected because it is the most common mechanism for building an IRI as object. However, we select the type Constant and type the value data:uni1/university, since all courses are given at the same university.

Mapping uni1.teaching

1. Go to the Lenses page, select uni1.teaching and generate a mirror lens lenses.uni1.teaching. We notice that there are not unique constraint, but two foreign keys a_id from uni1.academic and c_id from uni1.course.
2. Go to the Mapping page and select the lens lenses.uni1.teaching.
3. Let's create a mapping between the academic staff (a_id) and the taught course (c_id) through the object property teaches. Click on the button P+.
4. Choose in the subject section the template data:uni1-academic/{a_id}. Then select the property uni:teaches and in the object section the template data:uni1-course/{c_id}. Save.

Mapping uni1.registration

1. Go to the Lenses page, select uni1.course-registration and generate a mirror lens lenses.uni1.course-registration. We observe that there are two foreign keys c_id from uni1.course and s_id from uni1.student. Click on 0 mapping entries to be directed to the Mapping page.
2. Let's create a mapping between the students (s_id) and the attended course (c_id) through the object property uni:attends. Click on the button P+.
3. As subject select the template data:uni1-student/{s_id}, the property uni:attends and as object the template data:uni1-course/{c_id}.

Mapping uni1.academic: subclasses

1. Go to the Mapping page and select the lens lenses.uni1.academic.
2. We now want to map the subclasses of uni:FacultyMember depending on the values found in the column position. Let's create a new mapping entry by clicking on C+ on the right of the template data:uni1-academic/{a_id}.
3. For the class we don't want to assign the same value to each faculty member found in the lens, so we don't select the type Constant, but instead the type Dictionary.
4. Select the column position.
5. Let's now fill the first line of the dictionary. On the left field, insert the number 1 as found in the position column of the lens. In the right field, select the class uni:FullProfessor. Everytime the value 1 is found in the column position, the faculty member will be declared instance of the subclass uni:FullProfessor.
6. Click on + for adding a new line. Map this time the value 2 to the class uni:AssociateProfessor.
7. Complete with the other pairs value-class (see description of uni1.academic) and save.

Snapshot and mapping export

1. Go on the Dashboard page and click on Create snapshot. Technically speaking, it will create a Git commit with all the important files of the project.
2. Open the Repository page to see the following snapshotted/committed files:
   • lenses.json: definition of the lenses in the Ontop format.
   • mapping.ttl: R2RML mapping file defined over the lenses.
   • ontology.ttl: RDFS/OWL2QL ontology serialized in Turtle.
   • YOUR_PROJECT.ontopicprj: internal representation of your project. Can be imported in Ontopic Studio to override the mapping, lenses and the ontology.
3. Go back to the Ontopic Studio tab. Go the Mapping page. Click on Export mapping and then on Without lenses to download the mapping in R2RML that directly refers to tables in the source instead of the lenses. This file is interoperable with any R2RML processor.

SPARQL

Let's test our mapping by deploying Ontop directly in Ontopic Studio and issue our first SPARQL query.

1. Go to the Query page.
2. Execute the following SPARQL query which outputs the last names of the full professors:

PREFIX uni: <http://uni.example.org/voc#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT DISTINCT ?prof ?lastName {
  ?prof a uni:FullProfessor ;
    foaf:lastName ?lastName .
}

3. Click on View SQL to see the generated SQL query.

Inference

Ontop embeds some inference capabilities and is thus capable of answering a query like the following:

PREFIX uni: <http://uni.example.org/voc#>
SELECT DISTINCT ?teacher {
  ?teacher a uni:Teacher .
}

These inference capabilities can be, for a large part, understood as the ability to infer new mappings from the original mappings and the ontological axioms (e.g. Professor is a sub-class of Teacher).

Second data source: university 2

We now add the following mapping assertions together with the previous ones.

Mapping uni2.person

1. Go to the Lenses page, select the schema uni2 and generate a mirror lens for the table uni2.person. Open the newly created lens.
2. We can see it has similar kind of information of uni1.student but with different column names. Let's keep the lens as it is for the moment and start mapping it. For that, click at bottom right on 0 mapping entries to reach the mapping editing page for this lens.
3. Create a class mapping entry with a new IRI template data:uni2-person/{pid} to map all the rows of the lens to the class foaf:Person and save.
4. Map the first and last name of all persons through the properties foaf:firstName and foaf:lastName, respectively.
5. Map the following values of the column status to create the subclasses of the class foaf:Person by clicking on the C+ next to the template data:uni2-person/{pid} and selecting the type Dictionary:
   • 1 to uni:UndergraduateStudent
   • 2 to uni:GraduateStudent
   • 3 to uni:PostDoc
   • 7 to uni:FullProfessor
   • 8 to uni:AssociateProfessor
   • 9 to uni:AssistantProfessor

6. Snapshot the changes.
7. Go to the Query page and update the SPARQL endpoint.
8. Run the previous SPARQL and observe that it now returns more results. If you look at SQL query, you will see that it now contains a UNION.

Mapping uni2.course

1. Go to the Lenses page and generate a mirror lens for the table uni2.course.
2. Go to the Mapping page and select the lens lenses.uni2.course.
3. Create a class mapping entry with a new IRI template data:uni2-course/{cid} to map all the rows of the lens to the class uni:Course and save.
4. Map the course title to the property uni:title and select the column topic and save.
5. Create the property uni:isGivenAt for the same subject. As object choose the kind IRI/BNode and the type Constant with value data:uni2/university.

Mapping uni2.course: lecturers and lab-teachers

1. Go to the Mapping page and select the mirror lens lenses.uni2.course.
2. Create a new property mapping entry by clicking on the button P+.
3. As subject select the template data:uni2-person/{pid} and lecturer as binding. Note that the pid in the template is not a column name but just a placeholder that in this case is associated with the column lecturer.
4. Click on the property uni:givesLecture.
5. As object keep the default kind IRI/BNode and select the template data:uni2-course/{cid}.

6. Repeat the last four steps by modifying only the binding of the template to lab_teacher and the property to uni:givesLab.

Mapping uni2.registration

1. Go to the Mapping page and select the mirror lens lenses.uni2.registration.
2. Create a new property by clicking on the button P+.
3. As subject select the template data:uni2-person/{pid}, the property uni:attends and as object the template data:uni2-course/{cid}.

SPARQL

We can now run the previous SPARQL queries and observe that the results combine entries from the two datasets.