erDiagram
EXHIBITION }o--o| PLACE : "location"
CATALOGUE }o--o{ PERSON : "author"
CATALOGUE }o--o| ORGANIZATION : "publisher"
CATALOGUE }o--o| EXHIBITION : "catalogue_of"
ARTWORK }o--o| OBJECT_TYPE : "instance_of"
ARTWORK }o--o| PERSON : "creator"
ARTWORK }o--o{ MATERIAL : "made_from_material"
ARTWORK }o--o| PLACE : "location"
ARTWORK ||--o{ EXHIBITION_ITEM : "exhibited_at"
EXHIBITION ||--o{ EXHIBITION_ITEM : "exhibited_at"
ARTWORK ||--o{ SCAN_LOCATION : "appears_in_catalogue"
CATALOGUE ||--o{ SCAN_LOCATION : "appears_in_catalogue"
ARTWORK {
string id PK "local ID"
string title "monolingualtext"
string inception "time"
decimal height "quantity"
decimal width "quantity"
decimal depth "quantity"
string inventory_number
string image_url "url"
string iconclass "external-id"
string date_text
string object_type_text
string materials_text
string wikidata_qid "external-id"
}
PERSON {
string id PK "local ID"
string label "en and de"
string wikidata_qid "external-id"
string gnd_id "external-id"
}
EXHIBITION {
string id PK "local ID"
string title "monolingualtext"
string start_date "time"
string end_date "time"
string wikidata_qid "external-id"
}
CATALOGUE {
string id PK "local ID"
string title "monolingualtext"
string subtitle
string publication_date "time"
string isbn13 "external-id"
string dnb_edition_id "external-id"
string described_at_url "url"
string image_url "url"
string wikidata_qid "external-id"
}
ORGANIZATION {
string id PK "local ID"
string label "en and de"
string wikidata_qid "external-id"
}
MATERIAL {
string id PK "local ID"
string label "en and de"
string wikidata_qid "external-id"
string aat_id "external-id"
}
OBJECT_TYPE {
string id PK "local ID"
string label "en and de"
string wikidata_qid "external-id"
string aat_id "external-id"
}
PLACE {
string id PK "local ID"
string label "en and de"
string wikidata_qid "external-id"
}
EXHIBITION_ITEM {
string artwork_id FK
string exhibition_id FK
string catalogue_number "qualifier"
string catalogue_page "qualifier"
string title_as_exhibited "qualifier"
string note "qualifier"
}
SCAN_LOCATION {
string artwork_id FK
string catalogue_id FK
string scan_page "qualifier"
string scan_region "qualifier"
string extraction_method "qualifier"
string review_status "qualifier"
string note "qualifier"
}
Entity-relationship model
Conceptual model, SQLite EAV schema and data pipeline
Abstract
Catalogue exhibition contents using Linked Open Data and AI LLM assistants.
Keywords
Data Science, GLAM, Open Science
Generated by data-models/portability/build.py from wikibase-model.json and the CSV templates. Do not edit by hand.
Conceptual model
The classes of the Wikibase model and how the CSV files connect them. EXHIBITION_ITEM and SCAN_LOCATION are CSV files that become statements with qualifiers in Wikibase (Exhibited at and Appears in catalogue). The inverse link Exhibition catalogue is created from catalogue_of and is not drawn.
SQLite schema (Entity-Attribute-Value)
Read directly from linked-open-exhibition.sqlite. The model tables describe the attributes. The data tables hold every fact as one row in statement. Triggers reject statements whose property is not allowed on the entity’s class, whose value has the wrong kind, or whose target has the wrong class.
erDiagram
entity_class ||--o{ property_class : "class_key"
property ||--o{ property_class : "property_key"
entity_class ||--o{ property_range : "class_key"
property ||--o{ property_range : "property_key"
property ||--o{ qualifier_of : "property_key"
property ||--o{ qualifier_of : "qualifier_key"
entity_class ||--o{ entity : "class_key"
entity ||--o{ label : "entity_id"
entity o|--o{ statement : "value_entity_id"
property ||--o{ statement : "property_key"
entity ||--o{ statement : "entity_id"
property ||--o{ qualifier : "property_key"
statement ||--o{ qualifier : "statement_id"
meta {
TEXT key PK
TEXT value
}
entity_class {
TEXT class_key PK
TEXT label_en
TEXT label_de
TEXT description_en
TEXT wikidata
TEXT cidoc
TEXT linked_art
TEXT lido
}
property {
TEXT property_key PK
TEXT label_en
TEXT label_de
TEXT description_en
TEXT datatype
INTEGER is_qualifier
TEXT wikidata
TEXT cidoc
TEXT linked_art
TEXT lido
}
property_class {
TEXT property_key PK, FK
TEXT class_key PK, FK
}
property_range {
TEXT property_key PK, FK
TEXT class_key PK, FK
}
qualifier_of {
TEXT qualifier_key PK, FK
TEXT property_key PK, FK
}
entity {
TEXT entity_id PK
TEXT class_key FK
TEXT wikibase_id
TEXT source
}
label {
TEXT entity_id PK, FK
TEXT lang PK
TEXT label
}
statement {
INTEGER statement_id PK
TEXT entity_id FK
TEXT property_key FK
TEXT value_text
TEXT value_entity_id FK
TEXT lang
TEXT unit
INTEGER position
}
qualifier {
INTEGER qualifier_id PK
INTEGER statement_id FK
TEXT property_key FK
TEXT value_text
}
Setup
flowchart LR
CSV[/"CSV templates<br/>data-models/import"/]
MODEL[/"wikibase-model.json"/]
subgraph PRIVATE["Private review copy (portability/)"]
DTD["DTD"]
XML["XML<br/>validated against DTD"]
SQL[("SQLite EAV<br/>+ triggers + views")]
end
LOADER["Loader notebook<br/>wikibase-artwork-load.ipynb"]
WB[("Local Wikibase<br/>localhost:8090")]
WD[("Wikidata")]
MODEL -->|generates| DTD
MODEL -->|generates properties| LOADER
MODEL -->|seeds schema| SQL
CSV -->|build.py| XML
CSV -->|build.py| SQL
DTD -. validates .-> XML
CSV --> LOADER
LOADER --> WB
WB -. "later, via QuickStatements or API" .-> WD
SRC{{"Source of truth: not decided yet"}}
SRC -.-> CSV
SRC -.-> WB
SRC -.-> WD
style PRIVATE fill:transparent,stroke:#888,stroke-dasharray:5 5,color:#333
Example queries
-- artworks with their creator, date, materials and catalogue
SELECT * FROM v_artwork LIMIT 10;
-- every statement about one artwork
SELECT property_label, value, lang, unit FROM v_statement WHERE entity_id = 'ART001';
-- artworks that are not linked to a catalogue
SELECT entity_id, entity_label FROM v_entity e
WHERE class_key = 'artwork'
AND NOT EXISTS (SELECT 1 FROM statement s WHERE s.entity_id = e.entity_id
AND s.property_key = 'appears_in_catalogue');
-- how much of each property is filled in
SELECT p.label_en, COUNT(*) AS statements
FROM statement s JOIN property p USING (property_key)
GROUP BY p.label_en ORDER BY statements DESC;