In this lecture, we will look more in depth on saturation, how it helps make data more useful, and the different approaches to saturation.

Slides

Relevant Wiki-pages

• Saturation
• Data Transformation
• Lore
• OTTR
• SQL (PostgreSQL)
• SPARQL
• OWL
• RDFS

Exercises

Oh no! There has been a grand theft at the University in the City of Urb! Someone has stolen an ancient scroll from the Archeological Institute. The scroll is believed to contain a description of an ancient language used by pirates to describe where they have burried treasure.

The council of Urb has informed the police of your great expertice on data engineering, and the police of Urb has asked you to help them find the thief and return the stolen scroll.

Note: These exericses can be done without a working soltion to the mandatory exercise. You only need the original data downloaded by the oblig’s Makefile.

Setup

Make a fresh new folder, download the Oblig’s Makefile, update the database credentials at the top of the file, and execute

make setup

Exercise 1: Who Did It?

Your first task is to find out who the thief is. The police have the folloing clues based on witness observations and a wallet dropped by one of the thieves:

Clue 1: The police has reasons to believe that there were two thieves, and that they both are citizens of Urbania.

Clue 2: In the dropped wallet the police found a piece of paper that looks as follows:

| DW | YS |
|----|----|
| 1  | 3  |
| 2  | 1  |
| 1  | 3  |
| 2  | 1  |
| 3  | 2  |
| 1  | 2  |
|----|----|
| 10 | 12 |
|----|----|

Based on the notes on and around the paper, this seems to be a score card for a game, with the initials of the players on top. Thus, they believe that at least one of the thieves has the initials on top. Based on further analysis of the note, the police belive that the two people playing the above game lives toghether in the same apartment. Therefore, you should make a view containing each citizen’s attributes (from urbania.citizen) together with the initials. Name the view citizen_i.

Clue 3: Also in the wallet was a mailbox key only used in the smaller buildings in Urbania. A witness saw that one of the thieves had a key card attached to his or hers belt. Such key cards are only used to access the larger building blocks in Urbania. In this setting, a large building is a building having more than 50 people living or working in it, and small otherwise. Thus, make a view containing all attributes from citizens_i, together with the each citizen’s apartment building’s size, where size is either 'large' or 'small' depending on whether the building is large or small respectively, as described above.

Hint: You should now have enough clues to find out who the initials on the score card belongs to.

Clue 4: The two thieves are also believed to both be in family, that is, they share the same surename, and collegues (i.e. work for the same orginization). Thus, make a view in_family_with(cid1, cid2) that contains all pairs of citizen’s cid that have common surename.

Combining the views and the other information in the clues should be enough to determine both of the thieves!

Exercise 2: Finding the Scroll

Your great skills with saturating and querying the data for clues lead to the identification and capture of both the thieves. However, the stolen scroll is still missing, and the thieves have hidden it somewhere.

The thieves were spotted driving into Suburbia right after the theft, so the scroll must be hidden in that part of the city. Their car makes a loud BANG whenever it is started, and many citizens of Suburbia claim to have heard such a bang. The location of this bang is probably where they started the car after hiding the scroll.

Luckily, the people that heard the bang called the police and told them in which direction, relative to their home, they heard the bang. The police quickly jotted the information down as the folowing basic graph pattern, with the home where the bang was is denoted with the variable ?bang:

PREFIX exd: <http://example.org/directional/> 
PREFIX raws: <http://leifhka.org/in5800/urb/raw_data/suburbia/> 

?bang exd:southof     [ raws:homeof "s147" ] ;
      exd:westof      [ raws:homeof "s101" ] ;
      exd:northwestof [ raws:homeof "s16" ] ;
      exd:northof     [ raws:homeof "s309" ] ;
      exd:northeastof [ raws:homeof "s77" ] ;
      exd:southeastof [ raws:homeof "s252" ] ;
      exd:eastof      [ raws:homeof "s58" ] .

where ex:homeof relates a home to the Urb Id of the person living in it, and the directional relations (exd:southof, exd:northeastof, etc.) relate homes according to their relative direction. All of the directional relationships are irreflexive, so e.g. nothing is exd:southof itself. Furthermore, e.g. X exd:northof Y if Y is on any street that is north of the street that X is on, and similar (inverse) for exd:southof. Similarly, X exd:eastof Y if Y has a street number that is greater than that of X. Lastly, X exd:northeastof Y if both X exd:northof Y and X exd:eastof Y.

To be able to use this information, one needs to know the layout of streets and houses in Suburbia. The streets are laid out from north to south according to their zip code (all addresses on one street have the same zipcode) with the smallest zip code number being the northernmost, so e.g. Blossom Street with zip code 1002 is north of Grass road with zip code 1005. Within a zip code, the streets are ordered from north to south alphabetically, so e.g. Grass road is north of Harbour road as they both are in 1005. On each street, houses are numbered from west to east, so e.g. Blossom street 2 is west of Blossom street 10. The houses form a grid, so Blossom street 5 is north west of Grass road 7.

Use this information to find out in which home the scroll is hidden! If you do not know where to start, there are some hints below to get you going.

Hint 1: Start by making street and number explicit for each home (hid) directly in the suburbia.home table. This can be done using

ALTER TABLE suburbia.home ADD COLUMN streetname text;
ALTER TABLE suburbia.home ADD COLUMN streetnumber int;

and then running an UPDATE-command setting in the data in these new columns. You should add these commands into a new file, and execute this after data/urb.sql in the relational_dbs-rule in the Makefile.

Hint 2: Directly map the the tables suburbia.citizen and suburbia.home into RDF just like you did in the oblig. Executing make clean_dbs direct_mappings will now also map the updated columns with streetname and streetnumber above.

Hint 3: Make Lore-relations for the raws.homeof-relation and the directional relations. Then, make Lore-rules that captures the directional relationships between the homes according to the information about the street layout above. E.g. homes with zip code 1001 is north of homes with zipcode 1002, etc. Similarly for east/west relationships and streetnumbers. Remember that you can use all normal datatype relations like <, <=, etc. in Lore-rules (after the colon :).

Hint 4: Finally, apply the rules and make a SPARQL-query out of the basic graph pattern you got from the police above and use Triplelore and Ontop to query it. The ?bang-variable should give you the IRI of the home where the scroll is burried!

Bonus Exercise: Translate the Scroll (Difficult!)

After having found the stolen scroll, you see that it contains the following text:

tothohe totroreasosurore isos boburorroriedod
unondoderor tothohe sosea bobotottotomom 100
mometoterorsos sosoutothoh ofof tothohe
dodocockoksos inon tothohe cocitoty ofof urorbob

Are you able to decode it? If so, it turns out that the Archeological Institute contains lots of similar scrolls, and would very much like you to make an SQL-query that can translate all of their scrolls, once digitized!

Solution

The solutions can be found here.