Solution to exercises on data integration

Exercise 1: Ontop

The mappings (mappings/ontop_mappings.obda) can be defined as:

[PrefixDeclaration]
urb:        http://leifhka.org/in5800/urb/
owl:        http://www.w3.org/2002/07/owl#
rdf:        http://www.w3.org/1999/02/22-rdf-syntax-ns#
xml:        http://www.w3.org/XML/1998/namespace
xsd:        http://www.w3.org/2001/XMLSchema#
foaf:       http://xmlns.com/foaf/0.1/
obda:       https://w3id.org/obda/vocabulary#
rdfs:       http://www.w3.org/2000/01/rdf-schema#

[MappingDeclaration] @collection [[
mappingId   suburbia-citizen
target      urb:suburbia_citizen{cid} a urb:Person ; urb:name {name}^^xsd:string ; urb:locatedAt urb:suburbia_building{home} ; urb:urbId {urb_id}^^xsd:string .
source      SELECT * FROM suburbia.citizen

mappingId   suburbia-organization-buildings
target      urb:suburbia_building_{orgid} a urb:Building ; urb:address {address}^^xsd:string ; urb:zipcode {zipcode}^^xsd:string .
source      SELECT lower(regexp_replace(name, ' ', '_', 'g')) AS orgid, * FROM suburbia.organization

mappingId   suburbia-organization
target      urb:{orgid} a urb:Organization ; urb:name {name}^^xsd:string ; urb:locatedAt urb:suburbia_building_{orgid} .
source      SELECT lower(regexp_replace(name, ' ', '_', 'g')) AS orgid, * FROM suburbia.organization

mappingId   suburbia-farmer
target      urb:suburbia_citizen{cid} urb:worksAt urb:suburbia_citizen{cid} .
source      SELECT cid FROM suburbia.citizen WHERE occupation = 'farmer'

mappingId   suburbia-fisher
target      urb:suburbia_citizen{cid} urb:worksAt urb:docks .
source      SELECT cid FROM suburbia.citizen WHERE occupation = 'fisher'

mappingId   suburbia-student
target      urb:suburbia_citizen{cid} urb:worksAt urb:university .
source      SELECT cid FROM suburbia.citizen WHERE occupation = 'student'

mappingId   suburbia-pupil
target      urb:suburbia_citizen{cid} urb:worksAt urb:suburbian_school .
source      SELECT cid FROM suburbia.citizen WHERE occupation = 'pupil'

mappingId   suburbia-home
target      urb:suburbia_building{hid} a urb:Building ; urb:address {home_address}^^xsd:string ; urb:zipcode {zip_code}^^xsd:string .
source      SELECT * FROM suburbia.home

mappingId   urbania-citizen
target      urb:urbania_citizen{cid} a urb:Person ; urb:name {name}^^xsd:string ; urb:locatedAt urb:suburbia_building{apartment_building} ; urb:urbId {urb_id}^^xsd:string ; urb:worksAt urb:organization{works_at} .
source      SELECT * FROM urbania.citizen

mappingId   urbania-building
target      urb:urbania_building{bid} a urb:Building ; urb:address {address}^^xsd:string ; urb:zipcode {zipcode}^^xsd:string .
source      SELECT * FROM urbania.building

mappingId   urbania-organization
target      urb:organization{oid} a urb:Organization ; urb:name {name}^^xsd:string ; urb:locatedAt urb:urbania_building{offices_in} .
source      SELECT * FROM urbania.organization
]]

and the ontology (semantics/urb_global.ttl) is:

@prefix owl:  <http://www.w3.org/2002/07/owl#> .
@prefix rdf:  <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix xsd:  <http://www.w3.org/2001/XMLSchema#> .
@prefix urb:  <http://leifhka.org/in5800/urb/> . 

urb:contains a owl:ObjectProperty ;
    owl:inverseOf urb:locatedAt .

urb:hasEmployee a owl:ObjectProperty ;
    owl:inverseOf urb:worksAt .

urb:worksAt a owl:ObjectProperty ;
    rdfs:range urb:Organization .

Exercise 2: Foreign Data Wrappers

The foreign data wrapper script (mappings/fdw.sql) can de defined as:

BEGIN;

CREATE SERVER suburbia_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'dbpg-ifi-kurs01', port '5432', dbname 'in5800_leifhka_remote');

CREATE USER MAPPING FOR in5800_leifhka_user
SERVER suburbia_db_server
OPTIONS (user 'in5800_leifhka_remote_user', password 'Yaif1OhGii');

-- Create schema to map foreign source into
CREATE SCHEMA suburbia; -- Could also be named something different

-- Map the foreign source into the created schema
IMPORT FOREIGN SCHEMA suburbia
FROM SERVER suburbia_db_server
INTO suburbia;

COMMIT;

and the GAV-mappings (mappings/global.sql) can be defined as:

BEGIN;

CREATE SCHEMA urb;

CREATE VIEW urb.building(bid, address, zipcode) AS
SELECT 'sb' || hid::text, home_address, zip_code
FROM suburbia.home
UNION ALL
SELECT name || ' building', address, zipcode
FROM suburbia.organization
UNION ALL
SELECT 'ub' || bid::text, address, zipcode
FROM urbania.building;

CREATE VIEW urb.organization(oid, name, offices_in) AS
SELECT name, name, name || ' building'
FROM suburbia.organization
UNION ALL
SELECT urb_id, name, 'sb' || home
FROM suburbia.citizen
WHERE occupation = 'farmer'
UNION ALL
SELECT 'uo' || oid::text, name, 'ub' || offices_in::text
FROM urbania.organization;

CREATE VIEW urb.person(urb_id, name, lives_in, works_at) AS
SELECT urb_id, name, 'sb' || home,
    CASE
        WHEN occupation = 'farmer' THEN urb_id
        WHEN occupation = 'fisher' THEN 'Docks'
        WHEN occupation = 'pupil' THEN 'Suburbian School'
        ELSE (SELECT oid FROM urb.organization WHERE name = 'University') -- student
    END
FROM suburbia.citizen
UNION ALL
SELECT urb_id, name, 'uo' || apartment_building, 'uo' || works_at::text
FROM urbania.citizen;

COMMIT;