ER 2026 · Tutorial
Building Executable Entity-Relationship Models
Joshua Send · TypeDB
ER 2026, St. John's · 7 October 2026
What is TypeDB?
A new database whose schema is a conceptual model: entities, relations, attributes.
BEFORE IT RUNS
Queries are type-checked against the model before they run.
WHEN IT FAILS
A type-check failure is an unsatisfiable-query error, not an empty result.
Open source · Rust · typedb.com · github.com/typedb/typedb
TypeDB's journey
Origin
Started with the struggle to organise data for semantic search.
Influences
Borrowed from Chen's ER model, knowledge representation, programming languages, and built towards an executable system for production use.
2024
TypeQL Formalised using type theory: SIGMOD/PODS Best Newcomer Award.
Today
A production-grade, closed-world transactional database in use at scale.
Used in
Finance, cybersecurity (e.g. STIX), manufacturing, process mining, bio/pharma, AI.
Dorn & Pribadi, TypeQL: A Type-Theoretic & Polymorphic Query Language, SIGMOD 2024 (Best Newcomer Award, SIGMOD/PODS 2024)
Chen's Entity-Relationship model
“…incorporates some of the important semantic information about the real world.”
Chen, 1976
Entities, relationships, attributes
Relationships are n-ary with named roles: r₁/e₁, …, rₙ/eₙ
Relationships can have attributes
Min/max participation, weak entities, existence dependencies
Chen, P. P. (1976). The Entity-Relationship Model: Toward a Unified View of Data. ACM TODS 1(1).
Translating ER
CHEN: LEVELS 1–2
“In our minds” → information structure
REAL SYSTEMS: LEVELS 3–4
Data structures, access paths
So every ER model gets translated into a poorer implementation model.
LOST ALONG THE WAY
relationship identity
roles
subtypes
participation constraints
Teorey, Yang & Fry (1986). A Logical Design Methodology for Relational Databases Using the Extended ER Model. ACM Computing Surveys 18(2).
Our running example: a palm-oil trade flow
7
named roles in one relationship
3
attributes on the relationship
Dashed line: the refiner is optional.
Using relational models
TABLE trade_flow
flow_id
origin_fk
mill_fk
refiner_fk (NULL ok)
exporter_fk
importer_fk
destination_fk
commodity_fk
volume
ha
usd
1
17
342
NULL
88
1203
12
1
1250.0
3.2
912400
2
17
342
51
51
977
4
2
830.5
0.0
701200
3
40
118
NULL
205
1203
12
1
410.0
11.7
296300
- Roles survive only as column names
- Optional refiner → nullable FK
- Subtypes → nullable columns, or extra tables + joins
- Participation and cardinality → not native
CHEN §4.1.1
Joining EMPLOYEE.age to SHIP.age is legal, but “the database system … should be able to warn the user”.
Using graphs: choose loss or reification
A · Chain binary edges mill → refinery → exporter → importer
B · Reify the flow as a node. Roles become edge labels; nothing enforces “exactly 1 mill”.
Executable conceptual models: the literature
Taxis, Galileo
Typed conceptual languages
OO-Method, CMP
Conceptual-model programming (Embley, Liddle, Pastor)
NIAM/ORM, ConQuer
Nijssen → Halpin; querying at the conceptual level
TYPICAL
Generate SQL or code from the model.
COST
Model and runtime drift apart.
Mylopoulos, Bernstein & Wong (1980). Taxis. ACM TODS.
Albano, Cardelli & Orsini (1985). Galileo. ACM TODS.
Embley, Liddle & Pastor (2011). Conceptual-Model Programming: A Manifesto.
What if the database was the conceptual model?
PERA: the polymorphic entity-relation-attribute model
Chen's three concepts, all first-class types: entity, relation, attribute types.
n-ary relations with named roles (relates); types declare the roles they can play.
Relations own attributes, and can play roles in other relations.
Attributes are typed values, shared between owners.
Single inheritance (sub), abstract types.
Polymorphic queries automatically resolve subtyping and composition.
Dorn & Pribadi, SIGMOD 2024: queries as types
From Chen to TypeQL
Chen
TypeQL
entity set
entity
relationship set
relation
role (r/e)
relates / plays
value set
attribute type (value string, integer, …)
attribute, a function E → V
entity owns attribute
relationship attribute
relation owns attribute
subset entity sets (MALE-PERSON ⊆ PERSON)
sub
constraints
min/max participation (@card), key (@key), values (@values, @range, @regex)
Querying
CHEN §3.4
{AGE(e) | e ∈ EMPLOYEE, WEIGHT(e) > 170, [e, eⱼ] ∈ PROJECT-WORKER, PROJECT-NO(eⱼ) = 254}
TYPEQL
match
$e isa employee, has weight $w, has age $a; $w > 170;
project_worker (worker: $e, project: $p);
$p has project_no 254;
select $a;
Type checking: Chen's “warn the user”
1
Every query is type-inferred before it runs.
2
A query no data could ever satisfy is rejected before execution.
3
The schema is enforced on every write and at commit.
EMPLOYEE AND PROJECT ROLES SWAPPED
match
$e isa employee, has weight $w, has age $a;
$w > 170;
project_worker (worker: $p, project: $e);
$p has project_no 254;
select $a;
## Result> Error
[INF11] Type-inference was unable to find compatible types for the pair of variables 'e' & '_anonymous' across a 'links' constraint. Types were:
- e: [employee]
- _anonymous: [project_worker:project]
[QUA1] Type inference error while compiling query annotations.
[QEX8] Error analysing query.
Near 3:31
-----
match
$e isa employee, has weight $w, has age $a;
$w > 170;
--> project_worker (worker: $p, project: $e);
^
$p has project_no 254;
select $a;
-----
Some differences from Chen
One most-specific type per instance.
Relations have their own identity.
Multi-valued attributes configurable using @card(0..).
Roles are optional by default: @card(0..1).
NOT YET AVAILABLE
Constraints between values (TAX < SALARY, Chen §3.3).
NOTE
Picking up cues from the wonderful presentation by Terry, questions from Sudha, and discussions with lots of you, I see room for adding far more interesting inter-concept & connection constraints in the future!
Introduction to the dataset
Trase is a nonprofit tracking supply chain sustainability, such as deforestation factors.
SOURCE
trase.earth
LICENCE
Open data, CC BY 4.0
TODAY
Indonesia palm oil supply chain, 2018–2022
Benedict, J. J., Biddle, H., Gollnow, F., Heilmayr, R., Mueller, C., Ribeiro, V., & Suavet, C. (2024). Indonesia palm oil supply chain (2018–2022) (Version 1.2) [Data set]. Trase. https://doi.org/10.48650/X83N-7M36
Sample data
One row = one trade flow: palm oil sourced from a district (kabupaten), processed at a mill, maybe refined, exported by a company, imported by a company into a country.
csv
country_of_production, province_of_production, kabupaten_of_production,
mill, mill_group, refinery, refinery_group, exporter, exporter_group, importer, importer_group,
country_of_first_import, product_type, volume, fob, palm_oil_deforestation_10_year_total_exposure
CAVEAT
Flows are modelled, not real shipments.
SUBSAMPLED
16 of 35 columns · 81 rows of 1.7M
Let's get set up!
WHAT WE'LL NEED
A TypeDB server
A web browser
Instructions on the tutorial page
1
PART 1
Building the model
Jumping in (1)
Convert easy columns into types
column
value
year
2022
country_of_production
INDONESIA
product_type
PALM OIL
forest_500_palm_oil
3
zero_deforestation_indonesia_palm_oil
NDPE COMMITMENT
province_of_production
KALIMANTAN TIMUR
column
value
kabupaten_of_production
BERAU
mill
AGRO INDOMAS (BUMI JAYA)
mill_trase_id
ID-PALM-MILL-00011
mill_group
GOODHOPE
refinery
NOT REFINED
What would you start modelling?
Jumping in (2): places
Jumping in (3): encode it directly in TypeQL
SNIPPET 1
define
attribute name, value string;
### Places ###
attribute country_name, sub name;
attribute province_name, sub name;
attribute kabupaten_name, sub name;
entity place @abstract;
entity country,
sub place,
owns country_name @key;
entity province,
sub place,
owns province_name @key;
entity kabupaten,
sub place,
owns kabupaten_name @key;
Places contain places, presumably
SNIPPET 2
define
### Places hierarchy ###
relation containment,
relates parent @card(1),
relates child @card(1);
entity country,
plays containment:parent;
entity province,
plays containment:child @card(0..1), # at most 1 parent
plays containment:parent;
entity kabupaten,
plays containment:child @card(0..1); # at most 1 parent
Getting stuck
Columns aren't necessarily easy entities or attributes.
mill
mill_group
refinery
refinery_group
exporter
exporter_group
AGRI EASTBORNEO KENCANA
KENCANA AGRI
NOT REFINED
NOT REFINED
SUMBER HIJAU UTAMA
ROYAL GOLDEN EAGLE
NIAGA MAS GEMILANG
JC CHEMICAL
LDC EAST INDONESIA
LOUIS DREYFUS
LDC EAST INDONESIA
LOUIS DREYFUS
TAPIAN NADENGGAN (JAK LUAY)
SINAR MAS
SMART TBK (SURABAYA REFINERY)
SINAR MAS
SINAR MAS AGRO RESOURCES AND TECHNOLOGY
SINAR MAS
DWIWIRA LESTARI JAYA
TRIPUTRA AGRO PERSADA
LDC EAST INDONESIA
LOUIS DREYFUS
LDC EAST INDONESIA
LOUIS DREYFUS
The same names appear in different capacities. Are a mill and a mill group different entities? A refinery and a refinery group? An exporter and an exporter group?
Mills, refineries, exporters (1)
OPTION 1
entity mill;
entity mill_group;
entity refinery;
entity refinery_group;
entity exporter;
entity exporter_group;
Data sheet: “*_group groups the * column into parent companies where applicable.”
Mills and refineries are semantically closer to each other than to exporters.
What alternative representation could we adopt?
Mills, refineries, exporters (2)
OPTION 2
We only have companies and facilities, with ownership between them (the “groups”).
“Exporter” is a role that companies play.
Companies and facilities in TypeQL
SNIPPET 3
define
### Companies and facilities ###
attribute facility_name, sub name;
attribute mill_name, sub facility_name;
attribute refinery_name, sub facility_name;
attribute company_name, sub name;
entity legal_entity @abstract;
entity facility @abstract,
sub legal_entity,
plays ownership:owned @card(0..1); # at most 1 owner
entity mill,
sub facility,
owns mill_name;
entity refinery,
sub facility,
owns refinery_name;
entity company,
sub legal_entity,
owns company_name @key,
plays ownership:owner;
relation ownership,
relates owner @card(1),
relates owned @card(1);
Companies & facilities: even more interesting
mill
mill_group
refinery
refinery_group
exporter
exporter_group
DWIWIRA LESTARI JAYA
TRIPUTRA AGRO PERSADA
LDC EAST INDONESIA
LOUIS DREYFUS
LDC EAST INDONESIA
LOUIS DREYFUS
LDC EAST INDONESIA is named as both the refinery and the exporter!
Interpretation: it is a company that owns the refinery and handles exports; LOUIS DREYFUS is its parent company.
The model doesn't yet allow companies to own companies, so let's add it
SNIPPET 4
define
# companies can also be owned (by a parent company)
entity company,
plays ownership:owned @card(0..1);
Commodities (1)
Two commodities: PALM OIL and REFINED PALM OIL. How would you model this?
a)
1 entity type with an attribute
commodity
owns commodity_name
b)
2 independent entity types
palm_oil
refined_palm_oil
c)
1 abstract type, 2 subtypes
commodity @abstract
palm_oil
refined_palm_oil
Commodities (2)
I go with a), the simplest.
SNIPPET 5
define
### Commodities ###
attribute commodity_name,
sub name,
value string @values("PALM OIL", "REFINED PALM OIL"); # validated enum
entity commodity,
owns commodity_name @key;
Challenge: implement the 2-instances maximum with model c).
Trade flows (1)
How would you model the trade flow itself?
Trade flows (2)
SNIPPET 6
define
### Trade flow ###
attribute volume_tonnes, value double;
attribute deforestation_ha, value double;
attribute usd, value decimal;
relation trade_flow,
relates origin @card(1),
relates mill @card(1),
relates refiner @card(0..1), # optional!
relates exporter @card(1),
relates importer @card(1),
relates destination @card(1),
relates commodity @card(1),
owns volume_tonnes @card(1),
owns deforestation_ha @card(1),
owns usd @card(1);
entity kabupaten, plays trade_flow:origin;
entity mill, plays trade_flow:mill;
entity refinery, plays trade_flow:refiner;
entity company,
plays trade_flow:importer,
plays trade_flow:exporter;
entity country,
plays trade_flow:destination;
entity commodity,
plays trade_flow:commodity;
A single 6- or 7-way relation: type-checked and enforced by the database.
2
PART 2
The data
Grab the data from the tutorial page (one insert query), then paste it into Studio:
We won't look closely at data loading today.
3
PART 3
Querying
Look up all countries and their names
basic pattern matching
match $c isa country, has country_name $n;
Get all facilities and their types, polymorphically
polymorphic queries
QUERY
match
$f isa facility;
$f isa! $t;
EXTENSION: COUNT BY FACILITY TYPE
match
$f isa facility;
$f isa! $t;
reduce $count = count groupby $t;
Trade flows
relations, aggregation, negation
Q1 · COUNT BY IMPORT COUNTRY
match
$tf isa trade_flow, links (destination: $country);
$country has country_name $name;
reduce $count = count groupby $name;
Q2 · UNREFINED FLOWS
match
$tf isa trade_flow;
not { $tf links (refiner: $r); };
A sustainability question
challenge: deforestation exposure by importer
match
$f isa trade_flow, links (importer: $i), has deforestation_ha $d;
$i has company_name $importer;
reduce $total_ha = sum($d) groupby $importer;
sort $total_ha desc;
EXPECTED TOP RESULTS
2,764 ha
LOUIS DREYFUS
2,016 ha
FIRST RESOURCES
580 ha
GOLDEN AGRI INTERNATIONAL
Facility ownerships (1)
disjunction
Facilities can have nested company ownership. Can one query get all owners?
Q1 · ONE AND TWO HOPS, EXPLICITLY
match
$f isa facility, has name $facility_name; # polymorphic facility & name types
$facility_name == "LDC EAST INDONESIA";
{ ownership (owned: $f, owner: $c); }
or {
ownership (owned: $f, owner: $middle);
ownership (owned: $middle, owner: $c);
};
$c has name $company_name;
ANSWER
$company_name
type
LOUIS DREYFUS
company
LDC EAST INDONESIA
company
Both for the refinery LDC EAST INDONESIA.
Facility ownerships (2)
functions & recursion
with fun owners($f: legal_entity) -> { company }:
match
{ ownership (owned: $f, owner: $c); }
or {
ownership (owned: $f, owner: $middle);
let $c in owners($middle);
};
return { $c };
match
$f isa facility, has name $facility_name;
$facility_name == "LDC EAST INDONESIA";
let $c in owners($f);
$c has name $company_name;
Turn it into a recursive function.
owners($f: legal_entity) works for facilities and companies alike.
Type checking in action
type checking & schema enforcement
Q1 · INSERT AN ABSTRACT TYPE
insert $f isa facility;
TYPE ERROR
facility is abstract
Q2 · COUNTRIES PLAYING THE MILL ROLE
match
$c isa country;
trade_flow (mill: $c);
TYPE ERROR
country cannot play trade_flow:mill
Q3 · A COUNTRY WITHOUT A COUNTRY NAME
insert $c isa country;
REJECTED AT COMMIT
every country needs keyed country_name
Querying the model
schema queries
Q1 · TYPES THAT PLAY ANY ROLE IN TRADE_FLOW
match
trade_flow relates $role;
$t plays $role;
Q2 · LEGAL ENTITY TYPES THAT CANNOT OWN
match
$l sub legal_entity;
not { $l plays ownership:owner; };
The result includes legal_entity itself.
Q3 · QUERY ACROSS DATA AND TYPES
match
$x isa! $t;
$x has $a;
$a contains "OIL";
$t plays $role;
trade_flow relates $role;
The model is data too: the same language queries types, roles and instances.