There are several ways to do this, but here's 'David Spivak's trick', which can be packaged into a design pattern:
Define a PK-schema to be a map of schemas i: C0-->C with the properties that
C0 has discrete entity side, finitely many attributes, and no equations, and
i is identity on entity objects (i.e.obs.)
For an object c in C, let c_0 be the unique object with i(c_0)=c. Note that for c in C, we think of the attributes of c_0 as the primary key columns of c. Each table in C has a unique primary key, namely the set of columns in c_0.
Given a PK-schema i: C0-->C, we form a new schema S=SQL(i) as follows:
for each entity c of C, put an entity of the same name, c, in S;
for each attribute att: c--> tau in C, put an attribute of the same name and type in S, i.e., att: c-->tau;
for each foreign key f: c-->c' in C, and each pk column att: c'_0 --> tau on c', put an attribute f_att: c--> tau on c in S.
We have effectively replaced a PK-schema C with its relational version. Now there is an induced i.e.obs. functor q_C: SQL(C)-->C. Send each entity to itself, each attribute of the form att:c-->tau to the attribute of the same name and type in C, and each attribute of the form f_att: c-->tau to the observable (att o f): c-->c'-->tau in C.
There are several ways to do this, but here's 'David Spivak's trick', which can be packaged into a design pattern:
Define a PK-schema to be a map of schemas i: C0-->C with the properties that C0 has discrete entity side, finitely many attributes, and no equations, and i is identity on entity objects (i.e.obs.) For an object c in C, let c_0 be the unique object with i(c_0)=c. Note that for c in C, we think of the attributes of c_0 as the primary key columns of c. Each table in C has a unique primary key, namely the set of columns in c_0.
Given a PK-schema i: C0-->C, we form a new schema S=SQL(i) as follows: for each entity c of C, put an entity of the same name, c, in S; for each attribute att: c--> tau in C, put an attribute of the same name and type in S, i.e., att: c-->tau; for each foreign key f: c-->c' in C, and each pk column att: c'_0 --> tau on c', put an attribute f_att: c--> tau on c in S. We have effectively replaced a PK-schema C with its relational version. Now there is an induced i.e.obs. functor q_C: SQL(C)-->C. Send each entity to itself, each attribute of the form att:c-->tau to the attribute of the same name and type in C, and each attribute of the form f_att: c-->tau to the observable (att o f): c-->c'-->tau in C.
Now Pi along q_C will be the query you want.